数据库第一章实验报告

《数据库原理及应用》

实验报告

题目：实验四、五、六 SQL语言（视图、数据控制、空值处理） 学号：132935 姓名：么红帅

专业班级：软件131 指导教师：张满囤

完成日期： 2015年11月9日

1

实验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)

CREATETABLECUSTOMERS( CIDCHAR(8)UNIQUE, CNAMECHAR(20), CITYCHAR(20), DISCNTINT, PRIMARYKEY(CID))

CREATETABLEAGENTS( AIDCHAR(8)UNIQUE, ANAMECHAR(20), CITYCHAR(20), [PERCENT]FLOAT, PRIMARYKEY(AID))

CREATETABLEPRODUCTS( PIDCHAR(8)UNIQUE, PANMECHAR(20), PRIMARYKEY(PID))

CREATETABLEORDERS( ORDNACHAR(8)UNIQUE, MONTHINT,

CIDCHAR(8)UNIQUE, AIDCHAR(8)UNIQUE, PIDCHAR(8)UNIQUE, QTYINT, DOLLARSFLOAT, PRIMARYKEY(ORDNA),

FOREIGNKEY(CID)REFERENCESCUSTOMERS,

FOREIGNKEY(AID)REFERENCESAGENTS, FOREIGNKEY(PID)REFERENCESPRODUCTS)

(3)

ALTERTABLEPRODUCTSADDCITYCHAR(20)

ALTERTABLEPRODUCTSADDQUANTITYINT ALTERTABLEPRODUCTSADDPRICEFLOAT

2

(2)

(4)

CREATEINDEXXSNOONCUSTOMERS(CID) CREATEINDEXXSNOONAGENTS(AID) CREATEINDEXXSNOONPRODUCTS(PID) CREATEINDEXXSNOONORDERS(ORDNA)

(5)

DROPINDEXCUSTOMERS.XSNO DROPINDEXAGENTS.XSNO DROPINDEXPRODUCTS.XSNO DROPINDEXORDERS.XSNO

3

实验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的学生的编号。

4

(23)实现集合减运算，查询级选修课程database而又没有选修UML的学生的编号。

三、实验结果

(1)

SELECT* FROMCOURSES;

(2)

SELECTsid FROMCHOICES;

(3)

SELECTcid FROMCOURSES WHEREhour<88;

(4)

SELECTsid FROMCHOICES

GROUPBYsidHAVINGSUM(score)>400;

(5)

SELECTCOUNT(cid) FROMCOURSES;

(6)

SELECTcid,COUNT(sid) FROMCHOICES GROUPBYcid;

(7)

SELECTsid FROMCHOICES WHEREscore>60 GROUPBYsid

HAVINGCOUNT(cid)>2;

(8)

SELECTsid,COUNT(cid),AVG(score) FROMCHOICES GROUPBYsid;

(9)

SELECTsid,sname FROMSTUDENTS WHEREsidIN (SELECTsid FROMCHOICES

WHEREcid=(SELECTcid

FROMCOURSES

WHEREcname='Java'));

(10) 等值连接：

SELECTcid,score FROMSTUDENTS,CHOICES

WHERESTUDENTS.sid=CHOICES.sidANDsname='sssht';

谓词IN：

SELECTcid,score FROMCHOICES WHEREsidIN (SELECTsid FROMSTUDENTS

WHEREsname='sssht');

(11)

SELECTcname FROMCOURSES WHEREhour> (SELECThour FROMCOURSES

WHEREcname='C++');

(12)

SELECTsid,sname FROMSTUDENTS WHEREsidIN (SELECTC1.sid

FROMCHOICESC1,CHOICESC2 WHEREC1.score>C2.scoreANDC1.cid=C2.cid

ANDC1.cid=(SELECTcidFROMCOURSESWHEREcname='c++')

ANDC2.sid=(SELECTsidFROMSTUDE

NTSWHEREsname='znkoo'));

(13)

SELECTsname FROMSTUDENTS WHEREgradeIN (SELECTgrade FROMSTUDENTS

WHEREsid='883794999'ORsid='85

5

0955252');

(14)

SELECTsname FROMSTUDENTS WHEREsidNOTIN (SELECTsid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES

WHEREcname='Java'));

