数据库上机报告

最新推荐文章于 2023-11-21 22:45:51 发布
最新推荐文章于 2023-11-21 22:45:51 发布
阅读量2.6k 收藏 48
点赞数 9
分类专栏： 数据库 文章标签： 数据库 sql database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42437114/article/details/116088646
版权

目录

实验一

创建数据库

CREATE DATABASE student; // 创建 student 数据库
use student; // 选择要操作的 Mysql 数据库
  • 成功创建 student 数据库
    在这里插入图片描述

建表

  • 建立教学数据库的三个基本表（注意实现参照完整性，学号课程号为主键，添加参照完整性约束：成绩在 0-100 之间）:
S(Sno,Sname,Ssex,Sage,Sdept) 学生(学号,姓名,性别,年龄,系)
SC(Sno,Cno,Grade) 选课(学号,课程号,成绩)
C(Cno,Cname, Ccredit) 课程(课程号,课程名,学分)

CREATE TABLE if not exists S(
	Sno  CHAR(9)  PRIMARY KEY,
	Sname  CHAR(20) NOT NULL,
	Ssex  CHAR(2),
	Sage   SMALLINT,
	Sdept  CHAR(20) 
)  CHARSET=utf8; // 这里必须要设置字符集，否则无法插入中文

在这里插入图片描述

CREATE TABLE if not exists C(
	Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40),
	Ccredit SMALLINT
) CHARSET=utf8;

在这里插入图片描述

CREATE TABLE if not exists SC(
	Sno CHAR(9) NOT NULL,
	Cno CHAR(4) NOT NULL,
	Grade SMALLINT CHECK(Grade >= 0 and Grade <= 100),
	PRIMARY KEY (Sno, Cno),
	FOREIGN KEY (Sno) REFERENCES S(Sno)
		ON DELETE CASCADE
		ON UPDATE CASCADE, 
	FOREIGN KEY (Cno) REFERENCES C(Cno)
		ON DELETE CASCADE 
		ON UPDATE CASCADE
) CHARSET=utf8;

在这里插入图片描述

插入数据

  • S表数据如下：
INSERT INTO S VALUES 
('201215121', '李莉', '男', 20, 'CS'),
('201215122', '刘星', '女', 19, 'CS'),
('201215123', '王浩然', '女', 18, 'MA'),
('201215125', '张立', '男', 19, 'IS'),
('201215124', '王强', '男', 22, 'CS'),
('201215120', '李爽', '男', 20, 'CS');

在这里插入图片描述

  • C表数据如下：
INSERT INTO C VALUES 
('1', '数据库', 4),
('2', '数学', 2),
('3', '信息系统', 4),
('4', '操作系统', 3),
('5', '数据结构', 4),
('6', '数据处理', 2),
('7', 'C语言', 4);

在这里插入图片描述

  • SC表：
INSERT INTO SC VALUES 
('201215122', '3', 80),
('201215122', '2', 90),
('201215121', '3', 88),
('201215121', '1', 91),
('201215121', '2', 85),
('201215121', '5', 94),
('201215123', '3', 88),
('201215123', '1', 92),
('201215123', '7', 100),
('201215125', '1', 92),
('201215125', '6', 77),
('201215125', '2', 79),
('201215125', '4', 72),
('201215125', '3', 99),
('201215125', '5', 58),
('201215125', '7', 81),
('201215124', '1', 55),
('201215124', '3', 81),
('201215124', '6', 89),
('201215124', '2', 75),
('201215124', '4', 81),
('201215123', '4', 89),
('201215120', '1', 88);

在这里插入图片描述

查询

查询选修 2 号课程的学生学号与该课程成绩

select Sno, Grade
from SC
where Cno = '2';

在这里插入图片描述

查询选修课程名为数据结构的学生学号与年龄

select S.Sno, Sage
from S, SC, C
where S.Sno = SC.Sno and C.cno = SC.cno
	and C.Cname = '数据结构';

在这里插入图片描述

查询不选 3 号课程的学生学号与姓名

select S.Sno, S.Sname
from S
where S.Sno not in(
	select distinct Sno
	from SC
	where Cno = '3'
);

在这里插入图片描述

查询学习全部课程学生姓名

select S.Sname
from S
where not exists(
	select *
	from C
	where not exists(
		select *
		from SC
		where C.Cno = SC.Cno
			and SC.Sno = S.Sno
	)
);

在这里插入图片描述

查询所有学生除了选修 1 号课程外所有成绩均及格的学生的学号和平均成绩,其结果按平均成绩的降序排列

select SC1.Sno, avg(SC1.Grade) as AvgGrade
from SC SC1
where SC1.Sno not in(
		select distinct SC2.Sno
		from SC SC2
		where Grade < 60 and Cno != '1'
	)
group by SC1.Sno
order by avg(SC1.Grade) desc;

在这里插入图片描述

查询选修数据库成绩第 2 名的学生姓名

  • 由于我使用的 MySQL 5.7 没有 rank 函数，因此用变量自行实现排序效果; 这里的实现是分数相同排名相同，因此如果有两个并列第一的话就没有第二名，搜索结果为空集
