前端码农

Appearance

关键知识点 ​

SQL 类型 ​

类型核心目标关键语句操作影响范围是否支持回滚
DQL查数据SELECT仅返回数据，无修改-
DML改数据（增删改）INSERT/UPDATE/DELETE表中数据内容是（事务内）
DDL改结构CREATE/ALTER/DROP/TRUNCATE表/库/索引结构否
DCL控权限GRANT/REVOKE/CREATE USER用户权限否
TCL管事务COMMIT/ROLLBACK事务内的 DML 操作是

MySQL 数值类型 ​

整数类型 ​

类型字节数有符号范围无符号范围描述
TINYINT1-128 到 1270 到 255小整数
SMALLINT2-32768 到 327670 到 65535中等整数
MEDIUMINT3-8388608 到 83886080 到 16777215中等整数
INT4-2147483648 到 21474836470 到 4294967295标准整数
BIGINT8-2^63 到 2^63-10 到 2^64-1大整数

浮点类型 ​

类型字节数描述精度
FLOAT4单精度浮点数约 7 位小数
DOUBLE8双精度浮点数约 15 位小数
DECIMAL(M,D)变长精确小数M 位总数，D 位小数

日期和时间类型 ​

类型格式范围字节数描述
DATEYYYY-MM-DD1000-01-01 到 9999-12-313日期
TIMEHH:MM:SS-838:59:59 到 838:59:593时间
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 到 9999-12-31 23:59:598日期时间
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 到 2038-01-19 03:14:074时间戳
YEARYYYY1901 到 21551年份

文本字符串类型 ​

类型最大长度描述存储需求
CHAR(n)255 字符定长字符串n 字节
VARCHAR(n)65535 字符变长字符串实际长度 + 1/2 字节
TINYTEXT255 字符短文本实际长度 + 1 字节
TEXT65535 字符文本实际长度 + 2 字节
MEDIUMTEXT16,777,215 字符中等文本实际长度 + 3 字节
LONGTEXT4,294,967,295 字符长文本实际长度 + 4 字节

二进制字符串类型 ​

类型最大长度描述用途
BINARY(n)255 字节定长二进制存储二进制数据
VARBINARY(n)65535 字节变长二进制存储二进制数据
TINYBLOB255 字节短二进制小文件、图片
BLOB64KB二进制大对象图片、文档
MEDIUMBLOB16MB中等二进制较大文件
LONGBLOB4GB长二进制大文件

字符集和排序规则 ​

sql
-- 查看字符集支持
SHOW CHARACTER SET;

-- 创建表时指定字符集
CREATE TABLE charset_demo (
    content VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 推荐使用 utf8mb4 支持所有 Unicode 字符（包括表情符号）

DQL 语句 ​

分支条件查询case ​

sql
select name,
       salary,
       case
           when salary > 30000 then '高'
           when salary > 20000 then '中'
           else '低' end
           as "salary_level"
from user;

IFNULL() 函数 ​

  • IFNULL(expr1,expr2) 函数用于判断 expr1 是否为 NULL，如果 expr1 不为 NULL，则返回 expr1，否则返回 expr2。
sql
SELECT empno,ename,sal*12+IFNULL(comm,0) FROM emp;

聚合函数 ​

  • 聚合函数对一组值执行计算并返回单个值，通常与 GROUP BY 子句一起使用，用于数据统计和分析。
聚合函数说明
SUM()求所有行中单列结果的总和
AVG()平均值
MAX()最大值
MIN()最小值 null 当做 0
COUNT()求总数 null 不计数

示例数据准备 ​

sql
-- 创建示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    bonus DECIMAL(8,2)
);

INSERT INTO employees (name, department, salary, hire_date, bonus) VALUES
('张三', '技术部', 15000.00, '2020-01-15', 5000.00),
('李四', '技术部', 12000.00, '2021-03-20', 3000.00),
('王五', '销售部', 8000.00, '2019-11-10', 8000.00),
('赵六', '销售部', 9000.00, '2020-06-25', 12000.00),
('钱七', '人事部', 10000.00, '2022-02-18', 2000.00),
('孙八', '技术部', 18000.00, '2018-09-05', 6000.00),
('周九', '销售部', 8500.00, '2021-12-30', 9000.00);

COUNT() 函数 ​

sql
-- 统计所有员工数量
SELECT COUNT(*) AS total_employees FROM employees;

-- 统计有奖金的员工数量（忽略NULL）
SELECT COUNT(bonus) AS employees_with_bonus FROM employees;

-- 统计不同部门的数量
SELECT COUNT(DISTINCT department) AS department_count FROM employees;

-- 多个COUNT使用
SELECT
    COUNT(*) AS total_count,
    COUNT(bonus) AS bonus_count,
    COUNT(DISTINCT department) AS dept_count
FROM employees;

SUM() 函数 ​

