SQL数据查询语言-(高级)-DQL

多表查询操作

内连接查询

隐式内连接

语法格式：

1
2
3
SELECT 查询字段列表
FROM 表名1, 表名2
WHERE 表名1.字段 = 表名2.字段;

使用场景：

  • 适用于简单的两表关联查询
  • 当关联条件明确且简单时使用

示例：

1
2
3
4
-- 查询员工及其部门信息
SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id;

注意事项：

  • 当表较多时，WHERE 条件会变得复杂
  • 性能可能不如显式连接

显式内连接

语法格式：

1
2
3
4
SELECT 查询字段列表
FROM 表名1
[INNER] JOIN 表名2
ON 表名1.字段 = 表名2.字段;

外连接查询

左外连接

语法格式：

1
2
3
4
SELECT 字段列表
FROM 表名1
LEFT [OUTER] JOIN 表名2
ON 表名1.字段 = 表名2.字段;

特点：

  • 返回左表所有记录，即使右表没有匹配
  • 右表无匹配时显示 NULL

示例：

1
2
3
4
-- 查询所有部门及员工(包括没有员工的部门)
SELECT d.department_name, e.name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id;

图示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
左表 (A)                右表 (B)
+----+------+           +----+------+
| id | name |           | id | name |
+----+------+           +----+------+
|  1 | 部门1 |           |  1 | 张三 |
|  2 | 部门2 |           |  2 | 李四 |
|  3 | 部门3 |           |  4 | 王五 |
+----+------+           +----+------+

结果：
+------+------+
| 部门 | 员工 |
+------+------+
| 部门1 | 张三 |
| 部门2 | 李四 |
| 部门3 | NULL |
+------+------+

右外连接

语法格式：

1
2
3
4
SELECT 字段列表
FROM 表名1
RIGHT [OUTER] JOIN 表名2
ON 表名1.字段 = 表名2.字段;

特点：

  • 返回右表所有记录，即使左表没有匹配
  • 左表无匹配时显示 NULL

示例：

1
2
3
4
-- 查询所有员工及部门(包括没有部门的员工)
SELECT e.name, d.department_name
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id;

图示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
左表 (A)                右表 (B)
+----+------+           +----+------+
| id | name |           | id | name |
+----+------+           +----+------+
|  1 | 部门1 |           |  1 | 张三 |
|  2 | 部门2 |           |  2 | 李四 |
|  3 | 部门3 |           |  4 | 王五 |
+----+------+           +----+------+

结果：
+------+------+
| 员工 | 部门 |
+------+------+
| 张三 | 部门1 |
| 李四 | 部门2 |
| 王五 | NULL  |
+------+------+

子查询

基本概念

子查询是指一个查询语句嵌套在另一个查询语句内部的查询。

基本形式：

1
2
3
SELECT 字段列表
FROM 表名
WHERE 字段名 运算符 (SELECT 字段列表 FROM 表名);

应用场景：

  • 可用于 INSERT / UPDATE / DELETE / SELECT 语句中
  • 最常见于 SELECT 语句

子查询分类

  1. 标量子查询

    • 返回单个值
    • 示例：
      1
      2
      3
      4
      -- 查询工资高于平均工资的员工
      SELECT name, salary
      FROM employees
      WHERE salary > (SELECT AVG(salary) FROM employees);

  2. 列子查询

    • 返回一列值
    • 示例：
      1
      2
      3
      4
      5
      -- 查询在开发部的员工
      SELECT name
      FROM employees
      WHERE department_id IN
        (SELECT id FROM departments WHERE department_name = '开发部');

  3. 行子查询

    • 返回一行值
    • 示例：
      1
      2
      3
      4
      5
      -- 查询与张三工资和部门相同的员工
      SELECT name
      FROM employees
      WHERE (salary, department_id) =
        (SELECT salary, department_id FROM employees WHERE name = '张三');

  4. 表子查询

    • 返回一个结果集
    • 示例：
      1
      2
      3
      -- 从临时表中查询数据
      SELECT *
      FROM (SELECT id, name FROM employees WHERE salary > 10000) AS high_salary_emp;

子查询的位置

  1. WHERE 或 HAVING 后面

    • 通常使用标量子查询
    • 示例：
      1
      2
      3
      4
      SELECT department_id, AVG(salary)
      FROM employees
      GROUP BY department_id
      HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

  2. FROM 后面

    • 使用表子查询
    • 必须指定别名
    • 示例：
      1
      2
      3
      4
      5
      6
      SELECT d.department_name, emp_count.count
      FROM departments d
      JOIN (SELECT department_id, COUNT(*) AS count
            FROM employees
            GROUP BY department_id) emp_count
      ON d.id = emp_count.department_id;

  3. SELECT 后面

    • 通常使用标量子查询
    • 示例：
      1
      2
      3
      SELECT name, salary,
        (SELECT AVG(salary) FROM employees) AS avg_salary
      FROM employees;

子查询的优缺点

优点：

  • 逻辑清晰，易于理解
  • 可分解复杂查询为多个简单查询

缺点：

  • 性能可能较低，尤其是嵌套多层子查询时
  • 某些场景下可用 JOIN 替代以提高效率