开局第一步：创表
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;
按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助一些方法。
什么时候需要分组？
聚合函数
# 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 (42 S22): 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 操作符中常用的匹配方式。
选项
说明
例子
匹配值示例
^
匹配文本的开始字符
‘^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. 贪婪匹配和非贪婪匹配
正则表达式默认都是贪婪匹配的，将贪婪匹配变成非贪婪匹配只需要在正则表达式后面加？
.* 贪婪
.* ? 非贪婪
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 终于写完轮子一部分：tcp代理 了，记录一下
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理