1、第8章数据查询 计算中心 本章内容 8 1查询分析器简介8 2SELECT语句简介8 3SELECT基本查询8 4SELECT嵌套查询8 5连接查询8 6在INSERT UPDATE和DELETE语句中使用子查询 8 1查询分析器简介 通过开始菜单启动 通过企业管理器启动 启动查询分析器 8 1查询分析器简介 查询分析器的工作界面 对象浏览区域 选择数据库 输入SQL区域 显示查询结果 8 1查询分析器简介 查询分析器的常用操作执行SQL语句在 输入SQL区域 中输入SQL语句或打开一个SQL文件 单击工具栏执行按钮或按F5 查看和修改表中的数据 8 1查询分析器简介 控制查询结果的显示方式
2、以文本方式显示 以表格显示结果 将结果保存为文件 菜单 工具栏 8 1查询分析器简介 新建查询窗口单击工具栏 新建查询 按钮 选择菜单 文件 新建 保存在查询窗口中输入的SQL语句将光标置于输入SQL区域单击工具栏保存按钮选择菜单 文件 保存 保存查询结果将光标置于查询结果显示区域单击工具栏保存按钮选择菜单 文件 保存 8 1查询分析器简介 对象搜索单击工具栏对象搜索按钮或选择菜单 工具 对象搜索 新建 8 2SELECT语句简介 数据查询是数据库最重要也是最主要的功能 在数据库中 数据查询是通过SELECT语句来完成的 SELECT查询语句只有一条语句 却能实现各种需求的查询 功能非常强大
3、依赖于其子句 SELECT语句的框架 8 2SELECT语句简介 SELECT ALL DISTINCT TOPn PERCENT AS AS INTO FROM INNER LEFT RIGHT FULLJOIN ON WHERE GROUPBY HAVING ORDERBY ASC DESC 集合函数 BY 提示 SELECT和FROM是必备的 其他子句根据实际情况设定 8 3SELECT基本查询 无条件简单查询SELECT ALL DISTINCT TOPn PERCENT select listFROMtable name查询结果 从table name中返回数据 各选项含义 ALL
4、表示输出所有记录 包括重复记录 DISTINCT 输出无重复结果的记录 TOPn 返回查询结果的前n行数据 TOPnPERCENT 返回查询结果前n 行数据 select list 所要查询的选项 列 的集合 多个选项之间用逗号分开 table name 要查询的表名 8 3SELECT基本查询 例8 1 查询课程表中的所有数据 select课程号 课程名 学时from课程表提示 如果查询某表的所有列时 可用 代替各列名 select from课程表 例8 2 查询课程表中前两行数据 selecttop2课程号 课程名 学时from课程表 8 3SELECT基本查询 例8 3 查询课程表中前一
5、半数据 selecttop50percent from课程表例8 4 查询课程表中学时列 不显示重复记录 selectdistinct学时from课程表 和原数据比较 Select学时from课程表 8 3SELECT基本查询 提问 下列SELECT语句含义select from学生selecttop3学号 姓名 性别from学生selecttop20percent学号 姓名 性别from学生selectdistinct性别from学生 8 3SELECT基本查询 使用别名在输出结果中 如果不希望使用字段名作为各列的标题 可以根据需求设置列标题 格式 column name1 AS colum
6、n title1 例8 5 查询课程表所有数据 字段学时显示为课时 select课程号 课程名 学时as课时from课程表 提示 AS可以省略 select课程号 课程名 学时课时from课程表 8 3SELECT基本查询 使用集合函数进行统计可以通过集合函数对表数据进行统计计算 并返回计算结果 比如查询表中记录的个数 查询学生的平均成绩等 AVG 求一列数据的平均值 SUM 求一列数据的和 COUNT 统计查询的行数 MIN 求列中的最小值 MAX 求列中的最大值 8 3SELECT基本查询 例8 6 查询学生表中的记录数 selectcount from学生表例8 7 查询选课表中最高分和
7、最低分 selectmax 成绩 min 成绩 from选课表例8 8 查询选课表中的平均成绩 selectavg 成绩 from选课表提示 在使用集合函数时 常需重新命名列标题 改为 selectmax 成绩 as最高分 min 成绩 as最低分from选课表 8 3SELECT基本查询 带条件查询经常会查询符合某条件的记录 比如查询学生表中女同学的信息 查询成绩在90分以上的记录等 通过WHERE子句来指定查询条件 格式 WHERE条件表达式 通过比较操作符和逻辑操作符构成的逻辑判断的式子 比较操作符 或 90 not 成绩 90 课程号 c01 and成绩 80 8 3SELECT基本查
8、询 例8 9 查询学生表中女同学的个人信息 select学号 姓名 性别from学生表where性别 女 例8 10 查询课程表中c01和c03这两门课程情况 select from课程表where课程号 c01 or课程号 c03 提示 书写表达式时需要根据数据类型来加定界符 表达多个条件时使用AND和OR 注意其逻辑含义 8 3SELECT基本查询 查询结果处理排序输出ORDERBY语法格式 ORDERBYorder by expression1 ASC DESC order by expression2 ASC DESC ASC 升序 默认 DESC 降序order by express
9、ion1 通常为字段 字段与字段之间用逗号分隔 例如 orderby性别 以性别来排序 orderby性别 姓名 先以性别升序来排 性别相同的再以姓名升序来排 8 3SELECT基本查询 例8 11 将选课表的成绩按从低到高的顺序显示 select from选课表orderby成绩ASC例8 12 将选课表的成绩按从高到低的顺序显示 select from选课表orderby成绩DESC 提示 字母排序 A Z为升序 Z A为降序 汉字排序 取汉字的汉语拼音 再以字母序来排 课堂练习 在 学生表 中实现以下查询 所有学生的学号 姓名 性别信息 并按学号排序 所有女生的信息 所有女生的人数 在
10、选课表 中实现以下查询 查找学号为s2008003的课程号 成绩 查找成绩在80分以上的学生学号 成绩 查找课程号为c01的最高分 最低分 平均分 计算成绩在90分以上的记录数 表的基本操作在课程表中插入一条记录 课程号c06 课程名为photoshop 学时为40修改课程号为c06记录的学时数为60删除课程号为c06记录 8 3SELECT基本查询 重定向输入INTO把查询结果存放到一个新表中 语法 INTOnew table例8 12 查询出学生表中女同学的个人信息 并将结果存入女同学表中 select into女同学表from学生表where性别 女 Select from女同学表 8
11、3SELECT基本查询 输出合并UNION把来自不同查询的数据组合起来 形成一个具有综合信息的查询结果 语法 UNION ALL ALL 结果全部合并 若没有ALL 则重复的记录将被自动去掉 8 3SELECT基本查询 例8 13 将男同学表和女同学表的记录合并 select学号 姓名 性别from女同学表unionselect学号 姓名 性别from男同学表提示 两个SELECT语句必须输出同样的列数 两个表各相应列的数据类型必须相同 只有最后一个SELECT语句中可以用ORDERBY子句 8 3SELECT基本查询 分组统计GROUPBY通过GROUPBY子句可以将查询结果按指定列进行分组
12、 该列值相等的记录为一组 语法 GROUPBYgroup by expression1 group by expression2 例8 14 统计学生表中男女生人数 select性别 count as人数from学生表groupby性别提示 group by expression1通常为字段名 列名 通常和集合函数一起使用 课堂练习 统计选课表中每位学生的平均成绩 8 3SELECT基本查询 筛选HAVING若在分组后还要按照一定的条件进行筛选 则需使用HAVING子句 语法 HAVINGsearching condition例8 15 查询选课表中平均成绩在90分以上的学生学号和平均成绩 s
13、elect学号 avg 成绩 平均成绩from选课表groupby学号havingavg 成绩 90 8 3SELECT基本查询 提示 HAVING子句通常需要使用集合函数来限定查询结果 HAVING子句和WHERE子句都是条件限定子句 但其作用对象不同 8 3SELECT基本查询 在多表连接查询中使用分组统计举例 统计各门课程的平均成绩 并显示课程名 select课程表 课程号 max 课程名 课程名 avg 成绩 平均成绩from选课表innerjoin课程表on选课表 课程号 课程表 课程号groupby课程表 课程号 思考 统计平均分在90分以上的课程号 课程名和平均成绩 查询学生的平
14、均成绩 并显示学生姓名 课堂练习 完成对如下三个表的查询 职工 职工号int 姓名char 8 性别char 2 出生日期datetime 党员否bit 参加工作datetime 部门号int 部门 部门号int 部门名char 10 工资 职工号int 工资日期datetime 工资decimal 6 1 计算各部门员工数 显示所有职工的平均工资 显示所有职工的职工号 姓名和平均工资 8 3SELECT基本查询 使用COMPUTE和COMPUTEBY子句汇总使用COMPUTE子句可以在查询的结果集中生成汇总行的同时 还生成明细行 语法 COMPUTErow aggregate column
15、name row aggregate colornn name BYcolumn name column name 例8 16 查询选课表中学号为s2008001的平均成绩及各科成绩 select学号 课程号 成绩from选课表where学号 s2008001 computeavg 成绩 8 3SELECT基本查询 COMPUTE子句生成集合函数结果附加在结果集的最后 当与BY一起使用时 COMPUTE子句在结果集内对指定列进行分组汇总 例8 17 查询选课表中学生的各科成绩及平均成绩 select学号 课程号 成绩from选课表orderby学号computeavg 成绩 by学号 提示 C
16、OMPUTE子句中指定的列必须是SELECT子句中已有的 COMPUTEBY必须与ORDERBY子句一起使用 且两个子句指定的列及顺序相同 不能与SELECTINTO子句一起使用 8 4SELECT嵌套查询 有时一个SELECT语句无法完成查询任务 而需要一个子SELECT的结果作为查询的条件 即需要在一个SELECT语句的WHERE子句中出现另一个SELECT语句 这种查询称为嵌套查询 通常把仅嵌入一层子查询的SELECT语句称为单层嵌套查询 把嵌入子查询多于一层的查询称为多层嵌套查询 SQLServer允许多层嵌套查询 嵌套查询一般的查询方法是由里向外进行处理 8 4SELECT嵌套查询
17、单值嵌套查询子查询的返回结果是一个值的嵌套查询 例8 18 查询选课表中英语考试成绩 select学号 成绩 课程号from选课表where课程号 select课程号from课程表where课程名 英语 8 4SELECT嵌套查询 多值嵌套查询子查询的返回结果是一列值的嵌套查询 如果子查询的返回值不止一个 则必须指明在WHERE子句中怎样使用这些值 通常会使用条件运算符ANY 或SOME ALL和IN ANY运算符 满足子查询中任意一个值的记录 ALL运算符 满足子查询中所有值的记录 IN运算符 字段内容是结果集合或者子查询中的内容 8 4SELECT嵌套查询 例8 19 查询选课表中成绩高于
18、学号为s2008005的最低分的学号 课程号及考试成绩 select from选课表where成绩 any select成绩from选课表where学号 s2008005 例8 20 查询选课表中成绩高于学号为s2008005的最高分的学号 课程号及考试成绩 select from选课表where成绩 ALL select成绩from选课表where学号 s2008005 附学号为s2008005的考试成绩 8 4SELECT嵌套查询 例8 21 查询选c02课程的学生信息 select from学生表where学号in select学号from选课表where课程号 c02 提示 IN是属于
19、的意思 等价于 ANY 8 5连接查询 连接概述通过连接运算符可以实现多个表查询 连接是关系数据库模型的主要特点 也是它区别于其他类型数据库管理系统的一个标志 通过连接运算符可以实现多个表查询 建立连接的方法where子句例8 22 查询学生考试成绩 要求给出学号 课程名和成绩 select学号 课程名 成绩from选课表 课程表where选课表 课程号 课程表 课程号 8 5连接查询 from子句语法 FROM表1 连接类型 JOIN表2ON连接条件如上题 例8 22 用from子句来实现查询 select学号 课程名 成绩from选课表innerjoin课程表on选课表 课程号 课程表 课
20、程号课堂练习 查询学生考试成绩 要求显示课程号 姓名 成绩 分别用where子句和from子句实现 8 5连接查询 使用表别名由于查询来源于多个表 如果多个表有相同列 需要在列名前附加表名 形如 可以是原表名 也可以用别名 对比以下两段SQL语句 学会使用表别名 select学号 课程名 成绩 选课表 课程号from选课表 课程表where选课表 课程号 课程表 课程号 select学号 课程名 成绩 a 课程号from选课表a 课程表bwherea 课程号 b 课程号 课堂练习 查询学生考试成绩 要求显示学生学号 姓名 成绩 8 5连接查询 连接类型内连接 INNERJOIN 使用比较运算符
21、进行表间某 些 列数据的比较操作 并列出这些表中与连接条件相匹配的数据行 外连接 OUTERJOIN 不只列出与连接条件相匹配的数据行 而是列出左表 左外连接 右表 右外连接 或两个表 全外连接 中所有符合条件的数据行 交叉连接 CROSSJOIN 没有where子句 返回连接表中所有数据行的笛卡尔乘积 连接条件由被连接表中的列和比较运算符 逻辑运算符等构成 如 选课表 课程号 课程表 课程号 8 5连接查询 内连接列出与连接条件匹配的数据行 分为 种 等值连接 在连接条件中使用等号 比较被连接的列值 按对应列的共同值将一个表中的记录与另一个表中的记录相连接 包括重复列 例8 23 查询选课表
22、和课程表的等值连接 select from选课表innerjoin课程表on选课表 课程号 课程表 课程号 8 5连接查询 不等值连接 在连接条件中使用除等于 运算符以外的其他运算符比较被连接的列值 运算符 例8 24 查询课程表中与英语课时不同的课程 selecta 课程号 a 课程名from课程表ainnerjoin课程表bona 学时b 学时whereb 课程名 英语 课程表数据 查询结果 提示 连接对象是同一个表时 必须使用表别名 8 5连接查询 自然连接 连接条件中使用等于 运算符比较被连接列的列值 它使用选择列表方式来指出查询结果集合中所包括的列 并删除重复列 例8 25 查询选课
23、表和课程表的自然连接 select课程表 课程名 选课表 from选课表innerjoin课程表on选课表 课程号 课程表 课程号 8 5连接查询 外连接左外连接 引用左表的所有行 例8 26 学生表左外连接选课表 selecta b 课程号 b 成绩from学生表aleftouterjoin选课表bona 学号 b 学号 说明 左外连接限制选课表中的行 而不限制学生表中的行 即在左连接中 学生表中不满足条件的行也显示出来 8 5连接查询 右外连接 引用右表的所有行 例8 27 选课表右外连接课程表 selecta b 课程名from选课表arightouterjoin课程表bona 课程号
24、b 课程号全外连接 返回两个表的所有行 FULLOUTERJOIN 8 5连接查询 学生表 选课表 select学生表 学号 姓名 课程号 成绩from学生表innerjoin选课表on学生表 学号 选课表 学号 select学生表 学号 姓名 课程号 成绩from学生表leftjoin选课表on学生表 学号 选课表 学号 内连接 左外连接 交叉连接不带WHERE子句 返回被连接的两个表所有数据行的笛卡尔积 结果数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数 例8 28 学生表和课程表的交叉连接 select学号 姓名 课程名from学生表crossjoin课
25、程表问题 学生表中有 名学生 课程表中有 门课程 交叉连接结果有多少条记录 8 5连接查询 课堂练习 完成对如下三个表的查询 职工 职工号int 姓名char 8 性别char 2 出生日期datetime 党员否bit 参加工作datetime 部门号int 部门 部门号int 部门名char 10 工资 职工号int 工资日期datetime 工资decimal 6 1 显示所有职工的职工号 姓名 部门名 并按部门名升序排列 显示在2008年7月1日以前参加工作的职工号 姓名 性别 出生日期 部门名 显示部门号为1的职工号 姓名 工资日期 工资 8 5连接查询 将子查询作为来源表或输出列例
26、8 29 查询男同学的考试成绩 并显示学生姓名 selecta 学号 b 姓名 a 课程号 a 成绩from选课表ainnerjoin select学号 姓名from学生表where性别 男 bona 学号 b 学号 8 6在INSERT UPDATE和DELETE语句中使用子查询 在INSERT语句中使用子查询新增的数据可以来源于子查询 格式 INSERTSELECT语句例8 30 将选课表中90分以上的记录添加到高分表 高分表的数据模式与选课表相同 insert高分表select学号 课程号 成绩from选课表where成绩 90提示 新增表的列数和数据类型一定要和子查询返回结果一致 8
27、6在INSERT UPDATE和DELETE语句中使用子查询 在UPDATE语句中使用子查询例8 31 如果学生平均成绩在90分以上 则将学生表 考核 列的值改为 优 update学生表set考核 优 from学生表 select学号from选课表groupby学号havingavg 成绩 90 awhere学生表 学号 a 学号 8 6在INSERT UPDATE和DELETE语句中使用子查询 在DELETE语句中使用子查询删除条件来自一个子查询 例8 32 将男同学的记录从高分表中删除 deletefrom高分表where学号in select学号from学生表where性别 男 小结与提问 数据查询使用查询分析器SELECT语句简单查询嵌套查询连接查询在新增 修改和删除语句中使用子查询
