任务4使用高级查询获取论坛管理数据库中的数据

1、论坛管理系统论坛管理系统任务任务4 使用高级查询获取论坛管理数据库中的数据使用高级查询获取论坛管理数据库中的数据基于工作任务的基于工作任务的SQL Server 2008SQL Server 2008数据库应用数据库应用任务目标o 会使用简单子查询获取数据o 能使用IN子查询或EXISTS子查询获取数据o 会使用T-SQL进行综合查询o 通过分组，培养学生的沟通能力及团队协作能力o 培养学生分析问题解决问题的能力使用T-SQL语句实现论坛年度评估：o 论坛人气评估：论坛总点击率1000，为“人气熊旺旺”；否则就为“一般般” o 年度品牌版块：主帖量最多的版块o 年度倒胃版块：主帖量最少的版块o

2、 年度回帖人气最旺奖：回帖的点击率排名前2名的版主o 年度业绩最差版主：版块点击率低于500或主帖量等于0客户需求品牌版块品牌版块年度倒胃版块年度倒胃版块年度回帖人气最旺会员年度回帖人气最旺会员年度业绩最差版主年度业绩最差版主 客户需求假定心酸果冻在“.NET技术”版块发主帖。怯怯的问：“什么是.NET啊？”帖的内容为“微软的.NET广告超过半个北京城啊。”要求使用T-SQL实现以下业务规则：o 用户发主帖后，需要更新相应版块（.NET技术版块）的主帖数，主帖数+1 o 在论坛上发布用户（心酸果冻）的新帖 心酸果冻心酸果冻的新帖的新帖客户需求主帖的回复数量和点击率都主帖的回复数量和点击率都+1

3、钢镚儿的回复钢镚儿的回复假定钢镚儿回复主帖：“什么是.NET啊？”，他笑呵呵的回复道：“.NET是微软力推的企业级信息网络共享平台。”要求使用T-SQL实现以下业务规则：o 用户回帖后，需要更新对应主帖的信息：回复数量+ 1，点击率+1o 用户回帖后，还需要更新对应版块的点击率，点击率+1o 在论坛上发布主帖和跟帖任务分析第一个客户需求中，显然需要用到子查询。例如：论坛人气评估的实现，需要如下查询来实现。IF(论坛所有版块的总点击率1000) Print 人气熊旺旺，大家辛苦了！ ELSE Print 人气一般般，大家加油啊！而其中的“论坛所有版块的总点击率”需要进一步通过查询来得到。对于年度

4、品牌版块和年度倒胃版块，需要先查询出主帖量最多和最少值，再进一步查询。同理，其他评估内容也非常类似。任务分析第二个客户需求中，用户发主帖。o 显然要向主帖表（BBSTopic）中插入主帖记录。o 先查看主帖表（BBSTopic）的表结构，需要发帖人的账号ID和所在版块ID（SectorID），因此我们需要先查询出“心酸果冻”的账号ID（AccountID）以及“.NET技术”板块的版块ID。o 为了使后面的语句能够继续使用这些值，我们还需要定义变量来存放它们。任务分析第三个客户需求中，用户回帖。o 显然要向回帖表（BBSReply）中插入回帖记录。o 先查看回帖表（BBSReply）的表结构，

5、需要发帖人ID、帖子编号（TopicID）和所在版块ID（SectorID）。因此，我们需要先查询出“钢镚儿”的账号ID（AccountID）、按照帖子标题“什么是.NET啊？”查询出帖子编号（TopicID）和所在版块ID（SectorID）。o 同样为了使后面的语句能继续使用这些值，我们需要定义变量来存放它们。任务实施-【信息收集】o 什么是子查询？o 带有比较运算符的子查询o 带有IN和NOT IN的子查询o 带有EXISTS和NOT EXISTS的子查询任务实施-【信息收集】学员信息表学员信息表问题：问题：编写编写T-SQLT-SQL语句，查看年龄比语句，查看年龄比“李斯文李斯文”大的

6、学员，要求显示这大的学员，要求显示这些学员的信息些学员的信息 ？分析：分析： 第一步：求出第一步：求出“李斯文李斯文”的年龄；的年龄；第二步：利用第二步：利用WHEREWHERE语句，筛选年龄比语句，筛选年龄比“李斯文李斯文”大的学员；大的学员；子查询和带有比较运算符的子查询 子查询在WHERE语句中的一般用法： SELECT FROM 表1 WHERE 字段1 (子查询)外面的查询称为父查询，括号中嵌入的查询称为子查询。使用比较运算符引入的子查询语法如下：SELECT 列名FROM 表名WHERE 表达式 比较运算符（子查询）任务实施-【信息收集】带有IN和NOT IN的子查询在嵌套子查询中

7、，子查询的结果往往是一个集合，所以IN和NOT IN是嵌套子查询中经常使用的关键字。其中，IN表示属于，即外层查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOT IN表示不属于。使用IN引入的子查询语法如下：SELECT 列名FROM 表名WHERE NOT IN（子查询）任务实施-【信息收集】带有EXISTS和NOT EXISTS的子查询使用EXISTS关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实际上不返回任何数据；它只返回TRUE或FALSE值。使用EXISTS引入的子查询语法如下：SELECT 列名FRO

