MySQL | 数据库的表的增删改查【进阶】

文章目录

MySQL | 数据库的表的增删改查【进阶】
系列文章目录
- 本节目标：
- 数据库约束
- - 约束类型
  - NULL约束
  - UNIQUE：唯一约束
  - DEFAULT：默认值
  - PRIMARY KEY：主键
  - FOREIGN KEY：外键
  - CHECK
- 表的设计
- 新增
- 查询
- - 聚合查询
  - - 聚合函数
    - 内连接
    - 外连接
    - 自连接
    - 子查询
    - 合并查询
- 内容重点总结

系列文章目录

第一章：MySQL | 数据库的管理和操作(基本介绍)

第二章：MySQL | 数据库的基本操作和表的基本操作

第三章：MySQL | 数据库的表的增删改查

第四章：MySQL | 数据库的表的增删改查【进阶】

前言：

在前面我们已经学了数据库的增删查改（初阶），还没有学习的同学可以点击上面的连接进行跳转学习~~
本章就来学习数据库的进阶，让我们来一起走进吧~~

本节目标：

数据库约束
表的关系
删除
修改
查询

数据库约束

约束，就是数据库针对里面的数据能写啥，给出的一组"检验规则"
这样的约束，可以是程序猿人工来保证的，也可以是程序自动保证的
约束，就是为了提高效率，提高准确性，让数据库这个软件集成一个针对数据效验的功能

约束类型

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

NULL约束

创建表时，可以指定某列不为空：

在没有约束的时候，此时表中可以插入空值！！！

create table student(id int,name varchar(20));

insert into student values(null,null);

在这里插入图片描述

这里我们重新创建一个表，设置成不能为空

create table student (id int not null, name varchar(20) not null);

非空约束也就生效了~~

在这里插入图片描述

UNIQUE：唯一约束

插入/修改数据的时候，会先查询，先看看数据是否已经存在，如果不存在，就能插入/修改成功，如果存在，则插入/修改失败！
可以看到数据已经插入了一次，就不能插入了

create table student (id int unique, name varchar(20));

在这里插入图片描述

DEFAULT：默认值

这里插入数据不指定name就会默认NULL

insert into student (id) values(2);

在这里插入图片描述

这里设置了默认值为无名氏,再次插入不指定默认列

create table student (id int,name varchar(20) default '无名氏');

在这里插入图片描述

PRIMARY KEY：主键

主键，一条记录，在表中的身份标识
也是要求唯一的，并且不能为空~~
主键 = unique + not null
mysql要求一个表中只能有一个主键
- 创建主键的时候，可以使用一个列作为主键，也可以使用多个列作为主键（复合主键），这个很少见

create table student (id int primary key, name varchar(20));

在这里插入图片描述

当我插入一次数据后，再次插入，就不能了，看起来就是和not null + unique是类似的~~

insert into student values (1,'张三');

在这里插入图片描述

一个重要的问题：

如何给这个记录安排一个主键呢？
- mysql自身只是能够检查是否重复，设置的时候还是靠程序猿来设置~~
- 在这里，mysql提供了一个简单粗暴的办法，自增主键

create table student (id int primary key auto_increment, name varchar(20));

在这里插入图片描述

给自增主键插入数据的时候，可以手动指定一个值，也可以让mysql自己分配，如果让他自己分配，就在insert语句的时候，把id设为null即可

在这里插入图片描述

这里还可以手动分配

insert into student values(100,'赵六');

在这里插入图片描述

那么这里继续让mysql自己指定

insert into student values (null,'七七');

在这里插入图片描述

那么我再次指定·插入，再让mysq自己指定，那会是怎么样的呢？

在这里插入图片描述

可以看到，这里mysql还是继续默认浪费了~~
那么这里空间有浪费了吗？
- 这里分配的时候把这些序号跳过了，浪费了一部分序号，没有浪费空间

FOREIGN KEY：外键

