1、第三章 关系数据库标准语言 SQL,了解标准SQL语言的基本概念及特点 了解并掌握SQL语言的数据定义、数据查询、数据更新、数据控制功能 了解视图的概念和特点,要求,SQL（Structured Query Language）于1974年提出，并在IBM的数据库管理系统原型System R上实现。 1986年，美国国家标准局（ANSI）批准了SQL作为关系数据库的美国标准，国际化标准组织（ISO）也批准SQL作为关系数据库的国际标准。 SQL语言成为关系数据库的标准语言 各实用数据库管理系统中有一些小的差别 发展过程 SQL-86 SQL-89 SQL-92 SQL3,前言,SQL-86：“数
2、据库语言SQL” SQL-89：“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持。 SQL-92：“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 SQL-3：正在讨论中的新的标准，将增加对面向对象模型的支持。,3.1 SQL概述,SQL作为结构化查询语言，功能不仅仅是查询。 一个综合的、通用的、功能极强的关系数据库语言。,数据查询：data query 数据操纵：data manipulation 数据定义：data definition 数据控制：data control,3.1.1 SQL的特点,介
3、于关系代数 和关系演算之间,一、综合统一,数据库系统的主要功能是通过数据语言来实现的 语言的功能方面 非关系数据模型 数据语言一般分为：模式DDL、外模式DDL、DSDL、DML 需要修改模式时：需要停止数据库的运行，转储数据，修改模式，编译重装数据库。 关系数据模型中SQL语言 包括了：DDL、DML、DCL 完成的任务：数据库生命周期内的全部活动 修改模式时：可以逐步的进行，不影响数据库的运行 数据操作符： 非关系数据模型中：对实体和联系分别有对应的操作符； 关系数据模型中：使用统一的操作符。,二、高度非过程化,非关系数据模型 面向过程的语言； 要完成某项要求：需要指定存取路径。 数据独立
4、性差 关系数据模型：SQL 非过程化的语言 只要提出“做什么”，不需要指明“怎么做”；无需存储路径（存储路径的选择由系统自动完成） 用户负担轻，提高了数据独立性。,三、面向集合的操作方式,非关系数据模型 面向记录的操作方式； 操作的单位是记录 一次一条记录，需要循环； 关系数据模型 集合的操作方式 操作对象和结果：都是集合 查询、插入、删除、更新的对象：都可以是元组的集合,四、同一种语法提供两种使用方式,SQL：自含式语言、嵌入式语言 自含式语言 能够独立用于联机交互的使用； 用户可以直接使用SQL命令对数据库操作； 嵌入式语言 能嵌入到高级语言中使用：C、VB、VC等 程序员在设计程序时可使
5、用 作用：提供了极大的灵活性和方便性,五、语言简捷、易学易用,完成核心功能只需要9个动词 非常接近于英语,SQL语言支持关系数据库的三级模式结构,3.1.2 SQL语言的基本概念,SQL,View1,View2,Basetable1,Basetable2,Basetable3,Basetable4,Storefile1,Storefile1,外模式,模式,内模式,说明：,用户可以用SQL语言对视图和基本表进行查询操作。在用户观点里，视图和基本表都一样是关系。 视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，只存储视图的定义。 在视图上还可以再定义视图。 基本表本身是独立存在的表，
6、SQL中一个关系就对应一个基本表。 一个或多个基本表对应一个存储文件。 一个基本表可以带若干索引。 存储文件和索引构成了关系数据库的内模式。,3.2 数据定义,SQL的数据定义，其功能包括定义表、视图和索引。,关键字,视图和索引 附属于表 一般不提供 修改 先删 再建,定义基本表 一般格式：create table （ 列级完整性约束条件 ， 列级完整性约束条件 ， ）； 举例create table branch (branch-name char(15) NOT NULL UNIQUE, branch-city char(30) UNIQUE assets integer, primary
7、 key (branch-name), check (assets = 0),3.2.1 定义、删除与修改基本表,取值唯一,取值唯一，不允许空,执行之后，建立了一个空表，并将约束条件放入数据字典中。,完整性约束包括：primary key（A）：表示属性列A构成关系的主码。foreign key（A）references r ：表示属性列A构成关系的外码，且被参照关系为r 。check（P）：表示关系中的每个元组必须满足谓词P 。unique （A）：表示每个元组在属性列A上是唯一的。,3.2.1 定义、删除与修改基本表,修改基本表 一般格式：alter table add 列级完整性约束条件
8、 drop modify ；,add命令：在已有的关系表中增加一个新属性 drop命令：删除已有关系表的完整性约束条件 modify命令：对关系表中已有属性的数据类型进行修改,说明：SQL没有提供直接删除列的语句先建立新表复制数据，删除旧表。,修改基本表的例子,ADD ALTER TABLE student ADD Scome DATE； MODIFY ALTER TABLE student MODIFY Sage SMALLINT； DROP ALTER TABLE student DROP UNIQUE（Sname）,删除基本表 一般格式：drop table 说明：基本表删除，与表相关的
9、索引及视图都将一并删除。 例： DROP TABLE STUDENT,索引的作用 加快查询速度，系统自动选择作为存储路径。 建立索引 一般格式：create unique cluster index on (， ，) 说明： 指定索引值排序的次序，可取ASC或DESC，缺省为ASC unique表明此索引的每个索引值只对应唯一的一个记录 cluster为聚簇索引：数据结构中的概念，索引项的顺序和记录次序一致。对应每个基本表最多一个聚簇索引。,3.2.2 建立与删除索引,举例：为学生课程数据库中的Student，Course，SC三个表建立索引create unique index Stusno
10、 on Student(Sno)create unique index Coucno on Course(Cno)create unique index SCno on CS (Sno ASC，Cno DESC) 删除索引一般格式： drop index 索引名例：drop index Stusno,索引的有关说明 可以动态地定义索引，即可以随时建立和删除索引。 不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。 应该在使用频率高的、经常用于连接的列上建索引。 一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。,
11、3.3 查询,查询的简单格式：select A1, A2, ., An from r1, r2, ., rm where P Ai 代表属性 ri 代表关系表 P 代表条件表达式. 说明 该查询等价于以下的关系代数表达式： A1, A2, ., An( P (r1 x r2 x . x rm) 查询的结果仍是一个关系表。,3.3 查询,完整的查询形式 Select alldistinct, From , where group byhaving order byasc|desc; group by：对结构值进行分组，值相同的为一组，一般会再使用集函数。 Having：满足一定条件的组才输出；
12、order by：进行排序 All：保留结果中重复的元组 Distinct：消除结果中重复的元组,学生-课程数据库中的表,学生表 Student (sno,sname,ssex,sage,sdept) 学号、姓名、性别、年龄、所再系；sno为主码。 课程表 Course(cno,cname,cpno,ccredit) 课程号、课程名、选修课号、学分；cno为主码。 学生选课表 Sc(sno,cno,grade) 学号、课程号、成绩； (sno,cno)为主码。,3.3.1 单表查询,一、选择表中的若干列 查询指定列 查询全体学生的学号和姓名select sno，sname from stude
13、nt 查询全部列 查询全体学生的全部信息select * sno,sname,ssex,sage,sdeptfrom student 查询经过计算的值 目标列是算术表达式：查询学生的姓名和出生年份select sname，2004-sage from student,一、选择表中的若干列,查询经过计算的值 目标列是字符串常量、函数： 查询全体学生的姓名、出生年份和所在系。 select sname,yearof birth： ，2004-sage，ISLOWER(sdept) from student,查询经过计算的值 为查询结果的列名指定别名 select sname NAME, yearo
14、f birth： BIRTH，2004-sage BIRTHDAY，ISLOWER(sdept) DEPARTMENT from student,一、选择表中的若干列,二、选择表中的若干元组 消除取值重复的行： 查询选修了课程的学生学号select distinct sno from sc若没有distinct默认值为all，表示不去除重复行 查询满足条件的元组通过设定where子句来实现。常用的查询条件：,二、选择表中的若干元组,比较大小 查询有考试成绩不合格的学生的学号select DISTINCT sno from sc where grade 60 确定范围 查询年龄不在2023岁的学
15、生姓名、系别和年龄。select sname，sdept，sage from student where sage NOT between 20 and 23 确定集合：查询不在计算机系和数学系的学生姓名select sname from student where sdept NOT in (CS,MA),二、选择表中的若干元组,字符匹配： “%”：代表任意长度的字符串 “_”：代表任意单个字符 查询所有姓刘的学生的姓名、学号和性别select sname，sno，ssex from student where sname like 刘% 查询所有姓“欧阳”且全名为三个汉字的的学生的姓名se
16、lect sname from student where sname like 欧阳_ _ ESCAPE ：换码字符，用来对_和%进行转码 查询以“DB_”开头，且倒数第3个字符为I的课程select * from course where cname like DB _ %i _ _ ESCAPE,二、选择表中的若干元组,涉及空值的查询：IS NULL, IS NOT NULL 查询所有成绩的学生学号和课程号select sno，cno from sc where grade IS not null 多重条件查询： AND，OR 查询信息系和计算机科学系的学生的姓名和性别select sn
17、ame，ssex from student where sdept = IS OR sdept = CS 三、对查询结果排序：（缺省情况为ASC） 查询全体学生，并按照结果所在系的系号升序排列，同一系按年龄降序排列。select * from student order by sdept，sage desc,四、使用集函数 SQL提供的集函数： Count(distinct|all *):统计元组个数。 Count(distinct|all ):统计一列中值的个数。 Sum(distinct|all ):计算数值的总和。 Avg (distinct|all ):计算数值的平均值。 Max(di
18、stinct|all ):求一列值中的最大值。 Min(distinct|all ):求一列值中的最小值。 使用distinct：计算时取消指定列中的重复值 不使用distinct或使用all：不取消重复值 All为缺省值,四、使用集函数,计算1号课程的学生平均成绩select avg (grade) from SC where cno = 1 查询学生总人数select count (*) from student 查询选修了课程的学生人数select count (distinct sno) from SC,五、对查询结果分组,作用： 将查询结果表按某一列或多列值分组，相同的为一组。 可以
19、细化集函数的作用对象为每组内的对象。 例：求各课程号及相应的选课人数select cno, count (sno) from sc group by cno 带条件的分组查询：having语句 查询选修了3门以上课程的学生学号select sno from SC group by sno having COUNT (*) 3,WHERE：作用于基本表和视图 HAVING：作用于组,3.3.2 连接查询,定义：涉及多个表之间的查询。 类型： 等值连接 自然连接 非等值连接 自身连接 外连接 复合条件连接,连接条件（连接谓词） . . 比较操作符：=、=、. BETWEEN . AND . 等值连
20、接：比较操作符为“=” 非等值连接：比较操作符为除“=”之外的 连接字段：连接谓词中的列名，它们的类型必须相同。 连接的过程 表1中找到第一个元组 扫描表2，查找满足连接条件的元组； 和表1的第一个元组拼接结果表中的一个元组。 继续查找表2； 重复上述步骤，直到处理完成表1中全部元组。,一、 等值、非等值连接查询,连接条件,例：查询每个学生及其选修课程的情况。select student.*, SC.* from student, SC where student.sno = SC.sno,一、 等值、非等值连接查询,SELECT和WHERE子句中的属性名都要加前缀 属性是唯一的：可省略。 连
21、接运算的两种特殊情况： 自然连接：等值连接中去掉重复的属性列。 广义笛卡尔积： 例：select student.sno, sname, ssex, sage, sdept, cno, grade from student, SC where student.sno = SC.sno,一、 等值、非等值连接查询,二、自身连接 连接操作是在表和自己进行。（可对表起别名） 例：查询每门课的先修课程select FIRST.cno， SECOND.cpno from course FIRST, course SECOND where FIRST.cno = SECOND.cno,一般的连接操作： 有
22、满足连接条件的元组才能作为结果输出。 外连接：*（左外连接、右外连接） 对*所在的表要求输出全部元组，没有对应值则为NULL。 例：查询每个学生及其选修课程的情况，并且要求输出全部学生。select student.sno, sname, ssex, sage, sdept, cno, grade from student, SC where student.sno = SC.sno（*）,三、 外连接,定义：WHERE子句有多个连接条件 查询选修2号课程，并且成绩在90份以上的学生select student.sno, sname from student, SC where student
23、.sno=SC.sno AND SCCNO=2 AND SC.Grade90 多表连接：两个以上的表进行连接 查询每个学生的学号、姓名、选课的课程名及成绩select student.sno, sname，cname，grade from student, SC，course where student.sno=SC.sno AND o=SC.cno,四、复合条件连接,3.3.3 嵌套查询,查询块：SQL中的一个select-from-where。 嵌套查询 将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询。 外层查询（父查询）：上层的查询块 内层查询（子查询）：
24、下层的查询块 SQL还允许多层嵌套查询：子查询还可以嵌套其他子查询。 注意：子查询中不能使用order by排序， order by只能用于对最终查询结果排序。 嵌套查询的执行次序：由内向外,3.3.3 嵌套查询,嵌套查询的例子select sname from student where sno IN(select sno from SC where cno=2) 查询次序 对SC表查找所有选修了CNO为2的学生号学生号的集合 从student表中找出对应的学生的姓名,外层查询,内层查询,3.3.3 嵌套查询,带in谓词的子查询：子查询一般为一个集合 查询与“刘晨”在同一个系学习的学生sel
25、ect sno,sname,sdept from student where sdept IN (select sdept from studentWHERE sname=刘晨) 等价于select S1.sno, S1.sname,S1.sdept from student S1，student S2 where S1.sdept= S2.sdept AND S2.name=刘晨,NOT IN,3.3.3 嵌套查询,查询选修了课程名为“信息系统”的学生学号和姓名select sno,sname from student where sno IN (select sno from SC whe
26、re cno IN (select cno from course where cname=信息系统) 等价于select sno,sname from student，SC，course where student.sno= SC.sno AND SC.cno=o AND ame=信息系统,定义：父查询和子查询之间用比较运算符进行连接 条件：子查询必须返回的是单值 子查询返回的是单值时：、=、 例：查询与“刘晨”在同一个系学习的学生select sno,sname,sdept from student where sdept = (select sdept from studentWHERE
27、 sname=刘晨),注意：子查询必须跟在比较符之后,二、 带有比较运算符的子查询,三、带有any或all谓词的子查询,说明：在使用的同时必须使用比较运算符 ANY：大于子查询结果中的某个值； ALL：大于子查询结果中的所有值 =ANY：大于等于子查询结果中的某个值 =ALL：大于等于子查询结果中的所有值 )ANY：不等于子查询结果中的某个值 !=()ALL：不等于子查询结果中的任何一个值,三、带有any或all谓词的子查询,查询其他系中比信息系某一学生年龄小的学生姓名和年龄select sname，sage from student where sage ANY (select sage f
28、rom student where sdept = IS) 说明： 部分any、all谓词可与集函数及in谓词进行等价代换； 用集函数实现子查询比直接用any或all查询效率要高。,select sname，sage from student where sage IS,四、带有exists谓词的子查询,例：查询所有选修了1号课程的学生姓名select sname from student where exists ( select * from SC where sno=student.sno AND cno=1) 说明： 代表存在量词，不返回数据，只产生逻辑真值“true”或逻辑假值“fa
29、lse” exists引出的子查询属相关子查询。 相关子查询 子查询的查询条件依赖于外层父查询的某各属性值。,一般都是*，只需要返回真、假。,四、带有exists谓词的子查询,相关子查询的处理过程 取外层查询的第一个元组，用它的属性值处理内层查询； 结果为T，则取出此外层查询的元组结果集； 再取外层查询的下一个元组，并重复上述过程。 等价性问题： 所有带IN、比较运算符、ANY和ALL的子查询EXISTS的子查询 部分EXISTS、NOT EXISTS的子查询不能被替代。select sname from student where sno IN ( select sno from SC wh
30、ere cno=1),四、带有exists谓词的子查询,全称量词的转化： 例：查询选修了全部课程的学生姓名select sname from student where not exists ( select * from course where not exists ( select * from SC where sno=student.sno AND cno=o) 蕴涵运算的转化： 例：查询至少选修了学生95002选修的全部课程的学生号码select distinct sno from SC SCX where not exists ( select * from SC SCY whe
31、re o=95002 AND not exists ( select * from SC SCZ where scz.sno=scx.sno AND o=o),没有一门课是他没有选的,不存在这样的课 95002选了 但他没选,例：查询计算机科学系的学生及年龄不大于19岁的学生select * from student where sdept = CS unionselect * from student where sage = 19,3.3.4 集合查询,对多个select语句的查询结果进行集合操作。集合 操作包括： 并union：有实现 交intersect：无实现 差minus：无实现,
32、Schema Used in Examples,3.3.1 单表查询,选择表中的若干列 查询指定列e.g.1:查询给出贷款的银行名称select branch-name from loan 查询全部列e.g.2: 查询贷款的所有信息select * from loan 查询经过计算的值 目标列是算术表达式e.g.3: select loan-number, branch-name, amount 100 from loan,目标列是字符串常量、函数 e.g.4: select loan-number, ISLOWER(branch-name),centuplicate-amount, amou
33、nt 100 from loan 为查询结果的列名指定别名 e.g.5: select loan-number, ISLOWER(branch-name) branchn, centuplicate-amount cent-am, amount 100 amountc from loan,amount*100,3000 4000 1700,islower (branch-name),Downtown Redwood Perryridge,loan-number,L-170 L-230 L-260,e.g.4,e.g.5,Centuplicate -amount,Centuplicate-amo
34、unt Centuplicate-amount Centuplicate-amount,amountc,3000 4000 1700,branchn,Downtown Redwood Perryridge,loan-number,L-170 L-230 L-260,cent-am,Centuplicate-amount Centuplicate-amount Centuplicate-amount,选择表中的若干元组 消除取值重复的行e.g.6：select distinct branch-name from loan若没有distinct默认值为all，表示不去除重复行 查询满足条件的元组通
35、过设定where子句来实现。常用的查询条件：,比较大小 e.g.7: select loan-number from loan where branch-name = Perryridge 确定范围 e.g.8: select loan-number from loan where amount between 90000 and 100000 确定集合 e.g.9：select loan-number from loan where branch-name in (Downtown,Redwood,Perryridge),字符匹配 e.g.10:select customer-name fr
36、om customer where customer-street like %Main% “%”:代表任意长度的字符串 “_”：代表任意单个字符串 涉及空值的查询 e.g.11: select customer-name from depositor where account-number not null 多重条件查询 e.g.12: select loan-number from loan where branch-name = Perryridge and amount 1200,对查询结果排序 e.g.13: select distinct customer-name from b
37、orrower order by customer-name desc缺省情况为ASC 使用集函数 SQL提供的集函数： Count(distinct|all *):统计元组个数。 Count(distinct|all ):统计一列中值的个数。 Sum(distinct|all ):计算数值型列的总和。 Avg (distinct|all ):计算数值型列的平均值。 Max(distinct|all ):求一列值中的最大值。 Min(distinct|all ):求一列值中的最小值。,e.g.14: select avg (balance) from account where branch-
38、name = Perryridge select count (*) from customerselect count (distinct customer-name) from depositor,对查询结果分组 e.g.15: select branch-name, count (distinct customer-name) from depositor, account where depositor.account-number = account.account-number group by branch-name 带条件的分组查询 e.g.16: select branch-
39、name, avg (balance) from account group by branch-name having avg (balance) 1200,3.3.2 连接查询,涉及多个表之间的查询。,等值、非等值连接查询 e.g.17: select loan.*, borrower.* from borrower, loan where borrower.loan-number = loan.loan-number e.g.18: select customer-name, borrower.loan-number, amount from borrower, loan where b
40、orrower.loan-number = loan.loan-number,连接条件,e.g.17 result:,e.g.18 result:,自身连接e.g.19: select distinct T.branch-name from branch as T, branch as S where T.assets S.assets and S.branch-city = Brooklyn 外连接e.g.20:select loan.*, borrower.* from borrower, loan where borrower.loan-number （*） = loan.loan-nu
41、mber,复合条件连接 e.g.21: select customer-name, borrower.loan-number, amount from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = Perryridge,3.3.3 嵌套查询,SQL中一个select-from-where为一个查询块，将一个 查询块嵌套在另一个查询块的where子句或having短 语的条件中的查询称为嵌套查询。,注意：子查询中不能使用order by排序,带in谓词的子查询 e.g.22: sele
42、ct distinct customer-name from borrower where customer-name in (select customer-name from depositor)e.g.23: select distinct customer-name from borrower where customer-name not in (select customer-name from depositor),带有比较运算符的子查询e.g.24: select branch-name from branch where assets (select assets from
43、branch where branch-city = Brooklyn),注意：子查询必须跟在比较符之后,带有any或all谓词的子查询e.g.25: select branch-name from branch where assets all (select assets from branch where branch-city = Brooklyn) 说明：部分any、all谓词可与集函数及in谓词进行等价代换，但用集函数实现子查询比直接用any或all查询效率要高。,带有exists谓词的子查询 e.g.26: select distinct S.customer-name from
44、 depositor as S where not exists ( (select branch-name from branch where branch-city = Brooklyn) except (select R.branch-name from depositor as T, account as R where T.account-number = R.account-number and S.customer-name = T.customer-name) 说明： exists引出的子查询只返回真值或假值。 exists引出的子查询属相关子查询。,e.g.27: selec
45、t * from loan where branch-name = Perryridge unionselect * from loan where amount 3000,3.3.4 集合查询,对多个select语句的查询结果进行集合操作。集合 操作包括：并union，交intersect，减minus,3.4 数据更新,两种格式 插入单个元组 Insert Into ( ， ) Values( ， ) 插入子查询 Insert Into (属性列1，属性列2) 子查询,数据更新包括插入、修改、删除三种,3.4.1 插入数据,SQL中三条语句,没出现的属性列：取NULL 没出现任何列：每个列
46、上都有值,3.4.1 插入数据,插入单个元组 插入一条选课记录insert into sc（sno,cno） values (95020, 1) 这时在grade列上取空值。 插入子查询结果 对student表按系分组求平均年龄插入新的表。insert into deptage（sdept,avgage） select sdept,avg（sage） from student group by sdept,3.4.2 修改数据,格式 Update Set ， Where ,无，则表示修改所有元组,3.4.2 修改数据,修改某一个元组：将学生95001的年龄改为22岁update student
47、 set sage = 22 where sno =95001 修改多个元组：将所有学生的年龄增加1岁update student set sage = sage+1 带子查询的修改语句update SC set grade = 0 where CS=（select sdeptfrom studentwhere student.sno=sc.sno）,格式DeleteFrom where 删除一个元组的值 删除学号为95019的学生记录delete from student where sno = 95019 删除多个元组 删除所有的学生选课记录delete from sc,3.4.3 删除数
48、据,无，则表示删除所有元组,带子查询的删除语句 删除计算机科学系所有学生的选课记录delete from sc where CS = (select sdept from studentwhere student.sno=sc.sno) 共同的特性 对单元组操作 对多个元组操作 带子查询的操作,3.4.3 删除数据,更新中的数据库的一致性问题,更新操作中的问题： 对有关联的表的删除应该考虑实体完整性和参照完整性的问题。（多条语句的执行由事务机制来保证） 例：对student和SC进行删除 插入元组的策略：检查参照表。 有成功；无失败。 删除元组的策略： 自动删除参照表中的元组； 检查参照表，有相应的元组，则操作失败。,
