数据库实验四(共19页).doc

《数据库实验四(共19页).doc》由会员分享，可在线阅读，更多相关《数据库实验四(共19页).doc（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上实验项目名称： T-SQL程序设计 实验学时： 4 同组学生姓名： 实验地点： b513 实验日期： 2012.11.27 2012.12.04 实验成绩： 批改教师： 批改时间： 一、实验目的和要求1、掌握TSQL中运算符和表达式的使用；2、通过对Select的使用，掌握Select语句的结构及其应用；3、掌握TSQL中几个常用流程控制语句的使用；4、掌握系统内置函数的概念及其应用；5、通过定义和使用用户自定义函数，掌握自定义函数的概念及其应用。二、实验设备、环境设备：奔腾或奔腾以上计算机；环境：WINDOWS 2000 SERVER或WINDOWS 2003 SE

2、RVER、SQL Server2005中文版。三、实验步骤1、根据题目要求熟悉SQL Server2005的各种管理工具；2、分析题意，重点分析题目要求并给出解决方法；3、按题目要求完成实际操作任务，并将相关文档资料保存在以自己学号命名的文件夹中；4、提交完成的实验结果。四、实验内容一、SQL查询（*使用SQL Server样例数据库pubs完成）1、简单查询（1）查询所有作者的姓名和作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”表明显示信息是身份证信息；（authors表）（2）改变显示列名。显示所有作者的姓名信息和作者号信息，要求用“名”和“姓”来区别fname和lname，

3、“作者编号”来区分作者号；（authors表）（3）查询所有书在价格提高10%后的价格和书名信息；（titles表）（4）查询所有书的书号和税后价格。（titles表，royalty列表示税率）；（5）查询所有作者的姓和“名的第一个字符”以及作者号；（authors表，SUBSTRING函数）（6）查询邮政编码大于9000的作者姓名和电话信息；（authors表）（7）查询出版日期在1/1/1991到12/31/1991之间的书名（书名限制为38个字符）和出版日期；（titles表，SUBSTRING函数）（8）查询书的类型是mod_cook或trad_cook的书名和它的类型；（titles

4、表）（9）查询店名中包含Book的店的信息；(stores表)；（10）查询书名以T开头或者出版号为0877，且价格大于16美元的书的信息；（titles表）（11）查询所有作者的所在城市和州名，要求没有重复信息；（authors表）（12）按照类型的升序和价格的降序显示书的信息；（titles表）2、生成汇总数据（1）计算多少种书已被定价；（titles表）（2）计算每本书的书号及它的售书总量；（sales表）（3）求销售量大于30的书号及销售数量；（sales表）（4）显示在1994年1月1日到1994年10月31日间，每本书的销售总额；（sales表，titles表）3、连接查询（1）求

5、每本杂志上刊登的文章；（titles, publishers表）（2）求某书店销售某书的数量；（titles, stores, sales表）（3）查询所有合著的书及其作者。（4）显示所有已销售的书名。4、子查询（1）查询有销售记录的所有书信息，包括书的编号、书名、类型和价格；（2）求已销售的书的信息；二、函数1、自定义一个名为Sage_func函数，按出生年月计算年龄。然后从Student表中检索出含有年龄的学生信息。2、定义一个名为grade_func的自定义函数，将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。三、流程控制1、Student

6、表中若存在学号为“”的学生，则显示已存在的信息，否则插入该学生的记录。然后从student表中删除学号为“”的学生记录，重新执行该程序，观察与上次有何不同。2、使用While语句求1到100之间的累加和，输出结果。五、问题解答及实验结果1、简单查询（1）查询所有作者的姓名和作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”表明显示信息是身份证信息；（authors表）1. select 身份证号：+ au_id,au_fname,au_lname from authors;（2）改变显示列名。显示所有作者的姓名信息和作者号信息，要求用“名”和“姓”来区别fname和lname，“作者

7、编号”来区分作者号；（authors表）2. select au_id 作者编号,au_fname 姓,au_lname 名from authors; （3）查询所有书在价格提高10%后的价格和书名信息；（titles表）3. select title,price*1.1 from titles;（4）查询所有书的书号和税后价格。（titles表，royalty列表示税率）；select title_id,price*(1+royalty) price from titles;（5）查询所有作者的姓和“名的第一个字符”以及作者号；（authors表，SUBSTRING函数）select au_