// if(A,B,C) 表示，如果 A 条件成立，那么执行 B，否则执行 C
select Sname, Grade
from(
	select ref.Sname, ref.Grade,
	@curRank := if(@prevGrade = ref.Grade, @curRank, @incRank) as rank, 
	@incRank := @incRank + 1, 
	@prevGrade := ref.Grade
	from
	(select @curRank := 0, @prevGrade:= NULL, @incRank := 1) r,
	(select Sname, Grade
	from S, SC, C
	where S.Sno = SC.Sno and C.Cno = SC.Cno
		and C.Cname = '数据库'
	order by Grade desc) ref
) r
where rank = '2';

解析：

  • (1) 先选出所有选择数据库学生的姓名及其成绩，然后对其按成绩降序排列作为子表 ref
select Sname, Grade
from S, SC, C
where S.Sno = SC.Sno and C.Cno = SC.Cno
	and C.Cname = '数据库'
order by Grade desc
  • (2) 设置变量初值：curRank 为真实排名、prevGrade 记录上一个元素的成绩、incRank 记录由 1 开始的学生索引值
select @curRank := 0, @prevGrade:= NULL, @incRank := 1
  • (3) 之后每遍历子表 ref 中的一个元组，就计算 curRank 的值作为最终的排名，具体思路是，如果当前元组的成绩与之前元组的成绩相同，则说明他们排名并列，此时 curRank 不变，否则 curRank 设置为当前元组的索引值 incRank 作为其排名；之后索引值 incRank 递增，prevGrade 记录当前元组的成绩
@curRank := if(@prevGrade = ref.Grade, @curRank, @incRank) as rank, 
@incRank := @incRank + 1, 
@prevGrade := ref.Grade
  • 查询结果：
    在这里插入图片描述
  • 可以看到，查询结果为空。这是因为如下图所示，有两个并列第一，因此不存在第二名
    在这里插入图片描述

查询所有 3 个学分课程中有 3 门以上(含 3 门)课程获 80 分以上(含 80 分)的学生的姓名

给的数据里 3 学分的课只有 1 门，查出来的结果为空集，因此下面给出的查询是学分大于等于 3 学分的课

select Sname
from S, SC, C
where S.Sno = SC.Sno and C.Cno = SC.Cno 
	and C.Ccredit >= 3 and SC.Grade >= 80
group by SC.Sno having(Count(*) >= 3);

在这里插入图片描述

查询选课门数唯一的学生的学号

select Sno
from SC
group by SC.Sno having(Count(*) = 1);

在这里插入图片描述

数据修改、删除与视图

把 1 号课程的非空成绩提高 5%

update SC
set Grade = 1.05 * Grade
where Cno = '1' and Grade is not Null;
  • 修改前：
    在这里插入图片描述
  • 修改后：
    在这里插入图片描述

在 SC 表中删除课程名为数据结构的成绩的元组

delete
from SC
where Cno = (
	select Cno
	from C
	where Cname = '数据结构'
);
  • 修改前：
    在这里插入图片描述
  • 修改后：
    在这里插入图片描述

在 S 和 SC 表中删除学号为 201215122 的所有数据

  • 由于建 SC 表的时候外键 Sno 设置的是 ON DELETE CASCADE，因此只需要删除 S 表的学号为 201215122 的所有数据，SC 表的相应数据会自动删除
delete
from S
where S.Sno = '201215122';
  • 修改前：
    在这里插入图片描述
  • 修改后：
    在这里插入图片描述

建立男学生的视图,属性包括学号、姓名、选修课程名和成绩

create view male (Sno, Sname, Cname, Grade)
as (
	select SC.Sno, Sname, Cname, Grade
	from SC, C, S
	where Ssex='男' and SC.Sno=S.Sno and SC.Cno=C.Cno
)
with check option;

在这里插入图片描述

在男学生视图中查询平均成绩大于 80 分的学生学号与姓名

select Sno, Sname
from male
group by Sno
having avg(Grade)>80;

在这里插入图片描述

计算每个学生有成绩的课程门数、平均成绩

select Sname, count(*) as Cnum, avg(Grade) as AvgGrade
from SC, S
where Grade is not Null and SC.Sno = S.Sno
group by S.Sno;

在这里插入图片描述

使用 GRANT 语句,把对基本表 S、SC、C 的使用权限授给其它用户

# MySQL 不允许使用 to public
# 下面代码表示允许任何用户使用密码123，
# 通过本地连接使用基本表 S, SC, C 的使用权限
grant all
on table S
to ''@'localhost' IDENTIFIED BY '123';

grant all
on table SC
to ''@'localhost' IDENTIFIED BY '123';

grant all
on table C
to ''@'localhost' IDENTIFIED BY '123';

触发器

首先解除S表和SC表的参照关系。然后在Student表上创建一个触发器，使更新一个学生的学号信息时能够级联的更新此学生在SC表中的选课记录的学号信息，并进行验证

  • 首先查看 Sno 上外键约束的命名，然后进行删除
    在这里插入图片描述
ALTER TABLE SC 
DROP foreign key sc_ibfk_1;

在这里插入图片描述

create trigger update_sno
after update on S
for each row
begin
	if (new.Sno != old.Sno) then
		update SC
		set SC.Sno = new.Sno
		where SC.Sno = old.Sno;
	end if;
end;
  • 更改前的信息：
    在这里插入图片描述在这里插入图片描述
