实验七 TSQL程序设计.docx
《实验七 TSQL程序设计.docx》由会员分享，可在线阅读，更多相关《实验七 TSQL程序设计.docx（16页珍藏版）》请在冰点文库上搜索。
实验七TSQL程序设计
实验七T-SQL程序设计
（一）
一、实验目的
1、观察查询结果,体会SELECT语句实际应用；
2、要求学生能够在查询分析器中使用SELECT语句进行简单查询；
3、通过对SELECT的使用，掌握SELECT语句的结构及其应用
4、熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
二、实验要求
1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2、认真独立完成实验内容；
3、习后做好实验总结，根据实验情况完成总结报告。
三、实验准备
1、完成实验四和实验六，成功建立了基本表；
2、了解简单SELECT语句的用法；
3、比较熟悉查询分析器中的SQL脚本运行环境。
四、实验学时
2学时
五、实验指导
在众多的SQL语句中，SELECT语句是使用频率最高的一个。
SELECT语句主要被用来对数据库进行查询并返回符合用户查询要求的结果数据集。
1、检索单个表中的所有数据
当查询没有限制条件时，不需要编写WHERE子名，此时返回或显示表中的所有数据，这是SELECT语句最简单的情况。
例7.1检索stu表中的所有记录。
SELECT*FROMSTU
2、用WHERE条件从句进行选择查询
一个数据表中存放着大量相关的记录数据。
实际使用时，往往只需要其中满足要求的部分记录，这时就需要用到WHERE条件从句。
例7.2查询班级为“计算机0203”的学生情况。
要返回stu表中班级为“计算机0203”的学生情况，语句如下：
SELECT*FROMSTUWHERE班级=‘计算机0203’
例7.3查询姓名中含有“红”字的学生情况。
返回学生表（STU）中姓名内含有“红”字的学生情况，语句如下：
SELECT*FROMSTUWHERE姓名LIKE‘%红%’
3、用ORDERBY子名
一般存放在数据表中的记录是无法确定次序的。
但返回的数据集进行排序是常有的事。
可以通过ORDERBY子句对SELECT语句检索出来的数据按要求进行排序。
例7.4查询出生年月在“1981-01-01”至“1982-05-01”之间的学生情况，并以出生年月按降序排列。
SELECT*FROMSTUWHERE出生年月BETWEEN‘1981-01-01’AND‘1982-05-01’
ORDERBY出生年月DESC
4、使用汇总数据
数据汇总是数据库应用程序的重要组成部分。
SQL语句能将数据表中的数据按照一定规律和条件进行分类汇总，从而得到所需的统计结果。
GROUPBY子句把检索出来的记录按条件进行分组，HAVING子句对GROUPBY子句检索出来的记录进行约束和限制。
例7.5统计学生表（STU）表中“计算机0203”的班级有多少人。
SELECTCOUNT（*）计算机0203的人数FROMSTUWHERE班级=’计算机0203’
例7.6统计学生成绩表（XSCJ）表中每门课程的最低分，最高分和平均分。
要统计每门课程的最低分，最高分和平均分，需要使用GROUPBY子名、MAX、MIN和AVG函数，可以使用下面的语句实现：
SELECT课程号，MIN（成绩）最低分，MAX（成绩）最高分，AVG（成绩）平均分FROMXSCJGROUPBY课程号
5、使用子查询
可以将一个完整的SELECT语句嵌套在其他的SQL语句中，构成子查询，它通常用于一个操作需要依赖于另一个检索到的数据的场合。
例7.7检索有一门或以上功课不及格的学生的学号、姓名、专业、班级。
可以使用以下SELECT语句从学生成绩（XSCJ）表中返回有一门或以上功课不及格的学生的学号：
SELECT学号FROMXSCJWHERE成绩<60GROUPBY学号HAVINGCOUNT（*）>=1
因以上SQL语句返回的记录数可能多于一个，因此在子查询中需使用IN关键字，语句为：
SELECT学号，姓名，专业，班级FROMSTUWHERE学号IN（
SELECT学号FROMXSCJWHERE成绩<60GROUPBY学号HAVINGCOUNT（*）>=1）
六、实验内容
※还原实验六创建的姓名数据库，对其中的三张数据表执行以下查询
1、查询读者表中的所有信息
2、显示读者表中的借书证号、单位、姓名、性别信息
3、查询所有图书的总编号，书名，作者信息
4、查询图书表总编号，书名，作者信息，并将总编号列列名显示为“书号”
5、查询单价大于30的图书信息
6、查询谭浩强作者所编写书籍的书号和书名
7、查询女同学借书的信息
8、查询jim借书的时间
9、查询jim借书的书名、作者及单价信息
10、查询借阅“数据结构”的读者的单位、姓名、性别信息
11、查询Lily借阅的图书的总编号、作者信息
12、查询没有借书的读者的姓名，性别和地址信息
13、查询没有被借阅的书籍的信息
14、查询比Phtoshop设计书价格高的书的书名、作者、单价信息
※还原实验四所创建的EDUC数据库，其中有五张表，并执行下列基本查询
1、简单查询操作
此部分查询包括投影、选择条件表达、数据排序、使用临时表等。
对EDUC数据库实现以下查询：
①查询计算机系的学生学号和姓名；
②查询选修了课程的学生学号；
③查询选修C1课程的学生学号和成绩，并要查询对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；
④查询选修课程C1且成绩在80－90之间的学生学号和成绩，并将成绩乘以系数0.75输出；
⑤查询计算机系和数学系的姓张的学生的信息；
⑥查询缺少了成绩的学生的学号和课程号。
⑦将2000年以后的成绩大于90分的学生成绩存入永久成绩表；将2000年以前的成绩存入临时成绩表中。
2、连接查询操作
对EDUC数据库实现以下查询：
①查询每个学生的情况以及他（她）所选修的课程；
②查询学生的学号、姓名、选修的课程名及成绩；
②查询选修C1课程且成绩在90分以上的学生学号、姓名及成绩；
③查询每一门课的间接先行课。
实验七T-SQL程序设计
（二）
一、实验目的
1、掌握子查询的语句格式，了解子查询的作用；
2、进一步掌握SQLServer查询分析器的使用方法，加深对SQL语言的嵌套查询语句的理解；
3、熟练掌握数据查询中的分组、统计、计算和组合的操作方法；
二、实验要求
1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2、认真独立完成实验内容；
3、习后做好实验总结，根据实验情况完成总结报告。
三、实验准备
1、完成实验四和实验六，成功建立了基本表；
2、掌握SQL语句的基本语法，并熟悉查询分析器的工作环境；
3、了解子查询的表示方法，熟悉IN比较符、ANY、EXISTS操作符的用法。
4、了解SELECT语句的GROUPBY和ORDERBY子句的作用。
5、了解统计函数和分组统计函数的使用方法。
6、熟悉查询分析器的运行环境。
四、实验学时
2学时
五、实验内容
1、还原实验四所创建的数据库EDUC，实现一下查询：
1）求选修了高等数学的学生学号和姓名；
2）求C1课程的成绩高于张三的学生学号和成绩；
3）求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）；
4）求其他系中比计算机系学生年龄都小的学生信息；
5）求选修了C2课程的学生姓名；
6）求没有选修C2课程的学生姓名；
2、提高操作题目
建立“工程-零件”数据库及如下4个表，并输入实验数据，用SQL语句实现如下三个查询：
1）查询上海供应商的供应商代码和联系电话
2）查询红色零件的产地规格信息
3）查询工程的工程代码，工程名，供应商代码，姓名，颜色，所在城市信息
4）查询“赵二”负责的工程的名字，供应商的姓名，零件颜色和数量信息
5）求供应项目j4红色零件的供应商号及名称
6）求没有使用上海供应商零件的工程代号
7）查询供应商的代码及其供应的工程的数量
8）查询工程的代码及其供应商的数量，只显示供应商数量多于1的工程的信息
表结构如下：
供应商（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
要求：
①供应商表：
供应商代码为主码
②工程表：
工程代码为主码
③零件表：
零件代码为主码
④供应零件表：
供应商代码、工程代码、零件代码为联合主码；
供应商代码为外码，参照表、列是供应商表的供应商代码列；
工程代码为外码，参照表、列是工程表的工程代码列；
零件代码为外码，参照表、列是零件表的零件代码列
实验数据如下：
供应商：
供应商代码
姓名
所在城市
联系电话
S1
北京供应商
北京
010*******
S2
天津供应商
天津
022*******
S3
重庆供应商
重庆
023*******
S4
上海供应商1
上海
021*******
S5
广州供应商
广州
020*******
S6
上海供应商2
上海
021*******
工程：
工程代码
工程名
负责人
预算
J1
工程1
丁一
200000
J2
工程2
赵二
60000
J3
工程3
张三
70000
J4
工程4
李四
80000
J5
工程5
王五
150000
零件：
零件代码
零件名
规格
产地
颜色
P1
螺丝
中
济南
红色
P2
主板
集成
深圳
绿色
P3
显卡
独立
香港
蓝色
P4
声卡
集成
天津
红色
P5
网卡
100M
上海
黑色
P6
鼠标
无线
上海
黑色
供应零件：
供应商代码
工程代码
零件代码
数量
S1
J2
P4
50
S1
J3
P5
100
S2
J2
P6
500
S4
J1
P3
150
S4
J5
P1
200
S5
J4
P6
100
S6
J4
P2
90
3、还原数据库EDUC，实现如下查询
1）求学生的总人数；
2）求选修了课程的学生人数；
3）求课程的课程号和选修该课程的人数；
4）求选修课超过3门课的学生学号。
4、提高操作实验
建立“图书_读者”数据库及如下3个表，并输入实验数据，用SQL语句实现如下五个查询：
1）查找这样的图书类别：
要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的2倍。
2）求机械工业出版社出版的各类图书的平均定价，分别用GROUPBY和GROUPBYALL表示。
3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。
4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。
5）查询计算机类和机械工业出版社出版的图书。
表结构如下：
图书：
列名
类型
长度
是否可为空
书号
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约束
③借阅表：
串号为主码；
书号为外码，参照表、列是图书表的书号列；
读者编号为外码，参照表、列是读者表的编号列；
书号和读者编号的联合UNIQUE约束
实验数据：
图书：
书号
类别
出版社
作者
书名
定价
1001
计算机
机械工业出版社
王民
数据结构
80
1002
计算机
机械工业出版社
张建平
计算机应用
20
1003
计算机
电子工业出版社
王敏
数据库技术
15
1004
计算机
电子工业出版社
谭浩强
C语言
25
1005
英语
中国人民大学出版社
张锦芯
应用文写作
25
1006
管理
高等教育出版社
Robison
管理学
15
1007
管理
机械工业出版社
Fayol
工业管理
70
1008
数学
机械工业出版社
李平
线性代数
50
1009
管理
机械工业出版社
Durark
公司的概念
14
1010
数学
机械工业出版社
徐新国
统计学
15
读者：
编号
姓名
单位
性别
电话
1001
丁一
数学院
男
81234567
1002
赵二
经济学院
男
82234567
1003
张三
管理学院
女
83234567
1004
李四
文学院
男
84234567
1005
王五
历史文化学院
女
85234567
1006
孙六
物理学院
男
86234567
1007
周七
生命科学院
女
87234567
1008
徐八
化学院
男
88234567
1009
宋九
信息学院
女
89234567
1010
刘十
计算机学院
女
80234567
借阅：
串号
书号
读者编号
借阅日期
01
1001
1003
2000-1-1
02
1002
1005
2002-3-5
03
1003
1008
1998-6-18
04
1004
1003
1997-12-8
05
1005
1010
2001-5-4
06
1006
1001
2005-7-25
07
1007
1010
997-11-3
08
1008
1009
2004-2-1
09
1009
1004
1996-9-1
10
1010
1008
2000-6-4
实验七T-SQL程序设计（三）
一、实验目的
1、掌握流控制语句的基本语法；
2、能够熟练使用这些流控制语句（包括：
begin..end,if…else,while,break,continue,waitfor,case）；
3、掌握系统内置函数的概念及其应用；
4、通过定义和使用用户自定义函数，掌握自定义函数的概念及其应用。
二、实验要求
1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2、认真独立完成实验内容；
3、习后做好实验总结，根据实验情况完成总结报告。
三、实验准备
1、完成实验四和实验六，成功建立了基本表；
2.完成了前面的实验，并且掌握基本的SQL语句的使用；
3.了解流控制语句的基本语法和使用。
四、实验学时
2学时
五、实验指导
1、IF…ELSE语句
IF…ELSE语句是在执行T-SQL语句时强加条件。
如果条件满足，则在IF关键字及其条件之后执行T-SQL语句。
可选的ELSE关键字引入备用的T-SQL语句，当不满足IF条件时，就执行这个语句。
例7.7若存在学号为“02020101”的学生，则显示该学生的信息，否则插入该学生的记录。
其实现语句为：
ifexists（select学号fromxsqkwhere学号=’02020101’）
print‘已存在学号为02020101的学生’
else
insertintoxsqk（学号,姓名）values（’02020101’,’李萍’）
2、用户自定义函数的应用
用户可以使用CREATEFUNCTION语句编写自己的函数，以满足特殊需要。
用户自定义函数，可以传递0个或多个参数，并返回一个简单的数值，一般来说返回的都是数值或字符型的数据。
例7.8定义一个自定义函数，实现从出生年月到年龄的计算。
自定义函数如下：
createfunctiondbo.re_year
（@vardatedatetime,@curdatedatetime）
returnstinyint
as
begin
returndatediff（yyyy,@vardate,@curdate）
end
六、实验内容
1、如果stu表中有入校时间在2006年以后的学生，把该学生的学号，姓名和入学时间查询出来，否则输出“没有在2006年以后入学的学生”。
（if..else）
2、如果stu表中有名叫“李寻欢”的学生，就把他的名字修改为“李探花”，并输出修改前后的学号，姓名，性别信息，否则输出“没有李寻欢这个人，所以你无法修改啦！
”
3、查询stu表，只要有年龄小于20岁的学生，就将每个学生的出生日期都加1个月，如此循环下去，直到所有的学生的年龄都不小于20岁。
（while循环）
4、使用WHILE语句求1到100之间的累加和并输出
5、定义一个用户自定义的函数Score_ReChange，将成绩从百分制转化为五级记分制。
将该用户定义的函数用在查询每个学生的成绩中，给出五级记分制的成绩。
6、定义一个用户自定义的函数，完成如下功能：
如果学生有不及格的成绩，则在学生情况表的备注列中输入“有不及格的成绩”，否则输入“没有不及格的成绩”。
升级会员 