史上最常用sql字典，纯语法，无案例，方便查阅
一、基础操作
-
查询所有数据库
show databases ; --mysql数据库 \l --opengauss数据库 -
查询当前数据库
select database() ; -
创建数据库(可以指定用户名)
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] [owner 用户名]; -
创建模式(在mysql中好像没有模式的概念，该语法是opengauss的语法)
create schema 模式名 authorization 用户名; -
查看某数据库中的所有模式
\dn --opengauss数据库 -
删除数据库
drop database [ if exists ] 数据库名 ; -
切换数据库
use 数据库名 ; \c 数据库名 --opengauss数据库 -
数据库重新命名
alter database 旧数据库名 rename to 新数据库名; -
查询当前数据库所有表
show tables; -
分析一条SQL查询语句的执行计划
EXPLAIN + 查询语句
EXPLAIN中各个字段的含义：(378条消息) sql性能分析之explain详解_sql的explain_不慕的博客-优快云博客
二、DDL
2.1、创建表
-
创建表结构
CREATE TABLE 表名( 字段1 字段1类型 [ COMMENT 字段1注释 ], 字段2 字段2类型 [COMMENT 字段2注释 ], 字段3 字段3类型 [COMMENT 字段3注释 ], ...... 字段n 字段n类型 [COMMENT 字段n注释 ] ) [ COMMENT 表注释 ] ;
2.2、修改表
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; -
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度); -
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; -
删除字段
ALTER TABLE 表名 DROP 字段名; -
修改表名
ALTER TABLE 表名 RENAME TO 新表名; -
删除表
DROP TABLE [ IF EXISTS ] 表名;
三、DML
-
给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); -
给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...); -
批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ; INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ; -
修改数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ; -
删除数据
DELETE FROM 表名 [ WHERE 条件 ] ;
四、DQL(单表查询)
-
查询语句（总览）
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
4.1基础查询
-
查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 SELECT * FROM 表名 -
字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名; -
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
4.2、条件查询
-
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN … AND … 在某个范围之内(含最小、最大值) IN(…) 在in之后的列表中的值，多选一 LIKE 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符，[ ]匹配中括号中的其中一个字符) IS NULL 是NULL
4.3、聚合查询
SELECT 聚合函数(字段列表) FROM 表名 ;
| 聚合函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
4.4、分组查询
SELECT 字段列表/聚合函数 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];
4.5、排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
4.6、分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
五、DCL
5.1、查询所有用户及其权限
-
select * from mysql.user; \du --opengauss数据库
5.2、创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; --方式一
create user 用户名 with password "密码"; --方式二
create user 用户名 password "******";
5.3、修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
5.4、删除用户
-
DROP USER '用户名'@'主机名' ; drop user 用户名 cascade;
5.5、查询某个用户的权限
SHOW GRANTS FOR '用户名'@'主机名' ;
5.6、授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
给用户授权对某数据库的所有权限
grant all privileges on database 数据库名 to 用户名;
为用户追加有创建角色的CREATEROLE权限
alter user 用户名 createrole;
将sysadmin权限授权给用户
grant all privileges to 用户名;
5.7、撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
5.8、切换用户
\c - 用户名 --opengauss数据库
六、完整性约束
6.1、一般约束
| 约束 | 语法 |
|---|---|
| 非空约束 | NOT NULL |
| 唯一约束 | UNIQUE |
| 主键约束 | PRIMARY KEY |
| 默认约束 | DEFAULT |
| 检查约束 | CHECK |
| 外键约束 | FOREIGN KEY |
6.2、外键约束(FOREIGN KEY)
- 建表时添加外键
CREATE TABLE 表名(
字段名 数据类型 [CONSTRAINT] [外键名称] REFERENCES 主表 (主表列名), -- 方式1
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) -- 方式2
);
-
后期添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ; -
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; -
外键在删除/更新时的行为
行为 说明 NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为 RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为 CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。 SET DEFAULT 父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持) 具体语法为：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) [ON UPDATE 行为] [ON DELETE 行为];
6.3、检查约束(用户自定义)(CHECK)
- 建表时
CREATE TABLE 表名(
字段名 字段类型 [CONSTRAINT] [检查约束名] check( ... ), -- 方式1
...
[CONSTRAINT] [检查约束名] check( ... ), -- 方式2
);
- 后期添加
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 check(...);
6.4、主键约束(PRIMARY KEY)
- 建表时
CREATE TABLE 表名(
字段名 字段类型 [CONSTRAINT] [主键名称] primary key, -- 方式1
...
[CONSTRAINT] [主键名称] primary key(字段1,字段2 ... ), -- 方式2
);
(注：主键可以由多个字段共同组成)
- 后期添加
ALTER TABLE 表名 ADD CONSTRAINT 主键名称 primary key(字段1，字段2 ... );
七、多表查询
7.1、内连接
-
隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ; -
显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;7.2、外连接
-
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ; -
右外连接(一般转换为左外链接)
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;7.3、自链接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ; (注：自链接中必须要为表起别名)7.4、联合查询
-
不去重（union all）
SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....; (加上ALL不会进行去重)
7.5、子查询（嵌套查询）
- 带有比较运算符的子查询
SELECT * FROM t1 WHERE column1 [ = > < >= <= <> ...] ( SELECT column1 FROM t2 );
(注：内层查询返回值必须为单个值！！！)
- 带有IN谓词的子查询
SELECT * FROM t1 WHERE column1 IN ( SELECT column1 FROM t2 );
(注：内层查询返回值可以为一列)
- 带有ANY(SOME)或ALL谓词的子查询
SELECT * FROM t1 WHERE column1 比较运算符 ANY/ALL ( SELECT column1 FROM t2 );
(注：内层查询返回值可以为一列)
-
带有EXISTS谓词的子查询
(带有EXISTS的内层查询值返回true或false)
SELECT * FROM t1 WHERE EXISTS ( SELECT column1 FROM t2 );
八、事务
8.1、查看/设置事务提交方式
SELECT @@autocommit ;
SET @@autocommit = 0 ; -- 设置为手动提交
8.2、提交事务
COMMIT;
8.3、回滚事务
ROLLBACK;
8.4、开启事务
START TRANSACTION 或 BEGIN ;
8.5、事务的四大特性
| 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 |
| 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。 |
| 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 |
| 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。 |
8.6、并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 同一个事务中，读到另外一个事务还没有提交的数据。 |
| 不可重复读 | 同一个事务中，先后读取同一条记录，但两次读取的数据不同（读到另外一个事务已经提交的数据），称之为不可重复读。 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影"。 |
| 丢失修改(脏写) | 两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失 |
严重性：脏写>脏读>不可重复读>幻读
8.7、事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(mysql默认) | × | × | √ |
| Serializable | × | × | × |
-
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION; -
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
8.8、事务与日志、锁的关系
- 事务的持久性由REDO日志来体现
- 事务的原子性和一致性由UNDO日志来体现
- 事务的隔离性由 锁机制 和 MVCC 来体现
九、索引
-
作用：优化存取路径，提高查询效率
-
分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
聚集索引和二级索引
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index)或辅助索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
- 创建索引
CREATE [UNIQUE][fulltext] INDEX <索引名> ON <表名>
(<列名>[次序]， <列名>[次序])
(注：在有些数据库中，聚集索引不能使用create cluster...来创建,mysql好像不支持聚集索引,opengauss创建聚集索引语法为: cluster <索引名> on 表名(<列名>[次序]); )
- 查看索引
SHOW INDEX FROM 表名 ;
- 删除索引
DROP INDEX <索引名> ON 表名 ;
DROP INDEX 表名.<索引名> [on 表名]; (有些数据库(如opengauss)不支持加上[on 表名])
- 修改索引名
ALTER INDEX index_name RENAME TO new_index_name; -- 修改索引名称
ALTER INDEX index_name RENAME COLUMN column_name TO new_column_name; -- 修改索引列名称
十、视图
-
作用：1、简化用户操作，2、对重构数据库提供了一定程度的逻辑独立性，3、对机密数据提供安全保护
-
创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
(注：创建视图时既可以根据表也可以根据其他视图)
- 查询
查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;
- 修改
方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
- 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
- 检查选项
WITH [CASCADED/LOCAL] CHECK OPTION
(注：在进行插入数据操作时，CASCADED会强制检查其所依赖的视图是否满足条件，
LOCAL先判断其所依赖的视图在创建时后面是否有检查选项，然后在判断时将插入的数据是否满足条件)
- 插入数据
INSERT INTO 视图名称 VALUES(字段列表 )
(视图必须为可更新视图)
十一、存储过程
- 创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;
- 调用
CALL 名称 ([ 参数 ]);
- 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指 定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义
- 删除
DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；
11.1、参数
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
11.2、变量
11.1.1 查看系统变量(分为全局和会话)
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方 式查找变量
SELECT @@[SESSION | GLOBAL].系统变量名; -- 查看指定变量的值
11.1.2 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
注意:
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。(mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。)
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。
11.1.3 用户自定义变量
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量
名" 使用就可以。其作用域为当前连接。
1)赋值
方式一：
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
方式二：
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
2)使用
SELECT @var_name ;
11.1.4、局部变量
局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的
局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。
1)声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
2)赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
11.3、if条件判断
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;
十二、函数
12.1、字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,…Sn) | 字符串拼接，将S1，S2，… Sn拼接成一个字符串 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
12.2、数值函数
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
12.3、日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
12.4、流程函数
12.5、用户自定义函数(存储函数)
- 创建
CREATE FUNCTION函数名（参数名 参数类型，···）
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 --函数体中肯定有 RETURN 语句
END
(注：function中默认是in类型的参数)
- 调用
SELECT 函数名(实参列表)
13、触发器
在mysql中只支持行级触发器，不支持语句级触发器
| 触发器类型 | NEW 和 OLD |
|---|---|
| INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
- 13.1、创建触发器方式一
CREATE TRIGGER 触发器名
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;
-
创建触发器方式二
CREATE TRIGGER 触发器名 BEFORE/AFTER INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW -- 行级触发器 EXECUTE PROCEDURE/FUNCTION 函数名/存储过程名; -
13.2、查看触发器
SHOW TRIGGERS ;
- 13.3、删除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数 据库 。
14、锁
14.1、锁的分类
- 写锁：排他锁又称为写锁。若事务T对数据对象A加上写锁，则只允许T读取和修改A,其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A(既不能读也不能写)。
- 读锁：共享锁又称为读锁。若事务T对数据对象A加上读锁，则事务T可以读A但不能修改A,其他事务只能再对A加读锁，而不能加X锁，直到T释放A上的读锁为止。这就保证了其他事务可以读A，但在T释放A上的读锁之前不能对A做任何修改。
- 读锁和写锁的兼容性问题：读锁和读锁之间可以兼容， 读锁和写锁、写锁和写锁之间不能兼容。
- 活锁：某一个事务/进程因为一直获取不到锁而进入无限等待的状态。
- 死锁：两个或多个事务/进程无限期地相互等待资源，从而导致所有事务/进程都无法继续执行的情况。(活锁和死锁是两种异常状态，解决办法详见操作系统)
- 乐观锁：对数据进行操作时，先不加锁，而是在提交操作时再检查数据是否被其他事务修改过。加锁的方式是版本控制机制，常用于更新操作。并发性较高。
- 悲观锁：
14.3、如何选择锁的粒度(见《数据库系统概论第五版》p321)
- 需要处理某个关系的大量元组的事务可以以关系为封锁粒度(表级锁)
- 需要处理多个关系的大量元组的事务可以以数据库为封锁粒度(全局锁)
- 只需要处理少量元组的用户事务，以元组为封锁粒度比较合适(行级锁)
14.2、全局锁
-
加全局锁
flush tables with read lock; -
释放锁
unlock tables; -
缺点：1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。2.如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。
14.3、表锁
-
加表读锁
lock tables 表名 read; -
加表写锁
lock tables 表名 write; -
释放锁
unlock tables;
14.4、元数据锁
- MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加.上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突,保证读写的正确性。
14.5、意向锁
- 为了避免DML在执行时，加的行锁与表锁的冲突, 在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查。
- 意向共享锁(IS) :由语句select … lock in share mode添加。与表锁共享锁(read)兼容，与表锁排它锁(write) 互斥。
- 意向排他锁 (IX) :由insert、 update、delete、select … for update添加。与表锁共享锁(read)及排它锁(write) 都互斥。意向锁之间不会互斥。
14.6、行级锁
15、日志
15.1、错误日志
查看日志位置
show variables like '%log_error%'
15.2、二进制日志
二进制日志(BINLOG) 记录了所有的DDL (数据定义语言)语句和DML (数据操纵语言)语句,但不包括数据查询(SELECT、 SHOW)语句。
作用：①.灾难时的数据恢复;②. MySQL的主从复制。
查看日志位置
show variables like '%log_bin%'
15.3、REDO日志和UNDO日志
-
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo logfile）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘, 发生错误时, 进行数据恢复使用。
-
回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制) 。
undo log和redo log记录物理日志不一样，它是逻辑日志。记录反操作，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。
本文是我大二学数据库系统原理这门课时的心得，写了好久的，可能不是很全，各位可以使用百度网盘下载笔记后自行修改。
本文旨在帮助各位程序员减轻写sql的负担😊😊😊😊😊
扫一扫
857
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
