MySQL 表查询

表查询

前期准备一张表

create table emp(
    id int not null unique auto_increment,
    name varchar(32) not null,
    gender enum('male','female','other') not null default 'male',
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(64),
    post_comment varchar(100),
    salary double(15,2),
    office int,  
    depart_id int
);
​
# 插入记录 三个部门:教学部 销售部 运营部
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), 
​
('egon','male',78,'20150302','teacher',1000000.31,401,1), # 以下是教学部
('kevin','male',81,'20130305','teacher',8300,401,1),
('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)
;

1. 语法执行顺序

# 初识查询语句
select id,name from emp where id >= 3 and id <= 6;
# 翻译:展示emp表中id大于等于3且小于等于6这些数据的id和name
'''
执行顺序:
from    # 确定是那张表
where   # 根据条件,筛选数据
group by    # 分组
having　　# 对分组后的表筛选
select  # 拿出筛选出来的数据中的某些字段
distinct　　# 去重

'''
# 关键字 where group by 同时出现的情况下,group by 必须在 where 之后
# where 先对整张表进行一次筛选,group by 再对筛选后的表进行分组
# 如何验证where是在group by之前执行而不是之后 利用聚合函数 因为聚合函数只能在分组之后才能使用
select id,name,age from emp where max(salary) > 3000;  # 报错！
​
select max(salary) from emp;  
# 正常运行，不分组意味着每一个人都是一组，等运行到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件

2.where约束条件(筛选条件)

# 1.查询id大于等于3小于等于6的数据
解析:
    筛选条件:id大于等于3小于等于6
    展示字段:没要求展示全部
select * from emp where id >= 3 and id <= 6;  
select * from emp where id between 3 and 6;  # 另一种写法

# 2.查询薪资是20000或者18000或者17000的数据
解析:
    筛选条件:薪资是20000或者18000或者17000
    展示字段:没要求展示全部
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 另一种写法

# 3.查询员工姓名中包含o字母的员工姓名和薪资
解析: 
    筛选条件:员工姓名中包含o字母,
    展示字段:姓名和薪资
ps:模糊匹配 like 
    % 匹配多个任意字符
    _ 匹配一个任意字符
select name,salary from emp where name like '%o%';

# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
解析:
    筛选条件:员工姓名是由四个字符组成
    展示字段:姓名与薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;  # 统计字符长度

# 5.查询id小于3或大于6的数据
解析:
    筛选条件:id小于3或大于6
    展示字段:全部
select * from emp where id <3 or id > 6;
select * from emp where id not between 3 and 6; 

# 6.查询薪资不在20000,18000,17000范围的数据
解析:
    筛选条件:薪资不在20000,18000,17000范围
    展示字段:全部
select * from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工名与岗位名
解析:
    筛选条件:岗位描述为空
    展示字段:员工名与岗位名
select name,post from emp where post_comment = null;  # 查询为空
select name,post from emp where post_comment is null;  # 判断空只能用 is
# 查询岗位描述为不空的员工名与岗位名
select name,salary from emp where post_comment is not null;

3.group by 分组

# 数据分组应用场景: 每个部门的平均薪资,男女比例等
初识语句:
select * from emp group by post; # 分组后拿到的是每组的第一条数据
select id,name,gender from emp group by post;  # 对
分组后应该做到最小单位是组,而不应该在展示组内的单个数据信息
分组之后,只能拿到分组的字段信息,无法直接获取其他字段信息,但是可以通过(聚合函数)间接获取
'''
设置sql_mode为only_full_group_by，意味着以后分组,只能取到分组的依据,不应该再取组里面的单个元素值,那样分组就没有意义了,因为不分组就是对单个元素信息的随意获取
'''
show variables like '%mode%';
set global sql_mode="strict_trans_tables,only_full_group_by";
设置严格模式后要重启mysql
# 注意: 如果一张表没有group by 默认所有的数据就是一个组

# 设置严格模式后
select * from emp group by post;  # 报错
select id,name from emp group by post;  # 报错
# 1.按部门分组
select post from emp group by post;
# 强调 分组后就不能直接查到单个数据信息了,只能获取组名

聚合函数

# 以组为单位,统计组内数据>>>聚合查询(数据聚集到一起计算结果)
# 必须在分组之后使用
# 聚合函数会自动将每一个分组内的单个数据做想要的计算
max()   最大值
min()   最小值
avg()   平均值
sum()   求和
count() 计数,有几个计算几个
concat() 一般用来拼接字符串 # 不在组内使用
group_concat() 用来显式组内指定字段对应的数据或拼接字符串的作用

# 2.获取每个部门的最高工资
解析:
    筛选条件:无
    分组:按部门
    展示:部门的最高工资
select post,max(salary) from emp group by post;

# 每个部门最低工资
select post,min(salary) from emp group by port;
# 每个部门平均工资
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计数
select post,count(age) from emp group by post;  # 按年龄计数
select post,count(salary) from emp group by post;  # 按薪资计数
select post,count(post_comment) from emp group by post;  
# 注意: 在统计分组内个数的时候,填写任意非空字段都可以完成统计,推荐使用id字段

# 3.查询分组之后的部门名称和每个部门下所有的学生姓名
解析:
    筛选条件:无
    分组:按部门
    展示:部门名称 每个部门下所有的学生姓名
select post,name from emp group by post;  # 报错
select post,group_concat(name) from emp group by post;
select post,group_concat('NB_',name) from emp group by post;  # 拼接字符串
select post,group_concat(name,':',salary) from emp group by post; 

# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as '薪资' from emp;

# 补充as语法 即可以给字段起别名也可以给表起
select emp.id,emp.name from emp as t1; # 报错  因为表名已经被你改成了t1
select t1.id,t1.name from emp as t1;
# 查询四则运算
# 查询每个人的年薪
select name,salary*12 as '年薪' from emp;

练习题

1. 查询岗位名以及岗位包含的所有员工名字
解析:
    筛选条件:无
    分组:按岗位
    展示:岗位名,岗位里的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
解析:
    筛选条件:无
    分组:按岗位
    展示:岗位名,岗位里的员工个数
select post,count(id)from emp group by post;
3. 查询公司内男员工和女员工的个数
解析:
    筛选条件:无
    分组:按性别
    展示:性别,男女个数
select gender,count(id) from emp group by gender;
4. 查询岗位名以及各岗位的平均薪资
解析:
    筛选条件:无
    分组:按岗位
    展示:岗位,平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
解析:
    筛选条件:无
    分组:按岗位
    展示:岗位,最高薪资
select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
解析:
    筛选条件:无
    分组:按性别
    展示:;性别,平均薪资
select gender,avg(salary) from emp group by gender;
8.统计各部门年龄在30岁以上的员工平均工资
解析:
    筛选条件:年龄在30岁以上
    分组:按部门
    展示:;部门,平均薪资
select post,avg(salary) from emp where age > 30 group by post;

4.having 用在group by 之后的筛选

having 的语法格式与 where 一致,只是 having 是在分组之后进行过滤筛选
where 不能用聚合函数,但是 having 可以用聚合函数
1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
解析:
    where筛选:年龄在30岁以上
    group by分组:部门
    having筛选:保留平均工资大于10000部门
    select展示:部门,平均薪资
select post,avg(salary) as '平均薪资' from emp 
    where age > 30 
    group by post 
    having avg(salary) > 10000;
select * from emp where salary > 10000; # 对
select avg(salary) from emp having salary > 10000; # 错
select avg(salary) from emp having avg(salary) > 10000; # 对
select post,avg(salary) from emp group by post having avg(salary) > 10000 ; # 对
select max(salary) from emp where age > 20; # 对
select max(salary) from emp having age > 20; # 错

5.distinct 去重

# 对有重复的展示数据进行去重
select distinct post from emp;
select distinct age from emp;
select distinct id,age from emp;  # 不能去重,id + age 组合的数据没有重复的
'''
去重的数据必须是一模一样的,只要有一个不一样都不能去重
distinct 在select之后执行
'''

6.order by 排序

1.asc 升序
2.desc 降序
# 1.按salary排序
select * from emp order by salary asc; # 默认升序
select * from emp order by salary desc; # 降序
# 2.按age降序排,年龄相同的情况下按salary升序拍
select * from emp order by age desc,salary asc;

# 3.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
解析:
    where筛选:年龄在10岁以上
    group by分组:按部门
    having筛选:并且保留平均工资大于1000的部门
    order by排序:平均工资
    select展示:部门,平均工资
select post,avg(salary) from emp 
    where age > 10 
    group by post 
    having avg(salary) > 1000
    order by avg(salary) asc;

7.limit 限制展示数量

# 限制展示3条数据
select * from emp limit 3;
# 查询工资最高的人的详细信息
解析:
    按工资降序,再显示一条数据,就是工资最高的人的详情信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5;  # 第一个参数是起始位置,第二个参数是显示的条数

8.正则

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