单表查询,多表查询,子查询

一、单表查询

1、完整语法（语法级别关机子的排列顺序如下）

select distinct 字段1，字段2，字段3…… from 库名.表名
Where 约束条件
Group by 分组依据
Having 过滤条件
Order by 排序的字段
Limit 限制显示的条数
；

必须要有的关子字如下：
Select * from t1;

关键字执行的优先级
From
Where
Group by
Having
Distinct
Order by
Limit

2、简单查询

Select * from t1;
Select id,name,sex from t1;
Select distinct port from emp;
Select name,salary*12 as annual_salary from emp; #salary*12 as annual_salary 重命名

Select concat(“名字：”,name) as new_name,concat(“年龄:”,age) as new_age from emp;
Select concat(name,”:”,age) fron emp;#拼接

Select concat(name,”:”,age,”:”,sex) from emp;
Select concat_ws(“:”,name,age,sex)as info from emp;#多个内容拼接，符号相同的放在最前面，注意用concat_ws

一列内容分不同的需求满足

Select
(
Case
When name=”egon’ then
Name
When name = “alex” then
Concat(name,”_bigsb”)
Else
Concat(name,”sb”)
End
) as new_name
From
Emp;

3、Where

Select * from emp where id >= 10 and id <=15; #等同于select * from emp where id between 10 and 15;
Select * from emp where id=6 or id=9 or id=12;#等同于select* from emp where id in(6,9,12);

_代表任意单个字符
%代表任意无穷个字符
Select * from emp where name like”__”;
Select * from emp where name like “jin%”;
Select * fromemp where id not in (6,9,12);
Select * from emp where id not between 10 and 15;

4、group by 分组

什么分组：按照所有记录相同的部分进行归类，一定区分度低的字段

为何要分组：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义

严格模式下用分组，分组当中数据不清掉
set global sql_mode=”strict_trans_tables,only_full_group_by”;
注意：分组之后，只能查到分组的字段以及组内多条记录聚合的成果
Select * from emp group by post;

聚合函数
Max
Min
Avg
Sum
Count

Select post,count(id) from emp group by post;
Select post ,max(salary) from emp group by post;
Select post,avg(salary) from emp group by post;
Select sex,count(sex) from emp group by sex;

统计出每个部分年龄30以上的员工的平均薪资
Select post,avg(salary) from emp where age >= 30 group by post;
注意：分组是在where之后发生的
Mysql>select * from emp where max(salary) > 3000;#报错案例
Error 1111(HY000):Invalid use of group function 

group_concat 
Select post,group_concat(name,”:”,age) from emp group by post;

5、Having 过滤条件

Where是在分组之前的过滤，即在分组之前做了一次整体性的筛选
Having是在分组之后的过滤，即在分组之后专门针对聚合的结果进行进一步的塞选

Select post,avg(salary) from emp group by post having avg(salary) > 10000;
Select post,avg(salary) from emp group by post;

6、order by排序

Select * from emp order by age asc;#默认asc升序-》从小到大
Select * from emp order by age desc; #desc降序-》从大到小

Select* from emp by age asc,salary desc;#先按照age升序排列，如果age相同则按照salary降序排

Select post,avg(salary) from emp group by post order by avg(salary);

7、limit限制显示的条件

Select * from emp limit 3;
薪资最高那个人的详细信息
Select * from emp order by salary desc limit 1;#选择工资降序排列第一位工资人员信息
分页显示
Select * from emp limit 0,5;#从0开始往后取5条
Select * from emp limit 5,5;#从5开始往后取5条

正则表达式
Select * from emp where name regexp “^jin.*(g|n)$”;

二、多表查询

1、笛卡尔积
Select * from emp,dep;
表格不单单是对照准确的，以及不准确的ID对照也会在内
Select * from emp,dep where emp.dep_id=dep.id;
Select * from emp,dep where emp.dep_id = dep.id and dep.name = “技术”;

2、内连接：只取两张表有对应关系的记录
Select * from emp inner join dep on emp.dep_id = dep.id;
Select * from emp inner join dep on emp.dep_id = dep.id
Where dep.name = “技术”;

3、左连接：在内连接的基础上保留左表没有对应关系的记录
Select * from emp left join dep on emp.dep_id = dep.id;

4、右连接：在内连接的基础上保留右表没有对应关系的记录
Select * from emp right join dep on emp.dep_id = dep.id;

5、全连接： 在内连接的基础上保留左、右面表没有对应关系的记录
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 dep_id in(select id from dep where name = ”技术” or 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;

Select * from emp inner join dep on emp.dep_id = dep.id;