PAGE 2
《数据库原理及应用》
实验报告
题目：实验四、五、六 SQL语言（视图、数据控制、空值处理）
学号： 132935
姓名： 么红帅
专业班级： 软件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) 创建数据库表ORDERS(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(CID))
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 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 INT
ALTER TABLE PRODUCTS ADD PRICE FLOAT
(4)
CREATE INDEX XSNO ON CUSTOMERS(CID)
CREATE INDEX XSNO ON AGENTS(AID)
CREATE INDEX XSNO ON PRODUCTS(PID)
CREATE INDEX XSNO ON ORDERS(ORDNA)
(5)
DROP INDEX CUSTOMERS.XSNO
DROP INDEX AGENTS.XSNO
DROP INDEX PRODUCTS.XSNO
DROP INDEX ORDERS.XSNO
实验1.2 数据查询
实验目的
熟悉SQL语句的数据查询语言，能够使用SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。
实验内容
(1)查询全部课程的详细记录。
(2)查询所有有选课的学生的编号。
(3)查询课时<80（小时）的课程的编号。
(4)请找出总分超过400分的编号
(5)查询课程的总数。
(6)查询所有课程和选修该课程的学生总数。
(7)查询选修成绩合格的课程超过两门的学生的编号。
(8)统计各个学生的选修课程数目和平均成绩。
(9)查询选修java的所有学生的编号及姓名。
(10)分别使用等值连接和谓词IN两种方式查询姓名为sssht的学生所选的课程的编号和成绩。
(11) 查询课时比c++多的课程的名称。
(12)查询选修c++课程的成绩比姓名为znkoo学生高的所有学生的编号和姓名。
(13)找出和学生883794999或学生850955252的年级一样的学生的姓名。
(14)查询没有选修java的学生的名称。
(15) 查询课时最少的课程的详细信息。
(16)查询工资最高的教师的编号和开设的课号。
(17)找出选修课程ERP成绩最高的学生编号。
(18)查询没有学生选的课程的名称。
(19)找出讲授课程UML的教师讲授的所有课程名称。
(20查询选修了编号200102901的教师开设所有课程的学生编号。
(21)查询选修课程database的学生集合与选修UML的学生集合的并集。
(22)实现集合交运算，查询级选修课程database又选修UML的学生的编号。
(23)实现集合减运算，查询级选修课程database而又没有选修UML的学生的编号。
三、实验结果
(1)
SELECT *
FROM COURSES;
(2)
SELECT sid
FROM CHOICES;
(3)
SELECT cid
FROM COURSES
WHERE hour<88;
(4)
SELECT sid
FROM CHOICES
GROUP BY sid HAVING SUM(score)>400;
(5)
SELECT COUNT(cid)
FROM COURSES;
(6)
SELECT cid,COUNT(sid)
FROM CHOICES
GROUP BY cid;
(7)
SELECT sid
FROM CHOICES
WHERE score>60
GROUP BY sid
HAVING COUNT(cid)>2;
(8)
SELECT sid,COUNT(cid),AVG(score)
FROM CHOICES
GROUP BY sid;
(9)
SELECT sid,sname
FROM STUDENTS
WHERE sid IN
(SELECT sid
FROM CHOICES
WHERE cid=(SELECT cid
FROM COURSES
WHERE cname='Java'));
(10)
等值连接：
SELECT cid,score
FROM STUDENTS,CHOICES
WHERE STUDENTS.sid=CHOICES.sid AND sname='sssht';
谓词IN：
SELECT cid,score
FROM CHOICES
WHERE sid IN
(SELECT sid
FROM STUDENTS
WHERE sname='sssht');
(11)
SELECT cname
FROM COURSES
WHERE hour>
(SELECT hour
FROM COURSES
WHERE cname='C++');
(12)
SELECT sid,sname
FROM STUDENTS
WHERE sid IN
(SELECT C1.sid
FROM CHOICES C1,CHOICES C2
WHERE C1.score>C2.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 sname
FROM STUDENTS
WHERE grade IN
(SELECT grade
FROM STUDENTS
WHERE sid='883794999' OR sid='850955252');
(14)
SELECT sname
FROM STUDENTS
WHERE sid NOT IN
(SELECT sid
FROM CHOICES
WHERE cid=
(SELECT cid
FROM COURSES
WHERE cname='Java'));
(15)
SELECT *
FROM COURSES
WHERE hour=(SELECT MIN(hour) FROM COURSES);
(16)
SELECT tid,cid
FROM CHOICES
WHERE tid IN
(SELECT tid
FROM TEACHERS
WHERE salary=
(SELECT MAX(salary)
FROM TEACHERS));
(17)
SELECT sid
FROM CHOICES
WHERE 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 cname
FROM COURSES
WHERE cid NOT IN
(SELECT cid
FROM CHOICES);
(19)
SELECT cname
FROM COURSES
WHERE cid IN
(SELECT cid
FROM CHOICES
WHERE tid IN
(SELECT tid
FROM CHOICES
WHERE cid=
(SELECT cid
FROM COURSES
WHERE cname='UML')));
(20)
SELECT sid
FROM CHOICES
WHERE cid IN
(SELECT cid
FROM CHOICES
WHERE tid='200102901')
GROUP BY sid
HAVING COUNT(*)=
(SELECT COUNT(DISTINCT cid)
FROM CHOICES
WHERE tid='200102901');
(21)
SELECT sid
FROM CHOICES
WHERE cid=
(SELECT cid
FROM COURSES
WHERE cname='Database')
UNION
SELECT sid
FROM CHOICES
WHERE cid=
(SELECT cid
FROM COURSES
WHERE cname='UML');
(22)
SELECT C1.sid
FROM CHOICES C1,CHOICES C2
WHERE C1.cid=(SELECT cid FROM COURSES WHERE cname='Database')
AND C2.cid=(SELECT cid FROM COURSES WHERE cname='UML')
AND C1.sid=C2.sid;
(23)
SELECT C1.sid
FROM CHOICES C1,CHOICES C2
WHERE C1.cid=(SELECT cid FROM COURSES WHERE cname='Database')
AND C1.sid=C2.sid
AND C2.cid<>(SELECT cid FROM COURSES WHERE cname='UML');
实验1.3 数据更新
实验目的
熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、更新、删除操作
实验内容
(1) 向STUDENTS表插入编号是800022222且姓名是WangLan的元组。
(2) 向 TEACHERS表插入元组（200001000，LXL，s4zrck@pew.net，3024）
(3) 将 TEACHERS表中编号为200010493的老师工资改为4000。
(4) 将 TEACHERS表中所有工资小于2500的老师工资改为2500。
(5) 将有编号200016731老师讲授的课程全部改成姓名rnupx的老师讲授。
(6) 更新编号800071780的学生年纪为2001。
(7) 删除没有学生选修的课程。
(8) 删除年级高于1998的学生信息。
(9) 删除没有选修课程的学生信息。
(10) 删除成绩不及格的选课记录。
三、实验结果
(1)
INSERT
INTO STUDENTS(sid,sname)
VALUES ('800022222','WangLan');
(2)
INSERT
INTO TEACHERS
VALUES ('200001000','LXL','s4zrck@pew.net',3024);
(3)
UPDATE TEACHERS
SET salary=4000
WHERE tid='200010493';
(4)
UPDATE TEACHERS
SET salary=2500
WHERE salary<2500;
(5)
UPDATE CHOICES
SET tid=(SELECT tid FROM TEACHERS WHERE tname='rnupx')
WHERE tid='200016721';
(6)
UPDATE STUDENTS
SET grade='2001'
WHERE sid='800071780';
(7)
DELETE
FROM COURSES
WHERE cid NOT IN
(SELECT DISTINCT cid
FROM CHOICES);
(8)
DELETE
FROM STUDENTS
WHERE grade<1998;
(9)
DELETE
FROM STUDENTS
WHERE sid NOT IN
(SELECT DISTINCT sid
FROM CHOICES);
(10)
DELETE
FROM CHOICES
WHERE score<60;
软件131-么红帅-132935
PAGE 14
实验1.4 视图
实验目的
熟悉SQL支持的有关视图的操作，能够熟练地使用SQL语句来创建需要的视图，对视图进行查询和取消视图。
实验内容
(1) 定义选课信息和课程名称的视图VIEWC；
CREATE VIEW VIEWC
AS
SELECT CHOICES.no,CHOICES.sid,CHOICES.tid,CHOICES.score,COURSES.cname
FROM CHOICES,COURSES
WHERE CHOICES.cid=COURSES.cid;
(2) 定义学生姓名与选课信息的视图VIEWS；
CREATE VIEW VIEWS
AS
SELECT STUDENTS.sname,CHOICES.no,CHOICES.tid,CHOICES.cid,CHOICES.score
FROM STUDENTS,CHOICES
WHERE STUDENTS.sid=CHOICES.sid;
(3) 定义年级低于1998的学生的视图S1(SID，SNAME，GRADE)；
CREATE VIEW S1(sid,sname,grade)
AS
SELECT sid,sname,grade
FROM STUDENTS
WHERE grade>1998;
(4) 查询学生为“uxjof”的学生的选课信息；
SELECT *
FROM VIEWS
WHERE sname='uxjof';
(5) 查询选修课程“UML”的学生的编号和成绩；
SELECT sid,score
FROM VIEWC
WHERE cname='UML';
(6) 向视图S1插入记录(Lily，2001”)；
INSERT
INTO S1
VALUES (,'Lily',2001);
(7) 定义包括更新和插入约束的视图S1，尝试向视图插入记录(Lily，1997”)，删除所有年级为1999的学生记录，讨论更新和插入约束带来的影响。
定义视图：
CREATE VIEW S1(sid,sname,grade)
AS
SELECT sid,sname,grade
FROM STUDENTS
WHERE grade>1998
WITH CHECK OPTION;
插入元组：
INSERT
INTO S1
VALUES(,'Lily',1997);
执行结果：
消息550，级别16，状态1，第1 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。
语句已终止。
结果分析：
WITH CHECK OPTION表示对视图进行操作时要满足子查询的条件表达式，grade=1997本题的条件grade>1998不符，因此在本题中插入这个元组是不成功的。
删除元组：
DELETE
FROM S1
WHERE grade=1999;
执行结果：
删除成功
(8) 在视图VIEWS中将姓名为“uxjof”的学生的选课成绩都加上5分。
UPDATE VIEWS
SET score=score+5
WHERE sname='uxjof';
(9) 取消以上建立的所有视图。
DROP VIEW VIEWC
DROP VIEW VIEWS
DROP VIEW S1;
实验1.5 数据控制
实验目的
熟悉SQL的数据控制功能，能够使用SQL语句来向用户授予和收回权限。
实验内容
(1) 授予所有用户对表STUDENTS的查询权限。
GRANT SELECT
ON STUDENTS
TO PUBLIC;
(2) 授予所有用户对表COURSES的查询和更新权限。
GRANT SELECT,UPDATE
ON COURSES
TO PUBLIC;
(3) 授予USER1对表TEACHERS的查询，更新工资的权限，且允许USER1可以传播这些权限。
GRANT SELECT,UPDATE(salary)
ON TEACHERS
TO USER1
WITH GRANT OPTION;
(4) 授予USER2对表CHOICES的查询，更新成绩的权限。
GRANT SELECT,UPDATE(score)
ON CHOICES
TO USER2;
(5) 授予USER2对表TEACHERS的除了工资之外的所有信息的查询。
CREATE VIEW TV
AS
SELECT tid,tname,email
FROM TEACHERS;
GRANT SELECT
ON TV
TO UESR2;
(6) 由USER1授予USER2对表TEACHERS的查询权限和传播的此项权限的权利。
GRANT SELECT
ON TEACHERS
TO USER2
WITH GRANT OPTION;
(7) 由USER2授予USER3对表TEACHERS的查询权限，和传播的此项权限的权利。再由USER3授予USER2上述权限，这样的SQL语句能否成功得到执行?
GRANT SELECT
ON TEACHERS
TO USER3
WITH GRANT OPTION;
GRANT SELECT
ON TEACHERS
TO USER2
WITH GRANT OPTION;
(8) 取消USER1对表STUDENTS的查询权限，考虑由USER2的身份对表STUDENTS进行查询，操作能否成功？为什么？
REVOKE SELECT
ON TEACHERS
FROM UESR1 CASCADE;
操作不成功，取消授权操作的时候存在级联效应。
(9) 取消USER1和USER2的关于表COURSES的权限。
REVOKE SELECT,UPDATE
ON COURSES
FROM UESR1,UESR2;
实验1.6 空值和空集的处理
一、实验目的
认识NULL值在数据库中的特殊含义，了解空值和空集对于数据库的数据查询操作，特别是空值在条件表达式中与其他的算术运算符或逻辑运算符的运算中，空集作为嵌套查询的子查询返回结果的时候的特殊性，能够熟练使用SQL语句来进行与空值，空集相关的操作。
二、实验内容
(1) 查询所有课程记录的上课学时（数据库中为每星期学时），以一学期十八个星期计算每个课程的总学时，注意HOUR取NULL值的情况。
SELECT cid,hour*18
FROM COURSES;
(2) 通过查询选修课程C++的学生的人数，其中成绩合格的学生人数，不合格的学生人数，讨论NULL值得特殊含义。
查询选修C++的学生人数：
SELECT COUNT(*)
FROM COURSES,CHOICES
WHERE CHOICES.cid=COURSES.cid
AND cname='C++';
运行结果为：95
成绩合格的学生人数：
SELECT COUNT(*)
FROM COURSES,CHOICES
WHERE CHOICES.cid=COURSES.cid
AND cname='C++' AND score<60;
运行结果为：86
成绩不合格的学生人数：
SELECT COUNT(*)
FROM COURSES,CHOICES
WHERE CHOICES.cid=COURSES.cid
AND cname='C++' AND score>=60;
运行结果为：0
NULL与比较运算符的运算都返回FALSE值，都不会出现在结果的统计之中。
(3) 查询选修课程C++的学生的编号和成绩，使用ORDER BY按成绩进行排序时，取NULL的项是否出现在结果中？如果有，在什么位置？
SELECT sid,score
FROM CHOICES
WHERE cid=(SELECT cid FROM COURSES WHERE cname='C++')
ORDER BY score;
NULL的项出现在结果中，被当作最小值处理。
(4) 在上面的查询的过程中，如果加上保留字DISTINCT会有什么效果呢？
SELECT DISTINCT score
FROM CHOICES
WHERE cid =(SELECT cid
FROM COURSES
WHERE cname='C++')
ORDER BY score;
成绩为NULL的学生被合并
(5) 按年级对所有的学生进行分组，能得到多少个组？与现实的情况有什么不同？
SELECT grade
FROM STUDENTS
GROUP BY grade;
得到9个分组，现实中有8个年级。
(6) 结合分组，使用集合函数求每个课程选修的学生的平均分，总的选课记录数，最高成绩，最低成绩，讨论考察取空值的项对集合函数的作用的影响。
SELECT AVG(score),COUNT(*),MAX(score),MIN(score)
FROM CHOICES
GROUP BY cid;
在集合函数中，除了使用COUNT(*)计算元组时要把取空值的项计算进去，其他的集合函数都忽略了取空值的项。
(7) 采用嵌套查询的方式，利用比较运算符和谓词ALL的结合来查询表STUDENTS中最晚入学的学生年级。当存在GRADE取空值的项时，考虑可能出现的情况，并解释原因。
SELECT grade
FROM STUDENTS
WHERE grade>=ALL(SELECT grade FROM STUDENTS);
(8) 将操作步骤中的表的数据进行更新，使得表S中，NO为2和3的记录的SID列取NULL值，T表的NO为4的记录的TID取NULL值，NO为3的TID取0129871005。然后对这两个表按T.TID=S.SID作等值连接运算，找出编号相同的学生和教师的姓名，并分析原因。
CREATE TABLE S
(NO SMALLINT PRIMARY KEY,
SID VARCHAR(10),
SNAME VARCHAR(10))
CREATE TABLE T
(NO SMALLINT PRIMARY KEY,
TID VARCHAR(10),
TNAME VARCHAR(10))
INSERT INTO S VALUES(1,'0129871001','王小明'),
(2,'0129871002','李兰'),
(3,'0129871003',NULL),
(4,'0129871004','关红')
INSERT INTO T VALUES(1,'100189','王小明'),
(2,'100180','李小'),
(3,'100121',NULL),
(4,'100128',NULL)
UPDATE S SET SID=NULL WHERE NO=2 OR NO=3
UPDATE T SET TID=NULL WHERE NO=4
UPDATE T SET TID='0129871005' WHERE NO=3
SELECT SNAME,TNAME
FROM S,T
WHERE T.TID=S.SID
运行结果：无
等值连接时，值为NULL的项被忽略。
- 下载文档
- 收藏
- 0