【openGauss笔记】SQL语法-2 表的基本操作

2. 表模型定义

2.1 创建表 - CREATE

在关系模型中，每个关系是一个数据实体，在SQL中可以通过CREATE TABLE命令创建一个基本表来代表一个“关系”，具体语句如下：

CREATE TABLES 表名 (
	列名 列数据类型,
	列名 列数据类型,
	......	
)

例2-1 创建一个包含仓库信息的基本表：

CREATE TABLE warehouse (
	w_id SMALLINT,
	w_name VARCHAR(10),
	w_street_1 VARCHAR(20),
	w_street_2 VARCHAR(20),
	w_city VARCHAR(20),
	w_state CHAR(2),
	w_zip CHAR(9),
	w_tax DECIMAL(4,2),
	w_ytd DECIMAL(12,2)
);

其中CREATE TABLE创建一个保存仓库信息的基本表，warehouse是要创建的基本表的名称，warehouse基本表中有9个列（属性），每个列都有自己固有的数据类型，可以根据列的要求指定其对应的长度、精度等信息，例如w_id是仓库的编号信息，通过SAMALLINT类型表示编号，而w_name是仓库的名称，为VARCHAR类型，其最大长度是10。

warehouse基本表建立之后，在数据库内会建立一个模式，DML语句、DQL语句会根据这个模式来访问warehouse表中的数据。

基本表的数据组织形式：

2.2 修改表 - ALTER TABLE

在基本表创建之后，还可以通过ALTER TABLE语句来修改基本表的模式，可以增加新的列、删除已有的列、修改列的类型等。

例2-2 在warehouse基本表中增加一个mgr_id（管理员编号）的列。

ALTER TABLE warehouse ADD COLUMN mgr_id INTEGER;

如果基本表中已经存在数据，那么在增加了新的列之后，默认会将这个列中的值指定为NULL。

2.3 删除列 - ALTER TABLE…DROP COLUMN…

如果要删除基本表中的某个列，则可以使用ALTER TABLE…DROP COLUMN…语句实现。

例2-3 在warehouse基本表中删除管理员编号的列。

ALTER TABLE warehouse DROP COLUMN mgr_id;

2.4 修改列类型 - ALTER TABLE…ALTER COLUMN…

如果要修改基本表中某个列的类型，则可以通过ALTER TABLE…ALTER COLUMN…语句实现。

例2-4 修改warehouse基本表中w_id列的类型。

ALTER TABLE warehouse ALTER COLUMN w_id TYPE INTEGER;

修改列的数据类型时会导致基本表中的数据类型同时被强制转换类型，因此需要数据库本身支持转换前的数据类型和转换后的数据类型满足“类型兼容。

如果将warehouse基本表中的w_city列转换为INTEGER类型，由于w_city列本身是字符串类型（且字符串内容为非数值型字符），这种转换有可能是无法正常进行的。

2.5 删除表 - DROP TABLE

如果一个基本表已经没有用了，则可以通过DROP TABLE语句将其删除。

例2-5 删除warehouse基本表。

DROP TABLE warehouse;

基本表的删除分为两种模式：RESTRICTED模式和CASCADE模式。

如果没有指定具体的模式，则使用默认的RESTRICTED模式，该模式只尝试删除基本表本身，如果基本表上有依赖项，例如视图、触发器、外键等，那么删除不成功。

而CASCADE模式下，会同时删除基本表相关的所有依赖项。

例2-6 以CASCADE模式删除warehouse基本表，删除基本表的同时视图也会被删除。

CREATE VIEW warehouse_view AS SELECT * FROM warehouse;
DROP TABLE warehouse CASCADE;

3. 数据完整性检查

关系模型的数据完整性主要是为了保证数据不会被破坏，具体可以分为域完整性、实体完整性、参照完整性和用户定义完整性，其中用户定义完整性是指用户在具体的应用环境下对数据库提出的约束要求。

在创建基本表的同时，还可以指定表中数据完整性约束，例如在创建warehouse基本表时，通过分析可以得到如下结论：

• 不同的仓库必须有不同的w_id，且w_id不能为NULL。
• 仓库必须有具体的名称，不能为NULL。
• 仓库所在的街区地址的长度不能为0。
• 仓库所在的国家默认为'CN'。

例2-7 创建带有完整性约束的基本表。

CREATE TABLE warehouse (
	w_id SMALLINT PRIMARY KEY,
	w_name VARCHAR(10) NOT NULL,
	w_street_1 VARCHAR(20) CHECK(LENGTH(w_street_1) <> 0),
	w_street_2 VARCHAR(20) CHECK(LENGTH(w_street_2) <> 0),
	w_city VARCHAR(20),
	w_state CHAR(2) DEFAULT 'CN',
	w_zip CHAR(9),
	w_tax DECIMAL(4,2),
	w_ytd DECIMAL(12,2)
);

