还剩21页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
注本课件授课内容中标注的页码与《教程》一致“习题”来自《教程》,“练习”来自《一本通》第5章结构化查询语言(SQL)SQL概述P86同学们SQL的内容在笔试和上机考试中均占到大约30%的比例,此外它还是查询和视图的基础,因此是学习的重点也是难点在讲解本部分内容时,以《VFP程序设计教程》第5章为主,要求大家课后一定要结合例题解析进行复习虽然SQL对大家来说有一定的难度,但是熟能生巧,大家把书中所涉及的题目和课件中的例题都理解并且操作了,相信SQL这个难关一定能攻克SQL是结构化查询语言StructureQueryLanguage的缩写SQL包含了查询功能、数据定义、数据操纵和数据控制功能,在VFP中没有提供数据控制功能SQL主要特点P861.SQL是一种一体化语言2.SQL是一种高度非过程化的语言3.SQL语言非常简洁4.SQL语言可直接以命令方式交互使用,也可嵌入到程序设计语言中以程序方式使用第四节数据查询功能P94SELECT命令的特点P941.可以自动打开数据库、表文件加以查询,而不需要事先用OPENDATABASE或USE命令打开2.可以直接选取数据表中的数据,而不需要事先用SETRELATION命令建立关联3.当需要的索引文件不存在时,会自动建立暂存索引文件,以支持快速搜索技术(Rushmore)来查询4.其查询结果可输出到文件、表、屏幕或报表上,还可以转换成统计图表命令格式SELECT——FROM——WHEREP94可与LISTFIELDS——FOR——对照学习关系操作投影,选择,联接说明功能强大,语法灵活;要处理的数据表无须事先打开,通过FROM子句指明并打开1.SELECT短语说明要查询的数据;对应的关系操作为投影,类似于FIELDS子句2.FROM短语说明要查询的数据来自哪个或哪些表,可对单个表或多个表进行查询;3.WHERE短语说明查询条件;对应的关系操作为选择,类似于FOR子句如是多表查询还可能过该子句指明联接条件,进行联接4.GROUPBY短语用于对查询结果进行分组,可利用它进行分组汇总;类似于TOTAL命令5.HAVING短语跟随GROUPBY使用,它用来限定分组必须满足的条件;6.ORDERBY短语用于对查询的结果进行排序;类似于SORT命令示例数据库学生管理,包括三个表学生,课程,选课(通过该表反映“学生”与“课程”之间“多对多”的联系)问题请同学们分别指出三个表的主关键字
1、简单查询P95简单查询基于单个表例基本查询
1、列出学生信息SELE*FROM学生“*”号代表所有列类似于USE学生LIST
2、列出学生的学号,姓名条件查询㈠常用运算符P
951、列出男生学生的学号、姓名及生日SELE学号姓名生日FROM学生WHERE性别=”男”类似于USE学生LIST学号姓名生日FOR性别=”男”
2、列出学分大于5的课程的信息SELE*FROM课程WHERE学分5类似于USE学生LISTFOR学分5习题P95例5-11,5-12练习P
9211、13㈡特殊运算符P951.BETWEEN…AND…意为“…和…之间”例查询成绩在80分到90分之间的选课信息SELE*FROM选课WHERE成绩BETWEEN80AND90等价于SELE*FROM选课WHERE成绩=80AND成绩=90例查询成绩不在80分到90分之间的选课信息SELE*FROM选课WHERE成绩NOTBETWEEN80AND902.LIKE是字符串匹配运算符,通配符“%”表示0个或多个字符,“_”表示一个字符注意不是“*”和“?”,不正确!例从学生关系中查询姓“黄”的学生信息SELE*FROM学生WHERE姓名LIKE“黄%”不能写成SELE*FROM学生WHERE姓名=黄%例从学生关系中查询不姓“黄”的学生信息SELE*FROM学生WHERE姓名NOTLIKE“黄%”例SELE*FROM学生WHERE姓名LIKE黄_SELE*FROM学生WHERE姓名LIKE黄__
3、IN相当于集合运算符∈例列出湖南和湖北的学生SELE*FROM学生WHERE籍贯IN“湖南””湖北”练习P
929、10㈢使用空值进行查询空值的概念空值就是缺值或还没有确定值,不能把它理解为任何意义的数据比如表示价格的一个字段值,空值表示没有定价,而数值0可能表示免费空值与空(或空白)字符串、数值0等具有不同的含义例假设在选课中有些学生某门课程还没有考试,则成绩为空试找出尚未考试的选课信息SELE*FROM选课WHERE成绩ISNULL注不能写成“=NULL”例试找出成绩不为空的选课信息SELE*FROM选课WHERE成绩ISNOTNULL
2、联接查询P97联接是关系的基本操作之一,联接查询基于多个关系的查询,有等值联接和非等值联接例查询男生学生的选课信息,包括姓名,学号及成绩分析本例的查询结果包括两个表“学生”与“选课”的属性,适用于联接查询SELE姓名选课.学号成绩;FROM学生选课;WHERE学生.学号=选课.学号AND性别=”男”说明如果命令太长一行写不下可在行末加分号“”表续行选课.学号因为两个表中均有“学号”字段,所以必须指明所属表学生.学号=选课.学号连接条件还可使用如下SQL语句SELE姓名选课.学号成绩;FROM学生JOIN选课ON学生.学号=选课.学号;WHERE性别=”男”说明JOIN……ON建立表与表之间的联接超联接查询P99在两个表的超联接查询中,首先保证一个表中满足条件的元组都在结果中,然后将满足联接条件的元组与另一个表中的元组进行联接,不满足联接条件的则将应来自另一表的属性置为空值语法SELE……FROMTalbelINNER|LEFT|RIGHT|FULLJOINTableONJoinConditionWHERE……说明1.INNERJOIN等价于JOIN,为普通联接2.ONJoinCondition用于指定联接条件其余说明请同学们看书例比较几种联接方式SELE姓名课程号成绩FROM学生JOIN选课ON学生.学号=选课.学号SELE姓名课程号成绩FROM学生LEFTJOIN选课ON学生.学号=选课.学号SELE姓名课程号成绩FROM学生RIGHTJOIN选课ON学生.学号=选课.学号SELE姓名课程号成绩FROM学生FULLJOIN选课ON学生.学号=选课.学号习题:P98例5-145-155-165-
173、嵌套查询P98嵌套查询是另一类基于多个关系的查询,此类查询所要求的结果出自一个关系,但相关的条件却涉及多个关系例查询女生学生选课信息,包括学号,课程号,成绩SELE*;FROM选课;WHERE学号INSELE学号FROM学生WHERE性别=”女”还可使用如下SQL语句简单联接查询SELE选课.学号课程号成绩;FROM选课JOIN学生ON选课.学号=学生.学号;WHERE性别=”女”用量词和谓词的嵌套查询P101前面已学过[NOT]IN运算符,此处还有两种与子查询相关的运算符格式1表达式比较运算符[ANY|ALL|SOME]子查询格式2[NOT]EXISTS子查询说明1.ANY、ALL和SOME为量词,ANY和SOME是同义词,在进行比较运算时只要子查询中有一行能使结果为真,则结果为真;ALL则要求子查询中的所有行都使结果为真时,结果才为真2.EXISTS为谓词,EXISTS和NOTEXISTS是用来检查在子查询中是否有结果返回,也就是存在元组或不存在元组例查询选修有课程的学生的学号,姓名,性别及生日SELE*FROM学生WHEREEXISTSELE*FROM选课WHERE学生.学号=选课.学号注本例中内层查询引用了外层查询的表,只有这样使用谓EXISTS或NOTEXISTS才有意义例查询学分最高的课程信息SELE*FROM课程WHERE学分=ALLSELE学分FROM课程SELE*FROM课程WHERENOT学分ANYSELE学分FROM课程SELE*FROM课程WHERENOT学分SOMESELE学分FROM课程SELE*FROM课程WHERE学分=SELEMAX学分FROM课程例查询学分不是最高的课程信息SELE*FROM课程WHERE学分ANYSELE学分FROM课程SELE*FROM课程WHERE学分SOMESELE学分FROM课程习题P100例5-195-20练习P916/P
94194、排序P99SQLSELECT可将查询结果排序排序的短语格式如下ORDERBYOrder_Item[ASC|DESC][Order_Item[ASC|DESC]…]说明可按升序或降序排序,允许按一列或多列排序例按学分进行升序查询课程信息SELE*FROM课程ORDERBY学分ASC例先按学分进行升序排序,学分相同的再按课程名进行降序排序并输出全部课程信息SELE*FROM课程ORDERBY学分课程名DESC注ORDERBY对最终的查询结果进行排序,不能在子查询中使用此短语习题P102例5-215-22练习P
94205、简单计算查询P101SQL不但具有一般的检索能力,而且还有计算方式的检索用于计算检索的函数有1.COUNT(*)计数2.SUM()求和3.AVG()算平均值4.MAX()求最大值5.MIN()求最小值上面的函数可用在SELECT短语中对查询结果进行计算例查询学号为“03”的学生选修课程的考试成绩总分和平均分SELESUM成绩AVG成绩FROM选课WHERE学号=”03”SELESUM成绩总分AVG成绩平均分FROM选课WHERE学号=”03”SELE姓名SUM成绩AS总分AVG成绩AS平均分;FROM学生JOIN选课ON学生.学号=选课.学号;WHERE学生.学号=”03”习题P101例5-235-24练习P
915、6/P
94186、分组与计算查询利用GROUPBY子句进行分组计算查询使用得更加广泛GROUPBY短语格式如下GROUPBYGroupColumn[GroupColunm…][HAVINGFilterCondition]说明1.可按一列或多列分组,还可以用HAVING进一步限定分组的条件2.GROUPBY子句一般跟在WHERE子句之后,没有WHERE子句时,跟在FROM子句之后;HAVING子句必须跟在GROUPBY之后,不能单独使用在查询中是先用WHERE子句限定元组,然后进行分组,最后再用HAVING子句限定分组例求每个学生选课的考试成绩平均分SELE学号AVG成绩FROM选课GROUPBY学号说明在此查询中,选按学号属性进行分组,然后再计算每个学号的平均成绩例在选课表中求每个选课门数为4门的学生的总分和平均分SELE学号SUM成绩AS总分AVG成绩AS平均分;FROM选课;GROUPBY学号HAVINGCOUNT*=4SELE姓名SUM成绩AS总分AVG成绩AS平均分;FROM学生JOIN选课ON学生.学号=选课.学号;WHERE性别=”女”;GROUPBY选课.学号HAVINGCOUNT*=4例求平均成绩在80分以上的各课程的课程号与平均成绩SELE课程号AVG成绩FROM选课;GROUPBY课程号HAVINGAVG成绩80习题P102例5-25练习P
917、
12、
14、
15、16
七、集合并运算P103可将两个SELECT语句的查询结果通过并运算合并成一个查询结果为进行并运算,要求两个查询结果具有相同的字段个数,并且对应字段的值要出自同一值域,也就是具有相同的数据类型和取值范围例查询学号为01与02的选课信息SELE*FROM选课WHERE学号=”01”;UNION;SELE*FROM选课WHERE学号=”02”习题P103例5-26
八、VFP中有关SQLSELECT的几个特殊选项P1031.显示部分结果格式TOPn[PERCENT]功能只需要满足条件的前几个记录说明
(1)n是1至32767之间的整数,说明显示前几个记录
(2)当使用PERCENT时,说明显示结果中前百分之几的记录例显示学分最低的前2项课程记录SELE*TOP2FROM课程ORDERBY学分DESC通常与ORDERBY子句连用习题P145/46P152/122.将结果存放在数组中格式INTOARRAYArrayName3.将结果存放在临时文件中格式INTOCURSORCursorName说明临时表中一个只读的DBF文件,当查询结束后该临时文件是当前文件,可像一般的DBF文件一样使用,当关闭文件时该文件将自动删除4.将结果存放在永久表中,通过该子句可实现表的复制格式INTOTABLE|DBFTableName5.将结果存放到文本文件中格式TOFILEFileName[ADDITIVE]6.将结果直接输出到打印机格式TOPRINTER[PROMPT]习题P103例5-27SELECT语句的工作流程首先根据联接条件把几个表联接成一个临时表,然后根据WHERE中的查询条件进行过滤,过滤出来的结果根据分组条件分成一组一组,然后分别对这些组进行计算,最后又得出一个临时表,然后又根据HAVING中的条件对这个临时表再一次过滤,最后按指定的次序输出到指定的地方它中间生成的临时表完全由系统自己创建、使用、删除,完全不受用户控制上机练习
1、完成课件中所有实例,操作数据库学生成绩管理
2、完成教程上所有实例,操作数据库图书管理说明以上练习要求既会直接在命令窗口通过SQL语句完成,也要尝试使用查询设计器来实现如果在上机课上不能完成,可以在计算机课上完成,或者其他业余时间完成请一定在下周上课前完成所有练习!第三节数据操作功能P
921、插入命令格式1INSERTINTO数据表名[字段名1[字段名
2...]]VALUES表达式1[表达式
2...]功能在指定表尾添加一条新记录,其值为VALUES后面的表达式的值命令格式2INSERTINTO数据表名FROMARRAYArrayName|FROMMEMVAR功能向指定表中添加一条新记录,其值来自于数组或对应的同名内存变量例向课程表中插入记录(”05””数理逻辑””6””03”)INSERTINTO课程VALUES”05””数理逻辑”6”03”说明1.如未使用字段名指明,则VALUES中的表达式数目必须与表中的字段数相同,而且相应的数据类型必须一致2.如果表中设定了主索引,则插入记录时只能用该SQLINSERT命令而不能使用APPEND或INSERT命令3.注意插入数据的数据类型必须与表设计时的类型一致习题P92例5-7,5-8练习P89例9P911,3,
42、更新命令格式UPDATETableNameSET字段名1=表达式1[,字段名2=表达式
2...]WHERECondition类似于REPLACE命令一次只能在单一的表中更新记录,通常嵌入在循环结构内,用另一个表的数据来修改本表例将课程关系中课程号为“01”的课程的学分加1分UPDATE课程SET学分=学分+1WHERE课程号=”01”习题P93例5-10练习P88例7,例15P912操作第三套二
(2)/
三三、删除P93命令格式DELETEFROMTableName[WHERECondition]说明类似于VFP的DELE...FOR...命令1.若无WHERE子句会删除表中的全部记录2.此命令为逻辑删除用PACK命令进行物理删除例删除课程关系中课程号为“05”的元组DELEFROM课程WHERE课程号=”05”习题P93例5-9练习P89例10,例11,例12,例13,例14第二节数据定义功能P87
一、表的定义P87命令格式CREATETABLE数据表名字段名1字段类型字段宽度[小数位数][NULL|NOTNULL][CHECK][ERROR][DEFAULT][PRIMARYKEY|UNIQUE][字段名
2......]说明例用SQLCREATE命令建立学生2数据表CREATETABLE学生2学号C2NOTNULLPRIMARYKEY姓名C8;性别C2CHECK性别=”男”OR性别=”女”ERROR“性别只能是男或女”DEFAULT“男”生日D习题P88例5-1,例5-2练习P87例4,例5,例6,例8例第十套一
(3)
(4)二
(1)creatablegolden国家代码C3金牌数int银牌数int铜牌数int答案有错insertintogoldenvalues0119711**************************************************************************可使用查询设计器来完成SELECT国家.国家名称count获奖牌情况.名次AS金牌数;FROM国家INNERJOIN获奖牌情况;ON国家.国家代码=获奖牌情况.国家代码;WHERE获奖牌情况.名次=1;GROUPBY国家.国家名称;ORDERBY2DESC国家.国家名称DESC;INTOTABLEtemp.dbf
二、表的删除P91命令格式DROPTABLETableName功能直接从磁盘上删除指定的表,执行该命令前必须先打开相应的数据库例删除上例中的表“学生2”DROPTABLE学生2习题P91例5-
63、表结构的修改一本通P79命令格式1ALTERTABLETableNameADD|ALTER[COLUMN]字段名
1...]功能⑴添加(ADD)新的字段并同时设置数据表字段的有关属性;⑵修改(ALTER)已有的字段类型、宽度、有效性规则、错误信息、默认值、定义主关键字和联系等属性但不能修改字段名,不能删除字段,也不能删除已经定义的规则等;它的句法基本与CREATETABLE的句法相对应例为课程关系增加长度为6的字符型的先修课号属性ALTERTABLE课程ADD课程号C6例课程中的课程号属性的长度由6改为8ALTERTABLE课程ALTER课程号C8习题P90例5-3练习P87例1,例2,例3,命令格式2ALTERTABLE表名ALTER[COLUMN]字段名1[NULL|NOTNULL][SETDEFAULT][SETCHECK...ERROR...][DROPDEFAULT][DROPCHECK]说明此格式主要用于定义、修改和删除字段一级的有效性规则和默认值定义例为课程关系增加学分大于2的有效性规则ALTERTABLE课程ALTER学分SETCHECK学分2习题P90例5-4命令格式3ALTERTABLE表名[DROP[COLUMN]字段名][SETCHECK...ERROR...][DROPCHECK][RENAME[COLUMN]字段名TO新字段名]......说明此格式可删除字段,可修改字段名,可定义、修改和删除表一级的有效性规则等例将课程表的先修课号字段名改为先修课ALTERTABLE课程RENAME先修课号TO先修课例删除课程表中的先修课字段ALTERTABLE课程DROP先修课习题P91例5-5
四、视图操作1.定义视图命令格式CREATEVIEW视图名ASselect查询语句说明视图是一个虚表,它一旦建立使用方法跟表十分类似,它不会形成单独的文件,它的内容保存在数据库中,所以自由表不能建立视图这是视图与查询的一个区别例CREATEVIEW学生成绩AS;SELE姓名SUM成绩AS总分AVG成绩AS平均分;FROM学生JOIN选课ON学生.学号=选课.学号;GROUPBY选课.学号USE学生成绩BROWSELE*FROM学生成绩2.视图的删除命令格式DROPVIEW视图名第五节例题解析P104课后练习(上机作业)3
(4)sele选课.课程号课程名成绩学分from学生课程选课where学生.学号=选课.学号and课程.课程号=选课.课程号and姓名=李倩3
(5)sele课程名from课程wherenotexistsele*from选课where课程号=课程.课程号and成绩703
(6)sele学号from选课where课程号=sele先修课from课程where课程名=线性代数3
(7)sele学号姓名性别from学生whereexistsele*from选课课程where选课.课程号=课程.课程号and课程名=线性代数and学号=学生.学号and院系号=063
(8)selecountdist院系号from学生3
(9)sele*from学生where生日=allsele生日from学生3
(10)sele学号avg成绩as平均成绩max成绩as最高成绩min成绩as最低成绩from选课groupby学号3
(11)selecountdist学号from选课选课1wherenotexistsele*from选课选课where学号=选课
1.学号and成绩=703
(13)sele学生.*from学生选课课程where学生.学号=选课.学号and选课.课程号=课程.课程号;and课程名=高等数学and成绩seleavg成绩from选课课程where选课.课程号=课程.课程号;and课程名=高等数学3
(15)sele学号成绩from选课where课程号=01and成绩selemin成绩from选课where课程号=02intotabletemp自变量通常为数值型。