《[理学]Ch3 关系数据库标准语言 SQL1.doc》由会员分享，可在线阅读，更多相关《[理学]Ch3 关系数据库标准语言 SQL1.doc（63页珍藏版）》请在三一文库上搜索。
1、第三章 关系数据库标准语言 SQL-关系数据库系统的主要功能是通过SQL语言来实现的，因此上本章是学习学习关系数据库的重点之一。通过对本章的学习，你可以了解关系数据库的基本工作原理，对完整性约束将会有更进一步的认识。在学习过程中，我请读者注意以下几个方面。需要牢固掌握SQL语言的特点、体会面向过程的语言与SQL语言的区别以及SQL语言的优点；体会SQL语言之所以为业界认可并成为国际标准的原因。熟练而正确地使用SQL语言完成对数据库的建表、查询、插入、删除、更新操作；对于各种不同样式的查询，尽量多掌握一些，这些技术不仅对你现在有用，将来很长时期内都不会过时。在完成具体的SQL语句时，希望读者能有
2、意识地和关系代数、关系演算、等语言进行比较和类比，了解它们各自的特点。本章的难点在于用SQL语言正确完成复杂查询。另外需要说明的是，教材上讲的是标准SQL语言，而我们在现实工作中总是使用某一种具体的、由某一商家提供的RDBMS产品，这两者之间会有一定的差别。考虑到这一点，我已将本章部分例子翻译成MicroSoft SQL Server 2000上可运行的代码段。在翻译过程中，有些地方作了局部修改，主要是考虑到具体系统的限制或者是我本人想对知识点做一些不太大的扩充，不会影响对原来例子的学习，希望读者理解。如果你有更好的想法想告诉我，请发E-Mail或电话告知，以便在下一次改进。- SQL(Str
3、uctured Query Language)语言是1974年由Boyce和Chamberlin提出的。1975年-1979年IBM公司San Jose Research Laboratory研制了著名的关系数据库管理系统原型System R并实现了这种语言。由于它功能丰富，语言简捷倍受用户及计算机工业界欢迎，被众多计算机公司和软件公司所采用。经各公司的不断修改、扩充和完善，SQL语言最终发展成为关系数据库的标准语言。1986年10月美国国家标准局 (American National Standard Institute ，简称ANSI)的数据库委员会X3H2批准了SQL作为关系数据库语言的
4、美国标准。同年公布了SQL标准文本(简称SQL-86)。1987年国际标准化组织(International Organization for Standardization，简称ISO)也通过了这一标准。此后ANSI不断修改和完善SQL标准，并于1989年公布了SQL-89标准，1992年又公布了SQL-92标准。目前ANSI即将公布正在酝酿新的标准SQL-99，亦称SQL3。自SQL成为国际标准语言以后，各个数据库厂家纷纷推出各自的SQL软件或与SQL的接口软件。这就使大多数数据库均用SQL作为共同的数据存取语言和标准接口，使不同数据库系统之间的互操作有了共同的基础。这个意义十分重大。因此
5、，有人把确立SQL为关系数据库语言标准及其后的发展称为是一场革命。SQL成为国际标准，对数据库以外的领域也产生了很大影响，有不少软件产品将SQL语言的数据查询功能与图形功能、软件工程工具、软件开发工具、人工智能程序结合起来。SQL已成为数据库领域中一个主流语言。这一章详细介绍SQL语言，并进一步讲述关系数据库的基本概念。 3.1 SQL 概 述SQL是一种介于关系代数与关系演算之间的结构化查询语言，其功能并不仅仅是查询。SQL是一个通用的、功能极强的关系数据库语言。3.1.l SQL的特点SQL语言之所以能够为用户和业界所接受，并成为国际标准，是因为它是一个综合的、功能极强同时又简捷易学的语言
6、。SQL语言集数据查询 (Data Query)、数据操纵 (Data Manipulation)、数据定义 (Data Definition)和数据控制 (Data Control)功能于一体，主要特点包括:一、综合统一数据库系统的主要功能是通过数据库支持的数据语言来实现的。非关系模型 (层次模型、网状模型)的数据语言一般都分为模式数据定义语言 (Schema Data Definition Language，简称模式DDL)、外模式数据定义语言(Subschema Data Definition Language，简称外模式DDL或子模式DDL)、与数据存储有关的描述语言(Data Sto
7、re Description Language，简称DSDL:及数据操纵语言 (Data Manipulation Language，简称DML)，分别用于定义模式、外模式、内模式和进行数据的存取与处置。当用户数据库投入运行后，如果需要修改模式，必须停止现有数据库的运行，转储数据，修改模式并编译后再重装数据库，十分麻烦。SQL语言则集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、建立数据库、插入数据、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统的开发提供了良
8、好的环境。用户在数据库系统投入运行后，还可根据需要随时地逐步地修改模式，且并不影响数据库的运行，从而使系统具有良好的可扩展性。另外，在关系模型中实体和实体间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一，查找、插入、删除、修改等每一种操作都只需一种操作符，从而克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。例如，在DBTG中，需要两种插入操作符:STORE用来把记录存入数据库，CONNECT用来把记录插入系值以建立数据之间的联系。二、高度非过程化非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用 SQL语言进行数据操作，只要提出做什
9、么，而无须指明怎么做，因此无需了解存取路径，存取路径的选择以及 SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。三、面向集合的操作方式非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。例如查询所有平均成绩在80分以上的学生姓名，用户必须一条一条地把满足条件的学生记录找出来(通常要说明具体处理过程，即按照哪条路径，如何循环等)。而SQL语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。四、以同一种语法结构提供两种使用方式SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它
10、能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作；作为嵌入式语言，SQL语句能够嵌入到高级语言(例如C，COBOL，FORTRAN，PL/l)程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的做法，提供了极大的灵活性与方便性。五、语言简捷，易学易用SQL语言功能极强，但由于设计巧妙，语言十分简捷，完成核心功能只用了9个动词，如表3.1所示。SQL语言接近英语口语，因此容易学习，容易使用。3.1.2 SQL语言的基木概念SQL语言支持关系数据库三级模式结构，如图3.1所
11、示。其中外模式对应于视图(View)和部分基本表 (Base Table)，模式对应于基本表，内模式对应于存储文件。用户可以用 SQL语言对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系。基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一个(或多个)基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构是任意的，对用户是透明的。视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视
12、图在概念上与基本表等同，用户可以在视图上再定义视图。下面将逐一介绍各 SQL语句的功能和格式。为了突出基本概念和基本功能，略去了许多语法细节。各个DBMS产品在实现标准SQL语言时也各有差别，一般都做了某种扩充。因此，具体使用某个DBMS产品时，还应参阅系统提供的有关手册。3.2 数 据 定 义关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括定义表、定义视图和定义索引，如表3.2所示。视图是基于基本表的虚表，索引是依附于基本表的，因此SQL通常不提供修改视图定义和修改索引定义的操作。用户如果想修改视图定义或索引定义，只能先将他们删
13、除掉，然后再重建。不过有些关系数据库产品如 Oracle允许直接修改视图定义。表3.2 SQL的数据定义语句操作对象操作方式创建删除修改表CREATE TABLEDROP TABLEALTER TABLE视图CREATE VIEWDROP VIEW索引CREATE INDEXDROP INDEX本节只介绍如何定义基本表和索引，视图的概念及其定义方法将在3.4节专门讨论。3.2.1 定义、删除与修改基本表一、定义基本表建立数据库最重要的一步就是定义一些基本表。SQL语言使用CREATE TABLE语句定义基本表，其一般格式如下:CREATE TABLE(列级完整性约束条件,列级完整性约束条件,；
14、其中是所要定义的基本表的名字，它可以由一个或多个属性 (列)组成。建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。例1建立一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student(Sno CHAR(5) NOT NULL UNIQ
15、UE， /*列级完整性约束条件，SnoSname CHAR(2O) UNIQUE， 取值唯一，不许取空值*/Ssex CHAR(1) ，Sage INT，Sdept CHAR(l5);系统执行上面的CREATE TABLE语句后，就在数据库中建立一个新的空的学生表 Student，并将有关学生表的定义及有关约束条件存放在数据字典中。-注：以下是在MicroSoft SQL Server 2000中的几段有关建表的参考代码，考虑到有几个表也是在本章中经常引用的，故在此一并给出。create table student (sno char(5) primary key, /*列级完整性约束条件,将
16、sno定义为主码,但未对此约束命名*/ sname char(20)not null unique,/*列级完整性约束条件,sname不允许取空值且取值唯一*/ ssex char(2) not null, sage int, sdept char(15), constraint ck_ssex check(ssex in (男, 女), /*建立命名为ck_ssex的表级约束*/ constraint ck_sage check(sage0 and sage0 and creadit=0 and grade=100),/*列级命名约束*/ constraint pr_sc primary k
17、ey (sno,cno) /*表级命名约束*/ );create table dept (dno char(5) not null unique, dname nvarchar(30) not null, doffice nvarchar(30) not null );-定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同，例如IBM DB2 SQL主要支持以下等数据类型:二、修改基本表随着应用环境和应用需求的变化，有时需要修改已建立好的基本表，SQL语言用ALTER TABLE语句修改基本表，其一般格式为:ALTER TABLEADD完整性约束 DROP(完整
18、性约束名)MODlFY;其中是要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义 ，包括修改列名和数据类型。例2 向Student表增加入学时间列，其数据类型为日期型。ALTER TABLE Student ADD Scome DATE；不论基本表中原来是否已有数据，新增加的列一律为空值。-注： MicroSoft SQL Server 2000中的参考代码为：/*为表student新增加一列*/alter table student add scome datetime;/*为表student的scome
19、列增加约束*/alter table student add constraint rq_check check(scome 1999-01 );/*注意，在SQL Server 2000中没有date 数据类型*/-例3将年龄的数据类型改为半字长整数ALTER TABLE Student MODIFY Sage SMALLINT:修改原有的列定义有可能会破坏己有数据。-注： MicroSoft SQL Server 2000中的参考代码为：alter table student alter column sdept varchar(50) /*在表student中修改字段sdept的数据类型
20、*/ go-例4 删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname);SQL没有提供删除属性列的语句，用户只能间接实现这一功能，即先把表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重命名为原表名。-注： MicroSoft SQL Server 2000中的参考代码为：alter table student alter column sname char(20)/*对于未命名的约束,可以用对该列再定义的方法取消*/ go alter table student drop constraint ck_sage /*对于已命
21、名的约束,可以用drop命令取消之*/ goalter table sc drop constraint fk_sno /*取消表sc上名为fk_sno的列级约束*/ goalter table sc drop constraint fk_cno /*取消表sc上名为fk_cno的列级约束*/ goalter table course /*约束pr_course是一个主码约束,也可以取消*/ drop constraint pr_course /*取消表course上名为pr_course的表级约束*/ go三、删除基本表当某个基本表不再需要时，可以使用DROP TABLE语句删除它。其一般格
22、式为:DROP TABLE 例5 删除Student表。DROP TABLE Student基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定要格外小心。-注： MicroSoft SQL Server 2000中的参考代码为：/*SQL Srver 2000 有删除列的功能*/alter table dept drop column doffice /*删除一列*/godrop table dept /*删除一个基本表*/-注意:有的系统，如Oracle，删除基本表后建立在此表上的视图定义仍然保留在数据字典中。但是，当用户引用时就报错。3.2
23、.2 建立与删除索引建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。一般说来，建立与删除索引由数据库管理员DBA或表的属主 (即建立表的人)负责完成。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。一、建立索引在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为:CREATE UNIQUECLUSTER INDEXON(，.)其中，是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，可选ASC(升序)或DE
24、SC(降序)，缺省值为ASC。UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。例如，执行下面的CREATE INDEX语句:CREATE CLUSTER INDEX Stusname ON Student(Sname):将会在Student表的Sname(姓名)列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。用户可以在最常查询的列上建立聚簇索引以提高查询效率。显然在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中
25、记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。例6 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX Scno ON SC(Sno ASC，Cno DESC);-注： MicroSoft SQL Server 2000中的参
26、考代码为：/*为表student、表course、表scno分别建立索引*/create unique index stusno on student(sno); /*建立名为stusno的索引*/create unique index stusname on student(sname); /*建立名为stusname的索引*/create unique index coucno on course(cno); /*建立名为coucno的索引*/create unique index scno on sc(sno asc,cno desc); /*建立名为scno的索引*/*索引建立成功后请
27、在企业管理器中双击“数据库”/指定库/表显示出相应表名 */*然后右击某表名/设计表/弹出设计窗口,右击工具栏上右起到数第二个管理索引图标*/*在弹出“属性”对话框后, 观察各个索引的建立情况。*/-二、删除索引索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增加删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。在SQL语言中，删除索引使用DROP INDEX语句，其一般格式为:DROP INDEX；例7 删除Student表的Stusname索引。DROP INDEX Stusname;删除索引时，系统会同时从数据字典中删去
28、有关该索引的描述。-注： MicroSoft SQLServer 2000中的参考代码为：/*删除索引*/drop index student.stusname-3.3 查 询数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为:SELECT ALL |DISTINCT，.FROM，.WHERE GROUP BYHAVINGORDER BYASC|DESC;整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按 SELECT子句中的目标列表达式，选出元
29、组中的属性值形成结果表。如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有 ORDER子句，则结果表还要按的值的升序或降序排序。SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。下面我们仍以学生-课程数据库为例说明SELECT语句的各种用法。学生-课程数据库中包括三个表。学生表:Student(Sno，Sname，Ssex，Sage，Sdept)Student由学号 (Sno)、姓名 (Sname)、性别 (Ssex)、年龄 (Sage)、
30、所在系 (Sdept)五个属性组成，其中Sno为主码。课程表:Course(Cno，Cname，Cpno，Ccredit) Course由课程号(Cno)、课程名(Cname)、选修课号(Cpno)、学分 (Ccredit) 四个属性组成，其中Cno为主码。学生选课表:SC(Sno，Cno，Grade)由学号 (Sno)、课程号 (Cno)、成绩 (Grade)三个属性组成，主码为(Sno，Cno)。3.3.1 单表查询单表查询是指仅涉及一个表的查询。一、选择表中的若干列选择表中的全部列或部分列，这就是投影运算。1.查询指定列在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在SEL
31、ECT子句的中指定要查询的属性。例1 查询全体学生的学号与姓名。SELECT Sno，SnameFROM Student；例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM Student；中各个列的先后顺序可以与表中的顺序不一致。用户可以根据应用的需要改变列的显示顺序。本例中先列出姓名，再列学号和所在系。2.查询全部列将表中的所有属性列都选出来，可以有两种方法。一种方法就是在SELECT关键字后面列出所有列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将指定为*。例3 查询全体学生的详细记录。SELECT * 等价于: SELECT Sno
32、，Sname，Ssex，Sage，SdeptFROM Student； FROM Student；3. 查询经过计算的值SELECT子句的不仅可以是表中的属性列，也可以是表达式。例4 查全体学生的姓名及其出生年份。SELECT Sname，l996-Sage，FROM Student；在例4中，中第2项不是列名，而是一个计算表达式，是用当前的年份 (假设为 1996年)减去学生的年龄，这样，所得的即是学生的出生年份。输出的结果为:Sname 1996-Sage李勇 1976刘晨 1977王敏 1978张立 1977不仅可以是算术表达式，还可以是字符串常量、函数等。例5查询全体学生的姓名、出生年
33、份和所有系，要求用小写字母表示所有系名。SELECT Sname,Year of Birth:,1996-Sage,ISLOWER(Sdept)FROM Student；结果为：Sname Year of Birth: 1996-Sage ISLOWER(Sdept)李勇 Year of Birth: 1976 cs刘晨 Year of Birth: 1977 is王敏 Year of Birth: 1978 ma张立 Year of Birth: 1977 is用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以定义如下列别名
34、：SELECT Sname NAME，Year Of Birth:BlRTH，l996-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENTFROM Student；结果为:Sname BIRTH BIRTHDAY DEPARTMENT李勇 Year of Birth: 1976 cs刘晨 Year of Birth: 1977 is王敏 Year of Birth: 1978 ma张立 Year of Birth: 1977 is二、选择表中的若干元组1.消除取值重复的行两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了。例6 查询选修了课程的学
35、生学号。SELECT SnoFROM SC；假设SC表中有下列数据SnO CnO Grade95001 1 9295001 2 8595001 3 8895002 1 9095002 3 80执行上面的SELECT语句后，结果为:Sno9500195001950019500295002该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTNCT短语:SELECT DISTNCT SnoFrom SC;执行结果为: Sno9500195002如果没有指定DISTNCT短语，则缺省为ALL, 即保留结果表中取值重复的行。SELECT SnoFROM SC；等价于 SELECT
36、 ALL Sno From SC；2.查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3.3所示。表3.3 常用的查询条件 查询条件 谓词 比较=，=，=，!=，!，!(大于)，=(大于等于)，=(小于等于)，!=或(不等于)。有些产品中还包括:!(不大于)，!(不小于)。逻辑运算符NOT可与比较运算符同用，对条件求非。例7 查询计算机系全体学生的名单SELECT SnameFROMS StudentWHERE Sdept=CS；例8 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname，SageFROM StudentWHE
37、RE sage=20；例9 查询考试成绩有不及格的学生的学号。SELECT DISTINCT SnoFROM CouseWHERE Grade60；这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。(2)确定范围谓词BETWEENAND和NOT BETWEEN.AND 可以用来查找属性值在 (或不在)指定范围内的元组，其中BETWEEN 后是范围的下限（即低值），AND后是范围的上限(即高值)。例10 查询年龄在20-23岁 (包括20岁和23岁)之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23；与BETWEENAND相对的谓词是NOT BETWEENAND。例11查询年龄不在20-23岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 2O AND 23；(3)确定集合谓词IN可以用来查找