MySQL——子查询,分页查询

2021/9/29 2:11:03

本文主要是介绍MySQL——子查询,分页查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1、子查询?

1.1、什么是子查询?
select语句中嵌套select语句,被嵌套的select语句称为子查询。

1.2、子查询都可以出现在哪里呢?

	select
		..(select).
	from
		..(select).
	where
		..(select).

1.3、where子句中的子查询

	案例:找出比最低工资高的员工姓名和工资?
		select 
			ename,sal
		from
			emp 
		where
			sal > min(sal);

		ERROR 1111 (HY000): Invalid use of group function
		where子句中不能直接使用分组函数。
	
	实现思路:
		第一步:查询最低工资是多少
			select min(sal) from emp;
			+----------+
			| min(sal) |
			+----------+
			|   800.00 |
			+----------+
		第二步:找出>800的
			select ename,sal from emp where sal > 800;
		
		第三步:合并
			select ename,sal from emp where sal > (select min(sal) from emp);
			+--------+---------+
			| ename  | sal     |
			+--------+---------+
			| ALLEN  | 1600.00 |
			| WARD   | 1250.00 |
			| JONES  | 2975.00 |
			| MARTIN | 1250.00 |
			| BLAKE  | 2850.00 |
			| CLARK  | 2450.00 |
			| SCOTT  | 3000.00 |
			| KING   | 5000.00 |
			| TURNER | 1500.00 |
			| ADAMS  | 1100.00 |
			| JAMES  |  950.00 |
			| FORD   | 3000.00 |
			| MILLER | 1300.00 |
			+--------+---------+

1.4、from子句中的子查询
	注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)

	案例:找出每个岗位的平均工资的薪资等级。

	第一步:找出每个岗位的平均工资(按照岗位分组求平均值)
		select job,avg(sal) from emp group by job;
		+-----------+-------------+
		| job       | avgsal      |
		+-----------+-------------+
		| ANALYST   | 3000.000000 |
		| CLERK     | 1037.500000 |
		| MANAGER   | 2758.333333 |
		| PRESIDENT | 5000.000000 |
		| SALESMAN  | 1400.000000 |
		+-----------+-------------+t表

	第二步:克服心理障碍,把以上的查询结果就当做一张真实存在的表t。
	mysql> select * from salgrade; s表
	+-------+-------+-------+
	| GRADE | LOSAL | HISAL |
	+-------+-------+-------+
	|     1 |   700 |  1200 |
	|     2 |  1201 |  1400 |
	|     3 |  1401 |  2000 |
	|     4 |  2001 |  3000 |
	|     5 |  3001 |  9999 |
	+-------+-------+-------+
	t表和s表进行表连接,条件:t表avg(sal) between s.losal and s.hisal;
		
		select 
			t.*, s.grade
		from
			(select job,avg(sal) as avgsal from emp group by job) t
		join
			salgrade s
		on
			t.avgsal between s.losal and s.hisal;
		
		+-----------+-------------+-------+
		| job       | avgsal      | grade |
		+-----------+-------------+-------+
		| CLERK     | 1037.500000 |     1 |
		| SALESMAN  | 1400.000000 |     2 |
		| ANALYST   | 3000.000000 |     4 |
		| MANAGER   | 2758.333333 |     4 |
		| PRESIDENT | 5000.000000 |     5 |
		+-----------+-------------+-------+

1.5、select后面出现的子查询(这个内容不需要掌握,了解即可!!!)

案例:找出每个员工的部门名称,要求显示员工名,部门名?
	select 
		e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
	from 
		emp e;


	+--------+--------+------------+
	| ename  | deptno | dname      |
	+--------+--------+------------+
	| SMITH  |     20 | RESEARCH   |
	| ALLEN  |     30 | SALES      |
	| WARD   |     30 | SALES      |
	| JONES  |     20 | RESEARCH   |
	| MARTIN |     30 | SALES      |
	| BLAKE  |     30 | SALES      |
	| CLARK  |     10 | ACCOUNTING |
	| SCOTT  |     20 | RESEARCH   |
	| KING   |     10 | ACCOUNTING |
	| TURNER |     30 | SALES      |
	| ADAMS  |     20 | RESEARCH   |
	| JAMES  |     30 | SALES      |
	| FORD   |     20 | RESEARCH   |
	| MILLER |     10 | ACCOUNTING |
	+--------+--------+------------+

	//错误:ERROR 1242 (21000): Subquery returns more than 1 row
	select 
		e.ename,e.deptno,(select dname from dept) as dname
	from
		emp e;
	
	注意:对于select后面的子查询来说,这个子查询只能一次返回1条结果,
	多于1条,就报错了。!

