MySQL表的操作：创建(TEMPORARY表、分区表)、删除/清空、重命名、表结构修改

最新推荐文章于 2024-08-13 16:07:53 发布

董哥的黑板报

最新推荐文章于 2024-08-13 16:07:53 发布

阅读量1.8k

点赞数 2

分类专栏： MySQL 文章标签：数据表的创建表约束 create table drop table truncate

本文链接：https://blog.csdn.net/qq_41453285/article/details/87971552

版权

MySQL 专栏收录该内容

49 篇文章 8 订阅

订阅专栏

一、表的创建

格式如下：

create table <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ........
    [表级别约束条件]
)表选项;

相关注意事项：
- 表的名称不区分大小写。
- 在创建表时给表或字段指定字符集和校对规则，参阅：https://blog.csdn.net/qq_41453285/article/details/104055727

处理现有的表（if not exists）

在创建新表时，必须确保表名不存在，否则将出错。
为了防止意外覆盖已有的表，SQL要求手动删除该表，然后再重建，而不是简单地用创建表语句覆盖它。
如果你仅想在一个表不存在时创建它，那么可以在表名后加上“if not exists”，这样只有在表不存在的时候才创建。

索引

可以为表指定索引，方式有2种：
在创建表时定义索引。
一种做法是，先创建一个不带任何索引的表，然后再为它加上索引。
对于MylSAM表来说，如果你计划在开始对它査询之前先填入大量的数据，那么第二种做法通常会更好些。与“先把数据加载到一个不带任何索引的MylSAM表里，然后再去创建它的索引”这种做法相比，“在每次插入一个行时都进行一次索引”的做法则要慢得多。

二、表选项

如果要想更改某个表的存储特性，可以在CREATE TABLE语句中的右括号之后加上一个或多个表选项。
一个表选项是"ENGINE=engine_name"。它可以为表指定要用的存储引擎。例如，要创建 MEMORY表或MylSAM表，可以编写下面这样的语句：

CREATE TABLE mytbl(...) ENGINE=MEMORY;
CREATE TABLE mytbl(...) ENGINE=MyIASM;

存储引擎的名字不区分大小写。如果没有给出ENGINE选项，那么服务器将使用默认的存储引擎来创建表。内建的默认存储引擎是InnoDB，在后面"存储引擎配置"文章中会介绍服务器使用不同的默认引擎。
如果所给名字指定的那个存储引擎未启用，则会看到两条警告信息：

CREATE TABLE t(i INT) ENGINE=ARCHIVE;
SHOW WARNINGS;

如果想让某个表使用某个特定的存储引擎，那么一定要包含ENGINE表选项。由于默认的存储引擎可以更改，因此如果省略ENGINE选项，则可能无法获得你所期望的默认存储引擎。此外，一定要保证CREATE TABLE语句没有产生任何警告消息——它们经常是提示所指定的存储引擎不可用，并且提示使用了默认的存储引擎来代替。
如果想让MySQL在指定的存储引擎不可用时产生一个错误 (而不是使用默认的存储引擎来代替)，则需要启用SQL的NO_ENGINE_SUBSTITUTION模式。
如果想要确定某个表使用的是哪一种存储引擎，那么可以执行SHOW CREATE TABLE语句，并査看其输出内容里的ENGINE选项：

SHOW CREATE TABLE t1\G

存储引擎还可以在SHOW TABLE STATUS语句或INFORMATION_SCHEMA.TABLES表的输出内容里看到。
选项MAX_ROWS和AVG_ROW_LENGTH可用于调整MylSAM表的大小。默认情况下，MyISAM 表具有的大小与内部行指针长度一致，即允许表文件增长到256TB。如果指定了选项 MAX_ROWS和AVG_ROW_LENGTH，那么这就是在告知MylSAM：对于一个至少能够容纳 MAX_ROWS个行的表，应该使用与一个指针长度一样的大小。
如果想要修改某个已有表的存储特性，可以在ALTERTABLE语句里使用表选项。例如，想把 mytbl表的当前存储引擎更改为InnoDB，那么可以执行下面这条语句：

ALTER TABLE mytbl ENGINE=InnoDB;

三、TEMPORARY表

如果在表创建语句里加上TEMPORARY关键字，那么服务器将创建出一个临时表，它会在你与服务器的会话终止时自动消失：

CREATE TEMPORARY TABLE tbl_name ... ;

