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,指定数据的来源，一个表或多个表。可对表名重新命名，使用关键字 ASSelect A.学号,B.课程名称 from 选课 AS A,课程 AS Bwhere A.课程编号=B.课程编号,查询SQL数据,Select distinct 学号 from 选课Select distinct 学号,姓名 from 选课,查询SQL数据,设定查询条件：where 可以出现的运算关系运算：=、=、!=、!、!BETWEEN MinValue AND MaxValueselect*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 studentWhere 授课教师 in(select 授课教师 from lesson Where 课程名称=自动控制)Select*from student as A,lesson as BWhere 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 studentwhere 性别=男group by 年龄having count(*)2,查询SQL数据,排序查询结果：order by 对结果集合排序显示(ascending,descendant)Order by 字段 ASC|DESC,字段 ASC|DESC,缺省为 ASC示例：Select*from 人员 order by 职务 descSelect*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(工作时间)2000Insert into 正式职工(编号,姓名,工作时间)select 编号,姓名,工作时间 from 临时工 where year(工作时间)2000Insert into 正式职工(工作时间,姓名)select 工作时间,姓名 from 临时工 where year(工作时间)2000,数据维护,更新SQL数据Update set=,set where Where可选，不指定where，则对表中所有数据更新。指定where子句，则对满足条件的记录更新数据。示例:Update 选课 set 姓名=张三丰 where 姓名=张三Update 工资库 set 工资=工资*1.15where 职称 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 value2Value1和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 INSelect*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,ALLANY SOME：集合中存在任意一个满足要求的值就返回此行。SOME和ANY含义相同，可以替换使用。ALL：集合中每一个值均满足要求才返回此行。量化比较谓词把每一行指定列的值与子查询的结果进行比较。定量谓词不支持反操作。与关系运算符搭配使用。,使用函数和值表达式,聚合函数（集合函数、聚集函数）用来处理或计算数据并返回适当结果。使用聚合函数 COUNT,MAX,MIN,SUM,AVG集合函数要求数据按某种方法分组（例如GROUP BY子句）。如果表不能以某种方法分组，整个表就被看成是一组。,使用函数和值表达式,COUNT函数用来计算表中记录的个数或者列中值的个数计算的内容由SELECT语句指定使用时必须指定列名或星号（星号表示计算一个表中所有记录）MAX(MIN)函数要求指定列名。可以比较字符串。SUM函数AVG函数,使用函数和值表达式,使用值函数(用法见第二章)值函数是可以用来返回值的函数。字符串函数：SUBSTRING,UPPER,LOWERSELECT 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,DAYselect YEAR(工作时间)from 正式职工,使用函数和值表达式,使用值表达式：值表达式是用来返回一个数据值的表达式。表达式中包含列名、数值、数学运算符、关键字或者其他元素组成的公式或者返回单个值数值表达式：+,*,/select RTRIM(姓名)+职称 as 组合,工资 from 人员CASE 表达式：可改变值被表示的方法，或生成一个新值。每个值都根据CASE表达式中的条件进行修改。CASE表达式的结束要用关键字END,使用函数和值表达式,CASE示例1：分类查看值Select 姓名,工资,新工资=CASEWHEN 职务=助教 then 工资+100WHEN 职务=讲师 then 工资+200WHEN 职务=副教授 then 工资+300ELSE 工资ENDfrom 人员,使用函数和值表达式,CASE示例2：分类修改值Update 人员set 工资=CASEWHEN 职务=助教 then 工资+100WHEN 职务=讲师 then 工资+200WHEN 职务=副教授 then 工资+300ELSE 工资END,使用函数和值表达式,CASE示例3：分类重命名值Select 姓名,职务,级别=CASE 职务WHEN 助教 then 初级WHEN 讲师 then 中级WHEN 副教授 then 高级WHEN 教授 then 高级 ELSE 未知ENDfrom 人员,访问多个表,关系数据库的一个重要因素是可以在任何两个表之间存在关系。这个关系可将表间的数据联系在一起。内容：执行基本的连接操作使用条件连接执行合并操作,访问多个表,执行基本的连接操作最简单的连接执行类型是逗号间隔连接,Select*from 选课,课程,查询产生笛卡儿积表，它是一个表的每一行与另一个表的每一行连接在一起的一个表，这种查询结果用处不大。,访问多个表,使用 where语句的同等连接。同等连接是使第一个表中的一个或多个列中的值与第二个表中的一个或多个列的值相等的连接。Select*from 选课 as A,课程 as Bwhere 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 BON A.课程编号=B.课程编号,访问多个表,使用条件连接外连接外连接除了返回匹配行外，还返回一些或全部不匹配的行，决定于连接的类型。Left 返回所有的匹配行并从关键字JOIN左边的表中返回所有不匹配的行。RIGHT 返回所有匹配的行并从关键字JOIN右边的表中返回所有不匹配的行。FULL 返回两个表中所有匹配的行和不匹配的行。关键字：LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN,访问多个表,Select A.学号,A.姓名,B.课程名称from 选课 as A LEFT OUTER JOIN 课程 as BON 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 Awhere EXISTS(select*from 课程 as Bwhere B.课程名称=数据库 and A.课程编号=B.课程编号),使用子查询访问和修改数据,返回单行的子查询有时要求子查询的返回值只能有一个，这样才能把一列值和子查询返回的单值进行比较。常使用聚合函数返回单值。列出工资库中大于平均工资的人员select*from 工资库where 工资(select avg(工资)from 工资库)不一定在子查询中使用聚合函数来返回单个值，可以使用 where条件来返回一个值。示例：Select*from 人员where 姓名=(select name from stu where num=001),使用子查询访问和修改数据,使用嵌套子查询嵌套子查询指一个子查询是另外一个子查询的组成部分。示例：Select*from 学生库where 学号 in(select 学号 from 选课 where 课程编号 in(select 课程编号 from 课程)Insert,update,delete同样可以使用子查询示例：Insert into 人员 values(1,(select 姓名 from str where num=5),讲师);Update 人员 set 工资=工资*(select 上调比率 from 增长工资 where 职称=讲师)+1)where 职称=讲师;,掌握内容,Select查询语句语法(六个关键字)及优先次序。重点是 group by 和 having子句。插入 insert 和更新 update 数据库数据的语法。Where 条件中谓词的使用，between and,is not null,in,exists,like,like some,ANY(SOME),ALLCase函数在select语句中的使用方法表间连接。基本连接(逗号连接、同等连接、自连接)、使用条件连接(内连接INNER和外连接LEFT,RIGHT,FULL)及数据的合并方法UNION ALL。子查询的使用。多行查询、单行查询、嵌套查询的应用。,