openGauss 常用SQL语言

常用SQL语言：
1）创建数据库用户

create user 用户名 with password "密码";
create user jack password "******";
在每次创建新用户时，系统会在当前登录的数据库中为新用户创建一个同名Schema。对于其他数据库，若需要同名Schema，则需要用户手动创建。
 
openGauss=# create user jack password "******";
CREATE ROLE
openGauss=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
jack | | {}
kw | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
song | Create role, Sysadmin | {}

 
2）删除数据库用户

drop user 用户名 cascade;
drop user jack cascade;
1
2
3）创建数据库，并指定所有者owner

create database 数据库名 owner 用户名;
create database jack_test owner jack;
1
2
4）为用户授权

给用户授权对某数据库的所有权限
grant all privileges on database jack_test to jack;
为用户追加有创建角色的CREATEROLE权限
alter user jack createrole;
将sysadmin权限授权给用户joe
grant all privileges to jack;
 
openGauss=# create database jack_test owner jack;
CREATE DATABASE
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
db1 | song | SQL_ASCII | C | C |
db2 | song | SQL_ASCII | C | C |
jack_test | jack | SQL_ASCII | C | C |
kwdb | kw | SQL_ASCII | C | C |
mydb | song | GBK | C | C |
postgres | omm | SQL_ASCII | C | C |
song_suse | song | SQL_ASCII | C | C | =Tc/song +
| | | | | song=CTc/song +
| | | | | song=APm/song
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
(9 rows)

openGauss=# grant all privileges on database jack_test to jack;
GRANT
openGauss=# alter user jack createrole;
ALTER ROLE
openGauss=# grant all privileges to jack;
ALTER ROLE
openGauss=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
jack | Create role, Sysadmin | {}
kw | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
song | Create role, Sysadmin | {}

openGauss=#

 
5）删除数据库

drop database 数据库名；
drop database jack_test;
1
2
6）创建schema(模式，默认为public)

1. 切换到jack_test数据库：
openGauss=# \c jack_test
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "jack_test" as user "omm".
2. 切换到jack用户
jack_test=# \c - jack
Password for user jack:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "jack_test" as user "jack".
3. 查看schema
jack_test=> \dn
List of schemas
Name | Owner
----------------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_application_info | omm
dbe_file | omm
dbe_lob | omm
dbe_match | omm
dbe_output | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_random | omm
dbe_raw | omm
dbe_scheduler | omm
dbe_session | omm
dbe_sql | omm
dbe_task | omm
dbe_utility | omm
pkg_service | omm
pkg_util | omm
public | omm
snapshot | omm
sqladvisor | omm
sys | omm
(24 rows)

jack_test=>
4. 查看当前用户：
select * from current_user;
select user;
jack_test=> select * from current_user;
current_user
--------------
jack
(1 row)

jack_test=> select user;
current_user
--------------
jack
(1 row)

jack_test=>


5. 为用户jack创建一个模式jack1
create schema 模式名 authorization 用户名;
create schema jack1 authorization jack;
jack_test=> create schema jack1 authorization jack;
CREATE SCHEMA
jack_test=> \dn
List of schemas
Name | Owner
----------------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_application_info | omm
dbe_file | omm
dbe_lob | omm
dbe_match | omm
dbe_output | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_random | omm
dbe_raw | omm
dbe_scheduler | omm
dbe_session | omm
dbe_sql | omm
dbe_task | omm
dbe_utility | omm
jack1 | jack
pkg_service | omm
pkg_util | omm
public | omm
snapshot | omm
sqladvisor | omm
sys | omm
(25 rows)

jack_test=>
6. 修改schema名称
alter schema jack1 rename to jack2;
7. 要更改当前会话的默认Schema，请使用SET命令。
执行如下命令将搜索路径设置为myschema、public，首先搜索myschema。
set search_path to jack2;
查看当前schema
show search_path;
jack_test=> set search_path to jack2;
SET
jack_test=> show search_path;
search_path
-------------
jack2
(1 row)

jack_test=>


 
7）授权

grant usage on schema jack2 to jack;
回收(revoke)

将Schema中的表或者视图对象授权给其他用户或角色时，需要将表或视图所属Schema的USAGE权限同时授予该用户或角色。否则用户或角色将只能看到这些对象的名称，并不能实际进行对象访问。
 
8）数据库重新命名

alter database jack_test rename to jack_db;
1
9)命令查看当前数据库存储编码

show server_encoding;
1
10）查询表分布列（分布式openGauss才有，集中式没有）：

select getdistributekey ('schemaName.tableName');
select getdistributekey ('tableName');
1
2
11）修改omm管理员密码
openGauss数据库登陆如果提示omm密码过期，需要修改omm管理员密码，例如：

alter role omm identified by '新密码' replace '旧密码';