数据类型与约束

---

前言

1.完整性约束
列名 数据类型 [完整性约束]
2.完整性约束：
约束条件与数据类型的宽度一样，都是可选参数
作用：
用来保证数据的完整性和一致性 就是保证数据的正确性

主要分为：

约束	作用
PRIMARY KEY	主键
FOREIGN KEY	外键
NOT NULL	非空
UNIQUE KEY	唯一
AUTO_INCREMENT	自增
DEFAULT	默认

[案例]：
现在数据库有3个表要建立，分别是：请根据情况设计约束，并实现
student（sid，sname，sex) （学号，姓名，性别）

sid	sname
202001	张三
202002	李四
202003	王五

sex
男
男
男

course(cid,cname) （课程号，课程名）

cid	cname
C01	数据库
C02	C语言

grade(sid,cid,grade)

sid	cid
202001	C01
202001	C02
202002	C02

grade
80
20
30

---

一、表的约束

1.1主键约束

主键，也称主码，用于标识表中唯一的一条记录。一张表中只能有一个主键，并且主键值不能为空。
特点：1.唯一，2.非空
主键约束是最常用的一种约束，设置主键约束的关键字为PRIMARY KEY。
主键又分为定义字段时直接设置和定义所有字段后设置主键约束。

定义字段时设置主键约束

col_name 数据类型 primary key

定义所有字段后设置主键约束

PRIMARY KEY (col_name1，col_name2)

1.2自增约束

在向数据表中插入数据时，如果用户希望每条记录的“编号”自动生成，并且按顺序排列，可以为该字段设置自增约束。
设置自增约束的关键字为AUTO_INCREMENT，语法形式如下：

col_name 数据类型 AUTO_INCREMENT

• 一张表中只能设置一个字段为自增约束，并且该字段必须为主键。
• 每增加一条记录，字段值自动增加1。
• 字段类型必须为整数型。

1.3非空约束

设置非空约束的关键字为NOT NULL，作用是规定字段的值不能为空，用户在向数据表中插入数据时，如果设置非空约束的字段没有指定值，系统就会报错。
语法形式如下：

col_name data_type NOT NULL

1.4唯一性约束

设置唯一性约束的关键字为UNIQUE，作用是规定该字段的值不能重复
定义字段时设置唯一性约束：

col_name data_type UNIQUE

定义所有字段后设置唯一性约束：

UNIQUE KEY(col_name)

1.5默认约束

设置默认约束的关键字为DEFAULT，当该字段为空时，自动填入默认值，语法形式如下：

col_name data_type DEFAULT value

1.6外键约束

设置外键约束的主要作用是保证数据的完整性。 外键不是所属数据表的主键，但会对应着另外一张数据表的主键。
如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表
设置外键约束的语法形式如下：

FOREIGN KEY(child_col_name) REFERENCES parent_table_name(parent_col_name)
[ON DELETE reference_option]
[ON UPDATE reference_option]

child_col_name表示从表中需要设置外键约束的字段名，parent_table_name表示主表名，parent_col_name表示主表中主键的字段名。
注意：

• 设置外键约束的字段参照的一定是另一个表的主键字段
• 设置外键约束的字段和关联的主键必须具有相同的数据类型

级联外键的作用：

当主表的关联字段发生变更时，从表自动做相应的改变。

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

3. NO ACTION: InnoDB拒绝删除或者更新父表。

4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

判断以上三个表每一个表中有哪些约束，并建立这三个表

CREATE TABLE student(
sid INT(8)  PRIMARY KEY auto_increment COMMENT '学号',#主键约束
sname VARCHAR(10)  COMMENT '姓名',
sex ENUM('男','女') COMMENT '性别'
);
INSERT INTO student VALUES(202002，'张三');
DROP TABLE student;
CREATE TABLE course(
cid VARCHAR(4) COMMENT '课程号',
cname VARCHAR(20) COMMENT '课程'
);
CREATE TABLE grade(
sid INT(8) COMMENT '班级号',
cid CHAR(4)  COMMENT '班级',
grade INT(8) COMMENT '分数',
PRIMARY key(sid,cid)# 表级主键约束
);

二 、数据表的修改，删除和查看：

2.1数据表查看

方法一：show create table 表名
方法二：desc 表名

2.数据表修改
MySQL提供了ALTER关键字来修改表结构

2.2 修改表名

ALTER TABLE old_table_name RENAME new_table_name;

[例] 修改student的表名为stu

Alter table student rename stu;

