✨个人主页:bit me👇
✨当前专栏:MySQL数据库👇
✨算法专栏:算法基础👇
✨每日一语:悟已往之不谏,知来者之可追。实迷途其未远,觉今是而昨非。
约束的本质就是让我们及时发现数据中的错误,更好的保证数据的正确性
约束类型:
①:NOT NULL
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into student values(NULL, NULL); Query OK, 1 row affected (0.00 sec)
加上 NOT NULL 约束之后,就不再允许插入空值
mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> create table student (id int not null, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
再尝试插入空值就会报错!
mysql> insert into student values (NULL, NULL); ERROR 1048 (23000): Column 'id' cannot be null
②:UNIQUE 唯一性
mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> create table student (id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> insert into student values(1, '张三'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(1, '张三'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(1, '张三'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 1 | 张三 | | 1 | 张三 | +------+--------+ 3 rows in set (0.00 sec)
没有约束的表和有约束的表对比
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> create table student (id int unique, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
当我们想再次输入同样的数据的时候发现报错
mysql> insert into student values(1, '张三'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(1, '张三'); ERROR 1062 (23000): Duplicate entry '1' for key 'id'
Duplicate --> 重复, entry --> 条目(不是入口) 触发了 UNIQUE 约束
③:DEFAULT
默认的默认值是NULL,可以通过 default 约束来修改这里的默认值的取值
mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> create table student (id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
对表进行指定列插入的时候,会涉及到默认值的情况的
mysql> insert into student (id) values (1); Query OK, 1 row affected (0.00 sec)
指定了 id 这一列插入,此时的 name 就是按照默认值来走的
mysql> select * from student; +------+------+ | id | name | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec)
通过 default 约束来修改默认值
mysql> drop table student; Query OK, 0 rows affected (0.00 sec) mysql> create table student (id int default 0, name varchar(20) default '未命名'); Query OK, 0 rows affected (0.01 sec) mysql> desc student; +-------+-------------+------+-----+-----------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+-----------+-------+ | id | int(11) | YES | | 0 | | | name | varchar(20) | YES | | 未命名 | | +-------+-------------+------+-----+-----------+-------+ 2 rows in set (0.00 sec) mysql> insert into student (id) values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+-----------+ | id | name | +------+-----------+ | 1 | 未命名 | +------+-----------+ 1 row in set (0.00 sec) mysql> insert into student (name) values ('张三'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+-----------+ | id | name | +------+-----------+ | 1 | 未命名 | | 0 | 张三 | +------+-----------+ 2 rows in set (0.00 sec)
④:PRIMARY KEY --> 主键
注:
- 不能为空,相当于 NOT NULL
- 不能重复,相当于UNIQUE
- 一个表里只能有一个主键
mysql> drop table student; Query OK, 0 rows affected (0.00 sec) mysql> create table student (id int primary key,name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
添加元素:
mysql> insert into student values(null, null); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into student values(1,'张三'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(1,'张三'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
主键不能为空,主键的值也不能重复
由于主键必须要填,还不能重复,MySQL为了方便大家填写主键,内置了一个功能 ”自增主键“ ,帮助我们自动生成主键的值,就不用程序猿自己来保证了
mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> create table student (id int primary key auto_increment, name varchar(20)); Query OK, 0 rows 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 | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
当前 id 是自增主键,不同于主键,可以使用 null
mysql> insert into student values (null,'张三'); Query OK, 1 row affected (0.00 sec)
- 如果写的是具体数值,就是手动指定 id
- 如果写的是 null ,就是让 MySQL 按照自增主键自动生成
mysql> select * from student; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | +----+--------+ 1 row in set (0.00 sec) mysql> insert into student values (null,'张三'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values (null,'张三'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values (null,'张三'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | | 2 | 张三 | | 3 | 张三 | | 4 | 张三 | +----+--------+ 4 rows in set (0.00 sec)
中间插入手动增加之后自增从当前的数字自增
mysql> insert into student values (100,'张三'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +-----+--------+ | id | name | +-----+--------+ | 1 | 张三 | | 2 | 张三 | | 3 | 张三 | | 4 | 张三 | | 100 | 张三 | +-----+--------+ 5 rows in set (0.00 sec) mysql> insert into student values (null,'张三'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +-----+--------+ | id | name | +-----+--------+ | 1 | 张三 | | 2 | 张三 | | 3 | 张三 | | 4 | 张三 | | 100 | 张三 | | 101 | 张三 | +-----+--------+ 6 rows in set (0.00 sec)
- MySQL 想要自增,必须要能够记录下来当前 id 已经到哪了,还要保证自增后,得是不能重复的。
- MySQL里简单粗暴的做法,直接就是记录当前 自增主键 里的最大值,这就能保证自增速度很快,并且一定是不重复的
- 可能会觉得有数据库浪费了,但是数据库一般而言是很大的,一般不考虑这个浪费
自增主键,主要就是用来生成一个唯一的 id ,来保证不重复,如果数据库是分布式部署,这个时候自增主键就要带来问题。
在一个MySQL集群里,有许多的MySQL节点(多个节点上的数据放在一起才是一个完整的数据集合),MySQL生成自增主键的时候可以保证在自己这个节点上生成的 id 是唯一的,但是无法保证这个 id 在其他节点上也是唯一的。
生成一个 ID 这样的需求,是客观存在的,但是 MySQL 自增主键,已经难以满足要求了
一个典型的解决方法:在生成 id 的时候,让这些节点相互协商一下,彼此了解了对方的情况之后,就能生成唯一的 id 了,但是代价有点大。为了轻量,高效,又出了另外一个方法:唯一 id = 时间戳(ms) + 机房编号/主机编号 + 随机因子 (这里的 + 不是算术运算 而是字符串拼接),进入数据库的数据有先有后形成时间戳,并且会分摊到不同的主机上,就算是一个主机,又会生成随机数的!(也有极端情况三种一模一样,理论上存在但是工程上是忽略不计的!)
⑤:FOREIGN KEY
举例:
- 班级表是负责约束的一方,称为父表
- 学生表是被约束的一方,称为子表
mysql> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> create table class (classId int primary key,className varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> create table student (studentId int primary key, name varchar(20), classId int, foreign key (classId) references class (classId)); Query OK, 0 rows 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 | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
- 第一句为父表,第二句为子表
- 先把所有的列都定义完,逗号,然后再写外键约束
在父表为空的情况下,直接尝试往子表插入,就会报错!!!
mysql> insert into student values(1,'张三',1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
在父表里面添加一些数据:
mysql> insert into class values (1,'java101'); Query OK, 1 row affected (0.00 sec) mysql> insert into class values (2,'java102'); Query OK, 1 row affected (0.00 sec) mysql> insert into class values (3,'java103'); Query OK, 1 row affected (0.00 sec) mysql> select * from class; +---------+-----------+ | classId | className | +---------+-----------+ | 1 | java101 | | 2 | java102 | | 3 | java103 | +---------+-----------+ 3 rows in set (0.00 sec)
往子表插入数据
mysql> insert into student values(1,'张三',4); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
依旧报错,插入子表的数据没有在父表的 classId 中,同样也是报的刚才的错误
在外键的约束下,每次你插入 / 修改操作,都会先触发在父表中的查询,父表中存在才能插入 / 修改成功,否则就会失败。
mysql> insert into student values(1,'张三',1); Query OK, 1 row affected (0.00 sec)
每次插入都要先查询会拖慢执行效率,但是也没有特别拖慢
如果查询操作触发了遍历表,低效的;如果触发了索引,相对于快不少
建立外键约束的时候,MySQL 就要求,引用的父表的列,必须是主键或者UNIQUE(自带索引,查询速度就会快一些)
父表对子表产生了限制,但是反过来子表也会对父表产生限制
父表对子表的限制是不能随意插入 / 修改 子表对于父表的限制就是不能随意修改 / 删除
mysql> select * from student; +-----------+--------+---------+ | studentId | name | classId | +-----------+--------+---------+ | 1 | 张三 | 1 | +-----------+--------+---------+ 1 row in set (0.00 sec) mysql> delete from class where classId = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
由于在子表中,引入了 classId = 1 的记录,尝试删除父表中的对应记录,发现,这个就难以删除
⑥: CHECK(了解)
check (sex ='男' or sex='女')
例如限制性别这一列只能是 男 或 女
面试官:你的项目里的数据库是如何设计的?
- 其实是让你回答,你的数据库里有几个表,每个表是干啥的,以及表里有哪些字段
那我们该如何设计它呢?
我们需要明确需求场景,提取出需求中的 " 实体 " ,实体可以认为是关键性名词,一般来说,每个实体都会分配一个表来进行表示,除了实体之外,还需要理清楚,实体和实体之间的关系。
1. 一对一
2. 一对多
3. 多对多
4. 没关系
例如一个学校的教务系统:
有学生,账号,课程,班级
- 一个学生只能有一个账号,一个账号只能分配给一个学生(一对一)
- 一个同学只属于一个班级,一个班级包含多个学生(一对多)
- 一个学生可以选择多门课程,一个课程可以包含多个学生(多对多)
不同的关系,在设计表的时候有不同的套路,对应的设计表的套路
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 | +---------+-----------+ 3 rows in set (0.00 sec) mysql> select * from student; +-----------+--------+---------+ | studentId | name | classId | +-----------+--------+---------+ | 1 | 张三 | 1 | +-----------+--------+---------+ 1 row in set (0.00 sec)
例如这俩张表里都有 classId 但是详细讲就是 class.classId 和 student.classId
insert into 表名1 select 列名 from 表名2;
先执行查询操作,查询出来的结果插入到另外一个表里,保证查询结果的临时表的列要和插入的表的列匹配!!!
- 要求从表名 2 中查询出来的结果的列数和类型 和表名 1 匹配,不要求列名匹配
mysql> create table student (id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> insert into student values (1,'张三'),(2,'李四'),(3,'王五'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table student2 (id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> select * from student; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | +------+--------+ 3 rows in set (0.00 sec) mysql> select * from student2; Empty set (0.00 sec) mysql> insert into student2 select * from student; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student2; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | +------+--------+ 3 rows in set (0.00 sec)
聚合查询:
通过行和行之间进行的运算处理
表达式查询是列和列之间
函数 | 说明 |
---|---|
COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的 平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的 最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的 最小值,不是数字没有意义 |
这些都属于 SQL 内置函数,SQL 作为一个编程语言,也是内置了一些库函数供我们来使用的
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 | +------+-----------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select count(*) from exam_result; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> select count(name) from exam_result; +-------------+ | count(name) | +-------------+ | 7 | +-------------+ 1 row in set (0.00 sec)
那么其中使用 * 和 name(列名) 有什么区别呢?
mysql> insert into exam_result values(null, null, null, null, null); Query OK, 1 row 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 | +------+-----------+---------+------+---------+ 8 rows in set (0.00 sec) mysql> select count(*) from exam_result; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> select count(name) from exam_result; +-------------+ | count(name) | +-------------+ | 7 | +-------------+ 1 row in set (0.00 sec)
可以看到我们再添加一个全为空的信息,* 把 NULL 值也记录到行数中了,name(列名) 对于 NULL 不会计数。
注:
- count(name) 中间不能带有空格,否则就会出错,SQL 不能被正常解析
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 | +------+-----------+---------+------+---------+ 8 rows in set (0.01 sec) mysql> select sum(math) from exam_result; +-----------+ | sum(math) | +-----------+ | 581.5 | +-----------+ 1 row in set (0.00 sec)
在我们之前学过,NULL 和其他数字进行算数运算,结果还是 NULL,在这里可以得出结论SUM函数中 NULL 没有参加运算
mysql> select sum(english) from exam_result where english < 60; +--------------+ | sum(english) | +--------------+ | 131.0 | +--------------+ 1 row in set (0.00 sec) mysql> select sum(english) from exam_result; +--------------+ | sum(english) | +--------------+ | 443.5 | +--------------+ 1 row in set (0.00 sec)
对于带有条件的聚合查询,先会按照条件进行筛选,筛选后得到的结果进行聚合
mysql> select sum(name) from exam_result; +-----------+ | sum(name) | +-----------+ | 0 | +-----------+ 1 row in set, 7 warnings (0.00 sec)
名字是字符串无法求和,所以 sum 函数只能对数字进行求和
mysql> select avg(math) from exam_result; +-----------+ | avg(math) | +-----------+ | 83.07143 | +-----------+ 1 row in set (0.00 sec)
此处通过计算也可以得出平均值的计算是不会计入 NULL 值的个数的
mysql> select max(math) from exam_result; +-----------+ | max(math) | +-----------+ | 98.5 | +-----------+ 1 row in set (0.00 sec) mysql> select min(math) from exam_result; +-----------+ | min(math) | +-----------+ | 65.0 | +-----------+ 1 row in set (0.00 sec)
group by 列
建一张表:
mysql> create table student (id int, name varchar(20), gender varchar(20), score int); Query OK, 0 rows affected (0.01 sec) mysql> insert into student values (1, '张三', '男', 95); Query OK, 1 row affected (0.00 sec) mysql> insert into student values (2, '李四', '女', 75); Query OK, 1 row affected (0.00 sec) mysql> insert into student values (3, '王五', '男', 85); Query OK, 1 row affected (0.00 sec) mysql> insert into student values (4, '赵六', '女', 65); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+--------+--------+-------+ | id | name | gender | score | +------+--------+--------+-------+ | 1 | 张三 | 男 | 95 | | 2 | 李四 | 女 | 75 | | 3 | 王五 | 男 | 85 | | 4 | 赵六 | 女 | 65 | +------+--------+--------+-------+ 4 rows in set (0.00 sec)
需要统计,男生和女生分别各自的最高分,平均分,最低分
mysql> select gender, max(score), min(score), avg(score) from student group by gender; +--------+------------+------------+------------+ | gender | max(score) | min(score) | avg(score) | +--------+------------+------------+------------+ | 女 | 75 | 65 | 70.0000 | | 男 | 95 | 85 | 90.0000 | +--------+------------+------------+------------+ 2 rows in set (0.00 sec)
分组规则,是把记录的值相同的行作为一组
在进行聚合查询的时候,也能指定条件筛选
- 1. 在聚合之前,进行筛选,针对筛选后的结果,再聚合。(where 子句)
- 2. 在聚合之后,进行筛选。(having 子句)
mysql> select gender, avg(score) from student where name != '赵六' group by gender; +--------+------------+ | gender | avg(score) | +--------+------------+ | 女 | 75.0000 | | 男 | 90.0000 | +--------+------------+ 2 rows in set (0.00 sec)
mysql> select gender, avg(score) from student group by gender having avg(score) > 80; +--------+------------+ | gender | avg(score) | +--------+------------+ | 男 | 90.0000 | +--------+------------+ 1 row in set (0.00 sec)
mysql> select gender, avg(score) from student where name != '赵六' group by gender having avg(score) > 80; +--------+------------+ | gender | avg(score) | +--------+------------+ | 男 | 90.0000 | +--------+------------+ 1 row in set (0.00 sec)
聚合查询的执行过程:先按照 where 条件进行筛选记录,把筛选出来的结果按照 group by 来分组,分组之后按照 having 再进行筛选,最后再按照制定列中调用的聚合函数来显示计算结果。
联合查询
select * from 表1, 表2;
联合查询中的核心概念:笛卡尔积。
笛卡尔积就是把这两个表中的所有记录,进行排列组合,穷举出所有的可能情况
排列组合之后的结果就是笛卡尔积。笛卡尔积的列数就是原来俩张表的列数之和,笛卡尔积的行数就是原来俩张表的行数之和。
针对多个表进行联合查询,本质上就是先针对多个表进行笛卡尔积运算。万一原来的表就很大,再进行多个表的笛卡尔积,就会得到一个更复杂的表,这个过程就会很低效,因此,在实际开发中进行多表查询,一定要克制。
笛卡尔积中包含了大量无效数据,指定了合理的过滤条件,把有效的数据挑出来,这个时候就得到了一个非常有用的数据表,这个过程就是 " 联合查询 " 的过程。
多表查询的一般步骤:
- 1. 先根据需求理清楚说想要的数据都在哪些表中
- 2. 【核心操作】先针对多个表进行笛卡尔积
- 3. 根据连接条件,筛选出合法数据,过滤掉非法数据
- 4. 进一步增加条件,根据需求做更精细的筛选
- 5. 去掉不必要的列,保留最关注的信息
此处创建四个表:
mysql> create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20), classes_id int); Query OK, 0 rows affected (0.01 sec) mysql> create table course (id int primary key auto_increment, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> create table score (score decimal(3,1),student_id int, course_id int); Query OK, 0 rows affected (0.01 sec)
填入数据:
mysql> insert into classes(name, `desc`) values -> ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), -> ('中文系2019级3班','学习了中国传统文学'), -> ('自动化2019级5班','学习了机械自动化'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into student(sn, name, qq_mail, classes_id) values -> ('09982','黑旋风李逵','xuanfeng@qq.com',1), -> ('00835','菩提老祖',null,1), -> ('00391','白素贞',null,1), -> ('00031','许仙','xuxian@qq.com',1), -> ('00054','不想毕业',null,1), -> ('51234','好好说话','say@qq.com',2), -> ('83223','tellme',null,2), -> ('09527','老外学中文','foreigner@qq.com',2); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into course(name) values -> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into 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, 20 rows affected (0.00 sec) Records: 20 Duplicates: 0 Warnings: 0
打印四个表:
mysql> select * from student; +----+-------+-----------------+------------------+------------+ | id | sn | name | qq_mail | classes_id | +----+-------+-----------------+------------------+------------+ | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 2 | 00835 | 菩提老祖 | NULL | 1 | | 3 | 00391 | 白素贞 | NULL | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | | 5 | 00054 | 不想毕业 | NULL | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | | 7 | 83223 | tellme | NULL | 2 | | 8 | 09527 | 老外学中文 | foreigner@qq.com | 2 | +----+-------+-----------------+------------------+------------+ 8 rows in set (0.00 sec) mysql> select * from classes; +----+-------------------------+-------------------------------------------------------------------+ | id | name | desc | +----+-------------------------+-------------------------------------------------------------------+ | 1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 | | 2 | 中文系2019级3班 | 学习了中国传统文学 | | 3 | 自动化2019级5班 | 学习了机械自动化 | +----+-------------------------+-------------------------------------------------------------------+ 3 rows in set (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 | +-------+------------+-----------+ 20 rows in set (0.00 sec) mysql> select * from course; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中国传统文化 | | 3 | 计算机原理 | | 4 | 语文 | | 5 | 高阶数学 | | 6 | 英文 | +----+--------------------+ 6 rows in set (0.00 sec)
- 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 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 70.5 | 1 | 1 | | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 98.5 | 1 | 3 | | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 33.0 | 1 | 5 | | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 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 | 许仙 | xuxian@qq.com | 1 | 67.0 | 4 | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 23.0 | 4 | 3 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 56.0 | 4 | 5 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 72.0 | 4 | 6 | | 5 | 00054 | 不想毕业 | NULL | 1 | 81.0 | 5 | 1 | | 5 | 00054 | 不想毕业 | NULL | 1 | 37.0 | 5 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 56.0 | 6 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 43.0 | 6 | 4 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 92.0 | 7 | 6 | +----+-------+-----------------+-----------------+------------+-------+------------+-----------+ 20 rows in set (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 | 许仙 | xuxian@qq.com | 1 | 67.0 | 4 | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 23.0 | 4 | 3 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 56.0 | 4 | 5 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 72.0 | 4 | 6 | +----+-------+--------+---------------+------------+-------+------------+-----------+ 4 rows in set (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 | +--------+-----------+-------+ 4 rows in set (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 | 许仙 | xuxian@qq.com | 1 | 67.0 | 4 | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 23.0 | 4 | 3 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 56.0 | 4 | 5 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 72.0 | 4 | 6 | +----+-------+--------+---------------+------------+-------+------------+-----------+ 4 rows in set (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 | +--------+-----------+-------+ 4 rows in set (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 group by id; +----+-------+-----------------+-----------------+------------+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | score | student_id | course_id | +----+-------+-----------------+-----------------+------------+-------+------------+-----------+ | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 70.5 | 1 | 1 | | 2 | 00835 | 菩提老祖 | NULL | 1 | 60.0 | 2 | 1 | | 3 | 00391 | 白素贞 | NULL | 1 | 33.0 | 3 | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 67.0 | 4 | 1 | | 5 | 00054 | 不想毕业 | NULL | 1 | 81.0 | 5 | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 56.0 | 6 | 2 | | 7 | 83223 | tellme | NULL | 2 | 80.0 | 7 | 2 | +----+-------+-----------------+-----------------+------------+-------+------------+-----------+ 7 rows in set (0.00 sec)
在不使用聚合函数的情况下,后面这部分显示出来的结果,相当于每个分组中的第一条记录
总成绩:
mysql> select student.name, sum(score.score) from student, score where student.id = score.student_id group by id; +-----------------+------------------+ | name | sum(score.score) | +-----------------+------------------+ | 黑旋风李逵 | 300.0 | | 菩提老祖 | 119.5 | | 白素贞 | 200.0 | | 许仙 | 218.0 | | 不想毕业 | 118.0 | | 好好说话 | 178.0 | | tellme | 172.0 | +-----------------+------------------+ 7 rows in set (0.00 sec)
还可以指定别名
mysql> select student.name, sum(score.score) as total from student, score where student.id = score.student_id group by id; +-----------------+-------+ | name | total | +-----------------+-------+ | 黑旋风李逵 | 300.0 | | 菩提老祖 | 119.5 | | 白素贞 | 200.0 | | 许仙 | 218.0 | | 不想毕业 | 118.0 | | 好好说话 | 178.0 | | tellme | 172.0 | +-----------------+-------+ 7 rows in set (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 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | Java | 70.5 | 1 | 1 | | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 计算机原理 | 98.5 | 1 | 3 | | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 5 | 高阶数学 | 33.0 | 1 | 5 | | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 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 | 许仙 | xuxian@qq.com | 1 | 1 | Java | 67.0 | 4 | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 3 | 计算机原理 | 23.0 | 4 | 3 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 5 | 高阶数学 | 56.0 | 4 | 5 | | 4 | 00031 | 许仙 | xuxian@qq.com | 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 | 好好说话 | say@qq.com | 2 | 2 | 中国传统文化 | 56.0 | 6 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 4 | 语文 | 43.0 | 6 | 4 | | 6 | 51234 | 好好说话 | say@qq.com | 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 | +----+-------+-----------------+-----------------+------------+----+--------------------+-------+------------+-----------+ 20 rows in set (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 | +-----------------+--------------------+-------+ 20 rows in set (0.00 sec)
- select 列名 from 表1 left join 表2; 左外连接
- select 列名 from 表1 right join 表2; 右外连接
如果两张表里的数据是对应的,此时外连接和内连接看起来没区别,如果数据不对应,外连接和内连接的区别就明显了
制作一个表格:
mysql> create table student2(id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> create table score2(id int, score int); Query OK, 0 rows affected (0.01 sec) mysql> insert into student2 values(1, '张三'), (2, '李四'), (3, '王五'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into score2 values(1, 90), (2, 80), (3, 70); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student2; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | +------+--------+ 3 rows in set (0.00 sec) mysql> select * from score2; +------+-------+ | id | score | +------+-------+ | 1 | 90 | | 2 | 80 | | 3 | 70 | +------+-------+ 3 rows in set (0.00 sec) mysql> select name, score from student2 join score2 on student2.id = score2.id; +--------+-------+ | name | score | +--------+-------+ | 张三 | 90 | | 李四 | 80 | | 王五 | 70 | +--------+-------+ 3 rows in set (0.00 sec)
左右对比:(如果两个表数据记录都是一一对应的,此时内连接和外连接没有区别)
mysql> select name, score from student2 left join score2 on student2.id = score2.id; +--------+-------+ | name | score | +--------+-------+ | 张三 | 90 | | 李四 | 80 | | 王五 | 70 | +--------+-------+ 3 rows in set (0.00 sec) mysql> select name, score from student2 right join score2 on student2.id = score2.id; +--------+-------+ | name | score | +--------+-------+ | 张三 | 90 | | 李四 | 80 | | 王五 | 70 | +--------+-------+ 3 rows in set (0.00 sec)
对成绩序号进行修改,出现 id 4 无人对应,王五没有对应的成绩
mysql> update score2 set id = 4 where score = 70; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from score2; +------+-------+ | id | score | +------+-------+ | 1 | 90 | | 2 | 80 | | 4 | 70 | +------+-------+ 3 rows in set (0.00 sec) mysql> select * from student2; +------+--------+ | id | name | +------+--------+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | +------+--------+ 3 rows in set (0.00 sec)
从学生表的角度看,王五同学没有分数
从分数表的角度看,4 号同学没有身份信息
mysql> select name, score from student2 join score2 on student2.id = score2.id; +--------+-------+ | name | score | +--------+-------+ | 张三 | 90 | | 李四 | 80 | +--------+-------+ 2 rows in set (0.00 sec)
mysql> select name, score from student2 left join score2 on student2.id = score2.id; +--------+-------+ | name | score | +--------+-------+ | 张三 | 90 | | 李四 | 80 | | 王五 | NULL | +--------+-------+ 3 rows in set (0.00 sec)
mysql> select name, score from student2 right join score2 on student2.id = score2.id; +--------+-------+ | name | score | +--------+-------+ | 张三 | 90 | | 李四 | 80 | | NULL | 70 | +--------+-------+ 3 rows in set (0.00 sec)
综上来看,是否存在一种外连接,可以把整个全集都获取到呢?
答案是有的,全外连接,但是 MySQL 不支持全外连接
SQL 里面指定条件筛选,都是按照列和列之间进行筛选,难以进行行和行之间筛选,自连接操作能把行转化为列
mysql> select * from course; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中国传统文化 | | 3 | 计算机原理 | | 4 | 语文 | | 5 | 高阶数学 | | 6 | 英文 | +----+--------------------+ 6 rows in set (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 | +-------+------------+-----------+ 20 rows in set (0.00 sec)
SQL 中无法实现行和行之间的比较!行和行不能直接比较,转换成列和列,自连接就可以做到这一点
mysql> select * from score,score; ERROR 1066 (42000): Not unique table/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 = 3 and 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 | +-------+------------+-----------+-------+------------+-----------+ 3 rows in set (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 = 3 and s2.course_id = 1 and 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 | +-------+------------+-----------+-------+------------+-----------+ 2 rows in set (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 = 3 and s2.course_id = 1 and s1.score > s2.score; +------------+ | student_id | +------------+ | 1 | | 3 | +------------+ 2 rows in set (0.00 sec)
更加精准把 id 列单独列出来,结果就是学生 1 和 学生 3 的成绩数据符合
单行子查询:返回一行记录的子查询
查询与“不想毕业” 同学的同班同学:
- 先根据名字获取到班级 id
- 根据班级 id 查询 id 匹配同学
mysql> select * from student; +----+-------+-----------------+------------------+------------+ | id | sn | name | qq_mail | classes_id | +----+-------+-----------------+------------------+------------+ | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 2 | 00835 | 菩提老祖 | NULL | 1 | | 3 | 00391 | 白素贞 | NULL | 1 | | 4 | 00031 | 许仙 | xuxian@qq.com | 1 | | 5 | 00054 | 不想毕业 | NULL | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | | 7 | 83223 | tellme | NULL | 2 | | 8 | 09527 | 老外学中文 | foreigner@qq.com | 2 | +----+-------+-----------------+------------------+------------+ 8 rows in set (0.00 sec) mysql> select classes_id from student where name = '不想毕业'; +------------+ | classes_id | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> select name from student where classes_id = 1; +-----------------+ | name | +-----------------+ | 黑旋风李逵 | | 菩提老祖 | | 白素贞 | | 许仙 | | 不想毕业 | +-----------------+ 5 rows in set (0.00 sec)
把这俩 SQL 一合并就成了一个子查询了
mysql> select name from student where classes_id = (select classes_id from student where name = '不想毕业'); +-----------------+ | name | +-----------------+ | 黑旋风李逵 | | 菩提老祖 | | 白素贞 | | 许仙 | | 不想毕业 | +-----------------+ 5 rows in set (0.00 sec)
此处的子查询,可以任意级别的嵌套,N 个 SQL 组合成了一个巨无霸 SQL
多行子查询:返回多行记录的子查询
查询“语文”或“英文”课程的成绩信息:
- 先根据课程名,直到课程的 id
- 拿着课程的 id 去分数表里进行查询
mysql> select * from course; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中国传统文化 | | 3 | 计算机原理 | | 4 | 语文 | | 5 | 高阶数学 | | 6 | 英文 | +----+--------------------+ 6 rows in set (0.00 sec) mysql> select id from course where name = '语文' or name = '英文'; +----+ | id | +----+ | 4 | | 6 | +----+ 2 rows in set (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 | +-------+------------+-----------+ 5 rows in set (0.00 sec)
可以使用集合操作符 union,union all。使用 UNION 和 UNION ALL 时,前后查询的结果集中,字段需要一致。
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行
查询id小于3,或者名字为“英文”的课程:
mysql> select * from course where id < 3 or name = '英文'; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中国传统文化 | | 6 | 英文 | +----+--------------------+ 3 rows in set (0.00 sec) mysql> select * from course where id < 3 union select * from course where name = '英文'; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中国传统文化 | | 6 | 英文 | +----+--------------------+ 3 rows in set (0.00 sec)
和 union 的唯一区别就是 union 操作如果有重复记录,会去重,union all 不会去重
SQL查询中各个关键字的执行先后顺序: from > on> join > where > group by > with > having >select > distinct > order by > limit
上一篇:访问nginx显示未找到站点