当前位置：首页 > news >正文

MySQL数据的增删改查（二）

news 来源：原创 2024/9/20 13:54:26

约束

非空约束（NOT NULL）

唯一约束（UNIQUE）

默认值约束（DEFAULT）

主键约束（PRIMARY KEY）

外键约束（FOREIGN KEY）

检查约束（CHECK）

表的设计

一对一

一对多

多对多

查询

聚合查询

COUNT()

SUM() 和 AVG()

MAX() 和 MIN()

分组查询（GROUP BY）

联合（多表）查询

内连接

外连接

自连接

子查询

单行子查询

多行子查询

合并查询

union

union all

之前，我们学习了什么是约束，以及MySQL中的主要约束，接下来，我们就来具体学些每个约束如何进行使用，有哪些注意事项

约束

非空约束（NOT NULL）

非空约束用于确保列不能为 NULL 值，在创建表时，可以指定某列不能为空：

drop table if exists products;

create table products (

product_id int not null,

product_name varchar(10)

);

我们查看表结构：

desc products;

向表中插入数据：

insert into products values (null, "饼干");

由于 product_id 不能为空，插入失败

再次向表中插入数据：

insert into products values (1, null);

product_name 没有要求不能为空，因此可以插入 null 值，product_id 不为空，插入成功

唯一约束（UNIQUE）

唯一约束用于确保列中的每个值唯一（不重复）

drop table if exists student;

create table student(

id int unique,

name varchar(10)

);

student 表创建成功

可以发现，unique 约束的列中可以包含空值

插入数据：

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

再次插入数据：

insert into student values (1, "李四");

由于 id 为 1 的数据已经存在，因此插入失败

当加上 unique 约束后，后续进行插入或是修改时，都会先进行查询，确定当前这个值是否已经存在，约束能够引入更多的检查操作，但同时也会增加系统的开销

默认值约束（DEFAULT）

提供列的默认值，若插入时没有提供该列的值时，则使用默认值

drop table if exists student;

create table student(

id int,

name varchar(10) default "未知"

);

此时，name 的默认值就设置为了未知

当我们进行指定列插入时：

insert into student (id) values (1);

由于 name 未进行设置，此时就为默认值

主键约束（PRIMARY KEY）

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

drop table if exists student;

create table student(

id int primary key,

name varchar(10)

);

且，一个表中只能有一个主键

但存在 联合主键：只有一个主键，但是这个主键是由多个列联合构成的

我们通常会选用 xxx_id 这样的列来作为主键

主键是不允许重复的，那么，我们要怎样保证它不重复呢？

我们可以使用 AUTO_INCREMENT 来自动生成唯一数字，当插入新的记录时，数据库会自动为其生成一个递增的数字值，这样，就不需要我们手动指定这个值，AUTO_INCREMENT 会确保每条记录都有一个唯一的标识符

drop table if exists student;

create table student(

id int primary key auto_increment,

name varchar(10)

);

这样的主键也称为 自增主键

我们向表中插入数据：

insert into student (name) values ("张三"), ("李四"), ("王五");

此时并未指定 id，但自增的生成了 id 值

那么，我们可以指定 id 值吗？

我们插入数据：

insert into student values (10, "一一");

可以自行插入

此时的 id 值为 10，那么，接下来，再次插入数据，id 值会是 11 还是 4？

再次插入数据：

insert into student (name) values ("二二");

此时会从 10 继续自增，中间 4 - 9 也就不再使用（除非我们手动插入），后续再次进行指定列插入，id 为 12（即使我们将数据删除，也还是 12）

delete from student where id = 11;

insert into student (name) values ("二二");

外键约束（FOREIGN KEY）

确保列中的值在另一个表中存在，维护表之间的关系

我们首先创建一张班级表：

drop table if exists class;

create table class (

class_id int primary key auto_increment,

name varchar(20)

);

再创建一张学生表：

drop table if exists student;

create table student(

id int primary key auto_increment,

name varchar(10),

class_id int

);

我们向 class 表中插入数据：

insert into class (name) values ("高一1班"), ("高一2班"), ("高一3班");

此时，存在三个班级：

再向 student 表中插入数据：

insert into student (name, class_id) values ("张三", 1), ("李四", 1), ("王五", 4);

此时，出现了不属于三个班级的数据（王五），这种情况是不应该出现的，学生对应的班级应该是已经存在的班级

