MySQL 数据库操作完整指南

MySQL 数据库操作完整指南

目录

创建数据库
连接数据库
创建表
约束详解
插入数据
查询数据
多表联合查询
连接查询
高级查询
更新数据
删除数据
视图详解
存储过程详解
函数详解
触发器
事务处理
索引优化
安全性管理
备份和恢复
性能优化
删除表和数据库

1. 创建数据库

基本创建数据库

-- 创建基本数据库
CREATE DATABASE CompanyDB;-- 创建带字符集的数据库
CREATE DATABASE CompanyDB_UTF8
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;-- 创建数据库如果不存在
CREATE DATABASE IF NOT EXISTS CompanyDB_Test
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;-- 创建带注释的数据库
CREATE DATABASE CompanyDB_Advanced
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
COMMENT '公司数据库系统';

查看数据库信息

-- 查看所有数据库
SHOW DATABASES;-- 查看数据库创建语句
SHOW CREATE DATABASE CompanyDB;-- 查看当前数据库
SELECT DATABASE();-- 查看数据库详细信息
SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'CompanyDB';-- 查看数据库大小
SELECT table_schema AS 'Database',ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'CompanyDB'
GROUP BY table_schema;-- 查看所有表的详细信息
SELECT TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH / 1024 / 1024 AS DATA_SIZE_MB,INDEX_LENGTH / 1024 / 1024 AS INDEX_SIZE_MB,CREATE_TIME,UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
ORDER BY DATA_LENGTH DESC;

---

2. 连接数据库

使用数据库

-- 切换到指定数据库
USE CompanyDB;-- 验证当前使用的数据库
SELECT DATABASE();-- 查看当前数据库的所有表
SHOW TABLES;-- 查看表的详细信息
SHOW TABLE STATUS;-- 查看表结构
DESCRIBE Employees;
-- 或
SHOW COLUMNS FROM Employees;-- 查看创建表的语句
SHOW CREATE TABLE Employees;-- 查看当前用户
SELECT USER(), CURRENT_USER();-- 查看连接信息
SELECT CONNECTION_ID();-- 查看数据库版本
SELECT VERSION();

---

3. 创建表

创建单个表

