约束条件之主键/自增/外键

目录

• 约束条件之主键
• 约束条件之自增
• 约束条件之外键

 

 

 约束条件之主键

# 主键
primary key
1.从约束方面相当于not null unique  # 非空且唯一
    验证：
        create table t1(id int primary key);
        creare table t2(id int not null unique);
2.从存储方面主键是InnoDB组织数据表的依据
    2.1 InnoDB规定了一个表必须要有且只有一个主键
    2.2 如果不指定主键则会采用隐藏的字段作为主键
    2.3 当表中没有主键但是有非空且唯一的字段则自动升级为主键(顺序：自上而下第一个)

演示

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

 

 

 

结论

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

 

联合主键

create table t12(
       id int,
       name char(16),
       primary key(id,name) # 联合主键
    );

 

约束条件之自增

# 自增
auto_increment

eg:
    create table t13(
    id int primary key auto_increment,
    name char(16)
    );
    
    
"""
创建表、主键字段的固定写法：
    id int primary key auto_increment

"""

 

补充

1.自增的特性不会因为delete操作而重置/回退
    delete from 表名; # 只删除数据
2.重置需清空表数据和表结构
    truncate 表名; # 既删除数据又重置主键

 

 

约束条件之外键

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

 

表关系的种类

1.一对多关系
2.多对多关系
3.一对一关系
4.没有关系

 

如何判断表关系

 一对多关系

"""
判断表关系遵循<换位思考>的原则
"""

eg:
    # 员工表与部门表
    1.先站在员工表的层面
        问:一个员工能否对应多个部门
        答:不可以
    2.再站在部门表的层面
        问:一个部门能否对应多个员工
         答:可以
    # 结论:
        一个可以，一个不可以，那么员工表与部门表就是"一对多"关系
        "一对多"表关系外键字段建在多的一方

 

 

对应的SQL语句

# 1.先写基本字段类型与约束条件
# 2.再考虑外键
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
create table emp(
    id int primary key auto_increment,
    name char(16),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);

 

外键字段的特性

1.在创建表的时候一定要先创建被关联表
2.插入数据的时也是先插入被关联表再插入关联表
# 只能够填写被关联字段中出现的值
3.被关联表中的数据无法自由删除和修改
4.级联更新/级联删除

 

级联更新

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.在站在作者表
        问:一名作者能否对应多本书
        答:可以
    结论:两边都可以,那么表关系就是"多对多"关系

 

对应的SQL语句

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,
    author_id int,
    book_id int,
    foregin 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.再站在用户详情表层面
        问:一个用户详情能否对应多个用户
        答:不可以
    结论:两边都不可以，那么表关系要么是没有关系要么是"一对一"关系

 

 

 

相应的SQL语句

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 字段名  数据类型 [完整性约束条件…],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 旧字段名 新字段名 新数据类型 [完整性约束条件…];