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編寫，部分特性在早期版本中可能不可用。