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 UPDATE
    FirstName = 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 DESC
        SEPARATOR '; '
    ) 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 TotalSalary
    FROM Employees
    GROUP 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 EmpCount
    FROM Employees
    GROUP BY DepartmentID
),
DeptProjects AS (
    SELECT DepartmentID, COUNT(*) AS ProjCount
    FROM Projects
    GROUP 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 HierarchyPath
    FROM Employees e
    INNER JOIN Departments d ON e.EmployeeID = d.ManagerID
    
    UNION 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 e
    INNER JOIN EmployeeHierarchy eh ON e.DepartmentID = eh.DepartmentID
    WHERE 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.05
        WHEN DepartmentID = 2 THEN Salary * 1.10
        WHEN DepartmentID = 3 THEN Salary * 1.08
        ELSE Salary * 1.03
    END,
    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 SalaryRank
    FROM 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 EmployeeID
    WHEN 1 THEN 8500
    WHEN 2 THEN 12500
    WHEN 3 THEN 9000
    ELSE 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()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE offset_val INT DEFAULT 0;
    
    WHILE NOT done DO
        UPDATE Employees 
        SET Salary = Salary * 1.03,
            ModifiedDate = NOW()
        WHERE DepartmentID = 2
        LIMIT offset_val, batch_size;
        
        IF ROW_COUNT() < batch_size THEN
            SET done = TRUE;
        ELSE
            SET 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()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;
    
    WHILE rows_affected > 0 DO
        DELETE FROM LargeLogTable
        WHERE LogDate < DATE_SUB(NOW(), INTERVAL 90 DAY)
        LIMIT 1000;
        
        SET rows_affected = ROW_COUNT();
        
        IF rows_affected > 0 THEN
            DO 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()
BEGIN
    SELECT 
        EmployeeID,
        CONCAT(FirstName, ' ', LastName) AS FullName,
        Email,
        Salary,
        DepartmentID
    FROM Employees
    WHERE IsActive = TRUE
    ORDER BY LastName, FirstName;
END$$

-- 恢复分隔符
DELIMITER ;

-- 执行存储过程
CALL sp_GetAllEmployees();

-- 带参数的存储过程
DELIMITER $$
CREATE PROCEDURE sp_GetEmployeesByDepartment(
    IN p_DepartmentID INT
)
BEGIN
    SELECT 
        e.EmployeeID,
        CONCAT(e.FirstName, ' ', e.LastName) AS FullName,
        e.Email,
        e.Salary,
        d.DepartmentName
    FROM Employees e
    INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
    WHERE e.DepartmentID = p_DepartmentID
    AND e.IsActive = TRUE
    ORDER 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 THEN
        SET p_IsActive = TRUE;
    END IF;
    
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.Email,
        e.Salary,
        d.DepartmentName
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    WHERE (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_IsActive
    ORDER 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_TotalSalary
    FROM Employees
    WHERE DepartmentID = p_DepartmentID
    AND IsActive = TRUE;
    
    -- 获取部门名称
    SELECT DepartmentName 
    INTO p_DepartmentName
    FROM 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 RemainingBudget
    FROM Departments d
    WHERE 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
)
BEGIN
    DECLARE 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)
)
BEGIN
    DECLARE v_OldDepartmentID INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 错误处理
        ROLLBACK;
        SET p_Success = FALSE;
        SET p_Message = CONCAT('错误: ', @error_message);
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 设置错误消息变量
    SET @error_message = '未知错误';
    
    -- 检查员工是否存在
    SELECT DepartmentID INTO v_OldDepartmentID
    FROM Employees
    WHERE EmployeeID = p_EmployeeID;
    
    IF v_OldDepartmentID IS NULL THEN
        SET @error_message = '员工不存在';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '员工不存在';
    END IF;
    
    -- 检查部门是否存在
    IF NOT EXISTS (SELECT 1 FROM Departments WHERE DepartmentID = p_NewDepartmentID) THEN
        SET @error_message = '部门不存在';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '部门不存在';
    END IF;
    
    -- 更新员工部门
    UPDATE Employees
    SET 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 EmployeeProjects
    SET UnassignedDate = CURDATE()
    WHERE EmployeeID = p_EmployeeID
    AND 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
)
BEGIN
    DECLARE v_SQL TEXT;
    
    -- 构建基本查询
    SET v_SQL = 'SELECT ';
    
    -- 添加列（默认为所有列）
    IF p_SelectColumns IS NULL OR p_SelectColumns = '' THEN
        SET v_SQL = CONCAT(v_SQL, '*');
    ELSE
        SET 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 != '' THEN
        SET v_SQL = CONCAT(v_SQL, 'WHERE ', p_WhereClause, ' ');
    END IF;
    
    -- 添加ORDER BY子句
    IF p_OrderBy IS NOT NULL AND p_OrderBy != '' THEN
        SET v_SQL = CONCAT(v_SQL, 'ORDER BY ', p_OrderBy, ' ');
    END IF;
    
    -- 添加LIMIT子句
    IF p_Limit IS NOT NULL AND p_Limit > 0 THEN
        SET v_SQL = CONCAT(v_SQL, 'LIMIT ', p_Limit);
    END IF;
    
    -- 执行动态SQL
    SET @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()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_EmployeeID INT;
    DECLARE v_Salary DECIMAL(10,2);
    DECLARE v_IncreaseRate DECIMAL(3,2);
    
    -- 声明游标
    DECLARE emp_cursor CURSOR FOR
        SELECT EmployeeID, Salary
        FROM Employees
        WHERE IsActive = TRUE;
    
    -- 声明继续处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 开启游标
    OPEN emp_cursor;
    
    -- 循环处理
    read_loop: LOOP
        FETCH emp_cursor INTO v_EmployeeID, v_Salary;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 根据薪资级别确定涨幅
        SET v_IncreaseRate = CASE
            WHEN v_Salary < 6000 THEN 0.10
            WHEN v_Salary < 10000 THEN 0.08
            WHEN v_Salary < 15000 THEN 0.05
            ELSE 0.03
        END;
        
        -- 更新薪资
        UPDATE Employees
        SET 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
