mysql MySQL 筛选过滤分组去重排序分页

概要

1 select 与 from
2 关键字where 筛选
3 关键字group by 分组
4 group_concat 与 concat
5 having 过滤(分组之后用)
6 关键字distinct 去重
7 关键字order by排序
8 关键字limit分页
9 关键字regexp正则
10 练习

详细

# 数据准备

# 创建表
create table emp(
  id int primary key 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
(‘jason’,’male’,18,’20170301′,’张江第一帅形象代言’,7300.33,401,1), #以下是教学部
(‘tom’,’male’,78,’20150302′,’teacher’,1000000.31,401,1),
(‘kevin’,’male’,81,’20130305′,’teacher’,8300,401,1),
(‘tony’,’male’,73,’20140701′,’teacher’,3500,401,1),
(‘owen’,’male’,28,’20121101′,’teacher’,2100,401,1),
(‘jack’,’female’,18,’20110211′,’teacher’,9000,401,1),
(‘jenny’,’male’,18,’19000301′,’teacher’,30000,401,1),
(‘sank’,’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);

1 select 与 from

from 从哪个表查询
select 后跟的是查询的字段名
mysql MySQL 筛选过滤分组去重排序分页

2 where 筛选

“””
模糊查询:没有明确的筛选条件
    关键字:like
    关键符号:
        %:匹配任意个数任意字符
        _:匹配单个个数任意字符
show variables like ‘%mode%se’;
“””

# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6;
mysql MySQL 筛选过滤分组去重排序分页
~注: sql 语句支持 and or not

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

# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 建议你按照查询的优先级顺序拼写出你的sql语句
“””
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
“””
mysql MySQL 筛选过滤分组去重排序分页

# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like ‘____’;
select name,salary from emp where char_length(name) = 4;

# 5.查询id小于3或者大于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
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

3 group by 分组

前言1
含义:

按照某个指定的条件将单个单个的个体分成一个个整体
    eg:  按照男女将人分组
        按照肤色分组
        按照年龄分组

前言2
配置文件设置:

可参考 <MySQL_02中 “2-3 字符类型” #3>

针对5.6需要自己设置sql_mode
        set global sql_mode = ‘only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH’;
# 重新链接客户端

意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取

前言3
聚合函数
在分组之后使用
    max min sum count avg
数据分组应用场景：每个部门的平均薪资，男女比例等

# 1.按部门分组
select * from emp group by post;  # 报错
select id,name,sex from emp group by post;  # 报错
select post from emp group by post;  # 获取部门信息
# 再次强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名

# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;

mysql MySQL 筛选过滤分组去重排序分页

#3 每个部门的人数
select post,count(id) from emp group by post;
select post,count(post_comment) from emp group by post; # 对其中的null无法计数

4 group_concat 与 concat

4-1 group_concat  分组之后使用
如果真的需要获取分组意外的数据字段可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,’|’,sex) from emp group by post;
mysql MySQL 筛选过滤分组去重排序分页

4-2 concat  不分组使用
select concat(name,sex) from emp;
select concat(name,’|’,sex) from emp;

5 having 过滤(分组之后用)

5-1 where与having都是筛选功能区别是
    where在分组之前对数据进行筛选
    having在分组之后对数据进行筛选

5-2 一个认识: 一条SQL语句的结果也可以看成是一张全新的表

5-3 统计各部门年龄在30岁以上员工工资并保留平均薪资大于10000的部门
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
mysql MySQL 筛选过滤分组去重排序分页

6 关键字distinct 去重

补充(了解)

演示
带主键(本次为id)一定是无法取重的
select distinct id,age from emp;
select distinct age from emp;

7 order by排序

7-1 默认是升序

7-2 改为降序
select *from emp order by salary desc;
7-3 若遇到按年龄排序,但是年龄有相同的情况怎么办->再加一个条件
select * from emp order by age desc, salary;
“”” 先按照age升序,再按salary升序”””
7-4 统计各部门年龄在10岁以上员工工资并保留平均薪资大于1000的部门,然后对平均工资排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary);
mysql MySQL 筛选过滤分组去重排序分页

8 limit分页

8-1 只拿3条数据

8-2 拿某段数据
select *from emp limit 0,5;
select *from emp limit 5,5;
第一个参数: 起始位置,第二个参数:展示条数.

9 regexp正则
select *from emp where name regexp ‘^j.*(n|y)$’;
mysql MySQL 筛选过滤分组去重排序分页

10 练习

判断表关系
    # 班级表
    cid    caption
    # 学生表
    sid sname gender class_id
    # 老师表
    tid    tname
    # 课程表
    cid    cname    teacher_id
    # 成绩表
    sid    student_id course_id number

1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资