数据库课设-实验报告(共31页).doc
精选优质文档-倾情为你奉上数据库原理实验报告学号：姓名：实验一：SQL定义功能、数据插入 1建立教学数据库的三个基本表：S(Sno,Sname,Ssex,Sage,Sdept) 学生（学号，姓名，性别，年龄，系）SC(Sno,Cno,Grade) 选课（学号，课程号，成绩）C(Cno,Cname,Cpno,Ccredit) 课程（课程号，课程名，先行课，学分）_create table S(Sno char(9) PRIMARY KEY,Sname char(40),Ssex char(2),Sage SMALLINT,Sdept char(20); create table SC(Sno char(9),Cno char(4),Grade SMALLINT);create table C(Cno char(4),Cname char(40),Cpno char(4),Ccredit SMALLINT,);insert into Svalues('','李勇','男',20,'CS');insert into Svalues('','刘晨','女',19,'CS');insert into Svalues('20204','王敏','女',18,'MA');insert into Svalues('30203','张立','男',19,'IS');insert into SCvalues('',1,92);insert into SCvalues('',2,85);insert into SCvalues('',3,88);insert into SCvalues('',2,90);insert into SCvalues('',3,80); insert into Cvalues(1,'数据库','5',4);insert into Cvalues(1,'数学', ' ',2);insert into Cvalues(1,'信息系统','1',4);insert into Cvalues(1,'操作系统','6',3);insert into Cvalues(1,'数据结构','7',4);insert into Cvalues(1,'数据处理',' ',2);insert into Cvalues(1,'PASCAL语言','6',4);SQL> select * from SSNO SNAME SSEX SAGE SDEPT- - - - 李勇 男 20 CS 刘晨 女 19 CS 王敏 女 18 MA 张立 男 19 ISSQL> select * from SCSNO CNO GRADE- - - 1 92 2 85 3 88 2 90 3 80SQL> select * from CCNO CNAME CPNO CCREDIT- - - -1 数据库 5 41 数学 21 信息系统 1 41 操作系统 6 31 数据结构 7 41 数据处理 21 PASCAL语言 6 4_2DROP TABLE、ALTER TABLE、CREATE INDEX、DROP INDEX 及INSERT语句输入数据。_ create table z(Sno char(5), sage char(2), Sname char(6);insert into zvalues('15121','20','李勇');insert into zvalues('30504','20','朱猪');ALTER table z ADD s_entrance DATE;DROP table z;CREATE UNIQUE INDEX StuSno ON z(Sno);DROP INDEX StuSno;_实验二：数据查询 1查询选修1号课程的学生学号与姓名。 SQL> SELECT Sno,Sname 2 FROM S 3 WHERE EXISTS 4 (SELECT * 5 FROM SC 6 WHERE Sno=S.Sno AND Cno='1');SNO SNAME- - 李勇2查询选修课程名为数据结构的学生学号与姓名。SQL> SELECT Sno,Sname 2 FROM S 3 WHERE EXISTS 4 (SELECT * 5 FROM SC 6 WHERE Sno=S.Sno AND EXISTS 7 (SELECT * 8 FROM C 9 WHERE Cname='数据结构');SNO SNAME- - 刘晨 李勇3查询不选1号课程的学生学号与姓名。SQL> SELECT Sno,Sname 2 FROM S 3 WHERE NOT EXISTS 4 (SELECT * 5 FROM SC 6 WHERE Sno=S.Sno AND Cno='1');SNO SNAME- - 刘晨20204 王敏30203 张立4查询学习全部课程学生姓名。SQL> SELECT Sname 2 FROM S 3 WHERE NOT EXISTS 4 (SELECT * 5 FROM C 6 WHERE NOT EXISTS 7 (SELECT * 8 FROM SC 9 WHERE Sno=S.Sno AND Cno=C.Cno);SNAME-李勇5查询所有学生除了选修1号课程外所有成绩均及格的学生的学号和平均成绩，其结果按平均成绩的降序排列。create view dot (Sno,Cno,Grade)AS select S.Sno,Cno,Gradefrom S,SCwhere S.Sno=SC.Sno and Cno!=1;select Sno,avg(Grade) averagefrom dotwhere 60<=(select min(Grade) from dot xdot where dot.Sno=xdot.Sno )group by Snoorder by average desc;6查询选修数据库原理成绩第2名的学生姓名。select Sname,Snofrom Swhere Sno in(select Sno from SC where Cno in(select Cno from C where Cname='数据库') and Grade in(select max(Grade) from SC where Grade not in(select max(Grade) from SC where Cno in (select Cno from C where Cname='数据库') and Cno in(select Cno from C where Cname='数据库');7. 查询所有3个学分课程中有3门以上（含3门）课程获80分以上（含80分）的学生的姓名。create view xdot (Sno,Cno,Grade)AS select Sno,SC.Cno,Gradefrom SC,Cwhere Grade>=80 and ccredit=2 and SC.Cno=C.Cnoselect Snamefrom Swhere Sno in(select Sno from xdot group by Sno having count(*)>=3);8. 查询选课门数唯一的学生的学号。 create view dot (Sno,Ccount) AS select Sno,count(*) from SC group by Sno; select Sno from SC group by Sno having count(*)<>all (select Ccount from dot where dot.Sno!=SC.Sno); 实验三：数据修改、删除 1把1号课程的非空成绩提高10。SQL> UPDATE SC 2 SET Grade=Grade*(1+0.1) 3 WHERE GRADE IS NOT NULL AND GRADE<=100 AND CNO='1'已更新 1 行。SQL> SELECT * FROM SC;SNO CNO GRADE- - - 1 101 2 85 3 88 2 90 3 802在SC表中删除课程名为数据结构的成绩的元组。SQL> DELETE 2 FROM SC 3 WHERE CNO IN 4 (SELECT CNO 5 FROM C 6 WHERE C.CNO=SC.CNO AND CNAME='数据库');已删除 1 行。SQL> SELECT * FROM SC;SNO CNO GRADE- - - 2 85 3 88 2 90 3 803在S和SC表中删除学号为95002的所有数据。SQL> DELETE 2 FROM S 3 WHERE SNO =''已删除 1 行。SQL> DELETE 2 FROM SC 3 WHERE SNO =''已删除2行。SQL> SELECT * FROM S;SNO SNAME SS SAGE- - - -SDEPT- 李勇 男 20CS20204 王敏 女 18MA30203 张立 男 19ISSQL> SELECT * FROM S;SNO SNAME SS SAGE- - - -SDEPT- 李勇 男 20CS20204 王敏 女 18MA30203 张立 男 19ISSQL> SELECT * FROM SC;SNO CNO GRADE- - - 2 85 3 88实验四：视图的操作 1建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。Create view gstudent(sno,sname,cname,grade)ASSelectS.Sno,Sname,C.Cname,Gradefrom S,SC,Cwhere S.Sno=SC.Sno and SC.Cno=C.Cno and Ssex=1;2在男学生视图中查询平均成绩大于80分的学生学号与姓名。select sno,sname from Swhere sno in(select sno from gstudent group by sno having avg(grade)>80 );Notice: select sno,sname from gstudent group by sno; is wrong select sno,count(*) from gstudent group by sno; is true实验五：库函数，授权控制 1 计算每个学生有成绩的课程门数、平均成绩。select count(*),avg(grade)from SCwhere grade is not nullgroup by sno;2 使用GRANT语句，把对基本表S、SC、C的使用权限授给其它用户。GRANT ALL PRIVILEGESON TABLE S,SC,CTO PUBLIC;3实验完成后，撤消建立的基本表和视图。DROP TABLE S CASCADE;DROP TABLE SC CASCADE;DROP TABLE C CASCADE;DROP VIEW gstudent CASCADE;实验六：综合实验：实现一个小型管理信息系统 熟练掌握Visual C+和Pro*C访问数据库的方法，设计和实现学生通讯录或学生选课的一个小型管理信息系统。要求具有数据的增加、删除、修改和查询的基本功能，并尽可能提供较多的查询功能，用户界面要友好。课程结束前提交实验报告和程序。一、实验平台 操作系统：Windows XP 开发工具：Microsoft Visual Studio Pro*C/C+ 数据库：Oracle 9i二、系统介绍本系统功能模块划分：（1）对数据的浏览模块；（2）对数据的查询模块； (3) 对数据的添加模块；(4) 对数据的修改模块； 数据库部分包含一张表是addresslist其中建立表对应的SQL语句如下： 本通讯录会自动在没有表的情况下建立表三、实验步骤：1. 编写程序2. 通过Pro*C/C+预编译3. 用VC+编译4. 运行试用四、实验结果：1：登录界面2：所有信息3：插入界面4：删除界面5：修改界面6：查询界面五、实验心得： 本次实验在老师的指导下收获良多，pro*c程序经预编译后就能转变成C+程序了，在vc+工具中配置pro*c工具后就能编译，其实预编译可以不通过PROJECT.六、实验代码（预编译前的）：#include <iostream.h>#include <cstring>#include <stdio.h>#include <sqlca.h>#include <stdlib.h>void sql_error(char *msg)cout<<msg<<endl;return;int main()cout<<"*"<<endl;cout<<"*学生通讯录*"<<endl;cout<<"*"<<endl;int t,n;char m;EXEC SQL BEGIN DECLARE SECTION; char username20;char pASsword20;char server10;char schoolnum10;char name20;char roomnum30;char roomphone17;char mobilephone17;char homephone17;char qqnum12;char email22;char remark22;char temp 10;EXEC SQL END DECLARE SECTION;cout<<"输入用户名:"cin>>username;cout<<endl<<"输入密码:"cin>>pASsword;cout<<endl<<"输入服务器名:"cin>>server;EXEC SQL CONNECT:username IDENTIFIED BY :pASsword USING :server;if(sqlca.sqlcode=0)cout<<"Connect success!"<<endl;elseprintf("%.*sn",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);cout<<"Connected to ORACLE AS user:"<<username<<endl;EXEC SQL CREATE TABLE addresslist ( schoolnum varchar(9) PRIMARY KEY, name varchar(18) , roomnum varchar(28) , roomphone varchar(15) , mobilephone varchar(15) , homephone varchar(15) , qqnum varchar(10) , email varchar(40) , remark varchar(40) ); while(1)docout<<endl;cout<<"*"<<endl;cout<<" 请选择你要进行的操作"<<endl;cout<<" 0:所有信息"<<endl; cout<<" 1:插入数据"<<endl; cout<<" 2:删除数据"<<endl; cout<<" 3:修改数据"<<endl; cout<<" 4:查询"<<endl; cout<<" 5: 退出"<<endl;cout<<"*"<<endl;cin>>t;while(t>5|t<0); switch(t)cASe 0: EXEC SQL DECLARE tmp0 CURSOR FOR SELECT schoolnum,name,roomnum,roomphone,mobilephone,homephone,qqnum,email,remark FROM addresslist; EXEC SQL OPEN tmp0;EXEC SQL WHENEVER NOT FOUND DO break; for (;) EXEC SQL FETCH tmp0 INTO :schoolnum,:name,:roomnum,:roomphone,:mobilephone,:homephone,:qqnum,:email,:remark; cout<<"schoolnum="<<schoolnum<<endl; cout<<"name="<<name<<endl; cout<<"roomnum="<<roomnum<<endl; cout<<"roomphone="<<roomphone<<endl; cout<<"mobilephone="<<mobilephone<<endl; cout<<"homephone="<<homephone<<endl; cout<<"qqnum="<<qqnum<<endl; cout<<"email="<<email<<endl; cout<<"remark="<<remark<<endl; cout<<"."<<endl; EXEC SQL CLOSE tmp0; cout<<"按任意键返回" cin.ignore(); cin.get(m); system("cls"); break; cASe 1: system("cls"); cout<<endl<<"输入学号schoolnum:" cin>>schoolnum; cout<<endl<<"输入姓名name:" cin>>name; cout<<endl<<"输入宿舍号roomnum:" cin>>roomnum; cout<<endl<<"输入宿舍电话roomphone:"cin>>roomphone; cout<<endl<<"输入手机号mobilephone:"cin>>mobilephone; cout<<endl<<"输入家里电话homephone:"cin>>homephone; cout<<endl<<"输入qqnum:"cin>>qqnum; cout<<endl<<"输入email:"cin>>email; cout<<endl<<"输入备注remark:"cin>>remark;EXEC SQL INSERT INTO addresslist(schoolnum,name,roomnum,roomphone,mobilephone,homephone,qqnum,email,remark)values(:schoolnum,:name,:roomnum,:roomphone,:mobilephone,:homephone,:qqnum,:email,:remark);EXEC SQL COMMIT; cout<<"按任意键返回" cin.ignore(); cin.get(m); system("cls"); break;cASe 2: system("cls"); cout<<"按什么删除?学号=1OR姓名=2"<<endl; cin>>n; switch(n) cASe 1: cout<<"输入学号schoolnum:"<<endl; cin>>schoolnum; EXEC SQL DELETE FROM addresslist WHERE schoolnum=:schoolnum; break; cASe 2: cout<<"输入姓名name:"<<endl; cin>>name; EXEC SQL DELETE FROM addresslist WHERE name=:name; break; cout<<"按任意键返回" cin.ignore(); cin.get(m); system("cls"); break;cASe 3: system("cls"); cout<<"输入要修改的学生学号schoolnum"<<endl; cin>>temp; cout<<"输入修改后的数据:"<<endl; cout<<endl<<"输入学号schoolnum:" cin>>schoolnum; cout<<endl<<"输入姓名name:" cin>>name; cout<<endl<<"输入宿舍号roomnum:" cin>>roomnum; cout<<endl<<"输入宿舍电话roomphone:"cin>>roomphone; cout<<endl<<"输入手机号mobilephone:"cin>>mobilephone; cout<<endl<<"输入家里电话homephone"cin>>homephone; cout<<endl<<"输入qqnum:"cin>>qqnum; cout<<endl<<"输入email:"cin>>email; cout<<endl<<"输入备注remark:"cin>>remark; EXEC SQL UPDATE addresslist SET schoolnum= :schoolnum, name= :name, roomnum= :roomnum, roomphone= :roomphone, mobilephone= :mobilephone, homephone= :homephone, qqnum= :qqnum, email= :email, remark= :remark WHERE schoolnum=:temp; cout<<"按任意键返回" cin.ignore(); cin.get(m); system("cls"); break; cASe 4: system("cls"); cout<<"按什么查询?学号=1OR姓名=2"<<endl; cin>>n; switch(n) cASe 1: cout<<"输入要查询的学生学号schoolnum"<<endl;