这么做的好处在于你不必惦记着调用DROP TABLE语句来删除那个表，并且即使你与服务器的会话意外终止，那个表也不会遗留在那里。例如，你在一个批处理文件里存储了一个复杂的査询命令，你通过mysql来运行它，并且决定不想等它执行完毕，这时你可以毫无顾虑地取消那个脚本，服务器会删除由该脚本创建的所有TEMPORARY表。
如果想要创建使用某种存储引擎的临时表，则可以为CREATE TEMPORARY TABLE语句加上ENGINE表选项。
尽管服务器会在客户端会话结束时自动删除TEMP0RARY表，但最好是能在用完它之后立即显式地删除它，这可以让服务器尽快释放与之有关的资源。如果与服务器之间的会话还需要持续一段时间，那么将不再需要的资源(尤其是那些临时性的MEMORY表)及时释放掉，是一个很好的习惯。
TEM0PARY表只对创建该表的客户端可见。因为每个客户端都只能看到它自己创建的表，所以不同的客户端可以创建具有相同名字的TEMPORARY表，同时不会发生任何冲突。

TEMPORARY表的名字可以与某个已有的永久表的相同。这不会有任何问题，那个已有的永久表也不会因此遭到损坏。事实上，对于创建TEMPORARY表的那个客户端来说，当有TEMPORARY表存在时，那个永久表会隐藏起来(即无法访问)。
假设，在sampdb数据库中创建了一个名为member的TEMPORARY表。这时，原有的那个member表将被隐藏起来，而对member表的引用也会转向这个新建的TEMPORARY表。如果调用DROP TABLE member语句，那么被删除掉的将会是那个TEMPORARY表，而原有的那个member表此时会“再次显现”。如果在没有删除TEMPORARY表的情况下便断开了与服务器的连接，那么服务器将会自动为你删除它。当你下次再连接时，原有的那个member表又会是可见的。(如果重新命名了TEMPORARY表，那么原有的那个被隐藏的表也会变得可见)。
这种隐藏名字的机制只对一层操作有效。也就是说，你无法创建两个同名的TEMPORARY表。

请在考虑是否需要使用TEMPORARY表时，注意以下几个因素：
- 如果客户端程序在与服务器的连接意外断开时自动重建连接，那么在你重新连接之后，上次创建的那个TEMPORARY表将不复存在。如果你之前在用TEMPORARY表来 “隐藏”某个与之同名的永久表，那么这个永久表现在会变成你所使用的那个表。例如，在重新连接后，在未做检测的情况下调用了DROP TABLE语句，这时将会导致那个永久表被删除。为避免这类问题，则需要使用DROP TEMPORARY TABLE语句来代替。
- 因为TEMPORARY表只在创建它们的会话里是可见的，所以它们与连接池机制(它不保证为你调用的每一条语句都使用相同的连接)一起使用时没什么用处。
- 如果使用连接池或永久性连接，那么当你的应用程序终止时，你与MySQL服务器之间的连接不一定会被关闭。那些机制可能会保持连接处于打开状态，以供其他客户端使用，这表示，当应用程序终止时，你不能假定那个TEMPORARY表会自动消失。

四、使用其他表或查询结果来创建表

有时候，为某个表创建一份副本会很有用。例如，你有一个数据文件，并且想用LOAD DATA语句把它添加到某个表里，但你对用来指定数据格式的选项没什么把握。万一这些选项设置不正确，那么你就会把一些乱七八糟的行插到那个原有的表里。如果有一份那个原有表的空副本 (emptycopy)，那么你就可以放心地指定不同的列和行分隔符，尝试LOAD DATA的各种选项，最终达到正确分隔输入行的目的。然后，再次运行LOAD DATA语句，将这个数据文件加载到原有的那个表里。
有时也会想把査询结果存储到某个表里，而不是把它们显示到显示器上。在存储了査询结果之后，你可以不用再运行原来那条査询语句，也能引用它们，甚至还可以对它们做进一步的分析。
MySQL提供了两条语句，可用于根据其他的表或根据査询结果创建新表。这两条语句各有各的优缺点：
- CREATE TABLE ... LIKE：会根据原有表创建一个新表，该表是原有表的一个空副本。它会把原有表的结构丝毫不差地复制过来，而且会保留各列的所有属性。索引结构也照样会被复制。不过，由于这个新表是空的，因此如果想把数据填充到里面，还需要另外一条语句(如 INSERT INTO ... SELECT)。请注意，CREATE TABLE ... LIKE不能根据原有表的列子集创建出新表，它也不能使用除原有表以外的任何其他表里的列。
- CREATE TABLE ... SELECT：可以根据任意一条SELECT语句的査询结果创建一个新表。默认情况下，这条语句不会复制所有的列属性，如AUTO_INCREMENT列就不会被复制。根据查询数据来自动创建表，并把数据填入其中，这种做法也不会把原有表里任何索引复制过去，因为结果集不会自己索引。另一方面，CREATE TABLE...SELECT只用一条语句即可完成创建新表和填充新表的操作。它也可以用原有表的某个子集来创建新表，并且可以包括来自其他表的列，或者以表达式结果形式创建出来的列。
如果要使用CREATE TABLE ... LIKE来创建某个已有表的空副本，可以编写如下语句：

