【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 表中;
在这里插入图片描述

相关推荐

  1. MySQL 增删(基础)

    2024-06-09 22:00:06       30 阅读
  2. 【数据库】MySQL增删

    2024-06-09 22:00:06       25 阅读

最近更新

  1. TCP协议是安全的吗?

    2024-06-09 22:00:06       8 阅读
  2. 【Python教程】压缩PDF文件大小

    2024-06-09 22:00:06       9 阅读
  3. 通过文章id递归查询所有评论(xml)

    2024-06-09 22:00:06       10 阅读

热门阅读

  1. 前端通用样式调整方法(整理版)

    2024-06-09 22:00:06       6 阅读
  2. web 前端开发培训:深入探索与实战应用

    2024-06-09 22:00:06       11 阅读
  3. 线程+线程池+锁

    2024-06-09 22:00:06       8 阅读
  4. 多维vector定义

    2024-06-09 22:00:06       6 阅读
  5. 计算各聚类中心

    2024-06-09 22:00:06       6 阅读
  6. 程序代码问题随时记录

    2024-06-09 22:00:06       7 阅读
  7. 栈和队列的转换

    2024-06-09 22:00:06       6 阅读
  8. 全面解析LG webOS:从开发到智能电视的演进

    2024-06-09 22:00:06       9 阅读
  9. 【CS.SE】Tomcat启动闪退问题解决方法

    2024-06-09 22:00:06       5 阅读