27.mysql数据库之约束

目录

• 约束
  • 1.mysql中有一些普通约束
  • 2.主键约束primary key
  • 3.外键约束 foreign key
    • • 案例:

约束

约束是一种限制，对数据库中加以必要的约束是对数据安全性和完整性的保证。

1.mysql中有一些普通约束

1. unique:是唯一性约束，表示这个字段不能出现相同的值，可以用来标识唯一的一条记录，当作索引使用
2. not null:是非空约束，表示这个字段里的值不能为空
3. null:与not null相反，有些数据默认就是可以为空的
4. default:指默认值，可以给某一字段设置好相应的默认值

约束添加方式：

• 建表时：create table table_name(字段名称 字段类型[(宽度) 约束]) charset utf8;
• 表已存在时：alter table student modify id int unique not null; # 为已经存在的字段添加约束

2.主键约束primary key

​ 主键约束,从约束角度来看就等同于,非空+唯一

​ 在innodb存储引擎中 ,主键用于组织数据 (树形结构) ，主键对于innodb引擎来说是必要的，如果没有手动指定主键，在mysql中会自动查找一个具备非空且唯一的字段作为主键，若也没存在这样的字段,mysql会创建一个隐藏字段作为主键，主键的存在可以加快我们查找的速度。通常我们会将主键设置为类int 类型,方便保证其唯一性。

​ mysql有一个自动增长的属性auto_increment,可以添加在整形字段上,每次插入数据时,都可以自动的插入值,并且每次加1不会冲突。所以我们可以为主键设置自动增长，方便操作。

create table teacher(
    id int primary key auto_increment,
    name char(10)
);
insert into teacher values(null,"jack"); # 对于自动增长的字段可以给null  也会自动生成值
insert into teacher(name) values("jack");# 也可以跳过这个字段 



create table teacher3(
    id char unique auto_increment ,
    name char(10)
);

3.外键约束 foreign key

​ 在建表时添加外键约束,用于指向另一个表的主键字段, 所建的表称为从表，指向的表属于主表，在建表时主表已经存在，所以先有主表，再有从表

# 创建表的时候添加外键
create table teacher(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        dept_id int,
    	foreign key(dept_id) references dept(id)
	);
解释:
foreign key(dept_id) references dept(id)
dept_id 表示当前表的外键字段  
dept 表示要关联哪个表 
dept(id)  id表示关联的dept表的id字段 


主表与从表  
先有主表 再有从表  
先有dept 再有teacher 

添加外键约束后：

1. 必须保证设定外键约束的值是在主表中存在的
2. 插入数据的顺序是先插入主表记录，在插入从表记录
3. 从表更新外键时也必须保证外键的值在主表中是存在的
4. 删除主表记录前，要保证从表中没有外键关联的值存在，所以可以先删除从表，再去删除主表
5. 更新主表记录的主键时和删除主表记录也一样

这样先删从表后删主表很麻烦，可以通过级联操作cascade来简化操作，可以通过设置on delete cascade，当删除主表时自动删除从表中相关数据，on update cascade，当主表的主键更新时自动的更新关联的从表数据

外键使用的情况出现在表与表之间存在关联关系时，所以首先要确定表之间的关系:

1. 多对一,或者说一对多的关系时，处理方式为处理方式在一的一方的表中保存多的一方的编号;

   	#部门:
   	create table dept(
           id int primary key auto_increment,
           name char(20),
           job char(50),
           manager char(10)
   	);
   	#老师表:
   	create table teacher(
           id int primary key auto_increment,
           name char(20),
           gender char(1),
           dept_id int,
           foreign key(t_id) references teacher(id),
   	);
   

2. 多对多的关系时，处理方式为建立一个中间表用于存储关系,在中间表中至少存在两个字段分别指向不同的主键,这两个字段都设定为外键约束，还可以将这两个字段作为联合主键，这样就同时具备了 唯一且非空约束

   create table t_s_r(
       id int primary key auto_increment,
       t_id int,
       s_id int,
       foreign key(t_id) references teacher(id),
       foreign key(s_id) references student(id),
       unique key(t_id,s_id)
       );
       
    上表中id是可选的,问题是如何保证没有重复的关系 ?
   
    方式1:
    给两个字段设置为联合唯一  +  非空
    # 假设表已经存在了
    alter table t_s_r add unique key(t_id,s_id);
    # 创建表时指定 多字段联合唯一 
    create table t_s_r2(t_id int,s_id int,unique key(t_id,s_id));
    方式2:
    # 推荐 将中间的关系表 两个id 作为联合主键 同时具备了 唯一且非空约束 
    create table t_s_r3(t_id int,s_id int,primary key(t_id,s_id));
    
    
    
   处理多对多关系
   1.创建两个主表  如学员 和 老师 
   2.创建关系表 包含两个字段 分别设置外键 指向对应的表 
   3.将两个字段 作为联合主键  
   
    
   

   案例:

   create table student(id int primary key auto_increment,name char(10));
   create table teacher(id int primary key auto_increment,name char(10)); 
   create table t_s_r(
       t_id int,
       s_id int,
       foreign key(t_id) references teacher(id),
       foreign key(s_id) references student(id),
       primary key(t_id,s_id)
       );
    # 先插入 学生或是老师都可以 但是关系表一定是最后添加的 
    insert into teacher values(null,"bgon"),(null,"nike");
    
     insert into student values(null,"老王"),(null,"老李");
     
     # 老王被bgon教过 
     insert into t_s_r values(1,1);
      # nike教过老李 
     insert into t_s_r values(2,2);
      # nike教过老王 
     insert into t_s_r values(2,1);
   
   
   
    已知老师名称为bgon 请找出他教过那些学生			
   1.通过名字获取 bgon的id 			
   2.拿着id取关系表中拿到一堆学生的id			
   3.通过学生的id取出学生的信息			
    select id from teacher where name = "bgon";
    select s_id from t_s_r where t_id = 1;
    select  * from student where id = 1;
    
    # 子查询方式   把一条语句的结果作为另一条语句的条件!
    select  * from student where id = (select s_id from t_s_r where t_id = (select id from teacher where name = "bgon"));
     
     
     
    已知学生名为老李 请查询出 哪些老师教过他  				
   1.通过名字获取老李的id
   select id from student where name = "老李";
   2.拿着id去关系表 找出老师的id				
   select t_id from t_s_r where s_id = 2;
   3.通过老师的id取出老师的信息 			
   select name from teacher where id = x;
   
   
   # 子查询方式:
   select name from teacher where id = (
       select t_id from t_s_r where s_id = (
           select id from student where name = "老李"
       )
   );
   

3. 一对一时，处理方式为确定先后顺序，将先存在的数据作为主表，后存在的作为从表使两个表的id保持一一对应

# 人员表
create table person(
	id int primary key auto_increment,
    name char(10),
    age int
);
# 详情表 
create table person_info(
	id int primary key,
    height float,
    weight float,
    foreign key(id) references person(id)
);
#再这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据  

#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!