两张表之间相互关联

create table class(class_id int primary key auto_increment, class_name varchar(20));

create table student(student_id int primary key auto_increment, name varchar(20),class_id int, foreign key (class_id) references class(class_id));

此时就要求student表中的每个记录的class_id得在class表的class_id中存在！

在这里插入图片描述

这个时候插入数据就会失败

在这里插入图片描述

这里写的是不能新增或者修改子
这里我们插入点班级数据

insert into class values(null,'cls1');
insert into class values(null,'cls2');
insert into class values(null,'cls3');

在这里插入图片描述

再次插入数据，就成功了

insert into student values(null,'张三',1);

在这里插入图片描述

换句话说student表插入数据的时候，mysql先会做一件事，会拿着这个记录的class_id去class表中看看有没有~~
不仅是插入，修改也会有约束

update student set class_id = 10 where student_id = 1;

在这里插入图片描述

那我们尝试把班级表中的class_id为1的记录给删了，会不会报错呢？
- 可以看到是不能删除的~~

在这里插入图片描述

CHECK

这个对于mysql5是不支持的，所以这里就先不讲了~~

表的设计

设计表要求有一定的经验，才能理解~~
设计表，分两步走
- 梳理清楚需求中的"实体"
- 梳理清楚实体之间的关系，按照关系，带入到既定的公式中
很多时候，每个实体就需要对应一张表来进行表示~~

实体之间的关系。主要有三种（严格来说是四种）

一对一

一个学生，只能有一个账号
一个账号，只能供一个学生所有

在这里插入图片描述

一对多

一个班级可以包含多个学生
一个学生只能处于一个班级

在这里插入图片描述

多对多

一个学生可以选择多个课程
一个课程也可以提供多个学生

在这里插入图片描述

一般来说只要实体和关系明确了，此时表的设计就差不多了~~
如果实体比较多，关系比较复杂，可以画一个实体关系图【ER图】来表示这个关系~~

新增

把查询和新增联合起来

我先创建两个学生表（student，student2），插入数据到student
我想把数据拷贝到student2，怎么办呢？

create table student (id int ,name varchar(20));
create table student2 (id int ,name varchar(20));
insert into student values(1,'张三'),(2,'李四'),(3,'王五');

在这里插入图片描述

我们可以使用以下语句来进行操作

insert into student2 select * from student;

在这里插入图片描述

查询

聚合查询

聚合查询就是把查询过程中，表的行和行之间进行一定的运算~~

聚合函数

聚合函数，这些是sql提供的库函数

在这里插入图片描述

我们先插入一些数据

在这里插入图片描述

这里的count函数就是统计
这个操作可以理解成，先执行select * 然后再去计算select *查询结果有多少行

select count(*) from student;

在这里插入图片描述

再来看一下下一个sum

我们先来创建一个表

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

可以看到我们已经完成插入了

在这里插入图片描述

这里开始使用sum

select sum(chinese) from exam_result;

在这里插入图片描述

如果数据中有null咋办？
可以看到虽然插入了空，数据还是可以计算出来的~~

insert into exam_result values (8,'如来佛祖',null,null,null);
select sum(chinese) from exam_result;

在这里插入图片描述

sum操作会自动跳过结果为null的行~~

那我要是算字符串呢？

select sum(name) from exam_result;

可以看到这里有8个告警

在这里插入图片描述

我们可以查看这里的告警

show warnings;

在这里插入图片描述

所以针对非数字列进行求和是无法计算的~~

不光sum如此，后面的这几个都是一样的

我们再来看avg,这个查看平均分的

select avg(chinese) from exam_result;

在这里插入图片描述

聚合函数还能搭配表达式使用，比如求总分的平均分

select avg(chinese + math + english) as avg_total from exam_result;

在这里插入图片描述

可以看出mysql是有一定的统计计算能力的
就像excel一样
正因为sql有这样的统计能力，有的时候sql也是属于非技术岗（产品经理）需要掌握的技能~~