sql
-- 计算总薪资
SELECT SUM(salary) AS total_salary FROM employees;

-- 计算每个部门的总薪资
SELECT department, SUM(salary) AS dept_total_salary
FROM employees
GROUP BY department;

-- 计算总薪资和总奖金
SELECT
    SUM(salary) AS total_salary,
    SUM(bonus) AS total_bonus,
    SUM(salary + bonus) AS total_income
FROM employees;

AVG() 函数 ​

sql
-- 计算平均薪资
SELECT AVG(salary) AS average_salary FROM employees;

-- 计算每个部门的平均薪资
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- 保留两位小数
SELECT
    department,
    ROUND(AVG(salary), 2) AS avg_salary,
    ROUND(AVG(bonus), 2) AS avg_bonus
FROM employees
GROUP BY department;

MAX() / MIN() 函数 ​

sql
-- 最高和最低薪资
SELECT
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

-- 每个部门的最高和最低薪资
SELECT
    department,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY department;

-- 最早和最晚入职日期
SELECT
    MIN(hire_date) AS first_hire,
    MAX(hire_date) AS last_hire
FROM employees;

分组查询（GROUP BY） ​

select 列名列表

from 表名

where 条件

group by 列名(分组)

having 条件(分组后)

order by 列名(排序) [asc|desc 降序]

limit n,m

创建测试表和测试数据 ​

sql
-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees (name, department, position, salary, hire_date) VALUES
('张三', '技术部', '工程师', 15000.00, '2022-01-15'),
('李四', '技术部', '高级工程师', 20000.00, '2021-03-20'),
('王五', '技术部', '工程师', 16000.00, '2022-06-10'),
('赵六', '销售部', '销售经理', 18000.00, '2020-11-05'),
('钱七', '销售部', '销售代表', 12000.00, '2023-02-18'),
('孙八', '人事部', '人事经理', 17000.00, '2019-08-12'),
('周九', '人事部', '招聘专员', 10000.00, '2023-01-08'),
('吴十', '技术部', '架构师', 25000.00, '2018-05-30');

1. 按部门分组，统计每个部门的人数 ​

sql
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

2. 按部门分组，计算平均薪资 ​

sql
SELECT department,
       AVG(salary) as avg_salary,
       ROUND(AVG(salary), 2) as avg_salary_rounded
FROM employees
GROUP BY department;

3. 按部门和职位分组统计 ​

sql
SELECT department, position, COUNT(*) as count
FROM employees
GROUP BY department, position;

4. 统计每个部门的薪资情况 ​

sql
SELECT department,
       COUNT(*) as employee_count,
       SUM(salary) as total_salary,
       AVG(salary) as avg_salary,
       MAX(salary) as max_salary,
       MIN(salary) as min_salary
FROM employees
GROUP BY department;

5. 查询员工数量大于 2 的部门（HAVING） ​

sql
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

6. 查询平均薪资超过 15000 的部门 ​

sql
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 15000;

7. 复杂条件：人数大于 1 且平均薪资大于 14000 的部门 ​

sql
SELECT department,
       COUNT(*) as employee_count,
       AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 1 AND AVG(salary) > 14000;

8. 按平均薪资降序排列部门 ​

sql
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

9. 按部门人数和平均薪资综合排序 ​

sql
SELECT department,
       COUNT(*) as employee_count,
       AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY employee_count DESC, avg_salary DESC;

10. WHERE 和 HAVING 结合使用 ​

sql
-- 先过滤薪资大于12000的员工，再按部门分组，最后过滤人数大于1的部门
SELECT department,
       COUNT(*) as employee_count,
       AVG(salary) as avg_salary
FROM employees
WHERE salary > 12000  -- 先筛选行
GROUP BY department
HAVING COUNT(*) > 1;  -- 再筛选分组

11. 按入职年份分组统计 ​

sql
SELECT YEAR(hire_date) as hire_year,
       COUNT(*) as employee_count,
       AVG(salary) as avg_salary
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

约束 ​

主键约束 (PRIMARY KEY) ​

特点：唯一标识表中的一行数据，不可重复，不能为 NULL

sql
-- 创建表时定义主键
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 复合主键
CREATE TABLE course_selection (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

-- 修改表添加主键
ALTER TABLE students ADD PRIMARY KEY (id);

唯一约束 (UNIQUE) ​

特点：唯一标识表中的一行数据，不可重复，可以为 NULL

sql
-- 创建表时定义唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 修改表添加唯一约束
ALTER TABLE users ADD UNIQUE (username);

自动增长列 (AUTO_INCREMENT) ​

特点：自动增长，只能为整型类型设置，每次增加 1

sql
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

非空约束 (NOT NULL) ​

特点：设置非空约束的列，不允许为 NULL

sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL
);

默认值约束 (DEFAULT) ​

