计算机数据库SQL语言.ppt

《计算机数据库SQL语言.ppt》由会员分享，可在线阅读，更多相关《计算机数据库SQL语言.ppt（137页珍藏版）》请在三一办公上搜索。

1、1,关系数据库标准语言SQL语言,主 要 内 容,第3章,3.1 SQL概述3.2 查询语句3.3 更新语句3.4 SQL DDL3.5 SQL DCL3.6 视图3.7 嵌入式SQL(*),2,3.1 SQL概述,一、SQL的发展及现状二、SQL数据库的体系结构三、SQL的功能四、SQL的形式五、SQL的特点,3,3.1 SQL概述,一、SQL的发展及现状1974年，由Boyce和Chamberlin提出19751979，IBM San Jose Research Lab的关系数据库管理系统原型System R实施了这种语言SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)

2、、SQL-99(SQL3)、SQL-2003大部分DBMS产品都支持SQL，成为操作数据库的标准语言,4,3.1 SQL概述,SQL,用户,Base TableB1,View V1,View V2,Base TableB2,Base TableB3,Base TableB4,Stored Filestudent1,Stored Filestudent1,Stored Filestudent1,Stored Filestudent1,外模式,模式,内模式,SQL语言支持的关系数据库的三级模式结构,二、SQL数据库的体系结构,5,3.1 SQL概述,基本概念：1、用户可以用SQL语言对视图(Vie

3、w)和基本表(Base Table)进行查询等操作，在用户观点里，视图和表一样，都是关系。2、视图是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。3、基本表是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。,二、SQL数据库的体系结构,6,3.1 SQL概述,数据定义（DDL）定义、删除、修改关系模式（基本表）定义、删除视图（View）定义、删除索引（Index）数据操纵（DML）数据查询数据增、删、改 数据控制（DCL）用户访问权限的授予、收回,三、SQL的功能,7,3.1 SQL概述,DA

4、TABASE TABLECREATE VIEW INDEX DDL：ALTERDROP SQL QUERY：SELECT FROM INSERT SQL SQL DML：UPDATE DELETE GRANT SQL DCL：REVOKEAUDIT 嵌入式SQL：,四、SQL的组成(分类),8,3.1 SQL概述,交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对 数据库进行操作由DBMS来进行解释,嵌入式SQL能将SQL语句嵌入到高级语言（宿主语言）使应用程序充分利用SQL访问数据库的能力、宿主 语言的过程处理能力一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理

5、的语句,五、SQL的形式,9,3.1 SQL概述,4.具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，且每种操作只需一种操作符。,高度非过程化的语言：用户只需提出“干什么”，至于“怎么干”由DBMS解决；用户只需要早查询语句中提出需 要什么，DBMS即可按路径存取，并把结果返回给用户。,2.面向集合的语言：每一个SQL的操作对象是一个或多个关系，操作的结果也是一个关系。,3.一种语法结构，两种使用方式：即可独立使用，又可嵌入到宿主语言中使用，具有自主型和宿主型两种特点。,六、SQL的特点,10,3.1 SQL概述,5.语言简洁、易学易用：核心功

6、能只有9个动词，语法简单，接近英语。SQL功能 动词 数据库查询 SELECT 数据定义 CREATE，DROP，ALTER 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE,11,3.1 SQL概述,1、字符型：CHAR(n)，VARCHAR 2、数字型：INT,SMALLINT,REAL3、日期型：DATE,TIME,(一)数据类型,(二)函数,数字函数:ABS(X),SQRT(X),RAND(X),LOG(X),字符函数：LENGTH(X$),LOWER(X$),UPPER(X$),SUBSTRING(expression,start,length)

