第5章结构化询语言SQL

1、11/12/2021第五章 结构化查询语言sql1本章内容简介 本章主要介绍sqlselect语句，以及如何使用语句来完成数据的查询提取工作。通过实例详尽地描绘visual foxpro中sql-select语句的使用。学习目标 要求熟练掌握sqlselect语句的单表查询功能，能熟练写出单表查询的select语句及各子句。学习切入点及方法 本章的核心是select语句，它有很多子句，学习时，可由浅入深，从from子句开始，逐个子句进行学习，然后再逐步组合起来，形成比较复杂的语句。此外，对所有示例都要反复思考，上机操作实践，才能按要求写出查询的select语句。建议学时 建议课堂讲授810学时

2、，上机10 12学时。 第五章 结构化查询语言sql11/12/2021第五章 结构化查询语言sql2 sql语言具有如下优点：语言具有如下优点：（1）非过程化语言）非过程化语言（2）统一的语言）统一的语言（3）语言简洁，易学易用）语言简洁，易学易用（4）同一种语言，两种使用方式）同一种语言，两种使用方式 sql语言的命令动词语言的命令动词sql 功能命令动词数据查询数据查询select数据定义数据定义creat，drop，alter数据操纵数据操纵insert，update，delete数据控制数据控制grant，revoke11/12/2021第五章 结构化查询语言sql35.2.1 插入

3、插入格式格式1：insert into dbf_name (fname1 , fname2, .)values (eexpression1 , eexpression2, .)此格式在指定表的尾部插入由此格式在指定表的尾部插入由values (eexpression1 , eexpression2, .) 给出具体值的记录。给出具体值的记录。例例5.1 为参赛成绩表增加新的记录。为参赛成绩表增加新的记录。insert into 参赛成绩参赛成绩 values(27, 中国中国, 四强四强) 例例5.2 在基本信息表中加入新的届次信息。在基本信息表中加入新的届次信息。insert into 基本

4、信息基本信息(届次届次,日期日期,举办国举办国,点球决胜点球决胜);values(19,2010-08-08, 南非南非,.t.) 11/12/2021第五章 结构化查询语言sql45.2.1 插入插入 格式格式2：insert into dbf_name from array arrayname | from memvar 由指定数组或内存变量的值在指定表的尾部添加一条新记录。由指定数组或内存变量的值在指定表的尾部添加一条新记录。 from array短语是将一维数组元素值作为插入记录的数据短语是将一维数组元素值作为插入记录的数据 from memvar短语是将同名的内存变量值作为插入记录的

5、数据短语是将同名的内存变量值作为插入记录的数据 如果同名的内存变量不存在，则对应的字段为默认值或为空。如果同名的内存变量不存在，则对应的字段为默认值或为空。例例5.3 利用数组在基本信息表中加入新的届次信息。利用数组在基本信息表中加入新的届次信息。dimension arr1(3)arr1 (1) = 22arr1 (2) = 2022-08-06arr1 (3) = 中国中国insert into 基本信息基本信息 from array arr1例例5.4 利用同名内存变量在基本信息表中加入新的信息。利用同名内存变量在基本信息表中加入新的信息。 11/12/2021第五章 结构化查询语言sq

6、l55.2.2 更新更新格式：格式：update databasename1!tablename1set column_name1 = eexpression1, column_name2 = eexpression2 where filtercondition1 and | or filtercondition2 .对于指定表中的符合条件的记录，用指定的表达式值更新指定的字段值。对于指定表中的符合条件的记录，用指定的表达式值更新指定的字段值。例例5.5 修改参赛成绩表中成绩字段的值，冠军用修改参赛成绩表中成绩字段的值，冠军用01来表示。来表示。update 参赛成绩参赛成绩 set 成绩成绩

7、=01 where 成绩成绩=冠军冠军11/12/2021第五章 结构化查询语言sql65.2.3 删除删除格式：格式：delete from databasename!tablenamewhere filtercondition1 and | or filtercondition2 对表中符合条件的记录进行逻辑删除对表中符合条件的记录进行逻辑删除例例5.6 删除基本信息表中例删除基本信息表中例5.3插入的第插入的第28届信息的记录。届信息的记录。delete from 基本信息基本信息 where 届次届次=”28” 思考：思考：试比较一下sql的插入、更新和删除命令与visual foxp

