第4章结构化查询语言

第4章结构化查询语言第2页4.1SQL概述4.2数据查询（3学时）4.3数据定义

（2学时）4.4数据操纵（1学时）本章主要内容及课时安排

（6学时）第3页4.1SQL概述SQL是关系数据库的标准语言，VisualFoxPro中最强大的SQL命令是SELECT命令，VisualFoxPro只支持SQL其中的语句子集，如没有提供数据控制功能。但这些SQL语句对于数据库查询和维护是十分重要的。SQL（StructureQuerylanguage）是结构化查询语言的英文缩写。SQL语言是一种标准的关系数据库查询语言，充分体现了关系数据语言的特点和优点。第4页

关系数据库标准语言SQL

SQL命令动词

SQL功能命令动词数据查询SELECT

数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE第5页4.2数据查询第6页命令格式：

SELECT<输出项列表>FROM<数据表名>

[WHERE<筛选条件>][GROUPBY<分组项>[HAVING<过滤条件>]]

[ORDERBY<排序项>[ASC|DESC]][INTODBF<目标>]|[TOFILE<文件名>]……第7页考试表部分记录的浏览窗口

第8页SELExm,bs,js

FROM考试;ORDEBYbsDESC,jsDESC例3：将考试表中所有考生成绩按笔试成绩降序，再按机试成绩降序。3.排序查询例2：从考试表中查询笔试成绩≥90分的考生。2.简单条件查询例1:显示所有考生信息。1.无条件查询SELE*FROM考试WHEREbs>=90SELE*FROM考试第9页4．分组与计算查询例4：在考试表中，按语言类别分组查询：语言类别、考生人数、笔试成绩的最高分、笔试成绩的最低分及笔试成绩的平均分。查询结果保存在“成绩统计.dbf”数据表中。Selectyylb语言类别,count(*)考生人数,max(bs)笔试最高分,;min(bs)笔试最低分,avg(bs)笔试平均分;from考试groupbyyylbintotable成绩统计常用函数：COUNT----计数&&对数据表中的记录进行计数COUNT(*)，一般COUNT函数应该使用DISTINCT（DISTINCT短语表示去掉重复记录）SUM----求和AVG----计算平均值MAX----求最大值MIN----求最小值这些函数可以用在SELECT短语中对查询结果进行计算。（参加考试人数在20人以上的语言类别的成绩情况）第10页例5：在图书表中，查询所藏图书中，有三种及三种以上的图书出版社所出版图书的最高单价和平均单价。SELEcbdw出版单位,MAX(dj)最高单价,AVG(dj)平均单价;FROM图书GROUPBYcbdw;HAVINGCOUNT(DISTINCTzbh)>=3第11页例6：查找与“胡娟”同龄的考生姓名、年龄和成绩。SELExm,nl,bs,jsFROM考试WHEREnl=;(SELEnlFROM考试WHERExm="胡娟")

5.嵌套查询第12页6.多表内联查询例7：在图书管理数据库中，查询已借出书籍读者的借书证号、姓名和单位以及借出图书的书名，单价，借书日期。SELEa.jszh,a.xm,a.dw,b.sm,b.dj,c.jsrq;FROM读者A,图书B,借阅C;WHERE(a.jszh=c.jszh)AND(b.zbh=c.zbh)SELECT读者.jszh,读者.xm,读者.dw,图书.sm,图书.dj,借阅.jsrq;FROM图书管理!读者INNERJOIN图书管理!借阅;INNERJOIN图书管理!图书;ON借阅.zbh=图书.zbh;ON读者.jszh=借阅.jszh在“查询设计器”中自动生成的SQL命令是：内部联接：INNERJOIN等价于JOIN普通联接，只有满足联接条件的记录才出现在查询结果中。例8：在图书管理数据库中，求会计学院借阅图书的读者的人数。SELEdw单位,COUNT(DISTINCT借阅.jszh)借书人数;FROM借阅,读者;WHERE借阅.jszh=读者.jszhAND读者.jszhIN;(SELECT读者.jszhFROM读者WHEREdw="会计学院")SELExm姓名,dw单位FROM读者WHERENOTEXISTS;(SELE*FROM借阅WHERE借阅.jszh=读者.jszh);ORDERBYdw例9：对于图书管理数据库，检索没有借阅任何图书的读者的姓名和所在单位。SELEdw单位,COUNT(DISTINCT借阅.jszh)借书人数;FROM借阅,读者;WHERE借阅.jszh=读者.jszhANDdw="会计学院"7.利用BETWEEN、IN、LIKE的查询语句例10：显示所有笔试成绩在(60～80)分考生信息。SELE*FROM考试;WHEREbsBETW60AND80例11：显示所有参加“二级VB”和“二级VF”的考生。SELE*FROM考试WHEREyylbIN;("二级VB","二级VF")等价于bs>=60ANDbs<=80

