第5章数据查询ppt课件.ppt

第5章数据查询 本章教学概要 5 1SELECT简单查询5 2SELECT高级查询5 3使用SQLServerManagementStudio创建查询 5 1SELECT简单查询 5 1 1SELECT查询语句格式5 1 2数据表中列的筛选5 1 3数据表中记录的筛选5 1 4查询结果的排序5 1 5用查询结果生成新表5 1 6联合查询5 1 7实训项目9简单查询 5 1 1SELECT查询语句格式 SELECT语句的作用是让服务器从数据库中按用户的要求检索数据 并将结果以表格的形式返回给客户 它是SQL语言的核心 完整的SELECT语句比较复杂 主要的子句语法如下 SELECT INTO FROM n WHERE GROUPBY n HAVING ORDERBY n 5 1 2数据表中列的筛选 选择表中的列组成结果集合 其语法格式如下 SELECT ALL DISTINCT TOPexpression PERCENT WITHTIES table name view name table alias column name expression AS column alias column alias expression n 5 1 2数据表中列的筛选 1 选择一个表中指定的列使用SELECT语句选择一个表中的某些列时各列名之间要以逗号进行分割 语法格式为 SELECTcolumn name column name FROMtable nameWHEREsearch condition 例5 1 查询JXGL数据库的XS表中各个同学的XH XM和XB 5 1 2数据表中列的筛选 2 选择一个表中所有列当在SELECT语句指定的位置上使用 号时 表示选表的所有的列 例5 2 查询JXGL数据库的XS表中的所有列3 在查询结果中显示字符串在一些查询中 经常需要在检索结果中增加一些字符串来辅助说明相关查询结果 如要求查询结果为 计算机应用基础课程代码为 CS001SELECT子句中 将要增加的字符串用单引号括起来 然后和列的名字写在一起 中间用分隔符逗号隔开 例5 3 从KC表中要查询出所有课程的名称和类别 并在类别前面显示字符串 类别为 5 1 2数据表中列的筛选 4 修改查询结果中的列标题在显示结果时 可以指定以别名来代替原来的字段名称 总共有3种方法 采用 字段名称AS别名 的格式 采用 字段名称别名 的格式 采用 别名 字段名称 的格式 其中别名a用单引号括起来 例5 4 查询xs表中XH和XM字段 并在标题栏中显示 学号 和 姓名 字样 而不是显示XH和XM 5 替换查询结果中的数据在对表进行查询时 有时对所查询的某些列希望得到的是一种概念而不是具体的数据 例如学生学习某门课程成绩在查询结果中我们希望 优 良 及格和不及格 的方式显示 要替换查询结果中的数据 要使用CASE表达式 5 1 2数据表中列的筛选 6 查询经过计算的值在查询的过程中 不仅可以直接以列的原始值作为结果 而且还可以以对列值经过计算所得的值作为查询结果输出 即SELECT语句可以用表达式作为结果 语法格式 SELECTexpression expression fromtable name 例5 5 在XS表中 每个学生都有一个CSRQ 出生日期 现要查询出每个同学的姓名和年龄 7 消除结果集中的重复行如果查询只选择出表中的部分列时 就可能会出现重复的行 例如 从JXGL数据库的XS表中只选择出XB和bjh 就会出现多行重复的情况 可以使用DISTINCT关键字来消除结果集中的重复行 其格式为 SELECTDISTINCTcolumn name column name fromtable name 例5 6 对XS表只选择XB和BJH 消除结果中的重复行 5 1 3数据表中记录的筛选 数据表中记录的筛选 是通过在SELECT语句中使用 查询所有列以及使用WHERE子句指定选择的条件来实现的1 限制结果集的返回行数使用Top选项限制查询结果集返回的行数 格式为 Selecttopn percent fromtable name其中 n是一个正整数 表示返回查询结果集的前n行 若带percent关键字 则表示返回结果集的前n 行 例5 7 从XS表中查询所有学生的相关信息 并分别返回结果集的前10条和前10 条记录 5 1 3数据表中记录的筛选 2 查询满足条件的行是通过WHERE子句构成相应的查询条件来实现的 在这一部分中将详细讨论WHERE子句中查询条件的构成 WHERE子句同样适用于上节所介绍的列的筛选 WHERE子句必须紧跟在FROM子句之后 其基本的语法格式为 WHERE其中 search condition为查询条件 通常指定为逻辑表达式 结果集将返回表达式为真的数据行 可以用来构成查询条件的常用运算符及关键字如表5 1所示 5 1 3数据表中记录的筛选 2 查询满足条件的行是通过WHERE子句构成相应的查询条件来实现的 WHERE子句必须紧跟在FROM子句之后 其基本的语法格式为 WHERE其中 search condition为查询条件 通常指定为逻辑表达式 可以用来构成查询条件的常用运算符及关键字如下所示 比较 范围 BETWEEN 在某个范围内 NOTBETWEEN 不在某个范围内 列表 IN 在某个列表中 NOTIN 不在某个列表中 字符串匹配 LIKE 和指定字符串匹配 NOTLIKE 和指定字符串不匹配 空值判断 ISNULL 为空 ISNOTNULL 不为空 组合条件 AND 与 OR 或 取反 NOT 5 1 3数据表中记录的筛选 例5 8 在CJ 成绩 表中查询出ZP 课程成绩总评 不及格的同学的相关信息 例5 9 从JXGL数据库KC表中查询出KCH包含 计算机 一词的课程情况 例5 10 在CJ表查询出课程成绩ZP在80分到90之间学生成绩情况 例5 11 在JXGL数据库的KC表中查询出KCH为 CS001 CS002 和 CS003 的课程信息 例5 12 在JXGL数据库的XS表中查询出BJH尚不确定的学生的相关信息 例5 13 在JXGL数据库的XS表中查询出BJH为2班的女生的相关信息 5 1 4查询结果的排序 在SELECT语句中 可以使用ORDERBY子句对结果进行排序 该子句的格式为 ORDERBY order by expression ASC DESC n 例5 14 在JXGL数据库的XS表中将班级编号 BJH 为4班的学生按学号由小到大排序 例5 15 在JXGL数据库的XS表中查询出BJH为4的同学的XH和XM 要求以中文别名 学号 和 姓名 显示 并以学号由小到大来排序说明 除了可以使用列名和别名 也可以用一个表示列在选择表中所处位置的正整数来做为order by expression 但是前提条件是必须知道排序的列在选择表所处的位置 例5 16 如果已知在XS表中CSRQ字段位于第4列 现在要将BJH为4的学生按出生日先后排序 5 1 5用查询结果生成新表 SELECTINTO语句用于创建一个新表 并用SELECT语句的结果集填充该表 SELECTINTO可将几个表或视图中的数据组合成一个新表 新表的结构由选择列表中表达式的属性定义 例5 17 在JXGL数据库的XS表中查询出所有女生的XH XM和XB 并将查询结果插入到新表STUDENT中语句如下 SELECTXH XM XBINTOSTUDENTFROMXSWHEREXB 0 5 1 6联合查询 使用UNION子句可以将两个或更多查询的结果合并为单个结果集 该结果集包含联合查询中的所有查询的全部行 其格式为 UNION ALL UNION ALL n 其中 queryspecification和queryexpression都是查询语句 使用UNION子句组合两个查询的结果集有两个基本规则 所有查询中的列数和列的顺序必须相同 对应列的数据类型必须兼容关键字ALL表示合并结果集中包括所有的行 不去除重复行 不使用ALL则在合并结果中去除重复行 例5 18 查询出1989年以后出生的学生和1969年以后出生的老师的编号和姓名 5 1 7实训项目9简单查询 1 实训目的 1 熟悉SELECT语句的基本语法 理解并掌握语句中各子句的含义和用法 2 掌握使用SELECT语句编写简单的SQL查询 学会使用SELECT语句解决实际中的查询问题 2 实训内容 在数据库JXGL上 完成下列操作 实验练习1 在查询编辑器中 查询出平时成绩PSCJ高于85但实践成绩SJCJ低于60的同学的学号和课程号 实验练习2 在查询编辑器中 分别输出BJ表中所有记录和前8条记录 实验练习3 在查询编辑器中 查询出类别为专业基础课或专业课的课程的课程号和课程名 要求 使用IN关键字 实验练习4 在查询编辑器中 使用CASE语句输出每个学生的学号与总评分 要求总评分低于60分时显示 不及格 60到70之间则显示 一般 70到80之间则显示 还可以 80到90之间则显示 良好 90分以上则显示 优秀 要求 使用汉字的别名 5 1 7实训项目9简单查询 实验练习5 在查询编辑器中 从CJ表中输出学生的学号和总分 其中总分是由 平时成绩 0 3 实践成绩 0 3 期末成绩 0 4 计算确定 并且列名分别用 学号 和 总分 输出 实验练习6 在查询编辑器中 查询出姓张 李或王的学生的学号和姓名 实验练习7 在查询编辑器中 查询出出生日期为空值的教师的信息 实验练习8 在查询编辑器中 从CJ表中查询出学习CS001课程的学生的XH和ZP 并按ZP降序排列 实验练习9 在查询编辑器中 查询出1989年以后出生的女生的XH和XM 并将查询结果插入新表STU 实验练习10 查询出 网络091 班的学生名单和学习 计算机网络基础 课程的学生的名单 使用UNION子句 3 实训思考 1 若需要从课程表中查询出课程号以A到F之间的一个字母打头的课程的课程号和课程名 对应的SQL语句应如何编写 2 如果想在查询结果中列出总评成绩比60分多多少 则SQL语句如何编写 5 2SELECT高级查询 5 2 1数据汇总5 2 2联接查询5 2 3子查询5 2 4实训项目10高级查询 5 2 1数据汇总 对表数据进行查询时 经常要对结果进行计算或汇总 例如在学生成绩表中查询某位同学说学各门课程的总分 统计某门课程各分数段的人数等 1 聚合函数聚合函数用于计算表中的数据 返回单个计算结果 常用来增强检索功能 T SQL中常用的聚合函数如下 SUM和AVG函数 MAX和MIN函数 COUNT函数 5 2 1数据汇总 例5 19 求选修CS001课程的学生的平均成绩 例5 20 求选修CS001课程的学生的最高分和最低分 例5 21 求XS表中学生的总人数 5 2 1数据汇总 2 GROUPBY子句GROUPBY子句用于对表或视图的数据按字段分组 如果SELECT子句中包含聚合函数 则GROUPBY将计算每组的汇总值 指定GROUPBY时 选择列表中任意非聚合表达式内的所有列都应包含在GROUPBY列表中 或者GROUPBY表达式必须与选择列表表达式完全匹配 例5 22 从XS表中查询出各班级的学生的人数 例5 23 在JXGL数据库中查询出每个同学的学号 姓名所学各门课程的总分 5 2 1数据汇总 3 HAVING子句在使用GROUPBY子句和聚合对数据进行分组后 还可以使用HAVING子句指定组或聚合函数所应满足的搜索条件 即对分组数据进行附加筛选 注意 当HAVING与GROUPBYALL一起使用时 HAVING子句优于ALL 另外在HAVING子句中不能使用text image和ntext数据类型 例5 24 在JXGL数据库XS表中查询出班级男生人数大于等于2人的班级编号和男生人数 5 2 1数据汇总 4 COMPUTE和COMPUTEBY子句在SQLServer2005提供COMPUTE和COMPUTEBY子句是为了向后兼容 其语法格式为 COMPUTE AVG COUNT MAX MIN STDEV STDEVP VAR VARP SUM expression n BYexpression n 例5 25 使用COMPUTE子句汇总出专业基础课的总学时和总学分 例5 26 使用COMPUTEBY子句汇总出各类课程的总学时和总学分 5 2 2联接查询 在查询的过程中很多时候需要从有关联的多个表中来进行查询 例如要查询出学习 计算机应用基础 课程的学生的学号姓名和成绩 查询的过程中要用到XS KC和CJ表 这类查询称之为联接查询 联接查询主要包括内联接 INNERJOIN 外联接 OUTERJOIN 和交叉联接 CROSSJOIN 等 1 内联接查询内联接是一种最常用的联接类型 内联接查询实际上是一种任意条件的查询 格式一 在FROM子句中定义联接条件SELECT列的列表FROM表数据源 INNER JOIN表数据源ON联接条件 WHERE搜索条件 5 2 2联接查询 格式二 在WHERE子句中定义联接条件SELECT列的列表FROM表数据源 表数据源WHERE搜索条件AND联接条件另外内联接根据比较方式还可以分为 1 等值联接 在联接条件中使用等于号 运算符比较被联接列的列值 其查询结果中列出被联接表中的所有列 包括其中的重复列 2 不等联接 在联接条件使用除等于运算符以外的其它比较运算符比较被联接的列的列值 这些运算符包括 3 自然联接 在联接条件中使用等于 运算符比较被联接列的列值 但它使用选择列表指出查询结果集合中所包括的列 并删除联接表中的重复列 5 2 2联接查询 例5 27 查询出软件081班所有学生的相关信息及BJMC说明 从上例中可以看到在FROM和WHERE子句中都可以指定内联接的联接条件 但是在FROM子句指定联接条件将有助于将联接条件和其他搜索条件分开 指定联接时建议使用在FROM子句指定的方法 在内联接中 所有引用的列都必须明确 如上例中的BJH在两表中是重复的列 在引用的过程就必须要通过表名来限定 上例中联接就是自然联接 5 2 2联接查询 2 外联接查询外联接是只限制一张表中的数据必须满足联接条件 而另一张表中的数据可以不满足联接条件的联接方式 外联接总共有3种 1 左外联接 LEFTOUNTERJOIN 结果集合中除了包括满足联接条件的行外 还包括左表的所有行 2 右外联接 RIGHTOUTERJOIN 结果集合中除了包括满足联接条件的行外 还包括右表的所有行 3 全外联接 FULLOUTERJOIN 结果集合中除了包括满足联接条件的行外 还包括两个表的所有行 其中OUTER关键字均可省略 5 2 2联接查询 外联接查询的联接条件只能在FROM子句中指定具 体格式为 SELECT列的列表FROM表数据源 OUTER JOIN表数据源ON联接条件 WHERE搜索条件 例5 28 查询出每个学生的选课情况 包括已选课的学生和未选课的学生 5 2 2联接查询 3 交叉联接查询交叉联接即笛卡尔乘积 结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表 结果集合中记录的条数为两个表行数之乘积 交叉联接查询的联接条件也只能在FROM子句中指定 具体格式为 SELECT列的列表FROM表数据源CROSSJOIN表数据源 例5 29 查询出所有学生的可能的选课情况 5 2 3子查询 子查询是一个SELECT语句 它嵌套在一个SELECT INSERT UPDATE或DELETE语句中或嵌套在另一子查询中 子查询也称为内部查询或内部选择 而包含子查询的语句也称为外部查询或外部选择 在执行包含子查询的的语句时 系统先执行子查询 产生一个结果表 再执行外部查询 1 IN子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断 例5 30 查询出选修了 CS001 课程的学生的学号和姓名 5 2 3子查询 子查询是一个SELECT语句 它嵌套在一个SELECT INSERT UPDATE或DELETE语句中或嵌套在另一子查询中 子查询也称为内部查询或内部选择 而包含子查询的语句也称为外部查询或外部选择 在执行包含子查询的的语句时 系统先执行子查询 产生一个结果表 再执行外部查询 1 IN子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断 例5 30 查询出选修了 CS001 课程的学生的学号和姓名 5 2 3子查询 2 比较子查询比较子查询是由一个比较运算符 或 引入的 与使用IN引入的子查询一样 由未修改的比较运算符 即后面不接ANY或ALL的比较运算符 引入的子查询必须返回单个值而不是值列表 要使用由未修改的比较运算符引入的子查询 必须对数据和问题的本质非常熟悉 以了解该子查询实际是否只返回一个值 例5 31 查询出 C程序设计 课程成绩高于该门课平均成绩的学生姓名 例5 32 查询出比2班所有同学年龄都大的学生的相关情况 5 2 3子查询 3 EXISTS子查询使用EXISTS关键字引入一个子查询时 就相当于进行一次存在测试 外部查询的WHERE子句测试子查询返回的行是否存在 子查询实际上不产生任何数据 它只返回TRUE或FALSE值 例5 33 查询出所学课程成绩都及格的学生名单注意 使用EXISTS引入的子查询在以下几方面与其他子查询略有不同 EXISTS关键字前面没有列名 常量或其他表达式 由EXISTS引入的子查询的选择列表通常几乎都是由星号 组成 由于只是测试是否存在符合子查询中指定条件的行 所以不必列出列名 5 2 4实训项目10高级查询 1 实训目的 1 掌握GROUPBY子句的使用 2 熟悉COMPUTE和COMPUTEBY子句的使用 3 掌握联接查询的方法 4 掌握子查询的方法 2 实训内容 在数据库JXGL上 完成下列操作 实验练习1 查询出