SQL高级查询和视图课件

1、第五章高级查询回顾指出下列语句的错误：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=0SELECT mymoney=balance FROM bank建表语句后必须添加GO标志DECLARE mymoney INTSET mymoney=0WHERE

2、 userName=张三2回顾IF mymoney100 print 卡上目前余额不足100，请及时充值! print 卡上余额为：+mymoneyprint 您的年利息为: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子查询的用法掌

3、握EXISTS子查询的用法应用T-SQL进行综合查询4SELECT语句的语法形式 SELECT ALL|DISTINCT 字段名列表AS 标题名 INTO TABLE|CURSOR新表名FROM 数据库名1.AS， 数据库名2.AS,WHERE 筛选条件GROUP BY 分组表达式HAVING 分组条件ORDER BY 排序表达式 ASC|DESC 命令格式：功能：对一个或多个表进行查询操作，按其需求将表中的记录进行筛选、分组、排序，从而生成一个结果集，也可以将该结果集生成新表。 说明：5(1)SELECT子句列出所有要求SELECT语句查询的数据项,如指定AS，输出以指定的标题名作为字段名输

4、出。如指定INTO新表名，则将查询的结果作为新表保存； (2)FROM子句列出包含所要查询数据的表；(3)WHERE子句提供SQL只查询某些行的数据，也就是执行查询的条件；(4)GROUP BY用以指定汇总查询，即不是对每一行产生一个查询结果，而是行记录进行分组，再对每一组产生一个汇总结果；(5)HAVING子句告诉SQL只产生由GROUP BY得到的某些组的结果；(6)ORDER BY子句将查询结果按照一列或多列中的数据排序。 说明：65.1模糊查询LIKE查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容SELECT SName AS 姓名 FROM Students

5、WHERE SName LIKE 张%姓名张果老张飞张扬出去思考：以下的SQL语句：SELECT * FROM 数据表 WHERE 编号 LIKE 008%A,C%可能会查询出的编号值为( )。A、9890ACDB、007_AFFC、008&DCGD、KK8C7模糊查询IS NULL把某一字段中内容为空的记录查询出来SELECT student_Name As 姓名 ,home_addr AS 地址 FROM Student WHERE home_addr IS NULL姓名地址李红NULL左群声NULL猜一猜：把Student表中某些行的home_addr字段值删掉后： 使用IS NULL能

6、查询出来这些数据行吗？ 怎么查询出这些行来？8模糊查询BETWEEN把某一字段中内容在特定范围内的记录查询出来SELECT Student_ID, grade FROM student_course WHERE course_id=dep04_s002 and grade BETWEEN 60 AND 80 Student_IDGradeg994020278g994020468g9940205789模糊查询IN把某一字段中内容与所列出的查询内容列表匹配的记录查询出来SELECT student_Name As 姓名 ,home_addr AS 地址 FROM Student WHERE sub

7、string(home_addr,1,2) in(长沙,南京,江苏)学员姓名地址李扬长沙于紫电江苏李青霜南京司马弓上海10课堂练习查询教师表teacher中职称为教授、副教授的记录查询student表中年龄在20到25岁之间的记录提示：year(getdate)-year(birth)算出年龄。Between and查询student表中姓李的记录11问题成绩表中存储了所有学生的成绩，我想知道：学生的总成绩、平均成绩、有成绩的学生总共有多少名怎么办？125.2聚合函数SUMSELECT SUM(price) FROM bookAVG、MAX、MINSELECT AVG(grade) AS 平均

8、成绩, MAX (grade) AS 最高分, MIN (grade) AS 最低分 From student_course WHERE grade =60COUNTSELECT COUNT (*) AS 及格人数 From student_courseWHERE grade=6013SQL语言支持五个集合函数 ：函数功能AVG（字段名）求一列数据的平均值SUM（字段名）求一列数据的和COUNT(DISTINCT 字段名) 输出查询的行数 COUNT（*）MIN（字段名）给出列中的最小值MAX（字段名）给出列中的最大值14集合函数是作用于一组值的函数，而不是只作用于一个值上面的函数 。所有集合

9、函数可以操作一个变量，这个变量可以是列或表达式(惟一的例外是COUNT 函数的第二种形式：COUNT(*) 每个集合函数的结果是个常量，它显示在结果中不同的列上 15问题如果不是统计所有人所有课程的总成绩而是想求每一门课的平均绩或者某个人的所有课的总成绩怎么办？165.3分组汇总命令格式： GROUP BY 分组表达式 HAVING 分组条件说明： 1) 分组表达式：一般为字段名，对指定的字段进行分组。2）分组条件:对分组汇总后数据进入结果集的筛选条件，一般为集合函数或常量. 17不带HAVING的GROUP BY子句 GROUP BY子句将一列或多列定义为一组，按组输出查询结果。 GROUP

