sql学习笔记7—多表查询

作者: 风一样的我1 | 来源:发表于2021-03-24 09:36 被阅读0次

sql学习笔记7—多表查询
SQL多表查询高级应用
SQLAlchemy（四）
SQL入门系列（六）：多表查询
不可置信！SQL 优化终于干掉了“distinct”
sql
SQL表（增、删、改、查）基础：）
sql多表查询
SQL多表查询
sql多表查询

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   |
+------+--------+-------+---------------------+---------+
--

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

sql学习笔记7—多表查询
参考资料来源于Bilibili《一天学会MYSQL数据库》 [https://www.bilibili.com/v...
SQL多表查询高级应用
SQL多表查询多表连接示范两张表t_user t_judge 给两张表设置外键约束查询内容 SQL多表查询多...
SQLAlchemy（四）
知识要点： 1.多表查询 2.原生SQL的查询多表查询在MySQL中我们讲了多表查询，在SQLAlchemy中...
SQL入门系列（六）：多表查询
以下内容来源于《漫画SQL》系列课程的学习笔记这门课的链接：漫画SQL—网易云课堂多表查询的前提是需要将两张表...
不可置信！SQL 优化终于干掉了“distinct”
sql 优化之多表联合查询干掉 “distinct” 去重关键字所以需要把多表的子查询的 sql 结构进行优化。...
sql
sql语句查询简单查询例：多表连接查询例： 2.更新
SQL表（增、删、改、查）基础：）
学习SQL的第一天（7月17日）跟着W3school学习SQL语法.... 整理笔记 SQL(结构化查询语言)由...
sql多表查询
普通多表查询嵌套多表查询链接多表查询左链接（会将左表的内容全部输出，没有需要补NULL）右链接（会将右表的...
SQL多表查询
之前做过一个关于数据库的使用总结，里面写过一些关于数据库的常用方法的集合，但是我们在实例工作中，很可能涉及到一需要...
sql多表查询
在面试中经常有这样的问题,从两个表A和B中获取有特定关系的数据.碰到这样的问题我们一般需要用到union和join...