数据库原理与应用(第二版)Chapter3课件.ppt

1、第三章第三章关系数据库关系数据库结构化查询语言结构化查询语言(SQL)(SQL)3.1 SQL语言概述语言概述 SQLSQL语言功能极强，但由于设计巧妙，语言十分语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制简洁，完成数据定义、数据操纵、数据控制的核心功能只用了的核心功能只用了9 9个动词：个动词：CREATECREATE、DROPDROP、ALTERALTER、SELECTSELECT、INSERTINSERT、UPDATEUPDATE、DELETEDELETE、GRANTGRANT、REVOKEREVOKE SQLSQL功能功能相关动词相关动词数据查询数据查询

2、SELECTSELECT数据定义数据定义CREATECREATE，DROPDROP，ALTERALTER数据操纵数据操纵INSERTINSERT，UPDATEUPDATE，DELETEDELETE数据控制数据控制GRANTGRANT，REVOTEREVOTE视图视图2视图视图1基本表基本表1存储文件存储文件1SQL基本表基本表2基本表基本表3基本表基本表4存储文件存储文件2外模式外模式模式模式内模式内模式SQLSQL语言支持关系数据库三级模式结构语言支持关系数据库三级模式结构 3.2 数据定义语言数据定义语言 SQLSQL的数据定义功能包括定义表、定义视的数据定义功能包括定义表、定义视图和定义

