openGauss每日一练第3课（数据库db） | （【七七】学习内容/心得体会）

学习目标

学习openGauss创建数据库、修改数据库属性和删除数据库

课程学习

连接数据库

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r

1.创建数据库

–查看当前服务中有哪几个数据库

\l

omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
(4 rows)

omm=# 

–新创建名为music的数据库

CREATE DATABASE music;

–再次查看当前服务中的数据库，可以查看到新创建的数据库

\l

omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 music     | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
omm=#  omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
(5 rows)


omm=# 

2.修改数据库属性

–修改数据库的最大连接数

ALTER DATABASE music CONNECTION LIMIT= 10;

–将music重命名为music1

ALTER DATABASE music RENAME TO music1;

–查看修改成功

\l

omm=# ALTER DATABASE music CONNECTION LIMIT= 10;
ALTER DATABASE
omm=# ALTER DATABASE music RENAME TO music1;
ALTER DATABASE
omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 music1    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
(5 rows)

omm=# 

3.删除数据库

DROP DATABASE music1;

4.在新创建的数据库中创建表

CREATE DATABASE music2;

–进入数据库music2

\c music2;
CREATE TABLE t2(a int, b varchar(10));

–退出gsql程序

\q

omm=# CREATE DATABASE music2;
CREATE DATABASE
omm=# \c music2;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "music2" as user "omm".
music2=# CREATE TABLE t2(a int, b varchar(10));
CREATE TABLE
music2=# \q
omm@modb:~$ 

课后作业

1.分别创建名为tpcc1和tpcc2的数据库

omm=# create database tpcc1;
CREATE DATABASE
omm=# create database tpcc2;
CREATE DATABASE
omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 music2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 tpcc1     | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tpcc2     | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(7 rows)

omm=# 

2.将tpcc1数据库重命名为tpcc10

alter database tpcc1 rename to tpcc10;

3.分别使用\l和\l+两个元命令查看数据库信息

omm=# alter database tpcc1 rename to tpcc10;
ALTER DATABASE
omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 music2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
omm=#  postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 tpcc10    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tpcc2     | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(7 rows)


omm=# \l+
                                                               List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | 
               Description                 
           |       |          |             |             | omm=CTc/omm       |       |            | 
 tpcc10    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 10 MB | pg_default | 
 tpcc2     | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 10 MB | pg_default | 
(7 rows)

-----------+-------+----------+-------------+-------------+-------------------+-------+------------+-
-------------------------------------------
 music2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 10 MB | pg_default | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 10 MB | pg_default | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 10 MB | pg_default | 
default administrative connection database
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +| 10 MB | pg_default | 
default template for new databases
           |       |          |             |             | omm=CTc/omm       |       |            | 
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +| 10 MB | pg_default | 
unmodifiable empty database
omm=# 

4.在数据库tpcc2中创建customer表，字段自定义

\c tpcc2;
tpcc2=# create table customer(
customer_id integer not null,
customer_name char(10),
customer_age int);
CREATE TABLE
tpcc2=# insert into customer values(12,'tom',29);
INSERT 0 1
tpcc2=# select * from customer;
 customer_id | customer_name | customer_age 
-------------+---------------+--------------
          12 | tom           |           29
(1 row)

tpcc2=# 

5.删除新创建的数据库

tpcc2=# \dt   \\查看当前数据库的表
 public | customer | table | omm   | {orientation=row,compression=no}

tpcc2=# \dt+
 public | customer | table | omm   | 8192 bytes | {orientation=row,compression=no} | 

tpcc2=# drop table customer;   \\删除表
DROP TABLE
tpcc2=# \dt
No relations found.
tpcc2=# 
tpcc2=# drop database tpcc2;    \\删除数据库报错，由于当前数据库存在连接
ERROR:  cannot drop the currently open database
tpcc2=# \l
 music2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
tpcc2=#            |       |          |             |             | omm=CTc/omm
 tpcc10    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tpcc2     | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

\c omm   \\切换数据库
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# drop database tpcc2;      \\再次删除数据库，正常删除。
DROP DATABASE
omm=#

6.退出gsql程序