update S
set Sno = '201787001'
where Sno = '201215124';
  • 更改后的信息；可以看到王强的学号被我改成了 201787001，而且 SC 表中的 Sno 信息也变动了
    在这里插入图片描述在这里插入图片描述

首先解除S表和SC表的参照关系。然后在Student表上创建一个触发器，使删除一个学生时能够级联的删除此学生在SC表中的选课记录，并进行验证

create trigger delete_sno
after delete on S
for each row
begin
	delete 
	from SC
	where SC.Sno = old.Sno;
end;

delete
from S 
where S.Sno = '201787001';
  • 删除刚刚修改的 201787001 学号的有关信息，可以看到，SC 表中 201787001 学号的有关信息也被删除了：
    在这里插入图片描述
    在这里插入图片描述

在 S 表中编写 insert 的触发器，假如每个班的学生不能超过 30 个，如果低于此数，添加可以完成；如果超过此数，则插入将不能实现

create trigger limit_num
before insert on S
for each row
begin
	if (select count(Sno) from S) >= 30 then
		set new.Sno = '0000000000000000';
	end if;
end;
  • 说明：MySQL 在触发器中不允许使用 rollback，因此我采用如下思路：利用 before 触发器，如果插入前表内学生数大于等于 30，则将插入的主键更改为一个不合法的数值，使插入无法完成
  • 下面为了方便演示，我把限制数目调整为 5，此时 S 表中有 5 名学生
    在这里插入图片描述
  • 多插入一行后，插入数量超过 5，主键被修改为非法数值，因此插入失败
    在这里插入图片描述

在 SC 表上编写 update 触发器，当修改 SC 表中的 Grade 字段时将其修改前后的信息保存在 SC_log 表中

// 建 SC_log 表
CREATE TABLE if not exists SC_log(
	OldGrade SMALLINT CHECK(OldGrade >= 0 and OldGrade <= 100),
	NewGrade SMALLINT CHECK(NewGrade >= 0 and NewGrade <= 100)
)  CHARSET=utf8;

在这里插入图片描述

// 建立触发器
create trigger log
after update on SC
for each row
begin
	if old.Grade != new.Grade then
		insert into SC_log
			values(old.Grade, new.Grade);
	end if;
end;
  • 修改前的 SC 表：
    在这里插入图片描述
// 更改成绩
update sc
set Grade=2
where Sno='201215120' and Cno='1';
  • 更改成绩之后，修改前后信息被成功记录
    在这里插入图片描述在这里插入图片描述

实验完成后,撤消建立的基本表和视图

drop table SC cascade;
drop table S cascade;
drop table C cascade;
drop table SC_log cascade;
drop view male cascade;

在这里插入图片描述

实验二

  • 基于 MySQL，设计并实现一个简单的旅行预订系统。该系统涉及的信息有航班、大巴班车、宾馆房间和客户数据等信息
  • 全部代码见 Github

基本功能

  • 为该旅行预订系统设置两种用户：
    • (1) 预订系统管理者 admin。管理者可以直接对航班、大巴、宾馆、客户信息进行增删改查，还可以查询预约信息 (不允许 admin 直接在后台更改客户的预约信息，如果航班取消或航班号修改，数据库会自动利用触发器对相应的预约信息进行删改)。同时也支持 admin 直接输入 SQL 语句来操纵数据库
    • (2) 普通客户。支持客户的登录与注册。普通客户可以对航班/大巴/宾馆进行预约/取消预约、查询预约订单情况、查询旅行线路、检查预定线路合理性

ER 图设计及说明

  • 如下图所示，客户与航班、大巴车、宾馆之间均为多对多关系。例如，一个客户可以预约多个航班，一个航班也可以被多个用户所预约
    在这里插入图片描述

请忽视上图中的箭头…

数据库逻辑结构设计

  • FLIGHTS 表用来记录航班信息。这里假设在一个航班上，所有座位的价格都一样，因此 FLIGHTS 表中，航班号 flightNum 为主键。用户定义完整性主要是确保各个属性的取值有实际意义，具体定义如下：
create table FLIGHTS(
    flightNum   char(5) primary key,
    price       int check(price > 0),
    numSeats    int check(numSeats > 0),
    numAvail    int check(numAvail >= 0),
    FromCity    varchar(50) not null,
    ArivCity    varchar(50) not null
) CHARSET=utf8;
  • HOTELS 表用来记录宾馆信息。这里假设在一个宾馆内，所有房间的价格都一样，因此 HOTELS 表中，宾馆号 hotelNum 为主键。用户定义完整性主要是确保各个属性的取值有实际意义，具体定义如下：
create table HOTELS(
    hotelNum    char(5) primary key,
    location    varchar(50) not null,
    price       int check(price > 0),
    numRooms    int check(numRooms > 0),
    numAvail    int check(numAvail >= 0)
) CHARSET=utf8;
  • BUS 表用来记录大巴班车信息。这里假设同一个大巴车上所有座位价格一样，因此 BUS 表中，车号 BusNum 为主键。用户定义完整性主要是确保各个属性的取值有实际意义，具体定义如下：
create table BUS(
    BusNum      char(5) primary key,
    location    varchar(50) not null,
    price       int check(price > 0),
    numSeats    int check(numSeats > 0),
    numAvail    int check(numAvail >= 0)
) CHARSET=utf8;
  • CUSTOMERS 表用来记录客户信息，客户 ID custID 为主键。同时用户定义完整性规定 custName 不能为空，否则就没有了实际意义：