10、 BY子句可以统计每一门课的平均绩或者某个人的所有课的总成绩18【例】统计每个人的平均成绩。 SELECT student_id,AVG(grade) AS 平均成绩 FROM student_course GROUP BY student_id19注意事项SQL为每个定义的组产生一个列值，每个组只返回一行，不返回详细信息。 如果包括WHERE子句，VFP只分组统计满足WHERE条件的行。在包含GROUP BY子句的查询语句中，SELECT子句后的所有字段列表，除集合函数外，都应包含在GROUP BY子句中，否则将出错。如上例中，只能是student_id.否则将出错。 不要在含有空值的列上使

11、用GROUPBY子句，因为空值将作为一个组来处理。 20分组查询思考SELECT student_id,course_id,AVG(grade) AS 平均成绩 FROM student_course GROUP BY student_id思考：执行以下的T-SQL： 结果如何？21HAVING子句定义应用到分组行中的条件，HAVING子句对分组行的意义与WHERE子句对每个行的意义是相同的。 带HAVING的GROUP BY子句 22【例】 查询平均成绩大于80学生的学号和平均成绩。 SELECT student_id,AVG(grade) AS 平均成绩 FROM student_cour

12、se GROUP BY student_id HAVING AVG(grade)80课堂练习：查询课程不及格的人数大于2的课程号和人数SELECT course_id,count(*) FROM student_course group by course_id Having count(*)223分组查询对比WHEREGROUP BYHAVINGWHERE子句从数据源中去掉不符合其搜索条件的数据GROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句去掉不符合其组搜索条件的各组数据行24分组查询思考SELECT 部门编号, COUNT(*)FROM 员工信息表WH

13、ERE 工资 = 2000GROUP BY 部门编号HAVING COUNT(*) 1思考：分析以下T-SQL的含义255.4多表联结查询问题每次查询成绩时显示的都是学生的学号信息，因为成绩表中只存储了学生的学号；实际上最好显示学生的姓名，而姓名存储在student表；如何同时从这两个表中取得数据？26多表联结查询分类内联结(INNER JOIN)外联结左外联结 (LEFT JOIN)右外联结 (RIGHT JOIN)完整外联结(FULL JOIN)交叉联结(CROSS JOIN)使用多个表查询来产生检索结果。 27内联结内连接(INNER JOIN)：内连接返回的结果集中只包括满足连接条件的

14、行。例：查询所有学生的姓名、课程号和成绩SELECT S.student_Name,C.Course_ID,C.gradeFrom student_course AS C INNER JOIN Student AS SON C.Student_ID = S.student_id28SELECT S.student_Name,C.Course_ID,C.gradeFrom student_course AS C INNER JOIN Student AS SON C.Student_ID = S.student_idStudent_course（成绩表）Student_IDCourse_IDGr

15、ade122300100100200297896776300381猜一猜：这样写，返回的查询结果是一样的吗？SELECT S.student_Name,C.Course_ID,C.gradeFrom Student AS SINNER JOIN student_course AS CON C.Student_ID =S.student_id再猜一猜：以下返回多少行？SELECT S.student_Name,C.Course_ID,C.gradeFrom Student AS SINNER JOIN student_course AS CON C.Student_ID S.student_id

