数据库实验习题

in表示嵌套

/*
 1.	IN表示的嵌套
*/
# （1）	查询至少借阅了一本图书的读者编号、姓名和单位。
select rno,rname,dept from reader where rno in (select rno from borrow)

# （2）	查询2016年一次也没借阅过的读者编号、姓名和单位。
select rno,rname,dept from reader where rno not in (SELECT rno from borrow where year(borrowtime)=2016)

# （3）	查询还未归还的图书的书号、书名和出版社（还书时间为空的表示未还）。
select bno,bname,press from book where bno in (select bno from borrow where backtime is null)

#（4）	查询借阅了红楼梦的读者编号、姓名和单位。
select rno,rname,dept from reader where rno in (select rno from borrow where bno=(select bno from book where bname='红楼梦'))

# （5）	查询至少借阅过书号为“B11001”和“B11003”两本图书的读者编号。
select rno from borrow where bno='b11001' and rno in(select rno from borrow where bno='b11003')

# （6）	查询借阅了“数据库原理”或“图形图像处理”的读者信息。
select * from reader where rno in (SELECT rno from book natural join borrow where reader.rno=borrow.rno and (book.bname='数据库原理' or book.bname='图形图像处理'))
select reader.* from reader natural join borrow natural join book where book.bname='数据库原理' or book.bname='图形图像处理';
select * from reader where rno in(select rno from borrow where bno in(select bno from book where bname in('数据库原理','图形图像处理')));

# （7）	查询借阅图书有过迟还行为的读者信息（迟还费大于0的）。
select * from reader where rno in (select rno from borrow where latefee>0)
select reader.* from reader natural join borrow where latefee > 0

比较符表示嵌套

/*
2.	比较符表示的嵌套
*/
# （8）	查询借阅了红楼梦的读者编号、姓名、单位和借书时间。
select * from reader where rno =(select rno from borrow natural join book where book.bname='红楼梦')

# （9）	查询职工号比“张旗”大而年龄比他小的职工的职工号、姓名和年龄。
select zno,zname,age from employee where
zno>(select zno from employee where zname='张旗') 
and age<(SELECT age from employee where zname='张旗')

# （10）	查询年龄大于所有女职工年龄的男职工的信息（用ALL表示）。
select * from employee where age>all(select age from employee where sex='女') and sex = '男'

# （11）	查询年龄至少比一个女职工小的男职工的信息（用ANY表示）。
select * from employee where age <any(select age from employee where sex='女') and sex = '男'

exists表示嵌套

/* 
3.	EXISTS表示的嵌套
*/
# （12）	查询借阅了机械工业出版社出版的所有图书的读者编号、姓名、单位。
select rno,rname,dept from reader where not exists(select book.bno from book where press='机械工业出版社' and not exists(select * from borrow where book.bno=borrow.bno and reader.rno=borrow.rno))

简单统计

/* 
1.简单统计
*/
# （1）	查询女职工的人数和平均年龄。
select count(*) as '总人数',avg(age) as '平均年龄'from employee where sex='女'


# （2）	查询红楼梦被借阅次数。
select count(*) as '借阅次数'  from book natural join borrow where bname='红楼梦'


# （3）	查询年龄大于女职工平均年龄的男职工的姓名和年龄。
select zname,age from employee where age>(select avg(age) from employee where sex='女') and sex='男'

#  （4）	查询各单位年龄最大的读者的编号、姓名、年龄和单位(并列最大的要全在结果中，可以用where嵌套，也可用from嵌套)
select  DISTINCT rno,rname,age,A.dept from reader as A inner join (select dept,max(age) as MA from reader group by dept) as B on A.age=B.MA and A.dept=B.dept
select rno,rname,age,dept from reader as A where age=(select max(age) from reader as B where A.dept=B.dept);

分组统计和汇总

/*
分组统计和汇总
*/
#  （5）	查询读者人数在10人以上的单位及人数
select dept,count(*) from reader group by dept having count(*) > 10

#  （6）	统计每年收取的损毁费（结果含年份、损毁费，年份按还书时间算）
select year(backtime),sum(damagefee) from borrow group by year(backtime)

#  （7）	查询借阅过两次以上的读者编号、姓名和借阅次数（可以用from嵌套）
select reader.rno,rname,A.借阅次数 from(select rno,count(*) as '借阅次数' from borrow group by rno having count(*) >= 2) as A natural join reader;
select rno,rname,count(*) as '借阅次数' from borrow natural join reader group by rno having count(*)>=2;