create table CUSTOMERS(
    custID      char(5) primary key,
    custName    varchar(50) not null
) CHARSET=utf8;
  • RESERVATIONS 表用来记录客户的预约信息。规定预约号 resvNum 为主键 ((resvType, resvKey) 为候选码)。custID 为外码，参照 CUSTOMERS 表的 custID 属性；用户定义完整性规定 resvType 只能取值 1 / 2 / 3，分别表示预约类型为航班/大巴车/宾馆；规定 resvKey 对应 FLIGHTS / HOTELS / BUS 表的一个主键，用来表示预约的航班号/车号/宾馆号：
create table RESERVATIONS(
    resvNum     char(5) primary key,
    custID      char(5) not null,
    resvType    int check(resvType in (1, 2, 3)),
    resvKey     char(5) check(
        resvKey in (
            select flightNum from FLIGHTS
            union select hotelNum from HOTELS
            union select BusNum from BUS
        )
    ),
    foreign key (custID) references CUSTOMERS(custID)
) CHARSET=utf8;

数据库物理设计

  • MySQL 在表的主键上都建立了索引。除此之外，因为在查询预约对应的航班号/车号/宾馆号时经常要查询 (resvType, resvKey) 这一候补码，因此在 resvType 和 resvKey 属性上也建立索引：
create unique index resv_idx
on RESERVATIONS(resvType, resvKey);

详细设计与实现

触发器的定义与实现

航班、大巴车、宾馆的实现都类似，因此下面主要以航班 FLIGHTS 表为例进行讲解

主要使用了以下触发器保持数据库的一致性：

  • (1) 在 RESERVATIONS 表上设置插入触发器，如果插入一条航班预约信息 (new.resvType = 1) 且该航班号存在 (航班号在 FLIGHTS 表中)，则查看该航班的剩余座位数，如果该航班还有剩余座位，则到航班表中将该航班的剩余座位数减一，否则禁止此次预约 (回滚)
create trigger make_reservation 
before insert on RESERVATIONS
for each row
begin
    if new.resvType = 1 then
        if (select numAvail from FLIGHTS where flightNum = new.resvKey) > 0
             and exists(select * from FLIGHTS where flightNum = new.resvKey) 
        then
            update	FLIGHTS
            set		numAvail = numAvail - 1
            where	flightNum = new.resvKey;
        else
       	 	# 预约数已满，不允许预约
       	 	# 由于 MySQL 触发器中不允许使用 rollback，
       	 	# 因此通过将主键设为非法值，数据库插入报错来阻止插入
            set new.resvNum = '??????????????'; 
        end if;
    end if;
end;
  • (2) 在 RESERVATIONS 表上设置删除触发器，如果取消航班预约 (old.resvType = 1) 且 FLIGHTS 表中存在该航班信息，则将 FLIGHTS 表中对应航班的可用座位数加一
    • 这里一定要先检查 FLIGHTS 表中存在该航班信息是因为取消航班预约也可能是由对应航班取消导致的 (这是后面要讲的一个触发器的功能)，因此如果不检查的话可能会导致触发器循环调用 (删除 FLIGHTS 表中的航班信息导致删除对应航班的预约信息，进而触发 RESERVATIONS 表上的删除触发器，该触发器又企图对 FLIGHTS 表进行更新减少对应航班的可用座位数 (尽管这个更新实际不会发生，但 MySQL 依然会报错))，这在 MySQL 中是不允许的
create trigger cancel_reservation
after delete on RESERVATIONS
for each row
begin
    if old.resvType = 1 
        and exists(select * from FLIGHTS where flightNum = old.resvKey) then
            update	FLIGHTS
            set		numAvail = numAvail + 1
            where	flightNum = old.resvKey;
    end if;
end;
  • (3) 在 FLIGHTS 表上设置删除触发器，如果有航班被取消，则删除该航班对应的预约信息
create trigger delete_reservation
after delete on FLIGHTS
for each row
begin
    delete from RESERVATIONS
    where old.flightNum = resvKey and resvType = 1;
end;
  • (4) 在 FLIGHTS 表上设置更新触发器，如果有航班号被更改，则同步更新该航班对应的预约信息
create trigger update_reservation
after update on FLIGHTS
for each row
begin
    if new.flightNum != old.flightNum then
        update RESERVATIONS
        set resvKey = new.flightNum
        where old.flightNum = resvKey and resvType = 1;
    end if;
end;

数据库事务的定义与实现

三个主要的类

Database 类

  • 首先定义 Database 类，它主要通过 pymysql 库实现数据库连接、执行 sql 语句、返回 sql 语句的查询结果、用 MySQL 风格打印查询结果等功能

Menu 类

  • 主要提供各级菜单的显示以及用户功能选择的获取

BookingSys 类

  • 继承自 Database 类，旅行预订系统的各项功能主要都是由它提供的，下面主要讲述该类中方法的实现
旅行预订系统管理者

下面不会贴太多代码。代码的基本思路都是让用户输入必要的信息，然后组成 SQL 语句，通过 pymysql 库提交数据库执行

