# DataBase_Lab

**Repository Path**: mcpocket/DBSLab

## Basic Information

- **Project Name**: DataBase_Lab
- **Description**: 数据库大作业_pyQt5
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-12-04
- **Last Updated**: 2022-06-09

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 《数据库基础》实验报告

薛佳龙	PB19061212

## 前四次实验中一些语句的思路

### 第二次实验

1. 列出烹调书（类别名含有 cook）的种类和该类的平均价格。

   要计算每一类的平均价格，显然应该按照类别对书进行分组，所以要用`group by`语句。烹调书的类别名含有cook，所以应该使用字符串匹配作为条件，即 `like '%cook%'`。求出平均值使用`avg`即可。集函数和分组查询配合使用。

   `sql`语句为：

```sql
select type, avg(price) from pubs..titles where type like '%cook%' group by type
```

### 第三次实验

1. 按照下面的的结构与内容建两张表。表名分别以 T、S 开头，后面是建表人的学号(以下简记为 T**、S** )。要求：先用 **create table** 命令建立表 T** 的初始结构（初始结构只包括下面 T** 中前五个属性），然后再用 **alter table „add„** 添加一个属性: 出版时间，并插入相应的内容。

   建表和`alter`语句按照规定的语法写即可。建表操作和插入列的操作分开执行。

   ```sql
   CREATE TABLE TPB19061212(
   书名 varchar(100),
   作者 varchar(100),
   书号 varchar(10),
   价格 float,
   出版社 varchar(100)
   );
   ```

   ```sql
   ALTER TABLE TPB19061212 ADD 出版时间 int;
   ```

2.  用子查询方式建视图，视图名为 VT** , 并在视图上查找所需信息。

   建立价格小于20的书的书名,作者,价格,出版社视图。使用子查询的语法为：`create veiw ... as select ...`，会将子查询的结果建立试图。语句为：

   ```sql
   create view VTPB19061212 (书名,作者,价格,出版社) as select 书名,作者,价格,出版社 from TPB19061212 where 价格<20
   ```

3. 对表 T** 和 S** 进行其他操作，建其它索引与视图各一个。

   创建索引可以加快数据查询速度。创建视图可以使查询更加便捷，并提高安全性。视图虽然没有表的存储结构，但对于查询语句来说，视图可以几乎当作一个表来处理。

   对SPB19061212表建立一个书号索引，然后对页数大于300的书建立书号、页数、仓库号视图，并无条件查询这个视图。

   ```sql
   create index ISPB19061212 on SPB19061212(书号);
   create view VSPB19061212(书号,页数,仓库号) as select 书号,页数,仓库号 from SPB19061212 where 页数>300;
   select * from VSPB19061212;
   ```

### 第四次实验

1. 把书号以 D 开头的那些书的书价减掉 2.00 元。

   使用`where`先筛选出以D开头的书（使用字符串匹配作为条件），再使用`update ... set`更新数据。

```sql
update TPB19061212 set 价格=价格-2 where 书号 like 'D%';
```

2.  已知下面百分制成绩和 GPA 绩点、等级成绩之间的关系，建立名为PB** (即以 PB 开头，后面是建过程人的学号)的存储过程，用于实现对给定的百分制成绩，输出其 GPA 绩点和等级成绩。输入参数为百分制成绩，返回参数为绩点、等级成绩。如输出成绩不在 0~100 直接，存储过程返回-1，否则返回 0。

   存储过程可以类比为函数。存储过程的输入为：`@fz numeric(3,0),@jd numeric(3,1) output,@djcj char(2)`，先用一个判断语句判断输入的`fz`是否在0和100之间，如果不成立直接返回-1。如果存储过程没有返回，说明查询条件成立，使用`select`语句执行按条件查询即可。

   ```sql
   create procedure PB19061212 @fz numeric(3,0),@jd numeric(3,1) output,@djcj char(2) output
   as
   begin
    if @fz<0 or @fz>100 return -1
    select @jd=jd,@djcj=djcj from pubs..cjdzb where @fz>=startfz and @fz<=endfz
    return 0
   end
   ```

   执行存储过程，查询得分为100的绩点和成绩等级，语句为：

   ```sql
   declare @jd1 varchar(5),@djcj1 varchar(5)
   execute PB19061212 100, @jd1 output, @djcj1 output
   select @jd1,@djcj1
   ```

