【教学课件】第三章关系数据库标准语言-SQL.ppt

《【教学课件】第三章关系数据库标准语言-SQL.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第三章关系数据库标准语言-SQL.ppt（52页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第三章 关系数据库标准语言SQLSQL3.1 SQLSQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图03.1 SQL概述关系数据库的标准语言结构化查询语言(Structured Query Language)(Structured Query Language)，简称SQL。它是一种非过程化的、功能极强的、通用的数据库语言，几乎所有的关系数据库管理系统都支持SQL。1.SQL数据库的体系结构要点 SQL语言支持关系数据库三级模式结构:13.1 SQL概述基本表1 1基本表2 2基本表3 3基本表4 4视图2 2视图1 1存储文件1 1存储文件2 2SQLSQL用户外模式模式存

2、储模式23.1 SQL概述(1)(1)一个SQLSQL数据库是表的汇集。它用一个或多个SQLSQL模式定义。一个SQLSQL模式是表和授权的静态定义。(2)(2)一个SQLSQL表由行的集合构成，一行是列的序列，每列对应一个数据项。(3)(3)一个表可以是一个基本表或是一个视图。基本表是实际存储在数据库中的表，而视图是由基本表或其他视图构成的表的定义，称为虚表。(4)(4)一个基本表可以存储在一个或多个存储文件中，一个存储文件也可存放一个或多个基本表。(5)(5)用户可以用SQLSQL语句对基本表和视图进行查询等操作，视图和基本表在用户看来都是关系（即表格）。(6)(6)SQLSQL用户可以是

3、应用程序，也可以是终端用户。SQLSQL语句可嵌入主语言中使用，也可独立使用。33.1 SQL概述2.SQL的组成 主要由四部分组成：数据定义。这部分也称为“SQL DDL”，用于定义SQL模式、基本表、视图和索引。数据操纵。这部分也称为“SQL DML”，分为数据查询和数据更新两类。数据控制。这部分包括对基本表和视图的授权，完整性规则的描述，事务控制等。嵌入式SQL的使用规定。43.1 SQL概述3.SQL的特点 SQL的语言简洁，易学易用 SQL是一种非过程化语言 SQL采用面向集合的操作方式 SQL具有十分灵活和极强的查询功能 SQL以同一种语法结构提供两种使用方法5第三章 关系数据库标

