数据库数据查询教学PPT课件.ppt

1 第4章数据查询 基本查询 嵌套查询 连接查询嵌套查询和连接查询 2 学习目标 了解 SQL语言特点 SQL查询语句SELECT基本组成 理解 SQL查询语句的用法 SELECT语句语法格式及各项子句的含义 联接查询和嵌套查询的基本概念 掌握 查询语句SELECT的综合运用 能进行简单查询 多表联接以及子查询的应用 3 4 1基本查询 4 1 1简单查询4 1 2带条件查询4 1 3查询结果处理 4 4 1基本查询 数据查询语句SELECT的基本框架是 SELECT FROM WHERE SELECT语句的语法格式 SELECTselect list INTOnew table FROMtable source WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC 数据库技术与应用基础教材编写组 需要哪些列 从哪些表 根据什么条件 5 4 1 1简单查询 简单查询是指无条件查询 其语法格式为 SELECT ALL DISTINCT TOPn PERCENT FROMtable name其中 ALL表示输出所有记录 包括重复记录 DISTINCT表示输出无重复结果的记录 TOPn指定返回查询结果的前n行数据 select list中的选项可以是 字段名 表达式或函数 6 4 1 1简单查询 举例 SELECTallst nameFROMSt InfoSELECTDISTINCTSt nameFROMSt InfoSELECTDISTINCTst id St nameFROMSt InfoSELECTst nameas姓名 st sexAS性别FROMSt InfoSELECTst name姓名 st sex性别FROMSt InfoSELECTtop3St idFROMS c InfoSELECTtop20PERCENTSt idFROMS c Info 7 4 1 1简单查询 常用聚合函数 8 4 1 1简单查询 举例 例如 分别查询St Info表的学生总数和学生的平均年龄 查询学生总数 使用COUNT SELECTCOUNT AS总数FROMSt Info查询学生的平均年龄 使用AVG SELECTAVG YEAR GETDATE YEAR Born date AS平均年龄FROMSt info 9 4 1 2带条件查询 语法格式为 WHEREsearch condition如列出St Info表中年龄在20岁以上学生记录 SELECT FROMSt InfoWHEREYEAR GETDATE YEAR Born date 20 这是一个关系表达式 10 4 1 2带条件查询 WHERE子句中的条件运算符 11 4 1 2带条件查询 举例 例4 6 对s c info表 列出选修 大学计算机基础 和 体育 两门课的学生信息 SELECT FROMs c infoeINNERJOINc infodONe c no d c noWHEREd c nameIN 大学计算机基础 体育 例4 7 对st info表 列出年龄在18到19之间的学生名单 SELECT FROMst infoWHEREYEAR GETDATE YEAR born date BETWEEN18AND19 12 4 1 2带条件查询 举例 例4 8 对st info表 列出所有的姓 王 的学生名单 SELECT FROMst infoWHEREst nameLIKE 王 例4 9 对st info表 列出所有Telephone为空值的学生的信息 SELECTst info id st info nameFROMst infoWHERETelephoneISNULL 13 4 1 3查询结果处理 排序输出 ORDERBY 语法格式为 ORDERBYorder by expression1 ASC DESC order by expression2 ASC DESC 例4 10 对st info表 按性别顺序列出学生的信息 性别相同的再按年龄由小到大排序 SELECT FROMst infoORDERBYst sex born dateDESC 14 4 1 3查询结果处理 重定向输出 INTO 语法格式为 INTOnew table 例4 11 对s c info和c info表 查询出选修 大学计算机基础 所有学生的信息 并将结果存入newstudent表中 操作的语句为 SELECTs c info st id c info c name s c info scoreINTOnewstudentFROMs c infoINNERJOINc infoONs c info c no c info c noWHEREc info c name 大学计算机基础 15 4 1 3查询结果处理 输出合并 UNION 语法格式为 UNION ALL 例4 12 对c info表 列出课程编号为 9710011 或 9720033 的课程名称和学分 SELECTc name c creditFROMc infoWHEREc no 9710011 UNIONSELECTc name c creditFROMc infoWHEREc no 9720033 16 4 1 3查询结果处理 分组统计 GROUPBY 语法格式为 GROUPBYgroup by expression1 group by expression2 筛选 HAVING 语法格式为 HAVING 例4 15 对s c info表 列出平均成绩大于80的课程编号和平均成绩 SELECTc no AVG score as平均成绩FROMs c infoGROUPBYc noHAVINGAVG score 80 17 4 1 3查询结果处理 使用COMPUTE和COMPUTEBY子句汇总 例4 16 列出st info表中 材料科学0601班 学生的年龄及平均年龄 即年龄的明细行和汇总行 SELECTst id YEAR GETDATE YEAR born date as年龄FROMst infoWHEREcl name 材料科学0601班 ORDERBYst idCOMPUTEsum YEAR GETDATE YEAR born date 18 4 2嵌套查询 4 2 1单值嵌套查询4 2 2多值嵌套查询 19 4 2嵌套查询 嵌套查询 子查询 在一个SELECT语句的WHERE子句或HAVING子句中嵌套另一个SELECT语句的查询称为嵌套查询 又称子查询 嵌套查询的类型单值嵌套查询多值嵌套查询 20 4 2 1单值嵌套查询 单值嵌套查询子查询的返回结果是一个值的嵌套查询称为单值嵌套查询 例4 18 对student db数据库 列出选修 大学计算机基础 的所有学生的学号和成绩 操作的语句为 SELECTst id scoreFROMs c infoWHEREc no SELECTc noFROMc infoWHEREc name 大学计算机基础 21 4 2 2多值嵌套查询 多值嵌套查询子查询的返回结果是结果集的嵌套查询称为多值嵌套查询 若某个子查询的返回值不止一个 则必须在WHERE子句中指明如何使用这些返回值 通常使用条件运算符 ANY SOME ALLIN 22 4 2 2多值嵌套查询 举例 使用ANY运算符 例4 19 对student db数据库 列出选修 9710011 即 大学计算机基础 的学生的成绩比选修 29000011 即 体育 的学生的最低成绩高的学生的学号和成绩 操作的语句为 SELECTst id scoreFROMs c infoWHEREc no 9710011 andscore ANY SELECTscoreFROMs c infoWHEREc no 29000011 23 4 2 2多值嵌套查询 举例 使用ALL运算符 例4 20 对student db数据库 列出选修 29000011 即 体育 的学生的成绩比选修 9710011 即 大学计算机基础 的学生的最高成绩还要高的学生的学号和成绩 操作的语句为 SELECTst id scoreFROMs c infoWHEREc no 29000011 andscore ALL SELECTscoreFROMs c infoWHEREc no 9710011 24 4 2 2多值嵌套查询 举例 使用IN运算符 例4 21 对student db数据库 列出选修 29000011 即 体育 或选修 9710011 即 大学计算机基础 的学生学号和成绩 操作的语句为 SELECTst id scoreFROMs c infoWHEREc noIN SELECTc noFROMc infoWHEREc name 大学计算机基础 ORc name 体育 25 4 3连接查询 4 3 1连接查询概述4 3 2内连接4 3 3外连接 26 4 3连接查询 连接查询的概念一个查询同时涉及两个或两个以上的表 则称之为连接查询 可以根据各个表之间的逻辑关系从两个或多个表中检索数据 连接查询的类型内连接 Innerjoin 外连接 Outerjoin 交叉联接 Crossjoin 连接查询的建立在SELECT语句的WHERE子句中建立在连接FROM子句中建立 27 4 3 1连接查询概述 在SELECT语句的WHERE子句中建立连接当需要对两个或多个表连接时 可以指定连接的列 在WHERE子句中给出连接条件 在FROM子句中指定要连接的表 例4 22 对student db数据库输出所有学生的成绩单 要求给出学生的学号 姓名 课程号和成绩 SELECTst info st id st info st name c info c name s c info c no s c info scoreFROMst info c info s c infoWHEREst info st id s c info st idands c info c no c info c no 28 4 3连接查询 在FROM子句中建立连接 推荐使用 在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来 FROM子句连接的语法格式 FROMjoin table join type JOINjoin tableONjoin condition 例4 22 中的连接用FROM子句建立 SELECTst info st id st info st name c info c name s c info c no s c info scoreFROMst infoINNERJOINs c infoONst info st id s c info st idINNERJOINc infoONs c info c no c info c no 29 4 3 2内连接 内连接查询操作列出与连接条件匹配的数据行 它使用比较运算符比较被连接列的列值 内连接的类型等值连接不等值连接自然连接 30 4 3 2内连接 等值连接在连接条件中使用等号 运算符比较被连接列的列值 按对应列的共同值将一个表中的记录与另一个表中的记录相连接 包括其中的重复列 例4 23 对student db数据库 列出男生的选课情况 要求列出学号 姓名 课程号和成绩 SELECT FROMst infoINNERJOINs c infoONst info st id s c info st idINNERJOINc infoONs c info c no c info c noWHERE st info st sex 男 31 4 3 2内连接 不等值连接在连接条件中使用除等于运算符以外的其他比较运算符比较被连接的列的列值 这些运算符包括 例4 24 对student db数据库 列出选修 9720013 课程中 成绩大于学号为 2001050108 的学生该门课程成绩的学生的学号及成绩 SELECTa st id a scoreFROMs c infoaINNERJOINs c infobONa score b scoreANDa c no b c noWHERE b st id 2001050108 AND b c no 9720013 32 4 3 2内连接 自然连接在连接条件中使用等于 运算符比较被连接列的列值 它使用选择列表方式来指出查询结果集合中所包括的列 并删除连接表中的重复列 例4 25 实现s c info表和c info表的自然连接 SELECTa st name b FROMst infoaINNERJOINs c infobONa st id b st id 33 4 3 3外连接 外连接查询返回查询结果集合中的不仅包含符合联接条件的行 返回FROM子句中提到的至少一个表的所有行 外连接的类型左外连接右外连接全外连接 34 4 3 3外连接 使用左外连接左外连接通过左向外连接引用左表的所有行 例4 26 st info表左外连接s c info表 SELECTa st id a st name b c no b scoreFROMst infoaLEFTOUTERJOINs c infobONa st id b st id 35 4 3 3外连接 使用右外连接右外连接通过右向外连接引用右表的所有行 例4 27 st