sqlserver教程第6章.ppt

《sqlserver教程第6章.ppt》由会员分享，可在线阅读，更多相关《sqlserver教程第6章.ppt（53页珍藏版）》请在三一办公上搜索。

1、第6章 数据检索,鸡尾酒设计制作,6.1 SQL概述,6.2 使用SELECT语句的简单查询,6.3 常用的其他关键字,6.4 高级查询,6.5 本章小结,6.1 SQL概述,SQL语言集数据查询(Data Query)、数据操纵(Data Manipulation)、数据定义(Data Definition)和数据控制(Data Control)功能于一体，主要特点包括：1、综合统一 2、高度非过程化 3、使用广泛 4、简单易学，书写自由,6.1 SQL概述,1.一般语法格式 SELECT 目标列表达式 FROM 表名 2.演示【例1】，注意【思考】！3.自己看【例2】，同学演示，分析结果4

2、.【例3】消除重复行。了解关键字 ALL|DISTINCT,6.2 使用SELECT语句的简单查询-SELECT 子句,控制在查询的结果中显示重复行，可以按照如下的语法书写：SELECT ALL|DISTINCT 目标列表达式 FROM 表名 ALL关键字是默认的，DISTINCT消除重复列。【练习】显示课程表中教师姓名，尝试去掉重复行（一个教师可能教授多门课程）。,6.2 使用SELECT语句的简单查询-SELECT 子句,5、重新命名列标题 学习【例4】，总结得到一般格式 SELECT table_list AS new_name6、【练习】Select 班级号=ClaNo,班级名称=Cl

3、aName,所属系部=Department,人数=Number From Class,6.2 使用SELECT语句的简单查询-SELECT 子句,7、综合运用【例】查询所有学生的年龄。,6.2 使用SELECT语句的简单查询-SELECT 子句,FROM子句跟在SELECT子句之后，用于指定被查询的表，可以是单表或者派生表，也可以是多张表，还可以是视图或者临时表。【例6】查询所有选课学生的姓名，性别和成绩。-给表加别名,6.2 使用SELECT语句的简单查询-FROM 子句,WHERE子句作为查询的限定条件是可选的，但在使用时必须接在FROM子句之后，用来限定查询结果。【例8】查询出生时间在1

4、988年以后的学生的情况。Select*from Studentwhere Birthday=1989-1-1,6.2 使用SELECT语句的简单查询-WHERE 子句,【例9】查询男生中备注列不为空的学生的情况【分析】本题目有两个要求，一是性别为“男”，二是备注列不为空。Select StuNo,StuName,Sex,Notesfrom Studentwhere Sex=男 AND Notes IS NOT NULL 自学【例10】,6.2 使用SELECT语句的简单查询-WHERE 子句,GROUP BY子句指定对查询单结果分组的条件，并且如果SELECT子句中包含了聚合函数，则计算每组

5、的汇总值。【例11】查询班级表Class中每个系的总人数。Select Department,SUM(Number)as 人数from ClassGROUP BY Department,6.2 使用SELECT语句的简单查询-GROUP BY 子句,【相似】和SUM()相似的聚合函数还有求平均值的AVG()函数，求最大值的MAX()，求最小值的MIN()以及计算行数的COUNT（）函数。【例12】查询学生表Student中总共的学生数。Select count(*)as 总人数from Student 自学【例13】，完成之后的“思考”,6.2 使用SELECT语句的简单查询-GROUP BY

6、 子句,HAVING子句和WHERE子句类似，后面都是跟选择的条件。使用HAVING子句必须是在使用GROUP BY的前提下，HAVING对分组的结果进行筛选，不满足条件的不显示出来。【例11】选课表（成绩表）中存在一个学生选修多门课程的情况，查询每个学生所选课程的平均成绩。要求只显示平均成绩在80分以上（含80分）的学生信息。【分析】只需要在例10的结果集中筛选，将平均成绩在80分以下的信息不予显示。,6.2 使用SELECT语句的简单查询-Having 子句,Select StuNo,AVG(Grade)as averagefrom Scoregroup by StuNo Having A

7、VG(Grade)=80【思考与练习】既然HAVING子句和WHERE子句后面都是跟选择的条件，那么可以把本例中的HAVING子句后的条件写到WHERE上吗，试试看！【例12】查询每门课程的选修人数，包括课程编号和选修人数，只显示选修人数大于5人的情况。,6.2 使用SELECT语句的简单查询-Having 子句,【分析】查询选修人数实际就是看成绩表Score中同一门课程号对应有几个学号（或者几个成绩）。要用到分组和筛选。Select CouNo,count(StuNo)as 选修人数from Score group by CouNo having count(StuNo)5【注意】凡是要对聚

