数据库 约束，表之间关系修改表复制表代码集合

• 一 约束条件
  • 　　1.1 default默认值
  • 　　1.2 unique唯一
  • 　　1.3 primary key主键
  • 　　1.4auto_increment自增
• 二 表与表之间建立关系
  • 　　2.1 foreign key外键
  • 　　2.2 表关系（外键的使用）
  • 　　2.3 级联更新和级联删除
• 三 了解知识点
  • 　　3.1 修改表
  • 　　3.2 复制表

一、约束条件

1、default

如果数据很多都是重复的，为了方便可以为数据增加默认值，从而减少工作量，提高效率。

和Python中一样，函数可以设置默认参数，在传参时可以不用赋值，sql语句也有这么个默认值。

举个例子：假如绝大部分学生为男生，可以将学生表中的gender的默认值设为男，即

gender enum('male','female') default 'male'

2、unique

单列唯一

unique约束可以使值是唯一的，应用如注册用户名不得重复这类的

create table t3(
    id int unique,
    name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');报错
insert into t3 values(1,'jason'),(2,'egon');

联合唯一

有的参数是要联合起来表示一个唯一的东西，比如ip+port用于标志唯一一个应用程序，ip可以重复，port也可以重复，但是ip+port不能重复，所以就有这么个联合唯一机制。

create table t4(
    id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080);  报错

3、primary key主键

1.单单从约束效果上来看primary key等价于not null + unique非空且唯一！！！

create table t5(id int primary key);
insert into t5 values(null);  报错
insert into t5 values(1),(1);  报错
insert into t5 values(1),(2); 

 

2.它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据

2.1一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键

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

2.2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度

2.3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键

# 单个字段主键
create table t5(
    id int primary key
    name char(16)
);
# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t7(
    ip char(16),
    port int,
    primary key(ip,port)
);

 

4、auto_increment自增

我们平时看东西，如果有编号且按顺序排列，肯定会更加清晰明了，数据库也是如此

当表中数据编号特别多的时候，人工去排就很麻烦，所以就有了这么个auto_increment

注意:auto_increment通常都是加在主键上的 不能给普通字段加

create table t8(
    id int primary key auto_increment,
    name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');


create table t9(
    id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

补充知识点

delete from t1 删除表中数据后 主键的自增不会停止

truncate t1 清空表数据并且重置主键

讲道理，truncate其实很鸡肋，我与其要清空表数据，为啥不建个新表

 

返回最上

二、表与表之间建关系

1、外键

定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
1 该表的组织结构不是很清晰(可忽视)
2 浪费硬盘空间(可忽视)
3 数据的扩展性极差(无法忽视的)

如何优化？
"""上述问题就类似于你将所有的代码都写在了一个py文件中"""
将员工表拆分 员工表和部门表

为了解决上面的问题，数据库就通过引入外键来关联表，使表与表之前互相关联

外键就是用来帮助我们建立表与表之间关系的
foreign key

2、表关系

2.1 一对多

判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议
换位思考 分别站在两张表的角度考虑

员工表与部门表为例
先站在员工表
思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
不能！！！
(不能直接得出结论 一定要两张表都考虑完全)
再站在部门表
思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
能！！！
得出结论
员工表与部门表示单向的一对多
所以表关系就是一对多

foreign key
1 一对多表关系 外键字段建在多的一方
2 在创建表的时候 一定要先建被关联表
3 在录入数据的时候 也必须先录入被关联表

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),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);


# 修改dep表里面的id字段
update dep set id=200 where id=2;  不行
# 删除dep表里面的数据
delete from dep;  不行

1 先删除教学部对应的员工数据 之后再删除部门
操作太过繁琐

 

2.2级联更新和级联删除
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),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

 

2.2 多对多

有了上面一对多的表关系的知识，那么多对多的表关系建立也很简单了，但是事实是这样的吗，我们先按上面一对多的知识来写多对多建表的sql语句

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    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),
    age int,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

看完上面的sql语句以后是不是感觉似曾相识？

对，没错，这和python中的模块循环导入很相似，都在建表的时候要关联对方，这样的话，谁都别想建成功。

为了解决多对多关系的问题，我们必须用新的方法来建表

于是乎，就有了以下方法：

让我们先单独开一张表，专门用来存放两张表数据之间的关系，也就是让多对多，变成多对一对多，让一个中间商来解决多对多的循环问题。

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);
create table book2author(
    id int primary key auto_increment,
    author_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  # 同步删除
);

 

2.3 一对一

id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二
用户表
用户表
id name age
用户详情表
id addr phone hobby email........

站在用户表
一个用户能否对应多个用户详情 不能！！！
站在详情表
一个详情能否属于多个用户 不能！！！
结论:单向的一对多都不成立 那么这个时候两者之间的表关系
就是一对一
或者没有关系(好判断)

客户表和学生表
在你们报名之前你们是客户端
报名之后是学生(期间有一些客户不会报名)

    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,　　　　　　#一对一主要是 唯一并且关联 （unique）
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
)

总结

表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张存储
一对一
建在任意一方都可以 但是推荐你建在查询频率较高的表中

 

返回最上

三、修改表

1 修改表名
alter table 表名 rename 新表名;

2 增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;

3 删除字段
alter table 表名 drop 字段名;

4 修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;

alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

四、复制表

我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表; 不能复制主键 外键 ...

create table new_dep2 select * from dep where id>3;