SQLsever数据库第四章数据访问与修改.ppt

第四章 数据访问与修改,查询SQL数据 修改SQL数据 使用谓词 使用函数和值表达式 访问多个表 使用子查询访问和修改数据 掌握内容,查询SQL数据,SQL数据查询语句是SELECT语句。该语句的基本框架是SELECT-FROM-WHERE，它包含输出字段、数据来源和查询条件等基本子句。在这种固定格式中，可以不要WHERE，但是SELECT和FROM是必备的。查询语句的子句很多，理解了这条语句各项的含义，就能从数据库中查询出各种数据。,查询SQL数据,语法： Select distinct | all top npercent * | from , 数据源 where 查询条件 group by 分组 having 分组查询条件 order by 排序 必须的子句只有 select和from,其余为可选项。 Select语句中的子句按照下列次序应用：,From子句 - where 子句 - group by 子句 - having 子句 - select 子句- order by 子句,查询SQL数据,distinct | all 可选项 Distinct 确保查询结构不出现重复行，如果集合中指定的列有多行重复，那么只返回最前面的一行。 All 系统默认值，返回所有行。 * | * 表示返回集合中的所有列。 select list 可以是字段列表，算术表达式或使用聚合函数等。 如果结果集合从多个表获得，表名和字段名要同时出现。 语法：表名.字段名, 表名.字段名, From , 指定数据的来源，一个表或多个表。 可对表名重新命名，使用关键字 AS Select A.学号,B.课程名称 from 选课 AS A,课程 AS B where A.课程编号=B.课程编号,查询SQL数据,Select distinct 学号 from 选课 Select distinct 学号,姓名 from 选课,查询SQL数据,设定查询条件：where 可以出现的运算 关系运算： =、=、!=、!、! BETWEEN MinValue AND MaxValue select * from 工资库 where 工资 between 700 and 900 逻辑运算：NOT、AND、OR 集合运算： test_expression NOT IN (expression ,.n ) test_expression NOT IN (subquery ) Select 学号,姓名 from 选课 where 姓名 in (张三,李四) 嵌套查询 select * from 课程 where 课程编号 in (select 课程编号 from 选课),查询SQL数据,Select * from student Where 授课教师 in (select 授课教师 from lesson Where 课程名称=自动控制) Select * from student as A, lesson as B Where A.授课教师=B.授课教师 and B.课程名称=自动控制,查询SQL数据,分组：group by Group by 用于归纳信息类型，以汇总相关数据。 不论是否使用where字句，group by字句都可以包含在select语句中。 将表中的元组按指定列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到每一分组的单一值。 聚集函数：max , min , sum , avg , count 对具有NULL字段使用group by子句时，所有NULL行组成一组 示例： Select 职务, sum(工资) as 职务统计 from 人员 group by 职务 Select 职务,count(*) as 职务人数 from 人员 group by 职务,查询SQL数据,指定组搜索条件：having 如指定group by子句，那么这个having字句将应用于这个group by 字句创建的那些组。 如指定where子句，没有指定group by 子句，那么having子句将应用于where子句的输出，这个输出被看作是一个组。 Where、group by子句均未指定，那么having子句将应用于from子句的输出，这个输出被看作是一个组。 having最常与group by 一起使用，group by 归纳数据，having对分组数据进行筛选，去除不满足条件的组。 示例： Select 职务, sum(工资) as 职务统计 from 人员 group by 职务 having min(工资)850,查询SQL数据,Having示例： 一表student中包含字段：学号，姓名，性别，年龄。要求列出每一年龄组中男学生（超过2人）的人数？,Select 年龄, count (*) from student where 性别=男 group by 年龄 having count(*)2,查询SQL数据,排序查询结果：order by 对结果集合排序显示(ascending, descendant) Order by 字段 ASC|DESC ,字段 ASC|DESC , 缺省为 ASC 示例： Select * from 人员 order by 职务 desc Select * from 人员 order by 职务 desc, 工资 asc,数据维护,插入SQL数据 Insert into ( ,) values( , ) 如果没有指定列名，那么表中的每一列必须有一个值，其次序与表中定义的次序相同。 如指定了列名，那么值的次序必须与设定列次序一致。 字符串类型值必须放入单引号中。 示例： Create table student(num int, name char(10), score float); Insert into student values(1, 张三, 82); Insert into student(num,name) values(2, 李四); Insert into student(name,score,num) values(王五,92,3);,数据维护,使用select语句插入值 Insert into ( ,) select 语句 确保 select 语句返回值的数量和所需的数量相同。 select 语句返回值必须符合目标表上的所有限制。 示例： Insert into 正式职工 select * from 临时工 where year(工作时间)2000 Insert into 正式职工(编号,姓名,工作时间) select 编号,姓名,工作时间 from 临时工 where year(工作时间)2000 Insert into 正式职工(工作时间,姓名) select 工作时间,姓名 from 临时工 where year(工作时间)2000,数据维护,更新SQL数据 Update set = , set where Where可选，不指定where，则对表中所有数据更新。 指定where子句，则对满足条件的记录更新数据。 示例: Update 选课 set 姓名=张三丰 where 姓名=张三 Update 工资库 set 工资=工资*1.15 where 职称 in (教授 , 副教授),数据维护,使用select语句更新值 Update set =( select 语句) where Select语句返回的值赋给对应列上 Select只能返回一个值 示例： Update 选课 set 课程编号= ( select 课程编号 from 课程 where 课程名称=数据库) where 姓名 not in (张三 ,李四) Update 工资库 set 工资= 工资 *( select 工资增长率 from 增长率 where 职称=讲师) where 职称=讲师,数据维护,删除数据 1DELETE 语句 DELETE 语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。DELETE语句的语法格式如下： DELETE FROM table_name|view_name FROM ,.n WHERE ,数据维护,例:不带参数使用DELETE命令删除所有行。 delete from 增长率 本例从“增长率“表中删除所有行。 注意： 将DELETE语句与DROP TABLE语句的功能区分开来。 例:带WHERE子句的DELETE语句，有条件地删除行。 delete from 人员 where 人员.职称=教授,使用谓词,谓词：谓词是一种SQL表达式，它定义有关SELECT语句返回任意行的事实。 示例：Select 姓名,职务 from 人员 where 工资 850 and 工资 850） 指定查询结果中所有行在工资列中值必须大于850,第二个谓词（工资 1050）指定查询结果中所有行在工资列中的值必须小于1050。 每个谓词都要被单独评估，以确定它是否符合该谓词中定义的条件。 如果SQL不能确定值是否符合条件（如空值），那么谓词就评估为未知。,使用谓词,where 子句用来指定搜索条件，滤掉不需要select语句返回的行，或者滤掉无须update和delete语句修改的行。 Where 搜索条件包括一个或多个谓词，每个谓词规定了需要返回或修改的行。 谓词把每一行指定列的值与子查询的结果进行比较，如果每一行的比较结果满足条件，就返回此行。 谓词分类： 比较SQL数据 返回空值 返回近似值 引用附加数据源 量化比较谓词,使用谓词,比较SQL数据 比较运算符：=, , , = 使用between谓词 Between value1 and value2 Value1和value2 表示取值范围的上下界 返回空值 空值表示值不存在，与零、空格及默认值是不同的 is not null 示例： Select * from 人员 where 姓名 is not null and 职称 is not null,使用谓词,返回近似值 Like提供了灵活的环境，可用来指定与数据库中存储值的近似值。 使用like谓词可以获得要查询字符串内容的近似值。 Like谓词常与两个符号搭配使用：% , _ %：代表0个或多个字符 _：代表一个未知字符 % , _ 如不和 like谓词使用时，没有此功能,使用谓词,引用附加数据源 IN谓词和EXISTS谓词 两种谓词都使用子查询来引用表中的数据，而不是查询或修改的主表中的数据。 子查询是嵌套在另一个表达式中的表达式。常见的用法是在select, update, delete 语句的where子句中嵌套select语句。,使用谓词,附加数据源中的IN谓词 第一种用法：定义一表，包括所有和指定值相比较的值 姓名 IN (张三 , 李四) 等同于 姓名=张三 OR 姓名=李四 编号 IN (10, 20, 30) 第二种用法：使用子查询select语句 select * from 选课 where 课程名 in (select 课程名称 from 课程) 要求select子句中指定一个列名，不允许多个或 *号 Select指定的列名与要进行比较的列类型必须一致，列名称可以不同。 可以在 IN 谓词前加 NOT, NOT IN Select * from 人员 where 姓名 not in (张三 ,李四 ),使用谓词,附加数据源中的EXISTS谓词 EXISTS谓词只注重子查询是否返回行 如子查询返回一行或多行，EXISTS谓词评价为真 EXISTS谓词子查询中的select子句中可以使用任何列名，也可以使用任意多个列，此谓词只注重是否返回行，而不注重行的内容。 用于在子查询中匹配不同表的列。 语法:NOT EXISTS (select 语句) 示例： select * from 正式职工 where exists (select * from 工资 where 工资1000 and 正式职工.姓名=张三) Select * from 临时工 where exists(select * from 正式职工 where 工资1000),使用谓词,量化比较谓词 量化比较谓词 ANY SOME, ALL ANY SOME：集合中存在任意一个满足要求的值就返回此行。 SOME和ANY含义相同，可以替换使用。 ALL：集合中每一个值均满足要求才返回此行。 量化比较谓词把每一行指定列的值与子查询的结果进行比较。 定量谓词不支持反操作。 与关系运算符搭配使用。,使用函数和值表达式,聚合函数（集合函数、聚集函数）用来处理或计算数据并返回适当结果。 使用聚合函数 COUNT , MAX , MIN , SUM , AVG 集合函数要求数据按某种方法分组（例如GROUP BY子句）。 如果表不能以某种方法分组，整个表就被看成是一组。,使用函数和值表达式,COUNT函数 用来计算表中记录的个数或者列中值的个数 计算的内容由SELECT语句指定 使用时必须指定列名或星号（星号表示计算一个表中所有记录） MAX(MIN)函数 要求指定列名。 可以比较字符串。 SUM函数 AVG函数,使用函数和值表达式,使用值函数(用法见第二章) 值函数是可以用来返回值的函数。 字符串函数：SUBSTRING , UPPER , LOWER SELECT substring(姓名 ,1,1) as 姓 from 人员 SELECT * from 人员 where substring(姓名 ,1,1)=王 SELECT count(*) from 人员 group by substring(姓名 ,1,1) SELECT substring(姓名 ,1,1),count(*) from 人员 group by substring(姓名 ,1,1) 日期时间函数：GETDATE , YEAR , MONTH , DAY select YEAR(工作时间) from 正式职工,使用函数和值表达式,使用值表达式： 值表达式是用来返回一个数据值的表达式。 表达式中包含列名、数值、数学运算符、关键字或者其他元素组成的公式或者返回单个值 数值表达式：+ , , * , / select RTRIM( 姓名)+职称 as 组合,工资 from 人员 CASE 表达式：可改变值被表示的方法，或生成一个新值。每个值都根据CASE表达式中的条件进行修改。 CASE表达式的结束要用关键字END,使用函数和值表达式,CASE示例1：分类查看值 Select 姓名,工资, 新工资= CASE WHEN 职务=助教 then 工资+100 WHEN 职务=讲师 then 工资+200 WHEN 职务=副教授 then 工资+300 ELSE 工资 END from 人员,使用函数和值表达式,CASE示例2：分类修改值 Update 人员 set 工资= CASE WHEN 职务=助教 then 工资+100 WHEN 职务=讲师 then 工资+200 WHEN 职务=副教授 then 工资+300 ELSE 工资 END,使用函数和值表达式,CASE示例3：分类重命名值 Select 姓名,职务,级别= CASE 职务 WHEN 助教 then 初级 WHEN 讲师 then 中级 WHEN 副教授 then 高级 WHEN 教授 then 高级 ELSE 未知 END from 人员,访问多个表,关系数据库的一个重要因素是可以在任何两个表之间存在关系。这个关系可将表间的数据联系在一起。 内容： 执行基本的连接操作 使用条件连接 执行合并操作,访问多个表,执行基本的连接操作 最简单的连接执行类型是逗号间隔连接,Select * from 选课,课程,查询产生笛卡儿积表，它是一个表的每一行与另一个表的每一行连接在一起的一个表，这种查询结果用处不大。,访问多个表,使用 where语句的同等连接。同等连接是使第一个表中的一个或多个列中的值与第二个表中的一个或多个列的值相等的连接。 Select * from 选课 as A, 课程 as B where A.课程编号=B.课程编号 创建连接的基本原则： 1）From子句中应当包括所有表名。 2）Where子句应当定义一个同等连接。 3）当列名是多个表共有时，列名应该被限定,访问多个表,创建表的自连接,select A.雇员编号,A.姓名,B.姓名 as 管理者姓名 from 雇员 as A,雇员 as B where A.管理者编号=B.雇员编号,访问多个表,使用条件连接内连接 内连接：只返回满足select语句中的同等连接条件的行。内连接只返回匹配行。 内连接创建语法： select 字段1 , 字段2 , from 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段 where 条件 示例： Select A.学号, A.姓名, B.课程名称 from 选课 as A INNER JOIN 课程 AS B ON A.课程编号=B.课程编号,访问多个表,使用条件连接外连接 外连接除了返回匹配行外，还返回一些或全部不匹配的行，决定于连接的类型。 Left 返回所有的匹配行并从关键字JOIN左边的表中返回所有不匹配的行。 RIGHT 返回所有匹配的行并从关键字JOIN右边的表中返回所有不匹配的行。 FULL 返回两个表中所有匹配的行和不匹配的行。 关键字： LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN,访问多个表,Select A.学号,A.姓名,B.课程名称 from 选课 as A LEFT OUTER JOIN 课程 as B ON A.课程编号 = B.课程编号,LEFT JOIN,RIGHT JOIN,FULL JOIN,访问多个表,执行合并操作 合并不同表相似列的数据 关键字：UNION ALL 源列必须一致 语法：select 字段 UNION ALL select 语句,正式职工,临时工,UNION,UNION ALL,Select * from 正式职工 select * from 临时工,UNION,UNION ALL,使用子查询访问和修改数据,说明 子查询和连接一样提供了使用单个查询访问多个表中的数据的方法。 Select, insert, update, delete 等语句能够利用子查询返回的查询结果。 子查询本质上也是一种嵌入的select语句，可用它作为存取第二个表的接口。 返回多行的子查询 (in,exists,any,all) IN 谓词 Select * from 选课 where 课程编号 in (select distinct 课程编号 from 课程) EXISTS谓词 Select * from 选课 as A where EXISTS ( select * from 课程 as B where B.课程名称=数据库 and A.课