数据库原理 第3章 关系数据库标准语言SQL.docx
- 文档编号:3570181
- 上传时间:2022-11-24
- 格式:DOCX
- 页数:58
- 大小:74.63KB
数据库原理 第3章 关系数据库标准语言SQL.docx
《数据库原理 第3章 关系数据库标准语言SQL.docx》由会员分享,可在线阅读,更多相关《数据库原理 第3章 关系数据库标准语言SQL.docx(58页珍藏版)》请在冰豆网上搜索。
数据库原理第3章关系数据库标准语言SQL
第三章关系数据库标准语言SQL
一、本章的主要内容包括:
3.1SQL概述
3.2学生-课程数据库
3.3数据定义
3.4数据查询
3.5数据更新
3.6视图
3.7小结
3.1SQL概述
SQL是关系数据库的标准语言,是一个通用的、功能极强的关系数据库语言。
一、SQL的产生与发展
1970年,美国IBM研究中心的E.F.Codd连续发表多篇论文,提出关系模型。
1972年,IBM公司开始研制实验型关系数据库管理系统SYSTEMR,配制的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言,在语言中使用了较多的数学符号。
1974年,Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQUEryLanguage)语言。
后来SEQUEL简称为SQL(StructuredQueryLanguage),即“结构式查询语言”,SQL的发音仍为“sequel”。
现在SQL已经成为一个标准。
二、SQL的特点
SQL的主要特点是:
1.综合统一
2.高度非过程化
3.面向集合的操作方式
4.以同一种语法结构提供两种使用方法
5.语言简洁,易学易用
1.综合统一
(1)SQL集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体
(2)可以独立完成数据库生命周期中的全部活动,包括:
✓定义关系模式,插入数据,建立数据库;
✓对数据库中的数据进行查询和更新;
✓数据库重构和维护
✓数据库安全性、完整性控制等
(3)用户数据库投入运行后,可根据需要随时逐步修改模式,不影响数据的运行。
(4)数据操作符统一,克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。
2.高度非过程化
非关系数据模型的数据操纵语言“面向过程”,必须制定存取路径,而SQL只要提出“做什么”,无须了解存取路径(存取路径的选择以及SQL的操作过程由系统自动完成),减轻了用户的负担,有利于提高数据独立性。
3.面向集合的操作方式
非关系数据模型采用面向记录的操作方式,操作对象是一条记录。
而SQL采用集合操作方式,操作对象、查找结果可以是元组的集合,一次插入、删除、更新操作的对象也可以是元组的集合
4.以同一种语法结构提供两种使用方法
SQL是独立的语言,能够独立地用于联机交互的使用方式。
SQL又是嵌入式语言,能够嵌入到高级语言(例如C,C++,Java)程序中,供程序员设计程序时使用。
5.语言简洁,易学易用
SQL功能极强,完成核心功能只用了9个动词:
SQL功能
动词
数据查询
SELECT
数据定义
CREATE,DROP,ALTER
数据操纵
INSERT,UPDATE,DELETE
数据控制
GRANT,REVOKE
三、SQL的基本概念
SQL支持关系数据库三级模式结构:
(1)基本表
基本表是本身独立存在的表,SQL中一个关系就对应一个基本表。
一个(或多个)基本表对应一个或多个存储文件。
一个表可以带若干索引。
(2)存储文件
存储文件的逻辑结构组成了关系数据库的内模式,物理结构是任意的,对用户是透明的。
(3)视图
视图是从一个或几个基本表导出的表,是一个虚表,在数据库中只存放视图的定义而不存放视图对应的数据。
用户可以在视图上再定义视图。
3.2学生-课程数据库
我们用之前定义的学生-课程数据库例子来讲解SQL的数据定义、数据操纵、数据查询和数据控制语句。
首先,要定义一个学生-课程模式S-T。
学生-课程数据库包括以下3个表:
(1)学生表:
Student
学号
Sno
姓名
Sname
性别
Ssex
年龄
Sage
所在系
Sdept
200215121
李勇
男
20
CS
200215122
刘晨
女
19
CS
200215123
王敏
女
18
MA
200215125
张立
男
19
IS
(2)课程表:
Course
课程号
Cno
课程名
Cname
先行课
Cpno
学分
Ccredit
1
数据库
5
4
2
数学
2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理
2
7
PASCAL语言
6
4
(3)学生选课表:
SC
学号
Sno
课程号
Cno
成绩
Grade
200215121
1
92
200215121
2
85
200215121
3
88
200215122
2
90
200215122
3
80
3.3数据定义
SQL的数据定义功能包括:
模式定义、表定义、视图和索引的定义,所使用的语句如下表所示:
操作对象
操作方式
创建
删除
修改
模式
CREATESCHEMA
DROPSCHEMA
表
CREATETABLE
DROPTABLE
ALTERTABLE
视图
CREATEVIEW
DROPVIEW
索引
CREATEINDEX
DROPINDEX
一、模式的定义与删除
1.创建模式
SQL模式的创建可用CREATESCHEMA语句定义,其基本句法如下:
CREATESCHEMA〈模式名〉AUTHORIZATION〈用户名〉
例如:
为用户CHEN定义一个学生-课程模式S-T
CREATESCHEMAS-TAUTHORIZATIONCHEN
如果没有指定〈模式名〉,则〈模式名〉隐含为〈用户名〉。
CREATESCHEMAAUTHORIZATIONCHEN
该语句定义一个模式CHEN
定义模式实际上定义了一个命名空间,在此空间中可以进一步定义该模式包含的数据库对象。
2.删除模式
删除模式使用DROP语句,其基本句法如下:
DROPSCHEMA〈模式名〉[CASCADE│RESTRICT]
CASCADE│RESTRICT是两种删除方式,二者必选其一。
CASCADE是级联方式,表示在删除模式的同时将该模式中的所有DB对象全部一起删除。
RESTRICT是约束方式,表示如果该模式下已经定义了下属的DB对象(如表、视图等),则拒绝该删除语句的执行。
例如:
以约束方式删除学生-课程模式S-T
DROPSCHEMAS-TRESTRICT
二、基本表的定义、删除与修改
1.定义基本表
CREATETABLE<表名>
(<列名><数据类型>[<列级完整性约束条件>]
[,<列名><数据类型>[<列级完整性约束条件>]]…
[,<表级完整性约束条件>]);
说明:
<表名>:
所要定义的基本表的名字
<列名>:
组成该表的各个属性(列)
<列级完整性约束条件>:
涉及相应属性列的完整性约束条件
<表级完整性约束条件>:
涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。
常用完整性约束
✓主码约束:
PRIMARYKEY
✓唯一性约束:
UNIQUE
✓非空值约束:
NOTNULL
✓参照完整性约束:
FOREIGNKEY
提问:
PRIMARYKEY与UNIQUE的区别是什么?
1PRIMARYKEY在建立的时候会默认地建立该列的索引,且此PRIMARYKEY可以作为作为另外的表的FOREIGNKEY;
2PRIMARYKEY一定是NOTNULL,而UNIQUE则没有此限制
例如:
(1)建立“学生”表Student,学号是主码,姓名取值唯一
CREATETABLEStudent
(SnoCHAR(9)PRIMARYKEY,/*列级完整性约束条件*/
SnameCHAR(20)UNIQUE,/*Sname取唯一值*/
SsexCHAR
(2),
SageSMALLINT,
SdeptCHAR(20)
);
(2)建立一个“课程”表Course
CREATETABLECourse
(CnoCHAR(4)PRIMARYKEY,
CnameCHAR(40),
CpnoCHAR(4),/*先修课程*/
CcreditSMALLINT,
FOREIGNKEY(Cpno)REFERENCESCourse(Cno)
/*表级完整性约束条件,Cpno是外码,被参照表是Course,被参照列是Cno*/
);
本例说明,参照表和被参照表可以是同一个表。
(3)建立一个“学生选课”表SC
CREATETABLESC
(SnoCHAR(9),
CnoCHAR(4),
GradeSMALLINT,
PRIMARYKEY(Sno,Cno),
/*主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGNKEY(Sno)REFERENCESStudent(Sno),
/*表级完整性约束条件,Sno是外码,被参照表是Student*/
FOREIGNKEY(Cno)REFERENCESCourse(Cno)
/*表级完整性约束条件,Cno是外码,被参照表是Course*/
);
2.数据类型
关系模型中有一个很重要的概念是:
域。
SQL中域的概念用数据类型来实现。
定义表的属性时,需要指明其数据类型及长度。
一个属性选用哪种数据类型,要根据实际情况来决定,一般从两个方面来考虑:
1取值范围
2要做哪些运算
例如,对于年龄(Sage)属性,可以采用CHAR(3)作为数据类型,但是考虑到要在年龄上做算术运算,而CHAR(n)数据类型上是不能做算术运算的,所以要采用整数作为数据类型。
又因为一个人的年龄在百岁左右,所以采用SMALLINT(短整数)作为年龄的数据类型,而没必要采用INT(长整数)。
下表是数据类型的表示和含义:
数据类型
含义
CHAR(n)
长度为n的定长字符串
VARCHAR(n)
最大长度为n的变长字符串
INT
长整数(也可以写作INTEGER)
SMALLINT
短整数
NUMERIC(p,d)
定点数,由p位数字(不包括符号、小数点)组成,小数后面有d位数字
REAL
取决于机器精度的浮点数
DoublePrecision
取决于机器精度的双精度浮点数
FLOAT(n)
浮点数,精度至少为n位数字
DATE
日期,包含年、月、日,格式为YYYY-MM-DD
TIME
时间,包含一日的时、分、秒,格式为HH:
MM:
SS
3.模式与表
每一个基本表都属于某一个模式,一个模式包含多个基本表。
定义基本表所属模式,一般有三种方法:
1方法一:
在表名中明显地给出模式名
CreatetableS-T.Student(......);/*模式名为S-T*/
CreatetableS-T.Cource(......);
CreatetableS-T.SC(......);
2方法二:
在创建模式语句中同时创建表
CREATESCHEMAS-TAUTHORIZATIONCHEN
CREATETABLEStudent(......);
3方法三:
设置所属的模式,这样在创建表时不必给出模式名。
SETsearch_pathTO"S-T",PUBLIC;
CREATETABLEStudent(......);
4.修改基本表
SQL语言用ALTERTABLE来修改基本表,一般格式为:
ALTERTABLE<表名>
[ADD<新列名><数据类型>[完整性约束]]
[DROP<完整性约束名>]
[ALTERCOLUMN<列名><数据类型>];
例如:
(1)向Student表增加“入学时间”列,其数据类型为日期型。
ALTERTABLEStudentADDS_entranceDATE;
不论基本表中原来是否已有数据,新增加的列一律为空值。
(2)取消学生姓名必须是唯一值的约束条件
ALTERTABLEStudentDROPUNIQUE(Sname);
(3)将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
ALTERTABLEStudentALTERCOLUMNSageINT;
(4)增加课程名称必须取唯一值的约束条件。
ALTERTABLECourseADDUNIQUE(Cname);
SQL也可以删除一个属性列,分为直接和间接删除两种方式:
(1)间接方式是:
首先把表中要保留的列及其内容复制到一个新表中,然后删除原表,再将新表重命名为原表名。
(2)直接删除列的方式为:
例如,删除学生所在系这一列
ALTERTABLEStudentDROPSdept;
ALTERTABLEStudentDropCOLUMNSdept;/*SQLSever*/
5.删除基本表
当某个基本表不再需要时,可以使用DROPTABLE语句来删除它。
其一般格式为:
DROPTABLE<表名>[RESTRICT|CASCADE];
✓RESTRICT:
删除表是有限制的:
1欲删除的基本表不能被其他表的约束所引用
2如果存在依赖该表的对象(例如视图),则此表不能被删除
✓CASCADE:
删除该表没有限制,在删除基本表的同时,相关的依赖对象一起删除
例如:
(1)删除Student表
DROPTABLEStudentCASCADE;
基本表定义被删除,数据被删除;表上建立的索引、视图、触发器等一般也将被删除
(2)若表上建有视图,选择RESTRICT时表不能被删除;如果选择CASCADE时可以删除表,视图也自动被删除
DROPTABLEStudentCASCADE;
--NOTICE:
dropcascadestoviewIS_Student
SELECT*FROMIS_Student;
--ERROR:
relation"IS_Student"doesnotexist
三、索引的建立与删除
建立索引的目的是为了加快查询速度。
由谁建立索引:
✓DBA或表的属主(即建立表的人)
✓DBMS一般会自动建立以下列上的索引:
1PRIMARYKEY
2UNIQUE
由谁维护索引
✓DBMS自动完成
如何使用索引
✓DBMS自动选择是否使用索引以及使用哪些索引
RDBMS中索引一般采用B+树、HASH索引来实现
1B+树索引具有动态平衡的优点
2HASH索引具有查找速度快的特点
采用B+树,还是HASH索引则由具体的RDBMS来决定
索引是关系数据库的内部实现技术,属于内模式的范畴
CREATEINDEX语句定义索引时,可以定义索引是唯一索引、非唯一索引或聚簇索引
1.建立索引
语句格式
CREATE[UNIQUE][CLUSTER]INDEX<索引名>
ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);
用<表名>指定要建索引的基本表名字。
索引可以建立在该表的一列或多列上,各列名之间用逗号分隔。
用<次序>指定索引值的排列次序,升序:
ASC,降序:
DESC。
缺省值:
ASC。
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录;CLUSTER表示要建立的索引是聚簇索引。
所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织,也就是说,基表中数据也需要按指定的聚簇属性值的升序或降序存放。
用户可以在最经常查询的列上建立聚簇索引以提高查询效率;而经常需要更新的列不宜建立聚簇索引。
显然,在一个基本表上最多只能建立一个聚簇索引。
例如:
(1)在Student表的Sname(姓名)列上建立一个聚簇索引:
CREATECLUSTERINDEXStusnameONStudent(Sname);
Student表中记录将按照Sname值的升序存放。
(2)为学生-课程数据库中的Student,Course,SC三个表建立索引。
CREATEUNIQUEINDEXStusnoONStudent(Sno);
CREATEUNIQUEINDEXCoucnoONCourse(Cno);
CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);
建立唯一值索引需要注意的是:
对于已含重复值的属性列不能建UNIQUE索引。
对某个列建立UNIQUE索引后,插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。
这相当于增加了一个UNIQUE约束。
2.删除索引
索引一经建立,就由系统来维护它,不需要用户干预。
建立索引减少查询操作的时间,但如果数据增加删改频繁,系统会花费许多时间来维护索引。
所以,这时需要删除一些不必要的索引。
删除索引使用DROPINDEX语句,一般格式为:
DROPINDEX<索引名>;
删除索引时,系统会从数据字典中删去有关该索引的描述。
例如:
删除Student表的Stusname索引
DROPINDEXStusname;
3.4数据查询
数据查询使用SELECT语句,其一般格式是:
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>[ASC|DESC]];
✓SELECT子句:
指定要显示的属性列
✓FROM子句:
指定查询对象(基本表或视图)
✓WHERE子句:
指定查询条件
✓GROUPBY子句:
对查询结果按指定列的值分组,该属性列值相等的元组为一个组。
通常会在每组中作用集函数。
✓HAVING短语:
筛选出只有满足指定条件的组
✓ORDERBY子句:
对查询结果表按指定列值的升序或降序排序
在关系代数中最常用的式子是下列表达式:
πA1,…,An(σF(R1×…×Rm))
这里R1,…,Rm为关系,F是条件公式,A1、…、An为属性。
针对上述表达式,SQL为此设计了SELECT—FROM—WHERE句型:
SELECTA1,…,An
FROMR1,…,Rm
WHEREF
这个句型是从关系代数表达式演变来的,但WHERE子句中的条件表达式F要比关系代数中公式更灵活。
一、单表查询
单表查询仅涉及一个表,是一种最简单的查询操作。
1选择表中的若干列
2选择表中的若干元组
3对查询结果排序
4使用集函数
5对查询结果分组
1.选择表中的若干列
(1)查询指定列
[例1]查询全体学生的学号与姓名。
SELECTSno,Sname
FROMStudent;
[例2]查询全体学生的姓名、学号、所在系。
SELECTSname,Sno,Sdept
FROMStudent
(2)查询全部列:
选出所有属性列的两种方式:
1在SELECT关键字后面列出所有列名
2将<目标列表达式>指定为*
[例3]查询全体学生的详细记录。
SELECTSno,Sname,Ssex,Sage,Sdept
FROMStudent;
或
SELECT*
FROMStudent;
(3)查询经过计算的值
SELECT子句的<目标列表达式>可以是:
1算术表达式
2字符串常量
3函数
4列别名等
[例4]查全体学生的姓名及其出生年份。
SELECTSname,2010-Sage/*假定当年的年份为2010年*/
FROMStudent;
输出结果:
Sname
2010-Sage
李勇
1990
刘晨
1991
王敏
1992
张立
1991
[例5]查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,‘YearofBirth:
',2010-Sage,ISLOWER(Sdept)
FROMStudent;
输出结果:
Sname
'YearofBirth:
'
2010-Sage
ISLOWER(Sdept)
李勇
YearofBirth:
1990
cs
刘晨
YearofBirth:
1991
cs
王敏
YearofBirth:
1992
ma
张立
YearofBirth:
1991
is
使用列别名改变查询结果的列标题:
SELECTSnameNAME,'YearofBirth:
'BIRTH,
2000-SageBIRTHDAY,LOWER(Sdept)DEPARTMENT
FROMStudent;
输出结果:
NAME
BIRTH
BIRTHDAY
DEPARTMENT
李勇
YearofBirth:
1990
cs
刘晨
YearofBirth:
1991
cs
王敏
YearofBirth:
1992
ma
张立
YearofBirth:
1991
is
2.选择表中的若干元组
(1)消除取值重复的行
两个本来并不完全相同的元组,投影到指定的某些列上后,可能就变成相同的行了,可以用DISTINCT取消重复的行。
如果没有指定DISTINCT关键词,则缺省为ALL,即全部都显示。
[例6]查询选修了课程的学生学号。
SELECTSnoFROMSC;
等价于:
SELECTALLSnoFROMSC;
执行上面的SELECT语句后,结果为:
Sno
200215121
200215121
200215121
200215122
200215122
指定DISTINCT关键词,去掉表中重复的行
SELECTDISTINCTSno
FROMSC;
执行结果:
Sno
200215121
20021512
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库原理 第3章 关系数据库标准语言SQL 数据库 原理 关系 标准 语言 SQL