MOOC 数据库与系统原理大题总结

最新推荐文章于 2025-03-10 12:18:28 发布

zhezhidashi

最新推荐文章于 2025-03-10 12:18:28 发布

阅读量1.1k

点赞数 2

分类专栏：语言语法学习笔记文章标签：数据库 sql database

本文链接：https://blog.csdn.net/qq_45812711/article/details/121176463

版权

语言语法学习笔记专栏收录该内容

31 篇文章

订阅专栏

实验题

实验三数据库基本操作

（1）创建数据库 textbook：

CREATE database textbook

（2）显示并修改最大连接数

show variables like "max_connections";
set global max connections = 8;

（3）删除数据库 textbook

drop database textbook

实验四基于SQL的数据定义与修改

（1）在数据库 Antiepidemic 中，使用 SQL 语句创建数据表 aehero(aehero_id, aehero_name, aehero_gender)，设置 aehero_id 为主键。

create table aehero
{
	aehero_id CHAR(20) not null unique primary key,
	aehero_name VARCHAR(30),
	aehero_gender CHAR(4),
	aehero_team VARCHAR(40)
}

（2）设置默认值

eeage smallint default 18,

（3）在数据库 Antiepidemic 中, 使用 SQL 语句创建数据表 contribution(aehero_id, aemeasure_id, aehero_deeds)，设置主键、外键。

create table contribution
{
	aehero_id CHAR(20),
	aemeasure_id CHAR(20),
	aehero_deeds VARCHAR(40),
	primary key(aehero_id, aemeasure_id),
	constraint fk_aehero foreign key(aehero_id) references aehero(aehero_id),
	constraint fk_aemeasure foreign key(aemeasure_id) references aemeasure(aemeasure_id)
}

（4）使用 SQL 语句修改数据表 aehero 的名称为 hero1，并添加字段 aehero_team（抗疫英雄所属医疗队）

alter table aehero rename to hero1;
alter table hero1 add column aehero_team VARCHAE(30);

（5）使用 SQL 语句修改数据表 hero1 中的 aehero_name 字段的类型为文本类型。

alter table hero1 modify column aehero_name TEXT;

（6）使用SQL 语句把数据表 aemeasure 中的 aemeasure_name 改为 aemeasurename

alter table aemeasure change aemeasure_name aemeasurename VARCHAR(30);

（7）使用 SQL 语句删除数据表 hero1 中的字段 aehero_gender。

alter table hero1 drop column aehero_gender;

（8）删除表hero1（先删除外键所在的表）

drop table contribution;drop table hero1;

（9）使用 SQL 语句向 aehero 表中插入数据:

insert into aehero values('1', '李兰娟', '女', '中国工程院');

（10）使用 SQL 语句将 aemeasure 表中 aemeasure_id=1 的措施名称改成“封城”。

update aemeasure set aemeasure_detailr = '封城' where aemeasure_id = '1';

（11）使用 SQL 语句将 contribution 表中所有数据删除

SET SQL_SAFE_UPDATES = 0;delete from contribution;

实验五单表查询和联接查询

（1）使用 SQL 语句查询 medicine 表中 mid 以‘2018’开头并且 mdescription 为空的行，结果显示其所有属性。

select * from medicine where mid like "2018%" and mdescription is null;

（2）使用 SQL 语句查询 medicine 表中 myear 属性的值，并返回不重复的 myear 属性值。

select distinct myear from medicine;

（3）使用 SQL 语句查询阿胶单价是 3300 元的时间到当前过去了几年，结果显示年数即可。

select year(now()) - myear as year from medicine where mname = '阿胶' and mprice = 3300;

（4）使用 SQL 语句查询 medicine 表中“安国”市场和“玉林”市场在 2018~2019 年之间入库的单价在 58 元以上的药材名字（mname）和单价(mprice)，并按药材入库时间升序排列。

select mname, mprice from medicine 
where mlocation in ('安国', '玉林') 
and (myear between 2018 and 2019) 
and mprice > 58
order by myear asc;

（5）使用 AVG()函数统计 medicine 表中各个年份中药材的平均单价（命名为 avg_price），只选取那些平均单价在 800 以上的分组，结果显示入库年份以及对应的平均单价。

select myear, avg(mprice) as avg_price from medicine 
group by myear having avg_price > 800

（6）使用 COUNT()函数、MAX()函数统计 medicine 表中各年份入库药材的药材种类数量（命名为 kinds）和最高单价（命名为 max_price），结果显示入库年份、药材种类数量、最高单价，按照最高单价升序排列，并选取从第二行开始的两行（包括第二行）

select myear, count(myear) as kinds, max(mprice) as max_price
from medicine
group by myear
order by max_price asc
limit 2 offset 1

（7）使用笛卡儿积查询：各个单位参与预案编制的情况。

