作者:Lily_tj

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

目录

使用 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;
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