mysql表的处理

表的处理

第21章创建和操纵表

21.1 创建表

MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。一般有两种创建表的方法：
- 使用具有交互式创建和管理表的工具（如第2章讨论的工具）；
- 表也可以直接用MySQL语句操纵。
为了用程序创建表，可使用SQL的CREATE TABLE语句。值得注意的是，在使用交互式工具时，实际上使用的是MySQL语句。但是，这些语句不是用户编写的，界面工具会自动生成并执行相应的MySQL语句（更改现有表时也是这样）。

★21.1.1 表创建基础

★为利用CREATE TABLE创建表，必须给出下列信息：

新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔。

CREATE TABLE table_name
(
    field1 datatype,
    field2 datatype,
    field3 datatype,
    ...
)character set 字符集 collate 校对规则 engine 引擎

field: 指定列名
datatype: 指定列类型（字段类型）
character set: 如不指定则为所在数据库字符集
collate: 如不指定则为所在数据库校对规则
engine: 引擎（这个涉及内容较多，后面单独讲解）

注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。下面的MySQL语句创建本书中所用的customers表：
![](../../../../../Running Noob/计算机/Typora笔记/笔记-git仓库/Mysql_notebook/img/mysql创建表.jpg)
从上面的例子中可以看到，表名紧跟在CREATE TABLE关键字后面。实际的表定义（所有列）括在圆括号之中，各列之间用逗号分隔。这个表由9列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型（关于数据类型的解释，请参阅第1章。此外，附录D列出了MySQL支持的数据类型）。表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。整条语句由右圆括号后的分号结束。（现在先忽略 ENGINE=InnoDB 和AUTO_INCREMENT，后面会对它们进行介绍。）
处理现有的表：在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

21.1.2 使用NULL值

第6章中说过，NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。
每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。请看下面的例子：
![](../../../../../Running Noob/计算机/Typora笔记/笔记-git仓库/Mysql_notebook/img/mysql创建表2.jpg)
这条语句创建本书中所用的orders表。orders包含3个列，分别是订单号、订单日期和客户ID。所有3个列都需要，因此每个列的定义都含有关键字NOT NULL。这将会阻止插入没有值的列。如果试图插入没有值的列，将返回错误，且插入失败。
下一个例子将创建混合了NULL和NOT NULL列的表：
![](../../../../../Running Noob/计算机/Typora笔记/笔记-git仓库/Mysql_notebook/img/mysql创建表3.jpg)
这条语句创建本书中使用的vendors表。供应商ID和供应商名字列是必需的，因此指定为NOT NULL。其余5个列全都允许NULL值，所以不指定NOT NULL。NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。

21.1.3 主键再介绍

正如所述，主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
迄今为止我们看到的CREATE TABLE例子都是用单个列作为主键。其中主键用以下的类似的语句定义：PRIMARY KEY (vend_id)
为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名，如下所示：
![](../../../../../Running Noob/计算机/Typora笔记/笔记-git仓库/Mysql_notebook/img/mysql创建表4.jpg)
orderitems表包含orders表中每个订单的细节。每个订单有多项物品，但每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等等。因此，订单号（order_num列）和订单物品（order_item列）的组合是唯一的，从而适合作为主键，其定义为：PRIMARY KEY (order_num, order_item)
主键可以在创建表时定义（如这里所示），或者在创建表之后定义（本章稍后讨论）。
注意：第1章介绍过，主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

★21.1.4 使用AUTO_INCREMENT

让我们再次考察customers和orders表。customers表中的顾客由列cust_id唯一标识，每个顾客有一个唯一编号。类似，orders表中的每个订单有一个唯一的订单号，这个订单号存储在列order_num中。这些编号除它们是唯一的以外没有别的特殊意义。在增加一个新顾客或新订单时，需要一个新的顾客ID或订单号。这些编号可以任意，只要它们是唯一的即可。
显然，使用的最简单的编号是下一个编号，所谓下一个编号是大于当前最大编号的编号。例如，如果cust_id的最大编号为10005，则插入表中的下一个顾客可以具有等于10006的cust_id。
简单吗？不见得。你怎样确定下一个要使用的值？当然，你可以使用SELECT语句得出最大的数（使用第12章介绍的Max()函数），然后对它加1。但这样做并不可靠（你需要找出一种办法来保证，在你执行SELECT和INSERT两条语句之间没有其他人插入行，对于多用户应用，这种情况是很有可能出现的），而且效率也不高（执行额外的MySQL操作肯定不是理想的办法）。
这就是AUTO_INCREMENT发挥作用的时候了。请看以下代码行（用来创建customers表的CREATE TABLE语句的组成部分）：
cust_id int NOT NULL AUTO_INCREMENT AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。
注意：每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
★覆盖AUTO_INCREMENT：如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将从该手工插入的值开始。
★确定AUTO_INCREMENT值：让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。考虑这个场景：你正在增加一个新订单。这要求在orders表中创建一行，然后在orderitms表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。那么，如何在使用AUTO_INCREMENT列时获得这个值呢？可使用LAST_INSERT_ID()函数获得这个值，如下所示：SELECT LAST_INSERT_ID()此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。
自增长使用细节：
1. 一般来说自增长是和 PRIMARY KEY 配合使用的。
2. 自增长也可以单独使用 [但是需要配合一个 UNIQUE ]。
3. 自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用)。
4. 自增长默认从 1 开始，你也可以通过如下命令修改 ALTER TABLE 表名 AUTO_INCREMENT = 新的开始值;。
5. 如果添加数据时，给自增长字段（列）指定了值，则重新以指定的值为基准开始自增长。一般来说，如果指定了自增长，就按照自增长的规则来添加数据。

★21.1.5 指定默认值DEFAULT

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。请看下面的例子：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL  DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