(15)

SELECT* FROMCOURSES

WHEREhour=(SELECTMIN(hour)FROMCOURSES);

(16)

SELECTtid,cid FROMCHOICES WHEREtidIN (SELECTtid FROMTEACHERS WHEREsalary= (SELECTMAX(salary)

FROMTEACHERS));

(17)

SELECTsid FROMCHOICES WHEREscore= (SELECTMAX(score) FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES

WHEREcname='ERP'))AND cid=(SELECTcid FROMCOURSES

WHEREcname='ERP');

(18)

SELECTcname FROMCOURSES WHEREcidNOTIN (SELECTcid

FROMCHOICES);

(19)

SELECTcname FROMCOURSES WHEREcidIN (SELECTcid FROMCHOICES WHEREtidIN (SELECTtid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES

WHEREcname='UML')));

(20)

SELECTsid FROMCHOICES WHEREcidIN (SELECTcid FROMCHOICES

WHEREtid='200102901')

GROUPBYsid HAVINGCOUNT(*)= (SELECTCOUNT(DISTINCTcid) FROMCHOICES

WHEREtid='200102901');

(21)

SELECTsid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES

WHEREcname='Database')

UNION SELECTsid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES

WHEREcname='UML');

(22)

SELECTC1.sid

FROMCHOICESC1,CHOICESC2

WHEREC1.cid=(SELECTcidFROMCOURSESWHEREcname='Database')

6

ANDC2.cid=(SELECTcidFROMCOURSANDC1.sid=C2.sid;

WHEREC1.cid=(SELECTcidFROMCOURSESWHEREcname='Database')

ANDC1.sid=C2.sid

ANDC2.cid<>(SELECTcidFROMCOUR

ESWHEREcname='UML')

(23)

SELECTC1.sid

FROMCHOICESC1,CHOICESC2

SESWHEREcname='UML');

7

实验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

INTOSTUDENTS(sid,sname)

VALUES ('800022222','WangLan');

SETtid=(SELECTtidFROMTEACHERSWHEREtname='rnupx') WHEREtid='200016721';

(6)

UPDATESTUDENTS SETgrade='2001' WHEREsid='800071780';

(2)

INSERT INTOTEACHERS VALUES

('200001000','LXL','s4zrck@pew.net',3024);

(7)

DELETE FROMCOURSES WHEREcidNOTIN

(SELECTDISTINCTcid FROMCHOICES);

(3)

UPDATETEACHERS SETsalary=4000 WHEREtid='200010493';

(8)

DELETE FROMSTUDENTS WHEREgrade<1998;

(4)

UPDATETEACHERS SETsalary=2500 WHEREsalary<2500;

(9)

DELETE FROMSTUDENTS

8

(5)

UPDATECHOICES

WHEREsidNOTIN

(SELECTDISTINCTsid FROMCHOICES);

DELETE FROMCHOICES WHEREscore<60;

(10)

9

软件131-么红帅-132935

实验1.4视图

一、实验目的

熟悉SQL支持的有关视图的操作，能够熟练地使用SQL语句来创建需要的视图，对视图进行查询和取消视图。 二、实验内容

(1)定义选课信息和课程名称的视图VIEWC；

CREATEVIEWVIEWC AS

SELECTCHOICES.no,CHOICES.sid,CHOICES.tid,CHOICES.score,COURSES.cname

FROMCHOICES,COURSES

WHERECHOICES.cid=COURSES.cid;

2001”)；

INSERT INTOS1

VALUES ('60000001','Lily',2001);

(7)定义包括更新和插入约束的视图S1，

尝试向视图插入记录“(60000001，Lily，1997”)，删除所有年级为1999的学生记录，讨论更新和插入约束带来的影响。

定义视图：

CREATEVIEWS1(sid,sname,grade) AS

SELECTsid,sname,grade FROMSTUDENTS WHEREgrade>1998 WITHCHECKOPTION;

(2)定义学生姓名与选课信息的视图VIEWS；

