12 sql92语法的连接查询

连接查询

含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2 有n行，结果为 m*n行
如何发生：没有有效的连接条件

按功能分类：

• 内连接
  - 等值连接
  - 非等值连接
  - 自连接

• 外连接
  - 左外连接
  - 右外连接
  - 全外连接

• 交叉连接

内连接之等值连接

1、简单介绍

案例1：查询女神名和对应的男生名

SELECT beauty.`name`, boys.boyName
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id;

案例2：查询员工名和对应的部门名

SELECT employees.last_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.deaprtment_id;

2、为表起别名

作用：

• 提高语句的简洁度
• 区分多个重名的字段
  注意：起了别名后，查询的字段就不能再使用原来的表名了。

案例1：查询员工名、工种号、工种名

SELECT E.last_name, E.job_id, J.job_title
FROM employees AS E, jobs AS J
WHERE E.job_id = J.job_id;

表的顺序可以调换吗？ 可以！！以下代码输出的结果和前面是一样的

SELECT E.last_name, E.job_id, J.job_title
FROM jobs AS J, employees AS E
WHERE E.job_id = J.job_id;

3、添加筛选

案例1：查询有奖金的员工名、部门名

SELECT E.last_name, D.department_name
FROM employees AS E, departments AS D
WHERE E.department_id = D.department_id
AND E.commission_pct IS NOT NULL;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT D.department_name, L.city
FROM departments AS D, locations AS L
WHERE D.location_id = L.location_id;
AND L.city LIKE "_o%";

4、添加分组

案例1：查询每个城市的部门个数

SELECT L.city, COUNT(*)
FROM departments AS D, locations AS L
WHERE D.location_id = L.location_id
GROUP BY L.city;

5、添加排序

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

SELECT J.job_title, COUNT(*)
FROM jobs AS J, employees AS E
WHERE J.jon_id = E.job_id
GROUP BY J.job_id
ORDER BY COUNT(*) DESC;

6、三表连接

案例：查询员工名、部门名和所在的城市

SELECT E.last_name, D.department_name, L.city
FROM employees AS E, departments AS D, locations AS L
WHERE E.department_id = D.department_id
AND D.location_id = L.location_id;

7、等值连接总结

1、多表等值连接的结果为多表的交集部分
2、n表连接至少需要n-1个连接条件
3、多表的声明顺序没有要求
4、一般需要为表起别名
5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

内连接之非等值连接

案例1：查询员工的工作和工资级别

SELECT salary, grade_level
FROM employees, AS E, job_grades AS J
WHERE E.salary BETWEEN J.lowest_sal AND J.highest_sal;

内连接之自连接

案例：查询员工名和上级的名称

SELECT E.last_name, E.employee_id, M.last_name, M.employee_id
FROM employees AS E, employees AS M
WHERE E.manager_id = M.employee_id;