本文共 2694 字，大约阅读时间需要 8 分钟。

数据库连表与查询实践指南

1. 建表与数据准备

为了进行数据操作，我们首先需要建立数据库表格和插入测试数据。

表结构定义

CREATE DATABASE day4;USE day4;CREATE TABLE department (    id INT UNSIGNED NOT NULL UNIQUE COMMENT '部门ID',    name CHAR(20) COMMENT '部门名称');CREATE TABLE employee (    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',    name CHAR(12) NOT NULL COMMENT '员工姓名',    gender ENUM('male', 'female') NOT NULL DEFAULT 'male' COMMENT '性别',    age INT UNSIGNED COMMENT '年龄',    dep_id INT UNSIGNED NOT NULL COMMENT '所属部门ID');

数据插入

INSERT INTO department VALUES    (100, '技术'),    (101, '人事'),    (102, '销售'),    (103, '运营');INSERT INTO employee VALUES    ('唐建华', 'male', 18, 100),    ('黄淑珍', 'female', 48, 101),    ('李畅', 'male', 38, 101),    ('华桂珍', 'female', 28, 102),    ('钟涛', 'male', 18, 100),    ('廖婷婷', 'female', 18, 104);

2. 连表查询

2.1 不加条件的笛卡尔积

SELECT * FROM employee, department;

这种方法会生成大量冗余数据，通常不适用于实际场景。

2.2 内连表

内连表通过如下方式实现：

SELECT * FROM departmentINNER JOIN employee ON department.id = employee.dep_id;

注意事项： 会舍弃左表和右表不匹配的记录。

2.3 左外连表

SELECT * FROM departmentLEFT JOIN employee ON department.id = employee.dep_id;

注意事项： 会保留左表全部数据，舍弃右表不匹配的记录。

2.4 右外连表

SELECT * FROM departmentRIGHT JOIN employee ON department.id = employee.dep_id;

注意事项： 会保留右表全部数据，舍弃左表不匹配的记录。

2.5 全外连表

由于 MySQL 不支持全外连表，我们可以使用 UNION 关键字实现类似效果：

SELECT * FROM departmentLEFT JOIN employee ON department.id = employee.dep_idUNIONSELECT * FROM departmentRIGHT JOIN employee ON department.id = employee.dep_id;

3. 连表练习

3.1 查询人事部的员工名单

SELECT e.name AS员工名FROM department dINNER JOIN employee e ON d.id = e.dep_idWHERE d.name = '人事';

3.2 查询人事部年龄大于40岁的员工名单

SELECT e.name AS员工名FROM department dINNER JOIN employee e ON d.id = e.dep_idWHERE d.name = '人事' AND e.age > 40;

3.3 查询年龄大于20岁的员工及所在部门

SELECT e.name AS员工名, d.name AS部门名FROM department dINNER JOIN employee e ON d.id = e.dep_idWHERE e.age > 20;

3.4 查询所有部门的名字和员工人数

SELECT d.name AS部门名, COUNT(e.id) AS人数FROM department dLEFT JOIN employee e ON d.id = e.dep_idGROUP BY d.name;

3.5 查询所有部门的名字和员工人数（按人数从高到低排序）

SELECT d.name AS部门名, COUNT(e.id) AS人数FROM department dLEFT JOIN employee e ON d.id = e.dep_idGROUP BY d.nameORDER BY 人数 DESC;

4. 子查询

4.1 查询平均年龄在25岁以上的部门

SELECT d.name AS部门名FROM department dWHERE d.id IN (    SELECT dep_id    FROM employee    GROUP BY dep_id    HAVING AVG(age) > 25);

4.2 查询不足1人的部门名

SELECT name AS 部门名FROM department dWHERE d.id NOT IN (    SELECT dep_id    FROM employee    GROUP BY dep_id    HAVING COUNT(id) >= 1);

4.3 查询大于单位平均年龄的员工

SELECT name AS 姓名, age AS 年龄FROM employeeWHERE age > (    SELECT AVG(age)     FROM employee);

5. 多表查询总结

• 优先使用连表查询： 当可以使用连表查询解决问题时，选择连表查询，因为其效率通常高于子查询。
• 分层分步编写： 在编写多表查询时，建议分步进行，每一步都进行验证后再结合起来。
• 注重代码格式： 保持代码的阅读性，建议参考上述案例中的缩进和格式。

通过以上实践，可以更高效地操作多表数据库，并提升 SQL 查询性能。

转载地址：http://rvrqz.baihongyu.com/