華為openGauss高斯數(shù)據(jù)庫(kù) - gsql用法

這篇具有很好參考價(jià)值的文章主要介紹了華為openGauss高斯數(shù)據(jù)庫(kù) - gsql用法。希望對(duì)大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請(qǐng)大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問(wèn)。

1.1 gsql的語(yǔ)法

1.2 gsql常用選項(xiàng)

1.2.1? 最常用的必要選項(xiàng)

1.2.2 -r選項(xiàng)

1.2.3 -E選項(xiàng)

1.2.4 -t選項(xiàng)

1.2.5 -A選項(xiàng)

1.2.6 -v選項(xiàng)

1.2.7 -c選項(xiàng)

1.2.8 -f選項(xiàng)

1.2.9 -q選項(xiàng)

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命令和快捷鍵ctrl+d

1.4 gsql初始化文件.gsqlrc

2.客戶(hù)端安全

2.1配置客戶(hù)端接入認(rèn)證

2.2客戶(hù)端驗(yàn)證

1.1 gsql的語(yǔ)法

gsql --help

[omm@test ~]$ gsql --help

gsql is the FusionInsight LibrA interactive terminal.

Usage:

? gsql [OPTION]... [DBNAME [USERNAME]]

General options:

? -c, --command=COMMAND??? run only single command (SQL or internal) and exit

? -d, --dbname=DBNAME????? database name to connect to (default: "omm")

? -f, --file=FILENAME????? execute commands from file, then exit

? -l, --list?????????????? list available databases, then exit

? -v, --set=, --variable=NAME=VALUE

?????????????????????????? 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-key=KEY?????? the key for decrypting the encrypted file

? -L, --log-file=FILENAME? 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, --output=FILENAME??? 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-separator=STRING

?????????????????????????? set field separator (default: "|")

? -H, --html?????????????? HTML table output mode

? -P, --pset=VAR[=ARG]???? set printing option VAR to ARG (see \pset command)

? -R, --record-separator=STRING

?????????????????????????? set record separator (default: newline)

? -r??????? ????????????? ???if this parameter is set,use libedit

? -t, --tuples-only??????? print rows only

? -T, --table-attr=TEXT??? 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, --host=HOSTNAME????? database server host or socket directory (default: "/opt/gaussdbi/tmp")

? -p, --port=PORT????????? database server port (default: "5432")

? -U, --username=USERNAME? database user name (default: "omm")

? -W, --password=PASSWORD? 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-bugs@postgresql.org>.

[omm@test ~]$

1.2 gsql常用選項(xiàng)

1.2.1? 最常用的必要選項(xiàng)

使用gsql命令，用數(shù)據(jù)庫(kù)用戶(hù)student，連接到openGauss數(shù)據(jù)庫(kù)管理系統(tǒng)下的studentdb數(shù)據(jù)庫(kù)：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

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=>

其中的選項(xiàng)說(shuō)明如下：

-d選項(xiàng)：指定gsql客戶(hù)端連接的數(shù)據(jù)庫(kù)

-h選項(xiàng)：指定gsql客戶(hù)端連接的服務(wù)器IP

-U選項(xiàng)：指定gsql客戶(hù)端連接數(shù)據(jù)庫(kù)的用戶(hù)名

-p選項(xiàng)：指定gsql客戶(hù)端連接的服務(wù)器端口號(hào)

-W選項(xiàng)：指定gsql客戶(hù)端連接的用戶(hù)密碼

1.2.2 -r選項(xiàng)

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql命令的-r選項(xiàng)，第一個(gè)作用是當(dāng)我們?cè)趃sql中執(zhí)行了很多語(yǔ)句后，如果我們想重新執(zhí)行之前執(zhí)行過(guò)的語(yǔ)句，可以使用上箭頭和下箭頭，向前和向后翻閱之前執(zhí)行過(guò)的命令和SQL語(yǔ)句。

執(zhí)行下面的語(yǔ)句：使用上箭頭可以提供很多便利性。

SELECT * FROM instructor WHERE salary=90000;

studentdb=> SELECT * FROM instructor WHERE salary=90000;

? id?? | dept_name | name |? salary?

----------+------------------+---------+----------

12121? | Finance??? | Wu?? | 90000.00

(1 row)

studentdb=>

1.2.3 -E選項(xiàng)