8、id,au_lname,SUBSTRING(au_fname,1,1) from authors;（6）查询邮政编码大于9000的作者姓名和电话信息；（authors表）select au_fname,au_lname,phone from authors where zip9000;（7）查询出版日期在1/1/1991到12/31/1991之间的书名（书名限制为38个字符）和出版日期；（titles表，SUBSTRING函数）select SUBSTRING(title,1,38),pubdate from titles where pubdate 1/1/1991 and pubdate

9、16;（11）查询所有作者的所在城市和州名，要求没有重复信息；（authors表）select DISTINCT city,state from authors;（12）按照类型的升序和价格的降序显示书的信息；（titles表）select *from titles order by type asc,price desc;2、生成汇总数据（1）计算多少种书已被定价；（titles表）select count(title_id) from titles where price is not null;（2）计算每本书的书号及它的售书总量；（sales表）select title_id,sum(

10、qty) qty from sales group by title_id;（3）求销售量大于30的书号及销售数量；（sales表）select title_id,sum(qty) qty from sales group by title_id having (sum(qty)30);（4）显示在1994年1月1日到1994年10月31日间，每本书的销售总额；（sales表，titles表）select titles.title_id , sum(sales.qty)*titles.price 销售总额from sales,titles where titles.pubdate betwee

11、n 1/1/1991 and 12/31/1991group by sales.title_id,titles.price,titles.title_idhaving(sales.title_id=titles.title_id)3、连接查询（1）求每本杂志上刊登的文章；（titles, publishers表）select titles.title,publishers.pub_name from titles,publisherswhere titles.pub_id = publishers.pub_id（2）求某书店销售某书的数量；（titles, stores, sales表）sel

12、ect a.title,b.stor_name,c.qty from titles a,stores b ,sales c where a.title_id=c.title_id and b.stor_id=c.stor_id（3）查询所有合著的书及其作者。select distinct(a.title_id),b.au_id,a.au_idfrom titleauthor a,titleauthor b where a.title_id=b.title_id （4）显示所有已销售的书名。select title,sum(qty) qty from sales,titleswhere sale

13、s.title_id = titles.title_id group by title4、子查询（1）查询有销售记录的所有书信息，包括书的编号、书名、类型和价格；select sales.title_id,titles.title,titles.type,titles.pricefrom titles,sales where sales.title_id=titles.title_id（2）求已销售的书的信息；二函数1、自定义一个名为Sage_func函数，按出生年月计算年龄。然后从Student表中检索出含有年龄的学生信息。create function Sage_func ( vardat

14、e datetime , curdate datetime ) returns tinyintas beginreturn datediff ( yy , vardate , curdate ) end select SNO as 学号, SName as 姓名,dbo.Sage_func(Birthday,getdate() as 年龄 from Student2、定义一个名为grade_func的自定义函数，将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。create function grade_func ( Scorce int ) re

15、turns tinyintas begin return Scorce/20 end select SName as 姓名, dbo.grade_func(Scorce) as 五级分制 from Grade,Student where Grade.SNO=Student.SNO三、流程控制1.if exists (select * from Student where SNO=)begin select * from Student where SNO= endelse begininsert into Student values(,王军,男,CS01,下关#,1976-12-21 00:

16、00:00,1.82,10) end delete from Student where SNO=2、使用While语句求1到100之间的累加和，输出结果。流程控制语句为：Declare sum int, start int set start=1 set sum=0beginwhile(start101)begin set sum=sum+start set start=start+1endprint sumend六、实验体会和收获这次实验要求我们用各种SQL查询命令进行对表的一些简单操作，例子很多，在练习的同时也掌握了SQL查询语句，俗话说熟能生巧嘛，包括了单表查询，连接查询，子查询等等，但即使这样，我觉得还需要继续练习，要做到在没有老师同学帮助，没有参考资料的时候能够独立完成的漂漂亮亮。这次实验的内容比较多，时间比较紧，所以在做的时候有点匆忙，幸好在上实验课之前有预习，在理论课上对数据库理论知识的了解基础上，并在自己动手之前，有老师的详细讲解和指导，我慢慢熟悉了对查询操作，在实验课上，操作起来也更便捷和熟练，重要的一点，在上课时要记得认真做笔记，把自己的语句记下来，认真整理实验结果。最后，实验安排了函数的训练，这是SQL Server功能的又一体现，不过强大归强大，还必须得掌握才行，我得加油了！专心-专注-专业