前期知识储备
openGauss数据库基本操作
openGauss数据库备份与恢复
Postgresql - unrecognized configuration parameter
错误及其解决
- 设置外键遇到问题
[2021-05-11 11:11:22.498 CST] : [ERROR] 执行失败
错误代码:[0]SQL错误码: = 23503
[10.172.150.196:11234/139.9.240.153:26000] ERROR: insert or update on table "jsc909" violates foreign key constraint "studentnum"
详细:Key (s#)=(01032010) is not present in table "js909".
Line Number: 1
解决:
jsc里的01032010违反参照完整性约束
alter table jsc909 add foreign key (c#) references JC909 (c#) on DELETE RESTRICT;
alter table jsc909 add foreign key (s#) references JS909 (s#) on DELETE RESTRICT;
各关系模式属性设计及录入数据
一、 在openGauss中创建MYDB数据库,并在MYDB中创建学生、课程、选课三个表。
二、 将以下数据加入相应的表中:
三、完成以下操作,将相应SQL语句及执行结果截屏图保存,并写入实验报告中。
1.在上述基本表上完成以下查询:
(1) 查询电子工程系(EE)所开课程的课程编号、课程名称及学分数。
select c# ,cname,credit FROM jc909 WHERE c# LIKE 'EE%'
(2) 查询未选修课程“CS-01”的女生学号及其已选各课程编号、成绩。
select js909.s#,grade ,c# From JSC909 WHEN c# <> 'CS-01' and js909.s# = jsc909.s# and js909.sex='女';
select s#,grade,c# From JSC909 WHERE c# <> 'CS-01' and s# in (SELECT s# FROM js909 WHERE sex = '女')
(3) 查询2000年~2001年出生的学生的基本信息。
select * from js909 WHERE bdate>'2000-01-01 00:00:00' and bdate<'2001-1-1 00:00:00'
(4) 查询每位学生的学号、学生姓名及其已选修课程的学分总数。
select js909.s# ,js909.sname ,SUM (jc909.credit) from jsc909,js909 ,jc909 WHERE js909.s# = jsc909.s# and jsc909.c# = jc909.c# GROUP by js909.s#
(5) 查询选修课程“CS-02”的学生中成绩第二高的学生学号。
SELECT s# FROM jsc909 WHERE c#='CS-02'and grade =
(SELECT max(grade) FROM jsc909 WHERE c#='CS-02' AND grade < (SELECT MAX(grade) FROM jsc909 WHERE c#='CS-02'))
(6) 查询平均成绩超过“王涛“同学的学生学号、姓名和平均成绩,并按学号进行降序排列。
SELECT sname ,js909.s# ,avg(jsc909.grade) FROM js909 ,jsc909 WHERE js909.s# = jsc909.s#
GROUP BY js909.s#
HAVING avg(jsc909.grade)>(SELECT avg(grade) FROM jsc909 where S#=(SELECT s# FROM js909 WHERE sname = '王涛'))
ORDER by js909.s# DESC
(7) 查询选修了3门以上课程(包括3门)的学生中平均成绩最高的同学学号及姓名。
SELECT jsc909.s# ,avg(grade),js909.sname
FROM jsc909,js909 GROUP BY
jsc909.s#, js909.sname ,js909.s# HAVING
count(*)>2 AND
avg(grade)>=
all
(
SELECT avg(grade) FROM jsc909,js909
GROUP BY jsc909.s#,js909.sname ,js909.s#
HAVING count(*)>2
AND jsc909.s#=js909.s#
)
AND jsc909.s#=js909.s#
2.分别在JS×××和JC×××表中加入记录(‘01032005’,‘刘竞’,‘男’,‘1993-12-10’,1.75,‘东14舍312’)及(‘CS-03’,“离散数学”,64,4,‘陈建明’)。
INSERT INTO js909 VALUES ('01032005','刘竞','男','1993-12-10 00:00:00',1.75,'东14舍312')
INSERT INTO jc909 VALUES ('CS-03','离散数学',64,4,'陈建明')
3.将JS×××表中已修学分数大于60的学生记录删除。
DELETE FROM js909 WHERE s# IN
(
select js909.s#
from jsc909,js909 ,jc909
WHERE js909.s# = jsc909.s#
and jsc909.c# = jc909.c#
GROUP by js909.s#
HAVING SUM (jc909.credit)> 60
)
4.将“张明”老师负责的“信号与系统”课程的学时数调整为64,同时增加一个学分。
UPDATE jc909 SET period = 64 ,credit=credit+1 WHERE teacher = '张明'
5.建立如下视图:
(1) 居住在“东18舍”的男生视图,包括学号、姓名、出生日期、身高等属性。
CREATE VIEW 居住在东18舍的男生 AS
SELECT s# as 学号, sname as 姓名, bdate as 出生日期,height as 身高
FROM js909 WHERE sex = '男' and drom LIKE '东18%'
(2)“张明”老师所开设课程情况的视图,包括课程编号、课程名称、平均成绩等属性。
CREATE VIEW 张明的课程 AS
SELECT jc909.c# as 课程编号 ,jc909.cname as 课程名称,avg(jsc909.grade) as 平均成绩
FROM jc909 inner JOIN jsc909 ON jc909.c#=jsc909.C#
WHERE teacher='张明'
GROUP BY jc909.c#
(3)所有选修了“人工智能”课程的学生视图,包括学号、姓名、成绩等属性。
CREATE VIEW 修人工智能的学生 AS
SELECT js909.s# AS 学号 , js909.sname AS 姓名,jsc909.grade AS 成绩
FROM (js909 inner JOIN jsc909 ON js909.s#=jsc909.s#) INNER JOIN jc909 ON jsc909.c#=jc909.c#
WHERE jc909.cname='人工智能'
四.完成以下操作,将相应结果截屏图保存,并写入实验报告中。
1. 在JS×××表中补充数据至约1000行,在JC×××表中补充数据至约100行,在JSC×××表中补充数据至约5000行,为三、1.中的(4)-(7)查询编写不同的SQL语句实现,并分析其运行效率。
解决方法在excel扩充数据的行数并导入,利用第五六七章学习的内容进行分析
(4) 查询每位学生的学号、学生姓名及其已选修课程的学分总数。
select js909.s# ,js909.sname ,SUM (jc909.credit) from jsc909,js909 ,jc909
WHERE js909.s# = jsc909.s# and jsc909.c# = jc909.c# GROUP by js909.s#
from jsc909,js909 ,jc909直接对关系做笛卡尔积访问的内存读写的次数为三个关系的记录数的成绩效率较低
改写后
select js909.s# ,js909.sname ,SUM (jc909.credit)
from (jsc909 JOIN js909 ON js909.s# = jsc909.s# )
JOIN jc909 ON jsc909.c# = jc909.c#
GROUP by js909.s#
改写后的语句先对两个较大的表JSC909和JS909做连接操作,再将生成的结果与JC909连接大大减少了读写内存的操作
(5) 查询选修课程“CS-02”的学生中成绩第二高的学生学号。
SELECT s# FROM jsc909 WHERE c#='CS-02'and grade = ( SELECT max(grade) FROM jsc909
WHERE c#='CS-02' AND grade < (SELECT MAX(grade) FROM jsc909 WHERE c#='CS-02'))
先对jsc909做查询找出其中的最大值,然后再对jsc909做查询找出成绩小于最大值的人中成绩最高的
改写后
SELECT s# FROM jsc909
WHERE c#='CS-02'
ORDER BY GRADE DESC
LIMIT 1,1
改写后只进行一次查询,消去了子查询只对修了CS-02的学生进行排序选择其中成绩第二高的大大提高了效率
(6) 查询平均成绩超过“王涛“同学的学生学号、姓名和平均成绩,并按学号进行降序排列。
SELECT sname ,js909.s# ,avg(jsc909.grade) FROM js909 ,jsc909 WHERE js909.s# = jsc909.s#
GROUP BY js909.s#
HAVING avg(jsc909.grade)>(SELECT avg(grade) FROM jsc909 where S#=(SELECT s# FROM js909 WHERE sname = '王涛'))
ORDER by js909.s# DESC
(7) 查询选修了3门以上课程(包括3门)的学生中平均成绩最高的同学学号及姓名。
SELECT jsc909.s# ,avg(grade),js909.sname
FROM jsc909,js909 GROUP BY
jsc909.s#, js909.sname ,js909.s# HAVING
count(*)>2 AND
avg(grade)>=
all
(
SELECT avg(grade) FROM jsc909,js909
GROUP BY jsc909.s#,js909.sname ,js909.s#
HAVING count(*)>2
AND jsc909.s#=js909.s#
)
AND jsc909.s#=js909.s#
更改后:
SELECT js909.s#, js909.sname, t_avg_max.avg_grade FROM (
SELECT jsc909. s# , (avg(jsc909.grade)) avg_grade FROM jsc909
GROUPBY jsc909.s#
HAVING count(jsc909. s#)>= 3 ORDER BY avg(jsc909.grade) DEsc LIMIT 1
) t_avg_max
JOIN js909
ON t_avg_max.s#= js909.s#;
2.在JS×××表中补充数据至约5000行,在JC×××表中补充数据至约1000行,在JSC×××表中补充数据至约20000行,重复四、1.中的SQL语句运行,分析其运行效率,并给出可提高查询效率的改进方法。
(4) 查询每位学生的学号、学生姓名及其已选修课程的学分总数。
select js909.s# ,js909.sname ,SUM (jc909.credit) from jsc909,js909 ,jc909
WHERE js909.s# = jsc909.s# and jsc909.c# = jc909.c# GROUP by js909.s#
from jsc909,js909 ,jc909直接对关系做笛卡尔积访问的内存读写的次数为三个关系的记录数的成绩效率较低
改写后
select js909.s# ,js909.sname ,SUM (jc909.credit)
from (jsc909 JOIN js909 ON js909.s# = jsc909.s# )
JOIN jc909 ON jsc909.c# = jc909.c#
GROUP by js909.s#
改写后的语句先对两个较大的表JSC909和JS909做连接操作,再将生成的结果与JC909连接大大减少了读写内存的操作
(5) 查询选修课程“CS-02”的学生中成绩第二高的学生学号。
SELECT s# FROM jsc909 WHERE c#='CS-02'and grade = ( SELECT max(grade) FROM jsc909
WHERE c#='CS-02' AND grade < (SELECT MAX(grade) FROM jsc909 WHERE c#='CS-02'))
先对jsc909做查询找出其中的最大值,然后再对jsc909做查询找出成绩小于最大值的人中成绩最高的
改写后
SELECT s# FROM jsc909
WHERE c#='CS-02'
ORDER BY GRADE DESC
LIMIT 1,1
改写后只进行一次查询,消去了子查询只对修了CS-02的学生进行排序选择其中成绩第二高的大大提高了效率
(6) 查询平均成绩超过“王涛“同学的学生学号、姓名和平均成绩,并按学号进行降序排列。
SELECT sname ,js909.s# ,avg(jsc909.grade) FROM js909 ,jsc909 WHERE js909.s# = jsc909.s#
GROUP BY js909.s#
HAVING avg(jsc909.grade)>(SELECT avg(grade) FROM jsc909 where S#=(SELECT s# FROM js909 WHERE sname = '王涛'))
ORDER by js909.s# DESC
SELECT sname ,js909.s# ,avg(jsc909.grade)
FROM js909 ,jsc909 WHERE js909.s# = jsc909.s#
GROUP BY js909.s#
HAVING avg(jsc909.grade)>(SELECT avg(grade) FROM jsc909 where S#=(SELECT s# FROM js909 WHERE sname = '王涛'))
ORDER by js909.s# DESC
(7) 查询选修了3门以上课程(包括3门)的学生中平均成绩最高的同学学号及姓名。
SELECT jsc909.s# ,avg(grade),js909.sname
FROM jsc909,js909 GROUP BY
jsc909.s#, js909.sname ,js909.s# HAVING
count(*)>2 AND
avg(grade)>=
all
(
SELECT avg(grade) FROM jsc909,js909
GROUP BY jsc909.s#,js909.sname ,js909.s#
HAVING count(*)>2
AND jsc909.s#=js909.s#
)
AND jsc909.s#=js909.s#
五.实验过程中对数据库进行备份及恢复,将相应结果截屏图保存,并写入实验报告中。
备份与恢复共有两种方法,分别为:
- 逻辑备份恢复
- 硬件备份恢复
考虑到实际情况本次实验采取逻辑备份恢复的方法
在进行这一步知其难备份:
备份成功:
恢复步骤:
- 连接到默认数据库postgres。
gsql -d postgres - 删除损坏的数据库A,并使用模板数据库重新创建一个干净的数据库。
DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE template0;
注意:
如果不删除旧数据库,则新导入的数据可能与原数据库中的数据相冲突。
3. 恢复到数据库A。
gsql -d mydb -p 26000 -f dump1.sql
备份完成后,数据库回到四、1的状态