select * from department
cross join record
where department.depart_id = record.depart_id;

（8）使用自然联接查询：已编制预案的名称和参与编制单位名称。

select plan_name, depart_name
from plan natural join record natural join department;

（9）使用条件联接查询：参与 1 号预案编制的单位名称和电话。

select depart_name, depart_tel
from plan join record join department 
on plan.plan_id = record.plan_id and record.depart_id = department.depart_id
where plan.plan_id = 1

（10）使用属性联接查询：各个单位参与预案编制的情况

select * from department join record using(depart_id);

实验六嵌套查询

（1）使用 SQL 语句查询同年份获奖人的平均年龄。

select ayear, avg(aage) as avg_agefrom awardgroup by ayear;

（2）使用 select 子句嵌套查询王仕花与获奖人平均年龄差

select aname, aage - (select avg(aage) from award) as differencefrom awardwhere aname = "王仕花";

（3）使用 select 子句嵌套查询所有获奖人与平均年龄差。

select aname, aage - (select avg(aage) from award) as differencefrom award;

（4）使用 select 子句嵌套查询 award 表中杜富国与吕保民的年龄差

select aage - (select aage from award where aname = "吕保民") as differencefrom awardwhere aname = "杜富国";

（5）使用 SQL 查询 award 表中编号以‘2016’开头并且 description 为空的行。

select *from awardwhere aid like "2016%" and description is null

（6）使用 select 子句嵌套查询从孙家栋获奖的时间到现在过去了几年。

select year(curdate()) - ayear as "过去了几年"from awardwhere aname = "孙家栋";

（7）使用 with 子句嵌套查询民政部负责的预案号及职责

with temp(depart_id, depart_name) as(select depart_id, depart_name from depart where depart_name = "民政部")select depart_name,plan_id,depart_responfrom temp join record on temp.depart_id = record.depart_id;

（8）使用 select 子句嵌套查询各个单位名称及其负责的预案数量。

select depart_name, (select count(*) from record where depart.depart_id = record.depart_id)as num from depart;

自己写的

select depart_name, count(*) from (select * from depart cross join recordwhere depart.depart_id = record.depart_id)group by depart_name

（9）使用 from 子句嵌套查询应急部负责的预案个数，并将 from 子查询命名为 num

select count(*)from (select plan_id from depart, record where depart_name = "应急部" anddepart.depart_id = record.depart_id) as num;

（10）使用 where 子句嵌套查询：负责‘发布时间在预案编号为 2 的发布时间之后的预案’的单位名称及其在预案中的职责。

select depart_name, depart_responfrom depart,record,planwhere plan.plan_date > (select plan_date from plan where plan_id = 2)and depart.depart_id = record.depart_id andplan.plan_id = record.plan_id;

实验七 python 连接 MySQL

（1）在 MySQL 中，创建一个函数计算整数 a+b 的和。
在这里插入图片描述

DELIMITER $$drop function if exists sum_ab$$create function sum_ab(a int, b int)returns intbeginreturn (select a + b);end $$DELIMITER ;select sum_ab(1, 2);

（2）在 MySQL 中，创建一个函数 add_num_cn（weapon_id,num_cn），实现对中国人民志愿军每个军配备给定武器种类 id 的数量增加定值。例如：add_num_cn（2,1）是给中国人民志愿军每个军配备的汽车数量(num_cn)添加 1。

DELIMITER $$drop function if exists add_num_cn$$create function add_num_cn(id int, num int)returns intbeginupdate weapon set num_cn = num + num_cn where weapon_id = id;return (select num_cn from weapon where weapon_id = id);end $$DELIMITER ;select add_num_cn(2, 1);

实验九视图和访问控制

（1）在 master 表上创建一个名为 master_view1 的视图，要求只显示年龄在 50 岁以上的工匠的信息；向该视图中插入数据 master_id=6, master_name =‘乔素凯’

create view master_view1 as select * from master where master_age > 50; insert into master_view1 values(6, '乔素凯', null, null, null);

（2）若在创建视图时加上 with check option 的约束呢？还能成功插入吗？请说明原因

create view master_view2 as select * from master where master_age > 50 with check option;insert into master_view2 values(7, '陈行行', null, null, null);

创建视图成功，但是插入数据失败。如果在视图定义的末尾包含 WITH CHECK OPTION，数据库管理系统自动检查对视图的修改应满足视图定义中 WHERE 条件，这种情况下此插入操作便会被拒绝执行。

（3）创建用户 user1，密码为’12345’。创建角色 master_admin，授予其对 master 表的查询权限和更新字段 master_name 的权限，并允许将此权限授予其他角色/用户，将该角色的权限授予用户 user1。