-- 创建员工表
CREATE TABLE Employees (EmployeeID INT AUTO_INCREMENT PRIMARY KEY,FirstName VARCHAR(50) NOT NULL,LastName VARCHAR(50) NOT NULL,Email VARCHAR(100) UNIQUE,Phone VARCHAR(20),HireDate DATE DEFAULT (CURRENT_DATE),Salary DECIMAL(10,2) CHECK (Salary > 0),DepartmentID INT,IsActive BOOLEAN DEFAULT TRUE,CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,ModifiedDate DATETIME ON UPDATE CURRENT_TIMESTAMP,CreatedBy VARCHAR(50) DEFAULT (CURRENT_USER()),ModifiedBy VARCHAR(50),INDEX idx_lastname (LastName),INDEX idx_department (DepartmentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='员工信息表';

创建多个相关表

-- 创建部门表
CREATE TABLE Departments (DepartmentID INT AUTO_INCREMENT PRIMARY KEY,DepartmentName VARCHAR(100) NOT NULL UNIQUE,Location VARCHAR(100),Budget DECIMAL(15,2),ManagerID INT,ParentDepartmentID INT,CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,ModifiedDate DATETIME ON UPDATE CURRENT_TIMESTAMP,KEY idx_manager (ManagerID),KEY idx_parent (ParentDepartmentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门信息表';-- 创建项目表
CREATE TABLE Projects (ProjectID INT AUTO_INCREMENT PRIMARY KEY,ProjectName VARCHAR(200) NOT NULL,Description TEXT,StartDate DATE,EndDate DATE,Budget DECIMAL(15,2),Status ENUM('Planning', 'Active', 'OnHold', 'Completed', 'Cancelled') DEFAULT 'Planning',DepartmentID INT,ProjectManagerID INT,CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,ModifiedDate DATETIME ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT chk_dates CHECK (EndDate >= StartDate),INDEX idx_status (Status),INDEX idx_dept_status (DepartmentID, Status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 创建员工项目关联表（多对多关系）
CREATE TABLE EmployeeProjects (EmployeeProjectID INT AUTO_INCREMENT PRIMARY KEY,EmployeeID INT NOT NULL,ProjectID INT NOT NULL,Role VARCHAR(50),AssignedDate DATE DEFAULT (CURRENT_DATE),UnassignedDate DATE,HoursWorked DECIMAL(5,2) DEFAULT 0,UNIQUE KEY uk_emp_proj_date (EmployeeID, ProjectID, AssignedDate),CONSTRAINT chk_hours CHECK (HoursWorked >= 0),CONSTRAINT chk_dates CHECK (UnassignedDate IS NULL OR UnassignedDate >= AssignedDate),INDEX idx_employee (EmployeeID),INDEX idx_project (ProjectID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 创建薪资历史表
CREATE TABLE SalaryHistory (SalaryHistoryID INT AUTO_INCREMENT PRIMARY KEY,EmployeeID INT NOT NULL,OldSalary DECIMAL(10,2),NewSalary DECIMAL(10,2),ChangeDate DATETIME DEFAULT CURRENT_TIMESTAMP,Reason VARCHAR(200),ApprovedBy VARCHAR(50),CONSTRAINT chk_new_salary CHECK (NewSalary > 0),INDEX idx_employee_date (EmployeeID, ChangeDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 创建技能表
CREATE TABLE Skills (SkillID INT AUTO_INCREMENT PRIMARY KEY,SkillName VARCHAR(100) NOT NULL UNIQUE,SkillCategory VARCHAR(50),Description VARCHAR(500),INDEX idx_category (SkillCategory)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 创建员工技能关联表
CREATE TABLE EmployeeSkills (EmployeeSkillID INT AUTO_INCREMENT PRIMARY KEY,EmployeeID INT NOT NULL,SkillID INT NOT NULL,ProficiencyLevel TINYINT CHECK (ProficiencyLevel BETWEEN 1 AND 5),CertificationDate DATE,ExpiryDate DATE,UNIQUE KEY uk_emp_skill (EmployeeID, SkillID),INDEX idx_skill (SkillID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 创建分区表示例（MySQL 5.1+）
CREATE TABLE SalesData (SaleID INT AUTO_INCREMENT,SaleDate DATE NOT NULL,Amount DECIMAL(10,2),CustomerID INT,PRIMARY KEY (SaleID, SaleDate)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(SaleDate)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025),PARTITION p_future VALUES LESS THAN MAXVALUE
);

---

4. 约束详解

主键约束（PRIMARY KEY）

-- 创建表时添加主键
CREATE TABLE Products (ProductID INT PRIMARY KEY,ProductName VARCHAR(100) NOT NULL
);-- 自增主键
CREATE TABLE Categories (CategoryID INT AUTO_INCREMENT PRIMARY KEY,CategoryName VARCHAR(50) NOT NULL
);-- 复合主键
CREATE TABLE OrderDetails (OrderID INT,ProductID INT,Quantity INT,PRIMARY KEY (OrderID, ProductID)
);-- 为已存在的表添加主键
ALTER TABLE TableName 
ADD PRIMARY KEY (ColumnName);-- 删除主键
ALTER TABLE TableName 
DROP PRIMARY KEY;-- 修改自增值
ALTER TABLE Categories AUTO_INCREMENT = 100;

外键约束（FOREIGN KEY）

-- 创建表时添加外键
CREATE TABLE Orders (OrderID INT PRIMARY KEY AUTO_INCREMENT,CustomerID INT,OrderDate DATE,CONSTRAINT fk_orders_customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);-- 添加级联操作的外键
ALTER TABLE Employees 
ADD CONSTRAINT fk_employees_departments 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
ON DELETE SET NULL
ON UPDATE CASCADE;-- 添加多列外键
ALTER TABLE OrderDetails
ADD CONSTRAINT fk_orderdetails_orders
FOREIGN KEY (OrderID, CustomerID) 
REFERENCES Orders(OrderID, CustomerID);-- 查看外键信息
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
AND TABLE_SCHEMA = 'CompanyDB';-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
-- 执行操作...
SET FOREIGN_KEY_CHECKS = 1;-- 删除外键
ALTER TABLE Employees 
DROP FOREIGN KEY fk_employees_departments;

唯一约束（UNIQUE）

-- 创建表时添加唯一约束
CREATE TABLE Users (UserID INT PRIMARY KEY AUTO_INCREMENT,Username VARCHAR(50) UNIQUE,Email VARCHAR(100),CONSTRAINT uk_users_email UNIQUE (Email)
);-- 为已存在的表添加唯一约束
ALTER TABLE Employees 
ADD UNIQUE INDEX uk_employees_email (Email);-- 添加多列唯一约束
ALTER TABLE Products 
ADD UNIQUE KEY uk_products_name_category (ProductName, CategoryID);-- 删除唯一约束
ALTER TABLE Users 
DROP INDEX uk_users_email;-- 查看唯一约束
SHOW INDEX FROM Users WHERE Non_unique = 0;

检查约束（CHECK）- MySQL 8.0.16+

-- 创建表时添加检查约束
CREATE TABLE Products (ProductID INT PRIMARY KEY AUTO_INCREMENT,ProductName VARCHAR(100) NOT NULL,Price DECIMAL(10,2) CHECK (Price > 0),Stock INT,CONSTRAINT chk_products_stock CHECK (Stock >= 0)
);-- 为已存在的表添加检查约束
ALTER TABLE Employees 
ADD CONSTRAINT chk_employees_age 
CHECK (TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) >= 18);-- 复杂的检查约束
ALTER TABLE Projects 
ADD CONSTRAINT chk_projects_budget_status 
CHECK ((Status = 'Planning' AND Budget IS NULL) OR(Status IN ('Active', 'Completed') AND Budget IS NOT NULL)
);-- 查看检查约束
SELECT CONSTRAINT_NAME,CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'CompanyDB';-- 删除检查约束
ALTER TABLE Employees 
DROP CONSTRAINT chk_employees_age;

默认约束（DEFAULT）

-- 创建表时添加默认约束
CREATE TABLE AuditLog (LogID INT PRIMARY KEY AUTO_INCREMENT,Action VARCHAR(50),LogDate DATETIME DEFAULT CURRENT_TIMESTAMP,UserName VARCHAR(50) DEFAULT (CURRENT_USER()),IPAddress VARCHAR(15) DEFAULT '0.0.0.0'
);-- 为已存在的列添加默认值
ALTER TABLE Employees 
ALTER COLUMN IsActive SET DEFAULT 1;-- 使用函数作为默认值
ALTER TABLE Orders 
ALTER COLUMN OrderNumber SET DEFAULT (CONCAT('ORD-', DATE_FORMAT(NOW(), '%Y%m%d-%H%i%s')));-- 删除默认约束
ALTER TABLE Employees 
ALTER COLUMN IsActive DROP DEFAULT;-- 查看列的默认值
SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'CompanyDB'
AND TABLE_NAME = 'Employees'
AND COLUMN_DEFAULT IS NOT NULL;

生成列（Generated Columns）- MySQL 5.7+

-- 创建包含生成列的表
CREATE TABLE OrderItems (OrderItemID INT PRIMARY KEY AUTO_INCREMENT,Quantity INT NOT NULL,UnitPrice DECIMAL(10,2) NOT NULL,Discount DECIMAL(3,2) DEFAULT 0,-- 虚拟生成列LineTotal DECIMAL(10,2) AS (Quantity * UnitPrice * (1 - Discount)) VIRTUAL,-- 存储生成列DiscountAmount DECIMAL(10,2) AS (Quantity * UnitPrice * Discount) STORED
);-- 为已存在的表添加生成列
ALTER TABLE Employees 
ADD COLUMN FullName VARCHAR(101) AS (CONCAT(FirstName, ' ', LastName)) VIRTUAL;-- 添加存储生成列并创建索引
ALTER TABLE Employees 
ADD COLUMN Age INT AS (TIMESTAMPDIFF(YEAR, BirthDate, CURDATE())) STORED,
ADD INDEX idx_age (Age);-- JSON生成列示例
CREATE TABLE ProductInfo (ProductID INT PRIMARY KEY,Details JSON,ProductName VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(Details, '$.name'))) STORED,Price DECIMAL(10,2) AS (JSON_EXTRACT(Details, '$.price')) STORED,INDEX idx_name (ProductName)
);

---

5. 插入数据

基本插入操作

-- 插入部门数据
INSERT INTO Departments (DepartmentName, Location, Budget) VALUES
('人力资源部', '北京', 500000.00),
('技术部', '上海', 2000000.00),
('销售部', '广州', 1500000.00),
('财务部', '北京', 800000.00),
('市场部', '深圳', 1200000.00);-- 插入员工数据
INSERT INTO Employees (FirstName, LastName, Email, Phone, HireDate, Salary, DepartmentID) VALUES
('张', '三', 'zhang.san@company.com', '13800138001', '2023-01-15', 8000.00, 2),
('李', '四', 'li.si@company.com', '13800138002', '2023-02-20', 12000.00, 2),
('王', '五', 'wang.wu@company.com', '13800138003', '2023-03-10', 7000.00, 3),
('赵', '六', 'zhao.liu@company.com', '13800138004', '2023-04-05', 9000.00, 1),
('陈', '七', 'chen.qi@company.com', '13800138005', '2023-05-12', 11000.00, 4),
('刘', '八', 'liu.ba@company.com', '13800138006', '2023-06-18', 6500.00, 3),
('杨', '九', 'yang.jiu@company.com', '13800138007', '2023-07-22', 10000.00, 5),
('黄', '十', 'huang.shi@company.com', '13800138008', '2023-08-15', 13000.00, 2);-- 获取最后插入的ID
SELECT LAST_INSERT_ID();-- 插入并返回影响的行数
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES ('新', '员工', 'new.employee@company.com', 8500.00, 1);
SELECT ROW_COUNT();

批量插入和高级插入

-- 使用INSERT INTO SELECT批量插入
INSERT INTO Projects (ProjectName, Description, StartDate, EndDate, Budget, Status, DepartmentID)
SELECT CONCAT('Project-', DepartmentID, '-', YEAR(NOW())),'Auto-generated project for department',DATE_ADD(CURDATE(), INTERVAL DepartmentID * 10 DAY),DATE_ADD(CURDATE(), INTERVAL 6 MONTH),Budget * 0.1,'Planning',DepartmentID
FROM Departments
WHERE Budget > 500000;-- 插入或更新（UPSERT）
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, Salary, DepartmentID)
VALUES (1, '张', '三丰', 'zhang.sanfeng@company.com', 9000.00, 2)
ON DUPLICATE KEY UPDATEFirstName = VALUES(FirstName),LastName = VALUES(LastName),Salary = VALUES(Salary),ModifiedDate = NOW();-- 插入忽略重复
INSERT IGNORE INTO Skills (SkillName, SkillCategory, Description)
VALUES ('MySQL', '数据库', '开源关系型数据库管理系统'),('Python', '编程语言', '通用高级编程语言'),('项目管理', '管理技能', 'PMP认证项目管理技能');-- 条件插入（仅插入不存在的记录）
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
SELECT '测试', '用户', 'test.user@company.com', 7500.00, 1
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM Employees WHERE Email = 'test.user@company.com'
);-- 批量插入优化
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;-- 批量插入数据
INSERT INTO LargeTable VALUES
(1, 'data1'),
(2, 'data2'),
-- ... 更多数据
(1000, 'data1000');COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;
SET autocommit = 1;-- 从CSV文件导入数据
LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE Employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(FirstName, LastName, Email, @salary, DepartmentID)
SET Salary = CAST(@salary AS DECIMAL(10,2));

---

6. 查询数据

基本查询

-- 查询所有员工
SELECT * FROM Employees;-- 查询特定字段
SELECT FirstName, LastName, Email, Salary FROM Employees;-- 使用别名
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS FullName,e.Email AS EmailAddress,e.Salary AS MonthlySalary,e.Salary * 12 AS AnnualSalary
FROM Employees e;-- 条件查询
SELECT * FROM Employees WHERE Salary > 10000;-- 多条件查询
SELECT * FROM Employees 
WHERE Salary BETWEEN 8000 AND 12000 
AND DepartmentID IN (2, 3)
AND IsActive = TRUE;-- 模糊查询
SELECT * FROM Employees WHERE FirstName LIKE '张%';
SELECT * FROM Employees WHERE Email LIKE '%@company.com';
SELECT * FROM Employees WHERE LastName LIKE '_四';  -- 第二个字是"四"-- 正则表达式查询
SELECT * FROM Employees WHERE Email REGEXP '^[a-z]+\\.[a-z]+@company\\.com$';-- 空值查询
SELECT * FROM Employees WHERE Phone IS NULL;
SELECT * FROM Employees WHERE Phone IS NOT NULL;-- 排序查询
SELECT * FROM Employees ORDER BY Salary DESC, HireDate ASC;-- 限制结果数量
SELECT * FROM Employees ORDER BY Salary DESC LIMIT 5;
SELECT * FROM Employees ORDER BY Salary DESC LIMIT 5 OFFSET 10;  -- 跳过前10条-- 使用DISTINCT
SELECT DISTINCT DepartmentID FROM Employees;
SELECT COUNT(DISTINCT DepartmentID) AS DeptCount FROM Employees;

聚合函数查询

-- 基本聚合函数
SELECT COUNT(*) AS TotalEmployees,COUNT(DISTINCT DepartmentID) AS DepartmentCount,AVG(Salary) AS AverageSalary,MIN(Salary) AS MinSalary,MAX(Salary) AS MaxSalary,SUM(Salary) AS TotalSalaryExpense,STD(Salary) AS SalaryStdDev,VARIANCE(Salary) AS SalaryVariance
FROM Employees;-- 按部门统计
SELECT DepartmentID,COUNT(*) AS EmployeeCount,AVG(Salary) AS AvgSalary,MIN(Salary) AS MinSalary,MAX(Salary) AS MaxSalary,GROUP_CONCAT(CONCAT(FirstName, ' ', LastName) ORDER BY LastName SEPARATOR ', ') AS EmployeeNames
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 1;-- 带ROLLUP的分组
SELECT DepartmentID,YEAR(HireDate) AS HireYear,COUNT(*) AS EmployeeCount,SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID, YEAR(HireDate) WITH ROLLUP;-- JSON聚合（MySQL 5.7+）
SELECT DepartmentID,JSON_ARRAYAGG(JSON_OBJECT('id', EmployeeID,'name', CONCAT(FirstName, ' ', LastName),'salary', Salary)) AS EmployeesJSON
FROM Employees
GROUP BY DepartmentID;

---

7. 多表联合查询

UNION查询

-- 联合查询员工和部门经理信息
SELECT CONCAT(FirstName, ' ', LastName) AS Name, 'Employee' AS Type, Salary AS Amount
FROM Employees
UNION
SELECT DepartmentName AS Name, 'Department' AS Type, Budget AS Amount
FROM Departments
ORDER BY Amount DESC;-- UNION ALL（包含重复项）
SELECT DepartmentID, 'Employee' AS Source FROM Employees
UNION ALL
SELECT DepartmentID, 'Project' AS Source FROM Projects
ORDER BY DepartmentID, Source;-- 使用UNION模拟EXCEPT（MySQL不支持EXCEPT）
SELECT EmployeeID FROM Employees
WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM EmployeeProjects
);-- 使用UNION模拟INTERSECT（MySQL不支持INTERSECT）
SELECT DepartmentID FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Projects
);

---

8. 连接查询

内连接（INNER JOIN）

-- 查询员工及其部门信息
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,e.Email,e.Salary,d.DepartmentName,d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;-- 使用USING简化连接条件
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,d.DepartmentName
FROM Employees e
INNER JOIN Departments d USING (DepartmentID);-- 三表内连接
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,d.DepartmentName,p.ProjectName,ep.Role,ep.HoursWorked
FROM Employees e
INNER JOIN EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
INNER JOIN Projects p ON ep.ProjectID = p.ProjectID
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE ep.HoursWorked > 100;

左连接（LEFT JOIN）

-- 查询所有员工及其部门信息（包括没有分配部门的员工）
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,e.Email,e.Salary,IFNULL(d.DepartmentName, '未分配部门') AS DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;-- 查询所有部门及其员工数量
SELECT d.DepartmentName,d.Location,d.Budget,COUNT(e.EmployeeID) AS EmployeeCount,IFNULL(AVG(e.Salary), 0) AS AvgSalary
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName, d.Location, d.Budget;-- 查找没有员工的部门
SELECT d.*
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
WHERE e.EmployeeID IS NULL;

右连接（RIGHT JOIN）

-- 查询所有部门及其员工信息
SELECT d.DepartmentName,d.Location,CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,e.Salary
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY d.DepartmentName, e.Salary DESC;

交叉连接（CROSS JOIN）

-- 生成员工和项目的所有可能组合
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,p.ProjectName,'Potential Assignment' AS Status
FROM Employees e
CROSS JOIN Projects p
WHERE e.DepartmentID = p.DepartmentID  -- 限制为同部门
AND NOT EXISTS (SELECT 1 FROM EmployeeProjects ep WHERE ep.EmployeeID = e.EmployeeID AND ep.ProjectID = p.ProjectID
);

自连接（Self JOIN）

-- 查找同一部门的员工配对
SELECT CONCAT(e1.FirstName, ' ', e1.LastName) AS Employee1,CONCAT(e2.FirstName, ' ', e2.LastName) AS Employee2,d.DepartmentName
FROM Employees e1
INNER JOIN Employees e2 ON e1.DepartmentID = e2.DepartmentID AND e1.EmployeeID < e2.EmployeeID
INNER JOIN Departments d ON e1.DepartmentID = d.DepartmentID;-- 查找员工的上级（使用部门经理）
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,CONCAT(m.FirstName, ' ', m.LastName) AS ManagerName,d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN Employees m ON d.ManagerID = m.EmployeeID
WHERE e.EmployeeID != d.ManagerID OR d.ManagerID IS NULL;

Natural JOIN（自然连接）

-- 自然连接（基于同名列）
SELECT *
FROM Employees
NATURAL JOIN Departments;-- 相当于
SELECT *
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

---

9. 高级查询

GROUP BY 和 HAVING

-- 按部门分组统计，只显示平均薪资大于8000的部门
SELECT d.DepartmentName,COUNT(e.EmployeeID) AS EmployeeCount,AVG(e.Salary) AS AverageSalary,MIN(e.Salary) AS MinSalary,MAX(e.Salary) AS MaxSalary,STD(e.Salary) AS SalaryStdDev
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName
HAVING AVG(e.Salary) > 8000 AND COUNT(e.EmployeeID) > 1
ORDER BY AverageSalary DESC;-- 使用GROUP_CONCAT
SELECT d.DepartmentName,GROUP_CONCAT(CONCAT(e.FirstName, ' ', e.LastName, '(', e.Salary, ')')ORDER BY e.Salary DESCSEPARATOR '; ') AS EmployeesList
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID;

子查询

-- 标量子查询
SELECT CONCAT(FirstName, ' ', LastName) AS EmployeeName,Salary,(SELECT AVG(Salary) FROM Employees) AS CompanyAvgSalary,Salary - (SELECT AVG(Salary) FROM Employees) AS SalaryDifference
FROM Employees;-- 相关子查询
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,e.Salary,d.DepartmentName,(SELECT COUNT(*) FROM Employees e2 WHERE e2.DepartmentID = e.DepartmentID AND e2.Salary > e.Salary) AS HigherSalaryCount
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;-- EXISTS子查询
SELECT d.DepartmentName,d.Budget
FROM Departments d
WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID AND e.Salary > 10000
);-- NOT EXISTS子查询
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName
FROM Employees e
WHERE NOT EXISTS (SELECT 1 FROM EmployeeProjects ep WHERE ep.EmployeeID = e.EmployeeID
);-- IN和NOT IN子查询
SELECT * FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = '上海'
);-- ANY/SOME和ALL子查询
SELECT * FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE DepartmentID = 2
);SELECT * FROM Employees
WHERE Salary > ALL (SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID
);

窗口函数（MySQL 8.0+）

-- 排名函数
SELECT CONCAT(FirstName, ' ', LastName) AS EmployeeName,Salary,DepartmentID,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryDenseRank,NTILE(4) OVER (ORDER BY Salary DESC) AS SalaryQuartile,PERCENT_RANK() OVER (ORDER BY Salary DESC) AS PercentRank
FROM Employees;-- 分区排名
SELECT CONCAT(FirstName, ' ', LastName) AS EmployeeName,Salary,DepartmentID,ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptSalaryRank
FROM Employees;-- 聚合窗口函数
SELECT CONCAT(FirstName, ' ', LastName) AS EmployeeName,Salary,DepartmentID,SUM(Salary) OVER () AS TotalSalary,SUM(Salary) OVER (PARTITION BY DepartmentID) AS DeptTotalSalary,AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary,COUNT(*) OVER (PARTITION BY DepartmentID) AS DeptEmployeeCount
FROM Employees;-- 累计和移动聚合
SELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS EmployeeName,HireDate,Salary,-- 累计求和SUM(Salary) OVER (ORDER BY HireDate) AS RunningTotal,-- 移动平均（前2行到当前行）AVG(Salary) OVER (ORDER BY HireDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3,-- 移动求和（前1行到后1行）SUM(Salary) OVER (ORDER BY HireDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSum3
FROM Employees;-- LEAD和LAG函数
SELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS EmployeeName,Salary,LAG(Salary, 1, 0) OVER (ORDER BY Salary) AS PreviousSalary,LEAD(Salary, 1, 0) OVER (ORDER BY Salary) AS NextSalary,Salary - LAG(Salary, 1, 0) OVER (ORDER BY Salary) AS SalaryGap
FROM Employees;-- FIRST_VALUE和LAST_VALUE
SELECT CONCAT(FirstName, ' ', LastName) AS EmployeeName,Salary,DepartmentID,FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptMaxSalary,LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DeptMinSalary
FROM Employees;

CTE（公共表表达式）- MySQL 8.0+

-- 基本CTE
WITH DepartmentStats AS (SELECT DepartmentID,COUNT(*) AS EmployeeCount,AVG(Salary) AS AvgSalary,SUM(Salary) AS TotalSalaryFROM EmployeesGROUP BY DepartmentID
)
SELECT d.DepartmentName,ds.EmployeeCount,ds.AvgSalary,ds.TotalSalary,d.Budget,d.Budget - ds.TotalSalary AS RemainingBudget
FROM DepartmentStats ds
INNER JOIN Departments d ON ds.DepartmentID = d.DepartmentID
WHERE ds.EmployeeCount > 1;-- 多个CTE
WITH 
DeptEmployees AS (SELECT DepartmentID, COUNT(*) AS EmpCountFROM EmployeesGROUP BY DepartmentID
),
DeptProjects AS (SELECT DepartmentID, COUNT(*) AS ProjCountFROM ProjectsGROUP BY DepartmentID
)
SELECT d.DepartmentName,IFNULL(de.EmpCount, 0) AS EmployeeCount,IFNULL(dp.ProjCount, 0) AS ProjectCount
FROM Departments d
LEFT JOIN DeptEmployees de ON d.DepartmentID = de.DepartmentID
LEFT JOIN DeptProjects dp ON d.DepartmentID = dp.DepartmentID;-- 递归CTE
WITH RECURSIVE EmployeeHierarchy AS (-- 锚点成员：顶级经理SELECT e.EmployeeID,CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,e.DepartmentID,d.DepartmentName,0 AS Level,CAST(CONCAT(e.FirstName, ' ', e.LastName) AS CHAR(1000)) AS HierarchyPathFROM Employees eINNER JOIN Departments d ON e.EmployeeID = d.ManagerIDUNION ALL-- 递归成员：下属员工SELECT e.EmployeeID,CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,e.DepartmentID,eh.DepartmentName,eh.Level + 1,CONCAT(eh.HierarchyPath, ' -> ', e.FirstName, ' ', e.LastName)FROM Employees eINNER JOIN EmployeeHierarchy eh ON e.DepartmentID = eh.DepartmentIDWHERE e.EmployeeID NOT IN (SELECT ManagerID FROM Departments WHERE ManagerID IS NOT NULL)AND eh.Level < 3
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

JSON查询（MySQL 5.7+）

-- 创建包含JSON数据的表
CREATE TABLE ProductCatalog (ProductID INT PRIMARY KEY AUTO_INCREMENT,ProductInfo JSON
);-- 插入JSON数据
INSERT INTO ProductCatalog (ProductInfo) VALUES
('{"name": "笔记本电脑", "price": 5999, "specs": {"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}}'),
('{"name": "智能手机", "price": 3999, "tags": ["5G", "快充", "高清屏"]}');-- 查询JSON数据
SELECT ProductID,JSON_EXTRACT(ProductInfo, '$.name') AS ProductName,JSON_EXTRACT(ProductInfo, '$.price') AS Price,JSON_EXTRACT(ProductInfo, '$.specs.cpu') AS CPU
FROM ProductCatalog;-- 使用JSON路径表达式
SELECT ProductID,ProductInfo->>'$.name' AS ProductName,ProductInfo->>'$.price' AS Price
FROM ProductCatalog
WHERE ProductInfo->>'$.price' > 4000;-- JSON数组查询
SELECT ProductID,ProductInfo->>'$.name' AS ProductName,JSON_EXTRACT(ProductInfo, '$.tags[0]') AS FirstTag
FROM ProductCatalog
WHERE JSON_CONTAINS(ProductInfo->'$.tags', '"5G"');-- JSON聚合
SELECT JSON_OBJECT('total_products', COUNT(*),'avg_price', AVG(ProductInfo->>'$.price'),'products', JSON_ARRAYAGG(ProductInfo->>'$.name')) AS Summary
FROM ProductCatalog;

---

10. 更新数据

基本更新操作

-- 更新单个员工的薪资
UPDATE Employees 
SET Salary = 9000.00 
WHERE EmployeeID = 1;-- 更新多个字段
UPDATE Employees 
SET Phone = '13900139001',Email = 'zhang.san.new@company.com',ModifiedDate = NOW(),ModifiedBy = CURRENT_USER()
WHERE EmployeeID = 1;-- 条件更新
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2 AND Salary < 10000;-- 使用CASE语句的条件更新
UPDATE Employees
SET Salary = CASE WHEN DepartmentID = 1 THEN Salary * 1.05WHEN DepartmentID = 2 THEN Salary * 1.10WHEN DepartmentID = 3 THEN Salary * 1.08ELSE Salary * 1.03END,ModifiedDate = NOW();-- 限制更新行数
UPDATE Employees 
SET Salary = Salary * 1.05 
WHERE DepartmentID = 2 
ORDER BY Salary ASC 
LIMIT 5;

高级更新操作

-- 使用JOIN进行更新
UPDATE Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
SET e.Salary = e.Salary * 1.05,e.ModifiedDate = NOW()
WHERE d.DepartmentName = '技术部';-- 多表JOIN更新
UPDATE Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN SalaryGrades sg ON e.Salary BETWEEN sg.MinSalary AND sg.MaxSalary
SET e.Salary = e.Salary * sg.IncreaseRate,e.Grade = sg.Grade
WHERE d.Location = '上海';-- 使用子查询更新
UPDATE Employees 
SET Salary = (SELECT AVG(Salary) * 1.1 FROM (SELECT Salary FROM Employees WHERE DepartmentID = Employees.DepartmentID) AS t
)
WHERE Salary < (SELECT AVG(Salary) FROM (SELECT Salary FROM Employees WHERE DepartmentID = Employees.DepartmentID) AS t
);-- 使用CTE更新（MySQL 8.0+）
WITH EmployeeRanking AS (SELECT EmployeeID,Salary,ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRankFROM Employees
)
UPDATE Employees e
INNER JOIN EmployeeRanking er ON e.EmployeeID = er.EmployeeID
SET e.Salary = e.Salary * 1.15
WHERE er.SalaryRank = 1;-- 更新JSON字段
UPDATE ProductCatalog
SET ProductInfo = JSON_SET(ProductInfo,'$.price', 5499,'$.discount', 10,'$.updated_at', NOW()
)
WHERE ProductID = 1;-- JSON数组更新
UPDATE ProductCatalog
SET ProductInfo = JSON_ARRAY_APPEND(ProductInfo, '$.tags', '新品')
WHERE ProductID = 2;

批量更新优化

-- 使用CASE进行批量更新
UPDATE Employees 
SET Salary = CASE EmployeeIDWHEN 1 THEN 8500WHEN 2 THEN 12500WHEN 3 THEN 9000ELSE Salary
END
WHERE EmployeeID IN (1, 2, 3);-- 使用临时表批量更新
CREATE TEMPORARY TABLE TempSalaryUpdates (EmployeeID INT,NewSalary DECIMAL(10,2)
);INSERT INTO TempSalaryUpdates VALUES
(1, 8500.00),
(2, 12500.00),
(3, 9000.00);UPDATE Employees e
INNER JOIN TempSalaryUpdates t ON e.EmployeeID = t.EmployeeID
SET e.Salary = t.NewSalary,e.ModifiedDate = NOW();DROP TEMPORARY TABLE TempSalaryUpdates;-- 分批更新大表
DELIMITER $$
CREATE PROCEDURE BatchUpdateSalaries()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE batch_size INT DEFAULT 1000;DECLARE offset_val INT DEFAULT 0;WHILE NOT done DOUPDATE Employees SET Salary = Salary * 1.03,ModifiedDate = NOW()WHERE DepartmentID = 2LIMIT offset_val, batch_size;IF ROW_COUNT() < batch_size THENSET done = TRUE;ELSESET offset_val = offset_val + batch_size;DO SLEEP(0.1);  -- 避免锁定过久END IF;END WHILE;
END$$
DELIMITER ;

---

11. 删除数据

基本删除操作

-- 删除单个记录
DELETE FROM Employees WHERE EmployeeID = 100;-- 条件删除
DELETE FROM Employees 
WHERE Salary < 5000 AND IsActive = FALSE;-- 使用LIMIT限制删除数量
DELETE FROM Employees 
WHERE IsActive = FALSE 
ORDER BY HireDate ASC 
LIMIT 10;-- 删除并返回删除的行数
DELETE FROM TempTable WHERE CreatedDate < DATE_SUB(NOW(), INTERVAL 1 YEAR);
SELECT ROW_COUNT() AS DeletedRows;

高级删除操作

-- 使用JOIN删除
DELETE ep
FROM EmployeeProjects ep
INNER JOIN Projects p ON ep.ProjectID = p.ProjectID
WHERE p.Status = 'Cancelled';-- 多表JOIN删除
DELETE e, ep, es
FROM Employees e
LEFT JOIN EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
LEFT JOIN EmployeeSkills es ON e.EmployeeID = es.EmployeeID
WHERE e.IsActive = FALSE AND e.TerminationDate < DATE_SUB(NOW(), INTERVAL 2 YEAR);-- 使用子查询删除
DELETE FROM SalaryHistory 
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE IsActive = FALSE
);-- 删除重复数据（保留最小ID）
DELETE e1 FROM Employees e1
INNER JOIN Employees e2 
WHERE e1.Email = e2.Email 
AND e1.EmployeeID > e2.EmployeeID;-- 使用临时表删除重复数据
CREATE TEMPORARY TABLE TempUniqueEmployees AS
SELECT MIN(EmployeeID) AS EmployeeID
FROM Employees
GROUP BY Email;DELETE FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM TempUniqueEmployees);DROP TEMPORARY TABLE TempUniqueEmployees;-- 分批删除大量数据
DELIMITER $$
CREATE PROCEDURE BatchDelete()
BEGINDECLARE rows_affected INT DEFAULT 1;WHILE rows_affected > 0 DODELETE FROM LargeLogTableWHERE LogDate < DATE_SUB(NOW(), INTERVAL 90 DAY)LIMIT 1000;SET rows_affected = ROW_COUNT();IF rows_affected > 0 THENDO SLEEP(0.5);  -- 暂停0.5秒，避免锁定END IF;END WHILE;
END$$
DELIMITER ;

TRUNCATE TABLE

-- 快速删除所有数据（比DELETE更快，但不能回滚）
TRUNCATE TABLE TempEmployees;-- 重置自增ID
TRUNCATE TABLE TestTable;
-- 或者
ALTER TABLE TestTable AUTO_INCREMENT = 1;-- 注意：TRUNCATE不能用于有外键引用的表
-- 需要先禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE Employees;
SET FOREIGN_KEY_CHECKS = 1;

---

12. 视图详解

创建基本视图

-- 创建简单视图
CREATE VIEW vw_EmployeeBasicInfo AS
SELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS FullName,Email,Phone,HireDate
FROM Employees
WHERE IsActive = TRUE;-- 创建带JOIN的视图
CREATE VIEW vw_EmployeeDepartmentInfo AS
SELECT e.EmployeeID,CONCAT(e.FirstName, ' ', e.LastName) AS FullName,e.Email,e.Salary,d.DepartmentName,d.Location,CONCAT(m.FirstName, ' ', m.LastName) AS ManagerName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN Employees m ON d.ManagerID = m.EmployeeID
WHERE e.IsActive = TRUE;-- 使用视图
SELECT * FROM vw_EmployeeDepartmentInfo WHERE Salary > 10000;

创建高级视图

-- 带聚合的视图
CREATE VIEW vw_DepartmentStatistics AS
SELECT d.DepartmentID,d.DepartmentName,d.Location,d.Budget,COUNT(e.EmployeeID) AS EmployeeCount,IFNULL(AVG(e.Salary), 0) AS AverageSalary,IFNULL(SUM(e.Salary), 0) AS TotalSalaryExpense,d.Budget - IFNULL(SUM(e.Salary), 0) AS RemainingBudget
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID AND e.IsActive = TRUE
GROUP BY d.DepartmentID, d.DepartmentName, d.Location, d.Budget;-- 带子查询的视图
CREATE VIEW vw_EmployeeRanking AS
SELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS FullName,Salary,DepartmentID,(SELECT COUNT(*) + 1 FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID AND e2.Salary > e1.Salary) AS DeptSalaryRank,(SELECT COUNT(*) + 1 FROM Employees e3 WHERE e3.Salary > e1.Salary) AS CompanySalaryRank
FROM Employees e1
WHERE IsActive = TRUE;-- 带UNION的视图
CREATE VIEW vw_AllContacts AS
SELECT 'Employee' AS ContactType,EmployeeID AS ContactID,CONCAT(FirstName, ' ', LastName) AS Name,Email,Phone
FROM Employees
WHERE IsActive = TRUE
UNION ALL
SELECT 'Manager' AS ContactType,m.EmployeeID AS ContactID,CONCAT(m.FirstName, ' ', m.LastName) AS Name,m.Email,m.Phone
FROM Departments d
INNER JOIN Employees m ON d.ManagerID = m.EmployeeID;-- 使用算法指定的视图
CREATE ALGORITHM = MERGE VIEW vw_ActiveEmployees AS
SELECT * FROM Employees WHERE IsActive = TRUE;CREATE ALGORITHM = TEMPTABLE VIEW vw_ComplexCalculations AS
SELECT DepartmentID,AVG(Salary) AS AvgSalary,COUNT(*) AS EmpCount
FROM Employees
GROUP BY DepartmentID;

可更新视图

-- 创建可更新的视图
CREATE VIEW vw_UpdateableEmployees AS
SELECT EmployeeID,FirstName,LastName,Email,Phone,Salary,DepartmentID,IsActive
FROM Employees
WHERE IsActive = TRUE
WITH CHECK OPTION;  -- 确保通过视图的更新满足WHERE条件-- 通过视图更新数据
UPDATE vw_UpdateableEmployees
SET Salary = Salary * 1.05
WHERE DepartmentID = 2;-- 通过视图插入数据
INSERT INTO vw_UpdateableEmployees (FirstName, LastName, Email, Salary, DepartmentID, IsActive)
VALUES ('测试', '员工', 'test.view@company.com', 8000, 1, TRUE);-- 检查视图是否可更新
SELECT TABLE_NAME,IS_UPDATABLE,CHECK_OPTION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'CompanyDB';

视图管理

-- 修改视图
CREATE OR REPLACE VIEW vw_EmployeeBasicInfo AS
SELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS FullName,Email,Phone,HireDate,DepartmentID  -- 新增字段
FROM Employees
WHERE IsActive = TRUE;-- 或使用ALTER
ALTER VIEW vw_EmployeeBasicInfo AS
SELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS FullName,Email,Phone,HireDate,DepartmentID,Salary  -- 再次新增字段
FROM Employees
WHERE IsActive = TRUE;-- 查看视图定义
SHOW CREATE VIEW vw_EmployeeBasicInfo;-- 查看所有视图
SELECT TABLE_NAME AS ViewName,VIEW_DEFINITION,CHECK_OPTION,IS_UPDATABLE,DEFINER,SECURITY_TYPE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'CompanyDB';-- 查看视图依赖的表
SELECT VIEW_NAME,TABLE_NAME,COLUMN_NAME
FROM information_schema.VIEW_COLUMN_USAGE
WHERE VIEW_SCHEMA = 'CompanyDB';-- 删除视图
DROP VIEW IF EXISTS vw_EmployeeBasicInfo;-- 批量删除视图
SELECT CONCAT('DROP VIEW IF EXISTS ', TABLE_NAME, ';') AS DropStatement
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'CompanyDB'
AND TABLE_NAME LIKE 'vw_temp%';

---

13. 存储过程详解

创建基本存储过程

-- 修改分隔符
DELIMITER $$-- 创建简单存储过程
CREATE PROCEDURE sp_GetAllEmployees()
BEGINSELECT EmployeeID,CONCAT(FirstName, ' ', LastName) AS FullName,Email,Salary,DepartmentIDFROM EmployeesWHERE IsActive = TRUEORDER BY LastName, FirstName;
END$$-- 恢复分隔符
DELIMITER ;-- 执行存储过程
CALL sp_GetAllEmployees();-- 带参数的存储过程
DELIMITER $$
CREATE PROCEDURE sp_GetEmployeesByDepartment(IN p_DepartmentID INT
)
BEGINSELECT e.EmployeeID,CONCAT(e.FirstName, ' ', e.LastName) AS FullName,e.Email,e.Salary,d.DepartmentNameFROM Employees eINNER JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE e.DepartmentID = p_DepartmentIDAND e.IsActive = TRUEORDER BY e.Salary DESC;
END$$
DELIMITER ;-- 执行带参数的存储过程
CALL sp_GetEmployeesByDepartment(2);

带多个参数和默认值的存储过程

DELIMITER $$
CREATE PROCEDURE sp_SearchEmployees(IN p_FirstName VARCHAR(50),IN p_LastName VARCHAR(50),IN p_DepartmentID INT,IN p_MinSalary DECIMAL(10,2),IN p_MaxSalary DECIMAL(10,2),IN p_IsActive BOOLEAN
)
BEGIN-- 设置默认值IF p_IsActive IS NULL THENSET p_IsActive = TRUE;END IF;SELECT e.EmployeeID,e.FirstName,e.LastName,e.Email,e.Salary,d.DepartmentNameFROM Employees eLEFT JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE (p_FirstName IS NULL OR e.FirstName LIKE CONCAT('%', p_FirstName, '%'))AND (p_LastName IS NULL OR e.LastName LIKE CONCAT('%', p_LastName, '%'))AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)AND (p_MinSalary IS NULL OR e.Salary >= p_MinSalary)AND (p_MaxSalary IS NULL OR e.Salary <= p_MaxSalary)AND e.IsActive = p_IsActiveORDER BY e.LastName, e.FirstName;
END$$
DELIMITER ;-- 多种调用方式
CALL sp_SearchEmployees(NULL, NULL, NULL, NULL, NULL, TRUE);
CALL sp_SearchEmployees('张', NULL, NULL, NULL, NULL, TRUE);
CALL sp_SearchEmployees(NULL, NULL, 2, 8000, NULL, TRUE);

带输出参数的存储过程

DELIMITER $$
CREATE PROCEDURE sp_GetDepartmentStatistics(IN p_DepartmentID INT,OUT p_EmployeeCount INT,OUT p_AverageSalary DECIMAL(10,2),OUT p_TotalSalary DECIMAL(15,2),OUT p_DepartmentName VARCHAR(100)
)
BEGIN-- 获取统计信息SELECT COUNT(*),AVG(Salary),SUM(Salary)INTO p_EmployeeCount,p_AverageSalary,p_TotalSalaryFROM EmployeesWHERE DepartmentID = p_DepartmentIDAND IsActive = TRUE;-- 获取部门名称SELECT DepartmentName INTO p_DepartmentNameFROM Departments WHERE DepartmentID = p_DepartmentID;-- 返回详细信息SELECT d.DepartmentName,d.Location,d.Budget,p_EmployeeCount AS EmployeeCount,p_AverageSalary AS AverageSalary,p_TotalSalary AS TotalSalary,d.Budget - p_TotalSalary AS RemainingBudgetFROM Departments dWHERE d.DepartmentID = p_DepartmentID;
END$$
DELIMITER ;-- 调用带输出参数的存储过程
SET @emp_count = 0;
SET @avg_salary = 0;
SET @total_sal = 0;
SET @dept_name = '';CALL sp_GetDepartmentStatistics(2, @emp_count, @avg_salary, @total_sal, @dept_name);SELECT @emp_count AS EmployeeCount, @avg_salary AS AverageSalary, @total_sal AS TotalSalary,@dept_name AS DepartmentName;

INOUT参数示例

DELIMITER $$
CREATE PROCEDURE sp_SwapValues(INOUT p_Value1 INT,INOUT p_Value2 INT
)
BEGINDECLARE temp INT;SET temp = p_Value1;SET p_Value1 = p_Value2;SET p_Value2 = temp;
END$$
DELIMITER ;-- 使用INOUT参数
SET @a = 10;
SET @b = 20;
CALL sp_SwapValues(@a, @b);
SELECT @a, @b;  -- 结果：20, 10

带事务处理的存储过程

DELIMITER $$
CREATE PROCEDURE sp_TransferEmployee(IN p_EmployeeID INT,IN p_NewDepartmentID INT,IN p_Reason VARCHAR(200),OUT p_Success BOOLEAN,OUT p_Message VARCHAR(200)
)
BEGINDECLARE v_OldDepartmentID INT;DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN-- 错误处理ROLLBACK;SET p_Success = FALSE;SET p_Message = CONCAT('错误: ', @error_message);END;-- 开始事务START TRANSACTION;-- 设置错误消息变量SET @error_message = '未知错误';-- 检查员工是否存在SELECT DepartmentID INTO v_OldDepartmentIDFROM EmployeesWHERE EmployeeID = p_EmployeeID;IF v_OldDepartmentID IS NULL THENSET @error_message = '员工不存在';SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '员工不存在';END IF;-- 检查部门是否存在IF NOT EXISTS (SELECT 1 FROM Departments WHERE DepartmentID = p_NewDepartmentID) THENSET @error_message = '部门不存在';SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '部门不存在';END IF;-- 更新员工部门UPDATE EmployeesSET DepartmentID = p_NewDepartmentID,ModifiedDate = NOW()WHERE EmployeeID = p_EmployeeID;-- 记录转部门历史INSERT INTO TransferHistory (EmployeeID, OldDepartmentID, NewDepartmentID, TransferDate, Reason)VALUES (p_EmployeeID, v_OldDepartmentID, p_NewDepartmentID, NOW(), p_Reason);-- 更新相关项目UPDATE EmployeeProjectsSET UnassignedDate = CURDATE()WHERE EmployeeID = p_EmployeeIDAND ProjectID IN (SELECT ProjectID FROM Projects WHERE DepartmentID = v_OldDepartmentID)AND UnassignedDate IS NULL;-- 提交事务COMMIT;SET p_Success = TRUE;SET p_Message = '员工转部门成功';END$$
DELIMITER ;-- 调用存储过程
SET @success = FALSE;
SET @message = '';
CALL sp_TransferEmployee(1, 3, '部门调整', @success, @message);
SELECT @success, @message;

动态SQL存储过程

DELIMITER $$
CREATE PROCEDURE sp_DynamicEmployeeReport(IN p_SelectColumns VARCHAR(1000),IN p_WhereClause VARCHAR(1000),IN p_OrderBy VARCHAR(200),IN p_Limit INT
)
BEGINDECLARE v_SQL TEXT;-- 构建基本查询SET v_SQL = 'SELECT ';-- 添加列（默认为所有列）IF p_SelectColumns IS NULL OR p_SelectColumns = '' THENSET v_SQL = CONCAT(v_SQL, '*');ELSESET v_SQL = CONCAT(v_SQL, p_SelectColumns);END IF;SET v_SQL = CONCAT(v_SQL, ' FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID ');-- 添加WHERE子句IF p_WhereClause IS NOT NULL AND p_WhereClause != '' THENSET v_SQL = CONCAT(v_SQL, 'WHERE ', p_WhereClause, ' ');END IF;-- 添加ORDER BY子句IF p_OrderBy IS NOT NULL AND p_OrderBy != '' THENSET v_SQL = CONCAT(v_SQL, 'ORDER BY ', p_OrderBy, ' ');END IF;-- 添加LIMIT子句IF p_Limit IS NOT NULL AND p_Limit > 0 THENSET v_SQL = CONCAT(v_SQL, 'LIMIT ', p_Limit);END IF;-- 执行动态SQLSET @dynamic_sql = v_SQL;PREPARE stmt FROM @dynamic_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;-- 调用示例
CALL sp_DynamicEmployeeReport('e.FirstName, e.LastName, e.Salary, d.DepartmentName','e.Salary > 8000 AND e.IsActive = TRUE','e.Salary DESC',10
);

游标使用示例

DELIMITER $$
CREATE PROCEDURE sp_UpdateSalaryByGrade()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE v_EmployeeID INT;DECLARE v_Salary DECIMAL(10,2);DECLARE v_IncreaseRate DECIMAL(3,2);-- 声明游标DECLARE emp_cursor CURSOR FORSELECT EmployeeID, SalaryFROM EmployeesWHERE IsActive = TRUE;-- 声明继续处理器DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 开启游标OPEN emp_cursor;-- 循环处理read_loop: LOOPFETCH emp_cursor INTO v_EmployeeID, v_Salary;IF done THENLEAVE read_loop;END IF;-- 根据薪资级别确定涨幅SET v_IncreaseRate = CASEWHEN v_Salary < 6000 THEN 0.10WHEN v_Salary < 10000 THEN 0.08WHEN v_Salary < 15000 THEN 0.05ELSE 0.03END;-- 更新薪资UPDATE EmployeesSET Salary = Salary * (1 + v_IncreaseRate),ModifiedDate = NOW()WHERE EmployeeID = v_EmployeeID;END LOOP;-- 关闭游标CLOSE emp_cursor;SELECT '薪资更新完成' AS Result;
END$$
DELIMITER ;

存储过程管理

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'CompanyDB';-- 查看存储过程定义
SHOW CREATE PROCEDURE sp_GetAllEmployees;-- 查看存储过程参数
SELECT SPECIFIC_NAME,PARAMETER_MODE,PARAMETER_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'CompanyDB'
AND SPECIFIC_NAME = 'sp_GetDepartmentStatistics'
ORDER BY ORDINAL_POSITION;-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_GetAllEmployees;-- 修改存储过程（需要先删除再创建）
DROP PROCEDURE IF EXISTS sp_GetAllEmployees;
-- 然后重新创建...

---

14. 函数详解

标量函数（Scalar Functions）

-- 创建基本标量函数
DELIMITER $$
CREATE FUNCTION fn_GetFullName(p_FirstName VARCHAR(50),p_LastName VARCHAR(50)
) 
RETURNS VARCHAR(101)
DETERMINISTIC
BEGINRETURN CONCAT(p_FirstName, ' ', p_LastName);
END$$
DELIMITER ;-- 使用标量函数
SELECT EmployeeID,fn_GetFullName(FirstName, LastName) AS FullName,Email
FROM Employees;-- 计算年龄的函数
DELIMITER $$
CREATE FUNCTION fn_CalculateAge(p_BirthDate DATE
) 
RETURNS INT
DETERMINISTIC
BEGINRETURN TIMESTAMPDIFF(YEAR, p_BirthDate, CURDATE());
END$$
DELIMITER ;-- 计算工作年限
DELIMITER $$
CREATE FUNCTION fn_GetWorkYears(p_HireDate DATE
) 
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGINRETURN ROUND(DATEDIFF(CURDATE(), p_HireDate) / 365.25, 2);
END$$
DELIMITER ;-- 格式化货币
DELIMITER $$
CREATE FUNCTION fn_FormatCurrency(p_Amount DECIMAL(15,2),p_CurrencySymbol VARCHAR(5)
) 
RETURNS VARCHAR(25)
DETERMINISTIC
BEGINIF p_CurrencySymbol IS NULL THENSET p_CurrencySymbol = '¥';END IF;RETURN CONCAT(p_CurrencySymbol, FORMAT(p_Amount, 2));
END$$
DELIMITER ;-- 使用多个函数
SELECT fn_GetFullName(FirstName, LastName) AS FullName,fn_GetWorkYears(HireDate) AS WorkYears,fn_FormatCurrency(Salary, '$') AS FormattedSalary
FROM Employees;

复杂业务逻辑函数

-- 计算员工等级
DELIMITER $$
CREATE FUNCTION fn_GetEmployeeGrade(p_Salary DECIMAL(10,2),p_WorkYears DECIMAL(5,2)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGINDECLARE v_Grade VARCHAR(20);IF p_Salary >= 15000 AND p_WorkYears >= 5 THENSET v_Grade = '高级专家';ELSEIF p_Salary >= 12000 AND p_WorkYears >= 3 THENSET v_Grade = '资深员工';ELSEIF p_Salary >= 8000 AND p_WorkYears >= 1 THENSET v_Grade = '中级员工';ELSEIF p_WorkYears < 1 THENSET v_Grade = '新员工';ELSESET v_Grade = '初级员工';END IF;RETURN v_Grade;
END$$
DELIMITER ;-- 计算部门预算使用率
DELIMITER $$
CREATE FUNCTION fn_GetBudgetUsageRate(p_DepartmentID INT
) 
RETURNS DECIMAL(5,2)
READS SQL DATA
BEGINDECLARE v_Budget DECIMAL(15,2);DECLARE v_TotalSalary DECIMAL(15,2);DECLARE v_UsageRate DECIMAL(5,2);-- 获取部门预算SELECT Budget INTO v_BudgetFROM DepartmentsWHERE DepartmentID = p_DepartmentID;-- 获取部门总薪资SELECT IFNULL(SUM(Salary), 0) INTO v_TotalSalaryFROM EmployeesWHERE DepartmentID = p_DepartmentIDAND IsActive = TRUE;-- 计算使用率IF v_Budget > 0 THENSET v_UsageRate = ROUND((v_TotalSalary / v_Budget) * 100, 2);ELSESET v_UsageRate = 0;END IF;RETURN v_UsageRate;
END$$
DELIMITER ;

字符串处理函数

-- 提取邮箱域名
DELIMITER $$
CREATE FUNCTION fn_GetEmailDomain(p_Email VARCHAR(100)
) 
RETURNS VARCHAR(50)
DETERMINISTIC
BEGINDECLARE v_AtPosition INT;SET v_AtPosition = LOCATE('@', p_Email);IF v_AtPosition > 0 THENRETURN SUBSTRING(p_Email, v_AtPosition + 1);ELSERETURN NULL;END IF;
END$$
DELIMITER ;-- 手机号脱敏
DELIMITER $$
CREATE FUNCTION fn_MaskPhoneNumber(p_Phone VARCHAR(20)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGINIF LENGTH(p_Phone) >= 11 THENRETURN CONCAT(LEFT(p_Phone, 3),'****',RIGHT(p_Phone, 4));ELSERETURN p_Phone;END IF;
END$$
DELIMITER ;-- 生成随机密码
DELIMITER $$
CREATE FUNCTION fn_GeneratePassword(p_Length INT
) 
RETURNS VARCHAR(100)
NO SQL
BEGINDECLARE v_Chars VARCHAR(100) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*';DECLARE v_Password VARCHAR(100) DEFAULT '';DECLARE v_Index INT DEFAULT 1;WHILE v_Index <= p_Length DOSET v_Password = CONCAT(v_Password, SUBSTRING(v_Chars, FLOOR(1 + RAND() * LENGTH(v_Chars)), 1));SET v_Index = v_Index + 1;END WHILE;RETURN v_Password;
END$$
DELIMITER ;

日期处理函数

-- 获取工作日数量
DELIMITER $$
CREATE FUNCTION fn_GetWorkingDays(p_StartDate DATE,p_EndDate DATE
) 
RETURNS INT
DETERMINISTIC
BEGINDECLARE v_Days INT DEFAULT 0;DECLARE v_CurrentDate DATE;SET v_CurrentDate = p_StartDate;WHILE v_CurrentDate <= p_EndDate DO-- 排除周末（周六=6，周日=0）IF DAYOFWEEK(v_CurrentDate) NOT IN (1, 7) THENSET v_Days = v_Days + 1;END IF;SET v_CurrentDate = DATE_ADD(v_CurrentDate, INTERVAL 1 DAY);END WHILE;RETURN v_Days;
END$$
DELIMITER ;-- 获取季度
DELIMITER $$
CREATE FUNCTION fn_GetQuarterName(p_Date DATE
) 
RETURNS VARCHAR(10)
DETERMINISTIC
BEGINDECLARE v_Quarter INT;DECLARE v_Year INT;SET v_Quarter = QUARTER(p_Date);SET v_Year = YEAR(p_Date);RETURN CONCAT(v_Year, 'Q', v_Quarter);
END$$
DELIMITER ;

JSON处理函数

-- 安全提取JSON值
DELIMITER $$
CREATE FUNCTION fn_SafeJSONExtract(p_JSON JSON,p_Path VARCHAR(100)
) 
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGINDECLARE v_Result VARCHAR(1000);SET v_Result = JSON_UNQUOTE(JSON_EXTRACT(p_JSON, p_Path));IF v_Result IS NULL OR v_Result = 'null' THENRETURN NULL;ELSERETURN v_Result;END IF;
END$$
DELIMITER ;-- 构建员工JSON对象
DELIMITER $$
CREATE FUNCTION fn_BuildEmployeeJSON(p_EmployeeID INT
) 
RETURNS JSON
READS SQL DATA
BEGINDECLARE v_JSON JSON;SELECT JSON_OBJECT('id', e.EmployeeID,'name', CONCAT(e.FirstName, ' ', e.LastName),'email', e.Email,'phone', IFNULL(e.Phone, ''),'department', d.DepartmentName,'salary', e.Salary,'hireDate', DATE_FORMAT(e.HireDate, '%Y-%m-%d'),'isActive', e.IsActive) INTO v_JSONFROM Employees eLEFT JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE e.EmployeeID = p_EmployeeID;RETURN v_JSON;
END$$
DELIMITER ;

递归函数示例

-- 计算阶乘
DELIMITER $$
CREATE FUNCTION fn_Factorial(p_Number INT
) 
RETURNS BIGINT
DETERMINISTIC
BEGINIF p_Number <= 1 THENRETURN 1;ELSERETURN p_Number * fn_Factorial(p_Number - 1);END IF;
END$$
DELIMITER ;-- 斐波那契数列
DELIMITER $$
CREATE FUNCTION fn_Fibonacci(p_N INT
) 
RETURNS INT
DETERMINISTIC
BEGINDECLARE v_Prev INT DEFAULT 0;DECLARE v_Current INT DEFAULT 1;DECLARE v_Next INT;DECLARE v_Count INT DEFAULT 2;IF p_N <= 0 THENRETURN 0;ELSEIF p_N = 1 THENRETURN 1;END IF;WHILE v_Count <= p_N DOSET v_Next = v_Prev + v_Current;SET v_Prev = v_Current;SET v_Current = v_Next;SET v_Count = v_Count + 1;END WHILE;RETURN v_Current;
END$$
DELIMITER ;

函数管理

-- 查看所有函数
SHOW FUNCTION STATUS WHERE Db = 'CompanyDB';-- 查看函数定义
SHOW CREATE FUNCTION fn_GetFullName;-- 查看函数参数和返回值
SELECT SPECIFIC_NAME,ROUTINE_TYPE,DATA_TYPE AS RETURN_TYPE,ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'CompanyDB'
AND ROUTINE_TYPE = 'FUNCTION';-- 删除函数
DROP FUNCTION IF EXISTS fn_GetFullName;-- 查看函数权限
SELECT User,Host,Routine_name,Routine_type,Proc_priv,Grantor
FROM mysql.procs_priv
WHERE Db = 'CompanyDB';-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION CompanyDB.fn_GetFullName TO 'user'@'localhost';

---

15. 触发器

BEFORE触发器

-- 插入前触发器
DELIMITER $$
CREATE TRIGGER trg_Employees_BeforeInsert
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN-- 验证邮箱格式IF NEW.Email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '邮箱格式不正确';END IF;-- 自动设置创建信息SET NEW.CreatedDate = NOW();SET NEW.CreatedBy = CURRENT_USER();-- 确保薪资在合理范围内IF NEW.Salary < 3000 THENSET NEW.Salary = 3000;ELSEIF NEW.Salary > 100000 THENSET NEW.Salary = 100000;END IF;
END$$
DELIMITER ;-- 更新前触发器
DELIMITER $$
CREATE TRIGGER trg_Employees_BeforeUpdate
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN-- 记录修改信息SET NEW.ModifiedDate = NOW();SET NEW.ModifiedBy = CURRENT_USER();-- 防止降薪超过20%IF NEW.Salary < OLD.Salary * 0.8 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '降薪幅度不能超过20%';END IF;-- 记录重要字段变更IF OLD.Salary != NEW.Salary THENINSERT INTO AuditLog (TableName, RecordID, Action, FieldName, OldValue, NewValue, ChangedBy, ChangedAt)VALUES ('Employees', NEW.EmployeeID, 'UPDATE', 'Salary', OLD.Salary, NEW.Salary, CURRENT_USER(), NOW());END IF;
END$$
DELIMITER ;-- 删除前触发器
DELIMITER $$
CREATE TRIGGER trg_Employees_BeforeDelete
BEFORE DELETE ON Employees
FOR EACH ROW
BEGIN-- 检查是否有关联数据IF EXISTS (SELECT 1 FROM EmployeeProjects WHERE EmployeeID = OLD.EmployeeID AND UnassignedDate IS NULL) THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该员工还有未完成的项目，不能删除';END IF;-- 归档数据到历史表INSERT INTO EmployeesHistory SELECT *, 'DELETE', CURRENT_USER(), NOW() FROM Employees WHERE EmployeeID = OLD.EmployeeID;
END$$
DELIMITER ;

AFTER触发器

-- 插入后触发器
DELIMITER $$
CREATE TRIGGER trg_Employees_AfterInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN-- 更新部门员工计数UPDATE Departments SET EmployeeCount = (SELECT COUNT(*) FROM Employees WHERE DepartmentID = NEW.DepartmentID AND IsActive = TRUE)WHERE DepartmentID = NEW.DepartmentID;-- 发送欢迎邮件（插入到邮件队列表）INSERT INTO EmailQueue (RecipientEmail, Subject, Body, Status, CreatedAt)VALUES (NEW.Email,'欢迎加入公司',CONCAT('亲爱的 ', NEW.FirstName, ' ', NEW.LastName, '，欢迎加入我们的团队！'),'Pending',NOW());-- 记录操作日志INSERT INTO AuditLog (TableName, RecordID, Action, Details, ChangedBy, ChangedAt)VALUES ('Employees', NEW.EmployeeID, 'INSERT', CONCAT('新员工: ', NEW.FirstName, ' ', NEW.LastName),CURRENT_USER(), NOW());
END$$
DELIMITER ;-- 更新后触发器
DELIMITER $$
CREATE TRIGGER trg_Employees_AfterUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN-- 如果薪资变化，记录到薪资历史IF OLD.Salary != NEW.Salary THENINSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate, Reason, ApprovedBy)VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary, NOW(), '系统调整', CURRENT_USER());END IF;-- 如果部门变化，更新相关计数IF OLD.DepartmentID != NEW.DepartmentID THEN-- 更新原部门计数UPDATE Departments SET EmployeeCount = EmployeeCount - 1WHERE DepartmentID = OLD.DepartmentID;-- 更新新部门计数UPDATE Departments SET EmployeeCount = EmployeeCount + 1WHERE DepartmentID = NEW.DepartmentID;-- 记录转部门历史INSERT INTO TransferHistory (EmployeeID, OldDepartmentID, NewDepartmentID, TransferDate)VALUES (NEW.EmployeeID, OLD.DepartmentID, NEW.DepartmentID, NOW());END IF;
END$$
DELIMITER ;-- 删除后触发器
DELIMITER $$
CREATE TRIGGER trg_Employees_AfterDelete
AFTER DELETE ON Employees
FOR EACH ROW
BEGIN-- 更新部门员工计数UPDATE Departments SET EmployeeCount = EmployeeCount - 1WHERE DepartmentID = OLD.DepartmentID;-- 清理相关数据DELETE FROM EmployeeProjects WHERE EmployeeID = OLD.EmployeeID;DELETE FROM EmployeeSkills WHERE EmployeeID = OLD.EmployeeID;-- 记录删除日志INSERT INTO AuditLog (TableName, RecordID, Action, Details, ChangedBy, ChangedAt)VALUES ('Employees', OLD.EmployeeID, 'DELETE', CONCAT('删除员工: ', OLD.FirstName, ' ', OLD.LastName),CURRENT_USER(), NOW());
END$$
DELIMITER ;

复杂业务逻辑触发器

-- 项目状态变更触发器
DELIMITER $$
CREATE TRIGGER trg_Projects_StatusChange
AFTER UPDATE ON Projects
FOR EACH ROW
BEGIN-- 项目完成时的处理IF OLD.Status != 'Completed' AND NEW.Status = 'Completed' THEN-- 计算项目总工时UPDATE Projects pSET p.TotalHours = (SELECT SUM(HoursWorked) FROM EmployeeProjects WHERE ProjectID = NEW.ProjectID)WHERE p.ProjectID = NEW.ProjectID;-- 给项目成员发放奖金（插入待处理记录）INSERT INTO BonusQueue (EmployeeID, ProjectID, BonusType, Amount, Status)SELECT ep.EmployeeID,NEW.ProjectID,'ProjectCompletion',CASE WHEN ep.Role = 'Manager' THEN 5000WHEN ep.Role = 'Lead' THEN 3000ELSE 1000END,'Pending'FROM EmployeeProjects epWHERE ep.ProjectID = NEW.ProjectIDAND ep.UnassignedDate IS NULL;END IF;-- 项目取消时的处理IF OLD.Status != 'Cancelled' AND NEW.Status = 'Cancelled' THEN-- 释放所有项目成员UPDATE EmployeeProjectsSET UnassignedDate = CURDATE()WHERE ProjectID = NEW.ProjectIDAND UnassignedDate IS NULL;END IF;
END$$
DELIMITER ;-- 库存管理触发器
DELIMITER $
CREATE TRIGGER trg_OrderItems_AfterInsert
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGINDECLARE v_CurrentStock INT;-- 获取当前库存SELECT Stock INTO v_CurrentStockFROM ProductsWHERE ProductID = NEW.ProductIDFOR UPDATE;  -- 锁定行-- 检查库存是否充足IF v_CurrentStock < NEW.Quantity THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';END IF;-- 更新库存UPDATE ProductsSET Stock = Stock - NEW.Quantity,LastSoldDate = NOW()WHERE ProductID = NEW.ProductID;-- 如果库存低于阈值，创建采购提醒IF v_CurrentStock - NEW.Quantity < 10 THENINSERT INTO PurchaseAlerts (ProductID, CurrentStock, AlertType, CreatedAt)VALUES (NEW.ProductID, v_CurrentStock - NEW.Quantity, 'LowStock', NOW());END IF;
END$
DELIMITER ;

触发器管理

-- 查看所有触发器
SHOW TRIGGERS;-- 查看特定表的触发器
SHOW TRIGGERS FROM CompanyDB WHERE `Table` = 'Employees';-- 查看触发器详细信息
SELECT TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_TIMING,ACTION_STATEMENT,CREATED
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'CompanyDB'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;-- 查看触发器定义
SHOW CREATE TRIGGER trg_Employees_BeforeInsert;-- 禁用和启用触发器（通过删除和重建）
-- MySQL不支持直接禁用触发器，需要先保存定义再删除
SELECT ACTION_STATEMENT 
INTO @trigger_definition
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = 'trg_Employees_BeforeInsert';DROP TRIGGER IF EXISTS trg_Employees_BeforeInsert;
-- 执行需要的操作...
-- 然后重新创建触发器-- 删除触发器
DROP TRIGGER IF EXISTS trg_Employees_AfterUpdate;

---

16. 事务处理

基本事务操作

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;-- 执行一系列操作
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate, Reason)
SELECT EmployeeID, Salary / 1.1, Salary, NOW(), '年度调薪'
FROM Employees 
WHERE DepartmentID = 2;-- 提交事务
COMMIT;-- 回滚事务示例
START TRANSACTION;DELETE FROM Employees WHERE EmployeeID = 1;-- 假设发现错误，回滚
ROLLBACK;-- 使用保存点
START TRANSACTION;UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1;
SAVEPOINT after_dept1;UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 2;
SAVEPOINT after_dept2;-- 如果第三个操作失败，回滚到保存点
UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 3;
-- 假设出错
ROLLBACK TO SAVEPOINT after_dept2;-- 继续其他操作
UPDATE Employees SET Salary = Salary * 1.05 WHERE DepartmentID = 3;COMMIT;

事务隔离级别

-- 查看当前隔离级别
SELECT @@TRANSACTION_ISOLATION;-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;-- READ UNCOMMITTED（读未提交）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM Employees;  -- 可能读到其他事务未提交的数据（脏读）
COMMIT;-- READ COMMITTED（读已提交）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM Employees WHERE DepartmentID = 2;  -- 不会脏读
-- 其他事务提交后，再次查询可能得到不同结果（不可重复读）
SELECT * FROM Employees WHERE DepartmentID = 2;
COMMIT;-- REPEATABLE READ（可重复读）- MySQL默认级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM Employees WHERE DepartmentID = 2;
-- 在事务期间，多次查询得到相同结果
-- 但可能出现幻读（新插入的行）
SELECT * FROM Employees WHERE DepartmentID = 2;
COMMIT;-- SERIALIZABLE（串行化）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM Employees WHERE Salary > 10000;
-- 其他事务不能插入Salary > 10000的新行
COMMIT;

锁机制

-- 显式锁定表
LOCK TABLES Employees WRITE, Departments READ;-- 执行操作
UPDATE Employees SET Salary = Salary * 1.1;
SELECT * FROM Departments;-- 解锁表
UNLOCK TABLES;-- 行级锁（SELECT ... FOR UPDATE）
START TRANSACTION;SELECT * FROM Employees 
WHERE EmployeeID = 1 
FOR UPDATE;  -- 锁定该行，其他事务不能修改UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE EmployeeID = 1;COMMIT;-- 共享锁（SELECT ... LOCK IN SHARE MODE）
START TRANSACTION;SELECT * FROM Employees 
WHERE DepartmentID = 2 
LOCK IN SHARE MODE;  -- 其他事务可以读但不能写COMMIT;-- 跳过锁定的行（MySQL 8.0+）
SELECT * FROM Employees 
FOR UPDATE SKIP LOCKED;  -- 跳过被锁定的行SELECT * FROM Employees 
FOR UPDATE NOWAIT;  -- 如果无法立即获得锁则报错

死锁处理

-- 查看InnoDB状态（包含最近的死锁信息）
SHOW ENGINE INNODB STATUS;-- 查看当前锁等待
SELECT r.trx_id AS waiting_trx_id,r.trx_mysql_thread_id AS waiting_thread,r.trx_query AS waiting_query,b.trx_id AS blocking_trx_id,b.trx_mysql_thread_id AS blocking_thread,b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;-- 死锁重试机制示例
DELIMITER $
CREATE PROCEDURE sp_DeadlockRetry(IN p_MaxRetries INT
)
BEGINDECLARE v_RetryCount INT DEFAULT 0;DECLARE v_Success BOOLEAN DEFAULT FALSE;DECLARE CONTINUE HANDLER FOR 1213  -- 死锁错误代码BEGINSET v_RetryCount = v_RetryCount + 1;IF v_RetryCount < p_MaxRetries THENDO SLEEP(0.5);  -- 等待0.5秒END IF;END;WHILE v_RetryCount < p_MaxRetries AND NOT v_Success DOBEGINSTART TRANSACTION;-- 按照固定顺序访问表以避免死锁UPDATE Employees SET ModifiedDate = NOW() WHERE EmployeeID = 1;UPDATE Departments SET ModifiedDate = NOW() WHERE DepartmentID = 1;COMMIT;SET v_Success = TRUE;END;END WHILE;IF NOT v_Success THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '操作在多次重试后仍然失败';END IF;
END$
DELIMITER ;

分布式事务（XA事务）

-- XA事务示例
XA START 'xa_transaction_1';UPDATE Employees SET Salary = Salary * 1.1 WHERE EmployeeID = 1;XA END 'xa_transaction_1';-- 准备阶段
XA PREPARE 'xa_transaction_1';-- 提交或回滚
XA COMMIT 'xa_transaction_1';
-- 或
XA ROLLBACK 'xa_transaction_1';-- 查看XA事务状态
XA RECOVER;-- 两阶段提交示例
DELIMITER $
CREATE PROCEDURE sp_DistributedTransaction()
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINXA ROLLBACK 'xa_trans_1';XA ROLLBACK 'xa_trans_2';ROLLBACK;END;-- 本地事务START TRANSACTION;UPDATE LocalTable SET Status = 'Processing' WHERE ID = 1;-- 远程数据库1XA START 'xa_trans_1';-- 执行远程操作XA END 'xa_trans_1';XA PREPARE 'xa_trans_1';-- 远程数据库2XA START 'xa_trans_2';-- 执行远程操作XA END 'xa_trans_2';XA PREPARE 'xa_trans_2';-- 全部提交COMMIT;XA COMMIT 'xa_trans_1';XA COMMIT 'xa_trans_2';
END$
DELIMITER ;

事务监控和管理

-- 查看当前活动事务
SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,trx_query,trx_tables_in_use,trx_tables_locked,trx_rows_locked,trx_rows_modified
FROM information_schema.INNODB_TRX;-- 查看长时间运行的事务
SELECT trx_id,trx_started,TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,trx_mysql_thread_id,trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;-- 终止事务（通过终止连接）
-- 首先找到线程ID
SELECT trx_mysql_thread_id 
FROM information_schema.INNODB_TRX 
WHERE trx_id = 'transaction_id';-- 然后终止连接
KILL thread_id;-- 配置事务超时
SET SESSION innodb_lock_wait_timeout = 50;  -- 锁等待超时（秒）
SET SESSION wait_timeout = 28800;  -- 连接超时（秒）-- 查看事务相关配置
SHOW VARIABLES LIKE '%transaction%';
SHOW VARIABLES LIKE '%innodb_lock%';

---

17. 索引优化

创建索引

-- 创建单列索引
CREATE INDEX idx_lastname ON Employees(LastName);-- 创建唯一索引
CREATE UNIQUE INDEX uk_email ON Employees(Email);-- 创建复合索引
CREATE INDEX idx_dept_salary ON Employees(DepartmentID, Salary DESC);-- 创建前缀索引（对于长字符串）
CREATE INDEX idx_email_prefix ON Employees(Email(20));-- 创建全文索引
CREATE FULLTEXT INDEX ft_description ON Projects(Description);-- 创建空间索引
ALTER TABLE Locations ADD SPATIAL INDEX idx_coordinates (Coordinates);-- 创建函数索引（MySQL 8.0.13+）
CREATE INDEX idx_year_month ON Orders((YEAR(OrderDate)), (MONTH(OrderDate)));-- 创建降序索引（MySQL 8.0+）
CREATE INDEX idx_salary_desc ON Employees(Salary DESC);-- 不可见索引（MySQL 8.0+）
CREATE INDEX idx_test INVISIBLE ON Employees(Phone);
ALTER TABLE Employees ALTER INDEX idx_test VISIBLE;

索引类型和选择

-- B-Tree索引（默认）
CREATE INDEX idx_btree ON Employees(EmployeeID);-- Hash索引（仅Memory引擎支持）
CREATE TABLE MemoryTable (ID INT,Data VARCHAR(100),INDEX USING HASH (ID)
) ENGINE = MEMORY;-- 查看表的索引信息
SHOW INDEX FROM Employees;-- 详细的索引信息
SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX,CARDINALITY,INDEX_TYPE,IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'CompanyDB'
AND TABLE_NAME = 'Employees'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;-- 分析索引选择性
SELECT COLUMN_NAME,COUNT(DISTINCT COLUMN_NAME) / COUNT(*) AS Selectivity
FROM Employees
GROUP BY COLUMN_NAME;

索引优化策略

-- 覆盖索引
CREATE INDEX idx_covering ON Employees(DepartmentID, Salary, FirstName, LastName);-- 查询只需要索引中的数据，不需要回表
EXPLAIN SELECT DepartmentID, Salary, FirstName, LastName 
FROM Employees 
WHERE DepartmentID = 2;-- 索引合并
-- MySQL可能使用多个索引
EXPLAIN SELECT * FROM Employees 
WHERE DepartmentID = 2 OR Email = 'test@example.com';-- 强制使用特定索引
SELECT * FROM Employees 
USE INDEX (idx_dept_salary)
WHERE DepartmentID = 2 AND Salary > 10000;-- 忽略特定索引
SELECT * FROM Employees 
IGNORE INDEX (idx_lastname)
WHERE LastName = 'Smith';-- 强制使用索引进行排序
SELECT * FROM Employees 
FORCE INDEX (idx_salary_desc)
ORDER BY Salary DESC;

索引维护

-- 分析表（更新索引统计信息）
ANALYZE TABLE Employees;-- 优化表（重建表和索引）
OPTIMIZE TABLE Employees;-- 检查索引碎片（InnoDB）
SELECT TABLE_NAME,INDEX_NAME,ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
AND TABLE_NAME = 'Employees';-- 重建索引
ALTER TABLE Employees DROP INDEX idx_lastname, ADD INDEX idx_lastname (LastName);-- 禁用和启用索引（仅MyISAM）
ALTER TABLE MyISAMTable DISABLE KEYS;
-- 批量插入数据
ALTER TABLE MyISAMTable ENABLE KEYS;-- 查看索引使用情况
SELECT object_schema,object_name,index_name,count_star AS total_uses,count_read,count_write,count_fetch,count_insert,count_update,count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'CompanyDB'
AND index_name IS NOT NULL
ORDER BY count_star DESC;

索引性能分析

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM Employees WHERE LastName = 'Smith';-- 使用EXPLAIN FORMAT=JSON获取详细信息
EXPLAIN FORMAT=JSON 
SELECT e.*, d.DepartmentName 
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > 10000;-- 使用EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE 
SELECT * FROM Employees 
WHERE DepartmentID = 2 
ORDER BY Salary DESC;-- 查看查询优化器跟踪
SET optimizer_trace = "enabled=on";
SELECT * FROM Employees WHERE LastName = 'Smith';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = "enabled=off";-- 索引提示
-- 查找未使用的索引
SELECT s.table_schema,s.table_name,s.index_name,s.column_name,s.seq_in_index
FROM information_schema.statistics s
LEFT JOIN (SELECT object_schema,object_name,index_nameFROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name IS NOT NULLAND count_star > 0
) AS used_indexes
ON s.table_schema = used_indexes.object_schema
AND s.table_name = used_indexes.object_name
AND s.index_name = used_indexes.index_name
WHERE s.table_schema = 'CompanyDB'
AND s.index_name != 'PRIMARY'
AND used_indexes.index_name IS NULL;-- 查找重复的索引
SELECT t1.table_name,t1.index_name AS index1,t2.index_name AS index2,t1.column_names
FROM (SELECT table_name,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS column_namesFROM information_schema.statisticsWHERE table_schema = 'CompanyDB'GROUP BY table_name, index_name
) t1
INNER JOIN (SELECT table_name,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS column_namesFROM information_schema.statisticsWHERE table_schema = 'CompanyDB'GROUP BY table_name, index_name
) t2 ON t1.table_name = t2.table_name
AND t1.column_names = t2.column_names
AND t1.index_name < t2.index_name;

特殊索引优化

-- 全文搜索优化
-- 设置最小词长
SET GLOBAL innodb_ft_min_token_size = 2;-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON Articles(Title, Content);-- 使用全文搜索
SELECT * FROM Articles 
WHERE MATCH(Title, Content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);-- 布尔模式搜索
SELECT * FROM Articles 
WHERE MATCH(Title, Content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);-- 查询扩展
SELECT * FROM Articles 
WHERE MATCH(Title, Content) AGAINST('database' WITH QUERY EXPANSION);-- JSON索引（MySQL 5.7+）
ALTER TABLE Products 
ADD INDEX idx_json_name ((JSON_EXTRACT(ProductInfo, '$.name')));-- 使用JSON索引
SELECT * FROM Products 
WHERE JSON_EXTRACT(ProductInfo, '$.name') = 'Laptop';-- 虚拟列索引
ALTER TABLE Employees 
ADD COLUMN FullName VARCHAR(101) AS (CONCAT(FirstName, ' ', LastName)) STORED,
ADD INDEX idx_fullname (FullName);

---

18. 安全性管理

用户管理

-- 创建用户
CREATE USER 'company_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd123!';
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'SecureP@ss456!';
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppP@ss789!';-- 使用认证插件创建用户（MySQL 8.0+）
CREATE USER 'secure_user'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'UltraSecure@123';-- 修改用户密码
ALTER USER 'company_user'@'localhost' IDENTIFIED BY 'NewP@ssw0rd123!';-- 设置密码过期
ALTER USER 'company_user'@'localhost' PASSWORD EXPIRE;
ALTER USER 'company_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;-- 锁定和解锁用户
ALTER USER 'company_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'company_user'@'localhost' ACCOUNT UNLOCK;-- 设置资源限制
ALTER USER 'app_user'@'192.168.1.%' 
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100
MAX_USER_CONNECTIONS 10;-- 查看用户信息
SELECT User,Host,plugin,authentication_string,password_expired,password_last_changed,password_lifetime,account_locked
FROM mysql.user;-- 删除用户
DROP USER IF EXISTS 'old_user'@'localhost';

权限管理

-- 授予数据库级别权限
GRANT SELECT, INSERT, UPDATE ON CompanyDB.* TO 'company_user'@'localhost';-- 授予表级别权限
GRANT SELECT, INSERT ON CompanyDB.Employees TO 'hr_user'@'localhost';
GRANT ALL PRIVILEGES ON CompanyDB.TempTable TO 'temp_user'@'localhost';-- 授予列级别权限
GRANT SELECT (EmployeeID, FirstName, LastName, Email), UPDATE (Email, Phone) 
ON CompanyDB.Employees 
TO 'limited_user'@'localhost';-- 授予存储过程和函数权限
GRANT EXECUTE ON PROCEDURE CompanyDB.sp_GetEmployees TO 'app_user'@'%';
GRANT EXECUTE ON FUNCTION CompanyDB.fn_CalculateSalary TO 'app_user'@'%';-- 授予创建权限
GRANT CREATE, ALTER, DROP ON CompanyDB.* TO 'developer'@'localhost';-- 授予管理权限
GRANT SUPER ON *.* TO 'admin_user'@'localhost';
GRANT RELOAD, PROCESS ON *.* TO 'monitor_user'@'localhost';-- 使权限生效
FLUSH PRIVILEGES;-- 查看权限
SHOW GRANTS FOR 'company_user'@'localhost';
SHOW GRANTS FOR CURRENT_USER();-- 撤销权限
REVOKE INSERT, UPDATE ON CompanyDB.* FROM 'company_user'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'old_user'@'localhost';-- 查看权限表
SELECT * FROM mysql.db WHERE User = 'company_user';
SELECT * FROM mysql.tables_priv WHERE User = 'company_user';
SELECT * FROM mysql.columns_priv WHERE User = 'company_user';

角色管理（MySQL 8.0+）

-- 创建角色
CREATE ROLE 'hr_manager', 'finance_viewer', 'developer';-- 授权给角色
GRANT SELECT, INSERT, UPDATE, DELETE ON CompanyDB.Employees TO 'hr_manager';
GRANT SELECT, INSERT, UPDATE ON CompanyDB.SalaryHistory TO 'hr_manager';
GRANT SELECT ON CompanyDB.Departments TO 'hr_manager';GRANT SELECT ON CompanyDB.* TO 'finance_viewer';GRANT ALL ON CompanyDB.* TO 'developer';-- 将角色授予用户
GRANT 'hr_manager' TO 'alice'@'localhost';
GRANT 'finance_viewer' TO 'bob'@'localhost';
GRANT 'developer' TO 'charlie'@'localhost';-- 设置默认角色
SET DEFAULT ROLE 'hr_manager' TO 'alice'@'localhost';-- 激活角色
SET ROLE 'hr_manager';
SET ROLE ALL;  -- 激活所有角色-- 查看角色
SELECT CURRENT_ROLE();
SHOW GRANTS FOR 'hr_manager';-- 撤销角色
REVOKE 'hr_manager' FROM 'alice'@'localhost';-- 删除角色
DROP ROLE IF EXISTS 'temp_role';

SSL/TLS加密连接

-- 查看SSL状态
SHOW VARIABLES LIKE 'have_ssl';
SHOW VARIABLES LIKE '%ssl%';-- 创建需要SSL的用户
CREATE USER 'ssl_user'@'%' 
IDENTIFIED BY 'SecureP@ss123' 
REQUIRE SSL;-- 更严格的SSL要求
CREATE USER 'strict_ssl_user'@'%' 
IDENTIFIED BY 'StrictP@ss456' 
REQUIRE X509;-- 指定证书要求
CREATE USER 'cert_user'@'%' 
IDENTIFIED BY 'CertP@ss789' 
REQUIRE SUBJECT '/CN=client-cert/O=CompanyName/C=US'
AND ISSUER '/CN=ca-cert/O=CompanyName/C=US';-- 查看连接状态
SHOW STATUS LIKE 'Ssl_cipher';
SELECT * FROM performance_schema.session_status 
WHERE VARIABLE_NAME LIKE 'SSL%';

数据脱敏和加密

-- 使用AES加密敏感数据
-- 创建加密表
CREATE TABLE EncryptedData (ID INT PRIMARY KEY AUTO_INCREMENT,Username VARCHAR(50),EncryptedSSN VARBINARY(255),EncryptedCreditCard VARBINARY(255)
);-- 插入加密数据
INSERT INTO EncryptedData (Username, EncryptedSSN, EncryptedCreditCard)
VALUES ('john_doe',AES_ENCRYPT('123-45-6789', 'secret_key'),AES_ENCRYPT('1234-5678-9012-3456', 'secret_key')
);-- 查询解密数据
SELECT Username,CAST(AES_DECRYPT(EncryptedSSN, 'secret_key') AS CHAR) AS SSN,CAST(AES_DECRYPT(EncryptedCreditCard, 'secret_key') AS CHAR) AS CreditCard
FROM EncryptedData;-- 创建数据脱敏视图
CREATE VIEW vw_EmployeesMasked AS
SELECT EmployeeID,FirstName,LastName,CONCAT(LEFT(Email, 3), '****@****', RIGHT(Email, 4)) AS MaskedEmail,CONCAT('***-****-', RIGHT(Phone, 4)) AS MaskedPhone,CASE WHEN CURRENT_USER() LIKE '%hr_manager%' THEN SalaryELSE NULLEND AS Salary
FROM Employees;-- 使用SHA2哈希
SELECT SHA2('password123', 256) AS HashedPassword;-- 生成随机密码
SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', FLOOR(RAND() * 26) + 1, 1),SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND() * 26) + 1, 1),FLOOR(RAND() * 10000),SUBSTRING('!@#$%^&*', FLOOR(RAND() * 8) + 1, 1)
) AS RandomPassword;

