sql学习笔记7—多表查询

1、多表查询

(1)查询所有学生的s_name,c_no和grade列
由数据可知，s_name在student表，c_no和grade在score表，那么我们要考虑如何将两个表联立起来。
student表中的s_no和score表中的s_no通过外键联系在一起。因此我们采用以下的方法：

select s_name,c_no,grade from student,score where student.s_no = score.s_no;
--result
+--------+-------+-------+
| s_name | c_no  | grade |
+--------+-------+-------+
| 王丽   | 3-105 |    92 |
| 王丽   | 3-245 |    86 |
| 王丽   | 6-166 |    85 |
| 王芳   | 3-105 |    88 |
| 王芳   | 3-245 |    75 |
| 王芳   | 6-166 |    79 |
| 赵铁柱 | 3-105 |    76 |
| 赵铁柱 | 3-245 |    68 |
| 赵铁柱 | 6-166 |    81 |
+--------+-------+-------+

(2)查询所有学生的 s_name, c_name, grade列
三表联查

select s_name, c_name, grade from student,course,score where student.s_no=score.s_no and course.c_no=score.c_no；
--result
+--------+------------+-------+
| s_name | c_name     | grade |
+--------+------------+-------+
| 王丽   | 计算机导论 |    92 |
| 王丽   | 操作系统   |    86 |
| 王丽   | 数字电路   |    85 |
| 王芳   | 计算机导论 |    88 |
| 王芳   | 操作系统   |    75 |
| 王芳   | 数字电路   |    79 |
| 赵铁柱 | 计算机导论 |    76 |
| 赵铁柱 | 操作系统   |    68 |
| 赵铁柱 | 数字电路   |    81 |
+--------+------------+-------+

由下面的查询结果可知上述语句的含义是查询了三个表中共同字段相等的记录。

select s_name, c_name, grade,student.s_no as stu_sno, score.s_no as sco_sno 
from student,course,score 
where student.s_no=score.s_no and course.c_no=score.c_no;
--result
+--------+------------+-------+---------+---------+
| s_name | c_name     | grade | stu_sno | sco_sno |
+--------+------------+-------+---------+---------+
| 王丽   | 计算机导论 |    92 | 103     | 103     |
| 王丽   | 操作系统   |    86 | 103     | 103     |
| 王丽   | 数字电路   |    85 | 103     | 103     |
| 王芳   | 计算机导论 |    88 | 105     | 105     |
| 王芳   | 操作系统   |    75 | 105     | 105     |
| 王芳   | 数字电路   |    79 | 105     | 105     |
| 赵铁柱 | 计算机导论 |    76 | 109     | 109     |
| 赵铁柱 | 操作系统   |    68 | 109     | 109     |
| 赵铁柱 | 数字电路   |    81 | 109     | 109     |
+--------+------------+-------+---------+---------+

(3)查询"男"教师及其所上的课

select t.t_name, c.c_no, c.c_name from teacher as t, course as c where t_sex = '男' and t.t_no = c.t_no;
-- result 
+--------+-------+----------+
| t_name | c_no  | c_name   |
+--------+-------+----------+
| 李诚   | 3-245 | 操作系统 |
| 张旭   | 6-166 | 数字电路 |
+--------+-------+----------+

(4)按照等级进行查询
首先新建grade_table表，将分数的等级放进去；

CREATE TABLE grade_table(
    low INT(3),
    upp INT(3),
    grade CHAR(1));
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'c');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');

查询所有同学的s_no , c_no 和等级列

select s_no, c_no, grade_table.grade from score, grade_table 
where score.grade between low and upp;
-- result
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+

总结：

select 在同时查询两个表时，如果两个表中有相同的字段，要在字段前加入表名；
2.select同时查询两个表时，返回的结果是两个表结果的笛卡尔积。上述的where在这个笛卡尔积中进行条件过滤，返回符合条件的记录。

2、子查询

(1)查询班级是'95031'班学生每门课的平均分
涉及到的表：student表,score表
首先将班级为“95031”的学生信息提出，再按照课程进行分组求平均数

select * from student where s_class='95031';
-- 我们将其中的s_no数据提出作为查询score表时的条件
select * from score where s_no in (select s_no from student where s_class='95031');
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 105  | 3-105 |    88 |
| 105  | 3-245 |    75 |
| 105  | 6-166 |    79 |
| 109  | 3-105 |    76 |
| 109  | 3-245 |    68 |
| 109  | 6-166 |    81 |
+------+-------+-------+
--对其进行分组求平均
select c_no,avg(grade) from score where s_no in (select s_no from student where s_class='95031') group by c_no;
--result
+-------+------------+
| c_no  | avg(grade) |
+-------+------------+
| 3-105 |    82.0000 |
| 3-245 |    71.5000 |
| 6-166 |    80.0000 |
+-------+------------+

