MySQL学习笔记

基础篇：
进阶篇：

基础篇：

通用语法：

1.SQL语句以分号结尾 [^1]
2.MySQL数据库的SQL语句不区分大小写
3.单行注释：-- 或 # ；多行注释：/* */

基础操作：

DDL-数据库操作：

基本指令：

指令	作用
SHOW DATABASES	查询所有数据库
SELECT DATABASE()	查询当前数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则] [^2]	创建指定数据库
DROP DATABASE [IF EXISTS] 数据库名	删除指定数据库
USE 数据库名	切换到指定数据库
SHOW TABLES	查询当前数据库中所有表
DESC 表名	查询表结构
SHOW CREATE TABLE 表名	查询指定表的建表语句

-- 表结构创建
CREATE TABLE 表名
(
    字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段3注释],   
    ...    
    字段4 字段4类型[COMMENT 字段4注释]
)[COMMENT 表注释];
-- []中内容可忽略

数据类型：

数值类型：

类型	（无符号UNSIGNED）范围	描述
TINYINT	[0，255]	极短整数值
SMALLINT	[0，65535]	短整数值
MEDIUMINT	[0，2¹⁶-1]	整数值
INT	[0，2³²-1]	整数值
BIGINT	[0，2⁶⁴-1]	长整数值
FLOAT	-	单精度浮点型
DOUBLE	-	双精度浮点型
DECIMAL	取决于M（精度）和D（标度）	小数值（精确定点数）

字符串类型：

类型	描述
CHAR	定长字符串
VARCHAR	变长字符串
TINYBLOB	不超于255字符的二进制数据
TINYTEXT	短文本字符串
BLOB	二进制形式的长文本数据
TEXT	长文本数据
MEDIUMBLOB	二进制形式的中等长文本数据
MEDIUMTEXT	中等长文本数据
LONGBLOB	二进制形式的极大文本数据
LONGTEXT	极大文本数据

日期时间类型：

类型	格式	描述
DATE	YYYY-MM-DD	日期值
TIME	HH:MM:SS	时间值或持续时间
YEAR	YYYY	年份值
DATETIME	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

CREATE TABLE Emp
(
    Id INT COMMENT '编号',
    WorkNum VARCHAR(10) COMMENT '工号',
    Name VARCHAR(10) COMMENT '姓名',
    Gender CHAR(1) COMMENT '性别',
    Age TINYINT UNSIGNED COMMENT '年龄',
    Idcard CHAR(18) COMMENT '身份证号',   
    EntryDate DATE COMMENT '入职时间' 
) COMMENT '员工表';

表结构修改：

指令	作用
ALTER TABLE 表名 ADD 字段名类型（长度） [COMMENT 注释] [约束]	添加字段
ALTER TABLE 表名 MODIFY 字段名数据类型（长度）	修改字段数据类型
ALTER TABLE 表名 CHANGE 旧字段名新字段名类型（长度） [COMMENT 注释] [约束]	修改字段名与字段类型
ALTER TABLE 表名 DROP 字段名	删除字段
ALTER TABLE 表名 RENAME TO 新表名	修改表名
DROP TABLE [IF EXISTS] 表名	删除表
TRUNCATE TABLE 表名	格式化表

-- 添加字段
ALTER TABLE Emp ADD NickName VARCHAR(10) COMMENT '昵称';
-- 修改字段
ALTER TABLE Emp CHANGE NickName UserName VARCHAR(30) COMMENT '用户名';

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...)	为指定字段批量添加数据为所有字段批量添加数据

-- 指定字段添加数据
INSERT INTO Employee(Id, Name, Gender, EntryDate) VALUES (1,'流苏','男','2024-10-14');
-- 所有字段添加数据
INSERT INTO Employee VALUES (1,'10086','流苏','男',18,'301772','2024-10-14');
-- 指定字段批量添加数据
INSERT INTO Employee(Id, Name, Gender, EntryDate) VALUES (1,'流苏','男','2024-10-14'),(2,'Tassel','男','2024-10-24');
-- 所有字段批量添加数据
INSERT INTO Employee VALUES (1,'10086','流苏','男',18,'301772','2024-10-14'),(2,'10085','Tassel','男',34,'301723','2024-12-14');
# 注意插入数据互不影响,对于字符串类型数据以及日期类型数据需要使用单引号引用

修改、删除操作：

指令	作用
UPDATE 表名 SET 字段1 = 值1，字段2 = 值2，... [WHERE 条件]	修改数据
DELETE FROM 表名 [WHERE 条件]	删除数据

-- 修改数据
UPDATE Employee SET Name = '张无忌',Gender = '女' WHERE Id = 1;
# 无 WHERE 条件,将修改表中所有对应数据
UPDATE Employee SET EntryDate = '2004-10-14';
-- 删除数据
DELETE FROM Employee WHERE Gender = '女';
# 删除表中所有数据
DELETE FROM Employee;

DQL-查询操作：

DQL-编写顺序：

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

基础查询：

指令	作用
SELECT 字段1，字段2，字段3... FROM 表名	查询指定字段
SELECT * FROM 表名	查询所有字段
SELECT 字段1 [AS 别名1]，字段2 [AS 别名2]... FROM 表名	查询并设置别名
SELECT DISTINCT 字段列表 FROM 表名	对返回数据执行去重操作

-- 查询指定字段
SELECT Name,WorkNum,Age FROM Employee;
-- 查询所有字段
SELECT * FROM Employee;
-- 设置别名
SELECT EntryDate AS '入职日期' FROM Employee;
-- 对返回数据执行去重操作
SELECT DISTINCT EntryDate FROM Employee;

条件查询：

指令	作用
SELECT 字段列表 FROM 表名 WHERE 条件列表	条件查询

-- 条件查询
SELECT * FROM Employee WHERE Age <= 18;
SELECT * FROM Employee WHERE Age != 18;
SELECT * FROM Employee WHERE Age >= 18 && Age <= 40;
SELECT * FROM Employee WHERE Age BETWEEN 18 AND 40;

SELECT * FROM Employee WHERE Idcard IS NULL;
SELECT * FROM Employee WHERE Idcard IS NOT NULL;

SELECT * FROM Employee WHERE Age = 18 OR Age = 20 OR Age = 40;
SELECT * FROM Employee WHERE Age IN(18,20,40);

SELECT * FROM Employee WHERE Name LIKE '__'; # 查询Name长度为2的数据
SELECT * FROM Employee WHERE Name LIKE '%L'; # 查询Name末尾为L的数据

分组查询：

聚合函数：

函数	作用
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：

指令	作用
SELECT 聚合函数(字段列表) FROM 表名	调用聚合函数统计表信息
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件]	分组查询[^ 3]

🌟使用聚合函数时所有NULL值不参与运算

-- COUNT函数
SELECT COUNT(*) FROM Employee; # 返回Employee表中数据记录总条数
SELECT COUNT(Idcard) FROM Employee; # 返回Employee表中Idcard数据非空条数
-- AVG函数
SELECT AVG(Age) FROM Employee; # 返回Employee表中Age数据的平均值
-- MAX函数
SELECT MAX(Age) FROM Employee;
-- SUM函数
SELECT SUM(Age) FROM Employee WHERE Idcard = '301772'; # 返回编号为'301772'的员工的年龄之和

-- 分组查询
-- 1.根据性别分组,统计男性员工与女性员工数量
SELECT Gender,COUNT(*) FROM Employee GROUP BY Gender;
-- 2.根据性别分组,统计男性员工与女性员工平均年龄
SELECT Gender,AVG(Age) FROM Employee GROUP BY Gender;
-- 3.查询年龄小于20的员工,根据性别分组,获取员工数量大于2的性别
SELECT Gender,COUNT(*) FROM Employee WHERE Age < 20 GROUP BY Gender HAVING COUNT(*) >= 2;
# 或者使用别名
SELECT Gender,COUNT(*) Gender_Count FROM Employee WHERE Age < 20 GROUP BY Gender HAVING Gender_Count >= 1;

排序查询：

指令	作用
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2	排序查询

🌟默认情况为升序排序 ASC ，降序排序为 DESC，支持多字段排序

-- 排序查询
SELECT * FROM Employee ORDER BY Age ASC; # 按年龄升序排序
-- 多字段排序
SELECT * FROM Employee ORDER BY Age ASC, Id DESC;

分页查询：

指令	作用
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数[^4]	分页查询

-- 分页查询
-- 1.查询第1页员工数据,每页展示10条记录
SELECT * FROM Employee LIMIT 10;
-- 2.查询第2页员工数据,每页展示10条记录
SELECT * FROM Employee LIMIT 10,10;

DQL-执行顺序：

DCL-用户管理：

指令	作用
USE MySQL SELECT * FROM USER	切换数据库查询当前用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '访问密码'	创建用户
ALTER USER '用户名'@'主机名' IDENTIFIED WITH MySQL_native_password BY '新密码'	修改用户密码
DROP USER '用户名'@'主机名'	删除用户

-- 创建用户
-- 1.创建用户Itcast,只有当前主机localhost可以访问
CREATE USER 'Itcast'@'localhost' IDENTIFIED BY '666666';
-- 2.创建用户Tassel,可以在任意主机访问
CREATE USER 'Tassel'@'%' IDENTIFIED BY '666666';
-- 修改用户密码
ALTER USER 'Itcast'@'localhost' IDENTIFIED WITH MySQL_native_password BY '123456';
-- 删除用户
DROP USER 'Itcast'@'localhost';

DCL-权限控制：

指令	作用
SHOW GRANTS FOR '用户名'@'主机名'	查询用户权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名'	授予用户权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名'	撤销用户权限

-- 查询权限
SHOW GRANTS FOR 'Tassel'@'%';
-- 授予权限
GRANT ALL ON Test_01.* TO 'Tassel'@'%';
-- 撤销权限
REVOKE ALL ON Test_01.* FROM 'Tassel'@'%';

函数：

字符串函数：

函数	功能
CONCAT(S1，S2，S3...Sn)	字符串拼接，将S1，S2，S3...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)	取出长度为 len 的字符子串