8、ro的命令有什么不同？上述几个示例，利用visual foxpro自己提供的命令如何实现？11/12/2021第五章 结构化查询语言sql7格式：格式：select all | distinct top nexpr percentalias. select_item as column_name, alias. select_item as column_name .from forcedatabasename!table as local_aliasinner | left outer | right outer | full outer joindatabasename!table as

9、local_aliason joincondition into destination| to file filename additive | to printer prompt | to screenwhere joincondition and joincondition .and | or filtercondition and | or filtercondition .group by groupcolumn , groupcolumn .having filterconditionunion all selectcommandorder by order_item asc |

10、desc , order_item asc | desc . 11/12/2021第五章 结构化查询语言sql8 基本的形式由select、from和where三个子句组成，并具有如下形式： select from where 是一个属性名（即字段名）的列表，说明要查询的数据。 是一个该查询涉及的表名列表，说明要查询的数据来自哪个或哪些表，可以对单个表或多个表进行查询。 是一个条件表达式，说明查询条件，即选择元组的条件。 group by 短语：指明对查询结果进行分组输出，而其中的having子句用来指定每一个分组应满足的条件，只有满足此条件的分组才出现在结果中。 having子句不能单独使用

11、。 order by短语：指明对查询结果进行排序后输出。从概念上讲，此子句最后使用，是对查询结果的排序。11/12/2021第五章 结构化查询语言sql95.3.1 单表的查询单表的查询一、选择列一、选择列例例5.7 查询历届世界杯的冠军。查询历届世界杯的冠军。select 届次届次, 冠军冠军;from 基本信息基本信息例例5.8 查询历届世界杯的全部信息。查询历届世界杯的全部信息。select * from 基本信息基本信息用用“*”号代表所有的字段，显示顺序和表结构中的顺序一致。号代表所有的字段，显示顺序和表结构中的顺序一致。例例5.9 查询历届世界杯举办的月份。查询历届世界杯举办的月份

12、。select 届次届次, 开幕的月份是开幕的月份是: , month(日期日期) as 月份月份 from 基本信息基本信息11/12/2021第五章 结构化查询语言sql105.3.1 单表的查询单表的查询 二、选择行二、选择行 例例5.10 查询举办过世界杯的国家。查询举办过世界杯的国家。 select 举办国举办国 from 基本信息基本信息 select distinct 举办国举办国 from 基本信息基本信息 例例5.11 查询通过点球决胜的世界杯届次。查询通过点球决胜的世界杯届次。 select 届次届次 from 基本信息基本信息 where 点球决胜点球决胜 或或 sele

13、ct 届次届次 from 基本信息基本信息; where 点球决胜点球决胜=.t. 例例5.12 查询所有进入过四强的参赛队。查询所有进入过四强的参赛队。 select distinct 参赛队参赛队 四强球队四强球队 from 参赛成绩参赛成绩; where 成绩成绩= 冠军冠军 or 成绩成绩= 四强四强 例例5.13 查询最近五届世界杯的开幕日期、举办国和冠军队。查询最近五届世界杯的开幕日期、举办国和冠军队。 select 届次届次,日期日期,举办国举办国,冠军冠军 from 基本信息基本信息; where val(届次届次) between 14 and 1811/12/2021第五章

