MySQL数据库

MySQL数据库应用

一.基础篇

1.概述

• 数据库(DB)：存储数据的仓库，数据是有组织的进行存储
• SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

操作关系型数据库时都会使用SQL.

[windows下MySQL的下载地址](MySQL :: Download MySQL Installer)

数据库的启动与停止

net start mysql80：启动

net stop mysql80：停止

MySQL数据库的客户端连接，在cmd中执行mysql -u root -p命令即可。注意在使用这种方法时，需要配置PATH环境变量。

MySQL自带的客户端命令行也可以。

数据模型

客户端 -> DBMS -> 数据库 -> 具体的表

后三项包括在数据库服务器内

• 关系型数据库(RDBMS)

  • 概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
  • 特点：表结构储存数据，格式统一，便于维护。使用SQL语言，标准统一，使用方便。

• 非关系型数据库

在一个数据库服务器中可以有多个数据库，在一个数据库中又可以创建多张表，基于表存储的数据库又称为关系型数据库。

2.SQL

SQL通用语法

1. 语句可以单行或者多行书写，结尾要有分号。

2. 语句可以使用缩进来增强可读性。

3. 语句不区分大小写，关键字建议使用大写。

4. 注释：

   • 单行注释

     使用两个连续的减号 -- 来注释从该符号开始到行末的内容。例如：

     -- 这是一个单行注释
     SELECT * FROM users; -- 这里也可以加注释
     

     使用 # 符号，这在某些SQL数据库系统中有效，如 MySQL。例如：

     # 这是一个单行注释
     SELECT * FROM users; # 这里也可以加注释
     
     

   • 多行注释

     使用 /* ... */ 来包含多行文本。这种注释可以跨越多行，但不能嵌套。例如：

     /* 这是一个多行注释
        它可以跨越多行
        直到遇到闭合的符号 */
     SELECT * FROM users;
     

SQL语句的分类

SQL（Structured Query Language）语句可以分为以下几类，每种类型的语句都有其特定的用途：

1. 数据定义语言（DDL - Data Definition Language）
   • CREATE：用于创建数据库中的新表、视图或其他对象。
   • ALTER：用于修改现有数据库结构，如添加、删除或修改表中的列。
   • DROP：用于删除数据库中的表、视图或其他对象。
   • TRUNCATE：用于删除表中的所有数据，但保留表结构。
2. 数据操纵语言（DML - Data Manipulation Language）
   • INSERT：用于向表中插入新的数据行。
   • UPDATE：用于修改表中的现有数据。
   • DELETE：用于从表中删除数据行。
3. 数据查询语言（DQL - Data Query Language）
   • SELECT：用于从数据库中检索数据。虽然技术上它是DML的一部分，但通常被视为一个单独的类别，因为它主要用于查询而不是修改数据。
4. 数据控制语言（DCL - Data Control Language）
   • GRANT：用于授予用户对数据库对象的权限。
   • REVOKE：用于撤销之前授予用户的权限。

以下是各类SQL语句的简要讲解：

DDL（数据定义语言）

数据库操作

• 查询

  查询所有数据库

  SHOW DATABASES;
  

  查询当前数据库

  SELECT DATABASE();
  

• 创建

  CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集][COLLATE 排序规则];
  

  例如：create database yjl default charset utf8mb4;，创建了一个名称为yjl的数据库，后面的部分设置了数据库的默认字符集为utf8mb4。utf8mb4是UTF-8的一个扩展，它能够支持存储所有Unicode字符，包括 emojis 和其他4字节字符。

  数据库的字符集（Character Set）是指数据库管理系统（DBMS）用于存储、处理和检索文本数据的一套规则和编码。

• 删除

  DROP DATABASE [IF EXISTS] 数据库名;
  

• 使用

  USE 数据库名;
  

表操作——查询

• 查询当前数据库所有表

  SHOW TABLES;
  

  如果当前数据库没有表，则会输出Empty set.

• 查询表结构

  DESC 表名;
  

• 查询指定表的建表语句

  SHOW CREATE TABLE 表名;
  

表操作——创建

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

注意：[...]为可选参数，最后一个字段后没有逗号。

举例：

mysql> create table tb_user(
    -> id int comment '编号',
    -> name varchar(50) comment '姓名',
    -> age int comment '年龄',
    -> gender varchar(1) comment '性别'
    -> ) comment '用户表';
    --  创建表
Query OK, 0 rows affected (0.06 sec)  --  创建成功

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_user        |
+----------------+
1 row in set (0.00 sec)  --  查询当前数据库所有表

mysql> desc tb_user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)  --  查询表结构

mysql> show create table tb_user;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table


                                            |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)  --  查询指定表的建表语句

表操作——数据类型