如果向warehouse基本表中写入不符合完整性约束的值，那么数据不能被写入，数据库会提示错误。

例2-8： 向w_name列中写入NULL值，不符合完整性约束，写入数据时会报错，数据写入不成功。具体语句如下：

INSERT INTO warehouse VALUES(1, NULL, '', '', NULL, 'CN', NULL, 1.0, 1.0);
ERROR:  null value in column "w_name" violates not-null constraint
DETAIL:  Failing row contains (1, null, , , null, CN, null, 1.00, 1.00).

除了在列定义之后指定完整性约束之外，还可以使用表级的完整性约束来指定。

例2-9： 在表定义上指定完整性约束，注意NULL约束只能在列定义上指定。具体语句如下：

CREATE TABLE warehouse (
	w_id SMALLINT,
	w_name VARCHAR(10) NOT NULL,	# 设置NULL约束
	w_street_1 VARCHAR(20),
	w_street_2 VARCHAR(20),
	w_city VARCHAR(20),
	w_state CHAR(2) DEFAULT 'CN',	# 设置默认值
	w_zip CHAR(9),
	w_tax DECIMAL(4,2),
	w_ytd DECIMAL(12,2).
	CONSTRAINT w_id_pkey PRIMArY KEY(w_id),	# 增加主键约束
	CONSTRAINT w_street_1 CHECK(LENGTH(w_street_1) < 100),	# 增加CHECK约束
	CONSTRAINT w_street_2 CHECK(LENGTH(w_street_2) < 100),	# 增加CHECK约束
);

当一个表中的某一列或多列恰好引用的是另一个表的主键（或具有唯一性）时，可以考虑将其定义为外键，外键表示两个表之间相互的关联关系，包含主键的表通常可以称为主表，而包含外键的表则可以称为从表。外键的定义可以直接在属性上定义，也可以在基本表的创建语句中定义，两种方法本质上没有区别。

例2-10： 在新建订单表（new_orders）中引用了仓库表（warehouse）的列作为外键。具体语句如下：

CREATE TABLE new_orders
(
	no_o_id INTEGER NOT NULL,
	no_d_id SMALLINT NOT NULL,
	no_w_id SMALLINT NOT NULL REFERENCES warehouse(w_id)
);

除了在创建基本表的同时指定完整性约束之外，还可以通过ALTER TABLE语句对完整性约束进行修改。

例2-11： 在基本表warehouse上增加主键列。

ALTER TABLE warehouse ADD PRIMARY KEY (w_id);

例2-12： 在基本表warehouse上增加CHECK约束。

ALTER TABLE warehouse ADD CHECK(LENGTH(w_street_1) < 100);

例2-13： 在基本表warehouse上增加外键引用。

ALTER TABLE warehouse ADD FOREIGN KEY(no_w_id) REFERENCES warehouse(w_id);

例2-14： 在基本表new_orders上增加唯一列。

ALTER TABLE new_orders ADD UNIQUE(no_o_id, no_d_id, no_w_id);

4. 插入、删除、更新数据

基本表创建之后是一个空的集合，这时就可以对基本表做DML操作，如插入、删除以及更新基本表中的数据。

例2-15： 向new_orders基本表中插入数据。

INSERT INTO new_orders VALUES(1,1,1);
INSERT INTO new_orders VALUES(2,2,2);

例2-16： 删除new_orders基本表中no_o_id=3的元组。

INSERT INTO new_orders VALUES(3,3,3);
DELETE FROM new_orders WHERE no_o_id = 3;

例2-17： 更新new_orders基本表中的no_w_id列的值为3。

UPDATE new_orders SET no_w_id = 3 WHERE no_o_id = 2;

5. 简单查询

最基本的SQL查询结构通常由SELECT、FROM、WHERE构成，其中包含了关系代数中的投影（Projection）、选择（Selection）和连接（Join）。

SELECT projection FROM join WHERE selection;

• 连接（Join）可以由一个基本表构成，也可以是多个基本表的连接结果.
• 选择（Selection）操作是一组针对连接操作产生的结果的表达式，这些表达式为BOOL类型，它们对连接产生的结果做过滤，过滤之后的元组会组成新的中间关系.
• 最后由投影（Projection）操作输出。

例2-18： 获得warehouse基本表中的数据

SELECT w_name FROM warehouse WHERE w_id = 1;

对应的关系代数表达式：