mysql表的操作

一、学习任务1：表的基本概念

        在MySQL数据库中，表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。表的操作包含创建表、查看表、删除表和修改表，这些操作是数据库对象的表管理中最基本、最重要的操作。

        表是包含数据库中所有数据的数据库对象。数据在表中的组织方式与在电子表格中相似，都是按行和列的格式组织的。其中每一行代表一条唯一的记录，每一列代表记录中的一个字段。如下图所示。

•列（Cloumns)：也称属性列，在具体创建表时，必须指定列的名字和数据类型。

•索引（Indexes)：是指根据指定的数据库表列建立起来的顺序，提供了快速访问数据的途径且可监督表的数据，使其索引所指向的列中的数据不重复，后面章节将详细介绍。

•触发器（Triggers）：是指用户定义的事务命令的集合，当对一个表中的数据进行插入、更新或刪除时这组命令就会自动执行，可以用来确保数据的完整性和安全性，后面章节将详细介绍。

二、学习任务2：创建表

        表的操作包括创建表、查看表、删除表和修改表。本节将详细介绍如何创建表。所谓创建表就是在数据库中建立新表，该操作是进行其他表操作的基础。

创建表的语法形式

        在MySQL数据库管理系统中创建表通过SQL语句CREATE TABLE来实

现，其语法形式如下：

                上述语句中table_name参数表示所要创建的表名字，表名紧跟在关键字CREATETABLE后面。表的具体内容定义在圆括号之中，各列之间用逗号分隔。其中“属性名”参数表示表字段的名称， “数据类型”参数指定字段的数据类型，例如，如果列中存储的为数字，则相应的数据类型为数值类型。在具体创建数据库时，表名不能与已经存在的表对象重名，其命名规则与数据库名命名（标识符）规则一致。

        注意：在创建表之前，需要选择数据库。如果没有选择数据库，创建表时就会出现“No database selected”错误。在创建表时，如果数据库中已经存在该表，则会出现“Table‘t_dept’already exists”错误。

三、学习任务3：查看表结构

        当创建完表后，经常需要查看表信息。那么如何在MySQL软件中查看表信息呢？查看帮助文档，可以发现许多实现查看表信息的语句，例如describe、show create table等，为了便于讲解，本节将通过各种语句来查看数据库company中名为t_dept的表对象信息。

3.1 describe语句查看表定义

        创建完表，如果需要查看一下表的定义，可以通过执行SQL语句describe来实现，其语法形式如下：

        DESCRIBE table_name;

        上述语句中table_name参数表示所要查看表对象定义信息的名字。

3.2 show create table语句查看表定义

        创建完表，如果需要查看表结构的详细定义，可以通过执行SQL语句SHOW CREATE TABLE 来实现，其语法形式如下：

        SHOW CREATE TABLE table_name;

        上述语句中table_name参数表示所要查看表定义的名字。

        注意：在显示表详细定义信息时，可以使用“;”、“\g”和“\G”符号来结束。为了让结果显示的更加美观，便于用户查看，最好使用“\G”符号来结束。

四、学习任务4：删除表

        表的操作包括创建表、查看表、刪除表和修改表。所谓删除表就是指删除数据库中已经存在的表。在具体刪除表时，会直接删除表中所保存的所有数据，因此在删除表时应该非常小心。

         删除表的语法形式

        在MySQL数据库管理系统中删除表通过SQL语句DROP TABLE来实现， 其语法形式如下：

DROP TABLE table_name;

       上述语句中table_name参数表示所要删除的表名字,所要删除的表必须是数据库中已经存在的表。

五、学习任务5：修改表

        对于已经创建好的表，当使用一段时间后，就需要进行一些结构上的修改，即表的修改操作。该操作 的解决方案是先将表删除，然后再按照新的表定义重建表。但是这种解决方案有问题，即如果表中己经存在大量数据，那么重建表后还需要做许多额外工作，例如数据的重载等。为了解决上述问题，MySQl数据库提供“ALTER TABLE”语句实现修改表结构。

