MYSQL(七)

服务器
  目录
  1. 1. 多表攻略
    1. 1.1. 表关联
    2. 1.2. 笛卡尔积
    3. 1.3. JOIN
      1. 1.3.1. INNER JOIN
      2. 1.3.2. OUTER JOIN
      3. 1.3.3. SELF JOIN
    4. 1.4. 多对多
    5. 1.5. UNION
    6. 1.6. 多表删除
    7. 1.7. Exists

mysql学习笔记整理-多表攻略

多表攻略

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

表关联

一对一
比如说会员表与个人资料（QQ，邮箱）表即为一对一关系。
一对多
比如学生与班级表间即为一对多关系，一个班级有多个学生，一个学生属于一个班级。
多对多
粉丝表与用户表关系，一个粉丝可以关注多个用户，一个用户也可以有多个粉丝，像这种关系我们会使用一张中间表来记录关系。

笛卡尔积

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

1
SELECT * FROM stu ,class;

下面是添加条件后的结果

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

JOIN

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

INNER JOIN

获取用户的资料信息

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

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

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

查询一班的所有同学

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

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

1
2
3
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;

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

1
2
3
4
5
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;

一班的所有女生发表的文章

1
2
3
4
5
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 = '女';

每个班级发表的文章总数

1
2
3
4
5
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;

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

1
2
3
4
5
6
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;

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

1
2
3
4
5
6
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;

每个班级有多少同学

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

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

1
2
3
4
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 JOIN 与 RIGHT JOIN ，可以简单理解为 LEFT JOIN会包含左侧所有表记录，RIGHT JOIN 会包含右侧表全部记录。

获取没有设置 QQ 的用户

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

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

1
2
3
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;

哪个班级没有学生

1
2
3
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;

每个班级的平均年龄

1
2
3
4
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;

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

1
2
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为自连接即表与自身进行关联。虽然自连接的两张表都是同一张表，但也把它按两张表对待，这样理解就会容易些。
查找后盾人的同班同学
使用子查询操作

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

使用自连接查询

1
2
3
4
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 !='后盾人';

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

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

查找比后盾人大的同学

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

多对多

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

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
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 不过滤重复结果
  • 列表字段由是第一个查询的字段

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

1
2
3
4
(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;

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

1
2
3
4
5
6
7
8
9
10
(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);

多表删除

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

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

使用子查询操作

1
2
3
4
5
6
7
8
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
);

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
2
select * from stu s where s.sex='男' and 
exists(select * from stu_info si where s.id=si.stu_id and si.qq is not null );

  MYSQL笔记