7、分组函数：COUNT(),MAX(X),MIN(X),AVG(X),SUM(X),12,3.1 SQL概述,句法操作符：&数值操作符：+,逻辑操作符：=,IN,ANY,ALL,查询表达式操作符：UNION,其它操作符：,(+),(三)运算符,13,基本结构Select A1,A2,.,AnFrom R1,R2,.,RmWhere P,3.2 查询语句,数据查询是数据库应用的核心功能,A1,A2,.,An(p(R1R2.Rm),14,3.2 查询语句,查询语句格式(P72)SELECT ALL|DISTINCT 表名.*|列名|表达式AS 新列名INTO:主变量1,:主变量2FROM 表名表别名

8、,WHERE 条件表达式|子查询GROUP BY 列名1,HAVING 分组表达式UNION|INTERSECT|EXCEPTSELECTFROMORDER BY 列名|列序号ASC|DESC,；,15,SELECTFROM常用语句执行过程 sELECT 投影 FROM TABLE内存 WHERE 选取元组 GROUP 分组 HAVING 选择分组 UNION|查徇结果的集合运算 SELECT ORDER BY 排序输出,3.2 查询语句,16,3.2 查询语句,二、连接查询,多表连接查询、单表连接查询、外连接查询、复合条件连接查询.,三、嵌套查询,一、单表查询,返回单个值的子查询返回一组值的

9、子查询多重子查询,查询主要分类,17,A1,.,An(p(R1.Rm)Select A1,A2,.,An From R1,R2,.,Rm Where P,一、单表查询 1.选择表中的列 2.选择表中的行 3.分组与组函数 4.排 序,3.2 查询语句,18,一、单表查询,选择表中的列(投影)例1.求学生所在系及姓名SELECT Sdept,Sname FROM Student 例2.求学生的全部信息 SELECT Sno,Sname,Sage,SdeptFROM student(SELECT*),3.2 查询语句,19,SELECT Sname NAME,Year of Birth:BIRTH

10、,2010-sage BIRTHDAY,LOWER(Sdept)DEPARTMENT FROM Student;,例3.查全体学生的姓名、出生年份和所有系，要求用 小写字母表示所有系名,SELECT Sname,Year of Birth:,2010-sage,LOWER(Sdept)FROM Student;,3.2 查询语句,20,3.2 查询语句,21,选择表中的行(选择运算)1)消除重复行:DISTINCT(缺省为ALL)例:求选修了课程的学生号SELECT DISTINCT Sno FROM SC2)选满足一定条件的行：Where 子句运算符比较：、=、=、not+确定范围：Betw

11、een A and B、Not Between A and B确定集合：IN、NOT IN字符匹配：LIKE，NOT LIKE空值：IS NULL、IS NOT NULL多重条件：AND、OR、NOT,3.2 查询语句,22,3.2 查询语句,例:求年龄在1822(含18,22)之间的学生名及年龄（或不在1822之间)SELECT Sname,Sage FROM Student WHERE Sage BETWEEN 18 AND 22；(WHERE Sage=18 AND Sage22)；,例：求计算机学院年龄小于19的姓名及年龄 SELECT Sname,Sage FROM Student

12、WHERE Sdept=计算机 AND Sage19；,23,3.2 查询语句,3)查询条件来自集合：用IN or NOT IN例:求人力系、会计系、电商系的系名、学生名(或不是这些系的学生)SELECT Class,Sname FROM Student WHERE Class IN(人力,会计,电商)；(WHERE Class=人力 OR Class=会计 OR Class=电商)SELECT Class,Sname FROM Student WHERE Class NOT IN(人力,会计,电商)；(WHERE Class!=人力 OR Class!=会计 OR Class!=电商),wr

13、ong,24,3.2 查询语句,Where 子句Like 格式:NOT LIKE 匹配串 ESCAPE 换码字符%:表示任意长度(0的任意字符_:表示单个的任意字符ESCAPE 换码字符:匹配串中换码字符(转义符)之后的字(%,_)，被定义为普通字符(不作通配符用),4)字符匹配:,例1：列出课程名称中带有_的课号及课名。Select cno，cnameFrom CourseWhere cname LIKE%_%escape,25,3.2 查询语句,例2：求课程名中有数据库的课程记录SELECT*FROM CourseWHERE Cno LIKE%数据库%；例3：求倒数第三、四个汉字为系统的课

14、程名SELECT CnameFROM CourseWHERE Cname LIKE%系统_ _ _ _；例4：求以DATA_BASE开头且倒数第五个字符为S的课程名SELECT CnameFROM CourseWHERE Cname LIKEDATA_BASE%S_ _ _ _ ESCAPE；,26,例：查缺少成绩的学生的学号和相应的课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL;,3.2 查询语句,5)涉及空值的查询:,27,3.2 查询语句,将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现只有出现在Group B

