MySQL 表的增删改查

什么是 CURD

CURD 是针对于数据库的四个核心操作。

C：create (增)
U：update (改)
R：retrieve (查)
D：delete (删)

一、增加数据

1. insert into 语句

-- 默认将此表中的所有列都插入数据
insert into 表名 values (列的值);

-- 往表中的部分列中插入值，列数要和待插入的值的数量对应起来
insert into 表名(列A，列B...) values (值A，值B...);

2. insert 语句注意事项

注意①： 在 values 后面括号中，插入列的个数与表头约定的列数及其对应的类型，需要匹配。

注意②： 在 SQL 语句中表示字符串的时候，我们可以使用双引号，也可以使用单引号。因为在 SQL语句中，没有单独的字符类型。(但还是建议实用单引号)

注意③： 如果某一行是 datatime 类型，即日期类型，此时我们应该如何插入呢？

方式一：通过指定格式的字符串来插入一个指定的时间。

' YYYY-MM-DD hh:mm:ss '
年-月-日  时:分:秒

方式二：通过 now( ) 函数来插入一个当前的系统时间。

注意④： 使用 insert into 语句进行插入的时候，同样也支持一次性插入多行。

insert into 表名 values (列的值), (列的值), (列的值)...;

( 一次插入 n 条记录的速度 ) 比 ( 一次插入一条记录，分 n 次插入) 要快好几倍，因为每次插入一条记录，客户端和服务器两者之间就要交互一次。

注意⑤： 如果需要插入中文，数据库可能会失败哦 ! 如果失败了，我们就可以将数据库给配置成中文（utf 8 编码方式 ），步骤如下：

步骤1：先查看当前数据库的编码方式，使用下面的 SQL 语句：

show variables like 'character%';

由于我的数据库的编码方式已经是 utf 8 编码方式了，当然，如果小伙伴的编码方式不是和我一样，也可以解决。

步骤2：在 windows 【 服务 】中，打开 MySQL属性，如下界面，并复制红色框框中的文件路径。这个路径就是 MySQL 对应的配置文件的位置所在 。

步骤3：用记事本打开红色框框中的 【my.ini】文件，并准备对其修改。注意，打开此文件时，我们需要备份一份，以防止修改出错的情况，这样我们还有挽回的余地 !!! 这在工作中，是一个良好的习惯 !

步骤4：配置文件中的一些语法

#：表示注释

[client] ：从这里往下，给客户端的配置项
[mysql]：从这里往下，客户端服务器都用到的配置项
[mysqld]：从这里往下，服务器的配置项

此时 我们需要改动两个地方，如下图的红色框框：

步骤5：最后两步：

• 重启 MySQL / 重启电脑。
• 把之前应用不了中文输入的数据库删了，重新创建新的数据库。

二、查询数据

查找语句是 SQL 中最核心也是最复杂的操作。

1. 全列查找

最基础的查找，全列查找，直接把一个表中的所有列和所有行都查询出来。下面为语法格式：

select * from 表名;
--其中 * 称为通配符，表示一个表中所有的列

通过上面的 select 语句，实查询的结果是一个 " 临时表 "，显然，这个表不是放在硬盘上，而是放在内存中，其在输出之后，数据就被释放了。

针对这个操作【 select * from 表名 】，是一个很危险的操作， 如果生产环境中保存的数据非常大，若是有几个 TB 级别，此时意味着 MySQL 服务器会疯狂地读取硬盘数据，而生产环境的服务器无时无刻地给用户提供响应，数据库瞬间就会崩坏 ! 那么会给用户带来严重的损失。

2. 指定列查找

指定列查找，顾名思义，只关注自己需要的列。相比于之前的全列查找，指定列查找的效率要高很多，用此方法也较为频繁。

select 列名，列名... from 表名;

-- 列名可以不管表中顺序，即可以乱序输出

同样地，此语句的查询结果，只是一个临时表。( 在客户端内临时保存的一个数据表，随着输出的进行，内存就被释放了。) 临时表的结果对于数据库服务器的原始数据没有任何影响。

3. 指定列查询为表达式

在查询的时候，我们甚至可以进行一些运算操作 ( 列和列之间 )，也就是说：针对指定列中的每一行数据都进行同样的运算。而行和行之间互不影响。

select 列名 (加减乘除) 列名... from 表名;

① 例如：我查询一个同学的数学成绩，我可以让其输出多 10 分。

② 例如：我可以查询一个同学的总成绩，这是通过表中几个列一起相加的结果。

