随笔十七 ：SQL查询具体用法

郑统京

1、where（分组之前的筛选条件）
## 作业 ：对于整体数据的一个筛选
支持成员运算 条件 in()
支持逻辑运算between...and 包含连个边界值
支持算数运算 id>5
支持模糊匹配 like "%j_"
​
#1、查询id大于3小于5的数据
select id,name,age from emp where id>=3 and id<=5;
select id,name,age from emp where id between 3 and 6; ## between 包含了3和5
​
#2、查询薪资是20000或19000或者17000的数据
select id,name,age from emp where salary=20000 or salary=17000 or salary=19000;
select id,name,age from emp where salary in(20000,19000,17000);
​
# 3、查询员工姓名中包含o的员工姓名和薪资
"""
模糊查询
    like
        % 匹配任意多个字符
        _ 匹配任意单个字符
"""
select salary,name,age from emp where name like '%o%';
​
# 4、查询员工姓名是由4个字符组成的姓名和工资   高级用法:char_length（）
select salary,name,age from emp where name like '____';
select salary,name,age from emp where char_length(name)=4;
​
# 5、查询id小于3或者id>6的数据
select salary,name,age from emp where not between 3 and 6;
​
# 6、查询薪资不在20000，18000，17000范围的数据
select salary,name,age from emp where salary not in(20000,18000,17000);
​
# 7、查询岗位描述为空的员工姓名和岗位名  针对于null不能用=，要用is
select name,post from emp where post_comment is null;2、group by 分组
"""
统计部门的平均工资
统计男女比例
"""
# 1、按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 而不再但是组内的单个数据
    上述命令在你没有设置严格模式的时候是可以正常执行的 返回的是分组之后 每个组的第一条数据 但是这部服务分组的规范：分组之后不应该考虑单个数据，而应该以组为操作单位(分组之后没有办法直接获取组内的单位数据)
    如果设置了严格模式 那么上述的命令会直接报错
"""
设置分组的严格模式 分组 默认只能拿到分组的依据
set global set_mode = 'strict_trans_table,only_full_group_by';
按照什么分组就只能拿到分组的条件 其他字段不能直接获取，需要借助于一些方法
​
​
"""
什么时候需要分组？
    关键字 每个 平均 最高 最低
   
    聚合函数
        max
        min
        avg
        sum
        count
"""
# 1、获取每个部门最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post  '部门',max(salary)  '最高薪资' from emp group by post; # 不推荐 语意不明确
# as 语法不单单可以给字段，也可以给表临时取别名
select t1.post from emp as t1;
​
​
# 2、获取每个部门最低薪资
select post,min(salary) from emp group by post;
# 3、获取每个部门平均薪资
select post,avg(salary) from emp group by post;
# 4、获取每个部门总和薪资
select post,sum(salary) from emp group by post;
# 5、获取每个部门人数  count对于null没有办法计数
select post,count(id) from emp group by post
select post,count(post_count) from emp group by post =》不支持null
​
# 6、查询分组之后的部门名称和每个部门下所有员工姓名
# group_concat不单单可以支持你获取分组之后的其他字段 还支持其他操作（拼接）（多个字段）
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post; =>每组数据多了_DSB结尾
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用
select concat(name,':DSB'),concat(salary,':DSB') from emp ;
# concat_ws 如果多个字段连接符号相同的情况下
select concat_ws(':',name,age,sex) from emp ; =>数据用：做分割
'''
复习join拼接方法
"?".join([111,222,333])=>不能使用此方法，因为列表的元素必须是str才可以
'''
​
# 7、查询每个组最高的年薪
select post,max(salary*12) from group by post;
​
分组注意事项
# 关键字where和group by同时出现的时候group 不要必须在where的后面
where 先对整体数据进行过滤之后在进行分组
where 筛选条件不能和聚合函数使用
select id,name,age from emp where max(salary)>3000 =>错误
select max(salary) from emp; 不分组将整张表当作一组
​
# 统计各部分年龄在30岁以上的员工平均薪资
1、先求大于30岁的员工
select * from emp where age>30;
2、在求部门的员工工资
select post,avg(salary) from emp group by post
3、合并
select post,avg(salary) from emp where age>30 group by post
​3、having（分组后筛选）
"""
having的语法和where的语法是一样的
只不过having是在分组之后进行的过滤操作的
即having是可以使用聚合函数的
"""
# 1、统计各部分年龄在30岁以上的员工共字并且保留平均薪资大于10000的部门
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000
​
​4、distinct去重
"""
一定要注意 必须是一模一样的数据才能去重
[
{'id':1,'name':'egon'},
{'id':2,'name':'egon'},
{'id':3,'name':'egon'},
]
主键不一样是没办法去重的，可以不把主键选出来
​
ORM 对象关系映射
表                   类
一条条数据            对象
字段的值             对象的属性
​
"""
select distinct id,age from emp; =>这样id不同无法去重
select distinct age from emp;5、order by排序
"""
order by 默认是升序 asc 该asc可以忽略不写
"""
select * from emp order by salary;
select * from emp order by salary asc ; 升序
select * from emp order by salary desc; 降序
# 先按照age降序排列，在按照salary升序排（支持多个字段备用比较）
select * from emp order by age desc，salary asc;
​
# 1、统计各部分年龄在10岁以上的员工共字并且保留平均薪资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
        where age>10
        group by post
        having avg(salary)>1000
        order by avg(salary) desc
​6、limit限制展示条数
select * from emp; 数据量过长会直接奔溃，减少数据库压力
"""
针对数据过多的情况 我们通常都是做分页处理
"""
select * from emp limit 3;
select * from emp limit 0,5; # 第一个参数是起始位置，第二个参数是条数
select * from emp limit 5,5; 展示6-10的数据
​7、regexp正则表达式
select * from emp where name regexp '^j.*(n|y)$'  以j开头，中间匹配多个字符按，结尾是n或者y
"""
正则是一门独立的语言
在python中如果你想使用正则，需要借助与re模块
    面试题:
        1、re模块中常用的方法
            findall
            match
            search
        2、match和search、findall区别
            findall:分组优先展示
                ^j.*(n|y)$
                不会展示所有正则表达式匹配的内容
                而仅仅展示括号内正则表达式的内容
            match：从头匹配
            search：从整体匹配
        3、贪婪匹配和非贪婪匹配
            正则表达式默认都是贪婪匹配
            将贪婪变成非贪婪只需要在正则表达式变成？
            .* 贪婪
            .*？非贪婪
