MySQL学习之创建、修改、删除表
短信预约 -IT技能 免费直播动态提醒
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 exist4.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)
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
