DDL语句--改动表
改动表是指改动数据库中已经存在的表的定义。改动表比又一次定义表简单。不须要又一次载入数据。也不会影响正在进行的服务。
MySQL中通过ALTER TABLE语句来改动表。改动表包含改动表名。改动字段数据类型,改动字段名。添加字段,删除字段,改动字段的排列位置。更改默认存储引擎和删除表的外键约束等。
OK,这篇博客会具体的整理到上面的几种改动表的方式。如今我们開始。
- 1,改动表名
表名能够在一个数据库中唯一的确定一张表。数据库系统通过表名来区分不同的表。
注意的是。数据库中的表是唯一的,数据库中不可能存在两张同样的表。
MySQL中通过sql语句ALTER TABLE来实现表名的改动,语法形式例如以下:
ALTER TABLE 旧表名 RENAME [TO] 新表名;当中。‘旧表名’參数表示改动前的表名,‘新表名’參数表示改动后的新表名,TO參数是可选參数,这个属性是否在语句中出现不会影响语句的运行。rename的意思就是重命名,所以这个挺好记的。
OK,如今我们来实际操作下数据库。我们如今数据库中创建一张表,建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们改动下上面user表的表名:
ALTER TABLE linkinframe.user RENAME linkinframe.usr;OK,改动成功,我们查看下新的表。表结构和之前的表结构一致,里面的数据与之前的数据也一致。
- 2,改动字段的数据类型
字段的数据类型包含整数型。浮点数型,字符串型。二进制类型,日期和时间类型等。
数据类型决定了数据的存储格式,约束条件和有效范围。表中的每一个字段都有数据类型。
MySQL中,ALTER TABLE语句也能够改动字段的数据类型。基本的语法例如以下:
ALTER TABLE 表名 MODIFY 属性名 数据类型;
当中,‘表名’參数指所要改动的表的名称,‘属性名’參数指须要改动的字段的名称。‘数据类型’參数值改动后的新的数据类型。
OK。如今我们来实际操作下MySQL。首先我们建一张user表,建表语句例如以下:CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们改动name字段的长度为varhcar(10)。运行例如以下的sql语句来改动:
ALTER TABLE linkinframe.`user` MODIFY name varchar(10) NOT NULL;
改动后我们查看下数据库如今表结构,成功改动了name属性的长度了。
- 3,改动字段名
字段名是一张表中唯一的一个字段。数据库系统通过字段名来区分表中的不同字段。MySQL中,ALTER TABLE语句也能够改动表的字段名,基本的语法例如以下:
ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;当中,‘旧属性名’參数指改动前的字段名。‘新属性名’參数指改动后的字段名,‘新数据类型’參数指改动后的数据类型。如不须要改动,则将新数据类型设置成与原来的一样。
详细有2种情况:
- 1),仅仅改动字段名
使用ALTER TABLE 语句直接改动字段名。不改变字段的数据类型。建表语句:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们改动name字段为username字段,运行例如以下语句来改动:
ALTER TABLE linkinframe.`user` CHANGE name username varchar(10) NOT NULL;
改动后的表结构例如以下:
当然当中的数据也没有变化:
- 2)。改动字段名和字段数据类型
使用ALTER TABLE语句直接改动字段名和该字段的数据类型。建表语句:CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们改动name字段为username字段。然后我们将name原来的长度改动成为10,运行例如以下语句来改动:
ALTER TABLE linkinframe.`user` CHANGE name username varchar(10) NOT NULL;
改动后的表结构例如以下:
- 3),总结
MODIFY和CHANGE都能够改变字段的数据类型。不同的是:1,CHANGE能够在改变字段数据类型的同一时候,改变字段名。MODIFY仅仅能用来改变字段的数据类型,不能改动字段名。
2,CHANGE假设不改变字段名,仅仅改动字段类型。CHAGE后面必须跟两个相同的字段名。
3,养成良好的习惯,假设是指改动字段的数据结构就使用MODIFY,假设要改动字段名+数据结构就使用CHANGE。
4,在改动表的字段过程中,假设表中已经有记录。改动数据类型时应该特别小心。由于,改动数据类型时可能会影响表中的数据。
5,特别注意的是。字符类型的字段最好不要改成整数类型,浮点数类型。
- 4,添加字段
在创建表时,表中的字段就已经定义完毕。假设要添加新的字段。能够通过ALTER TABLE语句进行添加,在MySQL中,ALTER TBALE语句添加字段的基本的语法例如以下:ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
当中,‘属性名1’參数指须要添加的字段的名称,‘数据类型’參数指新添加字段的数据类型。‘完整性约束条件’是可选參数。用来设置新增字段的完整性约束条件;‘FIRST’參数也是可选參数,其作用是将新增字段设置为表的第一个字段,‘AFTER 属性名
2’參数也是可选參数,其作用是将新增字段加入到‘属性名2’所指的字段后。假设运行的sql语句中没有‘FIRST’,‘AFTER 属性名2’參数指定新增字段的位置,新增的字段默觉得表的最后一个字段。
详细有例如以下4种情况:
- 1),添加无完整性约束条件的字段
一个完整的字段包含字段名。数据类型和完整性约束条件,添加字段一般都是包含上述内容。当然,依据实际情况,一些字段也能够不用完整性约束条件进行约束。
如今我们来实际操作下,数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中加入一个phone字段:
ALTER TABLE linkinframe.`user` ADD phone varchar(11);
OK,加入字段成功。这里没有设置‘FRIST’和‘AFTER 属性名2’參数来指定插入位置,所以新增字段默觉得表的最后一个字段。
- 2)。添加有完整性约束条件的字段
添加字段时能够设置该字段的完整性约束条件。如设置字段是否为空。是否为主键。是否为外键,默认值和是否为自增类型等约束条件。如今我们来实际操作下,数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中加入一个phone字段:
ALTER TABLE linkinframe.`user` ADD phone varchar(11) NOT NULL;
OK。加入字段成功,这里没有设置‘FRIST’和‘AFTER 属性名2’參数来指定插入位置,所以新增字段默觉得表的最后一个字段。
- 3),表的第一个位置添加字段
默认情况下。新增字段为表的最后一个字段。假设加上FIRST參数,则能够将新增字段设置为表的第一个字段。如今我们来实际操作下。数据库建表语句例如以下:
CREATE TABLE linkinframe.`user` ( `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中加入一个id字段。然后设置id为user表的主键,并且放到表中的第一个位置上。
ALTER TABLE linkinframe.`user` ADD `id` int(11) primary KEY AUTO_INCREMENT first;
OK,加入成功,并且成功的放到了表中的第一个字段上面了。
- 4),表的指定位置之后添加字段
在添加字段时,因为特殊原因须要在表的指定位置添加字段。假设加上‘AFTER 属性名2’參数,那么新增的字段插入在‘属性名2’后面。如今我们来实际操作下。数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中name字段之后加入一个phone字段:
ALTER TABLE linkinframe.`user` ADD phone varchar(11) after name;
OK,加入字段成功,表中phone字段成功的放到了name字段之后。
- 总结:
1,添加字段时,假设能够加上完整性约束条件,一定要加上。这样能够保证此字段的安全性。甚至能够提高整个表的数据的安全性。2,对于一个数据库来说。字段的排列顺序对表不会有什么影响。
可是对于创建表的人来说。将有某种直接或者间接关系的字段放在一起,会更加好的理解这个表的结构,这事实上也是一种习惯。我们以后也要尽量将有关系的字段放在一起。方便我们查看。
- 5,删除字段
删除字段是指删除已经定义好的表中的某个字段,在表创建完毕之后,假设发现某个字段须要删除。能够採用将整个表都删除,然后又一次创建一张表的做法。这样做是能够达到目的的,但必定会影响到表中的数据。并且操作比較麻烦。MySQL中,ALTER TABLE语句也能够删除表中的字段,语法例如以下:
ALTER TABLE 表名 DROP 属性名;当中,‘属性名’參数指须要从表中删除的字段的名称。
如今我们来实际操作下,数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中删除掉name字段:
ALTER TABLE linkinframe.`user` DROP name;
OK,成功删除了。
- 6。改动字段的排列位置
创建表的时候,字段在表中的排列位置都已经确定了。假设要改变字段在表中的排列位置。也能够通过ALTER TABLE来处理。MySQL中。改动字段排列位置的ALTER TABLE语句的基本的语法例如以下:
ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2;当中,‘属性名1’參数指须要改动位置的字段的名称,‘数据类型’參数指‘属性名1’的数据类型,‘FIRST’參数指定位置为表的第一个位置。‘AFTER 属性名2’參数指定‘属性名1’插入到‘属性名2’之后。
详细的分2种情况:
- 1)。字段改动到第一个位置
FIRST參数能够指定字段为表的第一个字段。
如今我们来实际操作下。数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中的name字段改动到数据库的第一个位置上去:
ALTER TABLE linkinframe.`user` modify name varchar(5) first;
OK,显示name字段已经到了user表的第一个位置上了。
- 2),字段改动到指定位置
‘AFTER’參数能够将字段排在表中指定的字段之后。
如今我们来实际操作下,数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中的name字段改动到age之后去:
ALTER TABLE linkinframe.`user` modify name varchar(5) after age;
OK,显示name字段已经到了user表中age之后了。
- 总结:
改动字段的排列位置事实上也就是改动表的字段,这里因为没有改动字段名。所以我们能够使用MODIFY来操作字段的位置。当然我们也能够使用CHANGE来操作字段的位置。仅仅只是这个时候属性名要写2遍而已。
比方说我如今不自己写sql,然后使用MySQL的client来调整下表中的2字段的位置使用的就是CHANGE。
ALTER TABLE `linkinframe`.`user` CHANGE COLUMN `name` `name` VARCHAR(5) CHARACTER SET 'utf8' NULL DEFAULT NULL AFTER `id`;
- 7。更改表的存储引擎
MySQL存储引擎是指MySQL数据库中表的存储类型。
MySQL存储引擎包含InnoDB,MyISAM。Memory等。不同的表类型有着不同的优缺点,在前面的博客中我也已经整理到了。
在创建表时。存储引擎就已经设定好了。假设要改变,能够通过又一次创建一张表来实现。这么做是能够达到目的的,但必定会影响到表中的数据。并且操作比較麻烦。
MySQL中,ALTER TABLE语句也能够更改表的存储引擎的类型,其基本的语法例如以下:
ALTER TABLE 表名 ENGINE=存储引擎名当中。‘存储引擎名’參数指设置的新的存储引擎的名称。
如今我们来实际操作下,数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们user表中的存储引擎设置为MyISAM:
ALTER TABLE linkinframe.`user` ENGINE = MyISAM;
OK。如今我们SHOW CREATE TABLE看下user的表结构,如今user表的存储引擎成功的变成了MyISAM。
注意:使用Alter语句能够改变表的存储引擎,这能够避免又一次创建表。可是,假设表中已经有非常多的数据。改变存储引擎可能会造成一些意料之外的影响。假设一个表中已经存在了非常多数据,最好不要轻易更改其存储引擎。
- 8,删除表的外键约束
外键是一个特殊字段,其将某一表与其父表建立关联关系。
在创建表的时。外键约束就已经设定好了。
因为特殊须要,与父表之间的关联关系须要去除,要求删除外键约束。
MySQL中。ALTER TABLE语句也能够删除表的外键约束。其基本的语法例如以下:
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名:当中。‘外键别名’參数指创建表时设置的外键的代号。
如今我们来实际操作下,数据库建表语句例如以下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8_bin NOT NULL, `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL, `age` int(11) DEFAULT '25', PRIMARY KEY (`id`), UNIQUE KEY `age_UNIQUE` (`card_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; CREATE TABLE `address` ( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `name` varchar(45) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id_idx` (`user_id`), CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;如今我们删除掉从表address的外键关联。运行例如以下sql:
ALTER TABLE linkinframe.address DROP foreign key user_id;
OK,查询结果显示从表address的外键已经不存在了。
posted on 2017-08-05 11:26 gavanwanggw 阅读(190) 评论(0) 编辑 收藏 举报