此时，为了插入的学生数据进行约束，我们就可以使用外键

drop table if exists student;

create table student(

id int primary key auto_increment,

name varchar(10),

class_id int,

foreign key (class_id) references class(class_id)

);

之前的约束，是在定义表的时候，哪一列需要，就创建到哪一列的后面

而外键约束，需要写在最后面，将前面的列都定义好了之后，再使用 foreign key 创建外键约束，表明当前表的 class_id 字段受到 class 表中 class_id 字段的约束

此时，学生表受到了班级表的约束

像学生表这样被别的表约束的表，称为子表

像班级表这样约束其他表的表，称为父表

我们再尝试插入数据：

insert into student (name, class_id) values ("王五", 4);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`))

此时，插入失败，在引入外键约束之后，每新增一个记录，都会先在对应的父表中查询，看是否存在，若不存在，就会报错

外键约束是双向的，父表约束子表，子表也在约束父表

我们先向学生表中插入数据：

insert into student (name, class_id) values ("张三", 1), ("李四", 2), ("王五", 3);

我们尝试删除父表中数据：

delete from class where class_id = 1;

由于子表（学生表）中还有与父表（班级表）中 class_id = 1 相关的记录（张三），因此删除失败

在尝试删除或修改父表中的记录时，会先查询子表，看这个结果是否在子表中被引用，若被引用了，则删除失败

要想删除父表的某个记录，就需要先删除子表中对应的数据，确保子表中没有数据引用父表记录，才能执行删除

在使用外键约束时，操作子表，会先查询父表；操作父表时，也会先查询子表

检查约束（CHECK）

确保列中数据符合特定条件

drop table if exists employee(

id int primary key,

salary decimal(10, 2),

check(salary > 0)

);

但 MySQL 8.0 之前的版本都不支持 CHECK 约束功能，虽然在使用时不会报错，但是在解析时会忽略该约束

表的设计

要设计出一张表，我们需要：

（1）根据需求，找到实体

（2）梳理清楚实体之间的关系

首先，需要确定一些关键性质的对象，梳理清楚需求，提取出需要的实体，再确定多个实体之间的关系，不同的关系有不同的设计表方式

主要有三种关系：

一对一

一对多

多对多

一对一

表A的一条记录只能对应表B的一条记录；表B的一条记录只能对应表A的一条记录

例如，一个用户只能有一个用户配置，一个用户配置也只能对应一个用户

create table users (
user_id int primary key auto_increment,
username varchar(50) not null unique
);

create table user_profiles (
profile_id int primary key auto_increment,
user_id int unique, -- 设定为 UNIQUE 以确保一对一关系
bio text,
foreign key (user_id) references users(user_id)
);

user_profiles 中 user_id 是唯一的，保证了每个用户只能有一个配置

一对多

表A中的一条记录，可以对应表B的多条数据；而表B的一条数据，只能对应表A中的一条记录

例如，一个用户可以有多份订单；但一份订单只能对应一个用户

当存在一对多关系时，需要给多（订单表）的加上外键

create table users (
user_id int primary key auto_increment,
username varchar(50) not null unqique
);

CREATE TABLE orders (
order_id int primary key auto_increment,
user_id int,
order_date date,
foreign key (user_id) references users(user_id)
);

orders 表中的 user_id 列与 users 表中的 user_id 列形成外键关系

多对多

表A中的一条记录，可以对应表B中的多条记录；表B中的一条记录，也可以对应表A中的多条记录

例如，一个学生可以选多门课程，一门课程有多名学生

当存在多对多的关系时，需要涉及到第三张关系表的设计，而关系表就存放了多对多关系两张表的外键

create table students (
student_id int primary key auto_increment,
student_name varchar(50) not null
);

create table courses (
course_id int primary key auto_increment,
course_name varchar(50) not null
);

create table student_courses (
student_id int ,
course_id int ,
foreign key (student_id, course_id),
foreign key (student_id) references students(student_id),
foreign key (course_id) references courses(course_id)
);

student_courses 作为关联表，将 students 和 courses 表连接起来。通过组合 student_id 和 course_id 列的主键，实现多对多关系

即：

关系	实现
一对一	在子表中使用唯一外键（两张表中任选一张作为子表）
一对多	在子表中使用外键（多的作为子表）
多对多	使用关联表，将两张表连接起来

查询

聚合查询

在之前的文章中，我们学习了带有表达式的查询

如：

select name, chinese + math + english from exam_result;

chinese + math + english 是对三个列进行了计算，也就是列和列之间进行运算

那么，能否对行和行之间进行运算呢？

聚合查询，就是在进行行和行之间的运算

我们来看常见的聚合函数：

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

我们通过具体的例子来进一步学习聚合函数：

先创建一张学生表：

drop table if exists student;

create table student(

id int,

name varchar(10)

) ;

向表中插入数据：

insert into student values (1, "一一"), (2, "二二"), (3, "三三"), (4, "四四");

插入成功：

COUNT()

我们使用 COUNT() 来查询数据数量：

select count(*) from student;

上述 select count(*) from student 相当于：

（1）执行 select * from student

（2）使用 count() 计算结果行数

我们可以看到，使用 count 计算的结果与 select * from student

结果相同，在这里，不是通过聚合函数 count 获取到的，但数据只是在客户端方便看到，若我们后续进行编程，还是需要通过聚合函数来查询数量，且使用聚合函数还能够进行一些条件判断

我们再来对 name 的数量进行查询：

select count(name) from student;

查询结果与使用 count(*) 时相同

那么，count(*) 和 count(name) 以及 count(id) 的结果都是相同的吗？

再向表中插入数据：

insert into student values (5, null);

此时再使用 count(*) 和 count(name) 进行查询

此时结果并不相同，count(name) 的结果比 count(*) 的结果少 1

在未插入新数据之前，两者查询的结果相同，都为 4，在新插入数据 (5, null) 后，count(*) 的结果为 5， count(name) 的结果为 4

观察新插入的数据，name 值为 null, 此时 count(name) 并未将 null 值计入计数

我们再新增一条数据：

insert into student values (null, null);

新插入的数据所有字段都为null，此时count(*) 会将这条数据计入吗？

新增的数据被计入，即，使用 count(*) 时，即使是全 null 的行，也会被记录

接着，我们来看 sum() 和 avg()

SUM() 和 AVG()

sum() 用于计算查询到的数据的总和，且若查询到的数据不是数字，则没有意义

我们首先创建一张成绩表：

drop table if exists exam_result;

create table exam_result(

id int primary key auto_increment,

name varchar(10),

class int,

chinese int,

math int,

english int

);

向表中插入数据：

insert into exam_result (name, class, chinese, math, english) values

        ("一一", 1, 89, 60, 79),

        ("二二", 1, 60, 84, 93),

        ("三三", 2, 89, 95, 83),

        ("四四", 2, 86, 79, 92),

("五五", 2, null, null, null);

数据插入成功：

我们使用 sum() 计算所有学生的语文成绩：

select sum(chinese) from exam_result;

sum(chinese) 会将 chinese 这一列的所有值都加起来，若出现了 null，则直接忽略

sum() 中也可以使用表达式

如：我们查询所有学生所有成绩的总和

select sum(chinese + math + english) from exam_result;

若我们使用 sum() 计算非数字字段，结果是怎样的呢？

我们查询 name 的总和：

select sum(name) from exam_result;

此时出现了警告，计算结果为0，我们来查看 warnings：

show warnings;

SQL 有时会将字符串当作数字进行算术运算，此时会尝试将字符串转换为数字，而上述这些名字，无法转成数字，就报警告了

我们新增数据：

insert into exam_result (name) values (10);

此时再使用 sum(name)：

此时结果为10

字符串 10 能够转换为数字 10，因此也就可以进行求和运算，结果也就为 10

我们再来看 avg()

avg() 用于求数据的平均值，用法和 sum() 类似

求所有同学的数学成绩平均值：

select avg(math) from exam_result;

求所有同学，所有成绩的平均值：

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

接下来，我们再来看 MAX() 和 MIN()

MAX() 和 MIN()

max() 用来求数据中的最大值，min() 用来求数据中的最小值

例如：

求所有同学中英语成绩的最大值：

select max(english) from exam_result;

查询英语成绩最高的同学：

select name, max(english) from exam_result;

但是，此时的名字却不正确：

为什么会出现这样的情况呢？

这是因为在使用聚合函数的时候，列和列之间是互不相关的

正常情况下，一行数据，每个列都是对应的，共同构成一条记录，但若查询中包含聚合函数和非聚合函数时，此时就会各自是各自的

因此，聚合的列和非聚合的列，不能在一起配合使用，但是可以使用 group by 将它们聚合起来（我们后续在进行学习）

那我们此时要想查出英语成绩最高的同学，该怎么办呢？

我们可以使用 order by 对英语成绩进行降序排序，再使用 limit 取出英语成绩最高的同学：

select name, english from exam_result order by english desc limit 1;

同样的，我们也可以查出英语成绩最低的同学

select name, english from exam_result order by english limit 1;

但是，由于存在 null 数据，此时 null 才是最小值

可以使用 where 进行限制：

select name, english from exam_result where english is not null order by english limit 1;

分组查询（GROUP BY）

在上述查询中，我们将整张表作为整体，进行查询，但有的情况下，我们需要将所有行分为若干组，每个组分别进行聚合

例如：

在之前我们进行查询时，都是以所有学生为一个整体，查询所有学生中语文成绩的最小值、数学成绩的最大值...

但是，有时我们需要以班级作为整体，查询一个班级中语文成绩的最小值...

此时，我们就需要按照班级将表中的数据进行划分，将班级相同的数据聚合在一起：

此时就需要使用 group by + 分组依据字段，通过班级进行分组，即 group by class

查询每个班级中语文成绩的最大值：

select class, max(chinese) from exam_result group by class;

此时，是按照 class 进行分组的，在查询时，可以查询 class（因为每一组里的 class 值都是相同的），但若查询 id 或 name 就没有意义

例如：

select id, sum(chinese) from exam_result group by class;

分组查询时，也可以使用条件针对查询结果进行条件筛选：

分组之前，使用 where 进行筛选

分组之后，使用 having 进行筛选

例如：

查询每个班级语文的平均分，但除去班级为空的数据

班级为空，这个条件不必分组就可以得到，因此，使用 where 在分组之前将其除去

select class, avg(chinese) from exam_result where class is not null group by class;

查询每个班级的平均分，但除去语文平均分低于80的班级

平均分，需要分组后才能计算，因此，只能在分组后使用 having 进行筛选

select class, avg(chinese) from exam_result group by class having avg(chinese) >= 80;

分组操作，往往是和聚合一起配合使用的

联合（多表）查询

内连接

前面的查询，都是针对一张表中的数据进行查询的，但有的时候，数据可能会来自多张表，因此需要进行多表联合查询

而多表查询是对多张数据表取笛卡尔积

笛卡尔积是什么？

笛卡尔积（Cartesian Product）是数学中的一个概念，用于描述两个集合之间的所有可能的有序对，求两张表的笛卡尔集，就是对这两张表进行"排列组合"，我们通过具体的例子来看：

现在有一张学生表：

一张班级表：

求这两张表的笛卡尔积：

笛卡尔集，得到的是一个更大的表

其列数是之前两张表的列表之和

其行数是之前两张表的行数之积

若是三张表 A、B 和 C 计算笛卡尔积，此时先计算 A 和 B 的笛卡尔积，计算出来的结果，再和 C 一起计算笛卡尔积，更多的表也是如此两两进行计算

而进行多表查询，核心操作就是进行笛卡尔积

当需要使用两张表进行联合查询时，先将计算这两张表的笛卡尔积，再通过指定一定的条件，来获取需要的查询结果

我们通过具体的例子来学习多表查询：

创建一张学生表：

drop table if exists student;

create table student(

id int primary key auto_increment,

name varchar(10),

class_id int

);

向表中插入数据：

insert into student (name, class_id) values ("张三", 1), ("李四", 1), ("王五", 2);

创建一张班级表：

drop table if exists class;

create table class(

class_id int primary key auto_increment,

class_name varchar(10)

);

向表中插入数据：

insert into class (class_name) values ("一班"), ("二班");

SQL 进行笛卡尔积：

select .. from table_name1, table_name2;

即：

select * from student, class;

我们可以看到，表中出现了无效的数据，即学生表中班级id 和班级表中班级id 不匹配的数据

要让查询出来的学生表中班级id 和班级表中班级id 匹配，需要使用 where 条件进行约束

select * from student, class where student.class_id = class.class_id;

student.class_id = class.class_id 也就是将两张表连接在一起的连接条件

因此，当我们再进行多表查询时，一定要确保这两张表有一定的关联关系（至少有一个列是有关联的）

在进行多表查询时：

（1）计算两张表的笛卡尔积

（2）确定两张表的连接条件

（3）根据需求指定其他条件

（4）针对列进行精简（使用聚合函数或表达式进行计算）

也可以使用：

select columns from table1 [inner join] table2 on table1.column = table2.column;

查询学生表和班级表：

select * from student inner join class on student.class_id= class.class_id;

select columns from table1 [inner join] table2 on 连接条件 and 其他条件;

select columns from table1, table2 where 连接条件 and 其他条件

上述两种方式都可以用于进行多表查询，都称为内连接

什么是内连接呢？

内连接用于从两个或多个表中检索匹配的行，只有那些在连接条件下匹配的行会被返回

我们通过具体的例子来进行理解：

向学生表中插入一条数据：

insert into student (name, class_id) values ("赵六", 4);

再次进行查询：

由于新增数据中的 class_id 在 class 表中没有找到，不满足 student.class_id = class.class_id，因此，也就不会被查询出

接下来，我们来学习外连接

外连接

外连接分为左外连接、右外连接和完全外连接

左外连接：返回左表中的所有行以及右表中与左表行匹配的行，如果右表中没有匹配的行，结果中的右表列会填充 null

右外连接：返回右表中的所有行以及左表中与右表行匹配的行。如果左表中没有匹配的行，结果中的左表列会填充 null

完全外连接：返回两个表中所有的行，包括所有匹配的和不匹配的，MySQL 不直接支持完全外连接

左外连接语法：

select columns from table1 left join table2 on 连接条件;

右外连接语法：

select columns from table1 right join table2 on 连接条件;

我们先来看左外连接：

我们使用左外连接查询所有学生及其所在班级：

select * from student left join class on student.class_id = class.class_id;

此时，就会查询出 "赵六" 的数据，由于没有 class_id 为 4 的班级，因此，class 表中数据都使用 null 进行填充

我们来看右外连接：

向 class 表中插入数据：

insert into class (class_name) values ("三班");

查询所有班级以及学生：

select class.class_id, class.class_name, student.name from student right join class on class.class_id = student.class_id;

我们也可以使用右外连接查询查询所有学生及其所在班级，只需要将 class 表作为左表，student 表作为右表即可

select * from class right join student on class.class_id = student.class_id;

内连接、左外连接、右外连接以及完全外连接查询的数据范围如下图所示：

自连接

自连接：同一张表连接自身进行查询，也就是同一张表，自己和自己进行笛卡尔积

在进行条件查询时，条件是列和列之间的，但不能够给行和行之间指定条件，但是，我们有的时候需要对行和行之间指定一定的条件，此时，就可以通过自连接，将行之间的关系，转换为列之间的关系

同一张表自己对自己进行笛卡尔积，也就相当于有两张一模一样的表，此时我们该如何进行区分呢？

我们可以为这两张表分别取别名，从而进行上述内连接和外连接查询

语法：

select columns from table_name [as] 别名1, table_name [as] 别名2 where 连接条件;

select columns from table_name [as] 别名1[inner] join table_name [as] 别名2 on 连接条件;

select columns from table_name [as] 别名1 left join table_name [as] 别名2 on 连接条件;

select columns from table_name [as] 别名1 right join table_name [as] 别名2 on 连接条件;

我们通过一个具体的例子来理解：

我们创建一张员工表：

drop table if exists employees;

create table employees (

id int primary key auto_increment,

name varchar(10) unique,

manager_id int

);

向表中插入数据：

insert into employees (name, manager_id) values

        ("一一", null),

        ("二二", 1),

        ("三三", 1),

        ("四四", 2);

插入成功：

需要查询所有员工以及上级名字

此时，我们需要对不同行进行比较，即：

此时，我们就可以使用自连接，将行和行之间的关系转换成列与列之间的关系:

若 s1 的 manager_id 等于 s2 的 id，则表明，s2.name 是 s1 的上级名字，且我们需要查找所有员工的上级，即，无论该员工有没有上级，都应该进行查询，因此，我们使用 左外连接 进行查询：

select s1.id, s1.name, s1.manager_id, s2.name manager

from employees s1 left join employees s2 on s1.manager_id = s2.id;

子查询

子查询：嵌套在其他 SQL 语句中的 select 语句，也叫做嵌套语句

我们创建一张成绩表：

drop table if exists exam_result;

create table exam_result (

id int primary key auto_increment,

name varchar(10),

chinese int,

math int,

english int

);

向表中插入数据：