-E選項(xiàng)會(huì)讓gsql客戶(hù)端程序再執(zhí)行元命令的時(shí)候，顯示其對(duì)應(yīng)的SQL語(yǔ)句。

使用Linux用戶(hù)omm，打開(kāi)一個(gè)Linux終端窗口，執(zhí)行g(shù)sql的元命令\l，該命令的作用是顯示當(dāng)前系統(tǒng)有哪些數(shù)據(jù)庫(kù)：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -E

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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選項(xiàng)會(huì)顯示gsql客戶(hù)端的元命令對(duì)應(yīng)的SQL語(yǔ)句

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

-----------+-------+-----------+---------+-------+-------------------

?postgres? | omm?? | SQL_ASCII | C?????? | C???? |

?studentdb | omm?? | SQL_ASCII | C?????? | C???? |

?template0 | omm?? | SQL_ASCII | C?????? | C???? | =c/omm?????????? +

?????????? |?????? |?????????? |???????? |?????? | omm=CTc/omm

?template1 | omm? ?| SQL_ASCII | C?????? | C???? | =c/omm?????????? +

?????????? |?????? |?????????? |???????? |?????? | omm=CTc/omm

(4 rows)

studentdb=> \q

[omm@test ~]$

如果不使用-E選項(xiàng)，同樣執(zhí)行上面的命令系列，結(jié)果如下：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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

-----------+-------+-----------+---------+-------+-------------------

?postgres? | omm? ?| SQL_ASCII | C?????? | C???? |

?studentdb | omm?? | SQL_ASCII | C?????? | C???? |

?template0 | omm?? | SQL_ASCII | C?????? | C???? | =c/omm?????????? +

?????????? |?????? |?????????? |???????? |?????? | omm=CTc/omm

?template1 | omm?? | SQL_ASCII | C?? ????| C???? | =c/omm?????????? +

?????????? |?????? |?????????? |???????? |?????? | omm=CTc/omm

(4 rows)

studentdb=> \q

[omm@test ~]$

可以看出，如果沒(méi)有-E選項(xiàng)，不會(huì)顯示元命令\l對(duì)應(yīng)的SQL查詢(xún)語(yǔ)句。

1.2.4 -t選項(xiàng)

-t選項(xiàng)會(huì)讓gsql客戶(hù)端程序在執(zhí)行SQL查詢(xún)語(yǔ)句的時(shí)候，返回的結(jié)果不顯示列名及返回結(jié)果的行數(shù)。

使用Linux用戶(hù)omm，打開(kāi)一個(gè)Linux終端窗口，執(zhí)行如下的命令：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -t

select * from instructor where salary=80000;

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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 salary=80000;

?76543 | Finance??? | Singh | 80000.00

?98345 | Elec. Eng.?? | Kim?? | 80000.00

studentdb=> \q

[omm@test ~]$

1.2.5 -A選項(xiàng)

-A選項(xiàng)會(huì)讓gsql客戶(hù)端程序在執(zhí)行SQL查詢(xún)語(yǔ)句的時(shí)候，不對(duì)齊顯示查詢(xún)返回的行數(shù)據(jù),如圖6-7所示（列不是對(duì)齊的?。?/p>

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -A

select * from instructor where salary=80000;

華為openGauss高斯數(shù)據(jù)庫(kù) - gsql用法,收藏學(xué)習(xí),數(shù)據(jù)庫(kù),oracle,服務(wù)器

圖6-8 -A選項(xiàng)表示不對(duì)齊顯式

可以將-A和-t兩個(gè)選項(xiàng)同時(shí)應(yīng)用，表示不對(duì)齊顯示，也不顯示列名和返回行數(shù)：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -At

select * from instructor where salary=80000;

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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 salary=80000;

76543|Finance|Singh|80000.00

98345|Elec. Eng.|Kim|80000.00

studentdb=> \q

[omm@test ~]$

1.2.6 -v選項(xiàng)

-v選項(xiàng)會(huì)讓gsql客戶(hù)端程序在命令行中設(shè)置gsql環(huán)境變量。

如果我們想在命令行中告訴gsql啟動(dòng)后關(guān)閉自動(dòng)提交，設(shè)置為手動(dòng)事務(wù)提交，可以執(zhí)行如下命令：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

