SQL如何对表进行创建、更新和删除操作

  • Z时代
  • 2024-01-10
  • 分类：综合

database

目录

  • 一、表的创建
    • 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 语句，就保存在本文示例代码 CreateTablePostgreSQL 文件夹下的 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/

（完）

  1. 这里我们仅指定了使用该语法所需的最少项目，实际开发数据库时还需要指定各种其他项目。 ↩︎

  2. 如何在 Windows 10 中安装 PostgreSQL 和连接设置 中介绍了在 PostgreSQL 中运行 SQL 语句的方法。执行了 如何在 Windows 10 中安装 PostgreSQL 和连接设置 内容的读者应该已经创建好了名为 shop 的数据库。接下来请继续完成创建表的工作。 ↩︎

  3. 这里我们仅指定了使用该语法所需的最少项目，实际开发数据库时还需要指定各种其他项目。 ↩︎

  4. 但是 NOT NULL 约束只能以列为单位进行设置。 ↩︎

  5. 字节是计算机内部的数据单位。一个字符通常需要 1 到 3 个字节来表示（根据字符的种类和表现方式有所不同）。 ↩︎

  6. VARCHAR 中的 VAR 是 VARING（可变的）的缩写。 ↩︎

  7. NULL 这个词是无或空的意思，NULL 是使用 SQL 时的常见关键字，请大家牢记。 ↩︎

  8. 特定一行数据，也可以说是唯一确定一行数据。 ↩︎

  9. 随后还需使用 Product 表来学习相关知识，请不要删除 Product 表。如果已经删除，请重新创建 Product 表。 ↩︎

  10. 其实很多 RDBMS 都预留了恢复的功能，但还是请大家认为是无法恢复的。 ↩︎

以上是 SQL如何对表进行创建、更新和删除操作 的全部内容， 来源链接： utcz.com/z/536571.html

回到顶部