MySQL的数据类型有很多，主要分三类：数值类型、字符串类型、日期时间类型。

1. 数值类型

   类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
   TINYINT	1 byte	(-128，127)	(0，255)	小整数值
   SMALLINT	2 bytes	(-32768，32767)	(0，65535)	大整数值
   MEDIUMINT	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
   INT或INTEGER	4 bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
   BIGINT	8 bytes	(-263，263-1)	(0，2^64-1)	极大整数值
   FLOAT	4 bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
   DOUBLE	8 bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
   DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

​ 精度和标度：

​ 123.45的精度为5，标度为2.

​ 无符号：

​ age TINYINT UNSIGNED：年龄不许要负数，可以加上UNSIGNED。

​ 对于DECIMAL：

​ 需要定义一个精度为4，标度为1的。比如说100.0。

​ score DECIMAL(4,1)

2. 字符串类型

   类型	大小	描述
   CHAR	0-255 bytes	定长字符串
   VARCHAR	0-65535 bytes	变长字符串
   TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
   TINYTEXT	0-255 bytes	短文本字符串
   BLOB	0-65 535 bytes	二进制形式的长文本数据
   TEXT	0-65 535 bytes	长文本数据
   MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
   MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
   LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
   LONGTEXT	0-4 294 967 295 bytes	极大文本数据

3. 日期类型

   类型	大小	范围	格式	描述
   DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
   TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
   YEAR	1	1901 至 2155	YYYY	年份值
   DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
   TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

表操作——修改

• 添加字段

  ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
  

  例如：alter table tb_user add nickname varchar(20);

• 修改数据类型

  ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);	
  

• 修改字段名和字段类型

  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
  

• 删除字段

  ALTER TABLE 表名 DROP 字段名;
  

• 修改表名

  ALTER TABLE 表名 RENAME TO 新表名;
  

• 删除表

  DROP TABLE [IF EXISTS] 表名;
  

  在删除表时，表中的数据将全部被删除。

• 删除指定表，并重新创建该表

  TRUNCATE TABLE 表名;
  

  在删除表时，表中的数据将全部被删除。

推荐使用DataGrip进行学习。

DML（数据操纵语言）

对数据库中表的数据记录进行增删改查操作。

添加数据

