【MySQL#3】：数据库表的 CRUD 操作-CSDN博客

在这里插入图片描述

📃个人主页：island1314

⛺️ 欢迎关注：👍点赞 👂🏽留言 😍收藏 💞 💞 💞

生活总是不会一帆风顺，前进的道路也不会永远一马平川，如何面对挫折影响人生走向 – 《人民日报》

🔥 目录

一、insert

语法：

INSERT [INTO] table_name
    [(column [, column] ...)] #列字段
    VALUES (value_list) [, (value_list)] ... #列字段的内容
    
value_list: value, [, value] ...

案例：

-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    sn INT NOT NULL UNIQUE COMMENT '学号',
    name VARCHAR(20) NOT NULL,
    qq VARCHAR(20)
);

1. 单行数据 - 全列插入 + 指定列插入

指定列插入：列字段和列字段的内容一定要一一匹配

insert into student (sn, name, qq) values (123, '张飞', '12345');

全列插入：全列插入有两种方式，一个是省略 values 左侧字段名，一个是都指定

insert into students values (10, 124, '关羽', '13245');
insert into students (id, sn, name, qq) values (14, 125, '刘备', '14525');

可以省略 into

insert students (sn, name, qq) values (126, '诸葛亮', '12525');

2. 多行数据 - 全列插入 + 指定列插入

指定列多行插入

insert students (sn, name, qq) values (127, '曹操', '15256'), (128, '许攸', '23445');

全列多行插入

insert students values (20, 129, '孙权', '12256'), (21, 130, '吕布', '33445');

3. 插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败。

但我就是想让它先确认是不是在数据库中存在，不存在就插入，存在不要拦我然后执行后面的修改语句。

选择性的进行同步更新操作语法：

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

如果不存在就插入，存在发生主键或者唯一键冲突不要报错，接着执行后面的修改语句。

insert into students values (14, 111, '周瑜', '56321') on duplicate key update sn=111, name='周瑜', qq=56321;

注意更新的值不能和其他的主键和唯一键冲突，否则不能更新。

0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等
1 row affected：表中没有冲突数据，数据被插入
2 row affected：表中有冲突数据，并且数据已经被更新

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

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

4. 替换

主键或者唯一键没有冲突，则直接插入，如果冲突，则 删除后再插入（replace into）

mysql> insert into students values (22, 31,'Mike', '9856');
Query OK, 1 rows affected (0.00 sec)
mysql> select * from students;
+----+----+------+------+
| id | sn | name | qq   |
+----+----+------+------+
| 22 | 31 | Mike | 9856 |
+----+----+------+------+

mysql> REPLACE INTO students (sn, name, qq) values (31, 'Tom', '9856');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from students;
+----+----+------+------+
| id | sn | name | qq   |
+----+----+------+------+
| 23 | 31 | Tom  | 9856 |
+----+----+------+------+

1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，删除后重新插入
这里从 id 就可以看到是删除后插入的，因为 id 是自增的，刚才是 22，现在是 23 了。

二、Retrieve

语法：

