开局第一步:创表
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