MySQL表的增删改查（进阶）

1. 数据库的约束

约束就是让程序设计者定义一些对数据的限制规定,从而在我们对数据库进行修改/删除操作时按照这些规则进行效验,不通过就会报错,约束的本质就是让我们及时发现数据中的错误,更好的保证数据的准确性.

数据库完整性分为实体完整性、域完整性和参照完整性。实体完整性要求表中的主键字段不能为空且不能重复；域完整性要求表中数据都在有效范围内；参照完整性保证了相关联的表的数据一致性。约束是保证表中数据完整性和一致性的手段，分为主键约束、外键约束、检查约束、唯一约束、非空约束五种。不管哪种约束，体现在表中都可以有1列或多列

1.1 约束类型

1.1.1 NOT NULL - 非空约束

指示某列不能存储NULL值

案例:

我们先建立一个表不加任何约束条件,然后插入null数据:

然后加入约束条件;

再插入null数据就会报错.

1.1.2 UNIQUE - 唯一约束

保证某列的每行必须有唯一的值

案例:

在默认情况下,表是允许插入重复数据的:

这里的key就会帮助检查插入数据是否重复:

1.1.3 DEFAULT - 默认值约束

规定给列赋值时的默认值

案例:

当我们没有明确列的默认值时.在插入数据时,会插入默认的默认值.

指定默认值后:

1.1.4 PRIMARY KEY - 主键约束

NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识，有助于更容易更快速地找到表中的一个特定的记录

primary key 主键表示一条记录的身份标识,用来区分这条记录和其他记录的(比如身份证号码, 手机号,商品编号…)

• 单键 - 创建表的时候直接在字段上指定主键

• 复合主键 - 在所有字段之后，使用primary key(主键字段列表)来创建主键

• 删除主键 - alter table 表名 drop primary key;

• 自增主键 - 由于主键必须要填并且不能重复,所以自增主键可以帮助我们自动生成主键的值(默认从1开始累加).如果先前没有从1开始自增,MySQL想要自增必须记录当前id,还要保证下一次自增后的数据不重复,它的处理方式是直接记录当前自增列的最大值,这样就会保证不会重复且增加很快.

如果数据库是分布式部署(数据量大),这时候自增主键就会带来问题,在MySQL集群中有多个数据库,单独的MySQL自增主键时,可以保证在自己的库中id是唯一的,但是无法保证这个id在其他库中也是唯一,解决方案:唯一id = 时间戳(ms) + 机房编号/主机编号 + 随机因子 ;(这里的+为字符串拼接),原因:在向数据库中插入数据时,同一个时间戳之内,进入数据库的数据时有限的,使用时间戳,就可以区分出大量数据,然后又会分摊到不同主机上,再通过随机数,导致id基本上不会相同

它有以下要求:

1. 不能为null, 相当于NOT NULL

2. 不能重复, 相当于 UNIQUE

3. 一个表中只能有一个主键

1.1.5 FOREIGN KEY - 外键约束

保证一个表中的数据匹配另一个表中的值的参照完整性

外键用于关联其他表的主键或唯一键:

foreign key(字段名) references 主表(列)
1

案例:

学生表: 存放每个同学的信息,其中有一列为 班级编号/班级姓名
班级表: 班级的具体信息

通过外键约束后,学生表的每一个同学的班级编号.姓名必须再班级表中存在,否则为非法数据

班级表是负责约束的一方,称为父表 ; 学生表是被约束的一方, 称为子表

创建班级表,并确定主键为classId

create table class (classId int primary key, className varchar(20));
1

创建学生表,一个学生对应一个班级,一个班级对应多个学生,使用student中的classId为主键,class中的classId为外键.

create table student (
    studentId int primary key, 
 	name varchar(20), 
 	classId int, 
 	foreign key(classId) references class(classId)
);
1
2
3
4
5
6

注:

1. 在父表为空时,尝试向子表中插入数据,会报错

2. 父表对子表的限制是子表不能随意的插入/修改,原因是每一次插入数据时,会先在父表中查询,只有存在该数据,才能够成功的插入/修改

