Python基础之MySQL数据库

一、约束概述

1、为什么要约束

​ 为了防止数据库中存在不符合语义规定的数据和防止错误信息的输入、输出造成无效的操作而提出的

​ 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制，从以下四个方面考虑

• 实体完整性：例如一个表中不能存在两条相同的，无法区分的记录
• 域完整性：例如年龄范围，性别范围
• 引用完整性：例如学生所在的班级、学校，在学校中要能找到班级、在班级能找到学生
• 用户自定义完整性：例如用户名唯一，密码不能不空

2、什么是约束

​ 约束是指，对字段名下数据的强制约束

​ 约束的方式：

• 创建表时约束：create table

create table 表名（字段名 类型（数字） 约束条件）;

3、约束的分类

• 根据约束数据列的限制，约束可分为
  • 单列约束：每个约束只能约束一列
  • 多列约束：每个约束可约束多列数据
• 根据约束的作用范围，约束可分为
  • 列级约束：只能作用在一个列上，跟在列的定义后面
  • 表级约束：可以作用在多个列上，单独定义
• 根据约束的作用，约束可分为
  • 非空约束：规定某个字段不能为空
  • 唯一约束：规定某个字段的数据不能重复
  • 主键约束：非空且唯一，一个表只能拥有一个主键字段
  • 外键约束：主要体现在父子表中关系数据的约束操作
  • 检查约束：控制特定列中的值的完整性约束
  • 默认值约束：在不特别添加数据的情况下，该字段名下数据使用默认数据

4、查看当前表已有的约束

方式一：

​ show create table 表名；

方式二：

​ describe 表名；/desc 表名；

二、约束语法及用法

1、无符号

• 关键词：unsigned

• 作用：在SQL中，整型和浮点类型都自带正负号，而很多数据并用不到负数，例如人的年龄，手机号码，这时就可以通过约束条件解除正负号

