学习第三课
学习openGauss创建数据库、修改数据库属性和删除数据库
连接数据库
su - omm
gsql -r
1.创建数据库
–查看当前服务中有哪几个数据库
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
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的数据库
CREATE DATABASE music;
CREATE DATABASE
–再次查看当前服务中的数据库，可以查看到新创建的数据库
\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)
2.修改数据库属性
–修改数据库的最大连接数
ALTER DATABASE music CONNECTION LIMIT= 10;
ALTER DATABASE
–将music重命名为music1
ALTER DATABASE music RENAME TO music1;
ALTER DATABASE
–查看修改成功
\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)
3.删除数据库
DROP DATABASE music1;
DROP DATABASE
4.在新创建的数据库中创建表
CREATE DATABASE music2;
CREATE DATABASE
–进入数据库music2
\c music2;
CREATE TABLE t2(a int, b varchar(10));
CREATE TABLE
–退出gsql程序
\q
omm@modb:~$
第三天作业
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.
1.分别创建名为tpcc1和tpcc2的数据库
omm=# CREATE DATABASE tpcc1;
CREATE DATABASE
omm=# CREATE DATABASE tpcc2;
CREATE DATABASE
2.将tpcc1数据库重命名为tpcc10
ALTER DATABASE tpcc1 RENAME TO 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
-----------±------±---------±------------±------------±------------------
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 |
omm=# tpcc2 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
\l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
tpcc2 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 10 MB | pg_default |
(6 rows)
omm=# -----------±------±---------±------------±------------±------------------±------±-----------±-------------------------------------------
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=CTc/omm | | |
tpcc10 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 10 MB | pg_default |
4.在数据库tpcc2中创建customer表，字段自定义
\c tpcc2
CREATE TABLE t2(a int, b varchar(10));
\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 t2(a int,b varchar(20));
CREATE TABLE
tpcc2=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database “postgres” as user “omm”.
5.删除新创建的数据库
drop database tpcc10;
drop database tpcc2;
postgres=# DROP DATABASE tpcc10;
DROP DATABASE
postgres=# DROP DATABASE tpcc2;
DROP DATABASE
6.退出gsql程序
postgres=# \q
omm@modb:~$
