【MySQL】—— MySQL表的增删改查（CRUD）

前言

上一篇关于MySQL的文章，我们介绍了关于数据库、数据表的一些基本操作。今天，我们开始学习关于数据表里的内容的一些操作，也就是我们常说的“增删改查”、“CRUD”。

CRUD：Create（新增）、Retrieve（查询）、Update（更新）、Delete（删除）

前提

在开始关于数据表内容的操作前，我们一定要保证已经选中了对应的数据库并且创建好了对应的数据表！！！

一、新增

1. 单行插入

语法：

    insert into 表名 values（列，列，列……）；

括号里的列要和表定义时 个数、顺序、类型 保持一致

SQL中：字符串可以用单引号也可以用双引号

插入字符为中文，要确保数据库的字符集为utf-8

2. 指定列插入

语法：

    insert into 表名（指定列1，列2，…） values（值1，值2，… ）；

3. 多行插入

不指定列：

指定列：

可以看出，多行插入，只需要每一行的值之间用 “，”分隔开即可

当我们要一次性插入多个数据时：

一条sql语句插入多个数据的效率更高；多条语句，每条语句插入一个数据效率低

原因：

MySQL是客户端 - 服务器结构的程序，客户端和服务器之间使用网络进行通信

每执行一条sql语句，都会涉及客户端和服务器的交互和服务器对应的处理（检查语法、数据校验、对插入的数据的位置进行定位......）

一次性插入多个数据，虽然单次操作的消耗会多，但是网络开销、服务器开销都只有一次，更加高效并且效率差异明显。

题外话：

MySQL横向对比其他编程组件，执行效率更低，经常成为一个程序的性能短板，所以后续围绕MySQL有很多调优技巧

4. 时间日期的插入

简单写法：直接使用特定的格式化字符串表示时间：‘ 年 - 月 - 日 时：分：秒 ’

mysql会自动将其转换为 8个字节的时间戳（整数） 进行保存

查询时，也会将时间戳自动转换为格式化时间

插入当前时间：

    now（）函数——> 获取当前时间

二、查询

1. 全列查询

语法：

    select ***** from 表名；

“ * ” ：被称为通配符

在未来的工作中，select * 这个操作很危险，如果当前数据量很大，全列查询会产生很大的硬盘IO和网络IO，可能会导致服务器无法响应~

当然也有对应的解决方式：给服务器配置多个网卡，一部分用于传输数据，另一部分用于传输控制命令

2. 指定列查询

语法：

    select  列名，列名，…  from 表名；

这里的select后的列名要和表定义的一致，但顺序可以不同

3. 表达式查询

查询过程中，将查询的列代入表达式进行计算

注意：

• 表达式查询，并没有修改服务器硬盘上存储的数据本体，只是在查询结果的基础上进行运算得到一个“临时表”，当前查询操作结束，数据就销毁了；
• select操作得到的结果都是“临时表”，都不会改变数据本体；
• 查询出来的临时表每列的数据类型不再受限于原始的类型
• 表达式就是得到的临时表的列名
• 表达式只能对列进行计算，不能对行进行计算

4. 给查询结果设置别名

语法：

    select 表达式 **as** 别名 from 表名；

as可以省略，但是不推荐！！

也可以给表起别名

5. 对查询结果进行去重

语法：

    select **distinct** 列名 from 表名；

如果查询多个列，需要每一列的数据都相同才能去重

6. 对查询结果进行排序

语法：

    select 列名 from 表名 **order by 列名**；

“order by 列名” 中的列名相当于一个排序的规则，不一定得是被查询的列名

如果查询语句中没有order by 语句，那查询结果的顺序是未知的，虽然有时看起来有序！

例：按照语文成绩进行排序——默认是升序排序

降序排列：

    order by 列名 **desc**；  （desc——> descand（降序））

指定多个列进行排序：

优先级：越靠前优先级越高，优先级高的列值相同再比较优先级低的列

例：

先按数学升序排，数学相同再按语文升序排：

