第3章关系数据库语言SQLppt课件.ppt

第3章 关系数据库语言SQL,本章重要概念,（1）SQL数据库的体系结构，SQL的组成。（2）SQL的数据定义：SQL模式、基本表和索引的创建和撤销。（3）SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，基本表的联接操作，SQL3中的递归查询。（4）SQL的数据更新：插入、删除和修改语句。（5）视图的创建和撤销，对视图更新操作的限制。,本章概述,SQL是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言是介乎于关系代数和元组演算之间的一种语言。本章详细介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。,关系数据库语言SQL,3.1 SQL的体系结构 3.2 SQL的数据定义 3.3 SQL的数据查询3.4 数据更新 3.5 视图的定义和对视图的操作 3.6 嵌入式SQL,3.1 SQL的体系结构,3.1.1 SQL的产生与发展 3.1.2 SQL数据库的体系结构 3.1.3 SQL的组成,返回,SQL的产生与发展,1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE(Specifying Queries As Relational Expression)语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(Structured English QUEry Language)语言。后来SEQUEL简称为SQL(Structured Query Language)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。,SQL数据库的体系结构,SQL的组成,核心SQL主要有四个部分:(1)数据定义语言，即SQL DDL，用于定义SQL模式、基本表、视图、索引等结构。(2)数据操纵语言，即SQL DML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。(3)嵌入式SQL语言的使用规定。这一部分内容涉及到SQL语句嵌入在宿主语言程序中的规则。(4)数据控制语言，即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。,返回,3.2 SQL的数据定义,3.2.1 SQL模式的创建和撤销 3.2.2 基本数据类型 3.2.3 基本表的创建、修改和撤销 3.2.4 索引的创建和撤销,返回,SQL模式的创建和撤销,SQL模式的创建可用CREATE SCHEMA语句定义，其基本句法如下：CREATE SCHEMA模式名AUTHORIZATION用户名 DROP语句的句法如下：DROP SCHEMA模式名CASCADERESTRICT其方式有两种：CASCADE(级联式)方式。RESTRICT(约束式)方式。,SQL Server2008的schema,Schma 也称为架构，是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。以往 SQL Server 内的对象命名是“服务器.数据库.用户名.对象”，但新版的对象命名改为“服务器.数据库.Schema.对象”。完全限定的对象名称现在包含四部分：server.database.schema.objectSQL Server 2005 以后，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权,基本数据类型,SQL提供的主要数据类型(也称为“域类型”)有：（1）数值型（2）字符串型（3）位串型（4）时间型,返回,An Introduction to Database System,SQL Server支持的数据类型,请参见SQL Server 实用教程表2.3（P38），或参见联机帮助。,基本表的创建、修改和撤销(1),基本表的创建 例3.1 基本表SS（S#，SNAME，AGE，SEX）可用下列语句创建：CREATE TABLE S(S#CHAR(4)NOT NULL，SNAME CHAR(8)NOT NULL，AGECHAR(1)，SEX CHAR(1),PRIMARY KEY(S#)；,基本表的创建、修改和撤销(2),基本表结构的修改增加新的列用“ALTER ADD”语句，其句法如下：ALTER TABLE ADD 删除原有的列用“ALTER DROP”语句，句法如下：ALTER TABLE DROP CASCADERESTRICT 此处CASCADE方式表示：在基本表中删除某列时，所有引用到该列的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该列，否则拒绝删除操作。,基本表的创建、修改和撤销(3),例3.2 在基本表S中增加一个地址（ADDRESS）列，可用下列语句：ALTER TABLE S ADD ADDRESS VARCHAR（30）；应注意，新增加的列不能定义为“NOT NULL”。基本表在增加一 列后，原有元组在新增加的列上的值都被定义为空值（NULL）。例3.3 在基本表S中删除年龄（AGE）列，并且把引用该列的所有视图和约束也一起删除，可用下列语句：ALTER TABLE S DROP AGE CASCADE；例3.4 在基本表S中S#的长度修改为6，可用下列语句：ALTER TABLE S MODIFY S#CHAR（6）；,基本表的创建、修改和撤销(4),基本表的撤销撤销语句的句法如下：DROP TABLE CASCADERESTRICT 此处的CASCADE、RESTRICT的语义同前面句法中的语义一样。,返回,索引的创建和撤销,索引的创建创建索引可用“CREATE INDEX”语句实现。句法如下：CREATE UNIQUE INDEX ON（列名表）索引的撤销当索引不需要时，可以用“DROP INDEX”语句撤销，其句法如下：DROP INDEX,返回,3.3 SQL的数据查询,3.3.1 SELECT查询语句 3.3.2 SELECT语句完整的句法 3.3.3 SELECT语句中的限定 3.3.4 基本表的联接操作 3.3.5 SQL3中的递归查询,返回,SELECT查询语句(1),SELECTFROMWHERE句型在关系代数中最常用的式子是下列表达式：A1,An(F(R1Rm)这里R1、Rm为关系，F是公式，A1、An为属性。针对上述表达式，SQL为此设计了SELECTFROMWHERE句型：SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。,SELECT语句完整的句法(1),SELECT语句完整的句法如下：SELECT 目标表的列名或列表达式序列 FROM 基本表名和（或）视图序列 WHERE 行条件表达式 GROUP BY 列名序列 HAVING 组条件表达式 ORDER BY 列名 ASC|DESC，,SELECT语句完整的句法(2),整个语句的执行过程如下：（1）读取FROM子句中基本表、视图的数据，执行笛卡尔积操作。（2）选取满足WHERE子句中给出的条件表达式的元组。（3）按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。（4）按SELECT子句中给出的列名或列表达式求值输出。（5）ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。,SELECT查询语句(2),SELECT句型使用实例例3.8 教学数据中有三个基本表（关系）：S（S#，SNAME，AGE，SEX）SC（S#，C#，GRADE）C（C#，CNAME，TEACHER）下面用SELECT查询语句表达每个查询。检索学习课程号为C2的学生学号与成绩。检索学习课程号为C2的学生学号与姓名。检索选修课程名为MATHS的学生学号与姓名。检索选修课程号为C2或C4的学生学号。检索不学课程的学生学号。（）检索至少选修课程号为C2和C4的学生学号。（）检索学习了全部课程的学生姓名。,SELECT查询语句(4),聚合函数 COUNT（*）计算元组的个数COUNT（列名）对一列中的值计算个数SUM（列名）求某一列值的总和（此列的值必须是数值型）AVG（列名）求某一列值的平均值（此列的值必须是数值型）MAX（列名）求某一列值的最大值MIN（列名）求某一列值的最小值,返回,SELECT语句完整的句法(3),例3.11 对教学数据库的基本表S、SC、C中数据进行查询和计算。统计每一年龄选修课程的学生人数 SELECT AGE，COUNT（DISTINCT S.S#）FROM S，SC WHERE S.S#=SC.S#GROUP BY AGE；由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。,SELECT语句完整的句法(4),求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。SELECT AGE，COUNT（S#）FROM SWHERE SEX=MGROUP BY AGEHAVING COUNT（*）50ORDER BY 2，AGE DESC；,返回,SELECT语句中的限定(1),SELECT子句中的规定 SELECT子句描述查询输出的表格结构，即输出值的列名或表达式。其形式如下：SELECT ALL|DISTINCT|*条件表达式中的算术比较操作 条件表达式中可出现算术比较运算符（，!），也可以用“BETWEEN AND”比较运算符限定一个值的范围。列和基本表的改名操作,SELECT语句中的限定(2),字符串的匹配操作条件表达式中字符串匹配操作符是“LIKE”。在表达式中可使用两个通配符：百分号（%）：与零个或多个字符组成的字符串匹配。下划线（_）：与单个字符匹配。集合的并、交、差操作 当两个子查询结果的结构完全一致时，可以让这两个子查询执行并、交、差操作。并、交、差的运算符为UNION、INTERSECT和EXCEPT。空值的比较操作SQL中允许列值为空，空值用保留字NULL表示。,SELECT语句中的限定(3),集合的比较操作SQL提供SELECT语句的嵌套子查询机制。子查询是嵌套在另一个查询中的SELECT语句。（1）集合成员资格的比较（2）集合成员的算术比较（3）空关系的测试（4）重复元组的测试导出表的使用 SQL2允许在FROM子句中使用子查询。如果在FROM子句中使用了子查询，那么要给子查询的结果起个表名和相应的列名。,返回,基本表的联接操作,基本表的联接操作(例),3.4 数据更新,3.4.1 数据插入 3.4.2 数据删除 3.4.3 数据修改,返回,数据插入,（1）单元组的插入 INSERT INTO 基本表名（列名表）VALUES（元组值）（2）多元组的插入 INSERT INTO 基本表名（列名表）VALUES（元组值），（元组值），（元组值）（3）查询结果的插入 INSERT INTO 基本表名(列名表)SELECT查询语句 这个语句可把一个SELECT语句的查询结果插到某个基本表中。（4）表的插入 INSERT INTO 基本表名1（列名表）TABLE 基本表名2 这个语句可把基本表2的值插入到基本表1中。,数据删除,SQL的删除操作是指从基本表中删除元组，其句法如下：DELETE FROM基本表名WHERE 条件表达式其语义是从基本表中删除满足条件表达式的元组。删除语句实际上是“SELECT*FROM基本表名WHERE条件表达式”和DELETE操作的结合，每找到一个元组，就把它删去。应该注意，DELETE语句只能从一个基本表中删除元组。WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件。,数据修改,当需要修改基本表中元组的某些列值时，可以用UPDATE语句实现，其句法如下：UPDATE 基本表名SET 列名=值表达式，列名=值表达式ROW=（元组）WHERE 条件表达式 其语义是：修改基本表中满足条件表达式的那些元组中的列值，需修改的列值在SET子句中指出。,返回,3.5 视图的定义和对视图的操作,3.5.1 视图的创建和撤销 3.5.2 对视图的更新操作 3.5.3 视图的优点,返回,视图的创建和撤销(1),视图的创建 创建视图可用“CREATE VIEW”语句实现。其句法如下：CREATE VIEW（列表名）AS 例3.27 对于教学数据库中基本表S、SC、C，用户经常要用到S#、SNAME、CNAME和GRADE等列的数据，那么可用下列语句建立视图：CREATE VIEW STUDENT_GRADE(S#，SNAME，CNAME，GRADE)AS SELECT S.S#，SNAME，CNAME，GRADE FROM S，SC，C WHERE S.S#=SC.S#AND SC.C#=C.C#；,视图的创建和撤销(2),视图的撤销 在视图不需要时，可以用“DROP VIEW”语句把其从系统中撤销。其句法如下：DROP VIEW 视图名 例3.28 撤销STUDENT_GRADE视图，可用下列语句实现：DROP VIEW STUDENT_GRADE；,对视图的更新操作(1),定义3.1 如果视图是从单个基本表只使用选择、投影操作导出的，并且包含了基本表的主键，那么这样的视图称为“行列子集视图”，并且可以被执行更新操作。,对视图的更新操作(2),例3.29 下面讨论对视图更新的几个例子。设有一个视图定义CREATE VIEW SUDENT_GRADE AS SELECT S.S#,SNAME，CNAME，GRADEFROM S，SC，CWHERE S.S#=SC.S#AND SC.C#=C.C#；这个视图定义了学生选修的课程名和成绩，是由三个基本表联接而成的。如果用户要在视图中执行插入操作:INSERT INTO STUDENT_GRADEVALUES(S24,WANG,MATHS,80)；若在基本表C中，课程名为MATHS的课程号有多个，则往基本表SC插入元组时，课程号C#就不定了。因此，对这个视图的更新是不允许的。,对视图的更新操作(3),设有一个视图定义，包含每个学生选修课程（成绩为非空）的门数和平均成绩：CREATE VIEW S_GRADE（S#，C_NUM，AVG_GRADE）AS SELECT S#，COUNT（C#），AVG（GRADE）FROM SCWHERE GRADE IS NOT NULL视图S_GRADE虽然是从单个基本表导出，但导出时使用了分组和聚集操作，因此也是不能更新的。譬如，在未更改基本表SC中值时，要在视图S_GRADE中更改学生的平均成绩，显然是不切实际的。,对视图的更新操作(4),如果定义了一个有关男学生的视图：CREATE VIEW S_MALE AS SELECT S#,SNAME,AGEFROM SWHERE SEX=M；这个视图是从单个关系只使用选择和投影导出的，并且包含键S#，因此是可更新的。例如，执行插入操作：INSERT INTO S_MALE VALUES（S28,WU,18）；系统自动会把它转变成下列语句：INSERT INTO SVALUES(S28,WU,18,M)；,返回,视图的优点,视图是用户一级的数据观点，由于有了视图，使数据库系统具有下列优点：（1）视图提供了逻辑数据独立性。（2）简化了用户观点。数据库的全部结构是复杂的,并有多种联系。（3）数据的安全保护功能。,返回,数据库应用编程,存储过程ADO.net,小 结,SQL是关系数据库的标准语言，已广泛应用在商用系统中。SQL的数据定义部分包括对SQL模式、基本表、视图、索引的创建和撤销。SQL的数据操纵分成数据查询和数据更新两部分。SQL的数据查询是用SELECT语句实现，兼有关系代数和元组演算的特点。SQL的数据更新包括插入、删除和修改等三种操作，在视图中只有行列子集视图是可以更新的。嵌入式SQL涉及到SQL语句的宿主语言程序中的使用规定，以解决两种语言的不一致和相互联系的问题。同时还介绍了动态SQL语句。,本章的重点篇幅,（1）教材中P97的例3.8（SELECT语句）。（2）教材中P124的例3.31和的例3.32（嵌入式SQL）。,重要内容分析（一）,SELECT语句是SQL的核心内容，对于该语句考生应掌握下列内容。1SELECT语句的来历 在关系代数中最常用的式子是下列表达式：A1,An(F(R1Rm)这里R1、Rm为关系，F是公式，A1、An为属性。针对上述表达式，SQL为此设计了SELECTFROMWHERE句型：,重要内容分析（二）,SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。2SELECT语句中出现的基本表名，应理解为基本表中的元组变量，而列名应理解为元组分量。,重要内容分析（三）,3SELECT语句的语义有三种情况，下面以学生表S（S#，SNAME，AGE，SEX）为例说明。第一种情况：SELECT语句中未使用分组子句，也未使用聚合操作，那么SELECT子句的语义是对查询的结果执行投影操作。例如：SELECT S#，SNAME FROM S WHERE SEX=M；第二种情况：SELECT语句中未使用分组子句，,重要内容分析（四）,但在SELECT子句中使用了聚合操作，此时SELECT子句的语义是对查询结果执行聚合操作。例如：SELECT COUNT（*），AVG（AGE）FROM S WHERE SEX=M；该语句是求男同学的人数和平均年龄。,重要内容分析（五）,第三种情况：SELECT语句使用了分组子句和聚合操作（有分组子句时必有聚合操作），此时SELECT子句的语义是对查询结果的每一分组去做聚合操作。例如：SELECT AGE，COUNT（*）FROM S WHERE SEX=M GROUP BY AGE；该语句是求男同学每一年龄的人数。,重要内容分析（六）,4SELECT语句中使用分组子句的先决条件是要有聚合操作。但执行聚合操作不一定要用分组子句。譬如求男同学的人数，此时聚合值只有一个，因此不必分组。但同一个聚合操作的值有多个时，必须使用分组子句。譬如求每一年龄的学生人数。此时聚合值有多个，与年龄有关，因此必须分组。,