注意上面两个例子，select 语句操作的结果依旧是 " 临时表 "，这里的查询结果与数据库的持久数据不同，只能说明，数据库服务器针对查询的结果进行了加工，把加工后的数据作为临时表显示出来了。

这里同样需要注意一件事情：临时表的结果的数据类型不一定和原始的表的数据类型完全一致。比方说，我一开始为各门成绩设定为 3位 有效数字，当 select 查询并结算成绩总和的时候，可以设定为 4位有效数字。

4. 查询列指定别名

此操作相当于是给查询结果的临时表，指定新的列名。

select 列名 (加减乘除) 列名... as 自定义列名 from 表名;

其中 as 可以省略，但不建议省略
as 不省略更不容易出错，代码可读性也更高

例如：我们把一位同学的成绩相加之和的名字作为 total，如下图所示：

5. 查询结果去重

查询结果去重，是针对查询的结果，把重复的记录去掉，例如：同一列有两份及两份以上一样的数据，那么输出的时候只显示一份。

注意：如果是针对多个列来进行去重，此时多个列的值都相同才会去重。

select distinct 列名，列名... from 表名;

在下面程序中，我们发现，当使用 【distinct id, name】的时候，并没有将 id 多余的 1 去除掉，因为当 id, name 都有重复的时候，才能去重。

而我们单独使用 【distinct id】的时候，可以发现能够去重。

6. 针对查询结果进行排序

select 列名... from 表名 order by 列名 asc/desc;

-- asc：升序(ascend)
-- desc：降序(descend)
--如果不加 asc/desc，那么默认为是升序

注意事项：

① 有的数据库记录中是带有 NULL 值的，那么 NULL 就是被认为是最小的（所以，升序排序数据时，NULL 就在最前面，降序排序数据时，NULL 就在最后面。）

② 排序时，也可以依据表达式或别名来进行排序。

③ 排序时可以指定多个列来进行排序，如果第一个列结果相同，相同结果之间再通过第二个列来进行排序。（需要明确优先级）
也就是说：如果有多个列进行排序，所有的列都基于第一列的结果再次排序。

如下图分析，如果一位同学的语文成绩相同了，那么在此情况下，再去排序数学成绩。

7. select 中的条件查询

select 列名 from 表名 where 条件;

在 C/Java 中 " == " 表示比较相等，
而在 SQL 中，" = " ， " <=> " 都表示比较相等。

其中，SQL 中有一个特殊的语法：

NULL = NULL
=> 结果仍然是 NULL
其中 NULL 被认为是假，条件不成立

/
NULL <=> NULL
=> 结果是 真

注意事项：

① where 条件会针对查询结果进行筛选，服务器会遍历表中的每一条记录，如果记录符合条件，就会返回给客户端；反之，就跳过。显然，这里 select 语句依旧是创建了临时表。

② 这里需要注意一点：比较两个列的时候，是针对同一行的不同列进行比较的，不涉及行和行之间的比较。

③ where 后面的条件和 select 后面的表名之间没有任何关系。我们可以只比较两行，但输出五行。

④ where 语句不能使用别名。

⑤ 表达式中若有 and 和 or 同时存在，先计算 and，再计算 or，如果想要打破优先级，就需要加 ( ) 。这和 C/Java 中的语法十分相似。

⑥ between…and… 语句和 in( ) 语句的用法

between - and 侧重于范围(闭合)，in( ) 语句侧重于精确某个值。

8. 模糊查询 Like

模糊查询 like 可以搭配通配符来使用，只要查找的字符串符合此处描述的某个形式，就能被检索出来。

select 列名 from 表名 where '... %或_ ...'

-- 通配符 % ：代表任意个任意字符（也包含 0 个字符）
-- 通配符 _  ：代表一个字符

(1)

① 在下图中，我们查询一个人的姓氏为孙，那么可以采取 ’ 孙%’ 的形式。

注意，此时我们只能查找到任意以 ’ 孙 ’ 开头的字符串。但是类似于 【 aaa 孙，bbb 孙 ccc】，系统检索不出来，因为我们事先已经给系统约定了一个规则。

② 如果是 ’ %孙% '，我们可以匹配到下列字符串

aaa孙
aaa孙bbb
孙aaa

(2)

通配符 _    仅代表一个字符
两个 _ _    代表两个字符

9. 查询 NULL

select 列名... from 表名 where 列名 <=> null; //某列值是空值
select 列名... from 表名 where 列名 is null;  //某列值是空值
select 列名... from 表名 where 列名 is not null; //某列值不是空值

