实验三数据库和关系表.docx

实验三数据库和关系表
一、实验目的
1. 熟悉openGauss的基本操作环境
2. 掌握gsql命令的操作方法，掌握使用gsql创立管理数据库与数据表
3. 掌握使用Data Studio创立管理数据库与数据表
二、预备知识
I. 使用数据库
步骤1以操作系统用户omm登录数据库主节点。
[root@ecs-c9bf script]# su - omm
假设不确定数据库主节点部署在哪台服务器，请确认连接信息。
步骤2启动服务。
启动服务命令：
[omm@ecs-c9bf ~]$ gs_om -t start
Starting cluster.
Successfully started.
步骤3连接数据库。
| [omm@ecs-c9bf 〜]$ gsql d postgres -p 26000 r
当结果显示为如下信息，那么表示连接成功。
gsql ((openGauss 1.1.0 build 290d 125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help.
postgres=#
其中，postgres为openGauss安装完成后默认生成的数据库。初始可以连接到此数据库 进行新数据库的创立。2600()为数据库主节点的端口号，需根据。penGauss的实际情况做替 换，请确认连接信息获取。
引申信息：
使用数据库前，需先使用客户端程序或工具连接到数据库，然后就可以通过客户 端程序或工具执行SQL来使用数据库了。
gsql是openGauss数据库提供的命令行方式的数据库连接工具。
步骤4笫一次连接数据库时，需要先修改。mm用户密码，新密码修改为Bigdata@123 (建议用户自定义密码)。
poslgrcs=# alter role omm identified by *Bigdata@123' replace ,openGauss@123,; ALTER ROLE
步骤5创立数据库用户。
默认只有openGauss安装时创立的管理员用户可以访问初始数据库，您还可以创立其他 数据库用户帐号。
| postgres=# CREATE USER ecustOl WITH PASSWORD ”opengauss@123”；—
| select * from Student;stu_cou=
sno
9500295003
0 95016 9501896001
0 95001(8 rows)
from Student;
ssex I +女|1976-04-08
女I1977-02-02女I1977-03-05
女I1978-11-01勇I1977-04-06
男I1978-06-02男I1979-08-18
男I1977-05-01sbirth
00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:0056031863
22222112pt de
3似语语学会学学组
汁英英化社数数计
图5-4查找数据根据上述方法依次创立Course、SC表，并将数据插入到对应表中。
stu_cou=# stu_cou(# stu_cou-# INSERT 0 2 stu_cou=# stu_cou-# 3tU_COU~# stu_cou-# stu_cou-# stu_cou-# stu_cou-# stu_cou-# INSERT 0 7insert into Course values( ,I*,*数据库_设计七，必修*, *5', (2,，数学，，，必修七1 \2);
into Course values
信息系统七，必修' 'I*
操作系统七，必修七'61
数据结构七，必修七*7*
数据—处理，，，选修七八
PASCAL语言七，选修七1
概率统if , *必修七八,
计算机网络七，必修七，
,4),3)
,4),2)
6',3),
stu cou=#select * from Course;
cname I ctype | cpno数据库—设汁 数学 信息系统 操作系统 数据结构 数据—处理 PASCAL^ (*i" 概率统汁 计算机网络
rows)stu cou=#
图5-5插入Course表、SC表4),
；),
4)；
I ccredit +I4
I2I4
I3I4
I2I4
I3I4

=• u 1 o o c o-5 u 9 t • 3 (
into SC valuesselect
2,80),stu_cou=#
95001||
0 |95002|
95002|95016|
95018||
0 | (9 rows)