审计和日志

-- 启用通用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;-- 查看日志状态
SHOW VARIABLES LIKE 'general_log%';
SHOW VARIABLES LIKE 'slow_query_log%';-- 创建审计表
CREATE TABLE AuditTrail (AuditID INT PRIMARY KEY AUTO_INCREMENT,TableName VARCHAR(64),Operation VARCHAR(10),UserName VARCHAR(100),OperationTime DATETIME DEFAULT CURRENT_TIMESTAMP,OldData JSON,NewData JSON,IPAddress VARCHAR(45),INDEX idx_table_time (TableName, OperationTime)
);-- 创建审计触发器示例
DELIMITER $
CREATE TRIGGER trg_Employees_Audit
AFTER UPDATE ON Employees
FOR EACH ROW
BEGININSERT INTO AuditTrail (TableName, Operation, UserName, OldData, NewData)VALUES ('Employees','UPDATE',USER(),JSON_OBJECT('EmployeeID', OLD.EmployeeID,'FirstName', OLD.FirstName,'LastName', OLD.LastName,'Salary', OLD.Salary),JSON_OBJECT('EmployeeID', NEW.EmployeeID,'FirstName', NEW.FirstName,'LastName', NEW.LastName,'Salary', NEW.Salary));
END$
DELIMITER ;-- 查看二进制日志
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;-- 使用MySQL Enterprise Audit（企业版功能）
-- INSTALL PLUGIN audit_log SONAME 'audit_log.so';
-- SET GLOBAL audit_log_policy = 'ALL';