当我们使用 " select 列名 from 表名 where 列名 = null " 这条语句时，对于 ’ 杰克 ’ 来说，Chinese 成绩确实是空值，但此时相当于 " null = null " ，被视为 false，所以输出空集。

这样一来，还是建议大家使用上面的三条正确语句。另外，在 SQL 语句中，不区分大小写，所以 NULL 可以写成 null.

10. 分页查询 limit

select 列名... from 表名 limit num;
select 列名... from 表名 limit num1 offset num2;

-- limit	限制查询的行数 num
-- offset	代表 num2 偏移 num1 的量

①

② limit 可以搭配其他的 SQL 语句，比方说查询一个班级里，分数排名的前三位同学的名字和分数。

③ 分页查询 及 limit 存在的意义：

上文提到，当我们使用【 select * from 表名; 】这条语句时，对于生产环境来说，是非常危险的 ! 因为当我们在服务器中的数据量巨大时，会让数据库崩溃。

那么，我们就可以使用 where 和 limit 来限制查找时的范围，这样一来，就会让此时的查找比全列查找轻松许多 !

三、修改数据

update 表名 set 列名 = 值... where 条件;

注意事项：

① 在进行修改操作时，我们最好加上 where 条件，因为这针对了指定哪些条件进行修改，若省略了 where，那么就是对所有行都进行了修改。如下所示：

② update 是会修改数据库服务器上面的原始数据的 !

所以说，此处的修改操作也是比较危险的，甚至比误删数据库来的更加危险，因为误删数据库可以通过备份的数据来恢复，而修改数据的时候，有些可能改对了，有些可能改错了，所以在工作中不好控制，程序员很难控制结果。

四、删除数据

delete from 表名 where 条件;

删除 null 的时候，我们需要使用 【 where 列名 is null 】

注意：如果我们使用 delete 的时候，没有在代码末尾加上 where 条件，那么它会删除整个表中的数据，但是表还在，剩余的就是一张空表，而这与 【 drop table 表名; 】不一样，drop 操作是把表和数据都删除了。

五、数据库的约束

约束就是数据库在使用的时候，对于里面能够存的数据提出的要求和限制，程序员就能借助约束来完成更好的校验。

1. default 默认值

约定 / 改变一个默认值。

2. not null 不为空

指定某列不能存储空值。

插入空值的时候，会提示报错 !

3. unique 唯一值

数据唯一，插入重复的数据，会报错。

4. primary key 主键

primary key 相当于是 not null 和 unique 两者的结合体。它相当于数据的唯一身份标识，类似于身份证号码 / 手机号码。

primary key 这个约束是我们日常开发的时候，最常用，也是最重要的约束 ! 创建表的时候，很多时候都需要用到主键。

注意事项：

对于一个表来说，只能有一个列被指定为主键。

关于主键，典型的用法，就是直接用 1，2，3，4 整数递增的方式来进行表示。
在MySQL 中，把这种递增的主键，称为【 自增主键 】。

id int primary key auto_increment

当设定好自增主键之后，此时插入的记录，就可以不指定自增主键的值了，(直接使用 null)，交给 MySQL 自动分配。

我们自己也可以指定自定义的值，但指定之后，会从我们指定的值之后开始递增。

5. foreign key 外键

foreign key (子表列名) references 主表(主表列名);

-- 上面代码一般写在某行代码最后

(1) 演示：我们创建两张表，一张表是 student，一张表是 class，我们使用外键的目的是将学生和班级对应起来。

表 class

RoomID		Name
1			普通班	
2			重点班
3			英才班
4			竞赛班

表 student 

StudentID	Name	Class
1 		    Jack 	3(英才班)
2 			Rose 	1(普通班)
3 			Ron 	2(重点班)

① 表 class 对应的 SQL：

 create table class ( 
 	 RoomID int primary key auto_increment, 
	 Name varchar(20) 
);

② 表 student 对应的 SQL：

create table student ( 
	studentID int primary key auto_increment, 
	Name varchar(20), 
	Class int, 
	foreign key (Class) references class(RoomID) 
	
	-- 表示 子表student 中的 [列Class] 引用 父表class 中的 [列RoomID] 
);

外键约束：描述的是两张表的两个列之间的 " 依赖关系 "，子表依赖于父表(子表引用于父表)，要求子表中对应的每一行必须在父表中存在。

(2) 一些细节

① 学生可以在不同的班级中，学生也可以在相同的班级中。但学生不能在未定义序号的班级中，所有的班级序号只包括了【1, 2, 3, 4】，你不能将学生放入第 20班中。

下面有插入成功的情况，也有插入失败的情况，插入失败，会报错 【 constraint fails 】，表示约束失败。

