Mysql系列之四（sql语句）

前期准备

### 前期表准备
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), #以下是教学部
('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;

# 在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK

sql语句中的关键字以及执行顺序

关键字

select 
from 
where
like
	% # 查询零个到多个
	_ #  查询一个
having
group by 
order by 
distinct
limit 
regexp

执行顺序

select * from t1 where id >10;
执行顺序是先执行from找到表，再执行where筛选内容，最后将所选择的字段拿出

where筛选

where筛选时对整张表的操作，如以下例子

# 1.查询id大于等于3小于等于6的数据
select * from emp where id >=3 and id <= 6;
select * from emp where id between 3 and 6;
# between and 取值取的是[]集,即闭区间

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 180000 or salary =17000;
select * from emp where salary in (20000,18000,17000);

# 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或者id大于6的数据
select name from emp where id not between 3 and 6; 

# 6.查询薪资不在20000,18000,17000范围的数据
select name from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工姓名和岗位名 
# 针对关键字null要用is 而不能用等号
select * from emp where port_comment is null;

group by分组

group by介绍

当我们要统计比例，计算最低最高值等等的时候常常会用到分组
分组之后，最小的操作单位就是一个组，我们只能拿到分组依据的本生，如果想要拿到单个个体的数据则在严格模式下会报错。
我们可以使用一些聚合函数来获取想要的数据：
	min
	max
	sum
	avg
	count
	group_concat
	等等

# 开启严格模式
set global sql_mode = 'strict_trans_tables,only_full_group_by';

group by案例

# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post,max(salary) as '最高薪资' from emp group by post; #as 可以更改字段名，也可以更改表名，可以不写也可以写，一般写上。
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.获取每个部门的人数
#可以统计任意字段一般统计id字段
select post,count(id) from emp group by post;
select post,count(post) from emp group by post;
...

# 6.查询分组之后的部门名称和每个部门下所有的员工姓名 
# group_concat：1.能够获取所有的单个数据，2.支持字符串拼接，3.支持行拼接
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,'dsb') from emp where id > 3;

# 7.补充 as
1.给数据列起别名
2.给表起别名
select emp.name from emp ;
select emp.name from emp as t1;  #报错
select t1.name from emp as t1;
# 8.支持数据的数学运算
select name,salary*6 from emp;

group by注意事项

1. group by如果和where同时出现一定要在where的后面
2. 聚合函数如果在where后使用则会报错
3. 如果没有分组，那么一张完整的表就视为一个组，则此时可以使用
4. 聚合函数基本只用在from之前，和字段名匹配使用

having

having的用法与where一致，只不过他是专门用在group by后面的，可以使用聚合函数作为条件放在having后面

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

distinct

distinc是用来实现去重的
一定要数据完全一样才能够去重
而在一个表中必须要有一个字段每个都不一样来保证数据的独特性，
所以 如果我们要实现去重，那么我们绝对不能带上主键列，否则无法实现去重

select distinct id,name from emp;# 无法实现去重效果
select distinct name from em;# 实现了去重的效果

order by

order by 是用来实现数据的排列的

具体方法
升序：order by 字段名 asc # 不加默认升序
降序：order by 字段名 desc 
多行排列： order by 字段名 desc,字段名asc # 先按照第一个排列，如果有想同的再按照第二个排列

limit

limit是为了防止一次性数据量过大的一个关键字

具体用法：
	limit 5;显示五条
	limit 5,5;从第六位开始显示五条数据

正则

select * from emp where name regexp '^j.*n$';

多表查询

多表查询是指将多个表联合起来进行查询；

具体方法：
	1.现实共有的数据
		select * from 表名 inner join 表名 on 表名.字段名=表名.字段名
	2.显示左边所有的数据，右边没有的用null补全
		select * from 表名 left join 表名 on 表名.字段名=表名.字段名
	3.显示右边的所有数据，左边没用的用null补全
		select * from 表名 right join 表名 on 表名.字段名=表名.字段名	
	4.显两边所有的数据，没有的相互用Null补全	
		select * from 表名 left join 表名 on 表名.字段名=表名.字段名
		union
		select * from 表名 right join 表名 on 表名.字段名=表名.字段名

例如：
	1.select * from emp inner join dep on emp.dep_id = dep.id;
	2.select * from emp left join dep on emp.dep_id = dep.id;
	3.select * from emp right join dep on emp.dep_id = dep.id;	
	4.select * from emp left join dep on emp.dep_id = dep.id
		union
	  select * from emp right join dep on emp.dep_id = dep.id;

子查询

子查询是指将上一次查询的结果当做下次查询的条件

例如：
select * from emp where emp.dep_id in (select dep.id from dep where id>1);