notes/MySQL常用SQL语法使用文档.md

# MySQL 常用SQL语法使用文档

## 目录
1. [SQL基础概念](#sql基础概念)
2. [数据定义语言 DDL](#数据定义语言-ddl)
3. [数据操作语言 DML](#数据操作语言-dml)
4. [数据查询语言 DQL](#数据查询语言-dql)
5. [数据控制语言 DCL](#数据控制语言-dcl)
6. [复杂查询操作](#复杂查询操作)
7. [索引和约束](#索引和约束)
8. [存储过程和函数](#存储过程和函数)
9. [实际项目案例](#实际项目案例)
10. [性能优化和最佳实践](#性能优化和最佳实践)

## SQL基础概念

### SQL语言分类

| 类型 | 全称 | 功能 | 主要语句 |
|------|------|------|----------|
| DDL | Data Definition Language | 数据定义 | CREATE, ALTER, DROP |
| DML | Data Manipulation Language | 数据操作 | INSERT, UPDATE, DELETE |
| DQL | Data Query Language | 数据查询 | SELECT |
| DCL | Data Control Language | 数据控制 | GRANT, REVOKE |
| TCL | Transaction Control Language | 事务控制 | COMMIT, ROLLBACK |

### 基本概念

```sql
-- 数据库：存储数据的容器
-- 表：数据的结构化存储
-- 行（记录）：表中的每一条数据
-- 列（字段）：表中的每一个属性
-- 主键：唯一标识每行数据的字段
-- 外键：引用其他表主键的字段
```

## 数据定义语言 DDL

### 1. 数据库操作

```sql
-- 创建数据库
CREATE DATABASE company_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;
SHOW CREATE DATABASE company_db;

-- 修改数据库
ALTER DATABASE company_db CHARACTER SET utf8mb4;

-- 删除数据库
DROP DATABASE IF EXISTS company_db;

-- 使用数据库
USE company_db;
```

### 2. 数据表操作

#### 创建表

```sql
-- 员工信息表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    emp_code VARCHAR(20) NOT NULL UNIQUE COMMENT '员工编号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('M', 'F') DEFAULT 'M' COMMENT '性别',
    birth_date DATE COMMENT '出生日期',
    hire_date DATE NOT NULL DEFAULT (CURRENT_DATE) COMMENT '入职日期',
    department_id INT COMMENT '部门ID',
    position VARCHAR(50) COMMENT '职位',
    salary DECIMAL(10,2) CHECK (salary > 0) COMMENT '薪资',
    manager_id INT COMMENT '直属上级ID',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) COMMENT '电话',
    address TEXT COMMENT '地址',
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    INDEX idx_department (department_id),
    INDEX idx_manager (manager_id),
    INDEX idx_hire_date (hire_date),
    INDEX idx_name_dept (name, department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    dept_code VARCHAR(20) NOT NULL UNIQUE COMMENT '部门编码',
    dept_name VARCHAR(50) NOT NULL COMMENT '部门名称',
    parent_id INT COMMENT '上级部门ID',
    manager_id INT COMMENT '部门经理ID',
    location VARCHAR(100) COMMENT '办公地点',
    budget DECIMAL(12,2) COMMENT '部门预算',
    description TEXT COMMENT '部门描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_parent (parent_id),
    INDEX idx_manager (manager_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- 项目表
CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
    project_code VARCHAR(30) NOT NULL UNIQUE COMMENT '项目编码',
    project_name VARCHAR(100) NOT NULL COMMENT '项目名称',
    description TEXT COMMENT '项目描述',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE COMMENT '结束日期',
    budget DECIMAL(12,2) COMMENT '项目预算',
    actual_cost DECIMAL(12,2) DEFAULT 0 COMMENT '实际成本',
    status ENUM('planning', 'active', 'completed', 'cancelled') DEFAULT 'planning' COMMENT '项目状态',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium' COMMENT '优先级',
    client VARCHAR(100) COMMENT '客户名称',
    manager_id INT COMMENT '项目经理ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_manager (manager_id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目表';

-- 员工项目关联表
CREATE TABLE employee_projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL COMMENT '员工ID',
    project_id INT NOT NULL COMMENT '项目ID',
    role VARCHAR(50) COMMENT '在项目中的角色',
    allocation_percentage DECIMAL(5,2) DEFAULT 100.00 COMMENT '分配比例',
    start_date DATE NOT NULL COMMENT '开始参与日期',
    end_date DATE COMMENT '结束参与日期',
    hourly_rate DECIMAL(8,2) COMMENT '小时费率',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_emp_project (emp_id, project_id),
    INDEX idx_employee (emp_id),
    INDEX idx_project (project_id),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工项目关联表';

-- 考勤表
CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL COMMENT '员工ID',
    work_date DATE NOT NULL COMMENT '工作日期',
    check_in_time TIME COMMENT '签到时间',
    check_out_time TIME COMMENT '签退时间',
    break_hours DECIMAL(4,2) DEFAULT 1.00 COMMENT '休息时间（小时）',
    work_hours DECIMAL(4,2) COMMENT '工作时长',
    overtime_hours DECIMAL(4,2) DEFAULT 0 COMMENT '加班时长',
    status ENUM('present', 'absent', 'late', 'early_leave', 'holiday') DEFAULT 'present' COMMENT '考勤状态',
    notes TEXT COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_emp_date (emp_id, work_date),
    INDEX idx_employee (emp_id),
    INDEX idx_date (work_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤表';
```

#### 修改表结构

```sql
-- 添加列
ALTER TABLE employees 
ADD COLUMN emergency_contact VARCHAR(100) COMMENT '紧急联系人' AFTER phone,
ADD COLUMN skills TEXT COMMENT '技能描述';

-- 修改列
ALTER TABLE employees 
MODIFY COLUMN salary DECIMAL(12,2) COMMENT '薪资（支持更大金额）',
CHANGE COLUMN emp_code employee_code VARCHAR(25) NOT NULL UNIQUE COMMENT '员工编号';

-- 删除列
ALTER TABLE employees DROP COLUMN emergency_contact;

-- 添加索引
ALTER TABLE employees 
ADD INDEX idx_email (email),
ADD INDEX idx_status_dept (status, department_id);

-- 删除索引
ALTER TABLE employees DROP INDEX idx_email;

-- 添加外键约束
ALTER TABLE employees 
ADD CONSTRAINT fk_emp_department 
FOREIGN KEY (department_id) REFERENCES departments(dept_id),
ADD CONSTRAINT fk_emp_manager 
FOREIGN KEY (manager_id) REFERENCES employees(emp_id);

-- 删除外键约束
ALTER TABLE employees DROP FOREIGN KEY fk_emp_department;

-- 重命名表
RENAME TABLE employees TO staff;
RENAME TABLE staff TO employees;

-- 查看表结构
DESC employees;
SHOW CREATE TABLE employees;
SHOW INDEX FROM employees;
```

#### 删除表

```sql
-- 删除表（如果存在）
DROP TABLE IF EXISTS employee_projects;

-- 删除多个表
DROP TABLE IF EXISTS attendance, employee_projects;
```

## 数据操作语言 DML

### 插入示例数据

```sql
-- 插入部门数据
INSERT INTO departments (dept_code, dept_name, parent_id, location, budget, description) VALUES
('TECH', '技术部', NULL, '北京总部A座', 5000000.00, '负责产品研发和技术创新'),
('TECH001', '前端开发组', 1, '北京总部A座10层', 1500000.00, '负责前端页面开发'),
('TECH002', '后端开发组', 1, '北京总部A座11层', 2000000.00, '负责后端服务开发'),
('TECH003', '测试组', 1, '北京总部A座12层', 800000.00, '负责产品质量测试'),
('SALES', '销售部', NULL, '北京总部B座', 3000000.00, '负责产品销售和客户维护'),
('SALES001', '华北销售组', 5, '北京总部B座5层', 1000000.00, '负责华北地区销售'),
('SALES002', '华南销售组', 5, '深圳分公司', 1200000.00, '负责华南地区销售'),
('HR', '人力资源部', NULL, '北京总部C座', 1000000.00, '负责人力资源管理'),
('FINANCE', '财务部', NULL, '北京总部C座', 800000.00, '负责财务管理和成本控制'),
('ADMIN', '行政部', NULL, '北京总部C座', 600000.00, '负责行政后勤管理');

-- 插入员工数据
INSERT INTO employees (employee_code, name, gender, birth_date, hire_date, department_id, position, salary, manager_id, email, phone, address, status) VALUES
('EMP001', '张总', 'M', '1975-03-15', '2015-01-01', 1, 'CTO', 50000.00, NULL, 'zhang.cto@company.com', '13800001001', '北京市朝阳区', 'active'),
('EMP002', '李经理', 'F', '1982-07-20', '2018-03-15', 2, '前端技术经理', 25000.00, 1, 'li.frontend@company.com', '13800001002', '北京市海淀区', 'active'),
('EMP003', '王经理', 'M', '1980-11-08', '2017-09-01', 3, '后端技术经理', 28000.00, 1, 'wang.backend@company.com', '13800001003', '北京市西城区', 'active'),
('EMP004', '赵经理', 'F', '1985-05-12', '2019-06-01', 4, '测试经理', 22000.00, 1, 'zhao.test@company.com', '13800001004', '北京市东城区', 'active'),
('EMP005', '钱总监', 'M', '1978-09-25', '2016-08-15', 5, '销售总监', 35000.00, NULL, 'qian.sales@company.com', '13800001005', '北京市丰台区', 'active'),
('EMP006', '孙工程师', 'M', '1990-12-03', '2020-04-20', 2, '高级前端工程师', 18000.00, 2, 'sun.fe@company.com', '13800001006', '北京市昌平区', 'active'),
('EMP007', '周工程师', 'F', '1992-02-14', '2021-07-10', 2, '前端工程师', 15000.00, 2, 'zhou.fe@company.com', '13800001007', '北京市通州区', 'active'),
('EMP008', '吴工程师', 'M', '1988-06-30', '2019-11-25', 3, '高级后端工程师', 20000.00, 3, 'wu.be@company.com', '13800001008', '北京市房山区', 'active'),
('EMP009', '郑工程师', 'F', '1991-04-18', '2020-09-15', 3, '后端工程师', 16000.00, 3, 'zheng.be@company.com', '13800001009', '北京市大兴区', 'active'),
('EMP010', '刘测试', 'M', '1993-08-22', '2021-12-01', 4, '高级测试工程师', 16000.00, 4, 'liu.test@company.com', '13800001010', '北京市密云区', 'active'),
('EMP011', '陈销售', 'F', '1987-10-05', '2018-02-28', 6, '销售经理', 20000.00, 5, 'chen.sales@company.com', '13800001011', '北京市怀柔区', 'active'),
('EMP012', '林销售', 'M', '1989-12-16', '2019-05-20', 7, '销售经理', 22000.00, 5, 'lin.sales@company.com', '13800001012', '深圳市南山区', 'active');

-- 插入项目数据
INSERT INTO projects (project_code, project_name, description, start_date, end_date, budget, actual_cost, status, priority, client, manager_id) VALUES
('PRJ001', '电商平台开发', '为客户开发一个完整的电商平台系统', '2024-01-15', '2024-08-15', 2000000.00, 800000.00, 'active', 'high', '阿里巴巴', 3),
('PRJ002', '移动APP开发', '开发iOS和Android移动应用', '2024-02-01', '2024-07-01', 1500000.00, 600000.00, 'active', 'medium', '腾讯', 2),
('PRJ003', '数据分析系统', '构建企业级数据分析和报表系统', '2024-03-01', '2024-09-30', 1800000.00, 300000.00, 'active', 'high', '百度', 3),
('PRJ004', '官网重构', '重新设计和开发公司官方网站', '2024-01-01', '2024-04-30', 500000.00, 450000.00, 'completed', 'medium', '字节跳动', 2),
('PRJ005', '内部管理系统', '开发HR和财务内部管理系统', '2024-04-01', '2024-10-31', 1200000.00, 200000.00, 'planning', 'low', '内部项目', 3);

-- 插入员工项目关联数据
INSERT INTO employee_projects (emp_id, project_id, role, allocation_percentage, start_date, end_date, hourly_rate) VALUES
(2, 2, '项目经理', 100.00, '2024-02-01', NULL, 250.00),
(2, 4, '项目经理', 80.00, '2024-01-01', '2024-04-30', 250.00),
(3, 1, '技术负责人', 100.00, '2024-01-15', NULL, 280.00),
(3, 3, '技术负责人', 70.00, '2024-03-01', NULL, 280.00),
(3, 5, '技术顾问', 30.00, '2024-04-01', NULL, 280.00),
(6, 2, '高级前端开发', 100.00, '2024-02-01', NULL, 180.00),
(6, 4, '前端开发', 100.00, '2024-01-01', '2024-04-30', 180.00),
(7, 2, '前端开发', 100.00, '2024-02-15', NULL, 150.00),
(8, 1, '高级后端开发', 100.00, '2024-01-15', NULL, 200.00),
(8, 3, '后端开发', 50.00, '2024-03-01', NULL, 200.00),
(9, 1, '后端开发', 80.00, '2024-02-01', NULL, 160.00),
(9, 5, '后端开发', 60.00, '2024-04-01', NULL, 160.00),
(10, 1, '测试工程师', 70.00, '2024-02-15', NULL, 160.00),
(10, 2, '测试工程师', 80.00, '2024-03-01', NULL, 160.00),
(10, 3, '测试工程师', 50.00, '2024-03-15', NULL, 160.00);

-- 插入考勤数据（最近一周）
INSERT INTO attendance (emp_id, work_date, check_in_time, check_out_time, break_hours, work_hours, overtime_hours, status) VALUES
-- 张总考勤
(1, '2024-01-15', '09:00:00', '18:30:00', 1.00, 8.50, 0.50, 'present'),
(1, '2024-01-16', '09:15:00', '19:00:00', 1.00, 8.75, 0.75, 'late'),
(1, '2024-01-17', '08:45:00', '18:00:00', 1.00, 8.25, 0.00, 'present'),
(1, '2024-01-18', '09:00:00', '20:00:00', 1.00, 10.00, 2.00, 'present'),
(1, '2024-01-19', '09:00:00', '18:00:00', 1.00, 8.00, 0.00, 'present'),
-- 李经理考勤
(2, '2024-01-15', '09:30:00', '18:00:00', 1.00, 7.50, 0.00, 'late'),
(2, '2024-01-16', '09:00:00', '19:30:00', 1.00, 9.50, 1.50, 'present'),
(2, '2024-01-17', '09:00:00', '18:00:00', 1.00, 8.00, 0.00, 'present'),
(2, '2024-01-18', NULL, NULL, 0.00, 0.00, 0.00, 'absent'),
(2, '2024-01-19', '09:00:00', '17:30:00', 1.00, 7.50, 0.00, 'early_leave'),
-- 王经理考勤
(3, '2024-01-15', '08:50:00', '19:00:00', 1.00, 9.17, 1.17, 'present'),
(3, '2024-01-16', '09:00:00', '21:00:00', 1.00, 11.00, 3.00, 'present'),
(3, '2024-01-17', '09:00:00', '18:00:00', 1.00, 8.00, 0.00, 'present'),
(3, '2024-01-18', '08:45:00', '18:30:00', 1.00, 8.75, 0.75, 'present'),
(3, '2024-01-19', '09:00:00', '18:00:00', 1.00, 8.00, 0.00, 'present');
```

### 1. INSERT 语句详解

```sql
-- 基本插入
INSERT INTO departments (dept_code, dept_name, location) 
VALUES ('IT', 'IT部门', '3楼');

-- 插入多行
INSERT INTO departments (dept_code, dept_name, location) VALUES
('MKT', '市场部', '2楼'),
('OPS', '运营部', '4楼');

-- 插入查询结果
INSERT INTO departments (dept_code, dept_name, parent_id)
SELECT CONCAT('SUB_', dept_code), CONCAT(dept_name, '分部'), dept_id
FROM departments 
WHERE parent_id IS NULL;

-- 插入时忽略重复
INSERT IGNORE INTO departments (dept_code, dept_name) 
VALUES ('TECH', '技术部门');

-- 插入时更新重复
INSERT INTO departments (dept_code, dept_name, budget) 
VALUES ('TECH', '技术部', 6000000.00)
ON DUPLICATE KEY UPDATE 
    dept_name = VALUES(dept_name),
    budget = VALUES(budget),
    updated_at = CURRENT_TIMESTAMP;

-- 替换插入
REPLACE INTO departments (dept_id, dept_code, dept_name) 
VALUES (1, 'TECH', '技术研发部');
```

### 2. UPDATE 语句详解

```sql
-- 基本更新
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 1;

-- 多表更新
UPDATE employees e
JOIN departments d ON e.department_id = d.dept_id
SET e.salary = e.salary * 1.05
WHERE d.dept_name LIKE '%技术%';

-- 条件更新
UPDATE employees 
SET 
    salary = CASE 
        WHEN position LIKE '%经理%' THEN salary * 1.15
        WHEN position LIKE '%高级%' THEN salary * 1.10
        ELSE salary * 1.05
    END,
    updated_at = CURRENT_TIMESTAMP
WHERE status = 'active';

-- 使用子查询更新
UPDATE employees 
SET manager_id = (
    SELECT emp_id 
    FROM (SELECT emp_id FROM employees WHERE position = 'CTO') AS mgr
)
WHERE department_id IN (1, 2, 3, 4);

-- 安全更新（避免全表更新）
SET SQL_SAFE_UPDATES = 1;
UPDATE employees SET salary = 15000 WHERE emp_id = 10;
```

### 3. DELETE 语句详解

```sql
-- 基本删除
DELETE FROM employees WHERE status = 'terminated';

-- 多表删除
DELETE e 
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_name = '已撤销部门';

-- 使用子查询删除
DELETE FROM employee_projects 
WHERE project_id IN (
    SELECT project_id 
    FROM projects 
    WHERE status = 'cancelled'
);

-- 删除重复记录（保留最新的）
DELETE e1 FROM employees e1
INNER JOIN employees e2 
WHERE e1.email = e2.email 
  AND e1.emp_id < e2.emp_id;

-- 清空表（比DELETE快）
TRUNCATE TABLE attendance;
```

## 数据查询语言 DQL

### 1. 基础查询

```sql
-- 查询所有数据
SELECT * FROM employees;

-- 查询指定列
SELECT name, position, salary FROM employees;

-- 使用别名
SELECT 
    name AS '员工姓名',
    position AS '职位',
    salary AS '薪资',
    CONCAT(name, ' - ', position) AS '员工信息'
FROM employees;

-- 去重查询
SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT department_id, position FROM employees;

-- 限制结果数量
SELECT * FROM employees LIMIT 5;
SELECT * FROM employees LIMIT 5, 10;  -- 跳过5条，取10条
```

### 2. 条件查询

```sql
-- 基本WHERE条件
SELECT * FROM employees WHERE salary > 20000;
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM employees WHERE status = 'active';

-- 逻辑运算符
SELECT * FROM employees 
WHERE salary > 15000 AND department_id IN (1, 2, 3);

SELECT * FROM employees 
WHERE position LIKE '%经理%' OR salary > 25000;

SELECT * FROM employees 
WHERE NOT (status = 'terminated' OR hire_date < '2020-01-01');

-- 范围查询
SELECT * FROM employees WHERE salary BETWEEN 15000 AND 25000;
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31';

-- 模糊查询
SELECT * FROM employees WHERE name LIKE '张%';    -- 以张开头
SELECT * FROM employees WHERE name LIKE '%工程师';  -- 以工程师结尾
SELECT * FROM employees WHERE email LIKE '%@company.com';
SELECT * FROM employees WHERE phone LIKE '138%';

-- NULL值查询
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE end_date IS NOT NULL;

-- IN查询
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE position IN ('CTO', '总监', '经理');
```

### 3. 排序和分组

```sql
-- 排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id, salary DESC;
SELECT * FROM employees ORDER BY hire_date, name;

-- 分组统计
SELECT department_id, COUNT(*) as emp_count
FROM employees 
GROUP BY department_id;

SELECT 
    department_id,
    COUNT(*) as emp_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employees 
GROUP BY department_id;

-- HAVING过滤
SELECT department_id, COUNT(*) as emp_count
FROM employees 
GROUP BY department_id
HAVING COUNT(*) > 2;

-- 复杂分组
SELECT 
    YEAR(hire_date) as hire_year,
    department_id,
    COUNT(*) as emp_count
FROM employees 
GROUP BY YEAR(hire_date), department_id
ORDER BY hire_year, department_id;
```

## 复杂查询操作

### 1. 联表查询

```sql
-- 内连接（INNER JOIN）
SELECT 
    e.name,
    e.position,
    e.salary,
    d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id;

-- 左连接（LEFT JOIN）
SELECT 
    d.dept_name,
    COUNT(e.emp_id) as emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.department_id
GROUP BY d.dept_id, d.dept_name;

-- 右连接（RIGHT JOIN）
SELECT 
    e.name,
    d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.dept_id;

-- 多表连接
SELECT 
    e.name as employee_name,
    d.dept_name,
    p.project_name,
    ep.role,
    ep.allocation_percentage
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN employee_projects ep ON e.emp_id = ep.emp_id
JOIN projects p ON ep.project_id = p.project_id
WHERE e.status = 'active';

-- 自连接（查询员工及其上级）
SELECT 
    e.name as employee,
    e.position,
    m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
```

### 2. 子查询

```sql
-- 标量子查询
SELECT name, salary,
    (SELECT AVG(salary) FROM employees) as avg_salary,
    salary - (SELECT AVG(salary) FROM employees) as diff_from_avg
FROM employees;

-- 列子查询（IN/NOT IN）
SELECT * FROM employees 
WHERE department_id IN (
    SELECT dept_id FROM departments 
    WHERE dept_name LIKE '%技术%'
);

-- 行子查询
SELECT * FROM employees 
WHERE (department_id, position) IN (
    SELECT department_id, position 
    FROM employees 
    WHERE salary = (SELECT MAX(salary) FROM employees)
);

-- EXISTS子查询
SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e 
    WHERE e.department_id = d.dept_id
);

-- 相关子查询
SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id
);
```

### 3. 窗口函数

```sql
-- ROW_NUMBER() - 行号
SELECT 
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

-- RANK() 和 DENSE_RANK() - 排名
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY salary DESC) as rank_dense
FROM employees;

-- NTILE() - 分组
SELECT 
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) as salary_quartile
FROM employees;

-- LAG() 和 LEAD() - 偏移
SELECT 
    name,
    hire_date,
    LAG(hire_date, 1) OVER (ORDER BY hire_date) as prev_hire_date,
    LEAD(hire_date, 1) OVER (ORDER BY hire_date) as next_hire_date
FROM employees;

-- FIRST_VALUE() 和 LAST_VALUE()
SELECT 
    name,
    department_id,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC) as highest_paid,
    LAST_VALUE(name) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as lowest_paid
FROM employees;

-- 累计计算
SELECT 
    name,
    salary,
    SUM(salary) OVER (ORDER BY emp_id) as running_total,
    AVG(salary) OVER (ORDER BY emp_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM employees;
```

### 4. 通用表表达式 (CTE)

```sql
-- 基本CTE
WITH dept_stats AS (
    SELECT 
        department_id,
        COUNT(*) as emp_count,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.dept_name,
    ds.emp_count,
    ds.avg_salary
FROM dept_stats ds
JOIN departments d ON ds.department_id = d.dept_id;

-- 递归CTE（组织架构）
WITH RECURSIVE org_hierarchy AS (
    -- 基础查询：顶层管理者
    SELECT emp_id, name, manager_id, position, 0 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下级员工
    SELECT e.emp_id, e.name, e.manager_id, e.position, oh.level + 1
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.emp_id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) as hierarchy,
    position,
    level
FROM org_hierarchy
ORDER BY level, name;

-- 多个CTE
WITH 
high_performers AS (
    SELECT emp_id, name, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
),
project_leaders AS (
    SELECT DISTINCT emp_id
    FROM employee_projects
    WHERE role LIKE '%经理%' OR role LIKE '%负责人%'
)
SELECT hp.name, hp.salary
FROM high_performers hp
JOIN project_leaders pl ON hp.emp_id = pl.emp_id;
```

### 5. 联合查询

```sql
-- UNION（去重）
SELECT name, 'Employee' as type FROM employees
UNION
SELECT dept_name, 'Department' as type FROM departments;

-- UNION ALL（不去重）
SELECT emp_id as id, name FROM employees
UNION ALL
SELECT dept_id as id, dept_name as name FROM departments;

-- 复杂UNION查询
SELECT 
    'Active Projects' as category,
    COUNT(*) as count,
    SUM(budget) as total_budget
FROM projects 
WHERE status = 'active'

UNION ALL

SELECT 
    'Completed Projects' as category,
    COUNT(*) as count,
    SUM(budget) as total_budget
FROM projects 
WHERE status = 'completed'

UNION ALL

SELECT 
    'All Projects' as category,
    COUNT(*) as count,
    SUM(budget) as total_budget
FROM projects;
```

## 索引和约束

### 1. 索引管理

```sql
-- 查看索引
SHOW INDEX FROM employees;
SHOW INDEX FROM departments;

-- 创建索引
CREATE INDEX idx_emp_name ON employees(name);
CREATE INDEX idx_emp_salary_desc ON employees(salary DESC);
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_emp_email ON employees(email);

-- 创建全文索引（用于文本搜索）
ALTER TABLE projects ADD FULLTEXT(description);

-- 使用全文索引搜索
SELECT * FROM projects 
WHERE MATCH(description) AGAINST('系统 开发' IN NATURAL LANGUAGE MODE);

-- 删除索引
DROP INDEX idx_emp_name ON employees;
ALTER TABLE employees DROP INDEX idx_emp_salary_desc;

-- 分析索引使用情况
EXPLAIN SELECT * FROM employees WHERE name = '张总';
EXPLAIN SELECT * FROM employees WHERE salary > 20000 AND department_id = 1;
```

### 2. 约束管理

```sql
-- 主键约束
ALTER TABLE employees ADD PRIMARY KEY (emp_id);
ALTER TABLE employees DROP PRIMARY KEY;

-- 外键约束
ALTER TABLE employees 
ADD CONSTRAINT fk_emp_dept 
FOREIGN KEY (department_id) REFERENCES departments(dept_id)
ON UPDATE CASCADE ON DELETE SET NULL;

-- 唯一约束
ALTER TABLE employees ADD CONSTRAINT uk_emp_code UNIQUE (employee_code);
ALTER TABLE employees ADD CONSTRAINT uk_emp_email UNIQUE (email);

-- 检查约束（MySQL 8.0.16+）
ALTER TABLE employees 
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

ALTER TABLE employees 
ADD CONSTRAINT chk_hire_date CHECK (hire_date <= CURDATE());

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT fk_emp_dept;
ALTER TABLE employees DROP CONSTRAINT uk_emp_code;

-- 查看约束
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'company_db';
```

## 存储过程和函数

### 1. 存储过程

```sql
-- 创建简单存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
    SELECT 
        name,
        position,
        salary,
        hire_date
    FROM employees 
    WHERE department_id = dept_id 
      AND status = 'active'
    ORDER BY salary DESC;
END //
DELIMITER ;

-- 调用存储过程
CALL GetEmployeesByDept(1);

-- 复杂存储过程（带输出参数）
DELIMITER //
CREATE PROCEDURE GetDeptStatistics(
    IN dept_id INT,
    OUT emp_count INT,
    OUT avg_salary DECIMAL(10,2),
    OUT total_salary DECIMAL(12,2)
)
BEGIN
    SELECT 
        COUNT(*),
        ROUND(AVG(salary), 2),
        SUM(salary)
    INTO emp_count, avg_salary, total_salary
    FROM employees 
    WHERE department_id = dept_id AND status = 'active';
END //
DELIMITER ;

-- 调用带输出参数的存储过程
CALL GetDeptStatistics(1, @count, @avg, @total);
SELECT @count as employee_count, @avg as average_salary, @total as total_salary;

-- 复杂业务逻辑存储过程
DELIMITER //
CREATE PROCEDURE CalculateBonus(IN year INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE project_count INT;
    DECLARE bonus_amount DECIMAL(8,2);
    
    -- 声明游标
    DECLARE emp_cursor CURSOR FOR 
        SELECT e.emp_id, e.salary, COUNT(ep.project_id)
        FROM employees e
        LEFT JOIN employee_projects ep ON e.emp_id = ep.emp_id
        WHERE e.status = 'active'
        GROUP BY e.emp_id, e.salary;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_bonus (
        emp_id INT,
        bonus_amount DECIMAL(8,2)
    );
    
    OPEN emp_cursor;
    
    bonus_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_salary, project_count;
        IF done THEN
            LEAVE bonus_loop;
        END IF;
        
        -- 计算奖金逻辑
        SET bonus_amount = emp_salary * 0.1; -- 基础奖金10%
        
        IF project_count > 3 THEN
            SET bonus_amount = bonus_amount * 1.5; -- 项目多奖励50%
        ELSEIF project_count > 1 THEN
            SET bonus_amount = bonus_amount * 1.2; -- 项目适中奖励20%
        END IF;
        
        INSERT INTO temp_bonus VALUES (emp_id, bonus_amount);
    END LOOP;
    
    CLOSE emp_cursor;
    
    -- 返回结果
    SELECT 
        e.name,
        e.position,
        e.salary,
        tb.bonus_amount
    FROM temp_bonus tb
    JOIN employees e ON tb.emp_id = e.emp_id
    ORDER BY tb.bonus_amount DESC;
    
    DROP TEMPORARY TABLE temp_bonus;
END //
DELIMITER ;

-- 调用复杂存储过程
CALL CalculateBonus(2024);
```

### 2. 函数

```sql
-- 创建函数
DELIMITER //
CREATE FUNCTION GetEmployeeLevel(salary DECIMAL(10,2)) 
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE level_name VARCHAR(20);
    
    CASE 
        WHEN salary >= 30000 THEN SET level_name = '高级';
        WHEN salary >= 20000 THEN SET level_name = '中级';
        WHEN salary >= 15000 THEN SET level_name = '初级';
        ELSE SET level_name = '实习';
    END CASE;
    
    RETURN level_name;
END //
DELIMITER ;

-- 使用函数
SELECT 
    name,
    salary,
    GetEmployeeLevel(salary) as employee_level
FROM employees;

-- 复杂函数示例
DELIMITER //
CREATE FUNCTION CalculateWorkingDays(start_date DATE, end_date DATE) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE working_days INT DEFAULT 0;
    DECLARE current_date DATE;
    DECLARE day_of_week INT;
    
    SET current_date = start_date;
    
    WHILE current_date <= end_date DO
        SET day_of_week = DAYOFWEEK(current_date);
        -- 周一到周五为工作日（2-6）
        IF day_of_week BETWEEN 2 AND 6 THEN
            SET working_days = working_days + 1;
        END IF;
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END WHILE;
    
    RETURN working_days;
END //
DELIMITER ;

-- 使用复杂函数
SELECT 
    project_name,
    start_date,
    end_date,
    CalculateWorkingDays(start_date, IFNULL(end_date, CURDATE())) as working_days
FROM projects;

-- 删除函数和存储过程
DROP FUNCTION IF EXISTS GetEmployeeLevel;
DROP PROCEDURE IF EXISTS GetEmployeesByDept;

-- 查看存储过程和函数
SHOW PROCEDURE STATUS WHERE Db = 'company_db';
SHOW FUNCTION STATUS WHERE Db = 'company_db';
```

## 实际项目案例

### 案例1：员工绩效分析系统

```sql
-- 创建员工绩效评估视图
CREATE VIEW employee_performance_view AS
SELECT 
    e.emp_id,
    e.name,
    e.position,
    d.dept_name,
    e.salary,
    -- 项目参与情况
    COUNT(DISTINCT ep.project_id) as project_count,
    -- 工作时长统计
    AVG(a.work_hours) as avg_daily_hours,
    SUM(a.overtime_hours) as total_overtime,
    -- 出勤率
    COUNT(CASE WHEN a.status = 'present' THEN 1 END) / COUNT(a.work_date) * 100 as attendance_rate,
    -- 薪资水平
    e.salary / AVG(e2.salary) * 100 as salary_percentile
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
LEFT JOIN employee_projects ep ON e.emp_id = ep.emp_id
LEFT JOIN attendance a ON e.emp_id = a.emp_id 
LEFT JOIN employees e2 ON e.department_id = e2.department_id
WHERE e.status = 'active'
GROUP BY e.emp_id, e.name, e.position, d.dept_name, e.salary;

-- 查询高绩效员工
SELECT * FROM employee_performance_view
WHERE project_count >= 2 
  AND avg_daily_hours >= 8
  AND attendance_rate >= 95
  AND salary_percentile >= 110
ORDER BY salary_percentile DESC;
```

### 案例2：项目成本分析

```sql
-- 项目成本分析查询
SELECT 
    p.project_name,
    p.budget,
    p.actual_cost,
    -- 人力成本计算
    SUM(
        ep.hourly_rate * 
        DATEDIFF(
            IFNULL(ep.end_date, IFNULL(p.end_date, CURDATE())), 
            ep.start_date
        ) * 8 * -- 假设每天8小时
        (ep.allocation_percentage / 100)
    ) as estimated_labor_cost,
    -- 预算使用率
    p.actual_cost / p.budget * 100 as budget_utilization,
    -- 项目进度（简化计算）
    CASE 
        WHEN p.status = 'completed' THEN 100
        WHEN p.status = 'active' THEN 
            DATEDIFF(CURDATE(), p.start_date) / 
            DATEDIFF(IFNULL(p.end_date, CURDATE()), p.start_date) * 100
        ELSE 0
    END as progress_percentage
FROM projects p
LEFT JOIN employee_projects ep ON p.project_id = ep.project_id
GROUP BY p.project_id, p.project_name, p.budget, p.actual_cost, p.status, p.start_date, p.end_date
HAVING estimated_labor_cost > 0;
```

### 案例3：部门效率分析

```sql
-- 部门效率综合分析
WITH dept_metrics AS (
    SELECT 
        d.dept_id,
        d.dept_name,
        d.budget,
        COUNT(DISTINCT e.emp_id) as emp_count,
        AVG(e.salary) as avg_salary,
        SUM(e.salary) as total_salary,
        COUNT(DISTINCT ep.project_id) as project_count,
        AVG(a.work_hours) as avg_work_hours,
        AVG(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) * 100 as attendance_rate
    FROM departments d
    LEFT JOIN employees e ON d.dept_id = e.department_id AND e.status = 'active'
    LEFT JOIN employee_projects ep ON e.emp_id = ep.emp_id
    LEFT JOIN attendance a ON e.emp_id = a.emp_id
    GROUP BY d.dept_id, d.dept_name, d.budget
),
dept_rankings AS (
    SELECT *,
        RANK() OVER (ORDER BY project_count DESC) as project_rank,
        RANK() OVER (ORDER BY attendance_rate DESC) as attendance_rank,
        RANK() OVER (ORDER BY avg_work_hours DESC) as productivity_rank
    FROM dept_metrics
    WHERE emp_count > 0
)
SELECT 
    dept_name,
    emp_count,
    project_count,
    ROUND(avg_salary, 2) as avg_salary,
    ROUND(attendance_rate, 2) as attendance_rate,
    ROUND(avg_work_hours, 2) as avg_work_hours,
    project_rank,
    attendance_rank,
    productivity_rank,
    -- 综合效率评分
    ROUND((project_rank + attendance_rank + productivity_rank) / 3, 2) as efficiency_score
FROM dept_rankings
ORDER BY efficiency_score;
```

## 性能优化和最佳实践

### 1. 查询优化

```sql
-- 使用EXPLAIN分析查询计划
EXPLAIN FORMAT=JSON
SELECT e.name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN employee_projects ep ON e.emp_id = ep.emp_id
JOIN projects p ON ep.project_id = p.project_id
WHERE e.salary > 20000;

-- 优化前：全表扫描
SELECT * FROM employees WHERE YEAR(hire_date) = 2024;

-- 优化后：使用索引友好的条件
SELECT * FROM employees 
WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';

-- 优化前：函数使用导致无法使用索引
SELECT * FROM employees WHERE UPPER(name) = 'ZHANG';

-- 优化后：避免在WHERE中使用函数
SELECT * FROM employees WHERE name = 'zhang' COLLATE utf8mb4_general_ci;
```

### 2. 索引优化策略

```sql
-- 创建复合索引时考虑最左前缀原则
CREATE INDEX idx_emp_dept_salary_hire ON employees(department_id, salary, hire_date);

-- 以下查询可以使用上述索引
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM employees WHERE department_id = 1 AND salary > 15000;
SELECT * FROM employees WHERE department_id = 1 AND salary > 15000 AND hire_date > '2020-01-01';

-- 以下查询无法使用上述索引
SELECT * FROM employees WHERE salary > 15000;  -- 跳过了department_id
SELECT * FROM employees WHERE hire_date > '2020-01-01';  -- 跳过了department_id和salary

-- 覆盖索引优化
CREATE INDEX idx_emp_covering ON employees(department_id, name, salary);

-- 该查询只需要访问索引，不需要回表
SELECT name, salary FROM employees WHERE department_id = 1;
```

### 3. 分区表优化

```sql
-- 按日期分区的考勤表
CREATE TABLE attendance_partitioned (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    work_date DATE NOT NULL,
    check_in_time TIME,
    check_out_time TIME,
    work_hours DECIMAL(4,2),
    status ENUM('present', 'absent', 'late', 'early_leave', 'holiday') DEFAULT 'present',
    INDEX idx_emp_date (emp_id, work_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(work_date)) (
    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
);

-- 查询时自动使用分区剪枝
SELECT * FROM attendance_partitioned 
WHERE work_date BETWEEN '2024-01-01' AND '2024-12-31';
```

### 4. 查询缓存和性能监控

```sql
-- 查看慢查询
SELECT 
    sql_text,
    exec_count,
    avg_timer_wait/1000000000000 as avg_time_sec,
    sum_rows_examined/exec_count as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest 
WHERE avg_timer_wait > 1000000000000  -- 超过1秒的查询
ORDER BY avg_timer_wait DESC 
LIMIT 10;

-- 查看表的访问统计
SELECT 
    object_schema,
    object_name,
    count_star as total_queries,
    sum_timer_wait/1000000000000 as total_time_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'company_db'
ORDER BY sum_timer_wait DESC;

-- 查看索引使用情况
SELECT 
    object_schema,
    object_name,
    index_name,
    count_star as usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'company_db'
ORDER BY count_star DESC;
```

### 5. 最佳实践总结

```sql
-- ✅ 良好的SQL编写习惯

-- 1. 明确指定需要的列，避免SELECT *
SELECT emp_id, name, salary FROM employees WHERE department_id = 1;

-- 2. 合理使用索引
CREATE INDEX idx_emp_dept_status ON employees(department_id, status);
SELECT * FROM employees WHERE department_id = 1 AND status = 'active';

-- 3. 避免隐式类型转换
SELECT * FROM employees WHERE emp_id = 123;  -- 而不是 emp_id = '123'

-- 4. 使用LIMIT限制结果集
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 20;

-- 5. 合理使用JOIN替代子查询
-- 推荐
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;

-- 而不是
SELECT name, (SELECT dept_name FROM departments WHERE dept_id = e.department_id)
FROM employees e;

-- 6. 批量操作优化
-- 批量插入
INSERT INTO attendance (emp_id, work_date, work_hours) VALUES
(1, '2024-01-01', 8.0),
(1, '2024-01-02', 8.5),
(1, '2024-01-03', 8.0);

-- 批量更新
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id IN (1, 2, 3);

-- 7. 事务使用
START TRANSACTION;
INSERT INTO projects (project_name, start_date) VALUES ('新项目', '2024-01-01');
SET @project_id = LAST_INSERT_ID();
INSERT INTO employee_projects (emp_id, project_id, role) VALUES (1, @project_id, '项目经理');
COMMIT;
```

---

**总结：**

本文档涵盖了MySQL常用SQL语法的完整使用指南，包括：

1. **DDL操作**：数据库和表的创建、修改、删除
2. **DML操作**：数据的增删改查操作
3. **复杂查询**：联表查询、子查询、窗口函数、CTE
4. **索引约束**：性能优化的关键技术
5. **存储过程**：复杂业务逻辑的封装
6. **实际案例**：真实业务场景的应用
7. **性能优化**：查询优化和最佳实践

通过系统学习和实践这些内容，可以掌握MySQL数据库开发和优化的核心技能。