第三章 关系数据库标准语言文字SQL

第三章关系数据库标准语言SQL计算机科学技术教研室数据库系统概论AnIntroductiontoDatabaseSystem主讲教师:刘斌[目的要求]理解SQL语言的概念、特点、功能和使用方法；掌握SQL基本语句的使用。重点掌握查询语句的使用，包括连接查询和嵌套查询；了解嵌入式SQL和动态SQL技术。[基本内容]SQL概述；数据定义；查询；数据更新；视图；数据控制；嵌入SQL；动态SQL。[重点难点]重点:连接查询和嵌套查询。难点:EXISTS谓词的使用。[课时安排]建议：10学时。结构化查询语言SQL(StructuredQueryLanguage)是一种介于关系代数与关系演算之间的语言。功能包括：数据定义、数据查询、数据操纵和数据控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。3.1SQL概述3.1.1SQL的产生与发展SQL是在1974年由Boyce和Chamberlin提出,并在IBM的SystemR上实现。1986年10月成为美国关系数据库语言的标准1987年成为ISO标准标准大致页数发布日期SQL/861986.10SQL/89(FIPS127-1)120页1989年SQL/92(简称SQL2)622页1992年SQL991700页1999年SQL20033600页2003年SQL标准的制定使得几乎所有的数据库厂家都采用SQL语言作为其数据库语言。但各家又在SQL标准的基础上进行扩充，形成自己的语言。SQLServer扩充SQL标准称为Transact-SQL，简称T-SQL。3.1.2SQL的特点一、综合统一①语言功能的一体化:SQL集数据定义DDL、数据操纵DML、数据控制DCL功能为一体。并且它不严格区分数据定义和数据操纵，在一次操作中可以使用任何语句。SQL语言具有数据定义、数据操纵和数据控制功能，这为数据库应用开发提供了良好的环境。用户在数据库行以后，可以修改模式，不影响数据库的运行。不象非关系数据库系统，修改模式需要先停止数据库运行，转储数据，修改模式，编译修改了的模式以后再重新装入数据库，使得修改模式非常不方便。②模式结构的一体化:在关系模型中实体和实体间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一，即对实体及实体间的联系的每一种操作（如：查找、插入、删除、修改）都只需要一种操作符。二、高度非过程化非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径（如：早期的FoxPro）。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，简化了编程的复杂性，提高了数据的独立性。一条SQL语句可以完成过程语言多条语句的功能。三、面向集合的操作方式①非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。例如：查询所有平均成绩在80分以上的学生姓名，用户必须说明完成该请求的具体处理过程，即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。②SQL语言在元组集合上进行操作,操作结果仍是元组集合。查找、插入、删除和更新都是对元组集合操作。四、统一的语法结构、两种操作方式①SQL语言既是自含式语言，又是嵌入式语言。且在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。②作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。③作为嵌入式语言，SQL语句能够嵌入到高级语言（例如：VC、VB、Delphi、Java\FORTRAN）程序中，供程序员设计程序时使用。五、语言简捷，易学易用SQL语言功能极强，但由于设计巧妙，语言十分简洁。完成核心功能只用了9个动词.表3.1SQL语言的动词SQL功能动词数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT，UPDATEDELETE数据控制GRANT，REVOKE3.1.3SQL的基本概念SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式SQL支持关系数据库三级模式结构SQL的基本概念（续）基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图视图分类:(1)普通视图是虚表,仅在数据字典中有逻辑定义,可根据其它基本表导出,但不存在数据库中。可象基本表一样参加各种数据操作。(2)临时视图在复杂的查询中，可以将查询中相对对立的部分作为查询的中间结果，定义为临时视图。其功能和普通视图一样，但是其定义仅用于符在复杂查询的定义中，查询结束，视图消失。SQLServer2000中的基本表和视图①基本表列表②基本表的数据③基本表的结构④视图列表⑤视图的定义和数据3.3数据定义表3.2SQL的数据定义语句操作方式操作对象创建删除修改模式CREATESCHEMADROPSCHEMA表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX关系数据库系统支持三级模式结构，模式、外模式、内模式对应的基本对象分别是表、视图、索引。SQL的数据定义功能:模式定义、表定义、视图和索引的定义SQL通常不提供修改模式﹑视图﹑索引的操作,如果要修改这些对象，只能先删除他们再重新定义。数据类型SQL语言与其它计算机语言一样，有自己的词法和语法，并对操作的变量指定数据类型。关系模式中所有的关系属性都必须指定数据类型，不同的系统支持的数据类型稍有差别。选用哪种数据类型取值范围要做哪些运算数据类型含义CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT长整数（也可以写作INTEGER）范围:-2147483648到2147483647SMALLINTBIT短整数,范围从-32768到327671位的整型,只能表示0或1，一般不用来存储整数，而用来表示真假逻辑关系（TRUE/FALSE）。NUMERIC(p，d)定点数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字REAL取决于机器精度的浮点数,范围在-3.40E+38到3.40E+38之间，存储使用4个字节，可以精确到第7位的正负小数。在SQLServer中，real的同义词为float(24)。DoublePrecision取决于机器精度的双精度浮点数FLOAT(n)浮点数，精度至少为n位数字DATE日期，包含年、月、日，格式为YYYY-MM-DDTIME时间，包含一日的时、分、秒，格式为HH:MM:SS3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除模式定义语句：CREATESCHEMA模式名AUTHORIZATION用户名如果没有指定模式名，那么模式名隐含为用户名创建模式的用户必需有DBA权限，或获得DBA授权创建模式的权限。一.定义模式模式就是一个数据库的命名空间,可以在其中进一步定义该模式包含的数据库对象,如:表,视图,索引等.[例1]定义一个学生-课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户WANG定义了一个模式S-T[例2]CREATESCHEMAAUTHORIZATIONWANG；模式名隐含为用户名WANG在CREATESCHEMA中可以接受CREATETABLE，CREATEVIEW和GRANT子句。CREATESCHEMA模式名AUTHORIZATION用户名[表定义子句|视图定义子句|授权定义子句][例3]CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT，COL2INT，COL3CHAR(20)，COL4NUMERIC(10，3)，COL5DECIMAL(5，2))；为用户ZHANG创建了一个模式TEST，并在其中定义了一个表TAB1。二、删除模式CASCADE(级联)删除模式的同时把该模式中所有的数据库对象全部删除RESTRICT(限制)如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。DROPSCHEMA模式名CASCADE|RESTRICT[例4]DROPSCHEMAZHANGCASCADE；删除模式ZHANG同时该模式中定义的表TAB1也被删除补充材料：SQLSERVER2000中建立数据库①用企业管理器创建数据库：进入企业管理器（EnterpriseManager）界面后，操作步骤如下：选择要建立的数据库的服务器节点，双击并展开该节点，·选中数据库节点，打开操作菜单或点击鼠标右键，在弹出的快捷菜单中选择新建数据库命令在弹出的数据库属性中，先输入数据库名称，再切换到数据文件输入数据库名称在数据文件页输入正确的数据文件名字、位置、大小。SQLServer默认情况下自动为用户输入的文件名后面增加下划线和Data字样，并设置相应的文件扩展名。可以增加一个或多个数据库文件。并分别为每个数据库文件设置存放路径、初始大小和文件组等在左下角的文件属性栏中可以选择该数据库文件的增长情况。当存储的数据容量超过初始设置的数据库大小时，SQLServer会自动增加容量。这里有两种方法进行自动增加。一种是按兆字节，例如输入1，一次增加1MB。另一种方法是按百分比，例如输入10，一次增加当前数据库容量的10%。同时还可以设置数据文件的最大容量。在右下角最大文件大小中设置。可以设置数据文件的容量无限大，也可以把数据文件限制在某一范围之内。接下来切换到事务日志页，在这里设置日志文件与前面类似，如图。单击确定按钮，就可以将新的数据库添加到数据库服务器中了。删除数据库②用创建数据库向导创建数据库：在企业管理器中的工具栏中选择向导，如图，选择创建数据库向导。大部分选项与上面用企业管理器创建数据库类似。③用T-SQL语句创建数据库：用T-SQL语句创建数据库，可以在程序中使用，或在查询分析器中使用例:在查询分析器中创建数据库示例如图，其中相应的c:\mssql\data目录一定要事先存在。创建数据库的T-SQL语句如下3.3数据定义3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除在关系数据库中，每一个关系都体现为一张表。表是用来存储数据和操作数据的逻辑结构，关系数据库中的所有数据都表示为表格的形式。表的结构包括列(column)和行(row)。列描述了数据的属性，习惯称之为域。行是组织数据的单位，每一行是一条独立的数据记录，而每一列表示记录中的一种元素。在使用数据库时，绝大多数时间是在使用表。基本表3.3.2基本表的定义、删除与修改一、定义基本表CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]……[，表级完整性约束条件]）；注1：列级完整性约束通常包括：是否为空、缺省值。注2：表级完整性约束通常包括：主键、外键、唯一性、检查。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。[例5]建立“学生”表Student，学号是主码，姓名取值唯一。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，/*列级完整性约束条件*/SnameCHAR(20)UNIQUE，/*Sname取唯一值