【数据库】数据库系统原理-课内实验报告

软件准备

• 华为云部署openGauss v2.1.0数据库（CentOS 7.6）
• 可视化工具Navicat15 连接华为云数据库 openGauss

实验要求

一、在openGauss中创建MYDB数据库，并在MYDB中创建学生、课程、选课三个表。

• 创建学生、课程、选课三张表（S、C、SC）

create table S
(
    sno VARCHAR(10) NOT NULL,
    sname VARCHAR(20) NOT NULL,
    sex CHAR(4) NOT NULL,
	bdate DATE NOT NULL,
	height DEC(3,2) DEFAULT 0.00,
	dorm VARCHAR(20),
	primary KEY(sno)
);

create table C
(
    cno VARCHAR(10) NOT NULL,
    cname VARCHAR(40) NOT NULL,
    period SMALLINT NOT NULL,
	credit TINYINT NOT NULL,
	teacher VARCHAR(20) NOT NULL,
	primary KEY(cno)
);

create table SC
(
    sno VARCHAR(10) NOT NULL,
    cno VARCHAR(10) NOT NULL,
    grade DEC(4,1) DEFAULT NULL,
    primary KEY(sno,cno),
	FOREIGN KEY (sno) REFERENCES S648(sno) ON DELETE CASCADE,
	FOREIGN KEY (cno) REFERENCES C648(cno) ON DELETE CASCADE,
	CHECK((grade IS NULL) or (grade BETWEEN 0 AND 100))
);

• 查看数据库中创建的基本表

\d

二、将以下数据加入相应的表中。

• 在 Excel 中按如下格式保存数据，要求第一行记录属性，第二行开始记录数据：
  
• 因为数据量比较小，选择用 Navicat 直接将 Excel 导入数据库：
  

三、完成以下操作，将相应SQL语句及执行结果截屏图保存，并写入实验报告中。

1、在基本表中完成以下查询（仅供参考）

（1）查询电子工程系（EE）所开课程的课程编号、课程名称及学分数。

select cno,cname,credit from c648 where cno='EE-%';

（2）查询未选修课程“CS-01”的女生学号及其已选各课程编号、成绩。

select sc648.sno,cno,grade from s648,sc648
where sc648.sno not in (select sno from sc648 where cno='CS-01')
and s648.sno=sc648.sno and sex='女'; 

（3）查询2001年～2002年出生学生的基本信息。

select * from s648
 where bdate>'2000-12-31 00:00:00' and bdate<2003-01-01 00:00:00;

（4）查询每位学生的学号、学生姓名及其已选修课程的学分总数。

select s648.sno,sname,SUM(credit) from s648,c648,sc648
 where s648.sno=sc648.sno and sc648.cno=c648.cno GROUP BY s648.sno;

（5）查询选修课程“CS-02”的学生中成绩第二高的学生学号。

select sno from sc648
 where cno='CS-02' and grade<(select MAX(grade) from sc648
 where cno='CS-02') order by grade desc limit 1,1;

（6）查询平均成绩超过“王涛”同学的学生学号、姓名和平均成绩，并按学号进行降序排列。

select s648.sno,sname,AVG(grade) from s648,sc648
 where s648.sno=sc648.sno
 group by s648.sno
 having AVG(grade)>(select AVG(grade) from s648,sc648 where s648.sno=sc648.sno and sname='王涛') 
 order by s648.sno desc;

（7）查询选修了计算机专业全部课程（课程编号为“CS-××”）的学生姓名及已获得的学分总数。

select sname,SUM(credit)		
from s648,c648
where cno like 'CS-%' and not exists(
	select * from c648
	where cno like 'CS-%' and not exists(	
		select * from sc648
		where sc648.sno=s648.sno
		and sc648.cno=c648.cno
		)
	)
	group by sname;

（8）查询选修了3门以上课程（包括3门）的学生中平均成绩最高的同学学号及姓名。

select sc648.sno,sname from s648,sc648
 group by s648.sno,sc648.sno,sname
 having COUNT(cno)>2 and AVG(grade)>=ALL(
 select AVG(grade) from s648,sc648
 group by sc648.sno,sname,s648.sno
	having COUNT(*)>2 and s648.sno=sc648.sno
 ) and s648.sno=sc648.sno;

