首页 > 数据库 >MySQL之筛选和过滤条件

MySQL之筛选和过滤条件

时间：2024-03-03 18:47:10浏览次数：27  
标签：salary group name 过滤 emp MySQL post 筛选 select

开局第一步：创表

create database day03;
use day03;
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;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
# 插入记录
# 三个部门，教学 销售 运营

insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values('xiao','male',18,'2017-03-17','主管',10000.21,401,1),
('quan','female',68,'2017-11-17','teacher',10.21,401,1),
('zheng','male',78,'2016-11-17','teacher',99.71,401,1),
('zhang','female',58,'2010-08-17','teacher',100.21,401,1),
('a','male',43,'2012-01-17','sale',642.21,402,2),
('b','female',19,'2011-11-17','sale',642.21,402,2),
('c','female',23,'2023-01-17','sale',642.21,402,2),
('d','male',28,'2019-01-17','sale',642.21,402,2),
('e','female',64,'2012-02-17','operation',234.32,403,3),
('f','male',23,'2012-08-23','operation',314.54,403,3),
('g','female',67,'2012-01-01','operation',134.52,403,3),
('h','male',91,'2012-09-21','operation',1423.25,403,3);

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

# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象，你可以将字符编码统一设置成GBK

几个重要关键字的执行顺序（了解）

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

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

where约束条件

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

# 1. 查询id大于等于3小于等于5的数据
(1) select id,name,age from emp where id >= 2 and id <= 5;
(2) select id,name,age from emp where id between 2 and 5;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  2 | zheng |  78 |
|  3 | zhang |  58 |
|  4 | a     |  43 |
|  5 | b     |  19 |
+----+-------+-----+

 # 2. 查询薪资是642.21或者134.52或者10.21的数据
(1) select * from emp where salary=642.21 or salary=134.52 or salary=10.21;
(2) select * from emp where salary in (642.21,134.52,10.21);
 +----+------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| id | name | sex    | age | hire_date  | post      | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
|  2 | quan | female |  68 | 2017-11-17 | teacher   | NULL         |  10.21 |    401 |         1 |
|  5 | a    | male   |  43 | 2012-01-17 | sale      | NULL         | 642.21 |    402 |         2 |
|  6 | b    | female |  19 | 2011-11-17 | sale      | NULL         | 642.21 |    402 |         2 |
|  7 | c    | female |  23 | 2023-01-17 | sale      | NULL         | 642.21 |    402 |         2 |
|  8 | d    | male   |  28 | 2019-01-17 | sale      | NULL         | 642.21 |    402 |         2 |
| 11 | g    | female |  67 | 2012-01-01 | operation | NULL         | 134.52 |    403 |         3 |
+----+------+--------+-----+------------+-----------+--------------+--------+--------+-----------+

# 3. 查询员工姓名中包含字母含o的员工的姓名和薪资
"""
模糊查询 like
	% 匹配任意多个字符
	_ 匹配单个任意字符
"""
select name,salary from emp where name like '%o%';
+------+----------+
| name | salary   |
+------+----------+
| xiao | 10000.21 |
+------+----------+

# 4. 查询员工姓名是由四个字符组成的姓名和薪资
(1) select name,salary from emp where name like '____';
(2) select name,salary from emp where char_length(name) = 4;
+------+----------+
| name | salary   |
+------+----------+
| xiao | 10000.21 |
| quan |    10.21 |
+------+----------+

# 5. 查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;
+----+------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|  1 | xiao | male   |  18 | 2017-03-17 | 主管      | NULL         | 10000.21 |    401 |         1 |
|  2 | quan | female |  68 | 2017-11-17 | teacher   | NULL         |    10.21 |    401 |         1 |
|  7 | c    | female |  23 | 2023-01-17 | sale      | NULL         |   642.21 |    402 |         2 |
|  8 | d    | male   |  28 | 2019-01-17 | sale      | NULL         |   642.21 |    402 |         2 |
|  9 | e    | female |  64 | 2012-02-17 | operation | NULL         |   234.32 |    403 |         3 |
| 10 | f    | male   |  23 | 2012-08-23 | operation | NULL         |   314.54 |    403 |         3 |
| 11 | g    | female |  67 | 2012-01-01 | operation | NULL         |   134.52 |    403 |         3 |
| 12 | h    | male   |  91 | 2012-09-21 | operation | NULL         |  1423.25 |    403 |         3 |
+----+------+--------+-----+------------+-----------+--------------+----------+--------+-----------+

