mysql 表的关系&表连接&子查询

表的关系

• 一对多

建表原则：在多的一方添加 一方的主键作为外键

• 多对多
  

如果数据表是多对多的关系那么需要创建一张表，把2张关系吧的外键引入

• 一对一关系
  

在任何一方添加对方的主键作为外键

内连接

• 表的自然连接（多表查询)

使用的两张表

create database emp;
use emp;
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('AA','male',18,200),
('BB','female',48,201),
('CC','male',38,201),
('DD','female',28,202),
('EE','male',18,200),
('FF','female',18,204)
;

• 合并结果集
  1、合并结果集就是把两个select语句的查询结果合并到一起
  2、合并的select语句的列数量、类型、顺序必须完全一样
  3、合并结果集有两种方式：
  union:去除重复记录
  union all:不去除重复记录
  

select id,name from employee
union
select id,name from department;

• 连接查询（笛卡尔积）
  
  使用主外键关系作为条件来去除无用信息

select 列名
from 表名,表名
where 主表的主键值=子表的外键值

select *
from department,employee
where department.`id`=employee.dep_id;

• 标准版（内连接）
  关键字inner join...on
  特点：查询结果必须满足条件
  只有左右2个匹配才能在结果中显示，作用和连接查询一样

表1 inner join 表2 on 表1.字段=表2.字段
inner join 表3 on 表2.字段=表3.字段
...
where 条件

select * 
from employee e 
inner join department d
on e.dep_id=d.id;

外连接

外连接的特点：查询出的结果存在不满足条件的可能
1、左外连接
关键字left join ...on
左外连接是先查出左表（即以左表为主），然后查询右表，右表满足条件的显示出来，不满足的显示null
2、右外连接
关键字right join ...on
右外连接是先查出右表（即以右表为主），然后查询左表，左表满足条件的显示出来，不满足的显示null

全连接

mysql不支持全连接但是可以用union 模拟全连接
左连接一次 union 右连接一次

select * 
from employee e
left join department d
on e.dep_id=d.`id`
union 
select * 
from employee e
right join department d
on e.dep_id=d.`id`

子查询

当一个查询语句嵌套在另外一个查询语句中称之为子查询。

• 特点：
  1、子查询的结果作为外查询的条件
  2、认知:所有的子查询都可以使用连接查询实现，反之不行。子查询的更加灵活。
  3、执行顺序:先执行最内层的子查询，然后一层一层往外执行
  4、子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

单行单列

select * from employee where dep_id=
	(select id from department where name='销售');

select * from employee where dep_id in
   (select id from department where name not in('销售','技术'));

• 查询出现的位置:
  where后,作为条件的一部分;
  from后，作为被查询的一条表;
• 当子查询出现在where后作为条件时，还可以使用如下关键字:
  Any作用同SOME :满足设定条件的最小值均可返回结果
  all:当>|>=时，满足设定条件的最大值可返回结果
  当<│|=时，满足设定条件的最小值可返回结果
  some:跟any一样
• 子查询结果集的形式
  
  

多行单列

select * from employee where dep_id in
     (select id from department  where name in('销售','技术'));

单行多列
查询性别和部门和AA相同的员工信息

select * from employee where (sex,dep_id) in 
       (select sex, dep_id from employee where name='AA');

select * from employee where  name!='AA' and (sex,dep_id)  in 
       (select sex, dep_id from employee where name='AA');