vfp第四章关系数据库标准语言SQL

第四章

关系数据库标准语言SQLVisualFoxPro

数据库程序设计苏州科技学院电子与信息工程学院USTS计算机基础教学部目录4.1

SQL概述14.2查询功能24.3操作功能34.4定义功能44.1SQL概述ANSI提出SQL1986年1987年ISO采纳SQL1989年ISO提出SQL891992年ISO提出SQL92SQL功能命令动词数据查询SELECT数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE目录4.2.1简单查询4.2.2简单的连接查询4.2.3嵌套查询4.2.4几个特殊的运算符4.2.5排序4.2.6简单的计算查询4.2.7分组与计算查询4.2.8利用空值查询4.2.9别名与自连接查询内外层相关嵌套查询使用量词和谓词的查询超连接查询集合的并运算中SELECT的几个特殊选项4.1

SQL概述14.2查询功能24.3操作功能34.4定义功能4SELECTSQLSELECT[ALL|DISTINCT][TOPnExpr[PERCENT]]

[Alias.]Select_Item[ASColumn_Name]

[,[Alias.]Select_Item[ASColumn_Name]...]FROM[FORCE]

[DatabaseName!]Table[[AS]Local_Alias]

[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN

DatabaseName!]Table[[AS]Local_Alias]

[ONJoinCondition…][[INTODestination]

|[TOFILEFileName[ADDITIVE]|TOPRINTER[PROMPT]

|TOSCREEN]][PREFERENCEPreferenceName][NOCONSOLE][PLAIN][NOWAIT][WHEREJoinCondition[ANDJoinCondition...]

[AND|ORFilterCondition[AND|ORFilterCondition...]]][GROUPBYGroupColumn[,GroupColumn...]][HAVINGFilterCondition][UNION[ALL]SELECT命令][ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]...]]问题需要访问的数据涉及到多张表查询学生名、课程名、成绩xs.xmkc.kcmcj.cj需要访问的只是局部数据工商管理系的学生xh,xm需要访问计算或统计数据每个学生的平均分xs.xm平均(cj.cj)分组xs.xm每门课程80分以上的人数

kc.kcm个数()条件cj.cj>=80分组cj.kcdh解决方法查询和视图！共同点从表中检索或统计所需的数据区别查询是只读的；视图可以被修改，并且可以将更新结果送回源表查询以独立的文件存储；视图不以独立文件存储，系统将其名称及定义存储在数据库中SELECTSQL定义数据源指定数据源表FROM

子句确定源表间的联接…JOIN…ON…子句定义结果筛选源表记录WHERE子句指定输出字段字段、函数和表达式的列表或*指定输出类型INTO子句和TO子句定义记录的分组GROUPBY子句指定结果顺序ORDERBY子句筛选结果记录HAVING子句指定有无重复记录ALL/DISTINCT指定结果的范围TOPnExpr[PERCENT]简单查询例4.1：从职工表中检索所有工资值。例4.2：检索仓库关系中的所有元组。例4.3：检索工资多于1230的职工号。例4.4：检索哪些仓库有工资多于1210的职工。例4.5：给出在仓库“WH1”或“WH2”工作并且工资少于1250员的职工号。简单连接查询例4.7：找出工作在面积大于400的仓库的职工号以及这些职工工作的城市。Select职工号,城市from仓库，职工;

where(面积>400)and(职工.仓库号=仓库.仓库号)Select职工号,城市from仓库innerjoin职工;

On(职工.仓库号=仓库.仓库号)where面积>400嵌套查询例4.9：查询所有职工的工资都多于1210元的仓库的信息。没有一个职工的工资少于或等于1210元的仓库信息。Select*from仓库where仓库号notin;(select仓库号from职工where工资<=1210);

and仓库号in(select仓库号from职工)几个特殊运算符例4.11:检索出工资在1220元到1240元范围内的职工信息。Select*from职工;where工资between1220and1240例4.12:从供给商关系中检索出全部公司的信息〔不要工厂或其他供给商的信息〕。Select*from供给商;where供给商名like“%公司”4.2.5排序例4.15先按仓库号排序，再按工资排序输出全部职工信息。Select*from职工orderby仓库号,工资分组计算查询Count()、sum()、avg()、max()、min()例：查询cj表中每个学生的考试门数、最高分、最低分、平均分及总分，结果按学号升序排序。Selectcj.xh,count(*)as考试门数,;max(cj.cj)as最高分,min(cj.cj)as最低分,;avg(cj.cj)as平均分,sum(cj.cj)as总分;fromcj;groupbycj.xh;orderby1利用空值查询例4.23：找出尚未确定供给商的订购单。Select*from订购单;where供给商号isnull别名与自连接查询在FROM子句中使用：<关系名><别名>雇员关系：雇员〔雇员号，雇员姓名，经理〕雇员号雇员姓名经理E3赵勇E4钱潮E3E6孙洁E3E8李渌E6Selects.雇员姓名,“领导”,e.雇员姓名;from雇员s,雇员ewheres.雇员号=e.经理内外层互相关嵌套查询例：列出每个职工经手的具有最高金额的订购单信息。Selectout.职工号,out.供给商号,out.订购单号,out.订购日期,out.总金额;from订购单outwhere总金额=;(selectmax(总金额)from订购单inner1;whereout.职工号=inner1.职工号)使用量词和谓词查询<表达式><比较运算符>[ANY|ALL|SOME](子查询)ANY与SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，那么结果为真。ALL要求子查询中的所有行都能使结果为真时，结果才为真。[NOT]EXISTS(子查询)[not]Exists是谓词，用来检查在子查询中是否有结果返回〔即存在元组或不存在元组〕例：查询那些仓库中还没有职工的仓库信息。SELECT*FROM仓库WHERENOTEXISTS;(SELECT*FROM职工where仓库号=仓库.仓库号)<==>select*from仓库where仓库号notin;(select仓库号from职工)例：检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号。Selectdistinct仓库号from职工;where工资>=any;(select工资from职工where仓库号=“WH1”)超连接查询SELECT……from

