数据库原理及应用(SQL Server 2000部分)第九章.ppt

九数据查询与视图 9 1SELECT语句概述9 2单表查询9 3多表查询9 4嵌套查询9 5视图 SQL语言最主要的功能是数据库查询 它是数据库其他操作 如统计 插入 修改 删除 的基础 也是DBMS的核心功能之一 使用SQL语句将要连接的表 查询所需的字段 筛选记录的条件 记录分组的依据 排序的方式以及查询结果的显示方式 写在一条SQL语句中 就可以从表或视图中迅速 方便地检索数据 具有十分灵活的使用方式和丰富的功能 9 1SELECT语句概述 SELECTselect list INTOnew table FROMtable source WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC table name view name table alias column name expression AS column alias column alias expression n 将查询结果存入新表new table中 指定查询条件 指定将查询结果进行分组 与GROUPBY联合使用 指定分组条件 指定查询结果按order expression进行排序 指定在FROM子句内返回表或视图的所有列 指定查询的列名 指定列名 常量 函数以及由运算符连接的列名 常量和函数的任意组合 或者是子查询 指定查询结果集内列名的别名 整个SELECT语句的含义是 根据WHERE子句的条件表达式 从FROM子句指定的基本表或视图中找出满足条件的记录 再按照SELECT子句中的select list 选出记录中的列值 或计算值 或汇总值形成的查询结果 SELECT语句既可以完成简单的单表查询 也可以完成复杂的多表查询和嵌套查询 9 2单表查询 选择表中若干列 如果用户只对表中部分列感兴趣 查询时可不使用WHERE子句 选择表中的全部列或部分列 也称作投影查询 基本语法为 SELECT ALL DISTINCT TOPn PERCENT FROMtable source 参数说明 ALL 默认设置 指定在结果集中可以显示重复行 DISTINCT 指定在结果集中去除重复行 只显示唯一行 TOPn PERCENT 指定只从查询结果集中输出前n行或前百分之n行 例9 1 查询全体学生的学号 姓名和年龄 SELECTSNO SNAME SAGEFROMXS 查询指定列 9 2单表查询 选择表中若干列 例9 2 查询全体学生的情况 SELECTSNO SNAME SDEPT SSEX SAGE SHEIGHT SCPM SRMKFROMXS或SELECT FROMXS 查询全部列 9 2单表查询 选择表中若干列 例9 3 查询全体学生的姓名及出生年份 假设当前年份为2009年 SELECTSNAME 2009 SAGEFROMXS 查询结果中包含计算项 9 2单表查询 选择表中若干列 例9 4 查询全体学生的姓名 并将姓和名分别显示 SELECTSNAME SUBSTRING SNAME 1 1 SUBSTRING SNAME 2 2 FROMXS 查询结果中包含计算项 9 2单表查询 选择表中若干列 例9 5 对例9 4中的查询结果指定列标题 查询结果中的列标题显示 SELECTSNAMEAS学生姓名 SUBSTRING SNAME 1 1 AS姓 SUBSTRING SNAME 2 2 AS名FROMXS或SELECT学生姓名 SNAME 姓 SUBSTRING SNAME 1 1 名 SUBSTRING SNAME 2 2 FROMXS 9 2单表查询 选择表中若干列 例9 6 对比以下两条查询语句的执行结果 理解DISTINCT的作用 查询结果中消除重复行 SELECTSDEPT SSEXFROMXS SELECTDISTINCTSDEPT SSEXFROMXS 9 2单表查询 选择表中若干列 例9 7 查询XS表中前6个学生的情况 限制结果集的返回行数 SELECTTOP6 FROMXS SELECTTOP60PERCENT FROMXS 9 2单表查询 选择表中若干列 例9 8 查询xs kc表中学生成绩 对其成绩按以下规则进行替换 成绩90 100为 优秀 80 89为 良好 70 79为 中等 60 69为 及格 60为 不及格 替换显示结果的内容 SELECT学号 SNO 课程号 CNO 成绩 SCORE 等级 CASEWHENSCORE 60ANDSCORE 70ANDSCORE 80ANDSCORE 89THEN 良好 ELSE 优秀 ENDFROMXS KC 9 2单表查询 选择表中若干列 查询时使用WHERE子句 选择表中满足条件的若干行 也称作选择查询 基本语法为 SELECTselect listFROMtable sourceWHEREsearch condition 9 2单表查询 选择表中若干行 例9 9 查询选修课程号为 C01 的学生的学号和成绩 SELECTSNO SCOREFROMXS KCWHERECNO C01 比较运算符 例9 10 查询成绩高于85分的学生的学号 课程号和成绩 SELECTSNO CNO SCOREFROMXS KCWHERESCORE 85 9 2单表查询 选择表中若干行 例9 11 查询年龄在20 23岁 含20岁和23岁 之间的学生姓名 系别 SELECTSNAME SDEPTFROMXSWHERESAGEBETWEEN20AND23 确定范围 9 2单表查询 选择表中若干行 例9 12 查询选修C01或C02的学生的学号 课程号和成绩 SELECTSNO CNO SCOREFROMXS KCWHERECNOIN C01 C02 确定集合 9 2单表查询 选择表中若干行 例9 13 查询所有姓张的学生的学号和姓名 SELECTSNO SNAMEFROMXSWHERESNAMELIKE 张 字符匹配 例9 14 查询姓名中第二个汉字是 一 的学生的学号和姓名 SELECTSNO SNAMEFROMXSWHERESNAMELIKE 一 9 2单表查询 选择表中若干行 例9 15 查询没有备注信息的学生情况 SELECT FROMXSWHERESRMKISNULL 涉及空值 9 2单表查询 选择表中若干行 例9 16 查询电力专业所有男生的情况 SELECT FROMXSWHERESDEPT 电力 ANDSSEX 男 多重条件 例9 17 查询选修C01或C02且分数大于85分的学生的学号及成绩 SELECTSNO SCOREFROMXS KCWHERE CNO C01 ORCNO C02 ANDSCORE 85 9 2单表查询 选择表中若干行 9 2单表查询 聚集函数 计算诸如平均值和总和的函数被称为聚集函数 使用聚集函数时 系统对整个表或表的某个组中的列进行汇总 计算 然后为它创建相应字段的单个的值 在SELECT语句中可以单独使用聚集函数 也可以与语句GROUPBY联合使用 列的数据类型决定了在该列上可以使用的聚集函数类型 例9 18 查询电力系学生的总人数 SELECTCOUNT FROMXSWHERESDEPT 电力 COUNT函数 例9 19 查询选修了课程的学生人数 SELECTCOUNT DISTINCTSNO FROMXS KC 9 2单表查询 聚集函数 例9 20 查询课程号为 C01 的学生成绩的最高分和最低分 SELECTMAX SCORE 最高分 MIN SCORE 最低分FROMXS KCWHERECNO C01 MAX函数和MIN函数 例9 21 查询课程号为 C01 的学生成绩的总分和平均分 SELECTSUM SCORE 总分 AVG SCORE 平均分FROMXS KCWHERECNO C01 SUM函数和AVG函数 9 2单表查询 聚集函数 9 2单表查询 查询结果分组 在SELECT语句中使用GROUPBY子句可以将查询结果分组 并返回行的汇总信息 即对于GROUPBY子句中定义的每个组 各返回一个结果 此时 SELECT子句中的列名必须为分组列或列函数 基本语法为 GROUPBY ALL group by expression n WITH CUBE ROLLUP 其中 WITHCUBE或WITHROLLUP用于在查询结果中附加汇总结果 例9 22 查询各系学生人数 SELECTSDEPT系别 COUNT 人数FROMXSGROUPBYSDEPT GROUPBY基本用法 9 2单表查询 查询结果分组 例9 23 查询各门课程的平均成绩和选修人数 SELECTCNO课程号 AVG SCORE 平均成绩 COUNT 选修人数FROMXS KCGROUPBYCNO GROUPBY基本用法 9 2单表查询 查询结果分组 例9 24 查询各系男生人数 女生人数 SELECTSDEPT系别 SSEX性别 COUNT 人数FROMXSGROUPBYSDEPT SSEX GROUPBY基本用法 9 2单表查询 查询结果分组 例9 25 查询各系男生人数 女生人数 各系人数和总人数 SELECTSDEPT系别 SSEX性别 COUNT 人数FROMXSGROUPBYSDEPT SSEXWITHROLLUP 使用ROLLUP和CUBE子句 9 2单表查询 查询结果分组 例9 26 除例9 25的查询要求外 还需汇总男生总数和女生总数 SELECTSDEPT系别 SSEX性别 COUNT 人数FROMXSGROUPBYSDEPT SSEXWITHCUBE 使用ROLLUP和CUBE子句 9 2单表查询 查询结果分组 例9 27 查询平均成绩80分以上的学生学号和平均成绩 SELECTSNO AVG SCORE AS平均成绩FROMXS KCGROUPBYSNOHAVINGAVG SCORE 80 使用HAVING子句 9 2单表查询 查询结果分组 例9 28 查询全体学生的情况 查询结果按所在系的系号升序排列 同系学生按年龄降序排列 SELECT FROMXSORDERBYSDEPT SAGEDESC 例9 29 查询学生的学号和平均分 且按平均分降序排列 SELECTSNO AVG SCORE AS平均成绩FROMXS KCGROUPBYSNOORDERBY平均成绩DESC 9 2单表查询 查询结果分组 例9 30 查询电力系学生的学号 姓名 年龄 并产生一个学生总人数行 SELECTSNO SNAME SAGEFROMXSWHERESDEPT 电力 COMPUTECOUNT SNO 9 2单表查询 查询结果分组 例9 31 将学生按专业名排序 并汇总各专业人数和平均身高 SELECTSDEPT SNO SNAME SAGE SHEIGHTFROMXSORDERBYSDEPTCOMPUTECOUNT SNO AVG SHEIGHT BYSDEPT 9 2单表查询 查询结果分组 9 2单表查询 查询结果生成新表 例9 32 由XS表创建 电力系学生 表 包括学号 姓名 年龄 身高 SELECTSNO SNAME SAGE SHEIGHTINTO电力系学生FROMXSWHERESDEPT 电力 9 2单表查询 联合查询 例9 33 将XS表中数学系学生和 电力系学生 表数据合并 SELECTSNO SNAME SAGE SHEIGHTFROMXSWHERESDEPT 数学 UNIONSELECT FROM电力系学生 9 3多表查询 实际应用中 数据往往需要同时在相关联的多个表中得到 例如 需要查询电力系所有学生的成绩 多表查询是指同时涉及两个以上的表的查询 又称为连接查询 连接查询是关系数据库中最主要的查询 包括内连接 外连接和自连接 内连接 连接查询中用来连接多个表的条件称为连接条件 连接的表名之间用逗号隔开 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行 其一般格式为 也可使用 INNER JOINON子句实现 其一般格式为 FROM INNER JOIN ON 内连接 例9 33 查询每个学生的情况及选修课程的情况 SELECTXS XS KC FROMXS XS KCWHEREXS SNO XS KC SNO SELECTXS XS KC FROMXSJOINXS KCONXS SNO XS KC SNO 9 3多表查询 内连接 例9 34 查询选修了 电磁场 课程且成绩在85分以上的学生学号 姓名 课程名和成绩 SELECTXS SNO SNAME CNAME SCOREFROMXS KC XS KCWHEREXS SNO XS KC SNOANDKC CNO XS KC CNOANDCNAME 电磁场 ANDSCORE 85 SELECTXS SNO SNAME CNAME SCOREFROMXSJOINXS KCONXS SNO XS KC SNOJOINKCONKC CNO XS KC CNOWHERECNAME 电磁场 ANDSCORE 85 9 3多表查询 外连接 外连接返回FROM子句中提到的至少一个表或视图的所有行 包括左外连接 右外连接和全外连接 其一般格式为 FROMLEFT RIGHT FULL OUTER JOIN ON 左外连接的结果集包括LEFTOUTER子句中指定的左表的所有行 如果左表的某行在右表中没有匹配行 则在相关联的结果集行中右表的所有选择列表列均为NULL 右外连接是左外连接的反向连接 返回右表的所有行 如果右表的某行在左表中没有匹配行 则将为左表返回NULL 全外连接是左外连接和右外连接的并集 9 3多表查询 外连接 例9 37 查询课程被选修的情况 要求包含学号 课程号 课程名 同时还需显示没人选修的课程 SELECTXS KC SNO KC CNO CNAMEFROMXS KCRIGHTJOINKCONKC CNO XS KC CNO 9 3多表查询 自连接 将一个表与它自身进行连接 称为自连接 使用自连接时需为表指定两个别名 且对列的引用都要用别名限定 例9 38 查询与 赵一 同一专业的学生姓名 SELECTDISTINCTA SNAMEFROMXSAJOINXSBONA SDEPT B SDEPTANDB SNAME 赵一 9 3多表查询 在SQL语言中 一个SELECT FROM WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询称为嵌套查询 外层查询称为父查询 或主查询 内层查询称为子查询 或从查询 先处理子查询 再处理父查询 子查询可以嵌套多层 子查询的结果集又成为父查询的条件 9 4嵌套查询 9 4嵌套查询 简单嵌套查询 例9 39 查询课程号为 C01 且成绩高于学号为 010 的所有学生的学号和成绩 SELECTSNO SCOREFROMXS KCWHERECNO C01 ANDSCORE SELECTSCOREFROMXS KCWHERECNO C01 ANDSNO 010 例9 40 查询课程号为 C01 且成绩在平均分以上的学生的学号和姓名 SELECTXS SNO SNAMEFROMXS XS KCWHEREXS SNO XS KC SNOANDCNO C01 ANDSCORE SELECTAVG SCORE FROMXS KCWHERECNO C01 9 4嵌套查询 带IN谓词的嵌套查询 例9 41 查询选修了课程的学生情况 SELECT FROMXSWHERESNOIN SELECTDISTINCTSNOFROMXS KC 例9 42 查询所有课程中不及格的学生的学号 姓名和所在院系 SELECTSNO SNAME SDEPTFROMXSWHERESNOIN SELECTDISTINCTSNOFROMXS KCWHERESCORE 60 9 5视图 视图是从一个或多个表或视图中导出的表 是一个 虚 表 其结构和数据建立在对基本表的查询基础之上 和基本表一样 视图也包括多个被定义的列和多个数据行 但视图中的数据并不以视图结构存储在数据库中 而是存储在视图所引用的表中 并且在引用视图时动态生成 对视图的操作与对表的操作一样 可以对其进行查询 修改 有一定的限制 删除 对视图中的数据进行修改时 相应的基本表的数据也会发生变化 同时 若基本表的数据发生变化 则这种变化也可以自动地反映到视图中 9 5视图 9 5使用T SQL管理视图 创建视图 语法格式如下 CREATEVIEWview nameASselect statement注意 在SELECT语句中 不能使用COMPUTE COMPUTEBY等语句 不能使用INTO关键字 不能使用临时表 例9 47 使用T SQL语句创建视图 CREATEVIEWVIEW2ASSELECTXS SNO XS SNAME XS SDEPT XS KC CNO XS KC SCOREFROMXS XS KCWHEREXS SNO XS KC SNOANDXS SDEPT 电力 ANDXS KC SCORE 80 9 5使用T SQL管理视图 修改视图 语法格式如下 ALTERVIEWview nameASselect statement删除视图 语法格式如下 DROPVIEWview name 例9 48 修改例9 47中的视图 要求查询所有课程成绩80分以下的学生 ALTERVIEWVIEW2ASSELECTXS SNO XS S