前期准备：

#创建表
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,'20150311','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,'402','2'),
('呵呵','female',38,'20101101','sale',2000.35,'402','2'),
('西西','female',18,'sale','20110312',1000.37,'402','2'),
('乐乐','female',18,'sale','20160513',3000.29,'402','2'),
('拉拉','female',28,'sale','20170127',4000.33,'402','2'),
('僧龙','male',28,'20160311','operation',10000.13,403,3),
('程咬金','male',18,'19970312','operation',18000.13,403,3),
('程咬银','female',18,'20130311','operation',20000,403,3),
('程咬铜','male',18,'20150411','operation',19000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

补充：可以使用select * from emp\G;来更改表的展示模式。

#有可能改过编码后，在插入中文的时候，还是会出现乱码或者空白的现象。那么可以将字符编码统一设置成GBK，因为有的Window电脑比较老，默认编码是GBK。

sql的书写顺序建议：

几个重要关键字的执行顺序：

select id,name from emp where id > 3;

执行顺序：

1、from

2、where

3、select

虽然执行顺序和书写顺序不一致，你在写sql语句的时候可能不知道怎么写

你就按照书写顺序的方式写sql，selec * 先用*占位，之后去补全后面的sql语句，最后将*替换成你想要的字段。

where约束条件：

作用：对整体数据的筛选操作

题目：
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,17000,18000);

3、查询员工姓名中包含字母o的员工的姓名和薪资
select name,salary from emp where name like '%o%';
#%o%的意思是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； #对

group by 分组：

应用场景：

统计男女比例

部门平均薪资

一般出现：每个 平均 最高 最低的都要做分组，具体看你的语感

... ...

select * from emp group by post;

#会获取每个部门的第一条数据

分组后最小操作单位应该是组 而不是在组内的单个数据

上述命令在你没限制严格模式的时候是可以正常执行的，返回的是分组之后每个组的第一条数据，但是这不符合分组的规范：分组后不应该考虑单个数据，而应该以组为操作单位（分组后没法直接获得组内单个数据）。

如果开启了严格模式，那么上述命令会报错。

set global sql_mode = 'strict_trans_tables,only_full_group_by';

#开启严格模式以及分组设置。

设置严格模式后分组只能拿到分组的依据

按照什么分组就只能按照什么来划分，不能用*要用post

select post from emp group by post;

按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助一些方法（聚合函数）。

聚合函数：

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;
#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(salary) from emp group by post;
select post,count(id) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; #不行，因为post_comment的值是null。
#count对null没法计数

6、查询分组之后的部门名称和每个部门下所有的员工姓名
select post,group_concat(name) from emp group by post;
#group_concat可以支持你获取分组后的其他字段值，还可以支持拼接操作
select post,group_concat(name,'_HAHAHA') 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;

# as语法不单单可以给字段起别名还可以给表临时起别名
select emp.id,emp.name from emp; = select id,name from emp;
select emp.id,emp.name from emp as t1; #错
select t1.id,t1.name from emp as t1; #对，但只有当前有效,因为sql语句的执行顺序问题，字段在被提取之前，表名就已经被改为t1了
select id,name from emp as t1;#对

#查询每个人的年薪，12薪
select name,salary *12 from emp;

分组注意事项：

关键字where和group by同时出现的时候group by 必须在where的后面。因为where会先对整体数据进行过滤之后在分组操作。

聚合函数只能在分组之后使用

select id,name,age from emp where max(salary)> 3000; #报错

where的筛选条件不能使用聚合函数

select max(salary) from emp; #不分组默认整体就是一组

#统计各部门年龄在30岁以上的员工平均工资
1、先求所以年龄大于30岁的员工
select * from emp where age>30;
2、再对结果进行分组
select * from emp where age>30 group by post;
组合：
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; #去不了，因为带了主键（id号），就会使得数据不一样。
select distinct age from emp;

order by排序：

select * from emp order by salary asc; # 升序排序
select * from emp order by salary; # 默认是升序排序 asc可以省略
select * from emp order by salary desc; #改为降序

select * from emp order by age,salary asc;
#先按照age降序排，如果碰到age相同，则再按照salary升序排 

#统计各部门年龄在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; #只拿3条数据
select * from emp limit 0,5; #拿第一条到第五条
select * from emp limit 5,5; #6~10
第一个数字是起始位置，第二个是查询的条数

mysql也支持正则：
select *from emp where name regexp '^j.*(n|y)#39;;
j开头，中间任意，n或y结尾

多表操作：

#创建2张表
create table dep(id int,
name varchar(20)
);
create table enp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',age int,dep_id int)
;
#录入数据
insert into dep values(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into enp(name,sex,age,dep_id) values
('jason', 'male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18 ,204);

表查询：

可以将两张表一起拼接起来显示

select * from enp,dep; #结果叫 笛卡尔积
select * from enp,dep where enp.dep_id = dep.id; #拼表操作

推荐写法：

inner join #内连接
left join #左连接
right join #右连接
union #全连接
select * from enp inner join dep on enp.dep_id = dep.id;
#只拼接两张表中共有的数据
insert into dep(id,name) values(205,'sale');
select * from enp left join dep on enp.dep_id = dep.id;
#左表所有的数据都展示出来 没有对应的项就用null
select * from enp right join dep on enp.dep_id = dep.id;
#右表所有的数据都展示出来 没有对应的项就用null
#左右两表所有的数据都展示出来，用法：
select * from enp left join dep on enp.dep_id = dep.id
union
select * from enp right join dep on enp.dep_id = dep.id;

子查询：

子查询就是我们平时解决问题的思路

分步骤解决问题

第一步

第二部

....

将一个查询语句的结果当做另外一个查询语句的条件使用

#查询部门是技术 或者人力资源的员工信息

1、查询部门的id

select id from dep where name='技术' or name ='人力资源';

2、根据id筛选出合适的员工

select name from enp where dep_id in (200,201);

可以简化为：

select * from enp where dep_id in (select id from dep where name='技术' or name ='人力资源');

总结：表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把她作为一个虚拟表跟其他表关联

多表查询的两种方式：

1、先拼接表再查询

2、子查询 一步一步的来