【MySQL】表的CRUD{insert 替换 查询 运算符 语句执行顺序 排序 limit 分页 truncate截断表 去重 mysql日志 聚合函数}

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

1.重要点

insert – on duplicate key update

想插入 Jane 这位同学的数据，并不知道这位同学是否存在于这张表中，如果不存在就按照指定数据插入；存在将她的 stunum 更新为 1010：

insert into students(id, stunum, name) values(2, 1010, 'Jane') 
-> on duplicate key update stunum = 1010, name = 'Jane';

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

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

SELECT ROW_COUNT();
 +-------------+
 | ROW_COUNT() |
 +-------------+
 |           2 |
 +-------------+
 1 row in set (0.00 sec)
 -- ON DUPLICATE KEY 当发生重复key的时候

替换

主键或者唯一键没有冲突，则直接插入；主键 或者 唯一键 如果冲突，则删除后再插入。

查询

通常情况下不建议使用 * 进行全列查询

查询的列越多，意味着需要传输的数据量越大；
可能会影响到索引的使用。

查询内容为表达式

不等于null不安全

-- NULL 和 NULL 的比较，= 和 <=> 的区别 
SELECT NULL = NULL, NULL = 1, NULL = 0; 
+-------------+----------+----------+ 
| NULL = NULL | NULL = 1 | NULL = 0 | 
+-------------+----------+----------+ 
|        NULL |     NULL |     NULL | 
+-------------+----------+----------+ 
1 row in set (0.00 sec) 
SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0; 
+---------------+------------+------------+ 
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 | 
+---------------+------------+------------+ 
|             1 |          0 |          0 | 
+---------------+------------+------------+ 
1 row in set (0.00 sec) 

为查询结果指定别名

语法：SELECT column [AS] alias_name […] FROM table_name;

select id, name, chinese + math + english as 总分 from exam_result;  
-- as 可以加也可以不加

结果去重：

select distinct math from exam_result;

运算符

比较运算符

逻辑运算符

语句执行顺序

SQL查询中各个关键字的执行先后顺序

from > on> join > where > group by > with > having > select> distinct > order by > limit

distinct > order by > limit

1. from：从哪个表
2. where：从该表的哪些数据
3. group by：对这些数据进行分组
4. having：对去分组后的数据进一步筛选
5. select：选择某些列
6. order by：排序

执行where时 total根本不存在!

demo

成功样例

order by 与别名：只有数据准备好了 才会排序+显示

排序

-- ASC 为升序（从小到大） -- DESC 为降序（从大到小） -- 默认为 ASC 
SELECT ... FROM table_name [WHERE ...]  
ORDER BY column [ASC|DESC], [...]; 
注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序【无序查询 多次查询可能查到不同的结果】

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

SELECT name, qq FROM students ORDER BY qq; 
+-----------+-------+ 
| name      | qq    | 
+-----------+-------+ 
| 唐大师     | NULL  | 
| 孙仲谋     | NULL  | 
| 曹阿瞒     | NULL  | 
| 孙悟空     | 11111 | 
+-----------+-------+ 
4 rows in set (0.00 sec)

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

-- 多字段排序，排序优先级随书写顺序 
SELECT name, math, english, chinese FROM exam_result  
ORDER BY math DESC, english, chinese; 
+-----------+--------+--------+-------+ 
| name      | math | english | chinese | 
+-----------+--------+--------+-------+ 
| 唐三藏     |     98 |     56 |    67 | 
| 猪悟能     |     98 |     90 |    88 | 
| 刘玄德     |     85 |     45 |    55 | 
| 曹孟德     |     84 |     67 |    82 | 
| 孙悟空     |     78 |     77 |    87 | 
| 孙权       |     73 |     78 |    70 | 
| 宋公明     |     65 |     30 |    75 | 
+-----------+--------+--------+-------+ 
7 rows in set (0.00 sec)

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; 

对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

-- 第 1 页 
SELECT id, name, math, english, chinese FROM exam_result  
ORDER BY id LIMIT 3 OFFSET 0; 显示123
-- 第 2 页 
SELECT id, name, math, english, chinese FROM exam_result  
ORDER BY id LIMIT 3 OFFSET 3; 显示456
-- 第 3 页，如果结果不足 3 个，不会有影响 
SELECT id, name, math, english, chinese FROM exam_result  
ORDER BY id LIMIT 3 OFFSET 6; 显示7 89没有

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

