19 创建和操纵表

19 创建和操纵表
19.1 创建表
- MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。
- 一般有两种创建表的方法：
  - 使用具有交互式创建和管理表的工具
  - 表也可以直接用MySQL语句操纵
    19.1.1 表创建基础
- 为利用create table创建表，必须给出下列信息：
  - 新表的名字，在关键字create table之后给出
  - 表列的名字和定义，用逗号分隔
- create table语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。下面的MySQL语句创建本书中所用的customers表：
```
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
```
- 处理现有的表 在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。
19.1.2 使用NULL值
- NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。
- 每个表列或者是NULL列，或者是NOT NULL列，这种状态是创建时由表的定义规定。请看下面的例子：
```
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;
```
- 所有3个列都需要，因此每个列的定义都含有关键字NOT NULL。这将会阻止插入没有值的列。如果试图插入没有值的列，将返回错误，且插入失败。
- 下一个例子将创建混合了NULL和NOT NULL列的表：
```
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
```
19.1.3 主键再介绍
- 正如所述，主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
- 为创建多个列组成的主键，应该以逗号分隔的列表给出各列名，如下所示：
```
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
```
- 主键和NULL值 主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。
19.1.4 使用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生成主键的一个缺点是你不知道这些值都是谁。那么，如何在使用auto_increment列时获得这个值呢？可使用last_insert_id()函数获得这个值。如下所示：
```
select last_insert_id();
```
19.1.5 指定默认值
- 如果在插入行时没有给出值，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;
```
- 在未给出数量的情况下使用数量1.
- 不允许函数 MySQL不允许使用函数作为默认值，它只支持常量。
19.1.6 引擎类型
- MySQL有一个具体管理和处理数据的内部引擎。在你使用create table语句时，该引擎具体创建表，而在你使用select语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。
- MySQL具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都执行create table和select等命令。
- 为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
- 以下是几个需要知道的引擎：
  - InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
  - MEMORY在功能等于MyISAM,但由于数据存储在内存中，速度很快（特别适合于临时表）
  - MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
- 外键不能跨引擎 混用引擎类型有一个大缺陷。外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
19.2 更新表
- 理想状态下，当表中存储数据之后，该表就不应该再被更新。
- 为了使用ALTER TABLE更改表结构，必须给出下面的信息：
  - 在ALTER TABLE之后给出要更改的表名
  - 所做更改的列表
- 下面的例子给表添加一个列：
```
alter table vendors
add vend_phone char(20);
```
- 删除刚刚添加的列，可以这样做：
```
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 (cust_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);
```
- 为了对单个表进行多个更改，可以使用单条alter table语句，每个更改用逗号分隔。
- 复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
  - 用新的列布局创建一个新表
  - 使用insert select语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段
  - 检验包含所需数据的新表
  - 重命名旧表
  - 用旧表原来的名字重命名新表
  - 根据需要，重新创建触发器、存储过程、索引和外键。
- 小心使用alter table 使用alter table要极为小心，应该在进行改动前做一个完整的备份。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。
19.3 删除表
- 删除表使用drop table语句即可
```
drop table customers2;
```
- 删除表没有确认，也不能撤销，执行这条语句将永久删除该表。
19.4 重命名表
- 使用rename table语句可以重命名一个表：
```
rename table customers2 to customers;
```
- 使用下面的语句对多个表重命名
```
rename table backup_customers to customers,
             backup_vendors to vendors,
             backup_products to products;
```
相关阅读:
JAVA实验3 类与对象
 多种排序功能的实现
 （数据结构）HashTable的实现
 对string型变量的频率统计(文章单词检索)
对int型变量的频率统计
 二叉搜索树中序迭代器的实现
 二叉搜索树的实现
 Prim最小生成树
 Kruscal最小生成树
 算法分析实践大作业
原文地址：https://www.cnblogs.com/sanjun/p/8285012.html

19 创建和操纵表

19.1 创建表

19.2 更新表

19.3 删除表

19.4 重命名表