非等值连接

案例1：查询员工的工资以及对应的工资级别SELECT salary,grade_level FROM employees e,job_grades g WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;案例2：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别SELECT salary,grade_level from employees e,job_grades g where e.salary BETWEEN g.lowest_sal and g.highest_sal and e.last_name like \'__a_e%\'

内连接

语法：
select 字段1，字段2，…
from 表1
inner join 表2 on 连接条件;

特点：
① 效果和等值连接是一样的！
② 表的顺序可以调换
③ 可以为表起别名
④ 可以添加筛选、分组、排序
⑤ 将筛选条件和连接条件实现了分类，提高代码的可读性
⑥ n表连接，至少需要n-1 个连接条件

案例1 ：查询员工名、部门名SELECT last_name,department_namefrom employees e join departments don e.department_id=d.department_id;案例2：查询有奖金的员工名、部门名SELECT last_name,department_name,e.commission_pctfrom employees e join departments don e.department_id=d.department_idwhere e.commission_pct is not null;案例3：查询城市名、员工名和部门名SELECT city,last_name,department_namefrom locations l join departments don l.location_id=d.location_idjoin employees e on e.department_id=d.department_id;

外连接

语法：
select 字段1，字段2，…
from 表1
left|right 【outer】 join 表2
on 连接条件

特点：
①查询结果：内连接的结果+主表中有但从表没有的记录（从表的字段用null填充）
②左连接，左边的就是主表； 右连接，右边的就是主表
③一般来讲要查询的字段来自于哪个表，那哪个表就是主表
④一般用于查询主表中有但从表中没有的记录

案例1：查询没有男朋友的女神名称SELECT \'name\',boyName from beauty b LEFT JOIN boys o on b.boyfriend_id=o.id where o.id is not null案例2：查询哪个城市没有部门SELECT city,department_name from locations l LEFT JOIN departments d on l.location_id= d.location_id where department_id is not null案例3：查询哪个工种没有员工SELECT job_title,employee_id from jobs j LEFT JOIN employees e on j.job_id=e.job_id where e.job_id is null;

自连接

案例：查询员工名和上级领导的名字SELECT e.last_name,m.last_name from employees e INNER JOIN employees m on e.manager_id=m.employee_id

子查询

概念：
嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类
语法
select 字段1，字段2
from 表1
where Id in(
select 字段1 from 表2
)
特点或注意事项：
1、子查询放在小括号内
2、放在条件右侧
3、子查询优先于主查询执行

分类：
单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>）
多行子查询:使用多行操作符（any、all、in、not in）

单行子查询

案例1：谁的工资比Abel高select * from employees where salary>(SELECT salary from employees where last_name =\'Abel\');案例2：题目：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资SELECT last_name,job_id,salary from employees案例3：返回公司工资最少的员工的last_name,job_id和salarySELECT last_name,job_id,salary from employees WHERE salary=(select MIN(salary) from employees );案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT MIN(salary),department_id from employees GROUP BY department_idHAVING MIN(salary)>(SELECT MIN(salary) from employees where department_id=50);

多行子查询

案例：返回location_id是1400或1700的部门中的所有员工姓名SELECTlast_nameFROMemployeesWHEREdepartment_id IN (SELECTdepartment_idFROMdepartmentsWHERElocation_id IN (1400, 1700))

查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（三步走）

①、SELECT department_id,avg(salary) av_s from employees GROUP BY department_id②、SELECT employee_id,last_name,salary from employees e,(SELECT department_id,avg(salary) av_s from employees GROUP BY department_id) awhere e.department_id=a.department_idand e.salary>a.av_s③、SELECT employee_id,last_name,salary from employees e,(SELECT avg(salary) av,department_id  from employees GROUP BY department_id) awhere e.department_id=a.department_idand e.salary>a.av

查询平均工资最低的部门信息

①、查询每个部门的平均工资SELECT avg(salary) av,department_id from employees GROUP BY department_id②、查询①结果中avg(salary)字段中的最低值SELECT MIN(av) from (SELECT avg(salary) av,department_id from employees GROUP BY department_id) a③、查询部门编号，满足平均工资=②结果SELECT department_id,avg(salary) from employees GROUP BY department_id HAVING avg(salary)=(SELECT MIN(av) from (SELECT avg(salary) av,department_id from employees GROUP BY department_id) a)④、查询部门信息，满足 department_id=③SELECT * from departments d,(SELECT department_id,avg(salary) from employees GROUP BY department_id HAVING avg(salary)=(SELECT MIN(av) from (SELECT avg(salary) av,department_id from employees GROUP BY department_id) a)) mwhere d.department_id=m.department_id