2.2 openGauss基本使用
openGauss安装完成后会自动启动，用户可以使用openGauss自带的gsql（openGauss交互终端）客户端连接数据库进行相关SQL操作。
2.2.1 连接数据库
本节为您介绍如何使用gsql客户端连接数据库。gsql是openGauss自带的客户端工具。使用gsql连接数据库，可以交互式地输入、编辑、执行SQL语句。
- 确认连接信息
gsql客户端工具通过数据库主节点连接数据库。因此连接前需获取数据库主节点所在服务器的IP地址及数据库主节点的端口号信息。
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）中查到的数据库主节点数据路径下的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 port
alarm_report_interval = 10
support_extended_features=true
- 使用gsql本地连接
gsql是openGauss提供的在命令行下运行的数据库连接工具。此工具除了具备操作数据库的基本功能，还提供了若干高级特性，便于用户使用。本节只介绍如何使用gsql本地连接数据库。
1） 注意事项
缺省情况下，客户端连接数据库后处于空闲状态时会根据参数“session_timeout”的默认值自动断开连接。如果要关闭超时设置，设置参数“session_timeout”为0即可。
2） 前提条件
已确认连接信息，获取数据库主节点端口号信息。
3） 操作步骤
（1） 以操作系统用户omm登录数据库主节点。
（2） 连接数据库。第一次连接数据库时可以连接到安装后默认生成的数据库。执行如下命令连接数据库。
gsql -d -p
连接成功后，系统显示类似如下信息。
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.
openGauss=#
omm用户是管理员用户，因此系统显示“openGauss=#”。若使用普通用户身份登录和连接数据库，系统显示“openGauss=>”。
“Non-SSL connection”表示未使用SSL方式连接数据库。如果需要高安全性时，请使用SSL连接。
（3） 首次登录需要修改密码。原始密码为安装openGauss数据库时手动输入的密码，此处需将原始密码修改为自定义的密码，例如Mypwd123，命令如下。
openGauss=# ALTER ROLE omm IDENTIFIED BY ‘Mypwd123’ REPLACE ‘XuanYuan@2012’;
（4） 退出数据库。
openGauss=# \q
2.2.2 使用数据库
本节描述使用数据库的基本操作。通过此节用户可以完成创建数据库、创建表及向表中插入数据和查询表中数据等数据管理和基础权限管理操作。 - 从这里开始
本节为您完整地介绍一个使用数据库的示例，帮助您更好了解数据库，更多关于SQL语句的使用请参见后续章节。
（1） 以操作系统用户omm登录数据库主节点。
（2） 执行如下命令连接数据库。
gsql -d -p
当结果显示为如下信息，则表示连接成功。
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.
openGauss=#
（3） 创建数据库用户。
默认只有openGauss安装时创建的管理员用户可以访问初始数据库，您还可以创建其他数据库用户账号访问数据库。
openGauss=# CREATE USER joe WITH PASSWORD “Bigdata@123”;
当结果显示为如下信息，则表示创建成功。
CREATE ROLE
如上创建了一个用户名为joe，密码为Bigdata@123的用户。
（4） 创建数据库。
openGauss=# CREATE DATABASE db_tpcc OWNER joe;
当结果显示为如下信息，则表示创建成功。
CREATE DATABASE
db_tpcc数据库创建完成后，就可以按如下方法退出数据库，使用新用户joe连接db_tpcc数据库执行创建表等操作。您也可以选择继续在默认数据库下进行后续的体验。
openGauss=# \q
gsql -d db_tpcc -p 8000 -U joe -W Bigdata@123
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.
db_tpcc=>
（5） 创建schema。
db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe;
当结果显示为如下信息，则表示创建schema成功。
CREATE SCHEMA
（6） 创建表。
创建一个名称为mytable，只有一列的表。字段名为firstcol，字段类型为integer。
db_tpcc=> CREATE TABLE mytable (firstcol int);
当结果显示为如下信息，则表示表创建成功。
CREATE TABLE
向表中插入数据。
db_tpcc=> INSERT INTO mytable values (100);
当结果显示为如下信息，则表示插入数据成功。
INSERT 0 1
查看表中数据。
db_tpcc=> SELECT * FROM mytable;
firstcol
100
(1 row)
- 简单数据管理
在openGuass中拥有多种对象进行数据管理，例如表、数据库。用户可以通过创建表存储数据，查询表检索数据。也可以通过创建数据库进行数据隔离。本节为您介绍openGauss中如何创建数据库、表以及查看数据库中的对象。
1） 创建数据库
创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
（1） 注意事项
只有拥有CREATEDB权限的用户才可以创建新数据库，系统管理员默认拥有此权限。
不能在事务块中执行创建数据库语句。
在创建数据库过程中，若出现类似“could not initialize database directory”的错误提示，可能是由于文件系统上数据目录的权限不足或磁盘满等原因引起。
（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用户。
openGauss=# CREATE USER jim PASSWORD ‘Bigdata@123’;
openGauss=# CREATE USER tom PASSWORD ‘Bigdata@123’;
–创建一个GBK编码的数据库music（本地环境的编码格式必须也为GBK）。
openGauss=# CREATE DATABASE music ENCODING ‘GBK’ template = template0;
–创建数据库music2，并指定所有者为jim。
openGauss=# CREATE DATABASE music2 OWNER jim;
–用模板template0创建数据库music3，并指定所有者为jim。
openGauss=# CREATE DATABASE music3 OWNER jim TEMPLATE template0;
–设置music数据库的连接数为10。
openGauss=# ALTER DATABASE music CONNECTION LIMIT= 10;
–将music名称改为music4。
openGauss=# ALTER DATABASE music RENAME TO music4;
–将数据库music2的所属者改为tom。
openGauss=# ALTER DATABASE music2 OWNER TO tom;
–设置music3的表空间为PG_DEFAULT。
openGauss=# ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;
–关闭在数据库music3上缺省的索引扫描。
openGauss=# ALTER DATABASE music3 SET enable_indexscan TO off;
–重置enable_indexscan参数。
openGauss=# ALTER DATABASE music3 RESET enable_indexscan;
–删除数据库。
openGauss=# DROP DATABASE music2;
openGauss=# DROP DATABASE music3;
openGauss=# DROP DATABASE music4;
–删除jim和tom用户。
openGauss=# DROP USER jim;
openGauss=# DROP USER tom;
–创建兼容TD格式的数据库。
openGauss=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY ‘C’;
–创建兼容ORA格式的数据库。
openGauss=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY ‘A’;
–删除兼容TD、ORA格式的数据库。
openGauss=# DROP DATABASE td_compatible_db;
openGauss=# DROP DATABASE ora_compatible_db;
2） 创建表
在当前数据库中创建一个新的空白表，该表为命令执行者所有。
创建表时，如未指定表的存储方式，默认创建的是行存储表；如未指定分布列时，取表的主键列（如果有的话）或首个可以作为分布列的列。
（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） 示例
–创建简单的表。
openGauss=# 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)
);
openGauss=# 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。
openGauss=# 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字段是否有重复。
openGauss=# 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%填充因子的表。
openGauss=# 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)
);
–或者用下面的语法。
openGauss=# 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);
–创建表，并指定该表数据不写入预写日志。
openGauss=# 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)
);
–创建表临时表。
openGauss=# 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)
);
–创建本地临时表，并指定提交事务时删除该临时表数据。
openGauss=# 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;
–创建全局临时表，并指定会话结束时删除该临时表数据。
openGauss=# CREATE GLOBAL TEMPORARY TABLE gtt1
(
ID INTEGER NOT NULL,
NAME CHAR(16) NOT NULL,
ADDRESS VARCHAR(50) ,
POSTCODE CHAR(6)
) ON COMMIT PRESERVE ROWS;
–创建表时，不希望因为表已存在而报错。
openGauss=# 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)
);
–创建普通表空间。
openGauss=# CREATE TABLESPACE DS_TABLESPACE1 RELATIVE LOCATION ‘tablespace/tablespace_1’;
–创建表时，指定表空间。
openGauss=# 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的索引表空间。
openGauss=# 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)
);
–创建一个有主键约束的表。
openGauss=# 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)
);
—或是用下面的语法，效果完全一样。
openGauss=# 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)
);
–或是用下面的语法，指定约束的名称。
openGauss=# 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)
);
–创建一个有复合主键约束的表。
openGauss=# 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)
);
–创建列存储表。
openGauss=# 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);
–创建局部聚簇存储的列存储表。
openGauss=# 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);
–定义一个带压缩的列存储表。
openGauss=# 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);
–定义一个检查列约束。
openGauss=# 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)
);
openGauss=# 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列。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);
–给tpcds.warehouse_t19表增加一个检查约束。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);
–在一个操作中改变两个现存字段的类型。
openGauss=# ALTER TABLE tpcds.warehouse_t19
ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80),
ALTER COLUMN W_STREET_NAME TYPE varchar(100);
–此语句与上面语句等效。
openGauss=# ALTER TABLE tpcds.warehouse_t19 MODIFY (W_GOODS_CATEGORY varchar(30), W_STREET_NAME varchar(60));
–给一个已存在字段添加非空约束。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;
–移除已存在字段的非空约束。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;
–如果列存储表中还未指定局部聚簇，向在一个列存储表中添加局部聚簇列。
openGauss=# ALTER TABLE tpcds.warehouse_t17 ADD PARTIAL CLUSTER KEY(W_WAREHOUSE_SK);
–查看约束的名称，并删除一个列存储表中的局部聚簇列。
openGauss=# \d+ tpcds.warehouse_t17
Table “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: no
Location Nodes: ALL DATANODES
Options: compression=no, version=0.12
openGauss=# ALTER TABLE tpcds.warehouse_t17 DROP CONSTRAINT warehouse_t17_cluster;
–将表移动到另一个表空间。
openGauss=# ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT;
–创建模式joe。
openGauss=# CREATE SCHEMA joe;
–将表移动到另一个模式中。
openGauss=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;
–重命名已存在的表。
openGauss=# ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;
–从warehouse_t23表中删除一个字段。
openGauss=# ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;
–删除表空间、模式joe和模式表warehouse。
openGauss=# DROP TABLE tpcds.warehouse_t1;
openGauss=# DROP TABLE tpcds.warehouse_t2;
openGauss=# DROP TABLE tpcds.warehouse_t3;
openGauss=# DROP TABLE tpcds.warehouse_t4;
openGauss=# DROP TABLE tpcds.warehouse_t5;
openGauss=# DROP TABLE tpcds.warehouse_t6;
openGauss=# DROP TABLE tpcds.warehouse_t7;
openGauss=# DROP TABLE tpcds.warehouse_t8;
openGauss=# DROP TABLE tpcds.warehouse_t9;
openGauss=# DROP TABLE tpcds.warehouse_t10;
openGauss=# DROP TABLE tpcds.warehouse_t11;
openGauss=# DROP TABLE tpcds.warehouse_t12;
openGauss=# DROP TABLE tpcds.warehouse_t13;
openGauss=# DROP TABLE tpcds.warehouse_t14;
openGauss=# DROP TABLE tpcds.warehouse_t15;
openGauss=# DROP TABLE tpcds.warehouse_t16;
openGauss=# DROP TABLE tpcds.warehouse_t17;
openGauss=# DROP TABLE tpcds.warehouse_t18;
openGauss=# DROP TABLE tpcds.warehouse_t20;
openGauss=# DROP TABLE tpcds.warehouse_t21;
openGauss=# DROP TABLE tpcds.warehouse_t22;
openGauss=# DROP TABLE joe.warehouse_t23;
openGauss=# DROP TABLE tpcds.warehouse_t24;
openGauss=# DROP TABLE tpcds.warehouse_t25;
openGauss=# DROP TABLESPACE DS_TABLESPACE1;
openGauss=# DROP SCHEMA IF EXISTS joe CASCADE;
3） 查询数据
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_expression_clause为：
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中的所有数据。
openGauss=# WITH temp_t(name,isdba) AS (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;
–查询tpcds.reason表的所有r_reason_sk记录，且去除重复。
openGauss=# SELECT DISTINCT(r_reason_sk) FROM tpcds.reason;
–LIMIT子句示例：获取表中一条记录。
openGauss=# SELECT * FROM tpcds.reason LIMIT 1;
–查询所有记录，且按字母升序排列。
openGauss=# SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc;
–通过表别名，从pg_user和pg_user_status这两张表中获取数据。
openGauss=# 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这两张表的数据进行全连接显示，即数据的合集。
openGauss=# SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid;
–GROUP BY子句示例：根据查询条件过滤，并对结果进行分组。
openGauss=# 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子句示例：根据查询条件过滤，并对结果进行分组汇总。
openGauss=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY CUBE(r_reason_id,r_reason_sk);
–GROUP BY GROUPING SETS子句示例:根据查询条件过滤，并对结果进行分组汇总。
openGauss=# 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开头的进行合并。
openGauss=# SELECT r_reason_sk, tpcds.reason.r_reason_desc
FROM tpcds.reason
WHERE tpcds.reason.r_reason_desc LIKE ‘W%’
UNION
SELECT r_reason_sk, tpcds.reason.r_reason_desc
FROM tpcds.reason
WHERE tpcds.reason.r_reason_desc LIKE ‘N%’;
–NLS_SORT子句示例：中文拼音排序。
openGauss=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, ‘NLS_SORT = SCHINESE_PINYIN_M’);
–不区分大小写排序:
openGauss=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, ‘NLS_SORT = generic_m_ci’);
–创建分区表tpcds.reason_p
openGauss=# 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)
)
;
–插入数据。
openGauss=# 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中获取数据。
openGauss=# SELECT * FROM tpcds.reason_p PARTITION (P_05_BEFORE);
r_reason_sk | r_reason_id | r_reason_desc
-------------±-----------------±-----------------------------------
4 | AAAAAAAABAAAAAAA | reason 3
3 | AAAAAAAABAAAAAAA | reason 1
(2 rows)
–GROUP BY子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录数。
openGauss=# SELECT COUNT(),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id;
count | r_reason_id
-------±-----------------
2 | AAAAAAAACAAAAAAA
5 | AAAAAAAABAAAAAAA
(2 rows)
–GROUP BY CUBE子句示例：根据查询条件过滤，并对查询结果分组汇总。
openGauss=# SELECT * FROM tpcds.reason GROUP BY CUBE (r_reason_id,r_reason_sk,r_reason_desc);
–GROUP BY GROUPING SETS子句示例：根据查询条件过滤，并对查询结果分组汇总。
openGauss=# 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的信息。
openGauss=# 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的个数。
openGauss=# 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的信息。
openGauss=# SELECT * FROM tpcds.reason_p WHERE r_reason_id=‘AAAAAAAABAAAAAAA’ INTERSECT SELECT * FROM tpcds.reason_p WHERE r_reason_sk