-- SELECT 函数（参数）
SELECT CONCAT('Hello','MySQL');
SELECT LOWER('Hello');
SELECT UPPER('Hello');
SELECT LPAD('01',5,'-');
SELECT RPAD('01',5,'-');
SELECT TRIM(' Hello MySQL! ');
SELECT SUBSTRING('Hello MySQL!',1,5);
-- 案例
-- 将企业员工工号统一为5位,不足5位左侧补零
UPDATE Employee SET WorkNum = LPAD(WorkNum,8,'0');

数值函数：

函数	功能
CEIL(x)	向上取整
FLOOD(x)	向下取整
MOD(x)	运算 x/y 的模
RAND()	返回 0~1 内随机数
ROUND(x，y)	求解参数 x 四舍五入，保留 y 位小数

-- SELECT 函数（参数）
SELECT CEIL(1.1);
SELECT FLOOR(1.9);
SELECT MOD(7,4);
SELECT RAND();
SELECT ROUND(3.14159,4);
-- 案例
-- 生成六位数随机验证码
SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');

日期函数：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期与时间
YEAR(date)	获取指定 date 年份
MONTH(date)	获取指定 date 月份
DAY(date)	获取指定 date 日期
DATE_ADD(date，INTERVAL expr type)	返回 date+时间间隔expr 后的时间值
DATEDIFF(date1，date2)	返回起始时间date1 到结束时间date2 间天数

-- SELECT 函数（参数）
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);
SELECT DATEDIFF('2024-10-11',NOW());
-- 案例
-- 将员工按照入职时间长短降序排序
SELECT Name,DATEDIFF(CURDATE(),EntryDate) AS 'EntryDates' FROM Employee ORDER BY EntryDates DESC;

流程函数：

函数	功能
IF(value，t，f)	若 value 为 true，则返回 t，否则返回 f
IFNULL(value1，value2)	若 value1 不为空，则返回 value1，否则返回 value2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END	若 val 为 true，则返回 res1，... 否则返回 default 默认值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END	若 expr 值等于 val1，则返回 res1，... 否则返回 default 默认值

-- SELECT 函数（参数）
SELECT IF(True,'Ok','Error');
SELECT IFNULL('Ok','Default');
SELECT IFNULL('','Default');
SELECT IFNULL(NULL,'Default');
-- 案例
-- 查询并按照格式输出员工工作地址
SELECT
    Name,
    (CASE WorkAddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) AS '工作地址'
FROM Employee;

约束：

概述：

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段的数据不能为NULL	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某条件	CHECK
外键约束	使两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

⭐约束是作用于表中字段上的，可以在创建表/修改表时添加约束

CREATE TABLE Users(
    Id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    Name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    Age INT CHECK ( Age > 0 && Age <= 120 ) COMMENT '年龄',
    Status CHAR(1) DEFAULT '1' COMMENT '状态',
    Gender CHAR(1) COMMENT '性别'
)COMMENT '用户表';
-- 主键约束
INSERT INTO Users(Name, Age, Status, Gender) VALUES ('Tom',19,'1','男'),('Tim',25,'0','男');
INSERT INTO Users(Name, Age, Status, Gender) VALUES ('Jim',29,'1','男');
# Id主键会随数据的插入自增
-- 非空约束
INSERT INTO Users(Name, Age, Status, Gender) VALUES (NULL,29,'1','男'); # 插入失败,Name数据不为空
-- 唯一约束
INSERT INTO Users(Name, Age, Status, Gender) VALUES ('Tom',29,'1','男'); # 插入失败,Name数据需唯一
-- 检查约束
INSERT INTO Users(Name, Age, Status, Gender) VALUES ('Tomy',129,'1','男'); # 插入失败,Age数据超出范围
-- 默认约束
INSERT INTO Users(Name, Age, Gender) VALUES ('Jenny',12,'男'); # 未传输状态,默认为'1'

外键约束：

CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAING] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) #联系集
);
-- 准备数据
CREATE TABLE Dept(
    Id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    Name VARCHAR(50) NOT NULL COMMENT '部门名称'
)COMMENT '部门表';
INSERT INTO Dept (Id, Name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');

CREATE TABLE Emp(
    Id  INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    Name VARCHAR(50) NOT NULL COMMENT '姓名',
    Age INT COMMENT '年龄',
    Job VARCHAR(20) COMMENT '职位',
    Salary INT COMMENT '薪资',
    Entrydate DATE COMMENT '入职时间',
    Managerid INT COMMENT '直属领导ID',
    Dept_Id INT COMMENT '部门ID'
)COMMENT '员工表';
INSERT INTO Emp (Id, Name, Age, Job, Salary, Entrydate, Managerid, Dept_Id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);

-- 添加外键（外部）
ALTER TABLE 子表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(子表外键字段名) REFERENCES 父表名(父表字段名);
ALTER TABLE Emp ADD CONSTRAINT Fk_Emp_Dept_Id FOREIGN KEY(Dept_Id) REFERENCES Dept(Id);
-- 删除外键
ALTER TABLE 子表名 DROP FOREIGN KEY 外键名称;
ALTER TABLE Emp DROP FOREIGN KEY Fk_Emp_Dept_Id;

外键删除、更新：

指令	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为NULL

-- 设置外键删除、更新方式
ALTER TABLE 子表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(子表外键字段名) REFERENCES 父表名(父表字段名) ON UPDATE CASCADE ON DELETE CASCADE
# 默认为NO ACTION 或 RESTRICT模式

多表查询：

E-R模型：

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所
以各个表结构之间也存在着各种联系——" E-R模型 "。

# 多对多
CREATE TABLE Student(
    Id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    Name VARCHAR(10) COMMENT '姓名',
    No VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';
INSERT INTO Student VALUES (NULL, '黛绮丝', '2000100101'),(NULL, '谢逊', '2000100102'),(NULL, '殷天正', '2000100103'),(NULL, '韦一笑', '2000100104');
CREATE TABLE Course(
    Id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    Name VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';
INSERT INTO Course VALUES (NULL, 'Java'), (NULL, 'PHP'), (NULL , 'MySQL') , (NULL, 'Hadoop');

CREATE TABLE Student_Course(
    Id INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    StudentId INT NOT NULL COMMENT '学生ID',
    CourseId  INT NOT NULL COMMENT '课程ID',
    CONSTRAINT Fk_CourseId FOREIGN KEY (CourseId) REFERENCES Course (Id),
    CONSTRAINT Fk_StudentId FOREIGN KEY (StudentId) REFERENCES Student (Id)
)COMMENT '学生课程中间表';
INSERT INTO Student_Course VALUES (NULL,1,1),(NULL,1,2),(NULL,1,3),(NULL,2,2),(NULL,2,3),(NULL,3,4);

屏幕截图 2024-09-29 165009

屏幕截图 2024-09-29 164949

PixPin_2024-09-29_16-48-10

# 一对一
# "一对一"关系常用于单表拆分,提升操作效率
CREATE TABLE User(
    Id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    Name VARCHAR(10) COMMENT '姓名',
    Age INT COMMENT '年龄',
    Gender CHAR(1) COMMENT '1: 男 , 2: 女',
    Phone CHAR(11) COMMENT '手机号'
) COMMENT '用户基本信息表';
CREATE TABLE User_Education(
    Id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    Degree VARCHAR(20) COMMENT '学历',
    Major VARCHAR(50) COMMENT '专业',
    PrimarySchool VARCHAR(50) COMMENT '小学',
    MiddleSchool VARCHAR(50) COMMENT '中学',
    University VARCHAR(50) COMMENT '大学',
    UserId INT UNIQUE COMMENT '用户ID', # 唯一约束
    CONSTRAINT Fk_UserId FOREIGN KEY (UserId) REFERENCES User(Id) # 利用唯一外键将两表数据对应起来
) COMMENT '用户教育信息表';

INSERT INTO User(Id, Name, Age, Gender, Phone) VALUES
    (NULL, '黄渤', 45, '1', '18800001111'),
    (NULL, '冰冰', 35, '2', '18800002222'),
    (NULL, '码云', 55, '1', '18800008888'),
    (NULL, '李彦宏', 50, '1', '18800009999');
INSERT INTO User_Education(Id, Degree, Major, PrimarySchool, MiddleSchool, University, UserId) VALUES
    (NULL, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
    (NULL, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
    (NULL, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
    (NULL, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);

屏幕截图 2024-09-29 170142

屏幕截图 2024-09-29 170153

笛卡尔积：

-- 准备数据
CREATE TABLE Dept (
    Id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    Name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';

CREATE TABLE Emp (
    Id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    Name VARCHAR(50) NOT NULL COMMENT '姓名',
    Age INT COMMENT '年龄',
    Job VARCHAR(20) COMMENT '职位',
    Salary INT COMMENT '薪资',
    EntryDate DATE COMMENT '入职时间',
    ManagerId INT COMMENT '直属领导ID',
    Dept_Id INT COMMENT '部门ID'
) COMMENT '员工表';

-- 添加外键
ALTER TABLE Emp 
ADD CONSTRAINT Fk_Emp_Dept_Id FOREIGN KEY (Dept_Id) REFERENCES Dept(Id);

INSERT INTO Dept (Id, Name) VALUES 
    (1, '研发部'), 
    (2, '市场部'), 
    (3, '财务部'), 
    (4, '销售部'), 
    (5, '总经办'), 
    (6, '人事部');

INSERT INTO Emp (Id, Name, Age, Job, Salary, EntryDate, ManagerId, Dept_Id) VALUES
    (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5),
    (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
    (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
    (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
    (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
    (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
    (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
    (8, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3),
    (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
    (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
    (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
    (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
    (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
    (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
    (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
    (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
    (17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);

-- 多表查询(笛卡尔积-","连接)
SELECT * FROM Emp, Dept WHERE Emp.Dept_Id = Dept.Id;

查询分类：

内连接：

指令	功能
SELECT 字段列表 FROM 表1，表2 WHERE 条件	隐式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件	显示内连接

-- 内连接查询
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id
-- 查询结果：各员工的姓名及关联的部门的名称
# 1.隐式内连接实现
SELECT Emp.Name, Dept.Name FROM Emp, Dept WHERE Emp.Dept_Id = Dept.Id;
SELECT E.Name, D.Name FROM Emp E, Dept D WHERE E.Dept_Id = D.Id; # 利用别名简化代码
# 2.显式内连接实现
SELECT E.Name, D.Name FROM Emp E INNER JOIN Dept D ON E.Dept_Id = D.Id;
SELECT E.Name, D.Name FROM Emp E JOIN Dept D ON E.Dept_Id = D.Id; # 注意取别名之后不可利用原名操作表结构

外连接：

指令	功能
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件	左外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件	右外连接

-- 外连接查询
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id

-- 查询结果：Emp表的所有数据与对应的部门信息
# 1.左外连接
SELECT E.*, D.Name FROM Emp E LEFT OUTER JOIN Dept D ON E.Dept_Id = D.Id;
SELECT E.*, D.Name FROM Emp E LEFT JOIN Dept D ON E.Dept_Id = D.Id;
-- 查询结果：Dept表的所有数据与对应的员工信息
# 2.右外连接
SELECT D.*, E.* FROM Emp E RIGHT OUTER JOIN Dept D ON E.Dept_Id = D.Id;
SELECT D.*, E.* FROM Dept D LEFT OUTER JOIN Emp E ON E.Dept_Id = D.Id;

自连接：

指令	功能
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件	自连接

-- 自连接
-- 1.查询员工及其所属领导的名字 (内连接)
-- 表结构: Emp A, Emp B
SELECT A.Name, B.Name FROM Emp A, Emp B WHERE A.ManagerId = B.Id;

-- 2.查询所有员工Emp及其领导的名字Emp (没有领导的员工仍需要查询-左外连接)
-- 表结构: Emp A, Emp B
SELECT A.Name AS '员工', B.Name AS '领导' FROM Emp A LEFT JOIN Emp B ON A.ManagerId = B.Id;

联合查询：

指令	功能
SELECT 字段列表 FROM 表A UNION [ALL] SELECT 字段列表 FROM 表B	UNION 查询将多次查询结果合并形成查询结果集

-- UNION ALL / UNION
-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
SELECT * FROM Emp WHERE Salary < 5000
UNION ALL
SELECT * FROM Emp WHERE Age > 50;
-- 使用UNION进行去重操作
SELECT * FROM Emp WHERE Salary < 5000
UNION
SELECT * FROM Emp WHERE Age > 50;
# 注意联合查询要求字段类型与数量相同

嵌套查询（子查询）：

指令	功能
SELECT * FROM 表1 WHERE 字段1 = (SELECT 字段2 FROM 表2 WHERE 条件) [^5]	嵌套查询

-- 标量子查询(返回单值-数字、字符串、日期等)
-- 1. 查询 "销售部" 的所有员工信息
-- a. 查询 "销售部" 部门ID
SELECT Id FROM Dept WHERE Name = '销售部';
-- b. 根据销售部部门ID查询员工信息
SELECT * FROM Emp WHERE Dept_Id = (SELECT Id FROM Dept WHERE Name = '销售部');

-- 2. 查询在 "方东白" 入职之后的员工信息
-- a. 查询 方东白 的入职日期
SELECT EntryDate FROM Emp WHERE Name = '方东白';
-- b. 查询指定入职日期之后入职的员工信息
SELECT * FROM Emp WHERE EntryDate > (SELECT EntryDate FROM Emp WHERE Name = '方东白');

操作符	描述
IN	在指定集合范围之内
NOT IN	不在指定集合范围之内
ANY	子查询返回列表值中只需满足其一即可
SOME	效果与ANY等同
ALL	子查询返回列表中所有值均需满足

-- 列子查询(返回列值)
-- 1. 查询 "销售部" 和 "市场部" 的所有员工信息(IN)
-- a. 查询 "销售部" 和 "市场部" 的部门ID
SELECT Id FROM Dept WHERE Name = '销售部' OR Name = '市场部';
-- b. 根据部门ID查询员工信息
SELECT * FROM Emp WHERE Dept_Id IN (SELECT Id FROM Dept WHERE Name = '销售部' OR Name = '市场部');

-- 2. 查询比财务部所有人工资都高的员工信息(ALL)
-- a. 查询所有财务部人员工资
SELECT Id FROM Dept WHERE Name = '财务部';
SELECT Salary FROM Emp WHERE Dept_Id = (SELECT Id FROM Dept WHERE Name = '财务部');
-- b. 比财务部所有人工资都高的员工信息
SELECT * FROM Emp WHERE Salary > ALL (SELECT Salary FROM Emp WHERE Dept_Id = (SELECT Id FROM Dept WHERE Name = '财务部'));

-- 3. 查询比研发部其中任意人工资高的员工信息(ANY/SOME)
-- a. 查询研发部所有人工资
SELECT Salary FROM Emp WHERE Dept_Id = (SELECT Id FROM Dept WHERE Name = '研发部');
-- b. 比研发部其中任意人工资高的员工信息
SELECT * FROM Emp WHERE Salary > SOME (SELECT Salary FROM Emp WHERE Dept_Id = (SELECT Id FROM Dept WHERE Name = '研发部'));

-- 行子查询(返回行值)
-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息;
-- a. 查询 "张无忌" 的薪资及直属领导
SELECT Salary, ManagerId FROM Emp WHERE Name = '张无忌';
-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息;
SELECT * FROM Emp WHERE (Salary, ManagerId) = (SELECT Salary, ManagerId FROM Emp WHERE Name = '张无忌');

-- 表子查询(返回行、列值)
-- 1. 查询与 "鹿杖客", "宋远桥" 的职位和薪资相同的员工信息(IN)
-- a. 查询 "鹿杖客", "宋远桥" 的职位和薪资
SELECT Job, Salary FROM Emp WHERE Name = '鹿杖客' OR Name = '宋远桥';
-- b. 查询与 "鹿杖客", "宋远桥" 的职位和薪资相同的员工信息
SELECT * FROM Emp WHERE (Job, Salary) IN (SELECT Job, Salary FROM Emp WHERE Name = '鹿杖客' OR Name = '宋远桥');

-- 2. 查询入职日期是 "2006-01-01" 之后的员工及其部门信息
-- a. 入职日期是 "2006-01-01" 之后的员工信息
SELECT * FROM Emp WHERE EntryDate > '2006-01-01';
-- b. 查询此部分员工对应的部门信息;
SELECT E.*, D.Name FROM (SELECT * FROM Emp WHERE EntryDate > '2006-01-01') E LEFT JOIN Dept D ON E.Dept_Id = D.Id;

小结：

CREATE TABLE SalGrade (
    Grade INT,
    LoSal INT,
    HiSal INT
) COMMENT '薪资等级表';
INSERT INTO SalGrade VALUES (1, 0, 3000);
INSERT INTO SalGrade VALUES (2, 3001, 5000);
INSERT INTO SalGrade VALUES (3, 5001, 8000);
INSERT INTO SalGrade VALUES (4, 8001, 10000);
INSERT INTO SalGrade VALUES (5, 10001, 15000);
INSERT INTO SalGrade VALUES (6, 15001, 20000);
INSERT INTO SalGrade VALUES (7, 20001, 25000);
INSERT INTO SalGrade VALUES (8, 25001, 30000);

-- 1. 查询员工的姓名、年龄、职位、部门信息（隐式内连接）
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id
SELECT E.Name, E.Age, E.Job, D.Name FROM Emp E, Dept D WHERE E.Dept_Id = D.Id;

-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id
SELECT E.Name, E.Age, E.Job, D.Name FROM Emp E INNER JOIN Dept D ON E.Dept_Id = D.Id WHERE E.Age < 30;

-- 3. 查询拥有员工的部门ID、部门名称
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id
SELECT DISTINCT D.Id, D.Name FROM Emp E, Dept D WHERE E.Dept_Id = D.Id; # 查询各员工所属部门再进行去重

-- 4. 查询所有年龄大于40岁的员工及其归属的部门名称(员工未分配部门仍需展示)(左外连接)
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id
SELECT E.*, D.Name FROM Emp E LEFT JOIN Dept D ON E.Dept_Id = D.Id WHERE E.Age > 40;

-- 5. 查询所有员工的工资等级
-- 表结构: Emp, SalGrade
-- 连接条件: Emp.Salary >= SalGrade.LoSal AND Emp.Salary <= SalGrade.HiSal
SELECT E.*, S.Grade, S.LoSal, S.HiSal FROM Emp E, SalGrade S WHERE E.Salary >= S.LoSal AND E.Salary <= S.HiSal;
SELECT E.*, S.Grade, S.LoSal, S.HiSal FROM Emp E, SalGrade S WHERE E.Salary BETWEEN S.LoSal AND S.HiSal;

-- 6. 查询 "研发部" 所有员工的信息及工资等级
-- 表结构: Emp, SalGrade, Dept
-- 连接条件: Emp.Salary BETWEEN SalGrade.LoSal AND SalGrade.HiSal, Emp.Dept_Id = Dept.Id
-- 查询条件: Dept.Name = '研发部'
SELECT E.*, S.Grade FROM Emp E, Dept D, SalGrade S WHERE E.Dept_Id = D.Id AND (E.Salary BETWEEN S.LoSal AND S.HiSal) AND D.Name = '研发部';

-- 7. 查询 "研发部" 员工的平均工资
-- 表结构: Emp, Dept
-- 连接条件: Emp.Dept_Id = Dept.Id
SELECT AVG(E.Salary) FROM Emp E, Dept D WHERE E.Dept_Id = D.Id AND D.Name = '研发部';

-- 8. 查询工资比 "灭绝" 高的员工信息(标量子查询)
-- a. 查询 "灭绝" 的薪资
SELECT Salary FROM Emp WHERE Name = '灭绝';
-- b. 查询比灭绝工资高的员工数据
SELECT * FROM Emp WHERE Salary > (SELECT Salary FROM Emp WHERE Name = '灭绝');

-- 9. 查询比平均薪资高的员工信息(标量子查询)
-- a. 查询员工的平均薪资
SELECT AVG(Salary) FROM Emp;
-- b. 查询比平均薪资高的员工信息
SELECT * FROM Emp WHERE Salary > (SELECT AVG(Salary) FROM Emp);

-- 10. 查询低于本部门平均工资的员工信息
-- a. 查询指定部门平均薪资
SELECT AVG(E1.Salary) FROM Emp E1 WHERE E1.Dept_Id = 1;
SELECT AVG(E1.Salary) FROM Emp E1 WHERE E1.Dept_Id = 2;
-- b. 查询低于本部门平均工资的员工信息
SELECT * FROM Emp E2 WHERE E2.Salary < (SELECT AVG(E1.Salary) FROM Emp E1 WHERE E1.Dept_Id = E2.Dept_Id);

-- 11. 查询所有的部门信息并统计部门的员工人数
SELECT D.Id, D.Name, (SELECT COUNT(*) FROM Emp E WHERE E.Dept_Id = D.Id) AS '人数' FROM Dept D;
-- 或者使用分组查询

-- 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
-- 表结构: Student, Course, Student_Course
-- 连接条件: Student.Id = Student_Course.StudentId, Course.Id = Student_Course.CourseId
SELECT S.Name, S.No, C.Name FROM Student S, Student_Course SC, Course C WHERE S.Id = SC.StudentId AND SC.CourseId = C.Id;

事务：

事务为一组操作的集合，是一个不可分割的工作单位，事务会将所有操作视为整体向系统提交或撤销操作请求，要么同时成功要么同时失败

事务操作：

指令	功能
SELECT @@autocommit SET @@autocommit = 0	查询事务提交方式设置事务提交方式
COMMIT	提交事务(执行成功)
ROLLBACK	回滚事务(执行失败)
START TRANSACTION / BEGIN	开启事务

-- 数据准备
CREATE TABLE Account (
    Id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    Name VARCHAR(10) COMMENT '姓名',
    Money INT COMMENT '余额'
) COMMENT '账户表';
INSERT INTO Account (Id, Name, Money) VALUES (NULL, '张三', 2000), (NULL, '李四', 2000);

-- 方式1 ———— 设置手动提交
SELECT @@autocommit;
SET @@autocommit = 0; # 设置为手动提交
-- 转账操作
-- 1. 查询张三账户余额
SELECT * FROM Account WHERE Name = '张三';
-- 2. 将张三账户余额 - 1000
UPDATE Account SET Money = Money - 1000 WHERE Name = '张三';
-- 程序执行报错 ...
-- 3. 将李四账户余额 + 1000
UPDATE Account SET Money = Money + 1000 WHERE Name = '李四';
-- 回滚事务
ROLLBACK;

-- 方式2 ———— 开启事务
START TRANSACTION;
-- 转账操作
-- 1. 查询张三账户余额
SELECT * FROM Account WHERE Name = '张三';
-- 2. 将张三账户余额 - 1000
UPDATE Account SET Money = Money - 1000 WHERE Name = '张三';
-- 程序执行报错 ...
-- 3. 将李四账户余额 + 1000
UPDATE Account SET Money = Money + 1000 WHERE Name = '李四';
-- 回滚事务
ROLLBACK;

事务特性（ACID）：

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

事务并发问题：

问题	描述
脏读	某事务读取到另条事务还未提交的数据
不可重复读	某事务先后读取同条记录，但两次读取的数据不同
幻读	某事务按照条件查询数据时，未查询到对应的数据行，但在插入数据时，又因唯一性导致数据插入失败，好似"幻影"

隔离级别：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

指令	功能
SELECT @@transaction_isolation	查看事务隔离级别
SET [SESSION \| GLOBAL] TRANSACTION ISOLATION LEVEL [隔离级别]	设置事务隔离级别

-- 查看事务隔离级别
SELECT @@transaction_isolation;
-- 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

🌟事务的隔离级别越高，数据越安全，但性能越低

进阶篇：

存储引擎：

MySQL体系结构：

连接层、服务层、引擎层、存储层

PixPin_2024-10-02_20-49-25

简介：

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

-- 指定存储引擎
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
    ...
	字段n 字段n类型 [COMMENT 字段n注释]
) ENGINE = INNODB [COMMENT 表注释];
-- 查看存储引擎
SHOW ENGINES;

特点：

InnoDB：

MyISAM：

Memory：

区别：

选择：

索引：

索引（index）是帮助MySQL高效获取数据的数据结构(有序）。除数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，因此可以在这些数据结构上实现高级查找算法，该数据结构即为索引。

简介：

优缺点：

优点	缺点
提高数据检索的效率，降低数据库的IO成本	索引列也要占用空间
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引提高查询效率的同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE操作时，效率降低

索引结构：

索引结构	描述
B+ Tree索引	最常见的索引类型，大部分引擎均支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是MyISAM引擎的特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES

B+ Tree索引：

MySQL索引数据结构对经典的B+Tree进行优化。在原B+Tree的基础上，增加指向相邻叶子节点的链表指针，就形成带有顺序指针的B+Tree，提高区间访问的性能。

索引分类：

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

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引共同放到，索引结构的叶子节点保存有行数据	必须有且唯一
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可重复

聚集索引选取规则：
若存在主键，主键索引即为聚集索引
若不存在主键，将使用首个唯一（UNIQUE）索引作为聚集索引
若表无主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

语法：

命令	功能
CREATE [UNIQUE \| FULLTEXT] INDEX 索引名 ON 表名（字段列表[^6]）	创建索引
SHOW INDEX FROM 表名	查看索引
DROP INDEX 索引名 ON 表名	删除索引

CREATE INDEX Index_User_Name ON Tb_User (Name); -- 常规索引
CREATE UNIQUE INDEX Index_User_Phone ON Tb_User (Phone); -- 唯一索引
CREATE INDEX Index_User_Pro_Age_Sta ON Tb_User (Profession, Age, Status); -- 联合索引

索引使用原则：

最左前缀法则：

联合索引要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，若跳跃某列，索引将部分失效（后方的字段索引失效）

# 索引字段 ———— Pro、Age、Sta
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程' AND Age = 31 AND Status = '0';
EXPLAIN SELECT * FROM Tb_User WHERE Age = 31 AND Status = '0' AND Profession = '软件工程';
# 索引字段 ———— Pro、Age
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程' AND Age = 31;
# 索引字段 ———— Pro
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程';
# 索引字段 ———— Pro (Age字段缺失导致Pro索引失效)
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程' AND Status = '0';
# 无索引(全表扫描)
EXPLAIN SELECT * FROM Tb_User WHERE Age = 31 AND Status = '0';
# 无索引(全表扫描)
EXPLAIN SELECT * FROM Tb_User WHERE Status = '0';

索引失效：

范围查询：

联合索引中，出现范围查询（>、<），范围查询右侧的列索引失效

# 索引字段 ———— Pro、Age (Age字段使用'>'导致Status索引失效)
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程' AND Age > 30 AND Status = '0';
# 索引字段 ———— Pro、Age、Sta
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程' AND Age >= 30 AND Status = '0';

索引列运算：

不可在索引列上进行运算操作，索引将失效

EXPLAIN SELECT * FROM Tb_User WHERE SUBSTRING(Phone, 10, 2) = '15';

字符串查询：

字符串类型字段使用时，不加引号，查询不受影响但是索引将失效

EXPLAIN SELECT * FROM Tb_User WHERE Phone = 17799990015;
EXPLAIN SELECT * FROM Tb_User WHERE Profession = '软件工程' AND Age = 31 AND Status = 0;

模糊查询：

若使用尾部模糊匹配，索引不会失效；若使用头部模糊匹配，索引失效

EXPLAIN SELECT * FROM Tb_User WHERE Profession LIKE '软件%';
EXPLAIN SELECT * FROM Tb_User WHERE Profession LIKE '%工程';
EXPLAIN SELECT * FROM Tb_User WHERE Profession LIKE '%工%';

OR链接条件：

使用 OR 分隔的条件，只有当 OR 前后的字段均可使用索引时方可生效，否则双方索引均失效

EXPLAIN SELECT * FROM Tb_User WHERE Id = 10 OR Age = 23; 
EXPLAIN SELECT * FROM Tb_User WHERE Phone = '17799990017' OR Age = 23;

数据分布影响：

若MySQL评估使用索引比全表更慢，则不使用索引

SELECT * FROM Tb_User WHERE Phone >= '17799990005';
SELECT * FROM Tb_User WHERE Phone >= '17799990015';

SQL提示：

SQL提示是加入某些人为的提示来达到优化操作的目的，是优化数据库的重要手段

指令	作用
USE INDEX（索引名）	建议索引
IGNORE INDEX（索引名）	忽略索引
FORCE INDEX（索引名）	强制索引

EXPLAIN SELECT * FROM Tb_User USE INDEX (Idx_User_Pro) WHERE Profession = '软件工程';
EXPLAIN SELECT * FROM Tb_User IGNORE INDEX (Idx_User_Pro) WHERE Profession = '软件工程';
EXPLAIN SELECT * FROM Tb_User FORCE INDEX (Idx_User_Pro) WHERE Profession = '软件工程';

覆盖索引：

尽量使用覆盖索引（查询使用索引，并且需要返回的字段在该索引中已经全部能够找到），尽量避免使用 SELECT*。

# 覆盖查询
SELECT * FROM Tb_User WHERE Id = 2;
# 覆盖查询
SELECT Id, Name FROM Tb_User WHERE Name = 'Arm';
# 非覆盖查询
SELECT Id, Name, Gender FROM Tb_User WHERE Name = 'Arm';

前缀索引：

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

# 语法
CREATE INDEX 前缀索引名 ON 表名(字段名(前缀长度));

前缀长度可以根据索引的选择性来决定，选择性是指不重复的索引值（基数）和与数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性为 '1' ，性能是最好的

SELECT COUNT(DISTINCT Email) / COUNT(*) AS Unique_Email_Ratio FROM Tb_User;
# 计算Tb_User表中Email字段前5个字符的选择性
SELECT COUNT(DISTINCT SUBSTRING(Email, 1, 5)) / COUNT(*) AS Unique_Email_Prefix_Ratio FROM Tb_User;

单列索引与联合索引：

在业务场景中，若存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。若使用单列查询，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

小结：

针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）操作的字段建立索引
尽量选择区分度高的列作为索引，区分度越高，使用索引的效率越高
对于长度较长的字符串类型的字段，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多情况可以覆盖索引，节省存储空间，避免回表，提高查询效率
控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
若索引列不能存储NULL值，务必创建表时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，可以更好地确定使用哪个索引最有效地用于查询

SQL优化：

分析：

SQL性能分析：

MySQL 客户端连接成功后，通过 SHOW [SESSION | GLOBAL] STATUS 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次，从而确定具体的优化策略：

SHOW GLOBAL STATUS LIKE 'Com_______'
'Com_______' # 代表模糊匹配七个字符

慢查询日志：

慢查询日志中记录所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志（默认未开启，需要在MySQL的配置文件（/etc/my.cnf）中配置）。
配置完毕之后，重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

# 配置慢查询日志信息
# 开启MySQL慢日志查询开关
slow_query_log=1 
# 设置慢日志的时间为2秒
long_query_time=2

# 实时跟踪
tail -f localhost-slow.log

profile 详情：

指令	功能
SELECT @@have_profiling	查询是支持 profile功能
SET [SESSION \| GLOBAL] profiling = 1	开启 profile功能
SHOW profiles	查看每条SQL的耗时基本情况
SHOW profile FOR QUERY Query_Id	查看指定Query_Id的SQL语句各个阶段的耗时情况
SHOW profile CPU FOR QUERY Query_Id	查看指定Query_Id的SQL语句CPU的使用情况

explain 执行计划：

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

# SELECT语句之前加上关键字 EXPLAIN ／ DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件

字段详解：

字段名称	字段含义
id	SELECT查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type	表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中第二个或后面的查询语句）、SUBQUERY（SELECT/WHERE后包含子查询）等
type	表示连接类型，性能：NULL > system > const > eq_ref > ref > range > index > all
possible_key	显示可能应用的索引
key	实际使用的索引，如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows	MySQL认为必须要执行查询的行数（不准确）
filtered	表示返回结果的行数占需读取行数的百分比，越大越好

优化：

数据插入：

# 批量插入代替逐条插入
INSERT INTO Tb_Test VALUES (1, 'Tom'), (2, 'Cat'), (3, 'Jerry');

# 手动提交事务代替自动提交事务
START TRANSACTION;
INSERT INTO Tb_Test VALUES (1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
INSERT INTO Tb_Test VALUES (4, 'Tom'), (5, 'Cat'), (6, 'Jerry');
INSERT INTO Tb_Test VALUES (7, 'Tom'), (8, 'Cat'), (9, 'Jerry');
COMMIT;

# 主键优先考虑顺序插入
主键乱序插入: 8 19 21 88 24 15 89 57 3
主键顺序插入: 1 2 3 4 6 7 15 21 88 89

# 本地数据加载
# 客户端连接服务端时,参数 --local-infile 加载本地数据
mysql --local-infile -u root -p
# 设置全局参数 local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令,将准备好的数据加载到表结构中
load data local infile '数据文件路径' into table 表名 fields terminated by ',' lines terminated by '\n' ;

主键优化：

主键设计原则：
1、满足业务需求的情况下，尽量降低主键的长度。
2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号
4、业务操作时，避免对主键的修改。

页分裂与页合并

ORDER BY优化：

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

# 未创建索引时,根据Age,Phone进行排序(Using filesort)
EXPLAIN SELECT Id, Age, Phone FROM Tb_User ORDER BY Age, Phone;
# 创建索引
CREATE INDEX Idx_User_Age_Phone_AA ON Tb_User(Age, Phone);
# 创建索引后,根据Age,Phone进行升序排序(Using index)
EXPLAIN SELECT Id, Age, Phone FROM Tb_User ORDER BY Age, Phone;
# 创建索引后,根据Age,Phone进行降序排序(Using index)
EXPLAIN SELECT Id, Age, Phone FROM Tb_User ORDER BY Age DESC, Phone DESC;

# 根据Age升序,Phone降序排序(Using filesort)
EXPLAIN SELECT Id, Age, Phone FROM Tb_User ORDER BY Age ASC, Phone DESC;
# 创建索引
CREATE INDEX Idx_User_Age_Phone_AD ON Tb_User(Age ASC, Phone DESC);
# 根据Age升序,Phone降序排序(Using index)
EXPLAIN SELECT Id, Age, Phone FROM Tb_User ORDER BY Age ASC, Phone DESC;

1、据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2、尽量使用覆盖索引
3、多字段排序，升序与降序组合使用时需要注意联合索引在创建时的规则（ASC/DESC）
4、若不可避免地出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

GROUP BY优化：

# 删除掉当前的联合索引 Idx_User_Pro_Age_Sta
DROP INDEX Idx_User_Pro_Age_Sta ON Tb_User;
# 执行分组操作,根据Profession字段分组(Using temporary)
EXPLAIN SELECT Profession, COUNT(*) FROM Tb_User GROUP BY Profession;
# 创建索引
CREATE INDEX Idx_User_Pro_Age_Sta ON Tb_User(Profession, Age, Status);
# 执行分组操作,根据Profession字段分组(Using index)
EXPLAIN SELECT Profession, COUNT(*) FROM Tb_User GROUP BY Profession;
# 执行分组操作,根据Age字段分组(Using temporary,Using index)
EXPLAIN SELECT Age, COUNT(*) FROM Tb_User GROUP BY Age;
# 执行分组操作,根据Profession,Age字段分组(Using index)
EXPLAIN SELECT Profession, Age, COUNT(*) FROM Tb_User GROUP BY Profession, Age;
# 执行分组操作,根据Profession字段过滤再根据Age字段分组(Using index)
EXPLAIN SELECT Age, COUNT(*) FROM Tb_User WHERE Profession = '软件工程' GROUP BY Age;

LIMIT优化：

LIMIT语句在大数据查询时效率较低。例如LIMIT 2000000，10，此时需要 MySQL排序前2000010记录，仅仅返回排序后的
前十条记录，其他记录丢弃，查询排序的代价非常大

优化思路：分页查询时，可以通过 "覆盖索引 + 子查询" 形式进行优化

EXPLAIN SELECT * FROM Tb_Sku T, (SELECT Id FROM Tb_Sku ORDER BY Id LIMIT 2000000, 10) A WHERE T.Id = A.Id;

COUNT优化：

MyISAM 引擎将表的总行数存储磁盘上，因此执行count（）函数时会直接返回结果，效率很高
InnoDB 引擎执行count（）时则需要将数据逐行从引擎里读取，然后累积计数，效率较低
优化思路：自行计数

# count(主键)
InnoDB引擎 遍历整张表,将各行的主键Id值都取出来,返回服务层。服务层拿到主键后,直接按行进行累加(主键不可能为Null)
# count(字段)
无 Not Null 约束:InnoDB引擎 遍历整张表,将各行字段值取出,返回给服务层,服务层判断是否为Null,若不为Null,计数累加
有 Not Null 约束:InnoDB引擎 遍历整张表,将各行字段值取出,返回给服务层,直接按行进行累加
# count(1)
InnoDB引擎 遍历整张表,但不取值,服务层对于返回的各行,赋值数字"1",直接按行进行累加
# count(*)
InnoDB引擎 不会将所有字段取出,而是专门优化为不取值,服务层直接按行进行累加
# 按照效率排序
count(字段) < count(主键Id) < count(1) ~ count(*)

UPDATE优化：

InnoDB的行锁是针对索引添加的，而不是针对记录添加的，并且该索引不能失效，否则会从行锁升级为表锁，导致事务并发性降低

# 根据索引属性更新数据(行锁)
UPDATE Student SET No = '2000100100' WHERE Id = 1;
# 根据普通属性更新数据(表锁)
UPDATE Student SET No = '2000100105' WHERE Name = 'Tassel';

存储对象：

视图：

简介：

视图（View）是虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表（基表），并且是在使用视图时动态生成的；通俗地讲，视图只保存查询的SQL逻辑，不保存查询结果

指令	作用
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] 视图名称 [视图名称] ...	删除视图

基础用法：

-- 创建视图
CREATE OR REPLACE VIEW Stu_V_1 AS SELECT Id, Name FROM Student WHERE Id <= 10;
-- 查询视图
SHOW CREATE VIEW Stu_V_1;

SELECT * FROM Stu_V_1;
SELECT * FROM Stu_V_1 WHERE Id < 3;
-- 修改视图
CREATE OR REPLACE VIEW Stu_V_1 AS SELECT Id, Name, No FROM Student WHERE Id <= 10;

ALTER VIEW Stu_V_1 AS SELECT Id, Name FROM Student WHERE Id <= 10;
-- 删除视图
DROP VIEW IF EXISTS Stu_V_1;

检查选项：

使用 WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的各行，例如插入、更新、删除，以使其符合视图的定义。MySQL允许基于视图创建视图，且会检查依赖视图中的规则以保持数据一致性。为确定检查范围，MySQL提供两个参数选项：
CASCADED（级联）与 LOCAL，默认值为 CASCADED。

-- 视图更改表数据
CREATE OR REPLACE VIEW Stu_V_1 AS SELECT Id, Name FROM Student WHERE Id <= 10 WITH CASCADED CHECK OPTION;
SELECT * FROM Stu_V_1;

INSERT INTO Stu_V_1 VALUES (6, 'Tom');
INSERT INTO Stu_V_1 VALUES (17, 'Tom22'); # 执行失败(插入数据超出视图定义范围)

-- Cascaded
CREATE OR REPLACE VIEW Stu_V_1 AS SELECT Id, Name FROM Student WHERE Id <= 20;
INSERT INTO Stu_V_1 VALUES (5, 'Tom');  # 执行成功
INSERT INTO Stu_V_1 VALUES (25, 'Tom'); # 执行成功

CREATE OR REPLACE VIEW Stu_V_2 AS SELECT Id, Name FROM Stu_V_1 WHERE Id >= 10 WITH CASCADED CHECK OPTION;
INSERT INTO Stu_V_2 VALUES (7, 'Tom');  # 执行失败(Stu_V_2检查选项未通过)
INSERT INTO Stu_V_3 VALUES (26, 'Tom'); # 执行失败(Stu_V_1检查选项未通过)
INSERT INTO Stu_V_2 VALUES (15, 'Tom'); # 执行成功

CREATE OR REPLACE VIEW Stu_V_3 AS SELECT Id, Name FROM Stu_V_2 WHERE Id < 15;
INSERT INTO Stu_V_3 VALUES (11, 'Tom'); # 执行成功
INSERT INTO Stu_V_3 VALUES (17, 'Tom'); # 执行成功(Stu_V_3未加检查选项,则跳过其向前检查)
INSERT INTO Stu_V_3 VALUES (28, 'Tom'); # 执行失败(Stu_V_1检查选项未通过)

-- Local
CREATE OR REPLACE VIEW Stu_V_4 AS SELECT Id, Name FROM Student WHERE Id <= 15;
INSERT INTO Stu_V_4 VALUES (5, 'Tom');  # 执行成功
INSERT INTO Stu_V_4 VALUES (16, 'Tom'); # 执行成功

CREATE OR REPLACE VIEW Stu_V_5 AS SELECT Id, Name FROM Stu_V_4 WHERE Id >= 10 WITH LOCAL CHECK OPTION;
INSERT INTO Stu_V_5 VALUES (13, 'Tom'); # 执行成功
INSERT INTO Stu_V_5 VALUES (17, 'Tom'); # 执行成功(检查选项未继承到Stu_V_1)

CREATE OR REPLACE VIEW Stu_V_6 AS SELECT Id, Name FROM Stu_V_5 WHERE Id < 20;
INSERT INTO Stu_V_6 VALUES (14, 'Tom'); # 执行成功

视图更新：

若使视图可更新，视图中的行与基础表中的行之间必须存在一一映射的关系。若视图包含以下任何一项，则该视图不可更新：
1、聚合函数或窗口函数（SUM、MIN、MAX、COUNT等）
2、DISTINCT
3、GROUP BY
4、HAVING
5、UNION 或 UNION ALL

-- 创建视图使用聚合函数
CREATE VIEW Stu_V_Count AS SELECT COUNT(*) FROM Student;
INSERT INTO Stu_V_Count VALUES (10); # 执行失败

视图作用：

简单：
视图不仅可以简化用户对数据的理解，也可以简化其操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
安全：
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
数据独立：
视图可帮助用户屏蔽真实表结构变化带来的影响

案例应用：

-- 1. 保证数据库表的安全性,开发人员在操作 tb_user 表时,只能查看用户的基本字段,屏蔽手机号和邮箱
CREATE VIEW Tb_User_View AS SELECT Id, Name, Profession, Age, Gender, Status, CreateTime FROM Tb_User;
SELECT * FROM Tb_User_View;

-- 2. 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。
CREATE VIEW Tb_Stu_Course_View AS SELECT S.Name AS Student_Name, S.No AS Student_No, C.Name AS Course_Name 
FROM Student S, Student_Course Sc, Course C WHERE S.Id = Sc.StudentId AND Sc.CourseId = C.Id;
SELECT * FROM Tb_Stu_Course_View;

存储过程：

存储过程是事先经过编译并存储在数据库中的SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据
库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程在思想上很简单，就是数据库SQL语言层面的代码封装与重用。

基本语法：

# 存储过程创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	-- SQL语句
END;
# 存储过程调用
CALL 名称 ([参数 ]);
# 存储过程查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称; -- 查询指定存储过程的定义
# 存储过程删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;
# 注意,由于MySQL命令分隔默认为 ';',所以在命令行中创建存储过程将会出现异常,此时需要利用delimiter指定SQL语句结束符,创建完毕后再恢复默认值
delimiter $$

变量：

系统变量：

系统变量是由MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）

# 查看系统变量
SHOW [SESSION|GLOBAL] VARIABLES; -- 查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...'; -- 通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION|GLOBAL].系统变量名; -- 查看指定变量的值
# 设置系统变量
SET [SESSION|GLOBAL] 系统变量名 = 值;
SET @@[SESSION|GLOBAL] 系统变量名 = 值;
# 注意
若没有指定 SESSION|GLOBAL,默认 SESSION会话变量
MySQL服务重新启动后,所设置的全局参数会失效,若要永久保留可在/etc/my.cnf中配置

自定义变量：

用户定义变量是用户根据自身需要定义的变量，用户变量不用提前声明，使用时直接"@变量名"即可，其作用域为当前
会话。

# 赋值
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 表名;
# 使用
SELECT @var_name;
# 注意,用户自定义变量无需提前声明,可以直接获取未定义变量(默认为NULL)

局部变量：

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明，可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN..END块

# 声明
DECLARE 变量名 变量类型 [DEFAULT..];
变量类型: INT、BIGINT、CHAR、VARCHAR、DATE、TIME 等
# 赋值
SET 变量名=值;
SET 变量名:=值;
SELECT 字段名 INTO 变量名 FROM 表名;

执行流程：

IF语句：

# 语法
IF 条件1 THEN
	...
ELSE IF 条件2 THEN -- 可选
	...
ELSE -- 可选
	...
END IF;

# 实例
CREATE PROCEDURE P1()
BEGIN
    DECLARE Score INT DEFAULT 58;
    DECLARE Result VARCHAR(10);
    IF Score >= 85 THEN
        SET Result := '优秀';
    ELSEIF Score >= 60 THEN
        SET Result := '及格';
    ELSE
        SET Result := '不及格';
    END IF;
    SELECT Result;
END;

参数：

类型	含义	备注
IN	该类参数作为输入，即调用时传入值	默认
OUT	该类参数作为输出，即可作为返回值	——
INOUT	既可以作为输入参数，也可以作为输出参数	——

# 语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT 参数名 参数类型])
BEGIN
	-- SQL语句
END;
# 实例
CREATE PROCEDURE P2(IN Score INT,OUT Result VARCHAR(10))
BEGIN
    IF Score >= 85 THEN
        SET Result := '优秀';
    ELSEIF Score >= 60 THEN
        SET Result := '及格';
    ELSE
        SET Result := '不及格';
    END IF;
END;
CALL P2(99,@Result);
SELECT @Result;

CASE语句：

# 语法一
CASE Case_Value
	WHEN When_Valuel THEN Statement_List1
	[WHEN When_Valuel THEN Statement_List2]...
	[ELSE Statement_List]
END CASE;
# 语法二
CASE
	WHEN Search_Condition1 THEN Statement _List1
	[WHEN Search_Condition1 THEN Statement _List2]...
	[ELSE Statement _List]
END CASE;
# 实例
CREATE PROCEDURE P3(IN Month INT)
BEGIN
    DECLARE Result VARCHAR(10);
    CASE
        WHEN Month >= 1 AND Month <= 3 THEN
            SET Result := '第一季度';
        WHEN Month >= 4 AND Month <= 6 THEN
            SET Result := '第二季度';
        WHEN Month >= 7 AND Month <= 9 THEN
            SET Result := '第三季度';
        WHEN Month >= 10 AND Month <= 12 THEN
            SET Result := '第四季度';
        ELSE
            SET Result := '非法参数';
    END CASE;
    SELECT CONCAT('您输入的月份为：', Month, '，所属的季度为：', Result);
END;

WHILE语句：

# 语法
# 先判定条件,若条件为true,则执行逻辑,否则,不执行逻辑
WHILE 条件 DO
	SQL 逻辑...
END WHILE;
# 实例
CREATE PROCEDURE P4(IN N INT)
BEGIN
    DECLARE Total INT DEFAULT 0;
    WHILE N > 0 DO
        SET Total := Total + N;
        SET N := N - 1;
    END WHILE;
    SELECT Total;
END;

REPEAT语句：

# 先执行逻辑,后判定逻辑是否满足,若满足,则退出,否则继续下次循环
REPEAT
	SQL 逻辑...
	UNTIL 条件
END REPEAT;

LOOP语句：

# 语法
[Begin_Label:] LOOP
	SQL 逻辑...
END LOOP [End_Label];
LEAVE Label; -- 退出指定标记的循环体
ITERATE Label; -- 跳过当轮循环
# 实例
DECLARE Total INT DEFAULT 0;
Sum_Loop: LOOP
    IF N <= 0 THEN
        LEAVE Sum_Loop;
    END IF;
    IF N % 2 = 1 THEN
        SET N := N - 1;
        ITERATE Sum_Loop;
    END IF;
    SET Total := Total + N;
    SET N := N - 1;
END LOOP Sum_Loop;
SELECT Total;

CURSOR游标：

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE。

语法	功能
DECLARE 游标名称 CURSOR FOR 查询语句	声明游标
OPEN 游标名称	打开游标
FETCH 游标名称 INTO 变量[，变量 ]	获取游标记录
CLOSE 游标名称	关闭游标

# 案例
-- 根据传入参数Uage,查询用户表Tb_User中,所有的用户年龄小于等于Uage的用户姓名(Name)和专业(Profession)
-- 并将用户的姓名和专业插入到所创建的新表(Id,Name,Profession)中
CREATE PROCEDURE P5(IN UAge INT)
BEGIN
	# 声明变量用于接受数据
    DECLARE UName VARCHAR(100);
    DECLARE UPro VARCHAR(100);
    -- A. 声明游标, 存储查询结果集
    DECLARE UCursor CURSOR FOR SELECT Name, Profession FROM TB_User WHERE Age <= UAge;
    # 声明条件处理程序
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE UCursor;
	-- B. 准备:创建表结构
    DROP TABLE IF EXISTS TB_User_Pro;
    CREATE TABLE IF NOT EXISTS TB_User_Pro (
        ID INT PRIMARY KEY AUTO_INCREMENT,
        Name VARCHAR(100),
        Profession VARCHAR(100)
    );
	-- C. 开启游标
    OPEN UCursor;
    REPEAT
    	-- D. 获取游标中的记录
        FETCH UCursor INTO UName, UPro;
        -- E. 插入数据到新表中
        IF FOUND THEN
            INSERT INTO TB_User_Pro VALUES (NULL, UName, UPro);
        END IF;
    UNTIL NOT FOUND
    END REPEAT;
	-- F. 关闭游标
    CLOSE UCursor;
END;
CALL P5(30);

HANDLER程序：

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤

DECLARE Handler_Action HANDLER FOR Condition_Value [,Condition_Value]... Statement;
Handler_Action
	CONTINUE:继续执行当前程序
	EXIT:终止执行当前程序
Condition_Value
	SQLSTATE Sqlstate_Value:状态码,如 02000
	SQLWARNING:所有以O1开头的SQLSTATE代码的简写
	NOT FOUND:所有以O2开头的SQLSTATE代码的简写
	SQLEXCEPTION:所有没有被"SQLWARNING"或"NOT FOUND"捕获的SQLSTATE代码的简写

存储函数：

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型

# 语法
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS Type [Characteristic...]
BEGIN
	-- SQL语句
	RETURN ...;
END;
# Characteristic说明:
DETERMINISTIC:相同的输入参数总是产生相同的结果
NO SQL:不包含SQL语句
READS SQL DATA:包含读取数据的语句,但不包含写入数据的语句
# 实例
CREATE FUNCTION F1(N INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE Total INT DEFAULT 0;
    WHILE N > 0 DO
        SET Total := Total + N;
        SET N := N - 1;
    END WHILE;
    RETURN Total;
END;
SELECT F1(50);

触发器：

简介：

触发器是与表有关的数据库对象，指在 INSERT/UPDATE/DELETE 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。MySQL使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，目前触发器只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD
INSERT型触发器	NEW表示将要或者已经新增的数据
UPDATE型触发器	OLD表示修改前的数据，NEW表示将要或已经修改后的数据
DELETE型触发器	OLD表示将要或者已经删除的数据

基础语法：

# 创建
CREATE TRIGGER Trigger_Name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON Table_Name FOR EACH ROW -- 行级触发器
BEGIN
	Trigger_Stmt ;
END;
# 查看当前数据库所有触发器
SHOW TRIGGERS ;
# 删除
DROP TRIGGER [数据库名.]Trigger_Name; -- 若没有指定数据库名默认为当前数据库

实例：

-- 通过触发器记录User表的数据变更日志(User_Logs),包含增加、修改、删除
-- 准备:日志表User_Logs
CREATE TABLE User_Logs (
    ID INT(11) NOT NULL AUTO_INCREMENT,
    Operation VARCHAR(20) NOT NULL COMMENT '操作类型, INSERT/UPDATE/DELETE',
    Operate_Time DATETIME NOT NULL COMMENT '操作时间',
    Operate_ID INT(11) NOT NULL COMMENT '数据原始ID',
    Operate_Params VARCHAR(500) COMMENT '操作参数',
    PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据触发器
CREATE TRIGGER TB_User_Insert_Trigger
AFTER INSERT ON TB_User FOR EACH ROW
BEGIN
    INSERT INTO User_Logs (ID, Operation, Operate_Time, Operate_ID, Operate_Params) VALUES
    (NULL, 'Insert', NOW(), NEW.ID,
     	CONCAT('插入的数据内容为: ID=', NEW.ID, ', Name=', NEW.Name, ', Phone=', NEW.Phone, ', Email=', NEW.Email, ', Profession=', NEW.Profession));
END;
-- 插入数据到TB_User
INSERT INTO TB_User (ID, Name, Phone, Email, Profession, Age, Gender, Status, CreateTime) VALUES (26, '三皇子', '18809091212', 'erhuangzi@163.com', '软件工程', 23, '1', '1', NOW());

-- 修改数据触发器
CREATE TRIGGER TB_User_Update_Trigger
AFTER UPDATE ON TB_User FOR EACH ROW
BEGIN
    INSERT INTO User_Logs (ID, Operation, Operate_Time, Operate_ID, Operate_Params) VALUES
    (NULL, 'Update', NOW(), NEW.ID,
        CONCAT('更新前数据: ID=', OLD.ID, ', Name=', OLD.Name, ', Phone=', OLD.Phone, ', Email=', OLD.Email, ', Profession=', OLD.Profession,
               ' | 更新后数据: ID=', NEW.ID, ', Name=', NEW.Name, ', Phone=', NEW.Phone, ', Email=', NEW.Email, ', Profession=', NEW.Profession));
END;
-- 更新TB_User数据
UPDATE TB_User SET Profession = '会计' WHERE ID = 23;
UPDATE TB_User SET Profession = '会计' WHERE ID <= 5;

-- 删除数据触发器
CREATE TRIGGER TB_User_Delete_Trigger
AFTER DELETE ON TB_User FOR EACH ROW
BEGIN
    INSERT INTO User_Logs (ID, Operation, Operate_Time, Operate_ID, Operate_Params) VALUES
    (NULL, 'Delete', NOW(), OLD.ID,
        CONCAT('删除之前的数据: ID=', OLD.ID, ', Name=', OLD.Name, ', Phone=', OLD.Phone, ', Email=', OLD.Email, ', Profession=', OLD.Profession));
END;
-- 删除TB_User数据
DELETE FROM TB_User WHERE ID = 26;

锁：

简介：

锁是计算机协调多进程或线程并发访问某资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是
供许多用户共享的资源，因此如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的重要因素。从这角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁	描述
全局锁	锁定数据库中的所有表
表级锁	每次操作锁住整张表
行级锁	每次操作锁住对应的行数据

全局锁：

全局锁即对整个数据库实例加锁，加锁后整个实例处于只读状态，后续的DML写语句，DDL语句以及已更新操作的事务提交语句都
将被阻塞。其典型的应用场景即为全库的逻辑备份，此时对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

指令	作用
FLUSH TABLES WITH READ LOCK	开启全局锁
mysqldump -uroot -p密码数据库名 > 路径名[^7]	开启全库逻辑备份
UNLOCK TABLE	解除全局锁

问题：
1、若在主库上备份，那么在备份期间都不能执行更新，业务基本停摆
2、若在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟
拓展：
在InnoDB引擎中，可在备份时加上参数 --single-transaction 参数来完成不加锁的数据备份
mysqldump --single-transaction -uroot -p666666 tassel > tassel.sql

表级锁：

表锁：

# 语法
# 加锁
LOCK TABLES 表名... READ|WRITE
# 解锁
UNLOCK TABLES|客户端断开连接

表共享读锁（READ LOCK）：

加上表共享读锁后，本客户端与其他客户端对于该表均只有读权限，无写权限

表独占写锁（WRITE LOCK）：

加上表独占写锁后，本客户端对于该表具有读、写权限，但其他客户端无读、写权限

元数据锁锁（META DATA LOCK，MDL）：

MDL加锁过程是系统自动控制，无需显式使用，在访问表的时候会自动添加。MDL锁主要作用是维护表元数据（属性）的数据一致性，当表上有活动事务时，不可以对元数据（属性）进行写入操作，避免DML与DDL冲突，保证读写的正确性。
MySQL5.5后，当用户对表进行增删改查的时候，自动添加MDL读锁（共享）；对表结构进行变更操作的时候，自动添加MDL写锁（排他）

SQL语句	元数据锁类型	说明
LOCK TABLES xxx READ\|WRITE	SHARED_READ_ONLY / SHARED_NO_READ_WRITE	——
SELECT 、SELECT ... LOCK IN SHARE MODE	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
ALTER TABLE ...	EXCLUSIVE	与剩余MDL均互斥

查看元数据锁：
SELECT object_type，object_schema，object_name，lock_type，lock_duration FROM performance_schema.metadata_locks；

意向锁：

意向锁主要用于解决数据库中表锁与行锁之间的冲突问题

锁类型	添加情况	兼容情况
意向共享锁（IS）	由语句 SELECT ... LOCK IN SHARE MODE 添加	与表锁共享锁（READ）兼容，与表锁排它锁（WRITE）互斥
意向排他锁（IX）	由语句 INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE 添加	与表锁共享锁（READ）及排它锁（WRITE）均互斥，意向锁之间不会互斥

查看意向锁与行锁：
SELECT object_schema，object_name，index_name，lock_type，lock_mode，lock_data FROM performance_schema.data_locks；

行级锁：

简介：

行级锁，每次操作锁住对应的行数据，锁定力度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁

分类	描述	隔离
行锁（Record Lock）	锁定单行记录的锁，防止其他事务对此行进行UPDATE和DELETE	支持RC、RR隔离级别
间隙锁（Gap Lock）	锁定索引记录间隙（不含该记录），确保索引记录间隙不变防止其他事务在此间隙进行INSERT，产生幻读	支持RR隔离级别
临键锁（Next-KeyLock）	行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap	支持RR隔离级别

行锁：

行锁包括共享锁（S）与排他锁（X）两种类型：
共享锁（S）：允许事务读取行数据，阻止其他事务获得相同数据集的排它锁
排他锁（X）：允许获取排他锁的事更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

行锁兼容情况：

行锁添加情况：

默认情况，InnoDB在 REPEATABLE READ 事务隔离级别 运行时，InnoDB将用 Next-Key锁进行搜索和索引扫描，以防止幻读
1．针对UNIQUE索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
2．InnoDB的行锁是针对于索引加的锁，若不通过索引条件检索数据，InnoDB将对表中的所有记录加锁，此时就会升级为表锁

间隙锁、临键锁：

间隙锁作用是为防止其他事务插入间隙，间隙锁可以共存，某事务采用的间隙锁不会阻止另外事务在同间隙上采用间隙锁。

默认情况，InnoDB在 REPEATABLE READ 事务隔离级别 运行时，InnoDB将用 Next-Key锁进行搜索和索引扫描，以防止幻读
1、索引上的等值查询（UNIQUE索引），对不存在的记录加锁时，优化为间隙锁
2、索引上的等值查询（常规索引），向右遍历至最后不满足查询需求的值时，Next-Key Lock 退化为间隙锁
3、索引上的范围查询（UNIQUE索引），会访问到不满足条件的首个值为止

InnoDB引擎：

逻辑存储结构：

	表空间（TableSpace）→段（Segment）→区（Extent）→页（Page）→行（Row）

1、表空间（IBD文件）：SQL实例可拥有多个表空间，用于存储记录、索引等数据
2、段：分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB
是索引组织表，数据段为B+树的叶子节点，索引段即为B+树的非叶子节点
3、区：表空间的单元结构，每个区的大小为1M，默认情况下，InnoDB存储引擎页大小为16K，即单个区中共有64个连续的页
4、页：InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB；并且，为保证页的连续性，InnoDB存储引擎每次会从磁盘中申请 4-5 个区
5、行：InnoDB 存储引擎数据是按行进行存放的，在行中有两个特殊字段 —— Trx_id：每次对某条记录进行改动时，都会把对应的事务ID赋值给Trx_id隐藏列；Roll_pointer：每次对某条引记录进行改动时，都会将旧的版本写入到 Undo日志 中，Roll_pointer隐藏列相当于指针，可以通过它来找到该记录修改前的信息

架构：

内存架构：

Buffer Pool：
缓冲池是主内存中的区域，可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。缓冲池以Page页为单位，底层采用链表数据结构管理Page。

根据状态，将Page分为三种类型：
Free Page：空闲Page，未被使用
Clean Page：被使用Page，数据没有被修改过
Dirty Page：脏页，被使用Page，数据被修改过，其中数据与磁盘的数据不一致

Change Buffer：
更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，最后将合并后的数据刷新到磁盘中

Adaptive Hash Index：
自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，若观察到Hash索引可以提升查询速度，则建立Hash索引，称之为自适应Hash索引，自适应哈希索引无需人工干预，是系统根据情况自动完成

状态参数：adaptive_hash_index

Log Buffer：
日志缓冲区，用来保存要写入到磁盘中的 Log日志 数据（Redo Log、Undo Log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O

参数：
innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机
0：每秒将日志写入并刷新到磁盘
1：日志在每次事务提交时写入并刷新到磁盘
2：日志在每次事务提交后写入，并每秒刷新到磁盘

磁盘架构：

System Tablespace：
系统表空间是更改缓冲区的存储区域，若表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据（MySQL5.x版本中还包含 lnnoDB数据字典、Undo Log 等)

参数：innodb_data_file_path

File-Per-Table Tablespaces：
每个表的文件表空间包含单个lnnoDB表的数据和索引，并存储在文件系统上的单个数据文件中

参数：innodb_file_per_table

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

# 创建通用表空间
CREATE TABLESPACE XXXX ADD
DATAFILE 'File_Name'
ENGINE = Engine_Name;
# 关联表空间
CREATE TABLE XXX... TABLESPACE Tablespace_Name;

Undo Tablespaces：撤销表空间，MySQL实例在初始化时会自动创建两个默认的Undo表空间（初始大小16M），用于存储Undo Log日志
Temporary Tablespaces：InnoDB使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据
Doublewrite Buffer Files：双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
Redo Log：重做日志，用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（Redo Log Buffer）以及重做日志文件（Redo Log），前者是在内存中，后者在磁盘中。当事务提交后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用

后台线程：

Master Thread：
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据一致性，还包括脏页的刷新、合并插入缓存、Undo页的回收。

IO Thread：
在lnnoDB存储引擎中大量使用AIO来处理IO请求，可以极大地提高数据库的性能，而IO Thread主要负责 lO请求的回调

线程类型	默认数量	职责
Read thread	4	负责读操作
Write thread	4	负责写操作
Log thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区内容刷新到磁盘

Purge Thread：
主要用于回收事务已经提交的 Undo Log，在事务提交后，Undo Log 可能不再使用，便回收

Page Cleaner Thread：
协助 Master Thread 刷新脏页到磁盘的线程，可减轻 Master Thread 的工作压力，减少阻塞

事务原理：

Redo Log（持久性）：

重做日志，用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（Redo Log Buffer）以及重做日志文件（Redo Log），前者是在内存中，后者在磁盘中。当事务提交后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用

Undo Log（原子性）：

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和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

MVCC（隔离性）：

基本概念：

当前读：
读取的是记录的最新版本，读取时要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：
SELECT ... LOCK IN SHARE MODE（共享锁）、INSERT、UPDATE、DELETE（排他锁）、SELECT ... FOR UPDATE均为当前读

快照读：
简单的SELECT（不加锁）即为快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

Read Committed：每次SELECT，均生成快照读
Repeatable Read：开启事务后首个 SELECT语句才为快照读
Serializable：快照读会退化为当前读

MVCC：
全称 Multi-Version Concurrency Control，多版本并发控制，指维护数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现
MVCC提供了非阻塞读功能，MVCC的具体实现，还需要依赖于数据库记录中的 隐式字段、Undo Log日志以及 ReadView

隐式字段：

隐式字段	含义
DB_TRX_ID	最近修改事务ID，记录插入该记录或最后修改该记录的事务ID
DB_ROLL_PTR	回滚指针，指向该记录的上版本，配合 Undo Log 使用
DB_ROW_ID	隐藏主键，若表结构没有指定主键，将会生成该隐藏字段

Undo Log日志：

简介：
回滚日志，在INSERT、UPDATE、DELETE 时候产生的，便于数据回滚的日志。当INSERT操作执行时，产生的Undo Log日志只在回滚时需要，在事务提交后，可被立即删除，而UPDATE、DELETE操作执行时，产生的Undo Log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

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

ReadView：

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）ID
ReadView包含四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，最大事务ID+1（由于事务ID自增)
creator_trx_id	ReadView 创建者的事务ID

版本链数据访问规则（顺序执行）：

规则	解释
①.trx_id == creator_trx_id ? 可以访问该版本	成立，说明数据为当前事务更改
②.trx_id < min_trx_id ？可以访问该版本	成立，说明数据已提交
③.trx_id > max_trx_id ？不可以访问该版本	成立，说明该事务在ReadView生成后才开启
④.min_trx_id <= trx_id <= max_trx_id ？若 trx_id 不在 m_ids 中可以访问该版本	成立，说明数据已提交

不同隔离级别，生成ReadView的时机不同：
READ COMMITTED：在事务中每次执行快照读时生成ReadView
REPEATABLE READ：仅在事务中首次执行快照读时生成ReadView，后续复用该ReadView

MySQL管理：

系统数据库：

数据库	描述
MySQL	存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
Information_Schema	提供访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
Performance_Schema	为MySQL服务器行时状态提供了底层监控功能，主要用于收集数据库服务器性能参数
SYS	包含系列方便 DBA 和开发人员利用 Performance_Schema性能数据库进行性能调优和诊断的视图

常用工具：

MySQL客户端：

# 语法
mysql [options] [database]
# 选项
-u    --user=name          #指定用户名
-p    --password[=name]    #指定密码
-h    --host=name          #指定服务器IP或域名
-P    --port=port          #指定连接端口
-e    --execute=name       #执行SQL语句并退出
# 补充
# -e选项可以在MySQL客户端执行SQL语句,而不连接到MySQL数据库,对于批处理脚本尤其方便
mysql -uroot -p666666 -h192.168.88.130 -P3306 tassel -e"select * from stu";

MySQLBinLog（二进制日志查看）：

服务器生成的二进制日志文件以二进制格式保存，若想要检查这些文本的文本格式，可以使用MySQLBinLog日志管理工具

# 语法
mysqlbinlog [options] log-files1 log-files2 ...
# 选项
-d    --database=name       #指定数据库名称
-o    --offset=#            #忽略掉日志中的前n行命令
-r    --result-file=name    #将输出的文本格式日志输出到指定文件
-s    --short-form          #显示简单格式
--start-datatime=date1 --stop-datetime=date2   #指定日期间隔内的所有日志
--start-position=pos1 --stop-position=pos2     #指定位置间隔内的所有日志

MySQLShow（统计信息查看）：

# 快速查找存在的数据库、数据库中的表以及表中的列或者索引
# 语法
mysqlshow [options] [database_name [table_name [column_name]]]
# 选项
--count   #显示数据库及表的统计信息（数据库、表均可以不指定）
-i        #显示指定数据库或者指定表的状态信息
# 示例
# 查询各数据库的表的数量及表中记录的数量
mysqlshow -uroot-p6666 --count
# 查询tassel库中各表中的字段数及行数
mysqlshow -uroot -p6666 tassel --count 
# 查询tassel库中book表的详细情况
mysqlshow-uroot -p6666 tassel book --count

MySQLDump（数据备份）：

# 备份数据库或在不同数据库之间进行数据迁移,备份内容包含创建表及插入表的SQL语句
# 语法
mysqldump [options] database_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...] 
mysqldump [options] --all-databases/-A
# 连接选项
-U   --user=name         #指定用户名
-p   --password[=name]   #指定密码
-h   --host=name         #指定服务器IP或域名
-P   --port=#            #指定连接端口
# 输出选项
--add-drop-database      #在各数据库创建语句前加上drop database语句
--add-drop-table         #在各表创建语句前加上drop table语句,默认开启;不开启(--skip-add-drop-table)
-n    --no-create-db     #不包含数据库的创建语句
-t    --no-create-info   #不包含数据表的创建语句
-d    --no-data          #不包含数据
-T    --tab=name         #自动生成两个文件: .sql文件(创建表结构的语句) + .txt文件(数据文件)

MySQLImport（数据导入）：

# 用于导入"MySQLDump -T"导出的文本文件
# 语法
mysqlimport [options] database_name textfile1 [textfile2...]
# 示例
mysqlimport -uroot -p6666 test /tmp/love.txt
# 若需要导入SQL文件,可以使用MySQL中的source指令
source /root/xxx.sql

[^1]：以下演示代码均忽略分号

[^2]：中括号括起代表该指令可以省略

[^3]：where为分组前过滤，不满足where条件的数据不参与分组；having对分组后结果进行过滤
where无法对聚合函数进行判断，having允许

[^4]：起始索引由0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
若查询为首页数据，起始索引可以省略，简写为 LIMIT 10

[^5]：嵌套查询外部语句可以为 INSERT/UPDATE/DELETE/SELECT 中的任意

[^6]：若索引仅关联单个字段，则称之为单列索引

[^7]：此命令需要在系统命令行下执行，而非MySQL客户端执行；若非MySQL客户端执行需要添加参数 -h 指定主机地址
mysqldump -h 192.168.88.130 -uroot -p密码 tassel > D:/tassel.sql

posted @ 2024-10-22 21:22 Code_Yi 阅读(90) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MatLab学习笔记

· Linux 教程

· MySQL笔记汇总(1)基础篇

· MySQL数据库精选（从入门使用到底层结构）

· MySQL

阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理了，记录一下

公告

昵称： Code_Yi
园龄： 7个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

code-yiyi