数据库系统概论第三章.ppt

第三章关系数据库标准语言SQL,SQL语言,SQL概述SQL数据查询功能SQL数据定义功能SQL数据更新功能嵌入式SQL,SQL概述,历史1974年，由Boyce和Chamberlin提出，称为SEQUEL（Structured English Query Language）。1975-1979年，在IBM的San Jose研究室研制的System R上实现。1981年，IBM在推出SQL/DS关系数据库时，将其命名为SQL(Structured Query Language)。现在，不仅关系型数据库系统，甚至非关系型数据库系统都将SQL作为其数据库语言。SQL语言已成为当今最为成功和应用最广的数据库语言。,SQL概述（I）,有关标准 随着SQL语言应用的日益广泛，ANSI和ISO先后制定了多个SQL标准：SQL-86：较为简单，主要包括数据定义语言、数据操纵语言、嵌入式语法等几个部分。SQL-89：增加了对完整性约束的支持。SQL-92：也称SQL2，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-3：正在讨论中的新的标准，将增加对面向对象模型的支持。,SQL概述（II）,特点综合统一集数据定义，数据操纵，数据查询和数据控制于一体。单一的结构-关系，带来了数据操作符的统一。高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。面向集合的操作方式操作的对象和操作的结果均为集合。以同一种语法结构提供两种使用方式 SQL既是自含式语言，又是嵌入式语言,SQL概述（III）,语言简捷，易学易用类似于英语自然语言,SQL对关系数据库模式的支持,SQL对关系数据库模式的支持,基本表是本身独立存在的表，一个（或多个）基本表对应一个存储文件存储文件由数据块构成的存储空间，用于存储基本表、索引等。其逻辑结构组成了关系数据库的内模式视图是从一个或几个基本表中导出的表，其本身不独立存储于数据库中,例子表,STUDENT,SC,COURSE,SQL数据查询功能,查询语句SELECT的语法：Select ALL|DISTINCT,From,Where Group By,HAVING Order By,SQL数据查询功能,其语义是：从FROM子句给定的表中，检索出满足WHERE子句给定检索条件的记录，并根据SELECT子句从这些记录中选出指定的列（属性）所构成的结果集合。SELECT和FROM两个子句定义了投影操作。WHERE子句给出了选取操作的定义（当涉及多表操作时，WHERE子句要同时给出连接操作条件）。可见，查询语句作为整体代表着关系代数中的投影、选取、连接等操作的组合。但是，由于在查询语句中并不规定投影、选取、连接等操作的执行顺序。因此，它比关系代数更一般，更少过程化。,SQL数据查询功能,单表查询连接查询嵌套查询集合查询,单表查询,查询全体学生的姓名与学号Select Sname,Sno From Student;查询全体学生的全部属性Select*From Student;查看全体学生的姓名及其出生年份Select Sname,2001-Sage From Student;,单表查询（续I）,查询全体学生所在学校、姓名、出生年份和所在系（用小写字母显示系名）。Select“北航”,Sname,2001-Sage,ISLOWER(Sdept)From Student;Select“北航”School,Sname,2001-Sage Birth,ISLOWER(Sdept)From Student;,单表查询（续II）,单表查询（续III）,查询选修了课程的学生学号Select Distinct Sno From STUDENT;查询计算机系全体学生的姓名Select Sname From Student where Sdept=CS;查询所有年龄在20岁以下的学生姓名及其年龄Select Sname From Student where Sage20;,单表查询（续IV）,查询年龄在2023（含20和23岁）之间的学生姓名、性别和年龄。Select Sname,Ssex,Sage From Student Where Sage BETWEEN 20 AND 23;查询数学系和计算机系的学生的姓名和性别。Select Sname,Ssex From Student Where Sdept IN(MA,CS);,单表查询（续V）,查询所有姓刘的学生的学号、姓名Select Sno,Sname From Student Where Sname Like 刘%查询所有姓刘，并且名字为两个汉字的学生的学号、姓名Select Sno,Sname From Student Where Sname Like 刘_,单表查询（续VI）,查询所选课程没有成绩的学生的学号，所选课程号。Select Sno,Cno From SC Where Grade IS NULL,单表查询（续VII）,ORDER BY子句可以将查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排序。查询全体学生的情况，并按所在系的升序和年龄的降序排列Select*From Student Order By Sdept Asc,Sage Desc,单表查询（续VIII）,聚集函数Count()求元组个数Sum()对数值列求总和Avg()求数值列的平均值Max()求最大值Min()求最小值,单表查询（续IX）,查询学生总数Select Count(*)From Student;查询选修了课程的学生人数Select Count(Distinct Sno)From SC;计算1号课程的学生平均成绩Select AVG(Grade)From SC Where Cno=1;查询选修1号课程的学生最高分数Select MAX(Grade)From SC Where Cno=1;,单表查询（续X）,查询各门课及其相应选课人数。Select Cno,Count(Sno)From SC Group By Cno;查询选课至少为三门的学生的学号及其选课门数。Select Sno,Count(Sno)From SC Group By Sno Having Count(Sno)=3;,连接查询,连接条件连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：.其中的称为连接字段，连接条件的各个连接字段类型必须是可比的，但不必是相同的。主要有：=，=，=，!=。当连接运算符为等号时，称为等值连接，否则称非等值连接。,连接查询（续I）,查询每个学生及其所选课程的情况Select Student.*,SC.*From Student,SC Where Student.Sno=SC.Sno;查询每一门课的间接先修课（即先修课的先修课）Select FIRST.Cno,SECOND.Cpno From Course FIRST,Course SECOND Where FIRST.Cpno=SECOND.Cno查询所有学生的学号、姓名、所选修课程的课程号和成绩Select Student.Sno,Sname,Cno,Grade From Student,SC Where Student.Sno=SC.Sno(*),嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE 称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。通过子查询，可以利用一系列简单查询来构造一个复杂查询。子查询中的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。,嵌套查询（续I）,不相关子查询不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。相关子查询相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。其执行顺序是：首先取外部查询选取一行记录（称为候选行），然后子查询利用候选行中相关列的值查询结果数据。接着外部查询利用子查询返回的结果集合判断候选行是否满足检索条件，若满足，则把它放入外部查询的结果集合。重复上述步骤直到处理完外部查询的每一行候选行。,嵌套查询（续II）,查询与刘晨在同一个系学习的学生。Select Sno,Sname,Sdept From Student Where Sdept=(Select Sdept From Student Where Sname=刘晨);,嵌套查询（续III）,查询选修了课程名为信息系统的学生的学号和姓名。Select Sno,Sname From Student Where Sno IN(Select Sno From SC Where Cno IN(Select Cno From Course Where Cname=信息系统);,嵌套查询（续IV）,查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。Select Sname,Sage From StudentWhere Sdept IS And Sage Any(Select Sage From Student Where Sdept=IS),嵌套查询（续V）,查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄。Select Sname,Sage From StudentWhere Sdept IS And Sage All(Select Sage From Student Where Sdept=IS),嵌套查询（续VI）,查询选择了1号课程的学生姓名。Select Sname From StudentWhere Exists(Select*from SC Where Sno=Student.Sno And Cno=1)查询没有选择1号课程的学生姓名。Select Sname From StudentWhere Not Exists(Select*from SC Where Sno=Student.Sno And Cno=1),嵌套查询（续VII）,查询选修了全部课程的学生姓名Select Sname From Student Where Not Exists(Select*from Course Where Not Exists(Select*from SC Where Sno=Student.Sno And Cno=Course.Cno);,集合查询,查询选修了1号或2号课程的学生Select Sno From SC Where Cno=1UnionSelect Sno From SC Where Cno=2,集合查询（续I）,查询选修了1号课程的学生和选修了2号课程的学生的交集Select Sno From SC Where Cno=1 And Sno In(Select Sno From SC Where Cno=2),集合查询（续II）,查询计算机系的学生与年龄不大于19岁的学生的差集Select*from Student Where Sdept=CS And Sage 19;,SQL数据更新功能,Insert语句Update语句Delete语句,Insert语句,插入数据Insert Into(,)Values(,)插入子查询结果Insert Into(,),Insert语句（续I）,Insert Into Student Values(95020,陈冬,IS,18);Insert Into SC(Sno,Cno)Values(95020,1);Insert Into Dept_Age(Sdept,Avgage)Select Sdept,AVG(Sage)From Student Group By Sdept;,Update语句,修改数据Update Set=,=Where,Update语句（续I）,将学生95001的年龄改为22岁Update Student Set Sage=22 Where Sno=95001将所有学生的年龄增加1岁Update Student Set Sage=Sage+1,Update语句（续II）,将计算机系全体学生的成绩置零。Update SC Set Grade=0 Where CS=(Select Sdept From Student Where Student.Sno=SC.Sno),Delete语句,删除数据Delete From Where,Delete语句（续I）,删除学号为95019的学生的记录Delete From Student Where Sno=95019删除所有学生的选课记录Delete From SC,Delete语句（续II）,删除计算机系所有学生的选课记录Delete From SCWhere CS=(Select Sdept From Student Where Student.Sno=SC.Sno),SQL数据定义功能,定义、删除、修改基本表定义、删除索引定义、删除视图,SQL数据定义功能（续I）,定义、删除、修改基本表,定义基本表Create Table(,定义、删除、修改基本表（续I）,SQL92的数据类型char（n）：固定长度的字符串。varchar（n）：可变长字符串。int：整数。smallint：小整数类型。numeric（p，d）：定点数共p位，小数点右边q位。Real,double precision：浮点数与双精度浮点数,精度与机器有关。Float(n)：n位的精度浮点数。date：日期（年、月、日）。time：时间（小时、分、秒）。interval：两个date或time类型数据之间的差,定义、删除、修改基本表（续II）,完整性约束NULL/NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK,定义、删除、修改基本表（续III）,Create table student(Sno char(5)not null,Sname char(20)not null unique,Ssex char(2),Sage Int,Sdept char(15),Primary key(Sno),Check(sage=18 and sage=45);,定义、删除、修改基本表（续IV）,Create table SC(Sno char(5)not null,Cno char(5)not null,Grade number(4,2),Primary key(Sno,Cno),Foreign key(Sno)references Student(Sno)Foreign key(Cno)references Course(Cno);,定义、删除、修改基本表（续V）,Alter Table Add()Drop Modify(),定义、删除、修改基本表（续VI）,向Student表增加“入学时间”列Alter Table Student Add(Scome Date);将Sage的数据类型改为半字长整数Alter Table Student Modify(Sage Smallint)删除Student表的主键Alter Table Student Drop Primary Key,定义、删除、修改基本表（续VII）,删除基本表语法:Drop Table 例:Drop Table Student;,定义、删除索引,建立索引Create UniqueCluster Index On(次序,次序);删除索引Drop Index,定义、删除索引（续I）,Create Unique Index Stusno On Student(Sno);Create Unique Index Scno On Sc(Sno ASC,Cno DESC)Drop Index Stusno;,定义、删除视图,视图的定义视图是由一个或几个基本表（或视图）导出的表，数据库不存储视图所对应的 数据，而仅在 数据字典中存储其定义，因此，也称视图为虚表。视图一经定义，就可以像基本表一样被查询和删除。视图的作用能够简化用户操作使用户能够以多种角度看待同一数据对重构数据提供了一定程度的逻辑独立性能够对机密数据提供安全保护,定义、删除视图（续I）,创建视图Create View(,)As With Check Option删除视图Drop View,定义、删除视图（续II）,建立信息系的学生视图，并要求进行修改和插入操作时只能针对信息系的学生。Create View IS_Student As Select Sno,Sname,Sage From Student Where Sdept=IS With Check Option;,定义、删除视图（续III）,建立信息系选修了1号课程的学生的视图Create View IS_S1(Sno,Sname,Grade)As Select Student.Sno,Sname,Grade From Student,SC Where Sdept=IS And Student.Sno=Sc.Sno And Sc.Cno=1;,定义、删除视图（续IV）,建立信息系选修了1号课程且成绩在90分以上的学生的视图Create View IS_S2 As Select Sno,Sname,Grade From IS_S1 Where Grade=90;将学生的学号及平均成绩建立为一个视图Create View S_G(Sno,Gavg)As Select Sno,AVG(Grade)From SC Group By Sno;,定义、删除视图（续V）,删除视图IS_S1Drop View IS_S1;视图消解（View Resolution）DBMS执行对视图的查询时，从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正的查询。这一转换过程称为视图消解。,定义、删除视图（续VI）,在信息系学生的视图中找出年龄小于20岁的学生。Select Sno,Sage From IS_Student Where Sage 20;转换后为：Select Sno,Sage From Student Where Sdept=IS And Sage 20;,嵌入式SQL,为什么使用嵌入式SQL？许多事务处理都是过程性的，需要根据不同的条件执行不同的任务，单纯使用SQL语言较难实现。实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现。嵌入式SQL分为：预编译方式和函数方式。,嵌入式SQL（续I）,区分SQL语句与C语言语句 嵌入的SQL语句以EXEC SQL开始，以分号(;)结束。嵌入的SQL语句根据其内容不同又分为可执行语句和说明性语句。可执行语句又分为数据定义，数据操纵，数据控制三种。EXEC SQL delete from Studentwhere Sno=95001；,嵌入式SQL（续II）,嵌入SQL语句与C语言之间的数据传递主变量用来在C语句和SQL语句之间传递数据，宿主变量的声明与C变量的声明类似，但必须放在下列标识语句之间。EXEC SQL BEGIN DECLARE SECTIONint Sno;char Sname30;EXEC SQL END DECLARE SECTION,嵌入式SQL（续III）,主变量可以出现于SQL语句任意可以出现表达式的地方，但前面要加（:）以与数据库对象名（如表名、列名）相互区别。在C语言中使用时则不需要。主变量可以分为输入主变量（由应用程序赋值，SQL语句引用）和输出主变量（由SQL语句赋值，应用程序引用）。示例：EXEC SQL select Sname,Sdept into:Sname,:Sdept:Sdept_id from Student where Sno=:Sno;,嵌入式SQL（续IV）,指示变量 用来指示返回相关主变量的状态，指示变量的返回值包括：=0：取到主变量的值不空，没有发生截断。=1：取到主变量的值为空值。0：取到主变量的值发生了截断，指示变量的值是截断前的字符串的实际长度。指示变量必须紧跟在SQL语句中指定的主变量之后，前面也要加（:）以与数据库对象名（如表名、列名）相互区别。在C语言中使用时则不需要。,嵌入式SQL（续V）,SQL与主语言之间操作方式的协调SQL：一次一集合。C语言：一次一记录。游标：是系统为用户查询开辟的一个缓冲区，存放SQL语句的执行结果，每个游标都有一个名字，用户可以通过游标逐一获取记录，并赋给主变量，由主语言进行处理,嵌入式SQL（续VI）,定义与使用游标的语句declare：定义一个游标，使之对应一个select语句。declare 游标名 cursor for select语句for update of for update任选项，表示该游标可用于对当前行的指定列的修改与删除。open：打开一个游标，执行游标对应的查询，结果集合为该游标的活动集。open 游标名,嵌入式SQL（续）,fetch：在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中。fetch 游标名 into 主变量表close：关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句。close 游标名,嵌入式SQL（续X）,SQL语句执行信息反馈良好的应用程序必须提供对错误的处理,应用程序需要知道SQL语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息。SQL通讯域SQLCA是一结构,每一嵌入SQL语句的执行情况在其执行完成后写入SQLCA结构中的各变量中,根据SQLCA中的内容可以获得每一嵌入SQL语句执行后的信息,应用程序就可以做相应的处理。为了说明(SQLCA),必须在应用程序中包括:EXEC SQL INCLUDE SQLCA;,嵌入式SQL（续XI）,连接数据库EXEC SQL CONNECT:username IDENTIFIED BY:password;结束工作EXEC SQL COMMIT WORK RELEASEEXEC SQL ROLLBACK WORK RELEASE,动态SQL,如果在预编译时下列信息不能确定，就必须使用动态SQL技术，如：SQL语句正文，主变量个数，主变量的数据类型，SQL语句中引用的数据库对象等。动态SQL允许在程序运行过程中“临时”组装SQL语句，主要有三种形式：语句可变条件可变数据库对象，查询条件均可变,