首页 > 资讯 > 数据库 >MySQL学习之创建、修改、删除表

581

分享到

MySQL学习之创建、修改、删除表

2024-04-02 19:04:59 581人浏览八月长安

摘要

1、如何创建表结构？语法格式如下： CREATE TABLE [IF NOT EXISTS

1、如何创建表结构？

语法格式如下：

   CREATE TABLE [IF NOT EXISTS] 表名 (
          字段1 数据类型 [完整性约束条件],
          字段2 数据类型 [完整性约束条件],
          ....
          字段n 数据类型 [完整性约束条件]
         );

说明：[IF NOT EXISTS] 为可选字段，加上此选项后如果创建的数据表已经存在，不会报错，只会出现警告信息，而不会报错，如果不加此选项会出现报错信息。创建的表的名字不能为sql语言的关键字如create、updata和order等，每个字段的结束后用逗号隔开，最后一个字段不需要加逗号。

完整性约束条件也是可选字段，是对字段进行限制。Mysql中常用的完整性约束条件如下表所示：

表1 mysql中的完整性约束条件

约束条件	说明
PRIMARY KEY	表示该字段为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，与之联系的是父表的主键
NOT NULL	标识该字段不能为空
UNIQUE	标识该字段是唯一的
AUTO_INCREMENT	标识该属性的值自动增加，MySQL的特色
DEFAULT	可以为字段设置默认值

下面分小节说明一下，这几个完整性约束条件的测试。

1.1 主键的测试

设置主键的目的是可以帮助MySQL以最快的速度查找表中的某一条信息。

特点：主键必须唯一；

任意两条表中记录主键的字段不能相同；

主键必须是非空值；

主键可以是单一字段，也可以是多个字段的组合。

单个字段的主键测试

语法规则：字段名数据类型 PRIMARY KEY

    实例：创建一张student表,设置stu_id为主键，实现代码如下：
    
      CREATE TABLE IF NOT EXISTS student(
        stu_id INT PRIMARY KEY ,
        stu_name VARCHAR(20),
        stu_age INT,
        stu_sex ENUM('男','女','保密')
    );

多个主键的测试

语法规则：PRIMARY KEY(字段1,字段2,...字段n)

    实例：创建一张teacher表，设置主键为tea_id和course_id，实现代码如下：
    
    CREATE TABLE IF NOT EXISTS teacher(
        tea_id TINYINT,
        name VARCHAR(20),
        course_id INT,
        PRIMARY KEY(tea_id,course_id)
        );

1.2 外键的测试

语法规则如下：

CONSTRaiNT 外键别名 FOREIGN KEY(字段1.1 ,字段1.2,字段1.3, ...)

REFERENCES 表名（字段2.1 ,字段2.2，...）

说明：‘外键别名’是外键的代号；字段1中的参数列表是在子表中设置的外键，‘表名’参数是父表的名称；‘字段2’参数列表是父表的主键。

实例：新建一张grade表，设置stu_id为外键，与student表中的主键stu_id相关联。

SQL代码如下：

CREATE TABLE IF NOT EXISTS grade(
        id INT PRIMARY KEY,
        stu_id INT,
        stu_name VARCHAR(20),
        `数学` FLOAT,
        `英语` FLOAT,
        `语文` FLOAT,
        CONSTRAINT grade_fk FOREIGN KEY (stu_id)
            REFERENCES student(stu_id)        
);

注意：创建的子表的外键必须是父表的主键。并且两者的数据类型必须是一致的，如果不一致，则不能创建成功。

1.3 非空约束的测试

非空约束就是要求表中设置的字段的值不能为空值，如果用户插入的字段值为空值时，此时数据库就会出现报错信息，不能正确的插入数据，‘NOT NULL’ 约束经常与“DEFAULT”约束条件联合使用

基本语法规则：

字段数据类型 NOT NULL

实例：重新创建一张student2表，设置sex字段为非空。

 CREATE TABLE IF NOT EXISTS student2(
    id TINYINT PRIMARY KEY,
    name VARCHAR(20),
    sex ENUM('男','女','保密') NOT NULL,
    age TINYINT
);

1.4 测试唯一性约束

唯一性约束就是指所有记录中的该字段的值不能够重复出现，例如，每个人的×××号都是不同的，可以将×××字段设置为唯一性，当插入的数据出现两个相同的×××号时，数据库就会出现告警信息。简单的说，唯一性约束条件要求所有记录该字段的值不能重复出现。

基本语法如下：

字段数据类型 UNIQUE

实例：创建一张student3表，设置id字段的属性为唯一性，SQL 代码如下：

CREATE TABLE IF NOT EXISTS student3(
        id INT NOT NULL UNIQUE,
        name VARCHAR(20),
        age TINYINT
);

1.5 测试自增长

AUTO_INCREMENT 是MySQL数据库中一种特殊的约束条件，为表中插入的新纪录自动生成一个唯一的ID。且一张表中只能有一个字段使用AUTO_INCREMENT约束，该字段必须为主键的一部分，被约束的字段的类型可以为任何整数类型（INT TINYINT SMALLINT MEDIUMINT BIGINT）默认情况下该字段从1开始自增长。

基本语法：字段名数据类型 AUTO_INCREMENT

实例：新建一张表student4,设置字段id为自增长属性。

CREATE TABLE IF NOT EXISTS student4(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(30) ,
        age TINYINT
);

1.6 测试默认值

在创建表时可以为表中的字段添加默认值，如果插入数据时没有为这个字段插入数据，则这个字段就会为自动添加一个默认值。通过DEFAULT关键字来设置默认值的。

基本语法：字段数据类型 DEFAULT 默认值

实例：创建一张表student5,位表中字段age和sex设置默认值，SQL代码如下：

CREATE TABLE IF NOT EXISTS student5(
        id INT PRIMARY KEY ,
        name VARCHAR(20) NOT NULL,
        age TINYINT DEFAULT '20',
        sex ENUM('男','女','保密') DEFAULT '男'
  );

2、如何查询表结构？

数据库定义完成后，可以通过查询语句来查看已经定义好的数据库，经常使用的查询语句为

DESCRIBE和SHOW CREATE TABLE通过这两个语句可以查看表的字段名、数据类型和完整性约束条件。

2.1 测试DESCRIBE语句

语法结构： DESCRIBE 表名;

实例：查询一下刚刚建立好的student表结构。

 Thu Dec 08 00:58:40 2016localhosttest_db>DESCRIBE student;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(20)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

DESCRIBE 可以简写为DESC,代码运行如下:

Thu Dec 08 00:59:02 2016localhosttest_db>DESC student;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(20)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

从查询结果中，可以看到字段名（Field）、数据类型(Type)、是否为空（null）、是否为主外键（key）、默认值（Default）、额外信息（Extra）。

2.2 测试 SHOW CREATE TABLE 语句

通过SHOW CREATE TABLE语句可以查看更加详细的信息，除了能够查询字段名、数据类型、完整性约束条件信息外，还可以查看表的存储引擎和使用的字符编码。

基本语法： SHOW CREATE TABLE 表名;

实例：要查看刚刚建好的student1的表结构，SQL语句显示如下：

Thu Dec 08 01:12:27 2016localhosttest_db>SHOW CREATE TABLE student2 \G
*************************** 1. row ***************************
       Table: student2
Create Table: CREATE TABLE `student2` (
  `id` tinyint(4) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` enum('男','女','保密') NOT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注：后面加\G结尾可以使得显示的结果更加直观。

3、如何修改表？

数据库中的表建立好以后，可以通过SQL语句修改已经建立好的数据表。MySQL通过ALTER TABLE 语句修改表中的元素。例如，可以修改表名、修改字段的数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认的存储引擎和删除表的外键约束等。

3.1 修改表名

通过表名在一个数据库下是唯一确定的，不可能存在两个相同的数据表名。有时候为了实际需要，我们需要更改数据表的名字，重新建立一张一样的表费时费力，这时我们可以通过SQL语句ALTER TABLE修改表的名字。

基本语法：ALTER TABLE 旧的表名 RENAME [TO |AS] 新表名;

实例：我们要把表student表名改为stu,把student2表名改为stu1,把student3表名改为stu2。SQL代码如下：

--先查看一下库中都有那些表
Thu Dec 08 01:11:32 2016localhosttest_db>SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| grade             |
| student           |
| student2          |
| student3          |
| student4          |
| student5          |
| teacher           |
+-------------------+
7 rows in set (0.00 sec)
-- 执行SQL 代码
ALTER TABLE student RENAME TO stu;
ALTER TABLE student2 RENAME AS stu1;
ALTER TABLE student3 RENAME stu2;
-- 通过SHOW TABLES语句查看是否更改成功

Thu Dec 08 01:33:59 2016localhosttest_db>SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| grade             |
| stu               |
| stu1              |
| stu2              |
| student4          |
| student5          |
| teacher           |
+-------------------+
7 rows in set (0.00 sec)

说明：[TO|AS]为可选参数，可加可不加。

3.2 修改字段的数据类型

用ALTER TABLE 语句可以修改字段的数据类型，基本语法如下：

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

实例：将stu数据表中的name 字段的数据类型修改为VARCHAR(30),SQL代码如下：

--先查看一下stu_name 现有的数据类型
Thu Dec 08 01:34:09 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(20)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

-- 执行SQL代码，将stu_name的数据类型修改为VARCHAR(30)

Thu Dec 08 01:44:53 2016localhosttest_db>ALTER TABLE stu MODIFY stu_name VARCHAR(30);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询修改后的stu_name的数据类型

Thu Dec 08 01:45:55 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(30)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

3.3 修改表中的字段名

有时候我们需要修改表的字段名，通过ALTER TABLE 语句可以修改表的字段名。

基本语法如下：

ALTER TABLE 表名 CHANGE 旧字段名新字段名新数据属性;

实例1：修改表stu中的字段'stu_name'改为name,不改变他的数据类型。SQL语句实现如下：

-- 先查看一下stu的表结构

Thu Dec 08 20:54:26 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(30)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


-- 执行ALTER TABLE 语句修改字段的名字
Thu Dec 08 20:54:40 2016localhosttest_db>ALTER TABLE stu CHANGE stu_name name VARCHAR(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 20:56:56 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| stu_id  | int(11)                | NO   | PRI | NULL    |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| stu_age | int(11)                | YES  |     | NULL    |       |
| stu_sex | enum('男','女','保密') | YES  |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

实例2：修改字段名同时修改字段的数据类型，将stu表中的字段'stu_id'修改为'id'，数据类型变为TINYINT,约束条件仍为主键。将'stu_age'改为‘age’,数据类型为TINYINT,完整性约束条件设为‘非空’，

字段‘stu_sex’改为sex 数据类型不变，完整性约束条件改为‘DEFAULT '保密'’。执行代码如下：

Thu Dec 08 21:32:00 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| stu_id  | int(11)                | NO   | PRI | 0       |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| stu_age | int(11)                | YES  |     | NULL    |       |
| stu_sex | enum('男','女','保密') | YES  |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Thu Dec 08 21:32:12 2016localhosttest_db>ALTER TABLE stu CHANGE stu_id id TINYINT primary key;
ERROR 1068 (42000): Multiple primary key defined
-- 需要注意的是，修改主键名时，后面不需要添加 PRIMARY KEY 关键字，否则会出现以上报错信息。

Thu Dec 08 21:33:03 2016localhosttest_db>ALTER TABLE stu CHANGE stu_id id TINYINT ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 21:34:35 2016localhosttest_db>ALTER TABLE stu CHANGE stu_age age TINYINT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 21:35:31 2016localhosttest_db>ALTER TABLE stu CHANGE stu_sex sex ENUM ('男','女','保密') DEFAULT '保密';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 21:37:29 2016localhosttest_db>DESC stu;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | tinyint(4)             | NO   | PRI | 0       |       |
| name  | varchar(30)            | YES  |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | NULL    |       |
| sex   | enum('男','女','保密') | YES  |     | 保密    |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
-- 最后，查询一下表结构，可以看到表中的字段已经修改为我们需要的字段类型了。

3.4 为已经建好的表增加字段

对于已经建好的数据表来说，如果我们想对表进行修改，为表中再添加一个新的字段，使用ALTER TABLE 语句可以为表中增加一个新的字段。它的基本语法如下：

ALTER TABLE 表名 ADD 新的字段名数据类型 [完整性约束条件] [FIRST|AFTER 已有的字段名];

实例1：为stu表增加一个新的字段 Tel 不需要添加任何的完整性约束条件。SQL代码如下：

-- 先查看一下表结构

Thu Dec 08 21:37:29 2016localhosttest_db>DESC stu;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | tinyint(4)             | NO   | PRI | 0       |       |
| name  | varchar(30)            | YES  |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | NULL    |       |
| sex   | enum('男','女','保密') | YES  |     | 保密    |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 为表增加一个新的字段tel
Thu Dec 08 21:37:39 2016localhosttest_db>ALTER TABLE stu ADD tel CHAR(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询一下表结构，可以看到表stu增加的一个新的字段tel

Thu Dec 08 21:55:42 2016localhosttest_db>DESC stu;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | tinyint(4)             | NO   | PRI | 0       |       |
| name  | varchar(30)            | YES  |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | NULL    |       |
| sex   | enum('男','女','保密') | YES  |     | 保密    |       |
| tel   | char(11)               | YES  |     | NULL    |       |
+-------+------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

实例2：为stu表添加一个字段address ，设置完整性约束条件为‘非空’。SQL代码如下：

Thu Dec 08 21:55:49 2016localhosttest_db>ALTER TABLE stu ADD address VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:00:47 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| id      | tinyint(4)             | NO   | PRI | 0       |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| age     | tinyint(4)             | YES  |     | NULL    |       |
| sex     | enum('男','女','保密') | YES  |     | 保密    |       |
| tel     | char(11)               | YES  |     | NULL    |       |
| address | varchar(20)            | NO   |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

测试3:在表的第一个位置增加字段，在stu表中增加num字段，其SQL代码如下：

Thu Dec 08 22:00:53 2016localhosttest_db>ALTER TABLE stu ADD num TINYINT FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:28:24 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| num     | tinyint(4)             | YES  |     | NULL    |       |
| id      | tinyint(4)             | NO   | PRI | 0       |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| age     | tinyint(4)             | YES  |     | NULL    |       |
| sex     | enum('男','女','保密') | YES  |     | 保密    |       |
| tel     | char(11)               | YES  |     | NULL    |       |
| address | varchar(20)            | NO   |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

测试4：在指定位置之后添加一个字段。

-- 在stu表的tel 字段后面添加一个新的字段‘birthday’
Thu Dec 08 22:28:38 2016localhosttest_db>ALTER TABLE stu ADD birthday DATE AFTER tel;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:34:52 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| num      | tinyint(4)             | YES  |     | NULL    |       |
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| name     | varchar(30)            | YES  |     | NULL    |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | 保密    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

3.5 删除字段

删除字段是指我们可以删除表中已经定义好的字段。使用ALTER TABLE 可以实现将表中的某个定义好的字段进行删除操作。其基本语法如下：

ALTER TABLE 表名 DROP 字段名;

例如，表stu中的字段num我们不在需要，现在需要对它进行删除操作，SQL代码如下：

Thu Dec 08 22:42:31 2016localhosttest_db>ALTER TABLE stu DROP num;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:42:53 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| name     | varchar(30)            | YES  |     | NULL    |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | 保密    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

3.6 修改字段的排列位置

使用ALTER TABLE 语句也可以修改表中字段的排列位置，基本语法如下：

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;

说明：字段名1 是需要改变位置的字段名称；“数据类型”是字段1的数据类型；FIRST 参数是将字段1改变到第一个位置；“AFTER 字段名2”参数是将字段1加入到字段2的后面。

--测试将stu表中的name字段插入到第一个位置，将字段‘sex’修改到字段‘tel’的后面
ALTER TABLE stu MODIFY name VARCHAR(30) FIRST;
ALTER TABLE stu MODIFY sex ENUM('男','女','保密')  AFTER tel;

Thu Dec 08 22:43:08 2016localhosttest_db>ALTER TABLE stu MODIFY name VARCHAR(30) FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:56:00 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| name     | varchar(30)            | YES  |     | NULL    |       |
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | 保密    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
Thu Dec 08 22:56:12 2016localhosttest_db>ALTER TABLE stu MODIFY sex ENUM('男','女','保密')  AFTER tel;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:56:26 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| name     | varchar(30)            | YES  |     | NULL    |       |
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

3.7 更改表的存储引擎

MySQL中常用的存储引擎有三个:InnoDB、MyISAM、MEMORY等。通过ALTER TABLE 语句可以更改表的存储引擎类型。其语法结构如下：

ALTER TABLE 表名 ENGINE=存储引擎名;

例如，将stu表的存储引擎改为 MyISAM，SQL 代码如下：

Thu Dec 08 22:56:34 2016localhosttest_db>ALTER TABLE stu ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 23:03:35 2016localhosttest_db>SHOW CREATE TABLE stu \G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `name` varchar(30) DEFAULT NULL,
  `id` tinyint(4) NOT NULL DEFAULT '0',
  `age` tinyint(4) DEFAULT NULL,
  `tel` char(11) DEFAULT NULL,
  `sex` enum('男','女','保密') DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

注意：如果表中已经有很多数据，不建议更改表的存储引擎，如果更改了存储引擎可能会发生一些意料之外的影响。

3.8 删除表的外键的约束

外键是一个特殊的字段，它将某一表与父表建立关联关系。在创建表的时候外键约束就已经设定好了，如果想要去除与父表之间的关联，可以用ALTER TABLE 语句删除外键的约束。基本语法如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

-- 创建表grade 设置stu_num为表student主键的外键
Thu Dec 08 23:36:16 2016localhosttest_db>CREATE TABLE IF NOT EXISTS grade(
    -> 	id INT PRIMARY KEY,
    ->         stu_num INT ,
    ->         english FLOAT,
    ->  	math   FLOAT,
    ->       CONSTRAINT g_fk FOREIGN KEY (stu_num)
    ->        REFERENCES student(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

-- 查看一下grade的表结构

Thu Dec 08 23:40:53 2016localhosttest_db>SHOW CREATE TABLE grade;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`),
  CONSTRAINT `g_fk` FOREIGN KEY (`stu_num`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 删除外键约束

Thu Dec 08 23:41:18 2016localhosttest_db>ALTER TABLE grade DROP FOREIGN KEY g_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 23:42:45 2016localhosttest_db>SHOW  CREATE TABLE grade;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.删除表

删除表时指删除已经存在的数据表。删除表同时也会删除表中的所有数据，因此，删除前应做好必要的准备。MySQL中使用 DROP TABLE 语句来删除表。对于一些表来说，创建表的时候存在着外键的约束，一些表成为了与之相联系的表的父表。要删除这些父表，情况就比较复杂了。

4.1 删除没有被关联的普通表

直接使用DROP TABLE语句删除没有被关联的普通表，使用语法如下：

DROP TABLE 表名;

例如，要删除student5表，SQL 语句如下：

-- 先查看一下student5的表结构
Thu Dec 08 23:22:02 2016localhosttest_db>DESC student5;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | int(11)                | NO   | PRI | NULL    |       |
| name  | varchar(20)            | NO   |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | 20      |       |
| sex   | enum('男','女','保密') | YES  |     | 男      |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

-- 执行删除代码

Thu Dec 08 23:23:46 2016localhosttest_db>DROP TABLE student5;
Query OK, 0 rows affected (0.00 sec)

Thu Dec 08 23:24:05 2016localhosttest_db>DESC student5;
ERROR 1146 (42S02): Table 'test_db.student5' doesn't exist

4.2 删除有外键关联的表

要想删除一个带有外键关联的表，首先，需要删除表的外键，然后才能删除表结构，SQL语句如下：

-- 先创建一个grade表，字段stu_num为表student主键的外键
Thu Dec 08 23:48:16 2016localhosttest_db>CREATE TABLE IF NOT EXISTS grade(
    -> 	id INT PRIMARY KEY,
    ->         stu_num INT ,
    ->         english FLOAT,
    ->  	math   FLOAT,
    ->       CONSTRAINT g_fk FOREIGN KEY (stu_num)
    ->        REFERENCES student(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 查看一下grade的表结构

Thu Dec 08 23:48:51 2016localhosttest_db>SHOW CREATE TABLE grade \G
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`),
  CONSTRAINT `g_fk` FOREIGN KEY (`stu_num`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 执行一下删除命令，删除表student，发现不能执行删除命令

Thu Dec 08 23:49:19 2016localhosttest_db>DROP TABLE student;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

-- 执行命令，删除一下表grade的外键约束

Thu Dec 08 23:49:39 2016localhosttest_db>ALTER TABLE grade DROP FOREIGN KEY g_fk;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 23:50:46 2016localhosttest_db>SHOW CREATE TABLE grade \G
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 再次执行一下删除命令，发现成功删除了student表

Thu Dec 08 23:50:54 2016localhosttest_db>DROP TABLE student;
Query OK, 0 rows affected (0.02 sec)
Thu Dec 08 23:51:13 2016localhosttest_db>SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| animalinfo        |
| grade             |
| stu               |
| teacher           |
+-------------------+
4 rows in set (0.00 sec)