4、准语言SQLSQL3.1 SQLSQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图63.2 数据定义1.定义、删除、修改基本表 SQL数据定义功能包括：定义基本表(创建、删除、修改三个命令)定义视图(创建、删除，不提供修改，因为是个虚表)定义索引(创建、删除，不能修改，因为依附于基本表)定义基本表 CREATE TABLECREATE TABLE(列级完整性约束 ,列级完整性约束 ,）；73.2 数据定义修改基本表 ALTER TABLEALTER TABLE ADDADD 完整性约束 DROPDROP MODIFYMODIFY ；删除基本表 DROP TABLEDROP TA

5、BLE；83.2 数据定义2.建立与删除索引SQL支持用户根据应用环境需要，在基本表上建立一个或多个索引，以提供各种存取路径，加快查找速度。建立索引 CREATE CREATE UNIQUEUNIQUE CLUSTERCLUSTER INDEX INDEX ON ON(，)；删除索引 DROP INDEXDROP INDEX 93.2 数据定义【例1】建立一个学生关系StudentStudent，由学号SnoSno、姓名SnameSname、性别SsexSsex、年龄SageSage、所在系SdeptSdept五个属性组成，其中学号不能为空且值是唯一的。解：CREATE TABLECREATE

6、 TABLE StudentStudent (SnoSno CHAR(5)NOT NULL UNIQUE,CHAR(5)NOT NULL UNIQUE,SnameSname CHAR(20),CHAR(20),SsexSsex CHAR(2),CHAR(2),SageSage INT,INT,SdeptSdept CHAR(15);CHAR(15);103.2 数据定义【例2】向StudentStudent表增加“入学时间”列，其数据类型为日期型。解：ALTER TABLEALTER TABLE StudentStudent ADD DATE;ADD DATE;【例3】删除关于学号必须取唯一值

7、的约束。解：ALTER TABLEALTER TABLE StudentStudent DROP UNIQUE(DROP UNIQUE(SnoSno););【例4】为StudentStudent表按学号升序建立唯一索引。解：CREATE UNIQUE INDEXCREATE UNIQUE INDEX Stusno Stusno ONON Student Student(SnoSno););【例5】删除StudentStudent表的StusnoStusno索引。解：DROP INDEXDROP INDEX Stusno;Stusno;11第三章 关系数据库标准语言SQLSQL3.1 SQLSQ

8、L概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图123.3 查询数据库查询是数据库的核心操作，SQL语句提供了SELECT语句进行数据库的查询。该语句具有灵活的使用方式和丰富的功能，尤其是目标列表达式和条件表达式，可有多种选择形式。学会灵活应用SELECT语句组成不同形式的查询块，是掌握SQL的关键。SELECT语句格式 一个完整的SELECT语句包括五个子句，其中前两个子句是必不可少的，其他子句可以省略。133.3 查询格式如下：SELECT DISTINCT 序列 FROM 序列 WHERE GROUP BY HAVING ORDER BY ASCDESC;143.3 查询S

9、ELECT语句执行过程：根据WHERE子句的行条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中进行聚合操作。若GROUP子句带HAVING短语，则只有满足指定条件的组才给予输出。SELECT DISTINCTSELECT DISTINCT 序列序列FROM FROM 序列序列 WHEREWHERE GROUP BYGROUP BY 1 HAVINGHAVING ORDER BYORDER B

10、Y 2ASCDESC ASCDESC 153.3 查询如果有ORDER子句，则结果表还要按的值的升序或降序排序。SQL的查询语句，很容易看成是关系代数的表达式。SELECT子句指定作投影运算，当FROM子句指出多个关系时，则表示要做笛卡尔积运算，WHERE子句指定做选择运算，当查询要求做关系代数的自然连接时，则不仅要在FROM子句中给出多个关系，还必须在WHERE子句的条件中包含自然连接的条件。163.3 查询下面通过实例来讨论SELECT的使用方法。假设已建立三个表：学生表 S(S#,SN,SE,SA,SD)课程表 C(C#,CN,PC#,CR)选修表 SC(S#,C#,G)1.简单查询 用

11、SELECT语句实现投影运算与选择运算，一般只用到SELECT语句的一个查询块：173.3 查询 SELECT FROM 表名 WHERE 投影运算对应于SELECT子句，其变化方式主要表现在SELECT子句的上。而选择运算对应于WHERE子句，可通过不同的，查询满足要求的元组。大多数情况下，投影与选择运算是混合出现的。183.3 查询【例1】查询全体学生的姓名，学号，所在系。解：SELECT SNSELECT SN，S#S#，SDSD FROM S FROM S；【例2】查询考试成绩不及格的学生的学号。解：SELECT DISTINCT S#SELECT DISTINCT S#FROM SC

12、 FROM SC WHERE G WHERE G6060；【例3】查询计算机系或信息系年龄在20岁以下的学生姓名。解：SELECT SNSELECT SN FROM SFROM S WHERE(SDWHERE(SD=计算机 OR SDOR SD=信息)AND SA)AND SA2020；3.3 查询2.排序查询 通过SELECT语句中的ORDER BY子句，可以控制查询结果的显示顺序。ORDER BY子句对指定的一个或多个属性列，可按升序(ASC)或降序(DESC)重新排列。【例4】查询选修了C810号课程的学生的学号及成绩,查询结果按分数的降序排列。解：SELECT S#SELECT S#，

13、G G FROM SC FROM SC WHERE C#=C810 WHERE C#=C810 ORDER BY G DESC ORDER BY G DESC；203.3 查询3.使用聚合函数 聚合函数用于实现数据统计功能，SQL提供了许多聚合函数，主要包括：COUNT(DISTINCT/ALL*)COUNT(DISTINCT/ALL*)统计元组个数 COUNT(DISTINCT/ALLCOUNT(DISTINCT/ALL)统计一列中值的个数 SUM(DISTINCT/ALLSUM(DISTINCT/ALL)计算一列值的总和 AVG(DISTINCT/ALLAVG(DISTINCT/ALL)计

14、算一列值的平均值 MAX(DISTINCT/ALLMAX(DISTINCT/ALL)求一列值中的最大值 MIN(DISTINCT/ALLMIN(DISTINCT/ALL)求一列值中的最小值213.3 查询【例5】查询选修了课程的学生人数。解：SELECT COUNT(DISTINCT S#)SELECT COUNT(DISTINCT S#)FROM SC FROM SC【例6】统计C810号课程的学生平均成绩。解：SELECT AVGSELECT AVG（G G）FROM SC FROM SC WHERE C#=C810 WHERE C#=C810；223.3 查询4.数据分组查询 使用聚合函

15、数可以实现对关系中的所有查询的元组进行聚合运算，但在实际应用中，经常要将查询的结果分组，然后再对每个分组进行统计。这时就可以利用SELECT语句提供的GROUP BY子句和HAVING短语来实现分组统计。233.3 查询【例7】查询选修了3门以上4学分课程的学生的学号。解：SELECT S#SELECT S#FROM SC FROM SC WHERE CR=4 WHERE CR=4 GROUP BY S#GROUP BY S#HAVING COUNT HAVING COUNT（*）3 3；WHERE子句与HAVING短语的根本区别在于作用的对象不同 WHERE作用于基本表或视图 HAVING作

16、用于组，从中选出满足条件的组243.3 查询5.表的连接查询 数据库的多个表之间一般都存在某种内在的联系，它们共同提供有用的完整的信息。因此在查询中，经常涉及到多个表的数据，这就需要进行连接查询，这是关系数据库中最主要的查询。其格式一般为：SELECT 列名1，列名2，FROM 表1，表2，WHERE 连接条件 253.3 查询【例8】查询每个学生以及选修课程的情况。解：经分析可知，有关学生情况存储在S表中，而选课情况存储在SC表中，所以此查询涉及两个表中的数据。表S与表SC之间的联系是通过两个表都具有的属性S#实现的。因此，这是一个等值连接。SELECT S.SELECT S.，SC.SC.

17、FROM S FROM S，SCSC WHERE S.S#=SC.S#WHERE S.S#=SC.S#；263.3 查询【例9】查询选修了“C902”号课程，且成绩高于学号为“BP00204018”的学生成绩的所有学生情况。解：题目要求查询所有选修了某门课程且成绩高于另一指定的也选修了此门课的学生成绩。要得到这个信息，首先要找到那位指定的学生成绩，然后以此为标准，再查找所有超过标准的学生。SELECT a1.S#SELECT a1.S#，a1.C#a1.C#，a1.Ga1.G FROM SC a1 FROM SC a1，SC a2 SC a2 WHERE a1.C#=C902 AND a1.G

18、 a2.G WHERE a1.C#=C902 AND a1.G a2.G AND a2.S#AND a2.S#=BP00204018=BP00204018 AND a2.C#=C902 AND a2.C#=C902；a1和a2是为SC定义了两个不同的别名,相当于对SC表作自身连接273.3 查询6.嵌套查询 当一个查询是另一个查询的条件时，需要用嵌套查询来实现。嵌套查询可以使用几个简单的查询块构造功能强大的复合命令，且结构清晰。【例10】查询与陈莉同年龄的所有学生。解：完成这个查询可以首先确定陈莉的年龄，然后再查找所有与她同龄的学生。SELETE S#SELETE S#，SNSN，SASA F

19、ROM S FROM S WHERE SA IN WHERE SA IN (SELETE SA (SELETE SA FROM S FROM S WHERE SN WHERE SN=陈莉)；283.3 查询嵌套查询引出子查询的概念，子查询分为三种:(1)简单子查询：子查询只执行一次，其结果用于父查询，子查询的查询条件不依赖于父查询。子查询与父查询之间的连接:若返回单值(不论单表或多表),用比较运算符 若返回多值,用ANY,WHERE C#=C108 AND G (SELETE AVG(G)(SELETE AVG(G)FROM SC FROM SC WHERE C#=C108)WHERE C#=

20、C108)ORDER BY G DESC ORDER BY G DESC；这是简单子查询303.3 查询【例12】查询其成绩比该课程平均成绩高的学生成绩表。解：在SC中被学生选修的课程不止一门，对每门课程都要计算平均分，然后再找出成绩高于平均分的学生成绩表。程序的执行过程如下：主查询：SELETE S#SELETE S#，C#C#，G G FROM SC FROM SC WHERE G WHERE G (待查学生所选课程的平均分)子查询：SELETE AVG(G)SELETE AVG(G)FROM SC FROM SC WHERE C#=WHERE C#=(主查询待选行的课程号C#)扫描SC：

21、Record1 唤醒子查询，把Record1的C#传给子查询,子查询根据主查询的C#,计算C#的平均分并送回主查询。313.3 查询最后得到：SELETE S#SELETE S#，C#C#，G G FROM SC a FROM SC a WHERE G WHERE G (SELETE AVG(G)(SELETE AVG(G)FROM SC b FROM SC b WHERE a.C#=b.C#)WHERE a.C#=b.C#)理解这个相关子查询的关键是别名，出现在主查询和子查询的FROM语句中，这样同一个表相当于两个表。这是相关子查询323.3 查询(3)带EXIST测试的子查询：EXIST代

22、表存在量词，用在WHERE中，后面跟子查询，构成一个条件。当子查询返回值至少有一个时，条件为真(T)；否则条件为假(F)。【例12】查询与刘晨选修了同样课程的学生学号和姓名。分析：1)1)在S中找出刘晨的S#；2)2)在SC中找出刘晨所选的课程；3)3)在SC中找出与刘晨选修了同样课程的学生S#；4)4)在S中找出相对应的SN。刘晨不止选修一门课与刘晨选修同样课程的学生不止一个333.3 查询由第1)、2)步得到：SELETE C#SELETE C#FROM SC FROM SC WHERE EXIST WHERE EXIST (SELETE*(SELETE*FROM S FROM S WHE

23、RE SC.S#=S.S#AND SN=WHERE SC.S#=S.S#AND SN=刘晨)扫描SC：Record1 用S#与S连接,看是否刘晨选修,若是则放入结果表.Record2 用S#与S连接,全部扫描完,找出一组刘晨选修的课.343.3 查询由第3)、4)步得到：SELETE S#,SN SELETE S#,SN FROM S FROM S WHERE EXIST WHERE EXIST (SELETE*(SELETE*FROM SC FROM SC WHERE S.S#=SC.S#AND C#IN WHERE S.S#=SC.S#AND C#IN扫描S：Record1 用S#与SC连

24、接,看这个学生是否选修了课程,且选修的课程与刘晨相同,若是则放入结果表.Record2 用S#与SC连接,353.3 查询解:SELETE S#SELETE S#，SN SN FROM S a1 FROM S a1 WHERE EXIST WHERE EXIST (SELETE*(SELETE*FROM SC b1 FROM SC b1 WHERE a1.S#=b1.S#AND C#IN WHERE a1.S#=b1.S#AND C#IN (SELETE C#(SELETE C#FROM SC b2 FROM SC b2 WHERE EXIST WHERE EXIST (SELETE*(SEL

25、ETE*FROM S a2 FROM S a2 WHERE b2.S#=a2.S#AND a2.SN=WHERE b2.S#=a2.S#AND a2.SN=刘晨)36第三章 关系数据库标准语言SQLSQL3.1 SQLSQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图373.4 数据更新1.插入数据 SQL用INSERT命令向表中输入数据，有两种方式:插入单个元组 INSERT INTO VALUES(元组值)插入子查询结果 INSERT INTO 子查询383.4 数据更新2.删除数据 SQL用DELETE命令删除表中的行，格式为：DELETE FROM WHERE 该命令只

26、删除表的数据，不删除表的定义。而DROP命令则不但把表的内容删除，还把表的结构也一同删除。393.4 数据更新3.修改数据 UPDATE SET=,=WHERE 该命令可修改某一元组值，也可修改多个元组值，也可把子查询嵌入UPDATE语句中，构造修改的条件。403.4 数据更新【例1】将一个记录(BP00204008,陈莉,女,18,信息)插入S中.解：INSERTINSERT INTO S INTO S VALUES(VALUES(BP00204008BP00204008,陈莉,女,1818,信息););【例2】将学生BP00204001的年龄改为21岁。解：UPDATE SUPDATE S

27、 SET SA=21 SET SA=21 WHERE S#=WHERE S#=BP00204001BP00204001;【例3】删除所有的学生选课记录。解：DELETEDELETE FROM SC FROM SC；41第三章 关系数据库标准语言SQLSQL3.1 SQLSQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图423.5 视图视图是一个虚表，就像一个窗口，透过它可以看到DB中自己感兴趣的数据及其变化。视图一经定义就像基本表一样被查询、删除，也可在一个视图上再定义新视图，但视图的更新操作则有一定的限制。433.5 视图1.定义视图 CREATE VIEW(列名表)AS W

28、ITH CHECK OPTION2.删除视图 DROP VIEW 3.更新视图 可以用INSERT，DELETE，UPDATE三类操作。443.5 视图【例1】建立信息系学生的视图，并要求进行修改和插入操作时仍保证该视图只有信息系学生。解：CREATE VIEW CREATE VIEW 信息_S S AS AS SELECT S#SELECT S#，SNSN，SASA FROM S FROM S WHERE SD=WHERE SD=信息 WITH CHECK OPTION WITH CHECK OPTION；由于在定义信息_S S视图时加上了WITH CHECK OPTIONWITH CHEC

29、K OPTION 子句，以后对视图进行插入，修改和删除等操作时，DBMS DBMS会自动加上SD=SD=信息的条件。453.5 视图【例2】建立信息系选择了9801号课程的学生视图。解：CREATE VIEWCREATE VIEW 信息_9801(S#_9801(S#，SNSN，G)G)ASAS SELECT S.S#SELECT S.S#，SNSN，G G FROM S FROM S，SCSC WHERE SD=WHERE SD=信息 AND AND S.S#=SC.S#AND S.S#=SC.S#AND SC.C#=9801 SC.C#=9801；由于视图信息_9801_9801的属性列中

30、包括了S关系与SC关系的同名列S#，所以必须在视图名后面明确说明视图的各个属性列名。463.5 视图视图不仅可以建立在一个或多个基本表上，也可以建立在 一个或多个已定义好的视图上，或同时建立在基本表与视 图上。【例3】建立信息系选修了9801号课程且成绩在90分以上的学生视图。解：CREATE VIEWCREATE VIEW 信息1_98011_9801 ASAS SELECT S#SELECT S#，SNSN，G G FROM FROM 信息_9801_9801 WHERE G=90;WHERE G=90;473.5 视图【例4】将学生学号及平均成绩定义为一个视图。解：CREATE VIEW

31、CREATE VIEW S_GS_G ASAS SELECT S#SELECT S#，AVG(G)AVG(G)FROM SC FROM SC GROUP BY S#;GROUP BY S#;【例5】删除视图信息_9801_9801。解：DROP VIEWDROP VIEW 信息_9801;9801;483.5 视图4.查询视图 视图定义后，用户就可以像对基本表进行查询一样对 视图进行查询了。【例6】在信息系学生视图中找出年龄小于20的学生。解：SELETE S#SELETE S#，SA SA FROM FROM 信息_S S WHERE SA 20 WHERE SA 20 DBMS执行此查询时，将其与信息_S S视图定义中的子查 询结合起来，转换成对基本表S S的查询。493.5 视图修正后的查询语句为：SELETE S#SELETE S#，SA SA FROM S FROM S WHERE SD WHERE SD=信息 AND SA 20;AND SA 20;50 第三章结束 51