第4章结构化查询语言

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

2、言，充分体现了关系数据语言的特点和优点。第4页第5页4第6页 命令格式：命令格式： SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESCINTO DBF |TO FILE 第7页考试表部分记录的浏览窗口考试表部分记录的浏览窗口 第8页SELE xm,bs,js FROM 考试考试; ORDE BY bs DESC,js DESC例例3：将考试表中所有考生成绩按笔试成绩降序：将考试表中所有考生成绩按笔试成绩降序，再再按机试成绩降序。按机试成绩降序。3. 排序查询排序查询例例2：从考试表中查询笔试成绩：从考试表中查询笔试成绩90分的考生。分的考生。

3、2. 简单条件查询简单条件查询 例例1:显示所有考生信息。显示所有考生信息。1. 无无条件查询条件查询SELE * FROM 考试考试 WHERE bs=90 SELE * FROM 考试考试第9页4分组与计算查询分组与计算查询 例例4：在考试表中，按语言类别分组查询：语言类别、考生人数、：在考试表中，按语言类别分组查询：语言类别、考生人数、笔试成绩的最高分、笔试成绩的最低分及笔试成绩的平均分。查笔试成绩的最高分、笔试成绩的最低分及笔试成绩的平均分。查询结果保存在询结果保存在“成绩统计成绩统计.dbf”数据表中。数据表中。Select yylb 语言类别语言类别,count(*) 考生人数考生

4、人数,max(bs) 笔试最高分笔试最高分,;min(bs) 笔试最低分笔试最低分, avg(bs) 笔试平均分笔试平均分; from 考试考试 group by yylb into table 成绩统计成绩统计常用函数：常用函数：COUNT- 计数计数 & 对数据表中的记录进行计数对数据表中的记录进行计数COUNT(*)，一般，一般COUNT函数应该使用函数应该使用DISTINCT （DISTINCT短语表示去掉重复记录）短语表示去掉重复记录）SUM- 求和求和 AVG- 计算平均值计算平均值 MAX- 求最大值求最大值 MIN- 求最小值求最小值 这些函数可以用在这些函数可以用在S

5、ELECT短语中对查询结果进行计算。短语中对查询结果进行计算。（参加考试人数在（参加考试人数在20人以上的语言类别的成绩情况）人以上的语言类别的成绩情况）第10页例例5：在图书表中，查询所藏图书中，有三种及三种以上的图书在图书表中，查询所藏图书中，有三种及三种以上的图书出版社所出版图书的最高单价和平均单价。出版社所出版图书的最高单价和平均单价。SELE cbdw 出版单位出版单位,MAX(dj) 最高单价最高单价,AVG(dj) 平均单价平均单价 ;FROM 图书图书 GROUP BY cbdw; HAVING COUNT (DISTINCT zbh)=3第11页例例6：查找与：查找与“胡娟胡

6、娟”同龄的考生姓名、年龄和成绩。同龄的考生姓名、年龄和成绩。SELE xm,nl,bs,js FROM 考试考试 WHERE nl=;(SELE nl FROM 考试考试 WHERE xm=胡娟胡娟) 5. 嵌套查询嵌套查询第12页6. 多表内联查询多表内联查询 例例7：在图书管理数据库中，：在图书管理数据库中，查询查询已借出书籍读者的借书证号、已借出书籍读者的借书证号、姓名和单位以及借出图书的书名，单价，借书日期。姓名和单位以及借出图书的书名，单价，借书日期。SELE a.jszh,a.xm,a.dw,b.sm,b.dj,c.jsrq;FROM 读者读者 A,图书图书 B,借阅借阅 C;WH

7、ERE (a.jszh=c.jszh) AND (b.zbh=c.zbh)SELECT 读者读者.jszh, 读者读者.xm,读者读者.dw, 图书图书.sm, 图书图书.dj, 借阅借阅.jsrq;FROM 图书管理图书管理!读者读者 INNER JOIN 图书管理图书管理!借阅借阅;INNER JOIN 图书管理图书管理!图书图书 ;ON 借阅借阅.zbh = 图书图书.zbh ;ON 读者读者.jszh = 借阅借阅.jszh在在“查询设计器查询设计器”中自动生成的中自动生成的SQL命令是：命令是：内部联接：内部联接：INNER JOIN等价于等价于JOIN普通联接，只有满足联接条件的记

