分组查询

多条件分组查询

select avg(salary),department_id,job_id from employees 
group by department_id,job_id

分组查询并排序

select avg(salary),department_id,job_id from employees 
group by department_id,job_id 
order by avg(salary) desc

分组查询之后筛选

select avg(salary),department_id,job_id from employees 
group by department_id,job_id
having avg(salary) > 1000

分组查询中使用别名

select avg(salary) s,department_id,job_id from employees 
group by department_id,job_id
having s > 1000
order by s desc

多表简单关联查询

select name,boyName from boys,beauty where beauty.boyFriendId = boys.id

这种方式也叫等值连接

多表带条件查询

select last_name,department_name,commission_pct from eployees e, departments d
where e.department_id= d.departmentId and e.commisson_pct is not null

多表分组查询

select count(*) adrressCount ,city from departments d,locations l
where d.localtion_id = l.localtion_id group by city

按城市分组，查询每个城市的地址数量

三个表以上的查询

select last_name,department_name,city frome employess e,departments d,localtions l
where e.department_id=d.department_id and d.localtion_id=l.location_id

多表非等值查询

select salary,grade_level from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal