四、 单表查询的关键字、函数

一、单表查询准备

create table emp(
  id int primary key 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
('YLY','male',18,'20210101','CN帅giegie代言人',6.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);

二、查询关键字

1、select
2、from
3、where
4、group by
	聚合函数
    having
3、distinct
4、order by

1、select命令

select
	控制查询表中的哪些字段对应的数据

2、from

from
	控制查询的表

3、where (分组前筛选)

# where其实就是对数据进行筛选
	格式：
		where [字段] in ();
		where [字段] not in ();
		where [字段] between num_a and num_b;
			条件:有or和and。
        where [字段] like "% _";
        where [字段] >、<、= num_c;
        where [字段] is null;
        where [字段] char_length(num_d);
        

案例1.查询id大于等于3小于等于6的数据
    select id,name 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);  # 简写

"""
模糊查询
	关键字       like
	关键符号
				%:匹配任意个数的任意字符
				_:匹配单个个数的任意字符
"""
案例3.查询员工姓名中包含o字母的员工姓名和薪资
	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的数据
	select *  from emp where id not 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 = NULL;  # 查询为空！
    select name,post from emp where post_comment is NULL;
    select name,post from emp where post_comment is not NULL;

4、group by

分组
	按照某个指定的条件将单个单个的数据分为一个个整体
 		eg:
			咱班按照座位横向分组
             咱班按照年龄分组
             咱班按照省份分组
    应用场景
    	eg:
            求每个部门的平均薪资
            求每个国家的人均GDP
            求男女平均薪资
        
格式：
	select [分组的信息] from [表名] [where筛选] group by [分组字段] [having过滤];
"""
分组之后不再以单个个体为研究对象 也无法直接再获取单个个体的数据
研究对象应该是分组的整体
	分组之后默认只能直接获取到分组的依据 其他字段数据无法直接获取

如果需要实现上述要求 还是修改sql_mode
	set global sql_mode='only_full_group_by';
"""
案例1.获取每个部门的最大薪资
	select post,max(salary) from emp group by post;
  
案例2.统计每个部门的人数
	select post,count(id) from emp group by post;
    
案例3.获取每个部门的员工姓名
	select post,group_concat(name,'|',salary) from emp group by post;
    # group_concat用于分组之后获取分组以外的字段数据并支持拼接
    # concat用于分组之前的拼接操作
    	select id,concat(name,'|',salary) as '嘿嘿' from emp;
    # concat_ws当多个字段连接符相同的情况下推荐使用
    	select id,concat_ws('|',name,sex,salary,age) from emp;
    
"""
在查看结果的时候可以给字段起别名
	select post as '部门',max(salary) as '最高薪资' from emp group by post;
	as可以省略但是为了语义更加明确建议不要省略
"""

4.1 聚合函数

max()
min()
sum()
count()
avg()
# 上述聚合函数都是在分组之后使用 用于操作整体数据

4.2 having (分组后过滤)

where与having都是用来筛选数据的
	但是where用于分组之前的筛选、having用于分组之后的筛选
    为了人为的区分开 我们将where用筛选来形容 having用过滤来形容
    
# 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
	select post,avg(salary) from emp where age > 30 group by post having avg(salary)>10000;

"""
将一个复杂的查询题拆分成多个简单的小题
"""

3、distinct去重

"""
去重的前提示是存在一模一样的数据
	如果存在主键肯定无法去重
"""
# 对有重复的展示数据进行去重操作 一定要是重复的数据
select distinct id,age from emp;
select distinct post from emp;

4、order by排序

order by默认是升序  默认的关键字是asc
	select * from emp order by salary asc;
	也可以改为降序 desc
    select * from emp order by salary desc;
 
# order by排序支持多个字段组合(第一个不行 就往后继续排)
	select * from emp order by age,salary;
    select * from emp order by age asc,salary desc;

5、limit 分页

