在上一篇openGauss数据库源码解析系列文章——openGauss开发快速入门（上）中，我们介绍了openGauss的安装部署方法，本篇将具体介绍openGauss基本使用。
二、 openGauss基本使用
（一） 连接数据库
1. 确认连接信息
操作步骤：
（1） 以操作系统用户omm登录数据库主节点。
（2） 使用“gs_om -t status --detail”命令查询openGauss各实例情况。
gs_om -t status --detail
查询结果如下。
[ DBnode State ]node node_ip instance state-----------------------------------------------------------------------------1 plat1 192.168.0.11 5001 srv/BigData/gaussdb/data1/dbnode Normal
如上所示，部署了数据库主节点实例的服务器IP地址为192.168.10.11。数据库主节点数据路径“/srv/BigData/gaussdb/data1/dbnode”。
（3） 确认数据库主节点的端口号。
在（2）中查到的数据库主节点数据路径下的postgresql.conf文件中查看端口号信息。示例如下。
cat srv/BigData/gaussdb/data1/dbnode/postgresql.conf | grep port
查询结果如下，8000为数据库主节点的端口号。
port = 8000 # (change requires restart)#comm_sctp_port = 1024 # Assigned by installation (change requires restart)#comm_control_port = 10001 # Assigned by installation (change requires restart)# supported by the operating system:# e.g. 'localhost=10.145.130.2 localport=12211 remotehost=10.145.130.3 remoteport=12212, localhost=10.145.133.2 localport=12213 remotehost=10.145.133.3 remoteport=12214'# e.g. 'localhost=10.145.130.2 localport=12311 remotehost=10.145.130.4 remoteport=12312, localhost=10.145.133.2 localport=12313 remotehost=10.145.133.4 remoteport=12314'# %r = remote host and portalarm_report_interval = 10support_extended_features=true
2. 使用gsql本地连接
gsql是openGauss提供的在命令行下运行的数据库连接工具。此工具除了具备操作数据库的基本功能，还提供了若干高级特性，便于用户使用。本节只介绍如何使用gsql本地连接数据库。
1） 注意事项：
缺省情况下，客户端连接数据库后处于空闲状态时会根据参数“session_timeout”的默认值自动断开连接。如果要关闭超时设置，设置参数“session_timeout”为0即可。
2） 前提条件：
已确认连接信息，获取数据库主节点端口号信息。
3） 操作步骤：
（1）以操作系统用户omm登录数据库主节点。
（2）连接数据库。数据库安装完成后默认生成名称为postgres的数据库，第一次连接数据库时可以连接到此数据库。执行如下命令连接数据库。
gsql -d postgres -p 8000
其中postgres为需要连接的数据库名称，8000为数据库主节点的端口号。请根据实际情况替换。
连接成功后，系统显示类似如下信息。
gsql ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.postgres=#
omm用户是管理员用户，因此系统显示“postgres=#”。若使用普通用户身份登录和连接数据库，系统显示“postgres=>”。
（3）首次登录需要修改密码。原始密码为安装openGauss数据库时手动输入的密码，此处需将原始密码修改为自定义的密码，例如Mypwd123，命令如下。
postgres=# ALTER ROLE omm IDENTIFIED BY 'Mypwd123' REPLACE 'XuanYuan@2012';
（4） 退出数据库。
postgres=# \q
（二） 使用数据库
1. 从这里开始
（1）以操作系统用户omm登录数据库主节点。
（2）执行如下命令连接数据库。
gsql -d postgres -p 8000
gsql ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131）Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.postgres=#
（3） 创建数据库用户。
默认只有openGauss安装时创建的管理员用户可以访问初始数据库，您还可以创建其他数据库用户账号访问数据库。
postgres=# CREATE USER joe WITH PASSWORD "Bigdata@123";
当结果显示为如下信息，则表示创建成功。
CREATE ROLE
（4） 创建数据库。
postgres=# CREATE DATABASE db_tpcc OWNER joe;
当结果显示为如下信息，则表示创建成功。
CREATE DATABASE
db_tpcc数据库创建完成后，就可以按如下方法退出postgres数据库，使用新用户joe连接db_tpcc数据库执行创建表等操作。您也可以选择继续在默认的postgres数据库下进行后续的体验。
postgres=# \qgsql -d db_tpcc -p 8000 -U joe -W Bigdata@123gsql ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131)Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.db_tpcc=>
（5） 创建schema。
db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe;
当结果显示为如下信息，则表示创建schema成功。
CREATE SCHEMA
① 创建一个名称为mytable，只有一列的表。字段名为firstcol，字段类型为integer。
db_tpcc=> CREATE TABLE mytable (firstcol int);
当结果显示为如下信息，则表示表创建成功。
CREATE TABLE
② 向表中插入数据：
db_tpcc=> INSERT INTO mytable values (100);
当结果显示为如下信息，则表示插入数据成功。
INSERT 0 1
③ 查看表中数据：
b_tpcc=> SELECT * FROM mytable;firstcol----------100(1 row)
在openGuass中拥有多种对象进行数据管理，例如表、数据库。用户可以通过创建表存储数据，查询表检索数据。也可以通过创建数据库进行数据隔离。本节为您介绍openGauss中如何创建数据库、表以及查看数据库中的对象。
1） 创建数据库
创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
（1）注意事项：
（2）语法格式：
CREATE DATABASE database_name[ [ WITH ] { [ OWNER [=] user_name ] |[ TEMPLATE [=] template ] |[ ENCODING [=] encoding ] |[ LC_COLLATE [=] lc_collate ] |[ LC_CTYPE [=] lc_ctype ] |[ DBCOMPATIBILITY [=] compatibilty_type ] |[ TABLESPACE [=] tablespace_name ] |[ CONNECTION LIMIT [=] connlimit ]}[...] ];
（3） 示例：
--创建jim和tom用户。postgres=# CREATE USER jim PASSWORD 'Bigdata@123';postgres=# CREATE USER tom PASSWORD 'Bigdata@123';--创建一个GBK编码的数据库music（本地环境的编码格式必须也为GBK）。postgres=# CREATE DATABASE music ENCODING 'GBK' template = template0;--创建数据库music2，并指定所有者为jim。postgres=# CREATE DATABASE music2 OWNER jim;--用模板template0创建数据库music3，并指定所有者为jim。postgres=# CREATE DATABASE music3 OWNER jim TEMPLATE template0;--设置music数据库的连接数为10。postgres=# ALTER DATABASE music CONNECTION LIMIT= 10;--将music名称改为music4。postgres=# ALTER DATABASE music RENAME TO music4;--将数据库music2的所属者改为tom。postgres=# ALTER DATABASE music2 OWNER TO tom;--设置music3的表空间为PG_DEFAULT。postgres=# ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;--关闭在数据库music3上缺省的索引扫描。postgres=# ALTER DATABASE music3 SET enable_indexscan TO off;--重置enable_indexscan参数。postgres=# ALTER DATABASE music3 RESET enable_indexscan;--删除数据库。postgres=# DROP DATABASE music2;postgres=# DROP DATABASE music3;postgres=# DROP DATABASE music4;--删除jim和tom用户。postgres=# DROP USER jim;postgres=# DROP USER tom;--创建兼容TD格式的数据库。postgres=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'C';--创建兼容ORA格式的数据库。postgres=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';--删除兼容TD、ORA格式的数据库。postgres=# DROP DATABASE td_compatible_db;postgres=# DROP DATABASE ora_compatible_db;
创建表时，如未指定表的存储方式，默认创建的是行存储表；如未指定分布列时，取表的主键列（如果有的话）或首个可以作为分布列的列。
（1） 语法格式：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]| table_constraint| LIKE source_table [ like_option [...] ] }[, ... ])[ WITH ( {storage_parameter = value} [, ... ] ) ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ COMPRESS | NOCOMPRESS ][ TABLESPACE tablespace_name ];
其中列约束column_constraint为：
[ CONSTRAINT constraint_name ]{ NOT NULL |NULL |CHECK ( expression ) |DEFAULT default_expr |UNIQUE index_parameters |PRIMARY KEY index_parameters }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
其中列的压缩可选项compress_mode为：
{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
其中表约束table_constraint为：
[ CONSTRAINT constraint_name ]{ CHECK ( expression ) |UNIQUE ( column_name [, ... ] ) index_parameters |PRIMARY KEY ( column_name [, ... ] ) index_parameters |PARTIAL CLUSTER KEY ( column_name [, ... ] ) }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
其中like选项like_option为：
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }
其中索引参数index_parameters为：
[ WITH ( {storage_parameter = value} [, ... ] ) ][ USING INDEX TABLESPACE tablespace_name ]
（2）示例
--创建简单的表。postgres=# CREATE TABLE tpcds.warehouse_t1(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));postgres=# CREATE TABLE tpcds.warehouse_t2(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) DICTIONARY,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建表，并指定W_STATE字段的缺省值为GA。postgres=# CREATE TABLE tpcds.warehouse_t3(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) DEFAULT 'GA',W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建表，并在事务结束时检查W_WAREHOUSE_NAME字段是否有重复。postgres=# CREATE TABLE tpcds.warehouse_t4(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) UNIQUE DEFERRABLE,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建一个带有70%填充因子的表。postgres=# CREATE TABLE tpcds.warehouse_t5(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2),UNIQUE(W_WAREHOUSE_NAME) WITH(fillfactor=70));--或者用下面的语法。postgres=# CREATE TABLE tpcds.warehouse_t6(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) UNIQUE,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2)) WITH(fillfactor=70);--创建表，并指定该表数据不写入预写日志。postgres=# CREATE UNLOGGED TABLE tpcds.warehouse_t7(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建表临时表。postgres=# CREATE TEMPORARY TABLE warehouse_t24(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建本地临时表，并指定提交事务时删除该临时表数据。postgres=# CREATE TEMPORARY TABLE warehouse_t25(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2)) ON COMMIT DELETE ROWS;--创建全局临时表，并指定会话结束时删除该临时表数据。postgres=# CREATE GLOBAL TEMPORARY TABLE gtt1(ID INTEGER NOT NULL,NAME CHAR(16) NOT NULL,ADDRESS VARCHAR(50) ,POSTCODE CHAR(6)) ON COMMIT PRESERVE ROWS;--创建表时，不希望因为表已存在而报错。postgres=# CREATE TABLE IF NOT EXISTS tpcds.warehouse_t8(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建普通表空间。postgres=# CREATE TABLESPACE DS_TABLESPACE1 RELATIVE LOCATION 'tablespace/tablespace_1';--创建表时，指定表空间。postgres=# CREATE TABLE tpcds.warehouse_t9(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2)) TABLESPACE DS_TABLESPACE1;--创建表时，单独指定W_WAREHOUSE_NAME的索引表空间。postgres=# CREATE TABLE tpcds.warehouse_t10(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) UNIQUE USING INDEX TABLESPACE DS_TABLESPACE1,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));--创建一个有主键约束的表。postgres=# CREATE TABLE tpcds.warehouse_t11(W_WAREHOUSE_SK INTEGER PRIMARY KEY,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));---或是用下面的语法，效果完全一样。postgres=# CREATE TABLE tpcds.warehouse_t12(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2),PRIMARY KEY(W_WAREHOUSE_SK));--或是用下面的语法，指定约束的名称。postgres=# CREATE TABLE tpcds.warehouse_t13(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2),CONSTRAINT W_CSTR_KEY1 PRIMARY KEY(W_WAREHOUSE_SK));--创建一个有复合主键约束的表。postgres=# CREATE TABLE tpcds.warehouse_t14(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2),CONSTRAINT W_CSTR_KEY2 PRIMARY KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID));--创建列存储表。postgres=# CREATE TABLE tpcds.warehouse_t15(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2)) WITH (ORIENTATION = COLUMN);--创建局部聚簇存储的列存储表。postgres=# CREATE TABLE tpcds.warehouse_t16(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2),PARTIAL CLUSTER KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)) WITH (ORIENTATION = COLUMN);--定义一个带压缩的列存储表。postgres=# CREATE TABLE tpcds.warehouse_t17(W_WAREHOUSE_SK INTEGER NOT NULL,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2)) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);--定义一个检查列约束。postgres=# CREATE TABLE tpcds.warehouse_t19(W_WAREHOUSE_SK INTEGER PRIMARY KEY CHECK (W_WAREHOUSE_SK > 0),W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) CHECK (W_WAREHOUSE_NAME IS NOT NULL),W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2));postgres=# CREATE TABLE tpcds.warehouse_t20(W_WAREHOUSE_SK INTEGER PRIMARY KEY,W_WAREHOUSE_ID CHAR(16) NOT NULL,W_WAREHOUSE_NAME VARCHAR(20) CHECK (W_WAREHOUSE_NAME IS NOT NULL),W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,W_STREET_TYPE CHAR(15) ,W_SUITE_NUMBER CHAR(10) ,W_CITY VARCHAR(60) ,W_COUNTY VARCHAR(30) ,W_STATE CHAR(2) ,W_ZIP CHAR(10) ,W_COUNTRY VARCHAR(20) ,W_GMT_OFFSET DECIMAL(5,2),CONSTRAINT W_CONSTR_KEY2 CHECK(W_WAREHOUSE_SK > 0 AND W_WAREHOUSE_NAME IS NOT NULL));--向tpcds.warehouse_t19表中增加一个varchar列。postgres=# ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);--给tpcds.warehouse_t19表增加一个检查约束。postgres=# ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);--在一个操作中改变两个现存字段的类型。postgres=# ALTER TABLE tpcds.warehouse_t19ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80),ALTER COLUMN W_STREET_NAME TYPE varchar(100);--此语句与上面语句等效。postgres=# ALTER TABLE tpcds.warehouse_t19 MODIFY (W_GOODS_CATEGORY varchar(30), W_STREET_NAME varchar(60));--给一个已存在字段添加非空约束。postgres=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;--移除已存在字段的非空约束。postgres=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;--如果列存储表中还未指定局部聚簇，向在一个列存储表中添加局部聚簇列。postgres=# ALTER TABLE tpcds.warehouse_t17 ADD PARTIAL CLUSTER KEY(W_WAREHOUSE_SK);--查看约束的名称，并删除一个列存储表中的局部聚簇列。postgres=# \d+ tpcds.warehouse_t17Table "tpcds.warehouse_t17"Column | Type | Modifiers | Storage | Stats target | Description-------------------+-----------------------+-----------+----------+--------------+-------------w_warehouse_sk | integer | not null | plain | |w_warehouse_id | character(16) | not null | extended | |w_warehouse_name | character varying(20) | | extended | |w_warehouse_sq_ft | integer | | plain | |w_street_number | character(10) | | extended | |w_street_name | character varying(60) | | extended | |w_street_type | character(15) | | extended | |w_suite_number | character(10) | | extended | |w_city | character varying(60) | | extended | |w_county | character varying(30) | | extended | |w_state | character(2) | | extended | |w_zip | character(10) | | extended | |w_country | character varying(20) | | extended | |w_gmt_offset | numeric(5,2) | | main | |Partial Cluster :"warehouse_t17_cluster" PARTIAL CLUSTER KEY (w_warehouse_sk)Has OIDs: noLocation Nodes: ALL DATANODESOptions: compression=no, version=0.12postgres=# ALTER TABLE tpcds.warehouse_t17 DROP CONSTRAINT warehouse_t17_cluster;--将表移动到另一个表空间。postgres=# ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT;--创建模式joe。postgres=# CREATE SCHEMA joe;--将表移动到另一个模式中。postgres=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;--重命名已存在的表。postgres=# ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;--从warehouse_t23表中删除一个字段。postgres=# ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;--删除表空间、模式joe和模式表warehouse。postgres=# DROP TABLE tpcds.warehouse_t1;postgres=# DROP TABLE tpcds.warehouse_t2;postgres=# DROP TABLE tpcds.warehouse_t3;postgres=# DROP TABLE tpcds.warehouse_t4;postgres=# DROP TABLE tpcds.warehouse_t5;postgres=# DROP TABLE tpcds.warehouse_t6;postgres=# DROP TABLE tpcds.warehouse_t7;postgres=# DROP TABLE tpcds.warehouse_t8;postgres=# DROP TABLE tpcds.warehouse_t9;postgres=# DROP TABLE tpcds.warehouse_t10;postgres=# DROP TABLE tpcds.warehouse_t11;postgres=# DROP TABLE tpcds.warehouse_t12;postgres=# DROP TABLE tpcds.warehouse_t13;postgres=# DROP TABLE tpcds.warehouse_t14;postgres=# DROP TABLE tpcds.warehouse_t15;postgres=# DROP TABLE tpcds.warehouse_t16;postgres=# DROP TABLE tpcds.warehouse_t17;postgres=# DROP TABLE tpcds.warehouse_t18;postgres=# DROP TABLE tpcds.warehouse_t20;postgres=# DROP TABLE tpcds.warehouse_t21;postgres=# DROP TABLE tpcds.warehouse_t22;postgres=# DROP TABLE joe.warehouse_t23;postgres=# DROP TABLE tpcds.warehouse_t24;postgres=# DROP TABLE tpcds.warehouse_t25;postgres=# DROP TABLESPACE DS_TABLESPACE1;postgres=# DROP SCHEMA IF EXISTS joe CASCADE;
SELECT用于从表或视图中取出数据。
SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。
（1）注意事项：
① 必须对每个在SELECT命令中使用的字段有SELECT权限。
② 使用FOR UPDATE或FOR SHARE还要求UPDATE权限。
（2）语法格式：
查询数据。
[ WITH [ RECURSIVE ] with_query [, ...] ]SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]{ * | {expression [ [ AS ] output_name ]} [, ...] }[ FROM from_item [, ...] ][ WHERE condition ][ GROUP BY grouping_element [, ...] ][ HAVING condition [, ...] ][ WINDOW {window_name AS ( window_definition )} [, ...] ][ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] SELECT ][ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ][ LIMIT { [offset,] count | ALL } ][ OFFSET start [ ROW | ROWS ] ][ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ][ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ]
其中子查询with_query为：
with_query_name [ ( column_name [, ...] ) ]AS ( {SELECT | values | INSERT | update | delete} )
其中指定查询源from_item为：
{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ][ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]|( SELECT ) [ AS ] alias [ ( column_alias [, ...] ) ]|with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]|function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]|function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}
其中group子句为：
( )| expression| ( expression [, ...] )| ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )| CUBE ( { expression | ( expression [, ...] ) } [, ...] )| GROUPING SETS ( grouping_element [, ...] )
其中指定分区partition_clause为：
PARTITION { ( partition_name ) |FOR ( partition_value [, ...] ) }
NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )
简化版查询语法，功能相当于SELECT * FROM table_name。
TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};
（3） 示例：
--先通过子查询得到一张临时表temp_t，然后查询表temp_t中的所有数据。postgres=# WITH temp_t(name,isdba) AS (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;--查询tpcds.reason表的所有r_reason_sk记录，且去除重复。postgres=# SELECT DISTINCT(r_reason_sk) FROM tpcds.reason;--LIMIT子句示例：获取表中一条记录。postgres=# SELECT * FROM tpcds.reason LIMIT 1;--查询所有记录，且按字母升序排列。postgres=# SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc;--通过表别名，从pg_user和pg_user_status这两张表中获取数据。postgres=# SELECT a.usename,b.locktime FROM pg_user a,pg_user_status b WHERE a.usesysid=b.roloid;--FULL JOIN子句示例：将pg_user和pg_user_status这两张表的数据进行全连接显示，即数据的合集。postgres=# SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid;--GROUP BY子句示例：根据查询条件过滤，并对结果进行分组。postgres=# SELECT r_reason_id, AVG(r_reason_sk) FROM tpcds.reason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25;--GROUP BY CUBE子句示例：根据查询条件过滤，并对结果进行分组汇总。postgres=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY CUBE(r_reason_id,r_reason_sk);--GROUP BY GROUPING SETS子句示例:根据查询条件过滤，并对结果进行分组汇总。postgres=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY GROUPING SETS((r_reason_id,r_reason_sk),r_reason_sk);--UNION子句示例：将表tpcds.reason里r_reason_desc字段中的内容以W开头和以N开头的进行合并。postgres=# SELECT r_reason_sk, tpcds.reason.r_reason_descFROM tpcds.reasonWHERE tpcds.reason.r_reason_desc LIKE 'W%'UNIONSELECT r_reason_sk, tpcds.reason.r_reason_descFROM tpcds.reasonWHERE tpcds.reason.r_reason_desc LIKE 'N%';--NLS_SORT子句示例：中文拼音排序。postgres=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, 'NLS_SORT = SCHINESE_PINYIN_M');--不区分大小写排序:postgres=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, 'NLS_SORT = generic_m_ci');--创建分区表tpcds.reason_ppostgres=# CREATE TABLE tpcds.reason_p(r_reason_sk integer,r_reason_id character(16),r_reason_desc character(100))PARTITION BY RANGE (r_reason_sk)(partition P_05_BEFORE values less than (05),partition P_15 values less than (15),partition P_25 values less than (25),partition P_35 values less than (35),partition P_45_AFTER values less than (MAXVALUE));--插入数据。postgres=# INSERT INTO tpcds.reason_p values(3,'AAAAAAAABAAAAAAA','reason 1'),(10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),(20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');--PARTITION子句示例：从tpcds.reason_p的表分区P_05_BEFORE中获取数据。postgres=# SELECT * FROM tpcds.reason_p PARTITION (P_05_BEFORE);r_reason_sk | r_reason_id | r_reason_desc-------------+------------------+------------------------------------4 | AAAAAAAABAAAAAAA | reason 33 | AAAAAAAABAAAAAAA | reason 1(2 rows)--GROUP BY子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录数。postgres=# SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id;count | r_reason_id-------+------------------2 | AAAAAAAACAAAAAAA5 | AAAAAAAABAAAAAAA(2 rows)--GROUP BY CUBE子句示例：根据查询条件过滤，并对查询结果分组汇总。postgres=# SELECT * FROM tpcds.reason GROUP BY CUBE (r_reason_id,r_reason_sk,r_reason_desc);--GROUP BY GROUPING SETS子句示例：根据查询条件过滤，并对查询结果分组汇总。postgres=# SELECT * FROM tpcds.reason GROUP BY GROUPING SETS ((r_reason_id,r_reason_sk),r_reason_desc);--HAVING子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录，并只显示r_reason_id个数大于2的信息。postgres=# SELECT COUNT(*) c,r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING c>2;c | r_reason_id---+------------------5 | AAAAAAAABAAAAAAA(1 row)--IN子句示例：按r_reason_id分组统计tpcds.reason_p表中的r_reason_id个数，并只显示r_reason_id值为 AAAAAAAABAAAAAAA或AAAAAAAADAAAAAAA的个数。postgres=# SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING r_reason_id IN('AAAAAAAABAAAAAAA','AAAAAAAADAAAAAAA');count | r_reason_id-------+------------------5 | AAAAAAAABAAAAAAA(1 row)--INTERSECT子句示例：查询r_reason_id等于AAAAAAAABAAAAAAA，并且r_reason_sk小于5的信息。postgres=# SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' INTERSECT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<5;r_reason_sk | r_reason_id | r_reason_desc-------------+------------------+------------------------------------4 | AAAAAAAABAAAAAAA | reason 33 | AAAAAAAABAAAAAAA | reason 1(2 rows)--EXCEPT子句示例：查询r_reason_id等于AAAAAAAABAAAAAAA，并且去除r_reason_sk小于4的信息。postgres=# SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' EXCEPT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<4;r_reason_sk | r_reason_id | r_reason_desc-------------+------------------+------------------------------------10 | AAAAAAAABAAAAAAA | reason 210 | AAAAAAAABAAAAAAA | reason 510 | AAAAAAAABAAAAAAA | reason 44 | AAAAAAAABAAAAAAA | reason 3(4 rows)--通过在WHERE子句中指定"(+)"来实现左连接。postgres=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM store_returns t1, customer t2 WHERE t1.sr_customer_sk = t2.c_customer_sk(+)order by 1 desc limit 1;sr_item_sk | c_customer_id------------+---------------18000 |(1 row)--通过在WHERE子句中指定"(+)"来实现右连接。postgres=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM store_returns t1, customer t2 WHERE t1.sr_customer_sk(+) = t2.c_customer_skorder by 1 desc limit 1;sr_item_sk | c_customer_id------------+------------------| AAAAAAAAJNGEBAAA(1 row)--通过在WHERE子句中指定"(+)"来实现左连接，并且增加连接条件。postgres=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM store_returns t1, customer t2 WHERE t1.sr_customer_sk = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1 order by 1 limit 1;sr_item_sk | c_customer_id------------+---------------1 |(1 row)--不支持在WHERE子句中指定"(+)"的同时使用内层嵌套AND/OR的表达式。postgres=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM store_returns t1, customer t2 WHERE not(t1.sr_customer_sk = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1);ERROR: Operator "(+)" can not be used in nesting expression.LINE 1: ...tomer_id FROM store_returns t1, customer t2 WHERE not(t1.sr_...^--WHERE子句在不支持表达式宏指定"(+)"会报错。postgres=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM store_returns t1, customer t2 WHERE (t1.sr_customer_sk = t2.c_customer_sk(+))::bool;ERROR: Operator "(+)" can only be used in common expression.--WHERE子句在表达式的两边都指定"(+)"会报错。postgres=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM store_returns t1, customer t2 WHERE t1.sr_customer_sk(+) = t2.c_customer_sk(+);ERROR: Operator "(+)" can't be specified on more than one relation in one join conditionHINT: "t1", "t2"...are specified Operator "(+)" in one condition.--删除表。postgres=# DROP TABLE tpcds.reason_p;
最后修改时间：2021-04-01 09:11:54
文章转载自Gauss松鼠会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
