MySQL的查询语句
select [distinct][元素1，元素2] from 表名 where 分组前条件筛选 [group by 元素 having 分组后的条件筛选]
1.对于数据的查找首先找对应的表 from tablename 打开文件
2.分组前的限定条件（）读取每一行的数据
3.分组可选不分组则一个表就是一个组：注分组后只能获得分组依据的字段内容，不能直接获得分组内单个数据信息。
4.可以排序order by 元素或者是聚合函数 avg sum min max
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),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),
('sean','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)
;查找薪资大于等于3小于等于6的数据:
select * from emp where id between 3 and 6;
select id,name from emp where id>=3 and id<=6;查询薪资是2000或者18000或者17000的数据
select *from emp where salary in (20000,18000,17000);
select *from emp where salary=20000 or salary=17000 or salary=18000;查询员工姓名中包含o字母的员工姓名与薪资
select name,salary from emp where name like "%o%"查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like"____";四个下划线，代表四个字符查询是否有名字为空的用户：
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";重新链接客户端
select * from emp group by post; # 报错
select id,name,sex from emp group by post; # 报错
select post from emp group by post; # 获取部门信息强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
查找所有部门下的所有人的名字
select group_concat(name) as "名字",post from emp group by post;as取别名
聚合函数：
聚合函数只有在分组之后才能使用：avg max min sum count
查询最低最高工资：
select post,max(salary) from emp group by post;
select post,min(salary) from emp group by post;
查看每部门工资总和：
select post,sum(salary) from emp group by post;分组后连接分组信息group_concat关键字：
select post,group_concat(name,": ",salary) from emp group by post;
concat不分组是拼接字符串达到更好的效果concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
select name as 姓名,salary as 薪资 from emp;
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;查询每个人的年薪
select name,salary*12 as annual_salary from emp;关键字where 与group by同时出现的情况下，group by必须在where之后，
where先是整张表的筛选。
执行顺序：
from->where->->group by->having->select
选择各部门年龄大于20岁的员工的薪资
select post,group_concat(salary) as 薪资 from emp where age>20 group by post;
having关键字：
统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门：
select post,avg(salary) as 平均薪资 from emp where age>30 group by post having avg(salary)>10000;
distinct:用于去重
order by根据元素的大小排序：
统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) as 平均薪资 from emp where age>20 group by post having avg(salary) order by avg(salary);
limit 关键字限制打印的条目
slect *from emp limit 1;
正则：
select * from where name regexp "^o.**(n|y)$;
多表查询
#建表
create table dep(
id int,
name varchar(20)
);
create table emp(
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 emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;笛卡儿积：
select *from emp,dep;
内连接：
select *from emp inner join dep on emp.dep_id=dep.id;
左连接：
在内连接的基础上保留左表没有对应关系的记录
select *from emp left join dep on emp.dep_id=dep.id
右连接：
在内连接的基础上保留右表没有对应的记录
select *from emp left join dep on emp.dep_id=dep.id
子查询:
就是将一个查询语句的结果用括号括起来作为另一个查询语句的条件
select *from emp where dep_id in (select id from dep where name="技术")
select t1.id,t1.name,t1.hire_date,t1.post,t2.*from emp as t1 inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post=t2.post
where t1.hire_date=t2.max_date;
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询
