openGauss数据库管理实战指南——基本常用操作总结

查看所有数据库

查看所有表 \d

查看函数定义

查看所有用户 select usename from pg_user;

1.数据库创建管理

CREATE DATABASE test;

2.数据库用户创建管理

CREATE USER tom PASSWORD 'Root123456.';

3.表的创建及管理

3.1.创建表

CREATE TABLE test(ID INTEGER PRIMARY KEY, NAME TEXT);

3.2.数据的增删改查

--增
 insert into test values (1,'xiaoming'),(2,'xiaohong')，,(3,'xiaoqiang');
--删
 DELETE FROM test WHERE ID<2;
 --改
 UPDATE test SET ID = ID*2 WHERE ID=2;
 --查
 SELECT * FROM test LIMIT 1;

3.3.表的删除

DROP TABLE test;

--创建基表，并插入数据。
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl values (1, 'insertTable');
--创建视图
REATE VIEW ro_view1 AS SELECT a, b FROM base_tbl;
--视图插入、更新和删除数据
INSERT INTO ro_view1 values (2, 'insertView');
UPDATE ro_view1 SET b = 'updateView' WHERE a = 1;
DELETE FROM ro_view1 WHERE a= 2;

--创建check option视图
CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl WHERE a > 10 WITH CHECK OPTION;
--插入、更新视图不可见数据失败
INSERT INTO ro_view2 values (5, 'insertView');
INSERT INTO base_tbl values (15, 'insertTable');
UPDATE ro_view2 SET a = 5 WHERE a = 15;

4.SQL语法实验

4.1.DDL

定义数据库:CREATE DATABASE、ALTER DATABASE、DROP DATABASE

定义模式：CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA

定义表空间：CREATE TABLESPACE、ALTER TABLESPACE、DROP TABLESPACE

定义表：CREATE TABLE、ALTER TABLE、DROP TABLE

定义分区表：CREATE TABLE PARTITION、ALTER TABLE PARTITION

定义索引：CREATE INDEX、DROP INDEX

定义存储过程：CREATE PROCEDURE、ALTER PROCEDURE、DROP PROCEDURE

定义函数:CREATE FUNCTION、ALTER FUNCTION、DROPFUNCTION

定义视图：CREATE VIEW、ALTER VIEW、DROP VIEW

4.2.DML

INSERT。

UPDATE。

SELECT。

DELETE\TRUNCATE。

COPY。

CREATE TABLE test(ID INTEGER PRIMARY KEY, NAME TEXT);
insert into test values (1,'xiaoming'),(2,'xiaohong')，,(3,'xiaoqiang');
COPY test TO '/home/omm/ds_ship_mode.dat'

LOCK。

CALL。

4.3.DCL

定义角色CREATE/ALTER/DROP ROLE

定义用户CREATE/ALTER/DROP USER

授权GRANT

收回权限REVOKE

设置默认权限ALTER DEFAULT PRIVILEGES

关闭当前节点SHUTDOWN

5.创建和管理其他数据库对象

5.1.schema

CREATE SCHEMA ds;
ALTER SCHEMA ds RENAME TO ds_new;
ALTER SCHEMA ds_new OWNER TO tom;
DROP SCHEMA ds_new;

5.2.索引

create schema tpcds;
CREATE TABLE tpcds.ship_mode_t1
(
    SM_SHIP_MODE_SK           INTEGER               NOT NULL,
    SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,
    SM_TYPE                   CHAR(30)                      ,
    SM_CODE                   CHAR(10)                      ,
    SM_CARRIER                CHAR(20)                      ,
    SM_CONTRACT               CHAR(20)
) 
;
--在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建普通的唯一索引。
CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
--在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引。
CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
--在表tpcds.ship_mode_t1上SM_CODE字段上创建表达式索引。
CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
--设置索引不可用。
ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
--删除一个现有的索引。
DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP TABLE tpcds.ship_mode_t1;
DROP SCHEMA tpcds;

5.3.视图

--创建基表，并插入数据。
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl values (1, 'insertTable');
--创建视图
REATE VIEW ro_view1 AS SELECT a, b FROM base_tbl;
--视图插入、更新和删除数据
INSERT INTO ro_view1 values (2, 'insertView');
UPDATE ro_view1 SET b = 'updateView' WHERE a = 1;
DELETE FROM ro_view1 WHERE a= 2;

--创建check option视图
CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl WHERE a > 10 WITH CHECK OPTION;
--插入、更新视图不可见数据失败
INSERT INTO ro_view2 values (5, 'insertView');
INSERT INTO base_tbl values (15, 'insertTable');
UPDATE ro_view2 SET a = 5 WHERE a = 15;

5.4.存储过程

CREATE PROCEDURE proc_insert_sql()
AS BEGIN 
 insert into test values (1,'xiaoming'),(2,'xiaohong'),(3,'xiaoqiang');
END
/
CALL proc_insert_sql();
SELECT * FROM test;
DROP PROCEDURE proc_insert_sql;

5.5.函数

CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
AS BEGIN 
RETURN num1 + num2;
END
/
SELECT func_add_sql(4,2);
DROP FUNCTION func_add_sql;

6.用户权限控制

6.1.角色

CREATE ROLE paul IDENTIFIED BY 'Root123456.';
ALTER ROLE paul IDENTIFIED BY 'Root123456&' REPLACE 'Root123456.';

6.2.权限

CREATE USER jack PASSWORD 'Root123456.';
create schema tpcds;
ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT INSERT ON TABLES TO jack;

6.3.授权

GRANT ALL PRIVILEGES TO jack;
GRANT jack TO paul WITH ADMIN OPTION;

6.4.权限回收

REVOKE paul FROM jack;
REVOKE ALL PRIVILEGES FROM jack;
DROP ROLE paul;
DROP OWNED BY jack;
DROP USER jack;