SQL语句（综合实战篇）

更新: 12/31/2024 字数: 0 字时长: 0 分钟

特别鸣谢

前言

在经过了前面的基础部分学习，我们再来看一些复杂的场景

准备

创建表

sql

--建表
--学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');

--教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

--成绩表测试数据
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);

表结构总结

student 为学生表记录了学生的id 名字生日性别

teacher 为教师表记录了教师id 教师姓名

course 为课程表记录了课程id 课程名称上课老师id 与教师表是一对一的关系

score 为成绩表记录了学生的id 课程的id 成绩分数与课程表学生表均是一对多的关系

select语句回顾

sql

select 列名1,列名2,... 
from 表名
where 查询条件
group by 分组查询—————对查询结果分组
having 分组的条件
order by 列名  排序顺序 （desc降序 asc 升序）
limit 起始行, 行数  对查询结果抽取一部分作为最终结果

执行顺序优先级

FROM：从指定的表或视图中获取数据。这一步可能会涉及多个表的连接（JOIN），或者通过子查询获取数据。
WHERE：应用过滤条件，筛选符合条件的记录。只有满足 WHERE 条件的记录才会进入下一步。
GROUP BY：在过滤后的数据上，将记录分组。根据指定的列对数据进行分组，形成不同的组。
HAVING：对分组后的数据进行过滤。HAVING 类似于 WHERE，但作用于分组的结果上。通常用于筛选聚合条件。
SELECT：从分组和筛选后的数据中提取指定的列或表达式。可以在这一步使用聚合函数（如 SUM, AVG, COUNT 等）进行计算。
DISTINCT：去除重复的记录，使得每一条记录唯一。
ORDER BY：对结果集进行排序。可以按一个或多个列排序，默认为升序排序，可以使用 DESC 指定降序。
LIMIT / OFFSET：限制返回的记录数。LIMIT 用于指定返回的最大记录数，而 OFFSET 用于指定跳过的记录数。

查询"01"课程比"02"课程成绩高的学生的信息及课程分数(偏难)

知识点：自连接

自连接表示自己连接自己，我们可以在where中将同一个表查询两次，然后通过取别名的方式来区分两个表，进而对一个表符合特定关系的行或列之间的匹配

sql

select st.* ,sc1.s_score,sc2_score
from student st, score sc1,score sc2
where st.s_id=sc1.s_id
and st.s_id=sc2.s_id
and sc1.c_id='01'
and sc2.c_id='02'
and sc1.s_score>sc2.s_score

查询"01"课程比"02"课程成绩低的学生的信息及课程分数

sql

select st.*,sc1.s_score,sc2.s_score
from student st,score sc1,score sc2
where st.s_id=sc1.s_id
and st.s_id=sc2.s_id
and sc1.c_id='01'
and sc2.c_id='02'
and sc1.s_score<sc2.s_score

查询平均成绩大于等于60分的每个同学的学生编号和学生姓名和平均成绩

知识点：having与group by

聚合函数不能直接作为判断条件出现在where语句中，若想使用聚合函数的结果来进行判断，自能使用分组查询与having关键字

该场景体现出分组查询的好处，在一对多情况下对多的那一方在数据库层面就完成了数据的统计

sql

select st.s_id,st.s_name,avg(sc.s_score)
from score sc,student st
where sc.s_id=st.s_id
group by st.s_id
having avg(sc.s_score)>=60

查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

补充知识点

round函数表示对一个小数进行指定位数的保留，两个参数中第一个参数为小数，第二个参数为小数的要保留的位数

sql

select stu.s_id,stu.s_name,ROUND(avg(sc.s_score),2) avgsc
from student stu,score sc
where stu.s_id=sc.s_id
group by stu.s_id
having avg(sc.s_score)<60 or avgsc is null

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

注意

对于一对多的场景中，若想对多的那一方进行聚合计算，应该使用group by函数

因为不使用分组且多表的情况下，聚合函数无法明确聚合函数针对某一行的对应关系

sql

select s.s_id ,s.s_name,sum(sc.c_id),sum(sc.s_score)
from student s
left join score sc on sc.s_id=s.s_id
group by s.s_id,s.s_name

查询"李"姓老师的数量

模糊搜索，没啥好说的

sql

select count(1) from teacher where t_name like '李%'

查询学过"张三"老师授课的同学的信息

