mysql的分组查询

时间:2022-01-23 07:24:35

1.设置数据库为严格模式:

 

2.数据准备

mysql的分组查询mysql的分组查询
# 创建一张部门表
create table emp(
  id int not null unique auto_increment,
  name varchar(20) not null,
  sex enum(male,female) not null default male, #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, # 一个部门一个屋子
  depart_id int
);


# 插入记录
# 三个部门:教学,销售,运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(tank,male,17,20170301,张江第一帅形象代言部门,7300.33,401,1), # 以下是教学部
(egon,male,78,20150302,teacher,1000000.31,401,1),
(kevin,male,81,20130305,teacher,8300,401,1),
(jason,male,73,20140701,teacher,3500,401,1),
(owen,male,28,20121101,teacher,2100,401,1),
(jerry,female,18,20110211,teacher,9000,401,1),
(大饼,male,18,19000301,teacher,30000,401,1),
(sean,male,48,20101111,teacher,10000,401,1),

(歪歪,female,48,20150311,sale,3000.13,402,2),# 以下是销售部门
(丫丫,female,38,20101101,sale,2000.35,402,2),
(丁丁,female,18,20110312,sale,1000.37,402,2),
(星星,female,18,20160513,sale,3000.29,402,2),
(格格,female,28,20170127,sale,4000.33,402,2),

(张野,male,28,20160311,operation,10000.13,403,3), # 以下是运营部门
(程咬金,male,18,19970312,operation,20000,403,3),
(程咬银,female,18,20130311,operation,19000,403,3),
(程咬铜,male,18,20150411,operation,18000,403,3),
(程咬铁,female,18,20140512,operation,17000,403,3);

# PS:如果在windows系统中,插入中文字符,select的结果为空白,可以将所有字符编码统一设置成gbk
```
View Code

  完整的查询语句:

 完整的查询语句
    select  [distinct] {* | 字段名 | 聚合函数 | 表达式} from 表名
        [where 条件
        group by 字段名
        having 条件
        order by 字段名
        limit 显示的条数]

查询顺序:
- from
- where
- group by
- having
- select
- distinct

3 .  where 约束

# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6;
# between: 两者之间
# and: 与
select * from emp where id between 3 and 6;


# 2.查询薪资是20000或者18000或者17000的数据
# or:  或者
select * from emp where salary=20000 or salary=18000 or salary=17000;
# in: 在什么里
select * from emp where salary in (20000, 18000, 17000);


# 3.查询员工姓名中包含o字母 的 员工姓名和薪资
# like: 模糊匹配
# %: 匹配0个或多个任意字符   以什么开头 以什么结尾 包含什么
# _: 匹配一个任意字符       ___匹配任意的三个字符
select name, salary from emp where name like "%o%";


# 4.查询员工姓名是由四个字符组成的员工姓名与薪资
select name, salary from emp where name like "_____";
# char_length(字段): 获取字段长度
select name, salary from emp where char_length(name) = 4;


# 5.查询id小于3或者大于6的数据
select * from emp where id < 3 or id > 6;
select * from emp where id not between 3 and 6;


# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000, 18000, 17000);


# 7.查询岗位描述为空的员工名与岗位名  
# 注意: 针对null不能用等号,只能用is     is null
select name, post from emp where post_comment = null;
select name, post from emp where post_comment is null;
select name,post from emp where post_comment is not null;
```

4. group by        聚合函数: max, min, sum, avg, count

比如: 一张员工表中有性别字段,可以根据性别分组,一组是男性,一组是女性,或者是根据部门分组,有教学部、销售部等...

# 1.按部门分组
# 严格模式下只能获取分组字段post数据, 无法获取其他字段信息,就好比是进程之间数据隔离,但是可以使用聚合函数来获取
‘‘‘
聚合函数: max, min, sum, avg, count
‘‘‘
# 非严格模式下不报错
select * from emp group by post;  # 报错
select id, name from emp group by post;  # 报错
select post from emp group by post;

# 严格模式设置
"""
设置sql_mode为only_full_group_by,意味着以后但凡分组,只能取到分组的依据,
不应该在去取组里面的单个元素的值,那样的话分组就没有意义了,因为不分组就是对单个元素信息的随意获取
"""
show variables like "%mode%";
set global sql_mode="strict_trans_tables,only_full_group_by";


# 2.获取每个部门的最高工资 ----> 画图,讲述数据分组后,发生了什么事情
select post, max(salary) from emp group by post;
# as: 起别名; 给获取出来的数据字段名,设置别名
select post as 部门, max(salary) as 薪资 from emp group by post;
# 可简写, 但不推荐
select post 部门, max(salary) 薪资 from emp group by post;

# 每个部门的最低工资
select post, min(salary) from emp group by post;

# 每个部门的平均工资
select post, avg(salary) from emp group by post;

# 每个部门的工资总和
select post, sum(salary) from emp group by post;

# 每个部门的人数: count() 中传任意参数都没问题
select post, count(id) from emp group by post;
select post, count(post) from emp group by post;


# 3.查询分组之后的部门名称和每个部门下所有员工的姓名
# group_concat(name): 不仅可以获取分组后的某一个字段,并且可以对字符串进行拼接
select post, group_concat(name) from emp group by post;
# 给每个部门的员工名字前   NB_
select post, group_concat(NB_, name) from emp group by post;
# 拼接部门员工名字 薪资
select post, group_concat(name, ":", salary) from emp group by post;


# 4.补充concat(不分组时用)拼接字符串达到更好的显示效果 as语法使用
select concat(Name: , name) as 名字, concat(Sal: , salary) as 薪资 from emp;


# 5.补充as语法 即可以给字段起别名也可以给表起
select emp.name as 名字, emp.salary as 薪资 from emp;


# 6.查询四则运算
# 求各部门所有员工的年薪
select name, salary * 12 as annual_salary from emp;
```

 

5.having 语句

   where  与 having的区别:

       1. having与where语法一样,只不过having必须需要在group by后使用;

       2. where 不能使用聚合函数,但having可以;

         如 where  avg(salary) >1000  是错误的     having avg(salary) > 1000         

     

6. distinct : 去重

select distinct id, post from emp;
select distinct post from emp;
select distinct age from emp;

 

7.order  by  

# 1、根据薪资进行升序
select * from emp order by salary;  # 默认升序
select * from emp order by salary asc;  # 指定升序
select * from emp order by salary desc;  # 指定降序

 

8. limit  

# 1、从第一条开始,获取4条记录;
select * from emp limit 4;    # 查询前4行 # 2、limit可以有两个参数, 参数1:是限制的开始位置, 参数2:是从开始位置展示的条数;
select * from emp limit 0, 4;   
select * from emp limit 4, 4;  #  获取5-8行