《数据库语言 》PPT课件

《《数据库语言 》PPT课件》由会员分享，可在线阅读，更多相关《《数据库语言 》PPT课件（97页珍藏版）》请在装配图网上搜索。

1、第五章 数据库语言SQL,2,概述,SQL的发展 1974年，由Boyce和Chamberlin提出 19751979，IBM San Jose Research Lab的关系数据库管理系统原型System R实施了这种语言 SQL-86是第一个SQL标准 SQL-89、SQL-92(SQL2)、SQL-99(SQL3),3,概述,现状 大部分DBMS产品都支持SQL，成为操作数据库的标准语言 有方言，支持程度不同,4,数据定义（DDL） 定义、删除、修改关系模式（基本表） 定义、删除视图（View） 定义、删除索引（Index） 数据操纵（DML） 数据查询 数据增、删、改 数据控制（DCL

2、） 用户访问权限的授予、收回,概述SQL的功能,5,交互式SQL 一般DBMS都提供联机交互工具 用户可直接键入SQL命令对数据库进行操作 由DBMS来进行解释,概述SQL的形式,6,嵌入式SQL 能将SQL语句嵌入到高级语言（宿主语言） 使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力 一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句,概述SQL的形式,7,数据定义 Create、Drop、Alter 数据操纵 数据查询：Select 数据修改：Insert、Update、Delete 数据控制 Grant、Revoke,概述SQL的动词,8,数据查询是数据

