空间数据库课件：第三章 关系数据库标准语言SQL3-1

1、第三章 关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图3.6 数据控制3.1 SQL概述SQL(Structured Query Language)的发展1974年，由Boyce和Chamberlin提出19751979，IBM San Jose Research Lab的关系数据库管理系统原型System R实施了这种语言SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)、SQL99(SQL3)、SQL2003(SQL4) 、SQL2006、SQL2008、SQL2011（ISO/IEC 9075:2011）3.1 SQL

2、概述标准 大致页数 发布日期SQL/86 1986.10SQL/89(FIPS 127-1) 120页 1989年SQL/92 622页 1992年SQL99 1700页 1999年SQL2003 3600页 2003年SQL2011 2011年3.1 SQL概述SQL2008 :ISO/IEC 9075(1-4,9-11,13,14)ISO/IEC 9075-1:2008 Framework (SQL/Framework)：72pISO/IEC 9075-2:2008 Foundation (SQL/Foundation)：1317pISO/IEC 9075-3:2008 Call-Leve

3、l Interface (SQL/CLI)：378pISO/IEC 9075-4:2008 Persistent Stored Modules (SQL/PSM):161pISO/IEC 9075-9:2008 Management of External Data (SQL/MED):451pISO/IEC 9075-10:2008 Object Language Bindings (SQL/OLB):376pISO/IEC 9075-11:2008 Information and Definition Schemas (SQL/Schemata):278pISO/IEC 9075-13:2

4、008 SQL Routines and Types Using the Java TM Programming Language (SQL/JRT):186pISO/IEC 9075-14:2008 XML-Related Specifications (SQL/XML):411p3.1 SQL概述现状大部分产品都支持SQL，成为操作数据库的标准语言商业数据库软件对SQL的支持程度不同交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释SQL的形式交互式3.1 SQL概述SQL的特点1. 综合统一 SQL可用于所有用户的DB活动模型，包括系统

5、管理员、数据库管理员、应用程序员、决策支持系统人员及许多其它类型的终端用户。 SQL为许多任务提供了语句，包括：查询数据在表中插入、修改和删除记录建立、修改和删除数据对象控制对数据和数据对象的存取保证数据库一致性和完整性SQL的特点2. 高度非过程化SQL是一个非过程化的语言，因为它一次处理一个记录，对数据提供自动导航。SQL允许用户在高层的数据结构上工作，而不对单个记录进行操作，可操作记录集。所有SQL语句接受集合作为输入，返回集合作为输出。SQL的集合特性允许一条SQL语句的结果作为另一条SQL语句的输入。SQL不要求用户指定对数据的存放方法。这种特性使用户更易集中精力于要得到的结果。SQ

6、L的特点3. 面向集合的操作方式 非关系数据模型采用的是面向记录的操作方式、操作对象是一条记录。而SQL语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。4. 以同一种语法结构提供两种使用方法 SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端健盘上直接键入SQL命令对数据库进行操作:作为嵌入式语言，SQL语句能够嵌入到高级语言程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。5. 语言简洁，易学易用3.1 SQL概述SQL的基

7、本概念SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模 式内模式SQL支持关系数据库三级模式结构SQL的基本概念基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图学生-课程 数据库学生-课程模式 S-T : 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname

8、,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade)Student表学 号Sno姓 名Sname性 别 Ssex年 龄 Sage所 在 系 Sdept200215121200215122200215123200515125李勇刘晨王敏张立男女女男20191819CSCSMAISCourse表课程号Cno课程名Cname先行课Cpno学分Ccredit1234567数据库数学信息系统操作系统数据结构数据处理PASCAL语言516764243424SC表学 号Sno 课程号 Cno 成绩 Grade 200215121 200215121 200215121 2002151

9、22 200215122 1 2 3 2 3 92 85 88 90 80第三章 关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图3.6 数据控制3.2 数 据 定 义 3.2.1 模式的定义与删除定义模式例1定义一个学生-课程模式S-T CREATE SCHEMA “S-T” AUTHORIZATION WANG; 为用户WANG定义了一个模式S-T例2CREATE SCHEMA AUTHORIZATION WANG； 隐含为用户名WANG如果没有指定，那么隐含为一、定义模式（续）定义模式实际上定义了一个命名空间(Namespace)在这个空

10、间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。 CREATE SCHEMA AUTHORIZATION |定义模式（续）例3CREATE SCHEMA TEST AUTHORIZATION ZHANG CREATE TABLE TAB1(COL1 SMALLINT， COL2 INT， COL3 CHAR(20)， COL4 NUMERIC(10，3)， COL5 DECIMAL(5，2) )； 为用户ZHANG创建了一个模式TEST，并在其中定义了一个表TAB1。二、删除模

