第17课 SQL入门之创建和操纵表

warlack007

已于 2023-12-27 08:26:14 修改

阅读量779

点赞数 23

分类专栏： # SQL入门文章标签： sql 数据库

于 2023-12-18 08:30:00 首次发布

本文链接：https://blog.csdn.net/warlack007/article/details/134922723

版权

SQL入门专栏收录该内容

27 篇文章 0 订阅

订阅专栏

本专栏目录

第1课 SQL入门之了解SQL
第2课 SQL入门之检索数据
 第3课 SQL入门之排序检索数据
 第4课 SQL入门之过滤数据
 第5课 SQL入门之高级数据过滤
 第6课 SQL入门之用通配符进行过滤
 第7课 SQL入门之创建计算字段
 第8课 SQL入门之使用数据处理函数
 第9课 SQL入门之汇总数据
 第10课 SQL入门之分组数据
 第11课 SQL入门之使用子查询
 第12课 SQL入门之联结表
 第13课 SQL入门之创建高级联结
 第14课 SQL入门之组合查询
 第15课 SQL入门之插入数据
 第16课 SQL入门之更新和删除数据
 第17课 SQL入门之创建和操纵表
 第18课 SQL入门之使用视图
 第19课 SQL入门之使用存储过程
 第20课 SQL入门之管理事务处理
 第21课 SQL入门之使用游标
 第22课 SQL入门之高级SQL特性
 附录A SQL入门之SQL样例表脚本
 附录B SQL入门之SQL流行的应用程序
 附录C SQL入门之SQL语句的语法
 附录D SQL入门之SQL数据类型
 附录E SQL入门之SQL保留字

这一课讲授创建、更改和删除表的基本知识。

17.1 创建表

SQL不仅用于表数据操纵，还用来执行数据库和表的所有操作，包括表本身的创建和处理。
一般有两种创建表的方法：

多数DBMS都具有交互式创建和管理数据库表的工具；
表也可以直接用SQL语句操纵。
用程序创建表，可以使用SQL的CREATE TABLE语句。需要注意的是，使用交互式工具时实际上就是使用SQL语句。这些语句不是用户编写的，界面工具会自动生成并执行相应的SQL语句（更改已有的表时也是这样）。

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

这一课不会介绍创建表时可以使用的所有选项，那超出了本课的范围，我只给出一些基本选项。详细的信息说明，请参阅具体的DBMS文档。

说明：DBMS创建表的具体例子
关于DBMS的CREATE TABLE语句的具体例子，请参阅附录A中给出的样例表创建脚本。

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

分析▼
从上面的例子可以看到，表名紧跟CREATE TABLE关键字。实际的表定义（所有列）括在圆括号之中，各列之间用逗号分隔。这个表由5列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型（关于数据类型的解释，请参阅第1课。此外，附录D列出了常见的数据类型及兼容性）。整条语句以圆括号后的分号结束。
前面提到，不同DBMS的CREATE TABLE的语法有所不同，这个简单脚本也说明了这一点。这条语句在Oracle、PostgreSQL、SQL Server和SQLite中有效，而对于MySQL，varchar必须替换为text；对于DB2，必须从最后一列中去掉NULL。这就是对于不同的DBMS，要编写不同的表创建脚本的原因。

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

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

17.1.2 使用NULL值

第4课提到，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都这样。DB2要求指定关键字NULL，如果不指定将出错。关于完整的语法信息，请参阅具体的DBMS文档。

提示：主键和NULL值
第1课介绍过，主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。

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

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

DBMS	函数/变量
Access	NOW()
DB2	CURRENT_DATE
MySQL	CURRENT_DATE()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLite	date(‘now’)

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

17.2 更新表

更新表定义，可以使用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;

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

用新的列布局创建一个新表；
使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第15课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键。

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

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

17.3 删除表

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

DROP TABLE CustCopy;

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

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

17.4 重命名表

每个DBMS对表重命名的支持有所不同。对于这个操作，不存在严格的标准。DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句，SQL Server用户使用sp_rename存储过程，SQLite用户使用ALTER TABLE语句。
所有重命名操作的基本语法都要求指定旧表名和新表名。不过，存在DBMS实现差异。关于具体的语法，请参阅相应的DBMS文档。

上一篇：第16课 SQL入门之更新和删除数据
下一篇：第18课 SQL入门之使用视图