数据库实验报告.docx

1、数据库实验报告实验报告14select Student.sclass,Student.sno,sname,ssex,sage,sdept,cname,gradefrom Student LEFT JOIN SC ON (Student.sno=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )56select 2014-sage birthfrom Student where sname =刘晨;7select Student.sclass,Student.sno,sname,ssex,sage,sdept

2、,cname,gradefrom Student LEFT JOIN SC ON (Student.sno=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )where Student.sname like 刘%8select sname,ssex,gradefrom Student LEFT JOIN SC ON (Student.sno=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )where SC.cno=19

3、。select cno,cnamefrom Coursewhere cpon is NULL10、select Student.sclass,Student.sno,sname,ssex,sage,sdept,cname,gradefrom Student LEFT JOIN SC ON (Student.sno=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )where Student.ssex=女 and Student.sclass=211select cno ,cnamefrom Coursewh

4、ere ccredit=212select Student.sclass,Student.sno,sname,cname,gradefrom Student LEFT JOIN SC ON (Student.sno=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )where SC.cno =1 or SC. cno =213select Student.sclass,Student.sno,sname,ssex,sdept,SC.cno,gradefrom Student LEFT JOIN SC ON

5、(Student.sno=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )where Course.cpon=1 and SC.sclass=214select SC.sclass,SC.snofrom SC LEFT JOIN Course ON (SC.cno=Co)where SC.cno =2 and grade = (select max(grade) from SC Where cno =2)15.select SC.sclass,SC.snofrom SC LEFT JOIN Course

6、ON (SC.cno=Co)where SC.cno =2 and SC.sclass=1 and grade = (select max(grade) from SC Where cno =2 and sclass=1)16select SC.sclass,SC.snofrom SC LEFT JOIN Course ON (SC.cno=Co)where SC.cno =2 and grade (select min(grade) from SC Where cno =2)实验报告2一、1,select sclass,snofrom scwhere cno=1 and sno in (se

7、lect sno from sc where cno =2)2,select sclass,snofrom scwhere cno=1 and sno not in (select sno from sc where cno =2)3,Select student.sclass,student.sno,sname,ssex,sage,sdept,cname,gradeFrom student ,sc,courseWhere student.sno=sc.sno and student.scalss=sc.sclass and o=o and student.sclass=1 and sc.sn

