数据定义

数据定义

数据库可看作一个专门存储数据对象的容器,这里的数据对象包括表、视图、触发器、存储器过程等,其中表是最基本的数据对象。

定义数据库

创建数据库

创建数据库的基本语法格式是:

MySQL
1
2
3
create {datebase|schema} {if not exists} db_name
[[default] character set [=] charset_name]
[ [default] collate [=] collaction_name]

  • [ ]内为可选项;
  • |用于分隔花括号中的选择项,表示可任选其中一项来与花括号外的语法成分共同组成SQL语句命令;
  • db_name:数据库名,在文件系统中,MySQL的数据库存储区将以目录的方式表示MySQL数据库。因此命令中的数据库名字必须符合操纵系统中文件夹的命令规则,而在MySQL中是不区分大小写的。
  • if not exists:判断不存在的时候才会新建。
  • character set:用于指定数据库字符集,charset_name为字符集的名字。简体中文的字符集名称为gb2312
  • collate:用于指定字符集的校对规则,collaction_name为校对规则的名称。简体中文字符集的校对规则为gb2312_chinese_ci
  • default:指定默认的数据库字符集和字符集的校对规则。

例如,创建一个名为db_school的数据库,输入语句如下:

MySQL
1
2
3
create database db_school
default character set GB2312
default collate gb2312_chinese_ci;

上面的命令再次运行的时候是会报错的。MySQL不允许统一系统中存在相同名称的数据库,在上面的命令中加入if not exists可以避免报错。

MySQL
1
2
3
create database if not exists db_school
default character set GB2312
default collate gb2312_chinese_ci;

用户在使用create databasecreate schema创建数据库时必须获得相应的权限。

选择和查看数据库

选择数据库

使用use命令可以从一个数据库跳转到另一个数据库。在创建数据库之后,该数据库不会自动成为当前数据库,可使用use命令指定其为当前数据库。

MySQL
1
use db_school;

查看数据库

下面两个语句的作用是一样的,可以用来查看当前用户权限内所能看到的数据库名称。

MySQL
1
2
3
# 查看数据库
show databases;
show schemas;

系统数据库有下面四个:

  1. mysql:描述用户访问权限;
  2. information_schema:保存关于MySQL服务器所维护的所有其它数据库信息,如数据库名、数据库的表、表字段、表字段的数据类型与访问权限等;
  3. performance_schema:主要用于收集数据库服务性能参数;
  4. test:用户利用该数据库进行测试工作。

修改数据库

在MySQL中,数据库的默认字符集为latin1,默认校对规则为latin1_swedish_ci。使用alter databasealter schema语句可以修改。其语法格式为:

MySQL
1
2
3
alter {database | schema} [db_name]
[default] character set [=] charset_name
[default] collate [=] collaction_name;

  • 该命令可以用于修改数据库的全局特性,这些特性储存在数据库目录中的db.opt中;
  • 使用该命令时,用户必须具有对数据库进行修改的权限;
  • 数据库的名称可以被省略,表示修改当前数据库。
    MySQL
    1
    2
    3
    alter database db_school
    default character set gb2312
    default collate gb2312_chinese_ci;

删除数据库

基本语法格式为:

MySQL
1
drop {database | schema} [if exists] db_name;

  • 这个删除是永久性的;
  • 该命令需要相应的权限;
  • 但某个数据库被删除后,该数据库的用户权限不会自动删除,为了方便数据的维护,应手动删除它们;
  • 可以用if exists避免删除不存在的数据库是出现MySQL错误信息。
MySQL
1
2
drop database db_school;
drop database if exists db_school;

定义表

MySQL中常用的数据类型

MySQL的常用数据类型主要有数值型、日期和时间型、字符串型。