等价于yylb="二级VB“ORyylb="二级VF"第15页例13：显示所有名字中有“丽”的考生成绩。SELExm,bs,jsFROM考试;WHERExmLIKE"%丽%"例12：显示所有姓“李”的考生成绩。SELE*FROM考试WHERExmLIKE"李%"SELE*FROM考试WHERExmLIKE"李_"SELE*FROM考试WHERExmLIKE"李__"等价于xm="李"

等价于"丽"$xm

8.显示部分记录例14:显示笔试成绩最高的三位考生的记录。SELE*TOP3FROM考试ORDERBYbsDESC例15:显示笔试成绩最低的那30%考生的记录。SELE*TOP30PERCENTFROM考试;ORDERBYbs第17页4.3数据定义第18页

4.3.1建立数据表

CREATTABLE|DBF<表名>[FREE](<字段名1><字段类型>(<字段宽度>[,<小数位数>]),<字段名2>……)[NULL|NOTNULL][CHECK<逻辑表达式>[ERROR字符表达式]][DEFAULT<表达式>][PRIMARYKEY|UNIQUE][REFERECES<表名2>[TAG<标记名>]]

[,FOREIGNKEY<索引表达式>TAG<索引标识>REFERENCE<数据表>[TAG<索引标识>] ……第19页例：新建一个学生管理数据库，在该数据库中建立学生表stud.dbf(学号、姓名、性别、出生日期、籍贯、地址、家庭月收入、系别代号)和成绩表cj.dbf(学号、计算机、英语、会计、法学、总分、平均分)，并且将两个表按“学号”建立一对多的联系。CREATDATA学生管理第20页&&学号为主索引且不能为空值，对家庭月收入字段说明了字段有效性规则及出错信息。CREATTABLEstud(学号c(4)PRIMKEYNOT;NULL,姓名c(6),性别c(2),出生日期d,籍贯c(8),地址;c(40),家庭月收入n(8,2)CHECK家庭月收入>=0;ERROR"家庭月收入应大于等于零",系别代号c(2))第21页

CREATTABLEcj(学号c(10),计算机n(5,1),;英语n(5,1),会计n(5,1),法学n(5,1),总分n(3),;平均分n(5,1),FOREIGNKEY学号;

TAG学号REFERENCESstud)&&FOREIGNKEY学号

短语表明在该表的学号字段上建立一个普通索引。&&

TAG学号REFERENCESstud短语表明学号字段是连接字段，通过引用stud的主索引“学号”与stud建立联系。第22页学生表和成绩表的一对多的联系第23页4.3.2表的删除数据库在实际使用过程中，往往有些表不再需要了，对于这样的表可以删除以节省存储空间。要删除数据库中的表时，最好在当前打开的数据库中进行操作。

命令格式：

DROPTABLE<表名>|？[RECYCLE]

命令功能：删除指定的表。?参数用于在“删除”对话框中选择要删除的表名。RECYCLE：用于将删除的表放入回收站。第24页例1：增加电话字段

ALTERTABLEstudADD电话c(10)

例2：修改姓名字段的宽度

ALTERTABLEstudALTER姓名c(10)4.3.3修改数据表

ALTERTABLE<表名>ADD|ALTER<字段名><字段类型>[(<字段宽度>[,<小数位数>])]ALTERTABLE<表名>DROP<字段名>ALTERTABLE<表名>RENAMECOLUMN<字段名1>

TO<字段名2>第25页例3：删除电话字段ALTERTABLEstudDROP电话例4：将字段名由地址改为家庭地址ALTERTABLEstudRENAMECOLUMN地址TO;家庭地址

例5：修改或定义成绩表的英语字段的有效性规则。ALTERTABLEcjALTER英语SETCHECK;

英语>=0ERROR“成绩不应小于零！”第26页例6：删除学生表的家庭月收入字段的有效性规则。ALTERTABLEstudALTER家庭月收入;DROPCHECK例7：将学生表中姓名和出生日期定义为候选索引，索引标记名为“xmrqsy”。ALTERTABLEstudADDUNIQUE;姓名+DTOC(出生日期)TAGxmrqsy例8：删除学生表中索引标记为“xmrqsy”的候选索引。

ALTERTABLEstudDROPUNIQUETAGxmrqsy

第27页例9：删除成绩表与学生表两个表之间的永久关系。

ALTERTABLEcj

DROPFOREIGNKEYTAG学号例10：重新建立成绩表与学生表两个表之间一对多的永久关系。

ALTERTABLEcj

ADDFOREIGNKEY学号;TAG学号REFERENCESstud

第28页4.4数据操纵第29页

例：为stud表添加记录的全部字段数据。

INSERTINTOstudVALU("0110","李建国","男",;{^1981.02.06},"湖南","湖大北校区39栋",2345.00,"01")

&&数据的个数和顺序和表结构的字段个数和顺序必须一致。例：为stud表添加记录的部分字段数据。

INSERTINTOstud(学号,籍贯,性别,姓名)VALU;("0111","湖北",

"女","王丽丽")&&数据的顺序和字段名表中指定的顺序必须一致。4.4.1插入记录INSEINTO<表名>[(<字段名表>)]VALUE(<表达式表>)第30页

4.4.2更新记录

UPDATE<表名>SET<字段名1>=<表达式1>[，<字段名2>=<表达式2>[,...]][WHERE<条件>]例：将“考试.dbf”表中的“yylb”字段的“二级VF”改为“二级VisualFoxPro”。

UPDATE考试SETyylb="二级VisualFoxPro";WHEREyylb="二级VF"例：将“考试.dbf”的jl字段按分数添入优秀、及格、补考和不及格结论。

UPDATE考试SETJL="优秀"

WHEREbs>=90;ANDjs>=90第31页

4.4.3删除记录

DELETEFROM<表名>[WHERE<条件>]

例：删除不及格考生记录。

DELEFROM考试WHEREbs<60andjs<60

例：从借阅表中的删除科学出版社出版的“数据库导论”的借阅记录。（注意，不同出版单位所出版的相同书名“数据库导论”在借阅表中都有借阅记录）

DELETEFROM借阅WHEREzbh;IN(SELECT借阅.zbhFROM图书,借阅;WHERE借阅.zbh=图书.zbhANDsm="数据库导论";ANDcbdw="科学出版社")本章练习第33页“教师”表职工号姓名职称年龄工资系号11020001肖天海副教授352000.000111020002王岩盐教授403000.000211020003刘星魂讲师251500.000111020004张月新讲师301500.000311020005李明玉教授342000.000111020006孙民山教授472100.000211020007钱无名教授492200.0003“学院”表系号系名01英语02会计03工商管理第34页(1)使用SQL语句将一条新的记录插入学院表。INSERT______学院(系号,系名)______(“04”,“计算机”)(2)使用SQL语句求“工商管理”系的所有职工的工资总和SELE_____(工资)FROM教师WHERE系号IN;(SELE系号FROM_____WHERE系名=“工商管理”)(3)使用SQL语句完成将所有教授的工资提高5%的操作。_____教师SET工资=工资*1.05______职称=“教授”INTOVALU学院SUMUPDAWHERE第35页数据库表stock.dbf股票代码股票名称

单价

交易所600600青岛啤酒7.48上海600601方正科技

15.20

上海600602广电电子

10.40上海600603兴业房产

12.76上海600604二纺机

9.96上海600605轻工机械

14.59上海000001深发展

7.48

深圳000002深万科

12.50

深圳第36页(1)执行如下SQL语言后

SELE*FROMstockINTODBFstockORDERBY单价

A)系统会提示出错信息