先按数学升序排，数学相同再按语文降序排：

先按数学降序排，数学相同再按语文降序排：

注意：

    NULL：看作比任何数据都小；

    关于每一列降序还是升序需要**单独指定**，**不写默认是按照此列升序排列；**

order by 后面的列也可以是表达式 / 别名

7. 条件查询

查询时，指定筛选的条件，满足条件的数据则保留作为结果集，不满足则跳过，最后返回满足条件的数据集

语法

    select 列名 from 表名 **where 比较条件**；

执行条件查询时，会“遍历整个表”（实际流程中，大概率不会遍历全部，可能会有其他实现方式提高效率），把每个记录带入条件中，看是否满足条件

比较运算符

条件运算符

注意：

• sql语句中的 “=” ，可以表示赋值，也能判断相等
• = 和 < = > 的区别： NULL表示当前单元格没填，而两者对于“没填是否等于没填”的判定有区别
• like：模糊匹配，不要求完全一样，只要满足一定的规则就算匹配

下面我们通过几个案例对以上运算符做了解

案例

比较运算符

列和常数比较：查找英语成绩小于六十的

列和列比较：查询语文比英语好的

使用表达式表示条件：查询三门主科总分小于200的

注意：

    where后的条件，**不能使用别名**

原因：定义的别名，不能被where获取到 ——> 和mysql执行顺序有关

mysql执行顺序：

1. 遍历表，取出一条数据
2. 把记录代入条件，判断是否满足（where判断）
3. 如果条件满足，再把select后指定的列读出来，并代入表达式计算（这一步才取别名）

先执行where操作，后执行的表达式运算，所以表达式运算时才取的别名，where不会接收到

可以在select的表达式中使用别名，但where只能用表达式

逻辑运算符

逻辑与：查询语文大于80且英语大于80的

逻辑或：查询语文大于80或者英语大于80的

and和or的优先级：AND优先级更高

如果对于优先级不熟悉：推荐多使用小括号显示地指定执行顺序

范围查询

指定区间查询：查询语文在80 - 90 之间的 [ 80 , 90 ]

注意：

    这里的区间是**闭区间**

    这两种写法本质上相同

指定集合查询：查询数学分数在这个集合里的

模糊匹配

通配符：

    % ：匹配任意个字符

    _  ：匹配一个字符

%：查询名字以孙开头的 ‘孙%’

以孙结尾：‘%孙’

包含孙：‘%孙%’

_ ：查询名字以孙开头，但是孙后面只有一个字符 ：孙_

孙后面两个字符：孙_ _

注意：MySQL不支持正则表达式

8. 分页查询

关键字：

** limit** ：限制这次请求返回多少个记录（一页有几条）

   ** offset**：限制从第几条开始算，不写offset默认是从0开始（从这一条的下一个开始）

优点：

• 可以限制一次查询最多查多少个记录，可以解决select* 在数据过多时可能会阻塞服务器的情况
• 分页

limit：

offset：

三、修改

语法：

    **update**   表名   **set**   列名 = 值   where 条件；

注意：

    where用于限定修改哪些行，不写就是默认所有行

    可以修改多个列

例：

修改一个列：

修改两个列：

给总分倒三的同学加30分：

先查询出来

修改：

执行顺序：先执行order by 和limit找到结果 再分别进行set

修改后的数据如果不符合表定义时的规则，就会报错

这里的85 + 30 = 115 就不符合 decimal（3，1）

这里考虑：85这一行添加失败，其他行是否成功？？？？

所有数据都没有变：一条sql语句是一个整体，要么都执行成功，要么都不执行

改为 + 10 分

四、删除

语法：

    **delete** from 表名 where 条件  / order by / limit ；

注意：

    不添加条件 ——> 删除所有记录

    but！！！ 删除所有记录 ≠ 删除表 删除表是将表本身和表内的数据都删除

本文使用的数据表的创建代码

大家可以复制以下代码进行测试

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
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);