SQL 如何对表进行创建、更新和删除操作,一、表的创建本节要点
目录- 一、表的创建
- 1.1 表的内容的创建
- 1.2 数据库的创建(CREATE DATABASE 语句)
- 1.3 表的创建(CREATE TABLE 语句)
- 1.4 命名规则
- 1.5 数据类型的指定
- 1.6 约束的设置
- 二、表的删除和更新
- 2.1 表的删除(DROP TABLE 语句)
- 2.2 表定义的更新(ALTER TABLE 语句)
- 2.3 向 Product 表中插入数据
本文介绍如何使用 CREATE DATABASE
语句创建数据库、 CREATE TABLE
语句创建表、ALTER TABLE
语句更新表、DROP TABLE
语句删除表。
一、表的创建
本节要点
-
表通过
CREATE TABLE
语句创建而成。 -
表和列的命名要使用有意义的文字。
-
指定列的数据类型(整数型、字符型和日期型等)。
-
可以在表中设置约束(主键约束和
NOT NULL
约束等)。
1.1 表的内容的创建
我们将从 SQL SELECT WHERE 语句如何指定一个或多个查询条件 开始学习针对表的查询,以及数据变更等 SQL 语句。本节将会创建学习这些 SQL 语句所需的数据库和表。
表 2 是 数据库和 SQL 是什么关系 举例时使用的商品表。
表 2 商品表
商品编号 | 商品名称 | 商品种类 | 销售单价 | 进货单价 | 登记日期 |
---|---|---|---|---|---|
0001 | T 恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
0003 | 运动 T 恤 | 衣服 | 4000 | 2800 | |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
0006 | 叉子 | 厨房用具 | 500 | 2009-09-20 | |
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
0008 | 圆珠笔 | 办公用品 | 100 | 2009-11-11 |
该表是某家小商店销售商品的一览表。
商品的数量不多,不过我们可以把它想象成大量数据中的一部分(毕竟这只是为了学习 SQL 而创建的表)。
像 0003
号商品的登记日期以及 0006
号商品的进货单价这样的空白内容,我们可以认为是由于店主疏忽而忘记输入了。
大家可以看到表 2 由 6 列 8 行所组成。最上面一行是数据的项目名,真正的数据是从第 2 行开始的。
备忘
接下来,我们会逐步学习创建数据库和表所使用的 SQL 语句的书写方式。
还没有准备好学习环境(PostgreSQL)的读者,请按照 如何在 Windows 10 中安装 PostgreSQL 和连接设置 的内容进行准备。
1.2 数据库的创建(CREATE DATABASE 语句)
前面提到,在创建表之前,一定要先创建用来存储表的数据库。运行 CREATE DATABASE
语句就可以在 RDBMS 上创建数据库了。CREATE DATABASE
语句的语法如下所示 [1]。
语法 1 创建数据库的 CREATE DATABASE 语句
CREATE DATABASE <数据库名称>;
这里我们将数据库命名为 shop
,然后执行代码清单 1 中的 SQL 语句 [2]。
代码清单 1 创建数据库 shop
的 CREATE DATABASE
语句
CREATE DATABASE shop;
此外,数据库名称、表名以及列名都要使用半角字符(英文字母、数字、符号),具体内容随后会进行介绍。
1.3 表的创建(CREATE TABLE 语句)
创建好数据库之后,接下来我们使用 CREATE TABLE
语句在其中创建表。CREATE TABLE
语句的语法如下所示 [3]。
语法 2 创建表的 CREATE TABLE 语句
CREATE TABLE <表名>
(<列名1> <数据类型> <该列所需约束>,
<列名2> <数据类型> <该列所需约束>,
<列名3> <数据类型> <该列所需约束>,
<列名4> <数据类型> <该列所需约束>,
.
.
.
<该表的约束1>, <该表的约束2>,……);
该语法清楚地描述了我们要创建一个包含 <列名 1>
、<列名 2>
、……的名称为 <表名>
的表,非常容易理解。每一列的数据类型(后述)是必须要指定的,还要为需要的列设置约束(后述)。
约束可以在定义列的时候进行设置,也可以在语句的末尾进行设置 [4]。
在数据库中创建表 2 中的商品表(Product
表)的 CREATE TABLE
语句,如代码清单 2 所示。
代码清单 2 创建 Product 表的 CREATE TABLE 语句
CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
备忘
创建这些表的 SQL 语句可以从 https://cdn.developerastrid.com/file/sql/602-CreateTable.zip 下载。
格式为
\CreateTable\<RDBMS 名>
文件夹下的 CreateTable<表名>.sql 文件中。例如在 PostgreSQL 中创建
Product
表所使用的 SQL 语句,就保存在本文示例代码\CreateTable\PostgreSQL
文件夹下的 CreateTableProduct.sql 文件中。CreateTableProduct.sql 文件包含了创建
Product
表时用到的 SQL 语句(代码清单 2),以及向Product
表中插入数据的 SQL 语句(代码清单 6)。这样就可以在创建表的同时向表中预先插入数据了。
1.4 命名规则
我们只能使用半角英文字母、数字、下划线(_
)作为数据库、表和列的名称。例如,不能将 product_id
写成 product-id
,因为标准 SQL 并不允许使用连字符作为列名等名称。$
、#
、?
这样的符号同样不能作为名称使用。
尽管有些 RDBMS 允许使用上述符号作为列的名称,但这也仅限于在该 RDBMS 中使用,并不能保证在其他 RDBMS 中也能使用。
虽然大家可能会觉得限制有点太多了,但还是请遵守规则使用半角英文字母、数字和下划线(_
)吧。
法则 9
数据库名称、表名和列名等可以使用以下三种字符。
半角英文字母
半角数字
下划线(
_
)
此外,名称必须以半角英文字母开头。以符号开头的名称并不多见,但有时会碰到类似 1product
或者 2009_sales
这样以数字开头的名称。
虽然可以理解,但这在标准 SQL 中是被禁止的。请大家使用 product1
或者 sales_2009
这样符合规则的名称。
法则 10
名称必须以半角英文字母作为开头。
最后还有一点,在同一个数据库中不能创建两个相同名称的表,在同一个表中也不能创建两个名称相同的列。如果出现这样的情况,RDBMS 会返回错误信息。
法则 11
名称不能重复。
接下来我们根据上述规则,使用代码清单 2 中的 CREATE TABLE
语句来创建表 2 中的商品表。表名为 Product
,表中的列名如表 3 所示。
表 3 商品表和 Product 表列名的对应关系
商品表中的列名 | Product 表定义的列名 |
---|---|
商品编号 | product_id |
商品名称 | product_name |
商品种类 | product_type |
销售单价 | sale_price |
进货单价 | purchase_price |
登记日期 | regist_date |
1.5 数据类型的指定
Product
表所包含的列,定义在 CREATE TABLE Product()
的括号中。
列名右边的 INTEGER
或者 CHAR
等关键字,是用来声明该列的数据类型的,所有的列都必须指定数据类型。
数据类型表示数据的种类,包括数字型、字符型和日期型等。每一列都不能存储与该列数据类型不符的数据。
声明为整数型的列中不能存储 'abc'
这样的字符串,声明为字符型的列中也不能存储 1234
这样的数字。
数据类型的种类很多,各个 RDBMS 之间也存在很大差异。根据业务需要实际创建数据库时,一定要根据不同的 RDBMS 选用最恰当的数据类型。
在学习 SQL 的时候,使用最基本的数据类型就足够了。下面我们就来介绍四种基本的数据类型。
-
INTEGER
型用来指定存储整数的列的数据类型(数字型),不能存储小数。
-
CHAR
型CHAR
是 CHARACTER(字符)的缩写,是用来指定存储字符串的列的数据类型(字符型)。可以像
CHAR(10)
或者CHAR(200)
这样,在括号中指定该列可以存储的字符串的长度(最大长度)。字符串超出最大长度的部分是无法输入到该列中的。
RDBMS 不同,长度单位也不一样,既存在使用字符个数的情况,也存在使用字节长度 [5] 的情况。
字符串以定长字符串的形式存储在被指定为
CHAR
型的列中。所谓定长字符串,就是当列中存储的字符串长度达不到最大长度的时候,使用半角空格进行补足。
例如,我们向
CHAR(8)
类型的列中输入'abc'
的时候,会以 'abc '
(abc 后面有 5 个半角空格)的形式保存起来。另外,虽然之前我们说过 SQL 不区分英文字母的大小写,但是表中存储的字符串却是区分大小写的。也就是说,
'ABC'
和'abc'
代表了两个不同意义的字符串。 -
VARCHAR
型同
CHAR
类型一样,VARCHAR
型也是用来指定存储字符串的列的数据类型(字符串类型),也可以通过括号内的数字来指定字符串的长度(最大长度)。但该类型的列是以可变长字符串的形式来保存字符串的 [6]。
定长字符串在字符数未达到最大长度时会用半角空格补足,但可变长字符串不同,即使字符数未达到最大长度,也不会用半角空格补足。
例如,我们向
VARCHAR(8)
类型的列中输入字符串'abc'
的时候,保存的就是字符串'abc'
。该类型的列中存储的字符串也和
CHAR
类型一样,是区分大小写的。特定的 SQL
Oracle 中使用
VARCHAR2
型(Oracle 中也有VARCHAR
这种数据类型,但并不推荐使用)。 -
DATE
型用来指定存储日期(年月日)的列的数据类型(日期型)。
特定的 SQL
除了年月日之外,Oracle 中使用的
DATE
型还包含时分秒,但在本文中我们只学习日期部分。
1.6 约束的设置
约束是除了数据类型之外,对列中存储的数据进行限制或者追加条件的功能。Product
表中设置了两种约束。
Product
表的 product_id
列、product_name
列和 product_type
列的定义如下所示。
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
数据类型的右侧设置了 NOT NULL
约束。NULL
是代表空白(无记录)的关键字 [7]。
在 NULL
之前加上了表示否定的 NOT
,就是给该列设置了不能输入空白,也就是必须输入数据的约束(如果什么都不输入就会出错)。
这样一来,Product
表的 product_id
(商品编号)列、product_name
(商品名称)列和 product_type
(商品种类)列就都成了必须输入的项目。
另外,在创建 Product
表的 CREATE TABLE
语句的后面,还有下面这样的记述。
PRIMARY KEY (product_id)
这是用来给 product_id
列设置主键约束的。所谓键,就是在指定特定数据时使用的列的组合。键种类多样,主键(primary key)就是可以特定一行数据的列 [8]。
也就是说,如果把 product_id
列指定为主键,就可以通过该列取出特定的商品数据了。
反之,如果向 product_id
列中输入了重复数据,就无法取出唯一的特定数据了(因为无法确定唯一的一行数据)。这样就可以为某一列设置主键约束了。
二、表的删除和更新
本节要点
使用
DROP TABLE
语句来删除表。使用
ALTER TABLE
语句向表中添加列或者从表中删除列。
2.1 表的删除(DROP TABLE 语句)
此前介绍的都是关于 Product
表的内容的创建,下面我们就来介绍一下删除表的方法。删除表的 SQL 语句非常简单,只需要一行 DROP TABLE
语句即可。
语法 3 删除表时使用的 DROP TABLE 语句
DROP TABLE <表名>;
如果想要删除 Product
表,只需要像代码清单 3 那样书写 SQL 语句即可 [9]。
代码清单 3 删除 Product 表
DROP TABLE Product;
DROP
在英语中是“丢掉”“舍弃”的意思。需要特别注意的是,删除的表是无法恢复的 [10]。即使是被误删的表,也无法恢复,只能重新创建,然后重新插入数据。
如果不小心删除了重要的业务表,那就太悲剧了。特别是存储了大量数据的表,恢复起来费时费力,请大家务必注意!
法则 12
删除了的表是无法恢复的。
在执行
DROP TABLE
语句之前请务必仔细确认。
2.2 表定义的更新(ALTER TABLE 语句)
有时好不容易把表创建出来之后才发现少了几列,其实这时无需把表删除再重新创建,只需使用变更表定义的 ALTER TABLE
语句就可以了。
ALTER
在英语中就是“改变”的意思。下面就给大家介绍该语句通常的使用方法。
首先是添加列时使用的语法。
语法 4 添加列的 ALTER TABLE 语句
ALTER TABLE <表名> ADD COLUMN <列的定义>;
特定的 SQL
Oracle 和 SQL Server 中不用写
COLUMN
。ALTER TABLE <表名> ADD <列名> ;
另外,在 Oracle 中同时添加多列的时候,可以像下面这样使用括号。
ALTER TABLE <表名> ADD (<列名>,<列名>,……);
例如,我们可以使用代码清单 4 中的语句在 Product
表中添加这样一列,product_name_pinyin
(商品名称(拼音)),该列可以存储 100 位的可变长字符串。
代码清单 4 添加一列可以存储 100 位的可变长字符串的 product_name_pinyin
列
DB2 PostgreSQL MySQL
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);
反之,删除表中某列使用的语法如下所示。
语法 5 删除列的 ALTER TABLE 语句
ALTER TABLE <表名> DROP COLUMN <列名>;
特定的 SQL
Oracle 中不用写
COLUMN
。ALTER TABLE <表名> DROP <列名> ;
另外,在 Oracle 中同时删除多列的时候,可以像下面这样使用括号来实现。
ALTER TABLE <表名> DROP (<列名>,<列名>,……);
例如,我们可以使用代码清单 5 中的语句来删除之前添加的 product_name_pinyin
列。
代码清单 5 删除 product_name_pinyin 列
SQL Server DB2 PostgreSQL MySQL
ALTER TABLE Product DROP COLUMN product_name_pinyin;
Oracle
ALTER TABLE Product DROP (product_name_pinyin);
ALTER TABLE
语句和 DROP TABLE
语句一样,执行之后无法恢复。误添的列可以通过 ALTER TABLE
语句删除,或者将表全部删除之后重新再创建。
法则 13
表定义变更之后无法恢复。
在执行
ALTER TABLE
语句之前请务必仔细确认。
2.3 向 Product 表中插入数据
最后让我们来尝试一下向表中插入数据。从 SQL SELECT WHERE 语句如何指定一个或多个查询条件 开始,大家将会使用插入到 Product
表中的数据,来学习如何编写操作数据的 SQL 语句。
向 Product
表中插入数据的 SQL 语句如代码清单 6 所示。
代码清单 6 向 Product 表中插入数据的 SQL 语句
SQL Server PostgreSQL
-- DML :插入数据
BEGIN TRANSACTION;------------------①
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;
特定的 SQL
DBMS 不同,代码清单 6 中的 DML 语句也略有不同。
在 MySQL 中运行时,需要把 ① 中的
BEGIN TRANSACTION;
改写成START TRANSACTION;
在 Oracle 和 DB2 中运行时,无需使用 ① 中的
BEGIN TRANSACTION;
(请予以删除)。
使用插入行的指令语句 INSERT
,就可以把表 2 中的数据都插入到表中了。
开头的 BEGIN TRANSACTION
语句是开始插入行的指令语句,结尾的 COMMIT
语句是确定插入行的指令语句。
这些指令语句将会在 什么是 SQL 事务 详细介绍,大家不必急于记住这些语句。
专栏
表的修改
本节将名为
Product
的表作为例子进行了讲解,估计会有些读者在匆忙中把表名误写成了Poduct
,创建出了名称错误的表,这可怎么办呢?如果还没有向表中插入数据,那么只需要把表删除,再重新创建一个名称正确的表就可以了。
可是如果在发现表名错误之前就已经向表中插入了大量数据,再这样做就麻烦了。毕竟插入大量的数据既费时又费力。
抑或起初决定好的表名,之后又觉得不好想换掉,这种情况也很麻烦。
其实很多数据库都提供了可以修改表名的指令(
RENAME
)来解决这样的问题。例如,如果想把
Poduct
表的名称变为Product
,可以使用代码清单 A 中的指令。代码清单 A 变更表名
Oracle PostgreSQL
ALTER TABLE Poduct RENAME TO Product;
DB2
RENAME TABLE Poduct TO Product;
SQL Server
sp_rename 'Poduct', 'Product';
MySQL
RENAME TABLE Poduct to Product;
通常在
RENAME
之后按照<变更前的名称>
、<变更后的名称>
的顺序来指定表的名称。各个数据库的语法都不尽相同,是因为标准 SQL 并没有
RENAME
,于是各个数据库便使用了各自惯用的语法。如上所述,在创建了错误的表名,或者想要保存表的备份时,使用这些语句非常方便。
但美中不足的是,由于各个数据库的语法不同,很难一下子想出恰当的指令。这时大家就可以来参考本专栏。
原文链接:https://www.developerastrid.com/sql/sql-create-alter-drop-table/
(完)
这里我们仅指定了使用该语法所需的最少项目,实际开发数据库时还需要指定各种其他项目。 ↩︎
如何在 Windows 10 中安装 PostgreSQL 和连接设置 中介绍了在 PostgreSQL 中运行 SQL 语句的方法。执行了 如何在 Windows 10 中安装 PostgreSQL 和连接设置 内容的读者应该已经创建好了名为 shop 的数据库。接下来请继续完成创建表的工作。 ↩︎
这里我们仅指定了使用该语法所需的最少项目,实际开发数据库时还需要指定各种其他项目。 ↩︎
但是
NOT NULL
约束只能以列为单位进行设置。 ↩︎字节是计算机内部的数据单位。一个字符通常需要 1 到 3 个字节来表示(根据字符的种类和表现方式有所不同)。 ↩︎
VARCHAR
中的 VAR 是 VARING(可变的)的缩写。 ↩︎NULL
这个词是无或空的意思,NULL
是使用 SQL 时的常见关键字,请大家牢记。 ↩︎特定一行数据,也可以说是唯一确定一行数据。 ↩︎
随后还需使用
Product
表来学习相关知识,请不要删除Product
表。如果已经删除,请重新创建Product
表。 ↩︎其实很多 RDBMS 都预留了恢复的功能,但还是请大家认为是无法恢复的。 ↩︎