BEGIN
    RETURN 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
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_BirthDate, CURDATE());
END$$
DELIMITER ;

-- 计算工作年限
DELIMITER $$
CREATE FUNCTION fn_GetWorkYears(
    p_HireDate DATE
) 
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGIN
    RETURN 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
BEGIN
    IF p_CurrencySymbol IS NULL THEN
        SET 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
BEGIN
    DECLARE v_Grade VARCHAR(20);
    
    IF p_Salary >= 15000 AND p_WorkYears >= 5 THEN
        SET v_Grade = '高级专家';
    ELSEIF p_Salary >= 12000 AND p_WorkYears >= 3 THEN
        SET v_Grade = '资深员工';
    ELSEIF p_Salary >= 8000 AND p_WorkYears >= 1 THEN
        SET v_Grade = '中级员工';
    ELSEIF p_WorkYears < 1 THEN
        SET v_Grade = '新员工';
    ELSE
        SET v_Grade = '初级员工';
    END IF;
    
    RETURN v_Grade;
END$$
DELIMITER ;

-- 计算部门预算使用率
DELIMITER $$
CREATE FUNCTION fn_GetBudgetUsageRate(
    p_DepartmentID INT
) 
RETURNS DECIMAL(5,2)
READS SQL DATA
BEGIN
    DECLARE v_Budget DECIMAL(15,2);
    DECLARE v_TotalSalary DECIMAL(15,2);
    DECLARE v_UsageRate DECIMAL(5,2);
    
    -- 获取部门预算
    SELECT Budget INTO v_Budget
    FROM Departments
    WHERE DepartmentID = p_DepartmentID;
    
    -- 获取部门总薪资
    SELECT IFNULL(SUM(Salary), 0) INTO v_TotalSalary
    FROM Employees
    WHERE DepartmentID = p_DepartmentID
    AND IsActive = TRUE;
    
    -- 计算使用率
    IF v_Budget > 0 THEN
        SET v_UsageRate = ROUND((v_TotalSalary / v_Budget) * 100, 2);
    ELSE
        SET v_UsageRate = 0;
    END IF;
    
    RETURN v_UsageRate;
END$$
DELIMITER ;

字符串处理函数

-- 提取邮箱域名
DELIMITER $$
CREATE FUNCTION fn_GetEmailDomain(
    p_Email VARCHAR(100)
) 
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE v_AtPosition INT;
    
    SET v_AtPosition = LOCATE('@', p_Email);
    
    IF v_AtPosition > 0 THEN
        RETURN SUBSTRING(p_Email, v_AtPosition + 1);
    ELSE
        RETURN NULL;
    END IF;
END$$
DELIMITER ;

-- 手机号脱敏
DELIMITER $$
CREATE FUNCTION fn_MaskPhoneNumber(
    p_Phone VARCHAR(20)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF LENGTH(p_Phone) >= 11 THEN
        RETURN CONCAT(
            LEFT(p_Phone, 3),
            '****',
            RIGHT(p_Phone, 4)
        );
    ELSE
        RETURN p_Phone;
    END IF;
END$$
DELIMITER ;

-- 生成随机密码
DELIMITER $$
CREATE FUNCTION fn_GeneratePassword(
    p_Length INT
) 
RETURNS VARCHAR(100)
NO SQL
BEGIN
    DECLARE v_Chars VARCHAR(100) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*';
    DECLARE v_Password VARCHAR(100) DEFAULT '';
    DECLARE v_Index INT DEFAULT 1;
    
    WHILE v_Index <= p_Length DO
        SET 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
BEGIN
    DECLARE 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) THEN
            SET 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
BEGIN
    DECLARE 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
