数据库第一次实验_sql语言使用

CREATE TABLE books
 (bno CHAR(4),									# 图书编号
  cno CHAR(4) NOT NULL,					# 分类号
  bname VARCHAR(12),						# 书名
  author VARCHAR(5),						# 作者
	pubunit VARCHAR(10),					# 出版单位
	uniprice DEC(5,2),						# 单价
PRIMARY KEY(bno));

CREATE TABLE readers
(borid    CHAR(4) NOT NULL,					# 借书证号
 borname  char(5) not null,					# 姓名
 borunit  VARCHAR(10),							# 单位
 bortitle VARCHAR(6),								# 职称
PRIMARY KEY(borid));


CREATE TABLE borrow
(borid CHAR(4),										# 借书证号
bno 		 CHAR(4),									# 图书编号
bordate  DATE NOT NULL,						# 借阅日期
remark 	 VARCHAR(20),							# 备注
PRIMARY KEY(borid, bno),
FOREIGN KEY(borid) REFERENCES readers(borid),
FOREIGN KEY(bno) REFERENCES books(bno));

ALTER TABLE readers ADD CHECK (bortitle IN('初级','中级','高级'));

# 插入数据
## 图书
insert into books
values('0001','TP31','计算机基础','WANG','高等教育', 17.00);
insert into books(bno, cno, bname, uniprice)
values('0002','TP32','数据库原理', 16.50);
insert into books
values('0003','TN31','并行计算机', 'YANG', '清华大学', 12.80);
insert into books
values('0004','TN31','数据结构', 'jie', '北京大学', 40.80);

## 读者 
insert into readers
values('T201','LIXIN','计算机系','中级');
insert into readers
values('S981','WANG','通信系','高级');
insert into readers
values('Z003','CHEN','工厂','初级');

## 借阅表
insert into borrow(borid,bno,bordate)
values('T201','0001',STR_TO_DATE('2002-03-10','%Y-%m-%d'));
insert into borrow(borid,bno,bordate)
values('T201','0003',STR_TO_DATE('2001-04-01','%Y-%m-%d'));
insert into borrow(borid,bno,bordate)
values('S981','0002',STR_TO_DATE('2001-02-20','%Y-%m-%d'));
insert into borrow(borid,bno,bordate)
values('Z003','0001',STR_TO_DATE('2001-03-03','%Y-%m-%d'));

# 数据的修改和删除
## (a)	为编号为0002的图书填上作者和出版单位。
update books
	set author='ZHANG',pubunit='高等教育'
	where bno='0002';
## (b)	 将所有图书单价上调5％。
update books
	set uniprice=uniprice * 1.05;
## (c) 将书名包含‘计算机’的书分类号改为‘TP38’
update books set cno='TP38'
	where bname like '%计算机%';
## (d) 删除借书证号以S开头的所有读者信息和借阅信息
delete from borrow
	where borid like 'S%';
delete from readers
	where borid like 'S%';
	
# 数据查询
## (1) 列出图书馆中所有藏书的书名及出版单位。
select bname, pubunit
from books;
## (2) 查询工厂所有借阅了图书的读者姓名和职称。
select borname,bortitle 
FROM readers,borrow
where readers.borid=borrow.borid and borunit='工厂';
## (3)查询藏书中比高等教育出版社所有图书单价更高的书籍。
select  * from books
		where uniprice	> ALL(select uniprice from books   
										where pubunit='高等教育');
## (4) 查询各出版社图书的最高价、最低价和平均价格
select pubunit,MAX(uniprice),MIN(uniprice),AVG(uniprice)
from books GROUP BY pubunit;

## (5) 列出当前至少借阅了2本图书的读者及所在单位。
select borname, borunit from readers
where borid in (select borid from borrow
                   group by borid
                   having count(*)>=2);

# 6.用SQL DDL创建视图、索引
## (1)建立各单位当前借阅图书情况的简单统计视图，视图中包括单位名称，借书人数和借阅人次。
create view Vcount(borunit, numofpeople, numofbor)
as select borunit, count(distinct readers.borid), count(readers.borid) 
from readers, borrow  
where readers.borid=borrow.borid group by borunit;

## (2)对该视图进行查询。
select * from Vcount;

## (3)按出版社为图书表建立一个索引。
create index Indexbooks on books(pubunit desc);