如何创建、更改和删除表?
1. 创建表
- 多数DBMS都具有交互式创建和管理数据库表的工具;
- 表也可以直接用SQL语句操纵。
SQL:CREATE TABLE
利用CREATE TABLE创建表,必须给出下列信息:
- 新表的名字,在关键字CREATE TABLE之后给出;
- 表列的名字和定义,用逗号隔开;
- 有的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 NULL
CREATE 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。
注:
在不指定NOT NULL时,多数DBMS认为指定的是NULL,但不是所有的DBMS都这样。DB2要求指定关键字NULL,如果不指定将出错。关于完整的语法信息,请参阅具体的DBMS文档。
主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键,允许NULL值的列不能作为唯一标识。
不要把NULL值与空字符串相混淆。NULL值是没有值,不是空字符串。如果指定”(两个单引号,其间没有字符),这在NOT NULL列中是允许的。空字符串是一个有效的值,它不是无值。NULL值用关键字NULL而不是空字符串指定。
3. 指定默认值
SQL允许指定默认值,在插入行时,如果不给出值,DBMS将自动采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。
-- quantity列为订单中每个物品的数量,这一列的描述增加了DEFAULT 1,指示DBMS,如果不给出数量则使用数量1
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
);
默认值经常用于日期或时间戳列。例如,通过指定引用系统日期的函数或变量,将系统日期用作默认日期。MySQL用户指定DEFAULT CURRENT_DATE(),Oracle用户指定DEFAULT SYSDATE,而SQL server用户指定DEFAULT GETDATE()。
许多数据开发人员喜欢使用DEFAULT值而不是NULL列,对于用于计算或数据分组的列更是如此。
4. 更新表
更新表,使用ALTER TABLE语句。
- 理想情况下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做大改动。
- 所有的DBMS都允许给现有的表增加列,不过对所增加列的数据类型(以及NULL和DEFAULT的使用)有所限制。
- 许多DBMS不允许删除或更改表中的列。
- 多数DBMS允许重新命名表中的列。
- 许多DBMS限制对已经填有数据的列进行更改,对未填有数据的列几乎没有限制。
可以看出,对已有表做更改既复杂又不统一。对表的结构能进行何种更改,请参阅具体的DBMS文档。
-- 给已有表增加列可能是所有DBMS都支持的唯一操作
ALTER TABLE Vendors
ADD vend_phone char(20);
-- 更改或删除列、增加约束或增加键,这些操作也使用类似的语法
ALTER TABLE vendors
DROP 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 customers
WHERE cust_id = '1000000006';
删除列:UPDATE
UPDATE customers
SET cust_email = NULL
WHERE cust_id = '1000000005';
删除表:DROP
DROP TABLE custcopy;
5. 重命名表
对于这个操作,不存在严格的标准。DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句,SQL Server用户使用sp_rename存储过程,SQLite用户使用ALTER TABLE语句。
所有重命名操作的基本语法都要求指定旧表名和新表名。不过,存在DBMS实现差异。关于具体的语法,请参阅相应的DBMS文档。