第五章TransactSQL数据查询与更新.ppt

《第五章TransactSQL数据查询与更新.ppt》由会员分享，可在线阅读，更多相关《第五章TransactSQL数据查询与更新.ppt（46页珍藏版）》请在三一文库上搜索。

1、第五章 Transact-SQL数据查询与更新 假如有人提出这样的一些需求,我们如何处理呢 1、我希望将学生基本信息中的姓名,家庭住址,联 系电话单独放入一个新表中保存 2、我想统计不同政治面貌的人数 3、后勤处想知道表中有几种少数民族，方便安排 伙食. 4、我有个熟人叫马某某，是男同学，名字是两个 字的，帮我查查 1 第五章 Transact-SQL数据查询与更新 5.1 简单数据查询 5.2 分组查询 5.3 联接查询 5.4 子查询 5.5 联合查询 5.8 排名函数 5.9 数据更新 5.10 事务 2 学生基本信息表 第五章 Transact-SQL数据查询与更新 学生基本信息表 3

2、 成 绩 表 一 个 学 生 各 门 课 程 各 门 课 程 的 成 绩 4 系 部 表 课程信息表 5 5.1 简单数据查询 SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC COMPUTE 子句 6 5.1 简单数据查询 SELECT各子句执行顺序及功能简介： （1） SELECT子句：用于指定输出列（字段），也可

3、求值输出 。 （2） INTO子句：将检索结果存储到新表或视图中。 （3） FROM子句：用于指定检索数据的源表或视图。 （4） WHERE子句：指定选择行（记录）的过滤条件。 （5） GROUP BY：子句对检索到记录进行分组。 （6） HAVING子句：系指定记录辅助过滤条件，从分组的结果 中筛选行，即选取满足条件的那些组。 （7） ORDER BY子句：是对检索到数据进行排序；ASC 和 DESC 关键字用于指定行是按升序还是按降序排序，默认升序。 7 5.1 简单数据查询 例：使用 SELECT 语句查找学生基本信息表中回族同学 的姓名和家庭住址，按出生日期降序排列。 SELECT 姓

4、名,家庭住址 FROM 学生基本信息表 WHERE 族别=回族 ORDER BY 出生日期 DESC Select - from - where - 8 5.1 简单数据查询 5-1 SELECT子句和FROM子句 1、语法 SELECT ALL | DISTINCT TOP n PERCENT WITH TIES l ALL关键字:为默认设置，用于指定查询结果集的所有行， 包括重复行。 l DISTINCT: 用于删除结果集中重复的行。 l TOP n PERCENT : 指定只返回查询结果集中的前n行。 如果加了PERCENT，则表示只返回查询结果集中的前n%行。 WITH TIES 用于

