mysql之分组查询

注：mysql中不区分大小写，一般关键字用大写，自己写的用小写就可以

分组查询

1、select sum(salary) from person GROUP BY dept_id #按照部门分组后，求各个部门总的工资数。如果一个部门为空，那么单独作为一组

2、select sum(salary),dept_id from person GROUP BY dept_id #加上每个部门的id

3、select sum(salary) from person; #求出person表中所有人的工资

4、select sum(salary) as w,dept_id from person GROUP BY dept_id HAVING w>20000  #查询出哪个部门总工资高于两万的

5、select max(salary),dept_id from person group by dept_id; #找出每个部门工资最大的那个人

6、select avg(salary),dept_id from person group by dept_id; #找出每个部门的平均工资

7、select avg(salary),dept_id,GROUP_CONCAT(name) from person group by dept_id; #找出每个部门的平均工资,并显示每个组内所有的人

8、select avg(salary) as w,dept_id,GROUP_CONCAT(name) from person group by dept_id HAVING w>5000; 找出平均工资大于5k的部门,并显示每个部门内所有的人

where 与 having区别:
#执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

分页查询

1、

 原表格：

select * from person LIMIT 0,4;  #id为1-4的分为一页

select * from person LIMIT 0,4等价于select * from person LIMIT 4

SQL语句的执行顺序：

select name, max(salary)  5 查找数据

 多表联合查询

1、select * from person,dept; #笛卡尔乘积，两个表的数据结果相乘

2、select * from person p,dept d where p.dept_id=d.did; #这样加条件之后就无数据重复了，如果person中由没有部门或者是部门不在dept中的话，那么就不再显示了，同理如果dept中有person中没有的，那么也不会在这里显示的

只有表A和表B中共有的数据才会显示出来

3、select * from person left join dept on person.dept_id=dept.did #左连接查询,特点就是左边的表数据全出来,如果能跟右边的表匹配出来那么就显示，如果匹配不          出来，只显示左边表中的匹配不出来的数据，此时person为基准表

       以一张表为基准，符合条件的就拿过来，不符合条件的那么扔掉，优点是基准表的所有数据都会显示出来

4、select * from person right join dept on  person.dept_id=dept.did  #同理右连接，也可以将person和dept调换位置以实现右关联，此时dept为基准表

5、select * from person inner join dept on person.dept_id=dept.did #内连接

     多表联合查询和内连接是一样的

6、select * from person left join dept on person.dept_id=dept.did #同理右连接

     union         #将重复的数据去掉实现全连接
    select * from person right join dept on person.dept_id=dept.did #内连接

7、select * from person left join dept on person.dept_id=dept.did #同理右连接

     union all         #不去重实现全连接
    select * from person right join dept on person.dept_id=dept.did #内连接

问题：查询出 教学部 年两大于20岁 并且工资小于1万的员工 并按照工资进行降序排序

8、问题一步步分解

 1）select * from dept where dname = ‘教学部‘

 2）select did from dept where dname = ‘教学部‘

 3）select * from person where age>20 and dept_id=1;  #选出第二张表中年龄大于20且部门id为1的人

 4）select * from person where age>20 and dept_id=(select did from dept where dname = ‘教学部‘);   #选出第二张表中年龄大于20且部门id为1的人

  5)select * from person where age>20 and dept_id=(select did from dept where dname = ‘教学部‘) and salary<10000 order by salary desc;  #在括号里的成为子查       询

问题2： 查询每个部门中最高员工工资和最低工资是多少，并显示部门名称

解析：每个部门就是要分组了，根据部门分组；显示部门名称：因为person中只有部门对应的数字，而在dept表中有数字对应的部门，所以得用多表联合查询

  1）select max(salary),min(salary) from person group by dept_id  

  2）select max(salary),min(salary),dname from person left join dept on person.dept_id=dept.did group by dept_id

select avg(age),dname from person left join dept on person.dept_id=dept.did group by dept_id  #求每个组的平均年龄

子语句查询：

1、select * from (select * from person) as aaa

问题1：查找最大工资哪个人的姓名和薪水

select * from person where salary = (select max(salary) from person)

问题2：求工资高于所有人员平均工资的人员

select * from person where salary>(select avg(salary) from person)

问题3：查询平均年龄在20岁以上的部门名

同理在person中只有部门名对应的数字，在dept中有数字对应的部门名，所以要用到联合查询

 1）select * from person where (select avg(age) from person)>20

 2）select dept_id from person where (select avg(age) from person)>20  #只是查询出了person中平均年龄大于20岁的部门代表的数字

  3）select dname from person left join dept on (select avg(age) from person)>20 group by dname   #有问题的。。。不对

对于问题3别人的方法：

select dept_id from person GROUP BY dept_id HAVING avg(person.age) > 20  #就只能查到部门所对应的数字

问题4：查询教学部下的员工信息

select * from person where dept_id = (select did from dept where dname =‘教学部‘);

2019.01.06 晚  

20：16 距离睡觉还有好长时间

good night 

haijing in HZ