用SELECT语句查询记录-《SQL Server2005程序设计语言》.ppt

SQLServer2005数据库程序设计语言 第7章数据操作 7 1用SELECT语句查询记录1 检索数据2 数据分组与汇总3 其它操作 1 检索数据 1 1使用SELECT语句检索数据1 2过滤数据1 3设置结果集格式1 4需要考虑的性能问题1 5推荐操作 1 1使用SELECT语句检索数据 1 1 1使用SELECT语句1 1 2指定列1 1 3使用WHERE子句指定行 SELECT ALL DISTINCT TOPn FROM n WHERE GROUPBY n HAVING ORDERBY n 基本语法 使用SELECT语句 用 括起来的是可选项 SELECT是必需的选择列表指定了要返回的列WHERE子句指定限制查询的条件在搜索条件中 可以使用比较操作符 字符串 逻辑操作符来限制返回的行数 1 1 1使用SELECT语句 使用SELECT语句 续 FROM子句知道了返回的行和列所属的表DISTINCT选项从结果集中消除了重复的行 TOPn选项限定了要返回的行数GROUPBY子句是对结果集进行分组HAVING子句是在分组的时候 对字段或表达式指定搜索条件ORDERBY子句对结果集按某种条件进行排序 1 1 1使用SELECT语句 指定列 1 1 2指定列 在选择列表中指定要从表中返回的列在选择列表中可以包含字段名 表达式 所选关键字或要赋值的局部变量 其语法为 表名 视图名 表的别名 字段名 表达式 IDENTITY ROWGUID AS 字段别名 字段别名 表达式 n 指定要检索的字段时 请注意选择列表将按照指定顺序查找并显示所查询的字段不同的字段名用 分开在选择列表中 避免或尽量少使用符号 该符号代表选择表中所有的字段 查询指定列通过指定SELECT子句的来完成例1 查询全体学生的学号和姓名 SELECT 例2 查询全体学生的姓名 学号 年龄 SELECT Sno Sname FROM Student Sname Sno Sage FROM Student Student Sno Sname Ssex Sage Sdept 中各个列的先后顺序可以与表中的顺序不一致 用户可以根据应用的需要改变列的显示顺序 本例中先列出姓名 再列学号和年龄 查询全部列 表示 所有的属性 例3给出所有学生的信息 selectSno Sname Ssex Sage SdeptfromStudent select fromStudent 等价于 Student Sno Sname Ssex Sage Sdept 查询经过计算的值select子句的不仅可以是表中的属性列 也可以是表达式 select SNAME 2011 Sage from Student 例4给出所有学生的姓名及其出生年份 Student Sno Sname Ssex Sage Sdept 例5给出所有学生姓名 出生年份和所在系编号 要求用小写字母表示所有系名 select Sname YearofBirth 2011 Sage from LOWER Sdept STUDENT 不仅可以是算术表达式 还可以是字符串常量 函数 使用WHERE子句指定行 1 1 3使用WHERE子句指定行 通过使用WHERE子句 可以得到基于搜索条件的行 NOT AND OR NOT n 列出了可以在WHERE子句中包含的表达式列表使用WHERE子句指定行时 请注意在所有数据类型为char nchar varchar nvarchar text datetime和smalldatetime的数据周围 必须使用单引号 在使用SELECT语句时 尽量使用WHERE语句限制将要返回的行 使用WHERE子句指定行 续 示例 1 1 3使用WHERE子句指定行 1 检索数据 1 1使用SELECT语句检索数据1 2过滤数据1 3设置结果集格式1 4需要考虑的性能问题1 5推荐操作 过滤数据 1 2过滤数据 使用比较运算符 1 2 1使用比较运算符 使用比较操作符来比较表中的值与指定的值或表达式的值 USEnorthwindSELECTlastname cityFROMemployeesWHEREcountry USA GO 使用字符串比较符 1 2 2使用字符串比较符 在比较字符串时 可以使用LIKE和通配符来查找需要的行 通配符的种类 使用字符串比较符 续 1 2 2使用字符串比较符 使用逻辑运算符 1 2 3使用逻辑运算符 用逻辑操作符AND OR和NOT来连接一系列的表达式 及优化查询过程 使用逻辑运算符时 遵循下列原则使用AND返回满足所有条件的行使用OR返回满足任一条件的行使用NOT返回不满足条件的行有多个表达式时 使用圆括号为表达式分组改变表达式求值顺序增加表达式的可读性 使用逻辑运算符 续 搜索条件的顺序MicrosoftSQLServer首先求NOT表达式的值 然后是AND 最后是OR当表达式中所有的操作符优先级相同时 求值顺序由左到右 1 2 3使用逻辑运算符 productnameLIKE T ORproductid 46 AND unitprice 16 00 productnameLIKE T OR productid 46ANDunitprice 16 00 检索一定范围内的值 使用BETWEEN来查询在一定范围内的值 使用BETWEEN时 注意SQLServer返回的结果集中 包含范围内的边缘值尽量使用BETWEEN 而不用AND和比较操作符表示的表达式 xANDxAND y 要返回不在指定区域的行时 使用NOTBETWEEN 1 2 4检索一定范围内的值 unitpriceBETWEEN10AND20 使用值列表作为搜索条件 1 2 5使用值列表作为搜索条件 使用IN语句指定一个值的列表作为查询条件 使用IN时 注意使用IN搜索条件和使用由OR操作符连起来的一系列比较操作符 其效果是等价的在搜索条件中不能包含NULL值 这将返回不可预测的结果集使用NOTIN搜索条件来返回值不在指定列表中的行 使用NOT会降低数据检索速度 countryIN Japan Italy country Japan orcountry Italy 检索未知值 1 2 6检索未知值 输入数据时 没有指定一个字段的值 且此字段没有定义默认值的值 称此字段含有空值 NULL 使用ISNULL搜索条件来查询某指定列没任何信息的行 需要返回含有空值的行时 注意把空值和任何表达式比较都会失败 因为空值不等于任何表达式在CREATETABLE语句里 定义一个字段是否允许空值的存在使用ISNOTNULL来查询指定列中非空的行 ScoreISNULL 1 检索数据 1 1使用SELECT语句检索数据1 2过滤数据1 3设置结果集格式1 4需要考虑的性能问题1 5推荐操作 设置结果集格式 对数据进行排序消除重复行改变列名使用字面值FOR子句UNION ALL 1 3设置结果集格式 对数据进行排序 1 3 1对数据进行排序 使用ORDERBY子句为结果集中的行排序时 有升序和降序两种 使用ORDERBY时 注意在安装SQLServer时 排序顺序已指定除非使用ORDERBY指定顺序 SQLServer并不主动为结果集中的行排序SQLServer缺省地按升序排列ORDERBY子句中指定的列并不一定要出现在选择列表中可以按照字段名 计算出的值或表达式进行排序可以在ORDERBY子句中引用某字段在选择列表中的位置而进行排序ORDERBY子句不能用于数据类型text ntext或image字段 对数据进行排序 续 USEnorthwindSELECTproductid productname categoryid unitpriceFROMproductsORDERBYcategoryid unitpriceDESCGO 示例 1 3 1对数据进行排序 消除重复行 1 3 2消除重复行 用DISTINCT可以消除重复行 使结果集列出惟一的值 使用DISTINCT时 注意除非指定DISTINCT子句 否则SELECT语句将返回所有满足搜索条件的行选择列表中所有列的组合值将决定其惟一性包含任何惟一组合值的行都将被检索到并被返回到结果集中除非使用了ORDERBY子句 否则DISTINCT子句将按随机的顺序显示结果集中的行 消除重复行 续 USEnorthwindSELECTDISTINCTcountryFROMsuppliersORDERBYcountryGO 示例 1 3 2消除重复行 改变列名 1 3 3改变列名 为了提高可读性 可以为选择列表中的字段名起别名改变字段名时 应注意缺省情况下 结果集所显示的字段名 就是在CREATETABLE语句中指定的字段名如果字段名中有空格 或字段名不符合SQLServer命名规则 用单引号来标识这个字段名可以为经过计算的包含函数 字符符号的字段起别名字段名中至多可以包含128个字符 改变列名 续 USEnorthwindSELECTfirstnameASFirst lastnameLast EmployeeID employeeidFROMemployees 1 3 3改变列名 示例 使用字面值 1 3 4使用字面值 使用字面值可以增加结果集的可读性 符号可能是字母 数字或标识 它们被用作特定的值 其语法为 SELECT字段名 字符串符号 字段名 字符串符号 FROM表名 USEnorthwindSELECTfirstname lastname Identificationnumber employeeidFROMemployeesGO FOR子句 FOR子句用来指定BROWSE或XML选项 主要在应用程序的开发中经常使用 FOR子句生成XML的详细使用介绍请参考第24章 本节通过一个使用FOR子句的示例 简单说明其一般的功能 1 3 5FOR子句 UNION ALL 将两个或更多查询的结果合并为单个结果集 该结果集包含联合查询中的所有查询的全部行 UNION运算不同于使用联接合并两个表中的列的运算 1 3 6UNION ALL USEAdventureWorksSELECTProductModelID NameFROMProduction ProductModelUNIONSELECTProductModelID NameFROMdbo ProductModel2ORDERBYName 使用UNION合并两个查询结果集的基本规则 所有查询中的列数和列的顺序必须相同 数据类型必须兼容 需要考虑的性能问题 非逻辑操作会降低查询速度比如 NOTBETWEEN NOTIN和ISNOTNULLLIKE搜索条件会降低查询速度使用精确查询或在一定范围的查询可能会提高数据查询速度ORDERBY子句可能降低数据查询速度因为SQLServer在返回第一行前 必须对结果集中所有的行进行计算并排序 1 4需要考虑的性能问题 推荐操作 提出问题 我们遇到了下面的问题 我们拥有桌子和椅子两种产品 每种产品都有红色和蓝色两种颜色 每种颜色的桌子有多少库存 每种颜色的椅子有多少库存 椅子有多少库存 不论它是什么颜色 桌子有多少库存 不论它是什么颜色 红色的商品一共有多少库存 蓝色的商品一共有多少库存 这个时候大家发现 我们需要的是以不同商品种类和颜色进行排列组合的小计报表 2 数据分组与汇总 使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作 使用TOPn列出前n个记录 2 1使用TOPn列出前n个记录 关键字TOPn只列出结果集中前n个或前n 的记录 使用TOPn或TOPnPERCENT时 应注意在ORDERBY子句中指定值的范围关键字TOP后使用无符号的整数如果TOPnPERCENT生成小数 则SQLServer将把这个数取整可以在结果集中用WITHTIES子句包含那些值相等的记录 这时结果集中可以包含任意数目的行 部分语法 SELECT ALL DISTINCT TOPn PERCENT WITHTIES 要求 1 检索出SC表中的所有成绩记录 2 检索出SC表中的前5条成绩记录 3 检索出SC表中的前50 的成绩记录 4 检索出SC表中的前5 的成绩记录 注意观察有几条记录 5 检索出SC表中成绩前5名的学生学号 6 检索出SC表中成绩前5名的学生学号 使用TOPn列出前n个记录 2 1使用TOPn列出前n个记录 限制结果集大小的另一种方法是在执行一个语句之前执行SETROWCOUNTn语句 SETROWCOUNT与TOP的不同之处在于 TOP子句适用于指定了该子句的单个SELECT语句 在执行另一个SETROWCOUNT语句之前 SETROWCOUNT会一直有效 例如执行SETROWCOUNT0将会关闭此选项 SETROWCOUNTn 使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作 2 数据分组与汇总 使用TABLESAMPLE限制结果集 2 2使用TABLESAMPLE限制结果集 TABLESAMPLE子句将从FROM子句中的表返回的行数限制到样本数或行数的某一百分比 不能对视图使用TABLESAMPLE 部分语法 TABLESAMPLE SYSTEM sample number PERCENT ROWS 执行几次代码 所得到的结果是不同的USEAdventureWorks GOSELECTcount FROMSales CustomerTABLESAMPLESYSTEM 10PERCENT 使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作 2 数据分组与汇总 使用聚合函数 续 2 3使用聚合函数 计算诸如平均值和总和的函数称为聚合函数SQLServer对整个表或表里某个组中的字段进行汇总 计算 然后生成单个的值可以在SELECT语句中单独使用聚合函数 也可以与语句GROUPBY联合使用除了COUNT 函数 如果记录集中没有满足WHERE子句的记录 则所有函数返回空值 COUNT 返回0字段的数据类型决定了可以用在该字段上的聚合函数类型如 SUM和AVG只能用在数据类型代表数字的字段上 使用含有空值的聚合函数 空值能使聚合函数产生意想不到的结果对包含空值的字段使用聚合函数时 应注意SQLServer的聚合函数 COUNT 除外 将忽略字段中的空值COUNT 将计算所有的行 即使每个字段都含有空值 2 3 1使用含有空值的聚合函数 createtabletable1 snochar 5 agetinyint insertintotable1values 95001 null insertintotable1values 95002 null insertintotable1values null null insertintotable1values 95003 null 以下返回的结果分别是什么selectcount fromtable1selectcount sno fromtable1selectcount age fromtable1selectavg age fromtable1 ISNULL 的使用 selectavg salary fromavg test selectavg isnull salary 0 fromavg test 常见的聚合函数及其描述 使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作 2 数据分组与汇总 GROUPBY的基础知识 使用GROUPBY子句联合使用GROUPBY子句和HAVING子句 2 4GROUPBY的基础知识 GROUPBY子句包含以下组件 一个或多个自由聚合的表达式 通常是对分组列的引用 ALL关键字 可选 该关键字指定返回由GROUPBY子句产生的所有组 即使某些组没有符合搜索条件的行 CUBE或ROLLUP 注意 在GROUPBYALL子句中 不允许使用CUBE和ROLLUP选项 HAVING子句 注意 不要联合使用关键字ALL和HAVING子句 因为HAVING子句会忽略ALL关键字 并返回只符合HAVING条件的组 下面的例子给出了针对所有type和pub id的唯一组合进行分类并统计计算的情况 usepubsgoselecttype pub id avg price avg price sum price sum price fromtitleswheretypein business trad cook groupbyalltype pub idorderbytype 使用GROUPBY子句 2 4 1使用GROUPBY子句 联合使用聚合函数和GROUPBY子句 能够把表中的记录分组 并对组中数据进行汇总 使用GROUPBY子句时 应注意SQLServer将为每一组计算一个汇总值 并把汇总值保存在一个字段中对于指定的一组 SQLServer只生成一条记录 不返回详细信息SQLServer只对满足WHERE子句的记录进行分组和汇总GROUPBY子句的字段列表至多包含8060个字节不要对可包含空值的字段使用GROUPBY子句 因为空值也将被当作一组ALL关键字 可选 该关键字指定返回由GROUPBY子句产生的所有组 即使某些组没有符合搜索条件的行 createtablep1 snochar 3 sexchar 8 ageint insertintop1values 001 m 20 insertintop1values null null null insertintop1values null null null selectcount sno fromp1groupbysno 不要对可包含空值的字段使用GROUPBY子句 因为空值也将被当作一组 使用GROUPBY子句 续 SELECTproductid orderid quantityFROMorderhistGO SELECTproductid SUM quantity AStotal quantityFROMorderhistGROUPBYproductidGO 只对满足WHERE子句的行分组 SELECTproductid SUM quantity AStotal quantityFROMorderhistWHEREproductid 2GROUPBYproductidGO 示例1 示例2 2 4 1使用GROUPBY子句 联合使用GROUPBY子句和HAVING子句 2 4 2联合使用GROUPBY子句和HAVING子句 在分组的同时 对字段或表达式指定搜索条件使用HAVING子句时 应注意只在使用GROUPBY子句的同时 使用HAVING子句来限制分组可以引用任何出现在选择列表中的字段不要联合使用关键字ALL和HAVING子句 因为HAVING子句会忽略ALL关键字 并返回只符合HAVING条件的组 联合使用GROUPBY子句和HAVING子句 续 SELECTproductid orderid quantityFROMorderhistGO SELECTproductid SUM quantity AStotal quantityFROMorderhistGROUPBYproductidHAVINGSUM quantity 30GO 2 4 2联合使用GROUPBY子句和HAVING子句 使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作 2 数据分组与汇总 在结果集中生成汇总值 使用带有CUBE运算符的GROUPBY子句使用带有ROLLUP运算符的GROUPBY子句使用GROUPING函数 2 5在结果集中生成汇总值 使用带有CUBE运算符的GROUPBY子句 2 5 2使用带有CUBE运算符的GROUPBY子句 使用带有CUBE运算符的GROUPBY子句创建并汇总所有可能的基于GROUPBY子句分组的组合 使用带有CUBE运算符的GROUPBY子句用于提供标准的关系型格式的数据 selectitem color sum quantity fromcpgroupbyitem colorwithcube chair blue的详细值chair red的详细值汇总chair的所有记录table blue的详细值table red的详细值汇总table的所有记录总计汇总bule的所有记录汇总red的所有记录 每种产品的每种颜色的总数量 每种产品的所有颜色的总数量 每种颜色的所有产品的总数量 所有产品所有颜色的总数量 使用带有CUBE运算符的GROUPBY子句 2 5 2使用带有CUBE运算符的GROUPBY子句 使用GROUPBY子句和CUBE操作符时 应注意如果在GROUPBY子句中有n个字段或表达式 SQLServer将在结果集中返回2n种可能的组合结果集中含有NULL的记录代表该记录由CUBE操作符生成 注意此NULL不是基本表中的实际空值 不能同时使用关键字ALL和操作符CUBE 使用带有ROLLUP运算符的GROUPBY子句 2 5 1使用带有ROLLUP运算符的GROUPBY子句 ROLLUP运算符的功能类似于CUBE运算符 使用带有ROLLUP运算符的GROUPBY子句汇总分组值使用带有ROLLUP运算符的GROUPBY子句用于提供标准的关系型格式的数据 selectitem color sum quantity fromcpgroupbyitem colorwithrollup chair blue的详细值chair red的详细值汇总chair的所有记录table blue的详细值table red的详细值汇总table的所有记录总计 每种产品的每种颜色的总数量 每种产品的所有颜色的总数量 所有产品所有颜色的总数量 使用带有ROLLUP运算符的GROUPBY子句 2 5 1使用带有ROLLUP运算符的GROUPBY子句 使用GROUPBY子句和ROLLUP操作符时 应注意SQLServer将在结果集中增加一行 这行将显示总和或平均值之类的汇总值 新增的行以NULL标识不能同时使用关键字ALL和操作符ROLLUP 使用GROUPING函数 2 5 3使用GROUPING函数 如何区分CUBE操作或ROLLUP运算符所生成的NULL值和从实际数据中返回的NULL值 使用GROUPING区分空值如果列中的值来自事实数据 则GROUPING函数返回0 如果列中的值是CUBE操作或ROLLUP操作所生成的NULL 则返回1 在CUBE或ROLLUP操作中 所生成的NULL代表全体值 insertintocpvalues table null 202 insertintocpvalues table null 180 selectitem grouping item color grouping color sum quantity fromcpgroupbyitem colorwithcube 使用带有CUBE运算符的GROUPBY子句 示例 使用带有ROLLUP运算符的GROUPBY子句 SELECTproductid orderid SUM quantity AStotal quantityFROMorderhistGROUPBYproductid orderidWITHROLLUPORDERBYproductid orderidGO 示例 1代表前一字段的汇总值0代表前一字段的具体值 使用GROUPING函数 续 SELECTproductid GROUPING productid orderid GROUPING orderid SUM quantity AStotal quantityFROMorderhistGROUPBYproductid orderidWITHCUBEORDERBYproductid orderidGO total quantity 示例 问题 ROLLUP和CUBE的区别在什么地方 CUBE生成的结果集显示了所选列中值的所有组合的聚合 ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合 使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作 2 数据分组与汇总 使用COMPUTE和COMPUTEBY子句 2 6使用COMPUTE和COMPUTEBY子句 生成某一列的明细值和汇总值的报表为组中的子集生成明细值和汇总值的报表 使用COMPUTE和COMPUTEBY子句 续 COMPUTEBY COMPUTE SELECTproductid orderid quantityFROMorderhistORDERBYproductid orderidCOMPUTESUM quantity BYproductidCOMPUTESUM quantity SELECTproductid orderid quantityFROMorderhistORDERBYproductid orderidCOMPUTESUM quantity 示例2 示例1 2 6使用COMPUTE和COMPUTEBY子句 生