JinLinC个人在线文档库

Appearance

多表操作 ​

多表关系 ​

数据表的关系有：

  • 一对一的关系

    学生表，学生详情资料表，一个学生对应详情表中的一条数据，有且只有一条，学生表中方的id与详情表中的stu_id对应做表语表的联系

    有一个主表，一个从表，详情数据太多的情况下，我们可以将表字段进行切分，移动到另一张表中（从表）

  • 一对多的关系

    学生和班级的关系：一个学生对应一个班级，一个班级可以对应多个学生

    多的一方来记录少的一方，学生表中有个字段记录班级编号即可

  • 多对多的关系

    学生和课程的关系：学生可以学多个课程，一个课程也可以被多个学生学习

    用中间表来做衔接，记录哪个编号的学生对应哪个课程，同一个编号的学生可以对应多个课程

Exists的工作原理 ​

Exists类似于过滤，对于满足条件的内容进行筛选，过滤掉我们不需要的内容，使用示例：

sql
SELECT * FROM stu s WHERE EXISTS(SELECT * FROM stu WHERE s.id=1);

EXISTS()里面放上我们的过滤条件，只要这个条件有结果，那这个表达式就为真，会拿查询到的每一条数据和这个表达式进行判断，符合条件的就留下，不符合条件的就过滤掉

上述语义具体描述为：对查询到的数据起一个别名，如果其id为1，就留下数据，否则就过滤掉数据

EXISTS()的具体工作原理是将外层的结果依次传递给里面的sql语句

Exists有一个反函数：Not Exists，逻辑上与其相反，满足条件的被过滤掉，不满足条件的留下

Exists一般用于多表操作中会带来便利，如我们有两张表：一张学生信息表，一张学生所学课程表，该表的字段只有stu_id和lesson_id，我们查看哪些学生已经在学习课程了：

sql
SELECT * FROM stu s WHERE EXISTS(SELECT * FROM lesson l WHERE s.id=l.stu_id);

返回的是学生表中有在学习课程的学生信息，如果一个学生同时学习两门课程，只会显示最后查询到的

查询学生学习课程数量大于等于2门的学生信息：

sql
SELECT * FROM stu s WHERE EXISTS(
    SELECT id FROM lesson l WHERE s.id=l.stu_id GROUP BY id HAVING count(*)>=2
);

对课程表中的学生id进行分组，统计出学习课程数大于2的学生id

查询的数据是男同学，且其学生具体信息表中设置了QQ号：

sql
SELECT * FROM stu s WHERE s.sex='男' AND EXISTS(
    SELECT * FROM stu_info sl WHERE s.id=sl.stu_id AND sl.qq IS NOT null
);

EXISTS()的作用是过滤，是将上层查询的结果进行一个过滤，如果该数据在EXISTS()中有结果，就为真，这条数据就会被保留，否则就会被过滤掉

笛卡尔积 ​

笛卡尔积是多表关联产生的一种情况，对于两张表进行关联的时候，如学生表与班级表进行关联，学生表中有10条数据，班级表有5条数据，在两表进行关联的时候没有指定条件：SELECT * FROM stu,class;

那mysql就会认为学生表中的一条记录和班级表中的所有记录都相匹配，就会产生50条记录

为了解决这个情况，我们需要加上条件，让学生表中的班级编号与班级表中的主键相匹配（也就是说，要通过正确的过滤条件进行过滤）（多表查询的条件不能少于表的个数-1，否则出现的是笛卡尔积）：

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

这样就将两张表中所有的字段组合在一起进行返回了，返回的结果是学生表中的10条数据，学生表的信息在前面，班级表的信息在后面，同时又包括对应班级表中的具体信息，根据学生表中的班级编号和班级表中的主键对应进行匹配

上述sql语句中，如果两张表中只有一张表中有class_id，那么条件前面的表前缀是可以去掉的，变为：

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

如果两张表都有这个字段，表前缀是不可以去掉的

一般情况下，都是要加表前缀的，防止冲突，如果表前缀比较长，我们一般是使用别名：

sql
SELECT * FROM stu as s,class as c WHERE s.class_id = c.id;

多表关联取数据的时候，通常是不会将各个表中的所有数据都取过来的，一般是取部分数据的

sql
SELECT s.sname,c.cname FROM stu as s,class as c WHERE s.class_id = c.id;

上述表示取学生表中的学生名字和班级表中的班级名字

多表操作 ​

子查询 ​

子查询是指嵌入在其他SQL语句中的SELECT语句，也叫嵌套查询

  • 单行子查询：返回一行数据的子查询语句（子查询返回的结果只有一行）
  • 多行子查询：返回多行数据的子查询（子查询返回的结果有多行），使用关键字in