8、录才出普通联接，只有满足联接条件的记录才出现在查询结果中。现在查询结果中。例例8：在图书管理数据库中，求会计学院借阅图书的读者的人数。：在图书管理数据库中，求会计学院借阅图书的读者的人数。 SELE dw 单位单位,COUNT(DISTINCT 借阅借阅.jszh) 借书人数借书人数; FROM 借阅借阅,读者读者; WHERE 借阅借阅.jszh=读者读者.jszh AND 读者读者.jszh IN; (SELECT 读者读者.jszh FROM 读者读者 WHERE dw=会计学院会计学院) SELE xm 姓名姓名,dw 单位单位 FROM 读者读者 WHERE NOT EXISTS;

9、(SELE* FROM 借阅借阅 WHERE 借阅借阅.jszh=读者读者.jszh); ORDER BY dw 例例9：对于图书管理数据库，检索没有借阅任何图书的读者的姓：对于图书管理数据库，检索没有借阅任何图书的读者的姓名和所在单位。名和所在单位。 SELE dw 单位单位,COUNT(DISTINCT 借阅借阅.jszh) 借书人数借书人数; FROM 借阅借阅,读者读者; WHERE 借阅借阅.jszh=读者读者.jszh AND dw=会计学院会计学院7. 利用利用BETWEEN、IN、LIKE的查询语句的查询语句例例10：显示所有笔试成绩在：显示所有笔试成绩在(6080)分分考生信

10、息。考生信息。SELE * FROM 考试考试;WHERE bs BETW 60 AND 80 例例11：显示所有参加：显示所有参加“二级二级VB”和和“二级二级VF”的考生。的考生。SELE * FROM 考试考试 WHERE yylb IN; (二级二级VB,二级二级VF)等价于等价于 bs=60 AND bs=80 等价于等价于 yylb=二级二级VB“ OR yylb=二级二级VF 第15页例例13：显示所有名字中有：显示所有名字中有“丽丽”的考生成绩。的考生成绩。SELE xm,bs,js FROM 考试考试;WHERE xm LIKE %丽丽%例例12：显示所有姓：显示所有姓“李李

11、”的考生成绩。的考生成绩。SELE * FROM 考试考试 WHERE xm LIKE 李李%SELE * FROM 考试考试 WHERE xm LIKE 李李_SELE * FROM 考试考试 WHERE xm LIKE 李李_等价于等价于 xm= 李李 等价于等价于 丽丽 $ xm 8. 显示部分记录显示部分记录例例14: 显示笔试成绩最高的三位考生的记录。显示笔试成绩最高的三位考生的记录。SELE * TOP 3 FROM 考试考试 ORDER BY bs DESC例例15: 显示笔试成绩最低的那显示笔试成绩最低的那30%考生的记录。考生的记录。SELE * TOP 30 PERCENT

