SQL语句联表查询

文章目录
一、关联查询的分类
按年代分
按功能分
二、sql92语法的连接
语法
1. 简单应用
2. 为表起别名
3. 加入筛选
4. 加入分组
5. 三表连接
6. 非等值连接
7. 自连接
三、sql99语法的连接
连接类型分类
1. 内连接（INNER JOIN）
2. 外连接
3. 交叉连接
一、关联查询的分类
按年代分
sql92:仅仅支持内连接
sql99【推荐】:支持内连接+外连接（左外，右外）+交叉连接

按功能分
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接
交叉连接
二、sql92语法的连接
语法
SELECT    查询列表
FROM     待链接的多个表
WHERE     连接条件 [和筛选条件]
1
2
3
这里的连接条件写字段相等关系，如e.department_id = d.id等

1. 简单应用
查询员工名和对应的部门名

SELECT     last_name 员工名,department_name 部门名
FROM     employees, departments
WHERE     employees.department_id=departments.department_id;
# 连接条件为employees.department_id=departments.department_id
1
2
3
4

2. 为表起别名
SELECT     查询列表
FROM     表名 AS 别名, ...
WHERE     连接条件等
1
2
3
查询员工名，工种号，工种名

SELECT     last_name,e.job_id,job_title
FROM     employees AS e, jobs AS j
WHERE     e.job_id = j.job_id;
1
2
3
由于两个表中都有job_id这个字段，所以在SELECT中需要指明是哪张表

3. 加入筛选
查询有奖金的 员工名，部门名

SELECT    e.last_name, d.department_name
FROM    employees e, departments d
WHERE     e.department_id = d.department_id
        AND    e.commission_pct IS NOT NULL; <--加入的筛选条件
1
2
3
4
查询位于的城市的城市名中第二个字符为o的部门名和城市名

SELECT city,department_name
FROM locations l,departments d
WHERE d.location_id=l.location_id
        AND city LIKE '_o%';
1
2
3
4
4. 加入分组
查询每个城市的部门个数

SELECT      city,COUNT(*) 部门个数
FROM      locations l,departments d
WHERE      l.location_id=d.location_id
GROUP BY city
1
2
3
4

查询每个工种的工种名和员工个数，并按员工个数降序

SELECT     job_title,COUNT(*) 员工个数
FROM     jobs j,employees e
WHERE     j.job_id=e.job_id
GROUP BY job_title
ORDER BY 员工个数 DESC;
1
2
3
4
5
5. 三表连接
和两表连接是基本相同的，在WHERE语句中加入一个连接条件即可

获取所有员工的员工名，部门名和所在城市

SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
        AND d.location_id = l.location_id;
1
2
3
4

6. 非等值连接
查询员工的工资和工资级别

job_grades表：

SELECT     salary, grade_level
FROM     employees e, job_grades j
WHERE     e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;
1
2
3

7. 自连接
查询员工名及其对应上级的名称

SELECT     e.last_name 员工,m.last_name 上级
FROM     employees e, employees m
WHERE     e.manager_id=m.employee_id;
1
2
3

三、sql99语法的连接
SELECT 查询列表
FROM 表1别名 
    【连接类型】 join 表2 别名 
    on 连接条件
where xxx
1
2
3
4
5
连接类型分类
内连接：inner
和sql92的等值连接是等效的
外连接：
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉连接：cross
1. 内连接（INNER JOIN）
获取所有的员工名和其对应的部门名

SELECT e.last_name, d.department_name
FROM employees e INNER JOIN departments d
        ON e.department_id = d.department_id;
1
2
3
查询部门个数>3的城市名和部门个数

SELECT city, COUNT(1) 部门个数
FROM departments d INNER JOIN locations l
        ON d.location_id = l.location_id
GROUP BY city
HAVING 部门个数 > 3;
1
2
3
4
5
查询员工名、部门名、工种名，并按部门名排序【三表连接】

SELECT last_name, department_name, job_title
FROM employees e INNER JOIN departments d 
        ON e.department_id = d.department_id
    INNER JOIN jobs j
        ON e.job_id = j.job_id
ORDER BY department_name DESC;
1
2
3
4
5
6
查询员工的工资级别【非等值连接】

SELECT last_name, salary, grade_level
FROM employees e INNER JOIN job_grades j
    ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY salary;
1
2
3
4
查询员工名即其对应的上级名【自连接】

SELECT e.last_name 员工名, m.last_name 上级名
FROM employees e INNER JOIN employees m
    ON e.manager_id = m.employee_id;
1
2
3

由上面的例子可以看出，使用sql99的内连接(INNER JOIN)即可实现sql92的所有连接操作了。

2. 外连接
作用：查询一个表有，另一个表没有的记录

先使用girls.sql生成对应的数据，对应的文件可以在https://www.bilibili.com/video/BV12b411K7Zu?from=search&seid=2415880702283399133 这个b站视频的评论区中找到。

此时我们可以获得如下几张表：

然后我们可以分别使用内连接和外连接分别连接beauty和boys这两张表，查看结果的差异：

内连接：

SELECT *
FROM beauty b INNER JOIN boys y
    ON b.boyfriend_id = y.id;
1
2
3

外连接（左外）：

SELECT *
FROM beauty b LEFT JOIN boys y
    ON b.boyfriend_id = y.id;
1
2
3

可以发现，当左表（即beauty表）的boyfriend_id字段找不到boys表中对应的id进行连接时，它也仍会保留这一记录，而右表（即boys表）的记录则全部设置为Null。

一句话来说，就是左表的数据不管是否满足连接条件，都至少会保留在最终查询集的一条记录之中。

查找男朋友不在男神表的女神名【左外连接】：

SELECT `name`, boyName
FROM beauty b LEFT JOIN boys y
    ON b.boyfriend_id = y.id
WHERE y.id IS NULL;
1
2
3
4
【右外连接】

SELECT `name`, boyName
FROM boys y RIGHT JOIN beauty b
    ON b.boyfriend_id=y.id
WHERE y.id IS NULL;
1
2
3
4

查询没有联系女神的男生

SELECT `name`, boyName
FROM beauty b RIGHT JOIN boys y
    ON b.boyfriend_id=y.id
WHERE b.id IS NULL;
1
2
3
4
查询哪个部门 没有员工

SELECT department_name, COUNT(*)
FROM departments d LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY department_name
HAVING COUNT(*)=0;
1
2
3
4
5
3. 交叉连接
SELECT b.`name`, y.boyName
FROM beauty b CROSS JOIN boys y;
1
2

即返回笛卡尔积（即所有组合的可能），左表数据为m条，右表数据为n条，最终查询集数据为m * n条