tableinner|left|right|fulljointableOnjoinconditionWhere……例：普通连接Select仓库.仓库号,城市,面积,职工号,工资;from仓库[inner]join职工on仓库.仓库号=职工.仓库号例：左连接Select仓库.仓库号,城市,面积,职工号,工资;from仓库leftjoin

职工on仓库.仓库号=职工.仓库号例：右连接Select仓库.仓库号,城市,面积,职工号,工资;from仓库rightjoin

职工on仓库.仓库号=职工.仓库号例：全连接Select仓库.仓库号,城市,面积,职工号,工资;from仓库fulljoin

职工on仓库.仓库号=职工.仓库号集合的并运算UNION：两个查询结果具有相同结构的关系并在一起。查询全校教师与学生的姓名和类别信息。Selectjs.xmas姓名,"教师"as类别

fromjs;Union;Selectxs.xmas姓名,"学生"as类别

fromxs4.2.14VisualFoxProSQLSELECT的几个特殊选项显示局部数据：1.显示工资最高的3位职工信息Select*top3from职工orderby工资desc2.显示工资最低的那30%职工的信息Select*top30percentfrom职工orderby工资查询结果的去处：INTOARRAYArrayNameINTOCURSORCursorNameINTOTABLE|DBFTableNameTOFILEFileName[ADDITIVE]TOPRINTER[PROMPT]目录4.3.1插入数据4.3.2更新数据4.3.3删除4.1

SQL概述14.2查询功能24.3操作功能34.4定义功能4插入数据INSERTINTO

dbf_name[(fname1[,fname2,…])]VALUES(eExpression1[,eExpression2,…])Insertinto订购单values("E7","S4","OR01",{^2001-05-25},5000)更新数据UPDATETableName

SET

Column_Name1=eExpression1

[,Column_Name2=eExpression2...]

WHERE

Condition为js表添加一个年龄字段，并计算所有男教师的年龄。Altertablejsaddcolumnnln(3,0)Updatejssetjs.nl=year(date())-year(js.csrq);wherejs.xb=“男”删除数据DELETEFROMTableName[WHERECondition]

删除所有考试不及格的成绩信息。DELETEFROMcjWHEREcj.cj<60目录4.4.1表的定义4.4.2表的删除4.4.3表结构的修改4.4.4视图的定义4.1

SQL概述14.2查询功能24.3操作功能34.4定义功能4表的定义CREATETABLE|DBFTableName1[NAMELongTableName][FREE]

(FieldName1FieldType[(nFieldWidth[,nPrecision])]

[NULL|NOTNULL]

[CHECKlExpression1[ERRORcMessageText1]]

[DEFAULTeExpression1]

[PRIMARYKEY|UNIQUE]

[REFERENCESTableName2[TAGTagName1]]

[NOCPTRANS]

[,FieldName2...]

[,PRIMARYKEYeExpression2TAGTagName2

|,UNIQUEeExpression3TAGTagName3]

[,FOREIGNKEYeExpression4TAGTagName4[NODUP]

REFERENCESTableName3[TAGTagName5]]

[,CHECKlExpression2[ERRORcMessageText2]])

|FROMARRAYArrayName仓库1CREATETABLE仓库1(仓库号C(5)PRIMARYKEY,城市C(10),面积ICHECK(面积>0)ERROR“面积应该大于0”)职工1CREATETABLE职工1(仓库号C(5),职工号C(5)PRIMARYKEY,工资ICHECK(工资>=1000and工资<=5000)ERROR“工资值的范围在1000-5000！”DEFAULT1200,FOREIGNKEY仓库号TAG仓库号REFERENCES仓库1)表的删除DROPTABLEtable_name表结构的修改格式1：(定义或修改字段定义)ALTERTABLE

TableName1

ADD|ALTER[COLUMN]FieldName1

FieldType[(nFieldWidth[,nPrecision])]

[NULL|NOTNULL]

[CHECK

lExpression1[ERRORcMessageText1]]

[DEFAULT

eExpression1]

[PRIMARYKEY|UNIQUE]

[REFERENCES

TableName2[TAGTagName1]]AltertablexsaddrxrqDdefaultdate()格式2：〔修改字段的属性设置〕ALTERTABLETableName1

ALTER[COLUMN]FieldName2

[NULL|NOTNULL]

[SETDEFAULTeExpression2]

[SETCHECKlExpression2[ERRORcMessageText2]]

[DROPDEFAULT]

[DROPCHECK]

给xb字段设置check格式3：〔修改表级别的属性〕ALTERTABLETableName1

[DROP[COLUMN]FieldName3]

[SETCHECKlExpression3[ERRORcMes