安全配置建议

-- 检查安全配置
-- 确保没有匿名用户
SELECT User, Host FROM mysql.user WHERE User = '';-- 确保root只能本地访问
SELECT User, Host FROM mysql.user WHERE User = 'root' AND Host != 'localhost';-- 检查无密码用户
SELECT User, Host FROM mysql.user WHERE authentication_string = '';-- 检查过度权限
SELECT User, Host, Super_priv, Grant_priv 
FROM mysql.user 
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';-- 安全配置脚本
-- 删除匿名用户
DELETE FROM mysql.user WHERE User = '';-- 限制root访问
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';-- 删除测试数据库
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db LIKE 'test%';-- 刷新权限
FLUSH PRIVILEGES;-- 设置密码验证插件（MySQL 8.0+）
INSTALL COMPONENT 'file://component_validate_password';-- 设置密码策略
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 2;
SET GLOBAL validate_password.special_char_count = 2;
SET GLOBAL validate_password.number_count = 2;-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

---

19. 备份和恢复

逻辑备份（mysqldump）

-- 基本备份命令（在命令行执行）
-- 备份整个数据库
mysqldump -u root -p CompanyDB > CompanyDB_backup.sql-- 备份多个数据库
mysqldump -u root -p --databases CompanyDB TestDB > multiple_databases.sql-- 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql-- 备份特定表
mysqldump -u root -p CompanyDB Employees Departments > specific_tables.sql-- 备份结构不含数据
mysqldump -u root -p --no-data CompanyDB > CompanyDB_structure.sql-- 备份数据不含结构
mysqldump -u root -p --no-create-info CompanyDB > CompanyDB_data.sql-- 带压缩的备份
mysqldump -u root -p CompanyDB | gzip > CompanyDB_backup.sql.gz-- 备份存储过程和函数
mysqldump -u root -p --routines CompanyDB > CompanyDB_with_routines.sql-- 备份触发器
mysqldump -u root -p --triggers CompanyDB > CompanyDB_with_triggers.sql-- 完整备份（包含所有对象）
mysqldump -u root -p --routines --triggers --events CompanyDB > CompanyDB_complete.sql-- 适合主从复制的备份
mysqldump -u root -p --master-data=2 --single-transaction CompanyDB > CompanyDB_replication.sql-- 使用WHERE条件备份部分数据
mysqldump -u root -p CompanyDB Employees --where="DepartmentID=2" > dept2_employees.sql