数值类型

  • bit[(M)]:位字段类型。M表示每个值的位数,范围位1~64。如果M被省略,默认为1。
  • tinyint[(M)] [unsigned] [zerofill]:很小的整数。带符号的范围为-128~127,不带符号的范围为0~255。
  • bool, boolean:是tinyint(1)的同义词。zero值被视为假,非zero值被视为真。
  • smallint[(M)] [unsigned] [zerofill]:小的整数。
  • mediumint[(M)] [unsigned] [zerofill]:中等大小的整数
  • int[(M)] [unsigned] [zerofill]:普通整数。
  • integer[(M)] [unsigned] [zerofill]:int的同义词。
  • bigint[(M)] [unsigned] [zerofill]:大整数。
  • double[(M)] [unsigned] [zerofill]:双精度浮点数,M是小数总位数,D是小数的后面的位数。如果M和D被省略,根据硬件运行的限制来保存值。双精度浮点数精确到大约15位小数。如果指定unsigned则不允许负值。
  • decimal[(M[, D])] [unsigned] [zerofill]:压缩的“严格定点数”。M位小数位数(精度)的总数,不包括负号和小数点。D是小数点(标度)后面的位数。decimal整数的最大位数M位65,支持的十进制的最大位数D是30。如果D被省略,默认为0。如果M被省略,默认为10。如果指定unsigned则不允许负值。
  • dec:decimal的同义词。

日期类型

  • date: 日期型,MySQL中以“YYYY-MM-DD”格式显示date的值。但是允许使用字符串或数字为date列分配值。
  • datetime:日期和时间的组合,MySQL中以“YYYY-MM-DD HH:MM:SS”的格式显示datetime的值,但是允许使用字符串或数字为datetime列分配值。
  • timestamp[(M)]:时间戳。范围是‘1970-01-01 00:00:00’到2037年。timestamp列用于insert 或update操纵时记录日期和时间。如果不分配一个值,表中的第一个timestamp列自动设置为当前的日期和时间。timestamp值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串,显示宽度固定为19个字符。如果想要获得数字值,应在timestamp列添加+0。
  • time:时间型。
  • year[(2|4)]:两位或者四位格式的年。默认为四位格式。在四位格式中,允许的值为1901~2155和0000 。在两位格式中,允许的值为70~69,表示1970~2069。

字符串类型

  • char[(M)]:固定长度的字符数据类型,用于保存以文本格式存储的信息。但保存时在右侧填充空格以达到指定的长度。M表示列长度,其范围为0~255。
  • varchar[(M)]:可变长度的字符串,用以保存以文本格式存储的信息。M表示最大列长度,其范围为0~65535。
  • tinytext:最大长度为255字符的text列。
  • text:最大长度为65535(216-1)字符的text列。

创建表

在MySQL中,创建数据表使用create table语句,其基本的语法格式如下:

MySQL
1
2
3
4
5
6
7
create table tbl_name
(
字段1 数据类型 [列级完整性约束条件] [默认值],
字段2 数据类型 [列级完整性约束条件] [默认值]
[, ······]
[, 表级完整性约束条件]
)[engine = 引擎类型];

例如,在已有的数据库db_school中构建学生表tbl_student,要求使用InnoDB引擎存储表数据。其结构如下:

含义 字段名 数据类型 宽度
学号 student_No 字符型 10
姓名 student_Name 字符型 20
性别 sex 字符型 2
出生日期 birthday 日期型
籍贯 native 字符型 20
民族 nation 字符型 30
所属班级 classNo 字符型 6
MySQL
1
2
3
4
5
6
7
8
9
10
11
use db_school;
create table tb_student
(
studentNo char(10) not null unique,
studentName char(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(30),
classNo char(6)
) engine = InnoDB;

使用create table语句创建表时的主要语法说明如下:

  1. 表名是必须的,必须符合命名规则。表的创建需要首先选定当前数据库,如果表名被指定为dbname.tblname的格式,则可以在特定的数据库创建表,而不论是否为当前数据库,都可以以这种方式创建数据表。如果使用加引号的识别名方式,则应该在数据库的名称和表的名称上都加上引号,例如:
    ‘dbname’.’tblname’是合法的,但是’dbname.tblname’是不合法的。
  2. 完整性约束条件。包括实体完整性约束(primary key、unique)、参照完整性约束(foreign key)和用户自定义约束(not null、default、check约束等)。但用户操作表中的数据是,DBMS会自动检查该操作是否遵循这些完整性约束。
  3. null和not null。允许null的列也允许插入记录时不给出该列的值。not null值的列则不接受该列没有值的记录。
  4. auto_increment。将字段设置为自增属性可以给记录一个唯一而又容易确定的ID号,该字段可以唯一标识表中的每条记录。只有整型列才能设置这个属性。当往一个被定义为auto_increment的列中插入null或者0时,该列的值会被value加1递增。其中value为当前表中最大的值。每个表只能定义一个auto_increment列,并且必须在该列上定义主键约束(primary key)或者候选键约束(unique)。

例如,如果在上面的例子中,定义学号自动递增,则学号的数据类型必须为整型,而且必须在学号上定义primary key 或unique:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
use db_school;
drop table if exists tbl_student;
create table if not exists tbl_student
(
studentNo int(10) not null unique auto_increment,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(30),
classNo char(6)
)engine = InnoDB;

当一个列被指定为auto_increment后,其值是可以被覆盖的,即可以简单地在数据插入语句(insert语句)中为该列指定一个值后。只要该值是唯一的,那么这个值将被用来替代系统中自动生成的值,并且后续的增量将基于该手工插入的值。

  1. default。默认值是指在向数据表中插入数据时,如果没有明确给出某个字段所对应的值,则DBMS此时允许为此字段指定一个值。例如,如果汉族同学比较多,那么在学生表tbl_student中可将该字段的默认值设置为“汉”。当插入一条新记录时,如果没有给该字段赋值,则系统会自动给该字段赋值为“汉”。
    MySQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create table tb_student2
    (
    studentNo char(10) not null unique,
    studentName varchar(20) not null,
    sex char(2),
    birthday date,
    native varchar(20),
    nation varchar(30) default '汉',
    classNo char(6)
    )engine = InnoDB;

需要注意的是,如果没有为字段指定默认值,MySQL会自动为其分配一个,即如果字段可以取null值,这默认值就为null,如果该字段被声明为not null,那么默认值就取决于字段的类型:

  • 对于没有声明auto_increment属性的数值型,默认值为0;对于声明了auto_increment的则默认值为顺序中的下一个值。
  • 对于除timestamp以外的日期和时间类型,默认值是该类型适当的“零”值;对于表中的第一个timestamp列,默认值是当前的日期和时间。
  1. 存储引擎类型。存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据是以表的形式存储的,所以存储引擎简而言之就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。
    使用show engines;可以查看系统支持的引擎类型和默认引擎,如下所示:

    其中,InnoDB是系统默认的存储引擎,支持可靠的事务处理,是事务型数据库的首选引擎。

查看表

查看表的名称

MySQL
1
show tables [{from | in} db_name];

例如查看数据库db_school中的所有表名:

MySQL
1
2
use db_school;
show tables;

查看非当前数据库中的数据库名称时:

MySQL
1
2
3
show tables from 基础数据;
-- 或
show tables in 基础数据;

查看表的基本结构

这里的基本结构包括字段名、字段的数据类型、字段值是否允许为null,是否有主键、是否有默认值等。

MySQL
1
show columns {from | in} tb_name [ {from | in} db_name];

或者

MySQL
1
{describe | desc} tb_name;

例如,查看db_school 中表tb_student2的结构:

MySQL
1
2
3
desc tb_student2;
-- 或
show columns from tb_student2 from db_school;

查看表的详细结构

也就是查看创建表是create table的语句,语法格式为:

MySQL
1
show create table tb_name;

例如查看tb_student2的详细结构:

MySQL
1
show create table tb_student2;

修改表

添加字段

语法格式为:

MySQL
1
alter table tb_name add [column] 新字段名 数据类型 [约束条件] [first | after 已有字段名]

例如,向数据库db_school的表tb_student2中添加一个int型的字段id,要求其不能为null,取值唯一,自动增加,并将该字段添加为第一个字段。

MySQL
1
2
use db_school;
alter table tb_student2 add id int not null unique auto_increment first;

再添加一个varchar(16)类型的字符串以描述学生所在的院系,要求设置默认值为信息学院,并将该字段添加到nation列之后。

MySQL
1
2
alter table tb_student2 add department varchar(16) default '信息学院' after nation;
desc tb_student2;

修改字段

alter table 语句提供了三个修改字段的子句,分别如下:

  • change [column]子句。该子句可以同时修改表中指定列的名称和数据类型。alter table 语句中可以同时添加多个change [columns]子句,只需要彼此之间使用逗号隔开。
  • alter [column]子句。该子句可以用来修改或删除表中指定列的默认值。
  • modify [column]子句。与change [column]子句不同,该子句只会修改指定列的数据类型,而不会干涉它的列名。另外,modify [column]子句还可以通过first 或after关键字修改指定列在表中的位置。
    上述三个子句的常用语法格式分别是:
    MySQL
    1
    2
    3
    alter table tb_name change [column] 原字段 新字段名 数据类型 [约束类型];
    alter table tb_name alter [column] 字段名 [set | drop] default;
    alter table tb_name modify [column] 字段名 数据类型 [约束条件][first|after 已有字段名];

例如,进行下面的操作:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
alter table db_school.tb_student2
change column birthday age tinyint null default 18;

alter table db_school.tb_student2
alter column department drop default;

alter table db_school.tb_student2
alter column department set default '经济学院';

alter table db_school.tb_student2
modify column department varchar(20) not null after studentName;

desc tb_student2;

删除字段

语法格式为:

MySQL
1
alter table tb_name drop [column] 字段名;

例如删除id:

MySQL
1
2
alter table db_school.tb_student2 drop column id;
desc db_school.tb_student2;

重命名表

  1. rename [to]子句

    MySQL
    1
    alter table db_school.tb_student2 rename to db_school.tb_student3;
  2. rename table

    MySQL
    1
    rename table db_school.tb_student3 to db_school.tb_student2;

删除表

语法格式为:

MySQL
1
drop table [if exists] 表1 [, 表2] ······;

语法说明如下:

  • drop table 命令可以同时删除多个表,表和表之间用逗号隔开即可;
  • if exists用于在删除表之前判断要删除的表是否存在。
    例如删除db_school数据库中的所有表:
    MySQL
    1
    drop table db_school.tb_student2, db_school.tb_student;

注意,在删除表的同时,表的定义和表中的所有的数据都不会被删除,所以使用该语句需要格外小心。另外,用户在该表上的权限并不会自动被删除。

数据的完整性约束

关系模型的完整性规则就是对关系的某种约束条件。对关系模型施加完整性约束,则是为了在数据库应用中保障数据的正确性和一致性,防止数据库中存在不符合语义的、不正确的数据,这也是数据库服务器的功能之一。关系模型中有三类完整性约束:实体完整性参照完整性用户定义的完整性。其中实体完整性和参照完整性是关系模型必须满足的完整性约束条件,被称为两个关系的不变性

实体完整性

实体完整性规则是指关系的主属性不能取空值,即主键和候选键在关系中所对应的属性不能取空值。MySQL中实体完整性就是通过主键约束和候选键约束来实现的。

主键约束

主键是表中某一列或某些列所构成的一个组合。其中,由多个列组合而成的主键也被称为复合主键。主键的值必须是唯一的,而且构成主键的每一列的值都不允许为空。在MySQL中,主键列必须遵循如下一些规则:

  1. 每个表中只能定义一个主键;
  2. 主键的值,也被称为键值,必须能够唯一标识表中的每一行记录,且不能为null。也就是说,表中的两条不同记录在主键上不能有相同的值。这就是唯一性原则
  3. 复合主键不能包含不必要的列。也就是说,但从一个复合主键中删除一列后,如果剩下的列仍能满足唯一性原则,那么这个复合主键就是不争气的,这是最小化原则
  4. 一个列名在复合主键的列表中只能出现一次。

主键可以在create tablealter table语句中使用关键字primary key来实现。其方式有两种:

  1. 一种是作为列级完整性约束,此时只需要在表中的某个字段的定义后加上关键字primary key
  2. 另外一种是作为表级完整性约束,需要在表中所有字段的定义后添加一条primary key语法格式的子句。

例1: 在数据库db_school中重新定义学生表,要求以列级完整性约束的方式定义主键。

MySQL
1
2
3
4
5
6
7
8
9
10
11
use db_school;
create table tb_student
(
studentNo char(10) primary key,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
)engine = InnoDB;

例2:同上,但是以表级完整性约束的方式定义主键:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
drop table db_school.tb_student;
use db_school;
create table tb_student
(
studentNo char(10),
studentName varchar(20),
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
primary key(studentNo)
)engine = InnoDB;

完整性约束的命名

与数据库中的表和视图一样,可以对完整性约束进行添加、删除和修改等操作。其中,为了删除和修改完整性约束,首先需要在定义约束的同时对其进行命名。命令完整性约束的方法是,在各种完整性约束的定义前加上关键字constraint 和该约束的名字,其语法格式是:

MySQL
1
2
3
4
5
constraint <symbol>
{primary key(主键字段列表)}
| unique(候选键字段列表)
| foreign key(外键字段列表) references tb_被参照关系(主键字段列表)
| check(约束条件表达式);

其中,symbol为指定的约束名字,在完整性约束说明的前面被指定,其在数据库里必须是唯一的。倘若没有明确给出约束的名字,这MySQL会自动为其创建一个约束名字。

例如,指定上面的表中的主键约束的名称为PK_student。

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use db_school;
drop table tb_student;
create table db_school.tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint PK_student primary key(studentNo)
) engine = InnoDB;
desc db_school.tb_student;
show columns from db_school.tb_students;

