Python 高级 14(3)数据库约束

数据库常用约束、增加删除约束、数据库导入导出、数据表设计思想-三大范式

2.4.8 添加约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的准确性，完整性、唯一性，可靠性、联动性。

设置外键约束字段所关联的字段，必须是主键约束字段。

就是说必须是主键约束字段才能给其他字段设置外间约束！

数据库常用约束总结：

  <1>主键约束

      作用：让数据具有唯一标识，一般设置ID字段，一个表中只能有一个主键字段

      语法：primary key

          ■ create table 表名(字段名 字段类型 primary key);

          ■ create table tpk(id int primary key , name char(10));

      注意：

          ■ 字段被设置了主键约束，同时也具有了唯一性约束和非空约束。 在字段中插入重复数据时，或不给数据时会报错。

  <2>自动增长

      作用：让数值类型字段数字值自动累加，需要配合主键使用

      语法：auto_increment

          ■ create table 表名(字段名 字段类型 auto_increment primary key);

          ■ create table tai(id int auto_increment primary key,name varchar(10));

      注意：

          ■ 自动增长设置在数值型字段上，需要配合主键约束一起使用。 如果字段没有设置主键约束，是不允许设置自动增长的。

  <3>唯一性约束

      作用：保证数据的唯一性，不会出现重复数据

      语法：unique

          ■ create table 表名(字段名 字段类型 unique);

          ■ create table tuni(id int unique,name char(10)）；

      注意：

          ■ 一个表中可以给多个字段设置唯一性，如果有需要的话。

  <4>非空约束

      作用：不允许字段为空，添加数据时必须给值

      语法：not null

          ■ create table 表名(字段名 字段类型 not null);

          ■ create table tnn(id int,name char(10) not null);

  <5>默认约束

      作用：在添加数据时，如果没有给定有默认约束字段的数据，该字段使用默认值填充

      语法：default

          ■ create table 表名(字段名 字段类型 default 值);

          ■ create table tdt(id int,name char(10) default 'NoName');

  <6>外键约束

      作用：让两表之间产生联动关系,为两表建立引用参考关系

      语法：foreign key(字段名) references 表名(字段名)

          ■ create table 表名(字段名 字段类型… , foreign key(外键字段名) references 关联表名(关联字段名);

          ■  -- 表1

create table fClass(id int primary key,name char(10));

-- 表2

create table fStudent(id int primary key auto_increment, name char(10), cid int, foreign key(cid) references fClass(id));

      注意：

          ■ 设置外键约束字段所关联的字段，必须是主键约束字段。

              也就是说只能用主键约束字段给其他字段设置外键约束！

          ■ 想要删除有设置外键的表，必须先删除外键所关联的表。

              drop table fStudent;

              drop table fClass;

  <7>检查约束(在MySQL中无效，不起作用,但其它数据库中有效)

      作用：设置字段的域范围，也就是有效值范围

      语法：check(值范围条件)

          ■ create table 表名(字段名 字段类型 check(值范围条件));

          ■ create table tchk(id int,age int check(age > 0 and age < 150),gender char(10) check('boy' or 'girl'));

  <8>unsigned

      作用：unsigned属性是将数字类型无符号化，也就是非负数，只用于整型。特别用于主键是自增的类型。

      语法：unsigned

          ■ create table 表名(字段名 int unsigned);

          ■ create table tpk(id int unsigned);

增加删除约束总结：

添加约束前应该保证该字段上没有新添加的约束 删除约束前应该保证该字段上有这个约束

  <1>添加主键约束

      语法：alter table 表名 add constraint 约束名 primary key(字段名)

      alter table tpk add constraint PK_id primary key(id);

      注意：

          ■ 主键约束名一般以PK_字段名来命名

  <2>删除主键约束

      因为一个表中最多只能有一个主键约束，所以可以直接删除

      语法：alter table 表名 drop primary key

      alter table tpk drop primary key;

  <3>添加外键约束

      语法：alter table 表名 add constraint 外键约束名 foreign key(外键字段名) references 关联表(关联字段名)

      alter table tfk add constraint FK_id foreign key (id) references tpk(id);

      注意：

          ■ 外键约束名一般以FK_字段名来命名

  <4>删除外键约束

      语法：alter table 表名 drop foreign key 外键名

      alter table tfk drop foreign key FK_id;

1.没有对字段设置约束时容易出现的问题

  <1>数据冗余

  <2>失去数据完整性

  <3>数据缺少唯一标识

  <4>失去实体完整性

  <5>失去域完整性

  <6>失去引用完整性

<1>数据冗余

两个表中都存在学生姓名，没有必要。

<2>失去数据完整性

<3>数据缺少唯一标识

<4>失去实体完整性

<5>失去域完整性

<6>失去引用完整性

2.数据库常用约束

  <1>主键约束

      作用：让数据具有唯一标识

      语法：primary key

          ■ create table tpk(id int primary key , name char(10));

      注意：

          ■ 字段被设置了主键约束，同时也具有了唯一性约束和非空约束。 在字段中插入重复数据时，或不给数据时会报错。

  <2>自动增长

      作用：让数字值自动累加

      语法：auto_increment

          ■ create table tai(id int auto_increment primary key,name varchar(10));

      注意：

          ■ 自动增长设置在数值型字段上，需要配合主键约束一起使用。 如果字段没有设置主键约束，是不允许设置自动增长的。

  <3>唯一性约束

      作用：保证数据的准确性，不会出现重复数据

      语法：unique

          ■ create table tuni(id int unique,name char(10)）；

      注意：

          ■ 一个表中可以给多个字段设置唯一性，如果有需要的话。

  <4>非空约束

      作用：不允许字段为空，添加数据时必须给值

      语法：not null

          ■ create table tnn(id int,name char(10) not null);

  <5>默认约束

      作用：在添加数据时，如果没有给定有默认约束字段的数据，该字段使用默认值填充

      语法：default

          ■ create table tdt(id int,name char(10) default 'NoName');

  <6>外键约束

      作用：让两表之间产生联动关系

      语法：foreign key(字段名) references 表名(字段名)

          ■  -- 表1

create table fClass(id int primary key,name char(10));

-- 表2

create table fStudent(id int primary key auto_increment, name char(10), cid int, foreign key(cid) references fClass(id));

      注意：

          ■ 设置外键约束字段所关联的字段，必须是主键约束字段。

          ■ 想要删除有设置外键的表，必须先删除外键所关联的表。

              drop table fStudent;

              drop table fClass;

  <7>检查约束(在MySQL中无效，不起作用)

      作用：设置字段的域范围，也就是有效值范围

      语法：check(值范围条件)

          ■ create table tchk(id int,age int check(age > 0 and age < 150),gender char(10) check('boy' or 'girl'));

2.4.9 增加删除约束

添加约束前应该保证该字段上没有新添加的约束 删除约束前应该保证该字段上有这个约束

  添加主键约束

      语法：alter table 表名 add constraint 约束名 primary key(字段名)

      alter table tpk add constraint PK_id primary key(id);

  删除主键约束

      因为一个表中最多只能有一个主键约束，所以可以直接删除

      语法：alter table 表名 drop primary key

      alter table tpk drop primary key;

  添加外键约束

      语法：alter table 表名 add constraint 外键约束名 foreign key(外键字段名) references 关联表(关联字段名)

      alter table tfk add constraint FK_id foreign key (id) references tpk(id);

  删除外键约束

      语法：alter table 表名 drop foreign key 外键名

      alter table tfk drop foreign key FK_id;

2.4.10 数据库导入导出

作用：使用数据库和导入导出功能，可以对数据库进行备份，迁移等操作。

总结：

  导出

      mysqldump –uroot –p(注意不要输入密码) 要导出的数据库名 要导出的数据表 ... > 目标文件.sql

  导入

      mysql -uroot -p 数据库名 < 要导入的文件.sql

  导出数据库

      导出数据库使用 mysqldump 命令完成

      语法：mysqldump –uroot –p(注意不要输入密码) 要导出的数据库名 要导出的数据表 ... > 目标文件.sql

  导出整个数据库

      mysqldump -uroot -p School > school_bak.sql

  导出库当中的指定表

      mysqldump -uroot -p School tpk > tpk.sql

  导出库当中的多个表tpk和tfk

      mysqldump -uroot -p School tpk tfk > tpktfk.sql

  导入数据库

      导入数据库前需要先创建一个空数据库

      语法：mysql -uroot -p 数据库名 < 要导入的文件.sql

      mysql -uroot -p sch < school_bak.sql

2.4.11 数据表设计思想

  需求分析：根据用户的需求，分析出需要记录的数据

  需求设计：根据分析出的数据，设计E-R模型图

  详细设计：将E-R模型图转换成数据表

  三大范式：使用数据库三大范式的设计思想对数据表进行审核(数据库在设计时，需要满足三大范式的设计思想。)

      第一范式 : 每一列都具有原子性，也就是不能再分割

      第二范式 : 每个表只描述一件事情

      第三范式 : 表中不能存在冗余字段

          ■ 非主属性不存在依赖关系

      每一范式的实现都必须要在前一范式的实现基础上

<1>E-R模型图

概念：Entity-Relationship,实体关系图 组成元素：

  矩形 表示实体

  椭圆形 表示属性

  菱形 表示关系

ER图应用案例： 将下面的项目需求 以ER图形式表示出来

表示关系：

<2>三大范式（了解）

数据库在设计时，需要满足三大范式的设计思想。

三大范式的理论非常抽象，有兴趣可以查阅资料进行了解，

下面以简单的形式描述三大范式，方便大家有一个简单的概念上的理解。

  第一范式 : 每一列都具有原子性，也就是不能再分割

  第二范式 : 每个表只描述一件事情

      在满足第一范式的基础之上表的属性必须要有主键依赖

  第三范式 : 表中不能存在冗余字段

2.4.12 数据表练习

  创建数据库 School

      create database School;

  创建学生表 tStudent

      tStudent学生表结构：

      -- sId -- 学生编号 -- sName -- 姓名 -- sAge -- 年龄 -- sGender -- 性别 -- sAddress -- 地址 -- sPhone -- 电话 -- sBirthday -- 生日 -- sCardId -- 身份证号 -- sClassId -- 班级Id

      答案：

      create table tStudent(sId int,sName char(20),sAge tinyint,sGender enum('boy','girl'),sAddress varchar(50),sPhone char(11),sBirthday year,sCardId char(18),sClassid int);

  创建成绩表 tScore TblScore成绩表结构：

      -- sId（成绩id,主键,自动编号） -- sStuId（学生编号） -- sEnglish（英语成绩） -- sMath（数学成绩）

      答案：

          ■ create table tScore(sid int unsigned auto_increment primary key, sStuId int, sEnglish float(5,2), smath float(5,2) );

  创建班级表 tClass tClass班级表结构：

      -- cId –班级ID -- cName –班级名称 -- cDescription –班级描述

      答案：

          ■ create table tClass(cId int,cName char(10),cDescription varchar(100));

  创建老师表 tClass tTeacher老师表：

      -- tTId -- tTName -- tTGender -- tTAge -- tTSalary -- tTBirthday

      答案：

          ■ create table tTeacher(tId int, tName char(20),tAge tinyint,tGender enum('boy','girl'),tSalary float(10,2),tBirthday year);