MySQL学习笔记5-分组查询连接查询

1. 分组查询

语法：
     select 分组函数，列（要求出现在group by后面）
     from 表
     [where 筛选条件]
     group by 分组的列表
     [order by 子句]
注意：查询列表必须特殊，要求是分组函数，出现在group by后面的列

select max(salary),job_id from employees group by job_id;
select count(*), location_id from departments group by location_id;

2.连接查询

sql99语法

语法：select 查询列表 from 表1 别名 [连接类型]
      join 表2 别名
      on 连接条件
      [where 筛选条件]
      [group by 分组]
      [having 筛选条件]
      [order by 排序列表]
分类：
	内连接:inner
	外连接:左外left [outer]、右外right [outer]、全外full [order]
	交叉连接:cross

2.1 内连接

语法：
	select 查询列表
	from 表1 别名 
	inner join 表2 别名 
	on 连接条件;
分类：等值、非等值、自连接

select last_name,department_name
from employees e
inner join departments d
on e.department_id = d.department_id;

select last_name, job_title
from employees e
inner join jobs j
on e.job_id = j.job_id
where last_name like '%e%';

select city, count(*)
from  locations l
inner join departments d
on l.location_id = d.location_id
group by city
having count(*) > 3;

select department_name, count(*)
from departments d
inner join employees e
on d.department_id = e.department_id
group by department_name
having count(*) > 3
order by count(*) desc;

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;

select salary, grade_level
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;

#查询工资级别的个数>20的个数，并且按工资级别降序
select count(*), grade_level
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
group by grade_level
having count(*) > 20
order by  grade_level desc;

#自连接
select e.last_name, m.last_name
from employees e
inner join employees m
on e.manager_id = m.employee_id
where e.last_name like '%k%';

2.2 外连接

应用场景：用于查询一个表中有，另一个表没有的记录
特点：
	1.外连接的查询结果为主表中的所有记录
       如果表中有匹配的，则显示匹配的值；否则显示null
       外连接查询结果=内连接结果+主表中有而从表没有的记录
	2.左外连接，left join左边的是主表，右外连接，right join右边的是主表
    3.左外和右外交换两个表的顺序，可以实现同样的效果
    4.全外连接=内连接的结果+表1中有但表2没有的+表2有但表1没有的 
      mysql不支持全外

2.3 交叉连接

select b.*,bo.*
from beauty b
cross join boys bo;

3. 总结