数据库概论-关系数据库标准语言.ppt

《数据库概论-关系数据库标准语言.ppt》由会员分享，可在线阅读，更多相关《数据库概论-关系数据库标准语言.ppt（183页珍藏版）》请在三一办公上搜索。

1、结构化查询语言SQL(Structured Query Language)是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言，目前已成为关系数据库的标准语言。,第四章 关系数据库标准语言,SQL的历史 SQL语言最初是由IBM公司1974年在原型的关系数据库管理系统SYSTEM R上开发的语言。1979年，ORACLE公司首家推出商业上可执行的SQL。如今，SQL成为国际上标准的数据库存取语言。,SQL的标准 美国国家标准学会（ANSI)将SQL作为关系数据库管理系统的标准语言，并且定义在ANSI x3.125-1989”具

2、有完整性增强特征的数据库语言SQL”文档中，即ANSI SQL89。1986.10 ISO SQL86 1987.6-1989.4 ISO SQL89 1992年 ISO SQL92 SQL2 2000年 ISO SQL99 SQL3 2003年 SQL2003，包含了XML相关内容 2006年 SQL2003，定义了SQL与XML(包含XQuery)的 关联应用,4.1 SQL概述及其特点 1.SQL概述 按其功能分为四大类,其作用是建立和使用数据库。,SQL 的特点,综合统一的一体化的特点 高度非过程化 面向集合的操作方式，语言功能强 统一的语法结构，两种使用方式 语言简洁，易学易用 提供

3、数据控制功能 提供有数据视图的数据结构 所有关系数据库的公共语言,2.SQL数据库的体系结构 按支持SQL的数据库管理系统可称其SQL数据库，其结构基本上采用三级结构，但所用术语与传统关系模型的术语有些不同。,用户1,用户2,用户3,用户4,视图3,视图1,视图2,基本表1,基本表2,基本表3,基本表4,存储文件1,存储文件1,SQL用户,视图,基本表,存储文件,外模式,模式,内模式,表,说明：一个SQL数据库是表的汇集,它用一个或若干个SQL模式定义；基本表都是一个实际存在的关系，由行集构成，一行是列的序列，每列对应一个数据项；一个表或者是一个基本表，或者是一个视图；每个存储文件与外部存储上

4、一个物理文件对应；用户可以用SQL语句对视图和基本表进行查询操作；SQL用户可以应用程序，也可以是终端用户。,格式：SELECT ALL|DISTINCT，FROM，WHERE GROUP BY HAVINGORDER BY ASC|DESC；描述：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP BY子句则将结果按的值进行分组，该属性列相等的元组为一个组。通常会在每组中作用集函数。如果GROUP子句带有HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER B

5、Y子句，则结果表还要按的值升序或降序排列。,4.2 SQL查询,1.选择表中的若干列 选择表中的指定列；选择表中的所有列；结果列的显示顺序；使用列表达式；SELECT中的可以是表中的列，也可以是表达式，包括算术表达式、字符串常数、函数等。（字符串用单引号定界）。使用的列的别名:列名 AS 列的别名,单表查询 仅涉及一个表的查询,例1：求全体学生的学号、姓名。例2：求全体学生的详细信息。例3：求学生学号和学生出生的年份（经过计算的值）。例4：求学生的学号和出生年份，显示时 使用别名 Student_No 和 Birth_Day。,2.选择表中的若干元组 消除取值重复行 查找相异的行：在SELEC

6、T语句中使用关键字 DISTINCT 原本不完全相同的元组，经过向某些列投影操作后，可能变成相同的行了。如果想去掉结果表中的重复行，必须指定 DISTINCT 短语，没有指定，则使用用缺省值 ALL，意为保留结果表中所有的行。例5：求选修了课程的学生学号。,查询满足条件的元组 可以通过WHERE子句来实现。WHERE常用的查询条件如下表所示。,大小比较 条件表达式的形式如下：比较运算符 列名|常量|表达式 其中：字符串常量和日期常量要用一对 单引号括起来。例6：求年龄大于等于20岁的学生姓名和年龄。,BETWEEN 确定范围 谓词BETWEEN AND与NOT BETWEEN AND的一般格式

