《Visual FoxPro 数据库应用教程》教学课件：第5章结构化查询语言SQL

1、第5章 结构化查询语言SQL SQL是结构化查询语言 Structured Query Language 的缩写。 5.1 SQL 的特点5.2 SQL的查询功能5.3 SQL的定义功能5.4 SQL的操作功能本章要点5.1 SQL 的特点SQL语言具有如下主要特点： SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成与数据库相关的全部工作。 SQL语言是一种高度非过程化的语言，用户只需要描述清楚要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。 SQL语言非常简洁。 SQL 语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言当

2、中以程式方式使用。5.2 SQL的查询功能 SQL语句创建查询使用的是Select语句 Select All|Distinct | , From Left Right Join On Where Order By Asc Desc Group By Having SELECT ALL|DISTINCT TOP nExpr PERCENTAlias. Select_Item AS Column_Name，Alias.Select_ItemAS Column_NameFROMFORCEDatabaseName！Table AS Local_AliasINNER | LEFT OUTER | RIG

3、HT OUTER |FULL OUTER JOINDatabaseName！Table AS Local_AliasON joinConditionINTO Destination| TO FILE FileName ADDITIVE | TO PRINTER PROMPT | TO SCREENPREFERENCE PreferenceNameNOCONSOLEPLAINNOWAITWHERE JoinCondition AND JoinConditionAND | OR FilterCondition AND | OR FilterConditionGROUP BY GroupColumn

4、 ，GroupColumnHAVING FilterConditionUNION ALL SELECTCommandORDER BY Order_ItemASC | DESC，Order_Item ASC | DESC 5.2.1 简单查询 由SELECT和FROM短语构成无条件查询，或 由SELECT,FROM和WHERE短语构成条件查询。例1 从cj表中检索所有同学的数学成绩。SELECT 学号 ,数学 FROM cj如果要去掉重复值只需要指定DISTINCT短语：SELECT DISTINCT 数学 FROM cj DISTINCT短语的作用是去掉查询结果中的重复值 例2 检索cj表中的

5、所有记录。SELECT * FROM cj例3 检索数学成绩高于80分的所有记录。SELECT * FROM cj WHERE 数学80用WHERE短语指定了查询条件，查询条件可以是任意复杂的逻辑表达式。例4 检索数学成绩高于80分而且英语成绩及格的所有记录。 SELECT * FROM cj WHERE 数学80 and 英语=60 5.2.2 简单的联接查询联接是关系的基本操作之一，联接查询是一种基于多个关系的查询。例5 检索数学成绩和英语成绩都及格的所有记录的学号，姓名和数学SELECT cj.学号，姓名，数学 FROM xsqk,cj ； WHERE 数学=60 and 英语=60 A

6、nd xsqk.学号=cj.学号 5.2.3 嵌套查询 查询的结果出自一个关系，但相关的条件却出自多个关系。例6 查询选课成绩及格的学生的姓名SELECT 姓名 FROM xsqk WHERE 学号 in (sele 学号 from xk where 成绩=60)这个命令中含有两个SELECT-FROM-WHERE查询块，即内层查询块和外层查询块，这里IN相当于集合运算符。例7 查询所有选课成绩都不及格的学生的姓名。SELECT 姓名 FROM xsqk WHERE 学号 not in; (sele 学号 from xk where 成绩=60) 注意到刚才的检索出现了错误，没有选课学生的信息

7、也被检索出来了。正确的SQL命令。SELECT 姓名 FROM xsqk WHERE 学号 not in; (sele 学号 from xk where 成绩=60) and 学号 in (sele 学号 from xk) 例8 找出和学生“马大大”的身高相同的所有学生。SELECT 姓名 FROM xsqk WHERE 身高=； (SELECT 身高 FROM xsqk WHERE 姓名=马大大) 5.2.4 排序使用SQL SELECT 可以将查询结果排序，排序的短语是ORDER BY，可以按升序(ASC)或降序（DESC）排序，允许按一列或多列排序。例：按学生的身高升序检索出全部学生信息

