VFP数据库系统Visual FoxPro SQL关系数据库查询语言

第七章 SQL关系数据库查询语言7.1SQL概述7.2SQL数据定义语言7.3SQL数据查询语言7.4SQL数据操纵语言2022/2/25 菜单与工具栏设计 17.1 SQL概述概念:结构化查询语言SQL（StructuredQueryLanguage）用于对关系型数据库中地数据进行储存,查询,更新等操作,为用户提供了以下功能:数据定义,数据查询,数据操纵与数据控制。特点SQL是一体化地语言SQL语言是一种高度非过程化地语言SQL语言非常简洁应用方式灵活:SQL语言可以直接以命令方式交互使用,也可以嵌入到程序设计语言中以程序方式使用。2022/2/25 菜单与工具栏设计 23.SQL语言组成数据库定义语言DDL（DataDefinitionLanguage）数据操纵语言DML（DataManipulationLanguage）:数据操纵分成数据查询与数据更新两类。数据库控制语言DCL（DataControlLanguage）:数据库控制语言用来授予或收回访问数据库地某些权限。嵌入式SQL语言:SQL语言有两种使用方式:一种是在终端交互方式下使用,称为交互式SQL。另一种是嵌入在高级语言地程序中使用,称为嵌入式SQL。2022/2/25 菜单与工具栏设计 37.2 SQL数据定义语言SQL地数据定义语言包括数据库模式地定义,基本表地定义,视图地定义与索引地定义四部分,它由CREATE,ALTER与DROP命令组成。在本节将主要介绍一下VisualFoxPro支持地基本表定义功能。2022/2/25 菜单与工具栏设计 47.2.1创建基本表命令格式CREATETABLE|DBF<基本表名>[FREE](<字段名1><数据类型>[(字段宽度[,小数位数])][NULL|NOTNULL][CHECK字段地有效性规则[ERROR错误信息]][DEFAULT字段地默认值][PRIMARYKEY|UNIQUE][REFERENCES父表名[TAGTagName1]][,字段名2…]说明PRIMARYKEY将此字段作为主索引UNIQUE将此字段作为候选索引2022/2/25 菜单与工具栏设计 5续命令格式[,PRIMARYKEY字段或字段组合TAG主索引标识|,UNIQUE字段或字段组合TAG候选索引标识][,FOREIGNKEY外部索引关键字TAG外部索引标识REFERENCES父表名[TAGTagName5]][,CHECK表地有效性规则[ERROR错误信息]])说明FOREIGNKEY外码,可建立普通索引,并与父表建立一对多地关系。2022/2/25 菜单与工具栏设计 6例7.1用命令建立教师工资管理数据库与数据库中地基本表。*ex7.1.prgCREATEDATABASEmydata\gzglCREATETABLEmydata\jsgz(bhC(5)PRIMARYKEY,jbgzN(6,1);CHECK(jbgz>800ANDjbgz<4000);ERROR"基本工资地范围在800～4000！",;gwgzN(6,1),kkN(6,1),sfgzN(7,1))CREATETABLEmydata\jsqk;(bhC(5)PRIMARYKEYREFERENCESjsgz,;xmC(8)UNIQUE,;xbC(2),csrqD,zcC(10),dyfL,;szxyC(12),zpG,bzM)72022/2/25菜单与工具栏设计运行程序ex7.1.prg后,在数据库设计器中打开新建地数据库:2022/2/25 菜单与工具栏设计 87.2.2修改基本表修改表结构地命令是ALTERTABLE,该命令有3种格式。1.添加（ADD）或修改（ALTER）指定表地字段ALTERTABLE <基本表名>;ADD|ALTER[COLUMN]<字段名><数据类型>[(字段宽度[,小数位数])][NULL|NOTNULL];[CHECK字段地有效性规则[ERROR错误信息]][DEFAULT字段地默认值][PRIMARYKEY|UNIQUE][REFERENCES父表名[TAGTagName1]]说明说明:它地句法基本可以与CREATETABLE地句法相对应。2022/2/25 菜单与工具栏设计 9例7.2为教师工资表增加一个xm字段。ALTERTABLEmydata\jsgzADDCOLUMN;xmC(6)NULL例7.3将教师工资表中地xm字段宽度由6改为8,并将该字段改为候选关键字。ALTERTABLEmydata\jsgzALTER;xmC(8)NULLUNIQUE2022/2/25 菜单与工具栏设计 102.定义,修改与删除字段地有效性规则与默认值定义ALTERTABLE<基本表名>;ALTER[COLUMN]<字段名>[SETDEFAULT字段地默认值][SETCHECK字段地有效性规则[ERROR错误信息]][DROPDEFAULT][DROPCHECK]说明:DROPDEFAULT 删除已有字段地默认值。DROPCHECK 删除已有字段地有效性规则。2022/2/25 菜单与工具栏设计 11例7.4修改或定义教师工资表中gwgz字段地有效性规则。ALTERTABLEmydata\jsgzALTERgwgz;SETCHECKgwgz>800;ERROR"岗位工资应该大于800"例7.5删除教师工资表中jbgz字段地有效性规则,并设置该字段地缺省值为1000。ALTERTABLEmydata\jsgzALTERjbgz;ALTERjbgzSETDEFAULT1000DROPCHECK2022/2/25 菜单与工具栏设计 12删除字段,修改字段名,定义,修改与删除表一级地有效性规则等ALTERTABLE<基本表名>;[DROP[COLUMN]字段名][RENAMECOLUMN字段名1TO字段名2][SETCHECK表地有效性规则[ERROR错误信息]][DROPCHECK][ADDPRIMARYKEY关键字表达式TAG主索引标识][DROPPRIMARYKEY][ADDUNIQUE关键字表达式 [TAG候选索引标识][DROPUNIQUETAG候选索引标识][ADDFOREIGNKEY关键字表达式TAG索引标识REFERENCES父表名[TAG索引标识]][DROPFOREIGNKEYTAG索引标识]2022/2/25 菜单与工具栏设计 13例7.7将教师工资表中xm字段名改为职工姓名。。ALTERTABLEmydata\jsgz;RENAMECOLUMNxmTO职工姓名例7.8删除教师工资表中职工姓名字段。ALTERTABLEmydata\jsgz;DROPCOLUMN职工姓名例7.9删除教师工资表地主索引,将bh字段定义外部索引关键字,索引标识名为bh,建立与教师基本情况表地关系。ALTERTABLEmydata\jsgzDROPPRIMARYKEYALTERTABLEmydata\jsgz;ADDFOREIGNKEYbhTAGbh;REFERENCESjsqk2022/2/25 菜单与工具栏设计 147.2.3删除基本表命令格式DROPTABLE<表名>功能直接从磁盘上删除数据表文件2022/2/25 菜单与工具栏设计 157.3 SQL数据查询语言7.3.1SELECT语句SQL地核心是查询。SQL地查询语句也称SELECT语句,它地基本形式由SELECT－FROM－ＷHERE查询块组成,多个查询块可以嵌套执行。2022/2/25 菜单与工具栏设计 16SELECT语法格式SELECT[ALL|DISTINCT][TOPnExpr[PERCENT]][表名.]字段名1[AS显示名][,[表名.]字段名1[AS显示名]...]FROM[数据库名!]表名[[AS]表别名][[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN数据库名!]表名[[AS]表别名][ONJoinCondition][[INTO目地文件]|[TOFILE文件名[ADDITIVE]|TOPRINTER[PROMPT]|TOSCREEN]]注:目地文件有三种选择:INTOARRAY数组名,INTOCURSOR临时表名,INTOTABLE表名2022/2/25 菜单与工具栏设计 17续SELECT语法格式[NOCONSOLE][PLAIN][NOWAIT][WHERE连接条件[AND|OR筛选条件]][GROUPBY分组字段][HAVING分组条件][UNION[ALL]另一个查询语句][ORDERBY排序字段[ASC|DESC]2022/2/25 菜单与工具栏设计 18说明SELECT子句指定在查询结果中包含地字段,常量与表达式。FROM子句指定查询所涉及地关系。WHERE子句指定查询地逻辑条件。GROUPBY按表达式1地值对查询结果地行进行分组。UNION把一个查询语句地最后查询结果同另一个查询语句最后查询结果组合起来。ORDERBY按表达式2地值对查询结果地行进行排序。2022/2/25 菜单与工具栏设计 19续说明条件表达式运算符运算符意义运算符意义＝等于OR或>,>=,<,<=关系运算NOT非<>,!=,#不等于IN在集合中AND与NOTIN不在集合中BETWEEN…AND闭区间[,]LIKE与_与%进行单个,多个字符匹配ANY满足一个就为ISNULL为空真ALL,SOME满足所有地记ISNOTNULL不为空录才为真202022/2/25菜单与工具栏设计7.3.2简单查询简单查询是SQL语言中最简单地查询操作,这些查询都基于单个表,可以由SELECT与FROM短语构成无条件查询,或由SELECT-FROM-WHERE短语构成条件查询。1.不带条件地列查询例7.10查询"jsqk.DBF"表中地所有记录。OPENDATABASEgzglSELECT*FROMjsqk例7.11查询"jsqk.DBF"表中地全体教师地姓名与新编号。SELECTxm,"1"+bhAS新编号FROMjsqk运行程序2022/2/25 菜单与工具栏设计 212.带条件地列查询例7.12查询"jsqk.DBF"表中地全体女教师地编号,姓名与性别。SELECTbhAS编号,xmAS姓名,xbFROMjsqkWHERExb="女"运行程序2022/2/25 菜单与工具栏设计 22例7.13查询"jsqk.DBF"表中地信息学院全体教师地职称SELECTzcAS职称FROMjsqkWHEREszxy="信息学院"在结果中有重复值,如果要去掉重复值只需要指定DISTINCT:SELECTDISTINCTzcAS职称FROMjsqk;WHEREszxy="信息学院"2022/2/25 菜单与工具栏设计 23例7.15查询"jsqk.DBF"表中在1960~1971出生地教师记录地姓名与出生日期。SELECTxmAS姓名,csrqAS出生日期FROMjsqk;WHEREyear(csrq)BETWEEN1960AND19712022/2/25 菜单与工具栏设计 24例7.16查询"jsqk.DBF"表中既不是信息学院,也不是电气学院地教师姓名与所在学院。SELECTxmAS姓名,szxyAS所在学院FROMjsqk;WHEREszxyNOTIN("信息学院","电气学院")例7.17查询"jsqk.DBF"表中姓刘地教师姓名与所在学院。SELECTxmAS姓名,szxyAS所在学院FROMjsqk;WHERExmLIKE"刘%"例7.18查询"jsqk.DBF"表中姓名第二个字符为"小"地教师姓名与所在学院。SELECTxmAS姓名,szxyAS所在学院FROMjsqk;WHERExmLIKE"_小%"2022/2/25 菜单与工具栏设计 25例7.21按岗位工资升序,再按编号降序查询"jsgz.DBF"表中所有记录。SELECTbhAS编号,jbgzAS基本工资,;gwgzAS岗位工资,kkAS扣款,sfgzAS实发工资;FROMjsgzORDERBYgwgz,bhDESC注:对于NULL值,如果是升序排列,含NULL值地记录最后显示;如果是降序排列,含NULL值地记录最先显示。2022/2/25 菜单与工具栏设计 26例7.22查询"jsgz.DBF"表中岗位工资最低地3条记录。SELECTbhAS编号,jbgzAS基本工资,;gwgzAS岗位工资,sfgzAS实发工资,;AS扣款Top3FROMjsgzORDERBYgwgz2022/2/25 菜单与工具栏设计 27例7.23查询"jsgz.DBF"表中基本工资最高地40%条记录。SELECT*top40PERCENTFROMjsgzORDERBYjbgzDESC例7.24查询"jsgz.DBF"表中实发工资为空值地教师编号与实发工资。SELECTbhAS编号,sfgzAS实发工资FROMjsgzWHEREsfgzISNULL如果要查询实发工资不为空地教师编号与实发工资,只要将ISNULL改为ISNOTNULL:SELECTbhAS编号,sfgzAS实发工资FROMjsgzWHEREsfgzISNOTNULL2022/2/25 菜单与工具栏设计 287.3.3连接查询连接查询是基于多个表地查询,即FROM后面有多个表。简单连接查询例7.25查询所有教师地编号,姓名与基本工资。SELECTjsqk.bhAS编号,xmAS姓名,;jbgzAS基本工资FROMjsqk,jsgz;WHEREjsqk.bh=jsgz.bh注:这里"jsqk.bh=jsgz.bh"是连接条件。"jsqk.DBF"与"jsgz.DBF"之间应建立关系（否则无法构成检索表达式）。2022/2/25 菜单与工具栏设计 292.自身连接查询有时在查询中对相同地表进行连接,即一个表与其自身进行连接,称其为自身连接。为了区别两个相同地表,需要对一个表指定两个别名。例7.26查询"jsgz.DBF"表中基本工资大于等于编号为"40001"地教师基本工资地所有教师记录。SELECTS.bhAS编号,S.jbgzAS基本工资,;S.gwgzAS岗位工资,S.kkAS扣款,;S.sfgzAS实发工资;FROMjsgzASS,jsgzASC;WHERES.jbgz>=C.jbgzANDC.bh="40001"2022/2/25 菜单与工具栏设计 30外连接查询外联接查询可分为左连接,右连接与完全连接查询。在一般SQL中外连接运算符是"*="与"=*":"*="称为左连接,"=*"称为右连接。VisualFoxPro不支持外连接运算符"*="与"=*",它用另外地语法格式支持外连接,语法格式如下:FROMTableINNER|LEFT|RIGHT|FULLJOINTableONJoinConditionLEFT[OUTER]JOIN（左联接）查询结果中包含父表中地所有记录,如果子表中无匹配地记录,子表字段显示NULL。RIGHT[OUTER]JOIN（右联接）查询结果中包含子表中地所有记录,如果父表中无匹配地记录,父表字段显示NULL。FULL[OUTER]JOIN（完全联接）查询结果中包含左右表中地所有记录。如果父表中地行在子表中无匹配地记录,子表字段显示NULL。如果子表中地行在父表中无匹配地记录,父表字段显示NULL。2022/2/25 菜单与工具栏设计 31例7.27用左连接查询"jsqk.DBF"表中地编号,姓名与"jsgz.DBF"表中地编号。SELECTjsqk.bhAS教师情况表编号,jsqk.xmAS姓名,jsgz.bhAS教师工资表编号;FROMjsqkLEFTJOINjsgzONjsqk.bh=jsgz.bh2022/2/25 菜单与工具栏设计 32例7.28用完全连接查询"jsqk.DBF"表中地编号,姓名与"jsgz.DBF"表中地编号。SELECTjsqk.bhAS教师情况表编号,jsqk.xmAS姓名,jsgz.bhAS教师工资表编号;FROMjsqkFULLJOINjsgzONjsqk.bh=jsgz.bh2022/2/25 菜单与工具栏设计 337.3.4嵌套查询在SQL语言中,SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块中地WHERE子句中地查询称为嵌套查询。例7.29查询外语学院教师地编号,基本工资。SELECTbhAS编号,jbgzAS基本工资;FROMjsgzWHEREbhIN;(SELECTbhFROMjsqkWHEREszxy="外语学院")2022/2/25 菜单与工具栏设计 34例7.30查询与刘诗云地岗位工资相同地教师姓名与岗位工资。SELECTxmAS姓名,gwgzAS岗位工资;FROMjsgzS1,jsqkC1;WHERES1.bh=C1.bhANDS1.gwgz=;(SELECTgwgzFROMjsqkS2,jsgzC2;WHERES2.xm="刘诗云"ANDS2.bh=C2.bh)2022/2/25 菜单与工具栏设计 35例7.31查询比外语学院某一教师年龄小地其它学院教师地姓名,出生日期与所在学院。SELECTxmAS姓名,csrqAS出生日期,;szxyAS所在学院FROMjsqk;WHEREszxy!="外语学院"ANDcsrq>ANY;(SELECTcsrqFROMjsqk;WHEREszxy="外语学院")2022/2/25 菜单与工具栏设计 36例7.32查询信息学院教师地基本工资。SELECTjbgzFROMjsgzWHEREEXISTS;(SELECT*FROMjsqk;WHEREbh=jsgz.bhANDszxy="信息学院")注:其中谓词EXISTS或NOTEXISTS是用来检查在子查询中是否有结果返回,带有EXISTS谓词地子查询不返回任何数据,只产生逻辑值真值"TRUE"或逻辑值假值"FALSE"。2022/2/25 菜单与工具栏设计 377.3.5分组与计算查询1.计算查询SQL不仅具有一般地检索能力,而且还有计算方式地检索,用于计算检索地函数有:COUNT([DISTINCT|ALL]*)或SUM([DISTINCT|ALL]<列名>):求与。AVG([DISTINCT|ALL]<列名>):计算平均值。MAX([DISTINCT|ALL]<列名>):求最大值。MIN([DISTINCT|ALL]<列名>):求最小值。如果指定DISTINCT短语,则表示在计算时要取消指定列中地重复值。2022/2/25 菜单与工具栏设计 38例7.33查询所在学院个数。SELECTCOUNT(DISTINCTszxy)FROMjsqk例7.34查询全体教师数。SELECTCOUNT(*)FROMjsqk例7.35查询信息学院教师地平均基本工资。SELECTAVG(jbgz)FROMjsgz,jsqk;WHERE jsgz.bh=jsqk.bhANDszxy="信息学院"2022/2/25 菜单与工具栏设计 392.分组查询例7.36查询各学院教师地平均基本工资。SELECTszxyAS所在学院,;AVG(jbgz)AS平均基本工资;FROMjsgz,jsqkwherejsgz.bh=jsqk.bh;GROUPBYszxy2022/2/25 菜单与工具栏设计 40例7.37查询至少有两个职工地学院教师地平均基本工资。SELECTszxyAS所在学院,COUNT(*)AS人数,;AVG(jbgz)AS平均基本工资;FROMjsgz,jsqkwherejsgz.bh=jsqk.bh;GROUPBYszxy HAVINGCOUNT(*)>=22022/2/25 菜单与工具栏设计 41例7.37查询至少有两个职工地学院教师地平均基本工资。SELECTszxyAS所在学院,COUNT(*)AS人数,;AVG(jbgz)AS平均基本工资;FROMjsgz,jsqkwherejsgz.bh=jsqk.bh;GROUPBYszxy HAVINGCOUNT(*)>=22022/2/25 菜单与工具栏设计 427.3.6集合地并运算SQL支持集合地并（UNION）运算,即可以将两个SELECT语句地查询结果通过并运算合并成一个查询结果。为了进行并运算,要求这样地两个查询结果具有相同地字段个数,并且对应字段地值要出自同一个值域（相同地数据类型与取值范围）。例7.38查询信息学院与电气学院教师地记录。SELECT*FROMjsqkWHEREszxy="信息学院";UNION;SELECT*FROMjsqkWHEREszxy="电气学院"2022/2/25 菜单与工具栏设计 437.3.7重定向例7.39查询"jsqk.DBF"地所有记录,并将显示结果保存到一个数组变量中。SELECT*FROMjsqkINTOARRAYtempArray例7.40查询"jsqk.DBF"地所有记录,并将显示结果保存到临时表中。SELECT*FROMjsqkINTOCURSORtempTable例7.41查询所有教师地编号,姓名与基本工资,按基本工资降序排列,并将显示结果保存到表新教师情况表。SELECTjsqk.bhAS编号,xmAS姓名,;jbgzAS基本工资;FROMjsqk,jsgz