《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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