软件双语Lecture62011春.ppt

第6讲 SQL语言(DML) SQL Language(DML) Textbook: Chapter 3:Basic SQL Query Language,Instructor：孙瑜 Department:计算机科学学院计算机专业教研室 Email： QQ：782061453 QQ答疑群：79453765,2,Review: Relational Algebra,Unary Relational OperationsSELECT, PROJECT Binary Relational OperationsJOIN, DIVISION Relational Algebra Operations from Set TheoryUNION、SET DIFFERENCE、INTERSECTION、CARTESIAN PRODUCT,A Complete Set of Relational Algebra Operations,The set of relational algebra operations ， ， is a complete set(完备集) . 在关系代数运算中，集合的并运算、差运算、笛卡尔积运算以及选择运算和投影运算是五种基本运算，另三种运算（集合的交运算以及连接运算和除运算）可以用五种基本运算来表达，引进它们并不增加语言的能力，但是可以简化表达。,4,课程教材的架构,数据库基本概念(Introduction) Chapter 1: Introduction 补充：Data Model 补充：Database System Architecture 关系数据模型(逻辑模型之一：Relational Model) Chapter 2.1-2.4:The Relational Model(“数据结构”和“数据的约束条件”) Chapter 2.5-2.8:Relational Algebra(“数据操作”) Chapter 3:Basic SQL Query Language (“数据操作”) (本讲内容) Chapter 5:Programs to Access a Database (“数据操作”) 数据库设计 Chapter 6: Database Design Chapter 8: Indexing(物理数据库设计),5,本讲主要内容,ANSI-SQL及Transact SQL语言概述 数据查询 单表查询 多表连接查询 子查询 集合查询 数据更新 补充知识：注入式攻击,6,复习：数据模型的组成要素,数据结构：对系统静态特征的描述 数据操作：对系统动态特征的描述 检索(查询) 更新(插入、删除、修改：增删改) 数据的约束条件：完整性约束规则,7,关系操作,特点：集合(set)操作方式，即操作的对象和结果都是集合(关系),SQL：Structured Query Language 分类：DML， DDL，DCL,8,SQL语言概述,SQL语言的提出和发展 1974年，由Boyce和Chamber提出 1975-1979年，在System R上首次实现，由IBM的San Jose研究室研制，称为SEQUEL(Structured English QUEry Language) 1986年推出了SQL标准：SQL-86，“数据库语言SQL: Structured Query Language” 1989年ANSI / ISO推出了SQL标准: SQL-89, 数据库语言SQL的标准集合,9,SQL语言的提出和发展(续) 1992年进一步推出了SQL标准：SQL-92，也称为SQL2 是SQL-89的超集 增加了许多新特性，如新数据类型，更丰富数据操作，更强完整性支持等 原SQL-89被称为entry-SQL, 扩展的被称为Intermediate级和Full级 1999年进一步推出了SQL标准： SQL-99，也称为SQL3 对面向对象的一些特征予以支持，支持抽象数据类型，支持行对象和列对象等 有些特征，现有数据库厂商尚不能做到完全支持,10,标准SQL语言的“方言”,各数据库厂商的产品对SQL语言的支持大部分是相似的(至少要实现SQL-92的entry-SQL)，但它们之间也存在着一定的差异 Oracle: PL/SQL language SQL Server: Transact-SQL language(本课程的教学演示语言) 主要是在ANSI-SQL基础上加入了“过程性”编程元素 但我们在介绍SQL时主要介绍标准的SQL语言,11,SQL语言的功能组成,Data Manipulation Language (DML)(重点) 数据查询(Data Query Language (DQL)(核心) 数据查询功能用于实现对数据库中数据的查询、检索 数据操纵(Data Manipulation) 数据操纵功能用于实现对数据库数据的增加、删除和修改 Data Definition Language (DDL) 数据定义(Data Definition) 数据定义功能用于定义、删除和修改数据库中的对象 Data Control Language (DCL) 数据控制(Data Control) 数据控制功能用于控制用户对数据库的操作权限,12,13,附加的T-SQL语言要素,局部变量 全局变量 操作符 注释 流程控制语句 函数 存储过程 触发器 游标,14,SQL语言的特点,综合统一 高度非过程化 面向集合的操作方式 语言简捷，易学易用 以同一种语法结构提供两种使用方式 独立 嵌入,15,独立使用方式(又称“交互式”),例：查询所有员工的全部信息。,16,嵌入使用方式,17,18,SQLDML DQL(数据查询),数据查询是数据库的核心操作 单表查询 多表连接查询 子查询 集合查询,19,T-SQL语法要点,在每句T-SQL的结尾最好打上分号“；”，以与ANSI-SQL标准兼容 T-SQL语言不区分大小写，即使是字符串 建议：T-SQL的关键字最好用大写 分隔标识符( )或(“ “),20,例：查询所有女性员工的部分信息。,T-SQL语言不区分大小写,21,分隔标识符,22,关于“SELECT *”,当使用 * 时，结果集中的列的顺序与 CREATE TABLE、ALTER TABLE 或 CREATE VIEW(视图)语句中所指定的顺序相同 在应用程序中使用 SELECT时，最好显式地指定需要查询的列而不是指定一个星号 Why?,23,例：查询所有员工的部分信息。,“显式地指定需要查询的列”的另一个好处：可以控制结果集中列显示的顺序,24,“重复元组”去除,25,注意,如果在关系中没有指定键，则允许有重复元组，SQL中的表不是一个真正的“关系/集合”(而是包：multiset/bag) 在查询时，通过DISTINCT可以去掉重复的元组(单独的某列仍然可能有重复值) 如果在关系中指定了键或唯一性约束，则不允许有重复元组,26,ALL关键字(默认值),27,例：查询1960年以后出生员工的部分信息。,日期的输入格式,28,例：查询工资在30000和55000之间的员工的部分信息。,29,例：查询工资在30000和55000之间的员工的部分信息。,BETWEEN谓词,30,例：查询工资不是在30000和55000之间的员工的部分信息。,31,例：查询工资不是在30000和55000之间的员工的部分信息。,32,33,例：查询产品名称开头字母从A到F(E)的产品信息,相当于是查英语字典,34,例：查询居住在Houston,Humble,Spring这三个城市员工的部分信息,35,例：查询居住在Houston,Humble,Spring这三个城市员工的部分信息,IN谓词,36,一个简单示例：从EMPLOYEE表中找出不叫“孙瑜”的人,37,正确结果,错误结果,38,正确做法,39,德.摩根律,(AB) A B (AB) A B,40,LIKE关键字(字符串搜索，与前例的区别),可以为zero,41,42,例：查询FNAME为两个字母的员工的姓名,43,44,例：查询FNAME中至少包含两个n的员工的姓名,45,例：查询LNAME第二个字母为a，且第一个字母为J或N的员工的姓名,46,例：查询LNAME第二个字母为a，且第一个字母不是J或N的员工的姓名,47,还有一个员工呢？,48,怎么找出剩下的这名员工呢？,49,Three-valued logic(三值逻辑),任何有效的逻辑表达式都将返回下列布尔值：TRUE, FALSE, or Unknown Unkown：当参与比较的操作数为空值NULL时 WHERE：必须当逻辑表达式返回TRUE时才把该元组包括在结果集中,50,51,例：查询最高级别员工的信息,空值检查 注意：NULL与0、空字符串是完全不同的,52,例：查询除最高级别员工以外的其他员工信息,53,在关键字段尽量加上非空约束，比如“姓名”、“性别”、“院系编号”等,求“补集”的正确SQL表达式,54,例：查询所有员工的部分信息。 (先按性别排序，再按工资排序),排序,55,返回“计算列”,56,注入式攻击(Injection Attack),57,58,59,60,123456,61,OR 1=1,62,63,返回“计算列”,64,65,例：查询所有商品的库存总价值。,66,例：查询所有员工的年龄信息。,在Transact-SQL中有很多CAST()、GetDate()这样的函数，最好熟练掌握；另外还可自定义函数,67,聚集函数,汇总关系元组数,68,汇总某一列属性的行数,69,计算某一列(可以是计算列)属性的总和,70,71,计算某一列(可以是计算列)属性的最大/小值,72,计算某一列(可以是计算列)属性的平均值,73,SQLDML DQL(数据查询),数据查询是数据库的核心操作 单表查询 多表连接查询 内联接 外联接 LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN 交叉联接 自联接 子查询 集合查询,74,内连接(INNER JOIN),例：查询所有学生所在院系信息。,75,76,内连接执行过程,高度非过程化语言,77,如果对表进行了重命名，则限定 必须用新名称,在不发生混淆的情况下，可省略 表限定,78,可省略INNER和AS,79,80,多表查询时SELECT *表示的含义 当未使用限定符指定时，星号解析为对 FROM 子句中所指定的所有表或视图中的所有列的引用 当使用表或视图名称进行限定时，星号解析为对表或视图中的所有列的引用,81,注意：教材上部分章节使用的是老式连接方式,Retrieve the name and address of all employees who work for the Research department,SELECT FNAME,LNAME,ADDRESS FROM EMPLOYEE,DEPARTMENT WHERE DNAME=Research AND DNUMBER=DNO;,SELECT FNAME,LNAME,ADDRESS FROM EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO WHERE DNAME=Research;,82,多表内连接查询,连接表：桥梁,83,(EXAMRECORD+STUDENTINFOTEMP Table)+COURSE,84,85,例：查询各门课程任课教师信息,86,还有两门课程和两位老师呢？,87,左外连接LEFT OUTER JOIN,显示LEFT表(Course)中的全部元组，如果RIGHT表(Teacher) 中没有扫描到符合连接条件的元组，则用NULL代替,88,右外连接RIGHT OUTER JOIN,显示RIGHT表(Teacher)中的全部元组，如果LEFT表(Course) 中没有扫描到符合连接条件的元组，则用NULL代替,89,全外连接FULL OUTER JOIN,显示LEFT表(Course) 和RIGHT表(Teacher)中的全部元组，如果 没有扫描到符合连接条件的元组，则用NULL代替,90,多表外连接，表顺序很重要,91,92,例：查询所有学生选修的课程(包括未选课学生以及没有学生选修的课程信息) 第1步：连接“StudentInfo”和“ExamRecord”,用RIGHT可以吗 需要 FULL吗,93,第2步：把第1步生成的结果关系与“Course”连接,OUTER可省略,FULL,INNER、LEFT、RIGHT、FULL?,临时表,COURSE,94,再次强调：只写“JOIN”就代表是“INNER JOIN”,95,96,97,第3步：按照题目要求进行适当的投影操作,98,交叉连接(CROSS JOIN),实际就是“笛卡尔积”(Cartesian product) 例：将StudentInfo与Department表交叉连接,交叉连接(笛卡尔积)的结果关系有 多少元组？,99,The CROSS JOIN returns all rows from STUDENTINFO combined with all rows from DEPARTMENT. No ON clause exists to indicate any connecting column between the tables.,100,如果添加一个 WHERE 子句，则交叉连接的作用将同内连接一样,101,自连接,举例：查询每个员工上级的姓名。,E1,E2,102,left,103,104,带分组子句的完整的SELECT语句格式,SELECT 分组列名, 聚集函数 , 聚集函数 FROM 单表/连接生成的大表 WHERE 元组限定条件 GROUP BY 分组列名 HAVING 分组限定条件 ORDER BY 分组列名/聚集函数;,105,For each department, retrieve the department number, the number of employees in the department, and their average salary.,106,107,注意,子句的顺序不能颠倒 SELECT后可以只投影分组列名或聚集函数 SELECT后投影的列名集合必须小于等于GROUP BY后的“分组列名”集合 可以按多个字段分组 WHERE与HAVING的区别 HAVING分组条件可以是针对“分组列名”，也可以是“聚集函数” 只能针对“分组列名”或“聚集函数”排序,108,Group By多个字段,109,110,WHERE与HAVING的区别,111,For each department, retrieve the department number, the number of employees in the department, and their average salary.(排序),112,For each department, retrieve the department number, the number of employees in the department, and their average salary.(排序),113,查询执行过程,生成结果关系(“大表”)在“大表”上利用WHERE指定的条件筛选元组按照Group by指定的属性将元组分组将Group by的分组用Having指定的条件筛选按照Order By指定的属性对元组排序(排序的属性可以不在最后投影的属性列中)投影或用函数、表达式计算 注意：如果没有GROUP BY和HAVING的话，上述步骤中红色的部分将不会执行,114,例：查询各门课程平均成绩在70分以上的“资环院”学生姓名及其平均成绩,115,请先用手工计算。,116,有问题吗？,117,分解动作 (1)生成结果关系(“大表”),118,(2)在“大表”上利用WHERE指定的条件筛选元组,119,(3)按照Group by指定的属性将元组分组,如果SELECT后面没有Aggregate函数，或者 GROUP BY后面没有HAVING的话，这样分组 没有什么意义，实际就是筛选出不重复的属性,120,(4)将Group by的分组用Having指定的条件筛选,实际现在还未开始投影,121,(5)按照Order By指定的属性对元组排序,实际现在还未开始投影,122,(6)投影或用Aggregate函数计算,123,例：查询各门课程平均成绩在70分以上的“资环院”学生姓名及其课程最高分,不一定要一致,124,SQLDML DQL(数据查询),数据查询是数据库的核心操作 单表查询 多表连接查询 子查询 Subquery(子查询) Nesting Query(嵌套查询) 带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询(难点) 集合查询,125,嵌套查询,定义 将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询,126,举例：查询“John Smith”的完成项目情况,127,注意,SQL语言允许多层嵌套查询(建议：不要编写太复杂的嵌套查询，可以利用中间变量简化SQL语句) 子查询的SELECT语句不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序 嵌套查询一般的求解方法是由里向外处理,128,注意,在大多数情况下，“子查询”与“多表连接查询”是等价的(但执行效率可能不一样),同一个查询可以用多种方式来构造，具体使用哪种可以根据题目要求或者用户习惯来确定(但不同的方式执行效率会有差别)。,129,带有IN谓词的子查询,To retrieve the SSNs of employees who work on any of the projects that “Employee 123456789” works on.,130,To retrieve the names of employees who work on any of the projects that “Employee 123456789” works on.,131,与除运算举例类似，但“一字之差”引发的难度差异很大：To retrieve the names of employees who work on any of the projects that John Smith works on.,132,Retrieve the names of employees who have dependents.,133,Retrieve the names of employees who have dependents.(用连接查询方式),134,Retrieve the names of employees who have no dependents.,135,Retrieve the names of employees who have no dependents. (用连接查询方式),136,带有比较运算符的子查询,Retrieve the name and address of all employees who work for the Research department.,137,使用局部变量,138,举例：查询所有工资大于或等于全公司平均工资的员工信息,139,使用局部变量简化程序,过程编程与非过程编程结合,140,141,这种查询具有很大的不确定性，根据当前数据库表中的数据，查询在运行时有可能成功，有可能失败，因此应尽量避免(除非你确信不管数据库里的数据如何变化，子查询永远只返回一行数据),142,带有ANY或ALL谓词的子查询,143,上例查询等价于带有IN谓词的子查询,144,145,ANY与ALL的区别,查询工资大于或等于任意一个女性员工工资的男性员工信息,146,上例查询等价于使用汇总函数的子查询,MIN,147,查询工资大于或等于全部女性员工工资的男性员工信息,ANY与ALL的区别,148,上例查询等价于使用汇总函数的子查询,MAX,149,与汇总函数的等价性,WHY?,无实际意义,150,思考题：求工资最高的员工姓名,151,“不相关子查询”与“相关子查询”,不相关子查询(non-correlated subquery) 子查询只执行一次，其结果用于父查询。子查询的查询条件不依赖于父查询 相关子查询(correlated subquery) 子查询的查询条件依赖于外层父查询的某个属性值 主要是指“带有EXISTS谓词的子查询”,152,例A：查询所有选修了“计算机文化基础”的学生信息,153,154,如果子查询结果包含行，则返回 TRUE，否则为FALSE,“子查询”与“父查询”发生联系,155,相关子查询的执行过程,首先取外层查询中表的第1个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层查询中表的下一个元组，重复这一过程，直至外层表全部检查完毕为止。,156,SQL语言是高度非过程语言,157,关于模拟C#程序,在逻辑上，我们在内存中用数组表示元组，而在实际DBMS中，数据库元组是以某种方式保存在一个磁盘文件里的，而DBMS到底如何从这个文件中找到所需要的元组，这就涉及到数据库的“内模式”，即数据在数据库内部的表示方式,158,“例A：查询所有选修了“计算机文化基础”的学生信息”的等价SQL1(不相关子查询),159,“例A：查询所有选修了“计算机文化基础”的学生信息”的等价SQL2(相关子查询),160,例B：查询没有选修“计算机文化基础”的学生信息,通常都取“*”，WHY？,161,162,例：查询所有由部门经理直接管理的员工信息,163,“例：查询所有由部门经理直接管理的员工信息”等价SQL,164,例：查询所有不由部门经理直接管理的员工信息,165,“例：查询所有不由部门经理直接管理的员工信息”等价SQL,WHY?,166,167,168,Retrieve the names of employees who have dependents.,169,例C：查询选修了全部课程的学生姓名,170,171,求解思路,求出“至少有一门课程没有选修”的学生集合A 遍历(循环)所有的学生 给定一个学生S 遍历(循环)所有的课程 给定一门课程C，检查在学生S的成绩表上是否不存在课程C的成绩记录 对集合A取补集,172,课程选修记录,173,课程未选修记录,174,至少有一门课程没有选修的学生,175,利用Transact-SQL的过程编程，例如中间变量、循环、分支等 可简化求解过程,没有一门课程没有选修的学生=选修了全部课程的学生,176,177,类似的查询用关系代数的“除运算”比较简单,178,“例C：查询选修了全部课程的 学生姓名”等价SQL1,179,“例C：查询选修了全部课程的 学生姓名”等价SQL2,180,“例C：查询选修了全部课程的 学生姓名”等价SQL3,181,例D：查询至少选修了“张平”所选修课程(简称：张平课程)的学生姓名,求出“至少有一门张平课程没有选修”的学生集合A 遍历(循环)所有的学生 给定一个学生S 遍历(循环)所有的张平课程 给定一门张平课程C，检查在学生S的成绩表上是否不存在课程C的成绩记录 对集合A取补集,182,“张平课程”,注意括号的配对关系,183,184,“例D：查询至少选修了张平课程的学生 姓名”等价SQL1,185,第1步：,“例D：查询至少选修了张平课程的学生 姓名”等价SQL2,186,第2步：,“例D：查询至少选修了张平课程的学生 姓名”等价SQL2,187,集合查询,SELECT语句查询的结果是元组的集合，所以多个SELECT语句的查询结果可进行集合操作 集合操作符 并 Union 交 Intersect(SQL SERVER不支持) 差 Difference (SQL SERVER不支持)