openGauss每日一练第 3 天 | 学习笔记

👉openGauss SQL学习参考

学习目标

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

课程学习

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r   

gsql参数

1.创建数据库

--查看当前服务中有哪几个数据库
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)

--新创建名为 music 的数据库
omm=# CREATE DATABASE music;
CREATE DATABASE

--再次查看当前服务中的数据库，可以查看到新创建的数据库
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   | 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.修改数据库属性

--修改数据库的最大连接数
omm=# ALTER DATABASE music CONNECTION LIMIT= 10;
ALTER DATABASE

--将 music 重命名为 music1
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.删除数据库

omm=# DROP DATABASE music1;
DROP DATABASE
omm=# 

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

omm=# CREATE DATABASE music2;
CREATE DATABASE

--进入数据库music2
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=# \d
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t2   | table | omm   | {orientation=row,compression=no}
(1 row)

--退出gsql程序
music2=# \q
omm@modb:~$ 

课后作业

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

omm=# create database tpcc1 ;
CREATE DATABASE
omm=# create database if not exists tpcc2;
ERROR:  syntax error at or near "not exists"
LINE 1: create database if not exists tpcc2;
                           ^
omm=# create database tpcc2 ;
CREATE DATABASE
omm=#   --create database 不支持 if not exists

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)

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

omm=# ALTER DATABASE tpcc1 RENAME TO tpcc10;
ALTER DATABASE

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

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
 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=# 
omm=# \l+
                                                               List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace |                Descriptio
n                 
-----------+-------+----------+-------------+-------------+-------------------+-------+------------+--------------------------
------------------
 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 co
nnection database
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +| 10 MB | pg_default | default template for new 
databases
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +| 10 MB | pg_default | unmodifiable empty databa
se
           |       |          |             |             | omm=CTc/omm       |       |            | 
           |       |          |             |             | 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)

omm=# 

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

\c tpcc2
omm=# \c tpcc2
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "tpcc2" as user "omm".
tpcc2=# create table customer (id serial,name varchar2(20),dt date);
NOTICE:  CREATE TABLE will create implicit sequence "customer_id_seq" for serial column "customer.id"
CREATE TABLE
tpcc2=#  --支持 varchar2 类型
tpcc2=# \d customer
                                Table "public.customer"
 Column |         Type          |                       Modifiers                       
--------+-----------------------+-------------------------------------------------------
 id     | integer               | not null default nextval('customer_id_seq'::regclass)
 name   | character varying(20) | 
 dt     | date                  | 

tpcc2=# insert into customer (name,dt) values('a',now());
INSERT 0 1
tpcc2=# select * from customer;
 id | name |     dt     
----+------+------------
  1 | a    | 2021-12-04
(1 row)

tpcc2=# 

5.删除新创建的数据库

tpcc2=# drop database tpcc1;
ERROR:  database "tpcc1" does not exist
tpcc2=# drop database if exists tpcc1;
NOTICE:  database "tpcc1" does not exist, skipping
DROP DATABASE
tpcc2=# -- drop database 支持 if exists

tpcc2=# drop database if exists tpcc2;
ERROR:  cannot drop the currently open database
--无法drop 当前有连接的 database

tpcc2=# \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 if exists tpcc2;
DROP DATABASE
omm=# 

6.退出gsql程序

omm=# \q
omm@modb:~$