#练习讲解
1.查询哪个城市没有部门(左外or右外连接)
SELECT city,department_id
FROM departments d
RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;
2.查询部门名为 SAL 或 IT 的员工信息
SELECT e.*,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN('SAL','IT');
3.查询每个工种、每个部门的部门名、工种名和最低工资(三表连接)
SELECT department_name,job_title,MIN(salary)
FROM departments d,jobs j,employees e
WHERE d.department_id = e.department_id
AND e.job_id = j.job_id
GROUP BY e.job_id,department_name;
4.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*)部门个数
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY country_id
HAVING COUNT(*)>2;
5.选择指定员工的姓名、员工号、以及他的管理者的姓名和员工号,结果类似于下面的格式
emp_name emp_id mgr_name mgr_id
kochhar 101 king 100
SELECT e.last_name emp_name,e.employee_id emp_id,
m.last_name mgr_name,m.employee_id mgr_id
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar';
子查询
概念:出现在其他语句中的select语句,称为子查询
外面的查询语句为主查询或者外查询
分类:
①按子查询出现的位置:
select后面:
只支持标量子查询
from后面
支持查询
where或having后面:★
标量子查询(单行数据)★
列子查询 (多行数据)★
行子查询
exists后面:
查询
②按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集一列多行)
行子查询(结果集一行多列)
查询(结果集一般为多行多列)
一、where或having后面:
1.标量子查询(单行数据)★
2.列子查询 (多行数据)★
3.行子查询 (一行多列)
特点:①子查询放在小括号内
②子查询一般放在条件的右边
③标量子查询一般搭配单行的操作符来使用
> < >= <= = <>
列子查询搭配多行操作符来使用
in/not in、all、some/any
in/not in:等于/不等于列表中的任意一个
some/any:跟子查询中返回的某一个值进行比较
all:跟子查询返回的所有值进行比较
④子查询的执行顺序优先于主查询执行,主查询的条件用到了
子查询的结果
案例:谁的工资比Partners高?
①查Partners的工资
SELECT salary
FROM employees
WHERE last_name = 'Partners'
②查员工姓名和工资,满足①的条件
SELECT last_name,salary FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Partners'
)
案例2:返回job_id与141号员工相同,salary比143号员工多的员工姓名、job_id、工资
①查询141号(employee_id)员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
②查询143号的工资
SELECT salary
FROM employees
WHERE employee_id = 143
③查询员工姓名、job_id、工资,要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
)AND salary >(
SELECT salary
FROM employees
WHERE employee_id = 143
)
#案例3:查询公司工资最少的员工的姓名、job_id和工资
①查询最低工资
SELECT MIN(salary)
FROM employees
②查最低工资的员工的姓名
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
)
2.列子查询
案例1:返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门id
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
②查询员工姓名,满足部门号是①中查出来的
SELECT last_name
FROM employees
WHERE department_id
IN (
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
)
#表连接↓ 表连接可以替换所有的子查询 但是优先使用子查询
SELECT last_name,e.department_id,d.location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND d.location_id IN(1400,1700);
#案例2:返回其他工种中比job_id为IT_PROG工种任一工资低的员工号、姓名、job_id以及工资
①查询job_id为'IT_PROG'部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
②查询员工号、姓名、job_id以及工资,要求工资比①中低
任一 可以用 any
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
作业讲解:
#1.查询和 Zlotkey 相同部门的员工姓名和工资
①查询 Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
②查询部门号=①的员工姓名和工资
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)
2.查询工资比公司平均工资高的员工的员工号,姓名和工资
① 查询平均工资
SELECT AVG(salary)
FROM employees
② 查询工资比①高的工的员工号,姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)
3.查询最低工资大于50号部门最低工资的部门id和其最低工资
(要用到having)
①查询50号部门最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
②查到每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
③在②的基础上进行筛选,min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
)
4.查询其他部门中比姓名为Gee所在部门的所有工资低的员工的姓名,job_id以及工资 (所有可以用all)
①查询Gee所在部门的工资
SELECT salary
FROM employees
WHERE last_name = 'MWEISS'
②查询员工的姓名,job_id以及工资,要求工资比①低
SELECT last_name,job_id,salary
FROM employees
WHERE salary < ALL(
SELECT salary
FROM employees
WHERE last_name = 'Weiss'
)AND job_id <> (
SELECT job_id
FROM employees
WHERE last_name = 'Weiss'
)
#==================================================
3.行子查询(结果集是一行多列或多行多列)
#案例:查询员工编号最小 并且 工资最高的员工信息
①查最小的员工编号 employee_id
SELECT MIN(employee_id)
FROM employees
②查最高工资 salary
SELECT MAX(salary)
FROM employees
③查员工信息,要同时满足要求①②
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)AND salary = (
SELECT MAX(salary)
FROM employees
)
SELECT * FROM employees
WHERE (employee_id,salary) IN (
SELECT MIN(employee_id),MAX(salary)
FROM employees
)
二、select后面
只支持标量子查询
#案例:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) 员工个数
FROM departments d;
三、from后面
#注意:将子查询的结果充当一张表,所以必须要起别名
案例:查询每个部门的平均工资的工资等级
①查询每个部门的平均工资
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
SELECT * FROM job_grades
②连接①的结果集和job_grades表,筛选平均工资所在
#between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#相当于把查到的每个部门的平均工资、部门id看成表