SQl存储过程高级查询

ACCP V4.0,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,*,第四章,高级查询,回顾,指出下列语句的错误：,CREATE TABLE bank,(,userName,VARCHAR(10),balance MONEY,),INSERT INTO,bank(cardNo,userName,balance,),VALUES(,张三,500),INSERT INTO,bank(cardNo,userName,balance,),VALUES(,李四,700),DECLARE,mymoney,INT(4),mymoney,=0,SELECT,mymoney,=balance FROM bank,建表语句后必须添加,GO,标志,DECLARE ,mymoney,INT,SET ,mymoney,=0,WHERE,userName,=,张三,2,回顾,IF ,mymoney,100,print ,卡上目前余额不足,100,，请及时充值,!,print ,卡上余额为：,+,mymoney,print ,您的年利息为,:,SELECT,利息,=CASE,WHEN balance 1000 THEN balance*0.20,WHEN ELSE balance*0.10,FROM bank WHERE,userName,=,张三,GO,多条语句添加,BEGIN-END,去掉,WHEN,缺少配对的,END,转换,:,convert(varchar(5),mymoney,),3,目标,掌握简单子查询的用法,掌握,IN,子查询的用法,掌握,EXISTS,子查询的用法,应用,T-SQL,进行综合查询,4,什么是子查询,3-1,学员信息表,问题：,编写,T-SQL,语句，查看年龄比“李斯文”大的学员，要求显示这些学员的信息 ？,分析：,第一步：求出“李斯文”的年龄；,第二步：利用,WHERE,语句，筛选年龄比“李斯文”大的学员；,5,什么是子查询,3-2,实现方法一：采用,T-SQL,变量实现,DECLARE age INT -,定义变量，存放李斯文的年龄,SELECT age=,stuAge,FROM,stuInfo,WHERE,stuName,=,李斯文,-,求出李斯文的年龄,-,筛选比李斯文年龄大的学员,SELECT * FROM,stuInfo,WHERE,stuAge,age,GO,6,什么是子查询,3-3,实现方法二：采用子查询实现,SELECT * FROM,stuInfo,WHERE,stuAge,( SELECT,stuAge,FROM,stuInfo,where,stuName,=,李斯文,),GO,子查询,子查询在,WHERE,语句中的一般用法：,SELECT FROM,表,1 WHERE,字段,1 (,子查询,),外面的查询称为父查询，括号中嵌入的查询称为子查询,UPDATE,、,INSERT,、,DELETE,一起使用，语法类似于,SELECT,语句,将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个,7,使用子查询替换表连接,3-1,问题：,查询笔试刚好通过（,60,分）的学员。,学员信息表和成绩表,8,使用子查询替换表连接,3-2,实现方法一：采用表连接,SELECT,stuName,FROM,stuInfo,INNER JOIN,stuMarks,ON,stuInfo.stuNo,=,stuMarks.stuNo,WHERE,writtenExam,=60,GO,内连接（等值连接）,9,使用子查询替换表连接,3-3,实现方法二：采用子查询,SELECT,stuName,FROM,stuInfo,WHERE,stuNo,=,(SELECT,stuNo,FROM,stuMarks,WHERE,writtenExam,=60),GO,子查询,一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换,子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据,表连接更适合于查看多表的数据,10,IN,子查询,4-1,问题：,查询笔试刚好通过的学员名单。,如何解决？,11,IN,子查询,4-2,解决方法：采用,IN,子查询,SELECT,stuName,FROM,stuInfo,WHERE,stuNo,IN,(SELECT,stuNo,FROM,stuMarks,WHERE,writtenExam,=60),GO,将号改为,IN,IN,后面的子查询可以返回多条记录,常用,IN,替换等于（）的比较子查询,12,IN,子查询,4-3,问题：,查询参加考试的学员名单,学员信息表和成绩表（重抓本图）,分析：,判断一个学员是否参加考试其实很简单，只需要查看该学员对应的学号是否在考试成绩表,stuMarks,中出现即可,13,IN,子查询,4-4,/*-,采用,IN,子查询参加考试的学员名单,-*/,SELECT,stuName,FROM,stuInfo,WHERE,stuNo,IN (SELECT,stuNo,FROM,stuMarks,),GO,演示：使用,IN,子查询,参考语句,14,NOT IN,子查询,问题：,查询,未,参加考试的学员名单,分析：,加上否定的,NOT,即可,15,EXISTS,子查询,4-1,例如：数据库的存在检测,IF,EXISTS,（,SELECT * FROM,sysDatabases,WHERE name=,stuDB,）,DROP DATABASE,stuDB,CREATE DATABASE,stuDB,.,建库代码略,16,EXISTS,子查询,4-2,IF EXISTS,（子查询）,语句,EXISTS,子查询的语法：,如果子查询的结果非空，即记录条数,1,条以上，则,EXISTS,（子查询）将返回真（,true,），否则返回假,(false),EXISTS,也可以作为,WHERE,语句的子查询，但一般都能用,IN,子查询替换,17,EXISTS,子查询,4-3,问题：,检查本次考试，本班如果有人笔试成绩达到,80,分以上，则每人提,2,分；否则，每人允许提,5,分,分析：,是否有人笔试成绩达到,80,分以上，可以采用,EXISTS,检测,18,EXISTS,子查询,4-4,/*-,采用,EXISTS,子查询，进行酌情加分,-*/,IF,EXISTS,(SELECT * FROM,stuMarks,WHERE,writtenExam,80),BEGIN,print ,本班有人笔试成绩高于,80,分，每人加,2,分，加分后的成绩为：,UPDATE,stuMarks,SET,writtenExam,=writtenExam+2,SELECT * FROM,stumarks,END,ELSE,BEGIN,print ,本班无人笔试成绩高于,80,分，每人可以加,5,分，加分后的成绩：,UPDATE,stuMarks,SET,writtenExam,=writtenExam+5,SELECT * FROM,stumarks,END,GO,演示：使用,EXISTS,子查询,参考语句,19,NOT EXISTS,子查询,2-1,问题：,检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都,60,分），则试题偏难，每人加,3,分，否则，每人只加,1,分,分析：,没有一人通过考试，即不存在“笔试和机试成绩都,60,分”，可以采用,NOT EXISTS,检测,20,NOT EXISTS,子查询,2-2,IF,NOT EXISTS,(SELECT * FROM,stuMarks,WHERE,writtenExam,60 AND,labExam,60),BEGIN,print ,本班无人通过考试，试题偏难，每人加,3,分，加分后的成绩为：,UPDATE,stuMarks,SET,writtenExam,=writtenExam+3,labExam=labExam+3,SELECT * FROM,stuMarks,END,ELSE,BEGIN,print ,本班考试成绩一般，每人只加,1,分，加分后的成绩为：,UPDATE,stuMarks,SET,writtenExam,=writtenExam+1,labExam=labExam+1,SELECT * FROM,stuMarks,END,GO,演示：使用,NOT EXISTS,子查询,参考语句,21,T-SQL,语句的综合应用,学员信息表和成绩表,应到人数：,5,人,实到人数,4,人，缺考,1,人,22,T-SQL,语句的综合应用,如何实现？,本次考试的缺考情况,比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过,97,分 。加分后重新统计通过情况,统计通过率,23,T-SQL,语句的综合应用,1.,提示,:,使用子查询统计缺考情况：,应到人数：,SELECT,count(*),FROM,stuInfo,实到人数：,SELECT count(*) FROM,stuMarks,2.,提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过,1),提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（,LEFT JOIN,）；,SELECT,stuName,FROM,stuInfo,LEFT JOIN,stuMarks,2),要求新加一列“是否通过（,isPass,）”，可采用,CASE END,。为了便于后续的通过率统计，通过则为,1,，没通过为,0,SELECT ,isPass,=,CASE,WHEN,writtenExam,=60 THEN 1,ELSE 0,END,3),要求保存提取（查询）的结果，可以使用我们曾学习过的,SELECT INTO,newTable,语句，生成新表并保存数据,24,T-SQL,语句的综合应用,3.,比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过,97,分：,1),使用,IF,语句,判断笔试还是机试偏低，决定对笔试还是机试提分；,2),使用,WHILE,循环,给每个学员加分，缺考的除外，当最高分超过,97,分时退出循环；,3),因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新,isPass,（是否通过）列。,UPDATE,newTable,SET,isPass,=,CASE,WHEN,writtenExam,=60 and,labExam,=60 THEN 1,ELSE 0,END,25,T-SQL,语句的综合应用,4.,提分后，统计学员的成绩和通过情况：,1,）使用别名实现中文字段名，即,SELECT,姓名,=,stuName,学号,=,stuNo,2,）如果某个学员的成绩为,NULL(,空,),，则替换为”缺考”，否则原样显示；,3,）,isPass,列中的,1,替换为是，,0,替换为否；,SELECT ,机试成绩,=CASE,WHEN,labExam,IS NULL THEN ,缺考,ELSE convert(varchar(5),labExam),END,是否通过,=CASE,WHEN,isPass,=1 THEN ,是,ELSE ,否,END,26,T-SQL,语句的综合应用,5.,提分后统计学员的通过率情况：,1,）通过人数：因为通过用,1,表示，没通过用,0,表示，所以,isPass,列的累加和即是通过人数；,2,）通过率：同理，,isPass,列的平均值*,100,即是通过率；,27,T-SQL,参考语句,/*-,本次考试的原始数据,-*/,-SELECT * FROM,stuInfo,-SELECT * FROM,stuMarks,/*-,统计考试缺考情况,-*/,SELECT,应到人数,=(SELECT count(*) FROM,stuInfo,) , -,应到人数为子查询表达式的别名,实到人数,=(SELECT count(*) FROM,stuMarks,) ,缺考人数,=(SELECT count(*) FROM,stuInfo,)-(SELECT count(*) FROM,stuMarks,),28,T-SQL,参考语句,/*-,统计考试通过情况，并将结果存放在新表,newTable,中,-*/,IF EXISTS(SELECT * FROM,sysobjects,WHERE name=,newTable,),DROP TABLE,newTable,SELECT,stuName,stuInfo.stuNo,writtenExam,labExam,isPass,=CASE,WHEN,writtenExam,=60 and,labExam,=60 THEN 1,ELSE 0,END,INTO,newTable,FROM,stuInfo,LEFT JOIN,stuMarks,ON,stuInfo.stuNo,=,stuMarks.stuNo,-SELECT * FROM,newTable,-,查看统计结果，可用于调试,29,T-SQL,参考语句,/*-,酌情加分：比较笔试和机试平均分，决定加哪门,-*/,DECLARE ,avgWritten,numeric(4,1),DECLARE avgLab numeric(4,1),SELECT ,avgWritten,=,AVG(writtenExam,) FROM,newTable,WHERE,writtenExam,IS NOT NULL,SELECT ,avgLab,=,AVG(labExam)FROM,newTable,WHERE,labExam,IS NOT NULL,IF ,avgWritten,=97,BREAK,END,ELSE ,略, -,循环给笔试加分，最高分不能超过,97,分,30,T-SQL,参考语句,-,因为提分，所以需要更新,isPass,（是否通过）列的数据,UPDATE,newTable,SET,isPass,=CASE,WHEN,writtenExam,=60 and,labExam,=60 THEN 1,ELSE 0,END,-SELECT * FROM,newTable,-,可用于调试,/*-,显示考试最终通过情况,-*/,SELECT,姓名,=,stuName,学号,=,stuNo,笔试成绩,=CASE,WHEN,writtenExam,IS NULL THEN ,缺考,ELSE convert(varchar(5),writtenExam),END,机试成绩,=CASE,WHEN,labExam,IS NULL THEN ,缺考,ELSE convert(varchar(5),labExam),END,是否通过,=CASE,WHEN,isPass,=1 THEN ,是,ELSE ,否,END,FROM,newTable,31,T-SQL,参考语句,/*-,显示通过率及通过人数,-*/,SELECT,总人数,=count(*) ,通过人数,=,SUM(isPass,),通过率,=(convert(varchar(5),AVG(isPass*100)+%) FROM,newTable,32,总结,总结我们曾学习过的查询，合并多个表中的数据的方法有三种：,联合,(Union),合并多个数据表中的行,子查询将一个查询包含到另一个查询中,联接合并多个数据表中的列,通过在子查询中使用,EXISTS,子句，可以对子查询中的行是否存在进行检查,IN,子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否在某个范围,33,