(1) 直接输入 SQL 语句操纵数据库

  • 这个实现比较简单，就是通过 pymysql 库执行用户输入的 SQL 语句，如果发生错误则进行回滚

(2) 对航班、大巴、宾馆、客户信息进行增删改

航班、大巴、宾馆、客户信息的增删改查都比较类似，下面以航班为例进行说明

  • 注意：这里不允许 admin 直接在后台更改客户的预约信息，如果航班取消或航班号修改，数据库会自动利用触发器对相应的预约信息进行删改
  • 增加航班：要求用户输入 flightNum, price, numSeats, FromCity, ArivCity 这五个属性的值，然后构建出 insert 语句，利用 Database 类提供的 SQL 语句执行功能进行插入
  • 删除航班：要求用户输入要删除的航班号 flightNum，然后构建出 delete 语句，利用 Database 类提供的 SQL 语句执行功能进行删除
  • 修改航班信息：要求用户输入要修改的航班号 flightNum，然后输入要修改的属性名和新值，最后构建出 update 语句，利用 Database 类提供的 SQL 语句执行功能进行更新

(3) 对航班、大巴、宾馆、客户、预约信息进行查询

  • 查询航班信息时 (大巴、宾馆、客户信息查询同理)，直接利用 Database 类提供的 SQL 语句执行功能执行 select * from FLIGHTS 语句，然后利用 Database 类提供的查询结果打印功能将其规整地打印出来
  • 查询预约信息时，还要求客户选择查询航班/大巴/宾馆中哪一个的预约情况。以航班为例，将 RESERVATIONS, CUSTOMERS, FLIGHTS 表进行自然连接并筛选出航班的预约信息 (resvType = 1)，最后输出预约号、客户 id、预约的航班号、航班价格、航班起点及终点。也就是执行以下 SQL 语句：
select resvNum, CUSTOMERS.custID, flightNum, price, FromCity, ArivCity
from RESERVATIONS, CUSTOMERS, FLIGHTS
where resvKey = flightNum and resvType = 1
	and RESERVATIONS.custID = CUSTOMERS.custID;
普通客户

以下说明均以航班为例，大巴/宾馆的实现过程同理

(1) 客户登录与注册

  • 在用户输入其 id 后，首先构建 select custID from CUSTOMERS 的 SQL 查询语句，然后通过 Database 类提供的查询功能进行查询，返回查询结果
  • 如果用户输入的 id 在查询结果中，说明该客户存在，打印出登录成功的信息，之后再构建 select custName from CUSTOMERS where custid = %s 的 SQL 语句进行查询 (%s 这里为输入的客户 id)，打印出该客户 id 对应的名字
  • 如果用户输入的 id 不在查询结果中，则要求客户继续提供新用户名 name，最后构建 insert into CUSTOMERS values('%s', '%s') 插入 SQL 语句向客户表中插入新的客户信息 (id 和 name)，并打印出注册成功的提示信息

(2) 预约

  • 客户选择预约模式后，还要再选择预约航班/大巴车/宾馆；下面以预约航班为例进行说明：首先要求客户输入航班起点 FromCity 与到达城市 ArivCity，然后构建查询 SQL 语句：select * from FLIGHTS where FromCity = '%s' and ArivCity = '%s' and numAvail > 0 来选出所有符合起点与终点条件以及机上还有剩余座位的航班信息并将其打印出来
  • 接着查询 RESERVATIONS 表中所有预约信息的 resvNum 并随机生成一个不同于预约表中已有预约号的新的预约号
  • 接着要求客户继续输入想要预约的航班号，在进行确认后在 RESERVATIONS 表中插入一条预约信息，其中 resvType = 1 表示预约的是航班，resvKey 为 flightNum，custID 为进行预约的客户 id，resvNum 为刚才随机生成的新预约号

(3) 取消预约

  • 同样以取消预约航班为例，首先如下面的代码所示 (id 为当前取消预约的客户 id，verbose 设为 True 表示打印预约信息，output_resvNum 设为 True 表示除了打印航班的起点终点，还打印航班的预约号)，将 CUSTOMERS, FLIGHTS, RESERVATIONS 三个表做自然连接，然后选出指定客户的预约信息 (预约号、航班号、起点、终点)，并将选出的预约信息打印出来以供客户选择要取消哪个预约
def query_flights(self, id, verbose=False, output_resvNum=False):
	"""查询指定客户的航班信息"""
	key = "RESERVATIONS.resvNum, " if output_resvNum else ""
	sql = """select %s FLIGHTS.flightNum, FromCity, ArivCity
			from CUSTOMERS, FLIGHTS, RESERVATIONS
			where CUSTOMERS.custID = RESERVATIONS.custID
			and FLIGHTS.flightNum = RESERVATIONS.resvKey
			and resvType = %d
			and CUSTOMERS.custID = '%s'""" %(key, self.RESERVE_FLIGHT, id)
	flights = self.query(sql)

	if verbose:
		if len(flights) == 0:
			print("You haven't reserved any flight yet.")
		else :
			print("Reserved Flight:")
			self.print_query_results(flights)

	return flights
  • 在打印了航班预约信息之后，要求客户输入要取消的预约号 resvNum。此时将 RESERVATIONS 和 FLIGHTS 表做自然连接来查询客户输入的 resvNum 是否为航班预约，如果是，则构建 delete from RESERVATIONS where resvNum = '%s' SQL 语句对该条预约记录进行删除

