实验七 TSQL程序设计.docx

1、实验七 TSQL程序设计实验七 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 查询班级为“计算机0203

3、”的学生情况。要返回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 STU WHERE 班级= 计算机0203

5、例7.6 统计学生成绩表（XSCJ）表中每门课程的最低分，最高分和平均分。要统计每门课程的最低分，最高分和平均分，需要使用GROUP BY子名、MAX、MIN和AVG函数，可以使用下面的语句实现：SELECT 课程号，MIN（成绩）最低分，MAX（成绩）最高分，AVG（成绩）平均分FROM XSCJ GROUP BY 课程号5、使用子查询可以将一个完整的SELECT语句嵌套在其他的SQL语句中，构成子查询，它通常用于一个操作需要依赖于另一个检索到的数据的场合。例7.7 检索有一门或以上功课不及格的学生的学号、姓名、专业、班级。可以使用以下SELECT语句从学生成绩（XSCJ）表中返回有一门或以

6、上功课不及格的学生的学号：SELECT 学号 FROM XSCJ WHERE 成绩=1因以上SQL语句返回的记录数可能多于一个，因此在子查询中需使用IN关键字，语句为：SELECT 学号，姓名，专业，班级 FROM STU WHERE 学号 IN （SELECT 学号 FROM XSCJ WHERE 成绩=1）六、实验内容 还原实验六创建的姓名数据库，对其中的三张数据表执行以下查询1、查询读者表中的所有信息2、显示读者表中的借书证号、单位、姓名、性别信息3、查询所有图书的总编号，书名，作者信息4、查询图书表总编号，书名，作者信息，并将总编号列列名显示为“书号”5、查询单价大于30的图书信息6、

7、查询谭浩强作者所编写书籍的书号和书名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数据库实现以下查询： 查询每个学生的情况以及他（她）所选修的课程； 查询学生的学号、姓名、选修的课程名及成绩； 查询选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩； 查询每一门课的间接

9、先行课。实验七 T-SQL程序设计（二）一、实验目的1、掌握子查询的语句格式，了解子查询的作用；2、进一步掌握SQL Server 查询分析器的使用方法，加深对SQL 语言的嵌套查询语句的理解；3、熟练掌握数据查询中的分组、统计、计算和组合的操作方法；二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、认真独立完成实验内容；3、习后做好实验总结，根据实验情况完成总结报告。三、实验准备1、完成实验四和实验六，成功建立了基本表；2、掌握SQL语句的基本语法，并熟悉查询分析器的工作环境；3、了解子查询的表示方法，熟悉IN 比较符、AN

10、Y、EXISTS操作符的用法。4、了解SELECT语句的GROUP BY和ORDER BY子句的作用。5、了解统计函数和分组统计函数的使用方法。6、熟悉查询分析器的运行环境。四、实验学时2学时五、实验内容1、还原实验四所创建的数据库EDUC，实现一下查询：1）求选修了高等数学的学生学号和姓名；2）求C1 课程的成绩高于张三的学生学号和成绩；3）求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）；4）求其他系中比计算机系学生年龄都小的学生信息；5）求选修了C2 课程的学生姓名；6）求没有选修C2 课程的学生姓名；2、提高操作题目建立“工程-零件”数据库及

11、如下4个表，并输入实验数据，用SQL语句实现如下三个查询：1) 查询上海供应商的供应商代码和联系电话2) 查询红色零件的产地规格信息3) 查询工程的工程代码，工程名，供应商代码，姓名，颜色，所在城市信息4）查询“赵二”负责的工程的名字，供应商的姓名，零件颜色和数量信息5）求供应项目j4红色零件的供应商号及名称6）求没有使用上海供应商零件的工程代号7）查询供应商的代码及其供应的工程的数量8）查询工程的代码及其供应商的数量，只显示供应商数量多于1的工程的信息表结构如下：供应商（S）：列名类型长度是否可为空供应商代码Char5姓名Char50所在城市Char20联系电话Char20工程（J）：列名类

12、型长度是否可为空工程代码Char5工程名Char50负责人Char10预算Char8零件（P）：列名类型长度是否可为空零件代码Char5零件名Char50规格Char10产地Char20颜色Char10供应零件（SPJ）：列名类型长度是否可为空供应商代码Char5工程代码Char5零件代码Char5数量Int4要求：供应商表：供应商代码为主码工程表：工程代码为主码零件表：零件代码为主码供应零件表：供应商代码、工程代码、零件代码为联合主码；供应商代码为外码，参照表、列是供应商表的供应商代码列；工程代码为外码，参照表、列是工程表的工程代码列；零件代码为外码，参照表、列是零件表的零件代码列实验数据如

