本教程中所使用的数据库的建表语句都在“SQL教程——索引”这篇文章中,点击链接直达:索引&建表语句
摘要:本文主要介绍SQL99标准的连接查询
SQL99标准
语法:
select 查询列表
from 表1 别名 【链接类型】
join 表2 别名
on 链接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序】
分类:
-
内连接(※)inner
-
外连接
-
左外(※):left【outer】
-
右外(※):right【outer】
-
全外:full【outer】
-
-
交叉连接:cross
1、内连接
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 链接条件
分类:
-
等值连接
-
非等值连接
-
自连接
特点:
-
添加排序、分组、筛选。
-
inner可以省略。
-
筛选条件放在where后面,连接诶条件放在on后面,提高分离性,便于阅读。
-
inner join链接和sql92语法中的的等值连接效果是一样的。
#1、等值连接
#案例1:查询员工名、部门名
select last_name, department_name
from employees e
inner join departments d
on e.department_id = d.department_id;
#2、查询名字中包含e的员工名和工种名(添加筛选)
select last_name, job_title
from employees e
inner join jobs j
on j.job_id
where e.last_name like '%e%';
#3、查询部门个数>3的城市名和部门个数,(分组+筛选)
select city, count(*)
from departments d
inner join locations l
on d.location_id= l.location_id
group by city
having count(*) > 3;
#4、查询哪个部门的员工个数>3的部门名和员工个数,并按照个数降序(添加排序)
select department_name, count(*)
from employees e
inner join departments d
on e.department_id = d.department_id
group by department_name
having count(*) > 3
order by count(*) desc;
#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;
#2、非等值连接
#查询员工的工资级别
select last_name, grade_level
from employees e
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;
#查询员工工资级别个数>2的级别个数,并按工资级别排序
select count(*), grade_level
from employees e
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
group by grade_level
having count(*) > 2
order by grade_level desc;
#3、自连接
#查询员工的名字、上级的名字
select e.last_name 员工, m.last_name 上级员工
from employees e
inner join employees m
on e.manager_id = m.employee_id;
2、外连接
应用场景:用于查询一个表中有,另一个表中没有的情况
特点:
-
外连接查询结果为主表中的所有记录
如果从表中有和它匹配的记录,则显示匹配的
如果从表中没有和它匹配的记录,则显示null值
左右外链接查询结果 = 内连接结果 + 主表中有而从表没有的记录
全外连接不分主表从表,它的查询结果包括两表交集+A表有B表没有的部分+B表有A表没有的部分
-
左外连接,left join左边是主表
右外链接,right join右边的是主表
-
左外和右外交换两个表的顺序,可以实现同样的效果
#引入:查询男朋友不在男神表的女神名
#于是你可能会想到用嵌套查询:
select name
from beauty g
where boyfriend_id not in
(select id from boys);
但如果不让用嵌套呢?
两种方法:
左外:
select b.name, bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;
右外:
select b.name, bo.*
from boys bo
right outer join beauty b
on b.boyfriend_id = bo.id
where bo.id is null;
#案例1:查询哪个部门没有员工
#右外
select d.*, e.employee_id
from employees e
right outer join departments d
on e.department_id = d.department_id
where e.employee_id is null;
#左外
select d.*, e.employee_id
from departments d
left outer join employees e
on e.department_id = d.department_id
where e.employee_id is null;
#全外(mysql不支持全外连接)
select b.*, bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id = bo.id;
3、交叉连接(笛卡尔乘)
select b.*, bo.*
from beauty b
cross join boys bo;
小结:
SQL92 VS SQL99
-
功能:SQL99支持的较多
-
可读性:SQL99实现连接条件和筛选条件的分离,可读性较高
#Test
#案例1:查询编号>3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
select b.name, bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id = bo.id
where b.id > 3;
#案例2:查询哪个城市没有部门
select city, department_name
from locations l
left outer join departments d
on l.location_id = d.location_id
where department_id is null;
#案例3:查询部门名为SAL或IT的员工信息
select d.department_name, e.*
from employees e
join departments d
on e.department_id = d.department_id
where d.department_name in ('SAL', 'IT');