(4) 查询预约情况

  • 下面同样以查询航班信息为例进行说明：这里查询预约情况调用的就是上面的 query_flights 方法来打印用户的航班预约信息 (不同的是将 output_resvNum 设为 False 来避免打印出预约号)

(5) 查询旅行线路

  • 这里默认只有航班才能跨城市移动，大巴车只能在本地移动，因此查询旅行线路同样是调用 query_flights 方法查询客户已经预约的航班信息，最终打印出每一个航班的起点和终点

(6) 检查线路合理性

  • 主要是检查客户预约的大巴车和宾馆是否在航班途经的城市上，如果不在，则线路不合理。因此先调用 query_flights 方法查询客户已经预约的航班信息，然后将所有途经城市都存到一个集合 cities 中，之后分别调用 query_buses 和 query_hotels 方法查询客户预约的大巴车和宾馆记录，如果其中有大巴车或宾馆的地点不在 cities 中，则线路不合理

数据库测试

普通客户

登录

  • 登录界面: 要求输入用户 id
    在这里插入图片描述
  • 这里输入一个新客户 id 00001，系统提示需要先注册，要求输入客户名
    在这里插入图片描述
  • 这里输入客户名为 lianlio，系统提示注册成功，同时弹出客户服务菜单，从 0 ~ 5 依次表示退出登录、预约、查询预约、取消预约、查询旅行线路、检查旅行线路合理性
    在这里插入图片描述
  • 此时如果我退出系统再次输入 id 进行登录，系统就会提示登陆成功，并显示欢迎信息：
    在这里插入图片描述

预约

  • 输入 1 来选择预约服务，这里系统继续询问要预约航班、大巴还是宾馆
    在这里插入图片描述
  • 这里以预约航班为例，输入 1 以预约航班；系统继续询问要预约从哪里到哪里的航班
    在这里插入图片描述
  • 假设预约大连到无锡的航班，系统经过查询后输出了所有大连到无锡的航班信息，并进一步询问要预约的航班号
    在这里插入图片描述
  • 这里选择 17593 号航班，在确认预约后，系统提示预约成功
    在这里插入图片描述

预约查询

  • 继续选择客户服务 2 (预约查询)，并选择查询预约的航班信息。如下图所示，系统打印出了刚才预约的信息 (00001 号客户预约了 17593 号航班，从大连飞往无锡)
    在这里插入图片描述

取消预约

  • 选择客户服务 3 (取消预约)，并选择取消航班预约。此时系统自动打印出了当前客户的航班预约情况 (这里我事先又增加了几个航班预约)，并询问客户要取消哪个航班的预约
    在这里插入图片描述
  • 选择取消 19213 号从深圳飞往上海的航班，对应的预约号为 90670。之后系统会询问是否删除从深圳到上海的航班预约，确认后系统显示删除成功的提示
    在这里插入图片描述

查询旅行线路

  • 在选择查询旅行线路后，系统打印出旅行线路
    在这里插入图片描述

检查预定线路合理性

  • 如下图所示，我目前预约的宾馆均在航班途经城市上
    在这里插入图片描述
  • 此时选择"检查预定线路合理性"服务，系统显示旅行计划合理
    在这里插入图片描述
  • 下面我再多预定一个深圳的宾馆，这个宾馆不在我的旅行路线上
    在这里插入图片描述
  • 此时再次选择"检查预定线路合理性"服务，系统显示旅行计划不合理
    在这里插入图片描述

预订系统管理者

  • 在登录界面输入 admin 进入系统管理者界面，可以直接对航班、大巴、宾馆、客户信息进行增删改查，还可以查询预约信息 (不允许 admin 直接在后台更改客户的预约信息，如果航班取消或航班号修改，数据库会自动利用触发器对相应的预约信息进行删改)。同时也支持 admin 直接输入 SQL 语句来操纵数据库
    在这里插入图片描述

下面以航班为例演示增删改查

(1) 查询航班信息

  • 选择对 FLIGHTS 表进行操作后，系统询问是要进行增删改查哪种操作。这里选择“查询” (3. Query)
    在这里插入图片描述
  • 选择后，系统自动输出所有航班的信息：
    在这里插入图片描述

(2) 新增航班

  • 在选择对 FLIGHTS 表进行操作后，选择 1. Insert 进行航班信息的插入。系统要求输入航班的各项信息
    在这里插入图片描述
  • 下面插入一趟航班号为 12345，票价 999，载客量 100，从无锡飞往大连的航班。系统显示插入成功
    在这里插入图片描述
  • 再次进行查询，可以观察到这趟航班已被成功添加
    在这里插入图片描述

(3) 修改航班信息

  • 在选择对 FLIGHTS 表进行操作后，选择 4. Update 进行航班信息的更新。系统要求输入想要修改的航班号
    在这里插入图片描述
  • 这里就对刚才添加的航班 12345 进行修改。输入航班号后，系统要求输入想要更改的航班属性与新值。这里将航班号改为 54321.最后系统提示修改成功：
    在这里插入图片描述
  • 此时对航班信息进行查询，发现 12345 航班被修改为了 54321 航班：
    在这里插入图片描述