2、union合并查询结果集

案例:查询工作岗位是MANAGER和SALESMAN的员工?
	select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
	select ename,job from emp where job in('MANAGER','SALESMAN');
	+--------+----------+
	| ename  | job      |
	+--------+----------+
	| ALLEN  | SALESMAN |
	| WARD   | SALESMAN |
	| JONES  | MANAGER  |
	| MARTIN | SALESMAN |
	| BLAKE  | MANAGER  |
	| CLARK  | MANAGER  |
	| TURNER | SALESMAN |
	+--------+----------+
	
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,job from emp where job = 'SALESMAN';
	
	+--------+----------+
	| ename  | job      |
	+--------+----------+
	| JONES  | MANAGER  |
	| BLAKE  | MANAGER  |
	| CLARK  | MANAGER  |
	| ALLEN  | SALESMAN |
	| WARD   | SALESMAN |
	| MARTIN | SALESMAN |
	| TURNER | SALESMAN |
	+--------+----------+

	union的效率要高一些。对于表连接来说,每连接一次新表,
	则匹配的次数满足笛卡尔积,成倍的翻。。。
	但是union可以减少匹配的次数。在减少匹配次数的情况下,
	还可以完成两个结果集的拼接。

	a 连接 b 连接 c
	a 10条记录
	b 10条记录
	c 10条记录
	匹配次数是:1000

	a 连接 b一个结果:10 * 10 --> 100次
	a 连接 c一个结果:10 * 10 --> 100次
	使用union的话是:100次 + 100次 = 200次。(union把乘法变成了加法运算)

union在使用的时候有注意事项吗?

	//错误的:union在进行结果集合并的时候,要求两个结果集的列数相同。
	select ename,job from emp where job = 'MANAGER'
	union
	select ename from emp where job = 'SALESMAN';

	// MYSQL可以,oracle语法严格 ,不可以,报错。要求:结果集合并时列和列的数据类型也要一致。
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,sal from emp where job = 'SALESMAN';
	+--------+---------+
	| ename  | job     |
	+--------+---------+
	| JONES  | MANAGER |
	| BLAKE  | MANAGER |
	| CLARK  | MANAGER |
	| ALLEN  | 1600    |
	| WARD   | 1250    |
	| MARTIN | 1250    |
	| TURNER | 1500    |
	+--------+---------+

3、limit(非常重要)
	
3.1、limit作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
百度默认:一页显示10条记录。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差。
可以一页一页翻页看。

3.2、limit怎么用呢?

	完整用法:limit startIndex, length
		startIndex是起始下标,length是长度。
		起始下标从0开始。

	缺省用法:limit 5; 这是取前5.

	按照薪资降序,取出排名在前5名的员工?
	select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 5; //取前5

	select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 0,5;

	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| KING  | 5000.00 |
	| SCOTT | 3000.00 |
	| FORD  | 3000.00 |
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	+-------+---------+

3.3、注意:mysql当中limit在order by之后执行!!!!!!

3.4、取出工资排名在[3-5]名的员工?
	select 
		ename,sal
	from
		emp
	order by
		sal desc
	limit
		2, 3;
	
	2表示起始位置从下标2开始,就是第三条记录。
	3表示长度。

	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| FORD  | 3000.00 |
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	+-------+---------+

3.5、取出工资排名在[5-9]名的员工?
	select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit
		4, 5;

	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| BLAKE  | 2850.00 |
	| CLARK  | 2450.00 |
	| ALLEN  | 1600.00 |
	| TURNER | 1500.00 |
	| MILLER | 1300.00 |
	+--------+---------+

3.6、分页

每页显示3条记录
	第1页:limit 0,3		[0 1 2]
	第2页:limit 3,3		[3 4 5]
	第3页:limit 6,3		[6 7 8]
	第4页:limit 9,3		[9 10 11]

每页显示pageSize条记录
	第pageNo页:limit (pageNo - 1) * pageSize  , pageSize

	public static void main(String[] args){
		// 用户提交过来一个页码,以及每页显示的记录条数
		int pageNo = 5; //第5页
		int pageSize = 10; //每页显示10条

		int startIndex = (pageNo - 1) * pageSize;
		String sql = "select ...limit " + startIndex + ", " + pageSize;
	}

记公式:
	limit (pageNo-1)*pageSize , pageSize

4、关于DQL语句的大总结:
	select 
		...
	from
		...
	where
		...
	group by
		...
	having
		...
	order by
		...
	limit
		...
	
	执行顺序?
		1.from
		2.where
		3.group by
		4.having
		5.select
		6.order by
		7.limit..


这篇关于MySQL——子查询,分页查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程