12、 FROM 考试考试;ORDER BY bs 第17页第18页 4.3.1 建立数据表建立数据表 CREAT TABLE|DBF FREE( (,),) NULL|NOT NULL CHECK ERROR 字符表达式字符表达式 DEFAULT PRIMARY KEY|UNIQUE REFERECES TAG ,FOREIGN KEY TAG REFERENCE TAG 第19页例：新建一个学生管理数据库，在该数据库中建立学例：新建一个学生管理数据库，在该数据库中建立学生表生表stud.dbf (学号、姓名、性别、出生日期、籍贯、学号、姓名、性别、出生日期、籍贯、地址、家庭月收入、系别代号地址、

13、家庭月收入、系别代号)和成绩表和成绩表cj.dbf(学号、学号、计算机、英语、会计、法学、总分、平均分计算机、英语、会计、法学、总分、平均分)，并且将并且将两个表按两个表按“学号学号”建立一对多的联系。建立一对多的联系。CREAT DATA 学生管理学生管理 第20页& 学号为主索引且不能为空值，对家庭月收入字段学号为主索引且不能为空值，对家庭月收入字段说明了字段有效性规则及出错信息。说明了字段有效性规则及出错信息。CREAT TABLE stud (学号学号 c(4) PRIM KEY NOT; NULL,姓名姓名 c(6),性别性别 c(2),出生日期出生日期 d,籍贯籍贯 c(8

14、),地址地址; c(40),家庭月收入家庭月收入 n(8,2) CHECK 家庭月收入家庭月收入=0;ERROR 家庭月收入应大于等于零家庭月收入应大于等于零,系别代号系别代号 c(2)第21页 CREAT TABLE cj (学号学号 c(10),计算机计算机 n(5,1),;英语英语 n(5,1),会计会计 n(5,1),法学法学 n(5,1),总分总分 n(3),;平均分平均分 n (5,1), FOREIGN KEY 学号学号; TAG 学号学号 REFERENCES stud) & FOREIGN KEY 学号学号 短语表明短语表明在该表的学号字段在该表的学号字段上建立一个普

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

16、。进行操作。 命令格式：命令格式： DROP TABLE | ？RECYCLE 命令功能：删除指定的表。命令功能：删除指定的表。 ?参数用于在参数用于在“删除删除”对话框中选择要删除的表名。对话框中选择要删除的表名。 RECYCLE：用于将删除的表放入回收站。用于将删除的表放入回收站。第24页 例例1：增加电话字段：增加电话字段 ALTER TABLE stud ADD 电话电话 c(10) 例例2：修改姓名字段的宽度：修改姓名字段的宽度 ALTER TABLE stud ALTER 姓名姓名 c(10) 4.3.3 修改数据表修改数据表 ALTER TABLE ADD|ALTER (,) A

17、LTER TABLE DROP ALTER TABLE RENAME COLUMN TO 第25页例例3：删除电话字段：删除电话字段ALTER TABLE stud DROP 电话电话 例例4：将字段名由地址改为家庭地址：将字段名由地址改为家庭地址ALTER TABLE stud RENAME COLUMN 地址地址 TO;家庭地址家庭地址 例例5：修改或定义成绩表的英语字段的有效性规则。：修改或定义成绩表的英语字段的有效性规则。ALTER TABLE cj ALTER 英语英语 SET CHECK; 英语英语=0 ERROR “成绩不应小于零！成绩不应小于零！” 第26页例例6：删除学生表的

18、家庭月收入字段的有效性规则。：删除学生表的家庭月收入字段的有效性规则。ALTER TABLE stud ALTER 家庭月收入家庭月收入 ;DROP CHECK例例7：将学生表中姓名和出生日期定义为候选索引，索：将学生表中姓名和出生日期定义为候选索引，索引标记名为引标记名为“xmrqsy”。ALTER TABLE stud ADD UNIQUE ;姓名姓名+DTOC(出生日期出生日期)TAG xmrqsy 例例8：删除学生表中索引标记为：删除学生表中索引标记为“xmrqsy”的候选索引的候选索引。 ALTER TABLE stud DROP UNIQUE TAG xmrqsy 第27页例例9：

19、删除成绩表与学生表两个表之间的永久关系。：删除成绩表与学生表两个表之间的永久关系。 ALTER TABLE cj DROP FOREIGN KEY TAG 学号学号 例例10：重新建立成绩表与学生表两个表之间一对多的永：重新建立成绩表与学生表两个表之间一对多的永久关系。久关系。 ALTER TABLE cj ADD FOREIGN KEY 学号学号; TAG 学号学号 REFERENCES stud 第28页第29页 例：为例：为stud表添加记录的全部字段数据。表添加记录的全部字段数据。 INSERT INTO stud VALU (0110, 李建国李建国, 男男,;1981.02.06,

20、 湖南湖南, 湖大北校区湖大北校区39栋栋,2345.00, 01) & 数据的个数和顺序和表结构的字段个数和顺序必须一致。数据的个数和顺序和表结构的字段个数和顺序必须一致。例：为例：为stud表添加记录的部分字段数据。表添加记录的部分字段数据。 INSERT INTO stud (学号学号,籍贯籍贯,性别性别, 姓名姓名) VALU ; (0111, 湖北湖北, 女女, 王丽丽王丽丽) & 数据的顺序和字段名表中指定的顺序必须一致。数据的顺序和字段名表中指定的顺序必须一致。4.4.1 插入记录插入记录 INSE INTO () VALUE()第30页 4.4.2 更新记录更新

21、记录 UPDATE SET = ，= ,. WHERE 例：将例：将“考试考试.dbf”表中的表中的“yylb”字段的字段的“二级二级VF”改为改为“二级二级Visual FoxPro” 。 UPDATE 考试考试 SET yylb=二级二级Visual FoxPro; WHERE yylb=二级二级VF例：例：将将“考试考试.dbf”的的jl字段按分数添入优秀、及格、补字段按分数添入优秀、及格、补考和不及格结论。考和不及格结论。 UPDATE 考试考试 SET JL= 优秀优秀 WHERE bs=90; AND js=90 第31页 4.4.3 删除记录删除记录 DELETE FROM WH

22、ERE 例：删除不及格考生记录。例：删除不及格考生记录。 DELE FROM 考试考试 WHERE bs60 and js200 GROUP BY 产地产地 ;HAVING COUNT(*)=2 ORDER BY 2 DESC 查询结果的第一条记录的产地和提供的商品种类数是查询结果的第一条记录的产地和提供的商品种类数是 A) 北京北京,1 B) 上海上海,2 C) 广东广东,5 D) 广东广东,7(3) SQL语句语句SELE 部门表部门表.部门号部门号,部门名称部门名称,SUM(单价单价*数量数量) FROM部门表部门表,商品表商品表 WHERE 部门表部门表.部门号部门号=商品表商品表.部

