多表查询
笛卡尔积
概念:简单来说就是两个集合相乘的结果,集合A和集合B中任意两个元素结合在一起。
缺点:冗杂,得出来的结果并非是想要的,所以需要一种新的应用,将笛卡尔积出来的结果当成一个表格
/*笛卡尔积*/
SELECT * FROM course,teacher;
-- 给出条件,筛选得到我们所需要的有用的信息
SELECT * FROM course,teacher WHERE course.t_id = teacher.id;
-- 可以给表起别名,方便使用
SELECT * FROM course c,teacher t WHERE c.t_id = t.id;
1、内连接
内连接
内连接查询操作只列出与连接条件匹配的数据行,使用INNER JOIN或者直接使用JOIN 进行连接。
-- 第一种方式,join
select * from Table_A JOIN Table_B;
-- 第二种方式,inner join
select * from Table_A INNER JOIN Table_B;
内连接可以没有连接条件,没有条件之后的查询结果,会保留所有结果(笛卡尔集),与后面分享的交 叉连接差不多。
在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列, 包括其中的重复列。
-- 使用了join或者inner join 后面需要跟on进行连接
select * from Table_A AJOIN Table_B B ON A.id = B.id;
查询结果,注意列数是 4 列,两张表的字段直接拼接在一起,重复的字段在后面添加数字序列以做区分
通俗讲就是根据条件,找到表 A 和 表 B 的数据的交集
例子:
普通的多表查,课内连接接通相同
SELECT * from teacher t , course c where t.id = c.t_id (这样会先生成笛卡尔积,效率可能略低)
SELECT * from teacher t JOIN course c on t.id = c.t_id
SELECT * from teacher t inner JOIN course c on t.id = c.t_id
结果:只有满足条件的会显示,5号老师没课程,5号课程没老师都不会显示
1 王宝强 1 数学 1
2 贾宝玉 2 语文 2
3 温迪 3 c++ 3
4 路人甲 4 java 4
2、外连接(常用)
外连接不只列出与连接条件相匹配的行,而且还加上左表(左外连接时)或右表(右外连接时)或两个表(全 外连接时)中所有符合搜索条件的数据行。
(1)、左连接(左外连接)
-- 第一种:left join
SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.ID = B.ID
-- 第二种:left outer join
SELECT * FROM Table_A A LEFT OUTER JOIN Table_B B ON A.id = B.id;
查询结果:
根据条件,用右表(B)匹配左表(A),能匹配,正确保留,不能匹配其他表的字段都置空 Null。
也就是,根据条件找到表 A 和 表 B 的数据的交集,再加上左表的数据集, Venn 图表示就是
红色部分代表查询结果
例子:
SELECT * from teacher t LEFT JOIN course c on t.id = c.t_id
结果:只有满足条件的会显示,5号老师没课程,依然显示,5号课程没老师都不会显示, 左边表的所有数据都显示
1 王宝强 1 数学 1
2 贾宝玉 2 语文 2
3 温迪 3 c++ 3 4 路人甲
4 java 4
5 路人乙
(2)、右连接 (右外连接)
-- 第一种:right join
SELECT * FROM Tabel_A RIGHT JOIN Table_B B ON A.id = B.id;
-- 第二种:right outer join
SELECT * FROM Table_A A RIGHT OUTER JOIN Table_B B ON A.id =B.id;
查询结果:
根据条件,用左表(A)匹配右表(B),能匹配,正确保留,不能匹配其他表的字段都置空 Null。 也就是,根据条件找到表 A 和 表 B 的数据的交集,再加上右表的数据集, Venn 图表示就是
例子:
SELECT * from teacher t right JOIN course c on t.id = c.t_id
结果:只有满足条件的会显示,5号老师没课程不显示,5号课程没老师都,依然显示, 右边表的所有数据都显示
1 王宝强 1 数学 1
2 贾宝玉 2 语文 2
3 温迪 3 c++ 3
4 路人甲 4 java 4
5 php
3、全连接,mysql不支持,oracle支持
SELECT * FROM Table_A A FULL JOIN Table_B B ON A.id = B.id;
SELECT * FROM Table_A A FULL OUTER JOIN Table_B B ON A.id = B.id;
目前我的 MySQL 不支持此种方式,可以用其他方式替代解决,在此不展开。
理论上是根据条件找到表 A 和 表 B 的数据的交集,再加上左右表的数据集
4、子查询
1、where型子查询
将查询结果当条件
例子:查询有一门学科分数大于八十分的学生信息
SELECT * from student where id in (select DISTINCT s_id from scores where score > 90);
-- 等于号的使用
SELECT * FROM student WHERE id =
(SELECT s_id FROM scores GROUP BY s_id ORDER BY SUM(score) DESC LIMIT 1);
where 型子查询,如果是 where 列 =(内层 sql) 则内层 sql 返回的必须是单行单列,单个值。
where 型子查询,如果是 where 列 in(内层 sql) 则内层 sql 返回的必须是单列,可以多行。
2、from型子查询
在学习 from 子查询之前,需要理解一个概念:查询结果集在结构上可以当成表看,那就可以当成临时 表对他进行再次查询:
取排名数学成绩前五名的学生,正序排列。
-- FROM
-- 取排名数学成绩前五名学生,正序排列
SELECT * FROM student s left JOIN scores e on s.id = e.s_id
LEFT JOIN course c ON e.c_id = c.id WHERE c.`NAME` = '数学'
ORDER BY e.score DESC LIMIT 5;
-- 正序排列
SELECT * FROM (SELECT s.id id,s.name name,e.score score,c.name cname FROM student s LEFT JOIN scores e
ON s.id = e.s_id LEFT JOIN course c ON e.c_id = c.id
WHERE c.`NAME` = '数学' ORDER BY e.score DESC LIMIT 5 ) t ORDER BY t.score;
5、练习题
1、查询‘01’号学生的姓名和各科成绩。 *
-- 1、查询‘01’号学生的姓名和各科成绩
SELECT s.name,e.score,c.`NAME` FROM
student s LEFT JOIN scores e on s.id = e.s_id
LEFT JOIN course c on e.c_id = c.id WHERE s.id = '1';
2、查询各个学科的平均成绩,高成绩。 *
-- 2、查询各个学科的平均成绩,高成绩。
SELECT c.`NAME`,avg(score) '平均成绩',MAX(score) '最高成绩'
FROM scores s INNER JOIN course c ON c.id = s.c_id
GROUP BY c.`NAME`;
3、查询每个同学的高成绩及科目名称。 *
-- 3、查询每个同学的高成绩及科目名称。
-- 思路:先拿到每个id的最大成绩
SELECT st.id,st.`NAME`,c.`NAME` '课程名',sc.score '最高成绩'
FROM student st LEFT JOIN scores sc on st.id = sc.s_id
LEFT JOIN course c ON sc.c_id = c.id
WHERE sc.score in(
SELECT max(sc.score) FROM scores sc GROUP