图5-6查找Course表、SC表
【例3-5】在数据库Stu.Cou中创立表S1
create table S1
(Sno char(6) not null constraint s_u unique.
Sname varchar (10) not null,
Ssex char (8 ),
Sbirth date,
Scrcdit real,
Sclass char(6),
Sdept varchar(20) not null);
注：constraint s_u unique表示新建名称为“s u”的约束，其作用是约束学号取值唯一。
【例3-6】向S1表增加Sentrance （入学时间）列，其数据类型为日期型
alter table SI
add Sentrance date;
注：新增加的列一律为空值
【例3-7］修改表S1的列Scrcdit的类型
alter tabic S i
alter column Scrcdit type SM ALLI NT;
注：修改原有的列定义有可能会破坏已有数据
【例3-8］删除unique约束alter table SI drop constiaint s u;
【例3-9】删除表SI | drop table SI;
四、习题
该局部表格按照前面讲述的表格字段进行。Student表5-2结构：Course表5-4结构，SC表 5-6结构
如上表，完成下面习题
I、请在数据库Stu_Cou中建立表Course、表SC,并输入数据。
2^请应用gsql命令建立表Cl,表结构与表Course相同，并为列Cno建立unique约束c_u。
3、应用gsql命令向Cl表增加Cperiod (学时)列，其数据类型为整型。
4^应用gsql命令删除unique约束c_u。
5、应用gsql命令修改表C1的列Cno的类型为char(10)。
6、删除表Cl。
当结果显示为如下信息，那么表示创立成功。
| CREATE ROLE
如上创立了一个用户名为joe,密码为Bigdata@123的用户。
步骤6创立数据库。
CREATEDATABASE 功能描述
创立一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库templateO来创 建，且仅支持使用templateO来创立。
考前须知◊只有拥有CREATEDB权限的用户才可以创立新数据库，系统管理员默认拥有此权 限。
◊不能在事务块中执行创立数据库语句，即事务中不支持创立databaseo◊ 在创立数据库过程中，假设出现类似“could nol initialize database directory n的错误提 示，可能是由于文件系统上数据Fl录的权限缺乏或磁盘满等原因引起。
CREATE DATABASE 语法格式
CREATE DATABASE dalabase_name[[WITH ] { [ OWNER [=] user_name ] |
[TEMPLATE [=] template ] |[ENCODING [=] encoding ] |
[LC.COLLATE 1=] lc_collate ] | [LC.CTYPE [=] lc_clype ||[DBCOMPATIBILITY [=] compatibilty_type ] |
[TABLESPACE [=] tablespace_name ] |[CONNECTION LIMIT [=] connlimit ])[...]];
在执行系统界面下如下列图:
postgres=# \help create database
Command:CREATE DATABASE
Description: create a new database Syntax:
CREATE DATABASE database_name[[WITH ] {[ OWNER 7=] user_name ]|
[TEMPLATE [=] tenolate ]|[ENCODING [=] encoding ]|
[LC_COLLATE [=] lc_collate ]|[LC^CTYPE [=] lc_ctype ]|
[DBCOMPATIBILITY-[=] compatibility_type ]| [TABLESPACE [=] tablespace_name ]|[CONNECTION LIMIT [=] connlimit ]}[...]];
postgres=# |
database_name
数据库名称。
取值范围：字符串，要符合标识符的命名规范。
OWNER [ = ] user_name
数据库所有者。缺省时，新数据库的所有者是当前用户。
取值范围：已存在的用户名，如果不存在那么报错。
TEMPLATE [ = ] template
模板名。即从哪个模板创立新数据库。openGauss采用从模板数据库复制的方式来创立 新的数据库。初始时，openGauss包含两个模板数据库template。、template 1,以及•个默认 的用户数据库postgreso
取值范围：仅templateOo
ENCODING [ = ] encoding
指定数据库使用的字符编码，可以是字符串（如SQL_ASCID、整数编号。
不指定时，默认使用模版数据库的编码。模板数据库templateO和【emplatel的编码默认 与操作系统环境相关o template 1不允许修改字符编码，因此假设要变更编码，请使用templateO 创立数据库。
常用取值：GBK、UTF8、LatinK
LC_COLLATE [ = ] lc_collate
指定新数据库使用的字符集。例如，通过lc_collate = 'zh-CN.gbk'设定该参数。
该参数的使用会影响到对字符串的排序顺序（如使用ORDERBY执行，以及在文本列 上使用索引的顺序）。默认是使用模板数据库的排序顺序。
取值范围：有效的排序类型。
LC_CTYPE [ = ] Ic.ctype
指定新数据库使用的字符分类。例如，通过lc_ctypc = 'zlvCN.gbk'设定该参数。该参数 的使用会影响到字符的分类，如大写、小写和数字。默认是使用模板数据库的字符分类。
取值范围：有效的字符分类。
DBCOMPATIBILITY [ = ] compatibi lty_type
指定兼容的数据库的类型。
取值范围：A、B、Co分别表示兼容Oracle、Mysql和TD。
TABLESPACE [ = ] tab Iespace_name
指定数据库对应的表空间。
取值范围：己存在表空间名。
CONNECTION LIMIT [ = ] connlimit
数据库可以接受的并发连接数。
| postgres=# CREATE DATABASE db ecust01 OWNER ecustOl;
当结果显示为如下信息，那么表示创立成功。
| CREATE DATABASE—
创立完db.ecustOl数据库后，就可以按如下方法退出postgres数据库，使用新用户连
接到此数据库反行接卜来的创立表等操作。当然，也可以选择继续在默认的postgres数据库 下做后续的体验。
退出postgres数据库。
| postgres=# \q
使用新用户连接到此数据库。
| [omm@ecs-c9bf ~]$ gsql -d db ccust01 -p 26000 -E ecustOl -W opengauss@123 -r
当结果显示为如下信息，那么表示连接成功
gsql ((openGauss 1.1.0 build 290d 125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db_tpcc=>
步骤7创立SCHEMAo| db tpcc=> CREATE SCHEMA ecustOl AUTHORIZATION ecustOl;
当结果显示为如下信息，那么表示创立SCHEMA成功。
| CREATE SCHEMA
步骤8创立表。
创立一个名称为mytable,只有一列的表。字段名为firstcol,字段类型为integer。
db_tpcc=> CREATE TABLE mytablc (firstcol int);
CREATE TABLE
步骤9向表中插入数据：
| db tpcc=〉INSERT INTO mytable values (10);
当结果显示为如下信息，那么表示插入数据成功。
| INSERT。1
查看表中数据：
db_tpcc=> SELECT * from niytable;
firstcol
10
(1 row)
步骤10退出postgres数据库。
| postgres=# \q
2. Data Studio主界面包括：
1. 主菜单：提供使用Data Studio的基本操作；
2. 工具栏：提供常用操作入口；
3. “SQL终端”页签：在该窗口，可以执行SQL语句和函数/过程；
4. “PL/SQL Viewer”页签：显示函数/过程信息；
5. 编辑区域用于进行编辑操作；
6. “调用堆栈”窗格：显示执行栈：
7. “断点“窗格：显示断点信息；
8. “变量”窗格：显示变量及其变量值；
9. “SQL助手”页签：显示“SQL终端”和“PL/SQL Viewer”页签中输入信息的建议或参考;
10. “结果"页签：显示所执行的函数/过程或SQL语句的结果；
11. "消息"页签：显示进程输出。显示标准输入、标准输出和标准错误；“对象浏览器”窗格：显示数据库连接的层级树形结构和用户有权访问的相关数据库对象; 除公共模式外，所有默认创立的模式均分组在“系统模式"下，用户模式分组在相应数据库的 “用户模式''下；
12. “最小化窗口窗格”：用于翻开“调用堆栈”和“变量”窗格。该窗格仅在“调用堆栈”、“变量” 窗格中的一个或多个窗格最小化时显示。
13. 搜索工具栏：用于在“对象浏览器”窗格中搜索对象。
有些项不可见，除非触发特定功能。下列图以openGauss界面为例说明:
d)4UP^OIMM «
IX 95” m«U«MTH»J0| ROflUXVr

□ 1
AW

-O jf
•

，

•

-cQJ

SB o

It

f

• -ft
-o

[• u

"KAWWOWQADS nROATABASf na OGAUIT MMKXS ntlXTfNSION nRKMDGNTAMl<K)l HRKMOGNYAMf (K> nt FUNCTION TUGAOur nKRMX HRlAltaoeMCT TtRNOOf m nsounaKKx TUROU
TU SOMMA nt S€QUCMCf TW MRVIR TUSUSION TlRSVSTlMQUttSSIOI UK TAME
MUR TABU rMTmOH MRU TAtUSMCf

10图 5-1 Data Studio 主界面
♦
，

二、实验例如【例3-l】使用gsql语句连接数据库并创立一个新的数据库stub 以操作系统用户onim登录数据库主节点。
| |root@ecs-c9bf script]# su - omm
启动服务命令：
[omm@ecs-c9bf 〜]$ gs_om -t start Starting cluster.
Successfully started.
连接数据库
lomm@ecs-c9bf ~]$ gsql -d postgres -p 26000 -r
当结果显示为如下信息，那么表示连接成功。
gsql ((openGauss 1.1.0 build 290dl25D compiled at 2020-05-08 02:59:43 commit 2143 last mr 131 Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help.
postgres=#
创立数据库用户。
postgres=# CREATE USER ecustO 1 WITH PASSWORD "opengauss@123";
当结果显示为如下信息，那么表示创立成功。
CREATE ROLE
如上创立了一个用户名为ecustOl,密码为opcngauss@123的用户。
创立数据库| posigres=# CRE/VTE DATABASE slu cou OWNER ccustOl;"
当结果显示为如下信息，那么表示创立成功| CREATE DATABASE
退出postgres数据库。
postgres=# \q
使用新用户连接到此数据库。
[omm@ecs-c9bf ~]$ gsql -d stu cou -p 26000 -U ecustO 1 -W opengauss@ 123 -r
当结果显示为如下信息，那么表示连接成功
gsql ((openGauss 1.1.0 build 290dl25f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131 Non-SSL connection (SSLconnection is recommended when requiring high-securily) Type "help" for help.
stul=>
【例3-2】删除数据库stu_cou| postgres=# DROP DATABASE stu I;
用户可以使用DROP DATABASE命令删除数据库。这个命令删除了数据库中的系统目录,并旦删除了带有数据的磁盘上的数据库目录。用户必须是数据库的。wner或者系统管理员才 能删除数据库。当有人连接数据库时，删除操作会失败。删除数据库时清先连接到其他的数 据库。
【例3-3】创立数据库Stu_Cou posigres=# CREATE DATABASE Siu_Cou : CREATE DM'ABASE##连接数据库
|omm@ecs-c9bf 〜]$ gsql -d stu_Cou -p 26000 -U -r
【例3-4］在数据库Stu_Cou中创立三个表，分别为Student表、Course表、SC表。这三个表中的例如数据及表结构如I、.:
Student表（学生表5-1）
Sno
Sname
Ssex
Sbirth
Spre
Sclass
Sdept
95001
李勇
男
1977-5-1
23
计 9501
计算机
95002
刘晨
女
1976-4-8
25
计 9502
计算机
95003
李鸣
女
1977-2-2
26
英 9501
英语
95004
王敏
女
1977-3-5
20
英 9501
英语
95016
金瑾
女
1978-11-1
23
化 9501
化学
95018
汪劲
男
1977-4-6
21
社 9502
社会
96001,
张立
男
1978-6-2
18
数 9601
数学
96002
柳风
男
1979-8-18
16
数 9602
数学
Student 表 5-2 结构:
列名
含义
类型
允许空
Sno
学号
char( 6)
X
Sname
姓名
varchar( 1())
X
Ssex
性别
char(8)
q
Sbirth
出生日期
datetime
q
Sprc
己修学分
real
q
Sclass
班级
char(6)
q
Sdept
所在系
varchar(20)
q

Course表5-3 (课程表)
Cno
Cname
Ctype
Cpno
Ccredit
1
数据库一设计
必修
5
4
2
数学
必修

2
3
信息系统
选修
1
4
4
操作系统
必修
6
3
5
数据一结构
必修
7
4
6
数据一处理
选修

2
7
PASCAL语言
选修
6
4
8
概率统计
必修

3
9
计算机网络
必修
1
4

Course表5-4结构
列名
含义
类型
允许空
Cno
课程号
char(6)
X
Cname
课程名称
varchar(20)
X
Ctypc
课程类型
char(8)

Cpno
先修课号
char(6)

Ccredil
学分
ini

SC表5-5 （学生选课表）
Sno
Cno
Grade
95001
1
92
95001
2
80
95002
1

95002
2
71
95002
3
52
95016
2
88
95018
6
80
96001
1

96002
8
82

SC表5-6结构根据如下代码创立Student表：自己设置主码，是否为空等
列名
含义
类型
是否允许空
Sno
学号
char(6)
X
Cno
课程号
char(6)
X
Grade
成绩
int

CREATE TABLE Student (Sno integer,
Sname char(5),Ssex char(5),
Sbirth dale,Spre real、
Sclass char(6),Sdept varchaiX20)
CREATETABLE
其中 Sno、Sname^ Ssex 和 Sbirth、Spre、Sclass、Sdept 是表的字段名，integer> char(5)、 date、real、char(6)和varchar(20)分别是字段名称的类型。
3tU_COU=# 5tU_COU(# 5tU_COU(# 5tU_COU(# 5tU_COU(# 5tU_COU(# 3tU_COU(# 5tU_COU(# 5tU_COU(#
CREATE TABLE Student( Sno integer,
Sname char(5),
Ssex char(5),
Sbirth date,
Spre integer,
Sclass char(6),
Sdept varchar (20)
)；
:REATE TABLE 3tU_COU=# 3tU_COU(# 5tU_COU(# 5tU_COU(# 5tU_COU(# 5tU_COU(# 3tU_COU(#
CREATE TABLE Course( Cno char(6),
Cname varchar(20),
Ctype char(8),
Cpno char(6),
Ccedit integer
)；
:REATE TABLE 5tU_COU=# 5tU_COU(# 5tU_COU(# 5tU_COU(# 5tU_COU(#
CREATE TABLE SC( Sno char(6),
Cno char(6), Grade integer )；
ZREATE TABLE
图5-2创立数据表
根据如下命令将数据插入到Student表中:
INSERT INTO Student VALUES
('95001','李勇'，’男'，'5-1-1977',23，'计9501','计算机'), (，95002‘，'刘晨'，'女'，‘4-8-1976',25 ,，计 9502'；计算机'), (95003，，，李鸣'，'女'221977',26,'英9501','英语) (95004,,'王敏'，’女'，'351977', 20,'英 9501'英语')， (•95016'/ 金瑾女'，，11-1-1978', 23,'化 9501、化学) ('95018','汪劲'，'男'，'461977', 21 ,，社 9502'社会) (，96001二，张立二，男二，621978，，18,哦 9601*；数学')， (‘96002,「柳风丁男二，8-18-1979，，16,，数 9602，,，数学〉 INSERT 0 7
stu_cou=# 2，尸计算机 (•95003*,' (»95004',' (•95016*,' ('95018',' (• 96001 S ' ('96002',' INSERT 0 7
INTO Student VALUES(* 95002'
，4-8-1976',25,' it950
女女女男男男
,,2-2-1977',26,'英9501',
,*3-5-1977',20, '英9501',
,'H-l-1978',23,'化9501’
,'4-6-1977',21,'社9502
,'6-2-1978',18,，数9601', »8-18-1979',16,，数9602,
语语妇会学
英英,(社数
图5-3插入数据
查看数据是否插入: