结构化查询语言.ppt

2019/7/2,第五章 结构化查询语言SQL,1,本章内容简介 本章主要介绍SQLSELECT语句，以及如何使用语句来完成数据的查询提取工作。通过实例详尽地描绘Visual FoxPro中SQL-SELECT语句的使用。 学习目标 要求熟练掌握SQLSELECT语句的单表查询功能，能熟练写出单表查询的SELECT语句及各子句。 学习切入点及方法 本章的核心是SELECT语句，它有很多子句，学习时，可由浅入深，从FROM子句开始，逐个子句进行学习，然后再逐步组合起来，形成比较复杂的语句。此外，对所有示例都要反复思考，上机操作实践，才能按要求写出查询的SELECT语句。 建议学时 建议课堂讲授810学时，上机10 12学时。,第五章 结构化查询语言SQL,2019/7/2,第五章 结构化查询语言SQL,2,SQL语言具有如下优点： （1）非过程化语言 （2）统一的语言 （3）语言简洁，易学易用 （4）同一种语言，两种使用方式 SQL语言的命令动词,5.1 SQL概 述,SQL(Structured Query Language) 结构化查询语言，其功能包括查询、操纵、定义和控制4个方面，是一种通用的功能极强的关系数据库标准语言。,2019/7/2,第五章 结构化查询语言SQL,3,5.2 SQL的操作功能,5.2.1 插入,格式1： INSERT INTO dbf_name (fname1 , fname2, .) VALUES (eExpression1 , eExpression2, .) 此格式在指定表的尾部插入由VALUES (eExpression1 , eExpression2, .) 给出具体值的记录。,例5.1 为参赛成绩表增加新的记录。 INSERT INTO 参赛成绩 VALUES(27, 中国, 四强) 例5.2 在基本信息表中加入新的届次信息。 INSERT INTO 基本信息(届次,日期,举办国,点球决胜); VALUES(19,2010-08-08, 南非,.T.),2019/7/2,第五章 结构化查询语言SQL,4,5.2 SQL的操作功能,5.2.1 插入,格式2： INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR 由指定数组或内存变量的值在指定表的尾部添加一条新记录。 FROM ARRAY短语是将一维数组元素值作为插入记录的数据 FROM MEMVAR短语是将同名的内存变量值作为插入记录的数据 如果同名的内存变量不存在，则对应的字段为默认值或为空。,例5.3 利用数组在基本信息表中加入新的届次信息。 DIMENSION arr1(3) arr1 (1) = 22 arr1 (2) = 2022-08-06 arr1 (3) = 中国 INSERT INTO 基本信息 FROM ARRAY arr1 例5.4 利用同名内存变量在基本信息表中加入新的信息。,2019/7/2,第五章 结构化查询语言SQL,5,5.2 SQL的操作功能,5.2.2 更新,格式： UPDATE DatabaseName1!TableName1 SET Column_Name1 = eExpression1 , Column_Name2 = eExpression2 WHERE FilterCondition1 AND | OR FilterCondition2 . 对于指定表中的符合条件的记录，用指定的表达式值更新指定的字段值。,例5.5 修改参赛成绩表中成绩字段的值，冠军用01来表示。 UPDATE 参赛成绩 SET 成绩=01 WHERE 成绩=冠军,2019/7/2,第五章 结构化查询语言SQL,6,5.2 SQL的操作功能,5.2.3 删除,格式： DELETE FROM DatabaseName!TableName WHERE FilterCondition1 AND | OR FilterCondition2 对表中符合条件的记录进行逻辑删除,例5.6 删除基本信息表中例5.3插入的第28届信息的记录。 DELETE FROM 基本信息 WHERE 届次=”28”,思考：试比较一下SQL的插入、更新和删除命令与Visual FoxPro的命令有什么不同？上述几个示例，利用Visual FoxPro自己提供的命令如何实现？,2019/7/2,第五章 结构化查询语言SQL,7,5.3 SQL的查询功能,格式： SELECT ALL | DISTINCT TOP nExpr PERCENT Alias. Select_Item AS Column_Name , Alias. Select_Item AS Column_Name . FROM FORCE DatabaseName!Table AS Local_Alias INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN DatabaseName!Table AS Local_Alias ON JoinCondition INTO Destination | TO FILE FileName ADDITIVE | TO PRINTER PROMPT | TO SCREEN WHERE JoinCondition AND JoinCondition . AND | OR FilterCondition AND | OR FilterCondition . GROUP BY GroupColumn , GroupColumn . HAVING FilterCondition UNION ALL SELECTCommand ORDER BY Order_Item ASC | DESC , Order_Item ASC | DESC .,2019/7/2,第五章 结构化查询语言SQL,8,5.3 SQL的查询功能,基本的形式由SELECT、FROM和WHERE三个子句组成，并具有如下形式： SELECT FROM WHERE 是一个属性名（即字段名）的列表，说明要查询的数据。 是一个该查询涉及的表名列表，说明要查询的数据来自哪个或哪些表，可以对单个表或多个表进行查询。 是一个条件表达式，说明查询条件，即选择元组的条件。,GROUP BY 短语：指明对查询结果进行分组输出，而其中的HAVING子句用来指定每一个分组应满足的条件，只有满足此条件的分组才出现在结果中。 HAVING子句不能单独使用。 ORDER BY短语：指明对查询结果进行排序后输出。从概念上讲，此子句最后使用，是对查询结果的排序。,2019/7/2,第五章 结构化查询语言SQL,9,5.3 SQL的查询功能,5.3.1 单表的查询,一、选择列 例5.7 查询历届世界杯的冠军。 SELECT 届次, 冠军; FROM 基本信息 例5.8 查询历届世界杯的全部信息。 SELECT * FROM 基本信息 用“*”号代表所有的字段，显示顺序和表结构中的顺序一致。 例5.9 查询历届世界杯举办的月份。 SELECT 届次, 开幕的月份是: , MONTH(日期) AS 月份 FROM 基本信息,2019/7/2,第五章 结构化查询语言SQL,10,5.3 SQL的查询功能,5.3.1 单表的查询,二、选择行 例5.10 查询举办过世界杯的国家。 SELECT 举办国 FROM 基本信息 SELECT DISTINCT 举办国 FROM 基本信息 例5.11 查询通过点球决胜的世界杯届次。 SELECT 届次 FROM 基本信息 WHERE 点球决胜 或 SELECT 届次 FROM 基本信息; WHERE 点球决胜=.t. 例5.12 查询所有进入过四强的参赛队。 SELECT DISTINCT 参赛队 四强球队 FROM 参赛成绩; WHERE 成绩= 冠军 OR 成绩= 四强 例5.13 查询最近五届世界杯的开幕日期、举办国和冠军队。 SELECT 届次,日期,举办国,冠军 FROM 基本信息; WHERE val(届次) BETWEEN 14 AND 18,2019/7/2,第五章 结构化查询语言SQL,11,5.3 SQL的查询功能,5.3.1 单表的查询,二、选择行 例5.14 查询第一届和第五届的信息。 SELECT * FROM 基本信息 WHERE 届次 IN ( 01 , 05 ） 例5.15 查询在德国（包括东德、西德和现在的德国）举办的所有世界杯的信息。 SELECT * FROM 基本信息 WHERE 举办国 LIKE %德% 例5.16 查询所在地区不明确的参赛队。 SELECT * FROM 参赛队 WHERE 地区 IS NULL 利用“基本信息视图”查询没有评选最佳球员的届次： SELECT 届次,日期,举办国 FROM 基本信息视图 WHERE 姓名 IS NULL 例5.17 查询意大利不作为东道主而夺冠的历届世界杯信息。 SELECT * FROM 基本信息; WHERE 冠军= 意大利 AND NOT 举办国= 意大利,2019/7/2,第五章 结构化查询语言SQL,12,5.3 SQL的查询功能,5.3.1 单表的查询,三、使用集函数查询 常用的集函数,2019/7/2,第五章 结构化查询语言SQL,13,5.3 SQL的查询功能,5.3.1 单表的查询,三、使用集函数查询 例5.18 查询共有多少支球队参加过世界杯。 SELECT COUNT(*) 球队总数 FROM 参赛队 例5.19 统计共有多少支球队获得过冠军。 SELECT COUNT(DISTINCT 参赛队) 冠军球队数 FROM 参赛成绩; WHERE 成绩= 冠军 例5.20 统计历届世界杯中最佳射手进球数的最多、最少和平均值分别是多少个进球。 SELECT MAX(进球数), MIN(进球数) ; AVG(进球数); FROM 最佳射手,思考：本例查询中AVG函数的参数部分是否应该加“DISTINCT”短语？考虑到最佳射手表中有进球数相同的情况，怎样计算才合理？,2019/7/2,第五章 结构化查询语言SQL,14,5.3 SQL的查询功能,5.3.1 单表的查询,四、分组查询 例5.21 统计每支球队参加世界杯的次数。 SELECT 参赛队,COUNT(*) AS 参加次数; FROM 参赛成绩 GROUP BY 参赛队 例5.22 查询参加过十次(含十次)以上世界杯的球队。 SELECT 参赛队,COUNT(*) AS 参加次数; FROM 参赛成绩 ; GROUP BY 参赛队 HAVING 参加次数=10 或者： SELECT 参赛队, COUNT(*) AS 参加次数; FROM 参赛成绩 ; GROUP BY 参赛队 HAVING COUNT(*)=10,2019/7/2,第五章 结构化查询语言SQL,15,5.3 SQL的查询功能,5.3.1 单表的查询,五、查询结果进行排序 例5.23 按举办国升序排列基本信息表中的记录，举办国相同的按届次降序排列。 SELECT 举办国, 届次, 日期 FROM 基本信息 ; ORDER BY 举办国 ASC, 届次 DESC 例5.24 统计球队夺冠次数，并按夺冠次数降序排列。 SELECT 冠军 AS 国家, COUNT(*) 夺冠次数; FROM 基本信息; GROUP BY 冠军; ORDER BY 夺冠次数 DESC,2019/7/2,第五章 结构化查询语言SQL,16,5.3 SQL的查询功能,5.3.2 联结查询,例5.25 查询历届世界杯冠军和该届最佳球员。 SELECT 基本信息.届次,冠军,姓名 最佳球员; FROM 基本信息,最佳球员; WHERE 基本信息.届次=最佳球员.届次 例5.26 查询历史上进入世界杯十六强的亚洲球队和非洲球队。 SELECT DISTINCT 参赛队.参赛队,地区; FROM 参赛队,参赛成绩; WHERE 参赛队.参赛队=参赛成绩.参赛队 AND (地区= 亚洲 OR 地区= 非洲); AND NOT EMPTY(成绩),2019/7/2,第五章 结构化查询语言SQL,17,5.3 SQL的查询功能,5.3.2 联结查询,例5.27 查询历届世界杯中，最佳球员和最佳射手是同一人，并且这名球员来自于冠军队的相关球队和球员。 SELECT X.届次,冠军,Y.姓名 最佳射手,Z .姓名 最佳球员; FROM 基本信息 AS X, 最佳射手 Y , 最佳球员 Z; WHERE X.届次=Y.届次 AND Y.届次=Z.届次; AND Y.姓名=Z.姓名 AND Y.国籍=X.冠军 例5.28 在获得过世界杯冠军的国家举办的世界杯信息。 SELECT DISTINCT Y.举办国,Y.届次,Y.日期; FROM 基本信息 X, 基本信息 Y; WHERE X.冠军=Y.举办国,2019/7/2,第五章 结构化查询语言SQL,18,5.3 SQL的查询功能,5.3.2 联结查询,例5.29 使用以上格式查询历届世界杯中既是最佳球员又是最佳射手的球员。 SELECT 最佳球员.姓名 最佳球员,最佳射手.姓名 最佳射手; FROM 最佳球员 INNER JOIN 最佳射手 ON 最佳球员.姓名=最佳射手.姓名 例5.30 左联接。 SELECT 最佳球员.姓名 最佳球员,最佳射手.姓名 最佳射手; FROM 最佳球员 LEFT JOIN 最佳射手 ON 最佳球员.姓名=最佳射手.姓名 例5.31 右联接。 SELECT 最佳球员.姓名 最佳球员,最佳射手.姓名 最佳射手; FROM 最佳球员 RIGHT JOIN 最佳射手 ON 最佳球员.姓名=最佳射手.姓名 例5.32 完全联接。 SELECT 最佳球员.姓名 最佳球员,最佳射手.姓名 最佳射手; FROM 最佳球员 FULL JOIN 最佳射手 ON 最佳球员.姓名=最佳射手.姓名,2019/7/2,第五章 结构化查询语言SQL,19,5.3 SQL的查询功能,5.3.3 嵌套查询,一、不相关的嵌套查询 例5.33 查询亚洲球队在历届世界杯比赛中的成绩情况。 SELECT * FROM 参赛成绩 WHERE 参赛队 IN; (SELECT 参赛队 FROM 参赛队 WHERE 地区=“亚洲“) 例5.34 查询从未进入过十六强的球队。 SELECT *; FROM 参赛队 WHERE 参赛队 NOT IN; (SELECT 参赛队 FROM 参赛成绩 WHERE NOT EMPTY(成绩),2019/7/2,第五章 结构化查询语言SQL,20,5.3 SQL的查询功能,5.3.3 嵌套查询,二、相关的嵌套查询 例5.35 查询连续两次都进入决赛的球队。 SELECT 参赛队,届次,STR(VAL(届次)+1,2) 届次2; FROM 参赛成绩 X ; WHERE 成绩 IN (“冠军“,“亚军“) ; AND VAL(届次)+1 IN; (SELECT VAL(届次) ; FROM 参赛成绩 Y ; WHERE X.参赛队=Y.参赛队 ; AND 成绩 IN (“冠军“,“亚军“),2019/7/2,第五章 结构化查询语言SQL,21,5.3 SQL的查询功能,5.3.3 嵌套查询,三、带有EXISTS谓词的嵌套查询 例5.36 查询曾经有队员获得过最佳球员的球队信息。 SELECT * FROM 参赛队; WHERE EXISTS; (SELECT * FROM 最佳球员 WHERE 最佳球员.国籍=参赛队.参赛队 ) 四、 量词ANY和ALL的使用 例5.37 查询进球数超过巴西任何最佳射手进球数的其他最佳射手。 SELECT *; FROM 最佳射手; WHERE 进球数ALL; (SELECT 进球数 FROM 最佳射手 WHERE 国籍=“巴西“),2019/7/2,第五章 结构化查询语言SQL,22,5.3 SQL的查询功能,5.3.4 集合的并运算,例如： SELECT 参赛队 FROM 参赛队 WHERE 地区= 亚洲 ； UNION； SELECT 参赛队 FROM 参赛队 WHERE 地区= 非洲 以上语句的结果是亚洲和非洲的所有参赛队。,2019/7/2,第五章 结构化查询语言SQL,23,5.3 SQL的查询功能,5.3.5 几个特殊选项,1显示部分结果 例5.38 查询参赛次数排在前三位的球队。 SELECT TOP 3 参赛队,地区,COUNT(*) 参赛次数; FROM 参赛成绩,参赛队; WHERE 参赛成绩.参赛队=参赛队.参赛队; GROUP BY 参赛队; ORDER BY 参赛次数 DESC 例5.39 查询参赛次数排在前10%的球队。 SELECT TOP 10 PERCENT 参赛队.参赛队,地区,COUNT(*) 参赛次数; FROM 参赛成绩,参赛队; WHERE 参赛成绩.参赛队=参赛队.参赛队; GROUP BY 参赛队.参赛队; ORDER BY 参赛次数 DESC,2019/7/2,第五章 结构化查询语言SQL,24,5.3 SQL的查询功能,5.3.5 几个特殊选项,2指定查询结果去向 默认情况下，查询结果在“浏览”窗口中显示。 INTO ARRAY ArrayName：将查询结果存放在数组中。 如： SELECT * FROM 基本信息 INTO ARRAY arr1 INTO CURSOR CursorName：将查询结果存放到临时表中。 INTO DBF|TABLE TableName：将查询结果存放到永久表（DBF文件）中。 TO FILE FileName ADDITIVE：将查询结果存放到文本文件中，如果使用ADDITIVE，则是追加的方式，否则覆盖原有文件。 TO PRINTER PROMPT：将查询结果输出到打印机。如果使用PROMPT选项，在开始打印前会打开打印机设置对话框。,2019/7/2,第五章 结构化查询语言SQL,25,5.4 数据定义功能,5.4.1 建立表结构,例5.40 用命令建立基本信息数据库表，为了区别，文件名为基本信息1.dbf。 OPEN DATABASE 世界杯 CREATE TABLE 基本信息1(届次 C(2) PRIMARY KEY, ; 日期 D CHECK(MOD(YEAR(日期)-1930,4)=0); ERROR 世界杯每四年一次，第一届是1930年! ,举办国 C(20),; 队数 N(2),冠军 C(10),点球决胜 L, 举办背景 M, 照片 G , ; CHECK(YEAR(日期)=1930+(VAL(届次)-1)*4) ERROR 出错了！ ) 例5.41 用命令建立参赛队1表。 CREATE TABLE 参赛队1(参赛队 C(20) PRIMARY KEY, 地区 C(6),备注 M) 例5.42 建立参赛成绩1表，参赛成绩1表和基本信息1表、参赛队1表之间的关系。 CREATE TABLE 参赛成绩1(; 届次 C(2) REFERENCES 基本信息1 TAG 届次,; 参赛队 C(20),成绩 C(6), ; FOREIGN KEY 参赛队 TAG 参赛队 REFERENCES 参赛队1 TAG 参赛队,; PRIMARY KEY 届次+参赛队 TAG key1),2019/7/2,第五章 结构化查询语言SQL,26,5.4 数据定义功能,5.4.2 表结构的修改,例5.43 为参赛成绩表增加字段“成绩排序”。 ALTER TABLE 参赛成绩 ADD 成绩排序 C(2) 例5.44 修改参赛成绩表成绩排序字段的类型。 ALTER TABLE 参赛成绩 ALTER 成绩排序 N(2) 例5.45 为基本信息表中的“点球决胜”字段设置默认值“.F.”。 ALTER TABLE 基本信息 ALTER 点球决胜 SET DEFAULT .F. 例5.46 删除基本信息表中的“日期”字段约束条件。 ALTER TABLE 基本信息 ALTER 日期 DROP CHECK,2019/7/2,第五章 结构化查询语言SQL,27,5.4 数据定义功能,5.4.2 表结构的修改,例5.47 修改基本信息表中的冠军字段为夺冠球队。 ALTER TABLE 基本信息 RENAME COLUMN 冠军 TO 夺冠球队 例5.48 删除参赛成绩表中的成绩排序字段。 ALTER TABLE 参赛成绩 DROP 成绩排序 例5.49 删除参赛成绩表中的主索引。 ALTER TABLE 参赛成绩 DROP PRIMARY KEY 5.4.3 表的删除 删除表的命令是： DROP TABLE table_name,2019/7/2,第五章 结构化查询语言SQL,28,5.4 数据定义功能,5.4.4 使用SQL定义视图,一、从单个基本表创建视图 例5.50 在世界杯数据库中建立参赛球队数为32支的世界杯信息。 OPEN DATABASE 世界杯 CREAT VIEW 32支球队 AS ; SELECT