mysql-07mysql单表查询
一丶语法顺序
- select
- from
- where
- group by
二丶执行顺序
- from
- where
- group by
- select
实验表准备
创建表
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), #以下是教学部
('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)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
三丶where约束条件
在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
# 1.查询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的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000, 18000, 17000);
# 3.查询员工姓名中包含o字母的员工姓名和薪资
select * from emp where name like "%o%";
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like "____";
# 5.查询id小于3或者大于6的数据
select * from emp where id<= 3 or id>= 6;
select * from emp where not id between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment is not NULL;
四丶group by
分组后可以使用聚合函数
# 数据分组应用场景：每个部门的平均薪资，男女比例等
# 1.按部门分组
select post from emp group by post;
# 2.获取每个部门的最高工资
# 每个部门的最高工资()
select post,max(salary) 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;
# 3.查询分组之后的部门名称和每个部门下所有的成员姓名
select post,group_concat(name) from emp group by post;
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
select post, group_concat("教师:",name) from emp group by post;
# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
select concat("薪资:",salary),concat("姓名:",name) from emp where salary >= 5000;
# 补充as语法 即可以给字段起别名也可以给表起
select concat("薪资:",salary) as "薪资",concat("姓名:",name) as "姓名" from emp where salary >= 5000;
# 查询四则运算
# 查询每个人的年薪
select concat("姓名:",name) as "姓名",concat("薪资:",salary*12) as "年薪" from emp where salary >= 5000;
五丶having
having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！
强调：having必须在group by后面使用!!!
实验表建立
select post,avg(salary) from emp2
where age >= 30
group by post
having avg(salary) > 10000;
#强调：having必须在group by后面使用
select * from emp having avg(salary) > 10000; # 报错
六丶distinct
对有重复的展示数据进行去重操作
select distinct post from emp;
七丶order by
排序
asc(默认) 升序
desc 降序
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)
;
八丶limit
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
九丶正则
select * from emp where name regexp '^j.*(n|y)$'; #这边的正则解读为以j开头,以y或者n结尾,中间尽可能匹配更多的字符
