MySQL的单表查询关键字和多表查询
查询数据为主
- 前提准备
- 关键字where
- 关键字group by
- 聚合函数
- 关键字having过滤
- 关键字dictinct
- 关键字order by
- 关键字limit
- 关键字regexp正则（了解）
- 关键字COALESCE
前提
生成实验数据
# 生成表 create table emp( id int primary key auto_increment, name varchar(20) not null, gender 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,gender,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);
关键字where
功能：
对整个个表数据的进行一次初步筛选操作
语法：
select 字符名 from 名 where 筛选条件
例1：查询id大于等于3小于等于6的数据
select * from emp where id between 3 and 6;
例2：查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary =17000 or salary=18000; # 其他写法 select * from emp where salary in (20000,18000,17000);
模糊查询
关键字:like
关键符号：%：匹配任意个数的任意字符
_:一横表示匹配一个字符，多个横表示匹配多个字符
例1：查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';
例2：查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____'; # 其他方法，其他语言的运用 select name,salary from emp where char_length(name)=4;
例3：查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (17000,18000,20000);
例4：查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment is NULL; # 报错 select name,post from emp where post_comment=NULL;
关键字group by分组
功能：
把所有个体通过某种条件组合为某种整体
语法：
select 字符名 from 表名 group by 条件字符名；
例1：:按照部门分组
select * from emp group by post； # 报错 ''' 在严格模式下会直接报错，因为在分组之后，无法获得内部单个个体的所有数据，为了获取信息，需要接祖一些其他方法 ''' # 严格模式设置 set global sql_mode="strict_trans_tables,only_full_group_by"; select post from emp group by post;
聚合函数
主要用于分组之后的数据处理
- max：最大值
- min：最小值
- avg：平均
- sum：求和
- count：计数
例1：获取每个部门的最高工资
select post,max(salary) from emp group by post;
例2：获取每个部门的最低薪资
select post,min(salary) from emp group by post;
例3：获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
例4：获取每个部门的薪资总和
select post,sum(salary) from emp group by post;
例5：获取每个部门的员工人数
# 使用count优先选择主键 select post,count(id) from emp group by post;
关键字having过滤
功能：
功能与where相同，通过条件筛选需要的数据内容
语法：
select 字符名 from 表名 group by 分组字符名 having 条件；
having与where的区别
where用于分组之前的筛选
having用于分组之后的过滤
例1：统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary)>10000;
关键字distinct
功能：
顾名思义此关键字适用于去重
语法：
select distinct post from emp； ''' 去重的前提是数据必须完全相同 '''
# 默认为升序 select * from emp order by salary;
# asc是升序关键字 不写则默认 select name,salary from emp order by salary asc;
# desc是降序的意思，在这里不是显示 select name,salary from emp order by salary desc;
例：统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
# 年龄大于10
where age>10
# 按部门分组
group by post
# 平均工资大于1000
having avg(salary)>1000
# 按平均工资分配
order by avg(salary);
关键字limit
功能：
用于对数据进行分页处理 控制展示信息节省资源
语法：
select * from emp limit 5； # 一个数字表示条数,显示5行
# 第一个数字表示起始位置 第二个数字表示显示个数 起始行不显示
select * from emp limit 3,3;
例1：查询工资最高的人的详细信息
select name,salary from emp order by salary desc limit 1;
关键字regexp正则（了解）
功能：
使用特殊符号的组合，在表中来获取符合条件的数据内容
语法：
# 查询姓名是以字母j开头 n或者y结尾的数据 select name from emp where name regexp '^j.*(n|y)$';
关键字COALESCE
功能：
这个函数主要用来进行空值处理
语法：
COALESCE ( expression,value1,value2……,valuen) ''' COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。 COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。 如果expression不为空值则返回expression；否则判断value1是否是空值， 如果value1不为空值则返回value1；否则判断value2是否是空值， 如果value2不为空值则返回value2；……以此类推， 如果所有的表达式都为空值，则返回NULL。 '''
小练习
1. 查询岗位名以及岗位包含的所有员工名字
select name,post from emp;
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岁以上的员工平均工资
select post,avg(salary) from emp where age>30 group by post;