(4) 删除航班信息

  • 在选择对 FLIGHTS 表进行操作后，选择 2.Delete 进行航班信息的删除。系统要求输入想要删除的航班号，这里我们就选择删除航班 54321；最后系统提示删除成功：
    在这里插入图片描述
  • 再次查询航班信息，可以看到航班 54321 已被删除
    在这里插入图片描述

(5) 查询预约信息

  • 首先选择查询预约服务，接着系统询问要查询航班还是大巴还是宾馆的预约信息
    在这里插入图片描述
  • 这里选择查询航班预约信息：
    在这里插入图片描述

直接执行 SQL 语句

  • 管理者还可以选择直接使用 SQL 语句来更灵活的操纵数据库
  • 如下图所示，查询航班信息，目前最后一行上航班 19213 的票价为 999
    在这里插入图片描述
  • 下面将航班 19213 的票价涨价 301 元。输入 SQL 语句后系统提示执行成功：
    在这里插入图片描述
  • 再次查询航班信息，航班 19213 的票价涨价到了 1300
    在这里插入图片描述

触发器测试

  • (1) 插入一条航班预约信息 (new.resvType = 1) 且该航班号存在 (航班号在 FLIGHTS 表中)，则将该航班的剩余座位数减一，否则禁止此次预约 (回滚)
    • 首先查看航班信息，注意到航班 18791 目前剩余座位还有 30 个
      在这里插入图片描述
    • 下面切换为普通客户对航班 18791 进行预约
      在这里插入图片描述
  • 预约完成后切换回 admin 用户，查看航班信息，发现航班 18791 的可用座位数减少了一个
    在这里插入图片描述
  • (2) 如果取消航班预约 (old.resvType = 1) 且 FLIGHTS 表中存在该航班信息，则将 FLIGHTS 表中对应航班的可用座位数加一
    • 取消对航班 18791 的预约
      在这里插入图片描述
    • 再次查看航班 18791 的可用座位数，发现增加了一个
      在这里插入图片描述
  • (3) 如果有航班被取消，则删除该航班对应的预约信息；如果有航班号被更改，则同步更新该航班对应的预约信息
    • 首先查看客户目前的航班预约情况：
      在这里插入图片描述
    • 切换到 admin 模式，删除 11714 航班
      在这里插入图片描述
    • 此时再查看航班预约情况，发现 11714 航班的预约已被删除
      在这里插入图片描述
  • 源码已上传至 Github

参考项目

