第3章 关系数据库标准语言SQL.docx
- 文档编号:11564145
- 上传时间:2023-06-01
- 格式:DOCX
- 页数:40
- 大小:34.94KB
第3章 关系数据库标准语言SQL.docx
《第3章 关系数据库标准语言SQL.docx》由会员分享,可在线阅读,更多相关《第3章 关系数据库标准语言SQL.docx(40页珍藏版)》请在冰点文库上搜索。
第3章关系数据库标准语言SQL
第三章关系数据库标准语言SQL
主要内容:
✧SQL的基本概念
✧数据定义
✧数据查询
✧数据更新
✧视图
3.1SQL概述
SQL(StructuredQueryLanguage),即结构化查询语言,是关系数据库的标准语言,SQL是一个通用的、功能极强的关系数据库语言。
决大部分数据库管理系统都支持SQL,许多软件厂商对SQL基本命令集还进行了扩充和修改。
大多数据库均用SQL作为共同的数据存取语言和标准接口,使不同数据库系统之间的互操作有了共同的基础。
SQL已成为数据库领域中的主流语言。
3.1.1SQL的产生和发展
1974提出并实现
1986ANSI的数据库委员会批准作为关系数据库语言的美国标准。
公布了标准文本。
1987ISO也通过这一标准。
标准逐步发展。
3.1.2SQL的特点
1.综合统一
SQL集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)的功能于一体,语言风格统一,可以独立完成数据库生命令周期中的全部活动,包括:
✧定义关系模式,插入数据,建立数据库;
✧对数据库中的数据进行查询和更新;
✧数据库重构和维护;
✧数据库安全性、完整性控制等。
有了这些功能,就为数据库应用系统的开发提供了良好的环境(通过分层开发来解释)。
特别是在数据库系统投入运行后,还可以根据需要随进地逐步地修改模式,并不影响数据库的运行,从而使系统具有良好的可扩展性。
关系数据库数据结构的单一性带来数据操作符的统一性,查找、插入、删除、更新等每一种操作都只需一种操作符。
2.高度非过程化
非关系数据模型的数据操纵语言是“面向过程”,必须制定存取路径,指明“怎么做”;SQL只要提出“做什么”,无需指明“怎么做”,无须了解存取路径。
存取路径的选择以及SQL的操作过程由系统自动完成。
这样就减轻了用户负担,有利于提高数据独立性。
3.面向集合的操作方式
非关系数据模型采用面向记录的操作方式,操作对象是一条记录;SQL采用集合操作方式,操作对象、查找结果是元组的集合,一次插入、删除、更新操作的对象可以是元组的集合。
4.以同一种语法结构提供多种使用方式
SQL是独立的语言,能够独立地用于联机交互的使用方式。
SQL又是嵌入式语言,SQL能够嵌入到高级语言(例如C,C++,Java,.NET)程序中,供程序员设计程序时使用。
两种方式下,SQL的语法结构基本上是一致的,提供了极大的灵活性与方便性。
5.语言简洁,易学易用
SQL功能极强,但语言十分简洁,完成核心功能只用了9个动词,并且接近英语口语。
(见P81表)。
3.1.3SQL与三模式结构
1.关系数据库的模式结构
RDBMS支持关系数据库的三级模式结构。
(见P81图)。
外模式对应于视图和部分基本表;模式对应于基本表;内模式对应于存储文件。
基本表是本身独立存在的表,一个关系对应一个基本表;一个或多个基本表对应一个存储文件;一个表可有若干索引,索引也存放在存储文件中。
存储文件的逻辑结构组成了关系数据库的内模式。
存储文件的物理结构对用户是透明的。
2.SQL对关系数据库模式的支持
用户可以用SQL对基本表和视图进行查询或其它操作。
3.2学生-课程数据库
分析学生课程数据库,分析基本表,属性,主码,外码等。
3.3数据定义
SQL的数据定义功能有三个操作,分别是创建(CREATE)、删除(DROP)和修改(ALTER)。
主要包括模式定义、表定义、视图定义和索引定义(见p83表)。
SQL通常不提供修改模式、视图、索引的操作。
用户如果想修改这些对象,只要先将它们删除掉,然后再重新创建。
3.3.1模式的创建与删除
1什么是模式
定义模式实际上定义了一个命名空间,在这个空间中可以定义该模式包含的数据库对象,例如基本表、视图、索引等。
(Oracle中对模式的解释:
模式是一个命了名的对象的集合,对象可以是表、视图、索引、存储过程、触发器等。
一个用户被创建时,一个与之对应的模式也被创建;一个用户只能与一个模式相关;用户名与模式经常互换。
)
2定义模式
格式1:
CREATESCHEMA<模式名>AUTHORIZATION<用户名>
格式2:
CREATESCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]
说明:
1)如果没有指定<模式名>,那么<模式名>隐含为<用户名>。
2)用户可以在创建模式的同时在这个模式中创建基本表、视图,也可以授权。
例1:
定义一个学生-课程模式S-T。
CREATESCHEMA“S-T”AUTHORIZATIONWANG;
例2:
CREATESCHEMAAUTHORIZATIONWANG;
例3:
CREATESCHEMATESTAUTHORIZATIONZHANG
CREATETABLETAB1(COL1SMALLINT,
COL2INT,
COL3CHAR(20),
COL4NUMERIC(10,3),
COL5DECIMAL(5,2));
3删除模式
格式:
DROPSCHEMA<模式名>
说明:
1)CASCADE和RESTRICT两者必选其一。
2)CASCADE(级联),删除模式的同时把该模式中所有的数据库对象全部删除。
3)RESTRICT(限制),如果该模式中已定义了数据库对象,则拒绝该删除语句的执行。
当该模式中没有任何下属的对象时才能执行。
例:
DROPSCHEMAZHANGCASCADE;
3.3.2基本表的创建、删除与修改
1数据类型
关系模型中一个很重要的概念是域。
每一个属性来自一个域,它的取值必须是域中的值。
在SQL中域的概念用数据类型来实现。
定义表的各个属性时需要指明其数据类型及长度。
SQL提供了一些主要数据类型,见p86表。
不同的RDBMS中支持的数据类型不完全相同。
一个属性选用哪种数据类型,长度取多少要根据实际情况来决定,一般要从两个方面来考虑:
一是取值范围,二是要做哪些运算。
(举例说明:
1字符串、数值;2定长字符串、变长字符串;3整数、实数;4日期类型)
2定义基本表
主要完成:
定义表名;定义表结构;定义完整性约束。
格式:
CREATETABLE<表名>
(<列名><数据类型>[<列级完整性约束条件>]
[,<列名><数据类型>[<列级完整性约束条件>]]
…
[,<表级完整性约束条件>]
);
说明:
1)<表名>:
所要定义的基本表的名字,同一个模式中不能重名。
2)<列名>:
组成该表的各个属性(列,字段)名,同一个表中不能重名。
3)<列级完整性约束条件>:
涉及该属性列的完整性约束条件,也可以在“表级完整性约束条件”处定义。
4)<表级完整性约束条件>:
涉及一个或多个属性列的完整性约束条件。
5)用户操作表中数据时,由RDBMS自动检查是否违背完整性约束条件。
6)多个列之间、多个表级完整性约束条件之间用逗号分开。
7)右圆括号前一项后面不加逗号。
例1:
建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。
其中学号不能为空,是主键,并且姓名取值也唯一。
CREATETABLEStudent
(SnoCHAR(12)PRIMARYKEY,
SnameVARCHAR(20)UNIQUENOTNULL,
SsexCHAR
(2),
SageINT,
SdeptCHAR(15)
);
例2:
建立一个课程表Course。
CREATETABLECourse
(CnoCHAR(4)PRIMARYKEY,
CnameVARCHAR(30)UNIQUENOTNULL,
CpnoCHAR(4),
CcreditSMALLINT,
FOREIGNKEY(Cpno)REFERENCESCourse(Cno)
);
例3:
建立一个学生选课表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中(Sno,Cno)为主码。
CREATETABLESC
(SnoCHAR(12),
CnoCHAR(4),
GradeINT,
Primarykey(Sno,Cno),
FOREIGNKEY(Sno)REFERENCESStudent(Sno),
FOREIGNKEY(Cno)REFERENCESCourse(Cno)
);
3模式与表
每一个基本表都属于某一个模式,一个模式包含多个基本表,定义基本表时有三种方法确定它所属的模式:
✧在表名中明确地给出模式名,Createtable“S-T”.Student(......);
✧在创建模式语句中同时创建表。
✧设置所属的模式,这样在创建表时表名中不必给出模式名。
4修改基本表
随着应用环境和应用需求的变化,有时需要修改已经建立的基本表。
格式:
ALTERTABLE<表名>
[ADD<新列名><数据类型>[完整性约束]]
[DROP<完整性约束名>]
[ALTERCOLUMN<列名><数据类型>];
说明:
ADD子句:
增加新列或新的完整性约束条件
DROP子句:
删除指定的完整性约束条件或删除列(SQLServer:
CONSTRAINTconstraint_name;COLUMNcolumnname)。
ALTERCOLUMN子句:
用于修改原有的列定义,包括修改列名和数据类型。
例1:
向Student表增加“入学时间”列,其数据类型为日期型。
ALTERTABLEStudentADDS-entranceDATE;
不论基本表中原来是否已有数据,新增加的列一律为空值。
例2:
将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
ALTERTABLEStudentALTERCOLUMNSageINT;
例3:
增加课程名称必须取唯一值的约束条件。
ALTERTABLECourseADDUNIQUE(Cname);
5删除基本表
当某个基本表不再需要时,可删除它。
格式:
DROPTABLE<表名>[RESTRICT|CASCADE]
说明:
1)若选择RESTRICT,则该表的删除是有限制条件的。
要删除的基本表不能被其它表的约束所引用(如CHECK,FOREIGNKEY等约束),不能有视图,不能有触发器等;若选择CASCADE,则该表的删除没有限制条件,删除基本表的同时,相关的依赖对象也将一起被删除。
2)RESTRICT|CASCADE在SQLServer2000中不被支持。
3.3.3索引的建立与删除
1概述
建立索引是加快查询速度的有效手段。
用户可以根据需要,在基本表上建立一个或多个索引。
用户可以定义索引是唯一索引(每一个索引值只对应唯一的记录)、非唯一索引或聚簇索引(索引项的顺序与表中记录的物理顺序一致)。
谁来建立索引:
✧DBA或表的属主(即建立表的人)根据需要建立。
✧有些DBMS自动在主键列上或UNIQUE列上建立索引。
维护索引:
DBMS自动完成。
使用索引:
DBMS自动选择是否使用索引以及使用哪些索引。
首先在索引表中查找,然后根据索引表中的信息到物理表中找到满足的记录。
索引结构:
RDBMS中索引一般采用B+树、HASH索引来实现。
(B+树是多路搜索树,具有动态平衡的优点;HASH索引具有查找速度快的特点)。
索引是关系数据库的内部实现技术,属于内模式的范畴,采用B+树,还是HASH索引则由具体的RDBMS来决定。
在哪些列上建立索引:
用户可以在经常查询的列上建立索引
2建立索引
格式:
CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);
说明:
1)UNIQUE,唯一索引,每一个索引值只对应唯一的数据记录。
2)CLUSTER,聚簇索引,索引项的顺序与表中记录的物理顺序一致。
用户可以在最经常查询的列上建立聚簇索引以提高查询效率,一个基本表上只能建立一个聚簇索引。
更新索引列上的数据时,往往导致表中记录的物理顺序的变更,代价较大,因此对于经常更新的列不宜建聚簇索引。
3)可以依据多列建立索引。
4)索引值的排列次序可以是升序(ASC,默认),也可以是降序(DESC)。
例1:
CREATECLUSTERINDEXStusnameONStudent(Sname);
例2:
为学生-课程数据库中的Student,Course,SC三个表建立索引。
其中Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引。
CREATEUNIQUEINDEXStusnoONStudent(Sno);
CREATEUNIQUEINDEXCoucnoONCourse(Cno);
CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);
3删除索引
格式:
DROPINDEX<索引名>;
3.4数据查询
数据查询是数据库的核心操作,SQL提供了SELECT语句实现查询功能。
格式:
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>[ASC|DESC]];
说明:
解释格式。
SQLServer中,FROM部分也可以省略。
3.4.1单表查询
单表查询是指仅涉及一个表的查询。
1选择表中的若干列
关系代数的投影运算。
(1)查询指定列
在很多情况下,用户只对表中的一部分属性列感兴趣,可以通过SELECT<目标列表达式>指定要查询的属性列。
例1:
查询全体学生的学号与姓名。
SELECTSno,Sname
FROMStudent;
例2:
查询全体学生的姓名、学号、所在系。
SELECTSname,Sno,Sdept
FROMStudent;
(2)查询全部列
有两种方法:
在SELECT关键字后面列出所有列名;将<目标列表达式>指定为*。
例:
查询全体学生的详细记录。
SELECTSno,Sname,Ssex,Sage,Sdept
FROMStudent;
或
SELECT*
FROMStudent;
(3)查询经过计算的值
SELECT子句的<目标列表达式>不仅可以是表中的属性列,也可以是算术表达式、字符串常量、函数等。
例1:
查询全体学生的姓名、出生年份和所在系,要求用小写字母表示所有系名。
SELECTSname,'YearofBirth:
',2010-Sage,LOWER(Sdept)
FROMStudent;
关于查询结果的列名,如果查询对应的是基本表列名,结果表该列也是列名,如果是其它表达式,SQLServer则处理为“无列名”。
可以指定列名,格式<表达式>[AS]<列名>。
上例可以作如下查询:
SELECTSnameNAME,'YearofBirth:
’BIRTH,2010-SageBIRTHDAY,LOWER(Sdept)DEPARTMENT
FROMStudent;
2选择表中的若干元组
从行方向查询,关系代数中的选择运算。
(1)消除重复的行
经过投影运算后,原来不相同的元组可能变成相同了,可以用DISTINCT取消重复行,即只保留一行。
例:
选修了课程的学生学号
SELECTSno
FROMSC;
SELECTDISTINCTSno
FROMSC;
(2)查询满足条件的元组
通过WHERE子句实现,WHERE<条件表达式>,常用的查询条件及谓词,如下表所示:
查询条件
谓词
比较
=,>,>=,<,<=,!
=,<>,!
>,!
<;NOT+上述比较运算符
确定范围
BETWEENAND,NOTBETWEENAND
确定集合
IN,NOTIN
字符匹配
LIKE,NOTLIKE
空值
ISNULL,ISNOTNULL
逻辑运算
AND,OR,NOT
1)比较查询
条件表达式格式:
[NOT]<字段表达式>比较运算符<表达式>。
当运算结果为真时,相应记录就加入结果集。
例1:
查询计算机科学系全体学生的名单。
SELECTSnameFROMStudentWHERESdept=’CS’;
例2:
查询所有年龄在20岁以下的学生姓名及其年龄。
SELECTSname,Sage
FROMStudent
WHERESage<20;--条件还可以是NOTSage>=20
例3:
查询考试成绩有不及格的学生的学号。
SELECTDISTINCTSno
FROMSC
WHEREGrade<60;
2)范围查询
范围查询指查找属性值在(或不在)指定范围内的元组。
具体格式:
[NOT]BETWEENexp1ANDexp2(等价于x>=exp1andx<=exp2)。
例1:
查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,Sage
FROMStudent
WHERESageBETWEEN20AND23;
例2:
查询年龄不在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,Sage
FROMStudent
WHERESageNOTBETWEEN20AND23;
3)集合查询
集合查询用来查找属性值属于(或不属于)指定集合的元组。
通过谓词[NOT]IN来实现。
具体格式:
字段表达式[NOT]IN(集合元素列表)。
例1:
查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptIN('IS','MA','CS');
例2:
查询即不是信息系、数学系,也不是计算机科学系的学生姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptNOTIN('IS','MA','CS');
4)字符匹配查询
谓词LIKE可以用来进行字符的匹配查询。
格式如下:
match_expression[NOT]LIKEpattern
其含义是查找指定的属性列值与
✧%代表任意长度(长度可以为0)的字符串。
✧_代表任意单个字符。
当用户要查询的字符串本身就含有%或_时,要使用ESCAPE'<换码字符>'短语对通配符进行转义(C语言中的转意字符)。
例1:
查询学号为200215121的学生的详细情况。
SELECT*
FROMStudent
WHERESnoLIKE‘200215121’;--等价于Sno=’200215121’
例2:
查询所有姓刘学生的姓名、学号和性别。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameLIKE‘刘%’;
例3:
查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECTSname
FROMStudent
WHERESnameLIKE'欧阳_';
注意:
使用环境如果是ANSI字符集,则应两个下划线,如果是Unicode字符集,则一个下划线。
SQLServer2000使用的是Unicode字符集。
例3:
查询名字中第2个字为“阳”字的学生姓名和学号。
SELECTSname,Sno
FROMStudent
WHERESnameLIKE'_阳%';
例4:
查询所有不姓刘的学生姓名、学号、性别。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameNOTLIKE'刘%';
例5:
查询以“DB_”开头,且倒数第3个字符为i的课程的详细情况。
SELECT*
FROMCourse
WHERECnameLIKE'DB\_%i__'ESCAPE'\';
换码字符(转意字符)‘\’,表明其后的一个字符是普通字符而不是通配符。
换码符可以任意.。
5)涉及空值的查询
谓词ISNULL和ISNOTNULL可用来查询空值和非空值。
例1:
查询缺少成绩的学生学号和相应的课程号(某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩)。
SELECTSno,Cno
FROMSC
WHEREGradeISNULL;
6)多重条件查询
通过逻辑运算符AND和OR可用来联结多个查询条件,从而实现多重条件查询。
✧AND的优先级高于OR
✧可以用括号改变优先级
例1:
查询计算机系年龄在20岁以下的学生姓名。
SELECTSname
FROMStudent
WHERESdept='CS'ANDSage<20;
例2:
查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdept='IS'ORSdept='MA'ORSdept='CS';
3对查询结果排序
如果没有指定查询结果的显示顺序,DBMS将按其最方便的顺序(通常是元组中表中的先后顺序)输出查询结果。
用户可以用ORDERBY子句指定按照一个或多个属性列的升序(ASC默认)或降序(DESC)重新排列查询结果。
当排序列含空值时,ASC:
排序列为空值的元组最后显示;DESC:
排序列为空值的元组最先显示。
例1:
查询选修了3号课程的学生学号及其成绩,查询结果按分数降序排列。
SELECTSno,Grade
FROMSC
WHERECno='3'
ORDERBYGradeDESC;
例2:
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*
FROMStudent
ORDERBYSdept,SageDESC;
4使用聚合函数查询(汇总查询)
为了进一步方便用户,增强汇总查询功能,SQ提供了许多聚合函数。
聚合函数可以应用于表中的所有行、WHERE子句指定的表的子集。
下表显示了聚合函数的语法及结果(expression几乎总为列名)。
聚合函数
描述
COUNT(*)
选定的行数(统计元组个数)
COUNT([ALL|DISTINCT]
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第3章 关系数据库标准语言SQL 关系 数据库 标准 语言 SQL