单表查询与多表查询

一、单表查询

1.完整的语法(语法级别的关键字的排列顺序)

select distinct 字段1，字段2，字段3...from 库名.表名

　　　　　　　　where 过滤条件1

　　　　　　　　group by 分组依据

　　　　　　　　having 过滤条件2

　　　　　　　　order by 排序的字段

　　　　　　　　limit 限制显示的条数

　　　　　　　　；

2.关键字执行的优先顺序

from > where > group by > having > select > distinct > order by > limit

　　2.1 找到表

　　2.2 从表中初步过滤出一条条数据

　　2.3 将对过滤出来的数据进行分组

　　2.4 将对过滤出来的数据进行二次过滤

　　2.5 执行select

　　2.6 将二次过滤的结果去重

　　2.7 将去重的结果进行排序

　　2.8 限制显示的条数

3.简单查询

语法中必须有的关键字：select (字段名1,字段名2) from 表名 

create table employee(
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),
salary double(15,2),
depart_id int
);

# 插入数据
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('baobao','male',18,'20170301','aijiao',7300.33,401,1),
('mengchong','male',78,'20150302','teacher',1000000.31,401,1),
('bengben','male',81,'20130305','teacher',8300,401,1),
('laixici','male',73,'20140701','teacher',3500,401,1),
('zhoukang','male',28,'20121101','teacher',2100,401,1),
('jingyan','female',18,'20110211','teacher',9000,401,1),
('kenglog','male',18,'19000301','teacher',30000,401,1),
('meta','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.1 简单查询

　　select * from emp

　　select id,name from emp

3.2 对指定的字段进行去重操作

　　select distinct post from emp

3.3 将指定的字段(该字段一般是数字)进行+-*/四则运算操作

　　select id,name,salary*12 as annual_salary from emp;

3.4 字段的拼接---concat

　　select concat('姓名：',name) as name,concat('年龄：',age) as age from emp;

　　select concat(name,'-',age) from emp;

　　　　若有多个字段需要拼接时，可以使用concat_ws('拼接符号',字段名1,字段名2..)来一次性完成拼接

　　select concat_ws('-',name,age,sex) as info from emp;  #第一个参数是分隔符

select
        (
            case
            when name='baobao' then 
                name
            when name='bengben' then
                concat(name,'__hello__')
            else    
                concat(name,'__shine__')
            end
        ) as name
    from 
        emp;

4.where 初步筛选

　　where中可以使用的关键字：> | < | >= | <= | !=   (| 是分隔符号，不是where条件中的可用符号)

　　　　　　　　　　　　　　between num1 and num2 是表示在num1 和num2数字之间(包括num1和num2)

　　　　　　　　　　　　　　in(num1,num2,num3) 表示num1或者num2或者num3之间的一个

　　　　　　　　　　　　　　like '记录中的某一个值%'

　　　　　　　　　　　　　　　　% 代表任意多个字符

　　　　　　　　　　　　　　　　_ 代表任意单个字符

5.group by 将初步筛选数据分组

　　指的是按照某个相同的字段进行归类

set global sql_mode='strict_trans_tables,only_full_group_by';
#分组之后只可查到分组的字段以及组内多条记录聚合的成果
select * from emp group by post;

　　聚合函数：max min avg sum count

注意：若我们使用unique 字段作为分组的依据，这样每条记录都会自成一组，实际上这种分组是没有任何意义的。

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

6. having 二次筛选

此次的筛选是在分组之后进行的，即在分组之后专门针对聚合的结果进一步的筛选。

select post,avg(salary) from emp group by post having avg(salary) <10000;

7.order by 对二次筛选结果进行排序

7.1 单列排序

select * from emp order by salary asc;  # 默认情况下都是asc升序

select * from emp order by salary desc; # 降序排列

7.2 多列排序

select * from emp order by age asc,salary desc,...

8. limit 限制显示的记录数

分页显示：

　　select * from emp limit 0,5; # 表示从0开始往后取5条

　　select * from emp limit 5,5; # 表示从5开始往后取5条

正则表达式：

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

二、多表查询

# 创建表
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
('baobao','male',18,200),
('huohuo','female',48,201),
('maibenen','male',38,201),
('tangdou','female',28,202),
('leno','male',18,200),
('shusi','female',18,204)
;

1. 笛卡儿积---交叉链接，不适用任何匹配条件

　　select * from dep,emp  # 表示从表dep中取出一条与表emp进行一一对应，直至表dep中所有的数据都取完毕

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_di=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_di=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;