# 	（8）	查询所有读者借阅图书的次数（结果含编号、姓名和借阅次数，未借阅的也要在结果中，可以用from嵌套）。
select reader.rno,rname,A.借阅次数 from reader left join (select rno,count(*) as '借阅次数' from borrow group by rno ) as A on reader.rno=A.rno;
select reader.rno,rname,count(borrow.rno) from reader left join borrow on borrow.rno=reader.rno group by rno; 


#  （9）	查询借阅次数最多的读者编号及借阅次数(并列最多的要全在结果中，用having嵌套)
select rno,count(*) as 借阅次数 from borrow group by rno having count(*) >= all(select count(*) from borrow group by  rno)

# （10）	查询各单位读者人数，并汇总所有读者人数。
select dept,count(*) from reader group by dept with rollup

排序

/* 
排序
*/
# （11）	查询每个出版社图书的平均价，要求输出出版社名和平均价，并按平均价由高到低排列，平均价相同时按出版社名称升序排列
select press as '出版社',avg(price) as '平均价' from book group by press order by avg(price) desc ,bname

# （12）	查询每种图书的借阅次数，要求输出图书号、图书名和借阅次数，并按借阅次数由高到低排列。
select book.bno,bname,count(*) from book natural join  borrow  group by bname order by count(*) desc

组合查询

/*
组合查询
*/
# （13）	查询机械工业出版社和电子工业出版社出版的图书信息（使用组合查询）。
select * from book where press='机械工业出版社'
union 
select * from book where press='电子工业出版社'

/* 
限制查询结果数量
*/
# （14）	查询职工表中第3-8条记录。
select * from employee limit 2,6

# （15）	查询借阅所交费用最多的前3位读者，要求输出读者编号和所交费用
select rno,sum(latefee+damagefee) as '所交费用'from borrow group by rno order by 所交费用 desc limit 3 

存储过程定义和调用

#（1）	创建一名为“seek_reader”的存储过程，按指定单位、指定姓名查询读者信息（单位名、姓名由参数提供）
delimiter $$
create procedure seek_reader (in dept_ varchar(20) ,in rname_ varchar(20) )
begin
		select * from reader where dept = dept_ and rname=rname_;
end$$

#（2）	使用“seek_reader”存储过程，查询文学院XXX的信息（XXX为自己的姓名），观察结果。
call seek_reader('文学院','XXX')

#（3）	创建一名为“count_rs”的存储过程，统计指定单位的读者人数，并通过参数带回统计结果。

delimiter $$
create procedure count_rs(inout count_ varchar(10))
begin
	select count(*)  into count_ from reader group by dept having dept=count_;
end$$
delimiter ;

#（4）	使用“count_rs”存储过程，查询信息工程学院的人数，并显示。
drop procedure count_rs
set@count_='信息工程学院';
call count_rs(@count_);
select @count_ as 总人数;

#（5）	创建一名为“update_rdage”的存储过程，修改指定单位、指定姓名的读者的年龄（单位名、姓名和年龄由参数提供）
delimiter $$
create procedure update_rdage(in dept_ varchar(10),in rname_ varchar(10),in age_ int)
begin
update reader set age=age_ where rname=rname_ and dept=dept_;
end $$
delimiter ; 

#（6）	使用“update_rdage”存储过程，将文学院XXX读者的年龄改为22岁（XXX为自己的姓名），观察结果。
call update_rdage('文学院','XXX',22);
select * from reader where rname='XXX'

#（7）	删除“update_rdage”的存储过程
drop procedure update_rdage;

函数的定义和使用

#（8）	创建一名为“count_books”的函数，统计指定作者的出版的图书数。
delimiter $$
create function count_books(zz varchar(10))
returns int
READS SQL DATA # 开启了二进制，不加该语句就会报错
begin
return(select count(*) from book group by author having author=zz);
end $$

#（9）	使用“count_books”函数，查询XXX出版的图书数（XXX为自己的姓名），并显示。
select count_books('XXX')

触发器的使用

