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 主表(列)

案例:

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

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

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

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

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

创建学生表,一个学生对应一个班级,一个班级对应多个学生,使用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. 子表对父表的限制是父表不能随意修改/删除,原因是子表中的约束数据一定存在父表中,当父表要修改/删除,就会破坏对应关系,

问题延申:

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

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

2. 表的设计

2.1 一对一

案例: 学生 – 账户

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

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

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

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

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

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

或者

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

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

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

2.2 一对多

案例: 学生 – 班级

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

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

2.3 多对多

案例:学生 – 课程

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

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

student(studentId, name...)course(courseId, courseName...)student_course(studentId, courseId)

3. 新增

语法:

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

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

示例: 创建一张用户表，设计有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;

4. 查询

4.1 聚合查询

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

4.1.1聚合函数

常见的聚合函数:

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

案例:

-- 创建考试成绩表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);

• count

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

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

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

• sum

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

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

• avg

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

• max

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

• min

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

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

4.1.2 GROUP BY 子句

语法:

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

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

案例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;

案例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;

4.1.3 HAVING

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

使用上面案例2的student表:

• 聚合之前筛选

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

• 聚合之后筛选

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

• 同时筛选

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

聚合查询的执行过程:

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

4.2 联合查询

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

笛卡尔积介绍:

-- 学生表student(id, name, classId)1张三 12李四 23王五34赵六 4-- 班级表class(classId, name) 1java1012java102-- 对上面两表进行笛卡尔积idnameclassIdclassId name1张三 11java1011张三 12java1022李四 21java1012李四 22java1023王五31java1013王五32java1024赵六 41java1014赵六 42java102

结论:

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

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

4.2.1 内连接

语法:

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

案例:

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

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

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

update score set id = 4 where score = 78;

结论:

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 = '计算机原理';

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;

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;

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;

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;

4.2.4 子查询

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

案例:

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

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

-- 1.根据名字查询班级idselect 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 != '不想毕业';

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

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

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

4.2.5 合并查询

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

• union

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

-- 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行.-- 使用orselect * from course where id < 3 or name = '英文';-- 使用unionselect * from course where id < 3 union select * from course where name = '英文';

• union all

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

-- 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行select * from course where id < 3 union all select * from course where name = '英文';