SQL必知必会笔记十七(创建和操纵表)

创建、更改和删除表的基本知识。

一、创建表

SQL不仅用于表数据操纵,还用来执行数据库和表的所有操作,包括表本身的创建和处理。

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

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

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

用程序创建表,可以使用SQL的CREATE TABLE语句。需要注意的是,使用交互式工具时实际上就是使用SQL语句。这些语句不是用户编写的,界面工具会自动生成并执行相应的SQL语句(更改已有的表时也是这样)。

注意:语法差别

在不同的SQL实现中,CREATE TABLE语句的语法可能有所不同。对于具体的DBMS支持何种语法,请参阅相应的文档。

表创建基础

利用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 );

从上面的例子可以看到,表名紧跟CREATE TABLE关键字。实际的表定义(所有列)括在圆括号之中,各列之间用逗号分隔。这个表由5列组成。每列的定义以列名(它在表中必须是唯一的)开始,后跟列的数据类型(关于数据类型的解释,请参阅第1课。此外,附录D列出了常见的数据类型及兼容性)。整条语句以圆括号后的分号结束。

前面提到,不同DBMS的CREATE TABLE的语法有所不同,这个简单脚本也说明了这一点。这条语句在Oracle、PostgreSQL、SQL Server和SQLite中有效,而对于MySQL,varchar必须替换为text;对于DB2,必须从最后一列中去掉NULL。这就是对于不同的DBMS,要编写不同的表创建脚本的原因(参见附录A)。

提示:语句格式化

回想一下在SQL语句中忽略的空格。语句可以在一个长行上输入,也可以分成许多行,它们没有差别。这样,你就可以用最适合自己的方式安排语句的格式。前面的CREATE TABLE语句就是SQL语句格式化的一个好例子,代码安排在多个行上,列定义进行了恰当的缩进,更易阅读和编辑。以何种格式安排SQL语句并没有规定,但我强烈推荐采用某种缩进格式。

提示:替换现有的表

在创建新的表时,指定的表名必须不存在,否则会出错。防止意外覆盖已有的表,SQL要求首先手工删除该表(请参阅后面的内容),然后再重建它,而不是简单地用创建表语句覆盖它。

使用NULL值

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

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

例如:

CREATE TABLE Orders ( order_num INTEGER NOT NULL, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL );

创建Orders表。Orders包含三列:订单号、订单日期和顾客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) );

这条语句创建Vendors表。供应商ID和供应商名字列是必需的,因此指定为NOT NULL。其余五列全都允许NULL值,所以不指定NOT NULL。NULL为默认设置,如果不指定NOT NULL,就认为指定的是NULL。

注意:指定NULL

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

提示:主键和NULL值

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

注意:理解NULL

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

指定默认值

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用户指定DEFAULT CURRENT_DATE(),Oracle用户指定DEFAULT SYSDATE,而SQL Server用户指定DEFAULT GETDATE()。遗憾的是,这条获得系统日期的命令在不同的DBMS中几乎都是不同的。表17-1列出了这条命令在某些DBMS中的语法。这里若未列出某个DBMS,请参阅相应的文档。

表17-1 获得系统日期

 

提示:使用DEFAULT而不是NULL值

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

 

二、更新表

更新表定义,可以使用ALTER TABLE语句。虽然所有的DBMS都支持ALTER TABLE,但它们所允许更新的内容差别很大。以下是使用ALTER TABLE时需要考虑的事情。

• 理想情况下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做大改动。

• 所有的DBMS都允许给现有的表增加列,不过对所增加列的数据类型(以及NULL和DEFAULT的使用)有所限制。

• 许多DBMS不允许删除或更改表中的列。

• 多数DBMS允许重新命名表中的列。

• 许多DBMS限制对已经填有数据的列进行更改,对未填有数据的列几乎没有限制。

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

使用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;

复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:

(1)用新的列布局创建一个新表;

(2)使用INSERT SELECT语句,从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段;

(3)检验包含所需数据的新表;

(4)重命名旧表(如果确定,可以删除它);

(5)用旧表原来的名字重命名新表;

(6)根据需要,重新创建触发器、存储过程、索引和外键。

说明:ALTER TABLE和SQLite

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

注意:小心使用ALTER TABLE

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

 

三、删除表

删除表(删除整个表而不是其内容)非常简单,使用DROP TABLE语句即可:

例如:

DROP TABLE CustCopy;

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

提示:使用关系规则防止意外删除

许多DBMS允许强制实施有关规则,防止删除与其他表相关联的表。在实施这些规则时,如果对某个表发布一条DROP TABLE语句,且该表是某个关系的组成部分,则DBMS将阻止这条语句执行,直到该关系被删除为止。如果允许,应该启用这些选项,它能防止意外删除有用的表。

 

四、重命名表

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值