SQL语句实战学习(几乎涵盖了所有的SQL使用场景)

最新推荐文章于 2023-02-28 12:43:24 发布

心的步伐

最新推荐文章于 2023-02-28 12:43:24 发布

阅读量3.3k

点赞数 15

分类专栏： MySQL 文章标签： sql mysql

本文链接：https://blog.csdn.net/cao1315020626/article/details/111711332

版权

MySQL 专栏收录该内容

3 篇文章 2 订阅

订阅专栏

SQL语句实战

文章目录

SQL语句实战

内容参考： https://zhuanlan.zhihu.com/p/38354000
文章包含所有的建表语句，以及数据插入，可以直接使用！加油啊！

1. 数据准备

表：学生表student、成绩表score、课程表course、教师表teacher

-- 构建学生表student
drop table student;
create table student(
`id` varchar(20) not null primary key comment '学号',
`name` varchar(20) not null comment '姓名',
`brith` date not null comment '出生日期',
`sex` varchar(5) not null
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 向student中插入数据
insert into student (`id`,`name`,`brith`,`sex`) values
('0001','曹喜嗯','1996-01-01','男'),
('0002','曹喜嗯','1994-02-01','女'),
('0003','张曼玉','1964-09-20','女'),
('0004','周星驰','1962-06-22','男');

-- 构建成绩表score
drop table score;
create table score(
`stu_id` varchar(20) not null comment '学号',
`course_id` varchar(20) not null comment '课程号',
`grade` float(3) not null comment '成绩',
primary key(`stu_id`,`course_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
insert into score (`stu_id`,`course_id`,`grade`) values
('0001','0001',80),('0001','0002',90),('0001','0003',99),
('0002','0001',60),('0002','0002',80),('0002','0003',88),
('0003','0001',80),('0003','0002',80),('0003','0003',80);


-- 构建课程表course
drop table course;
create table course(
`id` varchar(20) not null primary key comment '课程号',
`name` varchar(20) not null comment '课程名称',
`teacher_id` varchar(20) not null comment '教师号'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
insert into course (`id`,`name`,`teacher_id`) values
('0001','语文','0002'),
('0002','数学','0001'),
('0003','英语','0003');

-- 教师表teacher
drop table teacher;
create table teacher(
`id` varchar(20) not null primary key comment '教师号',
`name` varchar(20) comment '教师姓名'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
insert into teacher(`id`,`name`) values
('0001','陈独秀'),
('0002','王后雄'),
('0003',null),
('0004','');
-- 查看所有数据
select * from student;
select * from score;
select * from course;
select * from teacher;

mysql> select * from student;
+------+-----------+------------+-----+
| id   | name      | brith      | sex |
+------+-----------+------------+-----+
| 0001 | 曹喜嗯    | 1996-01-01 | 男  |
| 0002 | 曹喜嗯    | 1994-02-01 | 女  |
| 0003 | 张曼玉    | 1964-09-20 | 女  |
| 0004 | 周星驰    | 1962-06-22 | 男  |
+------+-----------+------------+-----+
mysql> select * from score;
+--------+-----------+-------+
| stu_id | course_id | grade |
+--------+-----------+-------+
| 0001   | 0001      |    80 |
| 0001   | 0002      |    90 |
| 0001   | 0003      |    99 |
| 0002   | 0001      |    60 |
| 0002   | 0002      |    80 |
| 0002   | 0003      |    88 |
| 0003   | 0001      |    80 |
| 0003   | 0002      |    80 |
| 0003   | 0003      |    80 |
+--------+-----------+-------+
mysql> select * from course;
+------+--------+------------+
| id   | name   | teacher_id |
+------+--------+------------+
| 0001 | 语文   | 0002       |
| 0002 | 数学   | 0001       |
| 0003 | 英语   | 0003       |
+------+--------+------------+
mysql> select * from teacher;
+------+-----------+
| id   | name      |
+------+-----------+
| 0001 | 陈独秀    |
| 0002 | 王后雄    |
| 0003 | NULL      |
| 0004 |           |
+------+-----------+

2. SQL案例

2.1 简单查询

2.1.1 查询姓"曹"的学生名单

此处使用like模糊查询%放在前面表示前面是任意字符，放在后面表示以某个开始后面是任意字符，如果前后都有，则查询包含其中字符的所有数据，前后都是任意字符。

mysql> select * from student where name like '曹%';
+------+-----------+------------+-----+
| id   | name      | brith      | sex |
+------+-----------+------------+-----+
| 0001 | 曹喜嗯    | 1996-01-01 | 男  |
| 0002 | 曹喜嗯    | 1994-02-01 | 女  |
+------+-----------+------------+-----+

2.1.2 查询姓名中最后一个是"玉"的学生名单

mysql> select * from student where name like '%玉';
+------+-----------+------------+-----+
| id   | name      | brith      | sex |
+------+-----------+------------+-----+
| 0003 | 张曼玉    | 1964-09-20 | 女  |
+------+-----------+------------+-----+

2.1.3 查询姓名中带"星"的学生名单

mysql> select * from student where name like '%星%';
+------+-----------+------------+-----+
| id   | name      | brith      | sex |
+------+-----------+------------+-----+
| 0004 | 周星驰    | 1962-06-22 | 男  |
+------+-----------+------------+-----+

2.2 汇总查询

2.2.1 查询课程编号为"0002"的总成绩

涉及到汇总，则需要使用聚合函数中的sum()函数对总的查询记录进行总和计算。

mysql> select sum(grade) as '成绩和' from score where course_id='0002';
+-----------+
| 成绩和    |
+-----------+
|       250 |
+-----------+

2.2.2 查询选了课程的学生人数

即查询学生中有多少选了课程

一个学生可能选择多个课程，则会被统计多次，为了避免相同数据被统计多次，可以使用distinct实现去重

mysql> select count(distinct stu_id) as "选了课程的学生人数" from score;
+-----------------------------+
| 选了课程的学生人数          |
+-----------------------------+
|                           3 |
+-----------------------------+

2.3 分组查询

2.3.1 查询各科成绩最高和最低得分

因为是对于每一科来说，所以如果是获取最高和最低得分的话，需要按照course_id进行分组，方可将每一科的成绩取出来。

对于最大最小成绩可以使用max()/min()函数实现

mysql> select course_id,max(grade) as max_score,min(grade) as min_score from score group by course_id;
+-----------+-----------+-----------+
| course_id | max_score | min_score |
+-----------+-----------+-----------+
| 0001      |        80 |        60 |
| 0002      |        90 |        80 |
| 0003      |        99 |        80 |
+-----------+-----------+-----------+

2.3.2 查询每门课程被选修的学生数

对于每门课程来说，选修的学生数是按照学生的stu_id来获取的，此时需要按照course_id进行分组。

统计人数可以使用count()函数实现

mysql> select course_id,count(stu_id) as '人数' from score group by course_id;
+-----------+--------+
| course_id | 人数   |
+-----------+--------+
| 0001      |      3 |
| 0002      |      3 |
| 0003      |      3 |
+-----------+--------+

2.3.3 查询学生中男、女人数

学生中的男女则表明性别是要作为分组的对象，故按照sex进行分组

xx

mysql> select sex,count(*) as '人数' from student group by sex;
+-----+--------+
| sex | 人数   |
+-----+--------+
| 女  |      2 |
| 男  |      2 |
+-----+--------+

2.4 带条件的分组查询

2.4.1 查询平均成绩大于60分学生的学号和平均成绩

对于分组之后利用条件过滤的可以使用having关键字，其作用类似于where

mysql> select stu_id as '学号',avg(grade) as '平均成绩' from score group by stu_id having avg(grade) > 60;
+--------+-------------------+
| 学号   | 平均成绩          |
+--------+-------------------+
| 0001   | 89.66666666666667 |
| 0002   |                76 |
| 0003   |                80 |
+--------+-------------------+

2.4.2 查询至少选修两门课程的学生学号以及课程数目

即利用学号分组，对学号分组后的每一个学号的课程号进行统计，然后使用having进行条件的筛选

mysql> select stu_id as '学号',count(course_id) as '课程数目' from score group by stu_id having count(course_id) >= 2;
+--------+--------------+
| 学号   | 课程数目     |
+--------+--------------+
| 0001   |            3 |
| 0002   |            3 |
| 0003   |            3 |
+--------+--------------+

2.4.3 查询同名同姓学生名单并统计同名人数

是否同名，即查看分组之后统计姓名一栏，如果是大于等于2即表示出现了同名，此时利用count()对其进行统计，即可查询出同名同姓学生名单及人数

mysql> select name,count(*) as '人数' from student group by name having count(*) >= 2;
+-----------+--------+
| name      | 人数   |
+-----------+--------+
| 曹喜嗯    |      2 |
+-----------+--------+

2.4.4 查询不及格的课程并按照课程号从大到小排序

因为数据中所有成绩都是大于60分的，所以按照70分及格线计算吧，即查询课程成绩小于70分，对结果按照课程号从大到小排序(desc)。

下面是参照85分计算的

mysql> select course_id,grade from score where grade < 85 order by course_id desc;
+-----------+-------+
| course_id | grade |
+-----------+-------+
| 0003      |    80 |
| 0002      |    80 |
| 0002      |    80 |
| 0001      |    80 |
| 0001      |    60 |
| 0001      |    80 |
+-----------+-------+

2.4.5 查询每门课程的平均成绩，结果按照平均成绩升序排序，平均成绩相同时，按照课程好降序排序

每门课程的平均成绩即需要按照课程分组course_id，计算平均成绩可以采用avg()，最后的升序则asc，降序则desc

mysql> select course_id,avg(grade) from score group by course_id order by avg(grade) asc,course_id desc;
+-----------+-------------------+
| course_id | avg(grade)        |
+-----------+-------------------+
| 0001      | 73.33333333333333 |
| 0002      | 83.33333333333333 |
| 0003      |                89 |
+-----------+-------------------+

2.4.6 查询其中课程编号为"0003"且分数小于90的学生学号，结果按照分数降序排列

即通过课程编号和成绩进行直观的筛选即可，最后对结果进行分数的降序

mysql> select stu_id,grade from score where course_id='0003' and grade < 90 order by grade desc;
+--------+-------+
| stu_id | grade |
+--------+-------+
| 0002   |    88 |
| 0003   |    80 |
+--------+-------+

2.4.7 查询课程号和选修此课程人数，查询结果按照人数排序降序，若人数相同，按照课程号升序排序

需要查询的是课程号和此课程选修的人数，此时需要按照课程号分组，对学生的stu_id进行统计，最后对结果按照要求升序降序即可。

因为对于课程号来说，如果按照课程号分组，此时相同课程号会被归为一组，而后对于一个课程号来说可能对应1个或者多个学生，此时使用聚合函数对其进行处理即可将这些数据变为一行一列的数据输出。

mysql> select course_id,count(stu_id) as num from score group by course_id order by num desc,course_id asc;
+-----------+-----+
| course_id | num |
+-----------+-----+
| 0001      |   3 |
| 0002      |   3 |
| 0003      |   3 |
+-----------+-----+

2.4.8 查询两门以上成绩不满85分的同学的学号及其平均成绩

首先需要查询的是学号和平均成绩，首先查询出学号和平均成绩
select stu_id, avg(grade) from score;
条件要求成绩不满85分，且需要两门以上
select stu_id, avg(grade) from score where grade < 85 group by stu_id having count(course_id) >= 2;

mysql> select stu_id, avg(grade) from score where grade <= 85 group by stu_id having count(course_id) >= 2;
+--------+------------+
| stu_id | avg(grade) |
+--------+------------+
| 0002   |         70 |
| 0003   |         80 |
+--------+------------+

2.4.9 查询各科成绩前两名的记录

这里采用的是提取每一科的成绩的前两名。然后使用union来将所有的结果连接起来。

但是如果存在多门课程(如1000门)的话，此时要想取每一科的前两名该如何操作呢

mysql> (select stu_id, course_id, grade from score where course_id = '0001' order by grade desc limit 2)
    -> union
    -> (select stu_id, course_id, grade from score where course_id = '0002' order by grade desc limit 2)
    -> union
    -> (select stu_id, course_id, grade from score where course_id = '0003' order by grade desc limit 2);
+--------+-----------+-------+
| stu_id | course_id | grade |
+--------+-----------+-------+
| 0001   | 0001      |    80 |
| 0003   | 0001      |    80 |
| 0001   | 0002      |    90 |
| 0002   | 0002      |    80 |
| 0001   | 0003      |    99 |
| 0002   | 0003      |    88 |
+--------+-----------+-------+

2.5 汇总查询

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

按照学号分组可以得到一个学生会对应多门成绩，此时对多门成绩使用聚合函数sum()求和即可解决问题。

mysql> select stu_id,sum(grade) as sum from score group by stu_id order by sum ;
+--------+------+
| stu_id | sum  |
+--------+------+
| 0002   |  228 |
| 0003   |  240 |
| 0001   |  269 |
+--------+------+

2.5.2 查询平均成绩大于80分的学生的学号和平均成绩

求出学号和平均成绩之后，将条件加入即可

mysql> select stu_id, avg(grade) from score group by stu_id having avg(grade) > 80;
+--------+-------------------+
| stu_id | avg(grade)        |
+--------+-------------------+
| 0001   | 89.66666666666667 |
+--------+-------------------+

2.5.3 查询所有课程成绩小于85分的学生的学号、姓名

这里的话首先可以很容易得到所有课程成绩小于85分，即平均成绩小于85分的学生学号。
select stu_id from score group by stu_id having avg(grade) < 85;
但是题目要求的还有姓名，此时姓名只有student表中才有，所以可以使用in()将在student中查询出的记录的id都限定在上面查询出的学生号中

即：

mysql> select id, name from student where id in (select stu_id from score group by stu_id having avg(grade) < 85);
+------+-----------+
| id   | name      |
+------+-----------+
| 0002 | 曹喜嗯    |
| 0003 | 张曼玉    |
+------+-----------+

2.5.3 查询没有学全所有课程的学生的学号、姓名

首先很容易从score查询出来所有的学生的学号，其次需要带入条件学全所有课程，如何叫做学全所有的课程呢，即判断课程总共有多少门
-- 获取总课程数目
select count(id) from course;
姓名的获取和上面一样，只要查询出学号，限定在student中的学号即可查询出完整的姓名

插入数据的时候所有人都选了所有的课，所以最后的结果为空

mysql> select id,name from student where id in (select stu_id from score group by stu_id having count(course_id) < (select count(id) from course));
Empty set (0.00 sec)

2.5.4 查询1996年出生的学生名单

因为要求出生年份的限定，可以使用year(brith)来获取年份，最后比较年份即可。

mysql> select * from student where year(brith) = 1996;
+------+-----------+------------+-----+
| id   | name      | brith      | sex |
+------+-----------+------------+-----+
| 0001 | 曹喜嗯    | 1996-01-01 | 男  |
+------+-----------+------------+-----+

2.5.5 查询各学生的年龄

查询年龄可以使用timestampdiff这个函数第一个参数是单位，第二个第三个是时间，下面比较的是当前时间和出生年月之间相差的年份

mysql> select id,name,timestampdiff(year,brith,now()) from student;
+------+-----------+---------------------------------+
| id   | name      | timestampdiff(year,brith,now()) |
+------+-----------+---------------------------------+
| 0001 | 曹喜嗯    |                              24 |
| 0002 | 曹喜嗯    |                              26 |
| 0003 | 张曼玉    |                              56 |
| 0004 | 周星驰    |                              58 |
+------+-----------+---------------------------------+

2.6 多表查询

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

首先对于student和score表的联合查询来说，两者之间的联系依托与id => stu_id，故只需要将两张表连接起来，然后利用id / stu_id进行分组即可得到我们想要的数据

-- -- student s1, score s2
mysql> select s1.id, s1.name, count(s2.course_id) as '选课数',sum(s2.grade) as '总成绩' from student as s1 join score as s2 where s1.id = s2.stu_id group by s1.id;
+------+-----------+-----------+-----------+
| id   | name      | 选课数    | 总成绩    |
+------+-----------+-----------+-----------+
| 0001 | 曹喜嗯    |         3 |       269 |
| 0002 | 曹喜嗯    |         3 |       228 |
| 0003 | 张曼玉    |         3 |       240 |
+------+-----------+-----------+-----------+

2.6.2 查询平均成绩大于85分的所有学生的学号、姓名、平均成绩

跟上面的查询几乎一致，只是多出了平均成绩大于85分的条件的限制，利用having在后面限制条件即可

mysql> select s1.id,s1.name,avg(s2.grade) from student as s1 join score as s2 where s1.id = s2.stu_id group by s2.stu_id having avg(s2.grade)>85; 
+------+-----------+-------------------+
| id   | name      | avg(s2.grade)     |
+------+-----------+-------------------+
| 0001 | 曹喜嗯    | 89.66666666666667 |
+------+-----------+-------------------+

2.6.3 查询学生的选课情况：学号、姓名、课程号、课程名称

本次查询因为涉及课程号和课程名称，而student表中并没有任何课程的信息，但score中有课程和学生号的信息，可以采用score连接student和course表以查询。

mysql> select s1.id as '学号',s1.name as '姓名',c1.id as '课程好',c1.name as '课程名称' from student as s1 join course as c1 join score as s2 where s1.id = s2.stu_id and s2.course_id = c1.id;
+--------+-----------+-----------+--------------+
| 学号   | 姓名      | 课程好    | 课程名称     |
+--------+-----------+-----------+--------------+
| 0001   | 曹喜嗯    | 0001      | 语文         |
| 0001   | 曹喜嗯    | 0002      | 数学         |
| 0001   | 曹喜嗯    | 0003      | 英语         |
| 0002   | 曹喜嗯    | 0001      | 语文         |
| 0002   | 曹喜嗯    | 0002      | 数学         |
| 0002   | 曹喜嗯    | 0003      | 英语         |
| 0003   | 张曼玉    | 0001      | 语文         |
| 0003   | 张曼玉    | 0002      | 数学         |
| 0003   | 张曼玉    | 0003      | 英语         |
+--------+-----------+-----------+--------------+

2.6.4 查询出每门课程的大于80得人数和不大于80的人数

为了对每门课程中大于80与不大于80的进行统计，则得到以course_id进行分组即可，对于人数的统计可以使用函数来代替即利用case...when...then...else...end实现分数的判断。

mysql> select course_id,sum(case when grade > 80 then 1 else 0 end) as '大于80',sum(case when grade <= 80 then 1 else 0 end) as '不大于80' from score group by course_id;
+-----------+----------+-------------+
| course_id | 大于80   | 不大于80    |
+-----------+----------+-------------+
| 0001      |        0 |           3 |
| 0002      |        1 |           2 |
| 0003      |        2 |           1 |
+-----------+----------+-------------+

2.6.5 使用分段[90,100],[80-90),[70,80),[60,70)区间统计各科成绩，统计各分段人数和，课程号，课程名称

与上面查询差不多，也是使用函数只是表的连接变成了score与course的连接了

mysql> select c1.id,c1.name,sum(case when s.grade >= 60 and s.grade < 70 then 1 else 0 end) as '[60,70)',sum(case when s.grade >= 70 and s.grade < 80 then 1 else 0 end) as '[70,80)',sum(case when s.grade >= 80 and s.grade < 90 then 1 else 0 end) as '[80,90)',sum(case when s.grade >= 90 and s.grade < 100 then 1 else 0 end) as '[90,100)' from score as s join course as c1 where s.course_id = c1.id group by s.course_id;
+------+--------+---------+---------+---------+----------+
| id   | name   | [60,70) | [70,80) | [80,90) | [90,100) |
+------+--------+---------+---------+---------+----------+
| 0001 | 语文   |       1 |       0 |       2 |        0 |
| 0002 | 数学   |       0 |       0 |       2 |        1 |
| 0003 | 英语   |       0 |       0 |       2 |        1 |
+------+--------+---------+---------+---------+----------+
-- 便于理解的结构
select c1.id,c1.name,
sum(case when s.grade >= 60 and s.grade < 70 then 1 else 0 end) as '[60,70)',
sum(case when s.grade >= 70 and s.grade < 80 then 1 else 0 end) as '[70,80)',
sum(case when s.grade >= 80 and s.grade < 90 then 1 else 0 end) as '[80,90)',
sum(case when s.grade >= 90 and s.grade < 100 then 1 else 0 end) as '[90,100)' from score as s join course as c1 where s.course_id = c1.id group by s.course_id;

2.6.6. 查询课程编号为"0003"且课程成绩在90分以上的学生的学号和姓名

可以先查询课程编号为“0003”且课程成绩在90分以上的学生，但由于需要知道姓名，此时引入student表进行连接即可。

mysql> select s1.id,s1.name from student as s1 join score as s2 where s1.id = s2.stu_id and s2.course_id = '0003' and s2.grade > 90;
+------+-----------+
| id   | name      |
+------+-----------+
| 0001 | 曹喜嗯    |
+------+-----------+

2.6.7 数据的行列如何互换？

行列互换

可以看看不使用max情况下的结果

mysql> select stu_id,
    -> (case course_id when '0001' then grade else 0 end) as '课程号0001',
    -> (case course_id when '0002' then grade else 0 end) as '课程号0002',
    -> (case course_id when '0003' then grade else 0 end) as '课程号0003'
    -> from score;
+--------+---------------+---------------+---------------+
| stu_id | 课程号0001    | 课程号0002    | 课程号0003    |
+--------+---------------+---------------+---------------+
| 0001   |            80 |             0 |             0 |
| 0001   |             0 |            90 |             0 |
| 0001   |             0 |             0 |            99 |
| 0002   |            60 |             0 |             0 |
| 0002   |             0 |            80 |             0 |
| 0002   |             0 |             0 |            88 |
| 0003   |            80 |             0 |             0 |
| 0003   |             0 |            80 |             0 |
| 0003   |             0 |             0 |            80 |
+--------+---------------+---------------+---------------+

可以发现多出了许多0，这是因为课程号不匹配而多出来的，我们可以使用stu_id作为分组，但分组之后会发现每一个对应着3个分数，如何将其聚合呢，此时可以使用max()聚合函数将三个结果中的最大的提取出来即可

mysql> select stu_id,
    -> max((case course_id when '0001' then grade else 0 end)) as '课程号0001',
    -> max((case course_id when '0002' then grade else 0 end)) as '课程号0002',
    -> max((case course_id when '0003' then grade else 0 end)) as '课程号0003'
    -> from score group by stu_id;
+--------+---------------+---------------+---------------+
| stu_id | 课程号0001    | 课程号0002    | 课程号0003    |
+--------+---------------+---------------+---------------+
| 0001   |            80 |            90 |            99 |
| 0002   |            60 |            80 |            88 |
| 0003   |            80 |            80 |            80 |
+--------+---------------+---------------+---------------+

2.7 多表连接查询

2.7.1 查询课程号为"0001"的课程分数小于90的学生信息，按照分数降序排列

此查询单纯的是两张表的连接，最后加入条件过滤即可

mysql> select s1.id,s1.name,s2.course_id,s2.grade from student as s1 join score as s2 where s1.id = s2.stu_id and s2.course_id = '0001' and s2.grade < 90 order by s2.grade desc;
+------+-----------+-----------+-------+
| id   | name      | course_id | grade |
+------+-----------+-----------+-------+
| 0001 | 曹喜嗯    | 0001      |    80 |
| 0003 | 张曼玉    | 0001      |    80 |
| 0002 | 曹喜嗯    | 0001      |    60 |
+------+-----------+-----------+-------+

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

因为需要知道老师的姓名，同时还需要知道分数，而分数和老师之间的中间表为course表，故需要连接的表有score & course & teacher表。因为要平均分数，故按照course_id分组即可

注意连接的条件一般使用on关键字连接

mysql> select c.id,c.teacher_id,t.name,avg(s1.grade) from score as s1 join course as c join teacher as t where s1.course_id = c.id and t.id = c.teacher_id group by s1.course_id order by avg(s1.grade) desc;
+------+------------+-----------+-------------------+
| id   | teacher_id | name      | avg(s1.grade)     |
+------+------------+-----------+-------------------+
| 0003 | 0003       | NULL      |                89 |
| 0002 | 0001       | 陈独秀    | 83.33333333333333 |
| 0001 | 0002       | 王后雄    | 73.33333333333333 |
+------+------------+-----------+-------------------+

2.7.3 查询课程名称为"数学"，且分数低于90的学生姓名和分数

将学生表&分数表&课程表连接，然后通过条件进行筛选即可。注意表的连接通过其中关联的键来实现。

mysql> select s1.id as '学号',s1.name as '姓名',c1.name as '课程名称',s2.grade as '分数' from student as s1 join score as s2 join course as c1 on s1.id = s2.stu_id and s2.course_id = c1.id and c1.name = '数学' and s2.grade < 90;
+--------+-----------+--------------+--------+
| 学号   | 姓名      | 课程名称     | 分数   |
+--------+-----------+--------------+--------+
| 0002   | 曹喜嗯    | 数学         |     80 |
| 0003   | 张曼玉    | 数学         |     80 |
+--------+-----------+--------------+--------+

2.7.4 查询两门及其以上课程小于85的同学的学号，姓名及其平均成绩

通过连表查询将成绩小于的先筛选出来，而后直接统计数据中的学生学号个数即可判断是否满足条件大于等于两门

mysql> select s1.id as '学号',s1.name as '姓名',avg(s2.grade) as '平均成绩' from student as s1 join score as s2 on s1.id = s2.stu_id and s2.grade < 85 group by s2.stu_id having count(s1.id) >= 2;
+--------+-----------+--------------+
| 学号   | 姓名      | 平均成绩     |
+--------+-----------+--------------+
| 0002   | 曹喜嗯    |           70 |
| 0003   | 张曼玉    |           80 |
+--------+-----------+--------------+

2.7.5 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

利用score表自身连接即可得到，但是注意结果的去重，因为join是笛卡尔积形式展现数据，这样的话如果不使用distinct将会出现两组相同的答案，这是因为正向为正确答案，反向也为正确答案。

mysql> select distinct s2.stu_id,s2.course_id,s2.grade from score as s2 join score as s3 on s2.stu_id = s3.stu_id where s2.grade = s3.grade and s2.course_id != s3.course_id;
+--------+-----------+-------+
| stu_id | course_id | grade |
+--------+-----------+-------+
| 0003   | 0001      |    80 |
| 0003   | 0002      |    80 |
| 0003   | 0003      |    80 |
+--------+-----------+-------+

2.7.6 查询课程编号为“0002”的课程比“0001”的课程成绩高的所有学生的学号

此查询是关于两个课程号一个学生去对比，则可先通过课程号获取两个学生本课程的成绩，然后通过将这两个课程所获取的成绩分别作为不同的表进行连接，使用学号进行两表建立连接的条件即可。最后通过条件成绩的大小即可获取结果。

mysql> select a.stu_id,a.grade as '0002成绩',b.grade as '0001成绩' from 
    -> (select stu_id,grade from score where course_id = '0002') as a 
    -> join 
    -> (select stu_id,grade from score where course_id = '0001') as b 
    -> on a.stu_id = b.stu_id where a.grade > b.grade;
+--------+------------+------------+
| stu_id | 0002成绩   | 0001成绩   |
+--------+------------+------------+
| 0001   |         90 |         80 |
| 0002   |         80 |         60 |
+--------+------------+------------+

2.7.7 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

首先要找出学过两门课程的学生学号，然后通过学生学号连接student表格即可获取其中的学生姓名

mysql> select a.stu_id,s1.name from 
    -> (select stu_id from score where course_id = '0001') as a
    -> join 
    -> (select stu_id from score where course_id = '0002') as b
    -> on a.stu_id = b.stu_id 
    -> join student as s1 on a.stu_id = s1.id;
+--------+-----------+
| stu_id | name      |
+--------+-----------+
| 0001   | 曹喜嗯    |
| 0002   | 曹喜嗯    |
| 0003   | 张曼玉    |
+--------+-----------+

2.7.8 查询学过“陈独秀”老师所教的所有课的同学的学号、姓名

单步实现可通过下面sql实现

select id from teacher where name = '陈独秀';
select id from course where teacher_id = '0001';
select stu_id from score where course_id = '0002';
select id,name from student where id in (select stu_id from score where course_id = '0002');

mysql> select s1.id,s1.name from student as s1
    -> join score as s2 on s2.stu_id = s1.id
    -> join course as c1 on c1.id = s2.course_id
    -> join teacher as t1 on t1.id = c1.teacher_id
    -> where t1.name = '陈独秀';
+------+-----------+
| id   | name      |
+------+-----------+
| 0001 | 曹喜嗯    |
| 0002 | 曹喜嗯    |
| 0003 | 张曼玉    |
+------+-----------+

2.7.9 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名

思路的话首先插查询出学号为"0001"的学生学习的所有课程的课程号，然后通过查询课程好在其中的其余的去重的学生号即可最后通过连表student进行学生姓名的获取即可

mysql> select s1.id,s1.name from student as s1 where s1.id in (select distinct(stu_id) from score where course_id in (select course_id from score where stu_id = '0001')) and s1.id != '0001';
+------+-----------+
| id   | name      |
+------+-----------+
| 0002 | 曹喜嗯    |
| 0003 | 张曼玉    |
+------+-----------+

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

思路此时需要行列转换了需要将成绩从之前的列转换为行，使数据形成平铺状

可以将score与course连接起来进行处理，每当遇到各个课程的时候，此时获取此时的得分，注意因为需要使用学号stu_id作为分组，但是每一个case...when...then...end将会得到一组结果，此时需要利用聚合函数max()将其聚合为1个方可输出结果。

mysql> select s1.stu_id,
    -> max((case c1.name when '语文' then s1.grade else 0 end)) as '语文',
    -> max((case c1.name when '数学' then s1.grade else 0 end)) as '数学',
    -> max((case c1.name when '英语' then s1.grade else 0 end)) as '英语',
    -> avg(s1.grade) as '平均成绩'
    -> from score as s1 join course as c1 on s1.course_id = c1.id group by s1.stu_id;
+--------+--------+--------+--------+-------------------+
| stu_id | 语文   | 数学   | 英语   | 平均成绩          |
+--------+--------+--------+--------+-------------------+
| 0001   |     80 |     90 |     99 | 89.66666666666667 |
| 0002   |     60 |     80 |     88 |                76 |
| 0003   |     80 |     80 |     80 |                80 |
+--------+--------+--------+--------+-------------------+

2.8 SQL高级功能：窗口函数

2.8.1 查询学生平均成绩及其名次

此处的难点是增加了名次排序的问题，可以使用row_number()函数从而使得排名依次增加

注意，在这里row_number()对于排名失效具体原因不可知道

所以采用定义变量的方式来实现排名，具体实现如下

如果不需要显示姓名的话更简单，则直接通过内部的先取出所有的平均成绩按照降序排列，然后定义变量先进行排序即可
SELECT
	a.av,
	( @row_num := @row_num + 1 ) AS rank 
FROM
	( SELECT stu_id, avg( grade ) AS av FROM score GROUP BY stu_id ORDER BY av DESC ) a
	JOIN ( SELECT @row_num := 0 ) AS b;

mysql> select s1.id,s1.name,c.av,c.rank from (select a.stu_id, a.av, (@row_num := @row_num + 1) as rank from (select stu_id, avg(grade) as av from score group by stu_id order by av desc) a join (select @row_num := 0) as b) as c join student as s1 on c.stu_id = s1.id order by c.rank;
+------+-----------+-------------------+------+
| id   | name      | av                | rank |
+------+-----------+-------------------+------+
| 0001 | 曹喜嗯    | 89.66666666666667 |    1 |
| 0003 | 张曼玉    |                80 |    2 |
| 0002 | 曹喜嗯    |                76 |    3 |
+------+-----------+-------------------+------+

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

按照上面的要求的话此时我们需要查询课程号、学生号以及成绩，最后使用变量@rank来实现排名的递增递减问题。

这里与上面的不同，上面的如果按照这里的直接排序，并不能够正确的结果。如下所示：得到的排名是凌乱的。并没有实际按照av去进行rank值的变化。所以上面查询只能进行嵌套查询了。
mysql> select stu_id,avg(grade) as av,(@rank := @rank + 1) as rank from score join (select @rank := 0) as b group by stu_id order by av desc; 
+--------+-------------------+------+
| stu_id | av                | rank |
+--------+-------------------+------+
| 0001   | 89.66666666666667 |    1 |
| 0003   |                80 |    3 |
| 0002   |                76 |    2 |
+--------+-------------------+------+

mysql> select course_id,stu_id, grade, (@rank := @rank+1) as rank from score join (select @rank := 0 ) as b order by grade desc;
+-----------+--------+-------+------+
| course_id | stu_id | grade | rank |
+-----------+--------+-------+------+
| 0003      | 0001   |    99 |    1 |
| 0002      | 0001   |    90 |    2 |
| 0003      | 0002   |    88 |    3 |
| 0001      | 0001   |    80 |    4 |
| 0002      | 0002   |    80 |    5 |
| 0001      | 0003   |    80 |    6 |
| 0002      | 0003   |    80 |    7 |
| 0003      | 0003   |    80 |    8 |
| 0001      | 0002   |    60 |    9 |
+-----------+--------+-------+------+

2.8.3 查询每门成绩最好的前两名学生的姓名

下面采用的是关联子查询的方法来获取结果的，但是有一个问题，如果是第2和第3名成绩相同且均小于第一名的成绩，则此sql无法工作，只能查询出最大的那个成绩。即如果第一第二第三名成绩相同，想要获取第一和第二名则当前查询不能够工作。（问题点）

如果成绩相同只能采用rank排名的方式获取前N名的姓名了。

mysql> select s1.name, s.* from student as s1 join (select * from score s1 where (select count(*) from score s2 where s2.course_id = s1.course_id and s2.grade >= s1.grade) < 3 order by s1.course_id asc) as s on s.stu_id = s1.id order by s.course_id desc,s.grade desc;
+-----------+--------+-----------+-------+
| name      | stu_id | course_id | grade |
+-----------+--------+-----------+-------+
| 曹喜嗯    | 0001   | 0003      |    99 |
| 曹喜嗯    | 0002   | 0003      |    88 |
| 曹喜嗯    | 0001   | 0002      |    90 |
| 曹喜嗯    | 0002   | 0002      |    81 |
| 张曼玉    | 0003   | 0001      |    80 |
| 曹喜嗯    | 0001   | 0001      |    80 |
+-----------+--------+-----------+-------+

或者：但是此方法对待分数相等的第二和第三的依旧没有办法进行排序

mysql> select s1.course_id,s1.grade from score as s1 join (select distinct course_id, grade from score) as s2 on s1.course_id = s2.course_id and
s1.grade <= s2.grade group by s1.course_id , s1.grade having count(*) < 3 order by s1.course_id desc,s1.grade desc;
+-----------+-------+
| course_id | grade |
+-----------+-------+
| 0003      |    99 |
| 0003      |    88 |
| 0002      |    90 |
| 0002      |    81 |
| 0001      |    80 |
| 0001      |    60 |
+-----------+-------+

3. 一道亲身经历的面试题

题目很简单，给定两个标，其中有些数据A表中存在，B表中不存在

-- 建表
-- A
create table A(
`id` int(3),
`name` varchar(20),
`age` int(2)
);
-- A中插入数据
insert into A (id,name,age) values (101,'cdx',23),(102,'cdx',23),(103,'cdx',23),(104,'cdx',23);

-- B
create table B(
`id` int(3),
`name` varchar(20),
`age` int(2)
);
-- B中插入数据
insert into B (id,name,age) values (100,'cdx',23),(102,'cdx',23),(103,'cdx',23),(105,'cdx',23);

-- 数据展示
mysql> select * from A;
+------+------+------+
| id   | name | age  |
+------+------+------+
|  101 | cdx  |   23 |
|  102 | cdx  |   23 |
|  103 | cdx  |   23 |
|  104 | cdx  |   23 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from B;
+------+------+------+
| id   | name | age  |
+------+------+------+
|  100 | cdx  |   23 |
|  102 | cdx  |   23 |
|  103 | cdx  |   23 |
|  105 | cdx  |   23 |
+------+------+------+
4 rows in set (0.00 sec)

3.1 查找A中存在的且B中不存在的id

-- 找出的是A中存在B中不存在的可以看到是101 和 104
mysql> select A.id as 'Aid',B.id as 'Bid' from A left join B on A.id = B.id;
+------+------+
| Aid  | Bid  |
+------+------+
|  102 |  102 |
|  103 |  103 |
|  101 | NULL |
|  104 | NULL |
+------+------+
-- 只需要添加B=null的条件即可将A中存在B中不存在的取出
mysql> select A.id as 'Aid',B.id as 'Bid' from A left join B on A.id = B.id where B.id is null;
+------+------+
| Aid  | Bid  |
+------+------+
|  101 | NULL |
|  104 | NULL |
+------+------+
-- 若只想获取A中的id
mysql> select A.id from A left join B on A.id = B.id where B.id is null;
+------+
| id   |
+------+
|  101 |
|  104 |
+------+

特别注意：null值不能使用任何的运算符判断只能使用is null或者is not null 来判断

第二种方法

即判断在B中A.id找不到与自己相等的id的则算作一个id

mysql> select A.id from A where (select count(1) from B where A.id = B.id) = 0; 
+------+
| id   |
+------+
|  101 |
|  104 |
+------+

4. 小结

参考知乎大佬的文章给自己复习了一下SQL基本的语句操作，收益良多，这里结束，也算是了解了自己很久的想要将SQL语句复习一遍的心愿，今天算是完整敲了一天，结束了，后面正式工作了SQL语句肯定不会少写！还需要继续学习，对一些SQL语句还不是很熟练，以后要更加的多利用SQL语句！加油！

Keep thinking, keep coding! 2020年12月25日22:06:11 写于南京加油！cdx!

心的步伐

关注

15
点赞
踩
69

收藏

觉得还不错? 一键收藏
3
评论
SQL语句实战学习(几乎涵盖了所有的SQL使用场景)

SQL语句实战文章目录SQL语句实战1. 数据准备2. SQL案例2.1 简单查询2.1.1 查询姓"曹"的学生名单2.1.2 查询姓名中最后一个是"玉"的学生名单2.1.3 查询姓名中带"星"的学生名单2.2 汇总查询2.2.1 查询课程编号为"0002"的总成绩2.2.2 查询选了课程的学生人数2.3 分组查询2.3.1 查询各科成绩最高和最低得分2.3.2 查询每门课程被选修的学生数2.3.3 查询学生中男、女人数2.4 带条件的分组查询2.4.1 查询平均成绩大于60分学生的学号和平均成绩2.4.
复制链接

扫一扫

专栏目录