3. 子表对父表的限制是父表不能随意修改/删除,原因是子表中的约束数据一定存在父表中,当父表要修改/删除,就会破坏对应关系,

问题延申:

给你一个商品表(goodsId, name, price…)和订单表(goodsId,time, orderId…),这里商品表中外键约束goodsId(父表),订单表中主键约束goodsId(子表),用户已经下单过很多次了,订单表中存放了数据,假如过了一段时间,到了换季季节,要把之前的衣服给下线,在有外键约束的条件下,如何删除商品表中的goodsId呢? 同时不影响订单表中的记录呢?

在解决这个问题前,先问一个问题:操作系统是如何删除磁盘上的数据呢?答案是把对应的盘块标记为无效,并不是真正的删除.所以在下载数据时耗时十分长,这是因为要把数据填充到磁盘中,删除却很快.这里的删除思路很相似,在商品表中新添列flag,作为标记位,如果为0表示商品下线,为1表示商品上线;所以并没有真正的删除这个商品,那会不会占用太多空间呢?答案是不会,因为现在的磁盘都十分便宜成本低.

1.1.6 CHECK

保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句 .

check (条件);
1

比如: check (sex = ‘男’ or sex = ‘女’); 限制sex这一列的取值只能是男或者女.

2. 表的设计

2.1 一对一

案例: 学生 - 账户

一个学生只能有一个账户, 一个账户对应一个学生

方案1: 把学生和账户放到同一表里.

student(id, account, name, password...)
1

解释: 在表中通过id寻找到学生的信息

方案2: 把学生和账户各自放到一个表中,使用一个额外的id来关联.

student(studentId, name, accountId...) 
account(accountId, password...)
1
2

或者

student(name, accountId...) 
account(studentId, accountId, password...)
1
2

解释: 在一个表中寻找到id,拿到这个id在另一个表中拿到其信息.

但是方案一在面对多种不同身份角色时,比如有学生,老师,班主任等在同一个表中就会难以管理,并且在设计学生表,老师表时就难以控制.

2.2 一对多

案例: 学生 - 班级

一个学生只能属于一个班级, 一个班级可以拥有多个学生.

class (classId, className)
student (id, name, classId)
1
2

2.3 多对多

案例:学生 - 课程

一个学生可以选择多个课程,一个课程可以包括多个学生.

一般采用一个中间表来表示多对多的关系

student(studentId, name...)
course(courseId, courseName...)
student_course(studentId, courseId)
1
2
3

3. 新增

语法:

insert into 表名(列名,列名...) select 列名,列名.. from 表名 
1

解释: 先执行查询操作,要求查询出来的结果的列名和类型与被插入表匹配

示例: 创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
	id INT primary key auto_increment,
	name VARCHAR(20) comment '姓名',
	age INT comment '年龄',
	email VARCHAR(20) comment '邮箱',
	sex varchar(1) comment '性别',
	mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;
1
2
3
4
5
6
7
8
9
10
11
12

4. 查询

4.1 聚合查询

在之前学习的表达式查询是针对列与列之间的处理, 聚合查询是针对行与行之间的查询

4.1.1聚合函数

常见的聚合函数:

案例:

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1),
	math DECIMAL(3,1),
	english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
	(1,'唐三藏', 67, 98, 56),
	(2,'孙悟空', 87.5, 78, 77),
	(3,'猪悟能', 88, 98.5, 90),
	(4,'曹孟德', 82, 84, 67),
	(5,'刘玄德', 55.5, 85, 45),
	(6,'孙权', 70, 73, 78.5),
	(7,'宋公明', 75, 65, 30),
	(null,null,null,null,null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• count

-- 统计有多少行(null会计入)
select count(*) from exam_result;
1
2

-- 统计有姓名的学生的个数(null不会计入)
 select count(name) from exam_result;
1
2

-- 表达式之间不能有空格
select count (name) from exam_result;
1
2

• sum

-- 计算语文总分
 select sum(chinese) from exam_result;
1
2

-- 聚合查询可以搭配条件表达式使用
-- 计算数学及格同学的总分
select sum(math) from exam_result where math >= 60;
1
2
3

• avg

-- 统计平均分(不包含null)
select avg(math + chinese + english) from exam_result;
1
2

• max

-- 英语最高分
select max(english) from exam_result;
1
2

• min

-- 数学最低分
select min(math) from exam_result;
1
2

注: 以上函数只针对数值类型.

4.1.2 GROUP BY 子句

语法:

select 列名,函数(列名)... from 表名 group by 列名;
1

解释: 指定某一列为分组依据,分组依据相同的为一组,然后可以对分好的组使用聚合函数.

案例1:

-- 数据准备
create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	role varchar(20) not null,
	salary numeric(11,2)
);
-- 插入数据
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