恢复数据

-- 恢复整个数据库（在命令行执行）
mysql -u root -p CompanyDB < CompanyDB_backup.sql-- 恢复压缩备份
gunzip < CompanyDB_backup.sql.gz | mysql -u root -p CompanyDB-- 在MySQL中执行恢复
SOURCE /path/to/CompanyDB_backup.sql;-- 恢复到新数据库
CREATE DATABASE CompanyDB_New;
USE CompanyDB_New;
SOURCE /path/to/CompanyDB_backup.sql;-- 恢复特定表
mysql -u root -p CompanyDB < specific_tables.sql-- 忽略错误继续恢复
mysql -u root -p --force CompanyDB < CompanyDB_backup.sql

物理备份

-- 使用MySQL Enterprise Backup（企业版）
-- mysqlbackup --user=root --password --backup-dir=/backup backup-- 使用Percona XtraBackup（开源）
-- 全量备份
-- xtrabackup --backup --target-dir=/backup/full-- 增量备份
-- xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full-- 准备备份
-- xtrabackup --prepare --target-dir=/backup/full-- 恢复备份
-- xtrabackup --copy-back --target-dir=/backup/full-- 冷备份（停止MySQL服务后）
-- 1. 停止MySQL服务
-- 2. 复制数据目录
-- 3. 启动MySQL服务-- 查看数据目录位置
SELECT @@datadir;