• 给指定字段添加数据

  INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...)[,(值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,...);
  

select * from employee;：从名为 employee 的表中检索所有列的所有数据。

注意：

1. 插入数据时，指定的字段顺序需要与值的顺序一一对应。
2. 字符串和日期类型的数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

修改数据

UPDATE 表名 SET 字段1 = 值1,字段2 = 值2,...[WHERE 条件]

例如：update employee set name = 'yjl' where id = 1：将employee表格中id为1的那一行记录的name字段值修改为'yjl'。

update employee set name = 'yjl', gender = '男' where id = 1;修改两个数据。

update employee set time = '2008';修改所有的时间

注意：修改语句条件可以有，也可以没有，如果没有则修改整张表

删除数据

DELETE FROM 表名 [WHERE 条件];

例如：delete from employee where gender = '女'将会删除gender为女的记录。

注意：delete语句的条件可以有也可以没有，如果没有将删除整张表的数据。delete语句不能删除某一字段值，但是可以使用update。字段是指表中的列。

DQL（数据查询语言）

使用的频率很高

1. 查询多个字段

   SELECT 字段1,字段2,字段3... FROM 表名;
   SELECT * FROM 表名;  -- * 表示查找所有字段
   

2. 设置别名

   SELECT 字段1 [AS 别名1],字段2 [AS 别名2] ... FROM 表名;
   

3. 去除重复记录

   SELECT DISTINCT 字段列表 FROM 表名;
   

条件查询

语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

其中有条件运算符和逻辑运算符。

例子：

select * from emp where age = 88;  --  查询年龄等于88的
select * from emp where age < 20;  --  查询年龄小于20的
select * from emp where age <= 20;  --  查询年龄小于等于20的
select * from emp where idcard is null;  --  没有身份证号的
select * from emp where idcard is not nulll;  --  有身份证号的
select * from emp where age != 88;  --  年龄不等于88的
select * from emp where age <> 88;  --  年龄不等于88的
select * from emp where age >= 15 and age <= 20;  --  年龄15到20之间的
select * from emp where age between 15 and 20;  --  年龄15到20之间的  and前面要加最小值，后面要加最大值
select * from emp where gender = '女' and age < 25;  --  性别女，年龄小于25的
select * from emp where age = 18 or age = 20 or age = 40;  --  查询年龄等于18或20或40的
select * from emp where age in(18,20,40);  --  查询年龄等于18或20或40的
select * from emp where name like '__';  --  查询姓名为两个字的员工信息，n个字就用n个下划线
select * from emp where idcard like '%X';  --  查询最后一位是X的，%代表任意个字符

聚合函数

作用于表的某一列的。所有的NULL值不参与聚合函数的计算。

语法：

SELECT 聚合函数(字段列表) FROM 表名;

例子：

select count(*) from emp;  --  统计总共的数量
select avg(age) from emp;  --  统计平均年龄
select max(age) from emp;  --  最大年龄
select min(age) form emp;  --  最小年龄
select sum(age) from emp where address = '西安';  --  求西安地区的员工年龄综合

分组查询

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

WHERE和HAVING区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组，而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，having可以。

例子：

select gender, count(*) from emp group by gender;  --  根据性别分组，查询男员工和女员工的数量
结果：
女	7
男	9

select gender, avg(age) from emp group by gender;  --  根据性别分组，查询男员工和女员工的平均年龄
select workplace, count(*) from emp where age <= 45 group by workplace having count(*) >= 3  --  年龄小于等于45的，地点工作员工大于等于3个的

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：

• ASC:升序(默认)
• DESC:降序

例子：

select * from emp order by age;  --  根据年龄升序排序
select * from emp order by time desc;  --  根据时间降序
select * from emp order by age , time desc;  --  先根据年龄升序，后根据时间降序

多字段的时候，前面的字段相同时才会根据后面的字段排序。

分页查询

语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

注意：

• 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MYSQL中是LIMIT。
• 如果查询的是第一页，起始索引可以省略，直接简写为limit 10。

例子：

select * from emp limit 0,10;  --  查询第一页，每页展示10条
select * from emp limit 10;  --  查询第一页，每页展示10条
select * from emp limit 10,10;  --  查询第二页，每页展示10条

DQL语句练习

1. 查询年龄为20,21,22,23岁的女员工信息。
2. 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工.
3. 统计员工表中,年龄小于60岁的,男性员工和女性员工的人数。
4. 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
5. 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升席排序，年龄相同按入职时间升序排序。

select * from emp where gender = '女' and age in (20,21,22,23);
select * from emp where gender = '男' and ( age between 20 and 40 ) and name like '___';
select gender, count(*) from emp where age < 60 group by gender;
select name,age from emp where age <= 35 order by age asc , time desc;
select * from emp where gender = '男' and age between 20 and 40  order by age asc , time asc limit 5;

DQL语句的编写顺序

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后列表条件 ORDER BY 排序字段列表 LIMIT 分页参数

DCL（数据控制语言）

用来管理数据库用户控制数据库的访问权限。

用户管理

1. 查询用户

   USE mysql;
   SELECT * FROM user;
   

   localhost代表只能在本地访问。查询用户直接去查user表。

2. 创建用户

   CREATE USER `用户名`@`主机名` IDENTIFIED BY '密码';
   

   例子：

   create user 'itcast'@'localhost' identified by '1234555';  --  只能够在当前主机localhost访问
   create user 'kk'@'%' identified by '1234555';  --  %可以在任意主机访问该数据库
   

3. 修改用户密码

   ALTER USER `用户名`@`主机名` IDENTIFIED WITH mysql_native_password BY '新密码';
   alter user `kk`@`%` identified with mysql_native_password by '123456';
   

   可以在cmd中输入mysql -u kk -p，即可登录。

4. 删除用户

   DROP USER `用户名`@`主机名`;
   

权限控制

权限	说明
ALL	所有权限
SELECT	查询权限
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/试图
CREATE	创建数据库/表

1. 查询权限

   SHOW GRANTS FOR `用户名`@`主机名`;
   

2. 授予权限

   GRANT 权限列表 ON 数据库名.表名 TO `用户名`@`主机名`;
   

3. 撤销权限

   REVOKE 权限列表 ON 数据库名.表名 FROM `用户名`@`主机名`;
   

注意：

• 多个权限之间，使用逗号分隔
• 授权时，数据库名和表名可以使用*进行通配，代表所有。

3.函数

指一段可以直接被另一段程序调用的程序或代码。

字符串函数

字符串拼接

select concat('hello' , ' mysql');
--  hello mysql

全部转为小写

select lower('Hello');
--  hello

全部转为大写

select upper('Hello');
--  HELLO

左侧填充

select lpad('01',5,'-');
--  ---01

右侧填充

select rpad('01',5,'-');
--  01---

去除前后空格

select trim('  hello mysql  ');
--  hello mysql

截取字符串

select substring('hello mysql',1,5);
--  hello

变更工号，统一五位数，不足的五位数全部在前面补零

update emp set workno = lpad(workno,5,'0');

数值函数

ceil(向上取整)

select ceil(1.5);
--  2

floor(向下取整)

select floor(1.9);
--  1

mod(取模)

select mod(3,4);
--  3

rand(随机数)

select rand();
--  0到1之间的一个随机数

round(四舍五入)

select round(2.355,2);
--  2.36
--  前一个是原数，后一个是要求保留几位

随机六位的验证码

select lpad(round(rand()*1000000,0),6,'0');

日期函数

curdate()

select curdate();
--  2024-08-08

curtime()

select curtime();
--  14:07:27;

now()

select now();
--  2024-08-08 14:07:40

year,month,day

select year(now());  --  2024
select month(now());  --  8
select day(now());  --  8

date_add()

select date_add(now(),interval 70 day);
--  2024-10-17 14:10:53
可以是70个月，也可以是7年

datediff

两个指定时间之间相差的天数

select datediff('2024-12-01','2024-08-02');  --  121
select datediff('2024-08-02','2024-12-01');  --  -121

查询所有员工入职天数，并根据入职天数倒序排序

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

4.约束

概念：作用域表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中的数据的正确、有效性、完整性。

注意：约束作用于表中字段上的，可以在创建表或者修改表的时候添加约束。

案例：

外键约束：

• 概念：让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

• 语法：

  添加外键

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
  

  删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  

5.多表查询

多表关系

对数据库表结构设计时会根据实际将表结构之间建立为一些联系。这些联系基本上分为三种：一对多、多对多、、一对一。

• 一对多

  例如：部门与员工。

  实现：在多的一方建立外键，指向一的一方的主键

• 一对一

  例如：用户与用户详情。

  实现：在任意一方加入外键，关联另外一方的逐渐，并且设置外键为唯一的(UNIQUE)

• 多对多

  例如：学生与课程

  实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多表查询

• 概述：从多张表中查询数据

select * emp , dept where emp.dept_id = dept_id;  --  利用外键匹配筛选

内连接

• 隐式内连接

  SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
  
  select e.name , d.name from emp e , dept d where e.dept_id = d.id;  --  给表起别名，简化代码，其中emp别名为e，dept别名为d
  

• 显示内连接

  SELECT 字段列表 FROM 表1 [INNER]JOIN 表2 ON 连接条件...;
  
  select e.name , d.name from emp e inner join dept d on e.dept_id = d.id  --  inner关键词有省略
  

内连接查询的是两张表交集的部分

外连接

语法：

• 左外连接

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
  
  select e.* , d.name from emp e left join dept d on e.dept_id = d.id;
  

  查询表1的所有数据 包含 表1和表2交集部分的数据

• 右外连接

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
  
  select d.* , e.* form emp e right join dept d on e.dept_id = d.id;
  

  查询表2的所有数据 包含 表1和表2交集部分的数据

推荐使用左外连接

自连接

查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;


--  查询员工以及所属领导的名字
select a.name b.name from emp a , emp b where a.managerid = b.id;

--  查询员工以及所属领导的名字，如果员工没有领导也要查出来
--  这个时候我们不要考虑自连接了，我们考虑外连接，考虑左外连接，因为内连接只是查询两张表交集的数据，而外连接可以完整的查询到左表或者右表
select a.name '员工' , b.name '领导' from emp a left join emp b on a.manage = b.id;

自连接要给表起别名。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果。

SELECT 字段列表 FROM 表A ...
UNION[ALL]  --  去掉ALL可以去重
SELECT 字段列表 FROM 表B ...;

联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

子查询介绍

• 概念：SQL语句中嵌套SELECT语句，叫嵌套查询，又称子查询。

• 语法：

  SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)
  