#（10）	为tsglxt数据库创建一个日志表tsglxt_log，记录insert、update、delete等事件，日志表tsglxt_log的结构如下：
/*
字段名		类型及长度		主键	自定义约束			说明
id			bigint			是		auto_increment		自动编号
user_name	Varchar(30)				NOT NULL		用户名，取user()函数值
tb_name		Varchar(30)				NOT NULL		触发的表名
event_type	Varchar(20)				NOT NULL		取insert、update、delete
logtime		timestamp				NOT NULL		时间，取now()函数值*/
create table tsglxt_log(id bigint auto_increment primary key,
							user_name varchar(30) not null,
							tb_name varchar(30) not null,
							event_type enum('insert','update','delete') not null,
							logtime timestamp default now() not null)
#（11）	为book表创建一名为“del_bf_book”的删除触发器，功能是将book表删除的记录插入book_bak1表（book_bak1在实验一已创建，结构与book相同），并在日志表tsglxt_log中登记操作记录（user_name：user()，tb_name：book，event_type：delete，logtime：now）。
delimiter $$
create trigger del_bf_book before delete on book
for each row 
begin
	insert into book_bak1 values(old.bno,old.bname,old.press,old.price,old.author);
	insert into tsglxt_log(user_name,tb_name,event_type,logtime) values(user(),'book','delete',now());
end$$

#（12）	将book表书号为XXX的图书记录删除（XXX为图书作者是自己姓名的图书的书号，如果borrow表中有该书的借阅，请先删除这些借阅记录），观察book_bak1表和tsglxt_log表变化，分析触发器的执行情况。
delete from  book where bno='B81004'

#（13）	为borrow表创建一名为“update_bf_borrow”的修改触发器，功能是当修改的损毁费(damagefee)大于书价的3倍时，将修改的损毁费改为书价的3倍，并在日志表tsglxt_log中登记操作记录（user_name：user()，tb_name：borrow，event_type：update，logtime：now）。
delimiter $$
create trigger update_bf_borrow before update on borrow
for each row
begin
	set @price=0;
	select price into @price from book where bno=old.bno;
	if new.damagefee>3*@price then set new.damagefee=3*@price;
	end if;
	insert into tsglxt_log (user_name,tb_name,event_type,logtime) values(user(),'borrow','update',now());
end$$
#（14）	将borrow表id为22的借阅记录的damagefee改为300元（大于该书实际价格3倍），backtime改为当时系统日期，观察borrow表该借阅记录的damagefee实际值和tsglxt_log表的变化。
update borrow set damagefee=300 where id=22;
select * from borrow where id=22;

事件的设计

#（15）	为tsglxt数据库创建一个event_test表，用于事件测试，日志表event_test的结构如下：
#字段名		类型及长度	主键	自定义约束			说明
#id			int			是		auto_increment		自动编号
#event_name	Varchar(30)			NOT NULL			事件名
#event_time	timestamp			NOT NULL			发生时间，取now()函数值
create table event_test(id int auto_increment primary key,
												event_name varchar(30) not null,
												event_time timestamp not null);
												
##（16）	先开启事件功能，再创建一个事件event1，实现从事件创建开始，1分钟后结束，每隔10秒向event_test表中插入一条记录（event_name：event1，event_time：now()）。
create event event1 on schedule every 10 second
ends CURRENT_TIMESTAMP + INTERVAL 1 minute
do insert into event_test(event_name,event_time) values('event1',now());
#（17）	1分钟后查看event_test表，分析事件是否正确运行。
select * from event_test									

游标的使用

# （18）	创建一名为“seek_reader_deptname”的函数，返回reader表中所有单位名构成的字符串，如"文学院、旅游学院、信息工程学院……"，要求用游标实现（字符串连接可使用concat_ws()函数）。
delimiter $$
create function seek_reader_deptname()
returns varchar(30)
READS SQL DATA
begin
DECLARE _STOP int default 0;
declare co varchar(20);
declare res varchar(30);
declare cur cursor for select dept from reader group by dept;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=1;
open cur;
loop1:loop
	fetch cur into co;
		if _STOP=1 then 
			leave loop1;
		else
			set res = concat_ws('、',res,co);
		end if;
end loop loop1;
close cur;
return res;
end$$
#（19）	调用“seek_reader_deptname”函数，查询reader表中所有单位名构成的字符串。
select seek_reader_deptname()

用户的创建和使用

# （1）	添加两新的用户，其用户名分别为stu1、stu2，主机名为localhost，口令设置为123456，并查看mysql.user表变化 （用一条create user语句创建）
create user 'stu1'@'localhost' identified by '123456','stu2'@'localhost' identified by '123456'

