50.表的管理

本篇来讲讲表的创建、插入数据和删除数据

‍ ‍

表的创建

‍

商品表结构

接下来我们创建一个表：商品表。结构类似这样：

商品编号	商品名称	商品种类	销售单价	进货单价	登记日期
0001	T 恤衫	衣服	1000	500	2009-9-20
0002	打孔器	办公用品	500	320	2009-9-11
0003	运动 T 恤	衣服	4000	2800
0004	菜刀	厨房用具	3000	2800	2009-9-20
0005	高压锅	厨房用具	6800	5000	2009-1-15
0006	叉子	厨房用具	500		2009-9-20
0007	擦菜板	厨房用具	880	790	2008-4-28
0008	圆珠笔	办公用品	100		2009-11-11

可以看到该表由 6 列 8 行所组成。最上面一行是数据的项目名，真正的数据是从第 2 行开始的。

接下来我们的操作是在数据库 shop 中，请读者们准备好该数据库并切换。 ‍

创建表的语法

接下来我们使用 CREATE TABLE 语句 在其中创建表。CREATE TABLE 语句的语法如下所示：

CREATE TABLE <表名>
( <列名1> <数据类型> <该列所需约束>,
  <列名2> <数据类型> <该列所需约束>,
  <列名3> <数据类型> <该列所需约束>,
  <列名4> <数据类型> <该列所需约束>,
               .
               .
               .
  <该表的约束1>, <该表的约束2>，……
);

‍ 说明：

• 该语法清楚地描述了我们要创建一个包含 < 列名 1>、< 列名 2>、……的名称为 < 表名 > 的表，非常容易理解
• 创建表的时候，只需指定这个表的结构即可，至于这个表有什么内容，得先创建表后再填充内容
• 必须要指定每一列是什么数据类型（数字，字符串还是日期等）
• 指定了列名和数据类型后，还可指定约束：例如最多存储几位的数据，是否允许为空（NOT NULL）等，也可以没有约束
• 约束可以在定义列的时候进行设置，也可以在语句的末尾进行设置。NOT NULL 约束只能以列为单位进行设置
• 一个数据库中不允许同名表，假设数据库中已经有了 Product 表，那么再次创建会报错；后期我们会学习 create if not exist 语法，在创建前先判断是否有同名表

实际上，创建一个表可以配置很多内容，这里我们用了最少的配置。

开始创建

接下来我们创建商品表（Product）：

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)
);

‍ 商品表和 Product 表列名的对应关系：

商品表中的列名	Product 表定义的列名
商品编号	product_id
商品名称	product_name
商品种类	product_type
销售单价	sale_price
进货单价	purchase_price
登记日期	regist_date

数据类型

创建 Product 表时，在列名右侧有 INTEGER 或 CHAR 关键字，是用来声明该列的数据类型的，所有的列都必须指定数据类型。

数据类型表示数据的种类，包括数字型、字符型和日期型等。每一列都不能存储与该列数据类型不符的数据。声明为整数型的列中不能存储 'abc' 这样的字符串，声明为字符型的列中也不能存储 1234 这样的数字。

数据类型的种类很多，各个 RDBMS 之间也存在很大差异。根据业务需要实际创建数据库时，一定要根据不同的 RDBMS 选用最恰当的数据类型。

但在学习 SQL 的时候，使用最基本的数据类型就足够了。四种基本数据类型：

• INTEGER 型：用来指定存储整数的列的数据类型（数字型），不能存储小数
• CHAR 型：CHAR 是 CHARACTER（字符）的缩写，表明存储的是字符串。例如 CHAR(10)​ ， CHAR(200)​ ，在括号中指定该列可以存储的字符串的最大长度。如果超过长度则存储失败。不足最大长度则会用半角空格补足
• VARCHAR 型：同 CHAR 类型一样，也是存储字符串，也可以通过括号内的数字来指定字符串长度。区别在于，varchar 类型是以可变长字符串 的形式来保存字符串的（VAR 是 VARING “可变的”，的缩写） ，即使字符数未达到最大长度，也不会用半角空格补足
• DATE 型：用来指定存储日期（年月日）的列的数据类型（日期型）。

注意：之前说的是 SQL 语句的关键字不区分大小写，表中存储的字符串还是区分大小写的。

约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。

Product 表中设置了两种约束：not null 约束和主键约束 ‍

not null 约束

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 是代表空白（无记录）的关键字 。在 NULL 之前加上了表示否定的 NOT，就是给该列设置了不能输入空白，也就是必须输入数据的约束（如果什么都不输入就会出错）。

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

主键约束

在创建 Product 表的 CREATE TABLE 语句的后面，还有下面这样的语句：

PRIMARY KEY (product_id)

