作者：Lily_tj

通过学习openGauss 21天，对于使用SQL，进行了常用命令总结，方便以后查询使用。

目录

• 使用 gsql 本地连接数据库
• 数据库
• 用户和角色
• 模式SCHEMA
• 表空间
• 创建表和分区表
• gs_dump导出数据
• 事务
• 系统视图

使用 gsql 本地连接数据库

gsql 是 openGauss 提供的在命令行下运行的数据库连接工具。平台连接数据库语句：

 
 

  
  gsql -d postgres -U gaussdb -W'passwd';

 
 

数据库

openGauss 常用数据库操作语句：

• 切换数据库：

 
 

  
  \c test

 
 

上述语句代表从当前数据库切换到test数据库。

• 查看数据库列表：

 
 

  
  \l

 
 

• 创建数据库例子：

 
 

  
  -- 创建一个GBK编码的数据库music（本地环境的编码格式必须也为GBK）。
CREATE DATABASE music ENCODING 'GBK' template = template0;

 
 

– 创建数据库music2，并指定所有者为jim。
CREATE DATABASE music2 OWNER jim;

– 用模板template0创建数据库music3，并指定所有者为jim。
CREATE DATABASE music3 OWNER jim TEMPLATE template0;

– 重命名数据库名字

alter database music rename to music4;

用户和角色

• CREATE USER

通过CREATE USER创建的用户，默认具有LOGIN权限。
通过CREATE USER创建用户的同时，系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA。
系统管理员在普通用户同名schema下创建的对象，所有者为schema的同名用户（非系统管理员）。

• CREATE ROLE

角色是拥有数据库对象和权限的实体。在不同的环境中角色可以认为是一个用户，一个组或者兼顾两者。
在数据库中添加一个新角色，角色无登录权限。
创建角色的用户必须具备CREATE ROLE的权限或者是系统管理员。

• 查看角色或用户信息：

 
 

  
  \du

 
 

• 创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性

 
 

  
  -- 密码必须8个字符以上，包括3种字符
CREATE ROLE role1 LOGIN SYSADMIN  IDENTIFIED BY 'test_123456';
CREATE ROLE role2  IDENTIFIED BY 'test_123456' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
CREATE ROLE role3 with LOGIN IDENTIFIED BY 'test_123456';
\du

 
 

• 重命名role1

 
 

  
  alter role role1 rename to role10;
\du

 
 

• 修改role2密码

 
 

  
  alter role role2 IDENTIFIED BY 'test_654321';

 
 

• 将omm权限授权给role3,再回收role3的权限

 
 

  
  \du
grant omm to role3;
\du+

 
 

– 要求"过程中使用\du或\du+查看用户信息"，这是很好的习惯，推荐！

模式SCHEMA

• 创建一个名为tpcds的模式

 
 

  
  CREATE schema tpcds;

 
 

\dn+ tpcds

• 删除模式
  当schema非空时，如果要删除一个schema及其包含的所有对象，需要使用CASCADE关键字

 
 

  
  omm=# DROP SCHEMA ds_new;
ERROR:  cannot drop schema ds_new because other objects depend on it
DETAIL:  table ds_new.t1 depends on schema ds_new
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

 
 

omm=# DROP SCHEMA ds_new CASCADE;
NOTICE: drop cascades to table ds_new.t1
DROP SCHEMA

表空间

• 创建表空间

 
 

  
  create tablespace tspc1 relative location 'tablespace/ts1';
create tablespace tspc2 owner Lucy relative location 'tablespace/ts2';

 
 

• 查看表空间

 
 

  
  \db

 
 

create table customer(customer_id integer,customer_name char(30)) tablespace tspc1;
select * from pg_tables where tablename=‘customer’;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time
------------±----------±-----------±-----------±-----------±---------±------------±-------------±------------------------------±------------------------------
public | customer | omm | tspc1 | f | f | f | omm | 2021-12-13 22:29:07.778034+08 | 2021-12-13 22:29:07.778034+08
(1 row)

创建表和分区表

 
 

  
  CREATE TABLE products 
( product_id             integer,   
  product_name           char(30),    
  category               char(20) 
);

 
 

• 查看表列表及表信息：

 
 

  
  \d
\d products

 
 

• insert可插入一条，也可插入多条记录

 
 

  
  insert into products values(1502, 'olympus camera', 'electrncs');
insert into products values(1601, 'lamaze', 'toys'),( 1700, 'wait interface', 'Books'), (1666, 'harry potter', 'toys');

 
 

• 获取表中一条记录、三条记录和所有记录

 
 

  
  select * from products limit 1;
select * from products limit 3; 
select * from products;

 
 

– 创建分区表
Create schema tpcds;
create table tpcds.t1
( id int,
name CHAR(6)
)
partition by range (id)
(
partition update_table_p0 values less than (50),
partition update_table_p1 values less than (500),
partition update_table_p2 values less than (1000)
);
– 使用generate_series(1,N)函数对表插入数据
insert into tpcds.t1 values(generate_series(10, 200));
– 查看数据
select * from tpcds.t1;

• 查看一个分区上的数据

select * from tpcds.t1 partition(update_table_p0);

• 在系统表pg_partition中查看分区信息

\d+ tpcds.t1;
select * from pg_partition;

gs_dump导出数据

• 导出backup数据库

 
 

  
  全量信息
数据，不包含数据库对象定义
对象的定义
gs_dump -f /home/omm/backup_database_all.sql backup -F p
gs_dump -f /home/omm/backup_database_data.sql backup -a -F p 
gs_dump -f /home/omm/backup_database_define.sql backup -s -F p

 
 

• backup数据库中ds模式

 
 

  
  全量信息
数据
定义
gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p
gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p

 
 

• backup数据库中表customer_t

1. 全量信息
2. 数据
3. 定义

gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p
gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p
gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p

事务

• 通过START TRANSACTION和BEGIN语法启动事务
• 隔离级别为READ COMMITTED，访问模式为READ ONLY
• 隔离级别为repeatable read，读/写方式

 
 

  
  START TRANSACTION;
END;

 
 

BEGIN;
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
commit;

START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
rollback;

– 查看隔离级别
show transaction_isolation;

• 保存点的建立、回滚与删除

 
 

  
  START TRANSACTION;
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
RELEASE SAVEPOINT my_savepoint;
COMMIT;

 
 

系统视图

• 查询视图

 
 

  
  select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';

 
 

• 查询索引

 
 

  
  select * from pg_indexes where tablename = 'products';

 
 

• 查看存储过程或函数

 
 

  
  \sf func_add_sql1
select * from pg_proc where proname = 'func_add_sql1';

 
 

• 在系统视图pg_cursors中查看可用游标

 
 

  
  select * from pg_cursors;

 
 

• 查询分区

 
 

  
  \d+ store;
select * from pg_partition;