MySQL学习之创建、修改、删除表
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 &nbs
网站名称:MySQL学习之创建、修改、删除表
浏览路径:http://pwwzsj.com/article/jpjojd.html