8、合函数用比较运算符（,!=,=），条件必须放在HAVING子句。,6.2 使用SELECT语句的简单查询-Having 子句,如果要将数据按某列的顺序显示出来，就必须使用ORDER BY排序。ORDER BY使用两个保留字ASC 和DESC，ASC是按升序（递增）排列，DESC是按照降序（递减）排列，默认的排序顺序是升序排列（A-Z，a-z，0-9）.【例12】查询班级表Class中所有班级的名称和人数。将查询的结果按照人数的升序排列。Select ClaName,Numberfrom Classorder by Number ASC,6.2 使用SELECT语句的简单查询-ORDER BY

9、子句,【思考与联系】在排序列出现有NULL值的时候，如果按升序排列，那么NULL是在最顶端还是在最底端？,6.2 使用SELECT语句的简单查询-ORDER BY 子句,使用LIKE关键字可以实现模糊查询的功能。运算符LIKE可用来进行字符串的匹配，LIKE后面跟的是匹配模式。匹配模式可以是一个包含通配符“%”（百分号），“_”（下划线），“(中括号)”和“(取反)”的字符串。【例13】查询所有姓“李”的学生的学号、姓名和性别【分析】姓“李”那么有可能是两个字的姓名，也可能是三个字的姓名。Select StuNo,StuName,Sexfrom StudentWhere StuName lik

10、e 李%,6.3 常用的其他关键字-LIKE 关键字,【例13】查询课程名称中包含“计算机”的课程的编号、名称、开课类型及学分【分析】课程名称中包含“计算机”，它有可能出现在名称的开始，也可能是中间还可能是在结束部分。Select CouNo,CouName,Kind,Creditfrom CourseWhere CouName like%计算机%,6.3 常用的其他关键字-LIKE 关键字,【例14】查询课程编号第一个字符为“X”或者“Y”，第六个字符为数字的课程编号和课程名称。Select CouNo,CouNamefrom CourseWhere CouNo like xy_ _ _ _

11、0-9%自己学习【例20】,6.3 常用的其他关键字-LIKE 关键字,NULL值与“0”值不同，NULL表示该值没有输入，也就是还没有确定，而“0”值则已是确定的值。可以在 WHERE子句中用IS NULL或者 IS NOT NULL来判断某一列是否输入了数据。【例16】查询成绩表Score中选修了课程但是没有成绩的学生的选课情况。Select StuNo,CouNo,GradeFrom ScoreWhere Grade IS NULL;GO-【注意】,6.3 常用的其他关键字-NULL 关键字,指定查询结果中将只返回结果集的前面部分行数据。这组行可以是某一数量的行也可以是某一百分比数量的行

12、。TOP 表达式可用在 SELECT、INSERT、UPDATE 和 DELETE 语句中。【例17】查询年龄最大的前5个学生的记录【分析】学生的年龄最大实际就是出生时间最小，只需要将结果按照出生时间的升学排列。TOP 关键字的语法：TOP integer PERCENT WITH TIES,6.3 常用的其他关键字-TOP 关键字,Select TOP 5 StuName,Sex,ClaName,Birthdayfrom StudentORDER BY Birthday【例18】查询学生表Student中前20%的学生记录，包括学号，姓名，性别，班级和籍贯等信息。Select TOP 20

13、percent StuName,Sex,ClaName,Cityfrom StudentORDER BY Birthday 自己学习【例24】,6.3 常用的其他关键字-TOP 关键字,BETWEENAND关键字用与放在搜索条件中表示范围，可以是时间范围也可以是数字范围。【例20】查询出生时间在1988年至1989年学生的姓名、性别和出生时间。Select StuName,Sex,Birthdayfrom StudentWhere Birthday between 1988-1-1 and 1989-12-31GO,6.3 常用的其他关键字-BETWEENAND关键字,【注意】1、betwee