8、o in (select sno from sc where sclass=1 group by sno having avg(grade)85)4,Select sclasss,sno,sname from student Where not exists (select * from sc,s1 where s1.sclass=1 and s1.sno =2 and not exusts (select * from sc,s2 where s2.sno=student.sno and s2.sclass=student.sclass and o=o)5,Select distinct s

9、tudent.scalss,student.snoFrom student Where not exists (select * from sc where sno=student.sno and cno=1)6Selectsname,gradeFromstudent,scWherestudent.sclass=sc.sclassandstudent.sno=sc.snoandcno=2Orderbygradedesc7,Select student.sclass,student.sno,sum(ccredit)from Student LEFT JOIN SC ON (Student.sno

10、=SC.sno AND Student.sclass=SC.sclass )LEFT JOIN Course ON (SC.cno=Co )Group by student.sclass,student.sno8，select 1 sclass,avg(grade) gradefrom SCwhere Cno =3 and sclass=19insert into Student values(3,1,张三丰,男,21,WC)insert into SC values(3,1,8,100)insert into Course values(8,无机化学,NULL,6) 10Delete Fro

11、m sc Where cno=3Select * From sc Delete From sc Where cno=3Select *From sc11Update student Set sage=sage+1Where ssex=男and sno in( select sno from sc where cno=1and student.sno=sc.sno and student.sclass=sc.sclass)Select * From student二、2.1Create view s_c_gradeAs select student.sno,student.sclass,snam

12、e,cname,gradeFrom studnt LEFT JOIN sc ON (student.sno=sc.sno and syident .sclass=sc.sclass)LEFT JOIN course ON (o=o)2.2Create view v_is_scoreAs Select student.sno,student.sclass,cname,gradeFrom studnt LEFT JOIN sc ON (student.sno=sc.sno and syident .sclass=sc.sclass)LEFT JOIN course ON (o=o) where s

13、dept =I and grade 903Select * from s_c_gradeSelect * from v_is_score 4Select sclass,sno,ame,gradeFrom s_c_gradeWhere cname in (select cname from course where cno=1)实验报告31，计算器设计求解方程组：2、连接数据库 3、研究生管理系统实验报告4信息录入界面：相关代码：long ll_id,ll_idate ld_birthdaystring ls_name,ls_nation,ls_sex,ls_home,ls_tel,ls_par

14、ty/数据格式检查if sle_id.text=or isNull (sle_name.text) thenMessageBox(缺少数据,请输入学号)sle_name.setfocus()returnelseif sle_name.text=OR isNull(sle_name.text) thenmessagebox(缺少数据,请输入学生姓名)sle_name.setfocus()returnend ifif sle_birthday.textand not isNull(sle_birthday) thenif isDate(sle_birthday.text) thenld_birth

15、day=date(sle_birthday.text)elsemessagebox(输入数据有误,请使用“年-月-日”的日期格式)sle_birthday.setfocus()returnend ifend ifll_id=long(sle_id.text)/取出党员选择if rb_cy.checked thenls_party=rb_cy.textelseif rb_cp.checked thenls_party=rb_cp.textelsels_party=rb_none.textEND IF/取出性别选择IF rb_man.checked THENls_sex=rb_man.textEl

16、seIF rb_woman.checked THENls_sex=rb_woman.textEND IF/由民族ddlb，取出民族选择IF ddlb_nation.text=none THENls_nation=ELSEls_nation=trim(ddlb_nation.text)end if /检查学号有无重号select student.stud_idinto :ll_ifrom studentwhere student.stud_id=:ll_id;IF ll_i 0 thenmessagebox (错误信息,学号第 +string(ll_i)+ 号重号！请改正。)sle_id.set

17、focus()returnend ifLS_name=trim(sle_name.text)LS_home=trim(sle_home.text)LS_tel=trim(sle_tel.text)/向数据库写入数据INSERT INTO student( stud_id, name, birthday, nation, home, tel, resume ,sex,party) VALUES ( :ll_id, :ls_name, :ld_birthday, :ls_nation, :ls_home, :ls_tel, :mle_resume.text,:ls_sex,:ls_party) ;

18、sle_id.text=sle_name.text=sle_birthday.text=mle_resume.text=sle_home.text=sle_tel.text=sle_id.setfocus ()查询界面：略。实验报告5图书管理系统：主界面登陆代码：退出代码：主界面：书籍借阅：代码:新书入库：代码;保存代码：long SQLCODEint ls_sto=1/数据格式检验string ls_isbn,ls_bname,ls_public,ls_writer,ls_class,ls_cno,ls_intro,ls_cato,ll_isbnreal ls_pricelong ll_i=

19、0if sle_isbn.text= or isNull (sle_isbn.text) then MessageBox(缺少数据,请输入ISBN号) sle_isbn.setfocus () returnelseif sle_bname.text= or isNull (sle_bname.text) then MessageBox(缺少数据,请输入书名) sle_bname.setfocus () returnend ifif ddlb_class.text=none then ls_class=else ls_class=Trim(ddlb_class.text)end ifls_isb

20、n=Trim (sle_isbn.text) select isbn into :ll_isbn:ll_i from Book where Book.isbn=:ls_isbn; ll_isbn=trim(ll_isbn)if ls_isbn=ll_isbn then update Book set Bsto=Bsto+1 where Book.isbn=:ls_isbn; if sqlca.sqlcode = 0 then MessageBox(成功,保存成功) sle_isbn.text= sle_bname.text= sle_public.text= sle_price.text= s

21、le_writer.text= ddlb_class.text= mle_into.text= mle_cato.text= else MessageBox(错误,保存错误) end if sle_isbn.setfocus () returnend ifls_bname=Trim (sle_bname.text)ls_public=Trim (sle_public.text)ls_price=real(Trim (sle_price.text)ls_writer=Trim (sle_writer.text)ls_intro=trim(mle_into.text)ls_cato=trim(ml

22、e_cato.text)if ls_writer then Insert into writer (Isbn, Wname) values(:ls_isbn,:ls_writer);end ifls_class=trim(ddlb_class.text)/检查分类有没有存在select CNum into :ls_cnofrom Classwhere CName=:ls_class;if ls_cno= then open(w_addclass) returnend if INSERT INTO book ( Isbn, Bname, Bpublic, Bprice, Cnum, Bintro

23、, Bcato, Bsto )values ( :ls_isbn, :ls_bname, :ls_public, :ls_price , :ls_cno, :ls_intro, :ls_cato , :ls_sto );if sqlca.sqlcode = 0 then MessageBox(成功,保存成功) sle_isbn.text= sle_bname.text= sle_public.text= sle_price.text= sle_writer.text= ddlb_class.text= mle_into.text= mle_cato.text=else MessageBox(错误,保存错误)end if图书搜索：读者信息查询：查询代码：