# 6. 查询薪资不在642.21或者134.52或者10.21范围的数据
select * from emp where salary not in (642.21,134.52,10.21);
+----+-------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name  | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|  1 | xiao  | male   |  18 | 2017-03-17 | 主管      | NULL         | 10000.21 |    401 |         1 |
|  3 | zheng | male   |  78 | 2016-11-17 | teacher   | NULL         |    99.71 |    401 |         1 |
|  4 | zhang | female |  58 | 2010-08-17 | teacher   | NULL         |   100.21 |    401 |         1 |
|  9 | e     | female |  64 | 2012-02-17 | operation | NULL         |   234.32 |    403 |         3 |
| 10 | f     | male   |  23 | 2012-08-23 | operation | NULL         |   314.54 |    403 |         3 |
| 12 | h     | male   |  91 | 2012-09-21 | operation | NULL         |  1423.25 |    403 |         3 |
+----+-------+--------+-----+------------+-----------+--------------+----------+--------+-----------+

# 7. 查询岗位描述为空的员工姓名和岗位名
select name,post from emp where post_comment is null;
+-------+-----------+
| name  | post      |
+-------+-----------+
| xiao  | 主管      |
| quan  | teacher   |
| zheng | teacher   |
| zhang | teacher   |
| a     | sale      |
| b     | sale      |
| c     | sale      |
| d     | sale      |
| e     | operation |
| f     | operation |
| g     | operation |
| h     | operation |
+-------+-----------+

group by分组

# 分组实际应用场景
	男女比例
	部门平均薪资
	部门秃头率
	国家之间数据统计
	
# 1. 按照部门分组
select * from emp group by post;

"""
分组之后，最小可操作单位应该是组，而不是组内的单个数据
	上述命令在你没有设置严格模式的时候还是可正常执行的，返回的是分组之后，每个组的第一条数据，但是这不符合分组的规范；分组之后不应该考虑单个数据，而应该以组为操作单位（分组之后，没有办法直接获取组内单个数据）
	如果设置了严格模式，那么上述命令会直接报错
"""
set session sql_mode = 'strict_trans_tables,only_full_group_by';
show variables like '%mode';
select * from emp group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day03.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

设置严格模式之后，再分组，默认只能拿到分组的依据
select post from emp group by post;

按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助一些方法。

  • 聚合函数
  • concat 关键字
  • as 关键字

什么时候需要分组？
关键字： 每个、平均、最高、最低

聚合函数

# 1. 获取每个部门的最高薪资 （max）
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
# as 可以给字段取别名，也可以直接省略不写
+-----------+--------------+ +-----------+--------------+
| 部门      | 最高薪资       | | post      | max(salary) |
+-----------+--------------+ +-----------+-------------+
| operation |      1423.25 | | operation |     1423.25 |
| sale      |       642.21 | | sale      |      642.21 |
| teacher   |       100.21 | | teacher   |      100.21 |
| 主管       |     10000.21 | | 主管       |    10000.21 |
+-----------+--------------+ +-----------+-------------+

# 2. 获取每个部门的最低薪资（min）
select post,min(salary) from emp group by post;
# 3. 获取每个部门的平均薪资（avg）
select post,avg(salary) from emp group by post;
# 4. 获取每个部门的薪资总和（sum）
select post,sum(salary) from emp group by post;
# 5. 获取每个部门的人数（count）
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;# count对null 无法正常计数

concat 关键字

# 6. 查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat 从分组中获取分组中的数据
select post,group_concat(name) from emp group by post;
+-----------+--------------------+
| post      | group_concat(name) |
+-----------+--------------------+
| operation | e,f,g,h            |
| sale      | a,b,c,d            |
| teacher   | quan,zheng,zhang   |
| 主管      | xiao               |
+-----------+--------------------+

