MySQL主键与外键

MySQL主键与外键

约束条件之主键

# 主键 primary key
1.单从约束层面上来讲 相当于 not null unique >>> 非空且唯一
    验证：
        create table t1(id int primary key);
        create table t2(id int not null unique);  
2.主键还是InnoDB存储引擎组织数据表的依据
    # 1.InnoDB规定了一个表必须要有且只有一个主键
    # 2.如果你不指定主键则会采用隐藏的字段作为主键
    # 3.当表中没有主键但是有为不空且唯一的字段自动变为主键（从上而下第一个）

主键是可以加快查询速度的 隐藏意味着无法使用主键即速度无法提升  

    create table t2(
       id int,
       name char(16),
       age int not null unique,
       addr char(16) not null unique
    ); 

"""结论:
在创建表的时候一般都需要有一个id字段(uid sid pid...)
并且该字段应该设置为表的主键字段
"""

了解

主键可以单列主键也可以联合主键 但是联合主键使用频率很低
    create table t3(
       id int,
       name char(16),
       primary key(id,name)
    );

约束条件之自增

create table t4(
    id int primary key,
    name char(16)
);
# 自增  auto_increment
'''配合主键一起使用'''
create table t5(
    id int primary key auto_increment,
    name char(16)
);
主键字段的固定写法如下:
id int primary key auto_increment

补充

1.自增的特性不会因为delete操作而重置
      delete from 只删数据
2.如果真的想要重置表结构
    truncate 表名

约束条件之外键

# 前戏
'''定义一张员工表'''
id    name    age    dep_name    dep_desc
1.表结构不清晰    可以忽略
2.表数据重复        可以忽略
3.数据扩展性极差   不能忽略    
'''如何解决上面三个问题？''' 拆表
在员工表里面加一个部门编号字典dep_id(外键)

外键: 记录表与表之间数据关系的字段

表关系的种类

一对多关系
多对多关系
一对一关系
没有关系

如何判断表关系

一对多关系

'''判断表关系遵循 换位思考原则'''
先站在一张表的层面,看它里面的元素与另一张表的元素是否为一对多关系,如果是，就是一对多，如果不是，反过来判断。

"一对多"表关系外键字段建在多的一方
# 1.先写基本字段类型与约束条件
# 2.之后再考虑外键

代码演示

create table emp(
    id int primary key auto_increment,
    name char(16),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

外键字段的特性

1.在创建表的时候一定要先创建被关联的表(自身没有外键字段的表)
2.在插入数据的时候也是先插入被关联表再插入关联表
    只能够填写被关联字段的值
3.被关联表中的数据无法删除和修改

如何自由的删除和修改呢？

级联更新与级联删除
    create table emp(
        id int primary key auto_increment,
        name char(16),
        age int,
        dep_id int,
        foreign key(dep_id) references dep(id) 
        on update cascade  # 级联更新
        on delete cascade  # 级联删除
);

代码演示

create table dep1(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

create table emp1(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on update cascade
    on delete cascade
);
insert into dep1(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','驻上海形象大使'),
('nb技术部','nb技术能力有限部门');

insert into emp1(name,gender,dep_id)  values
('jason','male',1),
('tom','male',2),
('tony','male',1),
('kevin','male',1),
('jim','female',3);

多对多关系

# 以书籍与作者表为例
    1.先站在书籍表的角度
        问：一本书能否对应多个作者
        答：可以
    2.站在作者表的角度
        问：一个作者能否对应多本书
        答：可以
结论：两边都可以 那么表关系就是"多对多"

错误示范

create table book(
    id int primary key auto_increment,
    name char(16),
    price int,
    author_id int,
    foregin key(author_id) references author(id)
    on update cascade
    on delete cascade
);
create table author(
    id int primary key auto_increment,
    name char(16),
    age int,
    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,
    name char(16),
    price int
);
create table author(
    id int primary key auto_increment,
    name char(16),
    age int
);
create table book2author(
    id int primary key auto_increment,
    aucthor_id int,
    book_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

 

一对一关系

# 以用户表与用户详情表为例
    1.先站在用户表层面
        问:一个用户能否对应多个用户详情
        答:不可以
    2.再站在用户详情表层面
        问:一个用户详情能否对应多个用户
        答:不可以
    结论:两边都不可以，那么表关系要么是没有关系要么是"一对一"关系


"""
一对一表关系 
    外键建在任意一方都可以 但是推荐建在查询频率较高的表中
"""

代码演示

create table user(
    id int primary key auto_increment,
    name varchar(16),
    detail_id int unique,
    foreign key(detail_id) references user_detail(id)
    on update cascade
    on delete cascade
);
create table user_detail(
    id int primary key auto_increment,
    phone bigint,
    addr varchar(16)
);

 

修改表相关SQL语句

1.修改表名
    alter table 表名  rename 新表名;
2.末尾增加字段
    alter table 表名 add 字段名 字段类型 [完整性约束条件…]
    开头增加字段
    alter table 表名 add 字段名 字段类型 [完整性约束条件…] first;
    任意位置增加字段
    alter table 表名 add 字段名 字段类型 [完整性约束条件…] after 字段名;
3.删除字段
    alter table 表名 drop 字段名;
4.修改字段 # modify 只能改字段数据类型完整约束,不能改字段名,但是change可以！
    alter table 表名 modify 字段名 数据类型 [完整性约束条件...];
    alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件...];
    alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件...];

 总结：如何判断表关系？

1.选一张表观察这张表与另外一张表是否存在一对多或者多对多关系，如果存在一对多或者是多对多，即为这种类型，如果不存在，再到另外一张表中观察，同理即为这种类型，不存在则为没有关系。

2.观察一对一关系 同样是选一张表观察这张表与另外一张表是否存在一对一的关系，存在即为一对一，不存在则到另外一张表观察是否为一对一关系，还不存在则没有关系。

 作业

书籍表与出版社表

create table publish(    
　　 id int primary key auto_increment,
    name char(16),
    road char(16)      
);

create table book(
    id int primary key auto_increment,
    name char(16),
    price int,
    pub_id int,
    foreign key(pub_id) references publish(id)
　　 on update cascade
    on update cascade 
);

老师表与课程表

create table teacher (
    id int primary key auto_increment,
    name char(16),
   age int(16),
   course_id int,
   foreign key(course_id) references course(id)
   on update cascade
   on delete cascade      
);
create table course(
   id int primary key auto_increment,
   name char(16)  
);

学生表与班级表

create table student(
    id int primary key auto_increment,
    name char(16),
    age int,
    class_id int,
    foreign key(class_id) references class(id)
    on update cascade
    on delete  cascade
);

create table class(
    id int primary key auto_increment,
    number char(16)
);

书籍表与作者表

create table books(
   id int primary key auto_increment,
   name char(16),
   price int 
);
create table writer(
    id int primary key auto_increment,
    name char(16),
    age int
);  
create table bkw(
    id int primary key auto_increment,
    books_id int,
    writer_id int,
    foreign key(books_id) references writer(id)
    on update cascade
    on delete cascade,
    foreign key(writer_id) references books(id)
    on update cascade
    on delete cascade
);    

作者表与作者详情表

create table author(
    id int primary key auto_increment,
    name char(16),
    age int,
    audesc_id int unique,
    foreign key(audesc_id) references audesc(id)
    on update cascade
    on delete cascade
);

create table audesc(
    id int primary key auto_increment,
    hobby char(16),
    height int,
    weight int
);