15、y子句中的属性，才可出现在Select子句中例：统计各系学生的人数。Select sdept，count(*)as stu_countFrom StudentGroup By sdept,3、分组与组函数,Group By子句,28,3.2 查询语句,组函数的使用：COUNT(DISTINCT|ALL*|列名)SUM(DISTINCT|ALL 列名)AVG(DISTINCT|ALL 列名)MAX(DISTINCT|ALL 列名)MIN(DISTINCT|ALL 列名),组函数可用于SELECT子句中的目标列表中，或在HAVING子句的分组表达式中用作条件。对分出的每一组用HAVING进行筛选,

16、筛选条件要用到组函数。注意：除了count(*),使用Count(列名)函数或者其他统计函数将忽略空值。Distinct在count函数可以用于去除重复值，而在其他函数中则没有意义,29,例1:查询各个课程号与相应的选课人数 SELECT Cno,COUNT(Sno)FROM SC GROUP BY Cno;,3.2 查询语句,例2:查男女生人数 SELECT sex,COUNT(Sno)COUNTSNO FROM student GROUP BY sex;,例3：求选修了课程的学生人数SELECT COUNT(DISTINCT Sno)FROM SC；例4：求选修各门课的人数及平均成绩SEL

17、ECT Cno,COUNT(Sno),AVG(ALL GR)FROM SC GROUP BY Cno；例5：求选修课程在2门以上且都及格的学生号及总平均分SELECT Sno,AVG(ALL Grade)FROM SC GROUP BY Sno HAVING COUNT(Cno)2 AND MIN(Grade)=60；,30,3.2 查询语句,Where 决定哪些元组被选择参加运算，作用于关系中的元组Having 决定哪些分组符合要求，作用于分组,Having 与 Where的区别,31,3.2 查询语句,4、排序用ORDER BY子句对查询结果按照一个或多个列的值进行升/降排列输出,升序为A

18、SC；降序为DESC,空值将作为最大值排序,例1：对选修C5课程的学生按成绩降序排列，同分数者按学号升序排列 SELECT Sno,Grade From SC WHERE Cno=C5 ORDER BY Grade DESC，Sno ASC；例2：求每个学生的总分并按总分降序排列,学号升序SELECT Sno，SUM(ALL Grade)as tg FROM SC GROUP BY Sno ORDER BY tg DESC，Sno ASC,32,求学生中选修课程在 2门以上且都及格的学生号及总平均分，并按平均成绩排序。,3.2 查询语句,3.2 查询语句,COMPUTE函数的使用(用于汇总满足

19、条件元组在指定列的信息)Select sc.sno,cno,grade from sc,student where cno=c001 and sc.sno=student.sno and sdept=管理学院 compute avg(grade),3.2 查询语句,查询管理学院学生各门课程的平均分Select sc.sno,cno,grade from sc,student where sc.sno=student.sno and sdept=管理学院 order by cno compute avg(grade)by cno,35,3.2 查询语句,Group by 生成单个结果集，选择列表

20、只能包含分组列和聚合函数（分组函数）Compute生成多个结果集，一个结果集中包含每个组的明细行信息，另一个结果集中包含组的聚合信息。选择列表可包含除分组列或聚合函数外的其他表达式。同时，聚合函数在compute子句中指定，而不是在选择列中。,Compute 与 Group by的区别,36,3.2 查询语句,多表连接查询、单表连接查询(自连接)、外连接查询、复合条件连接查询.,二、连接查询,示例数据表:学生信息表Student;课程信息表Course;选课情况表SC,教师信息表teacher,37,3.2 查询语句,连接条件一表名1.列名1 比较运算符 表名2.列名2连接条件二表名1.列名1

21、 BETWEEN 表名2.列名2 AND 表名2.列名3,连接条件中的列名称为连接字段，其各 字段应是可比的。,1、多表连接,38,3.2 查询语句,在表1中找到第一个元组，然后从头开始扫描表2，查找到满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表2末尾。再从表1中取第二个元组，重复上述的操作，直到表1中的元组全部处理完毕。,执行过程：,39,3.2 查询语句例题,SELECT student.sno，Sname，Cname，GradeFROM Student S，Course C，SCWHERE S.Sno=SC.Sno AND C.Cno=SC.Cno；,例1：求