在定义完整性约束时,应当尽量地为其指定名字,以便在需要对完整性约束进行修改或删除操作时,可以更加容易地引用它们。需要注意的是,当前的MySQL版本只能给表级的完整性约束指定的名字,而无法给列级的完整性约束指定名字。因此表级完整性约束比列级完整性约束会更受欢迎。

候选键约束

与主键约束一样,候选键可以是表中的某一列,也可以是表中某些列所构成的一个组合。任何时候,候选键的值都必须是唯一的,且不能为null。候选键可以在create table或alter table语句中使用关键字unique来定义,其实现方法与主键约束相似,同业有列级或者表级完整性约束两种形式。

例如:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
use db_school;
create table tb_class
(
classNo char(6) primary key,
className varchar(20) not null unique,
department varchar(30) not null,
grade smallint,
classNum tinyint
)engine = InnoDB;

# 如果将候选键定义为表级
drop table tb_class;
create table db_school.tb_class
(
classNo char(6) primary key,
className varchar(20) not null,
department varchar(30) not null ,
grade smallint,
classNum tinyint,
constraint UQ_class unique(className)
)engine = InnoDB;

MySQL中primary key 与unique之间存在如下几点区别:

  • 一个表中只能创建一个primary key,但是可以创建好几个unique;
  • 定义为primary key的列不允许有空值,但是定义为unique的字段允许空值;
  • 定义为primary key约束时,系统会自动产生primary key索引,而定义unique时,系统自动产生unique索引。

