网站建设入门基础,中关村在线主页,wordpress 仿微博,沙井做网站的公司目录
1.1 gsql的语法
1.2 gsql常用选项
1.2.1 最常用的必要选项
1.2.2 -r选项
1.2.3 -E选项
1.2.4 -t选项
1.2.5 -A选项
1.2.6 -v选项
1.2.7 -c选项
1.2.8 -f选项
1.2.9 -q选项
1.3 gsql的元命令
1.3.1 \l命令
1.3.2 \du命令和\dg命令
1.3.3 \db命令
1.3.4 \d…目录
1.1 gsql的语法
1.2 gsql常用选项
1.2.1 最常用的必要选项
1.2.2 -r选项
1.2.3 -E选项
1.2.4 -t选项
1.2.5 -A选项
1.2.6 -v选项
1.2.7 -c选项
1.2.8 -f选项
1.2.9 -q选项
1.3 gsql的元命令
1.3.1 \l命令
1.3.2 \du命令和\dg命令
1.3.3 \db命令
1.3.4 \dn命令
1.3.5 \d命令
1.3.6 \dt命令
1.3.7 \di命令
1.3.8 \dv命令
1.3.9 \ds命令
1.3.10 \df命令
1.3.11 \d TableName命令
1.3.12 \di IndexName命令
1.3.13 \dx命令
1.3.14 \x命令
1.3.15 \timing命令
1.3.16 \h命令
1.3.17 \?命令
1.3.18 \! os_command命令
1.3.19 \o fileName命令
1.3.20 \i file.sql命令
1.3.21 \conninfo命令
1.3.22 \c[onnect] [DBNAME]命令
1.3.23 \echo [string]命令
1.3.24 \q命令和快捷键ctrld
1.4 gsql初始化文件.gsqlrc
2.客户端安全
2.1配置客户端接入认证
2.2客户端验证 1.1 gsql的语法
gsql --help [ommtest ~]$ gsql --help gsql is the FusionInsight LibrA interactive terminal. Usage: gsql [OPTION]... [DBNAME [USERNAME]] General options: -c, --commandCOMMAND run only single command (SQL or internal) and exit -d, --dbnameDBNAME database name to connect to (default: omm) -f, --fileFILENAME execute commands from file, then exit -l, --list list available databases, then exit -v, --set, --variableNAMEVALUE set gsql variable NAME to VALUE -V, --version output version information, then exit -X, --no-gsqlrc do not read startup file (~/.gsqlrc) -1 (one), --single-transaction execute command file as a single transaction -?, --help show this help, then exit Input and output options: -a, --echo-all echo all input from script -e, --echo-queries echo commands sent to server -E, --echo-hidden display queries that internal commands generate -k, --with-keyKEY the key for decrypting the encrypted file -L, --log-fileFILENAME send session log to file -m, --maintenance can connect to cluster during 2-pc transaction recovery -n, --no-libedit disable enhanced command line editing (libedit) -o, --outputFILENAME send query results to file (or |pipe) -q, --quiet run quietly (no messages, only query output) -s, --single-step single-step mode (confirm each query) -S, --single-line single-line mode (end of line terminates SQL command) Output format options: -A, --no-align unaligned table output mode -F, --field-separatorSTRING set field separator (default: |) -H, --html HTML table output mode -P, --psetVAR[ARG] set printing option VAR to ARG (see \pset command) -R, --record-separatorSTRING set record separator (default: newline) -r if this parameter is set,use libedit -t, --tuples-only print rows only -T, --table-attrTEXT set HTML table tag attributes (e.g., width, border) -x, --expanded turn on expanded table output -z, --field-separator-zero set field separator to zero byte -0, --record-separator-zero set record separator to zero byte Connection options: -h, --hostHOSTNAME database server host or socket directory (default: /opt/gaussdbi/tmp) -p, --portPORT database server port (default: 5432) -U, --usernameUSERNAME database user name (default: omm) -W, --passwordPASSWORD the password of specified database user For more information, type \? (for internal commands) or \help (for SQL commands) from within gsql, or consult the gsql section in the FusionInsight LibrA documentation. Report bugs to pgsql-bugspostgresql.org. [ommtest ~]$ 1.2 gsql常用选项
1.2.1 最常用的必要选项
使用gsql命令用数据库用户student连接到openGauss数据库管理系统下的studentdb数据库
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb 其中的选项说明如下
-d选项 指定gsql客户端连接的数据库
-h选项 指定gsql客户端连接的服务器IP
-U选项 指定gsql客户端连接数据库的用户名
-p选项 指定gsql客户端连接的服务器端口号
-W选项 指定gsql客户端连接的用户密码
1.2.2 -r选项
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql命令的-r选项第一个作用是当我们在gsql中执行了很多语句后如果我们想重新执行之前执行过的语句可以使用上箭头和下箭头向前和向后翻阅之前执行过的命令和SQL语句。
执行下面的语句使用上箭头可以提供很多便利性。
SELECT * FROM instructor WHERE salary90000; studentdb SELECT * FROM instructor WHERE salary90000; id | dept_name | name | salary ----------------------------------------------- 12121 | Finance | Wu | 90000.00 (1 row) studentdb 1.2.3 -E选项
-E选项会让gsql客户端程序再执行元命令的时候显示其对应的SQL语句。
使用Linux用户omm打开一个Linux终端窗口执行gsql的元命令\l该命令的作用是显示当前系统有哪些数据库
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -E
\l
\q [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -E gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb \l ********* QUERY ********** -E选项会显示gsql客户端的元命令对应的SQL语句 SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, pg_catalog.pg_encoding_to_char(d.encoding) as Encoding, d.datcollate as Collate, d.datctype as Ctype, pg_catalog.array_to_string(d.datacl, E\n) AS Access privileges FROM pg_catalog.pg_database d ORDER BY 1; ************************** List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------------------------------------------------------- postgres | omm | SQL_ASCII | C | C | studentdb | omm | SQL_ASCII | C | C | template0 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm template1 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm (4 rows) studentdb \q [ommtest ~]$ 如果不使用-E选项同样执行上面的命令系列结果如下
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r
\l
\q [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------------------------------------------------------- postgres | omm | SQL_ASCII | C | C | studentdb | omm | SQL_ASCII | C | C | template0 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm template1 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm (4 rows) studentdb \q [ommtest ~]$ 可以看出如果没有-E选项不会显示元命令\l对应的SQL查询语句。
1.2.4 -t选项
-t选项会让gsql客户端程序在执行SQL查询语句的时候返回的结果不显示列名及返回结果的行数。
使用Linux用户omm打开一个Linux终端窗口执行如下的命令
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -t
select * from instructor where salary80000; [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -t gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb select * from instructor where salary80000; 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 studentdb \q [ommtest ~]$ 1.2.5 -A选项
-A选项会让gsql客户端程序在执行SQL查询语句的时候不对齐显示查询返回的行数据,如图6-7所示列不是对齐的。
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -A
select * from instructor where salary80000;
\q 图6-8 -A选项表示不对齐显式
可以将-A和-t两个选项同时应用表示不对齐显示也不显示列名和返回行数
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -At
select * from instructor where salary80000;
\q [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r -At gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb select * from instructor where salary80000; 76543|Finance|Singh|80000.00 98345|Elec. Eng.|Kim|80000.00 studentdb \q [ommtest ~]$ 1.2.6 -v选项
-v选项会让gsql客户端程序在命令行中设置gsql环境变量。
如果我们想在命令行中告诉gsql启动后关闭自动提交设置为手动事务提交可以执行如下命令
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \
-v AUTOCOMMIToff -r [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -v AUTOCOMMIToff -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb 1.2.7 -c选项
-c选项会让gsql客户端程序直接在命令行中运行SQL语句示例如下
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c select * from instructor where salary80000 [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c select * from instructor where salary80000 id | dept_name | name | salary ---------------------------------------------------- 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) [ommtest ~]$ 1.2.8 -f选项
-f选项会让gsql客户端程序直接在命令行中直接运行SQL脚本文件。
首先用omm用户生成一个测试用的SQL语句脚本
cat test.sqlEOF
select * from instructor where salary80000;
EOF [ommtest ~]$ cat test.sqlEOF select * from instructor where salary80000; EOF [ommtest ~]$ 然后使用gsql客户端程序在命令行中直接执行刚刚创建的SQL语句脚本
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -f test.sql
rm test.sql [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -f test.sql id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) total time: 0 ms [ommtest ~]$ rm test.sql [ommtest ~]$ 1.2.9 -q选项
-q选项会让gsql客户端程序以安静的方式运行只显示查询结果。
首先用omm用户执行下面的gsql命令使用了-q选项创建test表然后再次执行gsql命令不使用-q选项删除刚刚创建的test表接着再次执行gsql命令不使用-q选项重新创建test表最后又一次执行gsql命令使用了-q选项删除刚刚创建的test表
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c create table test(col char) -q
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c drop table test
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c create table test(col char)
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c drop table test -q [ommtest script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c create table test(col char) -q [ommtest script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c drop table test DROP TABLE [ommtest script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c create table test(col char) CREATE TABLE [ommtest script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c drop table test -q [ommtest script]$ 我们发现使用了-q选项的gsql没有显示任何信息。
执行下面的命令进行SQL查询我们发现虽然有-q选项但是会显示查询结果。
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c select * from instructor where salary80000 -q [ommtest script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 \ -c select * from instructor where salary80000 -q id | dept_name | name | salary --------------------------------------------------- 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) [ommtest script]$ 1.3 gsql的元命令
本节的测试均使用下面的命令登录到openGauss数据库
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb 1.3.1 \l命令
元命令\l的作用是显示openGauss数据库集簇中目前有哪些数据库
\l studentdb \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------------------------------------------------------- postgres | omm | SQL_ASCII | C | C | studentdb | omm | SQL_ASCII | C | C | template0 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm template1 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm (4 rows) studentdb 1.3.2 \du命令和\dg命令
元命令\dg命令与元命令\du命令的作用类似都是显示openGauss数据库集簇中目前有哪些用户和角色。
\du
\dg studentdb \du List of roles Role name | Attributes | Member of ---------------------------------------------------------------------------------------------- omm | Sysadmin, Create role, Create DB, Replication, Administer audit, UseFT | {} student | Sysadmin | {} studentdb \dg List of roles Role name | Attributes | Member of ---------------------------------------------------------------------------------------------- omm | Sysadmin, Create role, Create DB, Replication, Administer audit, UseFT | {} student | Sysadmin | {} studentdb 1.3.3 \db命令
元命令\db的作用是显示openGauss数据库集簇中目前有哪些表空间
\db studentdb \db List of tablespaces Name | Owner | Location -------------------------------------------------------- pg_default | omm | pg_global | omm | student_ts | omm | tablespace/student_ts1 (3 rows) studentdb 1.3.4 \dn命令
元命令\dn的作用是显示当前数据库有哪些数据库模式
\dn studentdb \dn List of schemas Name | Owner -------------------------- cstore | omm dbe_perf | omm public | omm snapshot | omm (4 rows) studentdb 1.3.5 \d命令
元命令\d的作用是显示当前数据库下的所有的数据库对象(相当于命令\dtvsE这里E表示外部表)
\d List of relations Schema | Name | Type | Owner | Storage -------------------------------------------------------------------------------------------- public | advisor | table | student | {orientationrow,compressionno} public | classroom | table | student | {orientationrow,compressionno} public | course | table | student | {orientationrow,compressionno} public | department | table | student | {orientationrow,compressionno} public | instructor | table | student | {orientationrow,compressionno} public | prereq | table | student | {orientationrow,compressionno} public | section | table | student | {orientationrow,compressionno} public | student | table | student | {orientationrow,compressionno} public | takes | table | student | {orientationrow,compressionno} public | teaches | table | student | {orientationrow,compressionno} public | time_slot | table | student | {orientationrow,compressionno} (11 rows) studentdb 1.3.6 \dt命令
元命令\dt的作用是显示数据库中所有的表
\dt studentdb \dt List of relations Schema | Name | Type | Owner | Storage ---------------------------------------------------------------------- public | advisor | table | student | {orientationrow,compressionno} public | classroom | table | student | {orientationrow,compressionno} public | course | table | student | {orientationrow,compressionno} public | department | table | student | {orientationrow,compressionno} public | instructor | table | student | {orientationrow,compressionno} public | prereq | table | student | {orientationrow,compressionno} public | section | table | student | {orientationrow,compressionno} public | student | table | student | {orientationrow,compressionno} public | takes | table | student | {orientationrow,compressionno} public | teaches | table | student | {orientationrow,compressionno} public | time_slot | table | student | {orientationrow,compressionno} (11 rows) studentdb 元命令\dt的作用是以扩展的方式显示数据库中所有的表
\dt studentdb \dt List of relations Schema | Name | Type | Owner | Size | Storage | Description ----------------------------------------------------------------------------------------------- public | advisor | table | student | 8192 bytes | {orientationrow,compressionno} | public | classroom | table | student | 8192 bytes | {orientationrow,compressionno} | public | course | table | student | 8192 bytes | {orientationrow,compressionno} | public | department | table | student | 8192 bytes | {orientationrow,compressionno} | public | instructor | table | student | 8192 bytes | {orientationrow,compressionno} | public | prereq | table | student | 8192 bytes | {orientationrow,compressionno} | public | section | table | student | 8192 bytes | {orientationrow,compressionno} | public | student | table | student | 8192 bytes | {orientationrow,compressionno} | public | takes | table | student | 8192 bytes | {orientationrow,compressionno} | public | teaches | table | student | 8192 bytes | {orientationrow,compressionno} | public | time_slot | table | student | 8192 bytes | {orientationrow,compressionno} | (11 rows) studentdb 后面增加一个号表示显示更多的信息。
1.3.7 \di命令
元命令\di的作用是查看数据库中索引的信息
\di studentdb \di List of relations Schema | Name | Type | Owner | Table | Storage -------------------------------------------------------------- public | advisor_pkey | index | student | advisor | public | classroom_pkey | index | student | classroom | public | course_pkey | index | student | course | public | department_pkey | index | student | department | public | instructor_pkey | index | student | instructor | public | prereq_pkey | index | student | prereq | public | section_pkey | index | student | section | public | student_pkey | index | student | student | public | takes_pkey | index | student | takes | public | teaches_pkey | index | student | teaches | public | time_slot_pkey | index | student | time_slot | (11 rows) studentdb 1.3.8 \dv命令
元命令\di的作用是查看数据库中索引的信息。
测试数据集目前暂时没有视图因此首先创建一个视图
create or replace view faculty as select ID, name, dept_name from instructor; studentdb create or replace view faculty as studentdb- select ID, name, dept_name studentdb- from instructor; CREATE VIEW studentdb 执行gsql元命令\dv查询当前数据库下有哪些视图
\dv studentdb \dv List of relations Schema | Name | Type | Owner | Storage -------------------------------------------------- public | faculty | view | student | (1 row) studentdb 删除刚刚创建的视图 drop view faculty; studentdb drop view faculty; DROP VIEW studentdb 1.3.9 \ds命令
元命令\ds的作用是查看数据库中序列的信息。
测试数据集目前暂时没有序列因此首先创建一个表其两列都是序列创建该表会自动创建2个序列
DROP TABLE IF EXISTS test;
create table test(id serial primary key,testnum serial); studentdb DROP TABLE IF EXISTS test; NOTICE: table test does not exist, skipping DROP TABLE studentdb create table test(id serial primary key,testnum serial); NOTICE: CREATE TABLE will create implicit sequence test_id_seq for serial column test.id NOTICE: CREATE TABLE will create implicit sequence test_testnum_seq for serial column test.testnum NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test CREATE TABLE studentdb 执行gsql元命令\ds查询当前数据库下有哪些序列
\ds studentdb \ds List of relations Schema | Name | Type | Owner | Storage ------------------------------------------------------ public | test_id_seq | sequence | student | public | test_testnum_seq | sequence | student | (2 rows) studentdb 删除刚刚创建的测试表,序列也同时被删除使用\ds命令已经查不到有任何序列了
DROP TABLE IF EXISTS test;
\ds studentdb DROP TABLE IF EXISTS test; DROP TABLE studentdb \ds No relations found. studentdb 1.3.10 \df命令
元命令\df的作用是查看数据库中关于存储函数的信息。
因为目前数据库中暂时没有任何函数首先执行下面的语句创建一个测试用的函数
CREATE OR REPLACE FUNCTION myfunction(s INT)
RETURN INT
AS
BEGIN IF(s0) THEN RETURN 1; ELSEIF(s0) THEN RETURN -1; ELSE RETURN 0; END IF;
END
/ studentdb CREATE OR REPLACE FUNCTION myfunction(s INT) studentdb- RETURN INT studentdb- AS studentdb$ BEGIN studentdb$ IF(s0) THEN studentdb$ RETURN 1; studentdb$ ELSEIF(s0) THEN studentdb$ RETURN -1; studentdb$ ELSE studentdb$ RETURN 0; studentdb$ END IF; studentdb$ END studentdb$ / CREATE FUNCTION studentdb 然后执行元命令\df查看当前数据库下有什么函数
\df studentdb \df List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage ---------------------------------------------------------------------------------------------------------------------------------------- public | myfunction | integer | s integer | normal | f | f (1 row) studentdb 删除用于测试的函数
drop function myfunction;
\df studentdb drop function myfunction; DROP FUNCTION studentdb \df List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage --------------------------------------------------------------------------------------------------------------------- (0 rows) studentdb 1.3.11 \d TableName命令
元命令\d TableName的作用是查看某个表的信息。
执行下面的命令查看表instructor的信息
\d instructor studentdb \d instructor Table public.instructor Column | Type | Modifiers --------------------------------------------- id | character varying(5) | not null dept_name | character varying(20) | name | character varying(20) | not null salary | numeric(8,2) | Indexes: instructor_pkey PRIMARY KEY, btree (id) TABLESPACE student_ts Foreign-key constraints: fk_sys_c0011280 FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON UPDATE RESTRICT ON DELETE RESTRICT Referenced by: TABLE teaches CONSTRAINT fk_sys_c0011287 FOREIGN KEY (id) REFERENCES instructor(id) ON UPDATE RESTRICT ON DELETE RESTRICT TABLE advisor CONSTRAINT fk_sys_c0011297 FOREIGN KEY (id) REFERENCES instructor(id) ON UPDATE RESTRICT ON DELETE RESTRICT studentdb 可以看到显示表的信息包括表的列名及数据类型、索引、外键、以及被哪个表引用。如果表不在数据库默认的表空间还将显示表所在的表空间。这一点可以做个测试
CREATE TABLESPACE test_ts RELATIVE LOCATION tablespace/test_ts1;
DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 smallint) TABLESPACE test_ts;
\d test
drop table test;
drop tablespace test_ts; studentdb CREATE TABLESPACE test_ts RELATIVE LOCATION tablespace/test_ts1; CREATE TABLESPACE studentdb DROP TABLE IF EXISTS test; NOTICE: table test does not exist, skipping DROP TABLE studentdb CREATE TABLE test(col1 smallint) TABLESPACE test_ts; CREATE TABLE studentdb \d test Table public.test Column | Type | Modifiers ----------------------------- col1 | smallint | Tablespace: test_ts 因为表test不在数据库的默认表空间中因此显示表test所在的表空间 studentdb drop table test; DROP TABLE studentdb drop tablespace test_ts; DROP TABLESPACE studentdb 1.3.12 \di IndexName命令
元命令\di IndexName的作用是查看某个索引的信息。
执行下面的元命令查看当前数据库下有哪些索引
\di studentdb \di List of relations Schema | Name | Type | Owner | Table | Storage -------------------------------------------------------------- public | advisor_pkey | index | student | advisor | public | classroom_pkey | index | student | classroom | public | course_pkey | index | student | course | public | department_pkey | index | student | department | public | instructor_pkey | index | student | instructor | public | prereq_pkey | index | student | prereq | public | section_pkey | index | student | section | public | student_pkey | index | student | student | public | takes_pkey | index | student | takes | public | teaches_pkey | index | student | teaches | public | time_slot_pkey | index | student | time_slot | (11 rows) studentdb 执行下面的元命令显示索引instructor_pkey的详细信息
\di instructor_pkey studentdb \di instructor_pkey List of relations Schema | Name | Type | Owner | Table | Storage ------------------------------------------------------------------------------- public | instructor_pkey | index | student | instructor | (1 row) studentdb 1.3.13 \dx命令
元命令\dx的作用是查看已安装的扩展程序信息。
\dx studentdb \dx List of installed extensions Name | Version | Schema | Description -------------------------------------------------------------------------------------------------- mot_fdw | 1.0 | pg_catalog | foreign-data wrapper for MOT access plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) studentdb 1.3.14 \x命令
元命令\x的语法 \x [ on | off | auto ]
元命令\dx的作用是设置语句的输出模式。默认情况下记录上按行的方式来显示的。如果执行元命令\x on则显示将按每条记录每列的方式来显示。这种方式在有些情况下很有用。
DROP TABLE IF EXISTS test;
create table test(id int,name varchar(20));
insert into test values(1,zqf),(2,zfz);
select * from test;
\x on
select * from test;
\x off
DROP TABLE IF EXISTS test; studentdb DROP TABLE IF EXISTS test; DROP TABLE studentdb create table test(id int,name varchar(20)); CREATE TABLE studentdb insert into test values(1,zqf),(2,zfz); INSERT 0 2 studentdb select * from test; 默认情况下查询语句的显示方式为行方式 id | name ---------- 1 | zqf 2 | zfz (2 rows) studentdb \x on 修改显示方式为列方式 Expanded display is on. studentdb select * from test; -[ RECORD 1 ] id | 1 name | zqf -[ RECORD 2 ] id | 2 name | zfz studentdb \x off 修改显示方式为行方式 Expanded display is off. studentdb DROP TABLE IF EXISTS test; DROP TABLE studentdb 1.3.15 \timing命令
元命令\timing的语法 \timing [ on | off ]
元命令\timing的作用是如果设置为on将显示SQL语句的执行时间。
select * from instructor where salary80000;
\timing on
select * from instructor where salary80000;
\timing off studentdb select * from instructor where salary80000; id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) studentdb \timing on Timing is on. studentdb select * from instructor where salary80000; id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) Time: 0.436 ms studentdb \timing off Timing is off. studentdb 1.3.16 \h命令
元命令\h的作用是获取SQL语句的帮助。例如我们想获取update语句的帮助信息
\h update studentdb \h update Command: UPDATE Description: update rows of a table Syntax: UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET {column_name { expression | DEFAULT } | ( column_name [, ...] ) {( { expression | DEFAULT } [, ...] ) |sub_query } }[, ...] [ FROM from_list] [ WHERE condition ] [ RETURNING {* | {output_expression [ [ AS ] output_name ]} [, ...] }]; studentdb 想获取insert语句的帮助信息
\h insert studentdb \h insert Command: INSERT Description: create new rows in a table Syntax: [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] | query } [ ON DUPLICATE KEY UPDATE { column_name { expression | DEFAULT } } [, ...] ] [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ]; studentdb 1.3.17 \?命令
元命令\?的作用是获取gsql元命令的帮助。
\? studentdb \? General \copyright show FusionInsight LibrA usage and distribution terms \g [FILE] or ; execute query (and send results to file or |pipe) \h(\help) [NAME] help on syntax of SQL commands, * for all commands \parallel [on [num]|off] toggle status of execute (currently off) \q quit gsql Query Buffer \e [FILE] [LINE] edit the query buffer (or file) with external editor \ef [FUNCNAME [LINE]] edit function definition with external editor \p show the contents of the query buffer \r reset (clear) the query buffer \w FILE write query buffer to file Input/Output \copy ... perform SQL COPY with data stream to the client host \echo [STRING] write string to standard output \i FILE execute commands from file \i FILE KEY execute commands from encrypted file \ir FILE as \i, but relative to location of current script \ir FILE KEY as \i, but relative to location of current script \o [FILE] send all query results to file or |pipe \qecho [STRING] write string to query output stream (see \o) Informational (options: S show system objects, additional detail) \d[S] list tables, views, and sequences \d[S] NAME describe table, view, sequence, or index \da[S] [PATTERN] list aggregates \db[] [PATTERN] list tablespaces \dc[S] [PATTERN] list conversions \dC[] [PATTERN] list casts \dd[S] [PATTERN] show object descriptions not displayed elsewhere \ddp [PATTERN] list default privileges \dD[S] [PATTERN] list domains \ded[] [PATTERN] list data sources \det[] [PATTERN] list foreign tables \des[] [PATTERN] list foreign servers \deu[] [PATTERN] list user mappings \dew[] [PATTERN] list foreign-data wrappers \df[antw][S] [PATRN] list [only agg/normal/trigger/window] functions \dF[] [PATTERN] list text search configurations \dFd[] [PATTERN] list text search dictionaries \dFp[] [PATTERN] list text search parsers \dFt[] [PATTERN] list text search templates \dg[] [PATTERN] list roles \di[S] [PATTERN] list indexes \dl list large objects, same as \lo_list \dL[S] [PATTERN] list procedural languages \dm[S] [PATTERN] list materialized views \dn[S] [PATTERN] list schemas \do[S] [PATTERN] list operators \dO[S] [PATTERN] list collations \dp [PATTERN] list table, view, and sequence access privileges \drds [PATRN1 [PATRN2]] list per-database role settings \ds[S] [PATTERN] list sequences \dt[S] [PATTERN] list tables \dT[S] [PATTERN] list data types \du[] [PATTERN] list roles \dv[S] [PATTERN] list views \dE[S] [PATTERN] list foreign tables \dx[] [PATTERN] list extensions \l[] list all databases \sf[] FUNCNAME show a functions definition \z [PATTERN] same as \dp Formatting \a toggle between unaligned and aligned output mode \C [STRING] set table title, or unset if none \f [STRING] show or set field separator for unaligned query output \H toggle HTML output mode (currently off) \pset NAME [VALUE] set table output option (NAME : {format|border|expanded|fieldsep|fieldsep_zero|footer|null| numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager}) \t [on|off] show only rows (currently off) \T [STRING] set HTML table tag attributes, or unset if none \x [on|off|auto] toggle expanded output (currently off) Connection \c[onnect] [DBNAME|- USER|- HOST|- PORT|-] connect to new database (currently studentdb) \encoding [ENCODING] show or set client encoding \conninfo display information about current connection Operating System \cd [DIR] change the current working directory \setenv NAME [VALUE] set or unset environment variable \timing [on|off] toggle timing of commands (currently off) \! [COMMAND] execute command in shell or start interactive shell Variables \prompt [TEXT] NAME prompt user to set internal variable \set [NAME [VALUE]] set internal variable, or list all if no parameters \unset NAME unset (delete) internal variable Large Objects \lo_export LOBOID FILE \lo_import FILE [COMMENT] \lo_list \lo_unlink LOBOID large object operations studentdb 1.3.18 \! os_command命令
元命令\! os_command的作用是在gsql中执行操作系统命令。例如
\! ls -l studentdb \! ls -l total 20 -rw------- 1 omm dbgrp 7545 Nov 1 16:08 create_db_tables.sql -rw------- 1 omm dbgrp 10590 Nov 1 16:08 load_data.sql studentdb 1.3.19 \o fileName命令
元命令\o fileName的作用是重定向输出到文件fileName。例如
select * from instructor where salary80000;
\o myoutputfile
select * from instructor where salary80000;
\! cat myoutputfile studentdb select * from instructor where salary80000; id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) studentdb \o myoutputfile studentdb select * from instructor where salary80000; studentdb \! cat myoutputfile id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) studentdb 从上面的测试可以看出\o myoutputfile将输出重定向到文件myoutputfile中而不是把查询结果显示在终端上。
1.3.20 \i file.sql命令
元命令\i file.sql的作用是在交互式gsql中执行文件file.sql中的SQL语句。下面是一个示例。
用Linux用户omm打开一个终端执行下面的命令生成一个测试用的SQL语句脚本
cat test.sqlEOF
select * from instructor where salary80000;
EOF [ommtest ~]$ cat test.sqlEOF select * from instructor where salary80000; EOF [ommtest ~]$ 登录到openGauss
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb 执行下面的命令运行sql语句脚本文件
\i test.sql studentdb \i test.sql id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) studentdb 1.3.21 \conninfo命令
元命令\conninfo的作用是在gsql中显示会话的连接信息。
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r
\conninfo [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb \conninfo You are connected to database studentdb as user student on host 192.168.100.91 at port 26000. studentdb 1.3.22 \c[onnect] [DBNAME]命令
元命令\ c[onnect] [DBNAME]的作用是在gsql中切换连接的数据库。
使用Linux用户omm执行下面的命令和语句进行测试
gsql -d postgres -p 26000 -r
CREATE TABLESPACE test_ts RELATIVE LOCATION tablespace/test_ts1;
CREATE DATABASE testdb WITH TABLESPACE test_ts;
-- 切换连接到数据库testdb
\c testdb
-- 切换连接到数据库studentdb
\connect studentdb
DROP DATABASE testdb;
DROP TABLESPACE test_ts;
\q [ommtest ~]$ gsql -d postgres -p 26000 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type help for help. postgres# CREATE TABLESPACE test_ts RELATIVE LOCATION tablespace/test_ts1; CREATE TABLESPACE postgres# CREATE DATABASE testdb WITH TABLESPACE test_ts; CREATE DATABASE postgres# -- 切换连接到数据库testdb postgres# \c testdb Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database testdb as user omm. testdb# -- 切换连接到数据库studentdb testdb# \connect studentdb Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database studentdb as user omm. studentdb# DROP DATABASE testdb; DROP DATABASE studentdb# DROP TABLESPACE test_ts; DROP TABLESPACE studentdb# \q [ommtest ~]$ 1.3.23 \echo [string]命令
元命令\echo [string]的作用是在标准输出上显式信息。
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r
\echo Hello,openGauss from Huawei! [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb \echo Hello,openGauss from Huawei! Hello,openGauss from Huawei! studentdb 1.3.24 \q命令和快捷键ctrld
元命令\q的作用是退出gsql。也可以使用快捷键ctrld来退出gsql。
\q studentdb \q [ommtest ~]$ 1.4 gsql初始化文件.gsqlrc
环境变量PSQLRC用来设置.gsqlrc文件的目录位置。在Linux的终端上使用用户omm执行下面的命令来设置PSQLRC环境变量
export PLSQLRCPathYourWantItToBe
如果没有设置PSQLRC环境变量那么将默认读取Linux用户主目录下的.gsqlrc文件。
下面进行测试。首先不设置.gsqlrc文件在gsql中运行下面的SQL语句
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r
select * from instructor where salary80000;
\q [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb select * from instructor where salary80000; id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) 默认情况下不显示查询语句的执行时间 studentdb \q [ommtest ~]$ 我们看到执行完SQL语句不会显式该SQL语句的执行时间。
接下来在linux用户的主目录下创建如下的.gsqlrc文件
cat ~/.gsqlrcEOF
\timing on
EOF [ommtest ~]$ cat ~/.gsqlrcEOF \timing on 设置gsql在执行完一条查询语句后显示查询语句的执行时间 EOF [ommtest ~]$ 重新登录到openGauss数据库并执行同样的SQL语句
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r
select * from instructor where salary80000;
\q
rm ~/.gsqlrc [ommtest ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W studentustb2020 -r Timing is on. 已经通过.gsqlrc文件设置了Timing变量为on作用是显示查询语句的执行时机 gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr ) SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128) Type help for help. studentdb select * from instructor where salary80000; id | dept_name | name | salary ------------------------------------ 76543 | Finance | Singh | 80000.00 98345 | Elec. Eng. | Kim | 80000.00 (2 rows) Time: 0.687 ms 执行完一条查询语句后显示查询语句的执行时间 studentdb \q [ommtest ~]$ rm ~/.gsqlrc [ommtest ~]$ 以上的实验表明gsql命令启动时默认情况下会自动运行主目录下的初始化文件.gsqlrc中的设置命令。 2.客户端安全
2.1配置客户端接入认证
参考官方文档07 开发者指南-5.1 客户端接入认证-5.1.1 配置客户端接入认证 实验 以操作系统用户omm登录数据库主节点。配置客户端认证方式允许客户端以“jack”用户连接到本机此处远程连接禁止使用“omm”用户即数据库初始化用户。
例如下面示例中配置允许IP地址为192.168.0.1的客户端访问本机。
gs_guc set -N all -I all -h host all jack 192.168.0.1/32 sha256 postgres# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------------------------------------------------------- mydb | omm | GBK | C | C | postgres | omm | SQL_ASCII | C | C | template0 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm template1 | omm | SQL_ASCII | C | C | c/omm | | | | | ommCTc/omm testdb | jack | SQL_ASCII | C | C | testdb1 | omm | SQL_ASCII | C | C | testdb2 | omm | SQL_ASCII | C | C | testdb3 | omm | SQL_ASCII | C | C | (8 rows) [ommnode1 ~]$ gs_guc set -N all -I all -h host all all 192.168.0.1/32 sha256 Begin to perform the total nodes: 1. Popen count is 1, Popen success count is 1, Popen failure count is 0. Begin to perform gs_guc for datanodes. Command count is 1, Command success count is 1, Command failure count is 0. Total instances: 1. Failed instances: 0. ALL: Success to perform gs_guc! [ommnode1 ~]$ pg_om -t status bash: pg_om: command not found... [ommnode1 ~]$ gs_om -t status ----------------------------------------------------------------------- cluster_name : dbCluster cluster_state : Normal redistributing : No ----------------------------------------------------------------------- 1、如果为了支持MD5和sha256需要修改password_encryption_type参数 gs_guc reload -N all -I all -c password_encryption_type1
默认是password_encryption_type2 、postgresql.conf
# - Connection Settings - listen_addresses 192.168.0.11 # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to localhost; use * for all # (change requires restart)
local_bind_address 192.168.0.11
port 26000 # (change requires restart) 2.2客户端验证 说明
可以正常登录。
● 使用“jack”用户前需先本地连接数据库并在数据库中使用如下语句建立“jack”用
户
postgres# CREATE USER jack PASSWORD Test123;
● -N all表示openGauss的所有主机。
● -I all表示主机的所有实例。
● -h表示指定需要在“pg_hba.conf”增加的语句。
● all表示允许客户端连接到任意的数据库。
● jack表示连接数据库的用户。
● 192.168.0.1/32表示只允许IP地址为192.168.0.1的主机连接。此处的IP地址不能为openGauss内 的IP在使用过程中请根据用户的网络进行配置修改。32表示子网掩码为1的位数即
255.255.255.255
● sha256表示连接时jack用户的密码使用sha256算法加密。
这条命令在数据库主节点实例对应的“pg_hba.conf”文件中添加了一条规则用于对
连接数据库主节点的客户端进行鉴定。
“pg_hba.conf”文件中的每条记录可以是下面四种格式之一四种格式的参数说明请
参见5.1.2 配置文件参考。
local DATABASE USER METHOD [OPTIONS]
host DATABASE USER ADDRESS METHOD [OPTIONS]
hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
因为认证时系统是为每个连接请求顺序检查“pg_hba.conf”里的记录的所以这些记
录的顺序是非常关键的。
说明
在配置“pg_hba.conf”文件时请依据通讯需求按照格式内容从上至下配置记录优先级高的
需求需要配置在前面。openGauss和扩容配置的IP优先级最高用户手动配置的IP请放在这二者
之后如果已经进行的客户配置和扩容节点的IP在同一网段请在扩容前删除扩容成功后再进
行配置。
因此对于认证规则的配置建议如下
● 靠前的记录有比较严格的连接参数和比较弱的认证方法。
● 靠后的记录有比较宽松的连接参数和比较强的认证方法。
说明
● 一个用户要想成功连接到特定的数据库不仅需要通过pg_hba.conf中的规则检查还必须
要有该数据库上的CONNECT权限。如果希望控制某些用户只能连接到指定数据库赋予/撤 销CONNECT权限通常比在pg_hba.conf中设置规则更为简单。
● 对应openGauss外部客户端连接trust为不安全的认证方式请将认证方式设置为sha256。
【其他】
如果想要在服务器上
[ommnode1 dn]$ gsql -d testdb -p 26000 -U jack -h 192.168.0.11 -r
Password for user jack:
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr )
SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)
Type help for help. testdb 需要修改cd /opt/huawei/install/data/dn vim pg_hba.conf host testdb jack 192.168.0.11/32 sha256