5、指定从基本结果集中返回附加的行。 9 5.1 简单数据查询 2、选择列 （1）选择所有列* （2）选择指定列，各列之间用逗号分隔。 3、在查询结果集中加入常量,字符“-”将名称的两个部分分开。 Select 课程编号+-+课程名称 from 课程信息表 (说明:字段间用加号表示将字段值合并为一列,-也可改为其他） 4、为选择列指定别名 列表达式 as 列别名 或 列表达式 列别名 或 列别名=列表达式 Select avg(成绩) as 平均成绩 from 成绩表 10 4、选择列表中的计算表达式 （1）对数字列或常量使用算术运算或函数进行的运算。 Select MAX(成绩） as 最高分

6、from 成绩表 Select sum(奖金) as 奖金总和 from 职工工资表 ( 2 ) Case 语句 USE TEST SELECT 学号, 等级= CASE WHEN 成绩=90 THEN 优 WHEN 成绩=80 THEN 良 WHEN 成绩=70 THEN 中 END FROM 成绩 5.1 简单数据查询 11 5.1 简单数据查询 （3）数据类型函数 Select 学号, 课程编号+cast (成绩 as char(8) from 成绩表 6、使用distinct消除重复行 Select distinct 族别 from 学生基本信息表 7、使用top和percent限制结

7、果集 Select top 3 学号，姓名 from 学生 8、 从学生基本信息表中只显示10%的信息 select top 10 percent * from 学生基本信息表 9、显示成绩表1%行信息，要求附加行,按学号降序排列 Select top 1 percent with ties * from 成绩表 order by 学号 desc 12 关于select和from语句的课堂作业 1. 显示学生基本信息表中学生的姓名，家庭住址 2. 显示学生基本信息表的后10个学号的信息。 3. 将学生的成绩上涨10%显示，该字段设为“期望成绩” 4. 将学生基本信息表中的学生年龄降序排列 SE

8、LECT 姓名，家庭住址 FROM 学生基本信息表 SELECT TOP 10 * FROM 学生基本信息表 ORDER BY 学号 DESC SELECT 学号,成绩*1.1 AS 期望成绩 FROM 成绩表 SELECT 姓名,DATEDIFF(YEAR,出生日期,GETDATE() AS 年龄 FROM 学生基本信息表 ORDER BY 年龄 DESC 13 5.1 简单数据查询 2 WHERE子句 1、比较运算符(,2 2、范围 (between 和not between) Select 学号 from 成绩表 where 成绩 not between 60 and 80 3、关键字i

9、n 与not in Select * from 学生 where 学分 not in(2,8,12) 4、模式匹配（like和not like) _表示任何单个字符，%表示任意多个字符 在指定范围内的任何单个字符。 不在指定范围内的任何单个字符 14 5.1 简单数据查询 Like通配符的运用 搜索以张开头的姓名 搜索有个“丽”字的姓名 搜索姓名最后一个字是勇 搜索以9结尾的两位数学号 搜索姓张,王,李,赵的姓名 搜索除了张,王,李,赵的姓名 搜索以m开头,第二字母不是c的 所有名称 like 张% like %丽% like %勇 like _9 like 张王李赵% like 张王李赵%

10、like mc% 15 5.1 简单数据查询 5、空值(is null和is not null) 例：查询testdb库的成绩表中补考成绩空值情况 Select * from 成绩 where 补考成绩 is null 6、所有记录（=all,all,300) or (区域=西北 and 学号 like03%) 16 select * from 学生基本信息表 where 姓名 like 赵钱孙李% select * from 学生基本信息表 where 性别=男 and 姓名 like 马_ 关于where语句的课堂作业 1、 显示学生中姓名赵钱孙李的学生信息 2、 查询马(二字组成的姓名)

11、的男同学信息。 17 5.2 分组查询 9-3-1 常用统计统计 函数 在SELECT 语句中，可以使用统计函数、GROUP BY 子句和COMPUTE BY 子句对查询结果进行分类汇总 18 5.2 分组查询 1、显示学生基本信息表最小出生日期 Select min(出生日期) from 学生基本信息表 2、查询工资表中最高的基本工资信息 Select max(基本工资）from 工资表 3、显示工资表中平均基本工资信息 Select avg(基本工资） from 工资表 4、统计学生基本信息表中“汉族”学生人数 Select count(*) from 学生基本信息表 where 族别=汉

12、族 5、查询工资表中奖金的总和 Select sum（奖金) from 工资表 19 5.2 分组查询 2、 GROUP BY 子句的语法格式为： GROUP BY ALL group_by_expression ,.n HAVING search_condition 例：显示不同政治面貌的人数 SELECT 政治面貌, COUNT(*) AS 人数 FROM 学生表 GROUP BY 政治面貌 思考：显示不同族别的人数 例：显示成绩表每个学生的成绩总分 SELECT 学号,SUM(成绩) AS 总分 FROM 成绩表 GROUP BY 学号 思考：统计职工档案表不同职称的工资平均情况 20

13、l HAVING子句和WHERE子句很相似，均用于设置 数据筛选条件。 l WHERE子句对分组前的数据进行筛选，条件中不 能包含聚合函数； l HAVING子句对分组过后的数据进行筛选，条件中 经常包含聚合函数。 l HAVING子句必须和GROUP BY子句联合使用 例：查询学生基本信息表中“回族”，“蒙古”两个民族 的学生人数 Select 族别,count(*) as 人数 from 学生基本信息表 group by 族别 having 族别 in(回族,蒙古) 5.2 分组查询 21 5.2 分组查询 提高： 统计成绩表中每门课的及格人数 SELECT 课程编号, COUNT(*)

14、AS 人数 FROM 成绩表 WHERE (成绩 60) GROUP BY 课程编号 提高：显示总成绩大于等于520分以上的学生情况。 SELECT 学号,SUM(成绩) FROM 成绩表 GROUP BY学号 HAVING SUM(成绩)=520 22 5.2 分组查询 3、 使用COMPUTE BY汇总 1、而COMPUTE子句使用户得以用同一SELECT 语句既查看 明细行，又查看总计行。 2、COMPUTE BY 子句使用户得以用同一SELECT 语句既查 看明细行，又查看分类总行； 3、COMPUTE 子句需要下列信息： 可选的 BY 关键字，该关键字可对一列计算指定的行统计 ； 行

15、统计函数名称：例如，SUM、AVG、MIN、MAX 或 COUNT ； 要对其执行行统计函数的列。 23 5.2 分组查询 例：查询所有职工的工资总和，并显示明细记录。 不显示明细记录： SELECT SUM (工资) FROM 职工档案表 SELECT * FROM 职工档案表 COMPUTE SUM(工资 ) 例：按学号显示学生成绩，并计算每人的平均分.并显示 每个分组的明细记录内容。 不显示明细记录： SELECT 学号,AVG(成绩) FROM 成绩表 GROUP BY 学号 SELECT * FROM 成绩表 ORDER BY 学号 COMPUTE AVG(成绩) BY 学号 24

16、5.3 联接查询 * 联接，可以根据各个表之间的逻辑关系从两个或多个表 中检索数据。联接表示如何使用一个表中的数据来选择另 一个表中的行。 * 联接条件通过以下方法定义两个表在查询中的关联方式： （1） 指定每个表中要用于联接的列。典型的联接条件在一个表中 指定外键，在另一个表中指定与其关联的键。 （2） 指定比较各列的值时要使用的逻辑运算符（=、80) as 成绩二 select 姓名,成绩二.平均分 from 学生表 inner join on 成绩二.学号=学生表.学号 select 姓名,成绩二.平均分 from 学生表 inner join (select 学号,avg(成绩) as

17、 平均分 from 成绩表 group by 学号) as 成绩二 on 成绩二.学号=学生表.学号 WHERE 平均分8032 5.3 联接查询 (3)使用等号以外的运算符的联接 select * from 课程信息表,成绩表 where 课程信息表.课程编号a2.学号 思考：查询课程信息表中同一门课程任课教师情况 Select a1.* from 课程信息表 as a1 inner join 课程信息表 as a2 on a1.课程名称=a2.课程名称 and a1.课程编号a2.课程编号 34 5.3 外联接例题 学号成绩 00189 00278 00380 00485 00560 学号

18、姓名政治面貌 001张小军党员 002陈丽团员 005曾巧玲团员 006曹阳群众 009赵强团员 成绩表 档案表 查询学生的学号，姓名，成绩，通过三种联接方式，请 说出左联接，右联接，完整外部联接的结果 35 5.3 外联接例题 例：用DAN表和CJ表分别左联接、右联接、完整联接。 左联接 SELECT DAN.学号,姓名,成绩 FROM DAN LEFT OUTER JOIN CJ ON DAN.学号=CJ.学号 右联接 SELECT CJ.学号,姓名,成绩 FROM DAN RIGHT OUTER JOIN CJ ON DAN.学号=CJ.学号 36 5.3 外联接例题 完整外部联接 SE

19、LECT DAN.学号,姓名,成绩 FROM DAN FULL OUTER JOIN CJ ON DAN.学号=CJ.学号 37 5.3 联接查询 5、 多表联联接 虽然每个联接规范只联接两个表，但 FROM 子句可包含多个联接 规范。这样一个查询可以联接若干个表。 例:利用课程信息表,成绩表, 学生基本信息表显示学生的 学号,姓名,课程名称,成绩 select 成绩表.学号,姓名, 课程名称,成绩 from 课程信息表 inner join 成绩表 on 课程信息表.课程编号 = 成绩表.课程编号 inner join 学生基本信息表 on 成绩表.学号=学生基本信息表.学号 38 5.4

20、子查询 4、 子查询查询 基础础 子查询是一个 SELECT 查询，它返回单个值且嵌套在 SELECT、 INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使 用表达式的地方都可以使用子查询 注意事项: 1.通过比较运算符引入的子查询的选择列表只能包括一个表达式或 列名称. 2.使用外部查询的WHERE子句包括某个列名,则该子句必须与子查询 选择列表中的该列在联接上兼容 3.由于必须返回单个值,所以由于修改的比较运算符引入的子查询 不能包括GROUP BY 和HAVING子句 4.包括GROUP BY 的子查询不能使用DISTINCT关键字 5.只有同时指定了TOP,才可以

21、指定ORDER BY 6.约定通过EXISTS引入的子查询的选择列由*组成,不使用单个列名39 5.4 子查询 例：用子查询的形式，显示学生的姓名和平均分。 Select 学号,姓名,(select avg(成绩) from 成绩表 where 成绩表.学号=学生基本信息表.学号) as 平均成绩 from 学生基本信息表 40 5.4 子查询 2 EXIST与IN子查询查询 例：使用子查询从学生基本信息表中显示年龄比所有回族 学生都大的学生姓名 ( select 学号 from 成绩表 where 成绩75 ) 42 5.4 子查询 l一个子查询能够返回一个由行和列构成的数 据集，我们可以将

22、这个数据集再看作一个表 来处理，这个由子查询得出的新表就是我们 说的“派生表” l派生表有利于提高查询速度、简化操作 l派生表不是存储在数据库中的对象，它只在 查询期间有效。 43 5.4 子查询 select 学生基本信息表.学号,姓名,成绩二.总分 from 学生基本信息表 inner join on 学生基本信息表.学号=成绩二.学号 3、派生表 例：显示学生的姓名和相应的成绩总分 (select 学号,sum(成绩) as 总分 from 成绩表 group by 学号) as 成绩二 44 5.5 联合查询 UNION关键字可以将两个或更多相互独立的SELECT语 句的查询结果合并成

23、一个集合，即执行联合查询。 联合查询要求合并的几个数据集之间应该具有相同的字段 数目和类型。 例：显示学生基本信息表中的姓名和籍贯以及课程信息表 中的课程编号和课程名称。 select 姓名,籍贯 from 学生基本信息表 union select 课程编号,课程名称 from 课程信息表 45 -显示“罗小花”的各个科目成绩和平均分 select 课程名称,成绩 from 课程信息表 as kc inner join 成绩表as cj on kc.课程编号=cj.课程编号 where cj.学号=(select 学号from 学生基本信息表 where 姓名=罗小花) union select 课程名称=平均分,avg(成绩) from 成绩表where 学 号=(select 学号from 学生基本信息表where 姓名=罗小花) 5.5 联合查询提高 46