#（2）	在命令窗口使用stu1用户名登录，观察是否能登录？如果能登录，选择tsglxt数据库，观察是否有权限选择？

# （3）	将stu2用户名改为tea1，主机名仍为localhost，修改完成后查看mysql.user权限表变化。
rename user 'stu2'@'localhost' to 'tea1'@'localhost'
select * from mysql.user

# （4）	使用mysqladmin命令将stu1用户的密码改为“123”，并登录，观察是否能登录？（此命令不需要注释）
mysqladmin -ustu1  -p password

#（5）	使用set password 命令将tea1用户的密码改为“123”，并登录，观察是否能登录？ 
set password for 'tea1'@'localhost' =password('123')
# 这里的高版本mysql会报错，则可以试试 ='123'就是不加password

限权的授予于撤销

#（6）	将查询reader表rno、rname、dept列的权限授予给stu1用户，并观察tables_priv和columns_priv权限表的变化。
grant select(rno,rname,dept) on reader to 'stu1'@'loaclhost'
select * from mysql.tables_priv
select * from mysql.columns_priv

# （7）	使用stu1登录，观察是不有权查询reader表所有列信息，是否有权查询reader表rno、rname、dept列？ 	

# （8）	将tsglxt数据库的所有权限授予给tea1用户，并允许其将权限转移，并观察db权限表的变化。
grant all on tsglxt.* to 'tea1'@'loaclhost';
select * from mysql.db;

#（9）	使用tea1登录，观察是不有权查询reader表所有列信息，是否有权进行插入、删除、修改？（演示一种操作）

#（10）	将服务器的所有权限（含grant权限）授予给tea2用户，如果该用户不存在由创建该用户，主机名为“localhost“，密码为“123456”，并观察user权限表的变化。
grant all on 服务器.* to 'tea2'@'localhost' identified by '123456';
# 高版本不允许这么操作，需要在创建用户之后才能对其进行赋予权力的操作
create user 'tea2'@'localhost' identified by '123456'
grant all on 服务器.* to 'tea2'@'localhost';

# （11）	撤销tea1用户查询在tsglxt数据库的的更新权限(update)，并观察db权限表的变化。
revoke update on tsglxt.* from 'tea1'@'localhost'
select * from mysql.db;

# （12）	使用tea1重新登录（必然重新登录），将陈小丽读者年龄改成23岁，观察是不有权修改。

数据库的备份于恢复

#（13）	使用mysqldump命令备份数据库tsglxt中的表borrow，备份文件名为borrow_bak.sql，备份文件存储在D:\BACKUP目录中（该目录需先创建）。
msyqldump -uroot -proot tsglxt borrow >D:\BACKUP\borrow_bak.sql

#  （14）	先删除borrow表，再使用mysql命令从备份文件borrow_bak.sql恢复borrow表，观察是否恢复成功。
mysql -uroot -proot tsglxt <D:\BACKUP\borrow_bak.sql

# （15）	使用mysqldump命令备份tsglxt整个数据库（要求使用--databases 参数），备份文件名为tsglxt_bak.sql，备份文件存储在D:\BACKUP目录中（该目录需存在）。
mysqldump -uroo -proot --databses tsglxt >D:\BACKUP\tsglxt_bal.sql

#（16）	先删除tsglxt数据库，再使用mysql命令从备份文件tsglxt_bak.sql恢复该数据库，观察是否恢复成功（删除后无需再创建tsglxt数据库，可直接从备份文件恢复）。
mysql -uroot -proot <D:\BACKUP\tsglxt_bak.sql

表数据的导入与导出

#（17）	将borrow表中数据导出到文件文件borrow_bak.txt中，字段间用逗号分隔，字符串用双引号定界，每条记录分行，备份文件存储在D:\BACKUP目录中（该目录需存在）。
select * from borrow into outfile 'D:\BACKUP\borrow_bak.txt'
fields
	terminated BY '\,'
	enclosed by '\"'
lines
	terminated by '\r\n'
# 注意这里要设置my.ini中的source-file-prive为空字符串，然后保存，再重新启动mysql服务即可

#（18）	从备份文件borrow_bak.txt中将数据导入到borrow表中。
set global local_infile=1
load data local infile 'D:\BACKUP\borrow_bak.txt' replace into table borrow 
fields 
	terminated by '\,'
	enclosed by '\"'
lines
	terminated by '\r\n'