数据库源代码.doc

create user U_22002 identified by sayougou;grant connect,resource to U_22002;connect U_22002/sayougou;create table reader_U_22002(rno char(10) constraint reader_U22002_rno_pk primary key,rname varchar(32),sex varchar(4) constraint reader_U_22002_sex_ck check(sex in (男,女,其他,其它),age number(3),tel varchar2(13),email varchar2(32);insert into reader_U_22002 values(9091101120,张三, 男, 11, 07311560, 24601243931);insert into reader_U_22002 values(9091120021,张文博, 男, 12, 07311561, 24601243932);insert into reader_U_22002 values(9091101122,张冠, 男, 13, 07311562, 24601243933);insert into reader_U_22002 values(9091101123,李四, 男, 14, 07311563, 24601243934);insert into reader_U_22002 values(9091101124,王五, 男, 15, 07311564, 24601243935);insert into reader_U_22002 values(9091101125,赵六, 男, 16, 07311565, 24601243936);insert into reader_U_22002 values(9091101126,万七, 男, 17, 07311566, 24601243937);insert into reader_U_22002 values(9091101127,郑八, 男, 18, 07311567, 24601243938);insert into reader_U_22002 values(9091101128,珂九, 男, 19, 07311568, 24601243939);insert into reader_U_22002 values(9091101129,秦十, 男, 20, 07311569, 24601243940);insert into reader_U_22002 values(9091101130, 李正四, 男,20,07311570, 24601243941);create table book_U_22002(bno char(10) Primary key, btitle VARCHAR2(32), author VARCHAR2(32), publisher varchar2(32)，price number(7,2)check(price 10000); insert into book_U_22002 values(110,数据库1, 鼠, 出版社, 1000);insert into book_U_22002 values(111,数据库2, 虎, 出版社, 2000);insert into book_U_22002 values(112,数据库3, 牛, 出版社, 3000);insert into book_U_22002 values(113,数据库4, 马, 出版社, 4000);insert into book_U_22002 values(114,数据库5, 兔, 出版社, 5000);insert into book_U_22002 values(115,数据库6, 龙, 出版社, 6000);insert into book_U_22002 values(116,数据库7, 猴, 出版社, 7000);insert into book_U_22002 values(117,数据库8, 鸡, 出版社, 8000);insert into book_U_22002 values(118,数据库9, 狗, 出版社, 9000);insert into book_U_22002 values(119,数据库0, 蛇, 出版社, 0000);create table lend_U_22002(rno char(10) references reader_U_22002(rno) , bno char(10) references book_U_22002(bno), BORROWDATE DATE, RETURNDATE DATE, realRetDate date,PRIMARY KEY(RNO, BNO, BORROWDate),check(RETURNDATE- BORROWDATERETURNDATE;update lend_U_22002set isoverdue=是where realRetDateRETURNDATE;update lend_U_22002set isoverdue=否where realretdatereturndate;select * from lend_U_22002;delete from lend_U_22002 where BNO in(select bno where publisher=出版社 )；delete from book_U_22002 where publisher=出版社;select create user u_|substr(reader_U_22002.rno,7,4)| identified by p|substr(reader_U_22002.rno,7,4)|; from reader_U_22002;create user u_1120 identified by p1120;create user u_0021 identified by p0021;create user u_1122 identified by p1122;create user u_1123 identified by p1123;create user u_1124 identified by p1124;create user u_1125 identified by p1125;create user u_1126 identified by p1126;create user u_1127 identified by p1127;create user u_1128 identified by p1128;create user u_1129 identified by p1129;create user u_1130 identified by p1130;select grant connect,resource to u_|substr(reader_U_22002.rno,7,4)|; from reader_U_22002;grant connect,resource to u_1120;grant connect,resource to u_0021;grant connect,resource to u_1122;grant connect,resource to u_1123;grant connect,resource to u_1124;grant connect,resource to u_1125;grant connect,resource to u_1126;grant connect,resource to u_1127;grant connect,resource to u_1128;grant connect,resource to u_1129;grant connect,resource to u_1130;create view z_reader as select reader_U_22002.rno,reader_U_22002.rname,book_U_22002.bno,book_U_22002.btitle,lend_U_22002.BORROWDATEfrom book_U_22002,reader_U_22002,lend_U_22002where substr(reader_U_22002.rno,7,4)=substr(user,3,4) and lend_U_22002.rno=reader_U_22002.rno and book_U_22002.bno=lend_U_22002.bno;select grant select on z_reader to u_|substr(reader_U_22002.rno,7,4)|;from reader_U_22002;grant select on z_reader to u_1120;grant select on z_reader to u_0021;grant select on z_reader to u_1122;grant select on z_reader to u_1123;grant select on z_reader to u_1124;grant select on z_reader to u_1125;grant select on z_reader to u_1126;grant select on z_reader to u_1127;grant select on z_reader to u_1128;grant select on z_reader to u_1129;grant select on z_reader to u_1130;drop table sc;create table sc(sno char(10) constraint sc_sno_fk references stud(sno),cno char(9) constraint sc_cno_fk references course(cno),grand number(3),constraint sc_sno_cno_pk primary key(sno,cno);insert into lend_U_22002 values(9091101120,119, TO_DATE(2014-12-02,yyyy-mm-dd), TO_DATE (2014-12-05,yyyy-mm-dd), TO_DATE (2014-12-03,yyyy-mm-dd);insert into sc values(0909110112,090909102,80);insert into sc values(0909110112,090909103,90);insert into sc values(0909110112,090909104,80);insert into sc values(0909110112,090909105,90);insert into sc values(0909110113,090909101,90);insert into sc values(0909110113,090909102,40);insert into sc values(0909110113,090909103,90);insert into sc values(0909110113,090909104,40);insert into sc values(0909110114,090909101,80);insert into sc values(0909110114,090909102,70);insert into sc values(0909110114,090909103,70);insert into sc values(0909110115,090909101,90);insert into sc values(0909110115,090909102,90);insert into sc values(0909110115,090909103,90);insert into sc values(0909110115,090909104,90);insert into sc values(0909110115,090909105,90);insert into sc values(0909110116,090909103,95);insert into sc values(0909110116,090909104,90);insert into sc values(0909110116,090909105,92);create table emp(empno char(10) primary key,ename varchar2(32),mgr char(10) references emp(empno),hdate date,job varchar2(32),sal number(10,2),comm number(10,2),dno char(7),check(sal+comm=5000);insert into emp values(0909100101,张三,null,12-10月-1999,PRESIDENT,4000,1000,10001);insert into emp values(0909100102,里萨三,0909100101,12-10月-1999,MANAGER,7000,NULL,10001);insert into emp values(0909100103,里萨三,0909100101,12-10月-1999,MANAGER,NULL,10000,10001);insert into emp values(0909100104,里萨三,0909100101,12-10月-1999,MANAGER,6000,0,10001);DROP TABLE EMP;create table emp(empno char(10) primary key,ename varchar2(32),mgr char(10) references emp(empno),hdate date,job varchar2(32),sal number(10,2) NOT NULL,comm number(10,2) NOT NULL,dno char(7),check(sal+comm=5000);DROP TABLE EMP;create table emp(empno char(10) CONSTRAINT emp_empno_pk primary key,ename varchar2(32),mgr char(10) CONSTRAINT emp_mge_fk references emp(empno),hdate date,job varchar2(32),sal number(10,2),comm number(10,2),dno char(7),CONSTRAINT emp_sal_