连接查询(这章开始就是重点了,又称多表查询) 何时用到连接查询: 当查询的字段来自于多个表时,就需要用到连接查询 多表查询视图如下:
连接条件查询
select
beauty.name as "女神",
boys.boyName as "男神"
from
beauty , boys
where
beauty.boyfriend_id = boys.id;
按功能分类如下:
- 内连接:
1.等值连接
2.非等值连接
3.自连接
- 外连接:
1. 左外连接
1. 右外连接
2. 全外连接
- 交叉连接
等值连接案例如下:
- 案例1. 查询beauty女神名和boys表对应的男神名
select
beauty.name "女神名",
boys.boyName "男神名"
from
beauty,boys
where
beauty.boyfriend_id = boys.id
- 案例2. 查询员工名和对应的部门名
select
employees.last_name "员工名",
departments.department_name "部门"
from
myemployees.employees,myemployees.departments
where
employees.department_id = departments.department_id;
- 案例3. 查询员工名,工种号,工种名
select
employees.last_name "员工名",
jobs.job_id "工种号",
jobs.job_title "工种名"
from
myemployees.employees,myemployees.jobs
where
myemployees.employees.job_id = jobs.job_id;
- 案例4. 为表取别名···区分多个表中重名的字段
select
e.last_name "员工名",
j.job_id "工种号",
j.job_title "工种名"
from
myemployees.employees as e,myemployees.jobs as j
where
e.job_id = j.job_id;
等值连接加筛选条件查询
- 案例1. 查询employees和departments表中有奖金的员工名,部门名,以及奖金
select
last_name as "员工",
commission_pct as "奖金",
department_name as "部门名"
from
employees e join departments d
where
e.department_id = d.department_id and commission_pct is not null ;
- 案例2. 查询城市名中第二个字符为o的部门名和城市名
select
department_name "部门名",
city "城市名"
from
departments d join locations l
where
d.location_id = l.location_id and city like '_O%'
等值连接加分组查询
- 案例1. 查询每个城市的部门个数
select
count(*) as "部门个数",
city as "城市"
from
departments d join locations l
where
d.location_id = l.location_id
group by city;
- 案例2. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select
d.department_name as "部门名",
d.manager_id as "部门领导编号",
min(e.salary) as "最低工资"
from
departments d join employees e
where
d.department_id = e.department_id and e.commission_pct is not null
group by
d.department_name,d.manager_id;
等值连接加排序查询
- 案例1. 查询每个工种的工种名和员工的个数,并且按员工个数降序
select
job_title "工种名",
count(*) "员工的个数"
from
jobs j join employees e
where
j.job_id = e.job_id
group by
job_title
order by
count(*) desc ;
等值连接实现三表连接查询
- 案例1. 查询员工名,部门名和所在的城市,并且加like筛选条件,并且部门名降序
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 ;
以上就是等值连接的操作了,下一章继续推出,非等值连接,自连接,外连接等! 坚持学习,才是最大的收货,加油哦.