创建和操纵表
创建表
创建表基础
为利用CREATE TABLE创建表，必须给出下列信息
- 新表的名字，在关键字CREATE TABLE之后给出
- 表列的名字和定义，用逗号分隔
创建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
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
-> ;
Query OK, 0 rows affected (0.33 sec)
mysql>
- 表名紧跟在create table后面，实际的表定义括在圆括号之中。各列之间用逗号分隔
- 表的主键使用PEIMARY KEY关键字指定，
- 在创建新表时，指定的表名必须不存在，否则将出错。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样只是查看表名是否存在，并且仅在表名不存在时创建它
使用NULL值
NULL值就是没有值或缺值，允许NULL值的列，就是允许在插入行时不给出该列的值
不允许NULL值的列在插入或更新行时，该列必须有值
理解NULL
- 不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。
- 如果指定‘’(两个单引号，其间没有字符，这在NOT NULL列是允许的
主键
- 主键值必须为1，即表中的每个行必须具有唯一的主键值
- 如果主键使用单个列，则它的值必须唯一，如果使用多个列，则这些列的组合值，必须唯一
单个列作为主键，其中主键用以下的类似的语句定义
PRIMARY KEY(vend_id)
为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
PRIMARY KEY(order_num, order_item)
)ENGINE=InnoDB
订单号（order_num)和订单物品(order_item)的组合是唯一的，从而适合作为主键，其定义为 PRIMARY KEY(order_num, order_item)
AUTO_INCREMENT
AUTO_INCREMENT告诉mysql，本列每增加一行时自动增量，每次执行一个INSERT操作时，MYSQL自动对该列增量（从而才有了AUTO_INCREMENT），给该列赋予下一个可用的值，这样给每个行分配一个唯一的cust_id,从而可以用作主键值
- 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（通常使它成为主键）
- 可以通过SELECT last_insert_id()语句来获取最后一个AUTO_INCREMENT
指定默认值
如果在插入行时，没有给出值，mysql允许指定此时使用的默认值，默认值在CREATE TABLE时指定
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
quantity int NOT NULL DEFAULT,
PRIMARY KEY(order_num, order_item)
)ENGINE=InnoDB
quantity列包含订单中每项物品的数量，
在此例子中，给该列的描述添加文本DEFAULT 1指示MYSQL,在未给出数量的情况下使用数量1
- 不允许函数：mysql不允许使用函数作为默认值，只支持常量
- 使用默认值，而不是NULL值。
引擎类型
你可能注意到，前面在创建表时一直都是使用ENGINE=InnoDB结束
mysql具有多种引擎，它打包了多个引擎，这些引擎都隐藏在DBMS中，全都能执行CREATE TABLE和SELECT等命令
查看默认使用的引擎
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
mysql>
查看支持的引擎
show engines;
查看某个表使用的引擎
mysql> show create table CUSTOMERS;
| Table | Create Table | CUSTOMERS | CREATE TABLE `CUSTOMERS` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
需要知道的引擎
- InnoDB是一个可靠的事物处理引擎，它不支持全文本搜索
- MEMORY在功能等同于MyISAM，但是由于数据存储在内存中，速度很快（适合于临时表）
- MyISAM是一个性能极高的引擎，他支持全文本搜索，但是不支持事务处理
外键不能跨引擎，即使用一个引擎的表不能引用 使用不同引擎的表的外键
更新表
在理想状态下，当表中存储数据后，该表就不应该再被更新，在表的设计过程中需要花费大量时间来考虑，以便后去不对表进行大的改动
添加列或者删除列都应该在进行改动之前做一个完整的备份
添加一个列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
mysql> ALTER TABLE CUSTOMERS
-> ADD cust_phone CHAR(20);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
这条语句给CUSTOMERS表增加一个名为vend_phone的列，必须明确其数据类型
删除刚刚添加的列，可以这样
ALTER TABLE vendors
DROP COLUMN vend_phone
mysql> ALTER TABLE CUSTOMERS DROP COLUMN cust_phone;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
删除表
删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句
DROP TABLE customers
这条语句删除customers表，没有确认，也不能撤销，执行这条语句将永久删除该表
mysql> DROP TABLE customers;
Query OK, 0 rows affected (0.03 sec)
mysql>
重命名表
将CUSTOMERS表名改为customers
RENAME TABLE CUSTOMERS TO customers；
mysql> RENAME TABLE CUSTOMERS TO customers;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_sumyum |
+------------------+
| customers |
+------------------+
1 row in set (0.00 sec)
mysql>
对多个表重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