逆天连表,为了面试学习即可

阿里巴巴代码规范：禁止超过三张表 join

sql

select t.t_name,s.*
from student s,teacher t,course c,score sc
where s.s_id=sc.s_id
and sc.c_id=c.c_id
and c.t_id=t.t_id
and t.t_name='张三'

知识点:子查询

本题代码还可以替换为

sql

SELECT st.*
FROM student st
WHERE st.s_id IN (SELECT s_id FROM score WHERE c_id = 
                  (SELECT c_id FROM course WHERE t_id =
                   (SELECT t_id FROM teacher WHERE t_name = '张三' )))

子查询（Subquery）是嵌套在其他查询中的 SELECT 语句，用于在主查询中引用其返回的结果。

子查询本身也是一个查询语句，因此他返回的结果可以是一个表，一个值，一个链表（只有一列的表）

故子查询在语句中也可以等效替代上面三种情况

查询没学过"张三"老师授课的同学的信息

没学过张三老师课的学生=不是学过张三老师课的学生

sql

select s.*
from  student s
where s.s_id 
not in (select s.s_id
        from student s,teacher t,course c,score sc
        where s.s_id=sc.s_id
        and sc.c_id=c.c_id
        and c.t_id=t.t_id
        and t.t_name='张三')

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

sql

select s.*
from student s,score sc1 ,score sc2
where s.s_id=sc1.s_id
and sc2.s_id=s.s_id
and sc1.c_id='01'
and sc2.c_id='02'

查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息(偏难)

sql

select s.*
from student s
where s.s_id in
      (select s.s_id from student s , score sc where s.s_id=sc.s_id and sc.c_id='01')
  and s.s_id not in
      (select s.s_id from student s, score sc where s.s_id=sc.s_id and sc.c_id='02')

查询没有学全所有课程的同学的信息

sql

select s.s_id courseCount
from student s,score sc
where s.s_id=sc.s_id
group by s.s_id
having count(sc.c_id)!=(select count(c.c_id) from course c)

查询至少有一门课与学号为"01"的同学所学相同的同学的信息

sql

select distinct s.*
from student s ,score sc
where s.s_id=sc.s_id
  and sc.c_id in (select c_id
                  from score sc
                  where sc.s_id='01')

查询和"01"号的同学学习的课程完全相同的其他同学的信息

这个相对就比较考验sql语句执行顺序的了解和子查询的熟练度了

首先先把score和student连起来，以sid相同为条件

接着仅保留所学课程在01学生所学的课程中的行

接着进行分组并统计所学课程数

此时若课程数一致则说明和"01"号的同学学习的课程完全相同

sql

select s.*
from student s,score sc
where s.s_id=sc.s_id
and sc.c_id in (select sc.c_id from score sc where sc.s_id ='01')
and s.s_id!=01
group by s.s_id
having count(sc.c_id) = (select count(sc.c_id) from score sc where sc.s_id='01')

查询没学过"张三"老师讲授的任一门课程的学生姓名

要不就是学过，要不就是没学过

sql

SELECT
	*
FROM
	student
WHERE
	s_id NOT IN (
		SELECT
			st.s_id
		FROM
			student st,
			score sc
		WHERE
			st.s_id = sc.s_id
		AND c_id IN (
			SELECT
				c_id
			FROM
				teacher te
			LEFT JOIN course c ON te.t_id = c.t_id
			WHERE
				t_name = '张三'
		)
	)

sql

select s.s_id,s_name,avg(sc.s_score)
from student s,score sc,course c
where s.s_id=sc.s_id
and sc.c_id=c.c_id
and sc.s_score<60
group by s.s_id
having count(1)>=2

sql

select s.s_id,s_name,avg(sc.s_score)
from student s,score sc,course c
where s.s_id=sc.s_id
and sc.c_id=c.c_id
and sc.s_score<60
group by s.s_id
having count(1)>=2

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

sql

select s.s_id,s_name,avg(sc.s_score)
from student s,score sc,course c
where s.s_id=sc.s_id
and sc.c_id=c.c_id
and sc.s_score<60
group by s.s_id
having count(1)>=2

检索"01"课程分数小于60，按分数降序排列的学生信息

sql

select s.s_id,s_name
from student s,score sc
where s.s_id=sc.s_id
and sc.c_id='01'
and sc.s_score<60
order by s_score desc

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