sql
-- 显示与jlc同一个班级的学生  使用单行子查询来解决
-- 思路：1.先查询到jlc的班级 2.将步骤1的语句当作一个子查询来使用
SELECT * FROM stu WHRER class_id = {
	SELECT class_id FROM stu WHERE name = 'jlc'
};

可以将子查询当做一张临时表，可以解决很多复杂的查询，但是效率比较慢

sql
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
	FROM {
		SELECT cat_id, MAX(shop_price) AS max_price
		FROM ecs_goods
		GROUP BY cat_id
	} temp, ecs_goods
	WHERE temp.cat_id = ecs_goods.cat_id
	AND temp.max_price = ecs_goods.shop_price
all和any修饰符 ​
sql
-- 显示工资比30号部门的所有员工工资都高的员工姓名、工资和部门号
SELECT ename, sal, deptno
	FROM emp
	WHERE sal > ALL(   -- 这里的ALL可以使用MAX进行代替
    	SELECT sal FROM emp WHERE deptno = 30
    );
    
-- 显示工资比30号部门的其中一个员工工资高的员工姓名、工资和部门号
SELECT ename, sal, deptno
	FROM emp
	WHERE sal > ANY(   -- 这里的ALL可以使用MIN进行代替
    	SELECT sal FROM emp WHERE deptno = 30
    );
多列子查询 ​

多列子查询是指查询返回多个列数据的子查询语句（子查询返回的结果是多列）

sql
-- 查询与jlc数学、英语和语文成绩完全相同的学生
SELECT * FROM stu
	WHERE (math, english, chinese) = (
    	SELECT math, english, chinese
        FROM stu
        WHERE name = 'jlc'
    );

分组查询 ​

对于学生表与班级表进行多表关联：

sql
SELECT * FROM stu as s,class as c WHERE s.class_id = c.id;

一般推荐使用INNER JOIN和ON的方式进行多表关联，具体如下：

sql
SELECT * FROM stu as s INNER JOIN class as c ON s.class_id = c.id;

这样的方式语义更加清晰，INNER JOIN后面的表示被关联的表，关联前后的表，ON后面表示条件

对于一对一的表进行关联，学生表和学生具体信息表：

sql
SELECT * FROM stu as s INNER JOIN stu_info as si ON s.id = si.stu_id;

返回的结果数据条数是学生具体信息表的数据条数，对于没有学生具体信息的学生表中的学生，返回结果会将其过滤掉，只有完全匹配的会被拿出来

多个表关联后的结果，可以理解为其结果是一张表，对后续的查询和筛选操作可以当作对一张表进行操作，如查询班级为1班的学生：

sql
SELECT * FROM stu as s INNER JOIN class as c ON s.class_id = c.id WHERE c.cname = '1班';

关联后的结果可以继续与其他表进行关联，一直关联下去，直到得到想要的任务结果：

sql
SELECT c.cname,c.id,s.id,s.sname,a.title FROM stu as s INNER JOIN class as c
ON s,class_id = c.id
INNER JOIN article as a
ON s.id = a.stu_id;

也可以将INNER JOIN的内容连接起来写，ON的内容连接起来写：

sql
SELECT c.cname,c.id,s.id,s.sname,a.title FROM stu as s 
INNER JOIN class as c
INNER JOIN article as a
ON s,class_id = c.id AND ON s.id = a.stu_id;

分组筛选 ​

分组筛选是在分组查询的基础上进行WHERE的条件筛选，将多表关联分组后，理解为一张表，再进行查询

筛选为男生写的文章：

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

外链接在多表查询中的使用 ​

对于一对一的表进行关联，学生表和学生具体信息表：

sql
SELECT * FROM stu as s INNER JOIN stu_info as si ON s.id = si.stu_id;

返回的结果数据条数是学生具体信息表的数据条数，对于没有学生具体信息的学生表中的学生，返回结果会将其过滤掉，只有完全匹配的会被拿出来

对于学生具体信息没有设置的，其学生表中的学生基本信息也会被过滤掉，如果就需要找这些没有设计具体信息的学生，就需要使用到外链接，LEFT JOIN和RIGHT JOIN，左侧偏心（左外连接）（将左侧的表信息全部读取出来，无无论有没有匹配）和右侧偏心（右外连接）（将右侧的表信息全部读取出来，无论有没有匹配）

sql
SELECT * FROM stu as s LEFT JOIN stu_info as si ON s.id = si.stu_id;

这样如果没有具体信息的学生，其基本内容也会返回出来

查找哪些同学没有在具体信息表中设置QQ：

sql
SELECT * FROM stu as s LEFT JOIN stu_info as si ON s.id = si.stu_id WHERE si.qq is null;

右侧偏心关联和左侧偏心关联使用方式一样，会将右侧表的信息全部展示出来，即使没有被左侧的内容进行匹配