5.1 修改表名

       在数据库中，可以通过表名来区分不同的表，因为表名在数据库中是唯一的，不能重复。查看帮助文档发现，在MySQL数据库管理系统中修改表名可以通过SQL语句ALTER TABLE来实现， 其语法形式如下：

        ALTER TABLE old_table_name RENAME [TO] new_table_name；

        在上述语句中，old_table_name参数表示所要修改表的名字，new_table_name参数为修改后的新名字。所要操作的表对象必须在数据库中己经存在。

5.2 增加字段

        对于表，可以看成是由列和行来构成的，其中“列”经常被称为字段。根据创建表的语法可以发现，字段是由字段名和数据类型进行定义的。本节将详细介绍如何为一个已经存在的表増加字段。

1、在表的最后一个位置增加字段

        在MySQL数据库管理系统中增加字段通过SQL语句ALTER TABLE来实现，其语法形式如下：

ALTER TABLE table_name ADD属性名 属性类型；

        在上述语句中，参数table_name表示所要修改表的名字，“属性名”参数为所要增加字段的名称，“属性类型”为所要增加字段能存储数据的数据类型。如果该语句执行成功，字段将增加到所有字段的最后一个位罝。

2、在表的第一个位置增加字段

        通过SQL语句ALTER TABLE来实现增加字段时，如果不想让所增加的字段在所有字段的最后一个位置，可以通过FIRST关键字使所增加的字段在表中所有字段的第一个位置，具体的SQL语 句语法形式如下：

        ALTER TABLE table_name ADD属性名 属性类型FIRST;

        在上述语句中，多了一个关键字FIRST，表示所有增加的字段在所有字段之前，即在表中第一个位置。

3、在表的指定字段之后增加字段

        通过SQL语句ALTER TABLE来实现增加字段时，除了可以在表的第一个位置或最后一个位置增加字段外，还可以通过关键字AFTER在指定的字段之后添加字段，具体的SQL语句语法形式如下:

        ALTER TABLE table_name ADD属性 属性类型 AFTER 属性名；

        在上述语句中，多了一个关键字AFTER，表示所有增加的字段在该关键字所指定字段之后。

5.3 删除字段

        对于表，既然可以在修改表时进行字段的增加操作，那么也可以在修改表时进行字段的删除。所谓删除字段是指删除已经在表中定义好的某个字段。即在创建好的表格中，发现某个字段需要删除，在MySQL数据库管理系统中删除字段通过SQL语句ALTER TABLE来实现，其语法形式如下：

        ALTER TABLE table_name DROP属性；

上述语句中table_name参数表示所要修改表的名字，“属性名”参数表示所要删除的字段名。

5.4 修改字段

        根据创建表的语法可以发现，字段是由字段名和数据类型来进行定义的，如果要实现修改字段，除了可以修改字段名外，还可以实现修改字段所能存储的数据类型。由于一个表中会拥有许多字段， 因此还可以实现修改字段的顺序。本节将详细介绍如何修改字段。

1、修改字段的数据类型

        在MySQL数据库管理系统中修改字段类型通过SQL语句ALTER TABLE 来实现，其语法形式如下：

        ALTER TABLE table_name MODIFY属性名 数据类型；

        上述语句中table_name参数表示所要修改表的名字，“属性名”参数为所要修改的字段名，“数 据类型”为修改后的数据类型。

2、修改字段的名字

        在MySQL数据库管理系统中修改字段名称通过SQL语句ALTER TABLE 来实现，其语法形式如下：

        ALTER TABLE table_name CHANGE旧属性名 新属性 旧数据类型；

        上述语句中table_name参数表示所要修改表的名字，“旧属性名”参数表示所要修改的字段名， “新属性名”参数表示所要修改成的字段名。

