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

本文介绍如何使用 CREATE DATABASE 语句创建数据库、 CREATE TABLE 语句创建表、ALTER TABLE 语句更新表、DROP TABLE 语句删除表。

一、表的创建

本节要点

  • 表通过 CREATE TABLE 语句创建而成。

  • 表和列的命名要使用有意义的文字。

  • 指定列的数据类型(整数型、字符型和日期型等)。

  • 可以在表中设置约束(主键约束和 NOT NULL 约束等)。

1.1 表的内容的创建

我们将从 SQL SELECT WHERE 语句如何指定一个或多个查询条件 开始学习针对表的查询,以及数据变更等 SQL 语句。本节将会创建学习这些 SQL 语句所需的数据库和表。

表 2 是 数据库和 SQL 是什么关系 举例时使用的商品表。

表 2  商品表

商品编号商品名称商品种类销售单价进货单价登记日期
0001T 恤衫衣服10005002009-09-20
0002打孔器办公用品5003202009-09-11
0003运动 T 恤衣服40002800
0004菜刀厨房用具300028002009-09-20
0005高压锅厨房用具680050002009-01-15
0006叉子厨房用具5002009-09-20
0007擦菜板厨房用具8807902008-04-28
0008圆珠笔办公用品1002009-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 语句

1
CREATE DATABASE <数据库名称>;

这里我们将数据库命名为 shop,然后执行代码清单 1 中的 SQL 语句 2

代码清单 1 创建数据库 shopCREATE DATABASE 语句

1
CREATE DATABASE shop;

此外,数据库名称、表名以及列名都要使用半角字符(英文字母、数字、符号),具体内容随后会进行介绍。

1.3 表的创建(CREATE TABLE 语句)

创建好数据库之后,接下来我们使用 CREATE TABLE 语句在其中创建表。CREATE TABLE 语句的语法如下所示 3

语法 2 创建表的 CREATE TABLE 语句

1
2
3
4
5
6
7
8
9
 CREATE TABLE <表名>
<列名1> <数据类型> <该列所需约束>
  <列名2> <数据类型> <该列所需约束>
  <列名3> <数据类型> <该列所需约束>
  <列名4> <数据类型> <该列所需约束>
               .
               .
               .
  <该表的约束1> <该表的约束2>,……);

该语法清楚地描述了我们要创建一个包含 <列名 1><列名 2>、……的名称为 <表名> 的表,非常容易理解。每一列的数据类型(后述)是必须要指定的,还要为需要的列设置约束(后述)。

约束可以在定义列的时候进行设置,也可以在语句的末尾进行设置 4

在数据库中创建表 2 中的商品表(Product 表)的 CREATE TABLE 语句,如代码清单 2 所示。

代码清单 2 创建 Product 表的 CREATE TABLE 语句

1
2
3
4
5
6
7
8
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 型还包含时分秒,但在本文中我们只学习日期部分。