3、库应用的核心功能 基本结构 Select A1, A2, ., AnFrom r1, r2, ., rmWhere P A1, A2, ., An(p(r1r1.rm),数据查询,9,Select语句的含义,对 From 子句中的各关系，作笛卡儿积（） 对 Where 子句中的逻辑表达式进行选择（）运算，找出符合条件的元组 根据 Select 子句中的属性列表，对上述结果作投影（ ）操作,10,Select语句的含义,结果集 查询操作的对象是关系，结果还是一个关系，是一个结果集，是一个动态数据集,11,Select子句,对应于关系代数的投影（）运算，用以列出查询结果集中的期望属性,12,SQL

4、具有包的特性 Select 子句的缺省情况是保留重复元组（ ALL ），可用 Distinct 去除重复元组 Select Distinct sdept From Student Select All sdept From Student 去除重复元组:费时 需要临时表的支持,Select子句重复元组,13,Select子句 *与属性列表,星号 * 表示所有属性 星号 * ：按关系模式中属性的顺序排列，并具有一定的逻辑数据独立性 显式列出属性名：按用户顺序排列 Select * From Student Select Student.*，cno，grade From Student，SC Wh

5、ere Student.sno = SC.sno,14,Select子句更名,为结果集中的某个属性改名 使结果集更具可读性 Select sno as stu_no，cno as course_no， grade From SC Select sno，sname，2001 - sage as birthday From Student,15,Where 子句,where子句对应与关系代数中的选择() 查询满足指定条件的元组可以通过Where子句来实现 使where子句中的逻辑表达式返回True值的元组，是符合要求的元组，将被选择出来,16,Where 子句运算符,比较：、=、=、not + 确

6、定范围： Between A and B、Not Between A and B 确定集合：IN、NOT IN 字符匹配：LIKE，NOT LIKE 空值：IS NULL、IS NOT NULL 多重条件：AND、OR、NOT,17,Where 子句Like,字符匹配：Like、Not Like 通配符 % 匹配任意字符串 _ 匹配任意一个字符 大小写敏感,18,Where 子句Like,例：列出姓张的学生的学号、姓名。 Select sno，sname From Student Where sname LIKE 张%,19,Where 子句Like,例：列出张姓且单名的学生的学号、姓名。 S

7、elect sno，sname From Student Where sname LIKE 张_ _,20,Where 子句转义符 escape,例：列出课程名称中带有_的课号及课名。 Select cno，cname From Course Where cname LIKE %_% escape ,21,From 子句,From子句对应与关系代数中的笛卡儿积（） 列出将被扫描的关系（表） 例：列出所有学生的学号、姓名、课号、成绩。 Select Sudent.sno，sname，SC.cno，grade From Student，SC Where Student.sno = SC.sno,2

8、2,From 子句元组变量,为 From 子句中的关系定义元组变量 方便关系名的引用 在同一关系的笛卡儿积中进行辨别 例：列出与95001同岁的同学的学号,姓名,年龄 SelectT.sno,T.sname,T.sage FromStudent as T,Student as S WhereS.sno = 95001 AND T.sage = S.sage,23,Order By子句,指定结果集中元组的排列次序 耗时 ASC（缺省）、DESC、未选中的属性 例：列出CS系中的男生的学号、姓名、年龄，并按年龄进行排列（升序） Select sno, sname,sage From Student

9、 Where sdept = CS Order By sage ASC,24,SQL的集合操作,SQL的结果集是“包” 多个 Select 语句的结果可以进行集合操作，使结果为“集合”（default） SQL-92支持 参加集合操作的关系（结果集）必须是相容的,25,SQL的集合操作相容,属性个数必须一致 对应的类型必须一致 属性名无关 最终结果集采用第一个结果的属性名 缺省为自动去除重复元组 除非显式说明ALL Order By放在整个语句的最后,26,SQL的集合操作并,union（并，对应与关系代数的），标准SQL都支持的 采用集合的观点，合成多个查询的结果 select - with

10、out - order by . UNION ALL select - without - order by . UNION ALL select - without - order by . . ORDER BY integer ASC | DESC , . ,27,例：查询计算机系的学生或者年龄不大于19岁的学生，并按年龄倒排序。,SQL的集合操作并,28,intersect（交，对应与关系代数的），并不是所有的DBMS都支持 例：查询计算机系的学生并且年龄不大于19岁的学生，并按年龄倒排序。,SQL的集合操作交,29,except（差，对应与关系代数的 -），并不是所有的DBMS都支持

11、例：查询计算机系的男生。,SQL的集合操作差,30,数据定义语言（Data Definition Language） Create、Drop、Alter 定义一组关系（基本表）、说明各关系的信息 各关系的模式 各属性的值域 完整性约束 索引 安全性和权限,数据定义语言（DDL）,31,字符型 char(n) 、varchar(n) 数值型 integer、smallint numeric(p,d) real、double、float(n) 日期/时间型 date time,DDLSQL中的域类型,32,域定义语句（SQL-92支持） 需重复使用的 Create Domain stu_name

12、varchar(20) Create Domain zip_code char(6),DDLSQL中的域类型,33,Create Table r (A1D1， A2D2， AnDn， ， ） 其中： r 关系名（表名）、 Ai 关系 r 的一个属性名 Dn 属性Ai域值的域类型 主键声明： primary key (Aj1 ，Aj2 ，Ajvm ),DDLSQL的模式定义,34,Create Domain stu_name varchar(20) Create Table Student ( sno char(10) primary key(sno) , sname stu_name , sa

13、ge smallint, ssex char(1), sdept char(2),DDLSQL的模式定义,35,Create Table Course ( cno char(10) primary key (cno) , cname varchar(20) , credit smallint ) Create Table SC ( sno char(10) not null , cno char(10) not null , grade smallint, primary key (sno, cno),DDLSQL的模式定义,36,用SQL删除关系（表） 将整个关系模式（表结构）彻底删除 表中

14、的数据也将被删除 Drop Table r Drop Table student;,DDL删除表结构 Drop,37,删除表中的某属性 去除属性及相应的数据 Alter Table r Drop A,DDL修改模式 Alter,38,增加表中的属性 向已经存在的表中添加属性 allow null 已有的元组中该属性的值被置为Null Alter Table r Add A D Alter Table student phone char(16);,DDL修改模式 Alter,39,属性的默认值 用户不提供某属性的值时，默认值被使用 初始值 TimeStamp DDL中： ModifyDate

15、char(30) Default TimeStamp; Alter Table student Add ID integer Default AutoIncrement;,DDL Default Value,40,定义一个新的域（用户定义的域） 需要重复使用的域 必须具有相同类型的属性 Create Domain As ； Create Domain datelog As char(30) default timestamp 域的删除 Drop Domain datelog 各DBMS的方法是不同的Restrict/Cascade 使用该域的属性的处理,DDL 域定义,41,索引是一种数据结构

16、，是对照表、指针表 索引是为了加速对表中元组的检索而创建的一种分散存储结构（B树） 索引是对表而建立的，由除存放表的数据页面以外的索引页面组成 索引是把双刃剑，减慢更新的速度 索引不是SQL标准的要求,DDL 索引 Index,42,索引的种类 聚簇索引（Clustered Index） 非聚簇索引（Non-Clustered Index）,DDL 索引 Index,43,聚簇索引（Clustered Index） 表中的元组按聚簇索引的顺序物理地存放 根级页面-中间层页面-叶级页面（数据页面） 一个表中只能有一个聚簇索引 更新的复杂性，需要大量的临时空间,DDL 索引 Index,44,非聚

17、簇索引（Non-Clustered Index） 表中元组存储的物理顺序与索引的顺序无关 叶级索引页面是指向数据页面的指针 每个表可有多个非聚簇索引,DDL 索引 Index,45,CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index-name On TableName(Column,Column,) Create Index YearIndex On Movie(year); Create Clustered Index SnoIndex On student(sno) ; Drop Index YearIndex;,DDL 索引 Index,

18、46,查询与更新的评估 查询多？ 更新多？ 索引的覆盖 Where表达式 Where表达式的顺序 索引越多越好吗？ 了解优化器,DDL 索引的使用,47,数据添加 用SQL的插入语句，向数据库表中添加数据 按关系模式的属性顺序 Insert Into Student Values ( 95001, 张三,27, M, CS ) 按指定的属性顺序，也可以只添加部分属性（非Null属性为必需） Insert Into Student ( sno, sname, sage) Values ( 95002, 李四, 26 ),48,把一列中的值进行聚合运算，返回单值的函数 五个预定义的聚合函数 平均值

19、：Avg 总和： Sum 最小值：Min 最大值：Max 计数： Count Count(*)、Count(Distinct),聚合函数,49,Group By 将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现 只有出现在Group By子句中的属性，才可出现在Select子句中 例：统计各系学生的人数。 Select sdept，count(*) as stu_count From Student Group By sdept,聚合函数,50,Having 针对聚合函数的结果值进行筛选（选择），它作用于分组计算结果集 跟在Group By子句的后面，没有Gro

20、up By则针对全表 例：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。 Select sno，count(sno) From SC Where grade = 2,聚合函数,51,Having 与 Where的区别 Where 决定哪些元组被选择参加运算，作用于关系中的元组 Having 决定哪些分组符合要求，作用于分组 聚合函数的条件关系必须用Having，Where中不应出现聚合函数,聚合函数,52,聚合函数忽略Null Count：不计 Sum：不将其计入 Avg：具有 Null 的元组不参与 Max / Min：不参与 例：Select count(sdept) From

21、 Student Select Avg(sage) From Student,聚合函数 Null,53,子查询是嵌套在另一查询中的 Select-From-Where 表达式（Where/Having） SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件 可以用多个简单查询来构成复杂查询，以增强SQL的查询能力 子查询中不使用 Order By 子句，Order By子句只能对最终查询结果进行排序,子查询（Subquery ）,54,返回单值的子查询，只返回一行一列 父查询与单值子查询之间用比较运算符进行连接 运算符：、=、=、 例：找出与95001同龄的学生 Selec

22、t * From Student Where sage = ( Select sage From Student Where sno = 95001 ),子查询单值比较,55,子查询返回多行一列 运算符：In、All、Some(Any)、Exists,子查询多值,56,标量值与子查询返回集中的某一个相等，true IN 被用来测试多值中的成员 例：查询选修C01课程的学生的学号、姓名。 Select sno,sname From Student Where sno IN (Select sno From SC Where cno = C01 ),子查询多值成员In,57,例： 查询选修了 数据

23、库的学生的学号和姓名 Select sno,sname From Student Where sno IN ( Select sno From SC Where cno IN ( Select cno From Course Where cname = 数据库),子查询多值成员In,58,多值比较：多行一列 父查询与多值子查询之间的比较需用All来连接 标量值s比子查询返回集R中的每个都大时，sAll R 为True All表示所有 all、=all、 all all 等价于 not in 例：找出年龄最小的学生 Select * From Student Where sage all ( S

24、elect sage From Student ),子查询多值比较 ALL,59,多值比较：多行一列 父查询与多值子查询之间的比较需用Some/Any来连接 标量值s比子查询返回集R中的某一个都大时 s Some R为True 或 s Any R为True Some(早期用Any)表示某一个,只要有一个即返回真 some、=some、 some = some 等价于 in、 some 不等价于 not in,子查询多值比较Some/Any,60,例：找出不是最小年龄的学生 Select * From student Where sage some ( Select sage From Stud

25、ent ),子查询多值比较,61,例：找出具有最高平均成绩的学号及平均成绩 Select sno ,avg(grade) From SC Group By sno Having avg(grade) = all (Select avg(grade) From SC Group By sno),子查询多值比较,62,Exists + 子查询用来判断该子查询是否返回元组 当子查询的结果集非空时，Exists为True 当子查询的结果集为空时，Exists为False 不关心子查询的具体内容，因此用 Select *,子查询存在判断Exists,63,具有外部引用的子查询，称为相关子查询（Corre

26、lated Queries） 外层元组的属性作为内层子查询的条件,子查询相关子查询,64,例：列出选修了C01课程的学生的学号、姓名 Select sno,sname From Student Where Exists ( Select * From SC Where SC.sno = Student.sno And cno = C01),子查询Correlated & Exists,65,例：列出得过100分的学生的学号、姓名 Select sno,sname From Student Where Exists ( Select * From SC Where SC.sno = Studen

27、t.sno And grade = 100),子查询Correlated & Exists,66,例：查询选修了C01课程的学生的系主任 Select manager From department Where Exists ( Select * From student Where sdept = department.depid And Exists ( Select * From SC Where SC.sno = student.sno And cno = C01),子查询Correlated & Exists,67,例：列出没有选C01课程的学生的学号、姓名 Select sno,s

28、name From Student Where Not Exists ( Select * From SC Where SC.sno = Student.sno And cno = C01),子查询Correlated & Not Exists,68,例：查询选修了所有课程的学生的姓名(ForAll) Select sname From Student Where Not Exists ( Select * From Course Where Not Exists ( Select * From SC Where Student.sno = SC.sno And SC.cno = Co),子查

29、询Correlated & Not Exists,69,SQL-92支持多列的成员资格测试（ASA7.0不支持） 例：找出同系、同年龄、同性别的学生 Select * from Student as T Where （T.sdept,T.sage,T.ssex) IN ( Select sdept,sage,ssex From student as S Where S.sno T.sno ),子查询多列元组的比较,70,SQL-92允许在 From中使用查询表达式 必须为其取名 例：查询平均成绩大于75分的学号、姓名、平均成绩 Select stu_no,sname,avg_grade Fro

30、m Student, (Select sno,avg(grade) From SC Group By sno ) as S (stu_no,avg_grade) Where Student.sno = S.stu_no And avg_grade 75,派生关系,71,NULL 表示数据的缺失 一个确实存在，但我们不知道的值 对本实体此数值无意义，可能是设计上的失误 是SQL的关键字，用于任何类型描述缺失的值 UNKONW 是三值逻辑的一个真值(True/False/Unkonw),空值和连接,72,NULL的运算法则 对NULL值和其他任何值作算术运算时，结果为NULL 对NULL值和其他任

31、何值作比较时，结果为UNKOWN Is Null / Is Not Null,空值和连接,73,Unkown 视True为1，False为0，Unkown为1/2 AND：取小 OR：取大 NOT：取1的补 真值表不必死记硬背,空值和连接,74,关系的连接 连接操作是以两个关系为输入、将另一个关系作为结果返回 笛卡儿积、条件连接、自然连接、外连接 自然连接（ Natural ）： 连接条件是两个关系中具有公共名字的所有属性对均相等，不需要其他条件 对每个相等属性对之一进行投影（去除重复属性） Student Natural Join SC Student SC,75,关系的连接 内连接（Inn

32、er Join）： 它是条件连接，要求参加连接的关系中至少有一对元组满足连接要求，才能生成连接关系 可以是等值连接，也可以是不等值连接 新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性 Inner是缺省的连接方式 也可以用笛卡儿积 + 选择的方法实现 Student Inner Join SC on Student.sno = SC.sno,76,关系的连接 外连接（Outer Join ）： 它是条件连接，且条件是必需的。 若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上Null，而不是像Inner 那样被忽略。

33、新的关系的属性集是参加连接的关系的属性的组合，不去除重复属性 有左外连接、右外连接、全外连接,77,关系的连接 左外连接（Left Outer Join ）： Select * From ( Student Left Outer Join SC on Student.sno = SC.sno) 右外连接（Right Outer Join ）： Select * From ( Student Right Outer Join SC on Student.sno = SC.sno) 全外连接（Full Outer Join ）： Select * From ( Student Full Outer

34、 Join SC on Student.sno = SC.sno),78,数据库的修改,数据库修改包括插入数据、修改数据、删除数据,数据删除（Delete） 只能对整个元组操作，不能只删除某些属性上的值 只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令 Delete From r Where P 从关系 r 中删除满足P的元组，只是删除数据，而不是定义,79,删除单个元组 例：删除学号为95001的学生的选课信息 Delete From SC Where sno = 95001 删除多个元组 例：删除选课而未参加考试的学生的选课信息 Delete From SC

35、 Where grade is null 删除整个关系中的所有数据 例：删除所有学生的选课信息 Delete From SC,80,数据插入（Insert） 单行插入：一次只插入一个元组 例：新增一个学生信息 Insert Into Student Values ( 98001, Gloria,25, F, CS) 多行插入：插入一个集合 例:给CS系的学生开设必修课C05，建立选课信息 Insert Into SC Select sno,cno,null From Student,Course Where sdept = CS and cno = C05,81,数据更新（Update） 改变

36、符合条件的某个（某些）元组的属性值 例：将95001学生转入MA系 Update Student Set sdept = MA Where sno = 95001 例：所有学生年龄加1 Update Student Set sage = sage + 1,82,数据更新（Update） 例：将选修C05课程的学生的成绩改为该课的平均成绩 Update SC Set grade = (Select avg(grade) From SC Where cno = C05) Where cno = C05,先计算avg，再做Update,83,视图（View） 是从一个或几个基本表（或视图）中导出的虚

37、表 数据库中只保存它的定义 是RDBMS提供给用户以多种角度观察数据库中数据的重要机制 创建视图、删除 Create View v as Drop View v 例：计算机系的花名册 Create View CS_Stu as Select sno,sname,ssex From Student Where sdept = CS,84,视图（View） 视图名可以出现在任何关系名可以出现的地方 例：列出计算机系的男生 Select sno,sname From CS_Stu Where ssex = M 例：建立学生平均成绩视图 Create View avg_grade(sno,avg) a

38、s Select sno,avg(grade) From SC Group By sno,85,视图（View） 例：找出平均成绩大于等75的学生 Select * From avg_grade Where avg = 75 注意：此例的使用方法是非标准的。,86,视图（View） 视图的更新：单表、原始属性构成的视图可以更新（像基本表那样更新） 由多表构成的视图由很大的限制 视图中的非原始属性也不能更新 例：Update avg_grade Set avg = 100 Where sno = 95001,87,视图（View）视图的作用 简化用户的操作 不同的用户可从不同的角度看待同一数据

39、支持一定的逻辑数据独立性 数据的安全性,88,数据定义语言（Data Definition Language） Create、Drop、Alter 定义一组关系（基本表）、说明各关系的信息 各关系的模式 各属性的值域 完整性约束 索引 安全性和权限 各关系在磁盘上的物理存储结构,数据定义语言（DDL）,89,SQL中的域类型 字符型 char(n) varchar(n) 数值型 int smallint numeric(p,d) real、double float(n) 日期/时间型 date time,90,域定义语句（SQL-92支持） 需重复使用的 Create Domain stu_n

40、ame varchar(20) Create Domain zip_code char(6),91,SQL的模式定义 Create Table r (A1D1， A2D2， AnDn， ， ） 其中： r 关系名（表名） Ai 关系 r 的一个属性名 Dn 属性Ai域值的域类型 主键声明： primary key (Aj1 ，Aj2 ，Ajvm ) 校验声明： check (逻辑表达式 ),92,Examples Create Domain stu_name varchar(20) Create Table Student (sno char(10) not null , sname stu_

41、name , sage smallint, ssex char(1), sdept char(2), primary key (sno), check(ssex in (M, F),93,Create Table Course (cno char(10) not null , cname varchar(20) , credit smallint, primary key (cno) Create Table SC (sno char(10) not null , cno char(10) not null , grade smallint, primary key (sno, cno), c

42、heck(grade =0 and grade =100),94,用SQL删除关系（表） 将整个关系模式（表结构）彻底删除 Drop Table r 用SQL删除表中的某属性 去除属性及相应的数据 Alter Table r Drop A 用SQL增加表中的属性 向已经存在的表中添加属性 已有的元组中该属性的值被置为Null Alter Table r Add A D,95,数据添加 用SQL的插入语句，向数据库表中添加数据 按关系模式的属性顺序 Insert Into Student Values ( 95001, 张三,27, M, CS ) 按指定的属性顺序，也可以只添加部分属性（非Nu

43、ll属性为必需） Insert Into Student ( sno, sname, sage) Values ( 95002, 李四, 26 ),96,Null Null是unknown 与Null的比较都返回unknown (unknown or unknown) unknown (true and unknown) unknown (false and unknown) = false (unknown and unknown) = unknown,97,练习,针对学生-课程体系，作SQL查询 列出各系的学生数（人数多的排在前面） 找出各科成绩均在85分以上（含）的学生的学号、姓名 找出有三门课程的成绩在75分以下的学生的学号、姓名 列出数据库成绩的前五名学生的学号、姓名、成绩,