BEGIN
    DECLARE v_Result VARCHAR(1000);
    
    SET v_Result = JSON_UNQUOTE(JSON_EXTRACT(p_JSON, p_Path));
    
    IF v_Result IS NULL OR v_Result = 'null' THEN
        RETURN NULL;
    ELSE
        RETURN v_Result;
    END IF;
END$$
DELIMITER ;

-- 构建员工JSON对象
DELIMITER $$
CREATE FUNCTION fn_BuildEmployeeJSON(
    p_EmployeeID INT
) 
RETURNS JSON
READS SQL DATA
BEGIN
    DECLARE 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_JSON
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    WHERE e.EmployeeID = p_EmployeeID;
    
    RETURN v_JSON;
END$$
DELIMITER ;

递归函数示例

-- 计算阶乘
DELIMITER $$
CREATE FUNCTION fn_Factorial(
    p_Number INT
) 
RETURNS BIGINT
DETERMINISTIC
BEGIN
    IF p_Number <= 1 THEN
        RETURN 1;
    ELSE
        RETURN p_Number * fn_Factorial(p_Number - 1);
    END IF;
END$$
DELIMITER ;

-- 斐波那契数列
DELIMITER $$
CREATE FUNCTION fn_Fibonacci(
    p_N INT
) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE 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 THEN
        RETURN 0;
    ELSEIF p_N = 1 THEN
        RETURN 1;
    END IF;
    
    WHILE v_Count <= p_N DO
        SET 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,}$' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '邮箱格式不正确';
    END IF;
    
    -- 自动设置创建信息
    SET NEW.CreatedDate = NOW();
    SET NEW.CreatedBy = CURRENT_USER();
    
    -- 确保薪资在合理范围内
    IF NEW.Salary < 3000 THEN
        SET NEW.Salary = 3000;
    ELSEIF NEW.Salary > 100000 THEN
        SET 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 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '降薪幅度不能超过20%';
    END IF;
    
    -- 记录重要字段变更
    IF OLD.Salary != NEW.Salary THEN
        INSERT 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) THEN
        SIGNAL 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 THEN
        INSERT 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 - 1
        WHERE DepartmentID = OLD.DepartmentID;
        
        -- 更新新部门计数
        UPDATE Departments 
        SET EmployeeCount = EmployeeCount + 1
        WHERE 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 - 1
    WHERE 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 p
        SET 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 5000
                WHEN ep.Role = 'Lead' THEN 3000
                ELSE 1000
            END,
            'Pending'
        FROM EmployeeProjects ep
        WHERE ep.ProjectID = NEW.ProjectID
        AND ep.UnassignedDate IS NULL;
    END IF;
    
    -- 项目取消时的处理
    IF OLD.Status != 'Cancelled' AND NEW.Status = 'Cancelled' THEN
        -- 释放所有项目成员
        UPDATE EmployeeProjects
        SET UnassignedDate = CURDATE()
        WHERE ProjectID = NEW.ProjectID
        AND UnassignedDate IS NULL;
    END IF;
END$$
DELIMITER ;

-- 库存管理触发器
DELIMITER $
CREATE TRIGGER trg_OrderItems_AfterInsert
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGIN
    DECLARE v_CurrentStock INT;
    
    -- 获取当前库存
    SELECT Stock INTO v_CurrentStock
    FROM Products
    WHERE ProductID = NEW.ProductID
    FOR UPDATE;  -- 锁定行
    
    -- 检查库存是否充足
    IF v_CurrentStock < NEW.Quantity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '库存不足';
    END IF;
    
    -- 更新库存
    UPDATE Products
    SET Stock = Stock - NEW.Quantity,
        LastSoldDate = NOW()
    WHERE ProductID = NEW.ProductID;
    
    -- 如果库存低于阈值，创建采购提醒
    IF v_CurrentStock - NEW.Quantity < 10 THEN
        INSERT 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
)
BEGIN
    DECLARE v_RetryCount INT DEFAULT 0;
    DECLARE v_Success BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR 1213  -- 死锁错误代码
    BEGIN
        SET v_RetryCount = v_RetryCount + 1;
        IF v_RetryCount < p_MaxRetries THEN
            DO SLEEP(0.5);  -- 等待0.5秒
        END IF;
    END;
    
    WHILE v_RetryCount < p_MaxRetries AND NOT v_Success DO
        BEGIN
            START 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 THEN
        SIGNAL 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()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        XA ROLLBACK 'xa_trans_1';
        XA ROLLBACK 'xa_trans_2';
        ROLLBACK;
    END;
    
    -- 本地事务
    START TRANSACTION;
    UPDATE LocalTable SET Status = 'Processing' WHERE ID = 1;
    
    -- 远程数据库1
    XA START 'xa_trans_1';
    -- 执行远程操作
    XA END 'xa_trans_1';
    XA PREPARE 'xa_trans_1';
    
    -- 远程数据库2
    XA 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 ;