数据库.docx
- 文档编号:5485791
- 上传时间:2023-05-08
- 格式:DOCX
- 页数:23
- 大小:23.88KB
数据库.docx
《数据库.docx》由会员分享,可在线阅读,更多相关《数据库.docx(23页珍藏版)》请在冰点文库上搜索。
数据库
第三章关系数据库标准语言SQL
3.1SQL概述
vSQL(StructuredQueryLanguage)
结构化查询语言,是关系数据库的标准语言
vSQL是一个通用的、功能极强的关系数据库语言
SQL标准的进展过程
标准大致页数发布日期
⏹SQL/861986.10
⏹SQL/89(FIPS127-1)120页1989年
⏹SQL/92622页1992年
⏹SQL991700页1999年
⏹SQL20032003年
3.1.2SQL的特点
1.综合统一
⏹集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体。
⏹可以独立完成数据库生命周期中的全部活动:
Ø定义关系模式,插入数据,建立数据库;
Ø对数据库中的数据进行查询和更新;
Ø数据库重构和维护
Ø数据库安全性、完整性控制等
⏹用户数据库投入运行后,可根据需要随时逐步修改模式,不影响数据的运行。
⏹数据操作符统一
2.高度非过程化
v非关系数据模型的数据操纵语言“面向过程”,必须制定存取路径
vSQL只要提出“做什么”,无须了解存取路径。
v存取路径的选择以及SQL的操作过程由系统自动完成。
3.面向集合的操作方式
v非关系数据模型采用面向记录的操作方式,操作对象是一条记录
vSQL采用集合操作方式
Ø操作对象、查找结果可以是元组的集合
Ø一次插入、删除、更新操作的对象可以是元组的集合
4.以同一种语法结构提供多种使用方式
vSQL是独立的语言
能够独立地用于联机交互的使用方式
vSQL又是嵌入式语言
SQL能够嵌入到高级语言(例如C,C++,Java)程序中,供程序员设计程序时使用
5.语言简洁,易学易用
vSQL功能极强,完成核心功能只用了9个动词。
表3.1SQL语言的动词
SQL功能
动词
数据查询
SELECT
数据定义
CREATE,DROP,ALTER
数据操纵
INSERT,UPDATE
DELETE
数据控制
GRANT,REVOKE
SQL的基本概念(续)
SQL支持关系数据库三级模式结构
v基本表
⏹本身独立存在的表
⏹SQL中一个关系就对应一个基本表
⏹一个(或多个)基本表对应一个存储文件
⏹一个表可以带若干索引
v存储文件
⏹逻辑结构组成了关系数据库的内模式
⏹物理结构是任意的,对用户透明
v视图
⏹从一个或几个基本表导出的表
⏹数据库中只存放视图的定义而不存放视图对应的数据
⏹视图是一个虚表
⏹用户可以在视图上再定义视图
3.2学生-课程数据库
v学生表:
Student(Sno,Sname,Ssex,Sage,Sdept)
课程表:
Course(Cno,Cname,Cpno,Ccredit)
学生选课表:
SC(Sno,Cno,Grade)
Student表
学号
Sno
姓名
Sname
性别
Ssex
年龄
Sage
所在系
Sdept
200215121
200215122
200215123
200515125
李勇
刘晨
王敏
张立
男
女
女
男
20
19
18
19
CS
CS
MA
IS
Course表
课程号
Cno
课程名
Cname
先行课
Cpno
学分
Ccredit
1
2
3
4
5
6
7
数据库
数学
信息系统
操作系统
数据结构
数据处理
PASCAL语言
5
1
6
7
6
4
2
4
3
4
2
4
SC表
学号
Sno
课程号
Cno
成绩
Grade
200215121
200215121
200215121
200215122
200215122
1
2
3
2
3
92
85
88
90
80
3.3数据定义
SQL的数据定义功能:
表定义、视图和索引的定义。
表3.2SQL的数据定义语句
操作对象
操作方式
创建
删除
修改
表
CREATETABLE
DROPTABLE
ALTERTABLE
视图
CREATEVIEW
DROPVIEW
索引
CREATEINDEX
DROPINDEX
3.3数据定义
v3.3.1基本表的定义、删除与修改
v3.3.2索引的建立与删除
3.3.1基本表的定义、删除与修改
一、定义基本表
CREATETABLE<表名>
(<列名><数据类型>[<列级完整性约束条件>]
[,<列名><数据类型>[<列级完整性约束条件>]]…
[,<表级完整性约束条件>]);
学生表Student
[例1]建立“学生”表Student,学号是主码,姓名取值唯一。
CREATETABLEStudent
(SnoCHAR(9)PRIMARYKEY,/*列级完整性约束条件*/
SnameCHAR(20)UNIQUE,/*Sname取唯一值*/
SsexCHAR
(2),
SageSMALLINT,
SdeptCHAR(20)
);
课程表Course
[例2]建立一个“课程”表Course。
CREATETABLECourse
(CnoCHAR(4)PRIMARYKEY,
CnameCHAR(40),
CpnoCHAR(4),
CcreditSMALLINT,
FOREIGNKEY(Cpno)REFERENCESCourse(Cno)
);
学生选课表SC
[例3]建立一个“学生选课”表SC。
CREATETABLESC
(SnoCHAR(9),
CnoCHAR(4),
GradeSMALLINT,
PRIMARYKEY(Sno,Cno),
/*主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGNKEY(Sno)REFERENCESStudent(Sno),
/*表级完整性约束条件,Sno是外码,被参照表是Student*/
FOREIGNKEY(Cno)REFERENCESCourse(Cno)
/*表级完整性约束条件,Cno是外码,被参照表是Course*/
);
二、数据类型
定义表的属性时,需要指明其数据类型及长度。
数据类型
含义
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
三、修改基本表
ALTERTABLE<表名>
[ADD<新列名><数据类型>[完整性约束]]
[DROP<完整性约束名>]
[ALTERCOLUMN<列名><数据类型>];
修改基本表(续)
[例4]向Student表增加“入学时间”列,其数据类型为日期型。
ALTERTABLEStudentADDS_entranceDATE;
▪不论基本表中原来是否已有数据,新增加的列一律为空值。
[例5]将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
ALTERTABLEStudentALTERCOLUMNSageINT;
[例6]增加课程名称必须取唯一值的约束条件。
ALTERTABLECourseADDUNIQUE(Cname);
四、删除基本表
DROPTABLE<表名>[RESTRICT|CASCADE];
⏹RESTRICT:
删除表是有限制的。
Ø欲删除的基本表不能被其他表的约束所引用
Ø如果存在依赖该表的对象,则此表不能被删除
⏹CASCADE:
删除该表没有限制。
Ø在删除基本表的同时,相关的依赖对象一起删除
删除基本表(续)
【例7】删除Student表。
DROPTABLEStudentCASCADE;
⏹基本表定义被删除,数据被删除
⏹表上建立的索引、视图、触发器等一般也将被删除
【例8】若表上建有视图,选择RESTRICT时表不能删除。
CREATEVIEWIS_Student
AS
SELECTSno,Sname,Sage
FROMStudent
WHERESdept='IS';
DROPTABLEStudentRESTRICT;
--ERROR:
cannotdroptableStudentbecauseotherobjectsdependonit.
[例9]若选择CASCADE时可以删除表,视图也自动被删除。
DROPTABLEStudentCASCADE;
--NOTICE:
dropcascadestoviewIS_Student
SELECT*FROMIS_Student;
--ERROR:
relation"IS_Student"doesnotexist
3.3数据定义
v3.3.1基本表的定义、删除与修改
v3.3.2索引的建立与删除
3.3.2索引的建立与删除
v建立索引的目的:
加快查询速度
v谁可以建立索引
▪DBA或表的属主(即建立表的人)
▪DBMS一般会自动建立以下列的索引
PRIMARYKEY
UNIQUE
v谁维护索引
DBMS自动完成
v使用索引
DBMS自动选择是否使用索引以及使用哪些索引
一、建立索引
v语句格式
CREATE[UNIQUE][CLUSTER]INDEX<索引名>
ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);
[例10]CREATECLUSTERINDEXStusname
ONStudent(Sname);
▪在Student表的Sname(姓名)列上建立一个聚簇索引
v在最经常查询的列上建立聚簇索引以提高查询效率
v一个基本表上最多只能建立一个聚簇索引
v经常更新的列不宜建立聚簇索引
[例11]为学生-课程数据库中的Student,Course,SC三个表建立索引。
CREATEUNIQUEINDEXStusnoONStudent(Sno);
CREATEUNIQUEINDEXCoucnoONCourse(Cno);
CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);
⏹Student表按学号升序建唯一索引
⏹Course表按课程号升序建唯一索引
⏹SC表按学号升序和课程号降序建唯一索引
二、删除索引
vDROPINDEX<索引名>;
删除索引时,系统会从数据字典中删去有关该索引的描述
[例12]删除Student表的Stusname索引。
DROPINDEXStusname;
数据查询
v语句格式
SELECT[ALL|DISTINCT]<目标列表达式>
[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>[ASC|DESC]];
3.4.1单表查询
v查询仅涉及一个表:
⏹一、选择表中的若干列
⏹二、选择表中的若干元组
⏹三、ORDERBY子句
⏹四、聚集函数
一、选择表中的若干列
v查询指定列
[例1]查询全体学生的学号与姓名。
SELECTSno,Sname
FROMStudent;
[例2]查询全体学生的姓名、学号、所在系。
SELECTSname,Sno,Sdept
FROMStudent;
2.查询全部列
v选出所有属性列:
⏹在SELECT关键字后面列出所有列名
⏹将<目标列表达式>指定为*
[例3]查询全体学生的详细记录。
SELECTSno,Sname,Ssex,Sage,Sdept
FROMStudent;
或
SELECT*
FROMStudent;
3.查询经过计算的值
vSELECT子句的<目标列表达式>可以为:
▪算术表达式
▪字符串常量
▪函数
▪列别名
[例4]查全体学生的姓名及其出生年份。
SELECTSname,2004-Sage/*假定当年的年份为2004年*/
FROMStudent;
输出结果:
Sname2004-Sage
李勇1984
刘晨1985
王敏1986
张立1985
[例5]查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,‘YearofBirth:
',2004-Sage,
ISLOWER(Sdept)
FROMStudent;
输出结果:
Sname'YearofBirth:
'2004-SageISLOWER(Sdept)
李勇YearofBirth:
1984cs
刘晨YearofBirth:
1985is
王敏YearofBirth:
1986ma
张立YearofBirth:
1985is
3.4.1单表查询
v查询仅涉及一个表:
⏹一、选择表中的若干列
⏹二、选择表中的若干元组
⏹三、ORDERBY子句
⏹四、聚集函数
二、选择表中的若干元组
v1.消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL。
[例6]查询选修了课程的学生学号。
SELECTSnoFROMSC;
等价于:
SELECTALLSnoFROMSC;
执行上面的SELECT语句后,结果为:
Sno
200215121
200215121
200215121
200215122
200215122
消除取值重复的行(续)
v指定DISTINCT关键词,去掉表中重复的行。
SELECTDISTINCTSno
FROMSC;
执行结果:
Sno
200215121
200215122
2.查询满足条件的元组
表3.4常用的查询条件
查询条件
谓词
比较
=,>,<,>=,<=,!
=,<>,!
>,!
<;NOT+上述比较运算符
确定范围
BETWEENAND,NOTBETWEENAND
确定集合
IN,NOTIN
字符匹配
LIKE,NOTLIKE
空值
ISNULL,ISNOTNULL
多重条件(逻辑运算)
AND,OR,NOT
(1)比较大小
【例7】查询计算机科学系全体学生的名单。
SELECTSname
FROMStudent
WHERESdept=‘CS’;
【例8】查询所有年龄在20岁以下的学生姓名及其年龄。
SELECTSname,Sage
FROMStudent
WHERESage<20;
【例9】查询考试成绩有不及格的学生的学号。
SELECTDISTINCTSno
FROMSC
WHEREGrade<60;
(2)确定范围
v谓词:
BETWEEN…AND…
NOTBETWEEN…AND…
【例10】查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,Sage
FROMStudent
WHERESageBETWEEN20AND23;
【例11】查询年龄不在20~23岁之间的学生姓名、系别和年龄。
SELECTSname,Sdept,Sage
FROMStudent
WHERESageNOTBETWEEN20AND23;
(3)确定集合
v谓词:
IN<值表>,NOTIN<值表>
[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptIN('IS','MA','CS');
[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptNOTIN('IS','MA','CS');
(4)字符匹配
v谓词:
[NOT]LIKE‘<匹配串>’
v匹配串为固定字符串。
[例14]查询学号为200215121的学生的详细情况。
SELECT*
FROMStudent
WHERESnoLIKE‘200215121';
等价于:
SELECT*
FROMStudent
WHERESno='200215121';
2)匹配串为含通配符的字符串。
[例15]查询所有姓刘学生的姓名、学号和性别。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameLIKE‘刘%’;
[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECTSname
FROMStudent
WHERESnameLIKE'欧阳_';
[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECTSname,Sno
FROMStudent
WHERESnameLIKE‘_阳%’;
[例18]查询所有不姓刘的学生姓名。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameNOTLIKE'刘%';
(5)涉及空值的查询
⏹谓词:
ISNULL或ISNOTNULL
⏹“IS”不能用“=”代替
[例19]某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
查询缺少成绩的学生的学号和相应的课程号。
SELECTSno,Cno
FROMSC
WHEREGradeISNULL
[例20]查所有有成绩的学生学号和课程号。
SELECTSno,Cno
FROMSC
WHEREGradeISNOTNULL;
(6)多重条件查询
v逻辑运算符:
AND和OR来联结多个查询条件
•AND的优先级高于OR
•可以用括号改变优先级
v可用来实现多种其他谓词
•[NOT]IN
•[NOT]BETWEEN…AND…
[例21]查询计算机系年龄在20岁以下的学生姓名。
SELECTSname
FROMStudent
WHERESdept='CS'ANDSage<20;
v改写[例12]
[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptIN('IS','MA','CS')
可改写为:
SELECTSname,Ssex
FROMStudent
WHERESdept='IS'ORSdept='MA'ORSdept='CS';
3.4.1单表查询
v查询仅涉及一个表:
⏹一、选择表中的若干列
⏹二、选择表中的若干元组
⏹三、ORDERBY子句
⏹四、聚集函数
三、ORDERBY子句
vORDERBY子句
⏹可以按一个或多个属性列排序
⏹升序:
ASC;降序:
DESC;缺省值为升序
v当排序列含空值时
⏹ASC:
排序列为空值的元组最后显示
⏹DESC:
排序列为空值的元组最先显示
[例22]查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECTSno,Grade
FROMSC
WHERECno='3'
ORDERBYGradeDESC;
[例23]查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*
FROMStudent
ORDERBYSdept,SageDESC;
3.4.1单表查询
v查询仅涉及一个表:
⏹一、选择表中的若干列
⏹二、选择表中的若干元组
⏹三、ORDERBY子句
⏹四、聚集函数
四、聚集函数
v聚集函数:
▪计数
COUNT([DISTINCT|ALL]*)
COUNT([DISTINCT|ALL]<列名>)
▪计算总和
SUM([DISTINCT|ALL]<列名>)
▪计算平均值
AVG([DISTINCT|ALL]<列名>)
▪最大最小值
MAX([DISTINCT|ALL]<列名>)
MIN([DISTINCT|ALL]<列名>)
[例24]查询学生总人数。
SELECTCOUNT(*)
FROMStudent;
[例25]查询选修了课程的学生人数。
SELECTCOUNT(DISTINCTSno)
FROMSC;
[例26]计算1号课程的学生平均成绩。
SELECTAVG(Grade)
FROMSC
WHERECno='1';
[例27]查询选修1号课程的学生最高分数。
SELECTMAX(Grade)
FROMSC
WHERCno=‘1’;
3.4.2连接查询
v连接查询:
同时涉及多个表的查询。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库