sqlserver教程第6章.ppt

第6章数据检索 鸡尾酒设计制作 6 1SQL概述 6 2使用SELECT语句的简单查询 6 3常用的其他关键字 6 4高级查询 6 5本章小结 6 1SQL概述 SQL语言集数据查询 DataQuery 数据操纵 DataManipulation 数据定义 DataDefinition 和数据控制 DataControl 功能于一体 主要特点包括 1 综合统一2 高度非过程化3 使用广泛4 简单易学 书写自由 6 1SQL概述 1 一般语法格式SELECT目标列表达式FROM表名 2 演示 例1 注意 思考 3 自己看 例2 同学演示 分析结果4 例3 消除重复行 了解关键字ALL DISTINCT 6 2使用SELECT语句的简单查询 SELECT子句 控制在查询的结果中显示重复行 可以按照如下的语法书写 SELECTALL DISTINCT目标列表达式FROM表名 ALL关键字是默认的 DISTINCT消除重复列 练习 显示课程表中教师姓名 尝试去掉重复行 一个教师可能教授多门课程 6 2使用SELECT语句的简单查询 SELECT子句 5 重新命名列标题学习 例4 总结得到一般格式SELECTtable listASnew name 6 练习 Select班级号 ClaNo 班级名称 ClaName 所属系部 Department 人数 NumberFromClass 6 2使用SELECT语句的简单查询 SELECT子句 7 综合运用 例 查询所有学生的年龄 6 2使用SELECT语句的简单查询 SELECT子句 FROM子句跟在SELECT子句之后 用于指定被查询的表 可以是单表或者派生表 也可以是多张表 还可以是视图或者临时表 例6 查询所有选课学生的姓名 性别和成绩 给表加别名 6 2使用SELECT语句的简单查询 FROM子句 WHERE子句作为查询的限定条件是可选的 但在使用时必须接在FROM子句之后 用来限定查询结果 例8 查询出生时间在1988年以后的学生的情况 Select fromStudentwhereBirthday 1989 1 1 6 2使用SELECT语句的简单查询 WHERE子句 例9 查询男生中备注列不为空的学生的情况 分析 本题目有两个要求 一是性别为 男 二是备注列不为空 SelectStuNo StuName Sex NotesfromStudentwhereSex 男 ANDNotesISNOTNULL自学 例10 6 2使用SELECT语句的简单查询 WHERE子句 GROUPBY子句指定对查询单结果分组的条件 并且如果SELECT子句中包含了聚合函数 则计算每组的汇总值 例11 查询班级表Class中每个系的总人数 SelectDepartment SUM Number as人数fromClassGROUPBYDepartment 6 2使用SELECT语句的简单查询 GROUPBY子句 相似 和SUM 相似的聚合函数还有求平均值的AVG 函数 求最大值的MAX 求最小值的MIN 以及计算行数的COUNT 函数 例12 查询学生表Student中总共的学生数 Selectcount as总人数fromStudent自学 例13 完成之后的 思考 6 2使用SELECT语句的简单查询 GROUPBY子句 HAVING子句和WHERE子句类似 后面都是跟选择的条件 使用HAVING子句必须是在使用GROUPBY的前提下 HAVING对分组的结果进行筛选 不满足条件的不显示出来 例11 选课表 成绩表 中存在一个学生选修多门课程的情况 查询每个学生所选课程的平均成绩 要求只显示平均成绩在80分以上 含80分 的学生信息 分析 只需要在例10的结果集中筛选 将平均成绩在80分以下的信息不予显示 6 2使用SELECT语句的简单查询 Having子句 SelectStuNo AVG Grade asaveragefromScoregroupbyStuNoHavingAVG Grade 80 思考与练习 既然HAVING子句和WHERE子句后面都是跟选择的条件 那么可以把本例中的HAVING子句后的条件写到WHERE上吗 试试看 例12 查询每门课程的选修人数 包括课程编号和选修人数 只显示选修人数大于5人的情况 6 2使用SELECT语句的简单查询 Having子句 分析 查询选修人数实际就是看成绩表Score中同一门课程号对应有几个学号 或者几个成绩 要用到分组和筛选 SelectCouNo count StuNo as选修人数fromScoregroupbyCouNohavingcount StuNo 5 注意 凡是要对聚合函数用比较运算符 条件必须放在HAVING子句 6 2使用SELECT语句的简单查询 Having子句 如果要将数据按某列的顺序显示出来 就必须使用ORDERBY排序 ORDERBY使用两个保留字ASC和DESC ASC是按升序 递增 排列 DESC是按照降序 递减 排列 默认的排序顺序是升序排列 A Z a z 0 9 例12 查询班级表Class中所有班级的名称和人数 将查询的结果按照人数的升序排列 SelectClaName NumberfromClassorderbyNumberASC 6 2使用SELECT语句的简单查询 ORDERBY子句 思考与联系 在排序列出现有NULL值的时候 如果按升序排列 那么NULL是在最顶端还是在最底端 6 2使用SELECT语句的简单查询 ORDERBY子句 使用LIKE关键字可以实现模糊查询的功能 运算符LIKE可用来进行字符串的匹配 LIKE后面跟的是匹配模式 匹配模式可以是一个包含通配符 百分号 下划线 中括号 和 取反 的字符串 例13 查询所有姓 李 的学生的学号 姓名和性别 分析 姓 李 那么有可能是两个字的姓名 也可能是三个字的姓名 SelectStuNo StuName SexfromStudentWhereStuNamelike 李 6 3常用的其他关键字 LIKE关键字 例13 查询课程名称中包含 计算机 的课程的编号 名称 开课类型及学分 分析 课程名称中包含 计算机 它有可能出现在名称的开始 也可能是中间还可能是在结束部分 SelectCouNo CouName Kind CreditfromCourseWhereCouNamelike 计算机 6 3常用的其他关键字 LIKE关键字 例14 查询课程编号第一个字符为 X 或者 Y 第六个字符为数字的课程编号和课程名称 SelectCouNo CouNamefromCourseWhereCouNolike xy 0 9 自己学习 例20 6 3常用的其他关键字 LIKE关键字 NULL值与 0 值不同 NULL表示该值没有输入 也就是还没有确定 而 0 值则已是确定的值 可以在WHERE子句中用ISNULL或者ISNOTNULL来判断某一列是否输入了数据 例16 查询成绩表Score中选修了课程但是没有成绩的学生的选课情况 SelectStuNo CouNo GradeFromScoreWhereGradeISNULL GO 注意 6 3常用的其他关键字 NULL关键字 指定查询结果中将只返回结果集的前面部分行数据 这组行可以是某一数量的行也可以是某一百分比数量的行 TOP表达式可用在SELECT INSERT UPDATE和DELETE语句中 例17 查询年龄最大的前5个学生的记录 分析 学生的年龄最大实际就是出生时间最小 只需要将结果按照出生时间的升学排列 TOP关键字的语法 TOPinteger PERCENT WITHTIES 6 3常用的其他关键字 TOP关键字 SelectTOP5StuName Sex ClaName BirthdayfromStudentORDERBYBirthday 例18 查询学生表Student中前20 的学生记录 包括学号 姓名 性别 班级和籍贯等信息 SelectTOP20percentStuName Sex ClaName CityfromStudentORDERBYBirthday自己学习 例24 6 3常用的其他关键字 TOP关键字 BETWEEN AND 关键字用与放在搜索条件中表示范围 可以是时间范围也可以是数字范围 例20 查询出生时间在1988年至1989年学生的姓名 性别和出生时间 SelectStuName Sex BirthdayfromStudentWhereBirthdaybetween 1988 1 1 and 1989 12 31 GO 6 3常用的其他关键字 BETWEEN AND 关键字 注意 1 between后是范围的下限 and后是跟的范围的上限 包含边界值 2 这里的日期必须要加单引号 思考 WHERE上的表达式可以换成其他表达方式吗 6 3常用的其他关键字 BETWEEN AND 关键字 使用CASE可以计算条件列表并返回多个可能结果表达式之一 CASE关键字的表达需要用到CASE THEN子句和END关键字 以及可以用来重新命名列明的AS关键字 例20 查询成绩表Score中的每个学生的成绩 并进行分类 6 3常用的其他关键字 CASE关键字 SelectStuNo avg Grade as平均成绩 等级 CASE 新增的列 等级 whenavg Grade 80then 优秀 whenavg Grade 60then 及格 whenavg Grade 60then 不及格 else 无成绩 end 记得这里的 end fromScoreGroupbyStuNo 必须用 GROUPBY 分组 6 3常用的其他关键字 CASE关键字 思考与练习 实现判断学生表Student中 入学时间Enrolltime在2007年就判定年级为 07级 2006年就判定年级为 06级 2005年就判定年级为 05级 其他时间判定为未知 6 3常用的其他关键字 CASE关键字 Transact SQL提供了连接操作符JOIN 用于从两张或多张数据表的连接中获取数据 两张表之间的连接可以有以下几种不同的方式 内连接 INNER JOIN外连接LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN自连接 INNER JOIN交叉连接CROSSJOIN 6 4高级查询 连接查询 1 内连接在内连接中 可以使用等号 作为比较运算符 此时称作等值连接 也可以使用不等比较运算符 此时为不等值连接 例27 查询所有课程信息和选课学生的成绩 SelectSc Co fromScorescjoinCoursecoonsc CouNo co CouNo 注意 查询结果出现了连列完全相同 而这列刚好是两张表的连接列 6 4高级查询 连接查询 例22 查询所有课程信息和选课学生的成绩 去掉重复列 SelectStuNo Grade Co fromScorescjoinCoursecoonsc CouNo co CouNoGO 注意 例21的连接称为等值连接 本例将等值连接的重复列去掉 这种连接方法称为自然连接 6 4高级查询 连接查询 例23 查询所有选修了课程的学生的姓名 课程名和成绩 分析 使用到了学生表Student中的姓名 课程表Course中的课程名及成绩表Score中的成绩列 需要连接三张表 SelectStuName CouName GradefromScorescjoinCoursecoONsc CouNo co CouNojoinStudentstONst StuNo sc StuNoorderbyGradedesc 6 4高级查询 连接查询 思考 连接N张表需要多少个连接条件 2 外连接外连接包括左外连接 右外连接和全外连接 左外连接包含左表的所有数据行 右外连接包含右表的所有数据行 全外连接包含两个表的所有数据行 例24 查询所有学生的信息和选课学生的情况 分析 学生表中的学生有部分没有选课 但也要显示他们的信息 以及所有选课学生的选课情况 6 4高级查询 外连接 SelectStuName Sex ClaName CouNo GradefromStudentstleftjoinScorescONst StuNo sc StuNo 注意 指定在结果集中包括左表中所有不满足联接条件的行 并在由内部联接返回所有的行之外 将另外一个表的输出列设为NULL 例24 查询所有课程的信息和学生选课的信息 6 4高级查询 外连接 SelectCouName Kind Credit sc CouNo GradefromScorescrightjoinCoursecoONsc CouNo co CouNoGO 思考与练习 可以把本例中的右连接转化成左连接吗 如果可以 试试看 6 4高级查询 外连接 例25 查询所有学生信息和所有选课信息 分析 对学生表Student和选课表Score进行全外连接 SelectStuName Sex ClaName CouNo GradefromStudentstfulljoinScorescONst StuNo sc StuNo 6 4高级查询 外连接 3 自连接自连接 selfjoin 是SQL语句中经常要用的连接方式 使用自连接可以将自身表的一个镜像当作另一个表来对待 从而能够得到一些特殊的数据 例26 查找不同课程成绩相同的学生的学号 课程号 学生成绩 分析 本例要实现必须使用自连接 将同一张成绩表Score取两个不同的别名 作为两张表 6 4高级查询 自连接 在查询窗口执行如下的SQL语句Selects1 StuNo s1 CouNo s1 GradefromScores1joinScores2ONs1 Grade s2 Gradewheres1 CouNo s2 CouNoands1 StuNo s2 StuNo自学 例27 查找出每门功课成绩最好的前两名 6 4高级查询 自连接 4 交叉连接交叉连接返回进行连接操作的两个表中所有数据行的笛卡尔积 得到的结果集的行数是两个表的行数的乘积 例28 查找所有学生的选课的可能的情况 分析 本例就是要查看如果每个学生都选到了全部课程 那么会有多少种可能 fromCoursecrossjoinStudentorderbyCouNoGO 6 4高级查询 交叉连接 注意 CROSSJOIN不能带ON连接条件 也不能有WHERE子句 但可以有ORDERBY子句 6 4高级查询 交叉连接 将一个SELECT语句嵌套在另一个SELECT语句的WHERE子句中的查询形式 称为子查询 子查询中嵌套在WHERE子句里的SELECT语句称为内部查询 相对的 另一个包含内部查询的SELECT语句被称为外部查询 子查询要求服务器先计算内部查询并形成结果 然后外部查询根据内部查询的结果 产生最终查询结果 6 4高级查询 子查询 子查询可分为3种类型 NOT IN子查询比较子查询 NOT EXISTS子查询 例29 查询外语系学生的姓名 性别 班级和籍贯 分析 本例可以分两步来完成 第一步 查找到外语系下属有哪些班级 第二部 找到前一步班级所属的学生的姓名 性别 班级和籍贯 6 4高级查询 子查询 SelectStuName Sex ClaName CityfromStudentwhereClaNamein selectClaNamefromClasswhereDepartment 外语系 6 4高级查询 子查询 注意 嵌套查询是先执行子查询 再执行外层查询 这里用IN关键字 是因为子查询的结果集可能是多个 班级名称 用 等号则除非可以肯定子查询结果是唯一的一个值 思考与练习 将本例改成用连接查询完成 如果现在要求查询不是计算机系和外语系学生的姓名 性别 班级和籍贯 读者试着实现 6 4高级查询 子查询 例30 查询学生学号最小的学生的选课情况 分析 本例要使用比较子查询来完成 在查询窗口执行如下的SQL语句USECJGLGOSelect fromScorewhereStuNo all selectStuNofromdbo Student 6 4高级查询 子查询 注意 比较子查询就是将某个列值与内部查询的结果作运算比较 如果比较结果为真则返回该行 否则不返回 比较子查询通常要用到操作符ALL ANY SOME 其通用格式为 ALL ANY SOME ALL 列值必须和内部查询结果集的每一个值进行比较 只有每一次的比较结果都为真时 比较结果才为真 ANY SOME 列值和内部查询结果集的值进行比较 只要有一次为真 比较结果就为真 6 4高级查询 子查询 例31 查询不是计算机系和外语系学生的姓名