查找哪些班级没有学生：

sql
SELECT c.cname FROM stu as s RIGHT JOIN class as c ON s.class_id = c.id 
WHERE s.id is null;

查找学生所在的班级，如果学生没有分配班级，就显示null：

sql
SELECT s.sname,if(s.class_id,c.cname,'无') as cname FROM class as c 
RIGHT JOIN stu as s ON c.id = s.class_id;

自链接 ​

自链接就是自己链接自己（将同一张表看作两张表使用），具体使用为：与小金同学在一个班级的同学：

第一种方式是使用子查询：

sql
SELECT * FROM stu WHERE class_id = (SELECT class_id FROM stu WHERE sname = '小金')
AND sname != '小金';

使用自链接的方法进行查询：自己关联自己（需要使用别名），将一张表当作两张表进行关联使用

sql
SELECT s2.sname FROM stu as s1 INNER JOIN stu as s2
ON s1.class_id = s2.class_id
WHERE s1.sname = '小金' AND s2.sname != '小金';

查找出生年份比小金大的同学：（出生日期越小，年龄越大）

sql
SELECT s2.sname FROM stu as s1 INNER JOIN stu as s2
ON YEAR(s1.birthday) > YEAR(s2.birthday)
WHERE s1.sname = '小金';

多表操作中的多对多关系 ​

对于学生表和课程表之间，就是一个典型的多对多关系，一个学生可以学习多个课程，一个课程也可以被多个学生学习，多对多关系是不能让一方剔除另一方的，对于多对多关系的表，需要使用中间表来进行记录关系

查找小金学习的所有课程：

sql
SELECT s.sname,l.name FROM stu as s INNER JOIN stu_lesson as sl ON s.id = sl.stu_id
INNER JOIN lesson as l ON l.id = sl.lesson_id WHERE s.sname = '小金';

查找哪个班级的学生最喜欢mysql课程：

sql
SELECT c.id, count(*) as total FROM class as c INNER JOIN stu as s ON c.id = s.class_id
INNER JOIN stu_lesson as sl ON s.id = sl,stu_id
INNER JOIN lesson as l ON sl.lesson_id = l.id
WHERE l.name = 'mysql'
GROUP BY c.id
ORDER BY total desc LIMIT 1;

多表操作简单来说就是首先需要确定使用哪几张表，将这几张表全部连接起来，再当作一种表进行筛选处理

多表查询多个查询结果的连接和合并 ​

默认合并，如果有重复的是会过滤掉重复的，只显示一个重复的数据

sql
SELECT * FROM stu UNION SELECT * FROM stu;

如果我们不想让重复的数据被过滤，需要在UNION后面加上ALL即可：

sql
SELECT * FROM stu UNION ALL SELECT * FROM stu;

这样就会得到两次完整的查询结果

使用UNION ALL可以进行连接不同的表，但是连接的时候，字段的数量要相等：

sql
SELECT sname FROM stu UNION ALL SELECT cname FROM class;

两张表都提供一个字段进行连接，是可以进行连接的，连接后，两张表的内容被放到了同一列，使用的字段名是第一条记录的字段名，也就是sname

但是如果对不同的表进行条件的添加，需要使用括号进行包裹：

sql
(SELECT sname FROM stu LIMIT 3) UNION ALL (SELECT cname FROM class LIMIT 3);

对连接完后的结果，我们可以将其当作一张新表，可以进行其他的筛选处理操作：

sql
(SELECT sname FROM stu LIMIT 3) UNION ALL (SELECT cname FROM class LIMIT 3) LIMIT 2;

查询学生表中年龄最大的和最小的数据，进行合并：

sql
(SELECT * FROM stu WHERE birthday is not null ORDER BY birthday ASC LIMIT 1)
UNION ALL
(SELECT * FROM stu WHERE birthday is not null ORDER BY birthday DESC LIMIT 1);

多表查询数据的删除 ​

我们可以对多表查询的结果进行删除，删除操作是有破坏性的

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

  • 方法一：使用子查询的方式进行删除：

    sql
    DELECT FROM stu WHERE id in(
SELECT * FROM(
SELECT s.id FROM stu as s LEFT JOIN stu_lesson as sl 
ON s.id = sl.stu_id
WHERE sl.lesson_id is null) as s
);

    在实际开发中，一般是先发一条sql语句，在发一条语句进行处理，而不是写成一条语句，一条语句的形式往往不够清晰，不通俗易懂

  • 方法二：使用多表来进行删除操作：

    sql
    DELECT s FROM stu as s
LEFT JOIN stu_lesson as sl ON s.id = sl.stu_id
WHERE sl.lesson_id is null;

    明确告知要删除学生表s中的数据

删除操作也可以使用存储过程来进行删除

Released under the MIT License.

Copyright © 2023-present JinLinC