MySQL复习笔记（九）：分页查询（LIMIT）、联合查询（UNION）

分页查询

应用场景：要显示的数据一页显示不完，需要分页提交sql请求

语法：

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

特点：

• offset表示要显示条目的起始索引，索引从0开始，为0时可以省略
• size表示要获取的条目数目
• limit语句要放在查询语句的最后
• 公式：
  • 要显示的页数page，每页显示size则的
  • LIMIT (page-1)*size, size

1、查询前五条员工信息

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

2、查询第11条到第25条员工信息

SELECT * FROM employees LIMEIT 10,15;

3、显示有奖金的员工中，工资最高的前10名

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

练习

#查询所有学员的邮箱用户名（@之前的字符串）
SELECT SUBSTR(email,1,LENGTH(email) - 8) 
FROM stuinfo;


#分别查询男女生的个数
SELECT count(*), sex
FROM stuinfo
GROUP BY sex;


#查询年龄>18岁的所有学生的姓名和年级名称
SELECT name, gradeName
FROM stuinfo s
INNER JOIN grade g
ON s.gradeID = g.gradeID
WHERE age > 18


#查询哪个年级的学生最小年龄>20岁
SELECT gradeID, min(age)
FROM stuinfo
GROUP BY gradeID
HAVING min(age) > 20

LIMIT的妙用

使用LIMIT 1和GROUP BY、ORDER BY结合子查询可以找到数据中avg平均（max最大、min最小）的最大小值。

例子：

#查询工资最低的员工的信息：last_name,salary
SELECT last_name, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

#查询平均工资最低的部门信息
SELECT *
FROM departments 
WHERE department_id = (
	#技巧使用LIMIT配合ORDER BY获取最大最小值
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)


#查询平均工资最低的部门信息和该部门的平均工资
SELECT * 
FROM departments d
JOIN (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id;



#查找平均工资最高的job信息
SELECT *
FROM jobs
WHERE job_id = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
)


#查询平均工资高于公司平均工资的部门有哪些？
SELECT AVG(salary), 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 MIN(salary)
FROM employees
WHERE department_id = (
	#找到最高工资最低的那个部门编号
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
)


#查询平均工资最高的部门的manager的详细信息：last_name,department_id,salary
SELECT last_name,department_id,salary
FROM employees
WHERE employee_id = (
	SELECT manager_id
	FROM departments 
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
	)
)

联合查询

union(联合、合并)：将多条查询语句的结果合并为一个结果

语法：

查询语句1
union
查询语句2
union
...

应用场景：要查询的信息来自多个表，而且多个表之间没有直接的连接关系，但需要查询的信息一致

特点：

• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的每一列的类型和顺序要一致
• union关键字默认去重，可以使用union all来获取所有值

例子：

两个表分别包含国内外用户的姓名和性别信息

查询中国用户男性的信息和国外用户男性的信息

SELECT id,cname FROM t_ca WHERE csex = '男'
UNION
SELECT t_id,tName FROM t_ua WHERE tGender = 'male';

子：

[外链图片转存中…(img-4aKgj4bE-1587822224467)]

两个表分别包含国内外用户的姓名和性别信息

查询中国用户男性的信息和国外用户男性的信息

SELECT id,cname FROM t_ca WHERE csex = '男'
UNION
SELECT t_id,tName FROM t_ua WHERE tGender = 'male';