MySQL：表的增删查改

宁睿 · 2024-12-9 11:09:01

目录
一. Create
1.1 单行数据+全列插入
1.2 多行数据+全列插入
1.3 插入否则更新
1.3.1 要插入的值与原数据有辩说则更新
1.3.2 要插入的值与原数据没有辩说则正常插入
1.3.3 要插入的值与原数据有辩说但是连续更新了两次
1.4 替换
二. Retrieve
2.1 select 列
2.1.1 全列查询
2.1.2 指定列查询
2.1.3 查询表达式
2.1.4 查询效果重定名
2.1.5 对查询效果去重
2.2 where条件
2.3 效果排序
2.4 筛选分页效果
三. Update
四. Delete
4.1 删除数据
4.2 截断表
五. 插入查询效果
六. 聚合函数
七. group by子句的利用

一. Create

语法：

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

复制代码

1.1 单行数据+全列插入

单行指定列插入：

单行全列插入：

into也可以省略：

1.2 多行数据+全列插入

多行全列插入：

多行指定列插入：

1.3 插入否则更新

我们知道，假如发生主键大概唯一键辩说的时间，会报错。但是假如就是想要修改原来的数据呢？可以选择性的进行同步更新操作语法。
语法：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

复制代码

1.3.1 要插入的值与原数据有辩说则更新

而对于 x rows affected有下面的表明：

-- 0 row affected: 表中有辩说数据，但辩说数据的值和 update 的值相等
-- 1 row affected: 表中没有辩说数据，数据被插入
-- 2 row affected: 表中有辩说数据，而且数据已经被更新

通过 MySQL 函数获取受到影响的数据行数：

SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+

复制代码

1.3.2 要插入的值与原数据没有辩说则正常插入

假如要插入的值在原数据中并没有，则相当于正常插入语句。

1.3.3 要插入的值与原数据有辩说但是连续更新了两次

假如我们想更新原数据，但是连着运行了两次命令，则第一次正常修改，第二次并不会改变什么。

1.4 替换

-- 主键或者唯一键没有冲突，则直接插入；
-- 主键或者唯一键如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

复制代码

而且可以看到id是变了的。
二. Retrieve

语法：

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

复制代码

2.1 select 列

2.1.1 全列查询

//通常情况下不建议利用*进行全列查询
  //1.查询的列越多，意味着需要传输的数据量越大
  //2.大概会影响索引的利用

2.1.2 指定列查询

指定列的查询不需要按定义表的顺序来。

2.1.3 查询表达式

select背面也可以接表达式。

2.1.4 查询效果重定名

语法：

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

复制代码

也可以省略as：

2.1.5 对查询效果去重

利用关键字distinct去重：

2.2 where条件

比较运算符：

运算符	说明
>，>=，	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的效果是 NULL
<=>	等于，NULL 安全，例如 NULL NULL 的效果是 TRUE(1)
!=，<>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，假如 a0
IN (option, ...)	假如是 option 中的恣意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	含糊匹配。% 表示恣意多个（包括 0 个）恣意字符；_ 表示恣意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，效果才是 TRUE(1)
OR	恣意一个条件为 TRUE(1), 效果为 TRUE(1)
NOT	条件为 TRUE(1)，效果为 FALSE(0)

例子一：英语不及格的同学及英语结果 ( < 60 )

例子二：语文结果在 [80, 90] 分的同学及语文结果

例子三：数学结果是 58 大概 59 大概 98 大概 99 分的同学及数学结果

例子四：姓孙的同学及孙某同学

例子五：语文结果好于英语结果的同学

例子六：总分在 200 分以下的同学

而对于下面这种报错：

涉及到实行顺序的问题：

上面语句是按照ABC的顺序实行，所以在实行B的时间还没有total的定义，所以表现unknown。
而且不能再筛选条件处做重定名：

例子七：语文结果 > 80 而且不姓孙的同学

例子八：综合查询：孙某同学，否则要求总结果 > 200 而且语文结果 < 数学结果而且英语结果 > 80

例子九：查询NULL

''不等于NULL
2.3 效果排序

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

复制代码

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

例子一：同学及数学结果，按数学结果升序表现

默认是升序的。

例子二：同学及 qq 号，按 qq 号排序表现

假如有NULL，则NULL视作比任何值都小，升序出如今最上面，降序出如今最下面。

例子三：查询同学各门结果，依次按数学降序，英语升序，语文升序的方式表现（先按数学，数学相同按英语，英语再相同按语文）

例子四：查询同学及总分，由高到低

order by中可以利用表达式。

order by中可以利用列别名，这是区别于where的地方。
由于：

例子五：查询姓孙的同学大概姓曹的同学数学结果，效果按数学结果由高到低表现

2.4 筛选分页效果

语法：

-- 起始下标为 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;

复制代码

从0开始，筛选3条效果（不包括0）：

从1开始，筛选4条效果（不包括1）：

从2开始，筛选4条效果（这种更规范）：

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免由于表中数据过大，查询全表数据导致数据库卡死。
那么limit有什么用呢？可以进行简朴的分页操作。
例如：按id进行分页，每页3条记录，分别表现第1、2、3页。
第一页：

第二页：

第三页：

那么我们再来看看子语句的顺序：

三. Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

复制代码

对查询到的效果进行列值更新。意思就是先辈行查询，再进行列值更新。

例子一：将孙悟空同学的数学结果变更为 80 分

例子二：将曹孟德同学的数学结果变更为 60 分，语文结果变更为 70 分

例子三：将总结果倒数前三的 3 位同学的数学结果加上 30 分

例子四：将所有同学的语文结果更新为原来的 2 倍

注意：更新全表的语句慎用！！！

四. Delete

4.1 删除数据

语法：

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

复制代码

例子一：删除孙悟空同学的测验结果

也可以接order by，例如删除排名末了的一名：

例子二：删除整张表数据

注意：删除整表操作要慎用！！！
而且MySQL中，表分为表本身和表中的数据。删除整张表删除的是表中的数据，并不影响表结构，修改表结构用alter。

delete整张表并不会清空自增序列的值。

4.2 截断表

语法：

TRUNCATE [TABLE] table_name

复制代码

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部门数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时间，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

例子：

五. 插入查询效果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

复制代码

例子：删除表中的的重复复记录，重复的数据只能有一份

六. 聚合函数

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