8、。SELECT * FROM xsqk ORDER BY 身高 这里ORDER BY 是排序子句，如果需要将结果按降序排列，只有加上DESC即可：SELECT * FROM xsqk ORDER BY 身高 DESC按学生的身高升序，身高相同的按体重降序检索出全部学生信息。这是一个按多列排序的例子，可以由如下语句完成：SELECT * FROM xsqk ORDER BY 身高,体重 desc 注意：ORDER BY是对最终的查询结果进行排序，不可以在子查询中使用该短语。 5.2.5 简单的计算查询SQL 不仅具有一般的检索能力，而且还有计算方式的检索，比如检索的平均成绩、检索最高身高等。用于

9、计算检索的函数及格式如下：COUNT(*)：计算记录个数SUM(字段名)：求字段名所指定字段值的总和AVG(字段名)：求字段名所指定字段的平均值MAX(字段名)：求字段名所指定字段的最大值MIN(字段名)：求字段名所指定字段的最小值找出学生中血型的种类。SELECT COUNT (DISTINCT 血型) FROM xsqk 求学生的数学成绩总和。SELECT SUM(数学) FROM cj求“O”型和“B”型血的学生的数学成绩总和。SELECT SUM(数学) FROM cj WHERE 学号 IN ; (SELECT 学号 FROM xsqk WHERE 血型=B OR 血型=O) 求“O

10、”型和“B”型血的学生数学成绩的平均值SELECT avg(数学) FROM cj WHERE 学号 IN；(SELECT 学号 FROM xsqk WHERE 血型=B OR 血型=O) 5.2.6 分组与计算查询 上面例子是对整个表的计算查询，而利用GROUP BY 子句进行分组计算查询使用得更加广泛。求男同学和女同学的平均身高。SELECT 性别,avg(身高) FROM xsqk GROUP BY 性别求 “O”型和“B”型血的男同学和女同学的平均身高。SELECT 血型,avg(身高) FROM xsqk ;WHERE 血型=B OR 血型=O GROUP BY 性别GROUP BY

11、子句一般跟在WHERE子句之后，没有WHERE子句时，跟在FROM子句之后。另外，还可以根据多个属性进行分组。在分组查询时，有时要求分组满足某个条件时才检索，这时可以用HAVING子句来限定分组。求各种血型(每种血型至少有两个同学)的同学的平均身高。SELECT 血型,avg(身高) FROM xsqk GROUP BY 血型 ; HAVING COUN(*)=2 5.2.7 内外层互相关嵌套查询有时需要内、外层互相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。列出每个学生选课成绩最高分的记录。SELECT * from xk out where成绩=;

12、(SELECT MAX(成绩) FROM xk Intr WHERE out.学号=intr.学号)在这个查询中，外层查询和内层查询使用同一个表，给它们分别指定别名 out 和intr，外层查询提供out中每个记录的学号给内层查询使用；内层查询利用这个学号值，确定该学生的选课成绩最高分；随后外层查询再根据out表的同一记录的选课成绩与该最高分进行比较，如果相等，则该记录被选择。 5.2.8 使用量词和谓词的查询前面已经使用过和嵌套查询或子查询有关的IN 和NOT IN运算符，除此之外还有两类和子查询有关的运算符，它们有以下两种形式： ANY|ALL|SOME（子查询） NOT EXISTS（子

13、查询）ANY、ALL和SOME是量词。EXISTS是谓词，EXISTS或NOT EXISTS是用来检查在子查询中是否有结果返回，即存在记录或不存在记录。 在表xsqk中检索没有选课学生的信息。SELECT * FROM xsqk WHERE NOT EXISTS ; (SELECT 学号 FROM xk where xk.学号=xsqk.学号)以上的查询等价于：SELECT * FROM xsqk WHERE 学号 NOT IN ; (SELECT 学号 FROM xk )以上查询也可以使用量词如下：SELECT * FROM xsqk WHERE 学号 all ; (SELECT 学号 FR

14、OM xk where xk.学号=xsqk.学号)在表xsqk中检索有参加选课学生的信息。这个查询可以使用EXISTS或ANY 、SOME。SELECT * FROM xsqk WHERE EXISTS ; (SELECT 学号 FROM xk where xk.学号=xsqk.学号)或：SELECT * FROM xsqk WHERE 学号 = any ; (SELECT 学号 FROM xk where xk.学号=xsqk.学号)检索选课成绩大于学号为“1161003”的最低选课成绩的所有学生的学号。这个查询可以使用ANY或SOME量词。SELECT DISTINCT 学号 FROM

