openGauss数据库常用命令

  • 启动并连接数据库

    1
    2
    3
    gs_om -t start 
    gsql -d postgres -p 26000 -r
    gsql -d db_name -p 26000 -U USER -W PASSWORD  -r

  • 用户管理

    1
    2
    CREATE USER joe WITH PASSWORD "Bigdata@123";
    DROP USER kim CASCADE;

  • schema管理

    1
    2
    3
    4
    CREATE SCHEMA myschema;
    CREATE SCHEMA myschema AUTHORIZATION omm;
    CREATE TABLE myschema.mytable(id int, name varchar(20)); 
    SET SEARCH_PATH TO myschema,public;

  • 表空间管理

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLESPACE fastspace RELATIVE LOCATION 'tablespace/tablespace_1';
    GRANT CREATE ON TABLESPACE fastspace TO jack;

    SELECT spcname FROM pg_tablespace;

    DROP USER jack CASCADE;
    DROP TABLESPACE fspace;

  • 管理数据库

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLESPACE tpcds_local RELATIVE LOCATION 'tablespace/tablespace_2';
    CREATE DATABASE db_tpcc WITH TABLESPACE = tpcds_local;
    CREATE DATABASE db_tpcc OWNER joe;

    \l
    SELECT datname FROM pg_database;

    DROP DATABASE human_tpcds;

  • 管理表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    CREATE TABLE customer_t1 
    ( 
        c_customer_sk             integer, 
        c_customer_id             char(5), 
        c_first_name              char(6), 
        c_last_name               char(8) 
    );

    INSERT INTO customer_t1(c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', 'Grace');
    INSERT INTO customer_t1 VALUES (3769, 'hello', 'Grace');

    INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES  
    (6885, 'maps', 'Joes'), 
    (4321, 'tpcds', 'Lily'), 
    (9527, 'world', 'James');

    INSERT INTO customer_t2 SELECT * FROM customer_t1;

    UPDATE customer_t1 SET c_customer_sk = 9876 WHERE c_customer_sk = 9527;
    UPDATE customer_t1 SET c_customer_sk = c_customer_sk + 100;
    UPDATE customer_t1 SET  c_customer_id = 'Admin', c_first_name = 'Local' WHERE c_customer_sk = 4421;

    \d+

  • 索引

    1
    2
    CREATE UNIQUE INDEX xybno ON xyb(ydh);
    \di