数据库第一章实验报告

1、数据库原理及应用实验报告题目：实验四、五、六 SQL语言（视图、数据控制、空值处理） 学号： 姓名： 么红帅 专业班级： 软件131 指导教师： 张满囤 完成日期： 2015年11月9日 实验1.1 数据定义一、实验目的熟悉SQL的数据定义语言，能够熟练地使用SQL语句来创建和更改基本表，创建和取消索引。二、实验内容(1)创建数据库表CUSTOMERS(CID,CNAME,CITY,DISCNT)、数据库表AGENTS(AID,ANAME,CITY,PERCENT)、数据库表PRODUCTS(PID,PNAME)。其中CID、AID、PID分表是各表的主键，具有唯一性约束。(2)创建数据库表O

2、RDERS(ORDNA,MONTH,CID,AID,PID,QTY,DOLLARS)。其中ORDNA分表是主键，具有唯一性约束。CID、AID、PID分别是外键引用自表CUSTOMERS、表AGENTS,表PRODUCTS。(3)增加数据库表PRODUCTS三个属性列：CITY、QUANTITY、PRICE。(4)为以上四个表建立各自的按主键增序排列的序列。(5)取消（4）建立的四个索引。三、实验结果(1)CREATE TABLE CUSTOMERS(CID CHAR(8) UNIQUE,CNAME CHAR(20),CITY CHAR(20),DISCNT INT,PRIMARY KEY(C

3、ID)CREATE TABLE AGENTS(AID CHAR(8) UNIQUE,ANAME CHAR(20),CITY CHAR(20),PERCENT FLOAT,PRIMARY KEY(AID)CREATE TABLE PRODUCTS(PID CHAR(8) UNIQUE,PANME CHAR(20),PRIMARY KEY(PID)(2)CREATE TABLE ORDERS(ORDNA CHAR(8) UNIQUE,MONTH INT,CID CHAR(8) UNIQUE,AID CHAR(8) UNIQUE,PID CHAR(8) UNIQUE,QTY INT,DOLLARS

4、FLOAT,PRIMARY KEY(ORDNA),FOREIGN KEY(CID) REFERENCES CUSTOMERS,FOREIGN KEY(AID) REFERENCES AGENTS,FOREIGN KEY(PID) REFERENCES PRODUCTS)(3)ALTER TABLE PRODUCTS ADD CITY CHAR(20)ALTER TABLE PRODUCTS ADD QUANTITY INTALTER TABLE PRODUCTS ADD PRICE FLOAT(4)CREATE INDEX XSNO ON CUSTOMERS(CID)CREATE INDEX

5、XSNO ON AGENTS(AID)CREATE INDEX XSNO ON PRODUCTS(PID)CREATE INDEX XSNO ON ORDERS(ORDNA)(5)DROP INDEX CUSTOMERS.XSNODROP INDEX AGENTS.XSNODROP INDEX PRODUCTS.XSNODROP INDEX ORDERS.XSNO实验1.2 数据查询一、 实验目的熟悉SQL语句的数据查询语言，能够使用SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。二、 实验内容(1)查询全部课程的详细记录。(2)查询所有有选课的学生的编号。(3)查询课

6、时80（小时）的课程的编号。(4)请找出总分超过400分的编号(5)查询课程的总数。(6)查询所有课程和选修该课程的学生总数。(7)查询选修成绩合格的课程超过两门的学生的编号。(8)统计各个学生的选修课程数目和平均成绩。(9)查询选修java的所有学生的编号及姓名。(10)分别使用等值连接和谓词IN两种方式查询姓名为sssht的学生所选的课程的编号和成绩。(11) 查询课时比c+多的课程的名称。(12)查询选修c+课程的成绩比姓名为znkoo学生高的所有学生的编号和姓名。(13)找出和学生或学生的年级一样的学生的姓名。(14)查询没有选修java的学生的名称。(15) 查询课时最少的课程的详细

7、信息。(16)查询工资最高的教师的编号和开设的课号。(17)找出选修课程ERP成绩最高的学生编号。(18)查询没有学生选的课程的名称。(19)找出讲授课程UML的教师讲授的所有课程名称。(20查询选修了编号的教师开设所有课程的学生编号。(21)查询选修课程database的学生集合与选修UML的学生集合的并集。(22)实现集合交运算，查询级选修课程database又选修UML的学生的编号。(23)实现集合减运算，查询级选修课程database而又没有选修UML的学生的编号。三、实验结果(1)SELECT *FROM COURSES;(2)SELECT sidFROM CHOICES;(3)SE

8、LECT cidFROM COURSESWHERE hour400;(5)SELECT COUNT(cid)FROM COURSES;(6)SELECT cid,COUNT(sid)FROM CHOICESGROUP BY cid;(7)SELECT sidFROM CHOICESWHERE score60GROUP BY sidHAVING COUNT(cid)2;(8)SELECT sid,COUNT(cid),AVG(score)FROM CHOICESGROUP BY sid;(9)SELECT sid,snameFROM STUDENTSWHERE sid IN(SELECT sid

9、 FROM CHOICES WHERE cid=(SELECT cidFROM COURSESWHERE cname=Java);(10)等值连接：SELECT cid,scoreFROM STUDENTS,CHOICESWHERE STUDENTS.sid=CHOICES.sid AND sname=sssht;谓词IN：SELECT cid,scoreFROM CHOICESWHERE sid IN(SELECT sid FROM STUDENTS WHERE sname=sssht);(11)SELECT cnameFROM COURSESWHERE hour(SELECT hour F

10、ROM COURSES WHERE cname=C+);(12)SELECT sid,snameFROM STUDENTSWHERE sid IN(SELECT C1.sid FROM CHOICES C1,CHOICES C2 WHERE C1.scoreC2.score AND C1.cid=C2.cid AND C1.cid=(SELECT cid FROM COURSES WHERE cname=c+) AND C2.sid=(SELECT sid FROM STUDENTS WHERE sname=znkoo);(13)SELECT snameFROM STUDENTSWHERE g

11、rade IN(SELECT grade FROM STUDENTS WHERE sid= OR sid=);(14)SELECT snameFROM STUDENTSWHERE sid NOT IN(SELECT sid FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname=Java);(15)SELECT *FROM COURSESWHERE hour=(SELECT MIN(hour) FROM COURSES);(16)SELECT tid,cidFROM CHOICESWHERE tid IN(SELECT tid F

12、ROM TEACHERS WHERE salary=(SELECT MAX(salary) FROM TEACHERS);(17)SELECT sidFROM CHOICESWHERE score=(SELECT MAX(score) FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname=ERP) AND cid=(SELECT cid FROM COURSES WHERE cname=ERP);(18)SELECT cnameFROM COURSESWHERE cid NOT IN(SELECT cid FROM CHOICE

13、S);(19)SELECT cnameFROM COURSESWHERE cid IN(SELECT cid FROM CHOICES WHERE tid IN(SELECT tid FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname=UML); (20)SELECT sidFROM CHOICESWHERE cid IN(SELECT cid FROM CHOICES WHERE tid=)GROUP BY sidHAVING COUNT(*)=(SELECT COUNT(DISTINCT cid) FROM CHOICES

14、 WHERE tid=);(21)SELECT sidFROM CHOICESWHERE cid=(SELECT cid FROM COURSES WHERE cname=Database)UNIONSELECT sidFROM CHOICESWHERE cid=(SELECT cid FROM COURSES WHERE cname=UML);(22)SELECT C1.sidFROM CHOICES C1,CHOICES C2WHERE C1.cid=(SELECT cid FROM COURSES WHERE cname=Database)AND C2.cid=(SELECT cid F

15、ROM COURSES WHERE cname=UML)AND C1.sid=C2.sid; (23)SELECT C1.sidFROM CHOICES C1,CHOICES C2WHERE C1.cid=(SELECT cid FROM COURSES WHERE cname=Database)AND C1.sid=C2.sidAND C2.cid(SELECT cid FROM COURSES WHERE cname=UML);实验1.3 数据更新一、 实验目的熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、更新、删除操作二、 实验内容(1)向STUDENTS表插入编号

16、是且姓名是WangLan的元组。(2)向 TEACHERS表插入元组（，LXL，，3024）(3)将 TEACHERS表中编号为的老师工资改为4000。(4)将 TEACHERS表中所有工资小于2500的老师工资改为2500。(5)将有编号老师讲授的课程全部改成姓名rnupx的老师讲授。(6)更新编号的学生年纪为2001。(7)删除没有学生选修的课程。(8)删除年级高于1998的学生信息。(9)删除没有选修课程的学生信息。(10)删除成绩不及格的选课记录。三、实验结果(1)INSERTINTO STUDENTS(sid,sname)VALUES (,WangLan);

17、(2)INSERTINTO TEACHERSVALUES (,LXL,,3024);(3)UPDATE TEACHERSSET salary=4000WHERE tid=;(4)UPDATE TEACHERSSET salary=2500WHERE salary2500;(5)UPDATE CHOICESSET tid=(SELECT tid FROM TEACHERS WHERE tname=rnupx)WHERE tid=;(6)UPDATE STUDENTSSET grade=2001WHERE sid=;(7)DELETEFROM COURSESWHERE c

18、id NOT IN(SELECT DISTINCT cid FROM CHOICES);(8)DELETEFROM STUDENTSWHERE grade1998; (9)DELETEFROM STUDENTSWHERE sid NOT IN (SELECT DISTINCT sid FROM CHOICES);(10)DELETEFROM CHOICESWHERE score1998;(4) 查询学生为“uxjof”的学生的选课信息；SELECT *FROM VIEWSWHERE sname=uxjof;(5) 查询选修课程“UML”的学生的编号和成绩；SELECT sid,scoreFRO

19、M VIEWCWHERE cname=UML;(6) 向视图S1插入记录(“，Lily，2001”)；INSERTINTO S1VALUES (,Lily,2001);(7) 定义包括更新和插入约束的视图S1，尝试向视图插入记录(“，Lily，1997”)，删除所有年级为1999的学生记录，讨论更新和插入约束带来的影响。定义视图：CREATE VIEW S1(sid,sname,grade)ASSELECT sid,sname,gradeFROM STUDENTSWHERE grade1998WITH CHECK OPTION;插入元组：INSERTINTO S1VALUES(,Lily,19

20、97);执行结果：消息550，级别16，状态1，第1 行试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。语句已终止。结果分析：WITH CHECK OPTION表示对视图进行操作时要满足子查询的条件表达式，grade=1997本题的条件grade1998不符，因此在本题中插入这个元组是不成功的。删除元组：DELETEFROM S1WHERE grade=1999;执行结果：删除成功(8) 在视图VIEWS中将姓名为“uxjof”的学生的选课成绩都加上5分。UPDAT

21、E VIEWSSET score=score+5WHERE sname=uxjof;(9) 取消以上建立的所有视图。DROP VIEW VIEWCDROP VIEW VIEWSDROP VIEW S1;实验1.5 数据控制一、 实验目的熟悉SQL的数据控制功能，能够使用SQL语句来向用户授予和收回权限。二、 实验内容(1) 授予所有用户对表STUDENTS的查询权限。GRANT SELECTON STUDENTSTO PUBLIC;(2) 授予所有用户对表COURSES的查询和更新权限。GRANT SELECT,UPDATEON COURSESTO PUBLIC;(3) 授予USER1对表TE

22、ACHERS的查询，更新工资的权限，且允许USER1可以传播这些权限。GRANT SELECT,UPDATE(salary)ON TEACHERSTO USER1WITH GRANT OPTION;(4) 授予USER2对表CHOICES的查询，更新成绩的权限。GRANT SELECT,UPDATE(score)ON CHOICESTO USER2;(5) 授予USER2对表TEACHERS的除了工资之外的所有信息的查询。CREATE VIEW TVASSELECT tid,tname,emailFROM TEACHERS;GRANT SELECTON TVTO UESR2;(6) 由USER

23、1授予USER2对表TEACHERS的查询权限和传播的此项权限的权利。GRANT SELECTON TEACHERSTO USER2WITH GRANT OPTION;(7) 由USER2授予USER3对表TEACHERS的查询权限，和传播的此项权限的权利。再由USER3授予USER2上述权限，这样的SQL语句能否成功得到执行?GRANT SELECTON TEACHERSTO USER3WITH GRANT OPTION;GRANT SELECTON TEACHERSTO USER2WITH GRANT OPTION;(8) 取消USER1对表STUDENTS的查询权限，考虑由USER2的身

24、份对表STUDENTS进行查询，操作能否成功？为什么？REVOKE SELECTON TEACHERSFROM UESR1 CASCADE;操作不成功，取消授权操作的时候存在级联效应。(9) 取消USER1和USER2的关于表COURSES的权限。REVOKE SELECT,UPDATEON COURSESFROM UESR1,UESR2;实验1.6 空值和空集的处理一、实验目的认识NULL值在数据库中的特殊含义，了解空值和空集对于数据库的数据查询操作，特别是空值在条件表达式中与其他的算术运算符或逻辑运算符的运算中，空集作为嵌套查询的子查询返回结果的时候的特殊性，能够熟练使用SQL语句来进行与

25、空值，空集相关的操作。二、实验内容(1) 查询所有课程记录的上课学时（数据库中为每星期学时），以一学期十八个星期计算每个课程的总学时，注意HOUR取NULL值的情况。SELECT cid,hour*18FROM COURSES;(2) 通过查询选修课程C+的学生的人数，其中成绩合格的学生人数，不合格的学生人数，讨论NULL值得特殊含义。查询选修C+的学生人数：SELECT COUNT(*)FROM COURSES,CHOICESWHERE CHOICES.cid=COURSES.cid AND cname=C+;运行结果为：95成绩合格的学生人数：SELECT COUNT(*)FROM COU

26、RSES,CHOICESWHERE CHOICES.cid=COURSES.cid AND cname=C+ AND score=60;运行结果为：0NULL与比较运算符的运算都返回FALSE值，都不会出现在结果的统计之中。(3) 查询选修课程C+的学生的编号和成绩，使用ORDER BY按成绩进行排序时，取NULL的项是否出现在结果中？如果有，在什么位置？SELECT sid,scoreFROM CHOICESWHERE cid=(SELECT cid FROM COURSES WHERE cname=C+)ORDER BY score;NULL的项出现在结果中，被当作最小值处理。(4) 在上面的查询的过程中，如果加上保留字DISTINCT会有什么效果呢？SELECT DISTINCT scoreFROM CHOICESWHERE cid =(SELECT cid FROM COURSES WHERE cname=C+)ORDER BY score;成绩为NULL的学生被合并(5) 按年级对所有的学生进行分组，能得到多少个组？与现实的情况有什么不同？SELECT gradeFROM STUDENTSGROUP BY grade;得到9个分组，现实中有8个年级。(6