22、选课情况，要求输出学号、姓名、课程名与成绩,3.2 查询语句,40,3.2 查询语句,用表别名把一个表定义为两个不同的表进行连接。,2、单表连接(自连接),例1：求每门课的间接先修课名（即先修课的先修课）,SELECT FIRST.Cno，THIRD.CnameFROM Corse FIRST，Course SECOND,Course THIRDWHERE FIRST.CPno=SECOND.Cno AND SECOND.CPno=THIRD.Cno,41,3.2 查询语句,在连接条件的某侧加上()或(+)，表示该侧所对应的表中可形成一个各数据项均为空值的万能替代行，用来与另一侧对应的表中所有

23、不满足条件的元组进行连接。外连接符()或（+）出现在左侧称为右外连接、出现在右侧称为左连接、两侧都出现的称为全外连接。,SELECT DNAME，TNAME FROM department D，teacher TWHERE D.DNO=T.DNO(*)；,例1：求各部门名及职工名，要求输出 无职工的部门（职工以空值出现）,3、外连接,42,例题,SELECT DNAME，TNAME FROM department D，teacher TWHERE D.DNO(*)=T.DNO；,例2：求各部门名及职工名，要求输出 未分配部门的职工（部门以空值出现）,43,例题,SELECT DNAME，TNA

24、ME FROM department D，teacher TWHERE D.DNO(*)=T.DNO(*)；,例3：求各部门名及职工名，要求输出 无职工的部门和未分配部门的职工,注意,在sql server中,Left join 左外连接Right join 右外连接,Full join 全外连接,44,3.2 查询语句3.2,SELECT DNAME,TNAME FROM department D left join teacher Ton D.DNO=T.DNOSELECT DNAME,TNAME FROM department D right join teacher Ton D.DNO

25、=T.DNOSELECT DNAME,TNAME FROM department D full join teacher Ton D.DNO=T.DNO,45,3.2 查询语句,WHERE子句中除了连接条件，还有其它限制条件。,SELECT Sname,GradeFROM student,SCWHERE student.sno=SC.sno AND SC.Cno=C6 AND SC.Grade90；,例1：求选修C6课程且成绩超过90分的学生名与成绩,连接条件,限制条件,4、复合条件连接,46,3.2 查询语句,在SELECT FROM WHERE语句结构的 WHERE子句中可嵌入一个SELE

