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

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

文章目录

系列文章目录

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

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

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

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

---

前言：

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

---

本节目标：

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

数据库约束

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

约束类型

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

NULL约束

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

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

createtable student(id int,name varchar(20));

insertinto student values(null,null);

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

createtable student (id intnotnull, name varchar(20)notnull);

• 非空约束也就生效了~~

UNIQUE：唯一约束

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

createtable student (id intunique, name varchar(20));

DEFAULT：默认值

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

insertinto student (id)values(2);

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

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

PRIMARY KEY：主键

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

createtable student (id intprimarykey, name varchar(20));

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

insertinto student values(1,'张三');

一个重要的问题：

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

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

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

• 这里还可以手动分配

insertinto student values(100,'赵六');

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

insertinto student values(null,'七七');

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

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

FOREIGN KEY：外键

• 两张表之间相互关联

createtable class(class_id intprimarykeyauto_increment, class_name varchar(20));

createtable student(student_id intprimarykeyauto_increment, name varchar(20),class_id int,foreignkey(class_id)references class(class_id));

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

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

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

insertinto class values(null,'cls1');insertinto class values(null,'cls2');insertinto class values(null,'cls3');

• 再次插入数据，就成功了

insertinto student values(null,'张三',1);

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

update student set class_id =10where student_id =1;

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

CHECK

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

---

表的设计

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

---

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

一对一

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

---

一对多

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

---

多对多

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

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

---

新增

把查询和新增联合起来

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

createtable student (id int,name varchar(20));createtable student2 (id int,name varchar(20));insertinto student values(1,'张三'),(2,'李四'),(3,'王五');

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

insertinto student2 select*from student;

---

查询

聚合查询

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

聚合函数

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

• 我们先插入一些数据

---

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

selectcount(*)from student;

---

再来看一下下一个

sum

• 我们先来创建一个表

CREATETABLE exam_result ( id INT, name VARCHAR(20), chinese DECIMAL(3,1), math DECIMAL(3,1), english DECIMAL(3,1));

• 再插入几个数据

INSERTINTO 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

selectsum(chinese)from exam_result;

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

insertinto exam_result values(8,'如来佛祖',null,null,null);selectsum(chinese)from exam_result;

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

---

• 那我要是算字符串呢？

selectsum(name)from exam_result;

• 可以看到这里有8个告警

• 我们可以查看这里的告警

showwarnings;

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

---

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

---

我们再来看

avg

,这个查看平均分的

selectavg(chinese)from exam_result;

---

聚合函数还能搭配表达式使用，比如求

总分的平均分

selectavg(chinese + math + english)as avg_total from exam_result;

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

---

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

selectmax(chinese),min(chinese)from exam_result;

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

---

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

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

• 插入数据

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

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

select role,avg(salary)from emp groupby role;

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

---

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

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

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

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

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

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

---

联合查询（多表查询）

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

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

笛卡尔积

select*from student,score;

多表查询一般实现步骤

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

内连接

语法：

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

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

select sco.score from student stu innerjoin 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
GROUPBY
    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
ORDERBY
stu.id;

外连接

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

语法：

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

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

-- “老外学中文”同学 没有考试成绩，也显示出来了select*from student stu leftjoin score sco on stu.id=sco.student_id;-- 对应的右外连接为：select*from score sco rightjoin 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
LEFTJOIN score sco ON stu.id = sco.student_id
LEFTJOIN course cou ON sco.course_id = cou.id
ORDERBY
stu.id;

自连接

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

案例：

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

-- 先查询“计算机原理”和“Java”课程的idselect 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 =1AND 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 =1AND 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='不想毕业');

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

1. [NOT] IN关键字：

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

可以使用多列包含：

-- 插入重复的分数：score, student_id, course_id列重复insertinto 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 GROUPBY score, student_id, course_id HAVINGcount(0)>1);

1. [NOT] EXISTS关键字：

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

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

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

-- 获取“中文系2019级3班”的平均分，将其看作临时表SELECTavg( 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,(SELECTavg( 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<3unionselect*from course where name='英文';-- 或者使用or来实现select*from course where id<3or name='英文';

• union all

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

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

内容重点总结

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

新增：

INSERTINTO table_name [(column[,column...])]SELECT...

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

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

• 外连接：

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

• 自连接：

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

• 子查询：

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

• 合并查询：

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

• SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit

好了，MySQL | 数据库的表的增删改查【进阶】就到这里了，希望看完有所收获！！！
如果觉得有用的话三连一下吧~~🌹🌹🌹