# 多表攻略

前面我们学习了sql语句的操作,但是我们大部分都是操作的单张表,大部分问题只有一张表是不能解决的。我们需要从多张表中获得数据,或者通过其他表的数据删除某个表的记录等操作,这个时候就需要多表操作。

# 表关联

一对一

比如说会员表与个人资料(QQ,邮箱)表即为一对一关系。

一对多

比如学生与班级表间即为一对多关系,一个班级有多个学生,一个学生属于一个班级。

多对多

粉丝表与用户表关系,一个粉丝可以关注多个用户,一个用户也可以有多个粉丝,像这种关系我们会使用一张中间表来记录关系。

# 笛卡尔积

多个表的连接将会得到所有可能出现的行,即没有明确做两个表间的关联条件时,所有记录都将符合。

SELECT * FROM stu ,class;

下面是添加条件后的结果

SELECT * FROM stu ,class WHERE stu.class_id = class.id;

# INNER

所有多表操作都可以简单理解为,把多个表联系成一个表,最终思想成面上当成一个表对待。

# INNER JOIN

获取用户的资料信息

SELECT * FROM stu AS s INNER JOIN user_info as i
ON s.id = i.stu_id;

使用INNER JOIN 使用多表关联的语义更清晰

SELECT * FROM stu INNER JOIN class
ON stu.class_id = class.id;

查询一班的所有同学

SELECT * FROM stu INNER JOIN class
ON stu.class_id = class.id
WHERE class.id = 1;

为了性能和多表字段重名覆盖的问题,有必要在查询时明确获取的列

SELECT sname,class_id,stu.id as stu_id,sex,cname FROM stu INNER JOIN class
ON stu.class_id = class.id
WHERE class.id = 1;

每个班级发表的文章数量,来操作多张表的关联操作

SELECT c.id ,count(*) FROM stu as s 
INNER JOIN class as c
INNER JOIN article as a
ON s.class_id = c.id AND s.id = a.stu_id
GROUP BY c.id;

大学所有女生发表的文章

SELECT c.id,a.title FROM stu as s 
INNER JOIN class as c
INNER JOIN article as a
ON s.class_id = c.id AND s.id = a.stu_id
WHERE c.id=1 AND s.sex = '女';

每个班级发表的文章数

SELECT count(a.id) as article_sum,c.id FROM stu as s 
INNER JOIN class as c
INNER JOIN article as a
ON s.class_id = c.id AND s.id = a.stu_id
GROUP BY c.id;

哪个班级发表的文章超过两篇

SELECT c.id,count(*) as total FROM stu as s
INNER JOIN class as c
INNER join article as a
ON s.class_id = c.id AND s.id = a.stu_id
GROUP BY c.id
HAVING total >=2;

每个班级文章的总点击数与平均点击数

SELECT sum(a.click) as class_sum,avg(a.click) ,c.id FROM stu as s 
INNER JOIN class as c
INNER JOIN article as a
ON s.class_id = c.id AND s.id = a.stu_id
GROUP BY c.id
ORDER BY class_sum DESC;

每个班级有多少同学

SELECT count(*),c.cname FROM stu as s INNER JOIN class as c
ON s.class_id = c.id
GROUP BY c.cname;

学生数大于两个的班级名称

SELECT count(*) as total,c.cname FROM stu as s INNER JOIN class as c
ON s.class_id = c.id
GROUP BY c.cname
HAVING total>=2;

# OUTER JOIN

外链接包括LEFT JOINRIGHT JOIN ,可以简单理解为 LEFT JOIN会包含左侧所有表记录,RIGHT JOIN 会包含右侧表全部记录。

获取没有设置QQ的用户

SELECT s.sname FROM stu AS s LEFT JOIN user_info as i
ON s.id = i.stu_id
WHERE i.qq is null;

查找所有没有发表文章的同学

SELECT s.id,s.sname FROM stu as s LEFT JOIN article as a 
ON s.id = a.stu_id
WHERE a.id IS NULL;

哪个班级没有学生