2、修改记录

（1）分别在SXXX和CXXX表中加入记录(‘01032005’，‘刘竞’，‘男’，‘2003-12-10’，1.75，‘东14舍312’)及(‘CS-03’，“离散数学”，64，4，‘陈建明’)。

insert into s648 values
('01032005','刘竞','男','2003-12-10',1.75,'东14舍312')

查看基本表S，新增一行：

insert into c648 values
('CS-03','离散数学',64,4,'陈建明')

（2）将S表中已修学分数大于60的学生记录删除。

delete from s648 where sno in(select s648.sno from s648,c648,sc648
 where s648.sno=sc648.sno and sc648.cno=c648.cno
 group by s648.sno having SUM(credit)>60 )

（3）将“张明”老师负责的“信号与系统”课程的学时数调整为64，同时增加一个学分。

update c648 set period=64,credit=credit+1
 where cname=’信号与系统’ and teacher=’张明’

3、建立如下视图

（1）居住在“东18舍”的男生视图，包括学号、姓名、出生日期、身高等属性。

create view 居住在东18舍的男生 as
 select sno as 学号, sname as 姓名, bdate as 出生日期, height as 身高
from s648 where sex='男' and dorm like '东18舍%'

（2）“张明”老师所开设课程情况的视图，包括课程编号、课程名称、平均成绩等属性。

create view 张明老师所开设课程情况 as
 select c648.cno as 课程编号, cname as 课程名称, AVG(grade) as 平均成绩
 from c648,sc648 where teacher='张明' and c648.cno=sc648.cno
 group by c648.cno

（3）所有选修了“人工智能”课程的学生视图，包括学号、姓名、成绩等属性。

create view 选修人工智能的学生 as
 select s648.sno as 学号, sname as 姓名, grade as 成绩from s648,sc648,c648
 where cname='人工智能' and s648.sno=sc648.sno and sc648.cno=c648.cno

四、完成以下操作，将相应结果截屏图保存，并写入实验报告中。

1、初步扩充数据、并随机删除SC表成绩低于60分的200行选课记录。

在S×××表中补充数据至约1000行，在C×××表中补充数据至约100行，在SC×××表中补充数据至约20000行。
在向SC×××表中补充数据的过程中，随机选择成绩低于60分的200行选课记录删除。
以上过程不得在同一程序中串行完成。

• 首先实现本地excel文件的数据扩充，使用Python：

• 为了使生成数据更接近真实情况，选用faker库生成随机伪数据。
• 并且采用openpyxl库实现xlsx文件的读写。注意读写不能用xlutils库，因为它只能拷贝读取xls格式的文件，保存之后原来的xlsx将无法正常打开，而且xls格式最大行数为65535，不能满足题目要求，故采用openpyxl来操作xlsx。
• 此外，openpyxl的行列索引从1开始，所以相比于xlrd，要全部加上1。

• • 以学生表为例，扩充数据代码如下：

import faker,random
import openpyxl
from openpyxl import Workbook
"""
读取excel文件的步骤（在读取前要关闭文件）：
1.打开excel文件（路径+文件名）
2.获取要操作的sheet页
3.使用行或列去确定需要读取或写入的数据，写入数据后需要保存文件
4，关闭文件
"""
## 补充s表
fake = faker.Faker('zh_CN')
# 初始化faker,后面生成随机的名称和年龄需要用到
wb = openpyxl.load_workbook(r"C:\Users\10432\Desktop\data.xlsx")
# 根据sheet名称获取要操作的指定sheet页
wsheet = wb['Sheet1']
# 获取目前表的总行数
# nrows=wsheet.max_row # 注意，max_row只是最大索引数，会统计空行
# 故手动获取非空行数
nrows=0
for row in wsheet:
    if not all([cell.value == None for cell in row]):
        nrows=nrows+1
print(nrows)

