数据库Day03

多表

1.1 表关系分析

1. 员工表中有一个字段dep_id与部门表中的主键对应字段，这个字段就叫做关系属性

2. 拥有关系属性的员工表被称为 从表, 与关系属性对应的主键所在的表叫做 主表

多表设计上的问题

当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的。

应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在。

解决方案:

使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在。

外键约束

1. 外键

   外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键。使用外使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。

新建表时添加外键约束
create table emp(
eid int primary key auto_increment,
ename varchar(20),
age int,
did int,
constant emp_dept_fk foreign key (did) references dept(did)
);
desc emp;


为已创建好的表添加外键约束
create table emp(
eid int primary key auto_increment,
ename varchar(20),
age int,
did int
);
alter table emp add constraint emp_dept_fk foreign key (did) references dept(did);

外键约束作用：
    1.操作从表
        1.向子表中添加的外键值时，添加的外键值必须在关联的主表主键值中存在。
        2.修改子表中外键值时，修改后的外键值必须在关联的主表主键值中存在。
    
    2.操作主表
        1.删除主表中数据时，必须满足从表中的外键值在主表的主键值中存在
        2.变更主键中主键值是，必须满足从表中的外键值在主表的主键值中存在
        

外键约束:
		1.删除主表中数据时，必须满足从表中的外键值在主表的主键值中存在
		2.变更主键中主键值是，必须满足从表中的外键值在主表的主键值中存在
# 方式一
手动删除从表中关联的数据
delete from emp where did=1;
删除主表中的数据
delete from dept where did=1;

# 方式二：
手动将从表中关联的外键值设置为null
update emp set did=null where did=2;
再删除主表中的数据
delete from dept where did=2;

    删除表时：先删除从表，再删除主表
    创建表时：先创建主表，再创建从表
    

表的约束：为表中的字段添加约束，保证表中数据的完整性，正确性。
分类:
1.主键约束
特点：非空，唯一（值不允许为null且不能重复），一个表中只能有一个主键约束
关键词：primary key
表中通常都会创建一个id的字段，即使没有实际意义也会创建，将id添加主键约束。
主键自增：primary key auto_increment
create table 表名(
id int primary key,
...
);
				
alter table 表名 add primary key(字段);
				
2.非空约束
特点：非空（值不允许为null），一个表中可以使用多次，和其他约束配合使用
关键词：not null
create table 表名(
id int primary key,
name varchar(10) not null,
...
);
		
3.唯一约束
特点：唯一（值不能重复），一个表中可以使用多次，和其他约束配合使用
关键词：unique
create table 表名(
id int primary key,
email varchar(10) unique,
...
);
				
4.默认值约束
特点：添加时没有指定值或指定为default，使用默认值添加时执行了值使用指定值。
关键词：default 值	
create table 表名(
id int primary key,
sex varchar(10) default '男',
...
);
				
5.检查约束（MySQL8新增）
特点：检查添加的值是够符合要求
关键词：check
create table 表名(
id int primary key,
age int check(age>1 and and<100),
...
)
				
6.外键约束					
先创建主表，再创建从表
先删除从表，再删除主表
关键词：constrains foreign key references
create table 表名(
id int primary key,
...,
constrains 外键名 foreign key(字段) references主表(主键字段)
);
alter table 表名 add constrains 外键名 foreign key(字段) references主表(主键字段);

    外键约束设置：
        1.删除时
            1.RESTRICT(默认)  No action：作用类似，都会检查外键约束
            2.setNull：删除主表中数据时，将关联从表中的外键值设置为null
            3.cascade：删除主表中数据时，将关联从表中的数据删除
        2.变更时
            1.RESTRICT(默认)  No action：作用类似，都会检查外键约束
            2.setNull：变更主表中主键值时，将关联从表中的外键值设置为null
            3.cascade：变更主表中主键值时，将关联从表中的外键值变为新的主键值
 

多表关系设计

实际的开发过程中, 一个项目通常需要很多张表才能完成.

一对多关系，

• 一对多建表原则 在从表(多方)创建一个字段, 该字段作为外键指向主表的主键

多对多关系，

多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多。

一对一关系，

可以在任意一方添加一个外键，指向另一方的主键。

. 多表查询

• DQL: 查询多张表, 获取到需要的数据

笛卡尔积

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果

select * from 表一 cross jion 表二;

多表查询的分类

内连接

通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上就不显示

隐式内连接
	两张表中的字段名相同，需要区分对应表中的字段。
	
	方式1：
		表名.字段名
		
	方式2：
		为表定义别名，表的别名.字段名
select * from category,products where category.cid=products.cid;
select * from category c,products p where c.cid=p.cid;

显式内连接
使用 inner join ...on 这种方式

select * from category c inner join products p on c.cid = p.cid; 


不同的表进行内连接
2.同一张表(自连接)进行内连接
3.判断条件为等值判断，等值内连接
select 字段,... from 表1,表2 where 字段1=字段2;
select 字段,... from 表1 join 表2 on 字段1=字段2;
4.判断条件为非等值判断，非等值内连接
select 字段,... from 表1,表2 where 字段1 between 字段2 and 字段3;
select 字段,... from 表1 join 表2 on 字段1 between 字段2 and 字段3;

外连接查

左外连接

外连接：
1.左外连接：以左表为主，左表中的数据全部显示，右表没有匹配的数据以null填充。
select 字段,... from 表1 left [outter] join 表2 on 条件;
实现：
.不同的表进行左外连接
同一张表(自连接)进行左外连接
判断条件为等值判断，等值左外连接
select 字段,... from 表1 left join 表2 on 字段1=字段2;
判断条件为非等值判断，非等值左外连接
select 字段,... from 表1 left join 表2 on 字段1 between 字段2 and 字段3;
					
2.右外连接：以右表为主，右表中的数据全部显示，左表没有匹配的数据以null填充。
select 字段,... from 表1 right [outter] join 表2 on 条件;
实现：
.不同的表进行右外连接
同一张表(自连接)进行右外连接
判断条件为等值判断，等值右外连接
select 字段,... from 表1 right join 表2 on 字段1=字段2;
判断条件为非等值判断，非等值右外连接
select 字段,... from 表1 right join 表2 on 字段1 between 字段2 and 字段3;
												
3.MySQL不支持全外连接
左右表中数据都展示，没有匹配填充null
左外连接
union
右外连接
将左外连接的结果和右外连接的结果进行合并，合并时去重。

select * from category c left join products p on c.cid=p.cid
union
select * from category c right join products p on c.cid=p.cid;