【MySQL】表的增删查改

表的操作分为 新增(Create), 读取(Retrieve), 更新(Update), 删除(Delete), 简称 CRUD;
CRUD 是对表中的数据进行操作, 是典型的数据操纵语句 DML(Data Manipulation Language);

一. 表的插入

1. 单行插入

MySQL 中, 使用 insert 语句向表中插入数据

INSERT [INTO] table_name 
[(column [, column] ...)] 
VALUES (value_list) [, (value_list)] ...;

• column: 插入的字段名;
• value_list: (value [, value] …), 字段所对应的数据;

当全部的字段都插入时, 也可以省略字段名, 默认按照表中字段顺序全列插入;

INSERT [INTO] table_name 
VALUES (value_list) [, (value_list)] ...;

例:

向表中指定字段名插入数据;

向表中全列插入(省略字段名)数据, 注意插入数据个数和类型一定要和字段对应;

2. 多行插入

insert 语句同样支持多行插入, 插入的多行数据之间需要使用逗号隔开;

例:

多行插入同样支持全列插入和指定字段插入(若字段支持);

3. 插入否则更新

当插入主键或唯一键数据时, 可能会因为冲突导致插入失败; 可以根据场景选择性的进行同步更新操作;

INSERT ... ON DUPLICATE KEY UPDATE column1=value1 [, column2=value2] ...;

若插入主键或唯一键数据未冲突, 则正常插入; 若冲突, 则将冲突数据进行更新;

使用插入否则更新语句后, 受影响的数据行数会有三种情况:

• 0 rows affected: 表中有冲突数据, 但冲突的数据和更新的数据相同;
• 1 row affected: 表中没有冲突数据, 数据正常插入;
• 2 rows affected: 表中有冲突数据, 且冲突数据已更新;

例:

这里将 num1 字段设置为自增长主键, 正常插入重复的数据会失败;

使用插入否则更新语句的三种情况;

4. 替换

replace 替换语句, 可以插入或替换主键或唯一键冲突冲突数据, 类似插入否则更新语句;

REPLACE INTO table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ...;

若插入主键或唯一键数据未冲突, 则正常插入; 若冲突, 则将冲突数据行删除, 插入新的数据行;

例:

当插入重复的主键时, 会保留新的数据行;

二. 表的查询

MySQL 中, 使用 select 语句进行数据的查询;

SELECT [DISTINCT] {* | {column1 [, column2] ...}} FROM table_name [WHERE ...] [ORDER BY column [ASC|DESC], ...] [LIMIT ...];

• DISTINCT: 是否去重;
• * | {column1 [, column2] …}: 需要显示的字段数据, * 表示全列查询, 也可以指定字段查询;
• WHERE … : 条件语句, 接一个或多个条件, 只有满足条件的行数据为真, 才会显示;
• ORDER BY column [ASC|DESC], … : 是否排序, 可以将结果集根据指定的一个或多个字段进行排序;
• LIMIT: 限制结果集的显示行数;

1. 字段查询

使用 * 全列查询, 但通常不建议使用, 查询的字段越多, 需要传输的数据量越大, 可能会影响索引的使用;

select * from table_name;

也可以指定字段进行查询;

select column1 [, column2] ... from table_name;

selcet 语句也支持使用表达式表示字段;

并且可以为查询结果指定别名;

SELECT column [AS] alias_name [...] FROM table_name;

2. where 条件

where 子句用于指定查询条件, 只有满足条件的行数据才会出现在结果集中;

where 子句的主要目的是从数据表或查询结果中过滤出符合条件的行数据;

where 子句可以指明一个或多个条件, 条件之间使用逻辑运算符关联;

比较运算符	说明
<, <=, >, >=	小于, 小于等于, 大于, 大于等于
=	等于, NULL 不安全, 例如 若 NULL = NULL, 结果为 NULL
<=>	等于, NULL 安全, 例如 若NULL <=> NULL, 结果为 TRUE(1)
!=, <>	不等于, NULL 不安全
BETWEEN a AND b	范围匹配 [a, b], 若 a <= value <= b, 则返回 TRUE(1)
IN (option, ...)	若 value 等于任一 option , 则返回 TRUE(1)
IS NULL	判断是否为 NULL
IS NOT NULL	判断是否不为 NULL
LIKE	模糊匹配, % 表示任意数量的字符; _ 表示任意一个字符

逻辑运算符	说明
AND	逻辑与
OR	逻辑或
NOT	逻辑非

例:

全列显示;

显示主键为 [10,30] 的数据行;

显示主键为 1, 10, 20 的数据行;

显示主键不为空, 并且 num3 字段为空的数据行;

显示任意一字段包含数字 1 的数据行;

3. ORDER BY 排序

order by 排序, 可以根据指定的一个或多个字段进行升序或降序排序, asc 为升序, desc 为降序, 默认为升序排序;

当多个字段进行作为排序参数时, 只有当前字段的数据相同, 才会进行下一种字段的排序, 并且排序中可以使用别名;

例:

根据主键进行降序排序;

4. LIMIT 分页

limit 可以限制显示的结果集行数, 也可以用来分页显示;

// LIMIT 的下标从 0 开始// 从 0 下标开始, 向后筛选 n 条数据行
SELECT ... FROM table_name [WHERE ..] [ORDER BY ...] LIMIT n;// 从 s 下标开始, 向后筛选 n 条数据行
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;// 从 s 下标开始, 向后筛选 n 条数据行
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

例:

分页显示 t1 表, 每页两行数据;

三. 表的更新

MySQL 中, 可以使用 update 语句修改表中的数据, update 同样支持 where 条件, order by, limit 选项;

UPDATE table_name SET column1=expr1 [, column2=expr2] ... [WHERE ...] [ORDER BY ...] [LIMIT ...];

例:

将 num3 中的 null 修改为 30;

以主键降序排序, 将第一行的所有数据加一;

将表中所有的 num3 的数据 翻倍;

四. 表的删除

1. 删除表数据

MySQL 中, 可以使用 delete 语句删除表中的数据行, 同样支持 where 条件, order by, limit 选项;

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

例:

删除主键为 21 的数据行;

删除以主键降序排序, 第一行的数据;

删除整张表的数据, 但并不会删除表和表的结构;

2. 截断表

截断语句类似删除这张表, 可以清空表的内容;

TRUNCATE [TABLE] table_name;

和删除的不同:

• truncate 只能对整表进行操作;
• truncate 实际上并没有对数据进行操作, 不经过真正的事务, 所以无法回滚, 比 delete 操作更快;
• truncate 会重置 AUTO_INCREMENT 记录的数据;

例:

截断表后, AUTO_INCREMENT 的数据重置为了 1;

五. 插入查询结果

MySQL 支持从查询结果中插入数据;

INSERT [INTO] table_name [(column1 [, column2] ...)] SELECT ... [WHERE ...] [ORDER BY ...] [LIMIT ...];

例:

将 t1 表的主键值 插入至 t2 表中;