-- 创建用户create user user1 IDENTIFIED BY '12345';-- 创建角色create role master_admin;-- 授予其对 master 表的查询权限和更新字段 master_name 的权限并允许将此权限授予其他角色/用户grant select, update on masters.master to master_adminwith grant option;-- 将该角色的权限授予用户 user1grant master_admin to user1;set default role all to user1;

验证 user1 对 master 表的查询和更新权限（将 master_id 为 1 的工匠的姓名更新为‘高凤林副主席’）；

use masters;update master set master_name = '高凤林副主席' where master_id = 1;

（4）创建用户 user2，密码为’12345’。创建角色 master_reader，将该角色的权限授予 user2。

-- 创建用户create user user2 IDENTIFIED BY '12345';-- 创建角色create role master_reader;-- 将该角色的权限授予用户 user2grant master_reader to user2;set default role all to user2;

使用用户 user1 将 master 表的查询权限授予 master_reader 角色

-- 使用用户 user1 将 master 表的查询权限授予 master_reader 角色grant select on masters.master to master_reader;

（5）使用用户 user1 撤销 master_reader 对 master 表的查询权限

-- 使用用户 user1 撤销 master_reader 对 master 表的查询权限revoke select on masters.master from master_reader;

实验十

（1）向 record 表中添加如下数据，会发生什么情况，为什么？

insert into record values(5, 1, "应急物资", 150);

解释：插入的 depart_id 为 5，因为 depart_id 是 record 表的外键，但是在 depart 表中并没有 id 为 5 的部门，所以出现了错误。

（2）将 record 表中 plan_id 为 1 的值改为 5，会出现什么情况，为什么

update record set plan_id = 5 where plan_id = 1;

（3）在 plan 表上创建触发器，若触发事件针对的灾害地点为“华盛顿”时，则提示 “插入错误”

DELIMITER $$CREATE TRIGGER a BEFORE INSERT ON plan FOR EACH ROWBEGINIF(new.plan_area = '华盛顿') thensignal sqlstate 'HY000' set message_text = '插入错误!';end if;END $$DELIMITER;

（4）在 record 表上创建触发器，若 record 表中 depart_id 被修改，相应的 depart 表中的 depart_id 也被修改。

DELIMITER $$CREATE TRIGGER b after update ON record FOR EACH ROWBEGINIF(new.depart_id <> old.depart_id) thenupdate depart set depart_id = new.depart_idwhere depart_id = old.depart_id;end if;END $$DELIMITER ;

（5）删除 plan 表上的触发器。

drop trigger a;

实验十一

（1）显示开启一个事务，向 depart 表中插入如下数据，并使用 ROLLBACK 进行事务回滚。

begin;insert into depart values(5, '财政部', '68551114');rollback;commit;

（2）显示开启一个事务，向 depart 表中分别插入如下两行数据，并在第一条插入语句与第二条插入语句之间创建保存点，插入完成后将事务回滚到刚才的保存点。

begin;insert into depart values(6, '税务局', '63417425');savepoint sql1;insert into depart values(7, '统计局', '68573311');rollback to sql1;commit;

（3）显示开启事务 1，给 depart 表加锁，只允许 depart 表具有读操作

T1:begin;lock table depart read;commit;T2:begin;insert into depart values(9, '事业部', '58789777');commit;

（4）对字符串“I love database”使用 MD5 加密算法，返回加密结果。

select md5('I love database');

（5）用 AES 算法、以字符串“datebase”做密钥，对“abcdef”的对称加密, 输出加密密文，再对密文进行解密，还原出“abcdef”。

加密

select hex(aes_encrypt('abcdef', 'datebase'));

得到密文 83B78DCBD0503D3420DE40AE0EE6EE6D

解密

select aes_decrypt(unhex('83B78DCBD0503D3420DE40AE0EE6EE6D'), 'datebase');

期中期末考试

期中

（1）检索考号为2188110110888考生所报考试卷的试卷号和试卷名。
在这里插入图片描述
（2）‍某数据库包含两个表，一个表P，包括的列有a1、a2、b1；另一个表Q，包括属性b1、b2、b3。表P属性b1是外键，引用表Q的b1。表P主键a1，表Q主键b1；假定A有nnn个元组，B有mmm个元组。假设两个表中没有出现NULL。请问P和Q自然联接的结果有多少个元组？
答案：nnn
（3）
在这里插入图片描述
实验六的第（8）问，不再多说

期末

其他

E-R图

基本E-R图转换为关系模式学习体会分享与碰到问题探讨，设计一个图书馆数据库，此数据库保存每个借阅者每次节约图书的记录，包括：读者号，姓名，地址，性别，年龄，部门。对每本书存有：书号，书名，作者，出版社。每当有一本书被借还时，保存每次借阅信息：借出日期、应还日期、归还日期。要求：给出 E-R 图，并转换成关系模式。
在这里插入图片描述