B)会生成一个按“单价”升序排列的表文件，将原来的stock.dbf文件覆盖

C)会生成一个按“单价”降序排列的表文件，将原来的stock.dbf文件覆盖

D)不会生成排序文件，只在屏幕上显示一个按“单价”升序排序的结果(2)在当前盘当前目录下删除表stock的命令是

A)DROPstock

B)DELETABLstockC)DROPTABLstockD)DELEstock第37页(3)执行如下SQL语言后

SELEmax(单价)INTOARRAYaFROMstock

A)a(1)的内容为15.20B)a(1)的内容为7.48

C)a(0)的内容为15.20D)a(0)的内容为7.48(4)执行如下SQL语言后，temp表中第二条记录的“均价”字段的内容是

SELE股票代码,avg(单价)as均价FROMstock;GROUPBY交易所INTODBFtempA)7.48B)9.99C)11.73D)15.20(5)执行如下SQL语言后产生的视图包含的记录个数是

CREAVIEWstock_viewASSELE*FROMstock;WHERE交易所=“深圳”

A)1B)2C)3D)4第38页(6)将stock表的股票名称字段的宽度由８改为１０，应使用SQL语句

A)ALTERTABLEstock股票名称WITHc(10)B)ALTERTABLEstock股票名称c(10)C)ALTERTABLEstockALTER股票名称c(10)D)ALTERstockALTER股票名称c(10)(7)执行如下SQL语句后产生的视图含有的字段名是