子查询外部语句可以是其他的。

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。

--  查询“销售部”的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

--  查询在“房东白”入职之后的员工信息
select * from emp where time > (select time from emp where name = '房东白');

列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用操作符

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同
ALL	子查询返回列表的所有值必须都满足

--  查询“销售部”和“市场部”的所有员工的信息
select * from emp where dept_id in (select id from emp where name = '销售部' or name = '市场部');

--  查询比 财务部 所有人工作都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

--  查询比研发部其中任意一人工资都高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

概念：子查询返回的结果是一行(可以是多列)。

常用操作符：=、<>、IN、NOT IN

--  查询与 张无忌 的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

表子查询

概念：子查询返回的结果是多行多列

常用操作符：IN

--  查询入职如期是XXX之后的员工信息，及其部门信息
select * from emp where time > XXX;
select e.*,d.* from (select * from emp where time > XXX) e left join dept d on e.dept_id = d.id;

6.事务

概念：事务 是一组操作的集合，它是一个不可分制的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

例如：转账

事务操作

• 查看、设置事务提交方式

  SELECT @@autocommit;
  SET @@autocommit = 0;
  

• 提交事务

  COMMIT;
  

• 回滚事务

  ROLLBACK;
  

• 开启事务

  START TRANSACTION 或 BEGIN;
  

