MySQL高级查询

一、概述

　　主要记录一下mysql的高级查询，即使日后自己忘记了也能回顾一下。

二、示例

　　数据库表设计：

　　1.部门表：department

　　2.员工表:employee

 

　　案例1.查询各个部门的最高工资、最低工资、平均工资

SELECT (SELECT dept_name FROM department WHERE dept_id=e.dept_id) '部门', MAX(salary) '最高工资',MIN(salary) '最低工资',AVG(salary) '平均工资' FROM employee e GROUP BY dept_id
查询结果

 

　　案例2.查询平均工资大于30000的部门

SELECT (SELECT dept_name FROM department WHERE dept_id=e.dept_id) '部门', MAX(salary) '最高工资',MIN(salary) '最低工资',AVG(salary) '平均工资' FROM employee e GROUP BY dept_id HAVING AVG(salary)>30000
查询结果

 　　案例3.查询部门列表，并把部门下的员工列表带出来，并且统计部门下员工总数

SELECT d.dept_id,d.dept_name,e.*,
        (SELECT COUNT(e.dept_id) FROM employee e WHERE e.dept_id=d.dept_id) AS COUNT
        FROM department d LEFT JOIN employee e ON d.dept_id=e.dept_id
查询结果

 postman中的样式

{
    "code": 200,
    "msg": "查询部门全部信息列表，并且统计每个部门中的员工信息总数",
    "data": [
        {
            "deptId": "00d3f648-8a8a-430b-968a-5e73457cbc67",
            "deptName": "行政部",
            "count": 3,
            "avgSalary": null,
            "employees": [
                {
                    "empId": "0a3be641-9998-4a2e-8a1e-355293c9fa7d",
                    "empName": "荀彧",
                    "salary": 50000.0,
                    "department": null
                },
                {
                    "empId": "14ea7efb-487c-4410-85be-8f1918c637cf",
                    "empName": "牛头人族长",
                    "salary": 50000.0,
                    "department": null
                },
                {
                    "empId": "1f8142d8-70d4-469a-8b44-c3ebfeae1304",
                    "empName": "白骨精",
                    "salary": 20000.0,
                    "department": null
                }
            ]
        },
        {
            "deptId": "0bee0243-f193-4ee4-8906-c15be8f0b67e",
            "deptName": "运营部",
            "count": 4,
            "avgSalary": null,
            "employees": [
                {
                    "empId": "27a08532-35c9-4a53-b5c7-8cd568fc09de",
                    "empName": "白龙马",
                    "salary": 500.0,
                    "department": null
                },
                {
                    "empId": "294faee1-7198-43d2-9426-5927e59a62dd",
                    "empName": "孙权",
                    "salary": 60000.0,
                    "department": null
                },
                {
                    "empId": "3a0b78a0-5e45-40b9-a2d9-cca5feb58ad9",
                    "empName": "艾瑞莉娅",
                    "salary": 3000.0,
                    "department": null
                },
                {
                    "empId": "42be21a7-c2d3-4256-847c-8e53e2d7ec45",
                    "empName": "司马懿",
                    "salary": 30000.0,
                    "department": null
                }
            ]
        }
    ]
}

 

 　　批量：增删改　　

批量增删改：使用foreach语句
<!--    批量插入-->
    <insert id="bathEmployee">
        insert into employee (emp_id,emp_name,salary,dept_id) values
        <foreach collection="list" item="item" separator=",">
            (#{item.empId},#{item.empName},#{item.salary},#{item.department.deptId})
        </foreach>
    </insert>
    <!--    批量更新-->
    <update id="batchUpdateEmployees" >
        <foreach collection="list" item="item" index="index" separator=";">
            update employee set salary=#{item.salary} where emp_id=#{item.empId}
        </foreach>
    </update>
    <!--批量删除 where dept_id in 表示，如果id在以下集合范围内就删除掉-->
    <delete id="batchDeleteEmployees" parameterType="java.util.List">
        delete from employee where emp_id in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>