## 期末实验报告

### 实验目的

1. 通过设计一个数据库应用，熟悉数据库系统设计的基本流程。
2. 通过实验巩固`sql`语言的知识和使用方法。
3. 学习简单的图形界面设计，和桌面应用程序与数据库连接的方法。
4. 熟悉`sql`语言与主语言之间的通信

### 需求分析

1. 场景设计：作为中国科学技术大学机器人战队RoboWalker战队的队长，我需要时刻了解和统计战队物资的使用情况。战队的物资包括电机、调速器、遥控器、线材、控制板等。建立一个数据库，存储每一种物资的数量，已使用量，价格，以便于管理团队物资的收入和支出，并帮助战队合理规划预算。
2. 制作登录界面。
3. 数据库的初始化：
   - 连接现有的数据库。如果现有的数据库不存在，则在登录时创建一个新的数据库。
   - 查找现有的表是否存在。如果表不存在，则创建一个新表。
   - 设置数据库初始化按键，电机则删除并初始化所有数据。
4. 实现数据查询的功能：通过复选框按属性查询，或者直接输入sql语句进行查询。
5. 实现淘汰功能：如果某个物资损坏，可以使用淘汰功能。点击淘汰时更新数据。如果全部淘汰，则将该物资删除。
6. 购买功能：购买现有物资，更新物资总量数据。使用存储过程实现。
7. 使用和归还功能：更新已使用数量，判断是否全部用完。归还功能使用存储过程实现。
8. 上新功能：购买新的物资，在数据库中插入新的内容。
9. 直接输入sql语句对数据进行操作的窗口。

### 实验环境

1. Windows系统
2. MySQL
3. Python+PyMySQL+PyQt5
4. Qt+QtCreator

### 实现方法

#### 概述

##### MySQL与Python的通信区

​	主语言Python与SQL语言使用游标进行通信。Python的包PyMySQL定义了Python与MySQL的接口，也就是游标：

```python
conn = pymysql.connect(host=host, user=user, password=password, charset='gbk', autocommit=True)
cursor = conn.cursor()
```

​	主语言将字符串传递给游标之后，游标负责执行sql语句。如果执行的是查询语句，则将执行结果以二维元组的形式返回给python。

```python
sql = 'create database resourcesdb;'
cursor.execute(sql)
```

​	游标还可以执行存储过程，但前提是在数据库中已经存在指定的存储过程。执行存储过程的语句为：

```python
cursor.callproc(procname='buy', args=(buynum, buyname))
```

##### Python与Qt的通信区

​		实验使用Qt Creator工具设计图形界面。

​		Python的库PyQt5中提供了程序PyUIC，它可以把Qt Creator设计出来的`.ui`文件直接转化为PyQt5支持的Python图形界面代码。Qt使用信号和槽的方式对事件进行处理。

![image-20211213195845969](README.assets/image-20211213195845969.png)

​		以按键点击为例，每一次点击，按键发出`Clicked`信号。将这一按键与会话窗口`QDialog`的槽连接，会话窗口的槽接收到点击时间之后，会自动执行槽函数。

​		槽函数可以被定义在相应Qt类的子类中，使用子类实例化槽所在的对象，就可以在收到信号时触发槽函数。将子类定义在和PyUIC生成的文件的不同文件中，可以在更新图形界面时不被覆盖。

```python
class MyDialog(QtWidgets.QDialog):
    def __init__(self):
        super().__init__()
        

    def close_ui(self):
        self.close()
```

#### 登录界面

