表准备
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
);
desc emp;
#插入记录
#三个部门:教学,销售,运营
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;
几个关键字执行顺序[理解层面]
select id,name from emp where id > 3;
1. select * 先用*号占位
2. 之后去补全后面的sql语句
3. 最后将*号替换后你想要的具体字段
where 筛选条件
# 作用: 是对整体数据的一个筛选操作
# 1. 查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name,age 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);
# 模糊查询
# like
# % 匹配任意多个字符
# _ 匹配任意单个字符
# 3. 查询员工姓名中包含字母o的员工的姓名和薪资
select name,salary from emp where name like '%o%'; # 两个%, 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;
select * from emp where id<=3 or id>=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; # 查的到
select name,post from emp where post_comment is not NULL; # 查的到
group by分组
分组实际应用场景 分组应用场景非常的多
- 男女比例
- 部门平均薪资
- 部门秃头率
- 国家之间数据统计
# 按照部门分组, 拿到每个部门的第一条数据 select * from emp group by post; # 5.7及以上版本会直接报错
分组之后 最小可操作单位应该是组 而不再是组内的单个数据
上述命令在你没有设置严格模式的时候是可正常执行的
返回的是分组之后 每个组的第一条数据
但是这不符合分组的目的
分组之后不应该考虑单个数据 而应该以组为操作单位
(分组之后 应该没办法直接获取组内单个数据)如果设置了严格模式only_full_group_by --> 那么上述命令会直接报错
# 5.7以上版本不需要这句 # 因为5.7及以上版本的严格模式默认的就设置的多, 执行 select @@sql_mode; 可查看 set global sql_mode = 'strict_trans_tables, only_full_group_by'; # 设置严格模式之后 分组 默认只能拿到分组的依据 select post from emp group by post; # 5.7及以上版本直接这句就行 # 按照什么分组就只能拿到分组 # 其他字段不能直接获取 需要借助于一些方法(聚合函数)
什么时候需要分组??? --> 关键字: 每个 平均 最高 最低
聚合函数
- max
- min
- sum
- count
- avg
# 1. 获取每个部门的最高薪资 select post,max(salary) from emp group by post; # as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱 select post as '部门',max(salary) as '最高薪资' from emp group by post; select post '部门',max(salary) '最高薪资' from emp group by post; # 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(id) from emp group by post; # count(id) 常用 符合逻辑 select post,count(salary) from emp group by post; select post,count(age) from emp group by post; # 填null不行 select post,count(post_comment) from emp group by post; # 全是0 # 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) as x1, concat('SAL:',salary) from emp; # 补充: as语法: 不单单可以给字段起别名, 还可以给表临时起别名 select emp.id, emp.name from emp; select emp.id, emp.name from emp as t1; # 报错 # ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list' # 更正 select t1.id, t1.name from emp as t1; # 7. 查询每个人的年薪 12薪 select name, salary*12 from emp;
分组--注意事项
# 关键字where和group by同时出现的时候group by必须在where的后面 # where先对整体数据进行过滤之后再group by分组操作 # where筛选条件不能使用聚合函数 select id,name,age from emp where max(salary) > 3000; # ERROR 1111 (HY000): Invalid use of group function select max(salary) from emp; # 不分组 默认整体就是一组 # 8. 统计各部门年龄在30岁以上的员工的平均薪资 # 8.1 先求所有年龄大于30岁的员工 select * from emp where age>30; # 8.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是一致的 (where是在分组之前进行筛选)
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的# 9. 统计 各部门 年龄在30岁以上员工的 平均工资, 并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000;
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; # order by默认是升序 asc 该asc可以省略不写
select * from emp order by salary desc; # 也可以修改为降序 desc
# 先按照age降序排 如果碰到age相同 则再按照salary升序排
select * from emp order by age desc, salary asc; # 后边还可以跟
# 10. 统计各部门年龄在10岁以上的员工平均工资,
# 并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post,avg(salary) 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; # 起始位置0, 展示条数5 1,2,3,4,5
select * from emp limit 5,5; # 起始位置5, 展示条数5 6,7,8,9,10
正则
# ^j: j开头 .*: 中间无所谓 (n|y)$: n或者y结尾
select * from emp where name regexp '^j.*(n|y)$';
多表操作
前期表准备
# 建表 create table dep( id int, name varchar(20) ); # 前边有个叫emp的表, 先删掉 drop table emp; 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; select * from emp;
# 表拼接
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 左右两表所有的数据都展示出来
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 = '人力资源'; # 结果为 (200,201)
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 = '人力资源');
# =>
# 表的查询结果可以作为其他表的查询条件
多表查询总结
多表查询就两种方式
1. 先拼接表再查询
2. 子查询 一步一步来重点是要明白 拼表和子查询的概念
Over!!