sql基本查询语句进阶5和6

5.分组查询

select 列（要求出现在group by的后面），分组函数
from 表
where 分组前筛选
group by 分组的列表
having 分组后筛选

特点：
1.分组查询中的筛选条件分为两类：分组前筛选 where ；分组后筛选 group by
2.优先考虑分组前筛选
3.支持单字段、多字段分组，多字段分组无顺序要求

案例1:
列表分组
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

案例2:
表达式/函数分组
按员工名字的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
select count() as c ,length(last_name) as len_name
from employees
group by len_name
having c>5;
group by 和having可以用别名
*只有mysql可以，oracle不通用

案例3:
按多字段分组
查询每个部门的每个工种的员工的平均工资
select avg（salary），department_id, job_id
from employees
group by department_id, job_id;
*结果与多字段分组的顺序无关

6.连接查询/多表连接

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件

分类：
1.按年代：
sql92标准：仅支持内连接
sql99标准【推荐】：外连接中只支持左右外连接，其他都支持

2.按功能：
内连接：等值连接；非等值连接；自连接
外连接：左外；右外；全外
交叉连接

92标准总结

1.等值连接
多表连接结果为多表的交集部分
n表连接，至少需要n-1个连接条件
无顺序要求
一般需要为表起别名
可以搭配所有子句，比如排序、分组、筛选

等值连接
案例1
select name， boyname from beauty，boys
where beauty.boyfriend_id=boys.id;

案例2
查询员工名和对应的部门名
select last_name, department_name
from empoyees, departments
where employees.department_id=departments.department_id；

案例3
为表起别名
好处：
提高语句简洁度
区分多个重名的字段
*若为表起别名，则查询字段不能使用原来表名去限定
select e.last_name, e.job_id, j.job_title
from empoyees as e, jobs as j
where e.job_id=j.job_id；

案例4
加筛选
查询城市名中第二个字符为o的部门名和城市名
select department_name,city
from departments d, locations l
where d.location_id=l.location_id
and city like ‘_o%’;

案例5
加分组
加排序
多表连接

2.非等值连接
非等值连接
案例1
连接工资和工资区间
select salary,grade_level
from employees e, job_grade g
where salary between g.lowest_sal and g.highest_sal
and g.grade_level=’A’;

3.自连接
自连接
自己连接自己，
把一张表当作多张表使用
案例1
查询员工和上级名字
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;

99标准总结

语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序】

分类：
内连接：inner
外连接：
左外：left【outer】
右外：right【outer】
全外：full【outer】
交叉连接：cross

01.内连接：
select
from 表1 别名
inner join 表2 别名
on 条件
特点：
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

1.等值连接【三表连接】
select last_name, department_name, job_title
from emplyees 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 salary，gradelevel
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.employee_id;

02.外连接
1.左右连接
left join 左边的是主表
right join 右边的是主表
应用场景：查询一个表中有，另一个表中没有的记录

查询结果为主表中的所有记录
有匹配的，显示匹配值
无匹配的，显示null
外连接查询记录=内连接结果+主表中有而从表没有的记录

案例1
查询男朋友不在男神表的女神名字
select b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id=bo.id
where bo.id is null;

案例2
查询没有员工的部门
部门为主表
select d.*
from departments d
left outer join employees e on d.employee_id=e.employee_id
where e.employee_id is null；

2.全外连接【并集】
外连接查询记录=内连接结果+表1有但表2没有的记录+表2有但表1没有的记录

案例3

select b.，bo.
from beauty b
full join boys bo
on b.boyfriend_id=bo.id；

03.交叉连接
=笛卡尔乘集
案例4
select b.,bo.
from beauty b
cross join boys bo；

总结常用7种连接

左外连接 内连接 右外连接**