1、实验七 T-SQL 程序设计（一）一、实验目的1、观察查询结果, 体会 SELECT语句实际应用；2、要求学生能够在查询分析器中使用 SELECT语句进行简单查询；3、通过对 SELECT的使用，掌握 SELECT语句的结构及其应用4、熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、认真独立完成实验内容；3、习后做好实验总结，根据实验情况完成总结报告。三、实验准备1、完成实验四和实验六，成功建立了基本表；2、了解简单 SELECT语句的用法；3、比较熟悉查询分析器中
2、的 SQL脚本运行环境。四、实验学时2学时五、实验指导在众多的 SQL语句中，SELECT 语句是使用频率最高的一个。SELECT 语句主要被用来对数据库进行查询并返回符合用户查询要求的结果数据集。1、检索单个表中的所有数据当查询没有限制条件时，不需要编写 WHERE子名，此时返回或显示表中的所有数据，这是 SELECT语句最简单的情况。例 7.1 检索 stu表中的所有记录。SELECT * FROM STU2、用 WHERE条件从句进行选择查询一个数据表中存放着大量相关的记录数据。实际使用时，往往只需要其中满足要求的部分记录，这时就需要用到 WHERE条件从句。例 7.2 查询班级为“计算
3、机 0203”的学生情况。要返回 stu表中班级为“计算机 0203”的学生情况，语句如下：SELECT * FROM STU WHERE 班级=计算机 0203例 7.3查询姓名中含有“红”字的学生情况。返回学生表（STU）中姓名内含有“红”字的学生情况，语句如下：SELECT * FROM STU WHERE 姓名 LIKE%红%3、用 ORDER BY子名一般存放在数据表中的记录是无法确定次序的。但返回的数据集进行排序是常有的事。可以通过 ORDER BY子句对 SELECT语句检索出来的数据按要求进行排序。例 7.4 查询出生年月在“1981-01-01”至“1982-05-01”之间
4、的学生情况，并以出生年月按降序排列。SELECT * FROM STU WHERE 出生年月 BETWEEN 1981-01-01 AND 1982-05-01ORDER BY 出生年月 DESC4、使用汇总数据数据汇总是数据库应用程序的重要组成部分。SQL 语句能将数据表中的数据按照一定规律和条件进行分类汇总，从而得到所需的统计结果。GROUP BY子句把检索出来的记录按条件进行分组，HAVING 子句对 GROUP BY子句检索出来的记录进行约束和限制。例 7.5 统计学生表（STU）表中“计算机 0203”的班级有多少人。SELECT COUNT（*）计算机 0203的人数 FROM S
5、TU WHERE 班级= 计算机0203例 7.6 统计学生成绩表（XSCJ）表中每门课程的最低分，最高分和平均分。要统计每门课程的最低分，最高分和平均分，需要使用 GROUP BY子名、MAX、MIN 和 AVG函数，可以使用下面的语句实现：SELECT 课程号，MIN（成绩）最低分，MAX（成绩）最高分，AVG（成绩）平均分 FROM XSCJ GROUP BY 课程号5、使用子查询可以将一个完整的 SELECT语句嵌套在其他的 SQL语句中，构成子查询，它通常用于一个操作需要依赖于另一个检索到的数据的场合。例 7.7 检索有一门或以上功课不及格的学生的学号、姓名、专业、班级。可以使用以下
6、 SELECT语句从学生成绩（XSCJ）表中返回有一门或以上功课不及格的学生的学号：SELECT 学号 FROM XSCJ WHERE 成绩=1因以上 SQL语句返回的记录数可能多于一个，因此在子查询中需使用 IN关键字，语句为：SELECT 学号，姓名，专业，班级 FROM STU WHERE 学号 IN （SELECT 学号 FROM XSCJ WHERE 成绩=1）六、实验内容 还原实验六创建的姓名数据库，对其中的三张数据表执行以下查询1、查询读者表中的所有信息2、显示读者表中的借书证号、单位、姓名、性别信息3、查询所有图书的总编号，书名，作者信息4、查询图书表总编号，书名，作者信息，并
7、将总编号列列名显示为“书号”5、查询单价大于 30的图书信息6、查询谭浩强作者所编写书籍的书号和书名7、查询女同学借书的信息8、查询 jim借书的时间9、查询 jim借书的书名、作者及单价信息10、查询借阅“数据结构”的读者的单位、姓名、性别信息11、查询 Lily借阅的图书的总编号、作者信息12、查询没有借书的读者的姓名，性别和地址信息13、查询没有被借阅的书籍的信息14、查询比 Phtoshop设计书价格高的书的书名、作者、单价信息 还原实验四所创建的 EDUC数据库，其中有五张表，并执行下列基本查询1、简单查询操作此部分查询包括投影、选择条件表达、数据排序、使用临时表等。对 EDUC数据
8、库实现以下查询： 查询计算机系的学生学号和姓名； 查询选修了课程的学生学号； 查询选修 C1 课程的学生学号和成绩，并要查询对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列； 查询选修课程 C1 且成绩在 8090 之间的学生学号和成绩，并将成绩乘以系数 0.75 输出； 查询计算机系和数学系的姓张的学生的信息； 查询缺少了成绩的学生的学号和课程号。 将 2000年以后的成绩大于 90分的学生成绩存入永久成绩表；将 2000年以前的成绩存入临时成绩表中。2、连接查询操作对 EDUC数据库实现以下查询： 查询每个学生的情况以及他（她）所选修的课程； 查询学生的学号、姓名、选修的课程名及
9、成绩； 查询选修 C1 课程且成绩在 90 分以上的学生学号、姓名及成绩； 查询每一门课的间接先行课。实验七 T-SQL 程序设计（二）一、实验目的1、掌握子查询的语句格式，了解子查询的作用；2、进一步掌握 SQL Server 查询分析器的使用方法，加深对 SQL 语言的嵌套查询语句的理解；3、熟练掌握数据查询中的分组、统计、计算和组合的操作方法；二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、认真独立完成实验内容；3、习后做好实验总结，根据实验情况完成总结报告。三、实验准备1、完成实验四和实验六，成功建立了基本表；2、掌
10、握 SQL语句的基本语法，并熟悉查询分析器的工作环境；3、了解子查询的表示方法，熟悉 IN 比较符、ANY、EXISTS 操作符的用法。4、了解 SELECT语句的 GROUP BY和 ORDER BY子句的作用。5、了解统计函数和分组统计函数的使用方法。6、熟悉查询分析器的运行环境。四、实验学时2学时五、实验内容1、还原实验四所创建的数据库 EDUC，实现一下查询：1）求选修了高等数学的学生学号和姓名；2）求 C1 课程的成绩高于张三的学生学号和成绩；3）求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）；4）求其他系中比计算机系学生年龄都小的学生信
11、息；5）求选修了 C2 课程的学生姓名；6）求没有选修 C2 课程的学生姓名；2、提高操作题目建立“工程-零件”数据库及如下 4个表，并输入实验数据，用 SQL语句实现如下三个查询：1) 查询上海供应商的供应商代码和联系电话2) 查询红色零件的产地规格信息3) 查询工程的工程代码，工程名，供应商代码，姓名，颜色，所在城市信息4）查询“赵二”负责的工程的名字，供应商的姓名，零件颜色和数量信息5）求供应项目 j4红色零件的供应商号及名称6）求没有使用上海供应商零件的工程代号7）查询供应商的代码及其供应的工程的数量8）查询工程的代码及其供应商的数量，只显示供应商数量多于 1的工程的信息表结构如下：供
12、应商（S）：列名 类型 长度 是否可为空供应商代码 Char 5姓名 Char 50所在城市 Char 20联系电话 Char 20工程（J）：列名 类型 长度 是否可为空工程代码 Char 5工程名 Char 50负责人 Char 10预算 Char 8零件（P）：列名 类型 长度 是否可为空零件代码 Char 5零件名 Char 50规格 Char 10产地 Char 20颜色 Char 10供应零件（SPJ）：列名 类型 长度 是否可为空供应商代码 Char 5工程代码 Char 5零件代码 Char 5数量 Int 4要求：供应商表：供应商代码为主码工程表：工程代码为主码零件表：零件代
13、码为主码供应零件表：供应商代码、工程代码、零件代码为联合主码；供应商代码为外码，参照表、列是供应商表的供应商代码列；工程代码为外码，参照表、列是工程表的工程代码列；零件代码为外码，参照表、列是零件表的零件代码列实验数据如下：供应商： 供应商代码 姓名 所在城市 联系电话S1 北京供应商 北京 0108888888S2 天津供应商 天津 0228888888S3 重庆供应商 重庆 0238888888S4 上海供应商 1 上海 0218888888S5 广州供应商 广州 0208888888S6 上海供应商 2 上海 0216666666工程：工程代码 工程名 负责人 预算J1 工程 1 丁一
14、200000J2 工程 2 赵二 60000J3 工程 3 张三 70000J4 工程 4 李四 80000J5 工程 5 王五 150000零件：零件代码 零件名 规格 产地 颜色P1 螺丝 中 济南 红色P2 主板 集成 深圳 绿色P3 显卡 独立 香港 蓝色P4 声卡 集成 天津 红色P5 网卡 100M 上海 黑色P6 鼠标 无线 上海 黑色供应零件：供应商代码 工程代码 零件代码 数量S1 J2 P4 50S1 J3 P5 100S2 J2 P6 500S4 J1 P3 150S4 J5 P1 200S5 J4 P6 100S6 J4 P2 903、还原数据库 EDUC，实现如下查询
15、1)求学生的总人数；2)求选修了课程的学生人数；3)求课程的课程号和选修该课程的人数；4)求选修课超过 3 门课的学生学号。4、提高操作实验建立“图书_读者”数据库及如下 3个表，并输入实验数据，用 SQL语句实现如下五个查询：1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的 2 倍。2）求机械工业出版社出版的各类图书的平均定价，分别用 GROUP BY 和GROUP BY ALL表示。3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。5）查询计算机
16、类和机械工业出版社出版的图书。表结构如下：图书：列名 类型 长度 是否可为空书号 char 10类别 char 12 是出版社 char 50作者 char 20 是书名 char 50定价 money 8 是读者：列名 类型 长度 是否可为空编号 char 10姓名 char 8单位 char 50 是性别 char 2 是电话 char 15 是借阅：列名 类型 长度 是否可为空串号 char 10书号 char 10读者编号 char 10借阅日期 datetime 8要求：图书表：书号为主码读者表：编号为主码；性别只能是“男”或“女”的 CHECK 约束借阅表：串号为主码；书号为外码，
17、参照表、列是图书表的书号列；读者编号为外码，参照表、列是读者表的编号列；书号和读者编号的联合 UNIQUE 约束实验数据：图书：书号 类别 出版社 作者 书名 定价1001 计算机 机械工业出版社 王民 数据结构 801002 计算机 机械工业出版社 张建平 计算机应用 201003 计算机 电子工业出版社 王敏 数据库技术 151004 计算机 电子工业出版社 谭浩强 C 语言 251005 英语 中国人民大学出版社 张锦芯 应用文写作 251006 管理 高等教育出版社 Robison 管理学 151007 管理 机械工业出版社 Fayol 工业管理 701008 数学 机械工业出版社 李
18、平 线性代数 501009 管理 机械工业出版社 Durark 公司的概念 141010 数学 机械工业出版社 徐新国 统计学 15读者：编号 姓名 单位 性别 电话1001 丁一 数学院 男 812345671002 赵二 经济学院 男 822345671003 张三 管理学院 女 832345671004 李四 文学院 男 842345671005 王五 历史文化学院 女 852345671006 孙六 物理学院 男 862345671007 周七 生命科学院 女 872345671008 徐八 化学院 男 882345671009 宋九 信息学院 女 892345671010 刘十 计算机学院 女 80234567借阅：串号 书号 读者编号 借阅日期01 1001 1003 2000-1-102 1002 1005 2002-3-5
