1、1,第三章 关系数据库标准语言SQL,概述 SQL数据查询功能 SQL数据定义功能 视图操作 SQL数据更新 SQL数据控制 嵌入式SQL,2,SQL概述,SQL的产生与发展 1974年，由Boyce和Chamber提出，称为SEQUEL（Structured English Query Language）。 1975-1979年，在IBM的San Jose研究室研制的System R上实现。 1981年， IBM在推出SQL/DS关系数据库时，将其命名为SQL (Structured Query Language)。 随着SQL语言应用的日益广泛，ANSI和ISO先后制定了SQL-86、 S
2、QL-89、 SQL-92、 SQL-3等多个SQL标准,3,SQL特点,综合统一 高度非过程化 面向集合的操作方式 以同一种语法结构提供两种使用方式 语言简捷，易学易用,4,SQL语言的基本概念,基本表与导出表 基本表：是实际存在的，每个表在存储中可用一个存储文件来表示。 导出表:是从基本表导出的表，有视图（View）和快照(Snapshot)。 视图是一个虚表。即视图所对应的数据不实际存储在数据库中，只在数据库的数据字典中存储视图的定义。 视图一经定义就可以和基本表一样进行查询等操纵，也可以用来定义新的视图。,5,SQL语言的基本概念,关系数据库的三级模式结构,SQL,View V1,Vi
3、ew V2,Base tableB1,Stored fileS1,Base tableB1,Base tableB1,Base tableB1,Stored fileS1,Stored fileS1,Stored fileS1,外模式,模式,内模式,6,SQL数据查询功能,查询的基本结构是 SELECT-FROM-WHERE组成的查询块。一般形式：SELECT 目标列 要检索的数据项 FROM 基本表（或视图） 要操作的关系名，1个或多个 WHERE 检索条件 查询结果应满足的条件表达式查询块的结果仍是一个表。该表是从给定的表中检索出满足给定检索条件的指定列的集合； 查询块执行的过程是在表的水
4、平方向上按“检索条件”选取元组，又在垂直方向上按SELECT指定的列进行投影。 查询块可进行关系代数中投影、选取、连接等操作的组合。,7,示例用表,学生表 S,学生选课表SC,课程表C,8,投影检索,SELECT-FROM-WHERE查询块中，没有WHERE子句，是单纯的投影操作。采用DISTINCT消去SELECT结果中的重复行。,例1：检索学生的姓名，年龄SELECT SN，SAFROM S ；,例2：检索学生选修课的课程号SELECT DISTINCT C#FROM SC ；,9,选取检索,由WHERE子句指出查询条件。 检索条件可以包括如下运算符： 比较运算符：=,(!=), , =,
5、 , = 布尔运算符：AND，OR，NOT （ ）,例1：检索选修C2课程的所有学生的学号和成绩。SELECT S#，GFROM SCWHERE C#=C2；,10,例2：检索选修C1或C2且成绩高于70分的学生学 号、课程号和成绩。SELECT S#，C#，GFROM SCWHERE （C#=C1 OR C#=C2）AND G=70；,例3：检索成绩在70至85分之间的学生学号、课程号和成绩。SELECT S#，C#，GFROM SCWHERE G BETWEEN 70 AND 85；,11,排序检索,在SELECT-FROM-WHERE查询块后接ORDER BY子句，将结果按指定列排序。格
6、式：ORDER BY 列名 ASC 或DESC ASC为升序；DESC为降序，缺省为升序。 可以是单列排序或多列排序 该子句在SELECT语句中作为最后一个子句出现。,例1：检索全体学生信息，并按系号升序，同一个系按年龄降序排列。SELECT *FROM SORDER BY SD ，SA DESC；,12,连表检索（1）,将多个相互关联的表按照一定条件连接起来，实现多表数据检索。 SELECT-FROM-WHERE语句块结构：SELECT 指明选取的列名（来自多个表）FROM 指明要进行连接的表名WHERE指明连接条件（连接谓词）与选取条件。 连接条件一般格式为： . . 其中， 称为连接字段
7、； 主要有：=，=，=，!=,13,连表检索（2）,例1：检索学生李勇所学课程的成绩。SELECT SN，C#，GFROM S，SCWHERE S.S#=SC.S# AND SN=A;注：如果连接的表中有属性名相同，要用表名作前缀加以区分。,14,连表检索（3）,表自身的连接 通过定义别名，将一个表看成两个表，进行连接。,例2：检索所有比李勇年龄大的学生姓名、年龄。SELECT SN，SAFROM S X，S YWHERE S.SAY.SA AND Y.SN=李勇;,15,连表检索（4）,外连接 在连接谓词某一边加（*或+），则逻辑上为*所在边的表增加了一个空行。它可以与另一个表中所有不满足连
8、接条件的元组进行连接，使这些元组能够输出。,例3：检索所有学生的全部信息。SELECT *FROM S，SCWHERE S.S#=SC.S#(*);,16,子查询嵌套检索（1）,WHERE子句中可以包含另一个查询块，该查询块称为子查询或嵌套查询，包含子查询的语句称为外部查询。 外部查询利用子查询来获取检索条件的条件值，检索条件根据子查询的结果来确定外部查询的结果数据。 子查询按照与外部查询的联系不同，分为普通子查询和相关子查询。 普通子查询：与外部查询无关，可单独执行得一组值。 相关子查询：把外查询的列值作为检索条件的条件值。,17,子查询嵌套检索（2）,涉及同一个表的子查询如果子查询返回单值
9、，可以直接用比较运算符=,=,=等连接子查询。 如果子查询返回一组值，则必须在比较运算符和子查询之间插入ANY和ALL等操作符。,例1：检索与李勇同岁的学生姓名。SELECT SNFROM SWHERE S.SA=(SELECT SA FROM S WHERE SN=李勇);,18,子查询嵌套检索（3）,例2：检索选修C2课程的学生姓名。SELECT SNFROM SWHERE S#=ANY(SELECT S# FROM SC WHERE C#=C2);,例3：检索选修C2课程的成绩最高的学生学号。SELECT S#FROM SCWHERE C#=C2 AND G=ALL(SELECT G F
10、ROM SC WHERE C#=C2);,19,子查询嵌套检索（4）,用IN检索 IN在嵌套子查询中最常使用。可代替“=ANY”，是集合运算中的“”运算。,例2：检索选修C2课程的学生姓名。SELECT SNFROM SWHERE S# IN(SELECT S# FROM SC WHERE C#=C2);,用NOT IN检索 NOT IN表示不在集合中，与！=ALL相同。,20,子查询嵌套检索（5）,用EXISTS检索 EXISTS表示存在量词“”。 表达式 EXISTS（子查询）当且仅当子查询结果为非空时为真。,例2：检索选修C2课程的学生姓名。SELECT SNFROM SWHERE EX
11、ISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C2);,用NOT EXISTS 检索 表示“不存在”。 表达式NOT EXISTS（子查询）在子查询结果为空时为真。,21,子查询嵌套检索（6）,用NOT EXISTS表达全称量词（ ） 任何一个带有全称量词的谓词总可以转换为等价的带存在量词的谓词： (x)P ( x (P),例2：检索选修所有课程的学生姓名。 本题等价于“检索这样的学生的姓名，不存在他不选修的课程”。SELECT SNFROM SWHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS （SE
12、LECT * FROM SCWHERE S#=S.S# AND C#=C.C#);,检索s.s# 没有 选修的课程,22,子查询嵌套检索（7）,用NOT EXISTS 表达蕴涵 pq p q (y) pq (y(pq) ) (y (p q) y (p q),23,并、差、交检索（1）,并、差、交检索的操作对象必须是相容的，即必须有相同数量的属性列，且相应属性列的域也必须相同。 并、差、交的SQL运算符： 并：UNION 差：MINUS 交：INTERSECT,24,并、差、交检索（2）,例2：检索选修了C1或C2课程的学生学号。SELECT S# FROM SC WHERE C#=C1UNIO
13、N SELECT S# FROM SC WHERE C#=C2;,例2：检索无人选修的课程号和名称。SELECT C#，CN FROM C WHERE C# IN（SELECT C# FROM C MINUSSELECT DISTINCT C# FROM SC）；,25,库函数检索（1）,库（集）函数 COUNT( ) 按列求元组个数，COUNT(*)对行记数。 SUM() 对数值列求总和 AVG() 求数值列的平均值 MAX() 在列中找出最大值 MIN() 在列中找出最小值,26,库函数检索（2）,例2：检索学生总数。SELECT COUNT（*） FROM S ;,例2：检索选修了课程的
14、学生人数。SELECT COUNT（DISTINCT S#）FROM SC ;,例2：求学号为S4的学生的总分和平均分。SELECT SUM（G），AVG（G）FROM SC WHERE S#=S4 ;,例2：检索选修了C1课程的学生最高分。SELECT MAX（G）FROM SC WHERE C#=C1;,27,分组检索（1）,按属性列（列组）将关系的元组分组，每组在这些分组属性列（列组）上具有相同值，对每一组可执行SELECT操作。 分组子句：GROUP BY 列名 HAVING 条件表达式 分组条件 WHERE子句与HAVING子句 WHERE 子句是针对“行”进行，用于去掉不符合条件的
15、若干行；HAVING子句针对“分组”进行，必须和GROUP BY 连用，用于去掉不符合条件的若干分组。 在查询块中出现的顺序：WHERE GROUP BY HAVING,28,分组检索（2）,例2：检索至少选修三门课程的学生学号和选课门数。SELECT S#，COUNT（*）FROM SC GROUP BY S#HAVING COUNT（*）=3 ;,例2：求选修四门以上课程的学生学号和总成绩（不统计不及格的课程）。最后按降序列出总成绩排序名单。SELECT S#，SUM（G）FROM SC WHERE G=60GROUP BY S#HAVING COUNT（*）=4ORDER BY SUM(
16、G) DESC;,29,算术表达式值的检索,SELECT子句中，可包括由属性列、常数、库函数、算术运算符+-*/ 等组成的算术表达式。 检索结果数据项名可用表达式表示或用“别名”来表示。,例2：有职工表EMP（EMP#，EMPN，JOB，SALARY，BONUS，DEPT），要求检索所有PROGRAMMER的奖金大于工资25%的职工姓名和一年的总收入，并按奖金与工资之比的降序排列。SELECT EMPN，BONUS/SALARY BS ， 12*（SALARY+BONUS） TOTALFROM EMPWHERE JOB=PROGRAMMERAND BONUS0.25*SALARY ORDER
17、BY BONUS/SALARY DESC ;,30,部分匹配查询,使用谓词LIKE 或NOT LIKE，一般形式：LIKE/NOT LIKE “列名”必须为字符型或变量字符型。 “字符串常量”可包含两个特殊符号 % 与_ %：代表任意序列的0个或多个字符； _ ：代表任意单个字符,例2：检索所有姓刘的学生的学号、姓名。SELECT S#，SNFROM SWHERE SN LIKE 刘%;,31,SQL数据定义功能,定义、删除、修改基本表 定义、删除索引 定义、删除视图,32,定义基本表,定义基本表 Create Table (, ); 完整性约束 NULL/NOT NULL UNIQUE PR
18、IMARY KEY FOREIGN KEY CHECK,33,SQL92的数据类型 char（n）：固定长度的字符串。 varchar（n）：可变长字符串。 int：整数。 smallint：小整数类型。 numeric（p，q）：定点数共p位，小数点右边q位。 Real, double precision ：浮点数与双精度浮点数,精度与机器有关。 Float(n)：n位的精度浮点数。 date：日期（年、月、日）。 time：时间（小时、分、秒）。 interval：两个date或time类型数据之间的差,34,示例,Create table S (S# char(5) not null,
19、SN char(20) not null unique, SA int, SD char(15), Primary key(S#), Check (SA =18 and SA =45)； Create table SC (S# char(5) not null, C# char(5) not null, G number(4,2), Primary key (S#, C#), Foreign key (S#) references S (S#) Foreign key (C#) references C (C#);,35,修改、删除基本表定义,修改基本表 语法： Alter Table Add
20、 Drop Modify 示例 例1：S表增加“入学时间”属性Alter Table S Add (Scome Date); 例2：将SA的数据类型改为半字长整数Alter Table S Modify (SA Smallint) 例3：删除S表的主键Alter Table S Drop Primary Key 删除基本表 语法: Drop Table 示例: Drop Table S;,36,定义、删除索引,定义索引 语法：Create UniqueCluster Index On (次序, 次序 ); 示例： Create Unique Index Scno On SC(S# ASC, C
21、# DESC) 删除索引 语法：Drop Index 示例：Drop Index Stusno;,37,定义视图,定义视图 语法： Create View (, )As With Check Option 示例：建立信息系的学生视图Create View IS_StudentAsSELECT S#, SN, SA FROM SWHERE SD = IS 删除视图 Drop View ,38,查询视图,视图消解（View Resolution）DBMS执行对视图的查询时，从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正的查询。这一转换过
22、程称为视图消解。,39,视图查询示例,在信息系学生的视图中找出年龄小于20岁的学生。 Select S#, SA From IS_StudentWhere SA 20; 转换后为： Select S#, SA From SWhere SD=IS And SA 20;,40,视图的作用,能够简化用户操作 使用户能够以多种角度看待同一数据 对重构数据提供了一定程度的逻辑独立性 能够对机密数据提供安全保护,41,SQL数据更新,插入数据Insert语句 修改数据Update语句 删除数据Delete语句,42,插入数据,插入单个元组 Insert Into (,)Values(,) 插入子查询结果
23、Insert Into (,),43,示例,例1：Insert Into SValues (95020, 陈冬, IS, 18);例2：Insert Into SC (Sno, Cno)Values(95020, 1);例3：Insert Into Dept_Age (Sdept, Avgage) Select SD, AVG(SA) From SGroup By SD;,44,修改数据,语法：Update Set =, =Where 示例 例1：将学生95001的年龄改为22岁Update StudentSet Sage = 22Where Sno=95001,45,示例,例2：将所有学生的
24、年龄增加1岁 Update StudentSet Sage=Sage+1 例3：将计算机系全体学生的成绩置零。 Update SCSet G = 0Where CS = (Select SD From SWhere S.S# = SC.S#),46,删除数据,语法： Delete From Where 示例： 例1：删除学号为95019的学生的记录 Delete From S Where S#=95019 例2：删除所有学生的选课记录 Delete From SC 例3：删除计算机系所有学生的选课记录 Delete From SC Where CS = (Select SD From SWhe
25、re S .S# = SC.S#),47,SQL数据控制功能,定义完整性约束条件 支持事务操作 提供安全控制功能 授权 GRANT 权限ON 对象类型对象名TO 用户 收回权限 REVOKE 权限ON 对象类型对象名 FROM 用户,48,嵌入式SQL,嵌入式SQL的意义 SQL语言是非过程化语言。而许多事务处理都是过程性的，需要根据不同的条件执行不同的任务，单纯使用SQL语言较难实现。嵌入式SQL把SQL的最佳特性与程序设计语言的最佳特性结合起来，使SQL功能更强，灵活性更强。 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现。
26、,49,对于嵌入式SQL，DBMS多采用预编译方法进行处理。 把嵌入在程序中的SQL语句翻译为高级语言（主语言）源码，然后按主语言的通常方式进行编译、连接形成可执行代码。,50,把SQL嵌入主语言使用时必须解决的问题： 区分SQL语句与主语言语句。 在SQL语句前加前缀 EXEC SQL解决。数据库工作单元和程序工作单元之间的通信。 通过定义SQL通信区向主语言传递SQL语句执行状态信息； 通过主变量（主语言程序变量）交换参数与数据。SQL与主语言之间操作方式的协调。SQL是一次一集合。主语言是一次一记录（一组主变量一次只能存放一条记录）。 通过游标解决。游标是系统开辟的存放SQL执行结果的缓冲区，由游标指针执行一组记录。程序通过游标逐一获取记录，并赋给主变量。,51,动态SQL,如果在预编译时下列信息不能确定，就必须使用动态SQL技术，如：SQL语句正文，主变量个数，主变量的数据类型，SQL语句中引用的数据库对象等。 动态SQL允许在程序运行过程中“临时”组装SQL语句，主要有三种形式： 语句可变 条件可变 数据库对象，查询条件均可变,
