【Mysql】三、Mysql基础篇 --- 查询

子查询

一、WHERE 子查询

1、单行子查询

案例1：谁的工资比w.deng高

-- 1、查询w.deng的工资
SELECT salary FROM users WHERE username='w.deng'
-- 2、查询工资大于w.deng的salary
SELECT * FROM users 
WHERE salary > (SELECT salary FROM users WHERE username='w.deng')

案例2：查询和141号员工同部门（dep_id）并且工资比143号工资高的所有员工

-- 1、查询141号id的员工的dep_id
SELECT dep_id FROM users WHERE id=141
-- 2、查询143号id的员工的salary
SELECT salary FROM users WHERE id=143
-- 3、查询语句
SELECT * FROM users 
WHERE dep_id=(SELECT dep_id FROM users WHERE id=141)
			AND salary>(SELECT salary FROM users WHERE id=143)

案例3：查询工资最少的员工

SELECT * FROM users WHERE salary=(SELECT MIN(salary) FROM users)

案例4：查询比部门10最低薪资高的所有员工

SELECT * FROM users 
WHERE salary > (SELECT MIN(salary) FROM users where dep_id=10)

2、列子查询

案例1：loc_id为1400和1700下所有部门员工姓名

SELECT * FROM users
WHERE dep_id IN (SELECT dep_id FROM users WHERE loc_id IN (1400,1700))

3、行子查询

用处不多,不需要了解

SELECT * FROM users WHERE (dep_id,id)=(SELECT MAX(dep_id), MIN(id) FROM users)

二、SELECT 子查询

select后面的子查询支持单行单列

SELECT (
	SELECT porjects.name FROM users
	LEFT JOIN projects ON projects.user_id = users.id
	WHERE users.id=102
) 102用户负责项目名称

三、FROM 子查询

使用子查询 每个部门平均薪资的薪资等级

-- salary_grade 员工等级表
SELECT avg_dep.*, salary_grade.grade 
FROM (
	SELECT AVG(salary) AS avg_sal, dep_id
	FROM users
	GROUP BY dep_id
) avg_dep
INNER JOIN salary_grade ON avg_dep.avg_sal BETWEEN salary_grade.lower_sal AND salary_grade.high_sal

四、EXISTS 子查询

查询存在结果集就返回1，反之不存在返回0
存在结果集

	SELECT EXISTS(SELECT * FROM users)

不存在结果集

	SELECT EXISTS(SELECT * FROM users WHERE username='22222')

子查询案例分析

1、查询各部门比本部门平均工资高的员工

-- 	1、查询各部门的平均工资
SELECT AVG(salary) avg_salary, dep_id
FROM users
GROUP BY dep_id

-- 2、查询各部门比本平均工资高的员工
SELECT * FROM users
INNER JOIN (
	SELECT AVG(salary) avg_salary, dep_id
	FROM users
	GROUP BY dep_id
) avg_salary_dep 
ON avg_salary_dep.dep_id = users.dep_id AND users.salary > avg_salary_dep.avg_salary

2、查询ring管理者的所有员工姓名

SELECT * FROM users
WHERE margin_id IN (SELECT id FROM users WHERE username='ring')

3、查询工资最低的员工姓名和工资

-- 方法1：
SELECT * FROM (SELECT MIN(salary) sal FROM users) user_min_salary
INNER JOIN users ON user_min_salary.sal = users.salary
-- 方法2：
SELECT * FROM users 
WHERE salary = (SELECT MIN(salary) sal FROM users)

4、查询平均工资最低的部门

-- 方法1：
SELECT * 
FROM (SELECT AVG(salary) avg_sal, dep_id FROM users GROUP BY dep_id) dep_avg
INNER JOIN dep ON dep.id = dep_avg.dep_id 
ORDER BY avg_sal ASC LIMIT 1

-- 方法2：
SELECT * FROM dep
WHERE id=( 
	SELECT dep_id FROM users 
	GROUP BY dep_id 
	ORDER BY AVG(salary)
	LIMIT 1 
)

5、查询各部门的最高工资

-- 查询各部门的最高工资
SELECT * FROM dep
INNER JOIN (
	SELECT MAX(salary), dep_id FROM users 
	GROUP BY dep_id
) max_sal ON max_sal.dep_id = dep.id

6、那个部门最低工资>5000

-- 那个部门最低工资>5000
SELECT MIN(salary) min_sal, dep_id FROM users
GROUP BY dep_id
HAVING min_sal > 5000

分页查询

关键词：limit

案例1：查询users表前两条数据

-- SELECT * FROM users LIMIT offset,size   offset：启始条目位置（非id）   size：显示条目个数
SELECT * FROM users LIMIT 0,2

--如果起始位置是第一条，可省略offset
SELECT * FROM users LIMIT 2

查询结果：

案例2：分页公式
(page-1)*size,size

SELECT * FROM users LIMIT (page-1)*size,size

联合查询

UNION ALL 联合查询 将多条语句合并成一个结果集
查询规则：

1. 多条查询列数是一致的
2. 字段名默认第一条语句的字段名称
3. 多条语句查询列顺序保持一致
4. UNION默认去重，UNION ALL显示所有结果，包含重复的项

案例1：普通查询和联合查询

-- 普通查询
SELECT * FROM users WHERE username LIKE "zhang%" OR salary > 1500

-- 两种查询 结果集相等

-- 联合查询
SELECT * FROM users WHERE username LIKE "zhang%"
UNION
SELECT * FROM users WHERE salary > 1500

行转列、列转行

1. 数据准备

CREATE TABLE students (
	id INT NOT NULL auto_increment PRIMARY KEY,
	name VARCHAR(20),
	subject VARCHAR(20),
	score int
)
INSERT INTO students (name, subject, score) VALUES ('小明', '语文', 80),('小明', '英语', 40),('小明', '数学', 90),('阿花', '语文', 99),('阿花', '英语', 90),('阿花', '数学', 10)

2. 行转列

-- 创建视图，行转列做准备
-- CREATE VIEW max_source
SELECT 
	name,
	MAX(
		CASE subject
			WHEN '语文'
			THEN score
			ELSE 0
		END
	) AS '语文',
	MAX(
		CASE subject
			WHEN '英语'
			THEN score
			ELSE 0
		END
	) AS '英语',
	MAX(
		CASE subject
			WHEN '数学'
			THEN score
			ELSE 0
		END
	) AS '数学'
FROM students 
GROUP BY name

3. 列转行

SELECT name, '语文' AS subject, MAX(`语文`) AS score FROM max_source
GROUP BY name
UNION ALL
SELECT name, '数学' AS subject, MAX(`数学`) AS score FROM max_source
GROUP BY `name`
UNION ALL
SELECT name, '英语' AS subject, MAX(`英语`) AS score FROM max_source
GROUP BY `name`

4. 扩展联系

   每科最高分是多少

   -- 方法1:mysql传统方法子查询
   SELECT students.name, students.subject, students.score FROM (
   	SELECT subject, MAX(score) AS ms FROM students GROUP BY subject
   ) max_sorce
   LEFT JOIN students ON students.subject = max_sorce.subject AND max_sorce.ms = students.score
   
   -- 方法2:mysql8.0新特性，OVER()
   SELECT subject, name, score, RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rank_num FROM students