-v AUTOCOMMIT=off -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

> -v AUTOCOMMIT=off -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選項(xiàng)

-c選項(xiàng)會(huì)讓gsql客戶(hù)端程序直接在命令行中運(yùn)行SQL語(yǔ)句，示例如下：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

???? -c "select * from instructor where salary=80000"

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

>????? -c "select * from instructor where salary=80000"

? id?? | dept_name? | name? |? salary?

----------+--------------------+------------+----------

?76543 | Finance???? | Singh?? | 80000.00

?98345 | Elec. Eng.??? | Kim??? | 80000.00

(2 rows)

[omm@test ~]$

1.2.8 -f選項(xiàng)

-f選項(xiàng)會(huì)讓gsql客戶(hù)端程序直接在命令行中直接運(yùn)行SQL腳本文件。

首先用omm用戶(hù)，生成一個(gè)測(cè)試用的SQL語(yǔ)句腳本：

cat > test.sql<<EOF

select * from instructor where salary=80000;

EOF

[omm@test ~]$ cat > test.sql<<EOF

> select * from instructor where salary=80000;

> EOF

[omm@test ~]$

然后使用gsql客戶(hù)端程序在命令行中直接執(zhí)行剛剛創(chuàng)建的SQL語(yǔ)句腳本：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -f test.sql

rm test.sql

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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

[omm@test ~]$ rm test.sql

[omm@test ~]$

1.2.9 -q選項(xiàng)

-q選項(xiàng)會(huì)讓gsql客戶(hù)端程序以安靜的方式運(yùn)行，只顯示查詢(xún)結(jié)果。

首先用omm用戶(hù)，執(zhí)行下面的gsql命令（使用了-q選項(xiàng)），創(chuàng)建test表，然后再次執(zhí)行g(shù)sql命令（不使用-q選項(xiàng)），刪除剛剛創(chuàng)建的test表，接著再次執(zhí)行g(shù)sql命令（不使用-q選項(xiàng)），重新創(chuàng)建test表，最后又一次執(zhí)行g(shù)sql命令（使用了-q選項(xiàng)），刪除剛剛創(chuàng)建的test表：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

???? -c "create table test(col char)" -q

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

???? -c "drop table test"

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

???? -c "create table test(col char)"

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

???? -c "drop table test" -q

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

>????? -c "create table test(col char)" -q

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

>????? -c "drop table test"

DROP TABLE

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

>????? -c "create table test(col char)"

CREATE TABLE

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

>????? -c "drop table test" -q

[omm@test script]$

我們發(fā)現(xiàn)使用了-q選項(xiàng)的gsql沒(méi)有顯示任何信息。

執(zhí)行下面的命令，進(jìn)行SQL查詢(xún)，我們發(fā)現(xiàn)雖然有-q選項(xiàng)，但是會(huì)顯示查詢(xún)結(jié)果。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

???? -c "select * from instructor where salary=80000" -q

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 \

>????? -c "select * from instructor where salary=80000" -q

? id?? | dept_name? | name? |? salary?

----------+---------------------+----------+----------

?76543 | Finance???? | Singh? | 80000.00

?98345 | Elec. Eng.??? | Kim?? | 80000.00

(2 rows)

[omm@test script]$

1.3 gsql的元命令

本節(jié)的測(cè)試均使用下面的命令登錄到openGauss數(shù)據(jù)庫(kù)：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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數(shù)據(jù)庫(kù)集簇中，目前有哪些數(shù)據(jù)庫(kù)：

studentdb=> \l

????????????????????????? List of databases

-----------+-------+-----------+---------+-------+-------------------

?postgres? | omm ??| SQL_ASCII | C?????? | C???? |

?studentdb | omm?? | SQL_ASCII | C?????? | C???? |

?template0 | omm?? | SQL_ASCII | C?????? | C???? | =c/omm?????????? +

????????? |??????? |????????? |???????? |????? | omm=CTc/omm

?template1 | omm?? | SQL_ASCII | C??? ????| C??? | =c/omm?????????? +

????????? |??????? |????????? |???????? |????? | omm=CTc/omm

(4 rows)

studentdb=>

1.3.2 \du命令和\dg命令

元命令\dg命令與元命令\du命令的作用類(lèi)似，都是顯示openGauss數(shù)據(jù)庫(kù)集簇中，目前有哪些用戶(hù)和角色。