​		登陆界面使用Qt搭建，读取文本编辑框中的ip，用户名，口令内容，并使用`pymysql.connect`函数进行登录。使用`try ... except ...`语句来处理用户名密码错误的情况，即显示下方的登陆失败文本（默认不显示）。![image-20211213200905558](README.assets/image-20211213200905558.png)

​		如果发现不存在数据库，则使用

```sql
create database resourcesdb
```

​		语句创建一个名为`resourcesdb`的数据库。连接这个数据库。如果发现不存在表，则调用数据库初始化的槽函数。

#### 初始化数据库

​		初始化数据库的槽函数，在点击“初始化数据库”按键时被调用。这个槽函数的处理顺序为：

1. 如果已经存在名为resources的表，则删除这个表。

```python
if 'resources' in table_list:
    sql = 'drop table resources;'
    cursor.execute(sql)
```

2. 创建名为resources的表。

```sql
create table resources(
    name char(20) primary key,
    totalnum int,
    usednum int,
    price float,
    check(totalnum >= 0),
    check(usednum >= 0 and usednum <= totalnum)
);
```

3. 插入一条数据

```sql
insert into resources (name, totalnum, usednum, price) values('DT7', 9, 1, 629);
```

4. 创建物资购买的存储过程

```sql
create procedure buy(in num int, in id char(20))
begin
    if(num < 0) then set num = 0;
    end if;
    update resources set totalnum = totalnum + num where name = id;
end;
```

5. 创建物资归还的存储过程

```sql
create procedure giveback(in num int, in id char(20))
    begin
    if(num < 0) then set num = 0;
    end if;
    update resources set usednum = usednum - num where name = id;
    update resources set usednum = 0 where usednum < 0;
end;
```

#### 直接执行输入的sql语句

​		设置文本编辑框，在框中输入sql语句，点击按钮，则和触发函数，使用游标执行这条语句。

#### 物资上新

​		首先检查文本框是否为空。之后创建sql语句查询所有的物资，判断输入的物资是否已经存在，如果物资已经存在，则在输出文本框提示“物资已经存在，请使用“购买”功能添加”。

​		之后使用python的字符串加法操作构建sql语句，使用`try`尝试执行，如果执行失败，则说明输入格式不合法。构建字符串的方法为：

```python
sql = 'insert into resources (name, totalnum, usednum, price) values(\''
sql += mainwindow.buynewName.toPlainText() + '\', '
sql += mainwindow.buynewNum.toPlainText() + ', '
sql += '0, '
sql += mainwindow.buynewPrice.toPlainText() + ')'
```

#### 购买已有物资

​		执行存储过程即可。如果执行失败，则在输出文本框中打出格式错误。

#### 使用物资

​		根据使用物资的数量更新数据，即增加已使用属性的值。

​		如果物资不存在，则提示“物资不存在，无法取用”。

​		虽然在定义数据库中已经加了完整性约束，但依旧查询现在已经使用的数量和现有的数量，如果发现剩余数量不足，则提示无法使用。

#### 淘汰物资

​		如果某个物资损坏，可以使用淘汰功能。点击淘汰时更新数据，即在已有物资数量上往下减。如果全部淘汰，即已有物资数量更新之后为0，则将该物资删除。

#### 归还物资

​		尝试执行相应的存储过程，如果执行失败，则提示格式错误。

#### 生成exe文件

​		python是解释型语言，在运行时不会生成可执行文件。使用python库pyinstall给出的命令行指令，从python文件生成可执行文件。

### 程序使用方法

​		理论上，不需要安装python和pyqt5就可以执行程序（虽然我还没有放到其他机器上测试）。

​		但是，需要本机装有mysql，或者需要装有mysql的服务器，并可以从本机连接。

​		执行main.exe，在登陆界面输入ip(或域名)，用户名，口令即可。

​		程序没有在UI中隐藏密码，也没有处理可能存在sql注入的问题，所以实际应用的话还需要提高安全性。

### 实验总结

​		通过本次实验，学习了pyqt5，pymysql的使用方法，巩固了数据库、sql的知识，并熟悉了设计数据库系统的流程。