本篇目录
一.含义:
又称多表查询,当查询字段来自多个表时,就会用到连接查询。
二.分类:
按年代分:
1)sql92标准:只支持内连接
2)sql99标准:内连接+外连接(左外、右外)+交叉连接
按功能分:
1)内连接:等值连接、非等值连接、自连接
2)外连接:左外连接、右外连接、全外连接(MySQL不支持)
3)交叉连接
三.各种连接的解释:
1.交叉连接:
交叉连接AB表
2.等值连接:
等值连接A、B,连接条件:A.stu_id = B.stu_id
step1:AB先交叉连接
step2:按连接条件找出符合要求的观测
因此,最终的查询结果是:
3.非等值连接:
非等值连接C、D,连接条件:C.score介于D.score_low到D.score_high之间
step1:CD先交叉连接
step2:按连接条件找出符合要求的观测
最终查询结果:
4.左外连接:
step1:交叉连接AB
step2:按连接条件找到符合要求的观测
step3:对于主表有,而从表没有的记录,从表用null填充
最终查询结果:
5.右外连接:
step1:交叉连接AC
step2:按连接条件找到符合要求的观测
step3:对于主表有,而从表没有的记录,从表用null填充
最终查询结果是:
四.sql92代码:
1.基本语法:
select 查询字段
from 表1,表2
where 表之间的连接条件
2.等值连接:
#案例:查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments #employees是一张表,departments是另一张表
WHERE employees.`department_id`=departments.`department_id`; #等值体现在这里
#查询员工名、工种号、工种名
#方式一
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j #为第一张表起别名为"e",第二张表起别名为"j"
WHERE e.`job_id`=j.`job_id`;
#方式二:交换两表顺序,发现结果不变,说明表的顺序不影响查询结果
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
#还可以添加筛选条件、分组、排序等
#案例:查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
#三表连接
#案例:查询员工名、部门名和所在的城市
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;
3.非等值连接:
#案例:查询员工的工资和工资级别
SELECT last_name,salary,grade_level
FROM employees e,salary_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;#非等值体现在这里:salary不是等于g表中某个变量的某个值,而是介于某两个值之间
查询结果:
salary_grades表长这个样子:
4.自连接:
SELECT e.employee_id AS 该员工工号,e.last_name AS 该员工姓名,m.employee_id AS 该员工上级的工号,m.last_name AS 该员工上级的姓名
FROM employees e,employees m #自连接体现在这里,自己和自己连接。同时,这里也体现了为表起别名的重要性
WHERE e.`manager_id`=m.`employee_id`;
五.sql99代码:
1.基本语法:
select 查询列表
from 表1 as 别名 【连接类型】
join 表2 as 别名 on 连接条件
【where 筛选条件】
【group by 分组条件】
【having】
【order by】
连接类型:内连接:【inner】,左外:left【outer】,右外:right【outer】,全外:full【outer】,交叉连接:cross
2.内连接:
2.1等值连接:
#案例1:查询员工名、部门名
SELECT last_name,department_name
FROM employees AS e
INNER JOIN departments AS d #inner可以省略不写
ON e.`department_id` = d.`department_id`;
#案例2:查询部门个数>3的城市名及其所拥有的部门数
SELECT city,COUNT(*) AS 部门个数
FROM locations AS l
INNER JOIN departments AS d
ON l.`location_id` = d.`location_id`
GROUP BY city
HAVING 部门个数>3;
#三表连接
#案例3:查询员工名、部门名、工种名,并按部门名降序排列
SELECT last_name,d.department_name,j.job_title
FROM employees AS e
INNER JOIN departments AS d ON d.`department_id` = e.`department_id`
INNER JOIN jobs AS j ON j.`job_id` = e.`job_id`
ORDER BY d.`department_name` DESC;
2.2非等值连接
#案例:查询每个工资级别的员工个数,并按工资级别降序显示员工个数大于5的工资级别及其对应员工个数
SELECT grade_level,COUNT(*) AS 该等级员工人数
FROM employees AS e
INNER JOIN salary_grades AS g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY g.`grade_level`
HAVING 该等级员工人数>5
ORDER BY g.`grade_level`;
2.3自连接:
#查询员工工号、姓名,以及该员工的上司的工号及姓名
SELECT table1.last_name AS 员工姓名,table1.employee_id AS 员工工号,table2.last_name AS 经理姓名,table2.employee_id AS 经理工号
FROM employees AS table1
INNER JOIN employees AS table2
ON table1.`manager_id` = table2.`employee_id`;
3.外连接:
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
1.外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,则显示匹配的值;如果没有匹配的,则显示null。即外连接结果=内连接结果+主表中有而从表中没有的记录
2.左外连接中,left join左边的是主表;右外连接中,right join右边的是主表
3.左外和右外交换两个表的顺序,可以实现同样的效果
4.全外连接=内连接结果+表1中有但表2中没有的+表2中有但表1中没有的
5.交叉连接:笛卡尔乘积
3.1左外连接:
SELECT DISTINCT department_name AS 没有员工的部门
FROM departments
LEFT OUTER JOIN employees #outer可以省略不写,departments是主表
ON `departments`.`department_id` = `employees`.`department_id`
WHERE `employees`.`employee_id` IS NULL;
查询结果:
3.2右外连接:
#和上一题一样,只是换成了右外连接
SELECT DISTINCT department_name AS 没有员工的部门
FROM employees
RIGHT OUTER JOIN departments #departments是主表
ON `departments`.`department_id` = `employees`.`department_id`
WHERE `employees`.`employee_id` IS NULL;
4.交叉连接:
SELECT beauty.name,boys.boyName
FROM beauty
CROSS JOIN boys;