limit 分页
	 案例一：显示前5条记录
	 	select * from emp limit 5; 

	 案例二：显示从第6条记录开始往后3条数据
	 	select * from emp limit 5,3;

	综合案例:显示薪资最高的员工的所有数据
		select * from emp order by salary desc limit 1;

6、regexp 正则匹配

regexp	正则匹配	格式:		regexp "正则表达式"	案例一：筛选出"j"开头,"y"或者"k"结尾的名字的所有数据		select * from emp where name regexp "^j.*[y|k]$";	# 使用[]		或者		select * from emp where name regexp "^j.*(y|k)$";	# 使用()

7、exists 条件 存在

exists	是否存在    '后接条件，如果条件为真，则执行之前的内容匹配，如果为假，则不执行。'    EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，    而是返回一个真假值，True或False。    当返回True时，外层查询语句将进行查询    当返回值为False时，外层查询语句不进行查询。    select * from emp    where exists    (select id from emp where id > 3);	# emp表中存在id>3的，exists为真，执行显示emp所有的数据    select * from emp    where exists    (select id from emp where id > 250);    # emp表中不存在id>250的，exists为假，不查询emp所有的数据

三、单表查询总结

1、写单表查询语句顺序

当查询比较复杂的时候，一步步写，先where筛选，后having过滤，最后再排序,去重。

2、关键词总结

单表查询的格式:	SELECT [查询字段] FROM [表名] [where筛选语句] [显示规则] [having过滤条件]----------------------------------------------------where	like "% _"	between	in / not in	and / or	is / is not	char_length(字段)=num	group by	修改sql_mode	set global sql_mode='only_full_group_by';	group_concat("")	#将多个字段值进行拼接	distinct	select distinct id,age from emp;order by	select * from emp order by age,salary;limit	select * from emp limit 1;regexp	select * from emp where name regexp "^j.*[y|n]$";exists----------------------------------------------------函数方法：1、	concat(str1,str2,...)	将多个字段进行拼接		用法:		 select id,concat(name,'|',salary) from emp;	concat_ws(separator，str1,str2,...)		用分隔符将多个字段进行拼接		用法:		 select id,concat_ws('|',name,salary,age,post) from emp;	group_concat(str1,str2,..)  分组之后的拼接		用法：		 select group_concat("|",name,"-",age,"-",post,"|") from emp group by post;2、	max(str)3、	min(str)4、	sum(str1)5、	count(id)		# 计数有多少个不重复的数据6、	avg(str1)

四、上述案例集合

1.查询id大于等于3小于等于6的数据2.查询薪资是20000或者18000或者17000的数据3.查询员工姓名中包含o字母的员工姓名和薪资4.查询员工姓名为四个字符组成的员工姓名和薪资5.查询id小于3或者大于6的数据6.查询薪资不在20000，18000，17000范围的数据7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is1.获取每个部门的最大薪资2.统计每个部门的人数3.获取每个部门的员工姓名1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门案例一：显示前5条记录案例二：显示从第6条记录开始往后3条数据综合案例:显示薪资最高的员工的所有数据

五、测验

1. 查询岗位名以及岗位包含的所有员工名字2. 查询岗位名以及各岗位内包含的员工个数3. 查询公司内男员工和女员工的个数4. 查询岗位名以及各岗位的平均薪资5. 查询岗位名以及各岗位的最高薪资6. 查询岗位名以及各岗位的最低薪资7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

答案：

1、select post as "岗位",group_concat(name) as "姓名" from emp group by post;2、select post as "岗位",count(id) as "员工个数" from emp group by post;3、select post as "岗位",count(sex="male") as "男员工个数",count(sex="female") as "女员工个数" from emp group by post;4、select post as "岗位",avg(salary) as "平均薪资" from emp group by post;5、select post as "岗位",max(salary) as "最高薪资" from emp group by post;6、select post as "岗位",min(salary) as "最低薪资" from emp group by post;7、select sex,avg(salary) from emp group by sex;