MySq（十六）经典案例

最新推荐文章于 2022-07-26 00:40:17 发布

转载最新推荐文章于 2022-07-26 00:40:17 发布 · 184 阅读

Database - MySQL 专栏收录该内容

46 篇文章

订阅专栏

经典案例一：查询平均工资最低的部门信息

分析：

查询department_id根据每个部门的最低平均工资进行升序排序
利用limit显示第一条信息的department_id
通过department_id查询该部门的所有信息

/**1.按照部门进行分组，查询出每个部门的平均工资**/
SELECT AVG(salary) 平均工资
FROM employees
GROUP BY department_id
/**2.按照每个部门的平均工资升序排序;利用limit显示第一条信息的department_id
**/
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
/**3.通过department_id查询该部门的所有信息**/
SELECT * 
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

经典案例二：查询平均工资最低的部门信息和该部门的平均工资

分析：

查询出每个部门的平均工资和department_id并且按照平均工资的升序排序
利用limit获取第一行数据即最低的平均工资和该部门对应的department_id(将此结果集作为表：avgSalary)
将表：avgSalary和department表进行连接查询

SELECT d.* ,最低平均工资 
FROM departments d
INNER JOIN (
	SELECT AVG(salary) 最低平均工资,department_id
	FROM employees
	GROUP BY department_id
    ORDER BY 最低平均工资 ASC
    LIMIT 1
) avgSalary
ON avgSalary.'department_id' = d.'department_id';

经典案例三：查询平均工资最高的job信息

分析：

查询出job_id根据每个部门的平均工资进行降序排序
利用limit获取第一行数据，即最高平均工资的job_id
通过job_id获取该工种的所有信息

/**1,2**/
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
/**3**/
SELECT * 
FROM job
WHERE job_id = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

经典案例四：查询平均工资最高的job信息和对应的最高工资

分析：

查询每个工种的平均工资和job_id，降序排序
Limit显示job_id，最高平均工资
获取该job_id的所有信息

/**1. 查询每个工种的平均工资和job_id，降序排序
	2. Limit显示job_id,和对应的最低工资
	将此结果集作为表：avgSalary
**/
SELECT AVG(salary),job_id
FROM employees
GROUP BY AVG(salary) ASC
LIMIT 1
/**将表：avgSalary和job表进行连接**/
SELECT * 
FROM job
INNER JOIN (
	SELECT AVG(salary),job_id
	FROM employees
	GROUP BY job_id
    ORDER BY AVG(salary) DESC
	LIMIT 1
) avgSalary
ON avgSalary.'job_id' = job.'job_id';

经典案例五：查询平均工资高于公司平均工资的部门有哪些

分析

查询公司的平均平均工资（条件1）
查询每个部门的平均工资（表avgSalary）
在表avgSalary的基础上筛选条件1

SELECT AVG(salary) 平均工资,department_id
FROM employees
GROUP BY department_id
HAVING 平均工资 > (
	SELECT AVG(salary)
    FROM employees
)；

经典案例六：查询出公司中所有manager的详细信息

分析：

查询出employees表中所有manager_id(这里manager_id可能会重复，为了提高查询效率可以进行去重操作)（表1）
从employees表中筛选出emplooyee_id在表1中的所有信息

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

经典案例七：查询各部门中最高工资中最低那个部门的最低工资是多少

分析：将每个部门的最高工资组成一个表1–>查询表1中最低工资的那个部门–>查询该部门中的最低工资

查询各部门中最高的工资和对应的department_id，并按照工资的升序排列
limit获取第一行即最高工资中最低工资和对应的department_id
根据该department_id获取该部门的最低工资

/**1和2**/
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1
/**3**/
SELECT department_id,MIN(salary)
FROM employees
WHERE employee_id = (
	SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) ASC
    LIMIT 1
);

经典案例八:查询平均工资最高的部门的manager(领导)的详细信息：last_name、department_id、 email、salary

注：一个部门编号对应一个部门，一个部门对应一个manager_id；一个manager_id对应一个领导

分析：

查询平均工资最高的department_id；并按照平均工资的降序排序
limit获取第一行即平均工资最高的department_id
将employees表和departments表进行连接，筛选条件是1,2

/**1和2**/
SELECT department_id
FROM employees
GROUP BY department_id DESC
ORDER BY AVG(salary)
LIMIT 1
/****/
SELECT last_name,e.department_id,email,salary
FROM employees e
INNER JOIN departments d
ON e.employee_id = d.manager_id	
WHERE d.department_id = (
	SELECT department_id
    FROM employees
    GROUP BY department_id 
    ORDER BY AVG(salary) DESC
    LIMIT 1
);

经典案例九：查询每个专业的男生人数和女生人数

表student

studntinfo	studentName	majorid	sex

分析：

SELECT major_id
    (SELECT COUNTN(*) FROM student GROUP BY majorid 
     WHERE sex = '男'AND majorid =s.majorid) 男,
    (SELECT COUNTN(*) FROM student GROUP BY majorid 
     WHERE sex = '女'AND majorid = s.majorid) 女,
FROM student s
GROUP majorid;

经典案例十：查询专业和张翠山一样的学生的最低分

表student

studntinfo	studentName	majorid	sex

表result

id	studentinfo	score

SELECT COUNT(*)
FROM student s
LEFT JOIN result r
ON s.studentinfo = r.studentinfo
WHERE r.id IS NULL;

本文原文地址：https://blog.youkuaiyun.com/qq_43952245/article/details/90546153