定义参照完整性

在学校数据库的示例中,学生关系中的班级编号的取值需要参照班级关系中班级编号的取值。“班级编号”是班级关系的主键,在学生关系中,“班级编号”是外键
外键是一个表中一个或一组属性,它不是这个表的主键,但它对应另一个表的主键。外键的主要作用是保证数据引用的完整性,保持数据的一致性。定义外键后,不允许删除外键引用的另外一个表中具有关联关系的记录。外键所属的表被称作参照关系,相关联的主键所在的表被称作被被参照关系。
参照完整性规则定义的是外键和主键之间的引用规则,即外键的取值或者为空,或者等于被参照关系中某个主键的值。例如,学生关系中“班级编号”可以取如下两类值:

  • 空值,表示还没来急的给该学生分班;
  • 非空值,这是该值必须是班级关系中某个记录的班级编号字段值,表示该学生不可能被分陪到一个不存在的班级中。

在定义外键的时候,必须遵循下面的规则:

  1. 被参照表必须已经使用create table创建,或者必须是当前正在创建的表。若是最后一种情形,则被参照表与参照表是同一个表,这样的表被称为自参照表,这种结构被称为自参照完整性。
  2. 必须为被参照表定义主键或候选键。
  3. 必须在被参照表的后面指定列名或列名的组合,这个列或列组合必须是被参照表的主键或候选键。
  4. 尽管主键是不能包含空值的,但是允许外键中出现空值。这意味着,只要外键的某个非空值出现在出现在指定的主键中,这个外键的内容就是正确的。
  5. 外键对应列的数目必须和被参照表的主键对应的列的数目相同。
  6. 外键对应的列的数目必须和被参照表的主键对应的列的数据类型相同。