7、为：NOT BETWEEN AND 查询属性值在（或不在）指定范围内的元组 其中：为范围的下限（低值），为范围的上限（高值）。选出的元组包括边界与,要求a=b，等价于 a=属性列=b。,例7：求年龄在20岁与22岁之间（包括20岁和 22岁）的学生学号和年龄。例8：求年龄不在20岁与22岁之间的学生学号 和年龄。,IN 确定集合 谓词IN可以用来查找属性值属于指定集合的元组。NOT IN 谓词IN实际上是一系列谓词OR的缩写。所起的作用就是检查列值是否等于它后面括弧内的一组值中某一个。如果等于其中某一个值，则其结果为真，否则其结果为假。NOT IN 表示与IN完全相反的含义。,例9：求在下列各

8、系的学生：数学系（MA）、计算机科学系（CS）。例10：求不是数学系、计算机科学系的学生的 信息。,LIKE 匹配查询或模糊查询 谓词LIKE的一般格式为：NOT LIKE 查询指定的属性列与相匹配的元组。注释：可以是一个完整的字符串，也可以含有通配符的字符串。通配符包括：%（百分号）：代表任意长（长度为0）字符串。_（下划线）：代表任意单个字符 所有其它字符：只代表自己,例11：求姓名是以字母马打头的学生信息。例12：求姓名中含有字母马的学生信息。例13：求姓名长度至少是三个字符且倒数第 三个字符必须是字母马的学生信息。,使用换码字符将通配符转义为普通字符 例14：查询以DB_开头，且倒数第

9、3个字符为i的课程的详细情况。ESCAPE 表示“”为换码字符,NULL 涉及空值查询 NULL表示空值。空值是一种不存在的或者不知道、不可用的数据。列名 NOT IS NULL 这里的IS不能用=替代。数据库表的行中，未被赋值的字段自动被认为是空值。0长度的字符串自动解释为空值。空值的赋值：把连续两个单引号赋值给它；把空值常量NULL赋值给它。,例15：求缺少学习成绩的学生的学号和课程号。（查询含有空值的行）。SELECT SNO,CNO FROM SCORE WHERE GRADE IS NULL;GRADE=NULL;,多重条件 当查询条件涉及到多个时，可将若干条件通过逻辑运算符构成一个

10、更复杂的条件进行查询。可以使用三种逻辑运算符：NOT 逻辑非；AND 逻辑与；OR 逻辑或,运算符的优先顺序如下：=!=NOT=NOT BETWEENAND NOT IN NOT LIKE IS NOT NULL NOT AND OR 用户可以用括号()改变优先级。,例16：求计算机系(CS)或数学系(MA)，年龄 大于20岁的学生姓名、系和年龄。例17：求选修课程(001)或课程(002)，成绩在 85和95之间，学号为96xxx的学生的学号、课程与成绩。,3.控制行的显示顺序 ORDER BY 子句中，列名表2指出在显示查询结果时，数据按指定的列排序。ASC 升序排序（默认值）；DESC

11、降序排序 可以选择多列进行排序；ORDER BY子句中可以使用表达式；ORDER BY 子句必须是放在SELECT命令中影响选取行的所有子句的后面；如果排序列含有空值时，DESC排序时，含有空值的行位于最前面；ASC排序时，含有空值的行位于最后面。,例18：求选修课程(001)或课程(002)学生的 学号、课程号与成绩，结果按课程号升 序、成绩降序排序。,4.组函数 SQL提供的五种组函数 COUNT()计算所选数据（记录）的个数 SUM()计算某一数值列的和 AVG()计算某一数值列的平均值 MAX()求(字符、日期、数值列)的最大值 MIN()求(字符、日期、数值列)的最小值,例19：求学