16、内联结-1Stundent(学生表）Student_Name梅超风陈玄风陆乘风曲灵风Student_id1234查询结果Student_name梅超风陈玄风陈玄风陆乘风Course_IDgrade00100100200297896776陆乘风0038129内联结-2SELECT S.student_Name,C.Course_ID,C.gradeFrom student_course AS C , Student AS SWhere C.Student_ID = S.student_id基于WHERE子句的内连接语法形式30三表联结方法1：SELECT S.student_Name,L.Cou

17、rse_Name,C.gradeFrom student_course AS C , Student AS S,course as LWhere C.Student_ID = S.student_idand C.course_id=L.course_id方法2:SELECT S.student_Name,l.Course_Name,C.gradeFrom student_course AS C inner join Student AS S on C.Student_ID = S.student_idinner join course as L on C.course_id=L.course_

18、id例：查询学生的姓名、课程名、成绩31ScoreStudentsIDCourseIDScore122300100100200297896776300381左外联结StundentsSName梅超风陈玄风陆乘风曲灵风SCode1234查询结果SName梅超风陈玄风陈玄风陆乘风CourseIDScore00100100200297896776陆乘风00381曲灵风NULLNULLSELECT S.SName,C.CourseID,C.Score From Students AS SLEFT JOIN Score AS CON C.StudentID = S.SCode猜一猜：这样写，返回的查询结

19、果是一样的吗？SELECT S.SName,C.CourseID,C.Score From Score AS CLEFT JOIN Students AS SON C.StudentID = S.SCode左外连接除了包括满足连接条件的行外，还包括其中左表的全部行。32右外联结SELECT Titles.Title_id, Titles.Title, Publishers.Pub_nameFROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id右外连接除了包括满足连接条件的行外，还包括其中右表的全部

20、行。33课堂练习1、查询每个老师的部门名称和姓名2、查询部门名称为计算机科学的所有老师名单。提示：部门名称在department表select teacher_name,department_name from teacher as tinner join department as d on t.department_id=d.department_idwhere d.department_name=计算机科学345.5子查询 学员信息表问题：编写T-SQL语句，查看年龄比“李斯文”大的学员，要求显示这些学员的信息 ？分析： 第一步：求出“李斯文”的年龄；第二步：利用WHERE语句，筛选年龄

21、比“李斯文”大的学员；35什么是子查询 实现方法一：采用T-SQL变量实现 DECLARE age INT -定义变量，存放李斯文的年龄SELECT age=stuAge FROM stuInfo WHERE stuName=李斯文 -求出李斯文的年龄-筛选比李斯文年龄大的学员SELECT * FROM stuInfo WHERE stuAgeage GO 36什么是子查询实现方法二：采用子查询实现 SELECT * FROM stuInfoWHERE stuAge( SELECT stuAge FROM stuInfo where stuName=李斯文)GO 子查询子查询在WHERE语句中

22、的一般用法： SELECT FROM WHERE 字段1(子查询) 外面的查询称为父查询，括号中嵌入的查询称为子查询 UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 37使用子查询替换表连接3-1问题：查询笔试刚好通过（60分）的学员名单。学员信息表和成绩表38使用子查询替换表连接3-2实现方法一：采用表连接 SELECT stuName FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExa

23、m=60GO内连接（等值连接）39使用子查询替换表连接3-3实现方法二：采用子查询 SELECT stuName FROM stuInfo WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60)GO子查询一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据表连接更适合于查看多表的数据40IN子查询 4-1问题：查询笔试刚好通过的学员名单。如何解决？41IN子查询 4-2解决方法：采用 IN 子查询 SELECT stuName FROM stu

24、Info WHERE stuNo IN (SELECT stuNo FROM stuMarks WHERE writtenExam=60)GO将号改为ININ后面的子查询可以返回多条记录常用IN替换等于（）的比较子查询42IN子查询 4-3问题：查询参加考试的学员名单 学员信息表和成绩表（重抓本图）分析：判断一个学员是否参加考试其实很简单，只需要查看该学员对应的学号是否在考试成绩表stuMarks中出现即可 43IN子查询 4-4/*-采用IN子查询参加考试的学员名单-*/SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM

25、 stuMarks)GO演示：使用IN子查询 参考语句44NOT IN子查询问题：查询未参加考试的学员名单 分析：加上否定的NOT 即可45查询未参加数据库开发技术考试的名单分析：1、在course表中课程名称为数据库技术开发的course_idselect course_id from course where course_name=数据库开发技术2、在成绩表即student_course表中查询具有数据库开技术课程成绩的学生学号select student_id from student_course where course_id =(select course_id from cou

26、rse where course_name=数据库开发技术)3、最后在student表中查询所有在2步查询结果中没有的姓名。select student_name from student where student_id not in( select student_id from student_course where course_id =(select course_id from course where course_name=数据库开发技术)46课堂练习1、用子查询来查询JWGL库中部门为计算机科学的教师名单2、查询林红所有课程的成绩3、查询JWGL库中数据库开发技术课程不及格

27、的名单提示：第3题可以结合内联接和子查询一起完成47EXISTS子查询 4-1例如：数据库的存在检测IF EXISTS（SELECT * FROM sysDatabases WHERE name=stuDB） DROP DATABASE stuDBCREATE DATABASE stuDB.建库代码略 48EXISTS子查询 4-2IF EXISTS （子查询） 语句 EXISTS子查询的语法：如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false) EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换49EXIS

