MySq（十六）经典案例

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

分析：

查询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

studntinfostudentNamemajoridsex

分析：

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

studntinfostudentNamemajoridsex

表result

idstudentinfoscore

分析：

查询张翠山的专业查询所有studentinfo条件是和张翠山的专业一样在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

studntinfostudentNamemajoridsex

表major

majoridmajorname

表result

idstudentinfoscore

分析：三表连接

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

studntinfostudentNamemajoridsex

表result

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