第五章 sql语言

第五章SQL语言学习目的与要求：SQL语言是关系数据库的标准语言，是本课程的一个重点。本章总的要求是：全面掌握，深刻理解。熟练应用。要求掌握的技能是：SQL定义语句、嵌入式SQL语句的应用。要求熟练掌握的技能是:SQL查询语句、视图、SQL更新语句的应用。方法：多做题，多上机实践。考核知识点与考试要求5.1SQL简介（识记）5.2SQL的数据定义（综合应用）5.3SQL的数据查询5.3.1SELECT查询语句的基本结构，连接、嵌套、存在量词的三种查询方式（综合应用）5.3.2SELECT语句完整的结构，分组子句、排序子句的用法(综合应用)5.3.3数据查询中的限制和规定(简单应用)5.3.4条件表达式中的比较操作(简单应用)5.3.5嵌套查询的改进写法(领会)5.3.6基本表的连接操作(综合应用)5.4SQL的数据更新（综合应用）5.5视图(简单应用)5.6嵌入式SQL(领会)5.7存储过程SQL/PSM(识记)5.1SQL简介1.SQL发展历程SQL从1970年美国IBM研究中心的E.F.Codd发表论文到1974年Boyce和Chamberlin把SQUARE语言改为SEQUEL语言，到现在还在不断完善和发展之中，SQL(结构式查询语言)虽然名为查询，但实际上具有定义、查询、更新和控制等多种功能。2.SQL数据库的体系结构SQL数据库的体系结构也是三级结构，但术语与传统关系模型术语不同，在SQL中，关系模式称为基本表，存储模式称为存储文件，子模式称为视图，元组称行，属性称列。SQL数据库体系的结构要点如下：(1)一个SQL数据库是表的汇集。(2)一个SQL表由行集构成，行是列的序列，每列对应一个数据项。(3)表或者是基本表，或者是视图。基本表是实际存储在数据库中的表，视图由是由若干基本表或其他视图构成的表的定义。(4)一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。存储文件与物理文件对应。(5)用户可以用SQL语句对表进行操作，包括视图和基本表。(6)SQL的用户可以是应用程序，也可以是终端用户。3.SQL的组成SQL由四部分组成：(1)数据定义：SQLDDL。定义SQL模式，基本表、视图和索引。(2)数据操纵：SQLDML。包括数据查询和数据更新(增、删、改)。(3)数据控制：包括对基本表和视图的授权、完整性规则的描述，事务控制等。(4)嵌入式SQL的使用规定。4.SQL的特点（1）SQL具有十分灵活和强大的查询功能。（2）SQL不是一个应用开发语言，它只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能。（3）SQL是国际性标准语言，有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化。（4）SQL的词汇不多，完成核心功能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用。5.2SQL的数据定义1.SQL模式的创建和撤消：SQL模式的创建可简单理解为建立一个数据库，定义一个存储空间，其句法是：CREATESCHEMA模式名AUTHORIZATION用户名撤消SQL模式的句法为：DROPSCHEMA模式名[CASCADE|RESTRICT]方括号中的选项参数CASCADE表示连锁方式，执行时将模式下所有基本表、视图、索引等元素全部撤消。RESTRICT表示约束式，执行时必须在SQL模式中没有任何下属元素时方可撤消模式。2.SQL提供的基本数据类型数值型：包括integer、smallint、real、doubleprecision、float(n),numeric(p，d)字符串型：char(n)、varchar(n)，前者是定长，后者为变长串位串型：bit(n),bitvarying(n)，同上。时间型：date、time。3.基本表的创建、修改和撤消基本表的创建：(可理解为建立表结构)CREATTABLESQL模式名.基本表名(列名，类型，……完整性约束...)完整性约束包括主键子句(PRIMARYKEY)、检查子句(CHECK)和外键子句(FOREIGNKEY)。基本表结构的修改ALTERTABLE基本表名ADD/DROP(增加/删除)列名类型名(增加时写出)删除时有子句[CASCADE|RESTRICT]，前者为连锁删除，后者为约束删除，即没有对本列的任何引用时才能删除。基本表的撤消DROPTABLE基本表名[CASCADE|RESTRICT]4.索引的创建和撤消创建：CREATE[UNIQUE]INDEX索引名ON基本表名(列名表[ASC|DESC])撤消：DROPINDEX索引名总结：凡创建都用CREATE,删除都用DROP,改变用ALTER,再跟类型和名字，附加子句很容易了。5.3SQL的数据查询1.SELECT-FROM-WHERE句型SELECT列名表(逗号隔开)FROM基本表或视图序列WHERE条件表达式在这里，重点要掌握条件表达式中各种运算符的应用，如=，,,等算术比较运算符、逻辑运算符AND、OR、NOT、集合成员资格运算符：IN，NOTIN，以及嵌套的SELECT语句的用法要特别注意理解。针对课本的例题和课后习题进行掌握。在查询时，SELECT语句可以有多种写法，如联接查询、嵌套查询和使用存在量词的嵌套查询等。最好都掌握，但是起码应能写出一种正确的查询语句。2.SELECT语句完整的句法：SELECT列名表(逗号隔开)FROM基本表或视图序列[WHERE条件表达式](此为和条件子句)[GROUPBY列名序列](分组子句)[HAVING组条件表达式](组条件子句)[ORDERBY列名[ASC|DESC]..](排序子句)这段关于完整句法的内容能够理解也就问题不大了。3.SELECT语句中的限定这一段内容主要是对SELECT语句进一步使用进行的深入学习，领会下列各种限定的使用目的和方法。要求输出表格中不出现重复元组，则在SELECT后加一DISTINCTSELECT子句中允许出现加减乘除及列名，常数的算术表达式WHERE子句中可以用BETWEEN...AND...来限定一个值的范围同一个基本表在SELECT语句中多次引用时可用AS来增加别名WHERE子句中字符串匹配用LIKE和两个通配符，%和下划线_.查询结果的结构完全一致时可将两个查询进行并(UNION)交(INTERSECT)差(EXCPT)操作查询空值操作不是用='null',而是用ISNULL来测试。集合成员资格比较用IN/NOTIN，集合成员算术比较用元组θSOME/ALL可以用子查询结果取名(表名(列名序列))来作为导出表使用基本表的自然联接操作是用NATURALINNERJOIN来实现的。5.4SQL的数据更新1.数据插入：INSERTINTO基本表名(列名表)VALUES(元组值)或INSERTINTO基本表名(列名表)SELECT查询语句其中元组值可以连续插入。用查询语句可以按要求插入所需数据。2.数据删除：DELETEFROM基本表名[WHERE条件表达式]3.数据修改：UPDATE基本表名SET列名=值表达式,[列名=值表达式...][WHERE条件表达式]5.5视图1.视图的创建和撤消创建：CREATEVIEW视图名(列名表)ASSELECT查询语句撤消：DROPVIEW视图名2.视图的操作查询与关系中一致更新与关系不同如果视图是从多个使用联接操作导出的，不允许对此视图更新如果在导出视图的过程中，使用了分组和聚合函数，不允许对此视图更新。如果视图是从单个关系使用选择、投影操作导出的，并且包含了关系的主健或某个候选健，称此视图为“行列子集视图”，允许对此视图更新。CREATEVIEWS_MAILASSELECTS#,SNAME,AGEFROMSWHERESEX=‘M’INSERTINTOS_MALEVALUES(‘S28’,‘WU’,18)与INSERTINTOSVALUES(‘S28’,‘WU’,18,‘M’)相同3.视图的优点使用视图的优点：视图提供了逻辑数据的独立性简化了用户观点用户只需关心视图的内容，而不必关心构成视图的若干关系的联结、投影操作。能够将对数据库的访问限制在一定范围内，有利于数据安全保护功能。可针对不同的用户定义不同的视图，在视图种只出现用户需要的数据，系统提供视图让用户使用。5.6嵌入式SQL1.SQL语言提供了两种不同的使用方式：交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充。2.嵌入式SQL的一般形式为了区分SQL语句与主语言语句，需要:前缀：EXECSQL结束标志：随主语言的不同而不同以C为主语言的嵌入式SQL语句的一般形式EXECSQLSQL语句;例：EXECSQLDROPTABLEStudent;以COBOL作为主语言的嵌入式SQL语句的一般形式EXECSQLSQL语句END-EXEC例：EXECSQLDROPTABLEStudentEND-EXEC3.DBMS处理宿主型数据库语言SQL的方法预编译修改和扩充主语言使之能处理SQL语句。4.嵌入式SQL语句与主语言之间的通信将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句SQL语句描述性的面向集合的语句负责操纵数据库高级语言语句过程性的面向记录的语句负责控制程序流程5.工作单元之间的通信方式１）SQL通信区向主语言传递SQL语句的执行状态信息主语言能够据此控制程序流程２）共享变量主语言向SQL语句提供参数将SQL语句查询数据库的结果交主语言进一步处理３）游标解决集合性操作语言与过程性操作语言的不匹配6.共享变量什么是共享变量嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据在SQL语句中使用的主语言程序变量简称为共享变量（HostVariable）在SQL语句中使用共享变量和指示变量的方法1）说明共享变量和指示变量BEGINDECLARESECTION..................(说明共享变量和指示变量).........ENDDECLARESECTION7.游标（cursor）为什么要使用游标SQL语言与主语言具有不同数据处理方式SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录。什么是游标游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给共享变量，交由主语言进一步处理。1)说明游标使用DECLARE语句语句格式EXECSQLDECLARE游标名CURSORFORSELECT语句;功能是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。2)打开游标使用OPEN语句语句格式EXECSQLOPEN游标名;功能打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中第一条记录之前。3)移动游标指针，然后取当前记录使用FETCH语句语句格式EXECSQLFETCH[[NEXT|PRIOR|FIRST|LAST]FROM]游标名INTO共享变量[指示变量][,共享变量[指示变量]]...;功能指定方向推动游标指针，然后将缓冲区中的当前记录取出来送至共享变量供主语言进一步处理。NEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式。NEXT：向前推进一条记录PRIOR：向回退一条记录FIRST：推向第一条记录LAST：推向最后一条记录缺省值为NEXT说明：(1)共享变量必须与SELECT语句中的目标列表达式具有一一对应关系。(2)FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理。(3)为进一步方便用户处理数据，现在一些关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长移动游标指针