MySQL系列(五)之单表查询

表数据

#创建表
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','teacher',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);

# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

执行顺序

select ... from ... where ... group by ... having ... order by ... limit ...

where筛选条件

作用:是对整体数据的一个筛选操作
语法:
	1.判断的符号
	= (!= <>不等于) > >= < <=
	2.拼接不同的条件的关键字
	and or not 
	3.查询对应的区间值
	between 小值 and 大值[小值,大值] 查询两者之间的范围值
	4.查询具体在哪个范围中
	in(1,21,333,444) 指定范围
	5.模糊查询 like % 通配符  _ 通配符
		like "%b"  匹配以b结尾的任意长度的字符串
		like "b%"  匹配以b开头的任意长度的字符串
		like "%b%" 匹配字符串中含有b的任意长度的内容
		like "__b" 匹配总长度为3个字符,任意内容的字符串,并且以b结尾
		like "b_"  匹配总长度为2个字符,任意内容的字符串,并且以b开头

# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name 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的员工的姓名和薪资
模糊查询 like
		%  匹配任意多个字符
		_  匹配任意单个字符

select name,salary from emp where name like '%o%';

# 4.查询员工姓名是由四个字符组成的 姓名和薪资  char_length()
select name,salary from emp where char_length(name) = 4;

# 5.查询id小于3或者id大于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;

group by分组

什么时候需要分组？？？
	关键字: 每个 平均 最高 最低 
	聚合函数: max min sum count avg

# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名, 也可以直接省略不写, 但是不推荐, 因为省略的话语意不明确, 容易错乱

# 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.获取每个部门的人数
select post,count(id) from emp group by post;  # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post;  null不行

# 6.查询分组之后的部门名称和每个部门下所有的员工姓名 
# group_concat不单单可以支持你获取分组之后的其他字段值, 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;

# concat不分组的时候用 
select concat('NAME:',name),concat('SAL:',salary) from emp;

# 补充1: as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;

# 补充2: 查询每个人的年薪  12薪
select name,salary*12 from emp;

# 补充3: 分组注意事项
# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数

select id,name,age from emp where max(salary) > 3000;   报错
select max(salary) from emp;  # 不分组 默认整体就是一组

# 统计各部门年龄在30岁以上的员工平均薪资
1 先求所有年龄大于30岁的员工
   	select * from emp where age>30;
2 再对结果进行分组
   	select * from emp where age>30 group by post;
3 最终结果
    select post,avg(salary) from emp where age>30 group by post;

having分组之后的筛选条件

"""
having的语法跟where是一致的, 只不过having是在分组之后进行的过滤操作, 
即having是可以直接使用聚合函数的
"""

# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
		where age>30 
    	group by post
        having avg(salary) > 10000;

distinct去重

select distinct id,age from emp;
select distinct age from emp;

order by排序

# order by默认是升序 asc ,该asc可以省略不写, 也可以修改为降序 desc
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;

# 先按照age降序排  如果碰到age相同 则再按照salary升序排
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) desc;

limit限制展示条数

针对数据过多的情况 我们通常都是做分页处理
select * from emp limit 3;  # 只展示三条数据

第一个参数是起始位置, 第二个参数是展示条数
select * from emp limit 0,5;
select * from emp limit 5,5;