TransactSQL数据查询与更新.ppt

1,第五章 Transact-SQL数据查询与更新,假如有人提出这样的一些需求,我们如何处理呢,1、我希望将学生基本信息中的姓名,家庭住址,联系电话单独放入一个新表中保存 2、我想统计不同政治面貌的人数 3、后勤处想知道表中有几种少数民族，方便安排伙食. 4、我有个熟人叫马某某，是男同学，名字是两个字的，帮我查查,2,第五章 Transact-SQL数据查询与更新,5.1 简单数据查询 5.2 分组查询 5.3 联接查询 5.4 子查询 5.5 联合查询 5.8 排名函数 5.9 数据更新 5.10 事务,3,学生基本信息表,第五章 Transact-SQL数据查询与更新,学生基本信息表,4,成绩表,一个学生,各门课程,各门课程的成绩,5,系部表,课程信息表,6,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 子句,7,5.1 简单数据查询,SELECT各子句执行顺序及功能简介： （1） SELECT子句：用于指定输出列（字段），也可求值输出。 （2） INTO子句：将检索结果存储到新表或视图中。 （3） FROM子句：用于指定检索数据的源表或视图。 （4） WHERE子句：指定选择行（记录）的过滤条件。 （5） GROUP BY：子句对检索到记录进行分组。 （6） HAVING子句：系指定记录辅助过滤条件，从分组的结果中筛选行，即选取满足条件的那些组。 （7） ORDER BY子句：是对检索到数据进行排序；ASC 和 DESC 关键字用于指定行是按升序还是按降序排序，默认升序。,8,5.1 简单数据查询,例：使用 SELECT 语句查找学生基本信息表中回族同学的姓名和家庭住址，按出生日期降序排列。 SELECT 姓名,家庭住址 FROM 学生基本信息表 WHERE 族别=回族 ORDER BY 出生日期 DESC,Select - from - where -,9,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 用于指定从基本结果集中返回附加的行。,10,5.1 简单数据查询,2、选择列 （1）选择所有列* （2）选择指定列，各列之间用逗号分隔。 3、在查询结果集中加入常量,字符“-”将名称的两个部分分开。 Select 课程编号+-+课程名称 from 课程信息表 (说明:字段间用加号表示将字段值合并为一列,-也可改为其他） 4、为选择列指定别名 列表达式 as 列别名 或 列表达式 列别名 或 列别名=列表达式 Select avg(成绩) as 平均成绩 from 成绩表,11,4、选择列表中的计算表达式 （1）对数字列或常量使用算术运算或函数进行的运算。 Select MAX(成绩） as 最高分 from 成绩表 Select sum(奖金) as 奖金总和 from 职工工资表 ( 2 ) Case 语句 USE TEST SELECT 学号, 等级= CASE WHEN 成绩=90 THEN 优 WHEN 成绩=80 THEN 良 WHEN 成绩=70 THEN 中 END FROM 成绩,5.1 简单数据查询,12,5.1 简单数据查询,（3）数据类型函数 Select 学号, 课程编号+cast (成绩 as char(8) from 成绩表 6、使用distinct消除重复行 Select distinct 族别 from 学生基本信息表 7、使用top和percent限制结果集 Select top 3 学号，姓名 from 学生 8、 从学生基本信息表中只显示10%的信息 select top 10 percent * from 学生基本信息表 9、显示成绩表1%行信息，要求附加行,按学号降序排列 Select top 1 percent with ties * from 成绩表 order by 学号 desc,13,关于select和from语句的课堂作业,1. 显示学生基本信息表中学生的姓名，家庭住址 2. 显示学生基本信息表的后10个学号的信息。 将学生的成绩上涨10%显示，该字段设为“期望成绩” 将学生基本信息表中的学生年龄降序排列,SELECT 姓名，家庭住址 FROM 学生基本信息表 SELECT TOP 10 * FROM 学生基本信息表 ORDER BY 学号 DESC SELECT 学号,成绩*1.1 AS 期望成绩 FROM 成绩表 SELECT 姓名,DATEDIFF(YEAR,出生日期,GETDATE() AS 年龄 FROM 学生基本信息表 ORDER BY 年龄 DESC,14,5.1 简单数据查询,2 WHERE子句 1、比较运算符(,，=等等) Select * from 课程信息表 where 学分2 2、范围 (between 和not between) Select 学号 from 成绩表 where 成绩 not between 60 and 80 3、关键字in 与not in Select * from 学生 where 学分 not in(2,8,12) 4、模式匹配（like和not like) _表示任何单个字符，%表示任意多个字符 在指定范围内的任何单个字符。 不在指定范围内的任何单个字符,15,5.1 简单数据查询,Like通配符的运用 搜索以张开头的姓名 搜索有个“丽”字的姓名 搜索姓名最后一个字是勇 搜索以9结尾的两位数学号 搜索姓张,王,李,赵的姓名 搜索除了张,王,李,赵的姓名 搜索以m开头,第二字母不是c的所有名称,like 张% like %丽% like %勇 like _9 like 张王李赵% like 张王李赵% like mc%,16,5.1 简单数据查询,5、空值(is null和is not null) 例：查询testdb库的成绩表中补考成绩空值情况 Select * from 成绩 where 补考成绩 is null 6、所有记录（=all,all,300) or (区域=西北 and 学号 like03%),17,select * from 学生基本信息表 where 姓名 like 赵钱孙李% select * from 学生基本信息表 where 性别=男 and 姓名 like 马_,关于where语句的课堂作业,1、 显示学生中姓名赵钱孙李的学生信息 2、 查询马(二字组成的姓名)的男同学信息。,18,5.2 分组查询,9-3-1 常用统计函数,在SELECT 语句中，可以使用统计函数、GROUP BY 子句和COMPUTE BY 子句对查询结果进行分类汇总,19,5.2 分组查询,1、显示学生基本信息表最小出生日期 Select min(出生日期) from 学生基本信息表 2、查询工资表中最高的基本工资信息 Select max(基本工资）from 工资表 3、显示工资表中平均基本工资信息 Select avg(基本工资） from 工资表 4、统计学生基本信息表中“汉族”学生人数 Select count(*) from 学生基本信息表 where 族别=汉族 5、查询工资表中奖金的总和 Select sum（奖金) from 工资表,20,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 学号 思考：统计职工档案表不同职称的工资平均情况,21,HAVING子句和WHERE子句很相似，均用于设置数据筛选条件。 WHERE子句对分组前的数据进行筛选，条件中不能包含聚合函数； HAVING子句对分组过后的数据进行筛选，条件中经常包含聚合函数。 HAVING子句必须和GROUP BY子句联合使用 例：查询学生基本信息表中“回族”，“蒙古”两个民族的学生人数 Select 族别,count(*) as 人数 from 学生基本信息表 group by 族别 having 族别 in(回族,蒙古),5.2 分组查询,22,5.2 分组查询,提高： 统计成绩表中每门课的及格人数 SELECT 课程编号, COUNT(*) AS 人数 FROM 成绩表 WHERE (成绩 60) GROUP BY 课程编号 提高：显示总成绩大于等于520分以上的学生情况。 SELECT 学号,SUM(成绩) FROM 成绩表 GROUP BY学号 HAVING SUM(成绩)=520,23,5.2 分组查询,3、 使用COMPUTE BY汇总 1、而COMPUTE子句使用户得以用同一SELECT 语句既查看明细行，又查看总计行。 2、COMPUTE BY 子句使用户得以用同一SELECT 语句既查看明细行，又查看分类总行； 3、COMPUTE 子句需要下列信息： 可选的 BY 关键字，该关键字可对一列计算指定的行统计； 行统计函数名称：例如，SUM、AVG、MIN、MAX 或 COUNT； 要对其执行行统计函数的列。,24,5.2 分组查询,例：查询所有职工的工资总和，并显示明细记录。 不显示明细记录： SELECT SUM (工资) FROM 职工档案表 SELECT * FROM 职工档案表 COMPUTE SUM(工资) 例：按学号显示学生成绩，并计算每人的平均分.并显示每个分组的明细记录内容。 不显示明细记录： SELECT 学号,AVG(成绩) FROM 成绩表 GROUP BY 学号 SELECT * FROM 成绩表 ORDER BY 学号 COMPUTE AVG(成绩) BY 学号,25, 5.3 联接查询,* 联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接表示如何使用一个表中的数据来选择另一个表中的行。 * 联接条件通过以下方法定义两个表在查询中的关联方式： （1） 指定每个表中要用于联接的列。典型的联接条件在一个表中指定外键，在另一个表中指定与其关联的键。 （2） 指定比较各列的值时要使用的逻辑运算符（=、 等）。 说明: 1. 联接条件可在 FROM 或 WHERE 子句中指定，建议在 FROM 子句中指定联接条件，有助于将联接条件与 WHERE 子句中可能指定的其它搜索条件分开。 2. 引用多表字段时,任何重复的列名都必须用表名限定,26, 5.3 联接查询,1、内联接（inner join） 使用比较运算符根据每个表共有的列的值匹配两个表的行。包括：相等联接和自然联接 2、外联接 左外联接（left outer join）：通过左向外联接引用左表的所有行。如果左表的某行在右表中没有匹配行，则将为右表返回空值。 右向外联接（ right outer join）：通过右向外联接引用右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 完整外部联接（ full outer join）：返回两个表的所有行。凡对应表中没有匹配行，则返回付回空值，则整个结果集行包含基表的数据值。 3.交叉联接：左表每一行与右表中所有行组合。,27,交叉联接,28, 5.3 联接查询,2、 内联接 (1)相等联接 例1: 查询学生基本信息表和成绩表的所有信息,按学号联接. select * from 学生基本信息表 AS XS inner join 成绩表 on XS.学号=成绩表.学号 注:两个表中都有学号字段,因此显示结果中有两个学号字段. (2)自然联接 例2:更改选择列表消除两个相同列中的一个(学号),即自然联接 select XS.* ,课程编号,成绩 from 学生基本信息表 AS XS inner join 成绩表 AS CJ on XS.学号=CJ.学号,29, 5.3 联接查询,普通例题:利用“系部表”和“课程信息表”,查询任课教师所在系 select 任课教师,系部名称 from 系部表 inner join 课程信息表 on 系部表.系部编号=课程信息表.系部编号 增强：利用“系部表”和“课程信息表”,查询“基础科学部”和“信息与计算机科学”的课程名称,输出系部名称和课程名称，按系部 名称升序排列。 select 系部名称,课程名称 from 系部表 inner join 课程信息表 on 系部表.系部编号=课程信息表.系部编号 where 系部名称 in(基础科学部,信息与计算机科学) order by 系部名称,30, 5.3 联接查询,例: 利用成绩表，学生基本信息表显示学号，姓名，总分（用派生表的方法）,select 学生基本信息表.学号,姓名,总分 from 学生基本信息表 inner join on 学生基本信息表.学号=成绩二.学号,(select 学号,sum(成绩) as 总分 from 成绩表 group by 学号) as 成绩二,31, 5.3 联接查询,提高： 利用“系部表”和“课程信息表”,统计不同系的课程安排数目,显示输出系部名称和课程数目（参照课件中from中派生表的用法） (select 系部编号,count(*) as 课程数 from 课程信息表 group by 系部编号) as 课程表,select 系部名称,课程数 from 系部表 inner join on 系部表.系部编号=课程表.系部编号,32, 5.3 联接查询,提高：利用“成绩表”和“学生基本信息表”，统计平均分小于80的学生名单和平均分（参照课件中from中派生表的用法） (select 学号,avg(成绩) as 平均分 from 成绩表 group by 学号 having(avg(成绩)80) as 成绩二,select 姓名,成绩二.平均分 from 学生表 inner join on 成绩二.学号=学生表.学号,select 姓名,成绩二.平均分 from 学生表 inner join (select 学号,avg(成绩) as 平均分 from 成绩表 group by 学号) as 成绩二 on 成绩二.学号=学生表.学号 WHERE 平均分80,33, 5.3 联接查询,(3)使用等号以外的运算符的联接 select * from 课程信息表,成绩表 where 课程信息表.课程编号成绩表.课程编号 交叉联接 例：交叉联接系部表和课程信息表 Select * from 系部表,课程信息表 Select * from 系部表 cross join 课程信息表 注:如果在交叉联接后面添加一个where子句,它的作用就跟内联接一样了.,34, 5.3 联接查询,自联接: 例 查学生基本信息表中,同名同姓的情况 Select a1.* from 学生基本信息表 as a1,学生基本信息表 as a2 Where a1.姓名=a2.姓名 and a1.学号a2.学号 思考：查询课程信息表中同一门课程任课教师情况 Select a1.* from 课程信息表 as a1 inner join 课程信息表 as a2 on a1.课程名称=a2.课程名称 and a1.课程编号a2.课程编号,35, 5.3 外联接例题,成绩表,档案表,查询学生的学号，姓名，成绩，通过三种联接方式，请说出左联接，右联接，完整外部联接的结果,36, 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.学号,37, 5.3 外联接例题,完整外部联接 SELECT DAN.学号,姓名,成绩 FROM DAN FULL OUTER JOIN CJ ON DAN.学号=CJ.学号,38, 5.3 联接查询,5、 多表联接 虽然每个联接规范只联接两个表，但 FROM 子句可包含多个联接规范。这样一个查询可以联接若干个表。 例:利用课程信息表,成绩表, 学生基本信息表显示学生的学号,姓名,课程名称,成绩 select 成绩表.学号,姓名, 课程名称,成绩 from 课程信息表 inner join 成绩表 on 课程信息表.课程编号 = 成绩表.课程编号 inner join 学生基本信息表 on 成绩表.学号=学生基本信息表.学号,39, 5.4 子查询,4、 子查询基础 子查询是一个 SELECT 查询，它返回单个值且嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询 注意事项: 1.通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称. 2.使用外部查询的WHERE子句包括某个列名,则该子句必须与子查询选择列表中的该列在联接上兼容 3.由于必须返回单个值,所以由于修改的比较运算符引入的子查询不能包括GROUP BY 和HAVING子句 4.包括GROUP BY 的子查询不能使用DISTINCT关键字 5.只有同时指定了TOP,才可以指定ORDER BY 6.约定通过EXISTS引入的子查询的选择列由*组成,不使用单个列名,40, 5.4 子查询,例：用子查询的形式，显示学生的姓名和平均分。 Select 学号,姓名,(select avg(成绩) from 成绩表 where 成绩表.学号=学生基本信息表.学号) as 平均成绩 from 学生基本信息表,41, 5.4 子查询,2 EXIST与IN子查询 例：使用子查询从学生基本信息表中显示年龄比所有回族学生都大的学生姓名,( select 学号 from 成绩表 where 成绩60),例:显示成绩不及格的学生姓名. Select distinct 姓名 from 学生基本信息表 where 学号 in,( select 出生日期 from 学生基本信息表 where 族别=回族),select