MySql数据库基本操作六（where or having子查询）

子查询

含义：
  出现在其他语句中的select语句，称为子查询或内查询
  外部的查询语句，称为主查询或者外查询

分类：
  按子查询出现的位置：
    SELECT 后面
      仅仅支持标量子查询
    FROM 后面
      支持表子查询
    WHERE或having后面
      标量子查询
      列子查询
      行子查询
    exists后面（相关子查询）
      表子查询
  按结果集的列数不同：
    标量子查询（结果集只有一行多列）
    列子查询（结果集只有一列多行）
    行子查询（多行多列）
    表子查询（一般为多行多列）

一、where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行一列）
3、列子查询（多行多列）

特点：
  子查询放在小括号内侧
  子查询一般放在条件的右侧
  标量子查询，一般搭配着单行操作符使用
  <>=<=>=

  列子查询，一般搭配着多行操作符使用
  in，any，some，all

  子查询优先于主查询

1、标量子查询
  案例：谁的工资比Abel高？
  查询Abel的工资

SELECT salary
FROM employees
WHERE last_name='Abel';

  查询员工的信息，满足salary>abel结果

SELECT *
FROM employees
WHERE salary>(
			SELECT salary
			FROM employees
			WHERE last_name='Abel'
);

  案例：返回job_id与141号员工相同，salary比143号员工多的员工姓名，jio_id和工资
  查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id=141;
);

  查询143号员工的salary

SELECT salary
FROM employees
WHERE employee_id=143;

  查询员工的姓名，job_id和工资，要求job_id=141，salary>143

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

);

  案例：返回公司工资最少的员工的last_name,job_id,和salary
  查询公司的 最低工资

SELECT MIN(salary)
FROM employees;

  查询last_name,job_id,和salary，要求工资比上面低

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
			SELECT MIN(salary)
			FROM employees
);

  案例：查询最低工资大于100号部门最低工资的部门id和其最低工资
  查询100号部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=100;

  查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

  在2基础上筛选，满足min（salary）>1

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
			SELECT MIN(salary)
			FROM employees
			WHERE department_id=100
);

2、列子查询

  案例：返回location_id是1400或者1700的部门中的所有员工姓名
  查询location_id是1400或者1700的部门

SELECT department_id
FROM departments
WHERE location_id IN(1400,1700);

  查询员工姓名，要求部门是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
FROM employees
WHERE department_id =ANY(
			SELECT department_id
			FROM departments
			WHERE location_id IN(1400,1700)
);

  案例：返回其他部门中比job_id为it_prog部门任意工资低的员

工的员工号、姓名、job_id以及salary
#查询job_id为iy_prog部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

  查询员工号、姓名、job_id以及salary,salary<ANY(上述结果)

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
			SELECT DISTINCT salary
			FROM employees
			WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

  第二种方法

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
			SELECT MAX(salary)
			FROM employees
			WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

  案例：返回其他部门中比job_id为it_prog部门所有工资低的员工的员工号、姓名、job_id以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
			SELECT DISTINCT salary
			FROM employees
			WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

3、行子查询

  案例：查询员工编号最小且工资最高的员工信息
  查询最小的员工编号

SELECT MIN(employee_id)
FROM employees;

  查询最高工资

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)=(SELECT MIN(employee_id),MAX(salary)
FROM employees);