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

1、第三章关系数据库结构化查询语言(SQL)3.1 SQL语言概述 SQL语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词：CREATE、DROP、ALTER、SELECT、 INSERT、UPDATE、DELETE、GRANT、REVOKE SQL功能相关动词数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT， UPDATE， DELETE数据控制GRANT，REVOTE视图2视图1基本表1存储文件1SQL基本表2基本表3基本表4存储文件2外模式模式内模式SQL语言支持关系数据库三级模式结构 3.2 数据定义语言

2、 SQL的数据定义功能包括定义表、定义视图和定义索引 操作对象操作方式创建删除修改表CREATE TABLEDROP TABLEALTER TABLE视图CREATE VIEWDROP VIEW索引CREATE INDE XDROP INDEX3.2.1 定义基本表 格式：CREATE TABLE 表名(列名数据类型列级完整性约束条件 ，列名数据类型列级完整性约束条件 ，表级完整性约束条件）；表名是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。 3.2.2 修改基本表 格式：ALTER TABLE ADD 完整性约束| DROP | MODIFY ；其中 指定需要修改的基本表， A

3、DD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。“|”表示每次使用该命令时只能在ADD、DROP和MODIFY中选择其中之一进行使用。例3-1 建立学生档案表XSDAB，其中属性包括：学号（XH）、姓名（XM）、出生年月（CSNY）、性别（XB）、民族（MZ）、政治面貌（ZZMM）、班级编号（BJBH），学号为主键，其值不能为空。Create Table xsdab(xh Char(10) CONSTRAINT pk1 PRIMARY KEY not null,xm Char(8) not null,csny Char(

4、7),xb Char(2),mz Char(8),zzmm Char(10),bjbh Long)例3-2 向XSDAB表增加年龄（NL）一列，其数据类型为整型。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 （，）说明：索引可建在表的一列或多列上，

5、各列之间用逗号分隔。次序指定索引值的排列次序，ASC（升序）、DESC（降序）。UNIQUE：每一个索引值只对应唯一的数据记录。CLUSTER：要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。 例3-6 执行下面的create index 语句：CREATE CLUSTER INDEX Stusname ON XSDAB（XM）；将会在XSDAB表的XM（姓名）列上建立一个聚簇索引，而且XSDAB表中的记录将按学号升序建立唯一索引。 例3-7 对学生档案表中的XH（学号）属性列建立唯一索引Sno:CREATE UNIQUE INDEX SNo ON XS

6、DAB（XH）；3.2.5 删除索引 格式：DROP INDEX索引名例3-9 删除 XSDAB表中Sno索引。DROP INDEX Sno;注意：删除索引时，系统会同时从数据字典中删去有关该索引的描述。 3.3 数据查询 数据查询语句是SQL语言中最常用的语句，应灵活、全面掌握。格式：SELECT ALL | DISTINCT ，FROM ，WHERE GROUP BY HAVING ORDER BY ASC | DESC；整个SELECT语句的含义：根据WHERE子句的条件表达式从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形

7、成结果表。如果有GROUP子句，则将结果按列名1的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中使用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的元组才予以输出。如果有ORDER子句，则结果表还要按列名2的值的升序或降序排序 。3.3.1 单表查询 所谓“单表查询”是指所要查询的数据都从一个数据表中获得。这种查询是数据库查询的最基本形式。1. 基本表或视图的查询SELECT ALL | DISTINCT ，FROM ，例如，从XSDAB中查询所有的学号信息：SELECT XH FROM XSDAB2. 目标列表达式的设置 以基本表或视

8、图中的字段作为目标列表达式 例3-10 查询学生档案表中的学号(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子句的目标列表达式，可以将查询出来的属性列经过一定的计算后列出结果。例3-12 查询出XSDAB表中学生的学号、姓名和年龄，由于在该表中没有年龄字段，则必须经过计算

9、：（当前年-出生年月中的年），当前日期可以用DATE（）函数获得，日期中的年份可以通过YEAR（）函数来获得。查询语句为：SELECT XH,XM,YEAR(DATE()-YEAR(CSNY) FROM XSDAB(4）函数的使用 COUNT（）：统计元组个数， SUM（） ：计算一列值的总和 AVG（） ：计算一列值的平均值 MAX（） ：求一列值中的最大值 MIN（） ：求一列值中的最小值例3-13 查询XSDAB表的学生人数SELECT COUNT(*) FROM XSDAB 或SELECT COUNT(XM) FROM XSDAB(5）AS 的使用 例3-17 查询成绩表XSCJB中的

10、数据，并显示满足如下条件的记录：若CJ及格则显示“及格”汉字，否则显示“不及格”汉字。同时将及格与不及格所在的列改名为“成绩”，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)比较大小 例3-19 查询XSDAB中的1982年以前出生学生的全部信息SELECT * FROM XSDAB

11、WHERE MID (CSNY，1，4)1982(2) 确定范围谓词BETWEENAND和NOT BETWEENAND可以用来查找属性值在或不在指定范围内的元组，其中 BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。例3-22 查询在1982-01与1983-12出生的同学信息SELECT * FROM XSDAB WHERE CSNY BETWEEN 1982-01 AND 1983-12(3) 确定集合谓词IN用于查找属性值属于指定集合的元组。与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。例3-24 查询姓名为“陈海峰”、“孙磊”、“李莹莹”、

12、“连升辉”和“黄沙”的全部同学信息SELECT * FROM XSDAB WHERE XM IN (“陈海峰”,“孙磊”,“李莹莹”,“连升辉”,“黄沙”)(4) 字符匹配列名 NOT LIKE 查找指定的属性列值与匹配串相匹配的元组。匹配串可以是一个完整的字符串，也可以是用“?”和“*”表示的任意一个字符和任意多个字符。例3-25 查询学号的第8位为”1”的学生信息SELECT * FROM XSDAB WHERE XH LIKE “00000001*” (5) 涉及空值的查询谓词IS NULL和IS NOT NULL可用来查询空值和非空值。空值是一类非常特殊的值，它的判断应该用 IS NU

13、LL 和IS NOT NULL 或ISNULL（）函数来确定。例3-27 查询政治面貌为空的学生信息。SELECT * FROM XSDAB WHERE ISNULL（ZZMM）或者：SELECT * FROM XSDAB WHERE ZZMM IS NULL (6)多重条件查询逻辑运算符NOT、AND和OR可用来联结多个查询个体。如果这三个运算符同时出现在同一个WHERE条件子句中则NOT的优先级高于AND，而AND的优先级又高于OR，但用户可以用括号明确显示并改变优先级。例3-28 查询1982年以后出生的党员学生的信息。SELECT * FROM XSDAB WHERE ZZMM=党员

14、AND MID(CSNY,1,4)=1982 4. 对显示结果的排序例3-31 查询所有学生信息，并按姓名的拼音顺序进行降序(用DESC表示)排序。SELECT * FROM XSDAB ORDER BY XM DESC5. 对查询结果分组例3-32 对XSDAB表中的班级进行分组查询，得出每班的学生人数：SELECT BJBH,COUNT(*) as 人数 FROM XSDAB GROUP BY BJBH注意：如果查询结果为多个列与count、sum、avg等函数计算的结果混合在一起，那么必须用group by子句，并且必须按没有用以上函数的所有列进行分组。例3-33 查询每个班各类政治面貌

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

16、、=、=、!连接谓词形式： BETWEEN AND 例3-35 查询成绩表并输出姓名和对应的成绩。因为成绩表中只有学号(XH)，没有姓名（XM），故只能在学生档案表中取学生姓名。SELECT XSDAB.XM AS 姓名, XSCJB.CJ AS 成绩 FROM XSDAB, XSCJBWHERE XSDAB.XH= XSCJB.XH由于XSDAB和XSCJB各有XM和CJ列，所以该句也可以写成：SELECT XM AS 姓名, CJ AS 成绩 FROM XSDAB, XSCJBWHERE XSDAB.XH= XSCJB.XH2. 自身连接 连接操作可以是一个表与自己进行连接，这种连接称为表

17、的自身连接。例3-37 学生档案表中只有一个学生的民族为蒙古族，查询学生档案中每个学生的“蒙古族”同学名称。SELECT A2.XH AS 学号,A2.XM AS 姓名,A2.MZ AS 民族, A1.XM AS 蒙族同学姓名FROM XSDAB A1 ,XSDAB A2WHERE A1.MZ=蒙古族 AND A2.MZ蒙古族3. 复合条件连接在一个连接查询中，WHERE子句中有多个条件的连接操作，称为复合条件连接。例3-38 查询成绩表并输出任何成绩小于60分的学生姓名和成绩信息。因为成绩表中只有学号(XH)，没有姓名（XM），故只能在学生档案表中取学生姓名。SELECT XM AS 姓名,

18、 CJ AS 成绩 FROM XSDAB, XSCJB WHERE XSDAB.XH= XSCJB.XH AND CJ603.3.3 嵌套查询 在SQL语言中，一个“SELECTFROMWHERE”语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。例3-40 查询所有成绩均为及格的学生名单SELECT XH,XM FROM XSDAB WHERE XH NOT IN（ SELECT XH FROM XSCJB WHERE CJ60）1. 带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接 例3-4

19、1 查询班级名称为“2000计算机应用班”的学生档案，数据从学生档案表（XSDAB）和班级表（BJB）中提取。SELECT * FROM XSDAB WHERE BJBH IN ( SELECT BJBH from BJB WHERE BJMC=“2000计算机应用班)2. 带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、=、=、=、！=或等比较运算符。例3-43 查询和学号为“20000001”的同学同年同月出生的同学信息（。由于一个学生只能有一个出生年月值，所以子查询一定返回单值。SELECT * FR

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

21、 不等于子查询中的所有值4. 带有exists谓词的子查询exists代表存在量词()。其子查询不返回任何数据，只产生逻辑真假值。例3-46 查询没有参加“C语言”课程考试的所有同学档案信息。SELECT * FROM XSDAB WHERE NOT EXISTS( SELECT * FROM XSCJB WHERE XH=XSDAB.XH ANDPKBH IN(SELECT DISTINCT PKBH FROM PKQKBWHERE KCBH=( SELECT KCBH FROM KCMCB WHERE KCMC= C语言) )3.3.4 集合查询 集合操作主要包括并操作UNION、交操作I

22、NTERSECT和差操作MINUS。比较常用的是UNION操作。使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。注意：参加UNION操作的多个元组的数据列名和类型必须完全相同。例3-47 查询所有1982年以后出生以及政治面貌为党员的学生信息。SELECT * FROM XSDAB WHERE MID(CSNY,1,4)1982 UNION SELECT * FROM XSDAB WHERE ZZMM=党员 3.4 数据更新 SQL中数据更新包括插入数据（INSERT）、修改数据（UPDATE）和删除数据（DELETE）三条语句。利用它们可以对数据表进

23、行数据更新操作。3.4.1 插入数据1、插入单个元组格式：INSERT INTO (,) VALUES（，）说明：新记录属性列1的值为常量1，属性列2的值为常量2，如果某些列名在属性列中没有出现，则新记录的这些列将取空值。例3-48 向学生档案表中增加一名学生，其学号为“20000198”，姓名为“赵亮”，出生年月为“1979-01”，性别为“男”，民族为“汉”，政面面貌为“团员”,班级编号为“2”。INSERT INTO XSDAB(XH,XM,CSNY,XB,MZ,ZZMM,BJBH)VALUES (20000198,赵亮,1979-01,男,汉,团员,2)2、插入子查询结果 子查询可以嵌

24、套在INSERT语句中，用以生成要插入的数据，格式为：INSERT INTO表名（属性列1，属性列2）子查询例3-49 将成绩表中所有不及格同学的成绩信息添加到备份表CJBFB中。INSERT INTO CJBFB SELECT * FROM XSCJB WHERE CJ60注意：插入子查询结果时，必须预先建立被插入表，如上例中的CJBFB 3.4.2 修改数据 修改语句格式：UPDATE SET =，=WHERE；功能：修改指定表中满足WHERE子句条件的元组。SET子句用于指定修改方法，即用表达式的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。例3-51 将XS

25、DAB中学号为“20000001”的学生的政治面貌改为“党员”。UPDATE XSDAB SET ZZMM=“党员” WHERE XH=200000013.4.3 删除数据 删除语句格式：DELETE FROM WHERE 功能：从指定表删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍存在 1、删除一个元组的值例3-54 学号为“20000109”的学生因故退学。所以将其数据从XSDAB中删除DELETE FROM XSDAB WHERE XH=20000109 2、删除多个元组的值例3-55 删除XSCJB中所有学生的考试成绩DELETE FR

26、OM XSCJB3、带子查询的删除语句子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。例3-56 删除XSCJB中所有男生的考试成绩DELETE FROM XSCJB WHERE XH IN （SELECT XH FROM XSDAB WHERE XB=男）3.5 视图 视图是从一个或几个基本表或视图中导出的虚表。它并不像基本表一样存储元组，而是只保存导出数据的查询语句3.5.1 定义视图CREATE VIEW （，）ASWITH CHECK OPTION说明：(1)WITH CHECK OPTION表示对视图进行UPDATE， INSERT和 DELETE操作时要保证更

27、新、插入或删除的行满足视图定义中的谓词条件。(2)如果CREATE VIEW语句仅指定了视图名，省略了组成视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的所有字段组成 (3)在下列三种情况下必须明确指定组成视图的所有列名： 其中某个目标列不是单纯的属性名，而是集函数或列表达式。 多表连接时选出了几个同名列作为视图的字段。 需要在视图中为某个列启用新的更合适的名字。例3-57 建立学生档案的VIEW_DA视图，可查询出学号、姓名、出生年月、性别、民族、政治面貌、班级名称、专业名称和系别名称。CREATE VIEW VIEW_DA AS SELECT A.XH, A.XM, A.CSNY, A.XB, A.MZ, A.ZZMM, B.BJMC, C.ZYMC, D.XBMCFROM XSDAB A, BJB B, ZYMCB C, XBMCB DWHERE A.BJBH=B.BJBH AND B.ZYBH=C.ZYBH AND C