11、式DROP SCHEMA CASCADE(级联) 删除模式的同时把该模式中所有的数据库对象全部删除RESTRICT(限制) 如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。 当该模式中没有任何下属的对象时 才能执行。例4 DROP SCHEMA ZHANG CASCADE； 删除模式ZHANG 同时该模式中定义的表TAB1也被删除3.2.2 创建基本表CREATE TABLE （ ， ， ）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件 3.2.2 创建基本表例4 建立一个“学生”表Stud

12、ent，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student ( Sno CHAR(9) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(2)， Sage SMALLINT， Sdept CHAR(20)； 3.2.2 创建基本表列列名字母开头，可含字母、数字、$、_列类型Char(n)【定长字符串类型】Varchar2(n)【可变长字符串类型】Number【数值型】Date【日期时间型】3.2.2 创建基本表常用

13、完整性约束主码约束： PRIMARY KEY唯一性约束：UNIQUE非空值约束：NOT NULL参照完整性约束:REFERENCES检查约束（Check）：用户自定义完整性这些约束既可以定义在列上，也可以定义在基本表之上列约束：在每列后定义，只对当前列有效表约束：在全部列定义后定义，可定义多个列上的约束（1）定义列完整格式 DEFAULT NOT NULL Create Table Student( S# Varchar2(10) Constraint PK Primary Key, Sname Varchar2(20) NOT NULL, Age Number(3), Sex Char(1)

14、 DEFAULT F)NOT NULL表示不允许空值，实际上是Check约束的简化A）默认值当往表中插入一条新记录时，如果某列上有默认值，并且新记录中未指定该列的值，则自动以默认值填充Insert Into Student(s#,sname,age) Values(001,John,20)S#SnameAgeSex001John20F自动以默认值填充插入一条新记录B）列约束必须更在每个列定义后定义只对当前列有效可以使用四种类型的约束格式Constraint 例S# char(n) Constraint PK_Student Primary KeyS# char(n) Primary Key （

15、2）定义约束列约束：在每个列后定义，可以有多个约束子句不能定义多个列上的约束表约束：在全部列定义完成后定义，可以有多个约束子句多个列上的约束必须使用表约束单列上的约束可以用列约束，也可用表约束四种约束都可以作为列约束或表约束A）列约束和表约束举例Create Table Student( S# Varchar2(10) Constraint PK_S Primary Key, Sname Varchar2(20), Age Number(3) Constraint CK_S Check (age14 and age15)Constraint CK_S2 Check (Sex In (M,F)C

16、onstraint CK_SC Check (Score=0 and Score=100)(3)模式与表每一个基本表都属于某一个模式一个模式包含多个基本表定义基本表所属模式方法一：在表名中明显地给出模式名 Create table “S-T”.Student（.）; /*模式名为 S-T*/Create table “S-T”.Cource（.）;Create table “S-T”.SC（.）; 方法二：在创建模式语句中同时创建表 方法三：设置所属的模式 模式与表（续）创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式 RDBMS会使用模式列表中第一

17、个存在的模式作为数据库对象的模式名 若搜索路径中的模式名都不存在，系统将给出错误 显示当前的搜索路径： SHOW search_path; 搜索路径的当前默认值是：$user， PUBLIC 模式与表（续）DBA用户可以设置搜索路径，然后定义基本表 SET search_path TO “S-T”，PUBLIC； Create table Student（.）; 结果建立了S-T.Student基本表。RDBMS发现搜索路径中第一个模式名S-T存在，就把该模式作为基本表Student所属的模式。3.2.3 修改基本表ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN

18、 ；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTER COLUMN子句：修改列名和数据类型3.2.3 修改基本表例6 向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATE；不论基本表中原来是否已有数据，新增加的列一律为空值。删除属性列 直接/间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名 直接删除属性列:(新)例：ALTER TABLE Student Drop column Scome； 3.2.3 修改基本表例7 将年龄的数据类

19、型改为短整型。 ALTER TABLE Student ALTER COLUMN Sage SMALLINT；注：修改原有的列定义有可能会破坏已有数据例8 删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname)；3.2.4删除基本表 DROP TABLE RESTRICT| CASCADE；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除 删除基本表(续) 例9 删除Student表 DROP TAB

20、LE Student CASCADE ;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除 删除基本表（续）例10若表上建有视图，选择RESTRICT时表不能删除 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS； DROP TABLE Student RESTRICT; -ERROR: cannot drop table Student because other objects depend on it删除基本表（续）例11如果选择CASCADE时可以删除表，视图也自

21、动被删除 DROP TABLE Student CASCADE; -NOTICE: drop cascades to view IS_StudentSELECT * FROM IS_Student;-ERROR: relation IS_Student does not exist 删除基本表（续）DROP TABLE时，SQL99 与 3个RDBMS的处理策略比较R表示RESTRICT , C表示CASCADE 表示不能删除基本表，表示能删除基本表，保留表示删除基本表后，还保留依赖对象 3.2.5 建立与删除索引 建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需

22、要建立有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE维护索引 DBMS自动完成使用索引 DBMS自动选择是否使用索引以及使用哪些索引一、建立索引 语句格式CREATE UNIQUE CLUSTER INDEX ON (, )；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引一、建立索引 例8 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Stud

23、ent表按学号升序建唯一索引，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)； 一、建立索引唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束一、建立索引聚簇索引建立聚簇索

24、引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例：CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放 一、建立索引在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作 二、删除索引 DROP INDEX ；删除索引时，系统会从数据字典中删去有关该索引的描述。 例

25、7 删除Student表的Stusname索引。DROP INDEX Stusname；第三章 关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图3.6 数据控制3.3 查 询 3.3.1 概述3.3.2 单表查询3.3.3 连接查询3.3.4 嵌套查询3.3.5 集合查询3.3.6 小结 3.3.1 概述 语句格式SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；3.3.1 概述语句格式SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表

26、或视图)WHERE子句：指定查询条件GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDER BY子句：对查询结果表按指定列值的升序或降序排序 3.3.2 单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组 一、查询列 例1 查询全体学生的学号与姓名。SELECT Sno，SnameFROM Student； 例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM

27、 Student；例3 查询全体学生的详细记录。SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或SELECT *FROM Student； 查询经过计算的值 SELECT子句的为表达式算术表达式字符串常量函数列别名等 例4 查全体学生的姓名及其出生年份。SELECT Sname，2000-SageFROM Student；输出结果： Sname 2004-Sage - - 李勇 1984 刘晨 1985 查询经过计算的值例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname，Year of Birth:

28、，2000-Sage， LOWER(Sdept)FROM Student； 输出结果： Sname Year of Birth: 2004-Sage LOWER(Sdept) - - - - 李勇 Year of Birth: 1984 cs 刘晨 Year of Birth: 1985 is 王敏 Year of Birth: 1986 ma 张立 Year of Birth: 1985 is 使用列别名例5.1 使用列别名改变查询结果的列标题 SELECT Sname NAME，Year of Birth: BIRTH， 2004-Sage BIRTHDAY，LOWER(Sdept) DE

29、PARTMENT FROM Student；输出结果： NAME BIRTH BIRTHDAY DEPARTMENT - - - - 李勇 Year of Birth: 1984 cs 刘晨 Year of Birth: 1985 is 王敏 Year of Birth: 1986 ma 张立 Year of Birth: 1985 is二、选择表中的若干元组 1.消除取值重复的行:使用DISTINCT短语例6 查询选修了课程的学生学号。(1) SELECT Sno FROM SC;或(默认 ALL) SELECT ALL Sno FROM SC;结果： Sno 200215121200215

30、121200215121200215122200215122 (2) SELECT DISTINCT Sno FROM SC;结果：Sno 200215121200215122例题（续）注意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECT DISTINCT Cno，DISTINCT GradeFROM SC;正确的写法 SELECT DISTINCT Cno，Grade FROM SC;2.查询满足条件的元组WHERE子句常用的查询条件查 询 条 件谓 词比 较=，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEEN AND，NOT B

31、ETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件（逻辑运算）AND，OR，NOT表3.4 常用的查询条件(1) 比较大小例7 查询计算机科学系全体学生的名单。 SELECT Sname FROM Student WHERE Sdept=CS； 例8 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname，Sage FROM Student WHERE Sage 20；例9 查询考试成绩有不及格的学生的学号。 SELECT DISTINCT Sno FROM SC WHERE Grade60； （

32、2）确定范围谓词: BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的 姓名、系别和年龄 SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 例11 查询年龄不在2023岁之间的学生姓名、系别和年龄 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23； (3) 确定集合谓词：IN , NOT IN 例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生

33、的姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS );(4)字符匹配 NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时， 可以用 = 运算符取代 LIKE 谓词 用 != 或 运算符取代 NOT LIKE 谓词 当用户要查询的字符串本身就含有 % 或 _ 时，要使用E

34、SCAPE 短语对通配符进行转义。通配符% (百分号) 代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串_ (下横线) 代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串字符匹配(续)1）匹配串为固定字符串例14 查询学号为200215121的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 200215121；等价于： SELECT * FROM Student WHERE Sno = 200215121 ；字符匹

35、配（续） 2) 匹配串为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%；例16 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_；字符匹配（续）例17 查询名字中第2个字为阳字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _阳%；例18 查询所有不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM

36、Student WHERE Sname NOT LIKE 刘%；字符匹配（续）3) 使用换码字符将通配符转义为普通字符 例19 查询DB_Design课程的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ；例20查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ； ESCAPE 表示“ ” 为换码字符 (5) 涉及空值的查询 使用谓词 IS NULL 或 IS NOT NULL “

37、IS NULL” 不能用 “= NULL” 代替例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；例22 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；(6) 多重条件查询用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND 例23 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；三、对查询结果排序 使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示 三、