# group_concat还支持拼接操作
select post,group_concat(name,'_dsb') from emp group by post;
+-----------+------------------------------+
| post      | group_concat(name,'_dsb')    |
+-----------+------------------------------+
| operation | e_dsb,f_dsb,g_dsb,h_dsb      |
| sale      | a_dsb,b_dsb,c_dsb,d_dsb      |
| teacher   | quan_dsb,zheng_dsb,zhang_dsb |
| 主管      | xiao_dsb                      |
+-----------+------------------------------+
select post,group_concat(name,':',salary) from emp group by post;

# concat 不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;
+----------------------+-----------------------+
| concat('NAME:',name) | concat('SAL:',salary) |
+----------------------+-----------------------+
| NAME:xiao            | SAL:10000.21          |
| NAME:quan            | SAL:10.21             |
| NAME:zheng           | SAL:99.71             |
| NAME:zhang           | SAL:100.21            |
| NAME:a               | SAL:642.21            |
| NAME:b               | SAL:642.21            |
| NAME:c               | SAL:642.21            |
| NAME:d               | SAL:642.21            |
| NAME:e               | SAL:234.32            |
| NAME:f               | SAL:314.54            |
| NAME:g               | SAL:134.52            |
| NAME:h               | SAL:1423.25           |
+----------------------+-----------------------+

# concat_ws:
如果多个字段之间的连接符号是相同的情况下，你可以直接用concat_ws来完成。
select concat_ws(':',name,age,sex) from emp;
+-----------------------------+
| concat_ws(':',name,age,sex) |
+-----------------------------+
| xiao:18:male                |
| quan:78:female              |
| zheng:58:male               |
| zhang:48:male               |
| xu:18:female                |
| li:18:female                |
| chen:18:male                |
+-----------------------------+

as 关键字

as语法不单单可以给字段取别名，还可以给表起别名
select emp.id,emp.name from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | xiao  |
|  2 | quan  |
|  3 | zheng |
|  4 | zhang |
|  5 | a     |
|  6 | b     |
|  7 | c     |
|  8 | d     |
|  9 | e     |
| 10 | f     |
| 11 | g     |
| 12 | h     |
+----+-------+
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;  # 这样就可以了

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

分组注意事项

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

# 聚合函数只能在分组之后使用
select id,name,age from emp where max(salary) > 3000; # 这样是错误的
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;

having分组之后的筛选条件

having 的语法跟where是一致的，只不过having实在分组之后进行的过滤操作，即 having 是可以直接使用聚合函数的

# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于100的部门
select post,avg(salary) from emp 
	where age > 30 
	group by post
	having avg(salary) > 100;
+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| operation |  597.363333 |
| sale      |  642.210000 |
+-----------+-------------+

distinct去重

一定要注意，必须是完全一样的数据才可以去重！！！
一定不要将主键忽视了，有主键存在的情况下是不可能去重的！！！

select distinct id,age from emp;
select distinct age from emp;

# 补充：
"""
ORM    对象关系映射 
表					类
一条条的数据			对象
字段对应的值			对象.属性
你在写类的同时就意味着在创建表，用类生成对象就意味着在创建数据，对象.属性就是在获取数据字段对应的值。
目的就是减轻python程序员的压力，只需要会python面向对象的知识点就可以操作MySQL。
"""

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可以省略不写
也可以修改为降序，desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排，如果碰到age相同，则再按照salary升序排

# 统计各部门年龄在20岁以上的员工平均工资并且保留平均薪资大于10的部门，然后对平均工资降序排序
select post,avg(salary) from emp 
	where age > 20 
	group by post
	having avg(salary) > 10
	order by avg(salary) desc;
+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| sale      |  642.210000 |
| operation |  526.657500 |
| teacher   |   70.043333 |
+-----------+-------------+

limit限制展示条数

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

select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置，第二个参数是展示条数

正则表达式

语法

 属性名 REGEXP '匹配方式'
  • 其中，“属性名”表示需要查询的字段名称；
  • “匹配方式”表示以哪种方式来匹配查询。