14、n后是范围的下限，and后是跟的范围的上限，包含边界值。2、这里的日期必须要加单引号。【思考】WHERE 上的表达式可以换成其他表达方式吗？,6.3 常用的其他关键字-BETWEENAND关键字,使用CASE可以计算条件列表并返回多个可能结果表达式之一。CASE关键字的表达需要用到CASE，THEN子句和END关键字，以及可以用来重新命名列明的AS关键字。【例20】查询成绩表Score中的每个学生的成绩，并进行分类。,6.3 常用的其他关键字-CASE 关键字,Select StuNo,avg(Grade)as 平均成绩,等级=CASE-新增的列“等级”when avg(Grade)=80 t

15、hen 优秀 when avg(Grade)=60 then 及格when avg(Grade)=60 then 不及格else 无成绩 end-记得这里的“end”from ScoreGroup by StuNo-必须用“GROUP BY”分组,6.3 常用的其他关键字-CASE 关键字,【思考与练习】实现判断学生表Student中，入学时间Enrolltime在2007年就判定年级为“07级”，2006年就判定年级为“06级”，2005年就判定年级为“05级”，其他时间判定为未知。,6.3 常用的其他关键字-CASE 关键字,Transact-SQL提供了连接操作符JOIN，用于从两张或多

16、张数据表的连接中获取数据。两张表之间的连接可以有以下几种不同的方式：内连接 INNER JOIN外连接 LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN自连接 INNER JOIN交叉连接 CROSS JOIN,6.4 高级查询-连接查询,1、内连接 在内连接中，可以使用等号“=”作为比较运算符，此时称作等值连接；也可以使用不等比较运算符，此时为不等值连接。【例27】查询所有课程信息和选课学生的成绩。Select Sc.*,Co.*from Score sc join Course co on sc.CouNo=co.CouNo【注意】查询结果出

17、现了连列完全相同，而这列刚好是两张表的连接列,6.4 高级查询-连接查询,【例22】查询所有课程信息和选课学生的成绩，去掉重复列。Select StuNo,Grade,Co.*from Score sc join Course co on sc.CouNo=co.CouNoGO【注意】例21的连接称为等值连接，本例将等值连接的重复列去掉，这种连接方法称为自然连接。,6.4 高级查询-连接查询,【例23】查询所有选修了课程的学生的姓名、课程名和成绩。【分析】使用到了学生表Student中的姓名，课程表Course中的课程名及成绩表Score中的成绩列，需要连接三张表。Select StuName

18、,CouName,Gradefrom Score sc join Course co ON sc.CouNo=co.CouNo join Student st ON st.StuNo=sc.StuNoorder by Grade desc,6.4 高级查询-连接查询,【思考】连接N张表需要多少个连接条件？2、外连接 外连接包括左外连接、右外连接和全外连接。左外连接包含左表的所有数据行，右外连接包含右表的所有数据行，全外连接包含两个表的所有数据行。【例24】查询所有学生的信息和选课学生的情况。【分析】学生表中的学生有部分没有选课，但也要显示他们的信息，以及所有选课学生的选课情况。,6.4 高级查

19、询-外连接,Select StuName,Sex,ClaName,CouNo,Gradefrom Student st left join Score sc ON st.StuNo=sc.StuNo【注意】指定在结果集中包括左表中所有不满足联接条件的行，并在由内部联接返回所有的行之外，将另外一个表的输出列设为 NULL。【例24】查询所有课程的信息和学生选课的信息,6.4 高级查询-外连接,Select CouName,Kind,Credit,sc.CouNo,Gradefrom Score sc right join Course co ON sc.CouNo=co.CouNoGO【思考与练

20、习】可以把本例中的右连接转化成左连接吗，如果可以，试试看。,6.4 高级查询-外连接,【例25】查询所有学生信息和所有选课信息。【分析】对学生表Student和选课表Score进行全外连接。Select StuName,Sex,ClaName,CouNo,Gradefrom Student st full join Score sc ON st.StuNo=sc.StuNo,6.4 高级查询-外连接,3、自连接 自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。【例26】查找不同课程成绩相同的学生的学号

21、、课程号、学生成绩。【分析】本例要实现必须使用自连接，将同一张成绩表Score取两个不同的别名，作为两张表。,6.4 高级查询-自连接,在查询窗口执行如下的SQL语句Select s1.StuNo,s1.CouNo,s1.Gradefrom Score s1 join Score s2 ON s1.Grade=s2.Gradewhere s1.CouNo!=s2.CouNo and s1.StuNo!=s2.StuNo自学【例27】查找出每门功课成绩最好的前两名。,6.4 高级查询-自连接,4、交叉连接交叉连接返回进行连接操作的两个表中所有数据行的笛卡尔积，得到的结果集的行数是两个表的行数的乘

