多表查询案例

CREATE TABLE salgrade(
    grade  INT,   -- 等级
    losal  INT,   -- 最低薪资
    hisal  INT    -- 最高薪资
) COMMENT '薪资等级表';

INSERT INTO salgrade VALUES
(1, 0, 3000), (2, 3001, 5000), (3, 5001, 8000), (4, 8001, 10000),
(5, 10001, 15000), (6, 15001, 20000), (7, 20001, 25000), (8, 25001, 30000);

-- 1. 员工 + 部门（隐式内连接）
SELECT e.name, e.age, e.job, d.name FROM emp e, dept d WHERE e.dept_id = d.id;

-- 2. 年龄小于 30 岁的员工 + 部门（显式内连接）
SELECT e.name, e.age, e.job, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.age < 30;

-- 3. 有员工的部门（DISTINCT 去重）
SELECT DISTINCT d.id, d.name FROM dept d JOIN emp e ON d.id = e.dept_id;

-- 4. 年龄大于 40 的员工 + 部门，没有部门的也要展示（左外连接）
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40;

-- 5. 所有员工的工资等级
SELECT e.*, s.grade FROM emp e LEFT JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal;

-- 6. 研发部员工的信息及工资等级
SELECT e.*, s.grade FROM emp e
    LEFT JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
    WHERE e.dept_id = (SELECT id FROM dept WHERE name = '研发部');

-- 7. 研发部员工的平均工资
SELECT AVG(e.salary) FROM emp e
    WHERE e.dept_id = (SELECT id FROM dept WHERE name = '研发部');

-- 8. 工资比"灭绝"高的员工（标量子查询）
SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '灭绝');

-- 9. 工资高于全公司平均工资的员工
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

-- 10. 低于本部门平均工资的员工（相关子查询：内外层用同一个 dept_id）
SELECT * FROM emp e1
    WHERE e1.salary < (SELECT AVG(e2.salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id);

-- 11. 所有部门及其员工人数（SELECT 之后的子查询）
SELECT d.*, (SELECT COUNT(*) FROM emp WHERE dept_id = d.id) '人数' FROM dept d;

-- 12. 学生选课情况（三表联查：学生、中间表、课程）
SELECT s.name, s.no, c.name FROM student s, student_course sc, course c
    WHERE s.id = sc.studentid AND sc.courseid = c.id;