SQL-创建和操纵表

如何创建、更改和删除表？

---

1. 创建表

1. 多数DBMS都具有交互式创建和管理数据库表的工具；
2. 表也可以直接用SQL语句操纵。

SQL：CREATE TABLE

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

1. 新表的名字，在关键字CREATE TABLE之后给出；
2. 表列的名字和定义，用逗号隔开；
3. 有的DBMS还要求指定表的位置。

CREATE TABLE products(    prod_id       char(10)        NOT NULL,    vend_id       char(10)        NOT NULL,    prod_name     char(254)       NOT NULL,    prod_price    decimal(8,2)    NOT NULL,    prod_desc     varchar(1000)   NULL);

在创建新表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表，SQL首先手工删除该表，然后再重新创建它，而不是简单地创建表语句覆盖它。

2. 使用NULL值

NULL –没有值或缺值。

允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。

每个表列要么是NULL列，要么是NOT NULL列，这种状态在创建时由表的定义规定。

-- 供应商ID和供应商名字列是必需的，因此指定为NOT NULL。其余五列全都允许NULL值，所以不指定NOT NULLCREATE TABLE Vendors(    vend_id          CHAR(10)     NOT NULL,    vend_name        CHAR(50)     NOT NULL,    vend_address     CHAR(50)     ,    vend_city        CHAR(50)     ,    vend_state       CHAR(5)      ,    vend_zip         CHAR(10)     ,    vend_country     CHAR(50));

NULL为默认设置，如果不指定NOT NULL，就认为指定的是NULL。

注：

1. 在不指定NOT NULL时，多数DBMS认为指定的是NULL，但不是所有的DBMS都这样。DB2要求指定关键字NULL，如果不指定将出错。关于完整的语法信息，请参阅具体的DBMS文档。

2. 主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。

3. 不要把NULL值与空字符串相混淆。NULL值是没有值，不是空字符串。如果指定”（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空字符串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空字符串指定。

3. 指定默认值

SQL允许指定默认值，在插入行时，如果不给出值，DBMS将自动采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。

-- quantity列为订单中每个物品的数量，这一列的描述增加了DEFAULT 1，指示DBMS，如果不给出数量则使用数量1CREATE TABLE OrderItems(    order_num      INTEGER          NOT NULL,    order_item     INTEGER          NOT NULL,    prod_id        CHAR(10)         NOT NULL,    quantity       INTEGER          NOT NULL      DEFAULT 1,    item_price     DECIMAL(8,2)     NOT NULL);

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。MySQL用户指定DEFAULT CURRENT_DATE()，Oracle用户指定DEFAULT SYSDATE，而SQL server用户指定DEFAULT GETDATE()。

许多数据开发人员喜欢使用DEFAULT值而不是NULL列，对于用于计算或数据分组的列更是如此。

4. 更新表

更新表，使用ALTER TABLE语句。

1. 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。
2. 所有的DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及NULL和DEFAULT的使用）有所限制。
3. 许多DBMS不允许删除或更改表中的列。
4. 多数DBMS允许重新命名表中的列。
5. 许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

可以看出，对已有表做更改既复杂又不统一。对表的结构能进行何种更改，请参阅具体的DBMS文档。

-- 给已有表增加列可能是所有DBMS都支持的唯一操作ALTER TABLE VendorsADD vend_phone char(20);-- 更改或删除列、增加约束或增加键，这些操作也使用类似的语法ALTER TABLE vendorsDROP COLUMN vend_phone;

注：
ALTER TABLE和SQLite
SQLite对使用ALTER TABLE执行的操作有所限制。最重要的一个限制是，它不支持使用ALTER TABLE定义主键和外键，这些必须在最初创建表时指定。

小心使用ALTER TABLE
使用ALTER TABLE要极为小心，应该在进行改动前做完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

5. 删除表

删除表（删除整个表而不是其内容）：DROP。

DROP TABLE custcopy;

删除表没有确认，也不能撤销，执行这条语句将永远删除该表。

注意：使用关系规则防止意外删除
如果对某个表发布一条DROP TABLE语句，且该表是某个关系的组成部分，则DBMS将阻止这条语句执行，直到该关系被删除为止。如果允许，应该启用这些选项，它能防止意外删除有用的表。

注意区分：

删除行：DELETE

DELETE FROM customersWHERE cust_id = '1000000006';

删除列：UPDATE

UPDATE customersSET cust_email = NULLWHERE cust_id = '1000000005';

删除表：DROP

DROP TABLE custcopy;

5. 重命名表

对于这个操作，不存在严格的标准。DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句，SQL Server用户使用sp_rename存储过程，SQLite用户使用ALTER TABLE语句。

所有重命名操作的基本语法都要求指定旧表名和新表名。不过，存在DBMS实现差异。关于具体的语法，请参阅相应的DBMS文档。