连理o
关注
  • 9
    点赞
  • 踩
  • 48
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
数据库上机报告.docx
10-03
数据库上机报告 数据库上机报告是数据库实验报告的文档，涵盖了数据库设计、数据表创建、数据插入、查询和视图等操作。本文档将从数据库设计、数据表创建、数据插入、查询和视图等方面详细讲解数据库上机报告的知识...
【数据库】实验4：触发器实验
Coco416的博客
11-21 2837
触发器类似于约束，但比约束灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。before是先完成触发，再增删改，触发的语句先于监视的增删改，这样就可以对new进行修改了。before是先完成触发，再增删改，触发的语句先于监视的增删改。
第11章 触发器和事件
weixin_65409730的博客
05-31 141
【例11.5】 在stusys 数据库的student表创建一个触发器T_deleteStudentScore，当删除表student中某个学生的记录时，同时将score表中与该学生有关的数据全部删除。【例11.3】 在stusys 数据库的student表创建触发器T_inserStudentRecord，当向student表插入一条记录时，显示插入记录的学生的姓名。【例11.8】创建事件E_startDays，从第2天起，每天清空表tb，在2021年12月31日结束。
数据库实验四：触发器实验
12-11 2355
数据库实验四
[数据库] Navicat for MySQL触发器更新和插入操作
weixin_34025051的博客
08-31 8963
一、触发器概念 触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。 触发器经常用...
触发器的创建与使用
最新发布
user8527的博客
11-21 550
在MySQL中，创建触发器语法如下：其中：在MySQL中，BEGIN … END 语句的语法为：例子： 表示在course的表下面添加一个删除意义的触发器，并且关联score表，如果course表中course_id被删除，那么score表中的course_id也会被删除。例子2：更新一个触发器，表示在course的表下面添加一个更新意义的触发器，并且关联score表，如果course表中course_id被更新或改变，那么score表中的course_id也会被更新或改变。例子3：插入一个触发器，表示在c
数据库上机实验报告
03-23
数据库上机实验报告 本实验报告的目的是通过实际操作，深入理解数据库理论知识，并掌握SQL Server数据库管理系统的操作技术。实验内容包括创建数据库、基本表和修改表结构，输入数据到数据库，并学会创建和使用表的...
数据库上机实验报告.doc
11-24
数据库上机实验报告是数据库系统及应用课程的实验报告，旨在理解 SQL Server 数据库的存储结构，掌握 SQL Server 数据库的建立方法和维护方法。实验报告中涉及到数据库的创建、修改和删除等基本操作。 一、数据库的...
数据库上机实验报告.docx
11-29
数据库上机实验报告.docx 本实验报告旨在介绍数据库实验的基本知识和技术，涵盖数据库设计、查询优化、数据修改、权限管理等方面的内容。通过本实验报告，读者可以了解数据库实验的基本步骤和技术，并掌握数据库...
学生管理系统数据库上机报告及代码
11-21
本次上机作业主要涉及了学生管理系统的数据库设计与实现，包括E-R模型转换、数据库创建、视图、触发器、存储过程以及游标的使用。以下是各个知识点的详细说明： 1. **E-R模型**：实体-关系模型是数据库设计中的重要...
MySQL约束、触发器-数据库实验四
m0_51657509的博客
06-22 2713
MySQL约束、触发器相关实验例题。
实验六、完整性控制（包含触发器的使用方法）——SQLServer触发器
GHZ2443063059的博客
11-06 2706
掌握SQL Server数据库系统完整性控制方法 1. 创建一个触发器，“数据库”课程的选课人数不能超过5人。 2. 创建一个触发器，规定每位同学选课门数不能超过3门。 3. 创建一个触发器，当修改成绩表中成绩时，如果变化幅度超过10%，将操作记录在下表SC_U（Sno,Cno,Oldgrade,Newgrade）中。
触发器实验
mzpqq的博客
06-03 1375
《数据库系统 》 实验报告 实验名称 触发器实验 实验地点 实验楼423 实验日期 一、实验目的及要求 掌握触发器的创建、修改和删除操作 掌握触发器的触发执行 掌握触发器与约束的不同 二、实验环境 Windows10, SQL server2008 三、实验内容 创建触发器 （1） 启动SQL Server 查询编辑器，“S+学生学号”数据库。 （2） 在查询命令窗口中输入以下CREATE TRIGGER 语句，创建触发器。 为SC(学生选课)表创建一个基于UPDATE
MySQL——触发器的基本操作
ShawnYe1022的博客
01-03 1201
实验环境： 1、 操作系统：window10 2、 MySQL 5.7 实验内容与完成情况： 1.在sc表中创建insert、update触发器，检查学号是否为student表中的学号，若无则不许插入选课记录或修改选课表学生学号。 1.1 创建insert触发器： delimiter // create trigger TR_SC_IN_SNO after insert on sc for each row begin if new.sno not in (select sno from student)
数据库实验-触发器实验
weixin_42703039的博客
11-11 1万+
一、实验目的 1．掌握触发器的创建、修改和删除操作。 2．掌握触发器的触发执行。 3．掌握触发器与约束的不同。 二、实验环境 硬件平台:PC； 软件平台:Windows 7 / SQLSERVER 2008 R2； 三、实验内容 1、 在 Student 表中编写 insert 的触发器，假如每个班的学生不能超过 30 个，如果低于此数，添加可以完成；如果超过此数，则插入将不能实现。 2、 在 ...
数据库实验--数据修改、删除与视图，触发器
siper12138的博客
04-19 3197
数据库实验 数据修改、删除与视图 【题目基础】 建立教学数据库的三个基本表并用INSERT 语句输入数据（注意实现参照完整性，学号课程号为主键，添加参照完整性约束：成绩在0-100之间）: ``` S(Sno,Sname,Ssex,Sage,Sdept) 学生(学号,姓名,性别,年龄,系) SC(Sno,Cno,Grade) 选课(学号,课程号,成绩) C(Cno,Cname, Ccredit) 课程(课程号,课程名,学分) 【数据库建立】 【数据】 S表数据如下（其余数据自行补充）： 20121512
《数据库原理》实验报告DB5——触发器与存储过程(函数)
CityBoySHU的博客
12-23 4350
一. 实验内容、步骤以及结果 1. 针对SPJ_MNG数据库，创建并执行如下存储过程。（共计40分） (1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是：当执行该存储过程时，将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。（5分） 创建： DELIMITER $$ USE `spj_ming`$$ CREATE PROCEDURE `jsearch1` () BEGIN select * from s where city = '北京'; END$$ 验证： Ca
实验6 触发器的使用
欢迎来到我的博客
05-21 6874
实验6 触发器的使用 实验目的 掌握触发器的创建、修改和删除操作。 掌握触发器的触发执行。 掌握触发器与约束的不同。 二、实验要求 1.创建触发器。 2.触发器执行触发器。 3.验证约束与触发器的不同作用期。 4.删除新创建的触发器。 三、实验内容 （一）示例 1.创建触发器 ①启动SQL Server 查询编辑器，选择要操作数据库，如“学生_课程”数据库。 ②在查询命令窗口中输入以下CREATE TRIGGER语句，创建一个针对Student表的触发器，打印共修改了多少行数据。..
数据库实验六-触发器
热门推荐
服务猿
03-02 1万+
实验内容 将实验四的数据库按书中79页中的表对其填充数据，然后在数据库中做如下操作： （1）查询学号为201215121的姓名和所选修的课程门数，当门数为2门及以上时，则输出“XX,已经完成了选课”，否则输出“XX，还需选课”。（提示：需定义变量存储选修的课程门数和学生姓名） （2）在数据库中查找姓名为“张立”的学生，若找到，输出该学生的学号，姓名，年龄，院系，否则，输出“查无此人”的信息。...
"实用数据库上机实验报告：SQLServer2000环境配置和数据库创建
总之，这份实验报告是一份很实用、详细的数据库上机报告，内容涵盖了数据库的基本操作和一些高级功能，对初学者来说非常有帮助。报告中的截图直观清晰，操作步骤也相对详细，一定会对读者有所帮助。希望今后能够有更...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值