SELECT
    [DISTINCT] {* | {column [, column] ...}
    [FROM table_name] # 从那个表筛选
    [WHERE ...] # 筛选条件
    [ORDER BY column [ASC | DESC], ...] # 对筛选结果排序
    LIMIT ... # 限定筛选出来的条数

distinct：对内容进行去重

*：全列查询
column，column…：指定列查询

案例：

-- 创建表结构
CREATE TABLE exam_result (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL COMMENT '同学姓名',
    chinese float DEFAULT 0.0 COMMENT '语文成绩',
    math float DEFAULT 0.0 COMMENT '数学成绩',
    english float DEFAULT 0.0 COMMENT '英语成绩'
);
 
-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
    ('唐三藏', 67, 98, 56),
    ('孙悟空', 87, 78, 77),
    ('猪悟能', 88, 98, 90),
    ('曹孟德', 82, 84, 67),
    ('刘玄德', 55, 85, 45),
    ('孙权', 70, 73, 78),
    ('宋公明', 75, 65, 30);

1. select 列

全列查询通常情况下不建议使用 * 进行全列查询查询的列越多，意味着需要传输的数据量越大；可能会影响到索引的使用

select * from exam_result;

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

select id, name, chinese from exam_result;

查询字段为表达式 select 非常特殊，后面可以跟 select 自带的子句，筛选条件等，也可以跟表达式

为查询结果 指定别名 语法：

SELECT column [AS] alias_name [...] FROM table_name; # 这里 as 可以不带

结果去重

select distinct math from exam_result;

2. where 条件

刚刚是对表的整体信息做筛选，但是实际在做查询的时候一定有筛选条件。
按条件筛选影响的是未来显示出来信息的条目数或者说是行数，以前是按列位单位把全部行都拿出来了。
如果一列想拿那些行由 where 条件来决定。

where 是筛选子句，后面可以跟特定的比较运算符来决策我们应该如何进行筛选，

where 就有点像C/C++里面的 if 语句，根据后面条件进行判断。

📚 比较运算符：

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

💡 注意事项

= 时两侧进行 null 值比较，是不能参与运算的
如果想判断 null 是否相等使用 <=>
不过一般也不这样去判断，一般更喜欢用 IS NULL 去判断一个值是否是null

📚 逻辑运算符：

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

具体案例

基于上面的建表，下面只会写出指令，最终结果为了节省篇幅就省略了

① 英语不及格的同学名字及英语成绩 ( < 60 )

select name, english from exam_result where english < 60;

**② **语文成绩在 [80, 90] 分的同学及语文成绩 使用 AND 进行条件连接

select name, chinese from exam_result where chinese >= 80 and chinese <= 90;

使用 BETWEEN … AND … 条件

select name, chinese from exam_result where chinese between 80 and 90;

③ 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩使用 OR 进行条件连接，满足任意一个就为真

select name, math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

使用 IN 条件，满足 () 里任意一个就为真

select name, math from exam_result where math in (58, 59, 98, 99);

姓孙的同学及孙某同学 有时候匹配并不给具体值的更细节的字段含义，可能就只给一个模糊搜索的关键字。就如上面。反正条件不给全就给一个模糊的条件。
我们就可以用 LIKE 模糊匹配 % 匹配任意多个（包括 0 个）任意字符注意 MySQL 可以用 ‘ ’ 或者 “ ” 表示字符串

select name from exam_result where name like '孙%';

_ 匹配严格的一个任意字符：

select name from exam_result where name like '孙_';

④ 语文成绩好于英语成绩的同学

select name, chinese, english from exam_result where chinese > english;

⑤ 总分在 200 分以下的同学

mysql> select name, math + chinese + english total from exam_result where math + chinese + english < 200;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+

# 但是写成这样就会有问题
mysql> select name, math + chinese + english as total from exam_result where total < 200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

为啥这里它报错了未知列total，我们不是做过重命名吗。这个total不是已经有了吗，怎么这里报不知道total呢？

解释如下：

很简单，我们一定是先执行 from ，在执行 where ，然后在执行 select
筛选后再执行，从 1 中带着 2 去3中筛选
**原因：**只把小于操作 2 的相加后再打印，更节省空间
所以不可以在 where 中使用重命名

⑥ 语文成绩 > 80 并且不姓孙的同学

AND 与 NOT 的使用

select name, chinese from exam_result where chinese>80 and name not like '孙%';

⑦ 孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

要么就是孙某同学，要么就得满足后面的一堆要求，总共就两个条件，在mysql如果条件很多可以用（）把这个条件括起来，表示一个单元

select name, chinese, math, english, chinese + math + english total 
from exam_result 
where name like '孙_' or (chinese + math + english > 200 and chinese < math and english > 80);

3. 结果排序 – order by

语法：

SELECT ... FROM table_name [WHERE ...]
    ORDER BY column [ASC|DESC], [...];

ASC 为升序（从小到大）(ascending)
DESC 为降序（从大到小）(descding)
默认为 ASC

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

【案例】

基于上面建的表来操作

yi同学及数学成绩，按数学成绩升序显示

select name, math from exam_result order by math asc;

注意：NULL 视为比任何值都小，升序出现在最上面

② 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

select name, math, english, chinese from exam_result order by math desc, english, chinese;

③ 查询同学及总分，由高到低

select name, chinese + math + english total from exam_result order by total desc;

欸，看到这里有个问题 ❓ 为什么在order by这里可以使用别名进行排序，而where后面没有办法使用别名？

因此可以得到一个结论：能不能用别名完全是取决于当前sql中子句的执行顺序！

你要对表结构的数据进行排序，一定是得先有数据！
有人可能说不是表结构不就天然有数据吗，因此就直接可以把表结构数据全排完，然后在选行不行，但我们肯定不会愿意这样浪费时间处理数据排序
因为你没有筛选，在排序的时候一定有大批数据其实是不需要排序的，而对这些数据排序本身就是浪费空间和时间，mysql没有这么笨！
所以会先筛选 select 再排序 order by

4. 筛选分页 – limit

什么是分页呢？

如果一个表中数据量太大，这个时候如果全列查询就有一大堆，这样不便于查看分析
有时候我们不想一次显示这么多，因此我们就可以对结果进行 LIMIT 分页。

limit 本身没有筛选功能，只是按照它后面跟的数字把要显示的结果按照 起始位置 和步长，给我们显示多条记录。

语法：

-- 起始下标为 0
 
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
 
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
 
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议： 对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

默认从 0 下标开始，筛选多条记录。
limit 分页读取的执行顺序，是在最后的
也可以指定下标开始筛选后面跟的是步长。从指定位置开始，连续读取多条记录。

如下：

mysql> select id, name, math from exam_result order by id limit 3 offset 0;
+----+-----------+------+
| id | name      | math |
+----+-----------+------+
|  1 | 唐三藏    |   98 |
|  2 | 孙悟空    |   78 |
|  3 | 猪悟能    |   98 |
+----+-----------+------+

mysql> select id, name, math from exam_result order by id limit 3 offset 6;
+----+-----------+------+
| id | name      | math |
+----+-----------+------+
|  7 | 宋公明    |   65 |
+----+-----------+------+

LIMIT 后面跟的是筛选几行，OFFSET 后面跟的是从那行开始。
limit 可以进行分页。就比如数据多就可以这样进行分页读

关键字执行顺序总结

**from > on> join > where > group by > with > having > select（**含重命名） > distinct > order by > limit

三、Update | Delete | 插入查询结果

3.1 Update

语法：

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

set 后面跟的是要重新设定的值，可以是多列。
一般在 update 的时候必须采用对应 where 子句进行条件筛选，如果没有的话会把这个表中指定的列全部都更新，这是不合理的。

【案例】：基于上面创建的 exam_result 表

对查询到的结果进行列值更新： set

① 将孙悟空同学的数学成绩变更为 80 分

update exam_result set math=80 where name='孙悟空';

② 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60, chinese=70 where name='曹孟德';

③ 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

更新值为原值基础上变更。
注意据更新，不支持 math += 30 这种语法。

④ 将所有同学的语文成绩更新为原来的 2 倍

update exam_result set chinese=chinese*2;

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

3.2 Delete

语法：

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

一般都是拿着条件删除 where
不加条件就是把整表的内容删除了，不过表结构还在。
删表结构drop

比如删除之前 exam_result 中的孙悟空的成绩，如下：

delete from exam_result where name='孙悟空';

我们再来个测试，测试表 如下：

-- 准备测试表
create table for_delete(id int primary key auto_increment, name varchar(20));
 
 
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

-- 查询表数据
select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+

现在我们可以看到id设置了自增，目前已经插入三条记录了，如果在插入一条记录这个id就是4了。

但我们现在不插直接把表删除，如下：

-- 删除表
delete from for_delete;

mysql> show create table for_delete \G;
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

-- 插入新数据
insert into for_delete(name) values('E');

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | E    |
+----+------+

当新插入一条记录时这个id是4，并且自增长已经变成下一个id值了

说明 delete from 清空表 的方式不会将 自增值置 0

清空表还有一种做法叫做 截断表。在效果和 delete 一模一样，但是在细节和原理是有差别的。

截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

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

【案例】：

mysql> select * from for_delete; # 操作前
+----+------+
| id | name |
+----+------+
|  4 | E    |
+----+------+

-- 插入
insert into for_delete(name) values('A'), ('B'), ('C');

-- truncate 操作
mysql> truncate for_delete;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into for_delete(name) values('E');
Query OK, 1 row affected (0.01 sec)

mysql> select * from for_delete; # 操作后
+----+------+
| id | name |
+----+------+
|  1 | E    |
+----+------+

可以看到表结构还在，但是内容已经被清空了。但是这里值得注意的一点是，之前自增长是5，现在 truncata 清空表后自增长已经没有了。
然后新插一条记录，id变成1了。自增长变成 2 了
换句话说 truncate 会重置自增长。而 delete 并不会。

实际上，TRUNCATE 和 DELETE 还存在一些差异。TRUNCATE 操作是直接将表中的数据清空，并且这个操作不通过事务处理。而 DELETE 和其他 SQL 操作则会在执行时被包装进事务中，再由 MySQL 处理。

事务的影响

事务的使用与否会影响 MySQL 对操作信息的记录方式。MySQL 使用其自身的 日志系统 来记录数据操作的信息，主要包括以下几种日志：

bin.log：存储经过优化的历史 SQL 语句。
redo.log：用于在 MySQL 遭遇宕机时能够恢复数据。
undo.log：用于存储回滚段信息。

日志的作用

bin.log 记录的是历史 SQL 语句，并支持 MySQL 的主从同步机制。当一个数据库执行完某些操作后，可以通过 bin.log 将这些 SQL 同步到另一个数据库，从而使两个数据库的数据保持一致（主从同步），需要注意，默认情况下 bin.log 是关闭的。
redo.log 在 MySQL 遇到故障时 提供数据恢复功能

持久化方式

持久化方式指的是为了能够在系统崩溃后快速恢复数据库数据的方法。

将数据以文件的形式写入磁盘，通常有两种方式

记录历史sql语句
记录数据本身

Truncate的特点

由于 TRUNCATE 不记录自己的操作到日志中，也不将其作为事务的一部分，因此它仅是简单地清空表中的数据，这样做的结果是 TRUNCATE 的执行速度较快。

TRUNCATE 因为其非事务性及不记录日志的特点，在执行速度上有优势
但在数据恢复和一致性方面不如 DELETE

3.3 插入查询结果

语法：

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

我们要插就插，要删就删，要改就改，要查就查，实际我们也可以将select和insert组合。可以把数据从其他表里面筛选出来，然后插入到另一个表里面。

我们来实现如下一个小实验：

删除表中的重复记录，重复的数据只能有一份，现在我们有如下的一个表：

mysql> select * from duplicate_t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | a    |
|    2 | b    |
+------+------+

我的做法是

创建一个和原表一样结构的空表 no_duplicate_t，
从原始表中把去重之后的结果筛选出来插入到 no_duplicate_t 这个表不就是不重复的嘛
然后对 duplicate_t 重命名，no_duplicate_t 改名字为 duplicate_t。最终不就完成了对 duplicate_t去重

mysql> create table no_duplicate_t like duplicate_t; # 创建完全一样的表，用 like
mysql> insert into no_duplicate_t select distinct * from duplicate_t; # 全列插入就不用指定列

-- 重命名
mysql> rename table duplicate_t to old_duplicate_t;
mysql> rename table no_duplicate_t to duplicate_t;

mysql> select * from duplicate_t; # 查询最终结果
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+

❓这里有个细节问题，为什么最后是通过 rename 方式进行的？

如果今天想把一个文件上传到 linux 上，比如这个文件是1G上传时间可能是10分钟，我们想把这个文件上传号之后放到一个目录下，并且要求它是为原子性方式放入的。

所以一般我们不能直接把文件上传到对应的目录下，因为它上传的过程一种在写入一定不是原子，它太慢了。

所以我们把这个文件上传到临时目标下，全部上传之后然后再把文件 move 到那个目录下。

直接 move 这个动作实际上是原子的，其实对一个文件进行重命名也是同一个道理

所以我们最后通过 rename 方式，就是单纯的想等一切都就绪了，然后统一放入，更新，生效等！和冗长的其他动作相比，这个动作非常轻。

在这里插入图片描述