MySQL学习（9）|分页查询_mysql 查询user表,age大于18,查询第三页返回10 条

进阶8 分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交SQL请求
语法：

SELECT 查询列表
FROM 表
【JOIN TYPE JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段】
LIMIT 【OFFSET,】size;
1
2
3
4
5
6
7
8
9

OFFSET 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

特点：
①LIMIT语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
SELECT 查询列表
FROM 表
LIMIT (page-1)*size,size;

案例1：查询前五条员工信息

SELECT *
FROM `employees`
LIMIT 5;
1
2
3

案例2：查询第11条——第25条

SELECT *
FROM `employees`
LIMIT 10,15;
1
2
3

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

SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
1
2
3
4
5

测试题1

已知表 stuinfo

已知表 grade

一、查询所有学员的邮箱的用户名（注：邮箱中@前面的字符）

SELECT SUBSTR(email,1,INSTR(email,"@")-1) AS 用户名
FROM stuinfo;
1
2

二、查询男生和女生的个数

SELECT COUNT(*) AS 个数,sex
FROM stuinfo
GROUP BY sex;
1
2
3

三、查询年龄>18岁的所有学生的姓名和年级名称

SELECT name_a,grade_name
FROM stuinfo AS s
INNER JOIN grade AS g
ON s.grade_Id=g.id
WHERE age>18;
1
2
3
4
5

四、查询哪个年级的学生最小年龄>20岁

SELECT grade_name
FROM grade g
WHERE g.id=(
	SELECT s.grade_Id
	FROM stuinfo AS s
	GROUP BY s.grade_Id
	HAVING MIN(age)>20
);
1
2
3
4
5
6
7
8

五、试说出查询语句中涉及到的所有的关键字，以及执行先后顺序

测试题2

1.查询工资最低的员工信息：last_name，salary

SELECT `last_name`,`salary`
FROM `employees`
WHERE `salary`=(
	SELECT MIN(salary)
	FROM `employees`
);
1
2
3
4
5
6

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

SELECT d.*
FROM `departments` d
WHERE d.`department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(salary) ASC
	LIMIT 1
);
1
2
3
4
5
6
7
8
9

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

SELECT d.*,AVG(salary)
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE d.`department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(salary) ASC
	LIMIT 1
);
1
2
3
4
5
6
7
8
9
10
11

等价

SELECT d.*,a.avg_gz
FROM `departments` AS d
INNER JOIN (
	SELECT AVG(salary) AS avg_gz,`department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(salary) ASC
	LIMIT 1
) AS a
ON d.`department_id` =a.`department_id`;
1
2
3
4
5
6
7
8
9
10

4.查询平均工资最高的job信息

SELECT j.*
FROM `jobs` AS j
WHERE `job_id`=(
	SELECT `job_id`
	FROM `employees`
	GROUP BY `job_id`
	ORDER BY AVG(salary) DESC
	LIMIT 1);
1
2
3
4
5
6
7
8

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

SELECT a.*
FROM (
   SELECT AVG(salary) AS avg_gz,`department_id` 
   FROM `employees` 
   GROUP BY `department_id`) AS a 
WHERE a.avg_gz > (
   SELECT AVG(`salary`) 
   FROM `employees`) ;
1
2
3
4
5
6
7
8

等价

SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM `employees`
);
1
2
3
4
5
6
7

6.查询出公司中所有manager的详细信息

SELECT *
FROM `employees` AS e
WHERE e.`employee_id` IN(
	SELECT DISTINCT `manager_id`
	FROM `employees`
)
1
2
3
4
5
6

7.各个部门中最高工资中最低的那个部门的最低工资是多少

SELECT MIN(`salary`),`department_id`
FROM `employees` e
WHERE e.`department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY MAX(salary)
	LIMIT 1
);
1
2
3
4
5
6
7
8
9

8.查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary

SELECT `last_name`,e.`department_id`,`email`,`salary`
FROM `departments` AS d
INNER JOIN `employees` AS e
ON d.`manager_id`=e.`employee_id`
WHERE d.`department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY MAX(salary) DESC
	LIMIT 1
);
1
2
3
4
5
6
7
8
9
10
11

测试题3

一、查询每个专业的学生人数

SELECT COUNT(*),`majorname`
FROM `major` AS m
INNER JOIN `student` AS s
ON m.`majorid`=s.`majorid`
GROUP BY s.`majorid`;
1
2
3
4
5

二、查询参加考试的学生中，每个学生的平均分、最高分

SELECT ROUND(AVG(`score`),2),MAX(`score`),`studentno`
FROM `result`
GROUP BY `studentno`;
1
2
3

三、查询姓张的每个学生的最低分大于60的学号、姓名

SELECT s.`studentno`,`studentname`,MIN(`score`)
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE "张%"
GROUP BY s.`studentno`
HAVING MIN(`score`)>60;
1
2
3
4
5
6
7

四、查询生日在“1988-1-1”后的学生姓名、专业名称

SELECT s.`studentname`,`majorname`
FROM `student` s
INNER JOIN `major` m
ON s.`majorid`=m.`majorid`
WHERE  DATEDIFF(`borndate`,"1988-1-1")>0;
1
2
3
4
5

五、查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*) AS 人数,`sex`,`majorid`
FROM `student`
GROUP BY `majorid`,`sex`
等级
SELECT `majorid`,(
	SELECT COUNT(*)
	FROM `student`
	WHERE sex="男"
	AND `majorid`=s.`majorid`) AS 男,
	(SELECT COUNT(*)
	FROM `student`
	WHERE sex="女"
	AND `majorid`=s.`majorid`) AS 女
FROM `student` s
GROUP BY `majorid`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

#注意SQL执行顺序
六、查询专业和张翠山一样的学生的最低分

SELECT MIN(`score`)
FROM `result` AS r
WHERE `studentno`=ANY(
	SELECT `studentno`
	FROM `student` 
	WHERE `majorid`=(
		SELECT `majorid`
		FROM `student`
		WHERE `studentname`="张翠山"));
1
2
3
4
5
6
7
8
9

七、查询大于60分的学生的姓名、密码、专业名

SELECT `studentname`,`loginpwd`,`majorname`
FROM `student` s
INNER JOIN `major` m
ON s.`majorid`=m.`majorid`
WHERE `studentno` IN(
	SELECT `studentno`
	FROM `result`
	WHERE `score`>60
);
1
2
3
4
5
6
7
8
9

八、按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*),LENGTH(`email`)
FROM `student`
GROUP BY LENGTH(`email`);
1
2
3

九、查询学生名、专业名、分数

SELECT `studentname`,`majorname`,`score`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
LEFT JOIN `major` m
ON s.`majorid`=m.`majorid`;
1
2
3
4
5
6

十、查询哪个专业没有学生，分别用左连接和右连接实现

#左连
SELECT m.`majorid`,`majorname`,s.`studentno`
FROM `major` AS m
LEFT JOIN `student` AS s
ON m.`majorid`=s.`majorid`
WHERE s.`studentno` IS NULL;
#右连
SELECT m.`majorid`,`majorname`,s.`studentno`
FROM `student` AS s
RIGHT JOIN `major` AS m
ON m.`majorid`=s.`majorid`
WHERE s.`studentno` IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12

十一、查询没有成绩的学生人数

SELECT COUNT(*)
FROM `student`
WHERE `studentno`<>ALL(
	SELECT DISTINCT s.`studentno`
	FROM `student` AS s,`result` AS r
	WHERE s.`studentno`=r.`studentno`
)
#或者
SELECT COUNT(*)
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE r.`id` IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13

待