《SQL必知必会》第十七课 创建和操纵表 创建、更改和删除表的基本知识

第十七课 创建和操纵表

#创建、更改和删除表的基本知识
#CREATE TABLE 用来创建新表
#ALTER TABLE 用来更改表列（或其他诸如约束或索引等对象）
#DROP TABLE用来完整地删除一个表
#必须小心使用这些语句，且应在做了备份后使用
#这些语句的语法在不同的DBMS中有所不同，详细信息请参阅相应的DBMS文档

一、创建表

#MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理
#创建表的两种方法：

• 多数DBMS具有交互式创建和管理数据库表的工具
• 表直接用MySQL语句操纵

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

注意1：
[1] 使用交互式工具时，实际上就是使用MySQL语句
[2] 这些语句不是用户编写的，界面工具会自动生成并执行相应的MySQL语句（更改现有表时也是这样）
[3]在不同的SQL实现中，CREATE TABLE语句的语法可能有所不同

1.1 表创建基础：

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

• 新表的名字，在关键字CREATE TABLE之后给出
• 表列的名字和定义，用逗号分隔
• 有的DBMS还要求指定表的位置

#创建表之前：

【1】CREATE TABLE products2 ( 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列组成
#每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型
#表的主键可以在创建表时用PRIMARY KEY关键字指定
#整条语句以右圆括号后的分号结束

注意2：
语句格式化：
#SQL语句中可忽略空格
#语句可以在一个长行上输入，也可以分成许多行，它们并没有差别
#前面的CREATE TABLE语句就是语句格式化的一个很好的例子
#列定义进行了恰当的缩进，更便阅读和编辑
#强烈推荐采用某种缩进格式

注意3：
替换现有表：
#在创建新表时，指定的表名必须不存在，否则将出错
#防止意外覆盖已有的表， SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它

1.2 使用NULL值：

#NULL值就是没有值或缺值
#允许NULL值的列也允许在插入行时不给出该列的值
#不允许NULL值的列不接受没有值的行，即在插入或更新行时，该列必须有值
#每个表列要么是NULL列，要么是NOT NULL列，这种状态在创建时由表的定义规定

【2】CREATE TABLE orders2 ( order_num INTEGER NOT NULL, order_date datetime NOT NULL, cust_id CHAR(10) NOT NULL );
#创建orders2表
#orders2包含三列：订单号、订单日期和客户ID
#这三列都需要，因此每个列的定义都含有关键字NOT NULL
#这将会阻止插入没有值的列。如果试图插入没有值的列，将返回错误，且插入失败

【3】CREATE TABLE vendors2 ( 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) );
#创建vendors2表
#供应商ID和供应商名字列是必需的，因此指定为NOT NULL
#其余五列全都允许NULL值，所以不指定NOT NULL
#NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL

注意4：
指定NULL：
#在不指定NOT NULL时，多少DBMS认为指定的是NULL，但不是所有的DBMS都这样
#某些DBMS要求指定关键字NULL，如果不指定将出错

注意5：
主键和NULL值：
#主键是其值唯一标识表中每一行的列
#只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识
#主键用以下的类似的语句定义：primary key (vend_id)
#为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名：primary key (order_num, order_item)
#主键可以在创建表时定义， 或者在创建表之后定义

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

1.3 指定默认值：

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

【4】CREATE TABLE orderitems2 ( 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 );
#创建包含订单各项的orderitems2表（订单本身存储在orders表中）
#quantity列包含订单中每项物品的数量，该列的描述添加文本DEFAULT 1，指示DBMS（MySQL），如果不给出数量则使用数量1

#默认值经常用于日期或时间戳列
#如通过指定引用系统日期的函数或变量，将系统日期用作默认日期
#MySQL用户指定DEFAULT CURRENT_DATE()，Oracle用户指定DEFAULT SYSDATE，SQL Server用户指定DEFAULT GETDATE()

注意7：
不允许使用函数：
#与大多数DBMS不一样， MySQL不允许使用函数作为默认值，它只支持常量

注意8：
使用默认值而不是NULL值:
#许多数据库开发人员喜欢使用默认值而不是NULL列，特别是对用于计算或数据分组的列

二、更新表

#更新表定义，可使用ALTER TABLE语句
#所有的DBMS都支持ALTER TABLE，但它们所允许更新的内容差别很大
#使用ALTER TABLE时需要考虑的事情：

• 理想状态下，不要在表中包含数据时对其进行更新。
• 应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结果做大改动。
• 所有的DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及NULL、DAFAULT的使用）有所限制
• 许多DBMS不允许删除或更改表中的列
• 多数DBMS允许重新命名表中的列
• 许多DBMS限制对已填有数据的列进行更改，对未填有数据的列几乎没有限制
• 对已有表做更改既复杂又不统一
• 给已有表增加列可能是所有DBMS都支持的唯一操作

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

• 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）
• 列出要做哪些更改

vendors2表：

【5】ALTER TABLE vendors2 ADD vend_phone CHAR(20);
#给vendors表增加一个名为vend_phone的列，其数据类型为CHAR

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

#对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔

【6】ALTER TABLE vendors2 DROP COLUMN vend_phone ;

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

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

注意9：
ALTER TABLE和SQLite:
#SQLite对使用ALTER TABLE执行的操作有所限制
#SQLite不支持使用ALTER TABLE定义主键和外键，这些必须在最初创建表时指定

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

三、删除表

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

【7】DROP TABLE orders2;
#这条语句删除orders2表
#删除表没有确认，也不能撤销，执行这条语句将永久删除该表

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

四、重命名表

[1] 每个DBMS对表重命名的支持不同，对这个操作，没有严格的标准

[2]所有重命名操作的基本语法都要求指定旧表名和新表名，但存在DBMS实现差异

[3]DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句
[4]SQL Server用户使用sp_rename存储过程，SQLite用户使用ALTER TABLE语句

【8】RENAME TABLE products2 TO products3;

【9】RENAME TABLE products3 TO products4, vendors2 TO vendors3;
#可以使用逗号分隔对多个表重命名

程序代码（DBMS：MySQL）：

CREATE TABLE products2
(
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 orders2
(
order_num INTEGER NOT NULL,
order_date datetime NOT NULL,
cust_id CHAR(10) NOT NULL
);

CREATE TABLE vendors2
(
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)
);

CREATE TABLE orderitems2
(
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
);


ALTER TABLE vendors2
ADD vend_phone CHAR(20);

ALTER TABLE vendors2
DROP COLUMN vend_phone ;

DROP TABLE orders2;

RENAME TABLE products2 TO products3;

RENAME TABLE products3 TO products4,
vendors2 TO vendors3;

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译
【2】 https://blog.csdn.net/a303549861/article/details/82986926

本文地址：https://blog.csdn.net/weixin_43806252/article/details/111033094