47---表的查询进阶

如何查询表

1 表准备

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

当表的字段特别多，展示的时候错乱，可以使用\G分行展示

select * from emp\G;

# 个别可能会出现改了编码格式后，插入中文还是会出现乱码或者空白的现象，可以将字符编码统一设置成GBK

• 几个重要关键字的执行顺序

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

# 虽然执行顺序和书写顺序不一致，就按照书写顺序的方式写sql
select * ,先用*占位
最后将*替换成你想要的具体字段

where约束条件

• 作用：对整体数据的一个筛选操作

# 1查询id大于等于3小于等于6的数据,两种方式等价 
select * from emp where id>=3 and id <=6;
select id,name from emp where id between 3 and 6;

# 2查询薪资2w或者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的员工的姓名和薪资---模糊查询
select name,salary from emp where name like '%o%';

# 4 查询员工姓名是由四个字符组成的姓名和薪资
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 is null;  # 不要用等号

group by分组

• 分组的实际应用场景，分组应用场景非常多
  • 男女比例
  • 部门平均薪资
  • 国家之间数据统计
• 按照部门分组

select * from emp group by post;
# 分组之后，最小的可操作单位应该是组，而不是组内的单个数据。
上述命令在你没有设置严格模式的时候是可以正常执行的，返回的是分组之后，每个组的第一个数据，但是这不符合分组的规范，分组之后不应该开率单个数据，而应该以组为操作单位（分组之后，没办法直接获取组内单个数据）
如果设置了严格模式，上述命令会直接报错
 set global sql_mode = 'strict_trans_tables,only_full_group_by';
 设置严格模式之后，分组默认只能拿到分组的依据
 select post from emp group by post;
 按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助一些方法（聚合函数）。
 
 聚合函数
 	max
 	min
 	sum
 	avg...
 
 什么时候需要分组：靠自己的语感，哈哈哈
 	比如：每个 平均  最高 最低

# 1 获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' 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 统计每个部门的人数,count后面的字段放什么都可以，但是应该放标识数据唯一性的字段
select post,count(salary) from emp group by post;
select post,count(id) from emp group by post;  # 推荐使用id
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; #不行，对null不行

# 6 查询分组之后的部门ing成和每个部门下所有的员工姓名---group_concat
select post,group_concat(name) from emp group by post;
# group_concat不单单可以支持获取分组之后的其他字段值，还支持拼接操作
select post,group_concat(name_cute) from emp group by post;
# 还可以拿多个字段的值
select post,group_concat(name,':',salary) from emp group by post;

# concat不分组的时候用
select concat('NAME:',name),concat('SAl:',salary) from emp;


# 补充as语法：不单可以给字段起别名还可以给表临时起别名
select emp.id,emp.name from emp;
select t1.id,t1.name from emp as t1；
select emp.id,emp.name from emp as t1；# 报错

# 7 查询每个人的年薪 12薪
select name,salary*12 from emp;

• 分组注意事项

关键字where 和 group by 同时出现的时候，group by必须在where 的后面
where先对整体数据进行过滤之后，再进行分组操作
聚合函数只能在分组之后使用

#  统计各部门年龄在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;

having 分组之后的筛选条件

having的语法和where是一致的
只不过having是在分组之后进行的过滤操作

1 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select * from emp where age>30
	group by post
	having avg(salary)>1000;

distinct 去重

一定要注意：必须是完全一样的数据才可以去重！！！
一定不要将主键忽视了，有主键的情况下是不可能去重的。
select distinct id,age from emp;
select distinct age from emp;

[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]

ORM  对象关系映射   让不懂SQL语句的人也能够非常牛逼的操作数据库
表								类
一条条的数据						对象
字段对应的值						对象的属性

你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
"""

order by 排序

select * from emp order by salary;
select * from emp order by salary asc;
# order by默认是升序，asc 该关键字可以省略不写
也可以修改为降序--desc
select * from emp order by salary desc;

# 多个条件约束排序
select * from emp order by age,desc,salary asc;
# 先按照降序排，遇到年龄相同的就按照salary升序排


1 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门，对平均工资进行降序排序
select * from emp 
	where age>10
	group by post
	having avg(salary)>1000
	order by avg(salary) desc;

limit 限制展示条数

select * from emp;
# 针对数据过多的情况，通常那都是进行分页处理
select * from emp limit 3;  # 只展示三条数据

select * from emp limit 0,5; # 第一条到第五条
select * from emp limit 5,5; # 第六条到第第10条
# 第一个参数是起始位置
# 第二个参数是展示数据条数

正则

select * from emp where name regexp '^j.*(n|y)$';

子查询

子查询就是我们平时解决问题的思路

分步骤解决问题

​ 第一步

​ 第二步

将一个查询语句的结果当作另一个查询语句的条件取用

# 查询部门是技术或者人力资源的员工信息
	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 = '人力资源');

• 总结

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联

"""
多表查询就两种方式
	先拼接表再查询
	子查询 一步一步来
"""

2 连表操作理论

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

# right join----与左连接相同

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