创建和管理普通表
创建表
表是建立在数据库中的，在不同的数据库中可以存放相同的表。甚至可以通过使用模式在同一个数据库中创建相同名称的表。创建表前请先规划存储模型。
- 创建普通表，其中c_customer_sk 、c_customer_id、c_first_name和c_last_name是表的字段名，integer、char(5)、char(6)和char(8)分别是这四个字段的类型。
CREATE TABLE customer_t1 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); - 删除表。
DROP TABLE customer_t1;
向表中插入数据
在创建一个表后，表中并没有数据，在使用这个表之前，需要向表中插入数据。本小节介绍如何使用 INSERT 命令插入一行或多行数据，及从指定表插入数据。
背景信息
服务端与客户端使用不同的字符集时，两者字符集中单个字符的长度也会不同，客户端输入的字符串会以服务端字符集的格式进行处理，所以产生的最终结果可能会与预期不一致。
表1 客户端和服务端设置字符集的输出结果对比
| 操作过程 | 服务端和客户端编码一致 | 服务端和客户端编码不一致 |
|---|---|---|
| 存入和取出过程中没有对字符串进行操作 | 输出预期结果 | 输出预期结果（输入与显示的客户端编码必须一致）。 |
| 存入取出过程对字符串有做一定的操作（如字符串函数操作） | 输出预期结果 | 根据对字符串具体操作可能产生非预期结果。 |
| 存入过程中对超长字符串有截断处理 | 输出预期结果 | 字符集中字符编码长度是否一致，如果不一致可能会产生非预期的结果。 |
上述字符串函数操作和自动截断产生的效果会有叠加效果，例如：在客户端与服务端字符集不一致的场景下，如果既有字符串操作，又有字符串截断，在字符串被处理完以后的情况下继续截断，这样也会产生非预期的效果。详细的示例请参见表2。
CREATE TABLE table1(id int, a char(6), b varchar(6),c varchar(6));
CREATE TABLE table2(id int, a char(20), b varchar(20),c varchar(20));
| 编号 | 服务端字符集 | 客户端字符集 | 是否启用自动截断 | 示例 | 结果 | 说明 |
|---|---|---|---|---|---|---|
| 1 | SQL_ASCII | UTF8 | 是 | autoINSERT INTO table1 VALUES(1,reverse('123ＡＡ78'),reverse('123ＡＡ78'),reverse('123ＡＡ78')); | autoid |a|b|c ----+------+------+------ 1 | 87| 87| 87 |
字符串在服务端翻转后，并进行截断，由于服务端和客户端的字符集不一致，字符 A 在客户端由多个字节表示，结果产生异常。 |
| 2 | SQL_ASCII | UTF8 | 是 | autoINSERT INTO table1 VALUES(2,reverse('123Ａ78'),reverse('123Ａ78'),reverse('123Ａ78')); | autoid |a|b|c ----+------+------+------ 2 | 873| 873| 873 |
字符串翻转后，又进行了自动截断，所以产生了非预期的效果。 |
| 3 | SQL_ASCII | UTF8 | 是 | autoINSERT INTO table1 VALUES(3,'87Ａ123','87Ａ123','87Ａ123'); | autoid | a | b | c ----+-------+-------+------- 3 | 87Ａ1 | 87Ａ1 | 87Ａ1 |
字符串类型的字段长度是客户端字符编码长度的整数倍，所以截断后产生结果正常。 |
| 4 | SQL_ASCII | UTF8 | 否 | autoINSERT INTO table2 VALUES(1,reverse('123ＡＡ78'),reverse('123ＡＡ78'),reverse('123ＡＡ78')); INSERT INTO table2 VALUES(2,reverse('123Ａ78'),reverse('123Ａ78'),reverse('123Ａ78')); |
autoid |a|b|c ----+-------------------+--------+-------- 1 | 87 321| 87 321 | 87 321 2 | 87321| 87321| 87321 |
与示例1类似，多字节字符翻转之后不再表示原来的字符。 |
操作步骤
- 创建测试表。
CREATE TABLE test_a4740 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); - 向表test_a4740中插入一行：
数据值是按照这些字段在表中出现的顺序列出的，并且用逗号分隔。通常数据值是文本（常量），但也允许使用标量表达式。INSERT INTO test_a4740(c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', 'Grace');
如果用户已经知道表中字段的顺序，也可无需列出表中的字段。例如以下命令与上面的命令效果相同。INSERT INTO test_a4740 VALUES (3769, 'hello', 'Grace');
如果用户不知道所有字段的数值，可以忽略其中的一些。没有数值的字段将被填充为字段的缺省值。例如：INSERT INTO test_a4740 (c_customer_sk, c_first_name) VALUES (3769, 'Grace'); INSERT INTO test_a4740 VALUES (3769, 'hello');
用户也可以对独立的字段或者整个行明确缺省值：INSERT INTO test_a4740 (c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', DEFAULT); INSERT INTO test_a4740 DEFAULT VALUES; - 如果需要在表中插入多行，请使用以下命令：
INSERT INTO test_a4740 (c_customer_sk, c_customer_id, c_first_name) VALUES (6885, 'maps', 'Joes'), (4321, 'tpcds', 'Lily'), (9527, 'world', 'James');
如果需要向表中插入多条数据，除此命令外，也可以多次执行插入一行数据命令实现。但是建议使用此命令可以提升效率。 - 如果从指定表插入数据到当前表，例如在数据库中创建了一个表test_a4740的备份表test_a4740_1，现在需要将表test_a4740中的数据插入到表test_a4740_1中，则可以执行如下命令。
CREATE TABLE test_a4740_1 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); INSERT INTO test_a4740_1 SELECT * FROM test_a4740;
从指定表插入数据到当前表时，若指定表与当前表对应的字段数据类型之间不存在隐式转换，则这两种数据类型必须相同。 - 清理环境。
DROP TABLE test_a4740_1 CASCADE; DROP TABLE test_a4740 CASCADE;
在删除表的时候，若当前需删除的表与其他表有依赖关系，需先删除关联的表，然后再删除当前表。
更新表中数据
修改已经存储在数据库中数据的行为叫做更新。用户可以更新单独一行、所有行或者指定的部分行。还可以独立更新某个字段，而其它字段则不受影响。
使用 UPDATE 命令更新现有行，需要提供以下三种信息：
- 表的名称和要更新的字段名
- 字段的新值
- 要更新哪些行
SQL通常不会为数据行提供唯一标识，因此无法直接声明需要更新哪一行。但是可以通过声明一个被更新的行必须满足的条件来更新数据行。只有在表里存在主键的时候，才可以通过主键指定一个独立的行。
- 执行如下命令创建表并插入数据：
CREATE TABLE customer_t1 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (6885, 'maps', 'Joes'), (4321, 'tpcds', 'Lily'), (9527, 'world', 'James'); - 将表customer_t1中c_customer_sk为9527的地域重新定义为9876：
UPDATE customer_t1 SET c_customer_sk = 9876 WHERE c_customer_sk = 9527;
这里的表名称也可以使用模式名修饰，否则会从默认的模式路径找到这个表。SET后面紧跟字段和新的字段值。新的字段值不仅可以是常量，也可以是变量表达式。 - 把所有c_customer_sk的值增加100：
UPDATE customer_t1 SET c_customer_sk = c_customer_sk + 100;
在这里省略了WHERE子句，表示表中的所有行都要被更新。如果出现了WHERE子句，那么只有匹配其条件的行才会被更新。
在SET子句中的等号是一个赋值，而在WHERE子句中的等号是比较。WHERE条件不一定是相等测试，许多其他的操作符也可以使用。 - 在一个UPDATE命令中更新更多的字段，方法是在SET子句中列出更多赋值，比如：
UPDATE customer_t1 SET c_customer_id = 'Admin', c_first_name = 'Local' WHERE c_customer_sk = 4421;
批量更新或删除数据后，会在数据文件中产生大量的删除标记，查询过程中标记删除的数据也是需要扫描的。故多次批量更新/删除后，标记删除的数据量过大会严重影响查询的性能。建议在批量更新/删除业务会反复执行的场景下，定期执行VACUUM FULL以保持查询性能。 - 清理环境。
DROP TABLE customer_t1 CASCADE;
查看数据
- 执行如下命令创建表并插入数据：
CREATE TABLE customer_t1 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (6885, 'maps', 'Joes'), (4321, 'tpcds', 'Lily'), (9527, 'world', 'James'); - 执行以下操作。
- 使用系统表pg_tables查询数据库所有表的信息。
SELECT * FROM pg_tables; - 使用vsql的\d+命令查询表的属性。
\d+ customer_t1; - 执行如下命令查询表customer_t1的数据量。
SELECT count(*) FROM customer_t1; - 执行如下命令查询表customer_t1的所有数据。
SELECT * FROM customer_t1; - 执行如下命令只查询字段c_customer_sk的数据。
SELECT c_customer_sk FROM customer_t1; - 执行如下命令过滤字段c_customer_sk的重复数据。
SELECT DISTINCT( c_customer_sk ) FROM customer_t1; - 执行如下命令查询字段c_customer_sk为3869的所有数据。
SELECT * FROM customer_t1 WHERE c_customer_sk = 3869; - 执行如下命令按照字段c_customer_sk进行排序。
SELECT * FROM customer_t1 ORDER BY c_customer_sk;
- 使用系统表pg_tables查询数据库所有表的信息。
- 清理环境。
DROP TABLE customer_t1 CASCADE;
删除表中数据
在使用表的过程中，可能会需要删除已过期的数据，删除数据必须从表中整行的删除。
SQL不能直接访问独立的行，只能通过声明被删除行匹配的条件进行。如果表中有一个主键，用户可以指定准确的行。用户可以删除匹配条件的一组行或者一次删除表中的所有行。
- 使用DELETE命令删除行，如果删除表table_name中所有col_name字段的值为value的记录：
DELETE FROM table_name WHERE col_name = value; - 如果执行如下命令之一，会删除table_name表中所有的行。
DELETE FROM table_name;
或：TRUNCATE TABLE table_name;
说明全表删除的场景下，建议使用TRUNCATE，不建议使用DELETE。
- 删除创建的表table_name：
DROP TABLE table_name;