mysql> create table t2(id tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

2、零填充

• 关键词：zerofill

• 作用：在创建表时，整型和浮点型后一般不用输入数字，系统会默认生成，在添加数据后，系统指挥默认显示该数值的大小及位数，通过零填充加数值，可固定数据值现实位数，不足部分用零填充

mysql> create table t4(id tinyint(2) zerofill);
Query OK, 0 rows affected (0.01 sec)

3、非空

• 关键词：not null

• 作用：所有字段类型，在不做非空约束的情况下，插入的数据值都可以为空（null），在创建表阶段可以约束该字段下数据为非空，约束后在往表内添加数据时，该字段下不能为空，空字符除外

mysql> create table t5(id int(3) zerofill, name varchar(16) not null);
Query OK, 0 rows affected (0.02 sec)

4、默认值

• 关键词：default

• 作用：在创建表阶段，在类型后约束默认值，可在默认值后方填入默认值，添加该字段下的数据时，不进行特殊定义的话会采用默认值

mysql> create table t(id int primary key auto_increment,
    -> name varchar(32) not null,
    -> gender varchar(8) default 'mela');
Query OK, 0 rows affected (0.02 sec)

5、唯一值

5、1.单列唯一（列级）

• 关键词：unique

• 作用：唯一值是指，在创建表阶段，可通过约束唯一值的条件，使在往该表内约束的字段下添加的数据不能重复出现，例如往序号字段下添加‘序号1’后，该序号将不能在重复出现在其他列

mysql> create table t6(id int unique, name varchar(16) not null);
Query OK, 0 rows affected (0.04 sec)

5、2.联合唯一（表级）

• 关键词：unique(字段名1，字段名2，...)

• 作用：联合唯一不在单个字段名下定义，而是单独定义，在关键词后方括号内添加需要进行联合唯一的字段名，那么被添加的多个字段名的数据值将不能共同出现，单个字段名下的数据值可以重复

mysql> create table t7(id int, name varchar(16), unique(id, name));
Query OK, 0 rows affected (0.02 sec)

6、主键

• 关键词：primary key

• 作用：单从约束层面上而言的话，主键就相当于‘非空且唯一’，InnoDB存储引擎规定了所有的表都必须拥有且只能有一个主键（在创建表的时候，可以不用手动约束，系统会将其他非空且唯一的字段升级为主键，如果都没有约束的话，后台会自动生成一个隐藏的主键），主键可以加快数据的搜索速度（如果没有主键的话，查找数据就需要一行一行的进行），在我们创建表的时候应该主动约束一个字段为主键，用来标识数据的唯一性，通常使用‘id（编号）’字段

mysql> create table t8(id int primary key, name varchar(16));
Query OK, 0 rows affected (0.02 sec)

7、自增

• 关键词：auto_increment

• 作用：通常在存储多个数据的时候，我们都会给数据进行编号，方便查找，自增的意思就是帮助我们生成数据的编号，不需要在手动填写，这样的好处就是省时、省力，还不用专门的去记忆当前的序号是多少。

• 特性：自增不会因数据删除而退回，只会往前，如果自己设置了更大的数，那么自增就会基于这个数往更大的数去自增,，自增的字段只能是主键

• 格式化表格：

  • truncate 表名; (可以用来个格式化表格并重置自增)

mysql> insert into t9(name, age) values('jason', 18),('kang',19);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

8、外键

8、1.外键的定义

​ 外键是某个表中某一列，它包含在另一个表的主键中

​ 外键也是索引的一种，是通过一张表指向另一张表中的主键，来对两张表进行关联

​ 一张表可以有一个外键，也可以存在多个外键，与多张表进行关联

8、2.外键的作用

主要用于表与表之间的数据关联，减少数据冗余，保证数据的一致性，完整性，主要体现在以下两个方面：

• 阻止执行：
  • 从表插入新行，其外键值不是主表的主键值便阻止插入
  • 从表修改外键值，新表不是主表的主键值便阻止修改
  • 主表删除行，其主键值在从表里存在则阻止删除（想要删除必须删除从表的相关行）
  • 主表修改主键值，旧值在从表里存在则阻止修改（要想修改，必须先删除从表的相关行）
• 级联执行：
  • 主表删除行，连带删除从表关联行
  • 主表修改值，连带修改从表关联行的值

8、3.外键的建立

主表：

create table 主表名(
        id 类型 主键 ，
        字段名 类型 约束条件)

从表：

create table 从表名(
    id 类型 主键 ,
    字段名 类型(数字) 约束条件，
    绑定外键的字段名  int，
    foreign key(主表名) references 主表名(主表的主键字段名)
)

• 在创建主表和从表时，主表必须已经存在于数据库中
• 录入数据的时候一定要先录入主表的数据
• 修改数据的时候外键字段无法删除和修改

级联创建的方式：

​ 需要在创建表阶段，在从表外键关键词后加入：

• 级联更新：on update cascade
• 级联删除：on delete cascade

create table 从表名(
   id 类型 主键 ,
   字段名 类型(数字) 约束条件，
   绑定外键的字段名  int，
   foreign key(主表名) references 主表名(主表的主键字段名)
	on update cascade
	on delete cascade)

8、4.外键的多种关系

1.一对多：

​ 以员工表为例，通常，一个员工只可以对应一个部门，而一个部门可以对应多个员工，这就是一对多的关系

​ 对于一对多的关系，外键字段应该建立在‘多’的一方

create table dep(id int primary key auto_increment,
	dep_name varchar(32)
	);

create table emp(id int primary key auto_increment,
	emp_name varchar(32) not null,
 	dep_id int, foreign key(dep_id) references dep(id)
 	on update cascade
  	on delete cascade
  	);

2.多对多：

​ 以书籍表与作者表为例

• 先站在书籍表的角度
  问:一本书能否对应多个作者
  答:可以

• 再站在作者表的角度
  问:一个作者能否对应多本书
  答:可以

• 结论:两个都可以 关系就是'多对多'
  针对'多对多'不能在表中直接创建 需要新建第三张关系表

create table book(
	id int primary key auto_increment,
 	title varchar(32),
  	price float(5,2)
);

create table author(
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);

create table book2author(
	id int primary key auto_increment,
   author_id int,
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);

3.一对一：

​ 以用户表与用户详情表为例

• 先站在用户表的角度
  问:一个用户能否对应多个用户详情
  答:不可以
• 再站在用户详情表的角度
  问:一个用户详情能否对应多个用户
  答:不可以
• 结论:两个都可以 关系就是'一对一'或者没有关系
  针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);