这个题的知识点在于将子查询放在了select的位置充当一个值

同时设计了子查询可以使用主查询的表中的数据

sql

select s.s_name,
(select sc.s_score from score sc where s.s_id=sc.s_id and sc.c_id='01') '01',
(select sc.s_score from score sc where s.s_id=sc.s_id and sc.c_id='02') '02',
(select sc.s_score from score sc where s.s_id=sc.s_id and sc.c_id='03') '03',
avg(sc.s_score)
from student s
left join score sc on s.s_id=sc.s_id
group by s.s_id
order by avg(sc.s_score) desc

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

这里对聚合函数有了更加深层次的应用

聚合函数本身是遍历整个分组，然后将每次参数的值累加最后获得的结果

sql

select sc.c_id,c_name,max(s_score),min(s_score),ROUND(avg(s_score),2) '平均分',
ROUND(100*(SUM(case when sc.s_score>=60 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '及格率',
ROUND(100*(SUM(case when sc.s_score>=70 and sc.s_score<80 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '中等率',
ROUND(100*(SUM(case when sc.s_score>=80 and sc.s_score<90 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '优良率',
ROUND(100*(SUM(case when sc.s_score>=90 then 1 else 0 end)/SUM(case when sc.s_score then 1 else 0 end)),2) as '优秀率'
from course c,score sc where c.c_id=sc.c_id group by sc.c_id

按各科成绩进行排序，并显示排名

sql

select c.c_name,s.s_name,sc.s_score
from student s,score sc,course c
where sc.c_id=c.c_id
and sc.s_id=s.s_id
order by c.c_name,sc.s_score desc

查询学生的总成绩并进行排名

sql

select s_name, sum(sc.s_score)
from score sc,student s
where sc.s_id=s.s_id
group by s.s_name
order by sum(sc.s_score) desc

查询不同老师所教不同课程平均分从高到低显示

sql

select t.t_name,c.c_name,avg(sc.s_score)
from teacher t,course c,score sc
where t.t_id=c.t_id
and c.c_id=sc.c_id
group by t.t_name,c.c_name
order by avg(sc.s_score) desc

统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

sql

SELECT
    c.c_id AS 课程编号,
    c.c_name AS 课程名称,
    SUM(CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '100-85分人数',
    CONCAT(ROUND(SUM(CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) / COUNT(sc.s_score) * 100, 2), '%') AS '100-85分百分比',
    SUM(CASE WHEN sc.s_score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS '85-70分人数',
    CONCAT(ROUND(SUM(CASE WHEN sc.s_score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) / COUNT(sc.s_score) * 100, 2), '%') AS '85-70分百分比',
    SUM(CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS '70-60分人数',
    CONCAT(ROUND(SUM(CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) / COUNT(sc.s_score) * 100, 2), '%') AS '70-60分百分比',
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) AS '0-60分人数',
    CONCAT(ROUND(SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) / COUNT(sc.s_score) * 100, 2), '%') AS '0-60分百分比'
FROM
    score sc
        JOIN
    course c ON sc.c_id = c.c_id
GROUP BY
    c.c_id, c.c_name
ORDER BY
    c.c_id;

SQL语句（综合实战篇） ​

特别鸣谢 ​

前言 ​

准备 ​

查询"01"课程比"02"课程成绩高的学生的信息及课程分数(偏难) ​

查询"01"课程比"02"课程成绩低的学生的信息及课程分数 ​

查询平均成绩大于等于60分的每个同学的学生编号和学生姓名和平均成绩 ​

查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的) ​

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 ​

查询"李"姓老师的数量 ​

查询学过"张三"老师授课的同学的信息 ​

查询没学过"张三"老师授课的同学的信息 ​

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 ​

查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息(偏难) ​

查询没有学全所有课程的同学的信息 ​

查询至少有一门课与学号为"01"的同学所学相同的同学的信息 ​

查询和"01"号的同学学习的课程完全相同的其他同学的信息 ​

查询没学过"张三"老师讲授的任一门课程的学生姓名 ​

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 ​

检索"01"课程分数小于60，按分数降序排列的学生信息 ​

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ​

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 ​

按各科成绩进行排序，并显示排名 ​

查询学生的总成绩并进行排名 ​

查询不同老师所教不同课程平均分从高到低显示 ​

统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 ​