《数据库系统原理》第3章 关系数据库标准语言SQL.ppt
《《数据库系统原理》第3章 关系数据库标准语言SQL.ppt》由会员分享,可在线阅读,更多相关《《数据库系统原理》第3章 关系数据库标准语言SQL.ppt(272页珍藏版)》请在三一文库上搜索。
1、,计算机科学系 2017版,林子雨 厦门大学,第3章 关系数据库标准语言SQL (2017版),厦门大学计算机科学系本科生课程 数据库系统原理,提纲,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图,3.1 SQL概述,3.1.1 SQL的产生与发展 3.1.2 SQL的特点 3.1.3 SQL的基本概念,3.1.1 SQL的产生与发展,1974年,由Boyce和Chamberlin提出,并在IBM公司研制的关系数据库管理系统原型System R 上实现 1986年10月,美国国家标准局的数据库委员会X3H2批准了SQL作为关系数
2、据库语言的美国标准,并公布了SQL标准文本 1987年,国际标准化组织通过这一标准,3.1.2 SQL的特点,SQL的特点 1. 综合统一 2. 高度非过程化 3. 面向集合的操作方式 4. 以同一种语法结构提供两种使用方法 5. 语言简洁,易学易用,1、综合统一(操纵三级模式),SQL,视图1,视图2,基表1,基表2,基表3,基表4,存储文件1,存储文件2,外模式,模式,内模式,5、语言简捷,易学易用,表,语言的动词,SQL,功,能,动,词,数,据,定,义,CREATE, DROP, ALTER,数,据,查,询,SELECT,数,据,操,纵,数,据,控,制,GRANT , REVOKE,3.
3、1 SQL,INSERT , UPDATE , DELETE,第3章 关系数据库标准语言SQL,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图,3.2 学生-课程数据库,学生-课程数据库 学生表:Student(Sno,Sname,Ssex,Sage,Sdept) 课程表:Course(Cno,Cname,Cpno,Ccredit) 学生选课表:SC(Sno,Cno,Grade),3.2 学生-课程数据库,Student,(a),3.2 学生-课程数据库,Course,(b),3.2 学生-课程数据库,SC,(c),提纲,3.1
4、 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图,3.3 数据定义,3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除,3.3 数据定义,3.3 数据定义,3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除,3.3.1 模式的定义与删除,一、定义模式 CREATE SCHEMA AUTHORIZATION Ps: 1、若没有指定,那么隐含为 2、要创建模式,调用该命令的用户必须具有DBA权限,或者获得了DBA授予的CREATE SCHEMA的权限
5、,例题,例1 定义一个学生-课程模式S-T CREATE SCHEMA “S-T” AUTHORIZATION WANG; 为用户“WANG”定义了一个模式S-T,3.3.1 模式的定义与删除,二、删除模式 DROP SCHEMA 其中CASCADE和RESTRICT两者必选其一 1、CASCADE(级联),表示在删除模式同时把该模式中所有的数据库对象全部一起删除 2、RESTRICT(限制),表示若该模式下已定义了下属的数据库对象,则拒绝删除,例题,例4 删除模式ZHANG DROP SCHEMA ZHANG CASCADE,3.3 数据定义,3.3.1 模式的定义与删除 3.3.2 基本表
6、的定义、删除与修改 3.3.3 索引的建立与删除,3.3.2 基本表的定义、删除与修改,一、定义基本表,:所要定义的基本表的名字 :组成该表的各个属性(列) :涉及相应属性列的完整性约束条件 :涉及一个或多个属性列的完整性约束条件,CREATE TABLE ( , , );,例题,例1 建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号是主键,并且姓名取值也唯一。,CREATE TABLE Student (Sno CHAR(5) primary key, Sname CHAR(8) UNIQUE, Ssex C
7、HAR(2) , Sage INT, Sdept CHAR(10);,例题 (续),Sno,Sname,Ssex,Sage,Sdept, ,字符型,字符型,字符型,整数,字符型,长度为,5,长度为,8,长度为,2,长度为,10,不能为空值,定义基本表(续),常用完整性约束 主码约束: PRIMARY KEY 唯一性约束:UNIQUE 非空值约束:NOT NULL 参照完整性约束 PRIMARY KEY与 UNIQUE的区别?,例题 (续),例2 建立一个“学生选课”表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中(Sno, Cno)为主码。,CREATE TABLE SC(
8、Sno CHAR(5) , Cno CHAR(3) , Grade int, Primary key (Sno, Cno);,二、修改基本表,ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN DROP COLUMN ;,:要修改的基本表 ADD子句:增加新列和新的完整性约束条件 DROP子句:删除指定的完整性约束条件 ALTER COLUMN子句:用于修改列名和数据类型 DROP COLUMN子句:用于删除列,例题,例2 向Student表增加“入学时间”列,其数据类型为日期型。,ALTER TABLE Student ADD Scome DATETIME; 不论
9、基本表中原来是否已有数据,新增加的列一律为空值。,例题,例3 将入学日期的数据类型改为字符型。,ALTER TABLE Student ALTER COLUMN Scome char(8); 注:修改原有的列定义有可能会破坏已有数据,语句格式(续),删除属性列,例4 将入学日期列删除掉。,ALTER TABLE Student DROP COLUMN Scome,三、删除基本表,DROP TABLE ; 基本表删除 数据、表上的索引 自动都删除,例题,例5 删除SC表 DROP TABLE SC;,3.3 数据定义,3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3
10、 索引的建立与删除,3.3.3 索引的建立与删除,建立索引是加快查询速度的有效手段 建立索引 DBA或表的属主(即建立表的人)根据需要建立 有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE 维护索引 DBMS自动完成 使用索引 DBMS自动选择是否使用索引以及使用哪些索引,一、建立索引,语句格式 CREATE UNIQUE CLUSTER INDEX ON (, );,用指定要建索引的基本表名字 索引可以建立在该表的一列或多列上,各列名之间用逗号分隔 用指定索引值的排列次序,升序:ASC,降序:DESC。缺省值:ASC UNIQUE表明此索引的每一个索引值只对应唯一的数
11、据记录 CLUSTER表示要建立的索引是聚簇索引,建立索引 (续),唯一值索引 对于已含重复值的属性列不能建UNIQUE索引 对某个列建立UNIQUE索引后,插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束,建立索引 (续),聚簇索引 建立聚簇索引后,基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例: CREATE CLUSTER INDEX Stusname ON Student(Sname); 在Student表的Sname(姓名)列上建立一个聚簇索引,而 且Student表中的记录将按照
12、Sname值的升序存放,建立索引 (续),在一个基本表上最多只能建立一个聚簇索引 聚簇索引的用途:对于某些类型的查询,可以提高查询效率 聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作 聚簇索引的不适用情形 表记录太少 经常插入、删除、修改的表 数据分布平均的表字段,建立索引 (续),在下列三种情况下,有必要建立簇索引: (1)查询语句中采用该字段作为排序列 (2)需要返回局部范围的大量数据 (3)表格中某字段内容的重复性比较大例如,student表中dno(系号)一列有大量重复数据,当在dno列上建立了簇索引后,下面的连接查询速度会加快。,建立索引 (续),多列索引
13、和多个单列索引,考虑两种不同的建立索引方式: case 1:对c1,c2,c3三列按此顺序添加一个多列索引; case 2: 对c1,c2,c3分别建立三个单列索引; 问题1:按c1搜索时,哪种索引效率快? 答:case2 问题2:按C2搜索时,哪种索引效率快? 答:case2,并且,case1的索引无效 问题3:按C1,C2,C3搜索哪种效率快? 答:case1 问题4:按C2,C3,C1搜索时哪种效率快? 答:case2,因为没有按多列索引的顺序搜索,case1的索引没有使用到。,覆盖查询简单的说就是所有查询列被所使用的索引覆盖的查询,建立索引 (续),如何去建立一个多列索引,最重要的一个
14、问题是如何安排列的顺序是至关重要的 比如需要对一个表tb里面的两个字段foo,bar建一个索引,那么索引的顺序是(foo,bar)还是(bar,foo)呢 假设tb表有1700条记录,foo字段有750个不同的记录,那么foo字段上的cardinality是750。总规则可以说是cardinality越大的字段应该排在索引的第一位就是说索引的位置是(foo,bar),因为cardinality越大那么第一次取出来的记录集就越小,再进行第二次查询的次数就越少了。,建立索引 (续),当在同一表格中建立簇索引和非簇索引时,先建立簇索引后建非簇索引比较好。因为如先建非簇索引的话,当建立簇索引时,SQL
15、 Server会自动将非簇索引删除,然后重新建立非簇索引。每个表仅可以有一个簇索引,最多可以有249个非簇索引。它们均允许以一个或多个字段作为索引关键字(Index Key),但最多只能有16个字段。,SQL Server只对那些能加快数据查询速度的索引才能被选用。如果利用索引检索还不如顺序扫描速度快,SQL Server仍用扫描方法检索数据。建立不能被采用的索引只会增加系统的负担,降低检索速度。因此,可利用性是建立索引的首要条件。,例题,例6 为学生-课程数据库中的Student,Course,SC三个表建立索引。其中Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引
16、,SC表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);,二、删除索引,DROP INDEX ; 删除索引时,系统会从数据字典中删去有关该索引的描述。 例7 删除Student表的Stusname索引。 DROP INDEX Student.Stusname;,第3章 关系数据库标准语言SQL,3.1 SQL概述 3.2 学生-课程数据
17、库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图,3.4 查 询,3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询 3.4.4 集合查询 3.4.5 SELECT 语句的一般格式,3.4 查 询,语句格式 SELECT ALL|DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC ;,3.4 查 询,SELECT子句:指定要显示的属性列 FROM子句:指定查询对象(基本表或视图) WHERE子句:指定查询条件 GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会
18、在每组中作用集函数。 HAVING短语:筛选出只有满足指定条件的组 ORDER BY子句:对查询结果表按指定列值的升序或降序排序,3.4 查 询,3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询 3.4.4 集合查询 3.4.5 SELECT 语句的一般格式,3.4.1 单表查询,查询仅涉及一个表,是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组,查询指定列,例1 查询全体学生的学号与姓名。 SELECT Sno, Sname FROM Student; 例2 查询全体学生的姓名、学号、所在系。 SEL
19、ECT Sname,Sno,Sdept FROM Student;,查询全部列,例3 查询全体学生的详细记录。,SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或 SELECT * FROM Student;,3. 查询经过计算的值,SELECT子句的为表达式 算术表达式 字符串常量 函数 列别名 等,3. 查询经过计算的值,例4 查全体学生的姓名及其出生年份。,SELECT Sname,2011-Sage FROM Student;,3. 查询经过计算的值,例5 查询全体学生的姓名、出生年份和所在系。在出生年份前面增加一个说明,在系名称后面增加一
20、个“系”作为表示,SELECT Sname, 出生年份: , 2011-Sage, Sdept + 系 FROM Student;,例5.1 使用列别名改变查询结果的列标题,SELECT Sname 姓名, Year of Birth: 生日标识, 2011-Sage 生日, Sdept+系 系名 FROM Student;,二、选择表中的若干元组,消除取值重复的行 查询满足条件的元组,1. 消除取值重复的行,在SELECT子句中使用DISTINCT短语,假设SC表中有下列数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002
21、 2 90 95002 3 80,ALL 与 DISTINCT,例6 查询选修了课程的学生学号。,(1) SELECT Sno FROM SC; 或(默认 ALL) SELECT ALL Sno FROM SC;,(2) SELECT DISTINCT Sno FROM SC;,例题(续),注意 DISTINCT短语的作用范围是所有目标列 例:查询选修课程的各种成绩 错误的写法 SELECT DISTINCT Cno,DISTINCT Grade FROM SC; 正确的写法 SELECT DISTINCT Cno,Grade FROM SC;,2.查询满足条件的元组,WHERE子句常用的查询
22、条件,(1) 比较大小,在WHERE子句的中使用比较运算符 =,=,!,!, 逻辑运算符NOT + 比较运算符 例8 查询所有年龄在20岁以下的学生姓名及其年龄。,SELECT Sname, Sage FROM Student WHERE Sage = 20;,(2) 确定范围,使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。,SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;,例题(续),例11 查询年龄不在2
23、023岁之间的学生姓名、系别和年龄。,SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;,(3) 确定集合,使用谓词 IN , NOT IN :用逗号分隔的一组取值 例12查询信息系(IS)和计算机科学系(CS)学生的姓名和性别。,SELECT Sname, Ssex FROM Student WHERE Sdept IN ( IS, CS );,(3) 确定集合,例13查询既不是信息系又不是计算 机科学系的学生的姓名和性别。,SELECT Sname,Ssex FROM Student WHERE Sd
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库系统原理 数据库系统原理第3章 关系数据库标准语言SQL 数据库 系统 原理 关系 标准 语言 SQL
链接地址:https://www.31doc.com/p-4340747.html