实验五 高级查询.docx
- 文档编号:8822565
- 上传时间:2023-05-15
- 格式:DOCX
- 页数:26
- 大小:61.82KB
实验五 高级查询.docx
《实验五 高级查询.docx》由会员分享,可在线阅读,更多相关《实验五 高级查询.docx(26页珍藏版)》请在冰点文库上搜索。
实验五高级查询
替换查询结果中的数据
CASE
WHEN条件1THEN表达式1
WHEN条件2THEN表达式2
.
ELSE表达式n
END
【例】查询score表sno,sname,degree列,对degree列按以下规则进行转换;若degree为90~100,替换为“优秀”,若degree为80~89,替换为“良好”,若degree在70~79之间,替换为“中等”,若degree为60~69之间,替换为“及格”,若degree为0~59之间,替换为“不及格”,列标题更改为“evaluation”。
SELECTsno,cno,evaluation=
CASE
WHENdegree>=90ANDdegree<=100THEN'优秀'
WHENdegree>=80anddegree<=89THEN'良好'
WHENdegree>=70anddegree<=79THEN'中等'
WHENdegree>=60anddegree<=69THEN'及格'
ELSE'不及格'
END
FROMscore
查询经过计算的值
[例]查全体学生的姓名及其年龄。
SELECTsname,'age:
',2010-DATEPART(yy,sbirthday)
FROMstudent
消除结果集中的重复行(ALL与DISTINCT)
[例]查询选修了课程的学生学号。
(1)SELECTsno
FROMscore
或(默认ALL)
SELECTALLsno
FROMscore
(2)SELECTDISTINCTsno
FROMscore
限制结果集的返回行数(TOPn[PERCENT])
[例]
SELECTTOP4*FROMscore
SELECTTOP40PERCENT*FROMscore
WHERE子句常用的查询条件
[例]查询所有在’1975-01-01’后出生的学生学号
及其姓名。
SELECTsno,sname
FROMstudent
WHEREsbirthday>’1975-01-01’或
SELECTsno,sname
FROMstudent
WHERENOTsbirthday<='1975-01-01'
[例]查询成绩在60~80(包括60分和80分)之间的所有记录。
SELECT*FROMscore
WHEREdegreeBETWEEN60AND80
[例]查询成绩不在60~80之间的所有记录。
SELECT*FROMscore
WHEREdegreeNOTBETWEEN60AND80
[例]查询成绩为85、86或88的记录。
SELECT*
FROMscore
WHEREdegreeIN(85,86,88)
[例]查询成绩既不是85、86,也不是88的记录。
SELECT*
FROMscore
WHEREdegreeNOTIN(85,86,88)
[例]查询学号为101的学生的详细情况。
SELECT*
FROMstudent
WHEREsnoLIKE‘101'
等价于:
SELECT*
FROMstudent
WHEREsno=‘101'
字符串匹配(通配符)
◆%(百分号)代表任意长度(长度可以为0)的字符串
⏹例:
a%b表示以a开头,以b结尾的任意长度的字符串。
如acb,addgb,ab等都满足该匹配串
◆_(下划线)代表任意单个字符
⏹例:
a_b表示以a开头,以b结尾的长度为3的任意字符串。
如acb,afb等都满足该匹配串
[例]查询所有姓王学生的姓名、学号和性别。
SELECTsname,sno,ssex
FROMstudent
WHEREsnameLIKE'王%'
[例]查询姓“李”且全名为二个汉字的学生的姓名。
SELECTsname
FROMstudent
WHEREsnameLIKE'李_'
[例]查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECTsname,sno
FROMstudent
WHEREsnameLIKE'_阳%'
[例]查询所有不姓李的学生姓名。
SELECTsname
FROMstudent
WHEREsnameNOTLIKE‘李%'
ESCAPE短语
当用户要查询的字符串本身就含有%或_时,要使用ESCAPE'<换码字符>'短语对通配符进行转义。
[例]查询课程名为DB_Design课程的课程号和任课教师。
SELECTcno,tno
FROMcourse
WHEREcnameLIKE'DB\_Design'
ESCAPE'\'
[例]查询以"DB_"开头,且倒数第3个字符为i的课程的详细情况。
SELECT*
FROMcourse
WHEREcnameLIKE'DB\_%i__'ESCAPE'\'
注:
若匹配串中本身含有’\’,则用’?
’做换码符
涉及空值的查询
[例]某些学生选修课程后没有参加考试。
查询缺少成绩的学生的学号和相应的课程号。
SELECTsno,Cno
FROMscore
WHEREdegreeISNULL
[例]查所有有成绩的学生学号和课程号。
SELECTsno,cno
FROMscore
WHEREdegreeISNOTNULL
使用HAVING短语筛选最终输出结果
只有满足HAVING短语指定条件的组才输出
HAVING短语与WHERE子句的区别:
作用对象不同
WHERE子句作用于基表或视图,从中选择满足条件的元组。
HAVING短语作用于组,从中选择满足条件的组
[例]查询选修了2门或2门以上课程的学生学号。
SELECTsno
FROMscore
GROUPBYsno
HAVINGCOUNT(*)>=2
[例]查询有2门或2门以上课程都至少为85分的学生的学号及其课程数。
SELECTsno,COUNT(*)
FROMscore
WHEREdegree>=85
GROUPBYsno
HAVINGCOUNT(*)>=2
连接查询
表的连接方法
方法1:
表之间满足一定条件的行进行连接,此时FROM子句中指明进行连接的表名,WHERE子句指明连接的列名及其连接条件。
方法2:
利用关键字JOIN进行连接
当将JOIN关键词放于FROM子句中时,应有关键词ON与之相对应,以表明连接的条件。
CROSSJOIN:
将一个表的每条记录和另一表的每条记录拼接成新的记录行。
交叉连接:
又称为非限制连接,不带连接谓词的连接,它将两个表不加任何约束地组合在一起,也就是将第一个表的每条记录分别与第二个表的每条记录组成新记录
例:
方法1:
SELECTstudent.*,score.*
FROMstudent,score
方法2:
SELECTstudent.*,score.*
FROMstudentCROSSJOINscore
INNERJOIN:
显示符合条件的记录。
等值与非等值连接查询(内连接:
INNERJOIN):
[例35]查询每个学生及其选修课程的情况。
方法1:
SELECTstudent.*,score.*FROMstudent,score
WHEREstudent.sno=score.sno
方法2:
SELECTstudent.*,score.*FROMstudentINNERJOINscore
ONstudent.sno=score.sno
注:
本例中SELECT子句和WHERE子句中的列名前都加上了表名前缀,是因为两个表中的列名sno相同,必须用表名前缀来确切说明所指列属于哪个表,以避免二义性。
如果列名在参加连接的各表中是唯一的,就不必加前缀。
⏹student.sno=score.sno为连接条件,sno为连接字段。
连接条件的一般格式为:
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
⏹其中,比较运算符主要有:
=、>、<、>=、<=、!
=。
⏹当比较运算符为“=“时,称为等值连接,其他情况为非等值连接。
LEFT(OUTER)JOIN:
显示符合条件的数据行以及左边表中不符合条件的数据行,此时右边数据行会以NULL来显示,称为左外连接。
RIGHT(OUTER)JOIN:
显示符合条件的数据行以及右边表中不符合条件的数据行,此时左边数据行会以NULL来显示,称为右外连接。
FULL(OUTER)JOIN:
显示符合条件的数据行以及左边表和右边表中不符合条件的数据行,此时缺乏数据的数据行会以NULL来显示。
嵌套查询
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句的条件中的查询称为嵌套查询,此查询块称为子查询或内层查询,包含子查询的查询块称为父查询或外层查询。
子查询不能使用ORDERBY子句
例:
SELECTsno,sname/*外层查询/父查询*/
FROMStudent
WHEREsnoIN
(SELECTsno/*内层查询/子查询*/
FROMscore
WHEREcno='3-105')
(1)带有IN谓词的子查询
[例]查询与“王芳”在同一个班学习的学生。
此查询要求可以分步来完成:
SELECT*
FROMstudent
WHEREclassIN
(SELECTclass
FROMstudent
WHEREsname='王芳')
父查询和子查询中的表均可以定义别名
SELECT*
FROMstudentS1
WHERES1.classIN
(SELECTclass
FROMstudentS2
WHERES2.sname='王芳')
(2)带有ANY或ALL谓词的子查询
[例]查询其他班中比95031任意一个(其中某一个)学生后出生的学生姓名和出生日期
SELECTsname,sbirthday
FROMstudent
WHEREsbirthday>ANY(SELECTsbirthday
FROMstudent
WHEREclass='95031')
ANDclass<>'95031'/*注意这是父查询块中的条件*/
用集函数实现[例42]
SELECTsname,sbirthday
FROMstudent
WHEREsbirthday>(SELECTMIN(sbirthday)
FROMstudent
WHEREclass='95031')
ANDclass<>'95031'
(3)带有EXISTS谓词的子查询
[例]查询所有选修了3-245号课程的学生姓名。
用嵌套查询
SELECTsname
FROMstudent
WHEREEXISTS
(SELECT*
FROMscore/*相关子查询*/
WHEREsno=student.snoAND
cno='3-245')
用连接运算
SELECTsname
FROMstudent,score
WHEREstudent.sno=score.snoAND
o='3-245'
[例]查询没有选修3-245号课程的学生姓名。
SELECTsname
FROMstudent
WHERENOTEXISTS
(SELECT*
FROMscore
WHEREsno=student.sno
ANDcno='3-245')
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换
例:
查询与“王芳”在同一个班学习的学生。
可以用带EXISTS谓词的子查询替换:
SELECT*
FROMstudentS1
WHEREEXISTS
(SELECT*FROMstudentS2
WHERES2.class=S1.classAND
S2.sname='王芳')
COMPUTE子句
与集函数功能类似,对查询结果集中的所有记录进行汇总统计。
与集函数的区别是COMPUTE子句不仅要显示汇总数据,还要显示参加汇总的记录的详细信息,而集合函数仅显示汇总数据。
COMPUTEBY子句与GROUPBY子句的功能类似,对查询结果集进行分组统计。
与GROUPBY子句的区别是COMPUTEBY子句不仅要显示汇总数据,还要分组显示参加汇总的记录的详细信息,而GROUPBY仅显示汇总数据。
交互式SQL与嵌入式SQL
交互式SQL(interactiveSQL,ISQL)
联机终端用户在交互环境下独立使用的自含式语言,一种功能强大的非过程化查询语言。
嵌入式SQL(SembeddedSQL,ESQL)
将SQL作为一种数据子语言嵌入某些主语言(高级程序设计语言)中。
接受SQL嵌入的语言称为宿主语言,如C、C++、COBOL、FORTRAN等。
DBMS处理ESQL的方法:
将SQL语句进行预编译,DBMS预处理程序扫描源程序将其中SQL语句转换为主语言调用语句,由主语言编译程序将整个源程序编译成目标代码。
主语言的语句和SQL语句的识别:
对嵌入到主语言中的SQL语句加标识符。
如:
EXECSQL
EXECSQL
#SQL|
主语言变量和SQL变量的协调使用:
SQL可以使用主语言变量输入和输出数据;而主程序内一般不出现SQL变量。
共享变量:
SQL使用主语言定义的变量称为共享变量或主变量。
输入变量:
应用程序赋值,SQL引用
输出变量:
SQL语句赋值或设置状态信息,返回给应用程序。
指示变量(indicatorvariable):
由共享变量附带的变量,“指示”共享变量的值或条件。
指示变量标识为SQL_STATE,具有5个字符,由数字或大写英文字母组成。
(前两位状态码表示类别,后三位表示子类别。
)
00表示“success”
01表示“successwithinfo”或“warning”
02表示“nodata”
如:
“02000”表示未找到需要的元组
DBMS将描述系统当前状态和运行状况的状态码赋予指示变量SQL_STATE
应用程序根据SQL_STATE的值转向不同分支,实现程序流向控制。
SQL语句中的共享变量和指示变量名称前必须加“:
”。
【例】在C语言中,由关系表student中查询sno=101的学生姓名和出生日期。
EXECSQLSELECTsname,sbirthday
INTO:
sname,:
sbirthday
FROMstudent
WHEREsno=‘101’
注:
INTO表示将查询到的数据值输入到相应共享变量中。
【例】在表T中插入一行记录,各个属性值已由主变量给出
EXECSQLINSERTINTOT(la,lb,lc,ld)
VALUES(:
g1,:
g2,:
lc,:
ld);
【例】删除某个学生的成绩,学生姓名已由主变量给出
EXECSQLDELETEFROMscore
WHEREsno=(SELECTsno
FROMstudent
WHEREsname=:
sname);
游标
在嵌入方式下,SQL变量为集合型,主变量为标量型,SQL变量不能直接提供给主变量使用。
在嵌入式SQL中引入“游标”(cursor),协调和处理主语言和SQL两种不同的数据处理方式
游标本质上是系统为用户开设的一个数据缓冲区,存放SQL语句的结果数据集。
每个游标区都有一个名字。
用户可通过游标逐一读取数据记录,赋值给主变量交由主语言程序处理。
游标语句
(1)游标定义
SQL中使用DECLARECURSOR语句定义游标
语法格式:
EXECSQLDECLARE<游标名>[游标定义参数]CURSOR
FOR<映像语句>
[FOR{READONLY|UPDATE[OF列名[,…n]]}];
注:
映像语句得到的记录集合成为“游标区”的数据记录集。
“<游标名>”之后可增加一些游标定义参数
1)INSENSITIVE(insensitive):
创建作为游标(数据集)的数据临时副本
2)游标指针提取数据记录的位置参数,包括:
FIRST:
提取第一行
LAST:
提取最后一行
PRIOR:
提取前一行
NEXT:
提取后一行(当前行操作完之后的默认提取位置)
RELATIVE:
按相对位置提取
ABSOLUTE:
按绝对位置提取
3)游标类型-SQL2005支持如下四种游标类型:
STATIC:
静态游标。
游标的数据集不反应更新操作结果。
DYNAMIC:
动态游标。
游标的数据集跟随更新操作而改变。
FAST_FORWARD:
只进游标。
只支持游标指针从头至尾顺序提取游标数据集记录。
KEYSET:
键集驱动游标。
“READONLY”与“UPDATE”关键词分别表示游标数据记录集是只读还是可更新的属性。
(2)打开游标(EXECSQLOPEN<游标名>;)
游标定义后需打开游标,将定义中的查询结果集取到游标区。
打开的游标处于活动状态,游标指针指向集合的第一条记录。
(3)游标推进(EXECSQLFETCH[位置参数]<游标名>INTO<变量名列表>;)
读出当前记录(赋予主变量),并将游标指针推向指定的(默认为下一条)数据记录。
(4)关闭游标(EXECSQLCLOSE<游标名>;)
游标使用完之后需要关闭,目的是释放游标所占的内存缓冲区及其它资源
注:
T-SQL关闭游标后可再打开,不释放游标区,游标可重用。
T-SQL提供释放游标语句释放游标区:
EXECSQLDEALLOCATE<游标名>;
@@fetch_status
是MSSQL的一个全局变量,integer类型,其值有以下三种,分别表示三种不同含义:
0FETCH语句成功
-1FETCH语句失败或记录不在结果集中
-2被提取的记录不存在
@@fetch_status值的改变是通过 “FETCHNEXTFROMCursor”语句实现的
动态SQL
静态SQL语句:
嵌入式SQL语句中的主变量个数与类型在预处理时都已确定,只有值在程序运行中动态输入
动态SQL实质是:
在程序运行当中根据需要动态制定相应SQL语句。
方法:
不直接嵌入主语言,而是在在程序中设置一个字符串变量。
在程序运行过程中,通过交互输入或者从某文件中读取的方式接收SQL语句,存储到字符串变量中。
字符串变量接收到的SQL语句不可带前缀EXECSQL或DECLARE、OPEN、FETCH、CLOSE、WHENEVER、INCLUDE、PREPARE等关键词。
动态SQL实现语句
实际应用中,通过动态SQL预备语句和动态SQL执行语句实现动态SQL机制。
1、动态SQL预备语句
语句格式:
EXECSQLPREPARE<动态SQL语句名>
FROM<主变量或字符串>;
其中,主变量或字符串的值是一个完整的SQL语句,在程序运行中由用户输入,在这里不执行。
2、动态SQL执行语句
语句格式:
EXECSQLEXECUTE<动态SQL语句名>;
3、字符串接收的SQL语句只执行一次的情况
组合上述预备与执行语句:
EXECSQLEXECUTEIMMEDIATE<主变量或字符串>;
4、预备语句中主变量接收的SQL语句条件值缺少时的处理
在执行语句中使用USING短语填充:
EXECSQLEXECUTE<动态SQL语句名>
USING<主变量>;
【例】动态SQL的C语言程序段
EXECSQLBEGINDECLARESECTION;
char*query;
EXECSQLENDDECLARESECTION;
scanf(“%s”,query);/*由键盘输入一个SQL语句*/
EXECSQLPREPAREqueFROM:
query;
EXECSQLEXECUTEque;
说明:
上述程序段表示由用户从键盘输入一个SQL语句,送入字符串(主变量)query中,并通过动态SQL语句que来执行。
若用户输入的SQL语句只执行一次,则上述程序段后两句合并为:
EXECSQLEXECUTEIMMEDIATE:
query
事物与事务管理
事物(transaction)
是DBMS的执行单位。
事物本身就是构成单一逻辑工作单元的数据库操作的有限序列。
一条、一组SQL语句或者一个程序都可作为一个事物。
构成事物的序列中的操作,要么全做,要么不做,序列不可再分割。
事物可撤销执行。
每个事物可看做是数据库的一个状态,形成数据库某种一致性。
事物的性质
1、原子性(atomicity)
指事物对于数据库操作而言是一个不可分割的整体。
2、一致性(consistency)
指事物执行结果应使数据库有一种以执行达到一种新的一致性,保证数据库的完整性。
3、隔离性(isolation)
多个事物并发执行时,不必关心其他事物的执行。
隔离性是事物并发控制的基础。
4、持久性(durability)
指事物对数据库的更新应永久地反映在数据库中。
持久性保证数据库具有可恢复性。
事物操作与状态
事物基本操作:
事物开始(starting):
事物开始执行。
事务执行(readandwrite):
事物进行数据的读/写操作。
事物提交(commit):
事物完成所有数据操作,并保存操作结果。
标志事物成功完成。
事物回滚(rollback):
事物未完成所有数据操作,重新返回事物开始。
标志事物的撤销。
事物状态:
活动状态(active):
表明事物处于运行中。
局部提交状态(partialcommitted):
表明事物读/写语句的最后一条已被执行。
失败状态(failed):
表明事物无法正常运行。
终止状态(abord):
回到事务执行前的初始状态。
提交状态(committed):
事务执行成功,结果写入数据库。
SQL对事物的支持
1、SQL事务处理语句
(1)事物开始
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验五 高级查询 实验 高级 查询