四

今日内容概要

• 前期表准备

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

• where筛选条件

• [group by分组](#group by分组)

• 分组注意事项

• having分组之后的筛选条件

• distinct去重

• [order by排序](#order by排序)

• limit限制展示条数

• 正则

• 多表操作

• 前期表准备2

  • 表查询
  • 子查询
  • 总结

今日内容详细

前期表准备

返回

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','teacher',5413.65,401,1),  # 以下是教学部
('kevin','female',48,'20170425','teacher',15244.54,401,1),
('tony','male',58,'20170221','teacher',54153433.34,401,1),
('owen','female',38,'20171201','teacher',4354.04,401,1),
('jacjkk','male',88,'20220301','teacher',54134534533.45,401,1),
('sank','female',88,'20000301','teacher',453.78,401,1),
('egon','male',48,'20170930','teacher',435843.8,401,1),
('tank','male',58,'20110301','teacher',434598.73,401,1),
('阿萨德','male',36,'20110301','sale',15435.55,402,2),  # 以下是销售部
('案而言','female',53,'20221201','sale',1525.55,402,2),
('而是','male',45,'20220212','sale',154525.55,402,2),
('人太少','male',79,'20080301','sale',152385.55,402,2),
('人社局','female',14,'20020202','sale',78877.55,402,2),
('给对方听f','male',18,'20150315','sale',1525867.55,402,2),
('合到','male',32,'19570522','operation',4837583.55,403,3),  # 以下是销售部门
('呵呵','female',34,'19561226','operation',4837583.55,403,3),
('合人防到','male',38,'20341202','operation',4837583.55,403,3),
('有股份','male',64,'20671105','operation',4837583.55,403,3);

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

# 如果出现电脑在插入中文 的时候还是出现错乱或者空白的情况，可以将字符编码统一改成GBK格式

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

返回

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

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

where筛选条件

返回

# 作用：是对整体数据的一个筛选操作
# 1、查询ID大于等于3小于等于6的数据
select id,name from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6;  # 两者是一样的

#  2、查询薪资是435843.8或者4837583.55的数据
select * from emp where salary=435843.8 or salary=4837583.55;
select * from emp where salary in (435843.8,4837583.55);

# 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、查询薪资不是435843.8和4837583.55的人
select name from emp where salary not in (435843.8,4837583.55);

# 7、查询岗位描述都为空的员工姓名和岗位  针对null不能用等号，要用 is
select name,post from emp where post_comment is null;

group by分组

返回

# 分组实际应用场景 
	男女比例
    部门平均薪资
    国家之间的数据统计
    
    
# 1、按照部门分组
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
		count
		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 as '部门',min(salary) as '最低薪资' from emp group by post;

# 3、获取每个部门的平均薪资
select post as '部门',avg(salary) as '平均薪资' from emp group by post;

# 4、获取每个部门的工资总和
select post as '部门',sum(salary) as '薪资总和' from emp group by post;

# 5、获取每个部门的人数
select post as '部门',count(id) as '部门人数' from emp group by post;  # 最常用
select post as '部门',count(salary) as '部门人数' from emp group by post;
select post as '部门',count(age) as '部门人数' from emp group by post;
select post as '部门',count(post_comment) as '部门人数' 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 不分组的时候用
select concat('name:',name),concat('salary:',salary) from emp;

# 补充：as语法不单单可以给字段起别名，还可以给表临时起别名
select * from emp as t1;
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;

分组注意事项

返回

# 关键字where和group by同时出现的时候，group by必须在where后面
where先对整体数据进行过滤，然后再进行分组
聚合函数只能在分组之后使用
即where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary)>3000;  # 报错
select max(salary) from emp;  # 不分组就默认整体是一组

# 统计各部门年龄在三十岁以上的员工平均薪资
	1、先求所有年龄大于三十岁的员工
    	select * from emp where age > 30;
    2、在对结果进行分组
    	select * from emp where age > 30 group by post;
    select post,avg(salary) from emp where age>50 group by post;

having分组之后的筛选条件

返回

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

distinct去重

返回

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

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可以省略不写
"""

select * from emp order by age desc,salary asc;
# 先按照age降序排，如果age相同，则在按照salary的升序排

# 统计个部门年龄在三十岁以上的员工工资，并且保留平均工资大于十万的部门，然后对平均工资降序排序
select post,avg(salary) from emp 
    where age>50 
    group by post 
    having avg(salary)>100000
    order by avg(salary) desc;

limit限制展示条数

返回

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

select * from emp limit 0,5;  # 从1开始展示后面的五个数据
select * from emp limit 5,5;  # 从6开始展示后面的五个数据
第一个参数是起始位置，但是不包含
第二个参数是要展示的条数

正则

返回

select * from emp where name regexp '^j.*(n|y)$';
# 跟Python中的re模块中的正则一样

多表操作

返回

前期表准备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  左连接
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;

子查询

返回

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

总结

返回

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

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