CREATE TABLE new_tbl_name LIKE tbl_name;

如果要创建某个表的空副本，并根据原有表的数据来填充它，那么可以先调用CREATE TABLE... LIKE，再调用INSERT INTO ... SELECT：

CREATE TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name select * from tbl_name;

如果要把某个表创建为其自身的一个临时副本，则需要加上关键字TEMPORARY：

CREATE TEMPORARY TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name select * from tbl_name;

想要尝试某些会修改表内容的语句，而又不想改变原有表的内容时，便可以利用与原有表同名的TEMPORARY表。如果要使用那些事先编写好的使用了原有表名的脚本，那么你不必为了使用另外的表而去编辑它们。只要在脚本的开头加上CREATE TEMPORARY TABLE语句和INSERT语句即可。该脚本将创建一个临时副本，并在该副本上执行各种操作，服务器会在脚本结束时自动删除这个临时副本。(不过，请一定要对上面提到的自动重连问题多加注意)。
如果只想把原有表里的部分行插到新表中，则可以增加WHERE子句来标识要选取的那些行。下面的语句会创建一个名为student_f的新表，并且它只会包含student表里的女生行：

CREATE TABLE student_f LIKE student;
INSERT INTO student_f SELECT * FROM student WHERE sex='f';

如果不太关心新表是否完全保留了原有表里的列定义，那么CREATE TABLE ... SELECT有时要比CREATE TABLE ... LIKE更易使用，因为它可以在一条语句里创建并填充新表：

CREATE TABLE student_f SELECT * FROM student WHERE sex='f';

CREATE TABLE ... SELECT也可以创建这样的新表，它并不完全包含已有表的所有列。你可以随时根据需要用它来快速创建一个新表，用于保存任意SELECT査询的结果。这让我们可以非常简便快速地创建一个新表，并把我们感兴趣的数据填充到它里面，以便后面的语句使用。不过，如果不够小心的话，这个新表可能会包含一些奇怪的列名。当你通过把选择数据放入其中的方式来创建表时，新表的列会根据你所选择的那些列来命名。
如果某个列是以表达式的计算结果生成的，那么该列的名字将是这个表达式的文本表示，从而会使创建出的表包含一个不同寻常的列名：
CREATE TABLE mytbl SELECT PI() * 2;
SELECT * FROM mytbl;
引用该列名，下面两种方式都可以，尽量使用带有引号的：
SELECT `PI() * 2` FROM mytbl;
为避免这个问题，就需要使用别名来提供一个便于使用的列名：
DROP TABLE mytbl;
CREATE TABLE mytbl SELECT PI() * 2 AS mycol;
SELECT mycol FROM mytbl;
如果从具有相同列名的不同表里进行査询，则会遇到一个难题。假设表t1和t2都有一个列为c，并且你想要根据这两个表里的所有行的组合创建表。由于这时是在试图创建一个具有两个列名都为c的表，所以下面的语句将会以失败告终：
CREATE TABLE t3 SELECT * FROM t1 INNER JOIN t2;
解决这个问题的办法是，提供必要的别名，让每个列在新表里都各有一个唯一的名字：
CREATE TABLE t3 SELECT t1.c, t2.c AS c2 FROM t1 INNER JOIN t2;