这是指定 product_id 列作为主键（PRIMARY KEY）。

一个表中，有很多数据，那怎么确定某一行的数据呢？如果有两行相同的数据，那么就无法取出唯一的特定数据了（因为无法确定唯一的一行数据）

此时我们可以用主键来确定具体某一行。主键就好比一行数据的“身份证”，每一行的主键都是不一样的。这样就能确定具体的某一行了。

主键可以是一个列，也可以是多个列，一个表中只能有一个主键。 ‍

列出所有表

列出当前数据库的所有表，使用命令 show tables;​，例如查看 shop ​这个数据库下的所有表：

mysql> show tables;
+----------------+
| Tables_in_shop |
+----------------+
| product        |
+----------------+
1 row in set (0.00 sec)

‍

删除表

删除表的 SQL 语句非常简单，语法：

drop table  <表名>;

‍ 示例：删除 Product 表

drop table Product;

DROP 在英语中是“丢掉”“舍弃”的意思。

需要特别注意的是，删除的表是无法恢复的，删除需谨慎。

变更表

创建完表后，可能想要加多几列，或者修改下约束，这时无需把表删除再重新创建，只需使用变更表定义的 ALTER TABLE 语句 就可以了。 ‍

添加列

添加列的语法：

ALTER TABLE <表名> ADD COLUMN <列的定义>；

‍ 例如，在 Product 表中添加一列，product_name_pinyin （商品名称（拼音）），该列可以存储 100 位的可变长字符串。

ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

‍ MySQL 增加列的时候可以指定此列的位置。给指定位置加列需要两个关键字：FIRST 和 AFTER。

• FIRST 表示增加此列为第一个列
• AFTER 表示增加在某个列之后
• 注意没有 BEFORE 的用法，第一列可以使用 FIRST, 非第一列使用 AFTER

语法：

--语法
ALTER TABLE table_name 
ADD [COLUMN] col_name column_definition 
[ FIRST | AFTER col_name] 

‍ 特定 SQL：Oracle 和 SQL Server 中添加列不用写 COLUMN 。语法：

ALTER TABLE <表名> ADD <列名> ；

‍

删除列

删除列的语法：

ALTER TABLE <表名> DROP COLUMN <列名>；

‍ 例如，删除刚刚添加的列：

ALTER TABLE Product DROP COLUMN product_name_pinyin;

‍ 注意，ALTER 执行之后无法恢复。误添加的列可以通过 ALTER TABLE 语句删除，误删的列则不能恢复了（该列的数据会丢失）

修改表名

大部分 DBMS 都提供了修改表名的指令，例如 rename，有些数据库用的是 alter。通常在 RENAME 之后按照 <变更前的名称>、<变更后的名称>​ 的顺序来指定表的名称。

例如将 Product 表重命名为 Product2，各 DBMS 语法如下：

--MySQL
RENAME TABLE Product to Product2;

--Oracle  PostgreSQL
ALTER TABLE Product RENAME TO Product2;

--DB2
RENAME TABLE Product TO Product2;

--SQL Server
sp_rename 'Product', 'Product2';

各个数据库的语法都不尽相同，是因为标准 SQL 并没有 RENAME ，于是各个数据库便使用了各自惯用的语法。 ‍

查看表结构

有时候我们想要查看一个表的结构，例如有几列，每一列的数据类型是什么，长度限制，约束等，那么我们就可以用 DESCRIBE 关键字（也可以简写为 DESC）。语法：

DESCRIBE <表名>;

--简写：
DESC <表名>;

‍ 例如：

DESCRIBE Product;

-- 结果：
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id     | char(4)      | NO   | PRI | NULL    |       |
| product_name   | varchar(100) | NO   |     | NULL    |       |
| product_type   | varchar(32)  | NO   |     | NULL    |       |
| sale_price     | int          | YES  |     | NULL    |       |
| purchase_price | int          | YES  |     | NULL    |       |
| regist_date    | date         | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

‍

查看表信息

​show table status ​命令可以看到所有表的状态，例如使用的存储引擎，行数，数据量等（后续会深入讲解这些概念）

mysql> show table status\G;
*************************** 2. row ***************************
           Name: product
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-11-13 10:49:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

查看建表语句

有时候想看该表是用什么 SQL 创建的，就可以用该命令查看：

show create table <表名>;

-- 例如：
show create table Product;

-- 结果：
+---------+-------------+
| Table   | Create Table  |
+---------+----------+
| Product | CREATE TABLE `product` (
  `product_id` char(4) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `product_type` varchar(32) NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------+
1 row in set (0.00 sec)

添加数据

最后我们试着添加一下数据，便于我们后续学习查询数据。

添加数据的语句：

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');

至于添加数据的语法，我们后续再详细说明。 ‍ （完）