数据库实验一SQL语言.doc
文本预览下载声明
图书表
create table BookInfo(BookNo char(5),TypeId char(5) not null, Bookname char(10) not null, Author char(4), Publisher char(10), Price number(4,2) not null, primary key(BookNo));
读者表
create table Readers(CardNo char(5), Name char(5) not null, Faculty char(10), Title char(4) check(Title in(‘初级’,’中级’,’高级’)), primary key(CardNo));
借阅表
Create table Borrow(CardNo char(5), BookNo char(5) not null, time date, Remark char(10), primary key(CardNo,BookNo), Foreign key(CardNo) references Readers(CardNo), foreign key(BookNo) references BookInfo(BookNo));
插入数据
Insert into BookInfo values(‘0001’,’TP31’,’计算机基础’,’WANG’,’高等教育’,17.00);
Insert into BookInfo(BookNo,TypeId,Bookname,Price) values(‘0002’,’TP32’,’数据库原理’,’16.50’);
Insert into BookInfo values(‘0003’,’TN31’,’并行计算机’,’YANG’,’清华大学’,12.80);
Insert into Readers values(‘T201’,’LIXIN’,’计算机系’,’中级’);
Insert into Readers values(‘S981’,’YANG’,’通信系’,’高级’);
Insert into Readers values(‘Z003’,’CHEN’,’工厂’,’初级’);
Insert into Borrow(CardNo,BookNo,time) values(‘T201’,’0029’,to_date(’2001-3-10’,’yyyy-mm-dd’));
Insert into Borrow(CardNo,BookNo,Date) values(‘T201’,’0003’,to_date(’2001-4-1’,’yyyy-mm-dd’));
Insert into Borrow(CardNo,BookNo,Date) values(‘S981’,’0002’,to_date(’2001-2-20’,’yyyy-mm-dd’));
Insert into Borrow(CardNo,BookNo,Date) values(‘Z003’,’0001’,to_date(’2001-3-3’,’yyyy-mm-dd’));
修改和删除
Update BookInfo
set Author=’HANG’ ,Publisher=’高等教育’
Where BookNo = ‘0002’;
Update BookInfo
Set Price =Price*1.05;
update BookInfo set TypeId = ‘TP38’ where Bookname like ‘%计算机%’;
Delete from Borrow where CardNo like ‘S%’;
Delete from Readers where CardNo like’S%’;
查询功能
Select Bookname,Publisher from BookInfo;
Select Name,Title from Readers
Where Faculty=’工厂’;
Select Bookname from BookInfo
Where Price ALL(select price from BookInfo where Publisher = ‘高等教育’);
Select max(price),min(price), avg(price) from BookInfo
Where Publisher = ‘高等教育’;
Select max(Price),min(Price), avg(Price) from BookInfo
Where Publisher = ‘清华大学’;
Select Name,Faculty, count
显示全部