✨个人主页：bit me👇
✨当前专栏：MySQL数据库👇
✨算法专栏：算法基础👇
✨每日一语：悟已往之不谏，知来者之可追。实迷途其未远，觉今是而昨非。
目 录
🎄一. 数据库约束
- 约束就是数据库可以让程序猿定义一些对数据的限制规则，数据库会在插入 / 修改数据的时候按照这些规则对数据进行校验，如果校验不通过，就直接报错。
约束的本质就是让我们及时发现数据中的错误，更好的保证数据的正确性
约束类型：
- NOT NULL - 指示某列不能存储 NULL 值。（必填项）
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。（可以通过default修改这个默认值）
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句
①：NOT NULL
- 初始情况下，一个表允许为NULL。
mysql>desc student;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id |int(11)| YES ||NULL||| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)
mysql>insertinto student values(NULL,NULL);
Query OK,1row affected (0.00 sec)
加上 NOT NULL 约束之后，就不再允许插入空值
mysql>droptable student;
Query OK,0rows affected (0.01 sec)
mysql>createtable student (id intnotnull, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>desc student;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id |int(11)|NO||NULL||| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)
再尝试插入空值就会报错！
mysql>insertinto student values(NULL,NULL);
ERROR 1048(23000): Column'id' cannot be null
②：UNIQUE 唯一性
- 默认情况下，表里的数据都是可以重复的
mysql>droptable student;
Query OK,0rows affected (0.01 sec)
mysql>createtable student (id int, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>insertinto student values(1,'张三');
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(1,'张三');
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(1,'张三');
Query OK,1row affected (0.00 sec)
mysql>select*from student;+------+--------+| id | name |+------+--------+|1| 张三 ||1| 张三 ||1| 张三 |+------+--------+3rowsinset(0.00 sec)
没有约束的表和有约束的表对比
mysql>desc student;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id |int(11)| YES ||NULL||| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)
mysql>droptable student;
Query OK,0rows affected (0.01 sec)
mysql>createtable student (id intunique, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>desc student;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id |int(11)| YES | UNI |NULL||| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)
当我们想再次输入同样的数据的时候发现报错
mysql>insertinto student values(1,'张三');
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(1,'张三');
ERROR 1062(23000): Duplicate entry '1'forkey'id'
Duplicate --> 重复， entry --> 条目（不是入口） 触发了 UNIQUE 约束
③：DEFAULT
- 设定默认值
默认的默认值是NULL，可以通过 default 约束来修改这里的默认值的取值
mysql>droptable student;
Query OK,0rows affected (0.01 sec)
mysql>createtable student (id int, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>desc student;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id |int(11)| YES ||NULL||| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)
对表进行指定列插入的时候，会涉及到默认值的情况的
mysql>insertinto student (id)values(1);
Query OK,1row affected (0.00 sec)
指定了 id 这一列插入，此时的 name 就是按照默认值来走的
mysql>select*from student;+------+------+| id | name |+------+------+|1|NULL|+------+------+1rowinset(0.00 sec)
通过 default 约束来修改默认值
mysql>droptable student;
Query OK,0rows affected (0.00 sec)
mysql>createtable student (id intdefault0, name varchar(20)default'未命名');
Query OK,0rows affected (0.01 sec)
mysql>desc student;+-------+-------------+------+-----+-----------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+-----------+-------+| id |int(11)| YES ||0||| name |varchar(20)| YES || 未命名 ||+-------+-------------+------+-----+-----------+-------+2rowsinset(0.00 sec)
mysql>insertinto student (id)values(1);
Query OK,1row affected (0.00 sec)
mysql>select*from student;+------+-----------+| id | name |+------+-----------+|1| 未命名 |+------+-----------+1rowinset(0.00 sec)
mysql>insertinto student (name)values('张三');
Query OK,1row affected (0.00 sec)
mysql>select*from student;+------+-----------+| id | name |+------+-----------+|1| 未命名 ||0| 张三 |+------+-----------+2rowsinset(0.00 sec)
④：PRIMARY KEY --> 主键
- 在设计一个表的时候，非常重要的一个列，表示一条记录的身份标识，用来区分这条记录和别的记录的
注：
- 不能为空，相当于 NOT NULL
- 不能重复，相当于UNIQUE
- 一个表里只能有一个主键
mysql>droptable student;
Query OK,0rows affected (0.00 sec)
mysql>createtable student (id intprimarykey,name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>desc student;+-------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+-------+| id |int(11)|NO| PRI |NULL||| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00 sec)
添加元素：
mysql>insertinto student values(null,null);
ERROR 1048(23000): Column'id' cannot be null
mysql>insertinto student values(1,'张三');
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(1,'张三');
ERROR 1062(23000): Duplicate entry '1'forkey'PRIMARY'
主键不能为空，主键的值也不能重复
由于主键必须要填，还不能重复，MySQL为了方便大家填写主键，内置了一个功能 ”自增主键“ ，帮助我们自动生成主键的值，就不用程序猿自己来保证了
mysql>droptable student;
Query OK,0rows affected (0.01 sec)
mysql>createtable student (id intprimarykeyauto_increment, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>desc student;+-------+-------------+------+-----+---------+----------------+| Field |Type|Null|Key|Default| Extra |+-------+-------------+------+-----+---------+----------------+| id |int(11)|NO| PRI |NULL|auto_increment|| name |varchar(20)| YES ||NULL||+-------+-------------+------+-----+---------+----------------+2rowsinset(0.00 sec)
当前 id 是自增主键，不同于主键，可以使用 null
mysql>insertinto student values(null,'张三');
Query OK,1row affected (0.00 sec)
- 如果写的是具体数值，就是手动指定 id
- 如果写的是 null ，就是让 MySQL 按照自增主键自动生成
mysql>select*from student;+----+--------+| id | name |+----+--------+|1| 张三 |+----+--------+1rowinset(0.00 sec)
mysql>insertinto student values(null,'张三');
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(null,'张三');
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(null,'张三');
Query OK,1row affected (0.00 sec)
mysql>select*from student;+----+--------+| id | name |+----+--------+|1| 张三 ||2| 张三 ||3| 张三 ||4| 张三 |+----+--------+4rowsinset(0.00 sec)
中间插入手动增加之后自增从当前的数字自增
mysql>insertinto student values(100,'张三');
Query OK,1row affected (0.00 sec)
mysql>select*from student;+-----+--------+| id | name |+-----+--------+|1| 张三 ||2| 张三 ||3| 张三 ||4| 张三 ||100| 张三 |+-----+--------+5rowsinset(0.00 sec)
mysql>insertinto student values(null,'张三');
Query OK,1row affected (0.00 sec)
mysql>select*from student;+-----+--------+| id | name |+-----+--------+|1| 张三 ||2| 张三 ||3| 张三 ||4| 张三 ||100| 张三 ||101| 张三 |+-----+--------+6rowsinset(0.00 sec)
- MySQL 想要自增，必须要能够记录下来当前 id 已经到哪了，还要保证自增后，得是不能重复的。
- MySQL里简单粗暴的做法，直接就是记录当前 自增主键 里的最大值，这就能保证自增速度很快，并且一定是不重复的
- 可能会觉得有数据库浪费了，但是数据库一般而言是很大的，一般不考虑这个浪费
自增主键，主要就是用来生成一个唯一的 id ，来保证不重复，如果数据库是分布式部署，这个时候自增主键就要带来问题。
在一个MySQL集群里，有许多的MySQL节点（多个节点上的数据放在一起才是一个完整的数据集合），MySQL生成自增主键的时候可以保证在自己这个节点上生成的 id 是唯一的，但是无法保证这个 id 在其他节点上也是唯一的。
生成一个 ID 这样的需求，是客观存在的，但是 MySQL 自增主键，已经难以满足要求了
一个典型的解决方法：在生成 id 的时候，让这些节点相互协商一下，彼此了解了对方的情况之后，就能生成唯一的 id 了，但是代价有点大。为了轻量，高效，又出了另外一个方法：
唯一 id = 时间戳(ms) + 机房编号/主机编号 + 随机因子 （这里的 + 不是算术运算 而是字符串拼接）
，进入数据库的数据有先有后形成时间戳，并且会分摊到不同的主机上，就算是一个主机，又会生成随机数的！（也有极端情况三种一模一样，理论上存在但是工程上是忽略不计的！）
⑤：FOREIGN KEY
- 描述了两张表之间的关联关系
举例：
- 班级表是负责约束的一方，称为父表
- 学生表是被约束的一方，称为子表
mysql>droptable student;
Query OK,0rows affected (0.01 sec)
mysql>createtable class (classId intprimarykey,className varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>createtable student (studentId intprimarykey, name varchar(20), classId int,foreignkey(classId)references class (classId));
Query OK,0rows affected (0.01 sec)
mysql>
mysql>desc student;+-----------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+-----------+-------------+------+-----+---------+-------+| studentId |int(11)|NO| PRI |NULL||| name |varchar(20)| YES ||NULL||| classId |int(11)| YES | MUL |NULL||+-----------+-------------+------+-----+---------+-------+3rowsinset(0.00 sec)
- 第一句为父表，第二句为子表
- 先把所有的列都定义完，逗号，然后再写外键约束
在父表为空的情况下，直接尝试往子表插入，就会报错！！！
mysql>insertinto student values(1,'张三',1);
ERROR 1452(23000): Cannot addorupdate a child row: a foreignkeyconstraint fails (`java`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`classId`)REFERENCES`class`(`classId`))
在父表里面添加一些数据：
mysql>insertinto class values(1,'java101');
Query OK,1row affected (0.00 sec)
mysql>insertinto class values(2,'java102');
Query OK,1row affected (0.00 sec)
mysql>insertinto class values(3,'java103');
Query OK,1row affected (0.00 sec)
mysql>select*from class;+---------+-----------+| classId | className |+---------+-----------+|1| java101 ||2| java102 ||3| java103 |+---------+-----------+3rowsinset(0.00 sec)
往子表插入数据
mysql>insertinto student values(1,'张三',4);
ERROR 1452(23000): Cannot addorupdate a child row: a foreignkeyconstraint fails (`java`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`classId`)REFERENCES`class`(`classId`))
依旧报错，插入子表的数据没有在父表的 classId 中，同样也是报的刚才的错误
在外键的约束下，每次你插入 / 修改操作，都会先触发在父表中的查询，父表中存在才能插入 / 修改成功，否则就会失败。
mysql>insertinto student values(1,'张三',1);
Query OK,1row affected (0.00 sec)
每次插入都要先查询会拖慢执行效率，但是也没有特别拖慢
如果查询操作触发了遍历表，低效的；如果触发了索引，相对于快不少
建立外键约束的时候，MySQL 就要求，引用的父表的列，必须是主键或者UNIQUE（自带索引，查询速度就会快一些）
父表对子表产生了限制，但是反过来子表也会对父表产生限制
父表对子表的限制是不能随意插入 / 修改 子表对于父表的限制就是不能随意修改 / 删除
mysql>select*from student;+-----------+--------+---------+| studentId | name | classId |+-----------+--------+---------+|1| 张三 |1|+-----------+--------+---------+1rowinset(0.00 sec)
mysql>deletefrom class where classId =1;
ERROR 1451(23000): Cannot deleteorupdate a parent row: a foreignkeyconstraint fails (`java`.`student`,CONSTRAINT`student_ibfk_1`FOREIGNKEY(`classId`)REFERENCES`class`(`classId`))
由于在子表中，引入了 classId = 1 的记录，尝试删除父表中的对应记录，发现，这个就难以删除
⑥： CHECK（了解）
- 直接对表中的值做出限制
check(sex ='男'or sex='女')
例如限制性别这一列只能是 男 或 女
🌲二. 表的设计
- 根据一些实际的业务场景，来设计表，主要就是确定有几个表，每个表干啥，每个表有多少个字段。
面试官：你的项目里的数据库是如何设计的？
- 其实是让你回答，你的数据库里有几个表，每个表是干啥的，以及表里有哪些字段
那我们该如何设计它呢？
我们需要明确需求场景，提取出需求中的 " 实体 " ，实体可以认为是关键性名词，一般来说，每个实体都会分配一个表来进行表示，除了实体之外，还需要理清楚，实体和实体之间的关系。
- 一对一
- 一对多
- 多对多
- 没关系
例如一个学校的教务系统：
有学生，账号，课程，班级
- 一个学生只能有一个账号，一个账号只能分配给一个学生（一对一）
- 一个同学只属于一个班级，一个班级包含多个学生（一对多）
- 一个学生可以选择多门课程，一个课程可以包含多个学生（多对多）
不同的关系，在设计表的时候有不同的套路，对应的设计表的套路
- 一对一：
1.把学生和账号，直接放在一个表里！
student (id, name, account, password…)
这种设计方式是下册，尤其是一个系统中包含不同身份角色的时候
2.把学生和账号各自放到一个表里，使用一个额外的 id 来关联
student (studentId, name, accountId)
account (accountId, password)
或
student (studentId, name)
account (accountId, password,studentId)
- 一对多：
方法1：
student (id, name)
1, 张三
2, 李四
3, 王五
class (classId, className, studentList)
1 Java101 1，2
2 Java102 3
方法2:
class (classId, className）
1, java101
2, java102
student (id, name, classId)
1 张三 1
2 李四 1
3 王五 2
因为 MySQL 中没有 " 数组 " 这样的类型，第一种方案是无法实现的（可以用字符串拼接的方式凑合着来实现，但是实际上是不好设计的，比较低效，也失去了数据库对于数据校验的一些能力）
- 多对多：
一般就是采用个中间表，俩表示多对多的关系
student (studentId, name)
1 张三
2 李四
3 王五
course (courseId, courseName)
1 语文
2 数学
3 英语
student_course (studentId, courseId)
1 1 (张三选了语文课)
1 2 (张三选了数学课)
2 1 (李四选了语文课)
数据库设计要点：
找出实体描述清楚实体之间的关系代入固定套路即可
如果需求场景比较简单，很容易理清其中的实体关系
如果需求场景比较复杂，可能涉及到很多实体，会很乱，因此程序猿发明了一个工具 – ER 图（实体 – 关系图）
关联之后，字段名相同是不会有影响的！
mysql>select*from class;+---------+-----------+| classId | className |+---------+-----------+|1| java101 ||2| java102 ||3| java103 |+---------+-----------+3rowsinset(0.00 sec)
mysql>select*from student;+-----------+--------+---------+| studentId | name | classId |+-----------+--------+---------+|1| 张三 |1|+-----------+--------+---------+1rowinset(0.00 sec)
例如这俩张表里都有 classId 但是详细讲就是 class.classId 和 student.classId
🌳三. 新增
- 把查询结果作为新增的数据
insert into 表名1 select 列名 from 表名2;
先执行查询操作，查询出来的结果插入到另外一个表里，保证查询结果的临时表的列要和插入的表的列匹配！！！
- 要求从表名 2 中查询出来的结果的列数和类型 和表名 1 匹配，不要求列名匹配
mysql>createtable student (id int, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>insertinto student values(1,'张三'),(2,'李四'),(3,'王五');
Query OK,3rows affected (0.00 sec)
Records: 3 Duplicates: 0Warnings: 0
mysql>createtable student2 (id int, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>select*from student;+------+--------+| id | name |+------+--------+|1| 张三 ||2| 李四 ||3| 王五 |+------+--------+3rowsinset(0.00 sec)
mysql>select*from student2;
Empty set(0.00 sec)
mysql>insertinto student2 select*from student;
Query OK,3rows affected (0.00 sec)
Records: 3 Duplicates: 0Warnings: 0
mysql>select*from student2;+------+--------+| id | name |+------+--------+|1| 张三 ||2| 李四 ||3| 王五 |+------+--------+3rowsinset(0.00 sec)
🌴四. 查询
- 聚合查询：
- 通过行和行之间进行的运算处理
表达式查询是列和列之间
🏳️4.1 聚合函数（以下都是聚合查询）
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义
这些都属于 SQL 内置函数，SQL 作为一个编程语言，也是内置了一些库函数供我们来使用的
- COUNT函数的使用：
mysql>select*from exam_result;+------+-----------+---------+------+---------+| id | name | chinese | math | english |+------+-----------+---------+------+---------+|1| 唐三藏 |67.0|98.0|56.0||2| 孙悟空 |87.5|78.0|77.0||3| 猪悟能 |88.0|98.5|90.0||4| 曹孟德 |82.0|84.0|67.0||5| 刘玄德 |55.5|85.0|45.0||6| 孙权 |70.0|73.0|78.5||7| 宋公明 |75.0|65.0|30.0|+------+-----------+---------+------+---------+7rowsinset(0.00 sec)
mysql>selectcount(*)from exam_result;+----------+|count(*)|+----------+|7|+----------+1rowinset(0.00 sec)
mysql>selectcount(name)from exam_result;+-------------+|count(name)|+-------------+|7|+-------------+1rowinset(0.00 sec)
那么其中使用 * 和 name(列名) 有什么区别呢？
mysql>insertinto exam_result values(null,null,null,null,null);
Query OK,1row affected (0.00 sec)
mysql>select*from exam_result;+------+-----------+---------+------+---------+| id | name | chinese | math | english |+------+-----------+---------+------+---------+|1| 唐三藏 |67.0|98.0|56.0||2| 孙悟空 |87.5|78.0|77.0||3| 猪悟能 |88.0|98.5|90.0||4| 曹孟德 |82.0|84.0|67.0||5| 刘玄德 |55.5|85.0|45.0||6| 孙权 |70.0|73.0|78.5||7| 宋公明 |75.0|65.0|30.0||NULL|NULL|NULL|NULL|NULL|+------+-----------+---------+------+---------+8rowsinset(0.00 sec)
mysql>selectcount(*)from exam_result;+----------+|count(*)|+----------+|8|+----------+1rowinset(0.00 sec)
mysql>selectcount(name)from exam_result;+-------------+|count(name)|+-------------+|7|+-------------+1rowinset(0.00 sec)
可以看到我们再添加一个全为空的信息，
* 把 NULL 值也记录到行数中了，name(列名) 对于 NULL 不会计数。
注：
count(name) 中间不能带有空格，否则就会出错，SQL 不能被正常解析
- SUM函数的使用：
mysql>select*from exam_result;+------+-----------+---------+------+---------+| id | name | chinese | math | english |+------+-----------+---------+------+---------+|1| 唐三藏 |67.0|98.0|56.0||2| 孙悟空 |87.5|78.0|77.0||3| 猪悟能 |88.0|98.5|90.0||4| 曹孟德 |82.0|84.0|67.0||5| 刘玄德 |55.5|85.0|45.0||6| 孙权 |70.0|73.0|78.5||7| 宋公明 |75.0|65.0|30.0||NULL|NULL|NULL|NULL|NULL|+------+-----------+---------+------+---------+8rowsinset(0.01 sec)
mysql>selectsum(math)from exam_result;+-----------+|sum(math)|+-----------+|581.5|+-----------+1rowinset(0.00 sec)
在我们之前学过，
NULL 和其他数字进行算数运算，结果还是 NULL，在这里可以得出结论SUM函数中 NULL 没有参加运算
- 此处聚合查询也是完全可以指定筛选条件的！
mysql>selectsum(english)from exam_result where english <60;+--------------+|sum(english)|+--------------+|131.0|+--------------+1rowinset(0.00 sec)
mysql>selectsum(english)from exam_result;+--------------+|sum(english)|+--------------+|443.5|+--------------+1rowinset(0.00 sec)
对于带有条件的聚合查询，先会按照条件进行筛选，筛选后得到的结果进行聚合
- 对名字进行求和
mysql>selectsum(name)from exam_result;+-----------+|sum(name)|+-----------+|0|+-----------+1rowinset,7warnings(0.00 sec)
名字是字符串无法求和，所以 sum 函数只能对数字进行求和
- AVG函数的使用
mysql>selectavg(math)from exam_result;+-----------+|avg(math)|+-----------+|83.07143|+-----------+1rowinset(0.00 sec)
此处通过计算也可以得出平均值的计算是不会计入 NULL 值的个数的
- MAX 和 MIN 函数的使用：
mysql>selectmax(math)from exam_result;+-----------+|max(math)|+-----------+|98.5|+-----------+1rowinset(0.00 sec)
mysql>selectmin(math)from exam_result;+-----------+|min(math)|+-----------+|65.0|+-----------+1rowinset(0.00 sec)
🏴4.2 分组查询
- 把表中的若干行，分成好几组，指定某一列作为分组的依据，分组依据的列值相同，则被归为一组，分成多个组之后，还可以针对每个组，分别使用聚合函数。
group by 列
建一张表：
mysql>createtable student (id int, name varchar(20), gender varchar(20), score int);
Query OK,0rows affected (0.01 sec)
mysql>insertinto student values(1,'张三','男',95);
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(2,'李四','女',75);
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(3,'王五','男',85);
Query OK,1row affected (0.00 sec)
mysql>insertinto student values(4,'赵六','女',65);
Query OK,1row affected (0.00 sec)
mysql>select*from student;+------+--------+--------+-------+| id | name | gender | score |+------+--------+--------+-------+|1| 张三 | 男 |95||2| 李四 | 女 |75||3| 王五 | 男 |85||4| 赵六 | 女 |65|+------+--------+--------+-------+4rowsinset(0.00 sec)
需要统计，男生和女生分别各自的最高分，平均分，最低分
mysql>select gender,max(score),min(score),avg(score)from student groupby gender;+--------+------------+------------+------------+| gender |max(score)|min(score)|avg(score)|+--------+------------+------------+------------+| 女 |75|65|70.0000|| 男 |95|85|90.0000|+--------+------------+------------+------------+2rowsinset(0.00 sec)
分组规则，是把记录的值相同的行作为一组
在进行聚合查询的时候，也能指定条件筛选
- 在聚合之前，进行筛选，针对筛选后的结果，再聚合。（where 子句）
- 在聚合之后，进行筛选。（having 子句）
- 聚合之前查询：查询每个性别平均分（但是出去赵六同学）
mysql>select gender,avg(score)from student where name !='赵六'groupby gender;+--------+------------+| gender |avg(score)|+--------+------------+| 女 |75.0000|| 男 |90.0000|+--------+------------+2rowsinset(0.00 sec)
- 聚合之后筛选：查询平均分大于 80 的性别情况，需要先把平均分算出来（算平均分，需要先聚合）
mysql>select gender,avg(score)from student groupby gender havingavg(score)>80;+--------+------------+| gender |avg(score)|+--------+------------+| 男 |90.0000|+--------+------------+1rowinset(0.00 sec)
- 同时在前面和后面都筛选：查询平均分大于 80 的性别情况（除去赵六）
mysql>select gender,avg(score)from student where name !='赵六'groupby gender havingavg(score)>80;+--------+------------+| gender |avg(score)|+--------+------------+| 男 |90.0000|+--------+------------+1rowinset(0.00 sec)
聚合查询的执行过程：先按照 where 条件进行筛选记录，把筛选出来的结果按照 group by 来分组，分组之后按照 having 再进行筛选，最后再按照制定列中调用的聚合函数来显示计算结果。
联合查询
- 也叫多表查询，是把多个表的记录合并到一起，综合进行查询
select * from 表1, 表2;
联合查询中的核心概念：笛卡尔积。
笛卡尔积就是把这两个表中的所有记录，进行排列组合，穷举出所有的可能情况
排列组合之后的结果就是笛卡尔积。笛卡尔积的列数就是原来俩张表的列数之和，笛卡尔积的行数就是原来俩张表的行数之和。
针对多个表进行联合查询，本质上就是先针对多个表进行笛卡尔积运算。万一原来的表就很大，再进行多个表的笛卡尔积，就会得到一个更复杂的表，这个过程就会很低效，因此，在实际开发中进行多表查询，一定要克制。
笛卡尔积中包含了大量无效数据，指定了合理的过滤条件，把有效的数据挑出来，这个时候就得到了一个非常有用的数据表，这个过程就是 " 联合查询 " 的过程。
多表查询的一般步骤：
1. 先根据需求理清楚说想要的数据都在哪些表中2. 【核心操作】先针对多个表进行笛卡尔积3. 根据连接条件，筛选出合法数据，过滤掉非法数据4. 进一步增加条件，根据需求做更精细的筛选5. 去掉不必要的列，保留最关注的信息
此处创建四个表：
mysql>createtable classes (id intprimarykeyauto_increment, name varchar(20),`desc`varchar(100));
Query OK,0rows affected (0.01 sec)
mysql>createtable student (id intprimarykeyauto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20), classes_id int);
Query OK,0rows affected (0.01 sec)
mysql>createtable course (id intprimarykeyauto_increment, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>createtable score (score decimal(3,1),student_id int, course_id int);
Query OK,0rows affected (0.01 sec)
填入数据：
mysql>insertinto classes(name,`desc`)values->('计算机系2019级1班','学习了计算机原理、C和Java语言、数据结构和算法'),->('中文系2019级3班','学习了中国传统文学'),->('自动化2019级5班','学习了机械自动化');
Query OK,3rows affected (0.00 sec)
Records: 3 Duplicates: 0Warnings: 0
mysql>insertinto student(sn, name, qq_mail, classes_id)values->('09982','黑旋风李逵','[email protected]',1),->('00835','菩提老祖',null,1),->('00391','白素贞',null,1),->('00031','许仙','[email protected]',1),->('00054','不想毕业',null,1),->('51234','好好说话','[email protected]',2),->('83223','tellme',null,2),->('09527','老外学中文','[email protected]',2);
Query OK,8rows affected (0.00 sec)
Records: 8 Duplicates: 0Warnings: 0
mysql>insertinto course(name)values->('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
Query OK,6rows affected (0.00 sec)
Records: 6 Duplicates: 0Warnings: 0
mysql>insertinto score(score, student_id, course_id)values->-- 黑旋风李逵->(70.5,1,1),(98.5,1,3),(33,1,5),(98,1,6),->-- 菩提老祖->(60,2,1),(59.5,2,5),->-- 白素贞->(33,3,1),(68,3,3),(99,3,5),->-- 许仙->(67,4,1),(23,4,3),(56,4,5),(72,4,6),->-- 不想毕业->(81,5,1),(37,5,5),->-- 好好说话->(56,6,2),(43,6,4),(79,6,6),->-- tellme->(80,7,2),(92,7,6);
Query OK,20rows affected (0.00 sec)
Records: 20 Duplicates: 0Warnings: 0
打印四个表：
mysql>select*from student;+----+-------+-----------------+------------------+------------+| id | sn | name | qq_mail | classes_id |+----+-------+-----------------+------------------+------------+|1|09982| 黑旋风李逵 | [email protected]|1||2|00835| 菩提老祖 |NULL|1||3|00391| 白素贞 |NULL|1||4|00031| 许仙 | [email protected]|1||5|00054| 不想毕业 |NULL|1||6|51234| 好好说话 | [email protected]|2||7|83223| tellme |NULL|2||8|09527| 老外学中文 | [email protected]|2|+----+-------+-----------------+------------------+------------+8rowsinset(0.00 sec)
mysql>select*from classes;+----+-------------------------+-------------------------------------------------------------------+| id | name |desc|+----+-------------------------+-------------------------------------------------------------------+|1| 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 ||2| 中文系2019级3班 | 学习了中国传统文学 ||3| 自动化2019级5班 | 学习了机械自动化 |+----+-------------------------+-------------------------------------------------------------------+3rowsinset(0.00 sec)
mysql>select*from score;+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|70.5|1|1||98.5|1|3||33.0|1|5||98.0|1|6||60.0|2|1||59.5|2|5||33.0|3|1||68.0|3|3||99.0|3|5||67.0|4|1||23.0|4|3||56.0|4|5||72.0|4|6||81.0|5|1||37.0|5|5||56.0|6|2||43.0|6|4||79.0|6|6||80.0|7|2||92.0|7|6|+-------+------------+-----------+20rowsinset(0.00 sec)
mysql>select*from course;+----+--------------------+| id | name |+----+--------------------+|1| Java ||2| 中国传统文化 ||3| 计算机原理 ||4| 语文 ||5| 高阶数学 ||6| 英文 |+----+--------------------+6rowsinset(0.00 sec)
🏁4.3 内连接（以下都是联合查询）
- select 列名 from 表1，表2…;
- select 列名 from 表1 inner join 表2…;
①：目标：查询 “ 许仙 ” 同学的 成绩：
在这里提及一下为什么不用代码的形式，根据笛卡尔积行数是由两个表的行数之积得来的，成绩表和学生表行数相乘 160 行代码太长，因此截图省略位置。
其中 160 行代码大部分是无用数据，清理掉即可
mysql>select*from student, score where student.id = score.student_id;+----+-------+-----------------+-----------------+------------+-------+------------+-----------+| id | sn | name | qq_mail | classes_id | score | student_id | course_id |+----+-------+-----------------+-----------------+------------+-------+------------+-----------+|1|09982| 黑旋风李逵 | [email protected]|1|70.5|1|1||1|09982| 黑旋风李逵 | [email protected]|1|98.5|1|3||1|09982| 黑旋风李逵 | [email protected]|1|33.0|1|5||1|09982| 黑旋风李逵 | [email protected]|1|98.0|1|6||2|00835| 菩提老祖 |NULL|1|60.0|2|1||2|00835| 菩提老祖 |NULL|1|59.5|2|5||3|00391| 白素贞 |NULL|1|33.0|3|1||3|00391| 白素贞 |NULL|1|68.0|3|3||3|00391| 白素贞 |NULL|1|99.0|3|5||4|00031| 许仙 | [email protected]|1|67.0|4|1||4|00031| 许仙 | [email protected]|1|23.0|4|3||4|00031| 许仙 | [email protected]|1|56.0|4|5||4|00031| 许仙 | [email protected]|1|72.0|4|6||5|00054| 不想毕业 |NULL|1|81.0|5|1||5|00054| 不想毕业 |NULL|1|37.0|5|5||6|51234| 好好说话 | [email protected]|2|56.0|6|2||6|51234| 好好说话 | [email protected]|2|43.0|6|4||6|51234| 好好说话 | [email protected]|2|79.0|6|6||7|83223| tellme |NULL|2|80.0|7|2||7|83223| tellme |NULL|2|92.0|7|6|+----+-------+-----------------+-----------------+------------+-------+------------+-----------+20rowsinset(0.00 sec)
就只剩下同学的各科成绩，再对 “ 许仙 ” 进行筛选：
mysql>select*from student, score where student.id = score.student_id and student.name ='许仙';+----+-------+--------+---------------+------------+-------+------------+-----------+| id | sn | name | qq_mail | classes_id | score | student_id | course_id |+----+-------+--------+---------------+------------+-------+------------+-----------+|4|00031| 许仙 | [email protected]|1|67.0|4|1||4|00031| 许仙 | [email protected]|1|23.0|4|3||4|00031| 许仙 | [email protected]|1|56.0|4|5||4|00031| 许仙 | [email protected]|1|72.0|4|6|+----+-------+--------+---------------+------------+-------+------------+-----------+4rowsinset(0.00 sec)
最后只需要过滤掉不必要的列：
mysql>select name, course_id, score from student, score where student.id = score.student_id and student.name ='许仙';+--------+-----------+-------+| name | course_id | score |+--------+-----------+-------+| 许仙 |1|67.0|| 许仙 |3|23.0|| 许仙 |5|56.0|| 许仙 |6|72.0|+--------+-----------+-------+4rowsinset(0.00 sec)
一张清晰完整的结果就呈现在我们眼前。
通过 select 列名 from 表1, 表2 这种方式可以进行多表查询，除此之外，还有另外一种写法： select 列名 from 表1 join 表2 on 连接条件; (当使用 join 计算笛卡尔积的时候，后面的条件使用 on 来表示，而不是 where ，这个相当于 " 固定搭配 ")
mysql>select*from student join score on student.id = score.student_id and student.name ='许仙';+----+-------+--------+---------------+------------+-------+------------+-----------+| id | sn | name | qq_mail | classes_id | score | student_id | course_id |+----+-------+--------+---------------+------------+-------+------------+-----------+|4|00031| 许仙 | [email protected]|1|67.0|4|1||4|00031| 许仙 | [email protected]|1|23.0|4|3||4|00031| 许仙 | [email protected]|1|56.0|4|5||4|00031| 许仙 | [email protected]|1|72.0|4|6|+----+-------+--------+---------------+------------+-------+------------+-----------+4rowsinset(0.00 sec)
mysql>select student.name, score.course_id, score.score from student join score on student.id = score.student_id and student.name ='许仙';+--------+-----------+-------+| name | course_id | score |+--------+-----------+-------+| 许仙 |1|67.0|| 许仙 |3|23.0|| 许仙 |5|56.0|| 许仙 |6|72.0|+--------+-----------+-------+4rowsinset(0.00 sec)
既然存在了 from 多个表，那为什么还要引入 join on 出来呢？join on 默认情况下的行为和 from 多个表是一致的，但是 join on 还能延伸出一些其他的用法，功能比 from 多个表更广泛了
多张表的情况下：
select student.name score.course_id, score.score from student join score join classes;
join 多个表之后，最后来个 on 同意筛选条件就可以了
②：查询所有同学的总成绩，及同学的个人信息：
在我们上文已经写出了 160 行代码过滤后的每个人总成绩，在这里针对行和行之间的计算，就需要使用聚合查询，不能说一提到总成绩就想到表达式查询，要根据实际的表结构，见招拆招。
mysql>select*from student, score where student.id = score.student_id groupby id;+----+-------+-----------------+-----------------+------------+-------+------------+-----------+| id | sn | name | qq_mail | classes_id | score | student_id | course_id |+----+-------+-----------------+-----------------+------------+-------+------------+-----------+|1|09982| 黑旋风李逵 | [email protected]|1|70.5|1|1||2|00835| 菩提老祖 |NULL|1|60.0|2|1||3|00391| 白素贞 |NULL|1|33.0|3|1||4|00031| 许仙 | [email protected]|1|67.0|4|1||5|00054| 不想毕业 |NULL|1|81.0|5|1||6|51234| 好好说话 | [email protected]|2|56.0|6|2||7|83223| tellme |NULL|2|80.0|7|2|+----+-------+-----------------+-----------------+------------+-------+------------+-----------+7rowsinset(0.00 sec)
在不使用聚合函数的情况下，后面这部分显示出来的结果，相当于每个分组中的第一条记录
总成绩：
mysql>select student.name,sum(score.score)from student, score where student.id = score.student_id groupby id;+-----------------+------------------+| name |sum(score.score)|+-----------------+------------------+| 黑旋风李逵 |300.0|| 菩提老祖 |119.5|| 白素贞 |200.0|| 许仙 |218.0|| 不想毕业 |118.0|| 好好说话 |178.0|| tellme |172.0|+-----------------+------------------+7rowsinset(0.00 sec)
还可以指定别名
mysql>select student.name,sum(score.score)as total from student, score where student.id = score.student_id groupby id;+-----------------+-------+| name | total |+-----------------+-------+| 黑旋风李逵 |300.0|| 菩提老祖 |119.5|| 白素贞 |200.0|| 许仙 |218.0|| 不想毕业 |118.0|| 好好说话 |178.0|| tellme |172.0|+-----------------+-------+7rowsinset(0.00 sec)
③：查询每个同学姓名，课程名，分数
mysql>select*from student, course, score where student.id = score.student_id and course.id = score.course_id;+----+-------+-----------------+-----------------+------------+----+--------------------+-------+------------+-----------+| id | sn | name | qq_mail | classes_id | id | name | score | student_id | course_id |+----+-------+-----------------+-----------------+------------+----+--------------------+-------+------------+-----------+|1|09982| 黑旋风李逵 | [email protected]|1|1| Java |70.5|1|1||1|09982| 黑旋风李逵 | [email protected]|1|3| 计算机原理 |98.5|1|3||1|09982| 黑旋风李逵 | [email protected]|1|5| 高阶数学 |33.0|1|5||1|09982| 黑旋风李逵 | [email protected]|1|6| 英文 |98.0|1|6||2|00835| 菩提老祖 |NULL|1|1| Java |60.0|2|1||2|00835| 菩提老祖 |NULL|1|5| 高阶数学 |59.5|2|5||3|00391| 白素贞 |NULL|1|1| Java |33.0|3|1||3|00391| 白素贞 |NULL|1|3| 计算机原理 |68.0|3|3||3|00391| 白素贞 |NULL|1|5| 高阶数学 |99.0|3|5||4|00031| 许仙 | [email protected]|1|1| Java |67.0|4|1||4|00031| 许仙 | [email protected]|1|3| 计算机原理 |23.0|4|3||4|00031| 许仙 | [email protected]|1|5| 高阶数学 |56.0|4|5||4|00031| 许仙 | [email protected]|1|6| 英文 |72.0|4|6||5|00054| 不想毕业 |NULL|1|1| Java |81.0|5|1||5|00054| 不想毕业 |NULL|1|5| 高阶数学 |37.0|5|5||6|51234| 好好说话 | [email protected]|2|2| 中国传统文化 |56.0|6|2||6|51234| 好好说话 | [email protected]|2|4| 语文 |43.0|6|4||6|51234| 好好说话 | [email protected]|2|6| 英文 |79.0|6|6||7|83223| tellme |NULL|2|2| 中国传统文化 |80.0|7|2||7|83223| tellme |NULL|2|6| 英文 |92.0|7|6|+----+-------+-----------------+-----------------+------------+----+--------------------+-------+------------+-----------+20rowsinset(0.00 sec)
mysql>select student.name, course.name, score.score from student, course, score where student.id = score.student_id and course.id = score.course_id;+-----------------+--------------------+-------+| name | name | score |+-----------------+--------------------+-------+| 黑旋风李逵 | Java |70.5|| 黑旋风李逵 | 计算机原理 |98.5|| 黑旋风李逵 | 高阶数学 |33.0|| 黑旋风李逵 | 英文 |98.0|| 菩提老祖 | Java |60.0|| 菩提老祖 | 高阶数学 |59.5|| 白素贞 | Java |33.0|| 白素贞 | 计算机原理 |68.0|| 白素贞 | 高阶数学 |99.0|| 许仙 | Java |67.0|| 许仙 | 计算机原理 |23.0|| 许仙 | 高阶数学 |56.0|| 许仙 | 英文 |72.0|| 不想毕业 | Java |81.0|| 不想毕业 | 高阶数学 |37.0|| 好好说话 | 中国传统文化 |56.0|| 好好说话 | 语文 |43.0|| 好好说话 | 英文 |79.0|| tellme | 中国传统文化 |80.0|| tellme | 英文 |92.0|+-----------------+--------------------+-------+20rowsinset(0.00 sec)
🚩4.4 外连接
- select 列名 from 表1 left join 表2; 左外连接
- select 列名 from 表1 right join 表2; 右外连接
如果两张表里的数据是对应的，此时外连接和内连接看起来没区别，如果数据不对应，外连接和内连接的区别就明显了
制作一个表格：
mysql>createtable student2(id int, name varchar(20));
Query OK,0rows affected (0.01 sec)
mysql>createtable score2(id int, score int);
Query OK,0rows affected (0.01 sec)
mysql>insertinto student2 values(1,'张三'),(2,'李四'),(3,'王五');
Query OK,3rows affected (0.00 sec)
Records: 3 Duplicates: 0Warnings: 0
mysql>insertinto score2 values(1,90),(2,80),(3,70);
Query OK,3rows affected (0.00 sec)
Records: 3 Duplicates: 0Warnings: 0
mysql>select*from student2;+------+--------+| id | name |+------+--------+|1| 张三 ||2| 李四 ||3| 王五 |+------+--------+3rowsinset(0.00 sec)
mysql>select*from score2;+------+-------+| id | score |+------+-------+|1|90||2|80||3|70|+------+-------+3rowsinset(0.00 sec)
mysql>select name, score from student2 join score2 on student2.id = score2.id;+--------+-------+| name | score |+--------+-------+| 张三 |90|| 李四 |80|| 王五 |70|+--------+-------+3rowsinset(0.00 sec)
左右对比：（如果两个表数据记录都是一一对应的，此时内连接和外连接没有区别）
mysql>select name, score from student2 leftjoin score2 on student2.id = score2.id;+--------+-------+| name | score |+--------+-------+| 张三 |90|| 李四 |80|| 王五 |70|+--------+-------+3rowsinset(0.00 sec)
mysql>select name, score from student2 rightjoin score2 on student2.id = score2.id;+--------+-------+| name | score |+--------+-------+| 张三 |90|| 李四 |80|| 王五 |70|+--------+-------+3rowsinset(0.00 sec)
对成绩序号进行修改，出现 id 4 无人对应，王五没有对应的成绩
mysql>update score2 set id =4where score =70;
Query OK,1row affected (0.00 sec)Rowsmatched: 1 Changed: 1Warnings: 0
mysql>select*from score2;+------+-------+| id | score |+------+-------+|1|90||2|80||4|70|+------+-------+3rowsinset(0.00 sec)
mysql>select*from student2;+------+--------+| id | name |+------+--------+|1| 张三 ||2| 李四 ||3| 王五 |+------+--------+3rowsinset(0.00 sec)
从学生表的角度看，王五同学没有分数
从分数表的角度看，4 号同学没有身份信息
内连接产生的结果，是两张表都包含的数据
mysql>select name, score from student2 join score2 on student2.id = score2.id;+--------+-------+| name | score |+--------+-------+| 张三 |90|| 李四 |80|+--------+-------+2rowsinset(0.00 sec)
左外连接，就是以 join 左侧的表为主，保证左侧的表的每个记录都能体现在结果中，如果左侧的记录在右侧表中不存在，则填充 NULL。
mysql>select name, score from student2 leftjoin score2 on student2.id = score2.id;+--------+-------+| name | score |+--------+-------+| 张三 |90|| 李四 |80|| 王五 |NULL|+--------+-------+3rowsinset(0.00 sec)
右外连接，就是以 join 右侧的表为主，保证右侧的表每个记录都能体现在结果中，如果右侧的记录在左侧不存在，则填充 NULL。
mysql>select name, score from student2 rightjoin score2 on student2.id = score2.id;+--------+-------+| name | score |+--------+-------+| 张三 |90|| 李四 |80||NULL|70|+--------+-------+3rowsinset(0.00 sec)
综上来看，是否存在一种外连接，可以把整个全集都获取到呢？
答案是有的，全外连接，但是 MySQL 不支持全外连接
🏳️🌈4.5 自连接
- 自己和自己进行连接，同一张表和自己进行笛卡尔积
SQL 里面指定条件筛选，都是按照列和列之间进行筛选，难以进行行和行之间筛选，自连接操作能把行转化为列
- 显示所有 " 计算机原理 " 成绩比 " Java " 成绩高的成绩信息
mysql>select*from course;+----+--------------------+| id | name |+----+--------------------+|1| Java ||2| 中国传统文化 ||3| 计算机原理 ||4| 语文 ||5| 高阶数学 ||6| 英文 |+----+--------------------+6rowsinset(0.00 sec)
course_id 1 和 course_id 3的比较
mysql>select*from score;+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|70.5|1|1||98.5|1|3||33.0|1|5||98.0|1|6||60.0|2|1||59.5|2|5||33.0|3|1||68.0|3|3||99.0|3|5||67.0|4|1||23.0|4|3||56.0|4|5||72.0|4|6||81.0|5|1||37.0|5|5||56.0|6|2||43.0|6|4||79.0|6|6||80.0|7|2||92.0|7|6|+-------+------------+-----------+20rowsinset(0.00 sec)
SQL 中无法实现行和行之间的比较！行和行不能直接比较，转换成列和列，自连接就可以做到这一点
mysql>select*from score,score;
ERROR 1066(42000): Notuniquetable/alias: 'score'
可以看到出现了错误，因为自连接的情况下，必须给表起别名了
但是此处笛卡尔积数据太多了，需要过滤掉不合理的数据，保留关注的合法数据
我们的需求是找到哪个同学的 3 号课程比 1 号课程高
当学生 id 对齐之后，发现课程 id 仍在排列组合
mysql>select*from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id =3and s2.course_id =1;+-------+------------+-----------+-------+------------+-----------+| score | student_id | course_id | score | student_id | course_id |+-------+------------+-----------+-------+------------+-----------+|98.5|1|3|70.5|1|1||68.0|3|3|33.0|3|1||23.0|4|3|67.0|4|1|+-------+------------+-----------+-------+------------+-----------+3rowsinset(0.00 sec)
此时表里剩下的数据就是左侧都为 3 的课程分数，右侧就都是为 1 的课程分数
mysql>select*from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id =3and s2.course_id =1and s1.score > s2.score;+-------+------------+-----------+-------+------------+-----------+| score | student_id | course_id | score | student_id | course_id |+-------+------------+-----------+-------+------------+-----------+|98.5|1|3|70.5|1|1||68.0|3|3|33.0|3|1|+-------+------------+-----------+-------+------------+-----------+2rowsinset(0.00 sec)
加个课程 3 大于课程 1 的条件
mysql>select s1.student_id from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id =3and s2.course_id =1and s1.score > s2.score;+------------+| student_id |+------------+|1||3|+------------+2rowsinset(0.00 sec)
更加精准把 id 列单独列出来，结果就是学生 1 和 学生 3 的成绩数据符合
🏴☠️4.6 子查询
- 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询：返回一行记录的子查询
查询与“不想毕业” 同学的同班同学：
- 先根据名字获取到班级 id
- 根据班级 id 查询 id 匹配同学
mysql>select*from student;+----+-------+-----------------+------------------+------------+| id | sn | name | qq_mail | classes_id |+----+-------+-----------------+------------------+------------+|1|09982| 黑旋风李逵 | [email protected]|1||2|00835| 菩提老祖 |NULL|1||3|00391| 白素贞 |NULL|1||4|00031| 许仙 | [email protected]|1||5|00054| 不想毕业 |NULL|1||6|51234| 好好说话 | [email protected]|2||7|83223| tellme |NULL|2||8|09527| 老外学中文 | [email protected]|2|+----+-------+-----------------+------------------+------------+8rowsinset(0.00 sec)
mysql>select classes_id from student where name ='不想毕业';+------------+| classes_id |+------------+|1|+------------+1rowinset(0.00 sec)
mysql>select name from student where classes_id =1;+-----------------+| name |+-----------------+| 黑旋风李逵 || 菩提老祖 || 白素贞 || 许仙 || 不想毕业 |+-----------------+5rowsinset(0.00 sec)
把这俩 SQL 一合并就成了一个子查询了
mysql>select name from student where classes_id =(select classes_id from student where name ='不想毕业');+-----------------+| name |+-----------------+| 黑旋风李逵 || 菩提老祖 || 白素贞 || 许仙 || 不想毕业 |+-----------------+5rowsinset(0.00 sec)
此处的子查询，可以任意级别的嵌套，N 个 SQL 组合成了一个巨无霸 SQL
多行子查询：返回多行记录的子查询
查询“语文”或“英文”课程的成绩信息:
- 先根据课程名，直到课程的 id
- 拿着课程的 id 去分数表里进行查询
mysql>select*from course;+----+--------------------+| id | name |+----+--------------------+|1| Java ||2| 中国传统文化 ||3| 计算机原理 ||4| 语文 ||5| 高阶数学 ||6| 英文 |+----+--------------------+6rowsinset(0.00 sec)
mysql>select id from course where name ='语文'or name ='英文';+----+| id |+----+|4||6|+----+2rowsinset(0.00 sec)
mysql>select*from score where course_id in(select id from course where name ='语文'or name ='英文');+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|98.0|1|6||72.0|4|6||43.0|6|4||79.0|6|6||92.0|7|6|+-------+------------+-----------+5rowsinset(0.00 sec)
🏳️⚧️4.7 合并查询
- 把两个查询结果，结果集合，合并到一起
可以使用集合操作符 union，union all。使用 UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致。
- union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
查询id小于3，或者名字为“英文”的课程：
mysql>select*from course where id <3or name ='英文';+----+--------------------+| id | name |+----+--------------------+|1| Java ||2| 中国传统文化 ||6| 英文 |+----+--------------------+3rowsinset(0.00 sec)
mysql>select*from course where id <3unionselect*from course where name ='英文';+----+--------------------+| id | name |+----+--------------------+|1| Java ||2| 中国传统文化 ||6| 英文 |+----+--------------------+3rowsinset(0.00 sec)
- union all
和 union 的唯一区别就是 union 操作如果有重复记录，会去重，union all 不会去重
SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit
本文转载自: https://blog.csdn.net/m0_67660672/article/details/129092593
版权归原作者 bit me 所有， 如有侵权，请联系我们删除。
版权归原作者 bit me 所有， 如有侵权，请联系我们删除。