这条语句创建包含组成订单的各物品的orderitems表（订单本身存储在orders表中）。quantity列包含订单中每项物品的数量。在此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1。

不允许函数：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
使用默认值而不是NULL值：许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。
小技巧：一般来说，将 NOT NULL 和 DEFAULT 组合使用，是一种设计习惯。

21.1.6 引擎类型

你可能已经注意到，迄今为止使用的CREATE TABLE语句全都以ENGINE=InnoDB语句结束。
与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。当然，你完全可以忽略这些数据库引擎。如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因（也就是为什么本书的样列表中使用两种引擎的原因）。
以下是几个需要知道的引擎：
- InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
- MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
引擎类型可以混用：除productnotes表使用MyISAM外，本书中的样例表都使用InnoDB。原因是作者希望支持事务处理（因此，使用InnoDB），但也需要在productnotes中支持全文本搜索（因此，使用MyISAM）。
外键不能跨引擎：混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性，如第1章所述）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

★补充：MySQL表类型和存储引擎

MySQL 的表类型由存储引擎 (Storage Engines) 决定，主要包括 MyISAM、InnoDB、Memory等。
MySQL 数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB。
这六种又分为两类，一类是”事务安全型” (transaction-safe)，比如：InnoDB。其余都属于第二类，称为”非事务安全型” (non-transaction-safe) [MyISAM和Memory]。

主要的存储引擎 / 表类型特点：

特点	MyISAM	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

MyISAM 不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。
-> 添加速度快、支持表级锁、不支持事务和外键。
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起 MyISAM 存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
-> 支持事务、支持外键、支持行级锁。
MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引。但是一旦 MySQL 服务关闭，表中的数据就会丢失掉，表的结构还在。
-> 数据存储在内存中（关闭了 MySQL 服务，数据丢失，但是表的结构还在）、执行速度很快（没有 I/O 读写）、默认支持索引（hash表）。

如何选择表的存储引擎：
1. 如果你的应用不需要事务，处理的只是基本的 CRUD 操作，那么 MyISAM 是不二选择，速度快。
2. 如果需要支持事务，选择 InnoDB。
3. Memory 存储引擎就是将数据存储在内中，由于没有磁盘 I/O 的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法 -> 用户的在线状态的保存）

21.1.7 LIKE操作符

注意：通过 LIKE 操作符，可以让创建的表拥有和已有的表相同的结构（相同的字段和数据类型）：CREATE TABLE new_table LIKE exist_table;

★21.2 更新表

为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

★为了使用ALTER TABLE更改表结构，必须给出下面的信息：

在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
所做更改的列表。

使用 ALTER TABLE 语句追加、修改、删除列的语法：

添加列：

1
2
3

ALTER TABLE table_name
    ADD			(column datatype [DEFAULT expr]
                [, column datatype]...);

修改列：

1
2
3

ALTER TABLE table_name
    MODIFY		(column datatype [DEFAULT expr]
                [, column datatype]...);

删除列：

1 2	ALTER TABLE table_name DROP (column);

查看表的结构：DESC 表名; -- 可以查看表的列

修改表的列的名称：

1 2	ALTER TABLE table_name CHANGE old_name new_name datatype [NOT NULL] [DEFAULT expr];

下面的例子给表添加一个列：ALTER TABLE vendors ADD vend_phone char(20);
这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。
删除刚刚添加的列，可以这样做：ALTER TABLE vendors DROP COLUMN vend_phone;
★ALTER TABLE的一种常见用途是定义外键。下面是用来定义本书中的表所用的外键的代码：

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

这里，由于要更改4个不同的表，使用了4条ALTER TABLE语句。为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

修改表的存储引擎：ALTER TABLE table_name ENGINE = 存储引擎;
修改表的字符集：ALTER TABLE 表名 CHARACTER SET 字符集 ，例如：
1
ALTER TABLE test_table CHARACTER SET utf8;
将表 test_table 的字符集修改为 utf8。
复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
- 用新的列布局创建一个新表；
- 使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第19章）从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
- 检验包含所需数据的新表；
- 重命名旧表（如果确定，可以删除它）；
- 用旧表原来的名字重命名新表；
- 根据需要，重新创建触发器、存储过程、索引和外键。
小心使用ALTER TABLE：使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

21.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可：DROP TABLE test;
这条语句删除test表（假设它存在）。删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

21.4 重命名表

使用RENAME TABLE语句可以重命名一个表：RENAME TABLE customers2 TO customers;
将表 customers2 重命名为 customers。
RENAME TABLE所做的仅是重命名一个表。可以使用下面的语句对多个表重命名：

1
2
3

RENAME TABLE backup_customers TO customers,
			 backup_vendors TO vendors,
			 backup_products TO products;

补充：表复制和去重

表复制

有时，为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用该方法（自我复制数据 - 蠕虫复制）为表创建海量数据：
1
2
INSERT INTO table_name
SELECT * FROM table_name;

表去重

以对 example_table 表去重为例：
1. 先创建一张临时表 temp_table，该表的结构和 example_table 一样：
  1
  CREATE TABLE temp_table LIKE example_table;
2. 然后把 example_table 的记录通过 DISTINCT 关键字处理后，复制到 temp_table 表中。-> 去重发生在这一步
  1
  2
  INSERT INTO temp_table
  SELECT DISTINCT * FROM example_table;
3. 清除掉 example_table 表的内容：
  1
  TRUNCATE TABLE example_table;
4. 把 temp_table 表的记录复制到 example_table ：
  1
  2
  INSERT INTO example_table
  SELECT * FROM temp_table;
5. 删除临时表 temp_table ：
  1
  DROP TABLE temp_table;

第21章 创建和操纵表