15、xk WHERE 成绩any ;(SELECT 成绩 FROM xk where 学号=1161003) 或：SELECT DISTINCT 学号 FROM xk WHERE 成绩some ;(SELECT 成绩 FROM xk where 学号=1161003)它等价于：SELECT DISTINCT 学号 FROM xk WHERE 成绩;(SELECT min(成绩) FROM xk where 学号=1161003)5.2.9 超联接查询在一般 SQL 中超连接运算苻 “*=” 和“=*” 。其中“*=”称为左连接，含义是输出包含第一个表中满足条件的所有记录，如果第二个表在连接条件上有

16、匹配记录，则第二个表反回相应值，否则第二个表反回空值。注意：VISUAL FOXPOR不支持超级联接运算符 “*=” 和“=*”， VISUAL FOXPOR有专门的联接运算语法格式如下：SELECT 字段表达式表 FROM 表1 INNER |LEFT| RIGHT |FULL JOIN 表2 ON 连接条件 WHERE其中，INNER JOIN等价于 JOIN，为普通联接；LEFT JOIN称为左联接；RIGHT JION称为右联接；FULL JION可以称为全联接，即两个表的记录不管是否满足联接条件都在目标表或查询表中出现。 输出cj表和xsqk表的学号及姓名，使用普通联接。SELECT

17、 cj.学号,xsqk.学号 ,姓名 FROM cj join xsqk on cj.学号=xsqk.学号输出cj表和xsqk表的学号及姓名，使用左联接。SELECT cj.学号,xsqk.学号 ,姓名 FROM cj LEFT join xsqk on cj.学号=xsqk.学号输出cj表和xsqk表的学号及姓名，使用右联接。SELECT cj.学号,xsqk.学号 ,姓名 FROM cj RIGHT join xsqk on cj.学号=xsqk.学号5.2.10 集合的并运算SQL支持集合的并(UNION)运算，即可以将两个SELECT语句的查询结果通过并运算合成一个查询结果。为了进行并

18、运算，要求这样的两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域。输出数学成绩大于80分和小于70分的记录。sele * from cj where 数学80 union； sele * from cj where 数学705.2.11 SQL SELECT的几个特殊选项 只显示前几项记录 (TOP短语要与ORDER BY短语同时使用 )按学生的身高降序检索出身高最高的3位学生信息。 SELECT * top 3 FROM xsqk ORDER BY 身高 desc按学生的身高降序检索出身高最高的3成学生信息。SELECT * top 30 percent FROM xsqk

19、ORDER BY 身高 desc将查询结果存放到永久表中 SELECT * top 3 FROM xsqk ORDER BY 身高 desc into dbf xs3将查询结果存放在临时文件中SELECT * top 3 FROM xsqk ORDER BY 身高 desc into cursor xs3cur将查询结果存放在文本文件中SELECT * top 3 FROM xsqk ORDER BY 身高 desc to file xs3将查询结果送到打印机打印SELECT * top 3 FROM xsqk ORDER BY 身高 desc to printer查询结果存到数组中 SELE

