第三课
3.1 打卡第3天
👉openGauss SQL学习参考资料
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html
学习目标
学习openGauss创建数据库、修改数据库属性和删除数据库
课程学习
连接数据库
#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
1.创建数据库
–查看当前服务中有哪几个数据库
\l
–新创建名为music的数据库
CREATE DATABASE music;
–再次查看当前服务中的数据库,可以查看到新创建的数据库
\l
2.修改数据库属性
–修改数据库的最大连接数
ALTER DATABASE music CONNECTION LIMIT= 10;
–将music重命名为music1
ALTER DATABASE music RENAME TO music1;
–查看修改成功
\l
3.删除数据库
DROP DATABASE music1;
4.在新创建的数据库中创建表
CREATE DATABASE music2;
–进入数据库music2
\c music2;
CREATE TABLE t2(a int, b varchar(10));
–退出gsql程序
\q
课后作业
1.分别创建名为tpcc1和tpcc2的数据库
CREATE DATABASE tpcc1;
CREATE DATABASE tpcc2;
2.将tpcc1数据库重命名为tpcc10
ALTER DATABASE tpcc1 RENAME TO tpcc10;
3.分别使用\l和\l+两个元命令查看数据库信息
4.在数据库tpcc2中创建customer表,字段自定义
\c tpcc2
create table customer(cno integer,cname varchar(30));
5.删除新创建的数据库
drop database tpcc10;
drop database tpcc2;
6.退出gsql程序
执行过程:
omm@modb:~$ CREATE DATABASE tpcc1;
-bash: CREATE: command not found
omm@modb:~$ CREATE DATABASE tpcc2;
-bash: CREATE: command not found
omm@modb:~$
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.
omm=# CREATE DATABASE tpcc1;
CREATE DATABASE
omm=# CREATE DATABASE tpcc;2;
CREATE DATABASE
omm=#
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 |
omm=# 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
-----------+-------+----------+-------------+-------------+-------------------
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 +
(7 rows)
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 |
omm=#
omm=#
omm=# ALTER DATABASE tpcc1 RENAME TO tpcc10;
ALTER DATABASE
omm=# \l
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=# 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
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |
Description
-----------+-------+----------+-------------+-------------+-------------------+-------+------------+-------
-------------------------------------
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 | defaul
t administrative connection database
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| 10 MB | pg_default | defaul
t template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +| 10 MB | pg_default | unmodi
fiable empty database
tpcc2 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 10 MB | pg_default |
(7 rows)
| | | | | omm=CTc/omm | | |
tpcc10 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 10 MB | pg_default |
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=#
tpcc2=# create table customer(cno integer,cname varchar(30));
CREATE TABLE
tpcc2=#
tpcc2=# drop database tpcc10;
DROP DATABASE
tpcc2=# drop database tpcc2;
ERROR: cannot drop the currently open 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 tpcc2;
DROP DATABASE
omm=# \q
omm@modb:~$