sno=2000001 # 自定义生成连续学号初始值
for i in range(nrows+1,nrows+100):
    wsheet.cell(row=i,column=1).value=sno
    wsheet.cell(row=i,column=2).value=fake.name()
    wsheet.cell(row=i,column=3).value=random.choice(["男","女"])
    wsheet.cell(row=i,column=4).value=fake.date(pattern="%Y/%m/%d")
    wsheet.cell(row=i,column=5).value=round(random.uniform(1.5,2.0),2) # 控制浮点数精度为两位
    wsheet.cell(row=i,column=6).value="东"+str(random.randint(1,20))+"舍"+str(random.randint(101,701))
    sno=sno+1
    
wb.save(r"C:\Users\10432\Desktop\data.xlsx")  # 追加写入原文件

• • 同理，扩充课程表：

import faker,random
import openpyxl
from openpyxl import Workbook
"""
读取excel文件的步骤（在读取前要关闭文件）：
1.打开excel文件（路径+文件名）
2.获取要操作的sheet页
3.使用行或列去确定需要读取或写入的数据，写入数据后需要保存文件
4，关闭文件
"""
# 补充c表
fake = faker.Faker('zh_CN')
# 初始化faker,后面生成随机的名称和年龄需要用到
wb = openpyxl.load_workbook(r"C:\Users\10432\Desktop\data.xlsx")
# 根据sheet名称获取要操作的指定sheet页
wsheet = wb['Sheet2']
# 获取目前表的总行数
# nrows=wsheet.max_row # 注意，max_row只是最大索引数，会统计空行
# 故手动获取非空行数
nrows=0
for row in wsheet:
    if not all([cell.value == None for cell in row]):
        nrows=nrows+1
print(nrows)
csno=1
eeno=1
i=nrows+1
while i<(nrows+190): # 可能有重复cno，所以多生成一些数据
    flag=0
    name=random.choice(["CS-0","CS-","EE-0","EE-"])
    if (name=="CS-0" and csno<=9) or (name=="CS-" and csno>=10):
        wsheet.cell(row=i,column=1).value=name+str(csno)
        csno=csno+1
        flag=1
    elif (name=="EE-0" and eeno<=9) or (name=="EE-" and eeno>=10):
        wsheet.cell(row=i,column=1).value=name+str(eeno)
        eeno=eeno+1
        flag=1
    if(flag==1):
        wsheet.cell(row=i,column=2).value="课程"+str("".join(list(filter(str.isdigit, wsheet.cell(row=i,column=1).value))))
        wsheet.cell(row=i,column=3).value=random.randint(10,100)
        wsheet.cell(row=i,column=4).value=random.randint(1,6)
        wsheet.cell(row=i,column=5).value=fake.name()
        i=i+1
    
wb.save(r"C:\Users\10432\Desktop\data.xlsx")  #追加写入原文件

• • 扩充选课表：

import numpy as np
import faker,random
import openpyxl
from openpyxl import Workbook
"""
读取excel文件的步骤（在读取前要关闭文件）：
1.打开excel文件（路径+文件名）
2.获取要操作的sheet页
3.使用行或列去确定需要读取或写入的数据，写入数据后需要保存文件
4，关闭文件
"""
# 补充sc648表
fake = faker.Faker('zh_CN')
# 初始化faker,后面生成随机的名称和年龄需要用到
wb = openpyxl.load_workbook(r"C:\Users\10432\Desktop\data.xlsx")
# 根据sheet名称获取要操作的指定sheet页
wsheet = wb['Sheet3']
# 获取目前表的总行数
# nrows=wsheet.max_row # 注意，max_row只是最大索引数，会统计空行
# 故手动获取非空行数
nrows=0
for row in wsheet:
    if not all([cell.value == None for cell in row]):
        nrows=nrows+1
print(nrows)

