【MySQL】增删改查进阶

目录

 一、数据库约束

约束类型

NULL约束：非空约束

UNIQUE：唯一约束

DEFAULT：默认值约束

PRIMARY KEY：主键约束

FOREIGN KEY：外键约束

二、表的设计

三、新增

四、查询

聚合查询

聚合函数

GROUP BY子句

HAVING

联合查询

内连接

外连接

自连接

子查询

合并查询

---

一、数据库约束

数据库中的数据是如此重要，因此我们希望数据库里的数据，不要存在一些 "错误的" 数据。数据库中的 "约束''，其实就是对于数据库存储的数据，做出一些 "限制" "要求”。负责插入数据的程序员，得确保插入的数据是靠谱的，而人是不靠谱的，机器靠谱，程序靠谱。约束，就是数据库这样的程序，对于保存的数据，进行合法性检查这样的机制，一旦发现问题，数据库程序就能及时给出反馈。

约束类型

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

NULL约束：非空约束

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

--重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);

---

UNIQUE：唯一约束

指定 sn 列为唯一的、不重复的：

--重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20),
qq_mail VARCHAR(20)
);

---

DEFAULT：默认值约束

指定插入数据时，name列为空，默认值unkown：

--重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);

---

 

最初的默认值都是NULL

PRIMARY KEY：主键约束

指定 id 列为主键：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);

对于整数类型的主键，常配搭自增长 auto_increment 来使用。插入数据对应字段不给值时，使用最大值+1。

-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL

id INT PRIMARY KEY auto_increment

也能手动指定其他数值

接下来插入的时候，自动分配下一条记录是 5 ？还是 101 ？

在MySQL中，给每个表都记录了一个 "自增主键的最大值''，后续继续插入数据，无论之前的最大值是否仍然存在，都是根据之前保存的最大值继续往后分配的。

---

约定了这个表里某个列为 "身份标识"（非空且唯一）

不允许一个表中同时存在多个主键（多个身份标识）

FOREIGN KEY：外键约束

外键用于关联其他表的 主键 或 唯一键，语法：

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

👁‍🗨案例

● 创建班级表 classes，id 为主键：

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);

● 创建学生表 student，一个学生对应一个班级，一个班级对应多个学生。使用 id 为主键，classes_id 为外键，关联班级表 id：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);

---

涉及到两个表

没有指定约束之前

指定约束之后

此时，外键约束就出现了，往 student 表插入的时候会触发针对 class 表的查询，再次往 student中插入数据，就务必要确保插入的数据的 classld 的值，得在class表中存在。

如果父表中的某个记录，被子表引用了（classld=1），此时就不能删除/修改父表中的对应记录了，父表是约束了子表，子表也会对父表反向进行约束。学生表的 classld 列，引用自班级表的 classld 列，班级表中的数据，约束了/制约了学生表的数据。

删表的时候还需要确保先删除子表再删除父表

再创建表

为啥第一次能行，第二次就不行了？第一次和第二次，差别在哪里呢？指定外键的时候，后续往子表中插入数据，就需要查询父表，第二次没有给父表加主键。主键本身也是有 "唯一性要求的"，频繁触发查询，此处数据库就会对主键约束，给出特殊的处理（数据库会针对主键列创建索引），加快查询速度。

如果不指定主键，是一个 unique，也能达成类似的效果，都是要自动建立索引，以便于后续的快速查询。

关于外键还有一个更复杂一点的情况.

二、表的设计

一对一

一对多

多对多

三、新增

插入查询结果

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

👁‍🗨案例：创建一张用户表，设计有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;

---

得到的结果集合，列数/类型/顺序要和 insert into 后面的表相匹配，列的名字是不要求相同的

发现顺序不匹配的话可以通过指定列方式插入

或者

四、查询

聚合查询

针对行和行之间进行 “聚合操作”

聚合函数

进行聚合查询需要搭配聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量（行数）

如果使用 count 等聚合函数的时候，cout 和()之间加上空格，此时就会使函数执行失败

如果数据中包含了 null，可能对上述 count 产生影响

在刚刚统计行数的时候虽然这个表里有一条记录，含有 null，但最终查询到的行数还是会把这个
记录算进去的

如果是以 select+列名 的方式，那么遇到空值，就不会进行计数

函数	说明
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义

可以针对某一列进行求和

需要确保进行求和的列是数字，如果是字符串，就会出现问题

这里只有 6 个警告，因为有一条的 name 为空。null 和其他数值进行各种运算，结果都是 null，但是在 sum 中比较特殊，遇到 null 会直接跳过，不参与运算

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

其实MySQL中还提供更复杂的聚合函数，还能算标准差，方差等…

GROUP BY子句

SELECT中使用GROUP BY子句可以对指定列进行分组查询。需要满足：使用GROUP BY进行分组查询时，SELECT指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT中则必须包含在聚合函数中。

针对上述分组结果，也是可以排序的

HAVING

GROUP BY子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用WHERE语句，而需要用HAVING

⚠️需要区分好条件是分组之前的（使用原始数据比较）还是分组之后的（使用统计数据比较）

👁‍🗨案例

（1）分组之前的条件

查询每个岗位的平均薪资但是去除张三这个人

（2）分组之后的条件

排除薪资低于 5000 的记录

（3）一个查询，可以同时包含，分组前的条件和分组后的条件
统计每个岗位平均薪资，排除张三，也排除平均值低于 5w 的结果

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
 

进行多表查询的时候通常会通过一定的条件，把这里笛卡尔积中的 "合法数据” 筛选出来，这样的条件就称为 "连接条件”。如果拿两个表，这里不存在某个列，具有上述的对应关系，此时进行的笛卡尔积可以认为是 “无意义” 的。

