数据库系统课程上机实验手册
华为OpenGauss版
大连理工大学
Dalian University of Technology
目录
第一章 实验介绍
1.1关于本实验
本实验共包含6个子实验,从平台准备开始,逐一介绍了scoot数据库中的SQL语法,包括数据查询、数据更新、数据定义和数据控制。
1.2实验目的
学习SQL相关操作,了解OpenGauss系统对索引和事务并发控制的实现。
1.3内容描述
子实验2为数据定义实验,介绍了DDL的类型、语法格式和使用场景,帮助读者熟练掌握如何用数据定义语言定义或修改数据库中的对象。
子实验3为数据更新实验,通过对DML语言基本语法和使用,帮助读者掌握如何对数据库表中数据进行更新操作,包括数据插入、数据修改和数据删除。
子实验4为数据查询实验,通过基本的DQL语言使用,帮助读者掌握从一个或多个表查询数据的操作。
子实验5为索引操作,通过基本的索引使用,帮助读者掌握索引的创建和使用。
子实验6为事务并发控制,通过对事务并发场景的设计,帮助读者了解OpenGauss数据库关系对并发的支持情况。
第二章 平台准备
2.1 预备知识
参考OpenGauss安装手册。
2.2 实验任务
搭建OpenGauss数据库平台,并通过截图展示。需要将搭建成功的平台,根据自己的情况完成对应的如下两个截图。
截图1:自己购买的服务器信息(包含公网IP)
截图2:数据库链接成功界面(包含公网IP)
截图1示例
截图2示例
第三章 DDL
3.1 预备知识
数据库模式定义语言DDL(Data Definition Language),是用于描述数据库中要存储的现实世界实体的语言,用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#类型:使用限制字段必须以什么样的数据类型传值
#约束条件:约束条件是在类型之外添加一种额外的限制
3.2 实验任务
创建DEPT、BONUS、SALGRADE、EMP表,关系模式为:
DEPT (DEPTNO INT, DNAME VARCHAR(14),LOC VARCHAR(13));
EMP (EMPNO INT,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE, SAL FLOAT, COMM FLOAT, DEPTNO INT);
BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL INT, COMM INT);
SALGRADE ( GRADE INT, LOSAL INT, HISAL INT);
注意:EMP表中的DEPTNO属性为外键,对应DEPT表中的DEPTNO
3.3 SQL代码与对应结果
请输入如下代码,验证执行结果是否与答案相符。
CREATE TABLE DEPT (
DEPTNO INT,
DNAME VARCHAR(14),
LOC VARCHAR(13),
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);
CREATE TABLE BONUS (
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL INT,
COMM INT
);
CREATE TABLE SALGRADE (
GRADE INT,
LOSAL INT,
HISAL INT
);
CREATE TABLE EMP (
EMPNO INT,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT,
HIREDATE DATETIME,
SAL FLOAT,
COMM FLOAT,
DEPTNO INT,
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
答案:
第四章 DML
4.1 预备知识
DML是Data Manipulation Language的缩写,意思是数据操纵语言,是指在SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除,是开发以数据为中心的应用程序必定会使用到的指令。
1 增
INSERT INTO tb_name (col_name1, col_name2) VALUES ('val1', 'val1'),('val2', 'val2');
2 删
DELETE FROM table_name [WHER];
3 改
UPDATE tb_name SET col_name1 = 'new_val1', col_name2 = 'new_val2' [WHERE];
4.2 实验任务
4.2.1 实践题目1
在DEPT表中插入数据10, 'ACCOUNTING', 'NEW YORK'。
请写出满足查询结果的查询代码。
结果:
代码:
insert into dept(deptno,dname,loc)VALUES(10,'ACCOUNTING','NEW YORK');
4.2.2实践题目2
在DEPT表中根据DEPTNO修改LOC为BEIJING。
请写出满足查询结果的查询代码。
结果:
代码:
UPDATE dept SET LOC='BEIJING';
4.2.3实践题目3
在DEPT表中删除DEPTNO为10的数据,。
请写出满足查询结果的查询代码。
结果:
代码:
DELETE dept WHERE DEPTNO=10;
4.2.4实践题目4
在DEPT表中插入两条数据10, 'ACCOUNTING', 'NEW YORK'和20, 'RESEARCH', 'DALLAS'。
请写出满足查询结果的查询代码。
结果:
代码:
INSERT INTO dept(deptno,dname,loc)VALUES(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS');
4.2.5 实践题目5
删除DEPT表中所有数据。
请写出满足查询结果的查询代码。
结果:
代码:
DELETE FROM dept;
4.2.6 实践题目6
执行如下代码,并验证代码执行结果是否与图片给出的结果相同。
代码:
Delete from DEPT;
INSERT INTO DEPT VALUES
(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES
(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES
(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES
(40, 'OPERATIONS', 'BOSTON');
Delete from EMP;
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7566, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '1987-06-13', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURN...', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '1987-06-13', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
Delete from SALGRADE;
INSERT INTO SALGRADE VALUES
(1, 700, 1200);
INSERT INTO SALGRADE VALUES
(2, 1201, 1400);
INSERT INTO SALGRADE VALUES
(3, 1401, 2000);
INSERT INTO SALGRADE VALUES
(4, 2001, 3000);
INSERT INTO SALGRADE VALUES
(5, 3001, 9999);
结果:
DEPT表:
EMP表:
SALGRADE表:
BONUS表: 无数据
第五章 数据查询
对于每个查询需求,给出对应的查询语句,并验证是否与给出的结果相同。将查询语句写在“代码:”下面,具体行数可以根据语句行数自行调节。
5.1 单表查询
5.1.1 预备知识
单表查询是最简单的查询方式。所有要查询的信息,都集中在一张表中。也就是说,SQL语句中的FROM子句中只有一个表。我们可以通过以下的几道实践题目来巩固这个知识点。
5.1.2 实践题目1
查看EMP表中部门号为10的员工的姓名,职位,参加工作时间,工资。
结果:
代码:
SELECT ENAME,JOB,HIREDATE,sal
FROM emp
WHERE DEPTNO=1
5.1.3 实践题目2
查询每个员工每个月拿到的总金额(emp.sal为工资,emp.comm为补助)。(提示:gaussdb中,nvl(ex1,ex2)表示如果ex1为空则返回ex2)
结果:
代码:
SELECT ename,(emp.sal+nvl(emp.comm,0)) as total
FROM emp;
5.1.4 实践题目3
显示第3个字符为大写O的所有员工的姓名及工资。
结果:
代码:
SELECT ename,sal
FROM emp
where ename like'__O%';
5.1.5 实践题目4
显示有补助的员工的姓名,工资,补助。
结果:
代码:
SELECT ename,sal,comm
FROM emp
WHERE COMM IS NOT NULL;
5.1.6 实践题目5
显示员工的最高工资和最低工资。
结果:
代码:
SELECT MAX(SAL)最高工资 ,MIN(SAL) 最低工资
FROM emp;
5.2 聚合查询
5.2.1 预备知识
在查询中,我们经常会遇到这样的问题:求平均值、求最值等等。我们需要使用一些函数如AVG(), MAX()等来进行计算,也需要通过GROUP BY子句来聚合属性。
5.2.2 实践题目1
显示每种职业的平均工资。
结果:
代码:
SELECT JOB,AVG(SAL) average
FROM EMP
GROUP BY JOB
ORDER BY JOB;
5.2.3 实践题目2
显示每个部门每种岗位的平均工资和最高工资。
结果:
代码:
select deptno,job,avg(sal) average,max(sal) max
FROM EMP
GROUP BY deptno,job
ORDER BY JOB;
5.2.4 实践题目3
显示平均工资低于2500的部门号,平均工资及最高工资。
结果:
代码:
select deptno,avg(sal) average,max(sal)
FROM EMP
GROUP BY deptno
HAVING avg(sal)<2500;
5.3 多表查询
5.3.1 预备知识
在大部分情况下,我们所需要的信息并不仅仅包含在一张表中。我们首先需要使用join连接多个表,然后再进行查询
5.3.2 实践题目1
显示工资高于2500或岗位为MANAGER的所有员工的姓名,工资,职位,和部门号。
结果:
代码:
select ename,sal,job,deptno
FROM EMP
WHERE sal>2500 or job='MANAGER';
5.3.3 实践题目2
排序显示所有员工的姓名,部门号,工资(以部门号升序,工资降序,雇用日期升序显示)。
结果:
代码:
select ename,deptno,sal
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC, HIREDATE ASC;
5.3.4 实践题目3
采用自然连接的原理显示部门名以及相应的员工姓名。(Sql server不支持NATURAL JOIN语法。)
结果:
代码:
select dname,ename
FROM EMP JOIN dept USING(deptno);
5.3.5 实践题目4
查询SCOTT的上级领导的姓名。
结果:
代码:
select s.ename
FROM EMP t JOIN emp s ON t.deptno=s.deptno
WHERE t.ename='SCOTT' and t.mgr=s.empno
5.3.6 实践题目5
显示部门的部门名称,员工名即使部门没有员工也显示部门名称。
结果:
代码:
SELECT dname,ename
FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno
5.4 子查询(必须使用子查询实现)
5.4.1 预备知识
子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式。
5.4.2 实践题目1
显示所有员工的名称、工资以及工资级别。
结果:
代码:
SELECT ename,sal,(SELECT grade from salgrade WHERE sal BETWEEN losal AND hisal)
FROM emp
order by grade
5.4.3 实践题目2
显示所有工资等级为2的员工的姓名,工资。(分别给出相关子查询和不相关子查询的查询语句)
结果:
代码:
不相关子查询
SELECT ename,sal
FROM emp
WHERE sal BETWEEN (SELECT losal
FROM salgrade
WHERE grade=2) AND (SELECT hisal
from salgrade
WHERE grade=2)
相关子查询
SELECT ename,sal
FROM emp
WHERE EXISTS(SELECT*
from salgrade
WHERE emp.sal BETWEEN losal AND hisal
AND grade=2)
5.4.4 实践题目3
显示职位属于10号部门所提供职位范围的员工的姓名,职位,工资,部门号。
结果:
代码:
SELECT ename,job,sal,deptno
FROM emp
WHERE job in (SELECT job from emp WHERE deptno=10)
5.4.5 实践题目4
显示工资比30号部门中所有员工的工资都高的员工的姓名,工资和部门号。
结果:
代码:
SELECT ename,sal,deptno
FROM emp
WHERE sal>all(SELECT sal from emp WHERE deptno=30)
5.4.6 实践题目5
显示包含工资等级为4的员工部门信息,具体包括部门名称,部门所在地(使用双层嵌套子查询实现)
结果:
代码:
SELECT dname,loc
FROM dept
WHERE EXISTS(SELECT *
FROM emp
WHERE emp.deptno=dept.deptno
AND sal BETWEEN (SELECT losal
FROM salgrade
WHERE grade=4) AND
(SELECT hisal
FROM salgrade
WHERE grade=4))
5.5 集合查询
5.5.1 预备知识
当两张表的属性相同时,我们可以对它们做一些集合运算,如并集、交集等。
5.5.2 实践题目1
显示工资高于2500或职位为MANAGER的员工的姓名,工资和职位(采用UNION语法实现)。
结果:
代码:
SELECT ename,sal,job
FROM emp e
WHERE EXISTS (SELECT * FROM emp s WHERE e.sal>2500 AND s.ename=e.ename
UNION
SELECT * FROM emp t WHERE job='MANAGER' AND t.ename=e.ename)
5.5.3 实践题目2
显示工资高于2500且职位为MANAGER的员工的姓名,工资和职位(采用INTERSECT语法实现)。
结果:
代码:
SELECT ename,sal,job
FROM emp e
WHERE EXISTS (SELECT * FROM emp s WHERE e.sal>2500 AND s.ename=e.ename
INTERSECT
SELECT * FROM emp t WHERE job='MANAGER' AND t.ename=e.ename)
5.5.4 实践题目3
显示工资高于2500但职位不是MANAGER的员工的姓名,工资和职位(采用MINUS语法实现)。
结果:
代码:SELECT ename,sal,job
FROM emp e
WHERE EXISTS (SELECT * FROM emp s WHERE e.sal>2500 AND s.ename=e.ename
MINUS
SELECT* FROM emp t WHERE job='MANAGER' AND t.ename=e.ename)
5.5.5 实践题目4
显示提供了工资在2000~5000之间的所有职位的部门名称
结果:
代码:
SELECT dname,deptno
FROM dept
WHERE not EXISTS( SELECT job from emp WHERE sal > 2000 AND sal<5000
MINUS
SELECT job from emp WHERE dept.deptno=emp.deptno )
第六章 索引操作
6.1 预备知识
索引可以提高数据的访问速度,但同时也增加了插入、更新和删除表的处理时间。所以是否要为表增加索引,索引建立在哪些字段上,是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询条件或者被要求排序的字段来确定是否建立索引。索引相关的DDL包括创建索引、删除索引属性和删除索引。
利用索引检索,需要在大数据量的情况下才能够体现出效率差距。因此,本实验需要利用PLSQL(过程化SQL)知识自动构建一个含有大量数据的表作为实验对象。PLSQL相关知识请参考“HCIP-GaussDB-OLTP V1.0 培训教材”第236页
下面给出实验中可能用到的PLSQL代码示例,该代码用于向某个表循环插入100个数据。其中count是一个变量
declare
count int;
begin
count := 1;
while count<=100 loop
insert into table_name(id, name, password)
values(count,'some_name','some_name' || count);
count := count + 1;
end loop;
end;
6.2实验任务
创建一个表,然后向该表中循环插入大量数据(1000000条以上)。然后,对于该表中某个属性建立索引,并进行对比实验,对比利用索引和不利用索引的情况下,完成相同查询任务花费的时间,验证利用索引查询是否真的可以提升查询效率。
注:可以参考5.4节的视频。给出每个步骤的SQL语句,并对语句执行结果进行截图说明,尤其是其查询时间的区别截图。
//创建同样的两个表
CREATE TABLE TB_testtable
(id INT,
name VARCHAR(10),
PASSWORD VARCHAR(10)
);
CREATE TABLE TB_testtable_ind
(id INT,
name VARCHAR(10),
PASSWORD VARCHAR(10)
);
//向两个表分别插入1000000条数据
declare
count int;
begin
count := 1;
while count<=1000000 loop
insert into TB_testtable(id, name, password)
values(count,'zyh','dlut_2021' );
count := count + 1;
end loop;
end;
declare
count int;
begin
count := 1;
while count<=1000000 loop
insert into TB_testtable_ind(id, name, password)
values(count,'zyh','dlut_2021' );
count := count + 1;
end loop;
end;
//显示插入结果
SELECT *
FROM TB_testtable
//显示插入数量
SELECT count(*)
FROM TB_testtable
//显示另一张插入数据相同的表
SELECT *
from TB_testtable_ind
//显示插入数量
SELECT count(*)
from TB_testtable_ind
//对TB_testtable_ind表中的name建立索引
CREATE INDEX id_text on TB_testtable_ind(name);
//对没有建立索引的表格查询,并显示查询所花费时间
SELECT name,password from TB_testtable where name='20000'
//对建立索引的表格查询,并显示查询所花费时间
SELECT name,password from TB_testtable_ind where name='20000'
//删除对应索引
DROP INDEX id_text
第七章 事务的并发控制
7.1预备知识
事务并发可能带来的3大类问题:
1) dirty read(脏读),一个事务读取了另外一个事务未提交的数据。
- non-repeatable read(不可重复读),同一事务中,前后读取的数据不一致。
- phantom read(幻读),类似不可重复读,但针对插入/删除操作。
美国国家标准协会在SQL标准中,对于满足事务的隔离性的程度划分为以下四个级别。未提交读、已提交读、可重复读和序列化。不同的隔离级别能够解决的上述问题也不同(参考课程第一节)
7.2实验任务
通过并发实验设计,验证OpenGauss是否存在上述三类问题。根据验证结果给出OpenGauss系统默认的事务隔离级别。
注:实验设计可以模仿10.6节中的操作,设计两个事务的并发操作场景,判断是否存在对应问题。需要详细的实验设计过程描述和实验结果的截图。
1.打开两个终端模拟两个事务,并关闭事务自动提交
会话一查询:
会话二查询:
- 脏读:
事务1将工资加一百后查询,变为1100:
事务二查询仍是1000:
提交事务一:
事务二查询为1100:
结论:opengauss默认隔离级别可避免脏读
- 不可重复读:
事务二读工资为1100:
事务一更改工资为1200并提交:
事务二未提交情况下查询结果为1200:
结论:opengauss默认隔离级别不能防止不可重复读
三.幻读
事务二查询,结果有一条数据:
事务一插入一条数据并提交,此时有两条数据:
事务二未提交情况下查询结果有两条数据:
结论:opengauss默认隔离级别不能防止幻读
四.结论:opengauss的默认隔离级别为能防止脏读但不能防止不可重复读和幻读的Read committed(读已提交)隔离级别
第八章 用户权限设置及回收
使用GRANT命令进行用户授权包括以下三种场景:
- 将系统权限授权给角色或用户。
- 将数据库对象授权给角色或用户。
- 将角色或用户的权限授权给其他角色或用户。
8.1 将系统权限授权给用户或者角色
任务:用自己的姓名全拼代替“joe”完成下述操作,并将操作过程截屏
截图:
步骤1,启动服务器,再使用gsql客户端以管理员用户身份连接postgres数据库,假设端口号为26000。
gsql -d postgres -p 26000 -r
步骤2,创建名为joe的用户,并将sysadmin权限授权给joe。
openGauss=# CREATE USER joe PASSWORD 'Bigdata@123';
CREATE ROLE
openGauss=# GRANT ALL PRIVILEGES TO joe;
ALTER ROLE
8.2 将数据库对象授权给角色或用户
任务:用自己的姓名全拼代替“joe”完成下述操作,并将操作过程截屏
截图:
步骤1,撤销joe用户的sysadmin权限,然后创建tpcds模式,并给tpcds模式下创建一张reason表。
openGauss=# REVOKE ALL PRIVILEGES FROM joe;
ALTER ROLE
openGauss=# CREATE SCHEMA tpcds;
CREATE SCHEMA
openGauss=# CREATE TABLE tpcds.reason(r_reason_sk INTEGER NOT NULL,r_reason_id CHAR(16) NOT NULL,r_reason_desc VARCHAR(20) );
CREATE TABLE
步骤2,将模式tpcds的使用权限和表tpcds.reason的所有权限授权给用户joe。
openGauss=# GRANT USAGE ON SCHEMA tpcds TO joe;
GRANT
openGauss=# GRANT ALL PRIVILEGES ON tpcds.reason TO joe;
GRANT
授权成功后,joe用户就拥有了tpcds.reason表的所有权限,包括增删改查等权限。
步骤3,将tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查询权限,r_reason_desc的更新权限授权给joe。
openGauss=# GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;
GRANT
步骤4,将数据库postgres的连接权限授权给用户joe,并给予其在postgres中创建schema的权限,而且允许joe将此权限授权给其他用户。
openGauss=# GRANT create,connect on database postgres TO joe WITH GRANT OPTION;
GRANT
步骤5,创建角色tpcds_manager,将模式tpcds的访问权限授权给角色tpcds_manager,并授予该角色在tpcds下创建对象的权限,不允许该角色中的用户将权限授权给其人。
openGauss=# CREATE ROLE tpcds_manager PASSWORD 'Bigdata@123';
CREATE ROLE
openGauss=# GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;
GRANT
8.3 将用户或者角色的权限授权给其他用户或角色
任务:用自己的姓名全拼代替“joe”完成下述操作,并将操作过程截屏
截图:
步骤1,创建角色manager,将joe的权限授权给manager,并允许该角色将权限授权给其他人。
openGauss=# CREATE ROLE manager PASSWORD 'Bigdata@123';
CREATE ROLE
openGauss=# GRANT joe TO manager WITH ADMIN OPTION;
GRANT ROLE
步骤2,创建用户senior_manager,将用户manager的权限授权给该用户。
openGauss=# CREATE ROLE senior_manager PASSWORD 'Bigdata@123';
CREATE ROLE
openGauss=# GRANT manager TO senior_manager;
GRANT ROLE
8.4 权限回收
任务:用自己的姓名全拼代替“joe”完成下述操作,并将操作过程截屏
截图:
步骤1,撤销权限,并清理用户。
openGauss=# REVOKE joe FROM manager;
REVOKE ROLE
openGauss=# REVOKE manager FROM senior_manager;
REVOKE ROLE
openGauss=# DROP USER manager;
DROP ROLE
openGauss=# REVOKE ALL PRIVILEGES ON tpcds.reason FROM joe;
REVOKE
openGauss=# REVOKE ALL PRIVILEGES ON SCHEMA tpcds FROM joe;
REVOKE
openGauss=# REVOKE USAGE,CREATE ON SCHEMA tpcds FROM tpcds_manager;
REVOKE
openGauss=# DROP ROLE tpcds_manager;
DROP ROLE
openGauss=# DROP ROLE senior_manager;
DROP ROLE
openGauss=# DROP USER joe CASCADE;
DROP ROLE
注意:实验完成后请尽量清理本实验的对象,以免影响与其它实验产生冲突。
第九章 自我总结
(数据库系统理论及上机学习的心得体会,学习理解的难点和对于课程的改进建议)