数据库：数据库上的查询-实验报告.doc

应用数学学院 学院 信息安全 专业 班_组、学号 姓名 协作者 教师评定 实验题目 数据库的查询 一、实验目的与要求实验目的：掌握SQL Server查询分析器的使用方法，加深对SQL 和Transact-SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据联结查询的操作方法；嵌套查询语句；熟练掌握数据查询中的分组、统计、组合的操作方法。实验要求：1、简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等；2、连接查询操作；3、嵌套查询。在SQL Server查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作；4、分组查询实验。包括分组条件表达、选择组条件的表达方法；5、使用函数查询。包括统计函数和分组统计函数的使用方法；6、组合查询实验。二、实验方案将查询需求用Transact-SQL语言表示；在SQL Server查询分析器的输入区中输入Transact-SQL查询语句；设置查询分析器的结果区为标准执行或网格执行方式；发布执行命令，并在结果区中查看结果。三、实验结果和数据处理首先，我创建了两个数据库，以供实验的完成。具体如下：1、学生课程库（1）表结构（2）表信息（3）关系图2、图书借阅表（1）表结构（2）表信息（3）关系图实验步骤：1、简单查询：（l）用Transact-SQL语句表示下列操作。在学生选课库中实现其数据查询操作。求数学系学生的学号和姓名。SELECT 学号,姓名FROM 学生WHERE 所在系=数学系;求选修了课程的学生学号。SELECT DISTINCT 学号FROM 选课;求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT 学号,成绩FROM 选课WHERE 课程号=C1ORDER BY 成绩 DESC,学号 ASC;获选修课程C1且成绩在8090分之间的学生学号和成绩，并将成绩乘以系数0.8 输出。SELECT 学号,成绩*0.8FROM 选课WHERE 课程号=C1 AND 成绩 BETWEEN 80 AND 90;求数学系或计算机系姓张的学生的信息。SELECT *FROM 学生WHERE 所在系 IN (数学系,计算机系) AND 姓名 LIKE 张%;求缺少了成绩的学生的学号和课程号。SELECT 学号,课程号FROM 选课WHERE 成绩 IS NULL;（2）在图书借阅库中实现其查询操作：将计算机类的书存入永久的计算机图书表中，将借书日期在1999年以前的借阅记录存入临时的超期借阅表。SELECT *INTO 计算机图书FROM 图书WHERE 类别=计算机GOSELECT *INTO #超期借阅FROM 借阅WHERE 借阅日期90;查询每一门课的间接先行课（即先行课的先行课）。SELECT A.课程号,A.课程名,B.先行课FROM 课程 A,课程 BWHERE A.先行课=B.课程号3、嵌套查询：用Transact-SQL语句表示，在学生选课库中实现其数据嵌套查询操作。（1）求选修了高等数学的学生学号和姓名。SELECT 学号,姓名FROM 学生WHERE 学号 IN (SELECT 学号 FROM 选课 WHERE 课程号 IN (SELECT 课程号 FROM 课程 WHERE 课程名=高等数学);（2）求C1课程的成绩高于张立的学生学号和成绩。SELECT 学号,成绩FROM 选课WHERE 课程号=C1 AND 成绩(SELECT 成绩 FROM 选课 WHERE 课程号=C1AND 学号=(SELECT 学号 FROM 学生 WHERE 姓名=张立);（3）求其他系中年龄小于计算机系年龄最大者的学生。SELECT *FROM 学生WHERE 年龄 ANY (SELECT 年龄 FROM 学生 WHERE 所在系=计算机系) AND 所在系计算机系;（4）求其他系中比计算机系学生年龄都小的学生。SELECT *FROM 学生WHERE 年龄 ALL (SELECT 年龄 FROM 学生 WHERE 所在系=计算机系) AND 所在系计算机系;（5）求选修了C1课程的学生姓名。SELECT 姓名FROM 学生WHERE EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号=C1);（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 选课 WHERE 学生.学号=学号 AND 课程.课程号=课程号);（8）求选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。SELECT 学号,姓名FROM 学生WHERE NOT EXISTS (SELECT * FROM 选课 选课1 WHERE 选课1.学号=S2 AND NOT EXISTS (SELECT * FROM 选课 选课2 WHERE 学生.学号=选课2.学号 AND 选课2.课程号=选课1.课程号);4、组合查询和统计查询：在图书借阅库中实现其查询操作。（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍。SELECT A.*FROM 图书 AGROUP 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（3）列出计算机类图书的书号、名称及价格。最后求出册数和总价格。SELECT 书号,书名,定价FROM 图书WHERE 类别=计算机SELECT COUNT(*) ,SUM(定价)FROM 图书WHERE 类别=计算机（4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格。最后求出全部册数和总价格。select 书号,书名,定价from 图书where 类别=计算机select count(*) as 册数 ,sum(定价) as 总价格from 图书where 类别=计算机group by 出版社order by 出版社 ascselect count(*) as 全部册数 ,sum(定价) as 总价格from 图书（5）查询计算机类和机械工业出版社出版的图书。SELECT *FROM 图书WHERE 类别=计算机UNION ALLSELECT *FROM 图书WHERE 出版社=机械工业出版社四、结论这次的实验主要应用了数据库的建立以及数据库的查询等操作，通过对两个数据库的操作，我对建立数据库和Transact-SQL语句更加熟悉了。实验过程中有遇到过一些问题，比较低级的错误就是标点符号的使用错误，有时确实会使用到中文符号，导致无法执行命令。还有以下几个问题我想总结一下，以便以后能注意一下这几个方面：第一，在学生选课库中，学生表已经在选课表中有了外键，突然我发现学号信息需要修改，以便能够有更好的实验结果输出，由于在选课表中已经添加了信息，学生表此时是无法修改的，所以我的做法是现在选课表中把外键删掉，然后再在学生表中修改学号，最后再添加外键。不过我接着想，如果数据库庞大，涉及的关联比较多的话，用这种办法修改起来岂不是很麻烦？于是我在网上寻找其他更好的解决办法。有网友说可以直接级联更新，还有一逻辑就是保存数据到临时表，然后删掉原表，再重建表，再复制数据到新表。我都在尝试着。第二，关于临时表的使用，INTO子句具有创建表的功能，当创建临时表时，在表名前加“#”代表创建局部临时表，在表名前加“#”代表创建全局临时表。局部临时表只对当前连接有效，仅在当前会话中可见，当前连接断开时自动删除；全局临时表对其它连接也有效，在所有会话中都可见，在当前连接和其他访问过它的连接都会在断开时自动删除。所以关机后，临时表就不存在了。临时表缺省使用硬盘，一般来说速度比较慢，但在数据量比较大的时候，如果使用表变量，会把内存耗尽，反而会降低速度。所以大数据量时适合用临时表。第三，掌握了几个很常用的语句。ORDER BY是排序子句，INTO是创建表子句，GROUP BY是分组子句，COUNT 是统计记录数，“%”是多字符通配，“_”是单字符通配，CHAR是定长字符串，VarCHAR是变长字符串，ASC是升序，DESC是降序，DISTINCT可以使结果集中，不出现重复项，等等。我觉得只有通过上机实验操作，才能更好的理解和掌握书上的知识点，并且在解决遇到的问题的过程中，能够学到更多更实用的书上没有提到的知识。我相信在以后对SQL Server的使用和不断摸索中，我会把它掌握的更好。五、问题与讨论（回答思考题）1、在删除数据操作中，对于存在相互牵制关系的两个表，删除操作的先后次序有无关系？答：当然有关系。比如在上述的学生选课表中，要删除年龄为19的学生及选课记录，需要使用两条删除语句，一条用于删除选课表中的记录，另一条用于删除学生表中的学生信息：1、删除选课表中的记录：DELETEFROM 选课WHERE 学号 IN （SELECT 学号FROM 学生WHERE 年龄 =19）2、删除学生表中的学生信息：DELETEFROM 学生WHERE 年龄 =19由此可以看出，由于在删除选课表是需