特点：设置默认值，当插入数据未提供值时使用默认值

sql
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2) DEFAULT 0.00
);

检查约束 (CHECK) ​

特点：设置检查约束，确保列值满足特定条件

sql
-- MySQL 8.0.16+ 支持
CREATE TABLE teachers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) UNIQUE,
    age INT DEFAULT 18,
    gender CHAR(2),
    CHECK(gender IN ('男','女'))
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65)
);

事务 ​

事务（Transaction）是一个操作序列，这些操作被视为一个完整的单元。事务是数据库管理系统执行过程中的一个逻辑工作单位，它具有 ACID 属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

事务基本操作(三个) ​

开启事务:start transaction | begin

提交事务:commit

回滚事务:rollback

事务四大特性(ACID) ​

表示一个事务内的所有操作是一个整体，要么全部成功(提交事务)，要么全部失败(回滚事务)

表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

持久性事务完成之后，它对于系统的影响是永久性的。

事务隔离级别(事务并发) ​

SELECT @@Transaction_ISOLATION; -- mysql 中查看事务隔离级别(默认是 4)

  • READ UNCOMMITTED**(读未提交:1)**: 赃读,不可重复读、虚读(幻读)都有可能发生。

  • READ COMMITTED**(读已提交:2)**: 避免赃读,可能出现不可重复读、虚读(幻读)。（oracle 默认的）

  • REPEATABLE READ**(可重复读:4)**:避免赃读、重复读。虚读(幻读)有可能发生。（mysql 默认）

  • SERIALIZABLE**(串行化:8)**: 避免赃读、不可重复读、虚读(幻读)。

事务隔离级别越高,效率越低

所以:一般使用 read committed(2)和 repeatable read(4)较多

多表查询 ​

多表查询是SQL中最重要的功能之一，用于从多个表中获取相关数据。主要包括连接查询（JOIN）和子查询两大类。

表关系类型 ​

关系类型描述实现方式示例
一对一一个表中的记录对应另一个表中的一条记录共享主键或唯一外键用户 ↔ 身份证
一对多一个表中的记录对应另一个表中的多条记录在多的一方添加外键部门 ↔ 员工
多对多一个表中的记录对应另一个表中的多条记录，反之亦然通过中间表关联学生 ↔ 课程

连接查询（JOIN） ​

1. 内连接（INNER JOIN） ​

特点：只返回两个表中都有匹配记录的行（交集）

sql
-- 显式内连接
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 隐式内连接（不推荐，但需要了解）
SELECT e.name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

2. 左外连接（LEFT JOIN） ​

特点：返回左表的所有记录，右表没有匹配的用NULL填充

sql
-- 查询所有员工及其部门信息（包括没有部门的员工）
SELECT e.name, e.salary, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

3. 右外连接（RIGHT JOIN） ​

特点：返回右表的所有记录，左表没有匹配的用NULL填充

sql
-- 查询所有部门及其员工信息（包括没有员工的部门）
SELECT e.name, e.salary, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

4. 全外连接（FULL OUTER JOIN） ​

特点：返回两个表的所有记录，没有匹配的用NULL填充

sql
-- MySQL不支持FULL OUTER JOIN，可以用UNION实现
SELECT e.name, e.salary, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT e.name, e.salary, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.dept_id IS NULL;

5. 自连接（SELF JOIN） ​

特点：同一个表与自己进行连接

sql
-- 查询员工及其经理信息
SELECT 
    e.name AS 员工姓名,
    m.name AS 经理姓名
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 查询同一部门的员工
SELECT 
    e1.name AS 员工1,
    e2.name AS 员工2,
    e1.department
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department
WHERE e1.id < e2.id;  -- 避免重复和自匹配

子查询 ​

1. 标量子查询 ​

特点：返回单个值的子查询

sql
-- 查询工资高于平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询每个部门中工资最高的员工
SELECT name, salary, department
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

2. 列子查询 ​

特点：返回一列数据的子查询

sql
-- 查询在技术部或销售部工作的员工
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE department_name IN ('技术部', '销售部')
);

-- 查询工资不是部门最高工资的员工
SELECT name, salary, department
FROM employees
WHERE salary NOT IN (
    SELECT MAX(salary)
    FROM employees
    GROUP BY department
);

3. 行子查询 ​

特点：返回一行数据的子查询

sql
-- 查询与张三同部门且同职位的员工
SELECT name, department, position
FROM employees
WHERE (department, position) = (
    SELECT department, position
    FROM employees
    WHERE name = '张三'
);

4. 表子查询 ​

特点：返回多行多列的子查询，通常作为临时表使用

sql
-- 查询每个部门工资排名前2的员工
SELECT name, salary, department, rank_num
FROM (
    SELECT name, salary, department,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_num
    FROM employees
) ranked_employees
WHERE rank_num <= 2;

EXISTS 和 NOT EXISTS ​

sql
-- 查询有员工的部门
SELECT department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.id
);

-- 查询没有员工的部门
SELECT department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.id
);

多表查询示例 ​

创建测试数据 ​

sql
-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2),
    dept_id INT,
    manager_id INT,
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 创建项目表
CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12,2)
);

-- 创建员工项目关联表（多对多关系）
CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50),
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

-- 插入测试数据
INSERT INTO departments (department_name, location) VALUES
('技术部', '北京'),
('销售部', '上海'),
('人事部', '广州'),
('财务部', '深圳');

INSERT INTO employees (name, salary, dept_id, manager_id, hire_date) VALUES
('张三', 15000.00, 1, NULL, '2020-01-15'),
('李四', 20000.00, 1, 1, '2019-03-20'),
('王五', 12000.00, 2, NULL, '2021-06-10'),
('赵六', 18000.00, 2, 3, '2020-11-05'),
('钱七', 10000.00, 3, NULL, '2022-02-18'),
('孙八', 25000.00, 1, 1, '2018-05-30');

INSERT INTO projects (project_name, start_date, end_date, budget) VALUES
('电商平台', '2023-01-01', '2023-12-31', 1000000.00),
('移动应用', '2023-06-01', '2024-05-31', 800000.00),
('数据分析', '2023-03-01', '2023-09-30', 500000.00);

INSERT INTO employee_projects (employee_id, project_id, role) VALUES
(1, 1, '项目经理'),
(2, 1, '技术负责人'),
(2, 2, '架构师'),
(3, 2, '产品经理'),
(4, 3, '数据分析师'),
(5, 3, '测试工程师');

复杂多表查询示例 ​

sql
-- 1. 查询每个部门的员工数量、平均工资和总工资
SELECT 
    d.department_name,
    COUNT(e.id) AS employee_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.department_name
ORDER BY employee_count DESC;

-- 2. 查询每个员工及其部门、经理信息
SELECT 
    e.name AS 员工姓名,
    e.salary AS 工资,
    d.department_name AS 部门,
    m.name AS 经理姓名,
    m.salary AS 经理工资
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.salary DESC;

-- 3. 查询参与项目的员工及其项目信息
SELECT 
    e.name AS 员工姓名,
    d.department_name AS 部门,
    p.project_name AS 项目名称,
    ep.role AS 角色,
    p.budget AS 项目预算
FROM employees e
INNER JOIN employee_projects ep ON e.id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.id
LEFT JOIN departments d ON e.dept_id = d.id
ORDER BY p.budget DESC;

-- 4. 查询每个部门参与项目最多的员工
SELECT 
    d.department_name AS 部门,
    e.name AS 员工姓名,
    COUNT(ep.project_id) AS 参与项目数
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id
LEFT JOIN employee_projects ep ON e.id = ep.employee_id
GROUP BY d.id, d.department_name, e.id, e.name
HAVING COUNT(ep.project_id) = (
    SELECT MAX(project_count)
    FROM (
        SELECT COUNT(ep2.project_id) AS project_count
        FROM employees e2
        LEFT JOIN employee_projects ep2 ON e2.id = ep2.employee_id
        WHERE e2.dept_id = d.id
        GROUP BY e2.id
    ) max_projects
);

-- 5. 查询工资高于部门平均工资的员工
SELECT 
    e.name AS 员工姓名,
    e.salary AS 工资,
    d.department_name AS 部门,
    ROUND(dept_avg.avg_salary, 2) AS 部门平均工资
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary
ORDER BY d.department_name, e.salary DESC;

多表查询性能优化 ​

1. 索引优化 ​

sql
-- 为外键创建索引
CREATE INDEX idx_employee_dept_id ON employees(dept_id);
CREATE INDEX idx_employee_manager_id ON employees(manager_id);
CREATE INDEX idx_employee_projects_emp_id ON employee_projects(employee_id);
CREATE INDEX idx_employee_projects_proj_id ON employee_projects(project_id);

-- 为常用查询字段创建复合索引
CREATE INDEX idx_employee_dept_salary ON employees(dept_id, salary);
CREATE INDEX idx_employee_hire_date ON employees(hire_date);

2. 查询优化技巧 ​

sql
-- 使用EXISTS替代IN（当子查询返回大量数据时）
-- 不推荐
SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = '北京');

-- 推荐
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.id = e.dept_id AND d.location = '北京'
);

-- 避免SELECT *，只查询需要的字段
-- 不推荐
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 推荐
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 使用LIMIT限制结果集
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
ORDER BY e.salary DESC
LIMIT 10;

3. 执行计划分析 ​

sql
-- 使用EXPLAIN分析查询执行计划
EXPLAIN SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 15000;

-- 查看详细的执行信息
EXPLAIN FORMAT=JSON SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 15000;