匹配方式

  • “匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。
  • 下表列出了 REGEXP 操作符中常用的匹配方式。
选项 说明 例子 匹配值示例
^ 匹配文本的开始字符 ‘^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

案例

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

面试题

1. re模块中常用的方法
	finall: 分组优先展示 ^j.*(n|y)$  不会展示所有正则表达式匹配到的内容，而仅仅展示括号内正则表达式匹配到的内容。
	match: 从头匹配
	search: 从整体匹配

2. 贪婪匹配和非贪婪匹配
	正则表达式默认都是贪婪匹配的，将贪婪匹配变成非贪婪匹配只需要在正则表达式后面加？
	.*  贪婪
	.*?  非贪婪

标签：salary,group,name,过滤,emp,MySQL,post,筛选,select
From： https://www.cnblogs.com/xiao01/p/18050453

相关文章

  • MySQL之约束条件
    表完整性约束介绍：约束条件与数据类型的宽度一样，都是可选参数作用：用于保证数据的完整性和一致性主要分为：DEFAULT为该字段设置默认值NOTNULL标识该字段不能为空UNIQUEKEY(UK)标识该字段的值是唯一的AUTO_INCREMENT标识该字段的值自动增长（整数类型，而且为主......
  • Linux安装Docker并搭建MySql、Redis、RabbitMQ
    1.1安装docker(1)删除老版本sudoyumremovedocker\docker-client\docker-client-latest\docker-common\docker-latest\docker-latest-logrotate\......
  • MySQL之基本数据类型
    数据类型：类型类型举例整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT浮点类型FLOAT、DOUBLE定点数类型DECIMAL位类型BIT日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMT......
  • Docker启动MySQL容器自动停止问题
    可能是mysql使用的内存过多，导致系统杀死了对应的进程mysql启动后设置限制内存在宿主机任意位置下:vimdocker.cnf写入：skip-host-cacheskip-name-resolveperformance_schema_max_table_instances=400table_definition_cache=400table_open_cache=256performance_......
  • MySQL安装
    下载mysql官网地址：https://dev.mysql.com/downloads/mysql/配置解压文件解压下载好的压缩文件解压后目录中可能没有my.ini文件，自己创建一个使用记事本打开my.ini文件[mysqld]port=3306basedir=C:\ProgramFiles\MySQLdatadir=C:\ProgramFiles\MySQL\Datamax......
  • 新电脑装编程配置（jdk、MySQL、navicat、idea、Maven、gitee、环境配置）
    一、JDK（java运行环境，不安装不能在自己电脑上运行java语言）1.打开安装包     2.选择安装目录（建议选择非系统盘）      3.安装JRE（最好和JDK分开文件夹、建议选择非系统盘）     4.提示安装成功     配置环境变量：JAVA......
  • MySQL之存储引擎
    什么是存储引擎数据库存储引擎是数据库底层软件组织，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据。日常生活中文件格式有很多种，并且针对不同的文件格式会有对应不同存储方式和处理机制（txt、pdf、word、mp4等等）不同的存储引擎提供不同的存储机制、索引机制......
  • MySQL之SQL语句+严格模式
    SQL使用规范在数据库管理系统中，SQL语句关键字不区分大小写(但建议用大写)，参数区分大小写。建议命令大写，数据库名、数据表名、字段名统一小写，如数据库名、数据表名、字段名与关键字同名，使用反引号圈起来，避免冲突。SQL语句可单行或多行书写，默认以英文分号（;）结尾，关键词不能跨多......
  • MySQL之初识数据库
    linux安装更新YUM源：sudorpm-Uvhhttps://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm安装MySQL：sudoyum-yinstallmysql-community-server--enablerepo=mysql80-community--nogpgcheck启动数据库netstartmysql或者sudosystemctlstartmysq......
  • MySQL之MySQL数据库安装
    了解各种服务器的端口MySQL：3306Redis：6379MongoDB：27017Django：8000flask：5000MySQL介绍MySQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，目前属于Oracle旗下公司。MySQL最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(RelationalDa......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99