openGauss中想测试下SQL 这块的功能，没找到对应的示例库，所以用如下SQL 模拟创建几张测试表。http://www.cndba.cn/dave/article/121025

1 创建测试库

[dave@www.cndba.cn software]$ gsql  -p 15500 -d postgres -U omm -W omm@123456 -r 
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# /l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 cndba     | omm   | UTF8      | C       | C     | 
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(4 rows)

openGauss=# create database hr;
CREATE DATABASE
openGauss=# /l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 cndba     | omm   | UTF8      | C       | C     | 
 hr        | omm   | SQL_ASCII | C       | C     | 
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(5 rows)

2 创建5张测试表

openGauss=# /c hr
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "hr" as user "omm".
hr=# 

hr=# CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    gender VARCHAR(10),
    age INT,
    salary FLOAT
);

hr=# CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    location VARCHAR(255)
);

hr=# CREATE TABLE project (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    start_date DATE,
    end_date DATE
);

hr=# CREATE TABLE job (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    company VARCHAR(255),
    location VARCHAR(255),
    salary FLOAT,
    requirements TEXT
);

hr=# CREATE TABLE employee_department (
    eid INT,
    ed INT,
    de INT,
    salary FLOAT
);

INSERT INTO employee VALUES (1, 'Alice', 'F', 25, 5000.0);
INSERT INTO employee VALUES (2, 'Bob', 'M', 30, 6000.0);
INSERT INTO employee VALUES (3, 'Charlie', 'M', 35, 7000.0);
INSERT INTO employee VALUES (4, 'David', 'M', 40, 8000.0);
INSERT INTO department VALUES (1, 'IT', 'Beijing');
INSERT INTO department VALUES (2, 'HR', 'Shanghai');
INSERT INTO department VALUES (3, 'Sales', 'Guangzhou');
INSERT INTO department VALUES (4, 'Marketing', 'Shenzhen');
INSERT INTO project VALUES (1, 'Project A', '2022-01-01', '2022-02-28');
INSERT INTO project VALUES (2, 'Project B', '2022-03-01', '2022-05-31');
INSERT INTO job VALUES (1, 'Software Engineer', 'Google', 'San Francisco', 100000.0, 'Python, Java');
INSERT INTO job VALUES (2, 'Marketing Manager', 'Apple', 'New York', 120000.0, 'SEO, PPC');
INSERT INTO job VALUES (3, 'Product Manager', 'Amazon', 'Seattle', 150000.0, 'UX Design');
INSERT INTO job VALUES (4, 'Data Analyst', 'Facebook', 'London', 80000.0, 'SQL, Tableau');
INSERT INTO employee_department VALUES (1, 1, 1, 5000.0);
INSERT INTO employee_department VALUES (2, 2, 2, 6000.0);
INSERT INTO employee_department VALUES (3, 3, 3, 7000.0);
INSERT INTO employee_department VALUES (4, 4, 4, 8000.0);

hr=# SELECT * FROM employee;
 id |  name   | gender | age | salary 
----+---------+--------+-----+--------
  1 | Alice   | F      |  25 |   5000
  2 | Bob     | M      |  30 |   6000
  3 | Charlie | M      |  35 |   7000
  4 | David   | M      |  40 |   8000
(4 rows)

hr=# SELECT * FROM department;
 id |   name    | location  
----+-----------+-----------
  1 | IT        | Beijing
  2 | HR        | Shanghai
  3 | Sales     | Guangzhou
  4 | Marketing | Shenzhen
(4 rows)

hr=# SELECT * FROM project;
 id |   name    |     start_date      |      end_date       
----+-----------+---------------------+---------------------
  1 | Project A | 2022-01-01 00:00:00 | 2022-02-28 00:00:00
  2 | Project B | 2022-03-01 00:00:00 | 2022-05-31 00:00:00
(2 rows)

hr=# SELECT * FROM job;
 id |       title       | company  |   location    | salary | requirements 
----+-------------------+----------+---------------+--------+--------------
  1 | Software Engineer | Google   | San Francisco | 100000 | Python, Java
  2 | Marketing Manager | Apple    | New York      | 120000 | SEO, PPC
  3 | Product Manager   | Amazon   | Seattle       | 150000 | UX Design
  4 | Data Analyst      | Facebook | London        |  80000 | SQL, Tableau
(4 rows)

hr=#

版权声明：本文为博主原创文章，未经博主允许不得转载。