3、索引图和定义索引 操作对象操作对象操作方式操作方式创建创建删除删除修改修改表表CREATE TABLECREATE TABLEDROP TABLEDROP TABLEALTER TABLEALTER TABLE视图视图CREATE VIEWCREATE VIEWDROP VIEWDROP VIEW索引索引CREATE INDE XCREATE INDE XDROP INDEXDROP INDEX3.2.1 定义基本表定义基本表 格式：格式：CREATE TABLE CREATE TABLE 表名表名(列名数据类型列名数据类型列级完整性约束条件列级完整性约束条件 ，列名数据类型列级完整性约束，列

4、名数据类型列级完整性约束条件条件 ，表级完整性约束条件，表级完整性约束条件）；）；表名是所要定义的基本表的名字，它可以表名是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。由一个或多个属性（列）组成。3.2.2 修改基本表修改基本表 格式：格式：ALTER TABLE ADD 完整性约束完整性约束|DROP|MODIFY；其中其中 指定需要修改的基本表，指定需要修改的基本表，ADD子句用子句用于增加新列和新的完整性约束条件，于增加新列和新的完整性约束条件，DROP子句子句用于删除指定的完整性约束条件，用于删除指定的完整性约束条件，MODIFY子子句用于修改原有的列定义。句用于修改原有

5、的列定义。“|”表示每次使用该表示每次使用该命令时只能在命令时只能在ADD、DROP和和MODIFY中选择中选择其中之一进行使用。其中之一进行使用。例例3-1 3-1 建立学生档案表建立学生档案表XSDABXSDAB，其中属性包括：，其中属性包括：学 号（学 号（X HX H）、姓 名（）、姓 名（X MX M）、出 生 年 月）、出 生 年 月（CSNYCSNY）、性别（）、性别（XBXB）、民族（）、民族（MZMZ）、政治）、政治面貌（面貌（ZZMMZZMM）、班级编号（）、班级编号（BJBHBJBH），学号为），学号为主键，其值不能为空。主键，其值不能为空。Create Table xs

6、dabCreate Table xsdab(xhxh Char(10)CONSTRAINT pk1 PRIMARY KEY not null,Char(10)CONSTRAINT pk1 PRIMARY KEY not null,xmxm Char(8)not null,Char(8)not null,csnycsny Char(7),Char(7),xbxb Char(2),Char(2),mzmz Char(8),Char(8),zzmmzzmm Char(10),Char(10),bjbhbjbh Long Long)例例3-2 向向XSDAB表增加年龄（表增加年龄（NL）一列，）一列，

7、其数据类型为整型。其数据类型为整型。ALTER TABLE XSDAB ADD NL INTEGER；例例3-3 将将XSDAB中学号的数据类型改为整中学号的数据类型改为整数。数。ALTER TABLE XSDAB MODIFY XH INTEGER；3.2.3 删除基本表删除基本表 格式：格式：DROP TABLE 例例3-5 删除学生档案表（删除学生档案表（XSDAB）DROP TABLE XSDAB 3.2.4 建立索引建立索引 格式格式:CREATE UNIQUECLUSTER INDEX ON（，）说明：索引可建在表的一列或多列上，各列之说明：索引可建在表的一列或多列上，各列之间用逗

8、号分隔。次序指定索引值的排列间用逗号分隔。次序指定索引值的排列次序，次序，ASC（升序）、（升序）、DESC（降序）（降序）。UNIQUE：每一个索引值只对应唯一的数据记：每一个索引值只对应唯一的数据记录。录。CLUSTER：要建立的索引是聚簇索引。所谓聚：要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。顺序一致的索引组织。例例3-6 执行下面的执行下面的create index 语句：语句：CREATE CLUSTER INDEX Stusname ON XSDAB（XM）；）；将会在将会在XSDAB表的表的XM（

9、姓名）列上建立（姓名）列上建立一个聚簇索引，而且一个聚簇索引，而且XSDAB表中的记录表中的记录将按学号升序建立唯一索引。将按学号升序建立唯一索引。例例3-7 对学生档案表中的对学生档案表中的XH（学号）属性（学号）属性列建立唯一索引列建立唯一索引Sno:CREATE UNIQUE INDEX SNo ON XSDAB（XH）；）；3.2.5 删除索引删除索引 格式：格式：DROP INDEX索引名索引名例例3-9 删除删除 XSDAB表中表中Sno索引。索引。DROP INDEX Sno;注意：删除索引时，系统会同时从数据字注意：删除索引时，系统会同时从数据字典中删去有关该索引的描述。典中删

10、去有关该索引的描述。3.3 数据查询数据查询 数据查询语句是数据查询语句是SQL语言中最常用的语句，语言中最常用的语句，应灵活、全面掌握。应灵活、全面掌握。格式：格式：SELECT ALL|DISTINCT SELECT ALL|DISTINCT ，FROM FROM ，WHERE WHERE GROUP BY GROUP BY 1HAVING HAVING ORDER BY ORDER BY 2ASC|DESCASC|DESC；整个整个SELECT语句的含义：语句的含义：根据根据WHERE子句的条件表达式从子句的条件表达式从FROM子子句指定的基本表或视图中找出满足条件的元句指定的基本表或视

11、图中找出满足条件的元组，再按组，再按SELECT子句中的目标列表达式，子句中的目标列表达式，选出元组中的属性值形成结果表。选出元组中的属性值形成结果表。如果有如果有GROUP子句，则将结果按列名子句，则将结果按列名1的值进行分组，该属性列值相等的元组为一的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通个组，每个组产生结果表中的一条记录。通常会在每组中使用集函数。常会在每组中使用集函数。如果如果GROUP子句带子句带HAVING短语，则只有短语，则只有满足指定条件的元组才予以输出。满足指定条件的元组才予以输出。如果有如果有ORDER子句，则结果表还要按列子句，则结果表

12、还要按列名名2的值的升序或降序排序的值的升序或降序排序。3.3.1 单表查询单表查询 所谓所谓“单表查询单表查询”是指所要查询的数据都从一是指所要查询的数据都从一个数据表中获得。这种查询是数据库查询个数据表中获得。这种查询是数据库查询的最基本形式。的最基本形式。1.基本表或视图的查询基本表或视图的查询SELECT ALL|DISTINCT，FROM ，例如，从例如，从XSDAB中查询所有的学号信息：中查询所有的学号信息：SELECT XH FROM XSDAB2.目标列表达式的设置目标列表达式的设置(1)以基本表或视图中的字段作为目标列表达式以基本表或视图中的字段作为目标列表达式 例例3-10

13、 查询学生档案表中的学号查询学生档案表中的学号(XH)、姓名、姓名(XM)、出生年月、出生年月(CSNY)SELECT XH,XM,CSNY FROM XSDAB(2)若查询表或视图中的全部列，则可用星号若查询表或视图中的全部列，则可用星号（“*”）代替全部列）代替全部列 例例3-11 查询学生档案表中的全部内容查询学生档案表中的全部内容SELECT *FROM XSDAB该句相当于：该句相当于：SELECT XH,XM,CSNY,XB,MZ,ZZMM,BJBH FROM XSDAB(3）使用表达式）使用表达式 SELECT子句的目标列表达式，可以将查子句的目标列表达式，可以将查询出来的属性列

14、经过一定的计算后列出结果。询出来的属性列经过一定的计算后列出结果。例例3-12 查询出查询出XSDAB表中学生的学号、姓名和表中学生的学号、姓名和年龄，由于在该表中没有年龄字段，则必须年龄，由于在该表中没有年龄字段，则必须经过计算：（当前年经过计算：（当前年-出生年月中的年），当出生年月中的年），当前日期可以用前日期可以用DATE（）函数获得，日期中的（）函数获得，日期中的年份可以通过年份可以通过YEAR（）函数来获得。（）函数来获得。查询语句为：查询语句为：SELECT XH,XM,YEAR(DATE()-YEAR(CSNY)FROM XSDAB(4）函数的使用）函数的使用 COUNT（）：

15、统计元组个数，（）：统计元组个数，SUM（）（）：计算一列值的总和：计算一列值的总和 AVG（）（）：计算一列值的平均值：计算一列值的平均值 MAX（）（）：求一列值中的最大值：求一列值中的最大值 MIN（）（）：求一列值中的最小值：求一列值中的最小值例例3-13 查询查询XSDAB表的学生人数表的学生人数SELECT COUNT(*)FROM XSDAB 或或SELECT COUNT(XM)FROM XSDAB(5）AS 的使用的使用 例例3-17 查询成绩表查询成绩表XSCJB中的数据，并显示满中的数据，并显示满足如下条件的记录：若足如下条件的记录：若CJ及格则显示及格则显示“及格及格”汉

16、字，否则显示汉字，否则显示“不及格不及格”汉字。同时将及汉字。同时将及格与不及格所在的列改名为格与不及格所在的列改名为“成绩成绩”，XH列列改名为改名为“学号学号”SELECT XH AS 学号，学号，IIF(CJ=60,”及格及格”，”不及格不及格”)AS 成绩成绩 FROM XSCJB(6)DISTINCT 与与ALL的使用的使用例例3-18 在例在例3-16中查询学生档案表中所有的不中查询学生档案表中所有的不重复的姓氏。假定均为单姓，无复姓。重复的姓氏。假定均为单姓，无复姓。SELECT DISTINCT MID(XM,1,1)AS 姓姓FROM XSDAB3.Where子句子句(1)(

17、1)比较大小比较大小 例例3-19 3-19 查询查询XSDABXSDAB中的中的19821982年以前出生学生的年以前出生学生的全部信息全部信息SELECT SELECT*FROM XSDAB FROM XSDAB WHERE MID(CSNYWHERE MID(CSNY，1 1，4)19824)1982(2)(2)确定范围确定范围谓词谓词BETWEENBETWEENANDAND和和NOT BETWEENNOT BETWEENANDAND可以可以用来查找属性值在或不在指定范围内的元组，用来查找属性值在或不在指定范围内的元组，其中其中 BETWEENBETWEEN后是范围的下限（即低值），后是

18、范围的下限（即低值），ANDAND后是范围的上限（即高值）。后是范围的上限（即高值）。例例3-22 3-22 查询在查询在1982-011982-01与与1983-121983-12出生的同学信出生的同学信息息SELECT*FROM XSDAB WHERE CSNY BETWEEN 1982-01 AND 1983-12(3)(3)确定集合确定集合谓词谓词IN用于查找属性值属于指定集合的元组。用于查找属性值属于指定集合的元组。与与IN相对的谓词是相对的谓词是NOT IN，用于查找属性值，用于查找属性值不属于指定集合的元组。不属于指定集合的元组。例例3-24 查询姓名为查询姓名为“陈海峰陈海峰”

19、、“孙磊孙磊”、“李莹莹李莹莹”、“连升辉连升辉”和和“黄沙黄沙”的全部的全部同学信息同学信息SELECT*FROM XSDAB WHERE XM IN(“陈海峰陈海峰”,“孙磊孙磊”,“李莹莹李莹莹”,“连升连升辉辉”,“黄沙黄沙”)(4)(4)字符匹配字符匹配列名列名 NOT LIKE 查找指定的属性查找指定的属性列值与匹配串相匹配的元组。匹配串列值与匹配串相匹配的元组。匹配串可以是一个完整的字符串，也可以是用可以是一个完整的字符串，也可以是用“?”和和“*”表示的任意一个字符和任意多个字符。表示的任意一个字符和任意多个字符。例例3-25 查询学号的第查询学号的第8位为位为”1”的学生信息

20、的学生信息SELECT*FROM XSDAB WHERE XH LIKE“00000001*”(5)(5)涉及空值的查询涉及空值的查询谓词谓词IS NULL和和IS NOT NULL可用来查询空值可用来查询空值和非空值。空值是一类非常特殊的值，它的和非空值。空值是一类非常特殊的值，它的判断应该用判断应该用 IS NULL 和和IS NOT NULL 或或ISNULL（）函数来确定。（）函数来确定。例例3-27 查询政治面貌为空的学生信息。查询政治面貌为空的学生信息。SELECT*FROM XSDAB WHERE ISNULL（ZZMM）或者：或者：SELECT*FROM XSDAB WHERE

21、 ZZMM IS NULL(6)(6)多重条件查询多重条件查询逻辑运算符逻辑运算符NOTNOT、ANDAND和和OROR可用来联结多个查询可用来联结多个查询个体。如果这三个运算符同时出现在同一个个体。如果这三个运算符同时出现在同一个WHEREWHERE条件子句中则条件子句中则NOTNOT的优先级高于的优先级高于ANDAND，而而ANDAND的优先级又高于的优先级又高于OROR，但用户可以用括号，但用户可以用括号明确显示并改变优先级。明确显示并改变优先级。例例3-28 3-28 查询查询19821982年以后出生的党员学生的信息。年以后出生的党员学生的信息。SELECT*FROM XSDAB W

22、HERE ZZMM=党员党员 AND MID(CSNY,1,4)=1982 4.对显示结果的排序对显示结果的排序例例3-31 3-31 查询所有学生信息，并按姓名的拼音顺查询所有学生信息，并按姓名的拼音顺序进行降序序进行降序(用用DESCDESC表示表示)排序。排序。SELECT*FROM XSDAB ORDER BY XM DESC5.5.对查询结果分组对查询结果分组例例3-32 对对XSDAB表中的班级进行分组查询，得表中的班级进行分组查询，得出每班的学生人数：出每班的学生人数：SELECT BJBH,COUNT(*)as 人数人数 FROM XSDAB GROUP BY BJBH注意：如

23、果查询结果为多个列与注意：如果查询结果为多个列与count、sum、avg等函数计算的结果混合在一起，那么必须等函数计算的结果混合在一起，那么必须用用group by子句，并且必须按没有用以上函数子句，并且必须按没有用以上函数的所有列进行分组。的所有列进行分组。例例3-33 查询每个班各类政治面貌的人数。查询每个班各类政治面貌的人数。SELECT BJBH,ZZMM,COUNT(*)AS 人数人数 FROM XSDAB GROUP BY BJBH,ZZMM 例例3-34 查询每个班团员的人数。查询每个班团员的人数。SELECT BJBH,ZZMM,COUNT(*)AS 人数人数 FROM XS

24、DAB GROUP BY BJBH,ZZMMHAVING ZZMM=团员团员“请注意本例子与上一个例子的区别：由于请注意本例子与上一个例子的区别：由于ZZMM有多种有多种值，而我们只想查询团员的信息，所以必须用值，而我们只想查询团员的信息，所以必须用HAVING子句。子句。3.3.2 连接查询连接查询 连接查询是指从多个表中查询数据，由于表和表连接查询是指从多个表中查询数据，由于表和表之间数据之间可能存在某种联系，所以需要之间数据之间可能存在某种联系，所以需要进行连接处理。进行连接处理。1.1.等值与非等值连接查询等值与非等值连接查询 连接表达式格式：连接表达式格式：1 2 2比较运算符：比较

25、运算符：=、=、=、!连接谓词形式：连接谓词形式：1 BETWEEN BETWEEN 2 AND AND 2 3例例3-35 3-35 查询成绩表并输出姓名和对应的成绩。查询成绩表并输出姓名和对应的成绩。因为成绩表中只有学号因为成绩表中只有学号(XH)(XH)，没有姓名，没有姓名（XMXM），故只能在学生档案表中取学生姓名。），故只能在学生档案表中取学生姓名。SELECT XSDAB.XM AS 姓名姓名,XSCJB.CJ AS 成绩成绩 FROM XSDAB,XSCJBWHERE XSDAB.XH=XSCJB.XH由于由于XSDAB和和XSCJB各有各有XM和和CJ列，所以该句也列，所以该句

26、也可以写成：可以写成：SELECT XM AS 姓名姓名,CJ AS 成绩成绩 FROM XSDAB,XSCJBWHERE XSDAB.XH=XSCJB.XH2.2.自身连接自身连接 连接操作可以是一个表与自己进行连接，这种连接操作可以是一个表与自己进行连接，这种连接称为表的自身连接。连接称为表的自身连接。例例3-37 3-37 学生档案表中只有一个学生的民族为蒙学生档案表中只有一个学生的民族为蒙古族，查询学生档案中每个学生的古族，查询学生档案中每个学生的“蒙古族蒙古族”同学名称。同学名称。SELECT A2.XH AS SELECT A2.XH AS 学号学号,A2.XM AS,A2.XM

27、AS 姓名姓名,A2.MZ AS A2.MZ AS 民族民族,A1.XM AS,A1.XM AS 蒙族同学姓名蒙族同学姓名FROM XSDAB A1,XSDAB A2FROM XSDAB A1,XSDAB A2WHERE A1.MZ=WHERE A1.MZ=蒙古族蒙古族 AND A2.MZ AND A2.MZ蒙古族蒙古族 3.3.复合条件连接复合条件连接在一个连接查询中，在一个连接查询中，WHEREWHERE子句中有多个条件的子句中有多个条件的连接操作，称为复合条件连接。连接操作，称为复合条件连接。例例3-38 3-38 查询成绩表并输出任何成绩小于查询成绩表并输出任何成绩小于6060分的分的

28、学生姓名和成绩信息。因为成绩表中只有学学生姓名和成绩信息。因为成绩表中只有学号号(XH)(XH)，没有姓名（，没有姓名（XMXM），故只能在学生档），故只能在学生档案表中取学生姓名。案表中取学生姓名。SELECT XM AS 姓名姓名,CJ AS 成绩成绩 FROM XSDAB,XSCJB WHERE XSDAB.XH=XSCJB.XH AND CJ603.3.3 嵌套查询嵌套查询 在在SQLSQL语言中，一个语言中，一个“SELECTSELECTFROMFROMWHERE”WHERE”语句称为一个查询块。将一个查询块嵌套在语句称为一个查询块。将一个查询块嵌套在另一个查询块的另一个查询块的WH

29、EREWHERE子句或子句或HAVINGHAVING短语的条短语的条件中的查询称为嵌套查询或子查询。件中的查询称为嵌套查询或子查询。例例3-40 3-40 查询所有成绩均为及格的学生名单查询所有成绩均为及格的学生名单SELECT XH,XM FROM XSDAB WHERE XH NOT IN（SELECT XH FROM XSCJB WHERE CJ60）1.1.带有带有IN谓词的子查询谓词的子查询 带有带有ININ谓词的子查询是指父查询与子查询之间谓词的子查询是指父查询与子查询之间用用ININ进行连接进行连接 例例3-41 3-41 查询班级名称为查询班级名称为“20002000计算机应用

30、班计算机应用班”的学生档案，数据从学生档案表（的学生档案，数据从学生档案表（XSDABXSDAB）和班级表（和班级表（BJBBJB）中提取。）中提取。SELECT*FROM XSDAB WHERE BJBH IN(S E L E C T B J B H f r o m B J B W H E R E BJMC=“2000计算机应用班计算机应用班)2.2.带有比较运算符的子查询带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、道内层

31、查询返回的是单值时，可以用、=、=、=、！、！=或等比较运算符。或等比较运算符。例例3-43 3-43 查询和学号为查询和学号为“20000001”20000001”的同学同年的同学同年同月出生的同学信息（。由于一个学生只能有同月出生的同学信息（。由于一个学生只能有一个出生年月值，所以子查询一定返回单值。一个出生年月值，所以子查询一定返回单值。SELECT*FROM XSDAB WHERE CSNY=(SELECT CSNY FROM XSDAB WHERE XH=”20000001”）3.3.带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 子查询返回单值时可以用比较运算符，而使

32、用子查询返回单值时可以用比较运算符，而使用ANYANY或或ALLALL谓词时则必须和该谓词同时使用比谓词时则必须和该谓词同时使用比较运算符。较运算符。例例3-44 3-44 查询比查询比 陈海峰陈海峰、孙磊孙磊、李莹莹李莹莹、连升辉连升辉 等同学年龄小的同学信息。等同学年龄小的同学信息。SELECT SELECT*FROM XSDAB FROM XSDAB WHERE CSNYANY(WHERE CSNYANY大于子查询中的某个值大于子查询中的某个值ALL 大于子查询中的所有值大于子查询中的所有值ANY 小于子查询中的某个值小于子查询中的某个值ALL 小于子查询中的所有值小于子查询中的所有值=

33、ANY 等于子查询中的某个值等于子查询中的某个值=ALL 等于子查询中的所有值等于子查询中的所有值ANY 不等于子查询中的某个值不等于子查询中的某个值ALL 不等于子查询中的所有值不等于子查询中的所有值4.4.带有带有existsexists谓词的子查询谓词的子查询existsexists代表存在量词代表存在量词()。其子查询不返回任何数据，。其子查询不返回任何数据，只产生逻辑真假值。只产生逻辑真假值。例例3-46 3-46 查询没有参加查询没有参加“C C语言语言”课程考试的所有同学档课程考试的所有同学档案信息。案信息。SELECT*FROM XSDAB WHERE NOT EXISTS(S

34、ELECT*FROM XSCJB WHERE XH=XSDAB.XH ANDPKBH IN(SELECT DISTINCT PKBH FROM PKQKBWHERE KCBH=(SELECT KCBH FROM KCMCB WHERE KCMC=C语言语言)3.3.4 集合查询集合查询 集合操作主要包括并操作集合操作主要包括并操作UNIONUNION、交操作、交操作INTERSECTINTERSECT和差和差操作操作MINUSMINUS。比较常用的是。比较常用的是UNIONUNION操作。操作。使用使用UNIONUNION将将多个查询结果合并起来，形成一个完整的查询结果时，多个查询结果合并起来

35、，形成一个完整的查询结果时，系统会自动去掉重复的元组。系统会自动去掉重复的元组。注意：参加注意：参加UNIONUNION操作的多个元组的数据列名和类型必操作的多个元组的数据列名和类型必须完全相同。须完全相同。例例3-47 3-47 查询所有查询所有19821982年以后出生以及政治面貌为党员年以后出生以及政治面貌为党员的学生信息。的学生信息。SELECT*FROM XSDAB WHERE MID(CSNY,1,4)1982 UNION SELECT*FROM XSDAB WHERE ZZMM=党员党员 3.4 数据更新数据更新 SQL中数据更新包括插入数据中数据更新包括插入数据（INSERT）

36、、修改数据（）、修改数据（UPDATE）和删除数据（和删除数据（DELETE）三条语句。）三条语句。利用它们可以对数据表进行数据更新利用它们可以对数据表进行数据更新操作。操作。3.4.1 插入数据插入数据1 1、插入单个元组、插入单个元组格式：格式：INSERT INTO INSERT INTO(,2)VALUES)VALUES（1，2）说明：新记录属性列说明：新记录属性列1 1的值为常量的值为常量1 1，属性列，属性列2 2的的值为常量值为常量2 2，如果某些列名在属性列中没有出，如果某些列名在属性列中没有出现，则新记录的这些列将取空值。现，则新记录的这些列将取空值。例例3-48 向学生档案

37、表中增加一名学生，其学号向学生档案表中增加一名学生，其学号为为“20000198”20000198”，姓名为，姓名为“赵亮赵亮”，出生年，出生年月为月为“1979-01”1979-01”，性别为，性别为“男男”，民族为，民族为“汉汉”，政面面貌为，政面面貌为“团员团员”,班级编号为班级编号为“2”2”。INSERT INTO XSDABINSERT INTO XSDAB(XH,XM,CSNY,XB,MZ,ZZMM,BJBH)(XH,XM,CSNY,XB,MZ,ZZMM,BJBH)VALUES(20000198,VALUES(20000198,赵亮赵亮,1979-01,1979-01,男男,汉汉

38、,团员团员,2),2)2 2、插入子查询结果、插入子查询结果 子查询可以嵌套在子查询可以嵌套在INSERTINSERT语句中，用以生成要语句中，用以生成要插入的数据，格式为：插入的数据，格式为：INSERT INTOINSERT INTO表名（属性列表名（属性列1 1，属性列属性列2 2）子查询）子查询例例3-49 3-49 将成绩表中所有不及格同学的成绩信息将成绩表中所有不及格同学的成绩信息添加到备份表添加到备份表CJBFBCJBFB中。中。INSERT INTO CJBFB INSERT INTO CJBFB SELECT SELECT*FROM XSCJB FROM XSCJB WHER

39、E CJ60WHERE CJ60注意：注意：插入子查询结果时，必须预先建立被插插入子查询结果时，必须预先建立被插入表，如上例中的入表，如上例中的CJBFBCJBFB 3.4.2 修改数据修改数据 修改语句格式：修改语句格式：UPDATE SET=，=WHERE；功能：修改指定表中满足功能：修改指定表中满足WHERE子句条件的子句条件的元组。元组。SET子句用于指定修改方法，即用子句用于指定修改方法，即用表达式的值取代相应的属性列值。如果省表达式的值取代相应的属性列值。如果省略略WHERE子句，则表示要修改表中的所有元子句，则表示要修改表中的所有元组。组。例例3-51 将将XSDAB中学号为中学

40、号为“20000001”的学生的学生的政治面貌改为的政治面貌改为“党员党员”。UPDATE XSDAB SET ZZMM=“党员党员”WHERE XH=200000013.4.3 删除数据删除数据 删除语句格式：删除语句格式：DELETE FROM DELETE FROM WHERE WHERE 功能：从指定表删除满足功能：从指定表删除满足WHEREWHERE子句条件的所有子句条件的所有元组。如果省略元组。如果省略WHEREWHERE子句，表示删除表中全子句，表示删除表中全部元组，但表的定义仍存在部元组，但表的定义仍存在 1 1、删除一个元组的值、删除一个元组的值例例3-54 3-54 学号为

41、学号为“20000109”20000109”的学生因故退学。的学生因故退学。所以将其数据从所以将其数据从XSDABXSDAB中删除中删除DELETE FROM XSDAB WHERE XH=20000109DELETE FROM XSDAB WHERE XH=20000109 2 2、删除多个元组的值、删除多个元组的值例例3-55 3-55 删除删除XSCJBXSCJB中所有学生的考试成绩中所有学生的考试成绩DELETE FROM XSCJBDELETE FROM XSCJB3 3、带子查询的删除语句子、带子查询的删除语句子查询同样也可以嵌套在查询同样也可以嵌套在DELETEDELETE语句中

42、，用以构语句中，用以构造执行删除操作的条件。造执行删除操作的条件。例例3-56 3-56 删除删除XSCJBXSCJB中所有男生的考试成绩中所有男生的考试成绩DELETE DELETE FROM XSCJB FROM XSCJB WHERE XH IN WHERE XH IN（SELECT XH FROM XSDAB WHERE XB=SELECT XH FROM XSDAB WHERE XB=男男）3.5 视图视图 视图是从一个或几个基本表或视图中导出的虚视图是从一个或几个基本表或视图中导出的虚表。它并不像基本表一样存储元组，而是只表。它并不像基本表一样存储元组，而是只保存导出数据的查询语句

43、保存导出数据的查询语句3.5.1 3.5.1 定义视图定义视图CREATE VIEW CREATE VIEW（，）ASASWITH CHECK OPTIONWITH CHECK OPTION说明：说明：(1)(1)WITH CHECK OPTIONWITH CHECK OPTION表示对视图进行表示对视图进行UPDATEUPDATE，INSERTINSERT和和 DELETEDELETE操作时要保证更新、插入或删除的操作时要保证更新、插入或删除的行满足视图定义中的谓词条件。行满足视图定义中的谓词条件。(2)(2)如果如果CREATE VIEWCREATE VIEW语句仅指定了视图名，省略了组成

44、语句仅指定了视图名，省略了组成视图的各个属性列名，则隐含该视图由子查询中视图的各个属性列名，则隐含该视图由子查询中SELECTSELECT子句目标列中的所有字段组成子句目标列中的所有字段组成 (3)(3)在下列三种情况下必须明确指定组成视图的所有列在下列三种情况下必须明确指定组成视图的所有列名：名：其中某个目标列不是单纯的属性名，而是集函数或其中某个目标列不是单纯的属性名，而是集函数或列表达式。列表达式。多表连接时选出了几个同名列作为视图的字段。多表连接时选出了几个同名列作为视图的字段。需要在视图中为某个列启用新的更合适的名字。需要在视图中为某个列启用新的更合适的名字。例例3-57 3-57

45、建立学生档案的建立学生档案的VIEW_DAVIEW_DA视图，可查询视图，可查询出学号、姓名、出生年月、性别、民族、政出学号、姓名、出生年月、性别、民族、政治面貌、班级名称、专业名称和系别名称。治面貌、班级名称、专业名称和系别名称。CREATE VIEW VIEW_DA AS CREATE VIEW VIEW_DA AS SELECT A.XH,A.XM,A.CSNY,A.XB,A.MZ,SELECT A.XH,A.XM,A.CSNY,A.XB,A.MZ,A.ZZMM,B.BJMC,C.ZYMC,D.XBMCA.ZZMM,B.BJMC,C.ZYMC,D.XBMCFROM XSDAB A,BJB

46、 B,ZYMCB C,XBMCB DFROM XSDAB A,BJB B,ZYMCB C,XBMCB DWHERE A.BJBH=B.BJBH AND B.ZYBH=C.ZYBH AND WHERE A.BJBH=B.BJBH AND B.ZYBH=C.ZYBH AND C.XBBH=D.XBBHC.XBBH=D.XBBH3.5.2 删除视图删除视图 格式：格式：DROP VIEW DROP VIEW 视图名；视图名；一个视图被删除后，由该视图导出的其他视图一个视图被删除后，由该视图导出的其他视图也将失效也将失效。3.5.3 查询视图查询视图DBMSDBMS执行对视图的查询时，首先进行有效性检

47、执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据查，检查查询涉及的表、视图等是否在数据库中存在。如果存在，则从数据字典中取出库中存在。如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查本表的查询，然后再执行这个经过修正的查询。询。将对视图的查询转换为对基本表的查询的过程将对视图的查询转换为对基本表的查询的过程称为视图的消解。称为视图的消解。3.5.4 更新视图更新视图 更新视图：更新视图：INSERTI

48、NSERT、DELETEDELETE、UPDATEUPDATE三类操作。三类操作。首先进行有效性检查，检查所涉及的表、视图首先进行有效性检查，检查所涉及的表、视图等是否在数据库中存在，如果存在，则从数等是否在数据库中存在，如果存在，则从数据字典中取出该语句涉及的视图的定义，把据字典中取出该语句涉及的视图的定义，把定义中的子查询和用户对视图的更新操作结定义中的子查询和用户对视图的更新操作结合起来，转换成对基本表的更新，然后再执合起来，转换成对基本表的更新，然后再执行这个经过修正的更新操作行这个经过修正的更新操作 3.5.5 视图的用途视图的用途(1)(1)视图能够简化用户的操作视图能够简化用户的

49、操作(2)(2)视图使用户能以多种角度看待同一数据视图使用户能以多种角度看待同一数据(3)(3)视图对重构数据库提供了一定程度的逻辑视图对重构数据库提供了一定程度的逻辑独立性独立性(4)(4)视图能够对机密数据提供安全保护视图能够对机密数据提供安全保护3.6 3.6 数据控制数据控制包括数据的安全性控制、完整性控制、并发控包括数据的安全性控制、完整性控制、并发控制和恢复制和恢复 3.6.1 授权授权语句语句GRANTGRANTGRANT语句的一般格式为：语句的一般格式为：GRANT GRANT ,ON ON TO TO,WITH GRANT OPTIONWITH GRANT OPTION；其语

50、义为将对指定操作对象的指定操作权限授其语义为将对指定操作对象的指定操作权限授予指定的用户。予指定的用户。例例3-58 3-58 把查询把查询XSDABXSDAB的权限授给用户的权限授给用户A,A,同时允许同时允许A A将此权利授给其它用户将此权利授给其它用户GRANT SELECT ON XSDAB TO A WITH GRANT GRANT SELECT ON XSDAB TO A WITH GRANT OPTIONOPTION例例3-59 3-59 把对成绩表把对成绩表XSCJBXSCJB的全部权限授给用户的全部权限授给用户B B和和C CGRANT ALL PRIVILIGES ON X