sno=2000001
for i in range(nrows+1,nrows+20001):
    wsheet.cell(row=i,column=1).value=random.randint(sno,sno+999)
    wsheet.cell(row=i,column=2).value=random.choice(["CS-0"+str(random.randint(1,9)),"CS-"+str(random.randint(10,99)),"EE-0"+str(random.randint(1,9)),"EE-"+str(random.randint(10,99))])
    #wsheet.cell(row=i,column=3).value=np.random.choice(['',int(random.randint(0,100))],p=[0.1,0.9]) # 用空字符串表示null，p控制频率(使用np)
    wsheet.cell(row=i,column=3).value=random.choice(['',random.randint(0,100),random.randint(0,100),random.randint(0,100)]) # 用空字符串表示null，p控制频率
    
wb.save(r"C:\Users\10432\Desktop\data.xlsx")  #追加写入原文件

• 使用 python 连接 postgresSQL数据库，实现data写入（JDBC、Python数据库连接、ODBC简介）：
  我采用 Psycopg2 库实现，即 Python 连接 PostgresSQL 的数据库驱动。
• • 配置连接的过程、简单的select语句测试结果如下：

import psycopg2

def writeCoin(sno,sname,sex,bdate,height,dorm):
    conn = psycopg2.connect(database="mydb", user="u1",password="YourPwdHere123", host="120.46.45.130", port="5432")# 连接到指定数据库
    cursor = conn.cursor()# 建立游标
#     执行 Insert 命令
#     cursor.execute("INSERT ignore INTO hotapp_coin_type(rank,coin,market_value,global_index,all_amount,date_added) values(%s,%s,%s,%s,%s,%s)",(rank,coin,market_value,global_index,all_amount,date_added))
#     提交命令
#     conn.commit()
    cursor.execute("select * from sc648")# 执行查询
    rows = cursor.fetchall()# 获取SELECT返回的元组
    for row in rows:
        print('sno=',row[0], 'cno=',row[1],'grade=',row[2])
        #print('sno=',row[0], 'sname=',row[1],'sex=',row[2],'bdate=',row[3],'height=',row[4],'dorm=',row[5])

    cursor.close()# 关闭游标
    conn.close()# 关闭数据库连接

得到正确的select结果，说明配置正确，进行下一步操作：

• • 用多线程并发结构，实现批量数据的增删：

• 数据库的同一个连接和游标不能同时被多个线程所使用，所以为每个子线程都要新建连接和游标，并且及时释放。
• 此处遇到的一个问题是，当有违反唯一性约束（如生成重复主键）的数据要插入时，如果是mySQL可以选择“insert ignore”或者“on conflict(pkey) do nothing”来忽略这条数据；而openGauss是基于postgreSQL 9.2.4的，没有实现上述功能，但文档指明可以使用ON DUPLICATE KEY UPDATE NOTHING来替代。

import threading
import psycopg2
import openpyxl
import random,time
delcount=0 # 控制sc随机删除数据程度
"""
简单来说就是先用三张列表values把Excel数据保存；
然后用t1，t2，t3三个线程并发实现S、C、SC表的数据批量插入；
并且在t3中，随机选择执行SC表数据的插入还是删除
"""
def insert_data(tablename,*args):
    conn = psycopg2.connect(host="120.46.45.130",user="u1",password="@qazwsx123",port=5432,database="mydb")# 连接数据库
    cursor = conn.cursor()  # 获取游标
    sql = " "  # 定义默认SQL语句
    if tablename=="s648":
        sql = "insert into s648 values(%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE NOTHING"
    elif tablename=="c648":
        sql = "insert into c648 values(%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE NOTHING"
    elif tablename=="sc648": # 增加过程中，随机删除200行
        sql = "insert into sc648 values(%s,%s,%s) ON DUPLICATE KEY UPDATE NOTHING"
        cursor.executemany(sql, args) # 批量执行SQL语句
        for i in range (0,20):
            a=random.choices([0,1],weights=[1,9])
            if a==0 and delcount<10:# 随机删除200行
                sql = "delete top(200) from sc648 where grade is not null and grade<60;"
                cursor.execute(sql)
                delcount=delcount+1
                print("del done at time:",time.time())
        conn.commit()  # 提交保存数据
        cursor.close()# 关闭游标
        conn.close()# 关闭数据库连接
        return # sc648情况下，提前结束
    if sql !=" ":
        cursor.executemany(sql, args) # 批量执行SQL语句
        conn.commit()  # 提交保存数据
    cursor.close()# 关闭游标
    conn.close()# 关闭数据库连接