\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數(shù)據(jù)庫(kù)集簇中，目前有哪些表空間：

\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的作用是顯示當(dāng)前數(shù)據(jù)庫(kù)，有哪些數(shù)據(jù)庫(kù)模式：

\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的作用是顯示當(dāng)前數(shù)據(jù)庫(kù)下的所有的數(shù)據(jù)庫(kù)對(duì)象(相當(dāng)于命令\dtvsE，這里E表示外部表)：

???????????????????? List of relations

Schema |??? Name??? | Type? |? Owner? |???????????? Storage?????????????

-----------+---------------------+----------+----------------+----------------------------------

?public | advisor?????? | table? | student?? | {orientation=row,compression=no}

?public | classroom???? | table? | student?? | {orientation=row,compression=no}

?public | course??????? | table? | student?? | {orientation=row,compression=no}

?public | department?? | table? | student?? | {orientation=row,compression=no}

?public | instructor???? | table? | student?? | {orientation=row,compression=no}

?public | prereq??????? | table? | student?? | {orientation=row,compression=no}

?public | section??????? | table? | student?? | {orientation=row,compression=no}

?public | student?????? | table? | student?? | {orientation=row,compression=no}

?public | takes???????? | table? | student?? | {orientation=row,compression=no}

?public | teaches?????? | table? | student?? | {orientation=row,compression=no}

?public | time_slot????? | table? | student?? | {orientation=row,compression=no}

(11 rows)

studentdb=>

1.3.6 \dt命令

元命令\dt的作用是顯示數(shù)據(jù)庫(kù)中所有的表：

\dt

studentdb=> \dt

??????????????????????????? List of relations

?Schema |??? Name??? | Type? |? Owner? |???????????? Storage?????????????

--------+------------+-------+---------+----------------------------------

?public | advisor??? | table | student | {orientation=row,compression=no}

?public | classroom? | table | student | {orientation=row,compression=no}

?public | course???? | table | student | {orientation=row,compression=no}

?public | department | table | student | {orientation=row,compression=no}

?public | instructor | table | student | {orientation=row,compression=no}

?public | prereq???? | table | student | {orientation=row,compression=no}

?public | section??? | table | student | {orientation=row,compression=no}

?public | student??? | table | student | {orientation=row,compression=no}

?public | takes????? | table | student | {orientation=row,compression=no}

?public | teaches??? | table | student | {orientation=row,compression=no}

?public | time_slot? | table | student | {orientation=row,compression=no}

(11 rows)

studentdb=>

元命令\dt+的作用是以擴(kuò)展的方式，顯示數(shù)據(jù)庫(kù)中所有的表：

\dt+

studentdb=> \dt+

????????????????????????????????????????? List of relations

--------+------------+-------+---------+------------+----------------------------------+-------------

?public | advisor??? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | classroom? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | course???? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | department | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | instructor | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | prereq???? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | section??? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | student??? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | takes????? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | teaches??? | table | student | 8192 bytes | {orientation=row,compression=no} |

?public | time_slot? | table | student | 8192 bytes | {orientation=row,compression=no} |

(11 rows)

studentdb=>

后面增加一個(gè)+號(hào)，表示顯示更多的信息。

1.3.7 \di命令

元命令\di的作用是查看數(shù)據(jù)庫(kù)中索引的信息：

\di

studentdb=> \di

???????????????????????? List of relations

--------+-----------------+-------+---------+------------+---------

(11 rows)

studentdb=>

1.3.8 \dv命令

元命令\di的作用是查看數(shù)據(jù)庫(kù)中索引的信息。

測(cè)試數(shù)據(jù)集目前暫時(shí)沒(méi)有視圖，因此首先創(chuàng)建一個(gè)視圖：

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=>

執(zhí)行g(shù)sql元命令\dv，查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)下有哪些視圖：

\dv

studentdb=> \dv

????????????? List of relations

?Schema |? Name?? | Type |? Owner? | Storage

-------------+-------------+------+---------+---------

?public | faculty | view | student |

(1 row)

studentdb=>

刪除剛剛創(chuàng)建的視圖：

drop view faculty;

studentdb=> drop view faculty;

DROP VIEW

studentdb=>

1.3.9 \ds命令

