多表攻略
前面我们学习了 sql 语句的操作,但是我们大部分都是操作的单张表,大部分问题只有一张表是不能解决的。我们需要从多张表中获得数据,或者通过其他表的数据删除某个表的记录等操作,这个时候就需要多表操作。
表关联
一对一
比如说会员表与个人资料(QQ,邮箱)表即为一对一关系。
一对多
比如学生与班级表间即为一对多关系,一个班级有多个学生,一个学生属于一个班级。
多对多
粉丝表与用户表关系,一个粉丝可以关注多个用户,一个用户也可以有多个粉丝,像这种关系我们会使用一张中间表来记录关系。
笛卡尔积
多个表的连接将会得到所有可能出现的行,即没有明确这两个表间的关联条件时,所有记录都将符合。
1 | SELECT * FROM stu ,class; |
下面是添加条件后的结果
1 | SELECT * FROM stu ,class WHERE stu.class_id = class.id; |
JOIN
所有多表操作都可以简单理解为,把多个表联系成一个表,最终思想成面上当成一个表对待。
INNER JOIN
获取用户的资料信息
1 | SELECT * FROM stu AS s INNER JOIN user_info as i |
使用INNER JOIN 使用多表关联的语义更清晰
1 | SELECT * FROM stu INNER JOIN class |
查询一班的所有同学
1 | SELECT * FROM stu INNER JOIN class |
为了性能和多表字段重名覆盖的问题,有必要在查询时明确获取的列
1 | SELECT sname,class_id,stu.id as stu_id,sex,cname FROM stu INNER JOIN class |
每个班级发表的文章数量,来操作多张表的关联操作
1 | SELECT c.id ,count(*) FROM stu as s |
一班的所有女生发表的文章
1 | SELECT c.id,a.title FROM stu as s |
每个班级发表的文章总数
1 | SELECT count(a.id) as article_sum,c.id FROM stu as s |
哪个班级发表的文章超过两篇
1 | SELECT c.id,count(*) as total FROM stu as s |
每个班级文章的总点击数与平均点击数
1 | SELECT sum(a.click) as class_sum,avg(a.click),c.id FROM stu as s |
每个班级有多少同学
1 | SELECT count(*),c.cname FROM stu as s INNER JOIN class as c |
学生人数大于两个的班级名称
1 | SELECT count(*) as total,c.cname FROM stu as s INNER JOIN class as c |
OUTER JOIN
外链接包括LEFT JOIN
与 RIGHT JOIN
,可以简单理解为 LEFT JOIN
会包含左侧所有表记录,RIGHT JOIN
会包含右侧表全部记录。
获取没有设置 QQ 的用户
1 | SELECT s.sname FROM stu AS s LEFT JOIN user_info as i |
查找所有没有发表文章的同学
1 | SELECT s.id,s.sname FROM stu as s LEFT JOIN article as a |
哪个班级没有学生
1 | SELECT sname,c.id,c.cname FROM stu AS s RIGHT JOIN class as c |
每个班级的平均年龄
1 | SELECT c.cname,avg(timestampdiff(year,s.birthday,now())) as t |
查找学生所在班级,没有班级的学生显示无
1 | SELECT sname,ifnull(s.class_id,'无') FROM stu AS s LEFT JOIN class AS c |
SELF JOIN
SELF JOIN
为自连接即表与自身进行关联。虽然自连接的两张表都是同一张表,但也把它按两张表对待,这样理解就会容易些。
查找后盾人的同班同学
使用子查询操作
1 | SELECT * FROM stu WHERE class_id = |
使用自连接查询
1 | SELECT s1.sname,s2.sname FROM stu as s1 |
查找与后盾人同年出生的同学
1 | SELECT s2.* FROM stu as s1 INNER JOIN stu AS s2 |
查找比后盾人大的同学
1 | SELECT s2.sname,s2.birthday FROM stu AS s1 |
多对多
比如学生可以学习多个课程,一个课程也可以被多个学生学习,这种情况就是多对多的关系。需要创建一张中间表来把这种关系联系起来。
查找后盾人学习的课程
1 | SELECT sname,l.name FROM stu AS s |
哪个班级的同学最爱学习 PHP
1 | SELECT c.cname,count(*) AS total FROM stu AS s |
UNION
UNION
用于连接多个查询结果,要保证每个查询返回的列的数量与顺序要一样。
- UNION 会过滤重复的结果
- UNION ALL 不过滤重复结果
- 列表字段由是第一个查询的字段
查询年龄最大与最小的同学
1 | (SELECT sname,birthday FROM stu ORDER BY birthday DESC LIMIT 1) |
最新发表的文章和学习的课程组成动态数据
1 | (SELECT CONCAT(s.sname,'发表了文章:',a.title) from article as a |
多表删除
删除所有没有学习任何课程的同学
1 | DELETE s FROM stu as s |
使用子查询操作
1 | DELETE FROM stu WHERE id IN( |
Exists
Exists
与NOT EXISTS
都是通过子查询来决定是否返回结果,下面是是语法介绍
1 | SELECT * FROM h1 WHERE [conditions] AND EXISTS(SELECT * FROM h2) |
说明如下:
- EXISTS最终返回 true或false
- 外层查询会将每条结果传递给子查询,用于进行exists判断
- 如果子查询有结果,则外层查询的这条记录返回
- NOT EXISTS是EXISTS的反函数
哪些学习已经在学习课程
1 | select * from stu s where exists(select * from stu_lesson sl where s.id=sl.stu_id); |
哪些学生没有课程
1 | select * from stu s where not exists(select * from stu_lesson sl where s.id=sl.stu_id); |
学习的课程数量>=2个的学生
1 | select * from stu s where exists(select id from stu_lesson sl where s.id=sl.stu_id group by id HAVING count(*)>=2); |
性别是男生并且设置了QQ号的同学
1 | select * from stu s where s.sex='男' and |