BENET3.0第二学期课程-第二章T-SQL高级查询--理论部分.ppt

BENET3.0第二学期课程,第二章T-SQL高级查询,理论部分,2,课程回顾,SQLServer2005身份验证模式分为几种?在服务器角色中sysadmin的作用是什么?请说出固定服务器角色和自定义数据库角色的区别?并指出db_owner的作用.什么是数据库审核功能?主要分为哪几类?如果希望禁用xp_cmdshell,该如何做?简述SQLServer2005利用证书加密和解密数据的过程.,3,技能展示,会使用函数处理查询结果会使用聚合函数查询统计数值会使用GroupBy进行分组查询掌握多表联接查询,4,本章结构,T-SQL高级查询,SQLServer中的聚合函数,分组查询,SQLServer常用函数,多表联接查询,字符串函数,Groupby分组查询,多表查询分类,日期函数,数学函数,系统函数,Having子句分组筛选,内联接查询,外联接查询,5,SQLServer2005常用函数,函数的作用常用函数的分类字符串函数日期函数数学函数系统函数,6,字符串函数,字符串函数用于控制返回给用户的字符串，这些功能仅用于字符型数据。,部分常用的字符串函数,7,字符串函数,案例需求:查询用未缴费的市话账单信息。,Select用户+phonenumber+市话费用+cast(chargeasvarchar(10)+元AS市话费用FromaccountbillWherephonenumberNDIsPaid=0ANDcalltype=0,实施说明:字符串拼接时,需要在两个字符串之间使用“+”从待缴费账单表里accountbill，查询未缴费ispaid=0的账单信息使用字符串拼接，将电话号码字段+市话费用+费用字段转换字符串实现语句:,8,日期函数,不能直接对日期运用数学函数，需要使用日期函数用于操作日期值部分常用日期函数,9,日期函数,案例需求：显示用2008年6月份的通话记录的通话时长实施说明：使用Datediff函数求开始时间与结束时间之间差值，精确到分钟。实现语句,Select用户+phonenumber+通话时长+cast(datediff(mi,starttime,endtime)asvarchar(10)+分钟as通话时长Fromhistory-callWherephonenumber计算开始与结束时间的差(精确到分钟),10,数学函数,数学函数主要用于对数值进行代数运算,部分常用数学函数续表,11,数学函数,案例需求：显示用2008年6月份的通话记录的通话时长(通话时长精确到分钟，不到一分钟的按一分钟计算.)实施说明：使用使用Datediff函数求开始时间与结束时间之间差值，精确到秒。将通话时长除以60秒，使用ceiling函数，取通话时长除以60结果的上限整数值。实现语句,Select用户+phonenumber+通话时长+cast(ceiling(datediff(ss,starttime,endtime)/60.0)asvarchar(10)+分钟as通话时长From“history-call”Wherephonenumber12,系统函数,系统函数用来获取有关SQLServer中对象和设置的系统信息,部分常用的系统函数,13,系统函数,案例需求:查询用户的欠费账单记录，要求并显示用户号码和欠费信息。,Select用户+phonenumber+于+CAST(DATEPART(YEAR,accounttime)asVarchar(10)+年+CAST(DATEPART(MONTH,accounttime)asVarchar(10)+月发生市话费用+cast(chargeasvarchar(10)+元FromaccountbillWherephonenumberNDIsPaid=0ANDcalltype=0OrderByaccounttime,Select用户+phonenumber+于+CAST(DATEPART(YEAR,accounttime)asVarchar(10)+年+CAST(DATEPART(MONTH,accounttime)asVarchar(10)+月发生长话费用+CAST(chargeasvarchar(10)+元FromaccountbillWherephonenumberNDIsPaid=0ANDcalltype=1OrderByaccounttime,实施说明:查询过程是在accountbill表里查询ispaid=0表示未缴费的账单，calltype=0市话，calltype=1长话,charge金额，accounttime为账单时间。实现语句显示用费账单中的市话费用显示用费账单中的长话费用,14,常用函数综合实例,案例需求：用设在2008-10-3115:20:00缴费时，计算用户账单欠费的滞纳金。实施说明:滞纳金是超期的天数欠费金额来计算。用户缴费最后期限是用户账单时间所在月份的下个月的日。,Cast(cast(year(Dateadd(mm,1,accounttime)asvarchar(10)+-+CAST(MONTH(Dateadd(mm,1,accounttime)asvarchar(10)+-2600:00:00)asdatetime),DATEDIFF(dd,最后日期,cast(2008-10-3115:20:00asdatetime),SelectDateDiff(dd,Cast(cast(year(Dateadd(mm,1,accounttime)asvarchar(10)+-+cast(MONTH(Dateadd(mm,1,accounttime)asvarchar(10)+-2600:00:00)asdatetime),Cast(2008-10-3115:20:00asdatetime)as欠费天数,chargeas金额,accounttimeas账单日期FromaccountbillWherephonenumber关键思路:根据账单日期得到账单缴费最后日期计算缴费日期与账单最后日期的超期天数得出欠费天数和欠费的金额如果超期天数大于0，则滞纳金=超期天数欠费金额,该如何计算滞纳金?,15,小结,请思考：如果希望查询距离今天10天之后的日期,如何做?使用Celling函数的参数时为什么要将60写成60.0查询用户的欠费账单记录，要求并显示用户号码和欠费信息,该如何实现?函数在数据库中起到什么样的作用?它的优点是什么?,16,SQL中的聚合函数,聚合函数的作用在查询中会经常碰到的要求是取某些列的最大值、最小值、平均值等信息，有时候还需要计算出究竟查询到多少行数据项。这时候就会使用到聚合函数。,常见的聚合函数SumAvgMax和MinCount,17,常见聚合函数3-1,案例需求:查询用合计欠费金额。,UseTariffsmallGoSelectSum(CHARGE)FromaccountbillWhereisPaid=0ANDphoneNumbero,SelectAvg(CHARGE)as平均话费Fromhistory-accountbillWhereaccounttimebetween2008-06-0100:00:00AND2008-06-3023:59:59,案例需求:历史账单中2008年6月份的用户平均话费。,18,常见聚合函数3-2,案例需求，查询2008年6月电话话费单笔最高费用和单笔最低费用,SelectMax(CHARGE)as单笔最高话费,Min(CHARGE)as单笔最低话费Fromhistory-accountbillWhereaccounttimebetween2008-06-0100:00:00and2008-06-3023:59:59,19,常见聚合函数3-3,案例需求:在Tariffsmall数据库中查询欠费账单数，该如何统计账单个数:案例需求:如果查询欠费用户数，该如何统计不同用户号码的数量：,SelectCount(*)as欠费账单数FromaccountbillWhereispaid=0,SelectCount(Distinctphonenumber)as欠费用户数FromaccountbillWhereispaid=0,20,分组汇总,第一门课6个成绩取平均值,第二门课6个成绩取平均值,第三门课5个成绩取平均值,21,分组查询GROUPBY,SelectCourseID,Avg(Score)as课程平均成绩FromScoreGroupByCourseID,22,分组查询多列分组,怎么样来统计每门课的内部测试中,不同学员的最后成绩？,23,分组查询多列分组,StudentID和CourseID的组合存在重复,需要按照这两个来进行分组，避免StudentID和CourseID同时一样,24,分组查询多列分组,SelectStudentIDas学员编号,CourseIDas内部测试,Avg(Score)as内部测试平均成绩FromScoreGroupByStudentID,CourseID,25,分组查询Having子句进行分组筛选,案例需求：接上面的案例，如果查询内部测试的成绩，只显示“补考过的学员”的成绩怎么处理,增加条件：要求该学员的CourseID在分组内出现过一次以上,1.首先,既按学员编号又按照内部测试的课程名称编号进行分组查询:SelectStudentIDas学员编号,CourseIDas内部测试,Avg(Score)as内部测试平均成绩FromScoreGroupByStudentID,CourseID,26,分组查询Having子句进行分组筛选,SelectStudentIDas学员编号,CourseIDas内部测试,Avg(Score)as内部测试平均成绩FromScoreGroupByStudentID,CourseIDHavingCount(Score)1,27,分组查询对比,WHERE子句从数据源中去掉不符合其搜索条件的数据GROUPBY子句搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句去掉不符合其组搜索条件的各组数据行,28,实施分组查询,案例需求1:在按照部门分类的员工表中，要查询“有多个员工的工资不低于2000的部门编号”案例需求2:查询08年2月到7月的客户账单合计费用,Select部门编号,Count(*)From员工信息表Where工资=2000GROUPBY部门编号HavingCount(*)1,SelectCAST(DATEPART(YEAR,paytime)asvarchar(10)+年+CAST(DATEPART(MONTH,paytime)asvarchar(10)+月AS日期,Sum(CHARGE)合计费用Fromhistory-accountbill“WhereisPaid0ANDpaytimebetween2008-02-0100:00:00and2008-7-3123:59:59GroupByCAST(DATEPART(YEAR,paytime)asvarchar(10)+年+CAST(DATEPART(MONTH,paytime)asVarchar(10)+月,29,多表联接查询,多表查询应用：在上面介绍的学员内部测试成绩查询中，每次显示的都是学员的编号信息，因为该表中只存储了学员的编号。实际上最好显示学员的姓名，而姓名却存储在学员信息表中，像这种需要从多个表中选择或者比较数据项的情况，就需要使用到多表联接查询。多表查询分类:内联接(INNERJOIN)外联接左外联接(LEFTJOIN)右外联接(RIGHTJOIN)完整外联接(FULLJOIN)交叉联接(CROSSJOIN),30,SelectStudents.SName,Score.CourseID,Score.ScoreFromStudents,ScoreWhereStudents.SCode=Score.StudentID,SelectS.SName,C.CourseID,C.ScoreFromScoreasCINNERJOINStudentsasSOn(C.StudentID=S.Scode),多表联结查询内联接,案例需求：在Student和Score表中通过内联接查询学员姓名和成绩.,31,多表联结查询三表联接,案例需求：数据库中存在学员信息、学员成绩，课程名称三张表，要求查询结果不仅要显示学员姓名、分数，而且要通过课程编号来显示课程名称表中对应课程的名称,SelectS.SNameas学员姓名,CS.CourseNameas课程名称,C.Scoreas考试成绩FromStudentsasS,ScoreasC,CourseasCSWhereS.Scode=C.StudentIDandCS.CourseID=C.CourseID,SelectS.SNameas学员姓名,CS.CourseNameas课程名称,C.Scoreas考试成绩FromStudentsasSINNERJOINScoreasCOn(S.SCode=C.StudentID)INNERJOINCourseasCSOn(CS.CourseID=C.CourseID),实现语句,32,多表联接查询外联接,多表联接-外联接的作用,在外部联接中参与联接的表有主从之分，以主表的每行数据去匹配从表的数据列，符合联接条件的数据将直接返回到结果集中；对那些不符合联接条件的列，将被填上Null值（空值）后再返回到结果集中。外联接的分类：左外联接右外联接完整外联接,33,多表联接查询左外联接查询,案例需求:要统计所有学员的考试情况，要求显示所有参加考试学员的每次考试分数，没有参加考试的学员也要显示出来。这时候，以学员信息表为主表、学员成绩表为从表的左外联接查询：,34,多表联接查询左外联接查询,SelectS.SName,C.CourseID,C.ScoreFromStudentsasSLeftOuterJoinScoreasCOnS.SCode=C.StudentID,实现的语句如下:,35,多表联接查询右外联接查询,案例需求:在出版社数据库中，在书名表Titles和出版社Publishers表之间的右外联接查询，列出所有的出版商和对应的书名.如果出版商没有对应书名，则对应书名为NULL.表结构如下所示:,Publishers,36,多表联接查询右外连接查询,SelectTitles.Title_id,Titles.Title,Publishers.Pub_nameFromtitlesRightOuterJoinPublishersOnTitles.Pub_id=Publishers.Pub_id,实现的语句如下:,37,本章结构,T-SQL高级查询,SQLServer中的聚合函数,分组查询,SQLServer常用函数,多表联接查询,字符串函数,Groupby分组查询,多表查询分类,日期函数,数学函数,系统函数,Having子句分组筛选,内联接查询,外联接查询,BENET3.0第二学期课程,第二章T-SQL高级查询,上机部分,39,实验案例1：查询用户通话记录,需求描述：电信公司创建了Tariffsmall数据库用来存储手机用户的通话记录信息.需要查询手机号码用户在2008年8月所有的通话记录(所有通话记录都保存在call表中),40,实验案例1：查询用户通话记录,实现思路：确认要操作的数据库“开始时间”和“结束时间”的默认类型是datetime型,要考虑是否需要转换.一个月的周期,考虑如何设定条件,41,实验案例1：查询用户通话记录,学员练习：根据案例1的要求完成查询工作,20分钟内完成,42,实验案例2：查询用户市话和长话时长,需求描述：电信公司创建了Tariffsmall数据库用来存储手机用户的通话记录信息.需要查询手机号码用户在2008年8月所有市话（本市通话）的合计时长需要查询手机号码用户在2008年8月所有长话（长途通话）的合计时长,43,实验案例2：查询用户市话和长话时长,实现思路：获取每次通话的开始时间和结束时间的差值（实际通话时间都要计算到秒、所以按秒来取值）将得出的差值精确到分钟（要求：不到一分钟按一分钟来计算）把每笔通话的时间求和，满足需求。