MySQL子查询

1、什么是子查询

　　嵌套在其它语句内部的select语句称为子查询(也称为内查询),这里面的其它语句可以是insert、delete、update、select,一般情况下select语句作为外部语句居多,如果外部的语句为select语句,则将外部的select语句称为主查询(也称为外查询).

2、子查询特点

　　1、子查询优于主查询执行,主查询使用子查询的执行结果.

　　2、子查询都放在小括号内.

　　3、子查询可以放在 from、select、where、having、exists后面,但是一般是放在查询条件的右侧.

3、子查询的分类　　

　　按照查询的结果分类

　　　　1、标量子查询(单行子查询):结果集为一行一列.

　　　　2、列子查询(多行子查询):结果集为多行一列.

　　　　3、行子查询:结果集为一行多列.

　　　　4、表子查询:结果集为多行多列.

4、子查询案列　

　　表结构如下:

　　1、谁的工资比Chen高.

SELECT last_name,salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'chen'
);

　　2、查询job_id与141号员工相同,salary比143号员工多的员工id、员工姓名、job_id、salary.

SELECT 
　　 employee_id,
　　 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、查询公司工资最少的员工的last_name,job_id和salary.

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

　　4、查询最低工资大于50号部门最低工资的部门id和其最低工资.

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

　　5、查询location_id是1400、1700或者1800的部门中的所有员工姓名.

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

　　6、查询其它工种中比job_id为IT_PROG工种任意工资低的员工的员工号、姓名、job_id 以及salary.

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

　　7、查询和Zlotkey相同部门的员工姓名和工资.

SELECT 
    last_name,
    salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);

　　8、查询工资比公司平均工资高的员工的员工号、姓名和工资.

SELECT 
employee_id,
last_name,
salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

　　9、查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资,并按照员工号升序排序.

SELECT 
    e.employee_id,
    e.last_name,
    e.salary,
    avg_dep.ag
FROM employees e
INNER JOIN(
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) AS avg_dep
ON e.department_id = avg_dep.department_id
WHERE e.salary > avg_dep.ag
ORDER BY e.employee_id ASC;

　　10、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名.

SELECT 
    e.employee_id,
    e.last_name
FROM employees e
WHERE e.department_id IN (
    SELECT DISTINCT department_id
    FROM employees 
　　 WHERE last_name LIKE '%u%'
)
AND e.last_name NOT LIKE '%u%';

　　11、查询在部门的location_id为1700的部门工作的员工号和员工名.

SELECT 
    e.employee_id,
    e.last_name
FROM employees e
WHERE department_id IN(
    (
    　　SELECT d.department_id
    　　FROM departments d
    　　WHERE d.location_id = 1700
    )
);