START TRANSACTION;

........

--  如果没问题
commit;

--  如果有问题
rollback;

事务的四大特性(ACID)

1. 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
2. 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
3. 隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
4. 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

事务隔离级别

越往下隔离级别越高，数据越安全，性能越差

越往上隔离级别越低，数据越不安全，性能越好

--  查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

--  设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
--  如果是SESSION代表只对当前窗口有效，GLOBAL代表对全局有效

二.进阶篇

1.存储引擎

简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

--  在创建表时指定存储引擎
CREATE TABLE 表名( ... ) ENGINE = INNODB [COMMENT 表注释];
--  SHOW ENGINES;
SHOW ENGINES;

特点

• InnoDB

  介绍：兼顾高可靠和高性能，是MySQL默认的存储引擎。

  特点：DML操作遵循ACID模型，支持事务;行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

  文件：XXX.ibd:xxx代表的是表名，innoDB的引擎每张表都会对与这样一张表。

  

• MyISAM

  介绍：早期MySQL的默认存储引擎。

  特点：不支持事务，不支持外键。访问速度快。

  文件：xxx.sdi：存储表结构信息。xxx.MYD：存储数据。xxx.MYI：存储索引。

• Memory

  介绍：存储在内存中，会受到硬件、断电问题的影响。只用于临时表。

  特点：访问速度快，内存存放，hash索引。

  文件：xxx.sdi：存储表结构信息

存储引擎的特点

存储引擎的选择

Innodb：存储业务系统中对于事务、数据完整性要求较高的核心数据。

MyISAM:存储业务系统的非核心事务。

一般用 Innodb，如果用 MyISAM被 MongoDB 取代， MEMORY 被 redis 取代。

2.索引

介绍：帮助MySQL高效获取数据的数据结构(有序)。

索引优点：提高检索效率，降低排序成本。

索引缺点：占空间，降低更新表的速度。

平常的索引结构一般指B+树结构组织的索引。

所有的数据都会出现在叶子节点，并且叶子节点形成了一条双向链表。

Hash索引

• 只能用于对等比较
• 无法利用索引完成排序操作
• 查询效率高

MySQL中InnoDB具有自适应hash功能。

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，唯一	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以多个	UNIQUE
常规索引	快速定位特定数据	可以多个
全文索引	查找的是文本中的关键词，而不是索引的值	可以多个	FULLTEXT

回表查询：先走二级索引查找到对应的主键值，再根据主键值到聚集索引中查找行数据。

索引语法

• 创建索引

  CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
  --  一个索引是可以关联多个字段的
  --  on 哪张表 哪个字段
  

• 查看索引

  SHOW INDEX FROM table_name;
  

• 删除索引

  DROP INDEX index_name ON table_name;
  

实战

1. 为name字段建立索引，该字段值可能会重复。

   create index idx_user_name on tb_user(name);
   --  字段名一般为  idx_表名_字段名
   

2. 为phone建立索引，非空、唯一、建立唯一索引

   create unique index idx_user_phone on tb_user(phone)
   

3. 为profession,age,status创建联合索引

   create index id_user_pro_age_sta on tb_user(profession,age,status);
   --  联合索引中，字段的顺序是有讲究的
   

4. 为email建立合适的索引

   create index id_user_email on tb_user(email);
   

SQL性能分析

SQL的优化主要是优化查询语句。

1. 查看执行频次

SQL的执行频率：

SHOW GLOBAL STATUS LIKE ‘Com_______';
--  跟着的下划线 _ 是通配符，用于匹配任意单个字符

2. 慢查询日志

记录所有执行时间超过指定参数的SQL语句的日志

默认不开启。

3. profile详情

show profiles 能够在做SQL优化时帮助我们了解时间耗费到哪里去了。

--  查看当前SQL是否支持profile操作
SELECT @@haveprofiling;

--  默认的profiling时关闭
--  手动开启开关
SET profilling = 1;

--  查看每一条SQL的耗时基本情况
SHOW profiles

--  查看指定的query_id的SQL语句各个阶段的耗时情况
SHOW profile for query query_id;

--  查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

4. explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法：

--  直接在select语句之前加上关键字explain / desc
explain select 字段列表 from 表名 where 条件

索引使用

• 最左前缀法则

  如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果最左边的列不存在，索引将全部失效，如果跳跃某一列，索引将部分失效(后面的字段索引失效)。这个和索引的顺序无关。

• 范围查询

  联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

• 索引列运算

  不要再索引列上进行运算操作，索引将失效。

• 字符串不加引号，索引将失效

• 模糊查询

  如果是尾部模糊匹配，索引不会失效

  如果是头部，索引失效

