第17课 创建和操纵表

第17课 创建和操纵表

 

17.1 创建表

一般有两种创建表的方法：

 多数 DBMS 都具有交互式创建和管理数据库表的工具；

 表也可以直接用 SQL 语句操纵。

用程序创建表，可以使用 SQL 的 CREATE TABLE 语句。

 

17.1.1 表创建基础

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

 新表的名字，在关键字 CREATE TABLE 之后给出；

 表列的名字和定义，用逗号分隔；

 有的 DBMS 还要求指定表的位置。

下面的 SQL 语句创建本书中所用的 Products 表：

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

);

分析：

这条语句在 Oracle、PostgreSQL、SQL Server 和 SQLite中有效，而对于 MySQL，varchar 必须替换为 text；对于 DB2，必须从最后一列中去掉 NULL。这就是对于不同的 DBMS，要编写不同的表创建脚本的原因

 

17.2 使用NULL值

CREATE TABLE Orders

(

order_num INTEGER NOT NULL,

order_date DATETIME NOT NULL,

cust_id CHAR(10) NOT NULL

);

分析：

这条语句创建本书中所用的 Orders 表。Orders 包含三列：订单号、订单日期和顾客 ID。这三列都需要，因此每一列的定义都含有关键字 NOTNULL。这就会阻止插入没有值的列。如果插入没有值的列，将返回错误，且插入失败。

 

17.1.3 指定默认值

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

请看下面的例子：

输入：

CREATE 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

);

 

分析：

这条语句创建 OrderItems 表，包含构成订单的各项（订单本身存储在Orders 表中）。quantity 列为订单中每个物品的数量。在这个例子中，这一列的描述增加了 DEFAULT 1，指示 DBMS，如果不给出数量则使用数量 1。

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。MySQL 用户指定 DEFAULTCURRENT_DATE()，Oracle 用户指定 DEFAULT SYSDATE，而 SQL Server用户指定 DEFAULT GETDATE()。遗憾的是，这条获得系统日期的命令在不同的 DBMS 中几乎都是不同的。表 17-1 列出了这条命令在某些 DBMS中的语法。这里若未列出某个 DBMS，请参阅相应的文档。

 

17.2 更新表

更新表定义，可以使用 ALTER TABLE 语句。

使用 ALTER TABLE 更改表结构，必须给出下面的信息：

 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；

 列出要做哪些更改。

 

因为给已有表增加列可能是所有 DBMS 都支持的唯一操作，所以我们举个这样的例子：

输入：

ALTER TABLE Vendors

ADD vend_phone CHAR(20);

分析：

这条语句给 Vendors 表增加一个名为 vend_phone 的列，其数据类型为 CHAR。

 

更改或删除列、增加约束或增加键，这些操作也使用类似的语法（注意，下面的例子并非对所有 DBMS 都有效）：

输入：

ALTER TABLE Vendors

DROP COLUMN vend_phone;

 

17.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用 DROP TABLE 语句即可：

输入：

DROP TABLE CustCopy;

分析：

这条语句删除 CustCopy 表（第 15 课中创建的）。删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

 

17.4 重命名表

每个 DBMS 对表重命名的支持有所不同。对于这个操作，不存在严格的标准。DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME语句，SQL Server 用户使用 sp_rename 存储过程，SQLite 用户使用 ALTER TABLE 语句。