04数据库——数据库表单查询（where ，分组，聚合函数，筛选，去重，排序）、多表
查询、⼦查询
前期表准备
('tank','male',73,'20140701','teacher',3500,401,1),('owen','male',28,'20121101','teacher',2100,401,1),('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),('sean','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);#ps ：如果在windows 系统中，插⼊中⽂字符，select 的结果为空⽩，可以将所有字符编码统⼀设置成gbk 创建表，插⼊数据
⼀、语法的执⾏顺序select * from emp\G;
当表字段特别多的时候 结果的排版可能会出现混乱的现象 你可以在查询语句加\G 来规范查询结果#
语法顺序select from
where group by (having)# 再识执⾏顺序from where
group by (having)
select
#完整版sql 语句的查询
select distinct post,avg(salary)
from table1 where id > 1
group by post
having avg(salary) > 1000
order by avg(salary)
limit 5,5
⼆、where 约束条件
"""模糊匹配  like
%：匹配多个任意字符    _：匹配⼀个任意字符
三、group by 分组1.分组前戏 ——设置严格模式select * from emp group by post;  # 报错select id,name,sex from emp group by post;  # 报错
select post from emp group by post;  # 获取部门信息#查询详细信息报错，只能查询到分组的信息，说明设置成功
强调：只要分组了，就不能够再“直接”查到单个数据信息了，只能获取到组名2.聚合函数 max min avg sum count 以组为单位统计组内数据>>>聚合查询(聚集到⼀起合成为⼀个结果)
如果⼀张表没有写group by 默认所有的数据就是⼀组#在分组后，即select 后⾯或者having 后⾯才能使⽤
# 每个部门的最⾼⼯资
select post,max(salary) from emp group by post;
PS：给字段取别名（as 也可以省略，但是⼀般不要这样⼲）
select post as 部门,max(salary) as 最⾼⼯资 from emp group by post;
# 每个部门的最低⼯资
select post,min(salary) from emp group by post;
# 每个部门的平均⼯资
select post,avg(salary) from emp group by post;
# 每个部门的⼯资总和
select post,sum(salary) from emp group by post;
# 每个部门的⼈数总数
select post,count(id) from  emp group by post;
在统计分组内个数的时候，填写任意⾮空字段都可以完成计数，推荐使⽤能够⾮空且唯⼀标识数据的字段，⽐如id 字段
# 聚合函数max min sum count avg 只能在分组之后才能使⽤，也就是紧跟着select ⽤或者紧跟着having （分组后的⼆次where ）
select id,name,age from emp where max(salary) > 3000; # 报错！
select max(salary) from emp;
# 正常运⾏，不分组意味着每⼀个⼈都是⼀组，等运⾏到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件
group_concat（分组之后⽤）不仅可以⽤来显⽰除分组外字段还有拼接字符串的作⽤
#查询分组之后的部门名称和每个部门下所有⼈的姓名
select post,group_concat(name) from emp group by post;
#在每个⼈的名字前后拼接字符
select post,group_concat('D_',name,"_SB") from emp group by post;
#group_concat()能够拿到分组后每⼀个数据指定字段(可以是多个)对应的值
select post,group_concat(name,": ",salary) from emp group by post;
（不分组时⽤）就是⽤来拼接字符串达到更好的显⽰效果
select name as 姓名,salary as 薪资from emp;
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资from emp;
# 如果拼接的符号是统⼀的可以⽤ concat_ws
select concat_ws(':',name,age,sex) as info from emp;
⼩技巧:
concat就是⽤来帮你拼接数据，不分组情况下使⽤
group_concat  分组之后使⽤，可以拼接数据也可以⽤来显⽰其他字段信息
# 补充as语法既可以给字段起别名也可以给表起
select emp.id,emp.name from emp as t1; # 报错因为表名已经被你改成了t1
select t1.id,t1.name from emp as t1;
3.查询四则运算
# 查询每个⼈的年薪
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp;  # as可以省略
4.练习题
"""
View Code
8、统计各部门年龄在30岁以上的员⼯平均⼯资
四、having 筛选
跟where是⼀模⼀样的也是⽤来筛选数据
但是having是跟在group by之后的
where是对整体数据做⼀个初步的筛选
⽽having是对分组之后的数据再进⾏⼀次针对性的筛选
1、统计各部门年龄在30岁以上的员⼯平均⼯资，并且保留平均⼯资⼤于10000的部门
select post,avg(salary) from emp where age > 30 group by post where avg(salary) > 10000;  # 报错select post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
强调：having必须在group by后⾯使⽤
select * from emp having avg(salary) > 10000;  # 报错
五、distinct 去重
# 对有重复的展⽰数据进⾏去重操作
#去重⼀定要满⾜数据是⼀模⼀样的情况下才能达到去重的效果
#如果你查询出来的数据中包含主键字段，那么不可能去重成功
#只要有⼀个不⼀样都不能算是的重复的数
select distinct id,age from emp; #去重失败，id不⼀样，即使age⼀样也没⽑⽤
select distinct post from emp; #成功
六、limit 限制条数
# 限制展⽰条数
select * from emp limit 5;  # 只展⽰数据的五条
# 分页显⽰
select * from emp limit 5,5; #第6条开始，往后展⽰5条
当limit只有⼀个参数的时候表⽰的是只展⽰⼏条
当limit有两个参数的时候第⼀个参数表⽰的起始位置，是索引
第⼆个参数表⽰从起始位置开始往后展⽰的条数
# 查询⼯资最⾼的⼈的详细信息
select * from emp order by salary desc limit 1;
七、regexp 正则
# 在编程中只要看到reg开头的基本上都是跟正则相关
select * from emp where name regexp '^j.*(n|y)$';
re模块中
findall：分组优先会将括号内正则匹配到的优先返回
match：从头开始匹配匹配到⼀个就直接返回
res = match('^j.*n$','jason')distinct查询
up())
search：整体匹配匹配到⼀个就直接返回
⼋、order by 排序
select * from  emp order by salary asc; #默认升序排
select * from  emp order by salary desc; #降序排
select * from  emp order by age desc; #降序排
#先按照age 降序排，在年纪相同的情况下再按照薪资升序排
select * from  emp order by age desc,salary asc;
# 统计各部门年龄在10岁以上的员⼯平均⼯资，并且保留平均⼯资⼤于1000的部门，然后对平均⼯资进⾏排序select post,avg(salary) from  emp
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary)
;
九、多表查询
(203,'运营');insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),('kevin','male',38,201),
('nick','female',28,202),('owen','male',18,200),('jerry','female',18,204);# 当初为什么我们要分表，就是为了⽅便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成⼀张表进⾏查询才合理创建表
当初为什么我们要分表，就是为了⽅便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成⼀张表进⾏查询才合理
#笛卡尔积select * from  emp,dep;  # 左表⼀条记录与右表所有记录都对应⼀遍，即10*4=40条 >>>笛卡尔积# 将所有的数据都对应了⼀遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是出合理的数据# 查询员⼯及所在部门的信息select * from  emp,dep where emp.dep_id = dep.id;#
查询部门为技术部的员⼯及部门信息select * from  emp,dep where emp.dep_id = dep.id and  dep.name = '技术';
其实将两张表关联到⼀起的操作，有专门对应的⽅法：内连接、左连接、右链接、全连接

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。