CREAVIEWview_stockASSELE股票名称AS名称,单价FROMstockA)股票名称、单价B)名称、单价C)名称、单价、交易所D)股票名称、单价、交易所第39页(8)执行如下SQL语句后表stock1中的记录个数是

SELEDISTINCT单价FROMstockWHERE单价=(SELEmin(单价)FROMstock)INTODBFstock1A)1B)2C)3D)4(9)求每个交易所的平均单价的SQL语句是

A)SELE交易所,avg(单价)FROMstock;GROUPBY单价B)SELE交易所,avg(单价)FROMstock;ORDERBY单价C)SELE交易所,avg(单价)FROMstock;GROUPBY交易所D)SELE交易所,avg(单价)FROMstock;ORDERBY交易所第40页商品表:部门号商品号商品名称单价数量产地400101A牌电风扇200.0010广东400104A牌微波炉350.0010广东400105B牌微波炉600.0010广东201032C牌传真机1000.0020上海400107D牌微波炉_A420.0010北京200110A牌电话机200.0050广东200112B牌手机2000.0010广东400202A牌电冰箱3000.002广东301041B牌计算机6000.0010广东300204C牌计算机10000.0010上海第41页部门表:部门号部门名称40家用电器部10电视录摄象机部20电话手机部30计算机部SQL语句SELE部门号,MAX(单价*数量)FROM商品表;GROUPBY部门号查询结果有几条记录?

A)1B)4C)3D)10第42页(2)SQL语句SELE产地,COUNT(*)提供的商品种类数FROM商品表

WHERE单价>200GROUPBY产地;HAVINGCOUNT(*)>=2ORDERBY2DESC

查询结果的第一条记录的产地和提供的商品种类数是

A)北京,1B)上海,2C)广东,5D)广东,7(3)SQL语句SELE部门表.部门号,部门名称,SUM(单价*数量)FROM部门表,商品表WHERE部门表.部门号=商品表.部门号GROUPBY部门表.部门号各部门商品数量合计B)各部门商品金额合计C)所有商品金额合计D)各部门商品金额平均值第43页(4)SQL语句SELE部门表.部门号,部门名称,商品号,商品名称,单价FROM部门表,商品表WHERE部门表.部门号=商品表.部门号ORDERBY部门表.部门号DESC,单价查询结果的第一条记录的商品号是

A)0101B)0202C)0110D)0112(5)SQL语句SELE部门名称FROM部门表WHERE部门号IN;(SELE部门号FROM商品表WHERE单价BETW;420AND1000)家用电器部、电话手机部B)家用电器部、计算机C)电话手机部、电视录摄象机部D)家用电器部、电视录摄象机部第44页例:在标准SQL中，建立视图的命令是CREATESCHEMA(B)CREATETABLE(C)CREATEVIEW(D)CREATEINDEX例：在SQL语言中，修改基本表的语句是

(A)CREATETABLE(B)DROPTABLE(C)ALTERTABLE(D)UPDATETABLE例：SQL语言是(A)高级语言(B)结构化查询语言(C)第三代语言(D)宿主语言第45页例：SQL实现分组查询的短语是(A)ORDERBY(B)GROUPBY(C)HAVING(D)ASC例：在SQL的计算查询中，用于求平均值的函数是(A)AVG(B)AVERAGE(C)average(D)AVE例：SQL语句中的SELECT命令建立表之间联系的短语为(A)UNIQE(B)GROUPBY(C)JOIN(D)FROM例:下面所列条目中，错误的SQL语句是(A)ALTERTABLE(B)ALTERVIEW(C)CREATETABLE(D)CREATEVIEW例：SQL语句中修改表结构的命令是(A)MODIFYTABLE(B)MODIFYSTRUCTURE(C)ALTERTABLE(D)ALTERSTRUCTURE第46页例：用于显示部分查询结果的TOP短语，必须同时使用的短语是：(A)ORDERBY(B)FROM(C)WHERE(D)GROUPBY例：用SQL语句建立表时将属性定义为主关键字，应使用短语(A)CHECK(B)PRIMARYKEY(C)FREE(D)UNIQUE例：HAVING短语不能单独使用，必须接在下列哪个短语之后(A)ORDERBY(B)FROM(C)WHER