数据库数据库上的查询实验报告.pdf

《数据库数据库上的查询实验报告.pdf》由会员分享，可在线阅读，更多相关《数据库数据库上的查询实验报告.pdf（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 应用数学学院 学院 信息安全 专业 班_组、学号 姓名 协作者 教师评定 实验题目 数据库的查询 一、实验目的与要求 实验目的：掌握 SQL Server 查询分析器的使用方法，加深对 SQL 和 Transact-SQL 语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据联结查询的操作方法；嵌套查询语句；熟练掌握数据查询中的分组、统计、组合的操作方法。实验要求：1、简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等；2、连接查询操作；3、嵌套查询。在 SQL Server 查询分析器中使用 IN、比较符、ANY 或 ALL 和 EXISTS操作符进行嵌套查询操

2、作；4、分组查询实验。包括分组条件表达、选择组条件的表达方法；5、使用函数查询。包括统计函数和分组统计函数的使用方法；6、组合查询实验。二、实验方案 将查询需求用 Transact-SQL 语言表示；在 SQL Server 查询分析器的输入区中输入Transact-SQL 查询语句；设置查询分析器的结果区为标准执行或网格执行方式；发布执行命令，并在结果区中查看结果。2 三、实验结果和数据处理 首先，我创建了两个数据库，以供实验的完成。具体如下：1、学生课程库（1）表结构 （2）表信息 3 （3）关系图 2、图书借阅表（1）表结构 4 （2）表信息 5 （3）关系图 实验步骤：1、简单查询：（

3、l）用 Transact-SQL 语句表示下列操作。在学生选课库中实现其数据查询操作。求数学系学生的学号和姓名。6 SELECT 学号,姓名 FROM 学生 WHERE 所在系=数学系;求选修了课程的学生学号。SELECT DISTINCT 学号 FROM 选课;求选修 C1 课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT 学号,成绩 FROM 选课 WHERE 课程号=C1 ORDER BY 成绩 DESC,学号 ASC;获选修课程 C1 且成绩在 8090 分之间的学生学号和成绩，并将成绩乘以系数 0.8 输出。7 SELECT 学号,成绩

4、*0.8 FROM 选课 WHERE 课程号=C1 AND 成绩 BETWEEN 80 AND 90;求数学系或计算机系姓张的学生的信息。SELECT*FROM 学生 WHERE 所在系 IN(数学系,计算机系)AND 姓名 LIKE 张%;求缺少了成绩的学生的学号和课程号。SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL;（2）在图书借阅库中实现其查询操作：将计算机类的书存入永久的计算机图书表中，将借书日期在 1999 年以前的借阅记录存入临时的超期借阅表。SELECT*INTO 计算机图书 FROM 图书 WHERE 类别=计算机 GO 8 SELECT*INT

5、O#超期借阅 FROM 借阅 WHERE 借阅日期90;查询每一门课的间接先行课（即先行课的先行课）。SELECT A.课程号,A.课程名,B.先行课 FROM 课程 A,课程 B WHERE A.先行课=B.课程号 10 3、嵌套查询：用 Transact-SQL 语句表示，在学生选课库中实现其数据嵌套查询操作。（1）求选修了高等数学的学生学号和姓名。SELECT 学号,姓名 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 选课 WHERE 课程号 IN(SELECT 课程号 FROM 课程 WHERE 课程名=高等数学);（2）求 C1 课程的成绩高于张立的学生学号和

