MySQL学习-子查询，分页查询，联合查询

最新推荐文章于 2022-04-25 12:45:03 发布

H_X_P

最新推荐文章于 2022-04-25 12:45:03 发布

阅读量244

点赞数

分类专栏： MySQL学习文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/H_X_P_/article/details/105332777

版权

MySQL学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

子查询

    子查询：查询在其他语句中的SELECT语句，称为子查询或内查询
        外部的查询语句称为主查询或外查询
    外部的语句可以是INSERT,UPDATE,DELETE,SELECT等
    分类：
        按结果集的行列数：
                    标量子查询（单行子查询）（结果集只有一行一列）
                              一般搭配单行操作符：>，<，=，>=，<=，<>
                    列子查询（多行子查询）（结果集只有一列多行）
                              一般搭配多行操作符：in，any/some，all
                    行子查询（结果集有一行多列）
                    表子查询（结果集一般多行多列）
        按子查询出现的位置：
                    SELECT后面
                              只支持标量子查询
                    FROM后面
                              表子查询
                    WHERE或HAVING后面
                              标量子查询
                              列子查询
                              行子查询
                    EXISTS后面（相关子查询）结果只有1和0
                              表子查询

子查询放在括号内
子查询的执行优先于主查询的执行，因为主查询用到子查询的结果

一、WHERE或HAVING后面

#标量子查询（单行子查询）

#谁的工资比Abel高

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

#查询job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

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);

#查询公司工资最少的员工的姓名，job_id，salary

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

#查询最低工资大于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) ;

#查询与姓名中包含字母u的员工相同部门的员工的员工号和姓名

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

#查询管理者是K_ing的员工的姓名和工资

SELECT last_name,salary FROM employees WHERE manager_id IN(
		SELECT employee_id FROM employees WHERE last_name='K_ing');

SELECT e.last_name,e.salary FROM employees e INNER JOIN employees m ON
		e.manager_id=m.employee_id WHERE m.last_name='K_ing';

#列子查询（多行子查询）一列多行

#查询location_id是1400或1700的部门中所有的员工姓名

SELECT last_name FROM employees INNER JOIN departments
		ON employees.department_id=departments.department_id
				WHERE location_id IN (1400,1700);

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

SELECT last_name FROM employees WHERE department_id =ANY (
		SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700));

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

SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id<>'IT_PROG' AND
		salary<ANY(SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG');
#画区间图，小于最大值
SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id<>'IT_PROG' AND
		salary<(SELECT MAX(salary) FROM employees WHERE job_id='IT_PROG');

#查询其它工种中员工工资比job_id为IT_PROG工种的所有工资都低的员工的员工号、姓名、job_id以及salary

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

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

#行子查询

#查询员工编号最小并且工资最高的员工信息

SELECT * FROM employees WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary) FROM employees);

#查询平均工资高于公司平均工资的部门有哪些

SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)>(
		SELECT AVG(salary) FROM employees);

#查询公司中所有manager的详细信息

SELECT * FROM employees WHERE employee_id IN (
		SELECT DISTINCT manager_id FROM employees);

二、SELECT后面

#查询每个部门的员工个数（包含没人的部门）

SELECT d.department_id,(
		SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id)
				FROM departments d ORDER BY d.department_id;

三、FROM后面

子查询结果作为一个表
#查询每个部门的平均工资的工资等级

SELECT tab.*,job_grades.grade_level FROM (
		SELECT department_id,AVG(salary) result FROM employees GROUP BY department_id) AS tab
				INNER JOIN job_grades ON tab.result BETWEEN lowest_sal AND highest_sal;

#查询各部门中工资比本部门平均工资高的员工的员工号、姓名、工资

SELECT employee_id,last_name,salary FROM employees AS e INNER JOIN (
		SELECT AVG(salary) AS av,department_id FROM employees GROUP BY department_id) AS tab
				ON tab.department_id=e.department_id WHERE e.salary>tab.av;

#查询平均工资最低的部门信息和该部门的平均工资

SELECT d.*,av FROM departments AS d INNER JOIN (
SELECT department_id,AVG(salary) AS av FROM employees
GROUP BY department_id ORDER BY AVG(salary) LIMIT 1) AS tab
ON tab.department_id=d.department_id;

四、EXISTS后面（相关子查询）布尔类型

#查询有员工的部门名

SELECT DISTINCT department_name FROM departments LEFT JOIN employees
		ON departments.department_id=employees.department_id
				WHERE employee_id IS NOT NULL;

SELECT department_name FROM departments WHERE EXISTS(
		SELECT * FROM employees WHERE employees.department_id=departments.department_id);

SELECT department_name FROM departments WHERE department_id IN(
		SELECT DISTINCT department_id FROM employees);

#查询各个部门的最高工资员工中工资最低者所属部门的最低工资

SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING department_id IN(
SELECT department_id FROM employees GROUP BY department_id HAVING MIN(salary) IN(
SELECT MIN(max_sal) FROM (
SELECT MAX(salary) AS max_sal FROM employees GROUP BY department_id) AS tab))

总结：
WHERE型子查询：把内部查询的结果作为外层查询的比较条件。
FROM型子查询：把内层的查询结果当成临时表，供外层再次查询。
IN子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较。
EXISTS子查询：把外层的查询结果，拿到内层，看内层是否成立。如果内层返回true，外层（也就是前面的语句）才会执行，否则不执行。
ANY子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。
ALL子查询：内层子查询返回的结果需同时满足所有内层查询条件。

分页查询

    应用场景：当要显示的数据不可以一页显示完，需要分页提交SQL请求。
    语法：
                SELECT 查询列表------------7
                FROM 表1 别名----------------1
                [连接类型 JOIN 表2 别名]—2
                [ON 连接条件]------------------3
                [WHERE 筛选条件]-----------4
                [GROUP BY 分组]-------------5
                [HAVING 筛选条件]-----------6
                [ORDER BY 排序]-------------8
                LIMIT [OFFSET,] SIZE;------9

	OFFSET：要显示条目的起始索引（起始索引从0开始）
	SIZE：要显示的条目个数
	
	要显示的页数page，每页的条目数size
	LIMIT (page-1)*size,size

#显示前五条员工信息

SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

#显示第11条~第25员工信息

SELECT * FROM employees LIMIT 10,15;

#有奖金的员工信息，并且工资较高的前10名显示出来

SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

#查询平均工资最低的部门信息

SELECT * FROM departments WHERE department_id=(
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)=(
SELECT MIN(av) FROM(
SELECT department_id,AVG(salary) AS av FROM employees GROUP BY department_id) AS tab));

#降序取第一条

SELECT * FROM departments WHERE department_id=(
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1);

联合查询

    UNION：将多条查询语句的结果合并成一个结果
    语法：
            查询语句1
            UNION
            查询语句2
            UNION
             …
    应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系
    特点：
            1.要求多条查询语句的查询列数一致
            2.要求多条查询语句查询的每一列的类型和顺序最好一致
            2.UNION默认去重，如果不想去重使用 UNION ALL

#查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';

SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';