表的创建、更改和删除的基本知识

• 创建表 CREATE TABLE
  • 表创建基础
    • 处理现有的表
  • 使用NULL值
  • 主键再介绍
  • 使用AUTO_INCREMENT
    • 覆盖AUTO_INCREMENT
    • 确定AUTO_INCREMENT值
  • 指定默认值
  • 引擎类型
    • 关于引擎的简略不同点
    • 外键不能夸引擎
• 更新表定义 ALTER TABLE
  • 小心使用ALTER TABLE
• 删除表 DROP TABLE
  • 注意
• 重命名 RENAME TABLE

创建表 CREATE TABLE

• 创建表的2种方法
  1.交互式创建和管理表的工具 （ 生成SQL）
  2.用SQL语句操纵 （CREATE TABLE）

表创建基础

应给出下列信息

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

CREATE TABLE语句也可能包括其他关键字或选项，但至少要包括表的名字和列的细节。

CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一的顾客ID',
  `cust_name` char(50) NOT NULL COMMENT '顾客名',
  `cust_address` char(50) DEFAULT NULL COMMENT '顾客的地址',
  `cust_city` char(50) DEFAULT NULL COMMENT '顾客的城市',
  `cust_state` char(5) DEFAULT NULL COMMENT '顾客的州',
  `cust_zip` char(10) DEFAULT NULL COMMENT '顾客的邮政编码',
  `cust_country` char(50) DEFAULT NULL COMMENT '顾客的国家',
  `cust_contact` char(50) DEFAULT NULL COMMENT '顾客的联系名',
  `cust_email` char(255) DEFAULT NULL COMMENT '顾客的联系email地址',
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10012 DEFAULT CHARSET=utf8 COMMENT='顾客信息表';

表的主键可以在创建表时用PRIMARY KEY关键字指定

处理现有的表

• 在创建新表时，指定的表名必须不存在，否则将出错。SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
• 如果仅像在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS

使用NULL值

NULL值就是没有值或缺值。
允许NULL值的列也允许在插入行是不给出该列的值。
不允许NULL值的列不接受该列没有值的行，即在插入或更新时，该列必须有值。

主键再介绍

• 主键唯一性
  1.主键值必须唯一
  2.如果主键使用单个列，则其值必须唯一
  3.如果主键使用多个列，则这些列的组合值必须唯一。
• 创建主键
  1.单个主键PRIMARY KEY (vend_id)
  2.多个列组成主键PRIMARY KEY(order_num,order_item)
• 何时定义主键
  1.在创建表时定义
  2.在创建表之后定义
• 主键不允许NULL值
  因为主键的唯一性，所以允许NULL值的列不能作为主键

使用AUTO_INCREMENT

主键自增编号，最简单的编号是下一个编号，是大于当前最大编号的编号。

• 如何确定下一个要使用的值？
  如果使用SELECT语句得出最大的数(MAX())，然后对其加1，这样做并不可靠，因为无法保证SELECT和INSERT语句之间没有其他数据插入。且效率也不高。
  AUTO_INCREMENT使得MySQL中该列每当增加一行时，自动增量，从而可用作主键，每个列表值允许一个AUTO_INCREMENT列，且其必须被索引

覆盖AUTO_INCREMENT

可以在INSERT语句中指定一个值，只要其是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

确定AUTO_INCREMENT值

MySQL自动生成自增主键的缺点是无法知道这些值是多少。
可使用last_insert_id()函数获得这个值
SELECT last_insert_id()返回最后一个 AUTO_INCREMENT值

指定默认值

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定
如果插入行时没有给出值，MySQL允许指定词是使用的默认值。

• 默认值不允许为函数
  与大多数DBMS一样，MySQL不允许使用函数作为默认值，其只支持常量
• 使用默认值而不是NULL值
  应该使用默认值而不是NULL值，特别是对用于计算或数据分组的列更是如此。

引擎类型

MySQL有多个具体管理和处理数据的内部引擎。该引擎具体创建表处理各种请求
各个引擎具有各自不同的功能和特性。如果省略ENGINE=语句，则使用默认引擎。

关于引擎的简略不同点

• InnoDB时一个可靠的事物处理引擎，不支持全文本搜索
• MEMORY在功能等同于MyISAM，数据存储在内存中，速度很快，适合于临时表
• MyISAM是一个性能极高的引擎，支持全文本搜索，但不支持事物

外键不能夸引擎

外键不能夸引擎，即使用同一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表定义 ALTER TABLE

理想状态下，当表中存储数据以后，该表就不应该被更新。表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

• 使用ALTER TABLE更改表结构，应给出下面的信息
  1.要更改的表名
  2.更改的列表
• 例
  1.添加列

ALTER TABLE vendors ADD vend_phone CHAR ( 20 );

2.删除列

ALTER TABLE vendors DROP COLUMN vend_phone;

删除刚刚添加的vend_phone列

ALTER TABLE的一种常见用途就是定义外键，外键实际当中基本不使用就不写了。。。

小心使用ALTER TABLE

在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。如果删除了不应该删除的列，可能会丢失该列中所有数据

删除表 DROP TABLE

DROP TABLE customers2;

注意

删除表没有确认提示，也不能撤销，将永久删除该表

重命名 RENAME TABLE

其所做的仅是重命名一个表，可使用虾米哪语句对多个表重命名

RENAME TABLE customers TO customers1,
orderitems TO orderitems1;