多表查询v1

一：介绍

多表连接查询

复合条件查询

子查询

二：准备表


# 建表
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,'运营'),
    (205,'爆破');

insert into emp(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);

drop table emp;drop table dep;

三：笛卡尔积

select * from dep,emp;
select * from emp,dep where emp.dep_di = dep.id;

select emp.id,emp.name,emp.sex,emp.age,dep.name from emp,dep where emp.dep_id = dep.id;

四：多表连接查询

外连接语法

select 字段。。。 from 表1 inner|left|right| join 表2 on 表1.字段 = 表2.字段;

四种外连接介绍

MySQL也知道你在后面查询数据过程中肯定会经常用到拼表操作
所以特地给你开设了对应的方法:
inner join    外连接之内连接(只拼接2张表中共有的数据部分)
1eft  join    外连接之左连接(左表所有的数据都展示出来, 没有对应的项就用NULL)
right join    外连接之右连接(右表所有的数据都展示出来, 没有对应的项就用NULL)
union         外连接之全连接(全连接左右两 表所有的数据都展示出来)
注意：mysql不支持全外连接 full join
强调：mysql可以使用下面的方式间接实现全外连接.

上代码

内连接

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

左连接

select * from emp left join dep on emp.dep_id=dep.id;

右连接

select * from emp right join dep on emp.dep_id=dep.id;

全连接

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 union dep on emp.dep_id=dep.id;

五：条件连接查询

以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
```
select emp.name,dep.name
    from emp inner join dep on emp.dep_id=dep.id
    where emp.age>25;
```

以内连接的方式查询emp和dep表，并且以age字段的升序方式显示

select emp.id, emp.name, emp.sex, emp.age, dep.name
    from emp inner join dep on emp.dep_id=dep.id 
    order by age asc;

六：子查询

一：介绍

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等

二：带in关键字的子查询

子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		...
将一个查询语句的结果当做另外一个查询语句的条件去用

# 查询部门是技术或者人力资源的员工信息
1. 先获取部门的id号
2. 再去员工表里面筛选出对应的员工

# 第一步:
select id from dep where name in ('技术', '人力资源');
# 第二步:
select * from emp where dep_id in (200, 201);

# 得出结果:
select * from emp where dep_id in (select id from dep where name in ('技术', '人力资源'));

三：带比较运算符的子查询

比较运算符：=、!=、>、>=、<、<=、<>
查询大于所有人平均年龄的员工名与年龄
select avg(age) from emp;
select name,age from emp where age > (select avg(age) from emp);


# 查询大于部门内平均年龄的员工名、年龄
select dep_id,avg(age) from emp group by dep_id;
select emp.name,emp.age 
    from emp inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as t1 
    on emp.dep_id=t1.dep_id 
    where emp.age > t1.avg_age;

四：带exists关键字的字查询

"""
exists关字键字表示存在。在使用exists关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
"""
# dep表中存在dept_id=203，Ture
select * from emp
    where exists (select id from dep where id=203);
    
# dep表中不存在dept_id=204，False   
select * from emp
    where exists (select id from dep where id=204);

五：练习：查询每个部门最新入职的那位员工

company.emp
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
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
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;

# ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

外连接查询

select * from emp order by hire_date;
select post,min(hire_date) as min_hire_date from emp group by post;

select * 
    from emp inner join (select post,min(hire_date) as min_hire_date from emp group by post) t1
    on emp.hire_date=t1.min_hire_date;

子查询(难理解）
```
# 错误写法
select (select * from emp as t2 
        where t1.post=t2.post 
        order by hire_date desc
        limit 1)
    from emp as t1 group by post; 
```
答案一为正确答案，答案二中的limit 1有问题（每个部门可能有>1个为同一时间入职的新员工），我只是想用该例子来说明可以在select后使用子查询

可以基于上述方法解决：比如某网站在全国各个市都有站点，每个站点一条数据，想取每个省下最新的那一条市的网站质量信息

六：总结

表的查询结果可以作为其他表的查询条件, 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联.

多表查询就两种方式:
    先拼接表再查询
    子查询 一步一步来

七：查询语句的优先级

from   
笛卡尔积  # 把两张表简单粗暴的连接到一起
on
left join 或者 right join
inner join
where 
group by
having
distinct
order by
limit

多表查询v1

一：介绍

多表连接查询

复合条件查询

子查询

二：准备表

三：笛卡尔积

四：多表连接查询

外连接语法

四种外连接介绍

上代码

五：条件连接查询

六：子查询

一：介绍

二：带in关键字的子查询

三：带比较运算符的子查询

四：带exists关键字的字查询

五：练习：查询每个部门最新入职的那位员工

外连接查询

子查询(难理解）

六：总结

七：查询语句的优先级