例如:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 以表级完整性定义外键
use db_school;
drop table db_school.tb_student;
create table if not exists db_school.tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6) references db_school.tb_class(classNo)
)engine = InnoDB;

# 以表级完整性约束定义外键
drop table db_school.tb_student;
create table db_school.tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint PK_student primary key(studentNo),
constraint FK_student foreign key(classNo) references db_school.tb_class(classNo)
)engine = InnoDB;

在表tb_student的classNo列上定义外键约束之后,只有当某班级没有学生时才可以删除这个班级信息。MySQL可以通过定义一个参照动作来修改这个规则,即定义外键时可以显式说明参照完整性约束的违约处理策略。

在给外键定义参照动作时,需要包括两部分:一是要指定参照动作适当的语句,即update和delete语句;二是需要指定采取的动作,即cascade、restrict、set null、no action和set default,其中,restrict为默认值。具体策略如下:

  • restrict:限制策略,即当要删除或修改被参照表中被参照列上的其在外键中出现的值时,系统拒绝对被参照表的删除或修改操作。
  • cascade:级联策略,即从被参照表中删除或修改记录时,自动删除或修改参照表中匹配的记录。
  • set null:置空策略,即当从被参照表中删除或修改记录的时候,设定参照表中与之对应的外键列的值为null。这个策略需要被参照表中的外键列没有声明限定词not null。
  • no action:表示不采取实施策略,即当一个相关的外键值在被参照表中时,删除或修改被参照表中键值的动作不被允许。该策略的动作语义同restrict。
  • set default:默认值策略,即当从被参照表删除或修改记录行,设置参照表中与之对应的外键列的值为默认值。这个策略要求已经为该列定义了默认值。

例如:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop table db_school.tb_student;
create table db_school.tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint PK_student primary key(studentNo),
constraint FK_student foreign key(classNo) references db_school.tb_class(classNo)
on update restrict
on delete cascade
)engine = InnoDB;

这里定义了两个参照动作,on update restricton delete cascadeon update restrict表示当某个班级里有学生时不允许修改班级表中的该班级的编号;on delete cascade表示当要删除班级表中的某个班级的编号时,如果该班级里有学生,就将相对应学生记录级联删除。

需要注意的是:

  1. 外键只能引用主键和候选键。也就是说,只有当被参照关系的某个列上定义了主键或候选键,DBMS才允许在参照关系的引用列上定义外键。
  2. 外键只可以用在使用存储引擎InnoDB创建的表中,其它的存储引擎不支持外键。

用户定义的完整性

用户定义的完整性规则反映了某一具体应用所涉及的数据必须满足的语义要求。

关系模型提供定义和检验这类完整性规则的机制,其目的是用统一的方式由系统来处理它们,不再由应用程序来完成这项工作。在实际系统中,这类完整性规则一般在建立数据库表的同时进行定义,应用编程人员不需要在做考虑。如果某些约束条件在定义表时没有建立,则应用编程人员应该在各个模块的具体编程中通过应用程序进行检查和控制。

MySQL支持集中用户自定义完整性约束,分别是非空检查约束、check约束和触发器。
其中触发器会在后面介绍,这里主要介绍前面两个约束。

  1. 设置非空约束
    非空约束指某个字段的值不允许为空。对于使用里非空约束的字段,如果用户在添加数据时没有为其指定值,数据库系统就会报错。
    在MySQL中,非空约束的定义可以使用create table或alter table语句,在某个列后面添加not null作为限定词,来约束该值不能取空。

  2. check约束
    与非空约束一样,checku 约束也是在创建表或者修改表的同时,根据用户的实际完整性需要来定义的。check约束需要指定限定条件,它可以分别定义为列级或表级完整性约束。列级check约束定义的是单个字段需要满足的要求,表级check约束可以定义表中多个字段之间应满足的条件。check约束常用的语法格式为:

    MySQL
    1
    check (expr)