元命令\ds的作用是查看數(shù)據(jù)庫(kù)中序列的信息。

測(cè)試數(shù)據(jù)集目前暫時(shí)沒(méi)有序列，因此首先創(chuàng)建一個(gè)表，其兩列都是序列，創(chuàng)建該表會(huì)自動(dòng)創(chuàng)建2個(gè)序列：

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=>

執(zhí)行g(shù)sql元命令\ds，查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)下有哪些序列：

\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=>

刪除剛剛創(chuàng)建的測(cè)試表,序列也同時(shí)被刪除，使用\ds命令已經(jīng)查不到有任何序列了：

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的作用是查看數(shù)據(jù)庫(kù)中關(guān)于存儲(chǔ)函數(shù)的信息。

因?yàn)槟壳皵?shù)據(jù)庫(kù)中暫時(shí)沒(méi)有任何函數(shù)，首先執(zhí)行下面的語(yǔ)句，創(chuàng)建一個(gè)測(cè)試用的函數(shù)：

CREATE OR REPLACE FUNCTION myfunction(s INT)

RETURN INT

BEGIN

??????? IF(s>0) THEN

?????????? RETURN 1;

??????? ELSEIF(s<0) 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(s>0) THEN

studentdb$>??????????? RETURN 1;

studentdb$>???????? ELSEIF(s<0) THEN

studentdb$>??????????? RETURN -1;

studentdb$>???????? ELSE

studentdb$>??????????? RETURN 0;

studentdb$>???????? END IF;

studentdb$> END

studentdb$> /

CREATE FUNCTION

studentdb=>

然后執(zhí)行元命令\df，查看當(dāng)前數(shù)據(jù)庫(kù)下有什么函數(shù)：

\df

studentdb=> \df

??????????????????????????????????????? List of functions

-------------+---------------------+-----------------------+------------------------------+-------------+------------------+------------------

(1 row)

studentdb=>

刪除用于測(cè)試的函數(shù)：

drop function myfunction;

\df

studentdb=> drop function myfunction;

DROP FUNCTION

studentdb=> \df

??????????????????????????????????? List of functions

-------------+---------+------------------------+-----------------------------+--------+-------------------+---------------

(0 rows)

studentdb=>

1.3.11 \d TableName命令

元命令\d TableName的作用是查看某個(gè)表的信息。

執(zhí)行下面的命令，查看表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=>

可以看到，顯示表的信息包括表的列名及數(shù)據(jù)類(lèi)型、索引、外鍵、以及被哪個(gè)表引用。如果表不在數(shù)據(jù)庫(kù)默認(rèn)的表空間，還將顯示表所在的表空間。這一點(diǎn)可以做個(gè)測(cè)試：

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"????? （因?yàn)楸?/span>test不在數(shù)據(jù)庫(kù)的默認(rèn)表空間中，因此顯示表test所在的表空間）

studentdb=> drop table test;

DROP TABLE

studentdb=> drop tablespace test_ts;

DROP TABLESPACE

studentdb=>

1.3.12 \di IndexName命令

元命令\di IndexName的作用是查看某個(gè)索引的信息。

執(zhí)行下面的元命令，查看當(dāng)前數(shù)據(jù)庫(kù)下有哪些索引：

\di

studentdb=> \di

???????????????????????? List of relations

--------+-----------------+-------+---------+------------+---------

(11 rows)

studentdb=>

執(zhí)行下面的元命令，顯示索引instructor_pkey的詳細(xì)信息：

\di instructor_pkey

studentdb=> \di instructor_pkey

???????????????????????? List of relations

-------------+-----------------------------+-------+---------+------------+---------

(1 row)

studentdb=>

1.3.13 \dx命令

元命令\dx的作用是查看已安裝的擴(kuò)展程序信息。

\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的語(yǔ)法： \x [ on | off | auto ]

元命令\dx的作用是設(shè)置語(yǔ)句的輸出模式。默認(rèn)情況下記錄上按行的方式來(lái)顯示的。如果執(zhí)行元命令\x on，則顯示將按每條記錄每列的方式來(lái)顯示。這種方式在有些情況下很有用。

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;????? 默認(rèn)情況下，查詢(xún)語(yǔ)句的顯示方式為行方式

?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的語(yǔ)法： \timing [ on | off ]