22、积。【例28】查找所有学生的选课的可能的情况。【分析】本例就是要查看如果每个学生都选到了全部课程，那么会有多少种可能。from Course cross join Studentorder by CouNoGO,6.4 高级查询-交叉连接,【注意】CROSS JOIN不能带ON连接条件，也不能有WHERE子句，但可以有ORDER BY 子句。,6.4 高级查询-交叉连接,将一个SELECT语句嵌套在另一个SELECT语句的WHERE子句中的查询形式，称为子查询，子查询中嵌套在WHERE子句里的SELECT语句称为内部查询，相对的，另一个包含内部查询的SELECT语句被称为外部查询。子查询要求服

23、务器先计算内部查询并形成结果，然后外部查询根据内部查询的结果，产生最终查询结果。,6.4 高级查询-子查询,子查询可分为3种类型：NOT IN子查询比较子查询NOT EXISTS子查询【例29】查询外语系学生的姓名，性别，班级和籍贯。【分析】本例可以分两步来完成；第一步：查找到外语系下属有哪些班级；第二部：找到前一步班级所属的学生的姓名，性别，班级和籍贯。,6.4 高级查询-子查询,Select StuName,Sex,ClaName,Cityfrom Studentwhere ClaName in(select ClaName from Class where Department=外语系)

24、,6.4 高级查询-子查询,【注意】嵌套查询是先执行子查询，再执行外层查询。这里用IN关键字，是因为子查询的结果集可能是多个“班级名称”，用“=”等号则除非可以肯定子查询结果是唯一的一个值。【思考与练习】将本例改成用连接查询完成。如果现在要求查询不是计算机系和外语系学生的姓名，性别，班级和籍贯，读者试着实现。,6.4 高级查询-子查询,【例30】查询学生学号最小的学生的选课情况。【分析】本例要使用比较子查询来完成。在查询窗口执行如下的SQL语句USE CJGLGOSelect*from Scorewhere StuNo=all(select StuNo from dbo.Student),6.

25、4 高级查询-子查询,【注意】比较子查询就是将某个列值与内部查询的结果作运算比较，如果比较结果为真则返回该行，否则不返回。比较子查询通常要用到操作符ALL、ANY、SOME。其通用格式为：ALL|ANY|SOME ALL：列值必须和内部查询结果集的每一个值进行比较，只有每一次的比较结果都为真时，比较结果才为真。ANY、SOME：列值和内部查询结果集的值进行比较，只要有一次为真，比较结果就为真。,6.4 高级查询-子查询,【例31】查询不是计算机系和外语系学生的姓名，性别，班级和籍贯。【分析】本例可以用IN 子查询来完成，也可以使用EXISTS子查询来实现。Select StuName,Sex,

26、ClaName,Cityfrom Studentwhere EXISTS(Select*from Class ca join Student st on ca.ClaName=st.ClaName where Department!=外语系 and Department!=计算机系),6.4 高级查询-子查询,【注意】其实IN 子查询和EXISTS子查询是可以互相转换的。【分析】NOTEXISTS 子查询 EXISTS是测试子查询是否有数据行返回，如果有则返回TRUE，否则返回FALSE。NOT EXISTS 则相反，当结果表为空时，才返回TRUE。,6.4 高级查询-子查询,用于连接多个SE

27、LECT查询结果的集合操作有3种：并操作（UNION）、交操作（INTERSECTION）和差操作（MINUS）。【例32】查询籍贯为上海和北京的学生的姓名，性别，班级和籍贯。,6.4 高级查询-联合查询,【注意】将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。UNION 运算不同于使用联接合并两个表中的列的运算。下面列出了使用 UNION 合并两个查询结果集的基本规则：1、所有查询中的列数和列的顺序必须相同。2、数据类型必须兼容。,6.4 高级查询-联合查询,本章小结,本章主要介绍SQL查询语句的使用方法，读者连接SQL的发展及特点，掌握使用SQL的关键字SELECT关键字进行简单查询的方法。学会使用本章列举的一些常用关键字包括NULL,LIKE,TOP,CASE等。由于很多时候查询的列没有在一张表中，此时要求使用连接查询或者嵌套查询可以实现。在特定的应用中还可能用到联合查询，本章中也举了相应的例子，读者要仔细体会SQL查询语句的表达。,感谢您的关注,