8、M 表名WHERE NOT EXISTS（子查询）任务实施-【信息收集】任务实施-【制定计划】（1）根据本任务进行分组，小组成员讨论并制定具体实施步骤，明确任务分工。（2）实现论坛年度评估（20分钟）、发主帖（15分钟）和回帖（20分钟）功能。（3）每组提交一份论坛年度评估和发帖、回帖的SQL语句脚本。（4）教师点评，学生互评。任务实施-【具体实现】o 分析数据库o 确定使用哪几个表，以及各表存储的数据信息o 教师讲解并分析解题思路o 编写SQL语句o 调试语法错误和程序逻辑错误o 运行程序，查看执行结果任务实施-【具体实现】-评选品牌版块和倒胃版块：主帖数量最多和最少的版块print 年度品

9、牌版块： SELECT 版块名称=SectorName,主帖数量=TopicCount FROM BBSSectorWHERE TopicCount=(SELECT MAX(TopicCount) FROM BBSSector)print 年度倒胃版块：SELECT 版块名称=SectorName,主帖数量=TopicCount FROM BBSSectorWHERE TopicCount=(SELECT MIN(TopicCount) FROM BBSSector) -评选回帖人气最旺的前两名作者print 年度回帖人气最旺的前两名获奖版主：SELECT 大名=NickName FROM B

10、BSAccount WHERE AccountID IN (SELECT TOP 2 AccountID FROM BBSTopic ORDER BY ClickingRate DESC)-评选最差版主：如果存在发帖量为或点击率低于的版块，则评选最差版主IF EXISTS (SELECT * FROM BBSSector WHERE TopicCount=0 OR ClickingRate=500) BEGIN print 请下列版块的斑竹加加油哦！ SELECT 版块名称=SectorName,主帖数量=TopicCount,点击率=ClickingRate FROM BBSSector W

11、HERE TopicCount=0 OR ClickingRate各位大虾注意了，本论坛即将发布年度无记名评奖1000，人气较旺IF (SELECT SUM(ClickingRate) FROM BBSSector)1000 print 论坛人气年度评估：熊旺旺，大家辛苦了！ELSE print 论坛人气年度评估：一般般，大家加油啊！任务实施-【具体实现】/*-发主帖:心酸果冻在.Net技术版块发帖：怯怯的问：什么是.Net啊？微软的.Net广告超过半个北京城啊。-*/-定义变量存放用户编号和版块编号DECLARE AccountID INT,SectorID INT -获取心酸果冻的用户编号

12、SELECT AccountID=AccountID FROM BBSAccount WHERE NickName=心酸果冻 -获取.Net版块的编号SELECT SectorID=SectorID FROM BBSSector WHERE SectorName LIKE %.Net技术%-插入主帖表INSERT INTO BBSTopic (Title,TopicText,ReplyCount,AccountID,SectorID)VALUES(什么是.Net啊？,微软的.Net广告超过半个北京城啊。,0,AccountID,SectorID)-更新版块表：.Net技术版块主帖数+1UPDA

13、TE BBSSector SET TopicCount=TopicCount+1 WHERE SectorID=SectorID-在论坛上发布用户（心酸果冻）的新帖SELECT 发帖作者=心酸果冻,发帖时间=convert(varchar(10),TopicDate,111),标题=Title,正文=TopicText FROM BBSTopic WHERE TopicID=IDENTITYGO任务实施-【具体实现】/*-用户回帖:钢镚儿回复主帖：什么是.Net啊？回复道：”.NET是微软力推的企业级信息网络共享平台。“-*/-定义变量存放用户编号、版块编号和帖子编号DECLARE Accou

14、ntID INT,SectorID INT,TopicID INT -获取钢镚儿的用户编号SELECT AccountID=AccountID FROM BBSAccount WHERE NickName=钢镚儿 -获取.Net版块的编号和帖子编号SELECT SectorID=SectorID,TopicID=TopicID FROM BBSTopic WHERE Title LIKE %什么是.Net啊%-插入回帖表INSERT INTO BBSReply (ReplyText,TopicID,AccountID,SectorID) VALUES(.NET是微软力推的企业级信息网络共享平台

15、。,TopicID,AccountID,SectorID)-更新主帖表：回复数+1，单击率+1UPDATE BBSTopic SET ReplyCount=ReplyCount+1,ClickingRate=ClickingRate+1 WHERE TopicID=TopicID -更新版块表：点击率+1UPDATE BBSSector SET ClickingRate=ClickingRate+1 WHERE SectorID=SectorID -在论坛上发布主帖和跟帖PRINT 主帖SELECT 发帖作者=BBSAccount.NickName,发帖时间=convert(varchar(1

16、0),TopicDate,111), 标题=Title,正文=TopicText,回复数=ReplyCount,点击率=ClickingRate FROM BBSTopic inner join BBSAccount on BBSTopic.AccountID=BBSAccount.AccountID WHERE TopicID=TopicIDIF EXISTS(SELECT * FROM BBSReply WHERE TopicID=TopicID)BEGINPRINT 跟帖SELECT 正文=ReplyText,回帖时间=ReplyDate,点击率=ClickingRate,回帖人=BBS

17、Account.NickName FROM BBSReply inner join BBSAccount on BBSReply.AccountID=BBSAccount.AccountIDWHERE TopicID=TopicID ORDER BY ReplyDate DESCENDELSEPRINT 无跟帖GO 任务实施-【检查与评估】o 学生提交完成上述任务的SQL语句文件o 教师检查子查询IN/EXISTS的语法是否准确o 教师检查论坛年度评估和发帖回帖的结果是否准确o 教师检查是否有必要的注释o 教师对普遍存在的问题集中讲解拓展训练请同学们完成下面的任务:客户需求：（1）版主删帖：为了维护论坛环境的“空气清新”，版主会定期检查帖子或核实网友对某个帖