继续来看max和min一个是最高一个是最低~~

select max(chinese),min(chinese) from exam_result;

在这里插入图片描述

好了，聚合函数就这些了~~

上面这些聚合函数，默认都是针对这个表里的所有类进行了聚合
有时候我们还需要分组聚合（按照指定的字段，把记录分成若干组，每一组分别使用聚合函数）
比如说这里我们创建一个员工表

create table emp (id int,name varchar(20), role varchar(20),salary int);

插入数据

insert into emp values(1,'孙悟空','讲师',10000);
insert into emp values(2,'猪悟能','讲师',11000);
insert into emp values(3,'沙悟净','讲师',12000);
insert into emp values(4,'刘玄德','学管师',10000);
insert into emp values(5,'曹孟德','学管师',9000);
insert into emp values(6,'如来佛祖','老板',100000);
insert into emp values(7,'太上老君','老板',120000);

在这里插入图片描述

比如我们求出每个岗位的平均薪资~~
就需要使用分组查询，group by
指定一个列，把列里的值，想通的分到同一个组中~~

select role,avg(salary) from emp group by role;

在这里插入图片描述

select指定的列，要么是带有聚合函数的，要么就得是指定的group by的列，不能做指定一个非聚合，非group by的列
比如查询的时候写个name，name不是group by的列也不带聚合函数，此时这里的查询结果无意义，但是不会报错~~

分组的时候，可以指定条件筛选~~
先搞清楚，筛选条件是分组前，还是分组后~~
分组前筛选，使用where条件
比如求每个岗位的平均薪资，但是刨除孙悟空同学~~

select role,avg(salary) from emp where name != '孙悟空' group by role;

在这里插入图片描述

分组后筛选，使用having条件
求背个岗位的平均薪资，刨除老板

select role,avg(salary) from emp group by role having role != '老板';

在这里插入图片描述

还可以同时分组前和分组后筛选

select role,avg(salary) from emp where name != '孙悟空' group by role having role != '老板';

在这里插入图片描述

联合查询（多表查询）

把多个表联合到一起进行查询~~
- 联合查询基于笛卡尔积~~
- 笛卡尔积，其实就是一种排列组合，把两张表的记录，尽可能排列组合出N种情况~~

在这里插入图片描述

insert into classes(name, `desc`) values
	('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
	('中文系2019级3班','学习了中国传统文学'),
	('自动化2019级5班','学习了机械自动化');
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);
insert into course(name) values
	('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
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);

笛卡尔积

select * from student,score;

多表查询一般实现步骤

分析清楚需求中涉及到的信息都爱哪些表里
针对多个表进行笛卡尔积
筛选出其中的有效数据
结合需求中的条件，进一步加强条件
针对列进行精简

内连接

语法：

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

案例：
（1）查询“许仙”同学的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='许仙';
-- 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';

（2）查询所有同学的总成绩，及同学的个人信息：

-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的
SELECT
	stu.sn,
	stu.NAME,
	stu.qq_mail,
	sum( sco.score )
FROM
	student stu
JOIN score sco ON stu.id = sco.student_id
GROUP BY
	sco.student_id;

（3）查询所有同学的成绩，及同学的个人信息：

-- 查询出来的都是有成绩的同学，“老外学中文”同学 没有显示
select * from student stu join score sco on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
	stu.id,
	stu.sn,
	stu.NAME,
	stu.qq_mail,
	sco.score,
	sco.course_id,
	cou.NAME
FROM
student stu
JOIN score sco ON stu.id = sco.student_id
JOIN course cou ON sco.course_id = cou.id
ORDER BY
stu.id;

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

语法：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

案例：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

-- “老外学中文”同学 没有考试成绩，也显示出来了
select * from student stu left join score sco on stu.id=sco.student_id;
-- 对应的右外连接为：
select * from score sco right join student stu on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
	stu.id,
	stu.sn,
	stu.NAME,
	stu.qq_mail,
	sco.score,
	sco.course_id,
	cou.NAME
