含义
又称多表查询,当查询的字段来自于多个表时就会用到连接查询。
select 字段1,字段2
from 表1,表2,...;
笛卡尔积乘积现象:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接。
表1有m行,表2有n行,结果m*n行
发生的原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类
按年代分类:
sql92标准:仅支持内连接:包括等值、非等值、自连接
sql99标准:支持内连(等值、非等值、自连接),外连(左外、右外)全外(mysql不支持),交叉连接
按功能分类:
-
内连接:
等值、非等值、自连接 -
外连接
左外连接、右外连接、全外连接 -
交叉连接
等值连接语法
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 追加筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
eg:查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
eg2:查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
注:给表起别名
好处:
-
提高语句的简洁性
-
区分多个重名的字段**
添加筛选 追加and
eg3:查询有奖金的员工名、部门名
SELECT e.last_name,d.`department_name`,e.`commission_pct`
FROM employees e ,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
eg4:查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.city LIKE('_o%');
加分组函数
eg5:查询每个城市的部门个数
SELECT COUNT(*),city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
eg6:查询有奖金的每个部门的部门名和部门领导编号,和该部门的最低工资
SELECT d.department_name,d.manager_id,MIN(salary)
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL
GROUP BY d.`department_name`;
加排序
eg7:查询每个工种的工种名和员工个数,并且按照员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
实现三表连接
eg8:查询员工名、部门名、和所在的城市
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`
AND city LIKE('s%')
ORDER BY department_name DESC;
多表查询的特点
- 多表等值连接的结果为多表的交集部分
- n表连接,至少需要n-1个连接条件
- 多表的顺序没有要求,可以调换
- 一般需要为表起别名
- 可以搭配其他子句使用,比如加筛选、分组、排序
非等值连接语法
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
eg:查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees,job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal;
自连接语法
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
eg:查询员工名、和上级领导的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`, m.`last_name`
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
等值连接、非等值连接、自连接多表连接查询-练习题
#1、查询所有员工的姓名、部门号、和部门名称
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;
#2、查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id, d.department_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND d.`department_id`=90;
#3、选择所有有奖金的员工的
last_name,department_name,location_id,city
SELECT last_name,department_name,l.location_id,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.location_id
AND e.`commission_pct` IS NOT NULL;
#4、选择city在Toronto工作的员工的
last_name,job_id,department_id,department_name
SELECT last_name,job_id,d.department_id,department_name
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.location_id
AND city="Toronto";
#5、查询每个工种、每个部门的部门名称、工种名和最低工资
SELECT department_name,job_title,MIN(salary)
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;
#6、查询每个国家下的部门个数大于2的国家编号
SELECT COUNT(*),country_id
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;
#7、选择指定员工的姓名、员工号,以及他的管理者的姓名和员工号
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e,employees m
WHERE e.`manager_id`=m.employee_id
AND e.`last_name`='kochhar';