SQL 入门笔记(十七)创建和操纵表
本节将讲述创建、更改和删除表的基础知识
创建表
创建表的话,最好是通过图形化的管理工具来创建,但也可以手动通过 SQL 语句创建
本节不会介绍创建表时可以使用的所有选项,只会给出一些基本选项,详细的信息还是去翻对应的 DBMS 文档吧(
表创建基础
利用 CREATE TABLE
创建表,必须给出以下信息:
- 新表的名字,在关键字
CREATE TABLE
之后给出 - 表列的名字和定义,用逗号分隔
- 有点 DBMS 还要求指定表的位置
下面的 SQL 语句创建了样例中的 Products 表
1 | CREATE TABLE Products |
使用 NULL 值
某一列是否能为 NULL 值可以在创建时设置,就如上面的那样
如果缺省的话,大多数 DBMS 都会设置为 NULL
,而不是 NOT NULL
,当然也有一些例外,并且有些 DBMS 必须要你指定是 NULL
还是 NOT NULL
,所有为了兼容性,建议始终指定这一项
指定默认值
类似地,可以指定默认值
1 | CREATE TABLE OrderItems |
默认值经常用于日期或者时间戳列,设定获取当前时间的函数为默认值,这样在插入时就可以自动写入时间了
从检索的数据创建表
之前讲了可以使用 INSERT SELECT
将检索数据添加到一个已经存在的表,而 CREATE SELECT
能将使用检索数据创建一个新表(有些 DBMS 可以覆盖已存在的表)
下面的例子说明如何使用 CREATE SELECT
1 | CREATE TABLE CustCopy AS SELECT * FROM Customers; |
若是使用 SQL Server,可以这么写
1 | SELECT * INTO CustCopy FROM Customers; |
这条 SELECT
语句创建一个名为 CustCopy
的新表,并把 Customers
表的整个内容复制到新表中。因为这里用的是 SELECT *
,所以每一列都是相同的。如果只想复制部分列,可以给出列名
提示:进行表的复制
在试验新的 SQL 语句之前,可以先复制出来一份出来测试,CREATE SELECT
是很好的工具
更新表
更新表定义,可以使用 ALTER TABLE
语句。虽然所有的 DBMS 都支持,但它们所允许更新的内容差别很大,以下是使用 ALTER TABLE
时需要考虑的事情
- 在理想情况下,不要在表中包括数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结果做出大改动
- 所有的 DBMS 都允许给现有的表增加列,不过对所增加列的数据类型,以及 NULL 和 DEFAULT 的使用有所限制
- 许多 DBMS 不允许删除或更改表中的列
- 多数 DBMS 允许重命名表中的列
- 许多 DBMS 限制对已经填有数据的列进行改变,对未填有数据的列几乎没有限制
可以看出,对已有的表做出更改既复杂又不统一,需要参考具体的 DBMS 文档
使用 ALTER TABLE
更改表结构,必须给出下面的信息:
- 在
ALTER TABLE
之后给出要更改的表名 - 列出要做出哪些更改
因为给已有的表增加列可能是所有 DBMS 都支持的唯一操作,所以举下面这个例子
1 | ALTER TABLE Vendors |
而这个例子就并非对所有 DBMS 都有效
1 | ALTER TABLE Vendors |
如果要更改复杂的表结构,建议使用下面的步骤
- 用新的列布局创建一个新表
- 使用
INSERT SELECT
把数据搬过去 - 检验包含所需数据的新表
- 把旧表重命名或者删掉
- 用旧表原来的名字重命名新表
- 根据需要,重新创建触发器、存储过程、索引和外键
说明:ALTER TABLE 和 SQLite
在 SQLite 中,不允许使用 ALTER TABLE
定义主键和外键,这些必须在最初创建表时指定
注意:小心使用 ALTER TABLE
使用 ALTER TABLE
要极为小心,建议先备份,因为数据库表的更改不能撤销
删除表
非常简单,使用 DROP TABLE
即可
1 | DROP TABLE CustCopy; |
重命名表
每个 DBMS 对表重命名的支持有所不同,对于这个操作不存在严格的标准。DB2、MySQL、Oracle 和 PostgreSQL 使用 RENAME
语句,SQL Server 使用 sp_rename
存储过程,SQLite 使用 ALTER TABLE
语句,具体的还得看文档