FROM
	student stu
LEFT JOIN score sco ON stu.id = sco.student_id
LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
stu.id;

自连接

自连接是指在同一张表连接自身进行查询。

案例：

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

-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
SELECT
	s1.*
FROM
	score s1,
	score s2
WHERE
	s1.student_id = s2.student_id
	AND s1.score < s2.score
	AND s1.course_id = 1
	AND s2.course_id = 3;
-- 也可以使用join on 语句来进行自连接查询
SELECT
	s1.*
FROM
	score s1
JOIN score s2 ON s1.student_id = s2.student_id
	AND s1.score < s2.score
	AND s1.course_id = 1
	AND s2.course_id = 3;

以上查询只显示了成绩信息，并且是分布执行的。要显示学生及成绩信息，并在一条语句显示：

SELECT
	stu.*,
	s1.score Java,
	s2.score 计算机原理
FROM
	score s1
	JOIN score s2 ON s1.student_id = s2.student_id
	JOIN student stu ON s1.student_id = stu.id
	JOIN course c1 ON s1.course_id = c1.id
	JOIN course c2 ON s2.course_id = c2.id
	AND s1.score < s2.score
	AND c1.NAME = 'Java'
	AND c2.NAME = '计算机原理';

子查询

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

单行子查询：返回一行记录的子查询
- 查询与“不想毕业” 同学的同班同学：

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

多行子查询：返回多行记录的子查询
案例：查询“语文”或“英文”课程的成绩信息

[NOT] IN关键字：

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

可以使用多列包含：

-- 插入重复的分数：score, student_id, course_id列重复
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);
-- 查询重复的分数
SELECT * FROM
score
WHERE
	( score, student_id, course_id ) IN ( SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( 0 ) > 1 );

[NOT] EXISTS关键字：

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个
子查询当做一个临时表使用。

查询所有比“中文系2019级3班”平均分高的成绩信息：

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
	avg( sco.score ) score
FROM
	score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
	cls.NAME = '中文系2019级3班';

查询成绩表中，比以上临时表平均分高的成绩：

SELECT
	*
FROM
	score sco,
(
SELECT
	avg( sco.score ) score
FROM
	score sco
	JOIN student stu ON sco.student_id = stu.id
	JOIN classes cls ON stu.classes_id = cls.id
WHERE
	cls.NAME = '中文系2019级3班'
) tmp
WHERE
	sco.score > tmp.score;

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL时，前后查询的结果集中，字段需要一致。

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：查询id小于3，或者名字为“英文”的课程：

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';

内容重点总结

在这里插入图片描述

表的关系
- 一对一
- 一对多
- 多对多

新增：

INSERT INTO table_name [(column [, column ...])] SELECT ...

查询：
- 聚合函数：MAX、MIN、AVG、COUNT、SUM
- 分组查询：GROUP BY… HAVING …
内连接：

select ... from 表1，表2 where 条件
-- inner可以缺省
select ... from 表1 join 表2 on 条件 where 其他条件

外连接：

select ... from 表1 left/right join 表2 on 条件 where 其他条件

自连接：

select ... from 表1，表1 where 条件
select ... from 表1 join 表1 on 条件

子查询：

-- 单行子查询
select ... from 表1 where 字段1 = (select ... from ...);
-- [NOT] IN
select ... from 表1 where 字段1 in (select ... from ...);
-- [NOT] EXISTS
select ... from 表1 where exists (select ... from ... where 条件);
-- 临时表：form子句中的子查询
select ... from 表1， (select ... from ...) as tmp where 条件

合并查询：

-- UNION：去除重复数据
select ... from ... where 条件
union
select ... from ... where 条件
-- UNION ALL：不去重
select ... from ... where 条件
union all
select ... from ... where 条件
-- 使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致