二进制日志备份

-- 启用二进制日志
-- 在my.cnf中添加：
-- log-bin=mysql-bin
-- binlog-format=ROW
-- expire_logs_days=7-- 查看二进制日志
SHOW BINARY LOGS;
SHOW MASTER STATUS;-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';-- 刷新二进制日志
FLUSH LOGS;-- 清理二进制日志
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';-- 使用mysqlbinlog恢复
-- mysqlbinlog mysql-bin.000001 | mysql -u root -p-- 基于时间点恢复
-- mysqlbinlog --start-datetime="2024-01-01 10:00:00" --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 | mysql -u root -p-- 基于位置恢复
-- mysqlbinlog --start-position=154 --stop-position=1000 mysql-bin.000001 | mysql -u root -p

自动化备份策略

-- 创建备份用户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupP@ss123';
GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';-- 创建备份信息表
CREATE TABLE BackupHistory (BackupID INT PRIMARY KEY AUTO_INCREMENT,BackupType VARCHAR(20),BackupFile VARCHAR(255),BackupSize BIGINT,StartTime DATETIME,EndTime DATETIME,Status VARCHAR(20),ErrorMessage TEXT,CreatedBy VARCHAR(50) DEFAULT CURRENT_USER()
);-- 备份脚本示例（作为Shell脚本）
/*
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
DB_NAME="CompanyDB"
DB_USER="backup_user"
DB_PASS="BackupP@ss123"# 创建备份
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers --events $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql# 压缩备份
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete# 记录备份信息
mysql -u$DB_USER -p$DB_PASS $DB_NAME << EOF
INSERT INTO BackupHistory (BackupType, BackupFile, StartTime, EndTime, Status)
VALUES ('Full', '${DB_NAME}_${DATE}.sql.gz', NOW(), NOW(), 'Success');
EOF
*/-- 创建备份验证存储过程
DELIMITER $
CREATE PROCEDURE sp_VerifyBackup(IN p_BackupFile VARCHAR(255)
)
BEGINDECLARE v_TableCount INT;DECLARE v_RowCount INT;-- 创建临时数据库CREATE DATABASE IF NOT EXISTS BackupTest;-- 恢复备份到临时数据库-- 这里需要在应用层执行-- 验证表数量SELECT COUNT(*) INTO v_TableCountFROM information_schema.TABLESWHERE TABLE_SCHEMA = 'BackupTest';-- 验证数据行数SELECT SUM(TABLE_ROWS) INTO v_RowCountFROM information_schema.TABLESWHERE TABLE_SCHEMA = 'BackupTest';-- 清理临时数据库DROP DATABASE BackupTest;-- 返回结果SELECT v_TableCount AS TableCount, v_RowCount AS TotalRows;
END$
DELIMITER ;

