第8章结构化查询语言SQL

171第8章结构化查询语言SQL8．1SQL概述结构化查询语言SQL(StructuredQueryLanguage)是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的功能极强的关系数据库标准语言。目前，SQL语言已被确定为关系数据库系统的国际标准，被绝大多数商品化关系数据库系统采用。在SQL语言中，指定要做什么而不是怎么做，不需要告诉SQL如何访问数据库，只要告诉SQL需要数据库做什么。可以在设计或运行时对数据控件使用SQL语句。8．1．1SQL的发展历程SQL语言是1974年提出的，由于它功能丰富、使用方式灵活、语言简洁易学等突出优点，在计算机工业界和计算机用户中倍受欢迎。1986年10月，美国国家标准局(ANSI)的数据库委员会批准了SQL作为关系数据库语言的美国标准。1987年6月国际标准化组织(ISO)将其采纳为国际标准。这个标准也称为“SQL86”。随着SQL标准化工作的不断进行，相继出现了“SQL89”、“SQL2”(1992)和“SQL3”(1993)。SQL成为国际标准后，对数据库以外的领域也产生很大影响，不少软件产品将SQL语言的数据查询功能与图形功能、软件工程工具、软件开发工具、人工智能程序结合起来。我们在这里介绍基于SQL89和SQL2的语言使用概貌。8．1．2SQL数据库的体系结构SQL数据库的体系结构基本上也是三级模式结构如图8.1所示。SQL术语与传统的关系模型术语不同。在SQL中，外模式对应于视图，模式对应于基本表，元组称为“行”，属性称为“列”。内模式对应于存储文件。SQL数据库的体系结构具有如下特征：1．一个SQL模式(Schema)是表和约束的集合。2．一个表(Table)是行(Row)的集合。每行是列(Column)的序列，每列对应一个数据项。3．—个表可以是一个基本表，也可以是一个视图，基本表是实际存储在数据库中的表。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图的数据，这些数据仍存放在导出视图的基本表中。因此视图是一个虚表。4．一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表，172一个表可以带若干索引，索引也存放在存储文件中。每个存储文件与外部存储器上一个物理文件对应。存储文件的逻辑结构组成了关系数据库的内模式。SQL用户ViewBaseTableStoredFile图8.1SQL数据库的体系结构5．用户可以用SQL语句对视图和基本表进行查询等操作。在用户看来，视图和基本表是一样的，都是关系(即表格)。6．SQL用户可以是应用程序，也可以是终端用户。SQL语句可嵌入在宿主语言的程序中使用，宿主语言有FORTRAN、COBOL、PASCAL、PL／I、C和Ada等语言；SQL语言也能作为独立的用户接口，供交互环境下的终端用户使用。8．1．3SQL的组成SQL主要分成四个部分：1．数据定义：这一部分也称为“SQLDDL”，用于定义SQL模式、基本表、视图和索引。2．数据操纵：这一部分也称为“SQLDML”。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。3．数据控制：这一部分也称为“SQLDCL”。数据控制包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。4．嵌入式SQL使用：这一部分内容涉及到SQL语句嵌入在宿主语言程序中的使用规则。8．2SQL的数据定义（DDL）SQL的数据定义部分包括对SQL模式(Schema)、基本表(关系，Table)、视图(View)、索引（Index）的创建和撤消操作。用户1用户2用户3用户4视图1视图2基本表1基本表3基本表2基本表4存储文件1存储文件2存储文件3存储文件41738．2．1SQL模式的创建和撤消1．SQL模式的创建在SQL2中，一个SQL模式(即数据库模式)被定义为基本表的集合。一个SQL模式由模式名和模式拥有者的用户名或帐号来确定，并包含模式中每一个元素(基本表、视图、索引等)的定义。创建了一个SQL模式，就是定义了一个存储空间，其语法如下：CREATESCHEMA模式名AUTHORIZATION用户名例如，下面语句定义了教学数据库的SQL模式：CREATESCHEMAST_COURSEAUTHORIZATION李斌该模式名为ST_COURSE，拥有者为李斌。由于“SQL模式”这个名称学术味太重，因此大多数DBMS中不愿采用这个名词，而是采用“数据库”(DATABASE)这个名词。也就是大多数系统中创建SQL模式不是用“CREATESCHEMA…”字样，而是用“CREATEDATABASE…”字样。2．SQL模式的撤消当一个SQL模式及其所属的基本表、视图等元素都不需要时。可以用DROP语句撤消这个SQL模式。DROP语句的语法如下：DROPSCHEMA模式名[CASCADE|RESTRICT]撤消方式有两种：CASCADE(连锁式)方式，执行DROP语句时，把SQL模式及其下属的基本表、视图、索引等所有元素全部撤消；RESTRICT(约束式)方式，执行DRPO语句时，只有当SQL模式中没有任何下属元素时，才能撤消SQL模式，否则拒绝执行DROP语句。例如，要撤消SQL模式ST_COURSE及其下属所有的元素，可用下列语句实现：DROPSCHEMAST_COURSECASCADE8．2．2SQL提供的基本数据类型1．数值型INTEGER长整数(也可写成INT)SMALLINT短整数REAL取决于机器精度的浮点数DOUBLEPRECISION取决于机器精度的双精度浮点数FLOAT（n）浮点数,精度至少为n位数字NUMERIC（p,d）定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字(也可写成DECIMAL(P，d)或DEC(P，d))2．字符串型CHAR（n）长度为n的定长字符串VARCHAR(n)具有最大长度为n的变长字符串3．位串型BIT(n)长度为n的二进制位串174BITVARYING(n)最大长度为n的变长二进制位串4．时间型DATE日期，包含年、月、日，形式为YYYY-MM-DDTIME时间，包含一日的时、分、秒，形式为HH:MM:SS8．2．3基本表的创建、修改和撤消如果在系统中创建了一个SQL模式，那么就可以在该模式中创建基本表。对基本表结构的操作有创建、修改和撤消三种操作。1．基本表的创建句法：CREATETABLESQL模式名.基本表名（列名类型，…完整性约束…）创建基本表，就是定义基本表的结构，需指出它放在哪个模式中，后面为简单起见，模式名省略不写。每个属性的类型可以是基本类型，也可以是用户事先定义的类型。完整性规则主要有三种子句：主键子句(PRIMARYKEY)、检查子句(CHECK)和外键子句(FOREIGNKEY)。例8.l在有关零件、供应商、工程项目的数据库中，有四个关系，其结构如下供应商关系S，可用下列语句创建：CREATETABLES(SNOCHAR(4)NOTNULL，SNAMECHAR(20)NOTNULL，STATUSCHAR(10)，ADDRCHAR(20)，PRIMARYKEY（SNO））；这里定义的关系S有四个属性，分别是供应商号(SNO)、供应商名(SNAME)、状态(STATUS)和地址(ADDR)，属性的类型都是字符型，长度分别是4、20、10和20个字符。主键是供应商编号SNO。在SQL中允许属性值为空值，当规定某一属性值不能为空值时，就要在定义该属性时写上保留字“NOTNUIL”。本例中，规定供应商号和供应商名不能取空值。由于已规定供应商号为主码，所以对属性SNO的定义中的“NOTNULL”可以省略不写。对于基本表P,J,SPJ可以用下列语句创建：CREATETABLEP(PNOCHAR（4）NOTNULL，PNAMECHAR(20)NOTNULL，COLORCHAR(8)，WEIGHTSMALLINT，PRIMARYKEY(PNO))；供应商关系：S(SNO，SNAME，STATUS，ADDR)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO,JNAME，CITY，BALANCE)供应情况关系：SPJ(SNO,PNO,JNO，PRICE，QTY)175CREATETABLEJ(JNOCHAR(4)NOTNULL，JNAMECHAR(20)，CITYCHAR(20)，BALANCENUMERIC(7，2)，PRIMARYKEY(JNO))；CREATETABLESPJ(SNOCHAR(4)NOTNULL，PNOCHAR(4)NOTNULL，JNOCHAR(4)NOTNULL，PRICENUMERIC(7，2)，QTYSMALLINT，PRIMARYKEY(SN0，PNO，JNO)，FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO),CHECK(QTYBETWEEN0AND10000))；上述各属性的含义是：零件号(PNO)、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）、单价(PRICE)、工程项目号(JNO)、工程项目名称(JNAME)、城市(CITY)、余额（BALANCE)、供应数量(QTY)。基本表SPJ的定义中说明了有五个属性，主键是(SNO，PNO，JNO)。还定义了三个外键，并指出外键SNO和基本表S中SNO属性相对应，外键PNO和基本表P中PNO属性相对应，外键JNO和基本表J中JNO属性相对应，此处对应的属性名恰好同名，实际上也可以不同名，只要指出其对应性即可。外键体现了关系数据库的参照完整性。定义中还使用了一个检查子句，指出供应数量QTY在0—10000之间。2．基本表结构的修改基本表建立后，可根据需要对基本表结构进行修改，即增加新的属性或删除原有的属性。（1）增加新的属性句法：ALTERTABLE基本表名ADD新属性名新属性类型例8.2在基本表S中增加一个电话号码(TELE)属性，可用下列语句：ALTERTABLESADDTELECHAR(12)；应注意，新增加的属性不能定义为“NOTNULL”。基本表在增加一个属性后，原有元组在新增加的属性列上的值都被定义为空值(NULL)。（2）删除原有的属性句法：ALTERTABLE基本表名DROP属性名[CASCADE|RESTRICT]此处CASCADE方式表示：在基本表中删除某属性时，所有引用到该属性的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该属性，否则拒绝删除操作。例8.3在基本表S中删除状态(STATUS)属性，并且将引用该属性的所有视图和约束也一起删除，可用下列语句：176ALTERTABLESDROPSTATUSCASCADE；3．基本表的撤消可用“DROPTABLE”语句撤消基本表，其所有数据也丢失了。DROP语句的句法如下：DROPTABLE基本表名（CASCADE|RESTRICT）此处的CASCADE和RESTRICT的语义同前面句法中的语义一样。例8.4需要撤消基本表S。但只有在没有视图或约束引用基本表S中的列时才能撤消，否则拒绝撤消。可用下列语句实现：DROPTABLESRESTRICT；8．2．4视图的创建和撤消在SQL中，外模式一级数据结构的基本单位是视图(VIEW)，视图是从若干基本表和(或)其他视图构造出来的表。这种构造方式采用后面8.3节介绍的SELECT语句实现。在我们创建一个视图时，系统把视图的定义存放在数据字典中，而并不存储视图对应的数据，在用户使用视图时才去找对应的数据。因此，视图被称为“虚表”。1．视图的创建句法：CREATEVIEW视图名(列名表)ASSELECT查询语句例8.5对工程项目零件供应数据库中