14、 结构化查询语言sql115.3.1 单表的查询单表的查询二、选择行二、选择行例例5.14 查询第一届和第五届的信息。查询第一届和第五届的信息。select * from 基本信息基本信息 where 届次届次 in ( 01 , 05 ）例例5.15 查询在德国（包括东德、西德和现在的德国）举办的所有世界杯的信息。查询在德国（包括东德、西德和现在的德国）举办的所有世界杯的信息。select * from 基本信息基本信息 where 举办国举办国 like %德德% 例例5.16 查询所在地区不明确的参赛队。查询所在地区不明确的参赛队。select * from 参赛队参赛队 where 地

15、区地区 is null利用利用“基本信息视图基本信息视图”查询没有评选最佳球员的届次：查询没有评选最佳球员的届次：select 届次届次,日期日期,举办国举办国 from 基本信息视图基本信息视图 where 姓名姓名 is null例例5.17 查询意大利不作为东道主而夺冠的历届世界杯信息。查询意大利不作为东道主而夺冠的历届世界杯信息。select * from 基本信息基本信息;where 冠军冠军= 意大利意大利 and not 举办国举办国= 意大利意大利11/12/2021第五章 结构化查询语言sql125.3.1 单表的查询单表的查询 三、使用集函数查询三、使用集函数查询 常用的集

16、函数常用的集函数函数名功能sum（）计算指定数值列的总和avg（）计算指定数值列的平均值max（）求指定（数值、字符、日期）列的最大值min（）求指定（数值、字符、日期）列的最小值count（）求查询结果的一列中值的个数或元组（记录）数11/12/2021第五章 结构化查询语言sql135.3.1 单表的查询单表的查询三、使用集函数查询三、使用集函数查询例例5.18 查询共有多少支球队参加过世界杯。查询共有多少支球队参加过世界杯。select count(*) 球队总数球队总数 from 参赛队参赛队例例5.19 统计共有多少支球队获得过冠军。统计共有多少支球队获得过冠军。select cou

17、nt(distinct 参赛队参赛队) 冠军球队数冠军球队数 from 参赛成绩参赛成绩;where 成绩成绩= 冠军冠军例例5.20 统计历届世界杯中最佳射手进球数的最多、最少和平均值分别是多少个进球。统计历届世界杯中最佳射手进球数的最多、最少和平均值分别是多少个进球。select max(进球数进球数), min(进球数进球数) ; avg(进球数进球数);from 最佳射手最佳射手 思考：本例查询中思考：本例查询中avg函数的参数部分是否应该加函数的参数部分是否应该加“distinct”短语？考短语？考虑到最佳射手表中有进球数相同的情况，怎样计算才合理？虑到最佳射手表中有进球数相同的情况

18、，怎样计算才合理？ 11/12/2021第五章 结构化查询语言sql145.3.1 单表的查询单表的查询四、分组查询四、分组查询例例5.21 统计每支球队参加世界杯的次数。统计每支球队参加世界杯的次数。select 参赛队参赛队,count(*) as 参加次数参加次数;from 参赛成绩参赛成绩 group by 参赛队参赛队例例5.22 查询参加过十次查询参加过十次(含十次含十次)以上世界杯的球队。以上世界杯的球队。select 参赛队参赛队,count(*) as 参加次数参加次数;from 参赛成绩参赛成绩 ;group by 参赛队参赛队 having 参加次数参加次数=10或者：或

19、者：select 参赛队参赛队, count(*) as 参加次数参加次数;from 参赛成绩参赛成绩 ;group by 参赛队参赛队 having count(*)=10 11/12/2021第五章 结构化查询语言sql155.3.1 单表的查询单表的查询五、查询结果进行排序五、查询结果进行排序 例例5.23 按举办国升序排列基本信息表中的记录，举办国相同的按届次降序排列。按举办国升序排列基本信息表中的记录，举办国相同的按届次降序排列。select 举办国举办国, 届次届次, 日期日期 from 基本信息基本信息 ;order by 举办国举办国 asc, 届次届次 desc例例5.24

20、统计球队夺冠次数，并按夺冠次数降序排列。统计球队夺冠次数，并按夺冠次数降序排列。select 冠军冠军 as 国家国家, count(*) 夺冠次数夺冠次数;from 基本信息基本信息;group by 冠军冠军;order by 夺冠次数夺冠次数 desc 11/12/2021第五章 结构化查询语言sql165.3.2 联结查询联结查询例例5.25 查询历届世界杯冠军和该届最佳球员。查询历届世界杯冠军和该届最佳球员。select 基本信息基本信息.届次届次,冠军冠军,姓名姓名 最佳球员最佳球员;from 基本信息基本信息,最佳球员最佳球员;where 基本信息基本信息.届次届次=最佳球员最佳

21、球员.届次届次例例5.26 查询历史上进入世界杯十六强的亚洲球队和非洲球队。查询历史上进入世界杯十六强的亚洲球队和非洲球队。select distinct 参赛队参赛队.参赛队参赛队,地区地区;from 参赛队参赛队,参赛成绩参赛成绩;where 参赛队参赛队.参赛队参赛队=参赛成绩参赛成绩.参赛队参赛队 and (地区地区= 亚洲亚洲 or 地区地区= 非洲非洲);and not empty(成绩成绩)11/12/2021第五章 结构化查询语言sql175.3.2 联结查询联结查询例例5.27 查询历届世界杯中，最佳球员和最佳射手是同一人，并且这名球员来查询历届世界杯中，最佳球员和最佳射手是

22、同一人，并且这名球员来自于冠军队的相关球队和球员。自于冠军队的相关球队和球员。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

23、 基本信息基本信息 x, 基本信息基本信息 y;where x.冠军冠军=y.举办国举办国11/12/2021第五章 结构化查询语言sql185.3.2 联结查询联结查询例例5.29 使用以上格式查询历届世界杯中既是最佳球员又是最佳射手的球员。使用以上格式查询历届世界杯中既是最佳球员又是最佳射手的球员。select 最佳球员最佳球员.姓名姓名 最佳球员最佳球员,最佳射手最佳射手.姓名姓名 最佳射手最佳射手;from 最佳球员最佳球员 inner join 最佳射手最佳射手 on 最佳球员最佳球员.姓名姓名=最佳射手最佳射手.姓名姓名例例5.30 左联接。左联接。select 最佳球员最佳球员.

24、姓名姓名 最佳球员最佳球员,最佳射手最佳射手.姓名姓名 最佳射手最佳射手;from 最佳球员最佳球员 left join 最佳射手最佳射手 on 最佳球员最佳球员.姓名姓名=最佳射手最佳射手.姓名姓名例例5.31 右联接。右联接。select 最佳球员最佳球员.姓名姓名 最佳球员最佳球员,最佳射手最佳射手.姓名姓名 最佳射手最佳射手;from 最佳球员最佳球员 right join 最佳射手最佳射手 on 最佳球员最佳球员.姓名姓名=最佳射手最佳射手.姓名姓名例例5.32 完全联接。完全联接。select 最佳球员最佳球员.姓名姓名 最佳球员最佳球员,最佳射手最佳射手.姓名姓名 最佳射手最佳射

25、手;from 最佳球员最佳球员 full join 最佳射手最佳射手 on 最佳球员最佳球员.姓名姓名=最佳射手最佳射手.姓名姓名 11/12/2021第五章 结构化查询语言sql195.3.3 嵌套查询嵌套查询一、不相关的嵌套查询一、不相关的嵌套查询 例例5.33 查询亚洲球队在历届世界杯比赛中的成绩情况。查询亚洲球队在历届世界杯比赛中的成绩情况。select * from 参赛成绩参赛成绩 where 参赛队参赛队 in; (select 参赛队参赛队 from 参赛队参赛队 where 地区地区=亚洲亚洲)例例5.34 查询从未进入过十六强的球队。查询从未进入过十六强的球队。select

26、 *;from 参赛队参赛队 where 参赛队参赛队 not in;(select 参赛队参赛队 from 参赛成绩参赛成绩 where not empty(成绩成绩)11/12/2021第五章 结构化查询语言sql205.3.3 嵌套查询嵌套查询二、相关的嵌套查询二、相关的嵌套查询 例例5.35 查询连续两次都进入决赛的球队。查询连续两次都进入决赛的球队。select 参赛队参赛队,届次届次,str(val(届次届次)+1,2) 届次届次2;from 参赛成绩参赛成绩 x ;where 成绩成绩 in (冠军冠军,亚军亚军) ;and val(届次届次)+1 in;(select val(

27、届次届次) ;from 参赛成绩参赛成绩 y ;where x.参赛队参赛队=y.参赛队参赛队 ;and 成绩成绩 in (冠军冠军,亚军亚军)11/12/2021第五章 结构化查询语言sql215.3.3 嵌套查询嵌套查询三、带有三、带有exists谓词的嵌套查询谓词的嵌套查询 例例5.36 查询曾经有队员获得过最佳球员的球队信息。查询曾经有队员获得过最佳球员的球队信息。select * from 参赛队参赛队;where exists;(select * from 最佳球员最佳球员 where 最佳球员最佳球员.国籍国籍=参赛队参赛队.参赛队参赛队 )四、四、 量词量词any和和all的使

28、用的使用例例5.37 查询进球数超过巴西任何最佳射手进球数的其他最佳射手。查询进球数超过巴西任何最佳射手进球数的其他最佳射手。select *;from 最佳射手最佳射手;where 进球数进球数all;(select 进球数进球数 from 最佳射手最佳射手 where 国籍国籍=巴西巴西)11/12/2021第五章 结构化查询语言sql225.3.4 集合的并运算集合的并运算例如：例如：select 参赛队参赛队 from 参赛队参赛队 where 地区地区= 亚洲亚洲 ；union；select 参赛队参赛队 from 参赛队参赛队 where 地区地区= 非洲非洲以上语句的结果是亚洲和

29、非洲的所有参赛队。以上语句的结果是亚洲和非洲的所有参赛队。11/12/2021第五章 结构化查询语言sql235.3.5 几个特殊选项几个特殊选项1显示部分结果显示部分结果例例5.38 查询参赛次数排在前三位的球队。查询参赛次数排在前三位的球队。select top 3 参赛队参赛队,地区地区,count(*) 参赛次数参赛次数;from 参赛成绩参赛成绩,参赛队参赛队;where 参赛成绩参赛成绩.参赛队参赛队=参赛队参赛队.参赛队参赛队;group by 参赛队参赛队;order by 参赛次数参赛次数 desc例例5.39 查询参赛次数排在前查询参赛次数排在前10%的球队。的球队。sel

30、ect top 10 percent 参赛队参赛队.参赛队参赛队,地区地区,count(*) 参赛次数参赛次数;from 参赛成绩参赛成绩,参赛队参赛队;where 参赛成绩参赛成绩.参赛队参赛队=参赛队参赛队.参赛队参赛队;group by 参赛队参赛队.参赛队参赛队;order by 参赛次数参赛次数 desc11/12/2021第五章 结构化查询语言sql245.3.5 几个特殊选项几个特殊选项 2指定查询结果去向指定查询结果去向 默认情况下，查询结果在默认情况下，查询结果在“浏览浏览”窗口中显示。窗口中显示。 into array arrayname：将查询结果存放在数组中。：将查询结

31、果存放在数组中。 如：如： select * from 基本信息基本信息 into array arr1 into cursor cursorname：将查询结果存放到临时表中。：将查询结果存放到临时表中。 into dbf|table tablename：将查询结果存放到永久表（：将查询结果存放到永久表（dbf文件）文件）中。中。 to file filename additive：将查询结果存放到文本文件中，如：将查询结果存放到文本文件中，如果使用果使用additive，则是追加的方式，否则覆盖原有文件。，则是追加的方式，否则覆盖原有文件。 to printer prompt：将查询结果输

32、出到打印机。如果使用：将查询结果输出到打印机。如果使用prompt选项，在开始打印前会打开打印机设置对话框。选项，在开始打印前会打开打印机设置对话框。11/12/2021第五章 结构化查询语言sql255.4.1 建立表结构建立表结构 例例5.40 用命令建立基本信息数据库表，为了区别，文件名为基本信息用命令建立基本信息数据库表，为了区别，文件名为基本信息1.dbf。 open database 世界杯世界杯 create table 基本信息基本信息1(届次届次 c(2) primary key, ; 日期日期 d check(mod(year(日期日期)-1930,4)=0); error

33、 世界杯每四年一次，第一届是世界杯每四年一次，第一届是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表和基本信息表和基本信息

34、1表、参赛队表、参赛队1表之间的关系。表之间的关系。create table 参赛成绩参赛成绩1(;届次届次 c(2) references 基本信息基本信息1 tag 届次届次,;参赛队参赛队 c(20),成绩成绩 c(6), ;foreign key 参赛队参赛队 tag 参赛队参赛队 references 参赛队参赛队1 tag 参赛队参赛队,;primary key 届次届次+参赛队参赛队 tag key1) 11/12/2021第五章 结构化查询语言sql265.4.2 表结构的修改表结构的修改例例5.43 为参赛成绩表增加字段为参赛成绩表增加字段“成绩排序成绩排序”。alter t

35、able 参赛成绩参赛成绩 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 ch

36、eck11/12/2021第五章 结构化查询语言sql275.4.2 表结构的修改表结构的修改例例5.47 修改基本信息表中的冠军字段为夺冠球队。修改基本信息表中的冠军字段为夺冠球队。alter table 基本信息基本信息 rename column 冠军冠军 to 夺冠球队夺冠球队例例5.48 删除参赛成绩表中的成绩排序字段。删除参赛成绩表中的成绩排序字段。alter table 参赛成绩参赛成绩 drop 成绩排序成绩排序例例5.49 删除参赛成绩表中的主索引。删除参赛成绩表中的主索引。alter table 参赛成绩参赛成绩 drop primary key5.4.3 表的删除表的删除删除表的命令是：删除表的命令是：drop table table_name11/12/2021第五章 结构化查询语言sql285.4.4 使用使用sql定义视图定义视图一、从单个基本表创建视图一、从单个基本表创建视图 例例5.50 在世界杯数据库中建立参赛球队数为32支的世界杯信息。open database 世界杯creat view 32支球队 as ;select * from 基本信息 where 队数=32二、从多个基本表创建视图二、从多个基本表创建视图例例5.51 建立冠军球队的视图，视图中包括冠军队、地区和夺冠次数3