"""8、多表查询0、连表
'''
笛卡尔积
select * from dep,emp;=>一堆四，每个数据都拼接了一次
'''
​
select * from emp,dep where emp.dep_id=dep.id
"""
MySQL也知道 你在后面拆线呢数据过程中 肯定会经常用到拼表擦欧总
所以特地给你开设了对应的方法
    inner join  内链接
    left join   左链接
    right join  右链接
    union       全链接
"""
# inner join 只拼接两张表中共有的数据部分
select *from emp inner join dep
        on emp.dep_id=dep.id; 习惯型加上表单前缀，否则容易报错
​
# left join 左表所有的数据都展示出来，没有的对应的项就NULL
select *from emp left join dep on emp.dep_id=dep.id;
​
# right join 右表所有的数据都展示出来，没有的对应的项就NULL
select *from emp right join dep on emp.dep_id=dep.id;
​
# union 全连接左右两表所有的数据都展示出来，没有的对应的项就NULL
select *from emp left join dep on emp.dep_id=dep.id
union
select *from emp right join dep on emp.dep_id=dep.id;
​1、子查询
"""
子查询就是我们平时解决问题的思路
    分步骤解决问题
        第一步
        第二步
        ...
将一个查询语句的结果当作另外的查询语句的条件使用
​
"""
# 查询部门是技术或者人力字段的员工信息
    1、先获取部门id
    select * from emp where dep_id in (200,201)
    2、再通过员工表筛选对应的员工
    select id from dep where name='技术' or name='人力';
   
select * from emp where dep_id in (select id from dep where name='技术' or name='人力')2、总结:
# 1、 书写sql语句的时候select的后面先用*占位，写完在改
# 2、 较为复杂的sql，分布写，拆开
# 3、 在做多表查询的时候，连表和子查询会结合使用
​
表的查询结果可以当作其他表的查询条件
也可以通过起别名的方式把他当作虚拟表跟其他关联表拼接
"""
多表查询就两种方式
    先拼接表在查询
    子查询一步一步来
"""3、知识点补充# 查询平均年龄在25岁以上的部门名称
"""只要涉及到多表查询 都有两种"""
# 1、子查询
select * from dep where id in （select dep_id from emp group by dep_id having avg（age）>25）
# 2、连表
    1、先拿到部门和员工表拼接后的结果
    2、分析语意 得出需要分组
        select * from emp inner join dep
            on emp.dep_id = dep.id
            group by dep_id
            having avg(age)>25;
​
# 关键字exist（了解）
    只返回布尔值 True False
    返回True的时候外层查询语句执行
    返回False的时候外层擦汗寻语句不在执行
    select * from emp where exists (select id from dep where id>10)=>exists的后面数据大于10才会执行前面的语句