12、生总人数。例20：求选修了课程的学生人数。例21：求计算机系学生的平均年龄。例22：求选修了课程005的最高、最低与 平均成绩。,5.分组查询 实现行的分组和分组统计。在对表格进行操作时，要求将记录按某个或某几个字段上相同的值分成组，然后再对组进行相应的操作，称作分组查询。,设有学生关系,分组后的平均年龄,按系、班级分组计算学生的平均年龄,S,分组后的平均年龄,按系、班级分组计算学生的平均年龄,GROUP BY 子句 分组查询是通过 GROUP BY 子句实现的。将查询结果表按某一列或多列值分组，值相等的为一组；当对多列进行分组时，所有的组函数统计都是对最后的分组列进行的；对查询的结果分组的目

13、的是为了细化统计函数的作用对象；,如果未对查询分组，组函数将作用于整个查询结果；如果分组后组函数将作用于每一个组，即每一个分组都有一个组函数；在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除组函数外），均应该包含在GROUP BY 子句中，即所选项与分组的一致性。,例23：求各门课程的平均成绩与总成绩。例24：求各系中各个班级的人数及平均年龄。,例25(?）：SELECT SNAME,SDEPT,COUNT(*)FROM STUDENT GROUP BY SDEPT;例26(?）：SELECT SAGE FROM STUDENT GROUP BY SNO;,HAV

14、ING 子句 如果分组后还要按一定的条件对这些分组进行筛选，只输出满足条件的组，则应该使用HAVING短语指定筛选条件。HAVING 子句用来选择满足条件的分组。,WHERE与HAVING的区别：作用对象不同 WHERE 作用于基本表或视图，从中选择满足条件的元组；HAVING短语作用于组，从中选择满足条件的组。,例27：求学生人数不足百人的系号及其相应 学生人数的。例28：求各系中年龄大于20岁的除1班之外的 各班级人数。,SELECT FROM WHERE GROUP BY HAVING ORDER BY,投影连接选取分组去组排序,小结,连接查询是通过各表中相应列的公共数据把一个表中的某些

15、行与另一个表中的某些行连接起来。表之间的联系是通过表的字段值来体现的，这种字段通常称为连接字段。连接字段就是在两个表中都包含的一个公共字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来。,连接查询 涉及两个以上的表,1.无条件连接 在连接操作中，如果不用连接条件，则称为无条件连接。无条件连接将产生大量的行，当n1,n2,nn 这n个表进行连接时，产生的记录是n1*n2*nn。例：SELECT SNO,SNAME,CNO,GRADE FROM STUDENT,GRADE;不带WHERE子句的连接语句是笛卡儿乘积。,2.等值连接与非等值连接 连接条件的一般格式是：.其中：比较运算符主要

16、有：=、=、.：指明是哪个表的哪个列,限定符,连接查询一定要带上连接条件WHERE子句。,注意：连接条件中的各连接字段类型必须是可比较的（自然连接）。执行过程：首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接形成结果表中的一个元组。表2全部找完后，再找表1中的第2个元组，然后再从头扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接形成结果表中的一个元组。重复执行，直到表1中的全部元组都处理完毕为止。,例29：求学生以及其选修课程的情况。SELECT STUDENT.*,SCORE.*FROM ST

17、UDENT,SCORE WHERE STUDENT.SNO=SCORE.SNO;,连接条件,SNO：连接字段,例30：查询学生信息以及其选修课程的课程号 和成绩，但查询结果中只能有一个SNO 字段。例31：求选修课程001且成绩在70分以下或 成绩在90分以上的学生的姓名，课程 名称和成绩。,说明：在SELECT语句的FROM子句中规定连接的表，在WHERE子句中规定连接的列和连接的方式。WHERE子句既可用来规定（行）选择条件，又可用来规定连接条件（连接列和连接方式）。行选择条件和连接条件这两种条件的逻辑表达式用AND连接。,3.表的别名 一般格式是：表名 表的别名 表的别名有如下用途 简化

18、输入；表与自身连接；在相关嵌套查询中。,例32：求选修了课程的学生的学号、姓名、课程号和成绩。,4.自身连接 自身连接不仅可以作用在两个不同的表上，而且同一个表可进行自身连接。需要给表起别名以示区别 由于所有属性名都是同名属性，因此必须使用别名前缀执行过程：可以把一个看成两个副本，即两个相同的表，然后再对这两个表在连接字段上进行连接。表与自身的连接必须使用表的别名，若在FROM子句中用两个不同的别名对应于同一个表是时，就可以象连接两个分开的表一样来把表自身连接起来。,例33：求年龄大于黎明的所有学生的姓名、系和年龄。,5.外部连接 一般说来，如果某一表中所选取的一行不能与另一个表中的任何一行相

19、连接（即不满足条件）时，则在查询结果中此行不再出现。外部连接则不管条件是否满足，都选取相应的数据。不仅返回两个或两个以上的表中能够直接匹配的行，还返回一个表中无法从其它表中找到直接匹配的行。,C,SC,查询选修002、003课程的学号、课程名称与成绩。,002课程存在吗?,例34：求选修课程002和003的学生的学号、课程号、课程名与成绩。例35：用外部连接求选修课程002和003的学生的学号、课程号、课程名与成绩。,外部连接实际上是假设对一个表添加了一个各列全为空值的额外行。若另一个表中行不能与该表中任一行连接，则与该表的额外行连接，以保证另一个表中各行的信息在查询结果中表示出来。,Orac

20、le中，外连接有两种表现形式：某个连接列后面加一个外部连接操作符(+)；在FROM子句中采用如下表现形式：LEFT OUTER JOIN ON(连接条件)RIGHT OUTER JOIN ON(连接条件)FULL OUTER JOIN ON(连接条件),例如：SELECT Sno,Course.Cno,Cname,Score FROM Course LEFT OUTER JOIN SCore ON(Course.Cno=SCore.Cno)WHERE Course.Cno In(002,003);SELECT Sno,Course.Cno,Cname,Score FROM Course,SCo

21、re WHERE Course.Cno=SCore.Cno(+)AND Course.Cno In(002,003);,6.复合连接条件 一般说来，WHERE子句中可以有多个连接条件,称为复合条件连接。例36：求选修课程001且成绩在85分以上的所有学生。,概念 查询块：一个select-from-where语句称为一个查询块。嵌套查询：将一个查询块嵌套在另一个查询块的where子句或having子句中的查询。子查询的结果用于建立主查询的查找条件。处理步骤 一般由里向外进行处理。,嵌套查询,SELECT Sname FROM Student WHERE Sno IN,（SELECT Sno F

22、ROM SC WHERE Cno=002）;,例37：求选修了002课程的学生姓名。,（选修了课程号为 002课程的学生的学号）;,注意 子查询必须用括号括住。子查询可以有多层，所存取的表可以是父查询没有存取的表。当子查询所返回的不是一个值而是一个集合时，就不能使用简单的比较运算了。必须根据语义在子查询前加上ANY或者ALL；或者使用集合操作符IN及NOT;子查询中不能有ORDER BY子句。子查询选出的记录不显示。,1.返回单值的子查询 子查询返回的结果是一个值时，可以使用比较运算符（=,=,=,!=）将父查询和子查询连接起来。例38：求与王平年龄相同人的姓名与系。,2.返回一组值的子查询

23、子查询返回的结果不是一个值而是而是一个集合即多个值，就不能简单地使用比较运算符，而必须使用多值比较运算符，以指明在WHERE子句中应如何使用这些返回值。,多值比较运算符,多值比较运算符,需要配合使用比较运算符 ANY 大于子查询结果中的某个值 ALL 大于子查询结果中的所有值=ANY 大于等于子查询结果中的某个值=ALL 大于等于子查询结果中的所有值）ANY 不等于子查询结果中的某个值!=（或）ALL 不等于子查询结果中的任何一个值,例39：求其他系中比数学系中某一学生年龄小的学生姓名和系。（ANY）SELECT Sname，Sage FROM Student WHERE Sage MA;/*

24、父查询块中的条件*/,用聚集函数实现例39 SELECT Sname,Sage FROM Student WHERE Sage MA;,例40：求比数学系中全体学生年龄都大的学生姓名和系。（ALL）,ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系,=,或!=,=,=,ANY,IN,-,MAX,=MAX,MIN,=MIN,ALL,-,NOT IN,MIN,=MIN,MAX,=MAX,EXISTS 为存在量词，用来测试子查询是否返回结果；EXISTS表示存在量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。其目标列表达式通常都用*，给出列名无实际意

25、义。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOT EXISTS与此相反。含有IN的查询通常可用EXISTS表示，但反过来不一定。,3.嵌套查询可以是多层的 查询涉及多个关系时，利用嵌套查询逐次求解层次分明，容易理解也容易书写，具有结构化程序设计的特点。嵌套查询的执行效率比连接查询效率高。,例41：求选修了课程名为数据结构的学生的学号和姓名。,最后在Student关系中 取出Sno和Sname,然后在SC关系中找出选 修了003号课程的学生学号,首先在Course关系中找出“数据结构”的课程号，为003号,SELECT Sno，Sname FROM Stu

26、dentWHERE Sno IN,(SELECT Sno FROM SC WHERE Cno IN,(SELECT Cno FROM Course WHERE Cname=数据结构);,4.返回多列的子查询 子查询可以返回多个列。当子查询返回多列时，关系运算的另一侧也应是多个列，并且这些列的清单必须用括号括起来。子查询返回列的个数及类型必须要与主查询列的个数及类型匹配。例42：求与黎明同系和相同年龄的学生的姓名。,5.使用多个子查询（多重子查询）WHERE子句中可以包含任何数量的由AND和OR连接的含有子查询的条件。例43：求与黎明同系，或年龄大于李丽的学生的 信息。例44：求计算机系中与数学

27、系中同龄的学生信息（假设不知道系的编号）。设系编码数据库为DEPT(DNO,DNAME,ADDR)。,6.子查询中使用表连接 有时，子查询可能需要从每个表中检索数据项，这时可在子查询中使用表连接。例45：求数学系中年龄相同的学生姓名和年龄。,7.相关子查询例46：查询没有选修任何课程的学生学号和姓名。SELECT SNO,SNAME FROM STUDENT S WHERE NOT EXISTS(SELECT*FROM SCORE SC WHERE S.SNO=SC.SNO);本例中子查询的查询条件引用了父查询表中的属性值（Student表的SNO值），我们把这类查询称为相关子查询。,二者的执

28、行方式不同普通子查询的执行顺序是 首先执行子查询，然后把子查询的结果作为父查询的查询条件的值。普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。相关子查询的执行顺序是 首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询，然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件，则把该行放入父查询的查询结果集合中。重复执行这一过程，直到处理完父查询表中的每一行数据。相关子查询的执行次数是由父查询表的行数决定的。,例47：查询所有选修了001号课程的学生姓名。思路分析：本查询涉及Student和Score关系；在Stude

29、nt中依次取每个元组的Sno值，用此值去检查Score关系；若Score中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=001，则取此Student.Sname送入结果关系,用嵌套查询 SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM Score WHERE Sno=Student.Sno AND Cno=001);,用连接运算 SELECT Sname FROM Student,Score SC WHERE Student.Sno=SC.Sno AND SC.Cno=001;,例48：查询未选修001课程的学生姓名

30、。,SELECT SNAME FROM STUDENT S WHERE NOT EXISTS(SELECT*FROM SCORE SC WHERE CNO=001 AND SC.SNO=S.SNO);,思考：查询只选修001课程的学生姓名。,?,例49：查询选修全部课程的学生姓名。SELECT Sname FROM Student WHERE NOT EXISTS(SELECT*FROM Course WHERE NOT EXISTS(SELECT*FROM Score WHERE Sno=Student.Sno AND Cno=Course.Cno);,例50：查询成绩比所选课程平均成绩高的

31、学生的学号、课程号和成绩。,(SELECT AVG(SCORE)FROM SCORE X2 WHERE X1.CNO=X2.CNO),(该门课程的平均成绩),SELECT SNO,CNO,SCORE FROM SCORE X1 WHERE SCORE,本例中使用了别名。,ORDER BY X1.CNO;,例51：查询至少选修了学生96002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要96002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词“学生96002选修了课程y”用q表示谓词“学生x选修了课程y”则上述查询为:(y)p q,等价变

32、换：(y)p q(y(p q)(y(p q)y(pq)变换后语义：不存在这样的课程y，学生96002选修了y，而学生x没有选。,用NOT EXISTS谓词表示：SELECT DISTINCT Sno FROM Score SCX WHERE NOT EXISTS(SELECT*FROM Score SCY WHERE SCY.Sno=96002 AND NOT EXISTS(SELECT*FROM Score SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno);,集合查询,不作讲解，自学。,第四章作业：(第1次),1.P.122 2。2.P.122 1

33、0.。3.P.122 11.。思考题 1.P.122 1。2.P.122 4。,SQL的数据定义功能包括三部分:定义基本表 定义视图 定义索引表 TABLE TABLECREATE VIEW DROP VIEW INDEX INDEX ALTER TABLE,4.3 SQL数据定义-DDL,1.基本表 定义基本表结构 定义基本表名 定义表中各列的特征：列名、数据类型、长度以及能否取空值,基本表的定义和修改,一般格式：CREATE TABLE(数据类型 列级完整性约束条件,数据类型 列级完整性约束条件,表级完整性约束条件)；,说明：建表的同时可以定义与该表有关的完整性约束,这些约束条件被存入系统

34、的数据字典中,当用户操作表时,DBMS会自动检查该操作是否有违背完整约束条件.建立约束的考虑：如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上；否则既可以定义在列级上也可以定义在表级上。表名、列名是不区分大小写的。对一个用户而言，表名必须唯一；一个表中，列名必须唯一。表名、列名必须以字母开头，长度不超过30个字符。,在Oracle数据库中支持的数据类型与其他数据库支持的数据类型不大一样。下面列表说明Oracle数据库所支持的常用的数据类型。,例52：建立关于学生-课程数据库信息的数据库表,CREATE TABLE STUDENT(SNO CHAR(5)CONSTRAINT PK_S

35、 PRIMARY KEY(SNO),SNAME VARCHAR(10)NOT NULL,SDEPT CHAR(2)NOT NULL,SCLASS CHAR(2)NOT NULL,SSEX CHAR(2)CHECK(SSEX IN(男,女,),SAGE NUMBER(2)CHECK(SAGE BETWEEN 16 AND 25);CREATE TABLE STUDENT(SNO CHAR(5),SNAME VARCHAR(10)NOT NULL,SDEPT CHAR(2)NOT NULL,SCLASS CHAR(2)NOT NULL,SSEX CHAR(2)CHECK(SSEX IN(男,女,)

36、,SAGE NUMBER(2)CHECK(SAGE BETWEEN 16 AND 25),CONSTRAINT PK_S PRIMARY KEY(SNO);,CREATE TABLE SCORE(SNO CHAR(5),CNO CHAR(3),SCORE NUMBER(5,2),CONSTRAINT PK_SC PRIMARY KEY(SNO,CNO),CONSTRAINT FK_SC_S FOREIGN KEY(SNO)REFERENCES STUDENT(SNO)ON DELETE CASCADE,CONSTRAINT FK_SC_C FOREIGN KEY(CNO)REFERENCES

37、COURSE(CNO);,CONSTRAINT,修改基本表结构 修改已经定义的表的结构 修改已经定义的表的完整性约束：,一般格式：ALTER TABLE ADD 完整性约束 DROP COLUMN（）ADD CONSTRAINT DROP PRIMARY KEY|UNIQUE(列名)|CONSTRAINT CASCADE DROP CONSTRAINT MODIFY;,说明 ADD子句用于新增列及其完整性约束条件，新增加的字段只能作为表的最后一个字段；DROP子句用于删除指定的完整性约束条件;MODIFY子句用于修改原有的列定义,包括修改列的数据类型和长度；Oracle9提供了删除属性列的功能

38、。有些数据库软件产品的SQL没有提供删除属性列的语句，用户只能间接实现这一功能。方法是先将表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重新命名为原表；,只有当一个表为空表时，即表中无数据时，新增列才可选择 NOT NULL；只有当某列已有数据中未出现过空值时，才能将该列改为非空列(NOT NULL)；若要改变列的类型或减小列的宽度，此列对应的数据必须全为空值。,如何修改表的列名？,ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;,这样的语法是从Oracle9.2的版本才开始支持，9

39、.0.1 及以前的版本，是不支持这个语法的，只能通过重建表或添加/删除字段的方法来改变字段名。,如果我们采用Oralce9.2早期版本，可以采用以下5步来间接地修改表的列名。针对要重命名的表列，取消施加其上的所有约束和索引。添加新的表列，给这些表列赋新的名字。把数据从旧表列移动到新的表列。对新的表列重新创建所有的约束和索引。删除旧列。,例53：为表STUDENT添加一个新的列。例54：将刚添加的列SID改为DATE类型。,*建表后声明主关键字：alter table table_name add constraint primary_a primary key(column_name);注意：

40、table_name 是表的名字 primary_a 是定义了主键的约束名字 column_name 是要被定义为主键的字段名。,也可以在定义主键时不添加约束名字：alter table table_name add primary key(column_name);,*建表后声明外关键字：alter table table_name1 add constraint foreign_a foreign key(column_name)references table_name2(column_name);注意：table_name1 是要增加外部键的表的名字，table_name2 是相对ta

41、ble_name1的主表。foreign_a 是定义了外部键的约束名字 column_name 是要被定义为外键的字段名。,alter table table_name1 add foreign key(column_name)references table_name2(column_name);,删除基本表定义一般格式：DROP TABLE CASCADE CONSTRAINTS;,说明:基本表一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除。Oracle中，删除基本表后，建立在此表上的视图定义仍然保留在数据字典中，但是，用户不能使用，使用将出错。如果要删除的表中包含有被其他表外

42、码引用的主码或唯一性约束列，并且希望在删除该表的同时删除其他表中的相关的外码约束，需要使用CASCADE CONSTRAINTS子句。,例55：删除STUDENT表。,Oracle9.2的版本还提供了直接修改表名，直接删除表中的列。但要注意完整性约束问题。,修改表名：RENAME TO;删除表中的列：ALTER TABLE DROP COLUMN;,2.完整性约束的说明 域完整性 保证一个数据库不包含任何无意义的或不合理的值，即保证表的某一列的任何一个值都是该列域(即合法的数据集合)的成员。通过使用列数据类型实现域完整性，SQL可自动实现域完整性。,实体完整性 保证一个表的每行是唯一的。要实现

43、实体完整性，必须指定一个表中的一列或一组列作为表的主键。通过声明表级或列级的完整性约束条件 PRIMARY KEY 来定义一个表的主键，并实现实体完整性。,参照完整性 保证相关的表在时间上的一致性。参照完整性定义了一个关系数据库中不同的列和不同的表之间的关系。通过声明一个 FOREIGN KEY 完整性约束在表中定义一个或多个外键并实现参照完整性，用 REFERENCES 定义外键来自的表名，即主表或父表名。,FOREIGN KEY：如表R的属性f引用表P的属性a：R(f)-P(a)，则 a是P的 Primary Key R(f)出现的值必须在P(a)中有效 参照完整性约束不仅决定了外键所属于

44、的表中的这个外键可接受的数据，而且还定义了当参照完整性约束作用父键时，外键所能进行的引用操作。,在Oracle中，通过在定义外键时加参照完整性选择项 ON UPDATE/ON DELETE 来实现当主表被引用的主键被修改/删除时，为了不破坏参照完整性约束所有的二种可能的处理办法：RESTRICT：缺省选择项，称为限制引用操作。凡是被子表所引用的主键，不得进行任何操作；CASCADE：称为级联引用操作。凡是对主表的主键进行的操作，子表中引用此主键的行也随之自动被修改/删除。,insert into scott1(gg,hh)values(dddd,04-7月-2008);insert into

45、scott1(gg,hh)values(eeee,04-6月-2008);,create table scott1(gg char(4)constraint pk_s1 primary key,hh date);,关于参照完整性约束条件举例：,父表,create table scott2(gg char(4),jj date,constraint fk_s2_s1 foreign key(gg)references scott1(gg);,insert into scott2(gg,jj)values(eeee,04-10月-89);,子表,delete from scott1 where g

46、g=eeee;,对建立了主键和外部键的两个表scott1和scott2，执行下列删除运算后，会有什么结果？为什么？,SQL select*from scott1;GG HH-dddd 04-7月-89eeee 04-6月-89,SQL select*from scott2;GG JJ-eeee 04-10月-89,ERROR 位于第 1 行:ORA-02292:违反完整约束条件(SCOTT.SYS_C001233)-已找到子记录日志,用户自定义完整性 业务规则的约束。在Oracle中，可以使用存储过程和触发器来定制业务规则，实现用户自定义的完整性约束。,1.索引的概念,索引表,学生表,索引,2

47、.建立索引的目的说明:使用索引加速数据查询的速度，减少磁盘I/O的次数；有序输出；实施唯一性约束(与UNIQUE)。,谁可以建立索引 DBA 或表的属主（即建立表的人）DBMS一般会自动建立以下列上的索引PRIMARY KEYUNIQUE 谁维护索引DBMS自动完成 使用索引DBMS自动选择是否使用索引以及使用哪些索引,3.建立索引一般格式：CREATE UNIQUE INDEX ON(ASC|DESC,ASC|DESC);说明 是要建立索引的基本表名字；索引可建立在一列或多列上，各列名之间用逗号分隔；,ASC(升序)或DESC 指定索引值的排列次序，缺省为ASC；UNIQUE指明此索引的每一

48、个索引值只对应唯一的数据记录。（唯一性）当表中有数据时，建立索引将检查数据的唯一性，如果出现重复，将有下列提示：ORA-01452:无法 CREATE UNIQUE INDEX；找到重复的关键字 建立了唯一性索引后，以后对数据的修改将自动进行唯一性验证。,例56：对学生-课程关系数据库中的表建立索引。,4.删除索引一般格式：DROP INDEX ON;说明：ON子句是选择项，它导致索引与表名的校验；用户只能删除自己建立的索引；索引是不能修改的。,5.索引的自动引用 用户不必明显地标识出将使用什么索引以及为何使用，即用户不必也不能在存取数据时选择索引，SQL将自动使用索引，存取路径的选择有系统自

49、动选择。一般地，SQL优化程序将确定一个SQL语句是否适于使用索引。,在以下情况下，Oracle将不使用索引：不存在有WHERE子句，即选择所有行；索引列被函数引用或出现在表达式中；谓词中含有 IS NULL 或 IS NOT NULL。,注意：如果确信所要检索的行比例较大，则应抑制索引的使用。其常用的方法：把数字加上零；把字符与空值相连接。例如：GRADE+0=95|SNAME=张三,6.索引的说明 表越大，索引越能有效地改善查询的响应时间，对于少于100行的表建立索引可能不合算；对WHERE子句中最常使用的列建立索引。对连接列建立索引，也可大大地改善连接查询的速度；只要可能，尽量将索引列定

50、义为NOT NULL；Oracle 在一个表上最多可创建16个索引；应根据需要建立索引，应当在查询速度和插入更新速度之间进行权衡。通常不要在一个表上建立多于三个索引。,数据操纵命令将导致数据库中数据的改变。SQL语言的数据操纵功能包括三部分:INSERT 向表中插入数据 UPDATE 修改表中已存在行中的数据 DELETE 删除表中的数据,4.4 SQL数据操纵-DML,要求表必须是已存在的。1.单行插入一般格式：INSERT INTO(,)VALUES(,);,插入数据,注意：在表定义时说明了NOT NULL 的属性列不能取空值，否则会出错。如果 INTO 子句中选择了列名，则VALUES子