• or连接的条件

  用or分隔开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

• 数据分布影响

  如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

优化数据库的重要手段，简单来说，是在SQL语句中加入一些人为的提示来达到优化操作的目的。

• use index:

  explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
  

• ignore index:

  explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
  

• force index:

  explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
  

覆盖索引

减少使用select *

注意：

using index condition -- 查找使用了索引，但是需要回表查询数据
using where;using index;  --  查找使用了索引，但是需要的数据都在索引列中能找到

前缀索引

概念：当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

create index idx_xxxx on table_name(column(n));
--  其中n代表指定前n个字符来构建索引

关于前缀长度n的选择：

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

可以通过一下的代码来计算索引选择性

select count(distinct substring(email,1,5)) / count(*) from tb_user;

单列索引与联合索引

单列索引：指一个索引只包含单个列。

联合索引：指一个索引包含多个列。

业务场景中，如果存在多个查询条件，考虑正对查询字段建立索引，建议使用联合索引。

3.SQL优化

插入数据

• insert优化

  批量插入

  insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jetty');
  

  手动提交事务

  start transaction;
  insert ...
  insert ...
  commit;
  

• 大批量插入数据

  --  客户端连接服务端时，加上参数  --local-infile
  mysql --local-infile -u root -p
  --  开启本地加载文件导入数据的开关
  set global local_infile = 1;
  --  将数据加载到表结构中
  load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
  

  主键顺序插入性能高于乱序插入

主键优化

• 数据组织方式

  页分裂、页合并的原理。

• 主键设计原则

  尽量降低主键的长度

  插入数据时尽量选择顺序插入

  尽量避免对主键的修改

order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index，不需要额外排序，操作效率高。

总结：

• 根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC).

group by优化

• 分组操作时，可以通过索引来提高效率
• 分组操作时，索引的使用也满足最左前缀法则

limit优化

常常遇到问题：使用limit 200000000,10的时候，MySQL会排序前200000010个记录，然后仅仅返回十个记录，将其他记录丢弃。

覆盖索引（Covering Index）是一种数据库索引的特殊形式，它指的是一个索引包含了查询中所需要的所有数据列。

优化思路：分页查询时，通过创建覆盖索引能够比较好地提高性能。

代码：

explain select * from tb_sku t , (select id from tb_sku order by id limit 200000000,10) a where t.id = a.id;

count优化

优化思路：自己计数。

性能：count(字段) < count(主键 id) < count(1) ≈ count(*)

update优化

在执行update的指令的时候，要根据索引的字段进行更新，因为这样是只会触发行锁，否则将触发表锁。一旦锁表，并发性能将会降低。

4.视图/存储过程/触发器

暂时跳过

5.锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁对于数据库非常重要。

在MySQL中按照锁的粒度分，主要分为三类

1. 全局锁：锁定数据库中所有的表
2. 表级锁：每次操作锁住整张表
3. 行级锁：每次操作锁住对应的行数据

全局锁

对整个数据库的实例加锁，加锁后整个实例就处于只读状态。

典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

代码：

--  加全局锁
flush tables with read lock;

--  备份数据库
mysqldump -uroot -p1234 iscast > itcast.sql
--  不是MySQL语句，直接在Windows命令行运行
mysqldump：这是 MySQL 数据库系统中的一个工具，用于将数据库、表、或数据库中的数据备份成 SQL 文件。
-u：这是用户选项，后面跟着的是数据库的用户名。在这里，用户名是 root，这是一个常见的数据库管理员账户。
-p：这是密码选项，告诉 mysqldump 接下来输入的是数据库的密码。在实际使用中，为了安全考虑，通常不会直接在命令行中暴露密码，而是让命令提示你输入密码，但是在这个例子中，密码 1234 被直接放在了 -p 选项后面。
iscast：这是你要备份的数据库名称。这个命令将会备份名为 iscast 的数据库。
>：这是一个重定向符号，它告诉 shell 将 mysqldump 工具的输出重定向到一个文件中。
itcast.sql：这是你想要将数据库备份保存到的 SQL 文件的名称。在这个例子中，备份文件将被命名为 itcast.sql。

--  解锁
unlock tables;

特点：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。

InnoDB中，我们可以在备份时加上参数来完成不加锁的一致性数据备份

mysqldump --single-transaction -uroot -p1234 itcast > itcast.sql

表级锁

每次操作锁住整张表，锁定粒度大，发生冲突概率最高。

主要分为三类：

1. 表锁
2. 元数据锁(MDL)
3. 意向锁