(2)查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩的所有同学的记录

select * from score 
where c_no='3-105' and 
grade > ( select grade from score where s_no='109' and c_no='3-105');

(3) 查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
用到的函数：year()函数提取日期类型数据中的年份

select s_no,s_name from student 
    where year(s_birth) in (select year(s_birth)  
    from student where s_no in('108', '101')); 
--result
+------+--------+
| s_no | s_name |
+------+--------+
| 101  | 曾华   |
| 102  | 匡明   |
| 105  | 王芳   |
| 108  | 张全蛋 |
+------+--------+

(4)查询教师张旭所任课程的学生成绩

select * from score where s_no in
(select s_no from score where c_no = 
(select c_no from course 
where t_no = (select t_no from teacher where t_name='张旭')));

(5)查询选修某课程的同学人数多于5人的教师姓名
首先向数据表中添加数据

 INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','89');

查询：

select t_name from teacher where t_no in (select t_no  from course 
where c_no in (select c_no from score group by c_no having count(*) > 5));

进行分组后才可以统计数量；
(6) 查询95033班和95031班全体学生的记录

select * from student where s_class in ('95033','95031');
--result
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birth             | s_class |
+------+--------+-------+---------------------+---------+
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+

(7) 查出所有'计算机系' 教师所教课程的成绩表

select * from score 
where c_no in (select c_no from course
where t_no in (select t_no from teacher where t_depart= '计算机系'));
--result
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-245 |    86 |
| 105  | 3-245 |    75 |
| 109  | 3-245 |    68 |
| 101  | 3-105 |    90 |
| 102  | 3-105 |    91 |
| 103  | 3-105 |    92 |
| 104  | 3-105 |    89 |
| 105  | 3-105 |    88 |
| 109  | 3-105 |    76 |
+------+-------+-------+

(8)查询所有任课教师的t_name 和 t_depart(注意是任课的)

select t_name, t_depart from teacher 
where t_no in (select t_no from course where c_no in (select c_no from score group by c_no));
-- result
+--------+------------+
| t_name | t_depart   |
+--------+------------+
| 李诚   | 计算机系   |
| 王萍   | 计算机系   |
| 张旭   | 电子工程系 |
+--------+------------+

3.UNION和NOT IN的使用

语法：UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。如果要使得数据不重复，需要使用UNION ALL。
(1) 查询'计算机系'与'电子工程系' 不同职称的教师的name和rof
含义：除了在计算机系和电子工程系中同时出现的职称的所有记录

select t_name,t_prof from teacher where t_depart = '计算机系' 
and t_prof not in (select t_prof from teacher where t_depart ='电子工程系')
union 
select t_name,t_prof from teacher where t_depart = '电子工程系' 
and t_prof not in (select t_prof from teacher where t_depart = '计算机系');
--result
+--------+--------+
| t_name | t_prof |
+--------+--------+
| 李诚   | 副教授 |
| 张旭   | 讲师   |
+--------+--------+

4.ANY和ALL的使用

(1) 查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的c_no,s_no和grade,并且按照grade从高到低次序排序

select * from score where c_no = '3-105' 
and  grade > (select min(grade) from score where c_no = '3-245') order by grade desc;

