第3章_5 SQL Server数据查询.ppt

SQLServer数据查询 简单的数据查询聚合函数与分组查询联接查询子查询数据管理高级应用 数据查询 查询的分类SELECT语句基本格式选择表中的若干列选择表中的若干行 查询的分类 数据查询 SELECT语句的基本格式 SELECT的基本用法显示常量Select Thisisatest Select hello 查看全局变量Select languageSelect version获得函数值SelectGetdate SelectYear Getdate SELECT ALL DISTINCT TOPn PERCENT INTOnew table 指定结果存入新表 FROM n 指定表或视图 WHERE 指定查询条件 GROUPBY n 指定分组表达式 HAVING 指定分组统计条件 ORDERBY n 指定排序表达式和顺序 基本语法 SELECT语句的基本格式 选择表中的若干列 检索表中的所有列检索表中的指定列使用计算列为结果集中的列指定别名 选择表中的若干列 检索表中的所有列 USENorthwindGoSELECT FROMemployeesGO 表示所有字段 列 进行检索的表 选择表中的若干列 检索表中的指定列 USENorthwindGoSELECTEmployeeID LastName FirstNameFROMemployeesGO 希望显示的字段 列 名 进行检索的表名 选择表中的若干列 USEnorthwindSELECTemployeeid lastname firstname titleFROMemployeesGO 示例 选择表中的若干列 举例在Northwind数据库中查询以下内容 1 查询表orders的全部内容 2 在表customers中查询客户编号 公司名称 地址 所在城市 3 在表orders的订单编号 客户编号 员工编号 订单日期 供货日期 使用计算列 在titles表中检索所有书的编号 单价 打7折后的价格 USEpubsGOSELECTtitle id price price 0 7FROMtitles 使用计算列 在employees表中检索所有员工的编号 员工的姓名 firstname与lastname的组合 USENorthwindGOSELECTEmployeeID LastName FirstNameFROMemployees 为结果集中的列指定别名 语法SELECT表达式 AS 列别名FROM数据源 USEpubsGOSELECTtitle idas 编号 priceas 原价 price 0 7as现价FROMtitles 选择表中的若干行 消除取值重复的行限制返回的行数对查询的结果排序查询满足条件的行Top的使用 消除取值重复的行 使用DISTINCT关键字 SELECTstateFROMauthors SELECTdistinctstateFROMauthors SELECTstate cityFROMauthors SELECTdistinctstate cityFROMauthors 限制返回的行数 使用TOPn TOPnpercent关键字 SELECTtop6 FROMauthors SELECTtop10percent FROMauthors SELECTtop10au id phone addressFROMauthors SELECTtop5percentau id phoneFROMauthors 对查询的结果排序 语法格式为 ORDERBY 列名 ASC DESC n select fromtitlesOrderbyprice select fromtitlesOrderbypriceasc select fromtitlesOrderbypricedesc 对查询的结果排序 续 查询书的信息 按书的类型和价格升序排列 SELECT FROMtitlesORDERBYtitle pricedescGO 查询书的信息 按书名升序和价格降序排列 SELECT FROMtitlesORDERBYtype priceGO 查询满足条件的行 使用WHERE子句指定行例 查询5号员工的编号 姓 名 职位 查询满足条件的行 常用的查询条件 查询满足条件的行 使用比较运算符使用字符串比较符使用逻辑运算符检索一定范围内的值使用值列表作为搜索条件检索未知值 使用比较运算符 使用比较操作符来比较表中的值与指定的值或表达式的值 使用比较运算符 续 查询表employees中所有居住在美国的职员的姓和居住的城市 使用比较运算符 续 查询单价小于10的书的所有信息 查询类型为 business 的书的书名和单价 USEpubsGoSELECT FROMtitlesWHEREprice 10GO SELECTtitle priceFROMtitlesWHEREtype business GO 使用字符串比较符 在比较字符串时 可以使用LIKE和通配符来查找需要的行通配符的种类 使用字符串比较符 续 从表customers中 查找公司名字中含字符串 restaurant 的行 使用字符串比较符 续 selectau lname au fnamefromauthorswhereau fnamelike D 从表authors中 查找au fname以字符 D 打头的作者的姓 名 从表titles中 查找书名中包含字符串 computer 的书的所有信息 select fromtitleswheretitlelike Computer 使用字符串比较符 续 selectau lname au fname phone au idfromauthorswhereau idlike 72 从表authors中 查找au id类似于 72 格式的作者的编号 姓 名 电话 从表authors中 查找au id的第2个数字是7的作者的编号 姓 名 电话 selectau lname au fname phone au idfromauthorswhereau idlike 7 使用字符串比较符 续 selectau lname au fname phone au idfromauthorswhereau idlike 72 345 从表authors中 查找au id只能是 723 724 725 格式的作者的编号 姓 名 电话 从表authors中 查找au id的第1个数字是5 9中的一个 第2个数字是1 4中的一个的作者的编号 姓 名 电话 selectau lname au fname phone au idfromauthorswhereau idlike 5 9 1 4 使用字符串比较符 续 从表authors中 查找au id的第1个数字不是5 9中的一个 第2个数字不是1 4中的一个的作者的编号 姓 名 电话 selectau lname au fname phone au idfromauthorswhereau idlike 5 9 1 4 使用字符串比较符 续 使用LIKE时 请注意查询条件中的字符串内 所有的字符都有效 包括开始和结尾的空格LIKE只可用于下列数据类型 char nchar varchar nvarchar和datetime所有通配符都只有在LIKE子句中才有意义 否则通配符会被当作普通字符处理 select fromauthorswhereau fname D 随堂练习 写出下面各种情况的搜索条件 1 搜索以字母Mc开头的所有字符串 2 搜索以字母inger结尾的所有字符串 3 搜索在任何位置包含字母en的所有字符串 4 搜索以字符串inger结尾 以从M到Z的任何单个字母开头的所有名称 使用逻辑运算符 用逻辑操作符AND OR和NOT来连接一系列的表达式 及优化查询过程 使用逻辑运算符时 遵循下列原则使用AND返回满足所有条件的行使用OR返回满足任一条件的行使用NOT返回不满足条件的行搜索条件的顺序先求NOT表达式的值 然后是AND 最后是OR当表达式中所有的操作符优先级相同时 求值顺序由左到右 使用逻辑运算符 续 USEnorthwindSELECTproductid productname supplierid unitpriceFROMproductsGO 示例 查找产品名以 T 开头 或产品标识为46的产品 且产品的价格大于16美元 WHERE productnameLIKE T ORproductid 46 AND unitprice 16 00 检索一定范围内的值 使用BETWEEN来查询在一定范围内的值 使用BETWEEN时 注意SQLServer返回的结果集中 包含范围内的边缘值尽量使用BETWEEN 而不用AND和比较操作符表示的表达式 xANDxAND y 要返回不在指定区域的行时 使用NOTBETWEEN 检索一定范围内的值 续 从products表中查询所有产品的产品名字和单价 且此产品的单价在10美元到20美元之间 检索一定范围内的值 续 selecttitle pricefromtitleswherepricebetween15and20 查询书价在15到20元之间的图书 selecttitle pricefromtitleswhereprice 15andprice 20 selecttitle pricefromtitleswherepricenotbetween15and20等价于 检索一定范围内的值 续 使用值列表作为搜索条件 使用IN语句指定一个值的列表作为查询条件 使用IN时 注意使用IN搜索条件和使用由OR操作符连起来的一系列比较操作符 其效果是等价的在搜索条件中不能包含NULL值 这将返回不可预测的结果集使用NOTIN搜索条件来返回值不在指定列表中的行 使用NOT会降低数据检索速度 使用值列表作为搜索条件 续 从表suppliers中搜索位于日本和意大利的所有公司及所在地 使用值列表作为搜索条件 续 select fromauthorswherestatein CA KS MI IN 从表authors中搜索state位于 CA KS MI IN 的所有作者的信息 select fromauthorswherestate CA orstate KS orstate MI orstate IN 检索未知值 输入数据时 没有指定一个字段的值 且此字段没有定义缺省的值 称此字段含有空值 NULL 使用ISNULL搜索条件来查询某指定列没任何信息的行 需要返回含有空值的行时 注意把空值和任何表达式比较都会失败 因为空值不等于任何表达式使用ISNOTNULL来查询指定列中非空的行 检索未知值 续 查询表suppliers中字段fax含有空值的所有公司 SQLServer数据查询 简单的数据查询聚合函数与分组查询联接查询子查询数据管理高级应用 使用聚合函数 常见的聚合函数及其描述 使用聚合函数 对包含空值的字段使用聚合函数时 应注意SQLServer的聚合函数 COUNT 除外 将忽略字段中的空值COUNT 将计算所有的行 即使每个字段都含有空值 使用聚合函数 查询titles表中类型是 popular comp 的书的平均价格 查询publishers表中供应商的数量 查询publishers表中供应商所在州的数量 selectavg price as 平均价格 fromtitleswheretype popular comp selectcount as 供应商数量 frompublishers selectcount state as 供应商所在州的数量 frompublishers 使用聚合函数 续 查询titles表中书的类型有几种 selectcount type fromtitles selectcount distincttype fromtitles 说明 允许count sum avg 和distinct一起处理列或表达式中不同的值 分组的基础知识 使用GROUPBY子句联合使用GROUPBY子句和HAVING子句 分组的基础知识 简单分组按书的种类分类 求出各类书籍的数量 Selecttype count title as 数量 fromtitlesGroupbytype 使用GROUPBY子句 联合使用聚合函数和GROUPBY子句 能够把表中的记录分组 并对组中数据进行汇总 使用GROUPBY子句时 应注意对于指定的一组 SQLServer只生成一条记录 不返回详细信息不要对可包含空值的字段使用GROUPBY子句 因为空值也将被当作一组 使用GROUPBY子句 SELECTproductid SUM quantity AStotal quantityFROMorderhistGROUPBYproductidGO 使用GROUPBY子句 USEnorthwindSELECTproductid SUM quantity AStotal quantityFROMorderhistWHEREproductid 2GROUPBYproductidGO 使用GROUPBY子句 USEpubsSELECTroyalty AVG price 2 ASAveragePriceFROMpubs dbo titlesGROUPBYroyaltyGO 示例 对包含空值的字段使用GROUPBY子句 空值也将被当作一组 使用GROUPBY子句 按书的种类分类 求出3种类型书籍 business mod cook trad cook 的价格总和 平均价格以及各类书籍的数量 selecttype sum price astotal price avg price asavg price count title asquantityfromtitleswheretypein business mod cook trad cook groupbytype 使用GROUPBY子句 SELECT后面每一列 除了出现在统计函数中的列以外 都必须包含在在GROUPBY子句中 GROUPBY子句不支持对列分配的假名 也不支持任何使用统计函数的集合列 selectpub id type sum price astotal price avg price asavg price count title asquantityfromtitleswheretypein business mod cook trad cook groupbytype 使用GROUPBY子句 根据多列组合进行分组统计计算按书的类型分类 查询每个出版商销售的书的平均价和总销售额 selectpub id type sum price astotal price avg price asavg price count title asquantityfromtitlesgroupbytype pub id 随堂练习 使用学生 课程数据库完成下面练习 1 计算选2号课程的学生平均成绩 2 求各个课程号及相应的选课人数 3 求每门课程的平均成绩 4 求有成绩的每个学生的平均成绩 5 求平均成绩大于86分的学生学号及平均成绩 联合使用GROUPBY子句和HAVING子句 在分组的同时 对字段或表达式指定搜索条件使用HAVING子句时 应注意只在使用GROUPBY子句的同时 使用HAVING子句来限制分组可以引用任何出现在选择列表中的字段 联合使用GROUPBY子句和HAVING子句 续 SELECTproductid SUM quantity AStotal quantityFROMorderhistGROUPBYproductidHAVINGSUM quantity 30GO 列出表orderhist中销售量多于30的每种产品 selecttype avg price fromtitlesgroupbytypehavingavg price 15 selecttype avg price fromtitleswhereavg price 15groupbytype 联合使用GROUPBY子句和HAVING子句 续 按书的类型分类 查询平均价大于15的书的类型 平均价 联合使用GROUPBY子句和HAVING子句 续 2 从titles表中 查询得到至少出版六本书的出版商及其总销售额 3 从titles表中 查询标识号大于0800 支付的总预付款已超过 15 000且销售书籍的平均价格小于 20的出版商 例题 1 从titles表中 查询得到本年度的总销售额 ytd sales 超过 40 000的出版商 SQLServer数据查询 简单的数据查询聚合函数与分组查询联接查询子查询数据管理高级应用 使用表的别名 两种方式给字段起名使用多表联接时 必须慎重地指定字段名 任何数据库对象名称包含四个标识 服务器名 数据库名 主人名称 对象名 SELECT FROMserver database schema tableAStable alias SELECT FROMserver database schema tabletable alias 组合多个表中的数据 联接概述使用内联接使用外联接使用交叉联接联接两个以上的表自联接 Self Join 联接概述 从多个表中选择指定的字段关键字JOIN指定要联接的表 以及这些表联接的方式关键字ON指定联接条件查询两个或多个表并生成单个结果集所有联接的表必须共同拥有某些字段 这些字段必须有相同的或兼容的数据类型如果联接的表有相同字段 则引用这些字段时必须指定表名 联接概述 续 连接类型交叉连接 CrossJoin 不太用 内连接 Inner Join 最常用 外连接 左外连接 Left Outer Join右外连接 Right Outer Join完全连接 Full Outer Join自连接 SelfJoin 使用内联接 内联接通过比较被联接的表所共同拥有的字段 把多个表联接起来语法SELECT列名列表FROM表名1 INNER JOIN表名2ON表名1 列名表名2 列名注意INNER可以省略 包括 等 使用内联接 续 USEjoindbSELECTbuyer name sales buyer id qtyFROMbuyersINNERJOINsalesONbuyers buyer id sales buyer idGO sales 列出购买产品的顾客信息 使用内联接 续 USEjoindbSELECTbuyer name sales buyer id qtyFROMbuyers salesWHEREbuyers buyer id sales buyer idGO USEjoindbSELECTbuyer name sales buyer id qtyFROMbuyersINNERJOINsalesONbuyers buyer id sales buyer idGO 示例 比较之一 使用内联接 续 USEjoindbSELECTbuyer name sales buyer id qtyFROMbuyersINNERJOINsalesONbuyers buyer id sales buyer idGO USEjoindbSELECT FROMbuyersINNERJOINsalesONbuyers buyer id sales buyer idGO 比较之二 随堂练习 1 查询学生 李敏勇 选修的课程的平均分 2 查询选修 数学 课程的学生的平均分 3 查询成绩大于90分的学生的姓名 4 查询没有一门考试科目成绩低于85分的学生的姓名 5 查询所有学生的选课号和成绩 使用外联接 左外联接可以从两个表中返回符合联接条件的记录 同时也将返回左边表中不符合条件的记录使用左外联接时 请注意不满足联接条件的记录将显示空值左联接可以显示左表中所有记录可以把LEFTOUTERJOIN简写为LEFTJOIN 使用外联接 左联接 USEjoindbSELECTbuyer name sales buyer id qtyFROMbuyersLEFTOUTERJOINsalesONbuyers buyer id sales buyer idGO 查询所有购买者的购买情况 使用外联接 右联接 右外联接可以从两个表中返回符合联接条件的记录 同时也将返回右边表中不符合条件的记录使用右外联接时 请注意不满足联接条件的记录将显示空值右联接可以显示右表中所有记录可以把RIGHTOUTERJOIN简写为RIGHTJOIN 使用外联接 右联接 USEjoindbSELECTbuyer name sales buyer id qtyFROMbuyersRIGHTOUTERJOINsalesONbuyers buyer id sales buyer idGO 示例 联接两个以上的表 联接任意数目的表都有可能 通过使用共同拥有的字段 任何一个表都可以和其他表联接为什么要联接两个以上的表使用多重联接可以从多个表中得到彼此相关的信息至少有一个表具有外键 把要联接的表按一定关系联系起来组合键中的每一字段都必须由一个相应的ON子句引用可以使用WHERE子句限制结果集所返回的记录 联接两个以上的表 续 SELECTbuyer name prod name qtyFROMbuyersINNERJOINsalesONbuyers buyer id sales buyer idINNERJOINproduceONsales prod id produce prod idGO produce buyers sales 什么顾客购买了什么产品 多少数量 随堂练习 1 查找顾客购买产品的数量 显示顾客名 产品号 数量查找所有顾客购买产品的数量 显示顾客名 产品号 数量查找所有产品的购买情况 显示产品名 数量查找所有顾客对所有产品的购买情况 显示顾客名 产品名 数量 随堂练习 2 使用Pubs数据库 查找出版过书籍的出版商名及出版的书籍名称查找所有出版商及其出版的书籍名查询具有作者和出版商的书籍的名称 及其该书的作者人数和该书的出版商 查询所有书籍的作者人数 出版商名及书籍名称 使用交叉联接 交叉联接将从被联接的表中返回所有可能的记录组合为什么使用交叉联接在规范化的数据库中 很少使用交叉联接可以为数据库生成测试数据为清单及企业模板生成所有可能的组合数据 使用交叉联接 续 示例 自联接 Self Join 使用自联接 可以查询一个表中各记录之间的关系使用自联接时 应注意引用表的两份副本时 必须使用表的别名生成自联接时 表中每一行都和自身比较一下 并生成重复的记录 使用WHERE子句来消除这些重复记录 自联接 Self Join 续 USEjoindbSELECTa buyer idASbuyer1 a prod id b buyer idASbuyer2FROMsalesASaJOINsalesASbONa prod id b prod idWHEREa buyer id b buyer idGO salesb salesa 显示拥有相同产品的顾客列表 随堂练习 查询Employees表中 每一位员工的领导 需要显示员工名和领导名 selectR S fromRinnerjoinSonR C S C R S selectR S fromR SwhereR C S C 内连接 内连接与几种外连接的对比 select fromRleftouterjoinSonR C S C selectR A R B S fromRrightouterjoinSonR C S C 左外连接 右外连接 R S selectR S fromRfullouterjoinSonR C S C 全外连接 R S 合并多个结果集 使用UNION操作符可以将多个查询产生的结果集合并成一个结果集每一个查询必须有类似的数据 相同的字段数目 并且在选择列表中字段顺序相同UNION与UNIONALL的区别UNION在进行表联接后会筛选掉重复的记录 所以在表链接后会对所产生的结果集进行排序运算 删除重复的记录再返回结果 UNIONALL只是简单的将两个结果合并后就返回 如果需要排序 则要在最后一个有UNION的子句中使用ORDERBY 以指定排序方式 合并多个结果集 续 列出系号为12的所有人员 包括老师和学生 的姓名和出生日期 合并多个结果集 续 SELECT学生姓名AS 姓名 出生日期FROM学生信息表where系号 12UnionSELECT教师姓名 出生日期FROM教师信息表where系号 12GO 示例 合并多个结果集 续 SELECT学生姓名AS 姓名 出生日期FROM学生信息表where系号 12UnionallSELECT教师姓名 出生日期FROM教师信息表where系号 12GO 示例 合并多个结果集 续 UNION和JOIN的区别使用操作符UNION 要求所引用的表必须具有相似的数据类型 相同的字段数 每个查询中的选择列表必须具有相同的顺序 使用操作符JOIN 只要求联接的表共同拥有某些字段 用UNION分解复杂的查询会提高查询速度 而JOIN联接表越多 查询速度越慢 随堂练习 查询每一种类的产品个数 products categories表 查询 AroundtheHorn 客户购买的产品的名称及购买时间 orders orderdetails products表 查询被客户订购数量最多的前10种产品的名称及订购数量 包括并列项 products orderdetails表 SQLServer数据查询 简单的数据查询聚合函数与分组查询联接查询子查询数据管理高级应用 子查询 子查询介绍嵌套子查询相关子查询使用EXISTS操作符 子查询介绍 定义一个查询语句嵌套在DML语句中 则该语句被称为子查询 何时使用当查询需要多个步骤才能完成时 可以使用子查询分类嵌套子查询相关子查询 子查询介绍 续 子查询的使用方法子查询是一个SELECT语句 要用括号括起来 不能使用子查询检索数据类型为text或image的字段 子查询可以再包含子查询 至多可嵌套32层 嵌套子查询 嵌套子查询执行一次子查询并将结果值代入外部查询中进行评估 内层子查询执行完毕后 再执行外层查询 嵌套子查询 之一 把子查询作为派生表可以用子查询产生一个派生的表 用于代替FROM子句中的表 USEnorthwindselectT productnamefrom selectproductname unitpricefromproducts asTwhereT productname chai GO 嵌套子查询 之二 举例 查询产品名称为 chai 的产品价格与所有产品的平均价格的差价 USEnorthwindselectproductname unitprice selectavg unitprice fromproducts as diff fromproductswhereproductname chai GO 在Transact SQL中 所有使用表达式的地方 都可以使用子查询代替当子查询被用作表达式时 子查询可以认为是被当作一个表达式处理并计算 嵌套子查询 之三 子查询可以用于提供一系列的值 这些值可以出现在WHERE子句中的IN关键字中 USEnorthwindselectlastname firstnamefromemployeeswhereemployeeidin selectemployeeidfromorderswhereorders orderdate 1998 1 1 GO 举例 查询1998年1月1日 签订单的雇员的姓名