For image download, domain name resolution and time synchronization, please click Alibaba cloud open source mirror

This section describes the basic operations of using a database. This section enables you to create a database, create a table, insert data into the table, and query the data in the table.

2.1 preconditions

● openGauss operates normally.

● since this experiment is the basic use of openGauss database, it is necessary to master the basic operation and SQL syntax of openGauss database. openGauss database supports SQL2003 standard syntax. See Appendix II for the basic operation of database.

2.2 operation steps

Step 1 log in to the database master node as the operating system user omm.

[root@ecs-c9bf script]# su - omm       

If you are not sure which server the database master node is deployed on, confirm the connection information.

Step 2 start the service.

Start service command:

[omm@ecs-c9bf ~]$ gs_om -t start
Starting cluster.
=========================================
=========================================
Successfully started.

Step 3 connect to the database.

[omm@ecs-c9bf ~]$ gsql -d postgres -p 26000 -r    

When the result is displayed as the following information, it indicates that the connection is successful.

gsql ((openGauss 1.1.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131 
Non-SSL connection (SSL connection is recommended when requiring high-security) 
Type "help" for help. 
 
postgres=# 

postgres is the database generated by default after openGauss installation. You can initially connect to this database to create a new database. 26000 is the port number of the main node of the database. It needs to be replaced according to the actual situation of openGauss. Please confirm to obtain the connection information.

Extended information:

● before using the database, you need to use the client program or tool to connect to the database, and then you can use the database by executing SQL through the client program or tool.

● gsql is a command line database connection tool provided by openGauss database.

Step 4 when connecting to the database for the first time, you need to modify the omm user password, and the new password is Bigdata@123 (user defined password is recommended).

postgres=# alter role omm identified by 'Bigdata@123' replace 'openGauss@123';
ALTER ROLE

Step 5 create a database user.

By default, only the administrator user created during openGauss installation can access the initial database. You can also create other database user accounts.

postgres=# CREATE USER joe WITH PASSWORD "Bigdata@123"; 

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE ROLE 

The user name is joe and the password is Bigdata@123 Users.

Step 6 create the database.

postgres=# CREATE DATABASE db_tpcc OWNER joe; 

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE DATABASE 

Finished creating DB_ After the TPCC database, you can exit the postgres database as follows, connect to the database with a new user, and perform the following operations such as creating tables. Of course, you can also choose to continue the follow-up experience under the default postgres database.

Exit the postgres database.

postgres=#  \q 

Connect to this database with a new user.

[omm@ecs-c9bf ~]$ gsql -d db_tpcc -p 26000 -U joe -W Bigdata@123  -r

When the result is displayed as the following information, it indicates that the connection is successful.

gsql ((openGauss 1.1.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131 
Non-SSL connection (SSL connection is recommended when requiring high-security) 
Type "help" for help. 
  
db_tpcc=> 

Step 7 create SCHEMA.

db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe; 

When the result displays the following information, it indicates that SCHEMA creation is successful.

CREATE SCHEMA

Step 8 create a table.

Create a table named mytable with only one column. The field name is firstcol and the field type is integer.

db_tpcc=>  CREATE TABLE mytable (firstcol int); 
CREATE TABLE

Step 9 insert data into the table:

db_tpcc=> INSERT INTO mytable values (100); 
When the result is displayed as the following information, it indicates that the data insertion is successful.
INSERT 0 1    

To view data in a table:

db_tpcc=> SELECT * from mytable; 
 firstcol  
---------- 
      100 
(1 row)

Step 10 exit the postgres database.

postgres=#  \q

The experiment is over.

Original link: Gauss squirrel Club