元命令\timing的作用是，如果設(shè)置為on，將顯示SQL語(yǔ)句的執(zhí)行時(shí)間。

select * from instructor where salary=80000;

\timing on

select * from instructor where salary=80000;

\timing off

studentdb=> select * from instructor where salary=80000;

? 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 salary=80000;

? 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語(yǔ)句的幫助。例如我們想獲取update語(yǔ)句的幫助信息：

\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語(yǔ)句的幫助信息：

\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 function's 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中執(zhí)行操作系統(tǒng)命令。例如：

\! 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 salary=80000;

\o myoutputfile

select * from instructor where salary=80000;

\! cat myoutputfile

studentdb=> select * from instructor where salary=80000;

? 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 salary=80000;

studentdb=> \! cat myoutputfile

? id?? | dept_name? | name? |? salary?

-------+------------+-------+----------

?76543 | Finance??? | Singh | 80000.00

?98345 | Elec. Eng. | Kim?? | 80000.00

(2 rows)

studentdb=>

從上面的測(cè)試可以看出，\o myoutputfile將輸出重定向到文件myoutputfile中，而不是把查詢(xún)結(jié)果顯示在終端上。

1.3.20 \i? file.sql命令

元命令\i? file.sql的作用是在交互式gsql中，執(zhí)行文件file.sql中的SQL語(yǔ)句。下面是一個(gè)示例。

用Linux用戶(hù)omm，打開(kāi)一個(gè)終端，執(zhí)行下面的命令，生成一個(gè)測(cè)試用的SQL語(yǔ)句腳本：

cat > test.sql<<EOF

select * from instructor where salary=80000;

EOF

[omm@test ~]$ cat > test.sql<<EOF

> select * from instructor where salary=80000;

> EOF

[omm@test ~]$

登錄到openGauss：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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=>

執(zhí)行下面的命令，運(yùn)行sql語(yǔ)句腳本文件：

\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中，顯示會(huì)話(huà)的連接信息。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

\conninfo

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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中，切換連接的數(shù)據(jù)庫(kù)。

使用Linux用戶(hù)omm，執(zhí)行下面的命令和語(yǔ)句，進(jìn)行測(cè)試：

gsql -d postgres -p 26000 -r

CREATE TABLESPACE test_ts RELATIVE LOCATION 'tablespace/test_ts1';

CREATE DATABASE testdb? WITH TABLESPACE = test_ts;

-- 切換連接到數(shù)據(jù)庫(kù)testdb

\c testdb

-- 切換連接到數(shù)據(jù)庫(kù)studentdb

\connect studentdb

DROP DATABASE testdb;

DROP TABLESPACE test_ts;

[omm@test ~]$ 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=# -- 切換連接到數(shù)據(jù)庫(kù)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=# -- 切換連接到數(shù)據(jù)庫(kù)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

[omm@test ~]$

1.3.23 \echo [string]命令

元命令\echo [string]的作用是在標(biāo)準(zhǔn)輸出上顯式信息。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

\echo Hello,openGauss from Huawei!

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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命令和快捷鍵ctrl+d

元命令\q的作用是退出gsql。也可以使用快捷鍵ctrl+d來(lái)退出gsql。

studentdb=> \q

[omm@test ~]$

1.4 gsql初始化文件.gsqlrc

環(huán)境變量PSQLRC用來(lái)設(shè)置.gsqlrc文件的目錄位置。在Linux的終端上，使用用戶(hù)omm執(zhí)行下面的命令來(lái)設(shè)置PSQLRC環(huán)境變量：

export PLSQLRC=PathYourWantItToBe

如果沒(méi)有設(shè)置PSQLRC環(huán)境變量，那么將默認(rèn)讀取Linux用戶(hù)主目錄下的.gsqlrc文件。

下面進(jìn)行測(cè)試。首先不設(shè)置.gsqlrc文件，在gsql中運(yùn)行下面的SQL語(yǔ)句：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

select * from instructor where salary=80000;

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -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 salary=80000;

? id?? | dept_name? | name? |? salary?

-------+------------+-------+----------

?76543 | Finance??? | Singh | 80000.00

?98345 | Elec. Eng. | Kim?? | 80000.00

(2 rows)

?????????????????????????????? 默認(rèn)情況下不顯示查詢(xún)語(yǔ)句的執(zhí)行時(shí)間