# 读取数据
wb = openpyxl.load_workbook(r"C:\Users\10432\Desktop\data.xlsx")
sh1 = wb['Sheet1']
sh2 = wb['Sheet2']
sh3 = wb['Sheet3']
value1=[]
value2=[]
value3=[]
for row in list(sh1.rows)[0:]:
    sno =  row[0].value
    sname = row[1].value
    sex = row[2].value
    bdate = str(row[3].value)
    height = row[4].value
    dorm = row[5].value
    value1.append((sno,sname,sex,bdate,height,dorm))
for row in list(sh2.rows)[0:]:
    cno =  row[0].value
    cname = row[1].value
    period = row[2].value
    credit = row[3].value
    teacher = row[4].value
    value2.append((cno,cname,period,credit,teacher))
for row in list(sh3.rows)[0:]:
    sno =  row[0].value
    cno = row[1].value
    grade = row[2].value
    value3.append((sno,cno,grade))
if __name__ == "__main__":
    start_time = time.time()
    print("start time:",start_time)
    t1 = threading.Thread(target=insert_data,args=("s648",*value1[1:])) # 第一行表头不能被读入args
    t2 = threading.Thread(target=insert_data, args=("c648", *value2[1:]))
    t3 = threading.Thread(target=insert_data, args=("sc648", *value3[1:]))
    t1.start() # 启动线程
    t2.start()
    t3.start()
    end_time = time.time()
    print("end time:",end_time)
    print("执行程序总耗费时间：",end_time - start_time)
    
wb.close() # 关闭工作簿

执行后数据库记录统计：

本地excel中总共生成了20027条数据，插入SC的过程中随机删除，最终保留17418条数据：

2、再次扩充数据，修改查询并分析效率

在S×××表中补充数据至约5000行，在C×××表中补充数据至约1000行，在SC×××表中补充数据至约200000行。
尝试为三、1.中的部分查询（不少于3个）编写不同的SQL语句实现，分析其运行效率。
如果可能，请尝试给出可提高查询效率的改进方法。

• 继续扩充数据，并送入数据库：

• 生成数据的方法与之前类似，区别是增大循环次数和 sno、cno 的取值范围。
• 使用 Python 的 executemany() 方法可以批量插入多值数据，但是5000行大概花费几十秒，20万行则非常漫长。而且在这种方法下，只有插入指令全部执行后，才能在程序后台看到执行成功还是报错，看不到数据实际存入的动态过程，不利于分析方法的性能。也考虑可以使用 COPY 来提升并行数据导入的效率。
• 因此，SC表建议使用 Navicat 自带的方法导入，过程也很漫长，但能看到执行进度。

• 进行查询、分析效率或改进：
  以 3.1.4 为例：

④查询每位学生的学号、学生姓名及其已选修课程的学分总数。
原始语句：

select s648.sno,sname,SUM(credit) from s648,c648,sc648
 where s648.sno=sc648.sno and sc648.cno=c648.cno GROUP BY s648.sno;

采用连接，改写语句：
先将两个大表S、SC连接，再与小表C连接，减少读写开销。

select s648.sno,sname,SUM(credit) 
from (sc648 join s648 on s648.sno=sc648.sno) join c648 on sc648.cno=c648.cno
group by s648.sno;

五、备份与恢复

• 关于gsdb 逻辑备份与物理备份

• 为了避免绝对路径带来的表空间冲突问题，选择采用逻辑备份而非物理备份。
• 使用gs_dump命令自定义导出一个数据库或是其中的对象（模式、表、视图等）。

1、备份

gs_dump mydb -f dump1.sql -p 5432

备份文件默认存储在\home\feng\dump1.sql，将.sql文件下载导出即可。

2、恢复

• 首先删除旧的数据库，避免产生数据冲突：

drop database mydb;

• 上传并导入别人的数据库，进行恢复：
  这一步执行可能会报用户错，但没有实际影响。

gsql -d mydb -p 5432 -f dum2.sql

• 可以正常打开新的数据库：