CREATEVIEWVIEWS AS

SELECTSTUDENTS.sname,CHOICES.no,CHOICES.tid,CHOICES.cid,CHOICES.score

FROMSTUDENTS,CHOICES

WHERESTUDENTS.sid=CHOICES.sid;

插入元组：

INSERT INTOS1

VALUES('60000001','Lily',1997);

(3)定义年级低于1998的学生的视图S1(SID，SNAME，GRADE)；

CREATEVIEWS1(sid,sname,grade) AS

SELECTsid,sname,grade FROMSTUDENTS WHEREgrade>1998;

(4)查询学生为“uxjof”的学生的选课信息；

SELECT* FROMVIEWS

WHEREsname='uxjof';

(5)查询选修课程“UML”的学生的编号和成绩；

SELECTsid,score FROMVIEWC

WHEREcname='UML';

执行结果：

消息550，级别16，状态1，第1 行 试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。 语句已终止。 结果分析：

WITH CHECK OPTION表示对视图进行操作时要满足子查询的条件表达式，grade=1997本题的条件grade>1998不符，因此在本题中插入这个元组是不成功的。 删除元组：

DELETE FROMS1

WHEREgrade=1999;

10

(6)向视图S1插入记录“(60000001，Lily，

软件131-么红帅-132935

执行结果： 删除成功

(8)在视图VIEWS中将姓名为“uxjof”的学生的选课成绩都加上5分。

UPDATEVIEWS SETscore=score+5

WHEREsname='uxjof';

(9)取消以上建立的所有视图。

DROPVIEWVIEWC DROPVIEWVIEWS DROPVIEWS1;

11

软件131-么红帅-132935

实验1.5数据控制

一、实验目的

熟悉SQL的数据控制功能，能够使用SQL语句来向用户授予和收回权限。 二、实验内容

(1)授予所有用户对表STUDENTS的查询权限。

GRANTSELECT ONSTUDENTS TOPUBLIC;

的查询权限和传播的此项权限的权利。

GRANTSELECT ONTEACHERS TOUSER2

WITHGRANTOPTION;

(2)授予所有用户对表COURSES的查询

和更新权限。

GRANTSELECT,UPDATE ONCOURSES TOPUBLIC;

(7)由USER2授予USER3对表TEACHERS

的查询权限，和传播的此项权限的权利。再由USER3授予USER2上述权限，这样的SQL语句能否成功得到执行?

GRANTSELECT ONTEACHERS TOUSER3

WITHGRANTOPTION;

(3)授予USER1对表TEACHERS的查询，

更新工资的权限，且允许USER1可以传播这些权限。

GRANTSELECT,UPDATE(salary) ONTEACHERS TOUSER1

WITHGRANTOPTION;

GRANTSELECT ONTEACHERS TOUSER2

WITHGRANTOPTION;

(4)授予USER2对表CHOICES的查询，更新成绩的权限。

GRANTSELECT,UPDATE(score) ONCHOICES TOUSER2;

(8)取消USER1对表STUDENTS的查询权

限，考虑由USER2的身份对表STUDENTS进行查询，操作能否成功？为什么？

REVOKESELECT ONTEACHERS

FROMUESR1 CASCADE;

(5)授予USER2对表TEACHERS的除了工资之外的所有信息的查询。

CREATEVIEWTV AS

SELECTtid,tname,email FROMTEACHERS; GRANTSELECT ONTV TOUESR2;

操作不成功，取消授权操作的时候存在级联效应。

(9)取消USER1和USER2的关于表COURSES的权限。

REVOKESELECT,UPDATE ONCOURSES FROMUESR1,UESR2;

(6)由USER1授予USER2对表TEACHERS

12

软件131-么红帅-132935

实验1.6空值和空集的处理

一、实验目的

认识NULL值在数据库中的特殊含义，了解空值和空集对于数据库的数据查询操作，特别是空值在条件表达式中与其他的算术运算符或逻辑运算符的运算中，空集作为嵌套查询的子查询返回结果的时候的特殊性，能够熟练使用SQL语句来进行与空值，空集相关的操作。 二、实验内容

(1)查询所有课程记录的上课学时（数

如果有，在什么位置？

SELECTsid,score FROMCHOICES

WHEREcid=(SELECTcidFROMCOURSESWHEREcname='C++') ORDERBYscore;

据库中为每星期学时），以一学期十八个星期计算每个课程的总学时，注意HOUR取NULL值的情况。

SELECTcid,hour*18 FROMCOURSES;

(2)通过查询选修课程C++的学生的人

数，其中成绩合格的学生人数，不合格的学生人数，讨论NULL值得特殊含义。

查询选修C++的学生人数：

SELECTCOUNT(*) FROMCOURSES,CHOICES

WHERECHOICES.cid=COURSES.cid

ANDcname='C++';

NULL的项出现在结果中，被当作最小值处理。

(4)在上面的查询的过程中，如果加上保留字DISTINCT会有什么效果呢？

SELECTDISTINCTscore FROMCHOICES

WHEREcid=(SELECTcid FROMCOURSES WHEREcname='C++') ORDERBYscore;

运行结果为：95

成绩合格的学生人数：

SELECTCOUNT(*) FROMCOURSES,CHOICES

WHERECHOICES.cid=COURSES.cid

ANDcname='C++'ANDscore<60;

成绩为NULL的学生被合并

(5)按年级对所有的学生进行分组，能

得到多少个组？与现实的情况有什么不同？

SELECTgrade FROMSTUDENTS GROUPBYgrade;

运行结果为：86

成绩不合格的学生人数：

SELECTCOUNT(*) FROMCOURSES,CHOICES

WHERECHOICES.cid=COURSES.cid

ANDcname='C++'ANDscore>=60;

得到9个分组，现实中有8个年级。 (6)结合分组，使用集合函数求每个课

运行结果为：0

NULL与比较运算符的运算都返回FALSE值，都不会出现在结果的统计之中。

(3)查询选修课程C++的学生的编号和

程选修的学生的平均分，总的选课记录数，最高成绩，最低成绩，讨论考察取空值的项对集合函数的作用的影响。

SELECTAVG(score),COUNT(*),MAX(score),MIN(score) FROMCHOICES GROUPBYcid;

13

成绩，使用ORDER BY按成绩进行排序时，取NULL的项是否出现在结果中？

软件131-么红帅-132935

在集合函数中，除了使用COUNT(*)计算元组时要把取空值的项计算进去，其他的集合函数都忽略了取空值的项。

(7)采用嵌套查询的方式，利用比较运

O=3

SELECTSNAME,TNAME FROMS,T

WHERET.TID=S.SID

算符和谓词ALL的结合来查询表STUDENTS中最晚入学的学生年级。当存在GRADE取空值的项时，考虑可能出现的情况，并解释原因。

SELECTgrade FROMSTUDENTS

WHEREgrade>=ALL(SELECTgradeFROMSTUDENTS);

运行结果：无

等值连接时，值为NULL的项被忽略。

(8)将操作步骤中的表的数据进行更新，

使得表S中，NO为2和3的记录的SID列取NULL值，T表的NO为4的记录的TID取NULL值，NO为3的TID取0129871005。然后对这两个表按T.TID=S.SID作等值连接运算，找出编号相同的学生和教师的姓名，并分析原因。

CREATETABLES

(NOSMALLINTPRIMARYKEY, SIDVARCHAR(10), SNAMEVARCHAR(10)) CREATETABLET

(NOSMALLINTPRIMARYKEY, TIDVARCHAR(10), TNAMEVARCHAR(10))

INSERTINTOSVALUES(1,'0129871001','王小明'),

(2,'0129871002','李兰'), (3,'0129871003',NULL), (4,'0129871004','关红')

INSERTINTOTVALUES(1,'100189','王小明'),

(2,'100180','李小'), (3,'100121',NULL), (4,'100128',NULL)

UPDATESSETSID=NULLWHERENO=2 ORNO=3

UPDATETSETTID=NULLWHERENO=4 UPDATETSETTID='0129871005'WHEREN

14

本文来源：https://www.bwwdw.com/article/1nlp.html