1 / 52

第三章 关系数据库 标准语言－ SQL

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

audra-gonzales
Download Presentation

第三章 关系数据库 标准语言－ SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第三章 关系数据库标准语言－SQL • 3.1 SQL概述 • 3.2 数据定义 • 3.3 查询 • 3.4 数据更新 • 3.5 视图

  2. 3.1 SQL概述 • 关系数据库的标准语言—结构化查询语言(Structured Query Language)，简称SQL。它是一种非过程化的、功能极强的、通用的数据库语言，几乎所有的关系数据库管理系统都支持SQL。 1.SQL数据库的体系结构要点 SQL语言支持关系数据库三级模式结构:

  3. 用户 外模式 SQL 视图2 视图1 基本表1 基本表2 基本表3 基本表4 模式 存储模式 存储文件1 存储文件2 3.1 SQL概述

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

  5. 3.1 SQL概述 2.SQL的组成 主要由四部分组成： 数据定义。这部分也称为“SQL DDL”，用于定义SQL模式、基本表、视图和索引。 数据操纵。这部分也称为“SQL DML”，分为数据查询和数据更新两类。 数据控制。这部分包括对基本表和视图的授权，完整性规则的描述，事务控制等。 嵌入式SQL的使用规定。

  6. 3.1 SQL概述 3.SQL的特点 SQL的语言简洁，易学易用 SQL是一种非过程化语言 SQL采用面向集合的操作方式 SQL具有十分灵活和极强的查询功能 SQL以同一种语法结构提供两种使用方法

  7. 第三章 关系数据库标准语言－SQL • 3.1 SQL概述 • 3.2 数据定义 • 3.3 查询 • 3.4 数据更新 • 3.5 视图

  8. 3.2 数据定义 1.定义、删除、修改基本表 SQL数据定义功能包括： 定义基本表(创建、删除、修改三个命令) 定义视图(创建、删除，不提供修改，因为是个虚表) 定义索引(创建、删除，不能修改，因为依附于基本表) ⑴定义基本表 CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束] [,<列名> <数据类型>[列级完整性约束]…] [,<表级完整性约束>]）；

  9. 3.2 数据定义 ⑵修改基本表 ALTER TABLE <表名> [ADD <新列名> <数据类型>[完整性约束]] [DROP <完整性约束名>] [MODIFY <列名> <数据类型>]； ⑶删除基本表 DROP TABLE <表名>；

  10. 3.2 数据定义 2.建立与删除索引 • SQL支持用户根据应用环境需要，在基本表上建立一个或多个索引，以提供各种存取路径，加快查找速度。 ⑴建立索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][，<列名>[<次序>]]…)； ⑵删除索引 DROP INDEX <索引名>

  11. 3.2 数据定义 【例1】建立一个学生关系Student，由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号不能为空且值是唯一的。 解：CREATE TABLEStudent (SnoCHAR(5) NOT NULL UNIQUE, SnameCHAR(20), SsexCHAR(2), SageINT, SdeptCHAR(15));

  12. 3.2 数据定义 【例2】向Student表增加“入学时间”列，其数据类型为日期型。 解： ALTER TABLEStudentADD DATE; 【例3】删除关于学号必须取唯一值的约束。 解： ALTER TABLEStudentDROP UNIQUE(Sno); 【例4】为Student表按学号升序建立唯一索引。 解： CREATE UNIQUE INDEXStusno ON Student(Sno); 【例5】删除Student表的Stusno索引。 解： DROP INDEXStusno;

  13. 第三章 关系数据库标准语言－SQL • 3.1 SQL概述 • 3.2 数据定义 • 3.3 查询 • 3.4 数据更新 • 3.5 视图

  14. 3.3 查询 • 数据库查询是数据库的核心操作，SQL语句提供了SELECT语句进行数据库的查询。该语句具有灵活的使用方式和丰富的功能，尤其是目标列表达式和条件表达式，可有多种选择形式。学会灵活应用SELECT语句组成不同形式的查询块，是掌握SQL的关键。 • SELECT语句格式 一个完整的SELECT语句包括五个子句，其中前两个子句是必不可少的，其他子句可以省略。

  15. 3.3 查询 格式如下： SELECT [DISTINCT] <目标列表达式> 序列 FROM <基本表名或视图名> 序列 [WHERE <行条件表达式>] [GROUP BY <列名1> [HAVING <组条件表达式>]] [ORDER BY <列名2> [ASC∕DESC]];

  16. SELECT [DISTINCT] <目标列表达式> 序列 FROM <基本表名或视图名> 序列 [WHERE <行条件表达式>] [GROUP BY <列名1> [HAVING <组条件表达式>]] [ORDER BY<列名2>[ASC∕DESC]] 3.3 查询 • SELECT语句执行过程： 根据WHERE子句的行条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。 如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中进行聚合操作。若GROUP子句带HAVING短语，则只有满足指定条件的组才给予输出。

  17. 3.3 查询 如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。 • SQL的查询语句，很容易看成是关系代数的表达式。SELECT子句指定作投影运算，当FROM子句指出多个关系时，则表示要做笛卡尔积运算，WHERE子句指定做选择运算，当查询要求做关系代数的自然连接时，则不仅要在FROM子句中给出多个关系，还必须在WHERE子句的条件中包含自然连接的条件。

  18. 3.3 查询 • 下面通过实例来讨论SELECT的使用方法。假设已建立三个表： 学生表S(S#,SN,SE,SA,SD) 课程表 C(C#,CN,PC#,CR) 选修表SC(S#,C#,G) 1.简单查询 用SELECT语句实现投影运算与选择运算，一般只用到SELECT语句的一个查询块：

  19. 3.3 查询 SELECT <目标列表达式> FROM 表名 WHERE <行条件表达式> • 投影运算对应于SELECT子句，其变化方式主要表现在SELECT子句的<目标列表达式>上。 • 而选择运算对应于WHERE子句，可通过不同的 <行条件表达式>，查询满足要求的元组。 • 大多数情况下，投影与选择运算是混合出现的。

  20. 3.3 查询 【例1】查询全体学生的姓名，学号，所在系。 解： SELECT SN，S#，SD FROM S； 【例2】查询考试成绩不及格的学生的学号。 解： SELECT DISTINCT S# FROM SC WHERE G＜60； 【例3】查询计算机系或信息系年龄在20岁以下的学生姓名。 解： SELECT SN FROM S WHERE (SD=‘计算机’OR SD=‘信息’) AND SA＜20；

  21. 3.3 查询 2.排序查询 通过SELECT语句中的ORDER BY子句，可以控制查询结果的显示顺序。ORDER BY子句对指定的一个或多个属性列，可按升序(ASC)或降序(DESC)重新排列。 【例4】查询选修了C810号课程的学生的学号及成绩,查询结果按分数的降序排列。 解： SELECT S# ，G FROM SC WHERE C# =‘C810’ ORDER BY G DESC；

  22. 3.3 查询 3.使用聚合函数 聚合函数用于实现数据统计功能，SQL提供了许多聚合函数，主要包括：  COUNT([DISTINCT/ALL]*) 统计元组个数  COUNT([DISTINCT/ALL]<列名>) 统计一列中值的个数  SUM([DISTINCT/ALL]<列名>) 计算一列值的总和  AVG([DISTINCT/ALL]<列名>) 计算一列值的平均值  MAX([DISTINCT/ALL]<列名>) 求一列值中的最大值  MIN([DISTINCT/ALL]<列名>) 求一列值中的最小值

  23. 3.3 查询 【例5】查询选修了课程的学生人数。 解：SELECT COUNT (DISTINCT S#) FROM SC 【例6】统计C810号课程的学生平均成绩。 解：SELECT AVG（G） FROM SC WHERE C# =‘C810’；

  24. 3.3 查询 4.数据分组查询 使用聚合函数可以实现对关系中的所有查询的元组进行聚合运算，但在实际应用中，经常要将查询的结果分组，然后再对每个分组进行统计。这时就可以利用SELECT语句提供的GROUP BY子句和HAVING短语来实现分组统计。

  25. 3.3 查询 【例7】查询选修了3门以上4学分课程的学生的学号。 解： SELECT S# FROM SC WHERE CR =‘4’ GROUP BY S# HAVING COUNT（*）＞3； • WHERE子句与HAVING短语的根本区别在于作用的对象不同 WHERE作用于基本表或视图 HAVING作用于组，从中选出满足条件的组

  26. 3.3 查询 5.表的连接查询 数据库的多个表之间一般都存在某种内在的联系，它们共同提供有用的完整的信息。因此在查询中，经常涉及到多个表的数据，这就需要进行连接查询，这是关系数据库中最主要的查询。其格式一般为： SELECT列名1，列名2，… FROM 表1，表2，… WHERE 连接条件

  27. 3.3 查询 【例8】查询每个学生以及选修课程的情况。 解：经分析可知，有关学生情况存储在S表中，而选课情况存储在SC表中，所以此查询涉及两个表中的数据。表S与表SC之间的联系是通过两个表都具有的属性S#实现的。因此，这是一个等值连接。 SELECT S.﹡，SC.﹡ FROM S，SC WHERE S.S# = SC.S# ；

  28. 3.3 查询 【例9】查询选修了“C902”号课程，且成绩高于学号为“BP00204018”的学生成绩的所有学生情况。 解：题目要求查询所有选修了某门课程且成绩高于另一指定的也选修了此门课的学生成绩。要得到这个信息，首先要找到那位指定的学生成绩，然后以此为标准，再查找所有超过标准的学生。 SELECT a1.S#，a1.C#，a1.G FROM SC a1， SC a2 WHERE a1.C# =‘C902’ AND a1.G > a2.G AND a2.S# =‘BP00204018’ AND a2.C# =‘C902’； a1和a2是为SC定义了两个不同的别名,相当于对SC表作自身连接

  29. 3.3 查询 6.嵌套查询 当一个查询是另一个查询的条件时，需要用嵌套查询来实现。嵌套查询可以使用几个简单的查询块构造功能强大的复合命令，且结构清晰。 【例10】查询与陈莉同年龄的所有学生。 解：完成这个查询可以首先确定陈莉的年龄，然后再查找所有与她同龄的学生。 SELETE S#，SN，SA FROM S WHERE SA IN (SELETE SA FROM S WHERE SN=‘陈莉’)；

  30. 3.3 查询 • 嵌套查询引出子查询的概念，子查询分为三种: (1)简单子查询：子查询只执行一次，其结果用于父查询，子查询的查询条件不依赖于父查询。子查询与父查询之间的连接: 若返回单值(不论单表或多表),用比较运算符 若返回多值,用>ANY,<ALL,IN和NOT IN等与查询条件一起构造返回一组值的子查询。 (2)相关子查询：子查询的查询条件依赖于外层父查询的某个属性值，子查询反复执行。

  31. 3.3 查询 【例11】查询选修了课号为“C108”且成绩高于该课程平均分的学生成绩表，并由高到低排序。 解：1)在SC中求C108的平均分(这是子查询返回单值,且只执行一次) 2)在SC中求高于平均分的成绩表(这是主查询) SELETE S#，C#，G FROM SC WHERE C# =‘C108’ AND G > (SELETE AVG(G) FROM SC WHERE C# =‘C108’) ORDER BY G DESC； 这是简单子查询

  32. 扫描SC：→Record1 …… 唤醒子查询，把Record1的C#传给子查询,子查询根据主查询的C#,计算C#的平均分并送回主查询。 3.3 查询 【例12】查询其成绩比该课程平均成绩高的学生成绩表。 解：在SC中被学生选修的课程不止一门，对每门课程都要计算平均分，然后再找出成绩高于平均分的学生成绩表。程序的执行过程如下： 主查询：SELETE S#，C#，G FROM SC WHERE G > (待查学生所选课程的平均分) 子查询：SELETE AVG(G) FROM SC WHERE C# = (主查询待选行的课程号C#)

  33. 3.3 查询 • 最后得到： SELETE S#，C#，G FROM SC a WHERE G > (SELETE AVG(G) FROM SC b WHERE a.C# = b.C#) • 理解这个相关子查询的关键是别名，出现在主查询和子查询的FROM语句中，这样同一个表相当于两个表。 这是相关子查询

  34. 3.3 查询 (3)带EXIST测试的子查询：EXIST代表存在量词Ǝ ，用在WHERE中，后面跟子查询，构成一个条件。当子查询返回值至少有一个时，条件为真(‘T’)；否则条件为假(‘F’)。 【例12】查询与‘刘晨’选修了同样课程的学生学号和姓名。 分析：1)在S中找出刘晨的S#； 2)在SC中找出刘晨所选的课程； 3)在SC中找出与刘晨选修了同样课程的学生S#； 4)在S中找出相对应的SN。 与刘晨选修同样课程的学生不止一个 刘晨不止选修一门课

  35. 扫描SC：→Record1用S#与S连接,看是否刘晨选修,若是则放入结果表.扫描SC：→Record1用S#与S连接,看是否刘晨选修,若是则放入结果表. →Record2用S#与S连接, … …全部扫描完,找出一组刘晨选修的课. 3.3 查询 • 由第1)、2)步得到： SELETE C# FROM SC WHERE EXIST (SELETE * FROM S WHERE SC.S# = S.S# AND SN =‘刘晨’)

  36. 扫描S：→Record1用S#与SC连接,看这个学生是否选修了课程,且选修的课程与刘晨相同,若是则放入结果表.扫描S：→Record1用S#与SC连接,看这个学生是否选修了课程,且选修的课程与刘晨相同,若是则放入结果表. →Record2用S#与SC连接, … … 3.3 查询 • 由第3)、4)步得到： SELETE S#, SN FROM S WHERE EXIST (SELETE * FROM SC WHERE S.S# = SC.S# AND C# IN

  37. 3.3 查询 解: SELETE S#，SN FROM S a1 WHERE EXIST (SELETE * FROM SC b1 WHERE a1.S# = b1.S# AND C# IN (SELETE C# FROM SC b2 WHERE EXIST (SELETE * FROM S a2 WHERE b2.S#=a2.S# AND a2.SN=‘刘晨’)))

  38. 第三章 关系数据库标准语言－SQL • 3.1 SQL概述 • 3.2 数据定义 • 3.3 查询 • 3.4 数据更新 • 3.5 视图

  39. 3.4 数据更新 1.插入数据 SQL用INSERT命令向表中输入数据，有两种方式: 插入单个元组 INSERT INTO <表名> [<列名表>] VALUES (元组值) 插入子查询结果 INSERT INTO <表名> [<列名表>] 子查询

  40. 3.4 数据更新 2.删除数据 SQL用DELETE命令删除表中的行，格式为： DELETE FROM <表名> [WHERE <条件表达式>] • 该命令只删除表的数据，不删除表的定义。而DROP命令则不但把表的内容删除，还把表的结构也一同删除。

  41. 3.4 数据更新 3.修改数据 UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件表达式>] • 该命令可修改某一元组值，也可修改多个元组值，也可把子查询嵌入UPDATE语句中，构造修改的条件。

  42. 3.4 数据更新 【例1】将一个记录(BP00204008,陈莉,女,18,信息)插入S中. 解： INSERT INTO S VALUES (‘BP00204008’,‘陈莉’,‘女’,18,‘信息’); 【例2】将学生BP00204001的年龄改为21岁。 解： UPDATE S SET SA = 21 WHERE S# =‘BP00204001’; 【例3】删除所有的学生选课记录。 解： DELETE FROM SC；

  43. 第三章 关系数据库标准语言－SQL • 3.1 SQL概述 • 3.2 数据定义 • 3.3 查询 • 3.4 数据更新 • 3.5 视图

  44. 3.5 视图 • 视图是一个虚表，就像一个窗口，透过它可以看到DB中自己感兴趣的数据及其变化。 • 视图一经定义就像基本表一样被查询、删除，也可在一个视图上再定义新视图，但视图的更新操作则有一定的限制。

  45. 3.5 视图 1.定义视图 CREATE VIEW <视图名>[(列名表)] AS <子查询语句> [WITH CHECK OPTION] 2.删除视图 DROP VIEW <视图名> 3.更新视图 可以用INSERT，DELETE，UPDATE三类操作。

  46. 3.5 视图 【例1】建立信息系学生的视图，并要求进行修改和插入操作时仍保证该视图只有信息系学生。 解： CREATE VIEW 信息_S AS SELECT S#，SN，SA FROM S WHERE SD =‘信息’ WITH CHECK OPTION； 由于在定义信息_S视图时加上了WITH CHECK OPTION 子句，以后对视图进行插入，修改和删除等操作时， DBMS会自动加上SD =‘信息’的条件。

  47. 3.5 视图 【例2】建立信息系选择了9801号课程的学生视图。 解：CREATE VIEW信息_9801(S#，SN，G) AS SELECT S.S#，SN，G FROM S，SC WHERE SD =‘信息’AND S.S# = SC.S# AND SC.C# =‘9801’； • 由于视图信息_9801的属性列中包括了S关系与SC关系的同名列S#，所以必须在视图名后面明确说明视图的各个属性列名。

  48. 3.5 视图 视图不仅可以建立在一个或多个基本表上，也可以建立在 一个或多个已定义好的视图上，或同时建立在基本表与视 图上。 【例3】建立信息系选修了9801号课程且成绩在90分以上的学生视图。 解： CREATE VIEW信息1_9801 AS SELECT S#，SN，G FROM 信息_9801 WHERE G >= 90;

  49. 3.5 视图 【例4】将学生学号及平均成绩定义为一个视图。 解： CREATE VIEWS_G AS SELECT S#，AVG(G) FROM SC GROUP BY S#; 【例5】删除视图‘信息_9801’。 解： DROP VIEW信息_9801;

  50. 3.5 视图 4.查询视图 视图定义后，用户就可以像对基本表进行查询一样对 视图进行查询了。 【例6】在信息系学生视图中找出年龄小于20的学生。 解： SELETE S#，SA FROM 信息_S WHERE SA < 20 DBMS执行此查询时，将其与信息_S视图定义中的子查 询结合起来，转换成对基本表S的查询。

More Related