第4章SQL语言.docx
- 文档编号:24780649
- 上传时间:2023-06-01
- 格式:DOCX
- 页数:28
- 大小:36.35KB
第4章SQL语言.docx
《第4章SQL语言.docx》由会员分享,可在线阅读,更多相关《第4章SQL语言.docx(28页珍藏版)》请在冰豆网上搜索。
第4章SQL语言
第4章SQL语言
●教学目的:
SQL语言是RDBS的通用语言,若RDBS不支持SQL,则其不是一种可流行的RDBMS。
通过本章的学习,使学生掌握SQL语言的主要功能。
●教学重点:
SQL的查询
SQL的增、删、改
●教学难点:
嵌套查询
●教学内容:
SQL(StructuredQueryLanguage)是1974年由Boyce和Chamberlin提出的,1975—1979年在IBM公司的SanJoseRsearchLaboratary研制的SystemR上得以实现这种语言。
SQL语言的功能丰富,使用方式灵活,语言简洁易学。
1986.10ANSI(美国国家标准局)批准SQL作为RDB语言的美国标准,此后不久,ISO(国家化标准组织)也做出同样的规定,把SQL标准公布,SQL成为国际标准。
此后,DB产品各个厂家纷纷推出各自的支持SQL的软件或与SQL的接口软件,不支持SQL语言的RDBS已不存在其生命力。
SQL标准不仅成为RDBS的主流语言,在软件工程,人工智能软件开发工具等也很具潜力。
4.1SQL概述
●教学目的:
本节主要讲述SQL的特点和SQL的基本概念,是一个高层面上的论述。
●教学重点:
SQL的特点
●教学难点:
DDLDMLDCL功能于一体的语言。
●教学内容:
SQL是一种介于关系代数和关系演算之间的结构化查询语言,但其功能并非仅是查询。
SQL是一个通用的功能极强的RDB语言。
4.1.1SQL的特点
SQL是集数据查询(dataquery),
操纵(datamanipulation)于一体
定义(datadefinition),
控制(control)
1.综合统一
非关系数据语言一般SDDL(模式描述语言)
SSDDL(子模式描述语言)
DSDL(数据存储描述)
DML(数据操纵描述)
SQL能完成定义关系模式,录入数据,建立数据库,查询,更改,数据库重构,安全控制等一系列操作,
是集DDL、DML、DCL的功能于一体。
定义操纵控制
另外,在关系模型中,由于结构单一(关系),使数据的操纵符具有统一性,查询、增、删、改每种操作只需一种操作符。
2.高级非过程化
SQL只需whattodo?
不需howtodo?
无需了解存取路径,由RDBS自动完成。
3.面向集合的操作方式
操作对象,结果均为关系。
4.两种使用方法,统一的语法结构
①可以作为独立语言,由终端用户的联机交互方式使用(自含式)。
②可以作为子语言,嵌入到宿主语言中使用(嵌入式)。
两种使用方式,SQL语法结构是基本一致的。
5.语言简捷,易学易用
查询:
SELECT
定义:
CREATE、DROP、ALTER
操作:
INSERT、UPDATE、DELETE
控制:
GRANT、REVOKE
4.1.2SQL语言的基本概念
SQL支持RDB的三级模式结构
SQL
视图1视图2外模式
基本表1基本表2基本表3模式
存储文件1存储文件2内模式
1.SQL可以对视图,基本表进行查询等,在用户眼里,两者是一样的。
2.一个关系对应一个基本表,视图是导出的表,是虚表。
3.多个(一个)基本表对应一个存储文件,一个表可带若干索引。
4.2数据定义
●教学目的:
本节让学生掌握如何定义一个基本表,以及如何建立、删除索引。
●教学重点:
1、建立基本表
2、建立索引
●教学内容:
SQL的数据定义功能有三个部分,定义基本表
视图
索引
视图是由基本表导出的,是虚表
索引是依附于基本表的
故,SQL通常不提供修改视图、索引,用户只需把原视图、索引删除,再建立新的即可。
SQL的数据定义语句
建立删除修改
表createtabledroptablealtertable
视图createviewdropview
索引createindexdropindex
在本节介绍基本表,索引,视图后面介绍。
4.2.1定义、删除、修改基本表
1.定义基本表
格式CREATETABLE〈表名〉(〈列名1〉〈数据类型〉[列约束条件][,〈列名2〉〈数据类型〉[列约束条件]…[〈表完整性约束条件〉];
例1:
建立一个S表由SNO、SNAME、SSEX、SAGE、SDEPT组成。
SNO不能为空、取值唯一,SNAME取值唯一。
CERATETABLES(SNOCHAR(3)NOTNULLUNIQUE,SNAMECHAR(20)UNIQUE,SSEXCHAR
(2),SAGEINT,SDEPTCHAR(12));
2.修改基本表
格式ALTERTABLE〈表名〉
增加[ADD〈新列名〉〈数据类型〉[完整性约束条件]]
删[DROP〈完整性约束名〉]
修[MODIFY〈列名〉〈数据类型〉]
例:
向S表增加“SCOME”一列、类型为DATE。
ALTERTABLESADDSCOMEDATE;
将SAGE改为半字长整数。
ALTERTABLESMODIFYSAGESMALLINT;
删除SNAME的唯一取值。
ALTERTABLESDROPUNIQUE(SNAME);
3、删除基本表
DROPTABLE〈表名〉
例:
删除S表
DROPTABLES;
删除后,其结构、数据以及表上的索引,视图,全部删除。
4.2.2建立与删除索引
索引以提供多种存取路径,加快查找速度。
1.建立索引
格式:
CREATE[UNIQUE][CIUSTEP]INDEX〈索引名〉
ON〈表名〉(〈列名〉[〈次序〉][,〈列名〉[〈次序〉]]…);
〈表名〉:
基本表
〈列名〉:
索引的列,可以是多个,之间用‘,’隔开
ASC升DESC降
UNIQUE:
索引的值只取一个,不重复
CLUSTER:
聚簇索引,索引与物理顺序一致,一个表只有一个,一般情况下,不经常更新的列可做聚簇索引。
例:
为S表按SNO建立一个索引。
CREATEUNIQUEINDEXS1ONS(SNO);
2.删除索引
格式:
DROPINDEX〈索引名〉
例:
DROPINDEXS1;
作业
课后习题1、3
4.3SQL的数据查询
●教学目的:
本节是该章的重点,要求学生掌握SQL的核心功能,即查询的使用,使用技巧等。
●教学重点:
SELECT……FORM…….WHERE
中的多表查询,嵌套查询
●教学难点:
SQL的嵌套查询
在关系代数中最常用
ПA1A2…A3(σF(R1×R2×…×RN))
SQL针对这样简单的查询语句
SELECTA1,A2,…An
FORMR1,R2,…Rn
WHEREF
较完整的查询语句的格式:
SELECT[ALL][DISTINCT]<目标列1>[,<目标列2>]…
FORM<表名/视图名>[,<表名/视图名>]
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>][ASC/DESC];
其含义:
从指定表/视图查找满足条件的目标列,有GROUPBY则按指定列分组且满足其条件,有ORDERBY则输出时,按列的升/降序排列。
建立P92的三个表:
S、C、SC
4.3.1单表查询
1、选择表中若干列。
例1:
查全体学生的学号、姓名。
SELECTSNO,SNAME
FORMS
例2:
查全体学生的姓名、学号。
SELECTSNAME,SNO
FORMS
例3:
查全体学生的详细情况。
SELECT*
FORMS
例4:
查全体学生的姓名、出生年。
SELECTSNAME,2004-SAGE
FORMS
2、选择表中若干元组。
例6:
查选修了课程的学生的学号。
SELECTDISTINCTSNO
FORMSC
带条件的查询,其中条件为:
P95
算术比较运算符:
〈,〈=,〉,〈=,=,〈〉
逻辑比较运算符:
ANDORNOT
确定范围:
BETWEENAND、NOTBETWEENAND
集合:
IN、NOTIN
字符串:
LIKE、NOTLIKE
空值:
ISNULL,ISNOTNULL
例7:
查计算机系的学生的姓名。
SELECTSNAME
FORMS
WHERESDEPT=’CS’
例8:
查年龄小于20的学生的姓名和年龄。
SELECTSNAME,SAGE
FORMS
WHERESAGE<20
例9:
查年龄在20—23之间的学生的姓名和年龄。
SELECTSNAME,SAGE
FORMS
WHERESAGEBETWEEN20AND23
例10:
查IS、MA、CS系的学生的姓名。
SELECTSNAME
FORMS
WHERESDEPTIN(’IS’,’MA’,’CS’)
或WHERESDEPT=’IS’ORSDEPT=’MA’ORSDEPT=’CS’
字符匹配:
[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]
含义:
查找指定的属性列值与<匹配串>相匹配的元组。
匹配串中可以包含%和_通配符。
%:
任意长字符。
相当与DOS中的*
_:
任一字符。
相当与DOS中的?
当查找的字符本身就含有%_时,用<换码字符>\,在\后的字符不是通配符。
例11:
查所有姓刘的学生的姓名、学号和性别。
SELECTSNAME,SNO,SSEX
FORMS
WHERESNAMELIKE‘刘%’
例12:
查所有名子中的第二个字是“阳”的学生的姓名、学号。
SELECTSNAME,SNO
FORMS
WHERESNAMELIKE‘__阳%’
例13:
查以“DB_”开头,且倒数第三个字符为I的课程的详细情况。
SELECT*
FORMC
WHERECNAMELIKE‘DB\_%I__’ESCAPE‘\’
例14:
查缺少成绩的学生的学号、课程号。
SELECTSNO,CNO
FORMSC
WHEREGRADEISNULL
例15:
查计算机系、年龄小于20的学生的姓名。
SELECTSNAME
FORMS
WHERESDEPT=’CS’ANDSAGE<20
3、对查询结果排序。
例16:
查选修了“C3”的学生的学号及成绩,结果按成绩的降序排列。
SELECTSNO,GRADE
FORMSC
WHERECNO=’C3’
ORDERBYGRADEDESC
例17:
查全体学生的基本情况,结果按系的升序,同一戏的学生按年龄的降序排列。
SELECT*
FORMS
ORDERBYSDEPT,SAGEDESC
4、使用集函数。
COUNT([DISTINCT/ALL]*)统计元组个数
COUNT([DISTINCT/ALL]<列名>)统计一列中值的个数
SUM([DISTINCT/ALL]<列名>)统计一列中的总和(数值型)
AVG([DISTINCT/ALL]<列名>)统计一列中的平均值(数值)
MAX([DISTINCT/ALL]<列名>)求一列中的最大值
MIN([DISTINCT/ALL]<列名>)求一列中的最小值
例18:
查全体学生的总数。
SELECTCOUNT(*)
FORMS
例19:
查选修了课程的学生的总数。
SELECTCOUNT(DISTINCTSNO)
FORMSC
例20:
计算选修“C1”的学生的平均成绩。
SELECTAVG(GRADE)
FORMSC
WHERECNO=’C1’
例21:
计算选修“C1”的学生的最高成绩。
SELECTMAX(GRADE)
FORMSC
WHERECNO=’C1’
5、对查询结果分组。
例22:
求各个课程号及相应的选课人数。
SELECTCNO,COUNT(SNO)
FORMSC
GROUPBYCNO
例23:
查询选修了3门课以上的学生的学号。
SELECTSNO
FORMSC
GROUPBYSNOHAVINGCOUNT(*)>3
注意:
WHERE、HAVING后面条件的作用的范围不同。
WHERE作用于表,HAVING作用于分组。
4.3.2连接查询
以上的查询都是单表查询,这一节主要讲多表查询。
例24:
查每个学生及选课情况。
SELECT*
FORMS,SC
WHERES.SNO=SC.SNO
结果中会出现两个SNO,为避免可以在SELECT后面直接写S.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADE
例25:
查每门课的间接先行课,即先行课的先行课。
SELECTFIRST.CNO,SECOND.CPNO
FORMCFIRST,CSECOND
WHEREFIRST.CPNO=SECOND.CNO
例26:
查每个学生的学号、姓名、选修课名,成绩。
SELECTS.SNO,SNAME,CNAME,GRADE
FORMS,SC,C
WHERES.SNO=SC.SNOANDSC.CNO=C.CNO
例27:
查每个学生及选课情况。
(如果没有选课,只显示学生的基本情况)
SELECTS.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADE
FORMS,SC
WHERES.SNO=SC.SNO(*)
见P105和P103两个结果的比较。
4.3.3嵌套查询
在SQL中,WHERE、HAVING中可以包含一个SELECT…FROM…WHERE,称为嵌套查询。
例:
查选修‘C2’课程的学生的姓名。
SELECTSNAME
FROMS
WHERESNOIN
(SELECTSNO
FROMSC
WHERECNO=‘C2’)
外层的SELECT称为父查询,内层查询为子查询。
上例为不相关子查询。
即:
内层的查询独立存在,先执行完内查询,再执行外查询。
这也正是SQL的结构化的含义所在。
1、带有IN谓词的子查询。
例28:
查与“刘晨”在一个系的学生的学号、姓名。
SELECTSNO,SNAME
FORMS
WHERESDEPTIN
(SELECTSDEPT
FROMS
WHERESNAME=‘刘晨’)
例29:
查选修了‘数据库’的学生的学号、姓名。
SELECTSNO,SNAME
FORMS
WHERESNOIN
(SELECTSNO
FROMSC
WHERECNOIN
(SELECTCNO
FROMC
WHERRCNAME=‘数据库’))
本例同样可以用连接查询实现。
即有些嵌套是可以和连接查询等价的。
2、带有比较运算的子查询
当用户确切知道子查询的结果只有一个时,可以用=、>、<、>=、<=、<>等比较运算符。
如例28:
查与“刘晨”在一个系的学生的学号、姓名。
SELECTSNO,SNAME
FORMS
WHERESDEPT=
(SELECTSDEPT
FROMS
WHERESNAME=‘刘晨’)
3、带有ANY、ALL谓词的子查询
子查询为单值时,可以用比较运算符,而使用ANY、ALL时必须用比较运算符。
>ANY大于查询结果中的某个值
>ALL大于查询结果中的所有值
>=ANY大于等于查询结果中的某个值 >=ALL大于等于查询结果中的所有值 <=ANY小于等于查询结果中的某个值 <=ALL小于等于查询结果中的所有值 =ANY等于查询结果中的某个值 =ALL等于查询结果中的所有值(通常无意义) <>ANY不等于查询结果中的某个值 <>ALL不等于查询结果中的任一值 例30: 查其他系中比‘IS’某一学生年龄小的学生的姓名、年龄。 SELECTSNAME,SAGE FORMS WHERESAGE FROMS WHERESDEPT=’IS’)ANDSDEPT<>’IS’ 或: SELECTSNAME,SAGE FORMS WHERESAGE<(SELECTMAX(SAGE) FROMS WHERESDEPT=’IS’)ANDSDEPT<>’IS’ 例31: 查其他系中比‘IS’的学生年龄都小的学生的姓名、年龄。 SELECTSNAME,SAGE FORMS WHERESAGE FROMS WHERESDEPT=’IS’)ANDSDEPT<>’IS’ 或: SELECTSNAME,SAGE FORMS WHERESAGE<(SELECTMIN(SAGE) FROMS WHERESDEPT=’IS’)ANDSDEPT<>’IS’ 4、带有EXISTS谓词的子查询 SQL中只有EXISTS谓词,没有∀谓词。 例32: 查选修了‘C2’的学生的学号、姓名。 SELECTSNO,SNAME FORMS,SC WHERES.SNO=SC.SNOANDCNO=’C2’ 或: SELECTSNO,SNAME FORMS WHERESNOIN(SELECTSNO FROMSC WHERECNO=’C2’) 或: SELECTSNO,SNAME FORMS WHEREEXISTS(SELECT* FROMSC WHERESNO=S.SNOANDCNO=’C2’) 后一种情况,称为相关子查询。 即父查询中取一条记录,然后和子查询进行匹配,条件为真,则读出结果,然后再继续下一条…… 例33: 查没选修了‘C2’的学生的学号、姓名。 SELECTSNO,SNAME FORMS,SC不对 WHERES.SNO=SC.SNOANDCNO<>’C2’ 或: SELECTSNO,SNAME FORMS WHERESNONOTIN(SELECTSNO FROMSC WHERECNO=’C2’) 或: SELECTSNO,SNAME FORMS WHERENOTEXISTS(SELECT* FROMSC WHERESNO=S.SNOANDCNO=’C2’) 例34: 查选修全部课程的学生学号。 SELECTSNO FROMS WHERENOTEXISTS (SELECT* FROMC WHERENOTEXISTS (SELECT* FROMSC WHERESNO=S.SNOANDCNO=C.CNO)) 或: SELECTSNO FROMSC GROUPBYSNOHAVINGCOUNT(*)= (SELECTCOUNT(*) FROMC) 例35: 查所有学生都选修的课程的课号。 SELECTCNO FROMC WHERENOTEXISTS (SELECT* FROMS WHERENOTEXISTS (SELECT* FROMSC WHERESNO=S.SNOANDCNO=C.CNO)) 或: SELECTCNO FROMSC GROUPBYCNOHAVINGCOUNT(*)= (SELECTCOUNT(*) FROMS) 例35: 查至少包含‘S1’学生所选修全部课程的学生学号。 SELECTDISTINCTSNO FROMSCX WHERENOTEXISTS (SELECT* FROMSCY WHEREY.SNO=’S1’ANDNOTEXISTS (SELECT* FROMSCZ WHEREZ.SNO=X.SNOANDZ.CNO=Y.CNO)) 例36: 查选‘C2’或‘C4’的学生学号。 SELECTSNO FROMSC WHERECNO=‘C2’ORCNO=‘C4’ 例37: 查选修‘C2’和‘C4’的学生的学号 SELECTX.SNO FROMSCASX,SCASY WHEREX.SNO=Y.SNOAND X.CNO=‘C2’ANDY.CNO=‘C4’ 作业: 课后习题4 4.4SQL的数据更新 ●教学目的: 要求学生掌握SQL的数据的增、删、改的操作语句等。 ●教学重点: INSERTINTO DELETEFROM UPDATESET ●教学内容: 4.4.1插入数据 1、插入单个元组 INSERTINTO<表名>[(<属性列1>,<属性列2>……)]VALUES(<常量1>[,<常量2>…]) 说明: 列名省略时,全省: 为表中的所有列名,在每个列名上均有值。 有列名时: 其他省略的列名上取空。 例1: 插入一学生。 S10,张三,男,19,IS。 INSERTINTOS VALUES(‘S10’,‘张三’,‘男’,19,‘IS’) 例2: 插入一选课记录。 S10,C5。 INSERTINTOSC(SNO,CNO)VALUES(‘S10’,‘C5) 2、插入子查询结果 INSERTINTO<表名>[(<属性列1>,<属性列2>……)]子查询 例3: 对每系,求学生的平均年龄,并把结果存入数据库DEP。 CREATETABLEDEP (SDEPTCHAR(15),AVGSGEINT) INSERTINTODEP(SDEPT,AVGSGE) SELECTSDEPT,AVG(SAGE) FROMS GROUPBYSDEPT 3、多值插入 例4: 插入选课记录。 S10,C5;S10,C6。 INSERTINTOSC(SNO,CNO) VALUES(‘S10’,‘C5),(‘S10’,‘C6’) 4.4.2修改数据 UPDATE<表名> SET<列名>=<表达式>[,<列名>=<表达式>]… [WHERE<条件>] 例5: 将S1的年龄改为22。 UPDATES SETSAGE=22 WHERESNO=‘S1’ 例6: 将所有学生的年龄增加1。 UPDATES SETSAGE=SAGE+1 例7: 将‘CS’系的学生的成绩置0。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 语言