SELECT sname,c.id,c.cname FROM stu AS s RIGHT JOIN class as c
ON s.class_id = c.id
WHERE s.id IS NULL;

每个班级的平均年龄

SELECT c.cname,avg(timestampdiff(year,s.birthday,now())) as t 
FROM stu as s INNER JOIN class as c
ON s.class_id = c.id
GROUP BY c.cname;

查找学生所在班级,没有班级的学生显示无

SELECT sname,ifnull(s.class_id,'无') FROM stu AS s LEFT JOIN class AS c
ON s.class_id = c.id;

# SELF JOIN

SELF JOIN为自连接即表与自身进行关联。虽然自连接的两张表都是同一张表,但也把它按两张表对待,这样理解就会容易些。

查找后盾人的同班同学

使用子查询操作

SELECT * FROM stu WHERE class_id = 
(SELECT class_id FROM stu WHERE sname = '后盾人')
AND stu.sname !='后盾人';

使用自连接查询

SELECT s1.sname,s2.sname FROM stu as s1 
INNER JOIN stu as s2
ON s1.class_id = s2.class_id
WHERE s1.sname = '后盾人' AND s2.sname !='后盾人';

查找与后盾人同年出生的同学

SELECT s2.* FROM stu as s1 INNER JOIN stu AS s2
ON year(s1.birthday) = year(s2.birthday)
WHERE s1.sname ='后盾人' AND s2.sname !='后盾人';

查找比后盾人大的同学

SELECT s2.sname,s2.birthday FROM stu AS s1
INNER JOIN stu AS s2
ON year(s1.birthday)>year(s2.birthday)
WHERE s1.sname = '后盾人';

# 多对多

比如学生可以学习多个课程,一个课程也可以被多个学生学习,这种情况就是多对多的关系。需要创建一张中间表来把这种关系联系起来。

查找后盾人学习的课程

SELECT sname,l.name FROM stu AS s
INNER JOIN user_lesson AS ul
ON s.id = ul.stu_id
INNER JOIN lesson AS l
ON ul.lesson_id = l.id
WHERE s.sname = '后盾人';

哪个班级的同学最爱学习PHP

SELECT c.cname,count(*) AS total FROM stu AS s
INNER JOIN user_lesson AS ul
INNER JOIN lesson AS l
ON s.id = ul.stu_id AND ul.lesson_id = l.id
INNER JOIN class AS c
ON c.id = s.class_id
WHERE l.name='php'
GROUP BY c.cname
ORDER by total 
LIMIT 1;

# UNION

UNION 用于连接多个查询结果,要保证每个查询返回的列数与顺序要一样。

  • UNION会过滤重复的结果
  • UNION ALL 不过滤重复结果
  • 列表字段由是第一个查询的字段

查询年龄最大与最小的同学

(SELECT sname,birthday FROM stu ORDER BY birthday DESC LIMIT 1)
UNION
(SELECT sname,birthday from stu ORDER BY birthday ASC LIMIT 1)
ORDER BY birthday DESC;

最新发表的文章和学习的课程组成动态数据

(SELECT CONCAT(s.sname,'发表了文章:',a.title) from article as a
INNER JOIN stu as s
ON s.id = a.stu_id
LIMIT 2)
UNION
(SELECT CONCAT(s.sname,'正在学习:',l.name) FROM stu AS s 
INNER JOIN user_lesson as ul
INNER JOIN lesson as l
ON s.id = ul.stu_id AND ul.lesson_id = l.id 
LIMIT 2);

# 多表删除

删除所有没有学习任何课程的同学

DELETE s FROM stu as s 
LEFT JOIN user_lesson as ul
ON s.id = ul.stu_id
WHERE ul.lesson_id IS NULL;

使用子查询操作

DELETE FROM stu WHERE id IN(
  SELECT id FROM
    (SELECT s.id FROM stu as s
    LEFT JOIN user_lesson as ul
    ON s.id = ul.stu_id
    WHERE ul.lesson_id IS NULL) 
  AS s
);