备份最佳实践

-- 备份前检查
-- 检查表一致性
CHECK TABLE Employees, Departments, Projects;-- 优化表
OPTIMIZE TABLE Employees, Departments;-- 锁定表进行一致性备份（MyISAM）
FLUSH TABLES WITH READ LOCK;
-- 执行备份
UNLOCK TABLES;-- InnoDB一致性备份（使用事务）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- 执行备份查询
COMMIT;-- 备份监控查询
-- 查看备份进度（如果使用SHOW PROCESSLIST可见）
SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO
FROM information_schema.PROCESSLIST
WHERE INFO LIKE '%backup%' OR INFO LIKE '%dump%';-- 备份大小估算
SELECT TABLE_SCHEMA,ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Total Size (MB)',ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2) AS 'Data Size (MB)',ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2) AS 'Index Size (MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
GROUP BY TABLE_SCHEMA;

---

20. 性能优化

查询性能分析

-- 启用查询分析
SET profiling = 1;-- 执行查询
SELECT * FROM Employees WHERE DepartmentID = 2;-- 查看性能分析
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;-- 使用EXPLAIN分析查询计划
EXPLAIN SELECT e.*, d.DepartmentName 
FROM Employees e 
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID 
WHERE e.Salary > 10000;-- EXPLAIN扩展信息
EXPLAIN EXTENDED SELECT * FROM Employees WHERE LastName LIKE 'S%';
SHOW WARNINGS;  -- 查看优化后的查询-- JSON格式的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM Employees WHERE DepartmentID = 2;-- 分析查询执行（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM Employees WHERE Salary > 10000;

慢查询优化

-- 配置慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';-- 分析慢查询日志（使用mysqldumpslow工具）
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log-- 查找执行时间最长的查询
SELECT DIGEST_TEXT AS query,COUNT_STAR AS exec_count,SUM_TIMER_WAIT/1000000000000 AS total_latency_sec,AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec,MAX_TIMER_WAIT/1000000000000 AS max_latency_sec,SUM_ROWS_EXAMINED AS total_rows_examined,SUM_ROWS_SENT AS total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_latency_sec DESC
LIMIT 10;-- 查找全表扫描的查询
SELECT DIGEST_TEXT AS query,COUNT_STAR AS exec_count,SUM_NO_INDEX_USED AS full_scans,SUM_NO_GOOD_INDEX_USED AS full_scans_with_bad_index
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 10;

服务器性能监控

-- 查看服务器状态
SHOW GLOBAL STATUS;-- 关键性能指标
SELECT VARIABLE_NAME,VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Threads_connected','Threads_running','Questions','Slow_queries','Table_locks_waited','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Innodb_row_lock_waits'
);-- 计算缓冲池命中率
SELECT ROUND((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2) AS buffer_pool_hit_rate
FROM (SELECT MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_reads' THEN VARIABLE_VALUE END) AS Innodb_buffer_pool_reads,MAX(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' THEN VARIABLE_VALUE END) AS Innodb_buffer_pool_read_requestsFROM performance_schema.global_statusWHERE VARIABLE_NAME IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
) AS stats;-- 查看连接信息
SELECT id,user,host,db,command,time,state,info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;-- 查看锁等待
SELECT waiting_trx_id,waiting_thread,waiting_query,blocking_trx_id,blocking_thread,blocking_query
FROM sys.innodb_lock_waits;

