Django 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 练习

 

11 Django 聚合 分组 

　　官方文档: https://docs.djangoproject.com/zh-hans/3.1/topics/db/aggregation/

 

 

详细

 

# 数据准备

# 创建表

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 后跟的是查询的字段名

 

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;

~注: 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

"""

 

# 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;

 

 

#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;

 

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;

 

 

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);

 

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)$';

 

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. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资