#1.等值连接
案例1:查询女神名和对应的男神名
select name,boyName
from boys,beauty
where beauty.boyfriend_id = boys.id;
案例2:查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.`department_id`= department_id.`department_id`#2.为表起别名(提高语句的简洁度)
注意:如果为表起了别名,则查询的字段不能用原来的表名修饰
#查询员工名、工种号、工种名select e.last_name,e.job_id,j.job_title
from employees as e,jobs as j
where e.`job_id`=j.`job_id`;#3.两个表的顺序可以调换#4.添加筛选
案例:查询有奖金的员工名和部门名
select last_name,department_name,commision_pct
from employees e,departments d
where e.`department_id`=d.`department_id`and commision_pct isnotnull
案例:查询城市中第二个字符为0的部门名和城市名
select department_name,city
from departments d,locations l
where d.`location_id`= l.`location_id`and city like'_o%';#5.添加分组
案例:查询每个城市的部门个数
selectcount(*) 个数,city
from departments d,locations l
where d.`location_id`=l.`location_id`groupby city;
案例:查询出有奖金的每个部门的部门名和部门编号和该部门的最低工资
select department_name,d.manager_id,min(salary)from departments d,employees e
where d.`department_id`=e.`department_id`and commision_pct isnotnullgroupby department_name,d.manager_id;#6.添加排序
案例:查询每个工种的工种名和员工的个数,并且按员工个数降序
select job_title,count(*)from employees e,jobs j
where e.`job_id`=j.`job_id`groupby job_title
orderbycount(*)desc;#7.三表连接
案列:查询员工名、部门名和所在城市
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%';
5.1.2 非等值连接
案例:查询员工的工资和工资级别
select salary,grade_level
from employee e,job_grades g
where salary between g.`lowest_sal`and g.`highest_sal`;
5.1.3 自连接
案例:查询员工名及其上级的领导名
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;
5.2 sql99标准
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
where 筛选条件
groupby 分组
having 筛选条件
Orderby 排序列表
分类:
内连接:inner
外连接
左外连接:left(outer)
右外连接:right(outer)
全外连接:full(outer)
交叉连接:cross
5.2.1 内连接
语法:
select 查询列表
from 表1 别名
innerjoin 表2 别名
on 连接条件;
分类:
等值连接
非等值连接
自连接
特点:
添加排序分组筛选
inner可以省略
筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
innerjoin连接和sq92中的等值连接最终实现的效果都是一样的
1.等值连接
案例一:查询员工名,部门名
select last_name,department_name
from employees e
innerjoin departments d
on e.department_id = d.department_id;
案例二:查询名字中包含e的员工名和工种名
select last_name,job_title
from employees e
innerjoin jobs
on e。job_id = j.job.id
where e.last_name like'%e%'
案例三:查询部门个数>3的城市名和部门个数
select city ,count(*) 个数
from departments d
innerjoin locations l
on d.location_id = l.location_id
groupby city
havingcount(*)>3;
案例四:查询员工名、部门名、工种名,并按部门名降序
select last_name,department_id,job_id
from employees e
innerjoin departments d on e.department_id = d.department_id
innerjoin jobs j on j.job_id = e.job_id
orderby department_name desc;2.非等值连接
#查询员工的工资级别select salary,grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;3.自连接
#查询员工名字、上级的名字select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id = m.employeee_id;
5.2.2 外连接
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
1.外连接的查询结果为主表中的所有记录
如果从表中有匹配的显示对应的匹配值
如果从表中没有匹配的则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2.左外连接:leftjoin左边的是主表
右外连接:rightjoin右边的是主表
3.左外连接和右外连接交换两个表的顺序,可以实现同样的效果
引入:查询男朋友 不在男神表的女神名
select b.name,bo.*from beauty b
leftouterjoin boys bo
on b.`boyfriend_id`= bo.`id`;where bo.`id`isnull;
案例:查询哪个部门没有员工
#左外select d.*,e.employee_id
from departments d
leftouterjoin employees e
on d.`department_id`= e.`department_id`where e.`employee_id`isnull;#右外select d.*,e.employee_id
from employees e
rightouterjoin departments d
on d.`department_id`= e.`department_id`where e.`employee_id`isnull;