day47

02.今日内容概要

如何查询表
    """
    select
    where 
    group by
    having
    distinct
    order by
    limit
    regexp
    like
    ...
    """
连表操作理论

03.前期表准备及注意事项

# 创建表
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
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), # 以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;

select * from emp\G; # 当表字段特别多 展示的时候错乱,可以使用\G分行展示

# ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

04.SQL语句书写建议

# 书写顺序
select id,name from emp where id>3;
# 执行顺序
from
where
select 

"""
虽然执行顺序和书写顺序不一致 你在写sql语句的时候可能不知道怎么写
你就按照书写顺序的方式写sql
	select * 先用*号占位
	之后去补全后面的sql雨具
	最后*号替换后你想要的具体字段
"""

关键字执行优先级

select distinct 字段1,字段2,字段3 as 别名 
	from 表
	where 分组之前的筛选条件
    group by 分组条件
    having 分组之后的筛选条件
    order by 排序字段1 asc,排序字段2 des
    limit 显示条数
=====================================
---sql顺序
from join on where group by having select row_number order by limit union
on是连接条件,where是全局过滤,having在where,group by后

05.where过滤

# 作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6; # 两者等价
 
# 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的员工的姓名和薪资
"""
模糊查询
    like
        %  匹配任意多个字符
        _  匹配任意单个字符
"""
select name,salary from emp where name like '%o%';
 
# 4.查询员工姓名是由四个字符组成的 姓名和薪资  char_length()   _
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
 
# 5.查询id小于3或者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
select name,post from emp where post_comment = NULL; # 错误
select name,post from emp where post_comment is NULL;

06.group by分组

"""
分组实际应用场景  分组应用场景非常的多
    男女比例
    部门平均薪资
    部门秃头率
    国家之间数据统计
"""
 
# 1.按照部门分组
select * from emp group by post;
"""
分组之后,最小可操作单位应该是组,而不再是组内的单个数据
    上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
    如果设置了严格模式,那么上述命令会直接报错
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';
exit;
mysql -h 127.0.0.1 -P 3306 -uroot -p
show variables like "%mode%";

# 设置严格模式之后  分组 默认只能拿到分组的依据
select post from emp group by post; 
# 按照什么分组就只能拿到分组,其他字段不能直接获取,需要借助于一些方法(聚合函数)
 
 
"""
什么时候需要分组啊？？？
    关键字
        每个 平均 最高 最低
     
    聚合函数
        max
        min
        sum
        count
        avg
"""
# 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可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱
 
# 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.获取每个部门的人数
select post,count(id) from emp group by post;  # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) 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;
select post,group_concat(name,':',salary) from emp group by post;

# concat不分组的时候用,group_concat分组之后用
select concat('NAME:',name),concat('SAL:',salary) from emp;
 
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp; 
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;
 
# 查询每个人的年薪  12薪
select name,salary*12 from emp;

07.分组注意事项

# 关键字where和group by同时出现的时候group by必须在where的后面
"""
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
"""
select id,name,age from emp where max(salary) > 3000; # 报错,where筛选条件不能使用聚合函数
select max(salary) from emp;  # 不分组 默认整体就是一组
 
# 统计各部门年龄在30岁以上的员工平均薪资	
    1.先求所有年龄大于30岁的员工 # 第一步
        select * from emp where age > 30;
    2.再对结果进行分组 # 第二步
        select * from emp where age > 30 group by post;
     
    select post,avg(salary) from emp where age>30 group by post;

08.having分组之后筛选

"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
	where age>30
    group by post
    having avg(salary) > 10000;

09.distinct去重

"""
一定要注意 必须是完全一样的数据才可以去重！！！
一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
ORM  对象关系映射   让不懂SQL语句的人也能够非常牛逼的操作数据库
表                               类
一条条的数据                      对象
字段对应的值                      对象的属性
 
你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
"""
select distinct id,age from emp;
select distinct age from emp;

10.order by排序

select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序  asc 该asc可以省略不写
也可以修改为降序     desc
"""

# 先按照age降序排  如果碰到age相同 则再按照salary升序排
select * from emp order by age desc,salary asc;
 
# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post,avg(salary) from emp
    where age>10
    group by post
    having avg(salary) > 1000
    order by avg(salary) desc
    ;

11.limit限制展示条数

select * from emp;
"""针对数据过多的情况,我们通常做分页处理"""
# 限制展示条数
select * from emp limit 3; # 只展示三条数据
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;

# 分页显示
# 第一个参数表示起始位置,第二个参数表示的是条数,不是索引位置
select * from emp limit 0,5;  
select * from emp limit 5,5;

12.正则

# 正则表达式
#   用一些特殊符号的组合去字符串中筛选出符合条件的数据
   	
select * from emp where name regexp '^j.*(n|y)$';
# '^j.*(n|y)$'  j开头 中间无所谓 n或者y结尾,电话号码身份证

13.连表操作

准备表

create database db4 charset=utf8;
use db4;
# 建表
create table dep(
id int,
name varchar(20)
);
 
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
 
# 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
 
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

表查询

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   左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL


# right join  右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL


# union     全连接  左右两表所有的数据都展示出来
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;

14.子查询概念

"""
子查询就是我们平时解决问题的思路
    分步骤解决问题
        第一步
        第二步
        ...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
    第一步 1.先获取部门的id号
    第二步 2.再去员工表里面筛选出对应的员工
    select id from dep where name='技术' or name = '人力资源';
     
    select name from emp where dep_id in (200,201);
          
    select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

总结

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
 
"""
多表查询就两种方式
    先拼接表再查询
    子查询 一步一步来
"""