如前面所提到的， CREATE TABLE ... SELECT的一点不足在于：它不会把原有数据的所有特征全部复制到新表的结构里去。例如，通过把査询数据放入其中的方式来创建表，就不会把原有表里的索引复制过去，而且还可能会丢失列属性。可以保留的属性包括有：该列是NULL的还是NOT NULL的、字符集和排序规则、默认值，以及列注释。
在某些场合，你可以采用在语句的SELECT部分使用CAST()函数的方式，在新表里强制使用某些特定的属性。下面的CREATE TABLE ... SELECT语句，将强制把SELECT子句生成的各个列分别视为INT UNSIGNED、TIME和DECIMAL(10,5)。结果与使用DESCRIBE语句来验证时看到的内容一样：

CREATE TABLE mytbl SELECT 
CAST(1 AS UNSIGNED) AS i,
CAST(CURTIME() AS TIME) AS t,
CAST(PI() AS DECIMAL(10,5)) AS d;

DESCRIBE mytbl;

允许强制转换的类型包括：BINARY(二进制串)、CHAR、DATE、DATETIME、TIME、SIGNED、 SIGNED INTEGER、UNSIGNED、UNSIGNED INTEGER和DECIMAL。
也可以在CRAETE TABLE部分提供显式的列定义，以便将它们用在SELECT部分检索的列里。这两个部分里的列名是相匹配的(与位置无关)，因此必须在SELECT部分提供别名，才能让它们正确地匹配：

CREATE TABLE mytbl (i INT UNSIGNED, t TIME, d DECIMAL(10,5)) 
SELECT 1 AS i, CAST(CURTIME() AS TIME) AS t, CAST(PI() AS DECIMAL(10,5)) AS d;

DESCRIBE mytbl;

这种提供显式列定义的技术，可以让你实现许多操作，其中包括创建具有特定精度和小数字列、创建不同于结果集中最长值的宽度的字符列等。此外，请注意在这个例子里有几个列的Null和Default属性，与前面几个例子里的情况有所不同。如有必要，你可以在CREATE TABLE部分为这些属性提供显式的定义。

五、使用分区表

MySQL支持表分区，从而可以让表的内容分散存储在不同的物理存储位置。在对表存储进行分区后，得到分区表，使用分区表有很多好处，其中两个好处如下：
- 表存储可以分布在多个设备上，这样可以通过I/O并行机制来缩短访问时间。
- 优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区。
如果要创建分区表，可以像往常一样，在CREATE TABLE语句里提供一系列的列和索引。此外，还要指定PARTITION BY子句(它会定义一个可以把行分配到各个分区的分区函数)，以及其他一些与分区有关的选项。
分区函数可以根据范围、值列表或散列值来分配各行：
- 根据范围来分区。适用这种方式的情形是，行包含的是一些可划分为多个离散区间的值域，如日期、收入水平或重量。
- 根据列表来分区。适用这种方式的情形是，每个分区都分别对应于某些明确的列表值，如邮政编码表、电话号码区号、按地理区域划分出的各实体的编号。
- 根据散列值来分区。适用这种方式的情形是，根据从行键计算出的散列值，把行分布到各个分区。你可以自行提供一个散列函数；也可以告知MySQL要使用到的有哪些列，然后它会基于那些列使用内建的散列函数计算出散列值。
分区函数必须具有确定性，这样，在分配行时，才会把相同的输入值分配到同一个分区。有些函数不适合这个规则，如RAND()和NOW()。

演示案例