• 表锁

  可分为两类：表共享读锁(read lock)、表独占写锁(write lock)。

  语法：

  1. 加锁：lock tables 表名… read/write
  2. 释放锁：unlock tables / 客户端断开连接

  对于read lock：当前客户端可以DQL，不能DDL/DML。其他客户端可以DQL，不能DDL/DML。

  对于write lock：当前客户可以DQL,DDL/DML。其他客户端不能DQL,DDL/DML.

• 元数据锁(MDL)

  加锁过程系统自动控制，主要作用是维护表元数据的数据一致性。为了避免DML和DDL冲突，保证读写的正确性。

• 意向锁

  为了避免DML在执行时，加的行锁与表锁冲突，引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

  1. 意向共享锁(IS)：由语句select … lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
  2. 意向排他锁(IX)：由insert,update,delete,select … for update添加。与表锁共享锁(read)和排他锁(write)都互斥。意向锁之间不会互斥。

  对于共享锁(Shared Lock)补充的知识点：

  • 当一条记录被加上共享锁时，其他事务也可以读取这条记录，但任何事务都不能修改这条记录，直到所有持有共享锁的事务释放锁。

  • 使用LOCK IN SHARE MODE的查询会锁定涉及的行，直到事务结束。这意味着在事务提交或回滚之前，其他事务不能对这些行进行更新。

  • 通过加共享锁，可以确保在读取数据时，数据不会被其他事务修改，从而避免脏读的发生。

行级锁

• 介绍

  每次锁住对应行的行数据。发生冲突的概率最低。

  InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。行级锁主要分为以下三类

  1. 行锁：不能对此行update和delete
  2. 间隙锁：锁定索引记录前面的那个间隙，不能对记录前面insert打破原有顺序
  3. 临键锁：行锁和间隙锁的组合。

• 行锁

  有两种类型的行锁：

  1. 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  2. 排他锁(X)：允许获取排他锁的事务更新数据，组织其他事务获得相同数据集的共享锁和排他锁。

  类型	S(共享锁)	X(排他锁)
  S(共享锁)	兼容	冲突
  X(排他锁)	冲突	冲突

  InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时将会升级成表锁。

• 间隙锁/临键锁

  1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁
  2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁退化成间隙锁。比如查询一个3，索引中只有一个3，就锁住3和前后两个间隙。
  3. 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止。比如查询一个大于等于19，会行锁锁住19，然后临键锁锁住下一个值，以及对后面到正无穷的值加上一个临键锁。

  间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

幻读补充的知识点：指在同一个事务中，多次执行同一个查询，但是返回了不同的结果集，尤其是在第二次查询时返回了第一次查询没有返回的行。

6.InnoDB引擎

逻辑存储结构

• 表空间(ibd文件)，一个MySQL示例可以对应多个表空间，用于存储记录、索引等数据。

  在Linux中，MySQL的数据文件一般存储在/var/lib/mysql中。

• 段(Segment)，分为数据段、索引段、回滚段，InnoDB是所有组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个区(Extent)。

• 区(Extent)，表空间的单元结构，每个区大小为1M默认情况下，InnoDB存储引擎页大小为16K，即一个区共有64个连续的页。

• 页(Page)，InnoDB存储引擎磁盘管理的最小单元，InnoDB引擎每次从磁盘申请4-5个区。表中的记录、索引和其他数据确实都是存储在页中的。

  注意：为什么索引存储在页里而不是区里？

  当我们说索引存储在页中时，我们的意思是索引的数据（比如B+树节点的键和指针）实际上是存储在页内的。一个索引可以跨越多个页，这些页可能分布在不同的区中。换句话说，索引是由多个页组成的，而这些页可能属于不同的区。

• 行，InnoDB存储引擎数据是按行进行存放的。

  Trx_id:每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
  Rol_pointer:每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

架构

MySQL现在默认使用InnoDB，它擅长事务处理，具有崩溃恢复特性。

• 内存架构

  Buffer Pool：缓冲池是主内存中的一个区域，可以缓存磁盘上经常操作的数据，执行增删改查操作时，可以先操作缓冲池里的数据，然后再按照一定频率刷新到磁盘。

  缓冲池以Page为单位，底层采用链表管理Page，可以将Page分为三类。

  1. free page：没被使用过的
  2. clean page：被使用过，数据没有被修改过的
  3. dirty page：被使用过，数据被修改过

  Change Buffer：更改缓冲区。针对于非唯一的二级索引，在执行DML语句时，如果这样语句不在线程池里，不会直接操作磁盘，而是将数据变更存在更改缓冲区里，在未来数据被读取时，再将数据合并恢复到线程池，再合并后的数据刷新到磁盘中。

  Adaptive Hash Index：自适应hash索引，用于优化对线程池数据的查询。

  Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据(redo log,undo log)，默认大小16MB.

