SQl存储过程高级查询

ACCPV4.0第四章高级查询ACCPV4.02回顾指出下列语句的错误：CREATETABLEbank(userNameVARCHAR(10),balanceMONEY)INSERTINTObank(cardNo,userName,balance)VALUES('张三',500)INSERTINTObank(cardNo,userName,balance)VALUES('李四',700)DECLAREmymoneyINT(4)mymoney=0SELECTmymoney=balanceFROMbank建表语句后必须添加GO标志DECLARE@mymoneyINTSET@mymoney=0WHEREuserName='张三’ACCPV4.03回顾IF@mymoney100print'卡上目前余额不足100，请及时充值!'print'卡上余额为：'+@mymoneyprint'您的年利息为:'SELECT利息=CASEWHENbalance100THENbalance*0.01WHENbalance1000THENbalance*0.20WHENELSEbalance*0.10FROMbankWHEREuserName='张三‘GO多条语句添加BEGIN-END去掉WHEN缺少配对的END转换:convert(varchar(5),@mymoney)ACCPV4.04目标掌握简单子查询的用法掌握IN子查询的用法掌握EXISTS子查询的用法应用T-SQL进行综合查询ACCPV4.05什么是子查询3-1学员信息表问题：编写T-SQL语句，查看年龄比“李斯文”大的学员，要求显示这些学员的信息？分析：第一步：求出“李斯文”的年龄；第二步：利用WHERE语句，筛选年龄比“李斯文”大的学员；ACCPV4.06什么是子查询3-2实现方法一：采用T-SQL变量实现DECLARE@ageINT--定义变量，存放李斯文的年龄SELECT@age=stuAgeFROMstuInfoWHEREstuName=‘李斯文’--求出李斯文的年龄--筛选比李斯文年龄大的学员SELECT*FROMstuInfoWHEREstuAge@ageGOACCPV4.07什么是子查询3-3实现方法二：采用子查询实现SELECT*FROMstuInfoWHEREstuAge(SELECTstuAgeFROMstuInfowherestuName='李斯文')GO子查询子查询在WHERE语句中的一般用法：SELECT…FROM表1WHERE字段1(子查询)外面的查询称为父查询，括号中嵌入的查询称为子查询UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个ACCPV4.08使用子查询替换表连接3-1问题：查询笔试刚好通过（60分）的学员。学员信息表和成绩表ACCPV4.09使用子查询替换表连接3-2实现方法一：采用表连接SELECTstuNameFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam=60GO内连接（等值连接）ACCPV4.010使用子查询替换表连接3-3实现方法二：采用子查询SELECTstuNameFROMstuInfoWHEREstuNo=(SELECTstuNoFROMstuMarksWHEREwrittenExam=60)GO子查询一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据表连接更适合于查看多表的数据ACCPV4.011IN子查询4-1问题：查询笔试刚好通过的学员名单。如何解决？ACCPV4.012IN子查询4-2解决方法：采用IN子查询SELECTstuNameFROMstuInfoWHEREstuNoIN(SELECTstuNoFROMstuMarksWHEREwrittenExam=60)GO将＝号改为ININ后面的子查询可以返回多条记录常用IN替换等于（＝）的比较子查询ACCPV4.013IN子查询4-3问题：查询参加考试的学员名单学员信息表和成绩表（重抓本图）分析：判断一个学员是否参加考试其实很简单，只需要查看该学员对应的学号是否在考试成绩表stuMarks中出现即可ACCPV4.014IN子查询4-4/*--采用IN子查询参加考试的学员名单--*/SELECTstuNameFROMstuInfoWHEREstuNoIN(SELECTstuNoFROMstuMarks)GO演示：使用IN子查询参考语句ACCPV4.015NOTIN子查询问题：查询未参加考试的学员名单分析：加上否定的NOT即可ACCPV4.016EXISTS子查询4-1例如：数据库的存在检测IFEXISTS（SELECT*FROMsysDatabasesWHEREname=’stuDB’）DROPDATABASEstuDBCREATEDATABASEstuDB…….—建库代码略ACCPV4.017EXISTS子查询4-2IFEXISTS（子查询）语句EXISTS子查询的语法：如果子查询的结果非空，即记录条数1条以上，则EXISTS（子查询）将返回真（true），否则返回假(false)EXISTS也可以作为WHERE语句的子查询，但一般都能用IN子查询替换ACCPV4.018EXISTS子查询4-3问题：检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分分析：是否有人笔试成绩达到80分以上，可以采用EXISTS检测ACCPV4.019EXISTS子查询4-4/*--采用EXISTS子查询，进行酌情加分--*/IFEXISTS(SELECT*FROMstuMarksWHEREwrittenExam80)BEGINprint'本班有人笔试成绩高于80分，每人加2分，加分后的成绩为：'UPDATEstuMarksSETwrittenExam=writtenExam+2SELECT*FROMstumarksENDELSEBEGINprint'本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩：'UPDATEstuMarksSETwrittenExam=writtenExam+5SELECT*FROMstumarksENDGO演示：使用EXISTS子查询参考语句ACCPV4.020NOTEXISTS子查询2-1问题：检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都60分），则试题偏难，每人加3分，否则，每人只加1分分析：没有一人通过考试，即不存在“笔试和机试成绩都60分”，可以采用NOTEXISTS检测ACCPV4.021NOTEXISTS子查询2-2IFNOTEXISTS(SELECT*FROMstuMarksWHEREwrittenExam60ANDlabExam60)BEGINprint'本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：'UPDATEstuMarksSETwrittenExam=writtenExam+3,labExam=labExam+3SELECT*FROMstuMarksENDELSEBEGINprint'本班考试成绩一般，每人只加1分，加分后的成绩为：'UPDATEstuMarksSETwrittenExam=writtenExam+1,labExam=labExam+1SELECT*FROMstuMarksENDGO演示：使用NOTEXISTS子查询参考语句ACCPV4.022T-SQL语句的综合应用学员信息表和成绩表应到人数：5人实到人数4人，缺考1人ACCPV4.023T-SQL语句的综合应用如何实现？本次考试的缺考情况比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。加分后重新统计通过情况统计通过率ACCPV4.024T-SQL语句的综合应用1.提示:使用子查询统计缺考情况：应到人数：SELECTcount(*)FROMstuInfo实到人数：SELECTcount(*)FROMstuMarks2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（LEFTJOIN）；SELECTstuName…FROMstuInfoLEFTJOINstuMarks…2)要求新加一列“是否通过（isPass）”，可采用CASE…END。为了便于后续的通过率统计，通过则为1，没通过为0SELECT…isPass=CASEWHENwrittenExam=60……THEN1ELSE0END……3)要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT…INTOnewTable语句，生成新表并保存数据ACCPV4.025T-SQL语句的综合应用3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过97分：1)使用IF语句判断笔试还是机试偏低，决定对笔试还是机试提分；2)使用WHILE循环给每个学员加分，缺考的除外，当最高分超过97分时退出循环；3)因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新isPass（是否通过）列。UPDATEnewTableSETisPass=CASEWHENwrittenExam=60andlabExam=60THEN1ELSE0ENDACCPV4.026T-SQL语句的综合应用4.提分后，统计学员的成绩和通过情况：1）使用别名实现中文字段名，即SELECT姓名=stuName,学号=stuNo…2）如果某个学员的成绩为NULL(空)，则替换为”缺考”，否则原样显示；3）isPass列中的1替换为是，0替换为否；SELECT……,机试成绩=CASEWHENlabExamISNULLTHEN'缺考'ELSEconvert(varchar(5),labExam)END,是否通过=CASEWHENisPass=1THEN'是'ELSE'否'END……ACCPV4.027T-SQL语句的综合应用5.提分后统计学员的通过率情况：1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通过人数；2）通过率：同理，isPass列的平均值*100即是通过率；ACCPV4.028T-SQL参考语句/*--本次考试的原始数据--*/--SELECT*FROMstuInfo--SELECT*FROMstuMarks/*--------------统计考试缺考情况----------------------*/SELECT应到人数=(SELECTcount(*)FROMstuInfo),--应到人数为子查询表达式的别名实到人数=(SELECTcount(*)FROMstuMarks),缺考人数=((SELECTcount(*)FROMstuInfo)-(SELECTcount(*)FROMstuMarks))ACCPV4.029T-SQL参考语句/*----统计考试通过情况，并将结果存放在新表newTable中---*/IFEXISTS(SELECT*FROMsysobjectsWHEREname='newTable')DROPTABLEnewTableSELECTstuName,stuInfo.stuNo,writtenExam,labExam,isPass=CASEWHENwrittenExam=60andlabExam=60THEN1ELSE0ENDINTOnewTableFROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo--SELECT*FROMnewTable--查看统计结果，可用