20、CT * FROM cj INTO ARRAY tmp 5.3 SQL的定义功能5.3.1 表的定义 标准SQL的数据定义功能非常广泛，一般包括数据库的定义，表的定义，视图的定义，存储过程的定义，规则的定义和索引的定义等若干部分。在本节将主要介绍Visual Foxpro支持的定义功能和视图定义功能。 CREATE TABLE |DBF FREE ( 类型(长度,小数位数) CHECK 逻辑表达式l ERROR 字符表达式1 DEFAULT 表达式lPRIMARY KEY|UNIQUEREFERENCES 表名2 TAG 索引名l,PRIMARY KEY 表达式2 TAG 索引名2|,UNIQ

21、UE 表达式3 TAG 索引名3,FOREIGN KEY 表达式4 TAG 索引名4REFERENCES 表名3 TAG 索引名5,CHECK 逻辑表达式2 ERROR字符表达式2) 建立表xsqk1.dbf（结构和表xsqk.dbf类似）。create table xsqk1 (xh c(7),xm c(8),rq d ,sg n(4,2),ty l,bz m,zp g)建立表xsqk2.dbf（结构和表xsqk.dbf类似），要求建立主关键字等。只有数据库表才能建立主索引，所以建立表之前先建立数据库ABC。CREATE DATABASE ABCCREATE table xsqk2 (xh

22、c(7) primary key ,xm c(8),rq date ,sg n(4,2) ;check sg1.5 error 身高应该大于1.5 米 default 1.7, ty l,bz m,zp g)建立表cj2.dbf，要求该表有字段xh，并以xh字段与表xsqk2.dbf建立关联。CREATE table cj2 (xh c(7) ,sx n(3),yy n(3), ;foreign key xh tag xh references xsqk2)5.3.2 表的删除删除表的SQL命令是：DROP TABLE 表名5.3.3 表结构的修改修改表结构的命令是ALTER TABLE，该命

23、令有三种格式。格式1：ALTER TABLE 表名1 ADD | ALTER COLUMN 字段名1 字段类型 (字段宽度 ,小数位数) NULL | NOT NULLCHECK 逻辑表达式1 ERROP 字符表达式1DEFAULT表达式1PRIMARY KEY | UNIQUEREFERENCES表名2 TAG 索引名1该命令可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等； 把表xsqk1.dbf的xm字段的宽度改为12 ，sg字段改为n(5,2)。alter tabl xsqk1 alter xm c(12) alter sg n(5,2)给表xsqk1.db

24、f增加字段dz c(20)及df c(8)。alter tabl xsqk1 add dz c(20) add df c(8)格式2：ALTER TABLE 表名1 ALTER COLUMN 字段名2 SET DEFAULT表达式2 SET CHECK 逻辑表达式2 ERROR字符表达式2DROP DEFAULT DROP CHECK从命令格式可以看出，该格式主要用于定义、修改和删除有效性规则和默认值定义。把表xsqk1.dbf的sg字段的有效性规则修改为“sg1”。ALTER TABLE xsqk1 ALTER sg SET CHECK sg1删除表xsqk1.dbf的sg字段的有效性规则。

25、ALTER TABLE xsqk1 ALTER sg DROP CHECK 格式3：ALTER TABLE 表名1 DROP COLUMN 字段名3SET CHECK 逻辑表达式3 ERROR字符表达式3DROP CHECKADD PRIMARY KEY 表达式3 TAG 索引名2 FOR 逻辑表达式4DROP PRIMARY KEYADD UNIQUE 表达式4 TAG索引名3 FOR 逻辑表达式5DROP UNIQUE TAG 索引名4ADD FOREIGN KEY 表达式5 TAG TagName4 FOR 逻辑表达式6REFERENCES 表名2 TAG索引名5DROP FOREIGN

26、 KEY TAG TagName6 RENAME COLUMN字段名4 TO字段名5该格式可以删除字段（DROP COLUMN）、可以修改字段名（RENAME COLUMN）、可以定义、修改和删除表一级的有效性规则等。将表xsqk1.dbf的sg字段名改为“身高”。ALTER TABLE xsqk1 RENAME COLUMN sg TO 身高删除表xsqk1.dbf的dz及df字段。alter tabl xsqk1 drop dz drop df5.3.4 视图的定义及删除CREATE VIEW view_name (字段名 1,字段名2.) AS select_statement其中select_statement可以是任意的SELECT 查询语句，它说明和限定了视图中的数据；当没有为视图指定字段名时，视图的字段名将与select_statement中指定的字段名或表中的字段名同名。 从xsqk表中选择学号和姓名字段建立视图vxsqk。CREATE VIEW vxsqk AS;SELECT 学号, 姓名 FROM xsqk上面是限定列构成的视图，可以用WHERE限定行定义一个视图。例如：CREATE VIEW vxsqk1 AS；SELECT 学号, 姓名 FROM xsqk WHERE 身高1.7从xsqk表和cj表中选择学号、姓名和总分字段建立视图vxsqkcj