mysql-3

目录

• 约束条件
• 约束条件之外键
• Foreign Key
• 表关系之多对多
• 表关系之一对一

约束条件

1.primary key主键
	1.单从约束角度上而言主键等价于非空且唯一 not null unique
    	create table t1(
           id int primary key,
           name varchar(32)
        ); 
 	2.InnoDB存储引擎规定一张表必须有且只有一个主键
    	2.1.如果创建的表中没有主键也没有非空且唯一的字段 那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询:新华字典的目录)
   		2.2.如果创建的表中没有主键但是有非空且唯一的字段 那么InnoDB存储引擎会自动将该字段设置为主键
        	create table t2(
               nid int not null unique,
               sid int not null unique,
               uid int not null unique,
               name varchar(32)
        	); 
	3.创建表的时候都应该有一个'id'字段 并且该字段应该作为主键
    		uid、sid、pid、gid、cid、id
	补充说明
    		id int primary key  单列主键
			
          sid int,
			nid int,
			primary key(sid, nid)  联合主键
           
2.auto_increment自增
	该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用)
		create table t3(
        	id int auto_increment
        );
	there can be only one auto column and it must be defined as a key
		create table t4(
        	id int primary key auto_increment,
        	name varchar(32)
        );
    
 	补充说明:自增的特点
		自增的操作不会因为执行删除数据的操作而回退或者重置
			delete from 
		如果非要重置主键 需要格式化表
			truncate 表名;  # 删除表数据并重置主键值

约束条件之外键

1.外键前戏
	需要创建一张员工表
		id	name gender	dep_name dep_desc
	上述表的缺陷
        1.表结构不清晰 到底是员工表还是部门表(不严重 无所谓)
        2.字段数据反复存取 浪费存储空间(不严重 无所谓)
        3.表的扩展性极差 牵一发动全身(很严重 效率极低)
	优化操作>>>:拆表
        id	name	  gender
        id	 dep_name	dep_desc
        拆表之后解决了上述的三个问题 但是出现了一个致命的缺陷
   	解决措施
        id		name	gender 		dep_id
		添加一个部门编号字段填写部门数据的主键值
   	外键字段
		专门用于记录表与表之间数据的关系
  
2.外键字段的创建
	外键字段是用来记录表与表之间数据的关系 而数据的关系有四种
        一对多关系
        多对多关系
        一对一关系
        没有关系
	2.1.表数据关系的判定  >>>: '换位思考'
      针对员工表和部门表判断数据关系
    		1.先站在员工表的角度
                问:一条员工数据能否对应多条部门数据
                翻:一名员工能否属于多个部门
                答:不可以
          2.再站在部门表的角度
                问:一条部门数据能否对应多条员工数据
                翻:一个部门能否拥有多个员工
                答:可以
          完成换位思考之后得出的答案 一个可以一个不可以
          那么表关系就是"一对多"
            	部门是一 员工是多
          针对'一对多'的关系 外键字段建在多的一方 
          ps:没有多对一 统一称为'一对多'

Foreign Key

1.先写普通字段
2.然后再写外键字段   

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
"""
1.创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键)
2.插入表数据的时候 针对外键字段只能填写被关联表字段已经出现过的数据值
3.被关联字段无法修改和删除
	有点不太好 操作限制性太强
"""
级联更新、级联删除
	被关联数据一旦变动 关联的数据同步变动
create table emp1(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep1(id) 
    on update cascade  # 级联更新 
    on delete cascade  # 级联删除
);
create table dep1(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
        因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    我们为了能够描述出表数据的关系 又不想使用外键
        自己通过写SQL 建立代码层面的关系
"""

表关系之多对多

以书籍表和作者表为例
	1.先站在书籍表的角度
		问:一条书籍数据能否对应多条作者数据
		答:可以
	2.再站在作者表的角度
    	问:一条作者数据能否对应多条书籍数据
		答:可以
	总结:两边都可以 那么表数据关系就是'多对多'
	针对多对多表关系 外键字段不能建在任意一方!!!
   		create table book(
            id int primary key auto_increment,
            title varchar(32),
            author_id int,
            foreign key(author_id) references author(id) 
            on update cascade  # 级联更新 
            on delete cascade  # 级联删除
        );
       create table author(
           id int primary key auto_increment,
           name varchar(32),
           book_id int,
           foreign key(book_id) references book(id) 
           on update cascade  # 级联更新 
           on delete cascade  # 级联删除
       );
	需要单独开设第三张关系表 存储数据关系
            create table book(
                id int primary key auto_increment,
                title varchar(32)
        );
            create table author(
                id int primary key auto_increment,
                name varchar(32)
       );
            create table book2author(
                id int primary key auto_increment,
                book_id int,
                foreign key(book_id) references book(id) 
                on update cascade  # 级联更新 
                on delete cascade, # 级联删除
                author_id int,
                foreign key(author_id) references author(id) 
                on update cascade  # 级联更新 
                on delete cascade  # 级联删除
        );

表关系之一对一

以用户表和用户详情表
	1.先站在用户表的角度
  	2.再站在用户详情表的角度
   总结:两边都不可以 那么先考虑是不是没有关系
    	如果有关系那么肯定就是'一对一'
   针对'一对一'的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询
   create table user(
       id int primary key auto_increment,
       name varchar(32)
       detail_id int unique,
       foreign key(detail_id) references userDetail(id) 
       on update cascade  # 级联更新 
       on delete cascade  # 级联删除
   );
	create table userDetail(
        id int primary key auto_increment,
        phone bigint
    );