28、TS子查询 4-3问题：检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分 分析：是否有人笔试成绩达到80分以上，可以采用EXISTS检测 50EXISTS子查询 4-4/*-采用EXISTS子查询，进行酌情加分-*/IF EXISTS (SELECT * FROM stuMarks WHERE writtenExam80) BEGIN print 本班有人笔试成绩高于80分，每人加2分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+2 SELECT * FROM stumarks ENDELSE B

29、EGIN print 本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩： UPDATE stuMarks SET writtenExam=writtenExam+5 SELECT * FROM stumarks ENDGO演示：使用EXISTS子查询 参考语句51NOT EXISTS子查询 2-1问题：检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都60分），则试题偏难，每人加3分，否则，每人只加1分 分析：没有一人通过考试，即不存在“笔试和机试成绩都60分”，可以采用NOT EXISTS检测 52NOT EXISTS子查询 2-2IF NOT EXISTS (SELECT *

30、 FROM stuMarks WHERE writtenExam60 AND labExam60) BEGIN print 本班无人通过考试，试题偏难，每人加3分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+3,labExam=labExam+3 SELECT * FROM stuMarks ENDELSE BEGIN print 本班考试成绩一般，每人只加1分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+1,labExam=labExam+1 SELECT * FROM

31、 stuMarks ENDGO 演示：使用NOT EXISTS子查询 参考语句535.6T-SQL语句的综合应用学员信息表和成绩表 应到人数：5人实到人数4人，缺考1人54T-SQL语句的综合应用如何实现？本次考试的缺考情况 比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分 。加分后重新统计通过情况统计通过率 55T-SQL语句的综合应用1.提示: 使用子查询统计缺考情况：应到人数：SELECT count(*) FROM stuInfo实到人数：SELECT count(*) FROM stuMarks2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩

32、、机试成绩、是否通过1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（ LEFT JOIN ）； SELECT stuNameFROM 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语句，生成新表并保存数据 56T-SQL语句的综合应用3.比较笔试平

33、均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过97分：1) 使用IF语句判断笔试还是机试偏低，决定对笔试还是机试提分；2) 使用WHILE循环给每个学员加分，缺考的除外，当最高分超过97分时退出循环；3)因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新isPass（是否通过）列。 UPDATE newTable SET isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 END57T-SQL语句的综合应用4.提分后，统计学员的成绩和通过情况：1）使用别名实现中文字段名，即SELEC

34、T 姓名=stuName,学号=stuNo2）如果某个学员的成绩为NULL(空)，则替换为”缺考”，否则原样显示；3）isPass列中的1替换为是，0替换为否； SELECT ,机试成绩=CASE WHEN labExam IS NULL THEN 缺考 ELSE convert(varchar(5),labExam) END ,是否通过=CASE WHEN isPass=1 THEN 是 ELSE 否 END58T-SQL语句的综合应用5.提分后统计学员的通过率情况：1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通过人数；2）通过率：同理，isPass列的平均

35、值*100即是通过率；59T-SQL参考语句/*-本次考试的原始数据-*/-SELECT * FROM stuInfo-SELECT * FROM stuMarks/*-统计考试缺考情况-*/SELECT 应到人数=(SELECT count(*) FROM stuInfo) , -应到人数为子查询表达式的别名 实到人数=(SELECT count(*) FROM stuMarks) , 缺考人数=(SELECT count(*) FROM stuInfo)-(SELECT count(*) FROM stuMarks) 60T-SQL参考语句/*-统计考试通过情况，并将结果存放在新表newT

36、able中-*/IF EXISTS(SELECT * FROM sysobjects WHERE name=newTable) DROP TABLE newTableSELECT 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 newTabl

37、e -查看统计结果，可用于调试 61T-SQL参考语句/*-酌情加分：比较笔试和机试平均分，决定加哪门-*/DECLARE avgWritten numeric(4,1)DECLARE avgLab numeric(4,1) SELECT avgWritten=AVG(writtenExam) FROM newTable WHERE writtenExam IS NOT NULLSELECT avgLab=AVG(labExam)FROM newTable WHERE labExam IS NOT NULLIF avgWritten=97 BREAK ENDELSE 略 -循环给笔试加分，最高

38、分不能超过97分62T-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 63T-SQL参考语句 /*-显示通过率及通过人数-*/ SELECT 总人数=