python和表格关系_46.数据库02——约束、表之间关系

一、约束条件

1、default

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

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

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

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

2、unique

单列唯一

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

create tablet3(

idint unique,

namechar(16)

);insert into t3 values(1,'jason'),(1,'egon');报错insert into t3 values(1,'jason'),(2,'egon');

联合唯一

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

create tablet4(

idint,

ipchar(16),

portint,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 tablet6(

idint,

namechar(16),

ageint not null unique,

addrchar(32) not null unique);

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

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

# 单个字段主键create tablet5(

idint primary keynamechar(16)

);

# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)create tablet7(

ipchar(16),

portint,primary key(ip,port)

);

4、auto_increment自增

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

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

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

create tablet8(

idint primary keyauto_increment,

namechar(16)

);insert into t8(name) values('jason'),('egon'),('kevin');create tablet9(

idint primary keyauto_increment,

namechar(16),

cidintauto_increment

);

ERROR1075 (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 tabledep(

idint primary keyauto_increment,

dep_namechar(16),

dep_descchar(32)

);create tableemp(

idint primary keyauto_increment,

namechar(16),

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

dep_idint,foreign key(dep_id) referencesdep(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 真正做到数据之间有关系

更新就同步更新

删除就同步删除

级联更新 >>> 同步更新

级联删除 >>> 同步删除

create tabledep(

idint primary keyauto_increment,

dep_namechar(16),

dep_descchar(32)

);create tableemp(

idint primary keyauto_increment,

namechar(16),

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

dep_idint,foreign key(dep_id) referencesdep(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 tablebook(

idint primary keyauto_increment,

titlevarchar(32),

priceint,

author_idint,foreign key(author_id) referencesauthor(id)on update cascade# 同步更新on delete cascade# 同步删除

);create tableauthor(

idint primary keyauto_increment,

namevarchar(32),

ageint,

book_idint,foreign key(book_id) referencesbook(id)on update cascade# 同步更新on delete cascade# 同步删除

);

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

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

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

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

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

create tablebook(

idint primary keyauto_increment,

titlevarchar(32),

priceint);create tableauthor(

idint primary keyauto_increment,

namevarchar(32),

ageint);create tablebook2author(

idint primary keyauto_increment,

author_idint,

book_idint,foreign key(author_id) referencesauthor(id)on update cascade# 同步更新on delete cascade, # 同步删除foreign key(book_id) referencesbook(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 keyauto_increment,

phoneint,

addrvarchar(64)

);create tableauthor(

idint primary keyauto_increment,

namevarchar(32),

ageint,

authordetail_idint unique,foreign key(authordetail_id) referencesauthordetail(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;