• 第六十篇 约束、表关系


    一、约束

    1.什么是约束 constraint

    **1.约束(constraint):表中数据的限制条件 **

    2.约束的作用:为了保证表中的记录完整、有效

    2.mysql中的约束

    1.unique

    1.唯一性约束(unique):unique约束的字段,具有唯一性,不可重复,但可以为null

    2.使用场景:例如身份证号码,学号等

    2.not null

    **1.非空约束(not null):用not null约束的字段不能为null值,必须给定具体的数据 **

    2.应用场景:例如,账户名,密码等

    3.null

    有些数据类型默认可以为空,则可以使用这个约束

    4.default

    默认值,用于给某一个字段设置默认值,如果不给该字段输入值,则使用默认值,可以为null

    1.普通约束测试:

    #完整的建表语句
    create table table_name(字段名称 字段类型(宽度) 约束) charset utf8;
    
    # 学生表:姓名、性别、学号
    create table student(
        # 非空
    	name char(20) not null,
        # 默认值
        gender enum("g","b") default "b",
        # 唯一
        id int  unique
    )
    #测试:
    insert into student values(null,null,null);   # 错误原因是:name 字段设置的为非空约束 
    
    insert into student values("jack",null,null);  # ok,null是一个特殊的值,可以赋值给默认值约束,并且id的唯一约束,也可以为null
    
    insert into student(name,id) values("jack",null); #ok,当没有给gender指定参数时,将使用默认值 
    
    alter table student modify id int unique not null; # 为已经存在的字段添加约束
    

    5. primary key

    1.主键约束:从约束角度来看就等同于非空+唯一(表设计时一定要有主键)

    2.主键与普通的约束的区别

    create table person(
        id char(19) primary key,
    	name char(20)
    );
    
    insert into person values("1","rose");# ok 
    insert into person values("1","jack");# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'主键冲突;
    insert into person values("2","jack"); # ok
    insert into person values(null,"tom"); # Column 'id' cannot be null #主键不能为空  
    
    
    #从约束角度来看就等同于,非空+唯一  
    create table person2(
        id char(19),
    	name char(20)
    );
    
    # 特点:
    # 1.在innodb存储引擎中,主键用于组织数据 (树形结构);
    # 2.主键对于innodb引擎来说是必须要的,没有不行;
    # 3.如果没有手动指定主键,mysql会自动查找一个具备非空且唯一的字段作为主键; 
    # 4.如果也没有这样的字段,mysql会创建一个隐藏字段作为主键;
    # 5.首先明确主键是一种索引,unique也是;
    # 6.如果我们在查询语句中没有使用索引字段,mysql将无法为我们的加速查询,也就是说,索引的作用是加速查询,我们使用它可以提高查询速度
    
    # 补充:
    # 在关系型数据库中,索引是一种对表中的值进行排序的一种结构
    
    # 如何使用主键:
    # 1.主键具备约束的作用,还能加快我们的查询速度,所以今后在创建表的时候都应该创建索引字段;
    # 2.如果本来的业务中就存在非空且唯一的字段,那就把它设为主键,如果没有就自己添加一个字段专门作为主键,通常我们会将主键设置为类int类型,主要是为了方便保证其唯一
    
    # 案例:
    # 建表
    create table PC(
        id int primary key, 
        pp char(20),
        model char(10),
        price float
    );
    # 加值
    insert into PC values(1,"IBM","1214SB",40000);
    insert into PC values(2,"DELL","1200DSB",4000);
    # 查询
    select *from PC;
    select *from PC where id = 1;
    select *from PC where pp = "DELL";
    

    1.为主键设置自动增长

    1.当我们创建了主键字段时,插入数据必须保证主键是唯一的不能重复,这就需要我们自己管理主键值,每次赋值都要知道该赋值哪一个数据,比较麻烦,所以mysql有一个自动增长的属性,可以添加在整形字段上,每次插入数据时,都可以自动的插入值,并且每次加1不会冲突

    2.auto_increment:自增约束,可以对数字类型且具备索引(unique或primary key)的字段使用,通常与主键一起使用

    3.作用是按序递增,对于自动增长的字段可以赋值null,该字段也会自动生成值

    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)
    );
    '''
    ERROR 1063 (42000): Incorrect column specifier for column 'id'
    '''
    

    2.将两个字段设为主键

    create table user(
        id int not null unique,
    	username char(10) not null,
        password char(20) not null,
        primary key(username, password)
    );
    

    6.foreign key

    1.外键约束(foreign key):用于指向另一个表的主键字段,也即是用来保证两张表之间的关联关系是正确的

    2.格式:先在从表中设置一个数字字段,通过 foreign key(字段名) references 主表名(主表主键字段名)的书写格式进行外键的设置

    3.什么时候使用外键: 表之间存在关联关系

    # 创建表的时候添加外键
    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字段 
    

    二、表之间的关系

    1.外键的使用

    1.首先要确定表之间的关系

    2.表与表之间的关系分三种情况:一对一、一对多、多对多

    2.涉及表的三种关系

    1. 一对一关系

    1.特点:站在两边看都是一对一的关系

    2.处理方式:

    • 1.确定先后顺序

    • 2.将先存在的数据作为主表

    • 3.后存在的作为从表

    • 4.使两个表的id保持一一对应

      • 方法1: 从表的id既是主键又是外键
      • 方法2:从表的id设置为外键,并保证唯一

    3.案例:

    # 人员表
    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)
    );
    # 在这样的关系中,必须先插入主表的主键id,再添加详情表的数据 
    # 将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!
    # 补充:水平分表是保持相同的字段,将整个表水平切分,分成两个表
    

    2.一对多多对一(常用)

    1.站在部门的角度想:一个部门可以有多个职工

    2.站在老师的角度想:一个职工只能属于一个部门

    # 详细分析老师和部门的关系 :
    
    # 1.老师的角度看:
    	一个老师应该对应有一个部门 
    	一个老师可以对应对多个部门?  不行 一个老师只能属于一个部门 (要看具体业务要求)!
    	多个老师可以对应一个部门 
    	多对一
    # 2.部门的角度看
    	一个部门可以对应多个老师
    	一个部门可以对应一个老师
    	多个部门可以对应一个老师? 不行 
    	一对多 
    # 3.如何处理一对多(多对一)?
    	在老师表中存储部门id
    	即多的一方存储单的一方的id
    	多的作为从表,一的作为主表
    

    1.处理方式

    在一的一方即teacher表中保存相应部门(多的一方)的编号;
    

    案例:

    	#部门:
    	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.主表与从表

    1.先有主表 再有从表

    2.比如先有单位,后有职工,因此主表一般是单位信息表,从表一般是职工信息表,但也不是绝对的,根据业务需求来

    3.我们使用foreign key设置外键后,在插入记录时,必须保证外键的值是主表中存在的主键id

    4.插入数据的顺序:先插入主表记录,在插入从表记录

    5.当然,从表更新外键时也必须保证外键的值在主表中是存在的

    6.更新主表记录的主键时,要保证从表中没有外键关联主表中被更改的主键id

    7.必须先删除从表,再删除主表,删除主表记录前,要保证从表中没有外键关联主表中被删除的主键id

    creat table dept(id int primary key auto_increment, name char);
    
    create table worker(
    	id int primary key auto_increment, 
    	name char, 
    	dept_id int, 
    	foreign key(dept_id) references dept(id)
    	);
    	
    drop table dept;  # ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
    
    drop table worker;
    drop table dept;  # ok
    
    1.练习:

    1.班级表和学员表

    2.主表是班级,从表是学员

    create table class(
    	id int primary key auto_increment, 
    	name char(10),
    );
    
    create table student(
    	id int primary key auto_increment,
    	name char(10),
    	c_id int,
    	foreign key(c_id) references class(id)
    );
    

    2.级联操作

    1.cascade

    1.作用:当我们需要删除部门(主表)信息时,必须先删除从表中关联的数据,很麻烦 ,级联操作就是解决这个问题,它可以在你操作主表时,自动的随之操作从表

    2.级联操作应用场景: 用在外键关联中,可以级联的删除或更新从表记录

    **3.格式:

    # 级联更新/删除可以一起写,也可以分开写,按需求来
    foreign key(从表的外键字段) references 主表(主表主键字段)  
    	on update cascade    # 级联更新
    	on delete cascade    # 级联删除
    
    2.两种级联操作

    1.级联的删除

    **当删除主表时自动删除从表中相关数据**
    

    2.级联更新

    **当主表的主键更新时自动的更新关联的从表数据**
    
    1.案例:以班级和学员为例:
    drop table if exists class;
    # 如果这表存在 才执行删除  可以避免报错  if exists
    # if not exists  如果不存在才执行
    create table class(
            id int primary key auto_increment,
            name char(20)
    	);
    insert into class values(null,"py9");
    insert into class values(null,"py10");
    
    #创建表的时候指定级联操作 
    drop table if exists student;
    create table student(
            id int primary key auto_increment,
            name char(20),
            gender char(1),
            c_id int,
        	foreign key(c_id) references class(id)
            on update cascade
       	 	on delete cascade
    	);
    # 级联操作可以单独使用 也可以一起使用   空格隔开即可 
    insert into student values(null,"jack","m",1);
    insert into student values(null,"rose","m",1);
    insert into student values(null,"tom","m",2);
    

    3.多对多

    1.如何确定多对多关系

    # 老师表和学生表(套路一样)
    
    # 1.老师表角度: 一个老师可以教多个学生(一对多)
    
    # 2.学生表角度: 一个学生可以由多个老师教(一对多)
    
    # 3.如果双方都是一对多的关系,那么,两者是多对多关系  
    

    1.处理方式

    建立一个中间表,用于存储关系,至少具备两个字段分别指向老师和学生的主键,两个字段都是外键,如下:
    
    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 = "老李"
        )
    );
    
    # 子查询方式(查看多条记录):
    # 1.可以在条件处加any
    select name from teacher where id = any(
        select t_id from t_s_r where s_id = any(
        select id from student where name = "老李"
        )
    );
    
    # 2.可以将条件处的“=”换成“in”
    select name from teacher where id in (
        select t_id from t_s_r where s_id in (
        select id from student where name = "老李"
        )
    );
    

    2.子查询方式

    1.将先查询到的结果作为之后查询的条件

    2.可以查看多条记录:

    • 1.将查询条件语句中的"="换成"in"
    • 2.在查询条件语句中的"="后面加上"any"
    • 3.当想查询字段相同的多条记录时,可以用"or"将多个条件连在一起
    # 子查询方式(查询单条记录):
    select name from teacher where id = (
        select t_id from t_s_r where s_id = (
        select id from student where name = "老李"
        )
    );
    
    # 子查询方式(查看多条记录):
    # 1.可以在条件处加any
    select name from teacher where id = any(
        select t_id from t_s_r where s_id = any(
        select id from student where name = "老李"
        )
    );
    
    # 2.可以将条件处的“=”换成“in”
    select name from teacher where id in (
        select t_id from t_s_r where s_id in (
        select id from student where name = "老李"
        )
    );
    
    # 3.条件是同字段时,用"or"相连
    select name from teacher where id in (
    	select t_id from t_s_r where s_id in (
        select id from student where name = '老李' or name = '老王'
        )
    )
    
  • 相关阅读:
    The connection to the server localhost:8080 was refused
    Error: client: etcd cluster is unavailable or misconfigured; error #0: dial tcp 127.0.0.1:4001: getsockopt: connection refused
    docker-machine on azure
    Spine学习二 -播放Spine动画
    第三人称角色控制器解析
    3D人物移动控制实现方案
    Transform与Vector3 的API
    Animator.SetFloat(string name,float value,float dampTime,float deltaTime)详解
    Unity坑之 加了Rigidbody后主角反而朝天上飞?
    使用代码给Unity中的动画片段绑定回调函数
  • 原文地址:https://www.cnblogs.com/itboy-newking/p/11185374.html
Copyright © 2020-2023  润新知