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);
数据库第一次实验_sql语言使用
最新推荐文章于 2024-08-16 18:39:05 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)