过滤条件
【一】语法
select */字段名 from */表名 where */字段名=字段值;
-- 执行顺序
from 起手 知道是那张表
where 根据过滤条件在表中过滤数据
select 再过滤出自己想要的数据
【二】准备的数据
# 创建表
create table filt_eg(
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 filt_eg(name, sex, age, hire_date, post, salary, office, depart_id) values
("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);
【三】where关键字
1）查询一定范围数据
1.查询5<=id<=10的数据
select * from filt_eg where id>=5 and id<=10 ;
select * from filt_eg where id between 5 and 10;
2.查询id<=5 或id>=10
select * from filt_eg where id < 5 or id > 10;
select * from filt_eg where id not between 5 and 10;
3.查询age = 25，35，45
4.查询null只能使用is查询
select * from filt_eg where age=25 or age=35 or age=45;
select * from filt_eg where age in (25,35,45);
2）查询包含指定内容的数据
1.查询 name 中含有a的姓名和薪资
select name,salary from filt_eg where name like"%a%";
2.查询name是6个字符组成的姓名，年龄和薪资
select name,sex,salary from filt_eg where name like"______";
select name,sex,salary from filt_eg where char_length(name)=6;
【四】group by（分组）
1）按照部门分组
模糊查询需删除严格模式（ONLY_FULL_GROUP_BY）才能使用
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
set session sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
1.模糊查询模式
- 按照部门分组后显示的是每一个部门的第一个人的信息条
select * from filt_eg group by post;
2.严格查询模式
select post from filt_eg group by post;
2）获取最大值（max）
-
获取每个部门的最大薪资
select post,max(salary) from filt_eg group by post; # 先通过post分组，在寻找每一组的最大薪资# 指定名称版 select post as "部门" ,max(salary) as "最高薪资" from filt_eg group by post;
3）获取最小值（min）
-
获取每个办公室的最小薪资
select office as '办公室' , min(salary) as '最低薪资' from filt_eg group by office;
4)获取平均值（avg）
-
获取每个性别的平均工资
select sex as '性别' , avg(salary) as '平均薪资' from filt_eg group by sex;
5）获取总和（sum）
-
获取每个部门薪资的总和
select post as "部门" ,sum(salary) as "薪资总和" from filt_eg group by post;
6）获取个数（count）
-
获取每个部门的人数
select post as "部门" ,count(name) as "部门人生" from filt_eg group by post; -
count不能对null进行计数
7）获取分组后的具体值（group_concat）
-
查询分组之后的部门名称和每个部门下的所有员工
select post,group_concat(name) from filt_eg group by post;# 拼接多条数据版 select post,group_concat(name,'age','|') from filt_eg group by post;
8）获取分组前的具体数据（concat）
-
查询姓名和薪资
select concat('姓名：',name),concat('薪资：',salary) from filt_eg;
9）临时命名（as）
-
可给字段，表取别名，临时查数据使用
select eg.name,eg.post from filt_eg as eg;
10）查询数据直接计算
select name as '姓名',salary*12 as '年薪' from filt_eg;
11）注意事项
-
where 和 group by 同时出现时
- where先对整体数据进行过滤
- group by 在对数据进行分组
-
where 筛选条件不能使用聚合函数
- where 不分组，默认整张表就是一组
-
例：统计各部门50岁以下的员工平均薪资
select post,avg(salary) from filt_eg where age<50 group by post;
【五】having（分组之后筛选）
- having 与 where 功能一样
- having分组后筛选，可用聚合函数
- where分组前筛选
1）统计各部门年龄在40以下的员工的工资，并平均薪资在1000以下的部门
select post,avg(salary) from filt_eg
where age<40
group by post
having avg(salary)<1000
;
【六】distinct（ 去重）
必须是完全一样的数据才能去重
1）去重一种数据
-
对部门进行去重
select distinct post from filt_eg;
2）去重多种数据
-
对部门和性别进行去重
select distinct post,sex from filt_eg;
【七】order by （排序）
- 升序：asc
- 降序：desc
- 默认是升序
1）升序
-
将薪资按升序排列
select * from filt_eg order by salary;
2）降序
-
将薪资按降序排列
select * from filt_eg order by salary desc;
3）混合
-
按照办公室升序，薪资降序排列
select * from filt_eg order by office,salary desc;
【八】limit（限制展示条数）
1）单数字限制
# limit 页数
select * from filt_eg limit 5;
2）多条数字限制
# limit 起始位置 页数
select * from filt_eg limit 5,5;
【九】正则表达式
1）语法
- 其会寻找这一行内所有记录的数据进行筛选
属性名 REGEXP '匹配方式'
2）匹配方式
| 选项 | 说明 | 例子 | 匹配值示例 |
|---|---|---|---|
| ^ | 匹配文本的开始字符 | ‘^b’ 匹配以字母 b 开头的字符串 | book、big、banana、bike |
| $ | 匹配文本的结束字符 | ‘st$’ 匹配以 st 结尾的字符串 | test、resist、persist |
| . | 匹配任何单个字符 | ‘b.t’ 匹配任何 b 和 t 之间有一个字符 | bit、bat、but、bite |
| * | 匹配前面的字符 0 次或多次 | ‘f*n’ 匹配字符 n 前面有任意个字符 f | fn、fan、faan、abcn |
| + | 匹配前面的字符 1 次或多次 | ‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a | ba、bay、bare、battle |
| ? | 匹配前面的字符 0 次或1次 | ‘sa?’ 匹配0个或1个a字符 | sa、s |
| 字符串 | 匹配包含指定字符的文本 | ‘fa’ 匹配包含‘fa’的文本 | fan、afa、faad |
| [字符集合] | 匹配字符集合中的任何一个字符 | ‘[xz]’ 匹配 x 或者 z | dizzy、zebra、x-ray、extra |
| [^] | 匹配不在括号中的任何字符 | ‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串 | desk、fox、f8ke |
| 字符串 | 匹配前面的字符串至少 n 次 | ‘b{2}’ 匹配 2 个或更多的 b | bbb、bbbb、bbbbbbb |
| 字符串 | 匹配前面的字符串至少 n 次， 至多 m 次 | ‘b{2,4}’ 匹配最少 2 个，最多 4 个 b | bbb、bbbb |
3）使用
# 查询name字段以x开头
select name from filt_eg where name regexp '^x';
# 查询name字段以g结尾
select name from filt_eg where name regexp 'g$';
# 查询name字段包含a,m，且直接只有一个字母
select name from filt_eg where name regexp 'a.m';
# 查询name字段包含a，且后面出现n
select name from filt_eg where name regexp 'an*';
# 查询name字段包含a，且后面出现一次n
select name from filt_eg where name regexp 'an+';
# 查询name字段包含a，且后面出现至少一次n
select name from filt_eg where name regexp 'an?';