假设你想要创建一个表，用于存储一些简单的由日期和描述性字符串构成的条目，并且假设已有了累计多年的条目需要加载到这个表里。对于每一个包含有日期的数据条目，使用范围分区方式是最适合的。
为实现按年将各行分配到某个给定的分区，可以使用相应日期值里的“年”：
CREATE TABLE log_partition
(
  dt DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
)
PARTITION BY RANGE(YEAR(dt))
(
  PARTITION p0 VALUES LESS THAN (2010),
  PARTITION p1 VALUES LESS THAN (2011),
  PARTITION p2 VALUES LESS THAN (2012),
  PARTITION p3 VALUES LESS THAN (2013),
  PARTITION pmax VALUES LESS THAN MAXVALUE
)
2014年及以后的行都将被分配到MAXVALUE区。当到了2014年时，可以再把这个分区进行划分，从而让所有2014年的行都有了它们自己的分区；而对于2015年及以后的行，则会继续分到MAXVALUE区：
ALTER TABLE log_partition REORGANIZE PARTITION pmax
INTO (
  PARTITION p4 VALUES LESS THAN (2014),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

默认情况下，MySQL会将分区存储在专属于分区表的数据库目录里。若想将存储分散到其他地方(如放置到不同的物理设备上)，则需要用到分区选项data_directory和INDEX_DIRECTORY。关于这两个以及其他分区选项的语法，请参考附录E。

六、表的删除、清空

表的删除

删除表的一些约束条件：
如果表与表之间存在外键关联。则不能直接删除父表，否则会破坏表的参照完整性。想要删除父表，需要先删除与它关联的子表或者保留子表取消外键约束
drop可以同时删除多个表
语法书写：
drop table [if exists] 表名1,表名2....;
不加if exists，如果表不存在会报错
加if exists，如果表不存在不会报错，但是会警告

表的清空

truncate可以用来清空表数据，但不删除表。
truncate table 表名;
例如，查询表的内容如下
SELECT * FROM test;
输入下面的语句可以清空表的内容
TRUNCATE test;
SELECT * FROM test;

七、表的重命名

使用rename可以重命名一个表或同时命名多个表

RENAME TABLE old_table1_name to new_table1_name,
             old_table2_name to new_table2_name,
             old_table3_name to new_table3_name
             ...;

ALTER TABLE语句（在下面介绍）也可以对表进行重命名，这两者的区别是：RENAME TABLE语句可以同时对多个表进行重新命名。

演示案例

查看所有表
SHOW TABLES;
将test表的名字重命名为test2
RENAME table test TO test2;
SHOW TABLES;

八、表的修改

ALTER TABLE是一条万能型的语句，拥有许多用途。如更改存储引擎、创建和删除索引等，也可用ALTER TABLE语句来完成重命名表、添加或删除列、更改列的数据类型等操作。
小心使用alter table：在进行改动前尽量做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似的，如果删除了不应该删除的列，可能会丢失该列中的所有数据。
语法格式如下：其中的每个动作(action)指的是对表所做的修改。有些数据库系统只允许一条 ALTER TABLE语句带一个动作，但是MySQL支持带上多个动作，以逗号隔开。

ALTER TABLE tbl_name action [,action] ... ;

修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;
to为可选参数，有无可都已。
前面介绍的RENAME TABLE语句也可以用来对表进行重命名，具体比较见上。

修改字段的数据类型
ALTER TABLE tbl_name MODIFY 字段名 新数据类型;

修改字段名、字段数据类型
ALTER TABLE tbl_name CHANGE 旧字段名 新字段名 新数据类型;
CHANGE可以实现两个功能：
只修改字段数据类型（旧字段名和新字段名保持一致即可）。
修改字段名的同时修改数据类型。
例如，假设tbl表有一个INT类型的字段i，下面的语句只修改字段数据类型
ALTER TABLE tbl CHANGE i i MEDIUMINT UNSIGNED;
也可以同时修改字段名和字段数据类型，例如：
ALTER TABLE tbl CHANGE i j MEDIUMINT UNSIGNED;

添加字段
ALTER TABLE tbl_name ADD 新字段名 数据类型 [约束条件] [FIRST]/[AFTER 以存在的字段];
可选参数：约束条件、FIRST、AFTER
FIRST：将新字段添加到表的第一个字段
AFTER：将新字段添加到已存在的字段后面
如果没有填写FIRST或者AFTER字段，则默认将新添加的字段设置为数据表的最后列

删除字段

ALTER TABLE tbl_name DROP 字段名;

修改字段的排列位置
ALTER TABLE tbl_name MODIFY 字段1 数据类型 FIRST[AFTER 字段2;
参数说明：
字段1表示要修改位置的字段
只指定FIRST：将字段1设置为表的第一个字段
指定AFTER：将字段1设置到字段2的后面

更改列的字符集

更改字符集，只要设置CHARACTER SET属性即可。
例如：
ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;

更改表的存储引擎
ALTER TABLE tbl_name ENGINE=new_engine_name;
MySQL支持的存储引擎如下：

还有一些不应使用ALTER TABLE语句来更改表使用另一种存储引擎的情况。例如以下几种情况：
可以让 InnoDB表改用另一种存储引擎。不过，如果这个表定义了外键约束条件，那么这些约束条件在转换后将不复存在，因为只有InnoDB才支持外键。
MEMORY表存储在内存之中，在服务器退出时会消失。因此，如果你希望某个表的内容在服务器重新启动后仍然存在，那么就不要让它改用MEMORY引擎。