MySq（十六）经典案例

我不是女神ヾ 2022-01-07 10:17 261阅读 0赞

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

> 分析：
> 
> 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
    )；

![5w9y5d.jpg][]

经典案例六：查询出公司中所有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中最低工资的那个部门–>查询该部门中的最低工资
> 
> 查询各部门中最高的工资和对应的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对应一个领导

> 分析：
> 
> 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

<table style="width:638px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;">studntinfo</th> 
   <th style="text-align:center;">studentName</th> 
   <th style="width:149px;text-align:center;">majorid</th> 
   <th style="width:170px;text-align:center;">sex</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">&nbsp;</td> 
   <td style="text-align:center;">&nbsp;</td> 
   <td style="text-align:center;width:149px;">&nbsp;</td> 
   <td style="text-align:center;width:170px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

> 分析：

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

<table style="width:656px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;">studntinfo</th> 
   <th style="text-align:center;">studentName</th> 
   <th style="width:166px;text-align:center;">majorid</th> 
   <th style="width:170px;text-align:center;">sex</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">&nbsp;</td> 
   <td style="text-align:center;">&nbsp;</td> 
   <td style="text-align:center;width:166px;">&nbsp;</td> 
   <td style="text-align:center;width:170px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

表result

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;">id</th> 
   <th style="text-align:center;">studentinfo</th> 
   <th style="text-align:center;">score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">&nbsp;</td> 
   <td style="text-align:center;">&nbsp;</td> 
   <td style="text-align:center;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

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

本文原文地址：[https://blog.csdn.net/qq\_43952245/article/details/90546153][https_blog.csdn.net_qq_43952245_article_details_90546153]

[5w9y5d.jpg]: /images/20211223/9e25a6b28f914223be18594691d10cd5.png
[https_blog.csdn.net_qq_43952245_article_details_90546153]: https://blog.csdn.net/qq_43952245/article/details/90546153