-- 数据更新，不支持 math += 30 这种语法 
UPDATE exam_result SET math = math + 30 
ORDER BY chinese + math + english LIMIT 3; 

-- 删除整表数据 
DELETE FROM for_delete; 
Query OK, 3 rows affected (0.00 sec)

-- 再插入一条数据，自增 id 在原值上增长 
INSERT INTO for_delete (name) VALUES ('D'); 
Query OK, 1 row affected (0.00 sec) 
-- 查看数据 
SELECT * FROM for_delete; 
+----+------+ 
| id | name | 
+----+------+ 
|  4 | D    | 
+----+------+ 
1 row in set (0.00 sec)

-- 查看表结构，会有 AUTO_INCREMENT=n 项 
SHOW CREATE TABLE for_delete\G 
*************************** 1. row *************************** 
Table: for_delete 
Create Table: CREATE TABLE `for_delete` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(20) DEFAULT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

truncate截断表

这个操作慎用

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

-- 准备测试表 
CREATE TABLE for_truncate ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20) 
); 
Query OK, 0 rows affected (0.16 sec) -- 插入测试数据 
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C'); 
Query OK, 3 rows affected (1.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0 -- 查看测试数据 
SELECT * FROM for_truncate; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | A    | 
|  2 | B    | 
|  3 | C    | 
+----+------+ 
3 rows in set (0.00 sec)
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作 
TRUNCATE for_truncate; 
Query OK, 0 rows affected (0.10 sec) -- 查看删除结果 
SELECT * FROM for_truncate; 
Empty set (0.00 sec) 
-- 再插入一条数据，自增 id 重新增长 
INSERT INTO for_truncate (name) VALUES ('D'); 
Query OK, 1 row affected (0.00 sec) 
-- 查看数据 
SELECT * FROM for_truncate; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | D    | 
+----+------+ 
1 row in set (0.00 sec) 
-- 查看表结构，会有 AUTO_INCREMENT=2 项 【下一个id的值】
SHOW CREATE TABLE for_truncate\G 
*************************** 1. row *************************** 
Table: for_truncate 
Create Table: CREATE TABLE `for_truncate` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(20) DEFAULT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

去重

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

-- 创建原数据表 
CREATE TABLE duplicate_table (id int, name varchar(20)); 
Query OK, 0 rows affected (0.01 sec) -- 插入测试数据 

INSERT INTO duplicate_table VALUES 
(100, 'aaa'), 
(100, 'aaa'), 
(200, 'bbb'), 
(200, 'bbb'), 
(200, 'bbb'), 
(300, 'ccc'); 
Query OK, 6 rows affected (0.00 sec) 
Records: 6  Duplicates: 0  Warnings: 0 

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样 
CREATE TABLE no_duplicate_table LIKE duplicate_table; 
Query OK, 0 rows affected (0.00 sec) 

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table 
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table; 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 -- 通过重命名表，实现原子的去重操作 

RENAME TABLE duplicate_table TO old_duplicate_table,  
no_duplicate_table TO duplicate_table; 
Query OK, 0 rows affected (0.00 sec) -- 查看最终结果 

SELECT * FROM duplicate_table; 
+------+------+ 
| id   | name | 
+------+------+ 
|  100 | aaa  | 
|  200 | bbb  | 
|  300 | ccc  | 
+------+------+ 
3 rows in set (0.00 sec)

为什么最后是通过rename方式进行的?

就是单纯的想等一切都就绪了，然后统一放入、更新、生效等

2.mysql日志简介

MySQL中的binlog（二进制日志）、redolog（重做日志）和undolog（回滚日志）是三种重要的日志类型，它们在MySQL数据库的运行和维护中扮演着不同的角色。以下是对这三种日志的简要叙述：

1. binlog（二进制日志）

定义与作用：

binlog即binary log，是MySQL的二进制日志文件，也称为变更日志（update log）。它记录了所有更新数据库的语句（如DDL和DML语句），并以二进制的形式保存在磁盘中，但不包含没有修改任何数据的语句（如数据查询语句select、show等）。
binlog主要用于数据恢复和数据复制（如主从复制）。通过binlog，可以恢复某一时刻的误操作数据，也是实现MySQL数据备份、集群高可用、读写分离等功能的基础。
记录格式与写入时机：

binlog有三种记录格式：statement、row和mixed。

事务执行过程中，会先把日志写到binlog cache中去，事务提交时，才把binlog cache写到binlog文件中去（刷盘）。
binlog cache是为了保证一个事务的所有操作能够一次性写入binlog不被拆开而设置的缓存，其大小受binlog_cache_size参数控制。

写入策略：

sync_binlog参数控制binlog的写入策略。
sync_binlog=0：表示每次提交事务都只write，由操作系统自行判断什么时候执行fsync。
sync_binlog=N：表示每次提交事务都只write，积攒N个事务后才执行fsync。
sync_binlog=1（默认）：表示每次提交事务都会执行write和fsync，即将binlog数据持久化到磁盘中。

2. redolog（重做日志）

定义与作用：

redo log是MySQL中用于保证事务持久性的重要机制。当MySQL服务器意外崩溃或宕机后，它能帮助恢复已经提交的事务，确保数据不会丢失。
redo log是物理日志，记录的是在某个数据页上做了什么修改，属于InnoDB存储引擎层。

写入机制：

InnoDB以页为单位来管理存储空间，修改数据时会先将整个页加载到buffer pool中，然后对需要修改的记录进行修改。但修改后的数据不会立即刷新到磁盘，因为此时刷新是随机IO且效率低下。
因此，InnoDB引入了redo log，在修改数据后，不立即刷新磁盘，而是记录一条redo log，内容是关于哪个页面、多少偏移量、什么数据发生了什么变更。
redo log是循环写入固定的文件，顺序写入磁盘，提高了性能。

刷盘时机：

redo log的刷盘时机由innodb_flush_log_at_trx_commit参数控制。
设置为1时（最安全），每次事务提交都会将redo log写入磁盘。
设置为0时，由后台线程每秒刷新一次到磁盘。
设置为2时，事务提交时只写入操作系统的文件缓存，由操作系统决定何时刷新到磁盘。

3. undolog（回滚日志）

定义与作用：

undo log是MySQL中用于事务回滚和MVCC（多版本并发控制）的日志。它记录了事务回滚前的数据状态，当事务需要回滚时，可以通过undo log将数据恢复到事务开始前的状态。
同时，undo log也是MVCC实现的关键部分，用于保存数据的旧版本，以便支持非锁定读（如快照读）。

记录内容：

undo log只记录事务中的增删改操作，查询操作不会记录，因为查询不会修改数据。
undo日志段（undo log segment）是undo日志的集合，包含在回滚段（rollback segment）中。InnoDB支持多个回滚段，以提高并发性能。

作用：

事务回滚：保证事务的原子性，通过undo日志在事务需要回滚时，让数据恢复到最初的样子。
MVCC：作为MVCC版本链的一部分，存储老版本数据，支持非锁定读。

综上所述，binlog、redolog和undolog在MySQL中各自承担着不同的职责，共同保证了数据库的数据一致性和可靠性。

3.聚合函数

count(1)为什么也能显示5？因为select 1 的原因！

导入sql文件

mysql> source /home/sql_file/testdb data.sql;

group by

分组的目的是为了进行分组后，方便进行聚合统计。实际分组是用该列的不同的行数据来进行分组的！分组的条件deptno，组内一定是相同的! – 可以被聚合压缩
分组，不就是把一组按照条件拆成了多个组，进行各自组内的统计分组(“分表”)，不就是把一张表按照条件在逻辑上拆成了多个子表然后分别对各自的子表进行聚合统一！

不要单纯的认为，只有磁盘上表结构导入到mysql，真实存在的表，才叫做表
中间筛选出来的，包括最终结果，在我看来，全部都是逻辑上的表!“MySQL一切皆表

having 和 group by 配合使用，对 group by 结果进行过滤，having 经常和 group by 搭配使用，作用是对分组进行筛选，作用有些像 where.

having 是对聚合后的统计数据进行条件筛选。

其中 having 和 where 的区别在于：

执行的顺序不一样，where 是对任意列进行条件筛选，一般是最先执行；having 是对分组聚合后的结果进行条件筛选，一般是最后才执行