MySQL案例——子查询

#练习讲解
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看成表