• 磁盘结构

  系统表空间(System Tablespace)：更改缓冲区的存储区域。

  File-Per-Table Tablespaces：每个表的文件包空间包含单个InnoDB表的数据和索引，并存储再文件系统上的单个数据文件中。每一种表都会生产对应的表空间文件。也就是ibd结尾的文件。

  General Tablespaces：通用表空间。需要通过语法创建表空间，在创建表时，可以指定该表空间。

  Uodo Tablespaces：撤销表空间，用于存储undo log日志。

  Temporary Tablespaces：临时表空间。

  Doublewrite Buffer Files：双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，预防系统异常。

  Redo Log：重做日志，用来实现事务的持久性。

• 后台线程

  作用是将InnoDB存储引擎缓冲池的数据在合适的时间刷新到磁盘空间中。

  1. Master Thread
  2. IO Thread
  3. Purge Thread
  4. Page Cleaner Thread

事务原理

• 事务时一组操作的集合，是不可分割的工作单位。

• 特性：原子性，一致性，隔离性，持久性。

• 原子性、一致性、持久性：redo log、undo log

  隔离性：锁、MVCC

redo log：重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性。

undo log：回滚日志，用于记录被修改前的信息。用于实现事务的原子性。作用：提供回滚和MVCC(多版本并发控制)。

redo log和redo log综合起来实现事务的一致性。

MVCC

作用：在快照读的时候，通过MVCC来查找历史版本。

• 当前读

  读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select … lock in share mode(共享锁),select … for update、update、insert、delete(排他锁)都是一种当前读。

• 快照读

  简单的select，在不加锁的情况下就是快照读，库区的是记录数据的可见版本，有可能是历史数据。

• MVCC

  全称 Multi-version Concurency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MYSQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readView。

• 非阻塞读理解

  start transaction;
  --  本来值为100
  --  执行一个非阻塞读操作
  select balance from accounts where id = 1;
  
  --  更新
  update accounts set balance = 150 where id = 1;
  
  --  接下来再次进行一次非阻塞读
  select balance from accounts where id = 1;
  
  --  这次读取结果还是100，因为读取事务在进行非阻塞读（快照读），它仍然会看到事务开始时的数据状态。
  

MVCC实现原理

• 记录中的隐藏字段

  

• undo log

  回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

  不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

• readview(视读图)

  是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id.

  trx_id：代表当前事务ID。

  read view中包含了四个核心字段：

  字段	含义
  m_ids	当前活跃的事务ID集合
  min_trx_id	最小活跃的事务ID集合
  max_trx_id	预分配事务ID，当前最大事务ID+1
  creator_trx_id	readview创建者的事务ID

  一些规则：

  

• 非阻塞读与快照读

  非阻塞读和快照读是两种不同的数据读取机制，它们在处理并发访问时各有特点。

  非阻塞读指的是数据库在执行读取操作时，即使其他事务正在对同一数据行进行写操作，读取操作也能顺利完成，而不会因为锁等待而阻塞。这种读取方式的核心在于它不会与写操作产生锁冲突。例如，在MySQL的InnoDB引擎中，使用非锁定的一致性读（consistent non-locking read）就可以实现非阻塞读。这种读取机制允许读取操作在不影响写操作的情况下进行，从而提高了系统的并发性能。

  快照读则是一种特殊的读取方式，它依赖于数据库的多版本并发控制（MVCC）技术。在快照读的过程中，数据库会为每个事务提供一个数据的一致性视图，即事务开始时数据库的状态快照。这意味着，即使在事务执行期间其他事务对数据进行了修改，快照读操作依然会返回事务开始时的数据状态。快照读的主要优势是提供了高度的数据一致性保证，使得事务能够在一个稳定的数据库状态下执行。

  实现机制：非阻塞读更多关注的是读取操作的并发性，而快照读则侧重于提供数据的一致性视图。

  数据可见性：非阻塞读可能会看到其他事务提交的最新数据，而快照读则总是看到事务开始时的数据状态。

• 不同的隔离级别，生成readview的时机不同：

  RC：read committed，在食物中每一次执行快照读是生成readview

  RR：repeatable read。仅在事务中第一次执行快照读时生成readview，后续复用该readview。

og日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

7.MySQL管理

系统数据库

常用工具

• mysql客户端工具

  语法：mysql [options] [database]

  选项：

  • -u #指定用户名
  • -p #指定密码
  • -h #指定服务器IP或域名
  • -P #指定连接端口
  • -e #执行SQL语句并退出

• mysqladmin

  执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态

• mysqlbinlog

  服务器生成的二进制日志文件

• mysqlshow

  客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

• mysqldump

  用来备份数据库或在不同数据库之间进行数据迁移。

• mysqlimport/source

  数据导入工具