3、同时修改字段的名字和属性

        通过关键字MODIFY可以修改字段的数据类型，通过关键字CHANGE可以修改字段的名字， 那么有没有一个关键字能够同时修改字段的名字和数据类型呢？在MySQL 数据库管理系统中同时修改字段名字和数据类型，通过SQL语句ALTER TABLE来实现，其语法形式如下：

        ALTER TABLE table_name CHANGE旧属性名 新属性名 新数据类型；

        上述语句中“新属性名”参数表示所要修改成的字段名，“新数据类型”为所要修改成的数据类型。

4、修改字段的顺序

        在MySQL数据库管理系统中修改字段名称顺序，通过SQL语句ALTER TABLE来实现，其语法形式如下：

ALTER TABLE table_name MODIFY属性名1数据类型 FIRST|AFTER 属性名2；

        上述语句中，table_name参数表示所要修改表的名字，“属性名1”参数表示所要调整顺序的字段名，“FIRST”参数表示将字段调整到表的第一个位置，“AFTER属性名2”参数表示将字段调整到属性名2字段位罝之后。

注意：属性名1和属性名2必须是表中已经存在的字段名。

六、学习任务6：操作表的约束

        对于已经创建好的表，虽然字段的数据类型决定了所能存储的数据类型，但是表中所存储的数据是否合法并没有进行检查。在具体使用MySQL软件时，如果想针对表中的数据做一些完整性检查操作，可以通过表的约束来完成。本节将详细介绍关于表的约束。

6.1 MySQL支持的完整性约束

所谓完整性是指数据的准确性和一致性，而完整性检查就是指检查数据的准确性和一致性。 MySQL数据库管理系统提供了一致机制来检查数据库表中的数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性，这种机制就是约束。

可以发现MySQL数据库管理系统除了支持标准SQL的完整性约束外，还进行了相应扩展。扩展后增加AUTO_INCREMENT约束。

表 所示为MySQL软件所支持的完整性约束。

表 完整性约束

完整性约束关键字	含义
Not null	约束字段的值不能为空
Default	设置字段的默认值
Unique key(uk)	约束字段的值是唯一
Primary key（pk）	约束字段为表的主键，可以作为该表记录的唯一标识
Auto_increment	约束字段的值自动增加
Foreign key(fk)	约束字段为表的外键

        表显示的完整性约束中，MySQL数据库管理系统不支持check约束，即可以使用check约束但是却没有任何效果。根据约束数据列限制，约束可分为：单列约束，即每个约束只约束一列数据；多列约束，即每个约束可以约束多列数据。

6.2 设置非空约束（not null，nk）

        当数据库表中的某个字段上的内容不希望设置为null时，则可以使用NK约束进行设置。即NK约束在创建数据库表时为某些字段加上“NOT NULL”约束条件，保证所有记录中该字段都有值。如果用户插入的记录中，该字段为空值，则数据库管理系统会报错。

        设置表中某字段的NK约束非常简单， MySQL数据库管理系统中通过SQL语句NOT NULL来实现，其语法形式如下：

        CFEATE TABLE table_name (属性名 数据类型NOT NULL,……);

        在上述语句中，属性名参数表示所要设置非空约束的字段名字。

6.3 设置字段的默认值（default）

        当为数据库表中插入一条新记录时，如果没有为某个字段赋值，那么数据库系统会自动为这个字段插入默认值。为了达到这种效果，可以通过SQL语句关键字default来设置。

        设置数据库表中某字段的默认值非常简单，在MySQL数据库管理系统中通过SQL语句 DEFAULT来实现，其语法形式如下：

CREATE TABLE table_name (属性名 数据类型DEFAULT默认值，……);

在上述语句中，属性名参数表示所要设置默认值的字段名字，“默认值”为该字段的默认值。

6.4 设置唯一约束（unique，uk）

