数据库系统原理第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.1 SQL,INSERT,UPDATE,DELETE
3、,第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 SQL概述3.2 学生-课程数据库3.3 数据定义3.4 数据查询3.5
4、数据更新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的权限,例题,例1 定义一个学生-课程模式S-TCREATE SCHEMA“S-T”AUTHORIZAT
5、ION WANG;为用户“WANG”定义了一个模式S-T,3.3.1 模式的定义与删除,二、删除模式 DROP SCHEMA 其中CASCADE和RESTRICT两者必选其一1、CASCADE(级联),表示在删除模式同时把该模式中所有的数据库对象全部一起删除2、RESTRICT(限制),表示若该模式下已定义了下属的数据库对象,则拒绝删除,例题,例4 删除模式ZHANGDROP SCHEMA ZHANG CASCADE,3.3 数据定义,3.3.1 模式的定义与删除3.3.2 基本表的定义、删除与修改3.3.3 索引的建立与删除,3.3.2 基本表的定义、删除与修改,一、定义基本表,:所要定义的
6、基本表的名字:组成该表的各个属性(列):涉及相应属性列的完整性约束条件:涉及一个或多个属性列的完整性约束条件,CREATE TABLE(,);,例题,例1 建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号是主键,并且姓名取值也唯一。,CREATE TABLE Student(Sno CHAR(5)primary key,Sname CHAR(8)UNIQUE,Ssex CHAR(2),Sage INT,Sdept CHAR(10);,例题(续),Sno,Sname,Ssex,Sage,Sdept,字符型,字符型
7、,字符型,整数,字符型,长度为,5,长度为,8,长度为,2,长度为,10,不能为空值,定义基本表(续),常用完整性约束主码约束:PRIMARY KEY唯一性约束:UNIQUE非空值约束:NOT NULL参照完整性约束PRIMARY KEY与 UNIQUE的区别?,例题(续),例2 建立一个“学生选课”表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中(Sno,Cno)为主码。,CREATE TABLE SC(Sno CHAR(5),Cno CHAR(3),Grade int,Primary key(Sno,Cno);,二、修改基本表,ALTER TABLE ADD 完整性约束
8、DROP ALTER COLUMN DROP COLUMN;,:要修改的基本表ADD子句:增加新列和新的完整性约束条件DROP子句:删除指定的完整性约束条件ALTER COLUMN子句:用于修改列名和数据类型DROP COLUMN子句:用于删除列,例题,例2 向Student表增加“入学时间”列,其数据类型为日期型。,ALTER TABLE Student ADD Scome DATETIME;不论基本表中原来是否已有数据,新增加的列一律为空值。,例题,例3 将入学日期的数据类型改为字符型。,ALTER TABLE Student ALTER COLUMN Scome char(8);注:修改
9、原有的列定义有可能会破坏已有数据,语句格式(续),删除属性列,例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 索引的建立与删除,3.3.3 索引的建立与删除,建立索引是加快查询速度的有效手段建立索引DBA或表的属主(即建立表的人)根据需要建立有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE维护索
10、引 DBMS自动完成使用索引 DBMS自动选择是否使用索引以及使用哪些索引,一、建立索引,语句格式CREATE UNIQUE CLUSTER INDEX ON(,);,用指定要建索引的基本表名字索引可以建立在该表的一列或多列上,各列名之间用逗号分隔用指定索引值的排列次序,升序:ASC,降序:DESC。缺省值:ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引,建立索引(续),唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后,插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNI
11、QUE约束,建立索引(续),聚簇索引建立聚簇索引后,基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例:CREATE CLUSTER INDEX Stusname ON Student(Sname);在Student表的Sname(姓名)列上建立一个聚簇索引,而且Student表中的记录将按照Sname值的升序存放,建立索引(续),在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途:对于某些类型的查询,可以提高查询效率聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作聚簇索引的不适用情形表记录太少经常插入、删除、修改
12、的表数据分布平均的表字段,建立索引(续),在下列三种情况下,有必要建立簇索引:(1)查询语句中采用该字段作为排序列(2)需要返回局部范围的大量数据(3)表格中某字段内容的重复性比较大例如,student表中dno(系号)一列有大量重复数据,当在dno列上建立了簇索引后,下面的连接查询速度会加快。,建立索引(续),多列索引和多个单列索引,考虑两种不同的建立索引方式:case 1:对c1,c2,c3三列按此顺序添加一个多列索引;case 2:对c1,c2,c3分别建立三个单列索引;问题1:按c1搜索时,哪种索引效率快?答:case2问题2:按C2搜索时,哪种索引效率快?答:case2,并且,cas
13、e1的索引无效问题3:按C1,C2,C3搜索哪种效率快?答:case1问题4:按C2,C3,C1搜索时哪种效率快?答:case2,因为没有按多列索引的顺序搜索,case1的索引没有使用到。,覆盖查询简单的说就是所有查询列被所使用的索引覆盖的查询,建立索引(续),如何去建立一个多列索引,最重要的一个问题是如何安排列的顺序是至关重要的比如需要对一个表tb里面的两个字段foo,bar建一个索引,那么索引的顺序是(foo,bar)还是(bar,foo)呢假设tb表有1700条记录,foo字段有750个不同的记录,那么foo字段上的cardinality是750。总规则可以说是cardinality越大
14、的字段应该排在索引的第一位就是说索引的位置是(foo,bar),因为cardinality越大那么第一次取出来的记录集就越小,再进行第二次查询的次数就越少了。,建立索引(续),当在同一表格中建立簇索引和非簇索引时,先建立簇索引后建非簇索引比较好。因为如先建非簇索引的话,当建立簇索引时,SQL Server会自动将非簇索引删除,然后重新建立非簇索引。每个表仅可以有一个簇索引,最多可以有249个非簇索引。它们均允许以一个或多个字段作为索引关键字(Index Key),但最多只能有16个字段。,SQL Server只对那些能加快数据查询速度的索引才能被选用。如果利用索引检索还不如顺序扫描速度快,SQ
15、L Server仍用扫描方法检索数据。建立不能被采用的索引只会增加系统的负担,降低检索速度。因此,可利用性是建立索引的首要条件。,例题,例6 为学生-课程数据库中的Student,Course,SC三个表建立索引。其中Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,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)
16、;,二、删除索引,DROP INDEX;删除索引时,系统会从数据字典中删去有关该索引的描述。例7 删除Student表的Stusname索引。DROP INDEX Student.Stusname;,第3章 关系数据库标准语言SQL,3.1 SQL概述3.2 学生-课程数据库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 ORD
17、ER BY ASC|DESC;,3.4 查 询,SELECT子句:指定要显示的属性列FROM子句:指定查询对象(基本表或视图)WHERE子句:指定查询条件 GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语:筛选出只有满足指定条件的组ORDER BY子句:对查询结果表按指定列值的升序或降序排序,3.4 查 询,3.4.1 单表查询3.4.2 连接查询3.4.3 嵌套查询3.4.4 集合查询3.4.5 SELECT 语句的一般格式,3.4.1 单表查询,查询仅涉及一个表,是一种最简单的查询操作一、选择表中的若干列二、选择表中
18、的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组,查询指定列,例1 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student;例2 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student;,查询全部列,例3 查询全体学生的详细记录。,SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student;或SELECT*FROM Student;,3.查询经过计算的值,SELECT子句的为表达式算术表达式字符串常量函数列别名等,3.查询经过计算的值,例4 查全体学生的姓名及其出生年份。,S
19、ELECT Sname,2011-SageFROM Student;,3.查询经过计算的值,例5 查询全体学生的姓名、出生年份和所在系。在出生年份前面增加一个说明,在系名称后面增加一个“系”作为表示,SELECT Sname,出生年份:,2011-Sage,Sdept+系FROM Student;,例5.1 使用列别名改变查询结果的列标题,SELECT Sname 姓名,Year of Birth:生日标识,2011-Sage 生日,Sdept+系 系名FROM Student;,二、选择表中的若干元组,消除取值重复的行查询满足条件的元组,1.消除取值重复的行,在SELECT子句中使用DIST
20、INCT短语,假设SC表中有下列数据 Sno Cno Grade-95001 1 92 95001 2 85 95001 3 88 95002 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 GradeFROM SC;正确的
21、写法 SELECT DISTINCT Cno,Grade FROM SC;,2.查询满足条件的元组,WHERE子句常用的查询条件,(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,SageFROM Studen
22、tWHERE Sage BETWEEN 20 AND 23;,例题(续),例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。,SELECT Sname,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23;,(3)确定集合,使用谓词 IN,NOT IN:用逗号分隔的一组取值例12查询信息系(IS)和计算机科学系(CS)学生的姓名和性别。,SELECT Sname,SsexFROM StudentWHERE Sdept IN(IS,CS);,(3)确定集合,例13查询既不是信息系又不是计算 机科学系的学生的姓名和性别。,SELECT
23、 Sname,SsexFROM Student WHERE Sdept NOT IN(IS,CS);,(4)字符串匹配,NOT LIKE ESCAPE:指定匹配模板 匹配模板:固定字符串或含通配符的字符串 当匹配模板为固定字符串时,可以用=运算符取代 LIKE 谓词 用!=或 运算符取代 NOT LIKE 谓词,通配符,%(百分号)代表任意长度(长度可以为0)的字符串例:a%b表示以a开头,以b结尾的任意长度的字符串。如acb,addgb,ab 等都满足该匹配串_(下横线)代表任意单个字符例:a_b表示以a开头,以b结尾的长度为3的任意字符串。如acb,afb等都满足该匹配串,ESCAPE 短
24、语:,当用户要查询的字符串本身就含有%或 _ 时,要使用ESCAPE 短语对通配符进行转义。,例题,1)匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况。SELECT*FROM Student WHERE Sno LIKE 95001;等价于:SELECT*FROM Student WHERE Sno=95001;,例题(续),2)匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。,SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘%;,例题(续),匹配模板为含通配符的字符串(续)例16 查询姓“刘且
25、全名为三个汉字的学生的姓名。,SELECT Sname FROM Student WHERE Sname LIKE 刘_;,备注:这里有两个_,关于SQL Server 2008字符集,SELECT COLLATIONPROPERTY(Chinese_PRC_Stroke_CI_AI_KS_WS,CodePage),查询结果:936 简体中文GBK 950 繁体中文BIG5 437 美国/加拿大英语 932 日文 949 韩文 866 俄文 65001 unicode UFT-8,数据库字符集为ASCII时,一个汉字需要两个_;当数据库字符集为GBK时,一个汉字只需要一个_,例题(续),匹配模
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库系统原理第3章 关系数据库标准语言SQL 数据库 系统 原理 关系 标准 语言 SQL

链接地址:https://www.31ppt.com/p-4519883.html