studentdb=> \q

[omm@test ~]$

我們看到執(zhí)行完SQL語(yǔ)句，不會(huì)顯式該SQL語(yǔ)句的執(zhí)行時(shí)間。

接下來(lái)在linux用戶(hù)的主目錄下創(chuàng)建如下的.gsqlrc文件：

cat >~/.gsqlrc<<EOF

\timing on

EOF

[omm@test ~]$ cat >~/.gsqlrc<<EOF

> \timing on???????????? 設(shè)置gsql在執(zhí)行完一條查詢(xún)語(yǔ)句后，顯示查詢(xún)語(yǔ)句的執(zhí)行時(shí)間

> EOF

[omm@test ~]$

重新登錄到openGauss數(shù)據(jù)庫(kù)，并執(zhí)行同樣的SQL語(yǔ)句：

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

select * from instructor where salary=80000;

rm ~/.gsqlrc

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

Timing is on.????? 已經(jīng)通過(guò).gsqlrc文件設(shè)置了Timing變量為on，作用是顯示查詢(xún)語(yǔ)句的執(zhí)行時(shí)機(jī)

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 salary=80000;

? id?? | dept_name? | name? |? salary?

-------+------------+-------+----------

?76543 | Finance??? | Singh | 80000.00

?98345 | Elec. Eng. | Kim?? | 80000.00

(2 rows)

Time: 0.687 ms????????????? 執(zhí)行完一條查詢(xún)語(yǔ)句后，顯示查詢(xún)語(yǔ)句的執(zhí)行時(shí)間

studentdb=> \q

[omm@test ~]$ rm ~/.gsqlrc

[omm@test ~]$

以上的實(shí)驗(yàn)表明，gsql命令啟動(dòng)時(shí)，默認(rèn)情況下，會(huì)自動(dòng)運(yùn)行主目錄下的初始化文件.gsqlrc中的設(shè)置命令。

2.客戶(hù)端安全

2.1配置客戶(hù)端接入認(rèn)證

參考：官方文檔：07 開(kāi)發(fā)者指南-5.1 客戶(hù)端接入認(rèn)證-5.1.1 配置客戶(hù)端接入認(rèn)證

實(shí)驗(yàn)：

以操作系統(tǒng)用戶(hù)omm登錄數(shù)據(jù)庫(kù)主節(jié)點(diǎn)。
配置客戶(hù)端認(rèn)證方式，允許客戶(hù)端以“jack”用戶(hù)連接到本機(jī)，此處遠(yuǎn)程連接禁止使用“omm”用戶(hù)（即數(shù)據(jù)庫(kù)初始化用戶(hù)）。

例如，下面示例中配置允許IP地址為192.168.0.1的客戶(hù)端訪問(wèn)本機(jī)。

gs_guc set -N all -I all -h "host all jack 192.168.0.1/32 sha256"

postgres=# \l

????????????????????????? List of databases

-----------+-------+-----------+---------+-------+-------------------

?mydb????? | omm?? | GBK?????? | C ??????| C???? |

?postgres? | omm?? | SQL_ASCII | C?????? | C???? |

?template0 | omm?? | SQL_ASCII | C?????? | C???? | =c/omm?????????? +

?????????? |?????? |?????????? |???????? |?????? | omm=CTc/omm

?template1 | omm?? | SQL_ASCII | C?????? | C???? | =c/omm?????????? +

?????????? |?????? |?????????? |???????? |?????? | omm=CTc/omm

?testdb1?? | omm?? | SQL_ASCII | C?????? | C???? |

?testdb2?? | omm?? | SQL_ASCII | C?????? | C???? |

?testdb3?? | omm?? | SQL_ASCII | C?????? | C???? |

(8 rows)

[omm@node1 ~]$ 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!

[omm@node1 ~]$ pg_om -t status

bash: pg_om: command not found...

[omm@node1 ~]$ gs_om -t status

-----------------------------------------------------------------------

cluster_name??? : dbCluster

cluster_state?? : Normal

redistributing? : No

-----------------------------------------------------------------------

1）、如果為了支持MD5和sha256，需要修改password_encryption_type參數(shù)：

?gs_guc reload -N all -I all -c "password_encryption_type=1"

默認(rèn)是password_encryption_type=2