当数据库表中的某个字段上的内容不允许重复时，则可以使用UK约束进行设置。即UK约束在创建数据库表时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果用户插入的记录中，该字段上的值与其他记录里该字段上的值重复，则数据库管理系统会报错。

设置表中某字段的UK约束非常简单，在MySQL数据库管理系统中通过SQL语句unique来实现，其语法形式如下：

CREATE TABLE table_name (属性名 数据类型UNIQUE,……);

上述语句中,属性名参数表示所要设置唯一约束的字段名字。

注意：在为约束设置标识符时，推荐使用“约束缩写_字段名”，因此设置为uk_dname。

6.5 设置主键约束（primary key，pk）

当想用数据库表中的某个字段来唯一标识所有记录时，则可以使用PK约束进行设置。即PK约束在创建数据库表时为某些字段加上“PRIMARY KEY”约束条件，则该字段可以唯一地标示所有记录。

在数据库表中之所以设置主键，是为了便于数据库管理系统快速地查找到表中的记录。在具体设置主键约束时，必须要满足主键字段的值是唯一、非空的。由于主键可以是单一字段，也可以是多个字段，因此分为单字段主键和多字段主键。

1、单字段主键

设置表中某字段的PK约束非常简单，在MySQL数据库管理系统中通过SQL语句PRIMARY KEY来实现，其语法形式如下：

CREATE TABLE table_name （属性名 数据类型 PRIMARY KEY,……）;

上述语句中，属性名参数表示所要设置pk约束的字段名字。

注意：在为约束设置标识符时，推荐使用“约束缩写_字段名”，因此设置主健名为pk_dname等.

2、多字段主键

当主键由多个字段组合而成时，则需要通过SQL语句CONSTRAINT来实现，其语法形式如下：

CREATE TABLE table_name (属性 数据类型，……【CONSTRAINT 约束名】PRIMARY KEY(属性名1，属性名2……);

在上述语句中，在字段定义完之后统一设置主键，PRIMARY KEY关键字括号中的字段可以有多个，需要通过逗号分割，用来实现设置多字段主键。

6.6 设置字段值自动增加（auto_increment）

auto_increment唯一扩展的完整性约束，当为数据库表中插入新记录时，宇段上的值会自动生成唯一的ID。在具体设罝auto_increment约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID,所以该字段也经常会设罝成PK主键。

设置表中某字段值的自动增加约束非常简单，在MySQL数据库管理系统中通过SQL语句auto_increment来实现，其语法形式如下：

CREATE TABLE table_name (属性名 数据类型AUTO_INCREMENT,……);

在上述语句中，属性名参数表示所要设置自动增加约束的字段名字，默认情况下，该字段的值是从1开始增加，每增加一条记录，记录中该字段的值就会在前一条记录的基础上加1。

6.7 设置外键约束（foreign key，fk）

前面介绍的完整性约束都是在单表中进行设罝，而本节所要介绍的外键约束则保证多个表（通常为两个表）之间的参照完整性，即构建于两个表的两个字段之间的参照关系。

设置外键约束的两个表之间会具有父子关系，即子表中某个字段的取值范围由父表所决定。例如，表示一种部门和雇员关系，即每个部门有多个雇员。首先应该有两个表：部门表和雇员表，然后雇员表中有一个表示部门编号的字段deptno，其依赖于部门表的主键，这样字段deptno就是雇员表的外键，通过该字段部门表和雇员表建立了关系。

在具体设罝FK约束时，设置fk约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为NULL。

设置表中某字段的FK约束非常简单，在MySQL数据库管理系统中通过SQL语句FOREIGN KEY来实现，其语法形式如下：

CREATE TABLE table_name (属性名 数据类型,属性名 数据类型……【CONSTRAINT 外键约束名】 FOREIGN KEY(属性名1 ) REFERENCES 表名 （属性名2）)；

在上述语句中，“外键约束名”用来标识约束名，“属性名1”参数是子表中设置外键的字段名， “属性2”参数是父表中设罝主键约束的字段名。