2.3 修改字段数据类型/部分约束(可增加或删除 非空/自增约束）

修改字段数据类型的关键字为MODIFY，语法形式如下：

ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名 新类型 [字段属性];

[例] 将stu表中的sid数据类型改为bigint，并且该列非空

Alter table stu modify sid bigint not null;

2.4修改字段名

修改数据表字段名称的关键字为CHANGE，语法形式如下：

ALTER TABLE 数据表名 CHANGE [COLUMN] 旧字段名 新字段名 字段类型 [字段属性];

• “旧字段名”指的是字段修改前的名称。
• “新字段名”指的是字段修改后的名称。
• “数据类型”表示新字段名的数据类型，不能为空，即使与旧字段的数据类型相同，也必须重新设置。

[例] 将course表中的cname该名为cn，且数据类型转化为varchar(10)

Alter table course change cname cn varchar(10);

2.5 添加字段 （可增加 主键/外键/唯一约束)

常见添加字段的操作一般分为三种情况：在表的最后一列，在表的第一列或者在指定列之后。
在表的最后一列添加字段

# 语法格式1：
新增一个字段，并可指定其位置
ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [FIRST | AFTER 字段名]; 

# 语法格式2：同时新增多个字段 ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2, ...);

• 在不指定位置的情况下，新增的字段默认添加到表的最后。
• 新增多个字段时不能指定字段的位置

[例]在course表增加一个credit（学分)列，放在课程号和课程名之间

alter table course add credit int after cid; 

[例]增加course表的主键。

alter table course add primary key(cid);

[例]三个表中存在几个外键关系？每个外键应当建立在哪个表之上？请在已有的表上增加外键。

2个外键关系，
primary key(sid,cid),
foreign key (sid) REFERENCES student(sid),
foreign key(cid) REFERENCES course(cid) ON UPDATE CASCADE

将普通外键修改为级联修改级联更新外键：

foreign key (sid) REFERENCES student(sid) ON UPDATE CASCADE

2.6 删除字段 (删除主键，外键，唯一)

语法：

ALTER TABLE 数据表名 DROP [COLUMN] 字段名;

[例] 删除course表中的cid，可以成功吗？

不可以，ALTER TABLE course DROP cid;

[例]删除grade表上的外键

alter table grade
drop foreign key grade_ibfk_2

---

实验

1在上次sell数据库建立好的四个表的基础上，判断每个表或者表和表之间存在的约束，请把约束列填写完整，并通过编写sql代码将约束添加到表结构中。（利用alter去增加约束，不要重复建表）
顾客表：

属性名	数据类型
Cid	int(10)
Cname	varchar(10)
Cphone	char(11)
VIP	ENUM(‘S’,’R’,’J’,’O’)

含义	存储示例
顾客号	例如：20200101（固定长度）
顾客名	例如：张三
电话	例如：18482112222
会员	目前会员只有”食品会员“，”日用品会员“，”家居会员"三种，可以同时拥有0-3个会员身份

Alter table customer modify cid int primary key auto_increment ;
Alter table customer modify cname varchar(10) not null;
Alter table customer modify cphone int(11) unique;

产品表：

属性名	数据类型
Pid	varchar(5)
Pname	varchar(10)
Pprice	float(4,2)

含义	存储示例
产品号	例如：K1001，长度固定
产品名	例如果冻， 最多不超过10个字
单价	例：20.12（精确到小数点后2位）

Alter table product modify pid char(5) primary key;
Alter table product modify pname varchar(10) unique;
Alter table product modify pprice decimal(4,2) not null;

订单表：

属性名	数据类型
Oid	char(12)
Cid	char
Cnumber	int
Cotime	DATETIME
Cdtime	DATE

含义	存储示例
订单编号	例如：D20200728001，定长
顾客号	例如：20200101（固定长度）
订货数量	整数
订货日期	自动获取下单时间，格式为年月日时分秒
交货日期	记录交货时间，格式为年月日

Alter table sales modify oid char(10) primary key;
Alter table sales add foreign key(cid）references customer（cid）
Alter table sales modify cnumber int default’0’；

订单明细表：

属性名	数据类型
Oid	char
Pid	varchar
Dnumber	int
Dmoney	float(100,2)

含义	存储示例
订单编号	例如：D20200728001，定长
产品号	例如：K1001，长度固定
订货数量	整数
金额	精确到小数点后2位

Alter table detailed  add primary key(oid,pid)
Alter table detailed  add foreign key(oid）references sales（oid）
Alter table detailed  add foreign key(Pid）references product （Pid)