data

CREATE DATABASE db_exp
WITH OWNER = dbadmin
ENCODING = 'UTF8'
TEMPLATE = template0
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1
COMPATIBLE = 'MYSQL';

\c db_exp;

CREATE USER user_exp WITH PASSWORD 'GaussDB@2023';

GRANT sysadmin TO user_exp;

CREATE SCHEMA sche_exp AUTHORIZATION user_exp;

SET search_path TO sche_exp;

CREATE TABLE tab_row (
    id INT,
    stu_id INT,
    grade VARCHAR(20)
)
DISTRIBUTE BY HASH(id);

INSERT INTO tab_row (id, stu_id, grade) VALUES
(1, 1, 'first'),
(2, 2, 'second'),
(3, 3, 'third'),
(4, 4, 'fourth');

CREATE TABLE tab_column (
    id INT,
    name VARCHAR(30),
    score INT
)
DISTRIBUTE BY HASH(id)
COMPRESS LEVEL MIDDLE;

INSERT INTO tab_column (id, name, score) VALUES
(1, 'zhangsan', 80),
(2, 'lisi', 85),
(3, 'wangwu', 90);

CREATE INDEX idx_column ON tab_column (name, score);

CREATE VIEW view_tab AS
SELECT name, score
FROM tab_column
WHERE score > 80;