26、CT语句块 其上层查询称为外层查询或父查询 其下层查询称为内层查询或子查询 SQL语言允许使用多重嵌套查询 在子查询中不允许使用ORDER BY子句 嵌套查询的实现一般是从里到外，即先进行 子查询，再把其结果用于父查询作为条件,三、嵌套查询,47,三、嵌套查询,方法一：SELECT Sname,Sage FROM student WHERE Sdept=(SELECT sdept FROM student WHERE Sname=刘力）；方法二：SELECT FIRST.Sname,FIRST.Sage FROM Student FIRST,Student SECONDWHERE FIRST.

27、Sdept=SECOND.Sdept AND SECOND.Sname=刘力；,例1：求与刘力同一个学院的学生名，年龄,1、返回单个值的子查询：,48,三、嵌套查询,方法一：SELECT*FROM student WHERE sno IN(SELECT sno FROM SC WHERE Cno=C6 AND Grade90)；,例1：求选修C6课程且成绩超过90分的学生,2、返回一组值的子查询：,方法二（连接查询）：SELECT student.*FROM student，SCWHERE Student.Sno=SC.Sno AND Grade90 AND Cno=C6；,49,例题,方法一

28、：SELECT*FROM student WHERE sdept!=CS AND sage ANY(SELECT Sage FROM Student WHERE Sdept=CS)；方法二：SELECT*FROM StudentWHERE Sdept!=CS AND Sage(SELECT MAX(Sage)FROM Student WHERE Sdept=CS)；,例2：求比计算机系中某一学生年龄小的其他系的学生,50,3.2 查询语句,例1：求D01部门中工资与国贸系中任一职工相同的职工和工资Teacher(tno,tname,salary,dno)Department(dno,dname

29、),3、多重子查询：,51,例题,SELECT Tname，Salary FROM Teacher WHERE Dno=D01 AND salary IN(SELECT salary FROM teacher WHERE Dno=（SELECT DNO FROM department WHERE Dname=国贸)；,52,例题,SELECT*FROM teacher WHERE Salary=(SELECT MIN(Salary)FROM teacher WHERE Tname IN(张三,里司)AND Salary=(SELECT MAX(Salary)FROM teacher WHERE

30、 Tname IN（张三,里司)；,例2：求工资介于张三与里司两个之间的职工,53,3.2 查询语句,不相关子查询：子查询的查询条件不依赖于 父查询的称为不相关子查询。相关子查询：子查询的查询条件依赖于外层父 查询的某个属性值的称为相关子查询(Correlated Subquery),带EXISTS 的子查询就 是相关子查询 EXISTS表示存在量词 带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值 True 或 False,四、带有EXISTS的相关子查询,54,例题,不相关子查询：SELECT Sname FROM student WHERE snoIN(SELECT sno F

31、ROM SC WHERE Cno=C1)；,例1：求所有选修了C1课程的学生名。,相关子查询：,SELECT Sname FROM studentWHERE EXISTS(SELECT*FROM SC WHERE student.sno=SC.sno AND Cno=C1)；,55,先在外层查询中取student表的第一个元组（记录），用该记录的相关的属性值（在内层WHERE子句中给定的）处理内层查询，若外层的WHERE子句返回TRUE值，则此元组送入结果的表中。然后再取下一个元组；重复上述过程直到外层表的记录全部遍历一次为止。,相关子查询执行过程：,56,不关心子查询的具体内容，因此用 SE

32、LECT*,Exists+子查询用来判断该子查询是否返回元组 当子查询的结果集非空时，Exists 为 True 当子查询的结果集为空时，Exists为 False NOT EXISTS：若子查询结果为空，返回TRUE值，否则返回 FALSE,说明：,57,例题,SELECT sno,SnameFROM studentWHERE NOT EXISTS(SELECT*FROM SC WHERE student.sno=SC.sno AND Cno=C1)；,例2：列出没有选C1课程的学生的学号、姓名,58,例题,SELECT SnameFROM studentWHERE NOT EXISTS(S

33、ELECT*FROM Course WHERE NOT EXISTS(SELECT*FROM SC WHERE student.sno=SC.sno AND Course.Cno=SC.Cno)；,例3：查询选修了所有课程的学生的姓名,这门课他没选,这样的课是不存在的,59,例题,例4：查询至少选修了S1所选的全部课程的学生名,SELECT SnameFROM studentWHERE NOT EXISTS(SELECT*FROM SC SCX WHERE SCX.sno=s1 AND NOT EXISTS(SELECT*FROM SCSCY WHERE student.sno=SCY.sno

34、AND SCX.Cno=SCY.Cno)；,60,注意,在FROM语句中使用子查询，对查询结果定义表名及列名例：求平均成绩超过80分的学号及平均成绩,2.SELECTsno,AVG(Grade)FROMSCGROUPBYsnoHAVINGAVG(Grade)80,1.SELECT Sno,AVG_G FROM(SELECTSno,AVG(Grade)FROM SC GROUP BY Sno)AS RA(Sno,AVG_G)WHERE AVG_G80;,61,属性个数必须一致 对应的类型必须一致 属性名无关 最终结果集采用第一个结果的属性名 缺省为自动去除重复元组 除非显式说明ALL Order

35、 By放在整个语句的最后,五、SQL的集合操作,62,例1：查询计算机系的学生或者年龄不大于19岁的学生，并按年龄倒排序。,SELECT*FROM studentWHERE Sdept=CSUNIONSELECT*FROM studentWHERE AGE=19ORDER BY AGE DESC,SQL的集合操作并,63,例2：查询计算机系的学生并且年龄不大于19岁的学生，并按年龄倒排序。,(SELECT*FROM student WHERE Sdept=CS)INTERSECT(SELECT*FROM student WHEREAGE=19)ORDER BY AGE DESC,SQL的集合操

36、作交,64,例3：查询选修课程1但没有选修课程2的学生。,SELECTSname,SdeptFROMstudentWHEREsnoIN(SELECT sno FROM SC WHERE Cno=1)EXCEPT(SELECT sno FROM SC WHERE Cno=2),SQL的集合操作差,65,注意:SQL92中支持并交差运算,但不同的数据库产品对此支持是不同的,在使用前需要查询使用说明.Sql server 2000中支持并的操作.,66,3.3 更新语句,一、插入操作 INSERT,二、删除操作 DELETE,三、修改操作 UPDATE,67,一、插入操作,1、插入单个元组：,格式：

37、INSERT INTO 表名(列名1,)VALUES(列值1,)；,插入一已知元组的全部列值,插入一已知元组的部分列值,68,插入一已知元组的全部列值,INSERT INTO student VALUES(2003001,陈冬,18,男,电商,管理学院,徐州)；,例1：新增一个学生信息,INSERT INTO SC（Sno，Cno）VALUES(2003001,C003)；,69,格式：INSERT INTO 表名(列名1,)(子查询)；,例1：设关系S_G(Sno,AVG_G),把平均成绩大于80的男生的学号及平均成绩存入S_G中。,2、插入子查询的结果：,一、插入操作,需要先建立S_G表,

38、70,例题,INSERT INTO S_G(sno，AVG_G)(SELECT sno，AVG(GRADE)FROM SC WHERE Sno IN(SELECT Sno FROM Student WHERE SEX=男)GROUP BY Sno HAVING AVG(GRADE)80)；,Select sno,sname into student2 from student创建表并插入数据,71,二、删除操作,格式：DELETE FROM 表名 WHERE 条件；,只能对整个元组操作，不能只删除某些属性上 的值 只能对一个关系起作用，若要从多个关系中删 除元组，则必须对每个关系分别执行删除命

39、令 从关系 r 中删除满足条件的元组，只是删除数据，而不是定义,72,例1：删除学号为2003009的学生。,1、删除单个元组：,二、删除操作,DELETE FROM SCWHERE sno=2003009;,DELETE FROM studentWHERE sno=2003009;,73,例2：删除选课但无成绩的学生的选课信息,2、删除多个元组：,二、删除操作,DELETE FROM SCWHERE GRADE Is NULL;,DELETE FROM SC 清空SC表,74,例3：删除选修C004且成绩小于该课程的平均成绩的记录,3、带子查询的删除语句：,二、删除操作 3.5,DELETE

40、 FROM SC WHERE Cno=C004 AND Grade(SELECT AVG(GRADE)FROM SC WHERE Cno=C004）；,75,三、修改操作,格式1：UPDATE 表名 别名 SET 列名=表达式，WHERE 条件；格式2：UPDATE 表名 别名 SET(列名,)=(子查询)WHERE 条件；,76,例1：把名为MARTIN的职工的工种改为MANAGER，工资增加收入20%，,1、修改单个元组的值：,三、修改操作,UPDATE EMPLOYEE SET JOB=MANAGER，Salary=Salary*1.2 WHERE NAME=MARTIN；,77,例2：

41、将所有学生的年龄增加1岁,2、修改多个元组的值：,三、修改操作,UPDATE student SET Sage=Sage+1;,78,例3：工种为SALESMEN的职工的工资改为工种平均 工资的110%。,3、带子查询的修改语句：,三、修改操作,UPDATE EMPLOYEESET Salary=(SELECT 1.1*AVG(Salary)FROM EMPLOYEE WHERE JOB=SALESMEN)WHERE JOB=SALESMEN；,79,例4：管理学院全体学生成绩上浮10%,3、带子查询的修改语句：,三、修改操作,UPDATE SCSET GRADE=GRADE*1.10，WHE

42、RE sno IN(SELECT sno FROM student WHERE Sdept=管理学院）；,80,4、修改操作与数据库的一致性 UPDATE语句一次只能操作一个表。例:学号为020011的学生休学一年，复学后需要将其学号改为030011?Student表和SC表都有020011的信息，两表都修改，一般这种修改只能通过两条UPDATE语句进行。(假设student 与sc之间没有参照完整性约束）第一条UPDATE语句：UPDATE Student SET sno=030011 WHERE sno=020011 第二条UPDATE语句:UPDATE SC SET sno=030011

43、 WHERE sno=020011,三、修改操作,81,问题：执行第一条UPDATE语句之后，数据库中的数据是否一致？情况如下：此时student中已没有020011的学生SC表中仍记录着020011学生的选课信息,数据的完整性和一致性受到破坏,只有执行第2条UPDATE语句之后，数据才重新处于一致状态。假设执行完一条语句之后，机器突然出现故障，无法再继续执行第二条UPDATE语句，则数据库中的数据将永远处于不一致状态。因此必须保证这两条UPDATE语句要么都做，要么都不做。解决方法：事务（Transaction），我们将在后面章节介绍。本题可以通过在student 与sc之间设置参照完整性约

44、束,强制级联删除和更新.,82,3.4 数据定义,一、定义基本表 CREATE TABLE,二、删除基本表 DROP TABLE,三、修改基本表 ALTER TABLE,四、定义索引 CREATE INDEX,五、删除索引 DROP INDEX,83,一、定义基本表,格式：CREATE TABLE 表名(列名 类型(长度)NOT NULLDEFAULT 常量|系统变量|NULL列约束,)PRIMARY KEY(列名,)FOREIGN KEY(列名,)REFERENCES 表名(列名,)CHECK 条件；,84,例题,CREATE TABLE student(sno CHAR(6)primary

45、 key NOT NULL,Sname CHAR(8)NOT NULL,sage SMALLINT,Sdept CHAR(10),SEX CHAR(2)DEFAULT 男 CHECK(SEX=男)OR(SEX=女)也可以 PRIMARY KEY(Sno)；,例：建立学生student、课程Course、选课SC三个表,student表：,85,例题,CREATE TABLE Course(Cno CHAR(6)NOT NULL,Cname CHAR(30)NOT NULL,Tno CHAR(8),Cpno CHAR(6)PRIMARY KEY(Cno)；,Course表：,86,例题,CREA

46、TE TABLE SC(sno CHAR(6)NOT NULL,Cno CHAR(6)NOT NULL,Grade SMALLINT DEFAULT NULL,PRIMARY KEY(sno,Cno),FOREIGN KEY(sno)REFERENCES student(sno),FOREIGN KEY(Cno)REFERENCES Course(Cno),CHECK(Grade BETWEEN O AND 100)；,SC表：,87,3.4 数据定义,常用完整性约束主码约束：PRIMARY KEY唯一性约束：UNIQUE参照完整性约束:FOREIGN KEY非空值约束：NOT NULL默认值

47、:DEFAULT规则:RULE检查约束:CHECK,域完整性,实体完整性,88,主码约束主码值不允许空，也不允许出现重复主码定义形式表级主码定义：PRIMARY KEY(Sno)Constraint pk_student primary key(sno)列级主码定义：Sno CHAR(4)PRIMARY KEY唯一值约束UNIQUE定义形式表级定义：UNIQUE(Sname)Constraint UQ_student UNIQUE(sname)列级定义：Sname CHAR(4)UNIQUE,PRIMARY KEY与 UNIQUE的区别？,89,外码约束 定义形式在SC表中，定义FOREIGN

48、 KEY(Sno)REFERENCES Student(Sno)Sno在SC中是外码，在Student中是主码，Sno在SC中取值或为空或为Student中某个Sno值作为主码的关系称为基本（参照）关系，作为外码的关系称为依赖关系,90,删除基本关系元组RESTRICT方式只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作CASCADE方式将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除SET NULL方式删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值如FOREIGN KEY(Sno)R

49、EFERENCES Student(Sno)ON DELETE CASCADE|SET NULL,91,修改基本关系主码RESTRICT方式只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时，才可以修改该元组主码，否则系统拒绝此次修改CASCADE方式将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值SET NULL方式：修改基本关系中元组主码时，将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值如FOREIGN KEY(Sno)REFERENCES Student(Sno)ON UPDATE CASCADE|SET NULL,92,规则规则限定了属性

50、列的范围定义形式:CREATE RULE 规则名 AS 规则Create rule sex_rule as sex in(男,女)使用规则Sp_bindrule sex_rule,student.ssexSp_unbindrule student.ssex例:insert into student values(2003014,lisi,22,f,ec,管理学院,四川)将出现错误,93,在属性值上的约束-非空约束要求某属性取值不能为空值SNAME CHAR(8)NOT NULL-基于属性的检查子句CHECK(AGE15)-默认值 设置某属性为默认值,如 Sdept default 管理学院或者