13、下：供应商： 供应商代码姓名所在城市联系电话S1北京供应商北京010*S2天津供应商天津022*S3重庆供应商重庆023*S4上海供应商1上海021*S5广州供应商广州020*S6上海供应商2上海021*工程：工程代码工程名负责人预算J1工程1丁一200000J2工程2赵二60000J3工程3张三70000J4工程4李四80000J5工程5王五150000零件：零件代码零件名规格产地颜色P1螺丝中济南红色P2主板集成深圳绿色P3显卡独立香港蓝色P4声卡集成天津红色P5网卡100M上海黑色P6鼠标无线上海黑色供应零件：供应商代码工程代码零件代码数量S1J2P450S1J3P5100S2J2P65

14、00S4J1P3150S4J5P1200S5J4P6100S6J4P2903、还原数据库EDUC，实现如下查询1)求学生的总人数；2)求选修了课程的学生人数；3)求课程的课程号和选修该课程的人数；4)求选修课超过3 门课的学生学号。4、提高操作实验建立“图书_读者”数据库及如下3个表，并输入实验数据，用SQL语句实现如下五个查询：1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的2 倍。2）求机械工业出版社出版的各类图书的平均定价，分别用GROUP BY 和GROUP BY ALL表示。3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。4）列出计

15、算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。5）查询计算机类和机械工业出版社出版的图书。表结构如下：图书：列名类型长度是否可为空书号char10类别char12是出版社char50作者char20是书名char50定价money8是读者：列名类型长度是否可为空编号char10姓名char8单位char50是性别char2是电话char15是借阅：列名类型长度是否可为空串号char10书号char10读者编号char10借阅日期datetime8要求：图书表：书号为主码读者表：编号为主码；性别只能是“男”或“女”的CHECK 约束借阅表：串号为主码；书号

16、为外码，参照表、列是图书表的书号列；读者编号为外码，参照表、列是读者表的编号列；书号和读者编号的联合UNIQUE 约束实验数据：图书：书号类别出版社作者书名定价1001计算机机械工业出版社王民数据结构801002计算机机械工业出版社张建平计算机应用201003计算机电子工业出版社王敏数据库技术151004计算机电子工业出版社谭浩强C 语言251005英语中国人民大学出版社张锦芯应用文写作251006管理高等教育出版社Robison管理学151007管理机械工业出版社Fayol工业管理701008数学机械工业出版社李平线性代数501009管理机械工业出版社Durark公司的概念141010数学机

17、械工业出版社徐新国统计学15读者：编号姓名单位性别电话1001丁一数学院男812345671002赵二经济学院男822345671003张三管理学院女832345671004李四文学院男842345671005王五历史文化学院女852345671006孙六物理学院男862345671007周七生命科学院女872345671008徐八化学院男882345671009宋九信息学院女892345671010刘十计算机学院女80234567借阅：串号书号读者编号借阅日期01100110032000-1-102100210052002-3-503100310081998-6-18041004100319

18、97-12-805100510102001-5-406100610012005-7-250710071010997-11-308100810092004-2-109100910041996-9-110101010082000-6-4实验七 T-SQL程序设计（三）一、实验目的1、掌握流控制语句的基本语法；2、能够熟练使用这些流控制语句（包括：begin.end , ifelse, while, break, continue, waitfor, case）；3、掌握系统内置函数的概念及其应用；4、通过定义和使用用户自定义函数，掌握自定义函数的概念及其应用。二、实验要求1、实验前做好上机实验的准

19、备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、认真独立完成实验内容；3、习后做好实验总结，根据实验情况完成总结报告。三、实验准备1、完成实验四和实验六，成功建立了基本表；2. 完成了前面的实验，并且掌握基本的SQL语句的使用；3. 了解流控制语句的基本语法和使用。四、实验学时2学时五、实验指导1、IFELSE语句IFELSE语句是在执行T-SQL语句时强加条件。如果条件满足，则在IF关键字及其条件之后执行T-SQL语句。可选的ELSE关键字引入备用的T-SQL语句，当不满足IF条件时，就执行这个语句。例7.7 若存在学号为“02020101”的学生，则显示该学

20、生的信息，否则插入该学生的记录。其实现语句为： if exists（select 学号 from xsqk where 学号=02020101） print 已存在学号为02020101的学生 else insert into xsqk（学号,姓名）values （02020101,李萍）2、用户自定义函数的应用用户可以使用CREATE FUNCTION语句编写自己的函数，以满足特殊需要。用户自定义函数，可以传递0个或多个参数，并返回一个简单的数值，一般来说返回的都是数值或字符型的数据。例7.8 定义一个自定义函数，实现从出生年月到年龄的计算。自定义函数如下： create function

21、dbo.re_year （vardate datetime,curdate datetime） returns tinyint as begin return datediff（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、定义一个用户自定义的函数，完成如下功能：如果学生有不及格的成绩，则在学生情况表的备注列中输入“有不及格的成绩”，否则输入“没有不及格的成绩”。