6、成绩。SELECT 学号,成绩 FROM 选课 WHERE 课程号=C1 AND 成绩(SELECT 成绩 FROM 选课 WHERE 课程号=C1AND 学号=(SELECT 学号 FROM 学生 WHERE 姓名=张立);11 （3）求其他系中年龄小于计算机系年龄最大者的学生。SELECT*FROM 学生 WHERE 年龄 ANY(SELECT 年龄 FROM 学生 WHERE 所在系=计算机系)AND 所在系计算机系;（4）求其他系中比计算机系学生年龄都小的学生。SELECT*FROM 学生 WHERE 年龄 ALL(SELECT 年龄 FROM 学生 WHERE 所在系=计算机系)AN

7、D 所在系计算机系;（5）求选修了 C1 课程的学生姓名。SELECT 姓名 FROM 学生 WHERE EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=C1);12 （6）求没有选修 C1 课程的学生姓名。SELECT 姓名 FROM 学生 WHERE NOT EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=C1);（7）查询选修了全部课程的学生的姓名。SELECT 姓名 FROM 学生 WHERE NOT EXISTS(SELECT*FROM 课程 WHERE NOT EXISTS(SELECT*FROM

8、选课 WHERE 学生.学号=学号 AND 课程.课程号=课程号);（8）求选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。SELECT 学号,姓名 FROM 学生 WHERE NOT EXISTS(SELECT*FROM 选课 选课 1 WHERE 选课 1.学号=S2 AND NOT EXISTS(SELECT*13 FROM 选课 选课 2 WHERE 学生.学号=选课 2.学号 AND 选课 2.课程号=选课 1.课程号);4、组合查询和统计查询：在图书借阅库中实现其查询操作。（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的 2 倍。S

9、ELECT A.*FROM 图书 A GROUP BY A.类别 HAVING MAX(A.定价)=ALL(SELECT 2*AVG(B.定价)FROM 图书 B GROUP BY B.类别)（2）求机械工业出版社出版的各类图书的平均定价，用 GROUP BY 表示。SELECT 类别,AVG(定价)平均价 FROM 图书 WHERE 出版社=机械工业出版社 GROUP BY 类别 ORDER BY 类别 ASC 14 （3）列出计算机类图书的书号、名称及价格。最后求出册数和总价格。SELECT 书号,书名,定价 FROM 图书 WHERE 类别=计算机 SELECT COUNT(*),SUM

10、(定价)FROM 图书 WHERE 类别=计算机 （4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格。最后求出全部册数和总价格。select 书号,书名,定价 from 图书 where 类别=计算机 select count(*)as 册数,sum(定价)as 总价格 from 图书 where 类别=计算机 group by 出版社 order by 出版社 asc select count(*)as 全部册数,sum(定价)as 总价格 from 图书 15 （5）查询计算机类和机械工业出版社出版的图书。SELECT*FROM 图书 WHERE 类别=计算机 UNIO

11、N ALL SELECT*FROM 图书 WHERE 出版社=机械工业出版社 16 四、结论 这次的实验主要应用了数据库的建立以及数据库的查询等操作，通过对两个数据库的操作，我对建立数据库和Transact-SQL 语句更加熟悉了。实验过程中有遇到过一些问题，比较低级的错误就是标点符号的使用错误，有时确实会使用到中文符号，导致无法执行命令。还有以下几个问题我想总结一下，以便以后能注意一下这几个方面：第一，在学生选课库中，学生表已经在选课表中有了外键，突然我发现学号信息需要修改，以便能够有更好的实验结果输出，由于在选课表中已经添加了信息，学生表此时是无法修改的，所以我的做法是现在选课表中把外键删

12、掉，然后再在学生表中修改学号，最后再添加外键。不过我接着想，如果数据库庞大，涉及的关联比较多的话，用这种办法修改起来岂不是很麻烦？于是我在网上寻找其他更好的解决办法。有网友说可以直接级联更新，还有一逻辑就是保存数据到临时表，然后删掉原表，再重建表，再复制数据到新表。我都在尝试着。第二，关于临时表的使用，INTO 子句具有创建表的功能，当创建临时表时，在表名前加“#”代表创建局部临时表，在表名前加“#”代表创建全局临时表。局部临时表只对当前连接有效，仅在当前会话中可见，当前连接断开时自动删除；全局临时表对其它连接也有效，在所有会话中都可见，在当前连接和其他访问过它的连接都会在断开时自动删除。所以

13、关机后，临时表就不存在了。临时表缺省使用硬盘，一般来说速度比较慢，但在数据量比较大的时候，如果使用表变量，会把内存耗尽，反而会降低速度。所以大数据量时适合用临时表。第三，掌握了几个很常用的语句。ORDER BY 是排序子句，INTO 是创建表子句，GROUP BY 是分组子句，COUNT 是统计记录数，“%”是多字符通配，“_”是单字符通配，CHAR 是定长字符串，VarCHAR 是变长字符串，ASC 是升序，DESC 是降序，DISTINCT 可以使结果集中，不出现重复项，等等。我觉得只有通过上机实验操作，才能更好的理解和掌握书上的知识点，并且在解决遇到的问题的过程中，能够学到更多更实用的书

14、上没有提到的知识。我相信在以后对SQL Server 的使用和不断摸索中，我会把它掌握的更好。17 五、问题与讨论（回答思考题）1、在删除数据操作中，对于存在相互牵制关系的两个表，删除操作的先后次序有无关系？答：当然有关系。比如在上述的学生选课表中，要删除年龄为 19 的学生及选课记录，需要使用两条删除语句，一条用于删除选课表中的记录，另一条用于删除学生表中的学生信息：1、删除选课表中的记录：DELETE FROM 选课 WHERE 学号 IN（SELECT 学号 FROM 学生 WHERE 年龄=19）2、删除学生表中的学生信息：DELETE FROM 学生 WHERE 年龄=19 由此可以看出，由于在删除选课表是需要查询学生表，所以不能把这两条语句的执行顺序颠倒。如果先删除了学生记录，就得不到年龄为 19 的学生的学号。对应的选课记录就无法删除了。2、嵌套子查询和连接查询操作在许多情况下可以进行互换，但是双方都不能完全替代对方，为什么？答：上述实验操作“求没有选修 C2 课程的学生姓名”就是一个很好的例子，WHERE子句中的条件是元组选择条件，此时不能使用连接查询。嵌套子查询不需要两个表有关联字段，而连接查询必须有字段关联（即主外键关系）。