表和索引优化

-- 分析表统计信息
ANALYZE TABLE Employees, Departments, Projects;-- 优化表（重组存储碎片）
OPTIMIZE TABLE Employees;-- 查看表状态
SHOW TABLE STATUS LIKE 'Employees';-- 查找大表
SELECT TABLE_NAME,ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',TABLE_ROWS,AVG_ROW_LENGTH,DATA_FREE / 1024 / 1024 AS 'Free Space (MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;-- 查找缺失的索引（基于查询统计）
SELECT *
FROM sys.statements_with_full_table_scans
WHERE db = 'CompanyDB'
ORDER BY exec_count DESC
LIMIT 10;-- 索引使用统计
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'CompanyDB'
AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ DESC;

配置优化

-- 查看重要配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'query_cache%';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'thread_cache_size';-- 动态调整配置
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB
SET GLOBAL max_connections = 500;
SET GLOBAL thread_cache_size = 50;-- 查询缓存（MySQL 5.7及以下）
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 67108864;  -- 64MB-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;-- 监控建议配置脚本
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS innodb_buffer_pool_size_mb,@@key_buffer_size / 1024 / 1024 AS key_buffer_size_mb,@@max_connections AS max_connections,@@thread_cache_size AS thread_cache_size,(SELECT COUNT(*) FROM information_schema.processlist) AS current_connections,(SELECT COUNT(*) FROM information_schema.processlist WHERE command != 'Sleep') AS active_connections;

查询优化技巧

-- 1. 使用索引提示
SELECT /*+ INDEX(e idx_dept_salary) */ *
FROM Employees e
WHERE DepartmentID = 2 AND Salary > 10000;-- 2. 优化JOIN顺序
SELECT /*+ STRAIGHT_JOIN */ e.*, d.DepartmentName
FROM Departments d
INNER JOIN Employees e ON d.DepartmentID = e.DepartmentID
WHERE d.Location = '上海';-- 3. 避免SELECT *
-- 不好
SELECT * FROM Employees;
-- 好
SELECT EmployeeID, FirstName, LastName, Email FROM Employees;-- 4. 使用覆盖索引
CREATE INDEX idx_covering ON Employees(DepartmentID, Salary, FirstName, LastName);
SELECT DepartmentID, Salary, FirstName, LastName 
FROM Employees 
WHERE DepartmentID = 2;-- 5. 优化LIMIT查询
-- 不好（大偏移量）
SELECT * FROM Employees ORDER BY EmployeeID LIMIT 10000, 10;
-- 好（使用索引定位）
SELECT * FROM Employees 
WHERE EmployeeID > 10000 
ORDER BY EmployeeID 
LIMIT 10;-- 6. 批量操作优化
-- 使用批量插入
INSERT INTO TempTable (col1, col2) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');-- 7. 避免在WHERE子句中使用函数
-- 不好
SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;
-- 好
SELECT * FROM Employees 
WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';-- 8. 使用UNION ALL代替UNION（如果不需要去重）
-- UNION会排序去重，UNION ALL不会
SELECT EmployeeID FROM Employees WHERE DepartmentID = 1
UNION ALL
SELECT EmployeeID FROM Employees WHERE DepartmentID = 2;

性能诊断工具

-- 使用sys schema（MySQL 5.7+）
-- 查看等待事件
SELECT * FROM sys.waits_global_by_latency;-- 查看热点表
SELECT * FROM sys.schema_table_statistics_with_buffer;-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;-- 查看IO等待
SELECT * FROM sys.io_global_by_file_by_latency;-- Performance Schema配置
-- 启用所有性能监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES';UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES';-- 查看最耗时的SQL
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT/1000000000000 AS total_seconds,AVG_TIMER_WAIT/1000000000000 AS avg_seconds,MAX_TIMER_WAIT/1000000000000 AS max_seconds
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%'
ORDER BY total_seconds DESC
LIMIT 10;

---

21. 删除表和数据库

删除表

-- 删除单个表
DROP TABLE IF EXISTS TempEmployees;-- 删除多个表
DROP TABLE IF EXISTS Table1, Table2, Table3;-- 安全删除表（检查外键依赖）
-- 查看表的外键依赖
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'Employees'
AND TABLE_SCHEMA = 'CompanyDB';-- 禁用外键检查后删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Employees;
SET FOREIGN_KEY_CHECKS = 1;-- 删除表的同时备份数据
CREATE TABLE Employees_Backup AS SELECT * FROM Employees;
DROP TABLE Employees;-- 重命名表（作为软删除）
RENAME TABLE Employees TO Employees_deleted_20240101;

清空表数据

-- TRUNCATE删除所有数据（快速，重置自增ID）
TRUNCATE TABLE TempTable;-- DELETE删除所有数据（可以回滚，保留自增ID）
DELETE FROM TempTable;-- 重置自增ID
ALTER TABLE TempTable AUTO_INCREMENT = 1;-- 条件删除
DELETE FROM Employees WHERE IsActive = FALSE;-- 限制删除数量
DELETE FROM LogTable 
WHERE CreateTime < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 1000;-- 批量删除大表数据
DELIMITER $
CREATE PROCEDURE sp_BatchDelete(IN p_TableName VARCHAR(64),IN p_Condition VARCHAR(1000),IN p_BatchSize INT
)
BEGINDECLARE v_RowsAffected INT DEFAULT 1;SET @sql = CONCAT('DELETE FROM ', p_TableName, ' WHERE ', p_Condition, ' LIMIT ', p_BatchSize);WHILE v_RowsAffected > 0 DOPREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;SET v_RowsAffected = ROW_COUNT();-- 避免锁定过久DO SLEEP(0.1);END WHILE;
END$
DELIMITER ;-- 使用存储过程批量删除
CALL sp_BatchDelete('LogTable', 'CreateTime < DATE_SUB(NOW(), INTERVAL 90 DAY)', 1000);

删除数据库对象

-- 删除视图
DROP VIEW IF EXISTS vw_EmployeeDepartmentInfo;-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_GetEmployees;-- 删除函数
DROP FUNCTION IF EXISTS fn_CalculateSalary;-- 删除触发器
DROP TRIGGER IF EXISTS trg_Employees_BeforeInsert;-- 删除索引
DROP INDEX idx_lastname ON Employees;
ALTER TABLE Employees DROP INDEX idx_email;-- 删除主键
ALTER TABLE TempTable DROP PRIMARY KEY;-- 删除外键
ALTER TABLE Employees DROP FOREIGN KEY fk_employees_departments;-- 删除列
ALTER TABLE Employees DROP COLUMN TempColumn;-- 删除分区
ALTER TABLE SalesData DROP PARTITION p2022;-- 删除事件
DROP EVENT IF EXISTS evt_daily_cleanup;

删除数据库

-- 简单删除数据库
DROP DATABASE IF EXISTS TestDB;-- 查看数据库连接
SELECT id,user,host,db
FROM information_schema.processlist
WHERE db = 'CompanyDB';-- 终止数据库连接
-- 创建终止连接的存储过程
DELIMITER $
CREATE PROCEDURE sp_KillDatabaseConnections(IN p_DatabaseName VARCHAR(64)
)
BEGINDECLARE done INT DEFAULT FALSE;DECLARE v_id INT;DECLARE cur CURSOR FORSELECT id FROM information_schema.processlist WHERE db = p_DatabaseName AND id != CONNECTION_ID();DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur;read_loop: LOOPFETCH cur INTO v_id;IF done THENLEAVE read_loop;END IF;SET @sql = CONCAT('KILL ', v_id);PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END LOOP;CLOSE cur;
END$
DELIMITER ;-- 使用存储过程终止连接
CALL sp_KillDatabaseConnections('CompanyDB');-- 然后删除数据库
DROP DATABASE CompanyDB;

批量清理脚本

-- 生成删除所有表的脚本
SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '`;') AS drop_statement
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
ORDER BY TABLE_NAME;-- 生成删除所有视图的脚本
SELECT CONCAT('DROP VIEW IF EXISTS `', TABLE_NAME, '`;') AS drop_statement
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'CompanyDB';-- 生成删除所有存储过程的脚本
SELECT CONCAT('DROP PROCEDURE IF EXISTS `', ROUTINE_NAME, '`;') AS drop_statement
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'CompanyDB'
AND ROUTINE_TYPE = 'PROCEDURE';-- 生成删除所有函数的脚本
SELECT CONCAT('DROP FUNCTION IF EXISTS `', ROUTINE_NAME, '`;') AS drop_statement
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'CompanyDB'
AND ROUTINE_TYPE = 'FUNCTION';-- 生成删除所有触发器的脚本
SELECT CONCAT('DROP TRIGGER IF EXISTS `', TRIGGER_NAME, '`;') AS drop_statement
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'CompanyDB';-- 清理所有对象的存储过程
DELIMITER $
CREATE PROCEDURE sp_DropAllObjects(IN p_DatabaseName VARCHAR(64)
)
BEGINDECLARE done INT DEFAULT FALSE;DECLARE v_ObjectName VARCHAR(64);DECLARE v_ObjectType VARCHAR(20);-- 游标定义DECLARE cur CURSOR FORSELECT TABLE_NAME, 'TABLE' AS ObjectTypeFROM information_schema.TABLESWHERE TABLE_SCHEMA = p_DatabaseNameUNION ALLSELECT TABLE_NAME, 'VIEW'FROM information_schema.VIEWSWHERE TABLE_SCHEMA = p_DatabaseName;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 禁用外键检查SET FOREIGN_KEY_CHECKS = 0;OPEN cur;read_loop: LOOPFETCH cur INTO v_ObjectName, v_ObjectType;IF done THENLEAVE read_loop;END IF;SET @sql = CONCAT('DROP ', v_ObjectType, ' IF EXISTS `', p_DatabaseName, '`.`', v_ObjectName, '`');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END LOOP;CLOSE cur;-- 重新启用外键检查SET FOREIGN_KEY_CHECKS = 1;SELECT CONCAT('已清理数据库 ', p_DatabaseName, ' 中的所有对象') AS Result;
END$
DELIMITER ;

数据库维护和清理

-- 查看数据库大小
SELECT table_schema AS 'Database',ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',COUNT(DISTINCT table_name) AS 'Tables'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;-- 查找可以清理的表
SELECT TABLE_NAME,TABLE_ROWS,ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',CREATE_TIME,UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
AND (TABLE_NAME LIKE '%_temp%' OR TABLE_NAME LIKE '%_backup%'OR TABLE_NAME LIKE '%_old%'
)
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;-- 清理二进制日志
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);-- 清理错误日志（需要文件系统权限）
-- FLUSH ERROR LOGS;-- 优化所有表
SELECT CONCAT('OPTIMIZE TABLE `', TABLE_NAME, '`;') AS optimize_statement
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'CompanyDB'
AND DATA_FREE > 0;-- 最终清理检查
SELECT '检查外键约束' AS CheckType,COUNT(*) AS Count
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'CompanyDB'
AND REFERENCED_TABLE_NAME IS NOT NULL
UNION ALL
SELECT '检查触发器',COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'CompanyDB'
UNION ALL
SELECT '检查存储过程',COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'CompanyDB'
AND ROUTINE_TYPE = 'PROCEDURE'
UNION ALL
SELECT '检查函数',COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'CompanyDB'
AND ROUTINE_TYPE = 'FUNCTION';-- 数据库完整性检查
CHECK TABLE Employees, Departments, Projects;-- 修复表（如果需要）
REPAIR TABLE TableName;PRINT '数据库清理和维护完成!';

---

总结

本指南全面介绍了MySQL数据库的操作和管理，涵盖了从基础到高级的各个方面。

核心知识点

• 数据库设计：合理的表结构、数据类型选择、约束设计
• SQL操作：增删改查、复杂查询、窗口函数、JSON支持
• 性能优化：索引设计、查询优化、服务器调优
• 高可用性：备份恢复、主从复制、故障转移

MySQL特色功能

• 存储引擎：InnoDB的事务支持、MyISAM的全文索引
• JSON支持：原生JSON数据类型和函数
• 窗口函数：MySQL 8.0+的分析功能
• CTE支持：公共表表达式简化复杂查询

最佳实践建议

1. 设计阶段
   • 选择合适的存储引擎（通常使用InnoDB）
   • 使用utf8mb4字符集支持完整的Unicode
   • 合理设计索引，避免过度索引
2. 开发阶段
   • 使用预处理语句防止SQL注入
   • 编写可读性强的SQL语句
   • 充分利用MySQL的特性如分区、JSON等
3. 运维阶段
   • 定期备份，测试恢复流程
   • 监控慢查询，及时优化
   • 保持MySQL版本更新，获得新特性和安全补丁
4. 安全管理
   • 最小权限原则
   • 使用SSL加密连接
   • 定期审计用户权限

---

*注意：本指南基于MySQL 8.0编写，部分特性在早期版本中可能不可用。