23、门号部门号GROUP BY 部门表部门表.部门号部门号各部门商品数量合计各部门商品数量合计 B)各部门商品金额合计各部门商品金额合计C) 所有商品金额合计所有商品金额合计 D)各部门商品金额平均值各部门商品金额平均值第43页(4) SQL语句语句SELE 部门表部门表.部门号部门号,部门名称部门名称,商品号商品号,商品名称商品名称,单价单价FROM 部门表部门表,商品表商品表 WHERE 部门表部门表.部门号部门号=商品表商品表.部门号部门号 ORDER BY 部门表部门表.部门号部门号 DESC,单价单价查询结果的第一条记录的商品号是查询结果的第一条记录的商品号是 A) 0101 B) 02

24、02 C) 0110 D) 0112(5) SQL语句语句SELE 部门名称部门名称 FROM 部门表部门表 WHERE 部门号部门号 IN; (SELE 部门号部门号 FROM 商品表商品表 WHERE 单价单价 BETW ;420 AND 1000)家用电器部、电话手机部家用电器部、电话手机部 B) 家用电器部、计算机家用电器部、计算机C) 电话手机部、电视录摄象机部电话手机部、电视录摄象机部D) 家用电器部、电视录摄象机部家用电器部、电视录摄象机部第44页例例:在标准在标准SQL中，建立视图的命令是中，建立视图的命令是(A) CREATE SCHEMA (B) CREATE TABLE

25、(C) CREATE VIEW (D) CREATE INDEX例：在例：在SQL语言中，修改基本表的语句是语言中，修改基本表的语句是 (A) CREATE TABLE (B) DROP TABLE (C) ALTER TABLE (D) UPDATE TABLE 例：例：SQL语言是语言是 (A) 高级语言高级语言 (B) 结构化查询语言结构化查询语言 (C) 第三代语言第三代语言 (D) 宿主语言宿主语言第45页例：例：SQL实现分组查询的短语是实现分组查询的短语是(A) ORDER BY (B) GROUP BY (C) HAVING (D) ASC例：在例：在SQL的计算查询中，用于求

26、平均值的函数是的计算查询中，用于求平均值的函数是(A) AVG (B) AVERAGE (C) average (D) AVE例：例：SQL语句中的语句中的SELECT命令建立表之间联系的短语为命令建立表之间联系的短语为(A) UNIQE (B) GROUP BY (C) JOIN (D) FROM例例:下面所列条目中，错误的下面所列条目中，错误的SQL语句是语句是(A) ALTER TABLE (B) ALTER VIEW (C) CREATE TABLE (D) CREATE VIEW例：例：SQL语句中修改表结构的命令是语句中修改表结构的命令是(A) MODIFY TABLE (B) M

27、ODIFY STRUCTURE (C) ALTER TABLE (D) ALTER STRUCTURE第46页例：用于显示部分查询结果的例：用于显示部分查询结果的TOP短语，必须同时使用的短语是：短语，必须同时使用的短语是：(A) ORDER BY (B) FROM (C) WHERE (D) GROUP BY例：用例：用SQL语句建立表时将属性定义为主关键字，应使用短语语句建立表时将属性定义为主关键字，应使用短语(A) CHECK (B) PRIMARY KEY (C) FREE (D) UNIQUE例：例：HAVING短语不能单独使用，必须接在下列哪个短语之后短语不能单独使用，必须接在下列

28、哪个短语之后(A) ORDER BY (B) FROM (C) WHERE (D) GROUP BY例：例：SQL的核心是的核心是(A) 数据查询数据查询 (B) 数据修改数据修改 (C) 数据定义数据定义 (D) 数据控制数据控制例：将查询结果放在数组中应使用短语是例：将查询结果放在数组中应使用短语是 (A) INTO CURSOR (B) TO ARRAY (C) INTO TABLE (D) INTO ARRAY第47页例：例：SQL的数据操作语句不包括的数据操作语句不包括(A) INSERT (B) UPDATE (C) DELETE (D) CHANGE例：例：SQL 的查询语句中，实现关系的投影操作的短语是：的查询语句中，实现关系的投影操作的短语是：(A) WHERE (B) SELECT (C) FROM (D) GROUP BY例：下面有关例：下面有关HAVING子句描述错误的是子句描述错误的是(A) HAVING子句必须与子句必须与GROUP BY 子句同时使用，不能单独使用子句同时使用，不能单独使用(B) 使用使用HAVING子句的同时不能使用子句的同时不能使用