② 我们可以改变学生的班级，当然学生的班级依旧需要对应父表中班级存在的序号 !

③ 外键约束同样约束父表，当父表中的某一行被子表中依赖着的时候，此时进行删除或修改操作，都会失败。当然，直接删除班级表，也会发生失败哦 !

另外，我们可以删除子表都不实现父表的记录，比方说：所有的学生都在【1, 2, 3, 4】班，而现在我们新创建了一个 5班，但是 5班没有学生，我们就可以删除 5班。

(3) 外键约束的工作原理：

在子表中插入新的记录时，就会先根据对应的值，在父表中先查询，查询到之后，才能够执行后续的插入。这里的查询操作，可能是一个成本较高的操作( 比较耗时 )。

外键约束要求：父表中被依赖的这一列，必须要有索引，有了索引就能大大地提高查询速度。 而在上面的 class 表中的 RoomID 这一列，若是 primary key 或 unique，就会自动创建出索引了。

六、查询后新增

insert into B select 列名，列名... from A;

理解代码含义：将从A 表中查询的临时表中的数据插入至B 表中，insert 插入的时候依旧要符合B 表中的数据类型。

七、聚合查询

聚合函数

1. count( ) 函数

create table exam (

    ID int,
    Name varchar(20),
    Chinese decimal(3,1),
    Math decimal(3,1),
    English decimal(3,1)

);

insert into exam 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, null, 75, 95.5, 73),
    (7,'罗恩', 70, 73, 78.5);
    

利用 count 函数的时候，当数据为 null 的时候，不计入结果。在上述中名字有 7 个数据，但是有一个数据为 null，所以输出 6.

此外，使用聚合函数的时候也可以加上 where 条件。形如如下操作，会先执行条件筛选，然后再执行聚合。

2. sum( ) 函数

sum( ) 函数 对于数据的求和如下，它十分类似于 Excel 表的操作。

这里注意：sum( ) 函数对字符串进行操作时，显示无效。

3. group by

create table company(

    id int primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary numeric(11,2)

);

insert into company(name, role, salary) values

    ('Jack','项目负责人', 1800.20),
    ('Ron','项目负责人', 2000.99),
    ('Rose','员工', 999.11),
    ('George','董事长', 3333333.5),
    ('Bruce','员工', 700.33),
    ('Lin','总经理', 12000.66),
    ('Chen','员工', 888.0);
    

举个例子，我们现在对一家公司的职位和对应的薪资做一个调查，我们以 role 来将公司职位进行分组，然后通过聚合函数【max( ), min( ) ,avg( )】来显示一些数据。

在下面的例子中，我们需要明确：程序先执行 group by 分组，再根据分出来的组别来使用聚合函数。所以说：一个 SQL 语句的执行顺序，与其书写顺序并不完全一致。

注意 where 语句和 having 语句的区别：

where 语句在 【group by】的前面执行，having语句在 【group by】的后面执行。
如下图的例子

例子1：在通过 role 分组之前，我们先把董事长这个职位去掉。
例子2：在通过 role 分组之后，我们找出平均薪水大于 2000 的数据。

八、其他的查询

1. 自连接

自连接的关键在于：通过自己与自己进行笛卡尔积运算，能够将行转换成列。

【显示所有 " 计算机原理 " 比 “Java” 成绩高的数据。】

如下图，我们比较的是行与行，而以往我们比较的是列与列。所以，我们只能先将行转换成列。

步骤(1)

让 score与自己形成自连接，但由于自连接之后的这张表是 两张相同的 score 表形成的，所以我们必须使用指定别名操作。

步骤(2)

设置条件，将左表都设置成 " 计算机原理 " 的成绩。
将右表都设置成 “Java” 的成绩。

步骤(3)

再次设置条件，将左表的成绩大于右表的成绩打印出来即可。

2. 子查询

学习目标：认识并了解即可

查询1：【单行子查询，查询与 " 许仙 " 同班的同学】

正常写法：

子查询写法：

查询2：【多行子查询 " 语文 " 或 " 英文 " 课程的成绩信息】

正常写法：

子查询写法：

总结： 子查询实际上就是一个套娃的过程，子查询完全可以分开来写，有时候直接一步到位会让代码可读性变得更差，且更容易出错。( 特别是当今时代，数据库的数据就是钱，钱就是数据，一切应该谨慎一些才好 )

3. 合并查询

学习目标：认识并了解即可

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

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

使用 union 时，需要两张合并查询后的临时表需要有相同的列数，否则会报错。