### 第6篇：MySQL基本操作创建和操纵表
#### 创建表
- MySQL不仅仅用于数据操纵，而且还可以用来执行数据库和表的所有操作，包括本身的创建和处理
- 创建的方式有2种
- 使用具有交互式创建和管理表工具，如：Navicat等
- 直接使用MySQL语句操纵
- 使用程序创建表，使用 CREATE TABLE 语句
- **表创建基础**
- 利用CREATE TABBLE 创建表，必须给出以下信息
- 新表的名字，在关键字 CREATE TABLE 之后给出
- 表列的名字和定义，用逗号分隔
- 下面的语句是创建之前案例中 customers 中的表
```mysql
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;
```
- 实际的表定义(所有列)括在圆括号之中。各列之间用逗号分隔。这个表由9列组成。每列的定义以列名(它在表中必须是唯一的)开始，后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。整条语句由右圆括号后的分号结束。(现在先忽略ENGINE=InnoDB和AUTO_INCREMENT，后面会对它们进行介绍。)
- 注意点：如果建立表的时候，已经有同名的表，则创建失败，此时可以使用 IF NOT EXISTS 新进行判断 ，如果有就创建，没有就不创建
```mysql
CREATE TABLE IF NOT EXISTS 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;
```
- 在上表中，有NOT NULL 和 NULL 的列，NOT NULL 的列必须有值，否则插入的时候会报错
- 理解NULL 不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定''(两个单引号，其间没有字符)，这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。
- **主键再理解**
- 主键必须唯一，表中的每个行必须具有唯一的主键值，如果主键使用单个列，则它的值必须唯一。如果使用多个列作为主键，则这些列的组合值必须唯一。
- 之前使用的CREATE TABLE 例子都是使用单个列作为主键，其中主键使用类似下面的语句定义
- PRIMARY KEY (vend_id)
- 多个列组成的主键，应该以逗号分隔的列表给出各列名
```mysql
CREATE TABLE orderitems(
order_numintNOT NULL,
order_itemintNOT NULL,
prod_idchar(10)NOT NULL,
quantityintNOT NULL,
item_pricedecimal(8,2)NOT NULL,
PRIMARY KEY(order_num,order_item)
)ENGINE=InnoDB;
```
- orderitems 表包含orders表中每个订单的细节。每个订单有多项物品，但每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等等。因此，订单号(order_num列)和订单物品(order_item列)的组合是唯一的，从而适合作为主键
- 主键中不允许为NULL，**关于主键，现在说的比较浅显，但是这个地方是为之后的深入挖掘做铺垫，之后会有专门的底层解析**。
- 使用 AUTO_INCREMENT ，这个很常用，表示自增，也就是再进行数据插入的时候不需要给这个字段值，他会自动从上一个记录的值的位置自增1 [默认是自增1]，但是不一定是从现有的记录最大值开始自增1，**这个地方可能有些疑惑，之后会讲到，现在简单说明一下：一张demo表，主键的id现在最大是10，第二大的是9，然后我把id为10的记录删除，那么再插入一条数据，那么主键id是10还是11呢？答案是11，因为MySQL记录的是曾经生成过的值的下一个，它不恋旧，只用新的。**
- 当然，使用AUTO_INCREMENT 的时候，也可以自己插入主键数据，只要在现在的表中其没有一样的即可，不管之前是否删除过，这个时候只要表中没有这个记录即可。
- 确定AUTO_INCREMENT值，让MySQL生成(通过自动增量)主键的一个缺点是你不知道这些值都是谁 ，此时可以使用 SELECT last_insert_id()函数获得这个值
- **指定默认值**
- 使用 DEFAULT 关键字指定
```mysql
CREATE TABLE orderitems(
order_numintNOT NULL,
order_itemintNOT NULL,
prod_idchar(10)NOT NULL,
quantityintNOT NULLDEFAULT 1,
item_pricedecimal(8,2)NOT NULL,
PRIMARY KEY(order_num,order_item)
)ENGINE=InnoDB;
```
- 在上面的建表语句中，在没有给出 quantity 的值的情况下，默认赋值为1
- 使用默认值而不是NULL值 许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。
- **引擎类型**
- MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。关于**执行引擎，之后会有专门的部分对其进行完全解析。**
- 暂时只需要了解的引擎
- InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
- MEMORY在功能等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表)；
- MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
- 外键不能跨引擎 混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性)不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
- 外键就是，一张表中的某个列的值，为另外一张表的主键。这是一种约束。
#### 更新表
- 为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。
- 为了使用 ALERT TABLE 更改表结构，必须给出下面的信息
- 在 ALERT TABLE 之后要给出要更改的表名(该表必须存在，否则将出错)
- 所做更改的列表
- 添加列的例子
```mysql
ALERT TABLE vendors ADD vend_phone char(20);
```
- 删除列的例子
```mysql
ALERT TABLE vendors DROP vend_phone;
```
- ALERT TABLE 的一种常见用途是定义外键
```mysql
ALERT TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
```
- 复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
- 用新的列布局创建一个新表；
- 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段
- 检验包含所需数据的新表
- 重命名旧表(如果确定，可以删除它)
- 用旧表原来的名字重命名新表
- 根据需要，重新创建触发器、存储过程、索引和外键
#### 删除表
- 删除表(删除整个表而不是其内容)非常简单，使用DROP TABLE语句即可
```mysql
DROP TABLE customers2;
# 这条语句删除customers 2表(假设它存在)。删除表没有确认，也不能撤销，执行这条语句将永久删除该表。
```
#### 重命名表
- 使用 RENAME TABLE 语句可以重命名一个表
```mysql
RENAME TABLE customers2 TO customers;
```
- 可以同时修改多个表的名称
```mysql
RENAME TABLE backup_customers TO customers,backup_vendors TO vendors;
```
#### 参考
- 《MySQL必知必会》
一键复制
编辑
Web IDE
原始数据
按行查看
历史
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