-- 查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

案例2:

-- 准备数据
 create table student(id int, name varchar(20), gender varchar(1), score int);
 
-- 插入数据
insert into student values
   (1, '张三',' 男', 95),
   (2, '李四', '女', 78),
   (3, '王五', '女', 88),
   (4, '赵六', '男', 66);
   
-- 分别计算出男生女生的最高分,最低分,平均分
select gender, max(score), min(score), avg(score) from student group by gender;
1
2
3
4
5
6
7
8
9
10
11
12

4.1.3 HAVING

在聚合之前,进行筛选,针对筛选之后的结果,再聚合,使用where子句;在聚合之后,进行筛选,使having子句;根据问题的所需要操作对象是在聚合之前还是之后,灵活使用条件子句.

使用上面案例2的student表:

• 聚合之前筛选

-- 查询每个性别的平均分(除去张三同学)
select gender, avg(score) from student where name != '张三' group by gender;
1
2

• 聚合之后筛选

-- 查询平均分大于80的性别情况(需要先计算平均分 ->要先聚合)
select gender, avg(score) from student group by gender having avg(score) > 80;
1
2

• 同时筛选

-- 查询平均分大于80的性别情况(除去李四同学)
 select gender, avg(score) from student where name != '李四' group by gender having avg(score) > 80;
1
2

聚合查询的执行过程:

1. 先按照 where条件进行筛选记录
2. 把筛选出来的结果按照group by来分组
3. 分组之后按照having 再进行筛选
4. 最后再按照制定列中调用的聚合函数来显示计算结果

4.2 联合查询

联合查询也叫做’多表查询’,把多张表的记录合并到一起,综合进行查询,查询的核心概念:笛卡儿积(把几个表中的记录,进行排列组合,穷举出所有的组合).

笛卡尔积介绍:

-- 学生表
student(id, name, classId)
		1    张三     1
		2    李四   	2
		3    王五  	3
		4    赵六     4
		
-- 班级表
class(classId, name) 
		1      java101
		2      java102
		
-- 对上面两表进行笛卡尔积
id  name  classId  classId   name
1    张三     1		1      java101
1    张三     1		2      java102
2    李四   	2		1      java101
2    李四   	2		2      java102
3    王五  	3		1      java101
3    王五  	3		2      java102
4    赵六     4		1      java101
4    赵六     4		2      java102
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

结论:

1. 最终生成的笛卡尔积的列数,是原来两种表列数之和;行数就是原来两种表之积.
2. 再设计开发中应该避免多表查询,如果之前的表很大,会导致最终生成的表更加复杂,效率低下.
3. 简单生成的笛卡尔积中包含了许多无效数据,通过’连接条件’来过滤掉无效数据,比如这里应该以二者的classId相等作为连接条件
4. 如果多种表中存在列名相同的情况,可以通过"表名.列名"来访问.

初始化数据:

-- 班级表
create table classes(name varchar(20), `desc` varchar(40));
insert into classes(name, `desc`) values
	('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
	('中文系2019级3班','学习了中国传统文学'),
	('自动化2019级5班','学习了机械自动化');

-- 学生表
create table student(id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20), classes_id int);
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);
	
-- 课程表
create table course(id int primary key auto_increment, name varchar(20));
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

-- 分数表
create table score(score int, student_id int, course_id int);
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

上面的4张表,存在3个实体:学生,班级,课程,对应着(班级 - 学生 一对多)(学生 - 课程 多对多)(分数表 - 学生和课程的关联表)

4.2.1 内连接

语法:

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
1
2

案例:

• 查询’黑旋风李逵 '的成绩