题中提到“至少高于”，因此也可以用any表示，any(数据集）表示其中至少一个。

select * from score where c_no = '3-105' 
and  grade > any(select grade from score where c_no = '3-245') order by grade desc;

(2)查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和grade

select * from score where c_no = '3-105' 
and  grade > (select max(grade) from score where c_no = '3-245') order by grade desc;

也可以用all表示：

select * from score where c_no = '3-105' 
and  grade > all  (select grade from score where c_no = '3-245') order by grade desc;

总结: ANY 和 ALL
ANY:表示比任何一个就行了。
ALL:表示所有都要比较。

5.别名的使用

规则：
a. select 字段名 as 别名。
b. 当两个表连接时，union后的表列名与第一个表保持一致。

select t_name as name, t_sex as sex, t_birth as birth from teacher
union 
select  s_name, s_sex, s_birth from student;
-- result
+--------+-----+---------------------+
| name   | sex | birth               |
+--------+-----+---------------------+
| 李诚   | 男  | 1958-12-02 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 刘冰   | 女  | 1977-08-14 00:00:00 |
| 张旭   | 男  | 1969-03-12 00:00:00 |
| 曾华   | 男  | 1977-09-01 00:00:00 |
| 匡明   | 男  | 1975-10-02 00:00:00 |
| 王丽   | 女  | 1976-01-23 00:00:00 |
| 李军   | 男  | 1976-02-20 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 陆军   | 男  | 1974-06-03 00:00:00 |
| 王尼玛 | 男  | 1976-02-20 00:00:00 |
| 张全蛋 | 男  | 1975-02-10 00:00:00 |
| 赵铁柱 | 男  | 1974-06-03 00:00:00 |
| 张飞   | 男  | 1974-06-03 00:00:00 |
+--------+-----+---------------------+

注意：连接两个表时，两个表的字段顺序要保持一致。举例来说，如果将上述student表的查询顺序换为s_sex,s_birth,s_name，则结果变为：

-- 字段顺序不匹配结果
+------+---------------------+---------------------+
| name | sex                 | birth               |
+------+---------------------+---------------------+
| 李诚 | 男                  | 1958-12-02 00:00:00 |
| 王萍 | 女                  | 1972-05-05 00:00:00 |
| 刘冰 | 女                  | 1977-08-14 00:00:00 |
| 张旭 | 男                  | 1969-03-12 00:00:00 |
| 男   | 1977-09-01 00:00:00 | 曾华                |
| 男   | 1975-10-02 00:00:00 | 匡明                |
| 女   | 1976-01-23 00:00:00 | 王丽                |
| 男   | 1976-02-20 00:00:00 | 李军                |
| 女   | 1975-02-10 00:00:00 | 王芳                |
| 男   | 1974-06-03 00:00:00 | 陆军                |
| 男   | 1976-02-20 00:00:00 | 王尼玛              |
| 男   | 1975-02-10 00:00:00 | 张全蛋              |
| 男   | 1974-06-03 00:00:00 | 赵铁柱              |
| 男   | 1974-06-03 00:00:00 | 张飞                |
+------+---------------------+---------------------+

除了字段顺序要匹配，数量也要匹配，如果两个union查询的字段数量不一致，sql会进行报错。
(3)查询所有'女'教师和'女'学生的name,sex,birthday

select t_name as name, t_sex as sex, t_birth as birth from teacher where t_sex = '女'
union 
select  s_name, s_sex, s_birth from student where s_sex = '女';

总结：使用了别名之后的查询字段仍然是原字段名

6.复制表数据做条件查询

(1)查询成绩比该课程平均成绩低的同学的成绩表

select * from score as a 
where grade < (select avg(grade) from score as b where a.c_no = b.c_no);
-- result
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 105  | 3-245 |    75 |
| 105  | 6-166 |    79 |
| 109  | 3-105 |    76 |
| 109  | 3-245 |    68 |
| 109  | 6-166 |    81 |
+------+-------+-------+

总结：
查询平均值的两个方法：
a. 先按照某字段进行分组，再求每一组的平均值
select avg(grade) from score group by c_no ；
b.指定求某一条件的平均值
select avg(grade) from score where c_no = '3-105';

7.条件加分组筛选

(1) 查出至少有2名男生的班号

select s_class from student group by s_class having count(s_sex = '男') > 2;

也可以先查出男生的记录，再按照班级分组，再写计数的条件，如下：

select s_class from student where s_sex = '男' group by s_class having count(*) > 1;
-- result 
+---------+
| s_class |
+---------+
| 95033   |
| 95031   |
+---------+

总结：
a. group by 对相同的字段值进行聚合，聚合后形成的各个分组可以使用sum,count,avg等聚合函数。如果要对分组加入条件，则使用having。详情见：where和having的区别
b.count函数可以内置条件，但是最好加上or null 如：count(s_sex = '男' or null)，具体原因见：count 中加条件为什么要加or null

8.模糊查询

(1)year，now函数：查询student 表中不姓"王"的同学的记录

select * from student where s_name not like '王%';

总结：
like和not like进行模糊匹配

9.高级函数

(1)查询student 中每个学生的姓名和年龄

select s_name, year(now())- year(s_birth) as age from student;
-- result
+--------+------+
| s_name | age  |
+--------+------+
| 曾华   |   44 |
| 匡明   |   46 |
| 王丽   |   45 |
| 李军   |   45 |
| 王芳   |   46 |
| 陆军   |   47 |
| 王尼玛 |   45 |
| 张全蛋 |   46 |
| 赵铁柱 |   47 |
| 张飞   |   47 |
+--------+------+

总结：
a.now()函数返回当前系统的日期；
b.year()函数返回日期类型的年份。
(2)多字段排序：以班级号和年龄从大到小的顺序查询student表中的全部记录

select * from student order by s_class desc, s_birth;
-- result
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birth             | s_class |
+------+--------+-------+---------------------+---------+
| 110  | 张飞   | 男    | 1974-06-03 00:00:00 | 95038   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+
--

总结：
第一排序规则放在前面，第二排序规则依次在后。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,734评论 6赞 505
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,931评论 3赞 394
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,133评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,532评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,585评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,462评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,262评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,153评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,587评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,792评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,919评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,635评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,237评论 3赞 329
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,855评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,983评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,048评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,864评论 2赞 354