SQL语言实验.doc

计算机科学与工程学院 武 汉 工 程 大 学计算机科学与工程学院数据库系统原理实验报告专业班级信息技术03班实验地点J411学生学号0905060322指导教师丁杰敏学生姓名张东梅实验时间周五实验项目SQL语句的使用实验类别操作性（） 验证性（） 设计性（ ） 综合性（） 其它（ ）实验目的及要求1.掌握SELECT语句的基本语法。2.掌握子查询的表示。3.掌握连接查询的表示。4.掌握数据汇总的方法。5.掌握SELECT语句的GROUPBY子句的作用和使用方法。6掌握SELECT语句的ORDERBY子句的作用和使用方法。7掌握视图的概念及创建方法。8掌握安全性控制策略的实施方法。成 绩 评 定 表类 别评 分 标 准分值得分合 计上机表现积极出勤、遵守纪律主动完成实验设计任务30分程序代码比较规范、基本正确功能达到实验要求30分实验报告及时递交、填写规范内容完整、体现收获40分 评阅教师： 日 期： 年 月 日实 验 内 容一、对实验一的JXGL数据库，完成下列各查询语句。1. 查询计算机系的所有学生的信息。select *from STUDENT where Sdept=CS2. 查询所有姓李的学生的详细信息。select *from STUDENT where Sname LIKE李%3. 查询所有年龄在19到25之间的学生的详细信息。并按年龄由低到高的顺序排列。 select *from STUDENTwhere Sage between 19 and 25order BY Sage ASC4. 查询选修了课程的学生的学号及姓名。select DISTINCT STUDENT.Sno,Snamefrom STUDENT,SCwhere SC.Sno=STUDENT.Sno5. 查询所有选修现代数据库技术这门课程的学生成绩信息，显示学号、姓名、成绩，并按成绩的高低顺序排列。 SELECT STUDENT.Sno,Sname,SC.Gradefrom STUDENT,SC,COURSEwhere STUDENT.Sno=SC.Sno AND SC.Cno=COURSE.Cno AND COURSE.Cname=数据库6. 按系统计男、女生人数。select count(DISTINCT Sno)as人数,Sdept,Ssex from STUDENTGROUP BY Ssex,Sdept7. 查询每门课程的最高分、最低分及平均分。SELECT COURSE.Cname,MIN(Grade),MAX(Grade),AVG(Grade)from SC,COURSEwhere SC.Cno=COURSE.CnoGROUP BY COURSE.Cname8. 查询选修了三门以上课程的学生的学号、姓名及课程门数。SELECT STUDENT.Sno,Sname,count(*)as人数FROM SC,STUDENTWHERE SC.Sno=STUDENT.Snogroup by STUDENT.Sno,Snamehaving count(*)=3一、对实验一的JXGL数据库，完成下列各查询语句。9. 查询计算机系的所有学生的信息。select *from STUDENT where Sdept=CS10. 查询所有姓李的学生的详细信息。select *from STUDENT where Sname LIKE李%11. 查询所有年龄在19到25之间的学生的详细信息。并按年龄由低到高的顺序排列。 select *from STUDENTwhere Sage between 19 and 25order BY Sage ASC12. 查询选修了课程的学生的学号及姓名。select DISTINCT STUDENT.Sno,Snamefrom STUDENT,SCwhere SC.Sno=STUDENT.Sno13. 查询所有选修现代数据库技术这门课程的学生成绩信息，显示学号、姓名、成绩，并按成绩的高低顺序排列。 SELECT STUDENT.Sno,Sname,SC.Gradefrom STUDENT,SC,COURSEwhere STUDENT.Sno=SC.Sno AND SC.Cno=COURSE.Cno AND COURSE.Cname=数据库14. 按系统计男、女生人数。select count(DISTINCT Sno)as人数,Sdept,Ssex from STUDENTGROUP BY Ssex,Sdept15. 查询每门课程的最高分、最低分及平均分。SELECT COURSE.Cname,MIN(Grade),MAX(Grade),AVG(Grade)from SC,COURSEwhere SC.Cno=COURSE.CnoGROUP BY COURSE.Cname16. 查询选修了三门以上课程的学生的学号、姓名及课程门数。SELECT STUDENT.Sno,Sname,count(*)as人数FROM SC,STUDENTWHERE SC.Sno=STUDENT.Snogroup by STUDENT.Sno,Snamehaving count(*)=317. 查询既选修了现代数据库技术，又选修网络操作系统这两门课程的学生的详细信息。select *from STUDENT,sc,courseWHERE student.Sno in(select SC.sno from STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND CNAME=现代数据库技术)AND STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND CNAME=操作系统18. 查询计算机系选修了所有课程的学生的信息。select *from studentwhere sdept=cs and not exists (select * from course where not exists(select * from sc where sno=student.sno and cno=o)19. 查询计算机系没有选修现代数据库技术这门课程的学生信息。SELECT *FROM STUDENT WHERE Sdept=CS AND Sno NOT IN (SELECT s.SnoFROM STUDENT s, SC, COURSE cWHERE s.Sno=SC.Sno AND c.Cno=SC.CnoAND c.Cname=现代数据库技术) 二、视图的操作1创建“计算机系”所有学生的视图。并通过视图修改学生的信息。create view S1ASselect *from studentwhere sdept=cs UPDATE IS_CS set Sname=张三 WHERE Sno=2002151222创建所有选修“现代数据库技术”这门课程的学生及成绩信息。显示学号、姓名、系别、成绩信息。是否能修改视图中信息。 CREATE VIEW S2ASSELECT s.Sno,sname,Sdept,GradeFROM STUDENT s,SCWHERE s.Sno=SC.Sno三、安全性控制的实施使用JXGL的人员有：学生：李那、黎民、刘敏、王强；任课教师：张行、小叶教务管理人员：汪辉。其中学生只可以查看库中任何一张表的任何内容；教师可以查看任何一张表的内容，还可以修改自己所带学生的成绩（假设张行带1号课程，小叶带2号课程）；教务管理人员可以查看任何一张表，对表做任何更新操作。创建各用户，并授予权限。创建用户： 学生权限：GRANT SELECT ON STUDENT To 李那,黎民,刘敏,王强GRANT SELECT ON COURSE To 李那,黎民,刘敏,王强GRANT SELECT ON SCTo 李那,黎民,刘敏,王强教师权限：GRANT SELECTON STUDENTTO 小叶,张行CREATE VIEW SC_1AS SELECT * FROM SCWHERE Cno=1GRANT SELECT ,UPDATE,INSERT,DELETEON SC_1To 小叶CREATE VIEW SC_1AS SELECT * FROM SCWHERE Cno=1GRANT SELECT ,UPDATE,INSERT,DELETEON SC_2To CREATE VIEW SC_1AS SELECT * FROM SCWHERE Cno=2GRANT SELECT ,UPDATE,INSERT,DELETEON SC_2To 张行管理员权限：GRANT SELECT ,UPDATE,INSERT,DELETEON STUDENTTo 汪辉GRANT SELECT ,UPDATE,INSERT,DELETEON COURSETo 汪辉GRANT SELECT ,UPDATE,INSERT,DELETEON SCTo 汪辉实 验 总 结由于实验课时间有限自己的电脑不会安装软件，所以我没有截图直接写的各种要求。大概的都是可以实现的。通过这次的实验，巩固了SELECT语句的用法，还掌握了各种情况下的查询模式。例如：子查询、连接查询、数据汇总、SELECT语句的GROUPBY子句、ORDERBY