例如,在已有的数据库db_school中定义课程表tb_course,要求自定义约束:每16个课时对应一个学分:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# check约束
use db_school;
create table tb_course
(
courseNo char(6),
courseName varchar(20) not null,
credit int not null,
courseHour int not null,
term char(2),
priorCourse char(6),
constraint PK_course primary key (courseNo),
constraint FK_course foreign key (priorCourse)
references tb_course(courseNo),
constraint CK_course check (credit = courseHour/16)
)engine = InnoDB;

再例如,在已有数据库db_school中定义成绩表tb_course,要求成绩取值范围为0~100。

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
use db_school;
create table tb_score
(
studentNo char(10),
courseNo char(6),
score float check(score >= 0 and score <- 100),
constraint PK_score primary key (studentNo, courseNo),
constraint FK_score1 foreign key (studentNo)
references db_school.tb_student(studentNo),
constraint FK_score2 foreign key (courseNo)
references db_school.tb_course(courseNo)
)engine = InnoDB;

更新表级完整性约束

删除约束

  1. 删除外键约束
    如果外键约束是使用constraint子句命名的表级完整性约束,其语法格式为:
    MySQL
    1
    alter table <表名> drop foreign key <外键约束名>;

但要删除无命名的外键约束时,可先使用show create table语句查看系统给外键约束指定的名称,然后再删除该约束名。

例如,在表tb_score的字段studentNo上定义一个无命名的外键约束,然后删除它:

MySQL
1
2
3
4
5
6
7
alter table tb_score
add foreign key (studentNo)
references db_school.tb_student(studentNo);
show create table tb_score;

alter table tb_score
drop foreign key tb_score_ibfk_2;

  1. 删除主键约束
    因为一个表只能定义一个主键,所以无论有没有给主键命名,均可以用drop primary key删除,其语法格式为:
    MySQL
    1
    alter table <表名> drop primary key;

例如:

MySQL
1
2
alter table db_school.tb_student
drop primary key;

  1. 删除候选键约束
    删除候选键约束时,MySQL实际删除的是唯一性索引,应使用drop index子句删除,如果没有给约束命令,MySQL会自动将字段名定义为索引名。其语法格式为:
    MySQL
    1
    alter table <表名> drop index {约束名 | 候选字段名}

例如,如果没有给候选键命名,使用drop index子句删除的是定义候选键的字段名:

MySQL
1
2
alter table tb_class
drop index className;

如果使用了constraint子句给候选键命名,使用drop index子句删除的是约束名:

MySQL
1
2
alter table db_school.tb_class
drop index UQ_class;

添加约束

  1. 添加主键约束:

    MySQL
    1
    2
    alter table db_school.tb_student
    add constraint PK_student primary key (studentNo);
  2. 添加外键约束:

    MySQL
    1
    2
    3
    4
    5
    alter table db_school.tb_score
    drop foreign key FK_score1;
    alter table db_school.tb_score
    add constraint FK_score1 foreign key (studentNo)
    references db_school.tb_student(studentNo);
  3. 添加候选键约束:

    MySQL
    1
    2
    alter table db_school.tb_class
    add constraint UQ_class unique key(className);

注意,完整性约束不能直接被修改,若要修改某个约束,实际上是用alter table语句先删除它再增加一个同名的新约束。

练习题

这里面的这个语句比较陌生:constraint CK_S check (CITY != 'London' or STATUS = 20)这句表示城市为“London”时其STATUS均为20。

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
drop database db_sp;
create database if not exists db_sp
default character set gb2312
default collate gb2312_chinese_ci;
use db_sp;
create table S
(
SNO char(10),
SNAME varchar(10) unique not null,
STATUS int,
CITY varchar(10),
constraint PK_S primary key (SNO),
constraint CK_S check (CITY != 'London' or STATUS = 20)
)engine = InnoDB;

create table P
(
PNO char(10),
PNAME varchar(10) not null,
COLOR varchar(10) check (COLOR in ('Red', 'Yellow', 'Green', 'Blue')),
WEIGHT float,
constraint PK_P primary key (PNO)
)engine = InnoDB;

create table SP
(
SNO char(10),
PNO char(10),
QTY int,
constraint PK_SP primary key (SNO, PNO),
constraint FK_SP1 foreign key (SNO)
references db_sp.S(SNO),
constraint FK_SP2 foreign key (PNO)
references db_sp.P(PNO)
)engine = InnoDB;

# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了607.9k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×