数据库进阶知识（二）
exp:首先创建一个示例表
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
);
插入相应的记录
#三个部门：教学，销售，运营
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 empG;
where筛选条件
- 查询ID大于等于3小于等于6 的数据
way1:
select * from emp where id >= 3 and id <= 6;
way2:
select id as "编号",name as "姓名",sex as "性别" from emp where id between 3 and 6;
- 查询薪资是20000或者18000或者17000的数据
way1:
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
way2:
select * from emp where salary in (20000,18000,17000);
- 查询员工姓名中包含字母o的员工的姓名和薪资
# 模糊查询
like % 匹配任意多个字符
- 匹配任意单个字符
select name as "姓名",salary as "薪资" from emp where name like "%o%";
类似：查询员工姓名是由4个字符组成的 姓名和薪资
way1:
select name as "姓名",salary "薪资" from emp where name like "____";
way2;
select name as "姓名",salary "薪资" from emp where char_length(name) = 4;
- 查询ID<3或者ID>6的数据
# 使用关键字not
select * from emp where id not between 3 and 6;
类似：查询薪资不在2000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
- 查询岗位描述为空的员工姓名和岗位名
# 针对null不能使用等号，而应该使用is
select name,post from emp where post_comment is NULL;
group by 分组
# 分组要根据实际的应用场景
exp:
男女比例
新冠病毒分布比例
部门平均工资
部门秃头率
......
- 按照部门进行分组
select * from emp group by post;
ERROR 1055 (42000): 'day47_practice.emp.id' isn't in GROUP BY
在严格模式下，分组默认只能拿到分组的依据
# 查看是否处于严格模式
show variables like "%mode";
# 设置严格模式
set global sql_mode = 'strict_trans_tables';
查看分组的依据
select post from emp group by post;
其他的一些字段需要借助于一些方法（聚合函数）来展示
"""
关键字：每个、平均、最高、最低
聚合函数：max min sum count avg
"""
#1.获取每个部门的最高工资
select post as "部门",max(salary) as "最高薪资" from emp group by post;
#2.获取每个部门的最低工资
select post as "部门",min(salary) as "最低薪资" from emp group by post;
#3.获取每个部门的平均工资
select post as "部门",avg(salary) as "平均薪资" from emp group by post;
#4.获取每个部门的工资总和
select post as "部门",sum(salary) as "工资总和" from emp group by post;
#5.获取每个部门的人数
select post as "部门",count(id) as "人数统计" from emp group by post;
# count统计的是能够标识数据的字段（唯一且不为空，NULL不行)
#6.查询分组之后的部门名称和每个部门下所有的员工姓名
select post as "部门名称",group_concat(name) as "员工" from emp group by post;
# group_concat不单单可以支持你获取分组之后的其他字段值，还支持拼接操作
select post as "部门名称",group_concat(name,':',salary) as "员工" from emp group by post;
#不分组的时候使用concat
select concat('NAME:',name),concat('SALARY:',salary) from emp;
#补充：as语法不仅仅可以给字段起别名，还可以给表临时起别名
#7.查询每个人的年薪（12薪）
select name as "姓名",post as "部门",salary*12 as "年薪" from emp;
group分组注意事项
#关键字where和group同时出现的时候group by必须在where后面
#where先对整体数据进行过滤在进行分组操作
#where的筛选条件不能使用聚合函数
exp:统计各个部门年龄在30岁以上的员工的平均工资
select post as "部门名称",avg(salary) as "平均薪资" from emp where age > 30 group by post;
having分组之后的筛选条件
"""
having 的语法与 where是一致的，
不过where是做的分组之前的过滤操作
而having是做的分组之后的过滤操作
having是可以直接使用聚合函数的
"""
- 统计各个部门年龄在30岁以上的员工平均工资，并保留平均工资大于10000的部门
select post as "部门名称",avg(salary) as "平均工资" from emp where age > 30 group by post having avg(salary) > 10000;
distinct去重
#注意：必须是完全一样的数据才能够去重（即若筛选字段包含主键的话，是无法完成去重的）
ORM 对象关系映射
"""
表 类
一条条数据 对象
字段对应的值 对象的属性
"""
select distinct age from emp;
order by排序
"""
order by默认是升序 asc 该asc可以省略不写
也可以修改为降序 desc
"""
exp:统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post as "部门名称",avg(salary) as "平均工资" from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) desc;
limit限制展示条数
"""
针对数据过多的情况下，我们通常是做分页处理
"""
limit 起始位置,展示条数
exp:
select * from emp limit 3;# 只展示3条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
正则操作
select * from emp where name regexp '^j.*(n|y)$';
多表操作
- 数据准备
create table dep(
id int,
name varchar(20)
);
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 emp,dep;
# 拼表操作
select * from emp,dep where emp.dep_id = dep.id; # 等价于内连接
"""
四种拼表方法：
1.inner join 内连接
2.left join 左连接
3.right join 右连接
4.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;
嵌套查询
将一个查询语句的结果当做另外一个查询语句的条件去用
exp:查询部门是技术或者人力资源的员工信息
select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
"""
多表查询就两种方式
先拼接表再查询
子查询 一步一步来
"""