初始化测试数据：

create table classes(id int primary key auto_increment,name varchar(20),`desc` varchar(100));create table student(id int primary key auto_increment,sn varchar(20),name varchar(20),qq_email varchar(20),classes_id int);create table course(id int primary key auto_increment,name varchar(20));create table score(score decimal(3,1),student_id int,course_id int);insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');insert into student(sn, name, qq_email, 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 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

👁‍🗨案例

1️⃣明确要查询的信息来自于哪些表

2️⃣针对这些表进行笛卡尔积

3️⃣指定连接条件把有意义的数据筛选出来

4️⃣根据需要进一步指定条件对数据再筛选

5️⃣针对查询的列也去进行精简

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

1️⃣学生表和分数表

2️⃣对这两个表进行笛卡尔积

3️⃣筛选掉无效的数据

4️⃣

5️⃣

---

还可以通过另外一个写法 join

select * from student join score;

select * from student join score on student.id = score.student_id;

select * from student join score on student.id = score.student_id and student.name = '许仙';

select name, score from student join score on student.id = score.student_id and student.name = '许仙';

相比于直接多个表 where 的方式来说，join on 的方式，还有更多的变化（支持的功能更多一些）实际开发中，这两个写法都很常用，都要能够掌握

（2）查询所有同学的总成绩，及同学的个人信息
1️⃣学生表和分数表

2️⃣

select * from student,score;

3️⃣

select * from student,score where student.id = score.student_id;

4️⃣针对上述数据进行分组聚合

5️⃣搭配聚合函数

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

列出同学名字、课程名字、课程分数
1️⃣student course score

2️⃣

select * from student,course,score;

3️⃣

select * from student,course,score where student.id = score.student_id and course.id = score.course_id;

4️⃣

select student.name as studentName,course.name as courseName,score.score from student,course,score where student.id =score.student_id and course.id = score.course_id;

---

 也可以通过 join on 的方式来写，更能清楚地体现出联合查询的过程

3️⃣

select * from student join score on student.id = score.student_id;

select * from student join score on student.id = score.student_id join course on score.course_id = course.id;

4️⃣

select student.name as studentName,course.name as courseName,score.score from student
join score on student.id = score.student_id join course on score.course_id = course.id;

虽然上述的例子说明，计算笛卡尔积是可以拿任意个表进行的，但是由于笛卡尔积可能会产生出大量的 "中间结果"，此时就会对于性能影响很大，甚至严重的，可能会把数据库给搞挂了。因此，多表联合查询，可以用，用的时候，要慎重，需要先对当前这样的多表查询，大慨会涉及到多少数据量进行操作，有一个 "预估"。

外连接

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

左外连接就是以左表为基准，能够确保左表中的每个记录都出现在最终结果里，如果左表中的记录，在右表中没有对应的记录，此时就会把右表中的相关字段，填成NULL。

右外连接，是以右表为基准，然后确保右表中的每个记录都出现在最终结果里，如果右表中的某个记录在左表里没有对应的相关字段，填成NULL。

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

这俩表的数据，是 "一一对应" 的，此时，针对这两个表，进行内连接和外连接，操作结果，完全相同(内连接和外连接就是等价的效果)。但是，一旦上述的数据，不再一一对应，此时，内连接和外连接，就会出现差别。

update score set id = 4 where score = 70;

内连接：查询结果只包含两个表中同时具备的数据

select name, score from student, score where student.id = score.id;

或者

select name, score from student inner join score on student.id = score.id;

inner 可以省略，在这表示内连接

外连接不支持 from 多个表这种写法，left join 表示左外连接，right join 表示右外连接 

左外连接：

右外连接：

自连接

自连接是指在同一张表连接自身进行查询，即自己和自己进行笛卡尔积

SQL中进行的条件查询，是针对两个列进行比较的，不能比较两个行，自连接，本质上就能把行关系，转换成列关系

👁‍🗨案例 显示所有 “计算机原理” 成绩比 “Java” 成绩高的成绩信息

在自连接之前需要给表取不同的别名再进行连接

select * from score as s1, score as s2;

指定连接条件把无效数据干掉

select * from score as s1,score as s2 where s1.student_id = s2.student_id;

左侧的 score 列就是计算机原理的分数，右侧的 score 列就是 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;

这样的结果，就说明了上述 score 表中，只有三个同学同时具有计算机原理和 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;

select s1.student_id,s1.score,s2.score 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;

💡如果当前发现，要查询的条件，是针对两行，而不是两列，就可以考虑使用自连接，进行转换。当然自连接的时候，如果表非常大，此时的连接开销，也会非常庞大，容易就把数据库搞死了。

子查询

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

🚫本来一个需求，需要通过多个SQL来完成的，但是现在就偏偏要把多个SQL合并成一个子查询。原则上不推荐大家使用的。

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

👁‍🗨案例 查询与 “不想毕业” 同学的同班同学

不使用子查询：

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

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 != '不想毕业';

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

此时不能使用 = > <这样的运算符直接比较了，但是可以使用 in

👁‍🗨案例 查询 “语文” 或 “英文” 课程的成绩信息

1️⃣[NOT] IN关键字

不使用子查询：

select * from course where name = '语文';

select * from course where name = '英文';

select * from score where course_id in (4,6);

使用子查询：

select * from score where course_id in (select id from course where name = '语文' or name = '英文');

2️⃣[NOT] EXISTS关键字

更不推荐🚫因为 exists 非常消耗时间，背后会触发大量的硬盘IO操作，并且代码理解起来也比较复杂

合并查询

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

▪️union

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

▪️union all

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

select * from student union all select * from student2;

📢要求合并的双方，类型、个数、顺序匹配，列名不要求（以左侧为准）