、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客戶(hù)端驗(yàn)證

? 華為openGauss高斯數(shù)據(jù)庫(kù) - gsql用法,收藏學(xué)習(xí),數(shù)據(jù)庫(kù),oracle,服務(wù)器

說(shuō)明：

可以正常登錄。

● 使用“jack”用戶(hù)前，需先本地連接數(shù)據(jù)庫(kù)，并在數(shù)據(jù)庫(kù)中使用如下語(yǔ)句建立“jack”用

戶(hù)：

postgres=# CREATE USER jack PASSWORD 'Test@123';

● -N all表示openGauss的所有主機(jī)。

● -I all表示主機(jī)的所有實(shí)例。

● -h表示指定需要在“pg_hba.conf”增加的語(yǔ)句。

● all表示允許客戶(hù)端連接到任意的數(shù)據(jù)庫(kù)。

● jack表示連接數(shù)據(jù)庫(kù)的用戶(hù)。

● 192.168.0.1/32表示只允許IP地址為192.168.0.1的主機(jī)連接。此處的IP地址不能為openGauss內(nèi) 的IP，在使用過(guò)程中，請(qǐng)根據(jù)用戶(hù)的網(wǎng)絡(luò)進(jìn)行配置修改。32表示子網(wǎng)掩碼為1的位數(shù)，即

255.255.255.255

● sha256表示連接時(shí)jack用戶(hù)的密碼使用sha256算法加密。

這條命令在數(shù)據(jù)庫(kù)主節(jié)點(diǎn)實(shí)例對(duì)應(yīng)的“pg_hba.conf”文件中添加了一條規(guī)則，用于對(duì)

連接數(shù)據(jù)庫(kù)主節(jié)點(diǎn)的客戶(hù)端進(jìn)行鑒定。

“pg_hba.conf”文件中的每條記錄可以是下面四種格式之一，四種格式的參數(shù)說(shuō)明請(qǐng)

參見(jiàn)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]

因?yàn)檎J(rèn)證時(shí)系統(tǒng)是為每個(gè)連接請(qǐng)求順序檢查“pg_hba.conf”里的記錄的，所以這些記

錄的順序是非常關(guān)鍵的。

說(shuō)明

在配置“pg_hba.conf”文件時(shí)，請(qǐng)依據(jù)通訊需求按照格式內(nèi)容從上至下配置記錄，優(yōu)先級(jí)高的

需求需要配置在前面。openGauss和擴(kuò)容配置的IP優(yōu)先級(jí)最高，用戶(hù)手動(dòng)配置的IP請(qǐng)放在這二者

之后，如果已經(jīng)進(jìn)行的客戶(hù)配置和擴(kuò)容節(jié)點(diǎn)的IP在同一網(wǎng)段，請(qǐng)?jiān)跀U(kuò)容前刪除，擴(kuò)容成功后再進(jìn)

行配置。

因此對(duì)于認(rèn)證規(guī)則的配置建議如下：

● 靠前的記錄有比較嚴(yán)格的連接參數(shù)和比較弱的認(rèn)證方法。

● 靠后的記錄有比較寬松的連接參數(shù)和比較強(qiáng)的認(rèn)證方法。

說(shuō)明

● 一個(gè)用戶(hù)要想成功連接到特定的數(shù)據(jù)庫(kù)，不僅需要通過(guò)pg_hba.conf中的規(guī)則檢查，還必須

要有該數(shù)據(jù)庫(kù)上的CONNECT權(quán)限。如果希望控制某些用戶(hù)只能連接到指定數(shù)據(jù)庫(kù)，賦予/撤銷(xiāo)CONNECT權(quán)限通常比在pg_hba.conf中設(shè)置規(guī)則更為簡(jiǎn)單。

● 對(duì)應(yīng)openGauss外部客戶(hù)端連接，trust為不安全的認(rèn)證方式，請(qǐng)將認(rèn)證方式設(shè)置為sha256。

【其他】

如果想要在服務(wù)器上，

[omm@node1 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

華為openGauss高斯數(shù)據(jù)庫(kù) - gsql用法,收藏學(xué)習(xí),數(shù)據(jù)庫(kù),oracle,服務(wù)器文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-655796.html

到了這里，關(guān)于華為openGauss高斯數(shù)據(jù)庫(kù) - gsql用法的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！