MySq（十六）经典案例

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

分析：

1. 查询department_id根据每个部门的最低平均工资进行升序排序
2. 利用limit显示第一条信息的department_id
3. 通过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
);

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

分析：

1. 查询出每个部门的平均工资和department_id并且按照平均工资的升序排序
2. 利用limit获取第一行数据即最低的平均工资和该部门对应的department_id(将此结果集作为表：avgSalary)
3. 将表：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信息

分析：

1. 查询出job_id根据每个部门的平均工资进行降序排序
2. 利用limit获取第一行数据，即最高平均工资的job_id
3. 通过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信息和对应的最高工资

分析：

1. 查询每个工种的平均工资和job_id，降序排序
2. Limit显示job_id，最高平均工资
3. 获取该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. 查询公司的平均平均工资（条件1）
2. 查询每个部门的平均工资（表avgSalary）
3. 在表avgSalary的基础上筛选条件1

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

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

分析：

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

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

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

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

1. 查询各部门中最高的工资和对应的department_id，并按照工资的升序排列
2. limit获取第一行即最高工资中最低工资和对应的department_id
3. 根据该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对应一个领导

分析：

1. 查询平均工资最高的department_id；并按照平均工资的降序排序
2. limit获取第一行即平均工资最高的department_id
3. 将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

分析：

1. 查询张翠山的专业
2. 查询所有studentinfo条件是和张翠山的专业一样
3. 在2的基础上查询他们的最低成绩

/**1**/
SELECT major_id
FROM student
WHERE name = '张翠山'
/**2**/
SELECT studentinfo
FROM student
WHERE major_id = (
	SELECT major_id
    FROM student
    WHERE name = '张翠山'
)
/**3**/
SELECT MIN(score)
FROM result
WHERE studentinfo IN(
    SELECT studentinfo
    FROM student
    WHERE major_id = (
            SELECT major_id
            FROM student
            WHERE name = '张翠山'
        )
)

案例十一：查询大于60分的学生的姓名、专业名

表student

studntinfo	studentName	majorid	sex

表major

majorid	majorname

表result

id	studentinfo	score

分析：三表连接

SELECT studentname,majorName
FROM student s
JOIN major j ON s.majorid = j.majorid
JOIN result r ON s.studentinfo = r.studentinfo
WHERE r.score > 60;

经典案例十二：查询哪个专业没有学生（分别用左连和右连来实现）

/**左连接**/
SELECT m.majorid,m.majorname,s.studentinfo
FROM major m
LEFT JOIN student s
ON m.majorid = s.majorid
WHERE s.studentinfo IS NULL;
/**右连接**/
SELECT m.majorid,m.majorname,s.studentinfo
FROM student s
RIGHT JOIN major m
ON m.majorid = s.majorid
WHERE s.studentinfo IS NULL;

经典例题十三：查询没有成绩的学生人数

表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;