This article describes the basic operations of using a database: creating a database, creating a table, inserting data into a table, and querying data in a table.
prerequisite
openGauss is running normally.
Steps
Log in to the active database node as the operating system user omm.
If you are not sure which server the database master node is deployed on, see Confirming Connection Information.
Connect to the database.
gsql -d postgres -p 8000
When the result is displayed as the following information, it means the connection is successful.
gsql ((openGauss x.x.x build 50dc16a6) compiled at 2020-11-29 05:49:21 commit 1071 last mr 1373)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#
Among them, postgres is the database generated by default after the openGauss installation is completed. You can initially connect to this database to create a new database. 8000 is the port number of the master node of the database. It needs to be replaced according to the actual situation of openGauss. Please refer to Confirm Connection Information to obtain it.
Extended information:
Before using the database, you need to use a client program or tool to connect to the database, and then you can execute SQL through the client program or tool to use the database.
gsql is a command-line database connection tool provided by this product. More database connection methods can refer to connect to the database.
Create a database user.
By default, only the administrator user created during openGauss installation can access the initial database, and you can also create other database user accounts. If you use the following command to create a user, please remember to change the password. The default password rules of openGauss are: at least 3 different character combinations in English uppercase and lowercase, numbers, and special symbols.
openGauss=# CREATE USER joe WITH PASSWORD "xxxxxxxxx";
When the result displays the following information, it means the creation is successful.
CREATE ROLE
As above, a user named joe and password xxxxxxxxx is created.
The following command is to set the joe user as the system administrator.
openGauss=# GRANT ALL PRIVILEGES TO joe;
Use the GRANT command to set related permissions. For details, see GRANT.
Additional information: For more information about database users, please refer to Managing Users and Permissions.
Create a database.
openGauss=# CREATE DATABASE db_tpcc OWNER joe;
When the result displays the following information, it means the creation is successful.
CREATE DATABASE
After the db_tpcc database is created, you can exit the postgres database as follows, and use a new user to connect to the db_tpcc database to perform operations such as creating tables. You can also choose to continue the follow-up experience under the default postgres database.
openGauss=# \q
gsql -d db_tpcc -p 8000 -U joe
Password for user joe:
gsql ((openGauss x.x.x build 50dc16a6) compiled at 2020-11-29 05:49:21 commit 1071 last mr 1373)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db_tpcc=>
Create SCHEMAs.
db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe;
When the result displays the following information, it means that the schema is created successfully.
CREATE SCHEMA
Extended information:
The database is created under the pg_default tablespace by default. To specify a tablespace, you can use the following statement:
openGauss=# CREATE DATABASE db_tpcc WITH TABLESPACE = hr_local;
CREATE DATABASE
Where hr_local is the name of the tablespace. For how to create a tablespace, see Creating and Managing Tablespaces.
Create tables.
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
Insert data into the table:
db_tpcc=> INSERT INTO mytable values (100);
When the result is displayed as the following information, it means that the data is inserted successfully.
INSERT 0 1
View the data in the table:
db_tpcc=> SELECT * from mytable;
firstcol
100
(1 row)
Extended information:
By default, new database objects are created in the "$user" schema, such as the newly created table. See Creating and Managing Schemas for more information on schemas.
See Creating and Managing Tables for more information on creating tables.
In addition to the created tables, the database also contains many system tables. These system tables contain openGauss installation information and information about various queries and processes running on openGauss. Information about the database can be gathered by querying the system tables. See Viewing System Tables.
openGauss supports row-column mixed storage, which provides high query performance for interactive analysis in various complex scenarios. For the selection of storage models, please refer to planning storage models.
Top comments (0)