非等值连接
案例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_name
from employees e join departments d
on e.department_id=d.department_id;
案例2:查询有奖金的员工名、部门名
SELECT last_name,department_name,e.commission_pct
from employees e join departments d
on e.department_id=d.department_id
where e.commission_pct is not null;
案例3:查询城市名、员工名和部门名
SELECT city,last_name,department_name
from locations l join departments d
on l.location_id=d.location_id
join 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和salary
SELECT 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_id
HAVING MIN(salary)>(SELECT MIN(salary) from employees where department_id=50);
多行子查询
案例:返回location_id是1400或1700的部门中的所有员工姓名
SELECT
last_name
FROM
employees
WHERE
department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_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
) a
where e.department_id=a.department_id
and 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
) a
where e.department_id=a.department_id
and 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
)
) m
where d.department_id=m.department_id