数据库子查询

一、知识点

1. 内连接（INNER JOIN）

用途

获取两个表中匹配的记录，丢弃不匹配的数据。

结构

SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列 = 表2.列;

案例

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

2. 左外连接（LEFT JOIN）

用途

返回左表所有记录，右表无匹配时用 NULL 填充。

结构

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列 = 表2.列;

案例

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

3. 自连接（SELF JOIN）

用途

将表与自身连接，用于查询表内关联关系（如员工与经理）。

结构

SELECT a.列, b.列
FROM 表 a
JOIN 表 b ON a.关联列 = b.关联列;

案例

SELECT e.emp_name AS 员工, m.emp_name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

4. UNION 操作符

用途

合并多个查询结果，自动去重（使用 UNION ALL 保留重复项）。

结构

SELECT 列1 FROM 表1
UNION
SELECT 列1 FROM 表2;

案例

SELECT emp_name AS 姓名, '员工' AS 角色 FROM employees
UNION
SELECT dept_name AS 姓名, '部门' AS 角色 FROM departments;

5. 标量子查询

用途

返回单个值，用于单行计算（如平均值、计数）。

结构

SELECT (子查询) FROM 表;

案例

SELECT 
    emp_name, 
    salary,
    (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) AS 部门平均工资
FROM employees e;

6. 列子查询

用途

返回单列多行结果，常与 IN、ANY、ALL 配合使用。

结构

WHERE 列 IN (子查询);

案例

SELECT manager_id
FROM employees
WHERE manager_id IN (SELECT manager_id FROM employees GROUP BY manager_id HAVING COUNT(*) > 1);

7. 行子查询

用途

返回一行多列结果，用于多条件比较。

结构

WHERE (列1, 列2) = (子查询);

案例

SELECT emp_name
FROM employees
WHERE (dept_id, manager_id) = (SELECT dept_id, manager_id FROM employees WHERE emp_id = 102);

8. 表子查询

用途

返回多行多列结果（临时表），用于复杂过滤。

结构

FROM (子查询) AS 别名;

案例

SELECT d.dept_name, e.emp_name, e.salary
FROM employees e
JOIN (
    SELECT dept_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY dept_id
) AS max_salaries 
ON e.dept_id = max_salaries.dept_id AND e.salary = max_salaries.max_salary;

二、案例

包含内连接、外连接、自连接、UNION、标量子查询、列子查询、行子查询和表子查询的SQL案例。这个案例使用了一个简化的公司数据库模式，包含员工、部门和项目信息。

-- 创建示例表结构
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT,
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(50),
    leader_id INT,
    FOREIGN KEY (leader_id) REFERENCES employees(emp_id)
);

-- 插入示例数据
INSERT INTO departments (dept_id, dept_name) VALUES
(1, '人力资源'),
(2, '技术部'),
(3, '市场部'),
(4, '财务部');

INSERT INTO employees (emp_id, emp_name, manager_id, dept_id, salary) VALUES
(101, '张三', NULL, 2, 15000.00),
(102, '李四', 101, 2, 12000.00),
(103, '王五', 101, 2, 13000.00),
(104, '赵六', NULL, 1, 16000.00),
(105, '钱七', 104, 1, 11000.00),
(106, '孙八', NULL, 3, 14000.00),
(107, '周九', NULL, NULL, 10000.00);

INSERT INTO projects (project_id, project_name, leader_id) VALUES
(1, '网站重构', 101),
(2, '移动应用开发', 102),
(3, '数据分析平台', 103);

-- 1. 内连接：查询每个员工及其所在部门信息
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 2. 左外连接：查询所有员工及其部门，包括无部门员工
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 3. 自连接：查询员工及其经理姓名
SELECT e.emp_name AS 员工, m.emp_name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- 4. UNION：合并两个查询结果
SELECT emp_name AS 姓名, '员工' AS 角色 FROM employees
UNION
SELECT dept_name AS 姓名, '部门' AS 角色 FROM departments;

-- 5. 标量子查询：查询每个员工及其部门平均工资的比较
SELECT 
    emp_name, 
    salary,
    (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) AS 部门平均工资
FROM employees e;

-- 6. 列子查询：查询管理多个员工的经理ID
SELECT manager_id
FROM employees
WHERE manager_id IN (SELECT manager_id FROM employees GROUP BY manager_id HAVING COUNT(*) > 1);

-- 7. 行子查询：查询与特定员工具有相同部门和经理的员工
SELECT emp_name
FROM employees
WHERE (dept_id, manager_id) = (SELECT dept_id, manager_id FROM employees WHERE emp_id = 102);

-- 8. 表子查询：查询各部门最高工资员工
SELECT d.dept_name, e.emp_name, e.salary
FROM employees e
JOIN (
    SELECT dept_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY dept_id
) AS max_salaries 
ON e.dept_id = max_salaries.dept_id AND e.salary = max_salaries.max_salary
JOIN departments d ON e.dept_id = d.dept_id;  

这个SQL案例展示了不同类型的查询操作：

1. 内连接：只返回两个表中匹配的记录
2. 左外连接：返回左表所有记录，右表无匹配时显示NULL
3. 自连接：同一个表通过不同别名连接，用于层级关系查询
4. UNION：合并多个查询结果，自动去重
5. 标量子查询：返回单个值的子查询，可用于选择列表或条件
6. 列子查询：返回单列多行的子查询，通常用于IN操作符
7. 行子查询：返回一行多列的子查询，用于多条件比较
8. 表子查询：返回多行多列的子查询，可作为临时表参与连接