sql99语法
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
where [筛选条件]
group by [分组]
having [筛选条件]
order by [排序列表]
分类:
内连接: inner
外连接:
左外:left[outer]
右外 right[outer]
全外 full[outer]
交叉连接:cross
内连接语法:
select 查询列表
from 表1
inner join 表2
on 连接条件
分类:
等值,
非等值,
自连接
特点:
一:添加排序,分组,筛选
二:inner可以省略
三:筛选条件放在where后面,连接条件放在on后面,提高分离性,偏于阅读
四:inner连接和sql92语法中的等值连接效果一样。
等值连接案例:
查询员工名,部门名
select last_name,departments.department_name
from departments
inner join employees
where
employees.department_id=departments.department_id;
查询名字中包含e的员工名和工种名(添加筛选)
select last_name,job_title
from employees
inner join jobs
on
employees.job_id=jobs.job_id
where
last_name like '%e%';
查询部门个数>3的城市名和部门个数(添加分组+筛选)
select city,
count(*)
from departments
inner join locations
on
departments.location_id=locations.location_id
group by
city
having count(*)>3;
查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序
select department_name,
count(*)
from
departments
inner join employees
on departments.department_id=employees.department_id
group by
departments.department_id
having
count(*)>3
order by count(*) desc;
查询员工名,部门名,工种名,并按部门名降序
select
last_name,
department_name,
job_title
from employees
inner join
departments
on employees.department_id=departments.department_id
inner join jobs
on employees.job_id=jobs.job_id
group by department_name desc;
非等值连接
查询员工的工资级别
select salary,grade_level
from employees
inner join job_grades
on employees.salary
between
job_grades.lowest_sal
and job_grades.highest_sal;
查询工资级别的个数>20的个数,并且按工资级别降序
select
salary,
grade_level,
count(*)
from
employees
join job_grades on
employees.salary
between
job_grades.lowest_sal
and
job_grades.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc;
自连接:
查询员工的名字,上级的名字
select
e.last_name,
m.last_name
from employees e
join employees m
on e.manager_id=m.employee_id;
查询员工的名字包含k的员工,上级的名字
select e.last_name,
m.last_name
from employees e
join employees m
on e.manager_id=m.employee_id
where e.last_name like '%k%';
外连接
应用场景:用于查询一个表中有,一个表中没有
外连接的特点:
一:外连接的查询结果为主表中的所有记录
如果从表中有和他匹配的,则显示匹配的值,如果从表中没有和他匹配的,则显示null
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
外连接查询结果=内连接结果+主表中主表中有而从表的记录
二:左外连接:left join 左表的是主表
右外连接:right join 右边的是主表
左外和右外交换两个表的顺序,可以实现同样的效果
查询没有男朋友的女神名
select
b.name,
bo.*
from
beauty b
left join
boys bo
on
b.boyfriend_id=bo.id
where
bo.id is null;
查询哪个部门没有员工
select
e.employee_id,
e.last_name,
d.department_name
from
departments d
left 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;
交叉连接(笛卡尔乘积)
select b.*,bo.*
from beauty b
cross join boys bo;
连接查询总结:
案例:查询编号>3的女神名的男朋友信息,如果有则列出详细,如果没有,用null填充
select b.id '女神编号',
bo.*
from beauty b
left join boys bo
on b.boyfriend_id=bo.id
where b.id>3;
查询部门名为SAL或IT的员工信息
select
d.department_name,
e.*
from departments d
left join employees e
on
d.department_id=e.department_id
where
d.department_name in ('SAL','IT');
查询那个城市没有部门
select
l.city,
d.department_name
from locations l
left join
departments d
on
d.location_id=l.location_id
where
d.department_name is null;