自考04735数据库原理及应用SQL语言.docx
- 文档编号:11009584
- 上传时间:2023-05-28
- 格式:DOCX
- 页数:27
- 大小:29.27KB
自考04735数据库原理及应用SQL语言.docx
《自考04735数据库原理及应用SQL语言.docx》由会员分享,可在线阅读,更多相关《自考04735数据库原理及应用SQL语言.docx(27页珍藏版)》请在冰点文库上搜索。
自考04735数据库原理及应用SQL语言
自考04735数据库原理及应用SQL语言
要求、目标:
SQL语言是关系数据库的标准语言,是本课程的一个重点。
本章总的要求是:
全面掌握、深刻理解、熟练应用。
要求了解嵌入式SQL和存储过程,掌握SQL定义语句的应用,熟练掌握SQL查询语句、视图、SQL更新语句的应用。
一、SQL简介
1.SQL:
结构化查询语言,关系数据库的标准语言。
2.SQL数据库的体系结构
SQL数据库的体系结构基本上也是三级结构,但术语与传统的关系模型术语不同。
在SQL中,关系模式称为“基本表”,存储模式称为“存储文件”,子模式称为“视图”,元组称为“行”,属性称为“列”。
SQL数据库的体系结构要点:
1)一个SQL模式是表和约束的集合。
2)一个表由行集构成,一行是列的序列,每列对应一个数据项。
3)表有三种类型:
基本表、视图和导出表。
基本表:
实际存储在数据库中的表。
视图:
由若干基本表或其他视图构成的表的定义。
导出表:
执行了查询时产生的表。
4)一个基本表可以跨一个或多个存储文件,一个存储文件也可以存放一个或多个基本表。
每个存储文件与外部存储器上一个物理文件对应。
5)用户可以用SQL语句对基本表和视图进行查询等操作。
在用户看来,两者一样,都是表。
6)SQL语句可嵌在C、FORTRAN等主语言的程序中使用,也可在交互环境下供终端用户使用。
3.SQL的组成
核心SQL主要有四个部分:
1)数据定义语言,即DDL,用于定义SQL模式、基本表、视图、索引等结构。
2)数据操纵语言,即SQLDML。
数据操纵分成数据查询和数据更新两类。
而数据更新又分成插入、删除和修改三种操作。
3)嵌入式SQL语言的使用规定
4)数据控制语言,即SQLDCL,这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。
4.SQL的特点
1)SQL具有十分灵活和强大的查询功能。
2)SQL不是一个应用开发语言,它只提供对数据库的操作功能。
但SQL既可作为交互式语言独立使用,也可作为子语言嵌入在主语言中使用,成为应用开发语言的一部分。
3)SQL是国际标准语言,有利于各种数据库之间交换数据,有利于程序的移植,有利于实现高度的数据独立性,有利于实现标准化。
4)SQL完成核心功能只用9个英语动词,语法结构接近英语,容易学习和使用。
二、SQL的数据定义
1.SQL模式的创建
在SQL中,一个SQL模式定义为基本表的集合。
一个SQL模式由模式名和模式拥有者的用户名或账号来确定,并包含模式中每一个元素(基本表、视图、索引等)的定义。
创建SQL模式,就是定义了一个存储空间。
创建模式语法:
CREATESCHEMA<模式名>AUTHORIZATION<用户名>
2.SQL模式的撤销
语法:
DROPSCHEMA<模式名>[CASCADE|RESTRICT]
说明:
CASCADE(级联式)方式:
执行DROP语句时,把SQL模式及其下属的基本表、视图、索引等所有元素全部撤销。
RESTRICT(约束式)方式:
只有当SQL模式中没有任何下属元素时,才能撤销SQL模式,否则拒绝执行DROP语句。
3.SQL的基本数据类型
1)数值型
INTEGER(或INT)长整数
SMALLINT短整数
REAL浮点数
DOUBLEPRECISION双精度浮点数
FLOAT(n)浮点数,精度至少为n位数字
NUMERIC(p,d)定点数,有p位数字(不包括符号、小数点)组成,小数点后有d位数字,也可写成DECIMAL(p,d)或DEC(p,d)
2)字符串型
CHAR(n)长度为n的定长字符串
VARCHAR(n)具有最大长度为n的变长字符串
3)位串型
BIT(n)长度为n的二进制位串
BITVARYING(n)最大长度为n的变长二进制位串
4)时间型
DATE日期,包含年、月、日,形为YYYY-MM-DD
TIME时间,包含时、分、秒,形为HH:
MM:
SS
4.基本表结构的创建
语法:
CREATETABLE<基本表名>
(<列名类型>,
……
<完整性约束>,
……)
说明:
完整性约束常用以下几种子句:
主键子句(PRIMARYKEY)、外键子句(FOREIGNKEY)等
NOTNULL:
表示不允许某一列的值为空值
例:
对于教学数据库中的四个关系:
教师关系T(T#,TNAME,TITLE)
课程关系C(C#,CNAME,T#)
学生关系S(S#,SNAME,AGE,SEX)
选课关系SC(S#,C#,SCORE)
创建基本表。
CREATETABLET
(T#CHAR(4)NOTNULL,
TNAMECHAR(8)NOTNULL,
TITLECHAR(10),
PRIMARYKEY(T#))
CREATETABLEC
(C#CHAR(4)NOTNULL,
CNAMECHAR(10)NOTNULL,
T#CHAR(4),
PRIMARYKEY(C#),
FOREIGNKEY(T#)REFERENCEST(T#))
CREATETABLES
(S#CHAR(4)NOTNULL,
SNAMECHAR(8)NOTNULL,
AGESMALLINT,
SEXCHAR
(2),
PRIMARYKEY(S#))
CREATETABLESC
(S#CHAR(4),
C#CHAR(4),
SCORESMALLINT,
PRIMARYKEY(S#,C#),
FOREIGNKEY(S#)REFERENCESS(S#),
FOREIGNKEY(C#)REFERENCESC(C#))
5.基本表结构的修改
1)增加新的列
语法:
ALTERTABLE<基本表名>ADD<列名><类型>
2)删除原有的列
语法:
ALTERTABLE<基本表名>DROP<列名>[CASCADE|RESTRICT]
说明:
CASCADE方式表示在基本表中删除某列时,所有引用到该列的视图和约束也要一起自动地被删除;RESTRICT方式表示在没有视图或约束引用该属性时,才能在基本表中删除该列,否则拒绝删除。
3)修改原有列的类型、宽度
语法:
ALTERTABLE<基本表名>MODIFY<列名><类型>
6.基本表的撤销
语法:
DROPTABLE<基本表名>[CASCADE|RESTRICT]
7.索引的创建
语法:
CREATE[UNIQUE]INDEX<索引名>ON<基本表名>(<列名序列>)
说明:
UNIQUE表示每个索引值对应惟一的数据记录。
索引在用户查询时会自动起作用。
一个索引键可以对应多个列。
索引排列时可以升序,也可以降序,升序排列用ASC表示,降序排列用DESC表示,默认时表示升序。
8.索引的撤销
语法:
DROPINDEX<索引名>
三、SQL的数据查询
1.SELECT查询语句句型
在关系代数中最常用的式子是下列表达式:
ΠA1,…,An(σF(R1×…×Rm))
这里,R1、…、Rm为关系,F是公式,A1、…、An为属性。
与该表达式对应,SQL设计了SELECT—FROM—WHERE句型:
SELECTA1,…,An
FROMR1,…,Rm
WHEREF
注意:
1)在学习时,应把SELECT语句和关系代数表达式联系起来考虑问题。
2)在WHERE子句的条件表达式F中可使用下列运算符:
比较运算符:
〈、〈=、〉、〉=、=、〈〉或!
=
逻辑运算符:
AND、OR、NOT
集合成员资格运算符:
IN、NOTIN
谓词:
EXISTS、ALL、SOME
聚合函数:
AVG、MIN、MAX、SUM、COUNT
F中运算对象还可以是另一个SELECT语句,即SELECT语句可以嵌套。
2.SELECT语句的使用技术
SELECT使用时有三种写法:
连接查询、嵌套查询和带存在量词的嵌套查询。
例:
针对前面使用的教学数据库,检索学习课程号为C2课程的学生学号与姓名。
第一种写法(连接查询):
SELECTS.S#,SNAME
FROMS,SC
WHERES.S#=SC.S#ANDC#=’C2’
第二种写法(嵌套查询):
SELECTS#,SNAME
FROMS
WHERES#IN(SELECTS#
FROMSC
WHEREC#=‘C2’)
第三种写法(使用存在量词的嵌套查询):
SELECTS#,SNAME
FROMS
WHEREEXISTS(SELECT*
FROMSC
WHERESC.S#=S.S#ANDC#=’C2’)
例:
对于教学数据库中四个基本表T、C、S、SC,用SELECT语句表示下面查询。
1)检索学习课程号为C2课程的学生学号与成绩。
SELECTS#,SCORE
FROMSC
WHEREC#=‘C2’
2)检索学习课程号为C2课程的学生学号和姓名。
见上例
3)检索至少选修LIU老师所授课程中一门课程的学生学号与姓名。
SELECTS.S#,SNAME
FROMS,SC,C,T
WHERES.S#=SC.S#ANDSC.C#=C.C#
ANDC.T#=T.T#ANDTNAME=’LIU’
4)检索选修课程号为C2或C4课程的学生学号。
SELECTS#
FROMSC
WHEREC#=’C2’ORC#=’C4’
5)检索至少选修课程号为C2和C4课程的学生学号。
SELECTX.S#
FROMSCASX,SCASY
WHEREX.S#=Y.S#ANDX.C#=’C2’ANDY.C#=’C4’
6)检索不学C2课程的学生姓名与年龄。
SELECTSNAME,AGE
FROMS
WHERES#NOTIN(SELECTS#
FROMSC
WHEREC#=’C2’)
7)检索学习全部课程的学生姓名。
(在表S中找学生,在C中不存在一门课程,这个学生没有学。
)
SELECTSNAME
FROMS
WHERENOTEXISTS
(SELECT*
FROMC
WHERENOTEXISTS
(SELECT*
FROMSC
WHERESC.S#=S.S#ANDSC.C#=C.C#))
8)检索所学课程包含学号为S3学生所学课程的学生学号。
(在SC表中找一个学生,不存在S3学的一门课,该学生没有学。
)
SELECTDISTINCTS#
FROMSCASX
WHERENOTEXISTS
(SELECT*
FROMSCASY
WHEREY.S#=’S3’
ANDNOTEXISTS
(SELECT*
FROMSCASZ
WHEREZ.S#=X.S#ANDZ.C#=Y.C#))
注:
DISTINCT表示去掉重复值。
3.聚合函数
COUNT(*):
计算元组个数
COUNT(<列名>)对一列中的值计算个数
SUM(<列名>)求某一列值的总和(此列值为数值型)
AVG(<列名>)求某一列值的平均值(此列值为数值型)
MAX(<列名>)求某一列值的最大值
MIN(<列名>)求某一列值的最小值
说明:
列名前均可加DISTINCT,表示去掉重复值。
例1:
求男生的总人数和平均年龄。
SELECTCOUNT(*),AVG(AGE)
FROMS
WHERESEX=‘男’
例2:
统计选修了课程的学生人数。
SELECTCOUNT(DISTINCTS#)
FROMSC
4.SELECT语句完整的句法
SELECT<目标表的列名或列表达式序列>
FROM<基本表名和(或)视图序列>
[WHERE<行条件表达式>]
[GROUPBY<列名序列>
[HAVING<组条件表达式>]]
[ORDERBY<列名[ASC|DESC]>,…]
说明:
[]表示其中的内容根据需要可以省略。
|表示二选一。
各子句的作用:
SELECT子句用于指定输出的内容;FROM子句用于指定要检索的数据的来源表;WHERE子句称为“行条件子句”,用于指定对元组的选取条件;GROUPBY子句称为“分组子句”,作用是指定对元组进行分类后再检索;HAVING子句称为“组条件子句”,用于指定对分类后的元组的选取条件;ORDERBY子句称为“排序子句”,作用是对检索到的元组进行排序。
整个语句的执行过程为:
1)读取FROM子句中基本表、视图的数据,执行笛卡儿积操作。
2)选取满足WHERE子句中给出的条件表达式的元组。
3)按GROUP子句中指定列的值分组,同时提取满足HAVING子句中组条件表达式的那些组。
4)按SELECT子句中给出的列名或列表达式求值输出。
5)ORDER子句对输出的目标表进行排序,按附加说明ASC升序排列,或按DESC降序排列。
例1:
统计每门课程的学生选修人数。
SELECTCOUNT(S#)
FROMSC
GROUPBYC#
例2:
统计每门课程的学生选修人数,要求只查询出选修人数超过50人的结果。
SELECTCOUNT(S#)
FROMSC
GROUPBYC#
HAVINGCOUNT(S#)>50
例3:
检索每名学生的学号和年龄,查询结果按年龄降序排列,年龄相同按学号升序排列。
SELECTS#,AGE>
FROMS
ORDERBYAGEDESC,S#ASC
5.SELECT子句的具体规定
SELECT[ALL|DISTINCT]〈列名或列表达式序列>|*
说明:
1)DISTINCT选项保证重复的行将从结构中去除;而ALL选项是默认的,将保证重复的行留在结果中,一般可不必写出。
2)*是对于在FROM子句中命名表的所有列的简写。
3)列表达式中允许出现+、-、*、/等运算符以及列名和常数、聚合函数等。
例1:
检索男同学选修的课程的课程号。
SELECTDISTINCTC#
FROMS,SC
WHERES.S#=SC.S#ANDSEX=’男’
例2:
检索所有的学生信息。
SELECT*
FROMS
例3:
检索每个学生的学号和出生年份。
SELECTS#,2008-AGE
FROMS
6.列和基本表的改名操作
有时,一个基本表在SELECT语句中多次出现,即这个表被多次调用,为区别不同的引用,应给每次的引用加上不同的名字。
当然,除此情况之外,也可以给任何一个表起别名。
但要注意:
一旦给表起了别名,在该语句中,凡是引用该表名时,必须用别名而不能用原表名。
有时,用户也可以要求输出的列名与基本表中列名不一致,可在SELECT子句用“旧名AS新名”形式改名。
其中AS可以省略。
例:
检索每个学生的姓名和出生年份,输出的列名分别为STUDENT_NAME和BIRTH_YEAR。
SELECTS#ASSTUDENT_NAME,2008-AGEASBIRTH_YEAR
FROMS
7.条件表达式中的比较操作
1)算术比较操作:
常用运算符有〈、〈=、〉、〉=、=、<>或!
=
[NOT]BETWEEN…AND用来查找(不)在给定范围内的数据,下限在前,上限在后,包括上、下限。
例:
检索18—20岁的学生姓名。
SELECTSNAME
FROMS
WHEREAGE〉=18ANDAGE〈=20
或:
SELECTSNAME
FROMS
WHEREAGEBETWEEN18AND20
2)字符串的匹配操作
语法:
<字符串>[NOT]LIKE<匹配模式>
说明:
匹配模式中常使用两个通配符:
%:
与零个或多个字符组成的字符串匹配。
_:
与单个字符匹配。
例:
检索姓名以字符D打头的学生姓名。
SELECTSNAME
FROMS
WHERESNAMELIKE‘D%’
转义字符:
为使字符串中包含特殊字符(即%和_),允许定义转义字符。
转义字符紧靠特殊字符并放在它前面,表示该特殊字符被当成普通字符。
使用ESCAPE定义转义字符。
例:
LIKE‘ab\%cd%’ESCAPE‘\’匹配所有以ab%cd开头的字符串。
LIKE‘ab\\cd%’ESCAPE‘\’匹配所有以ab\cd开头的字符串。
3)空值的比较操作
使用IS[NOT]NULL来比较。
例:
检索年龄为空值的学生姓名。
SELECTSNAME
FROMS
WHEREAGEISNULL
4)集合成员资格的比较
语法:
[NOT]IN(<集合>)
例1:
检索年龄分别是18、20、21岁的学生姓名。
SELECTSNAME
FROMS
WHEREAGEIN(18,20,21)
例2:
检索男同学选修课程的课程号。
SELECTDISTINCTC#
FROMSC
WHERES#IN(SELECTS#FROMSWHERESEX=‘男’)
5)集合成员的算术比较
语法:
元组θALL|SOME|ANY(〈集合〉)
说明:
ALL表示左边那个元组与右边集合中每一个元组满足θ运算;
ANY和SOME含义相同,表示左边那个元组与右边集合中至少一个元组满足θ运算。
例1:
检索学习C2课程的学生学号和姓名。
SELECTS#,SNAME
FROMS
WHERES#=SOME(SELECTS#
FROMSC
WHEREC#=‘C2’)
例2:
检索不学C2课程的学生姓名与年龄。
SELECTSNAME,AGE
FROMS
WHERES#〈〉ALL(SELECTS#
FROMSC
WHEREC#=‘C2’)
6)集合空否的测试
使用[NOT]EXISTS。
见前面例子。
四、数据更新
1.数据插入
语法:
1)单元组的插入:
INSERTINTO<基本表名>[(<列名序列>)]VALUES(<元组值>)
2)多元组的插入
INSERTINTO<基本表名>[(<列名序列>)]VALUES(<元组值>),
(<元组值>),…,(<元组值>)
3)查询结果的插入
INSERTINTO<基本表名>[(<列名序列>)]
4)表的插入
INSERTINTO<基本表名1>[(<列名序列>)]TABLE<基本表名2>
说明:
在上述各种插入语句中,如果插入的值在属性个数、顺序与基本表的结构完全一致,那么基本表后的(<列名序列>)可省略,否则必须详细列出。
2.数据删除
语法:
DELETEFROM<基本表名>
[WHERE<条件表达式>]
说明:
如果省略WHERE子句,表示删除基本表中所有元组。
例:
把课程名为MATHS的成绩从基本表SC中删除。
DELETEFROMSC
WHEREC#IN(SELECTC#FROMCWHERECNAME=‘MATHS’)
3.数据修改
语法:
UPDATE<基本表名>
SET<列名>=<值表达式>[,<列名>=<值表达式>…]
|ROW=(<元组>)[WHERE<条件表达式>]
例1:
把C5课程的课程名改为DB。
UPDATEC
SETCNAME=‘DB’
WHEREC#=‘C5’
例2:
把课程号为C5的元组修改为(C5,DB,T3)
UPDATEC
SETROW=(‘C5’,‘DB’,‘T3’)
WHEREC#=‘C5’
五、视图
1.在SQL中,外模式一级数据结构的基本单位是视图。
2.视图的定义:
视图是从若干基本表和(或)其他视图构造出来的表。
这种构造方法采用SELECT语句实现。
在我们创建一个视图时,只是把其视图的定义存放在数据字典中,而不存储视图对应的数据,在用户使用视图时才去求对应的数据。
因此,视图被称为“虚表”。
3.视图的创建
语法:
CREATEVIEW<视图名>(<列表序列>)
AS
4.视图的撤销
语法:
DROPVIEW<视图名>
5.对视图的操作
1)视图的查询操作,与基本表一样。
2)视图的更新操作,一般仅限于“行列子集视图”。
行列子集视图:
如果视图是从单个基本表中只使用选择、投影操作导出的,并且包含了基本表的主键,那么这样的视图称为“行列子集视图”。
六、嵌入式SQL
1.SQL语言有两种使用方式:
一种是在终端交互方式下使用,称为交互式SQL;另一种是在主语言的程序中使用,称为嵌入式SQL。
2.嵌入式SQL的实现,有两种处理方式:
一种是扩充主语言的编译程序,使之能处理SQL语句;另一种是采用预处理方式。
目前多数系统采用后一种方式。
预处理方式是先用预处理程序对源程序进行扫描,识别出SQL语句,并处理成主语言的函数调用形式;然后再用主语言的编译程序编译成目标程序。
3.嵌入式SQL的使用规定:
1)在程序中要区分SQL语句与主语言语句。
2)允许嵌入的SQL语句引用主语言的程序变量(成为共享变量)。
3)通过游标协调SQL的集合处理方式和主语言的单记录处理方式
七、存储过程
1.存储过程是使用SQL语句和流程控制语句编写的模块,存储过程经编译和优化后存储在数据库服务器端的数据库中,使用时调用即可。
2.使用存储过程的优点:
1)提高运行速度;
2)增强了SQL的功能和灵活性。
3)可以降低网络的通信量;
4)减轻了程序编写的工作量;
5)间接实现安全控制功能。
第五章复习题
一、单项选择题
1.已知学生选课表SC(S#,C#,SCORE),其中:
S#代表学生学号,C#代表课程号,SCORE代表成绩。
为了提高查询速度,对该表创建唯一索引,应该创建在哪个(组)属性上?
(A)
A)(S#,C#)B)S#C)C#D)SCORE
2.有如下两个关系,其中雇员信息表关系EMP的主键是雇员号,外键是部门号;部门信息表关系DEPT的主键是部门号。
EMP
雇员号
雇员名
部门号
工资
001
张山
02
2000
010
王宏达
01
1200
056
马林生
02
1000
101
赵敏
04
1500
DEPT
部门号
部门名
地址
01
业务部
1号楼
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 自考 04735 数据库 原理 应用 SQL 语言