1. 黑旋风李逵 是名字在student 表中, 成绩是分数在score表中;所以我们先对两种表进行笛卡尔积.(一共160行)

2. 过滤无效数据,根据连接条件 id 相同

3. 再加入根据姓名筛选

4. 省略不必要的列

或者使用select 列名… from 表名 join 表名 join 表名… on 筛选条件

总结:

1. 根据需求分析所需数据分布在那些表中
2. 对多个表进行笛卡尔积
3. 根据连接条件,过滤非法数据,得到合法数据
4. 进一步增加条件,得到更加细致的信息
5. 去掉不必要的列,保留关键列

• 查询所有同学的总成绩，及同学的个人信息

1. 查询出所有同学的个人信息,及成绩

2. 要每一个同学的个人信息,再继续筛选(这里根据id分组查询的数据,是每个分组后的第一条记录)

3. 使用聚合函数sum(),得到总成绩

• 查询每个同学的姓名,课程名,分数

1. 对三张表进行笛卡尔积根据连接条件.

2. 筛选出需要的列保留

4.2.2 外连接

外连接分为左外连接和右外连接.

解释:

 create table student(id int, name varchar(20));
 insert into student values (1, '张三'), (2, '李四'), (3, '王五');
 
 create table score(id int, score double);
 insert into score values(1, 90), (2,85), (3,78);
1
2
3
4
5

在两张表中记录可以一一对应时,内连接,左右外连接都是相同的.现在修改分数表id为3的修改为4

update score set id = 4 where score = 78;
1

结论:

1. 内连接产生的结果,是两张表都包含的数据
2. 左外连接,就是以join 左侧的表为主.保证左侧的表每个记录都能体现在结果中.如果左侧的记录在右侧表中不存在,则填充NULL
3. 右外连接,就是以join右侧的表为主.保证右侧的表每个记录都能体现在结果中.如果右侧的记录在左侧不存在,则填充NULL.

注:存在一种外连接可以把整个集合(记录)获取到,被称为全外连接,但是MySQL不支持.

4.2.3 自连接

自连接是自己与自己进行笛卡尔积;在之前的表达式查询中针对的是列与列的关系,难以进行行与行之间的筛选,然而自连接可以把列转化为行.

案例:

• 显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

1. 在course表中查询“计算机原理”和“Java”的课程id

 select id, name from course where name = 'java' or name = '计算机原理';
1

2. 进行自连接

 select * from score, score;
 //这是错误的 -- 错误信息Not unique table/alias: 'score'
 -- 解决方式: 起别名
 select * from score as s1, score as s2;
 -- 根据同学id筛选
 select * from score as s1, score as s2 where s1.student_id = s2.student_id;
1
2
3
4
5
6

3. 根据课程id筛选.

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

4. 增加筛选条件,计算机原理”比“Java"高

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

5. 省略不必要的列

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

4.2.4 子查询

子查询 是之嵌入在sql语句中的select语句,也叫嵌套查询,俗称’套娃’

案例:

单行子查询：返回一行记录的子查询

• 查询与“不想毕业” 同学的同班同学：

-- 1.根据名字查询班级id
select classes_id from student where name = '不想毕业';
-- 2.根据id查询与id匹配的记录
select name from student where classes_id = 1 and name != '不想毕业';

-- 子查询
select name from student where classes_id = (select classes_id from student where name = '不想毕业') and name != '不想毕业';
1
2
3
4
5
6
7

多行子查询：返回多行记录的子查询

• 查询“语文”或“英文”课程的成绩信息

-- 子查询的结果有多个,就可以使用in
select * from score where course_id in(select id from course where name = '语文' or name = '英文');
1
2

4.2.5 合并查询

合并查询就是合并多个select的执行结果 .

• union

案例：查询id小于3，或者名字为“英文”的课程

-- 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行.

-- 使用or
select * from course where id < 3 or name = '英文';

-- 使用union
select * from course where id < 3 union select * from course where name = '英文';
1
2
3
4
5
6
7

• union all

案例：查询id大于3，或者课程名为英文的.

-- 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

select * from course where id < 3 union all select * from course where name = '英文'; 
1
2
3