SQL命令和查询技术
SQL语言概述
SQL语言是关系数据库系统通用的结构查询语言。IBM公司最早开发了SQL语言,ORACLE公司开发了最早的商用SQL语言。SQL语言的标准经历了以下几个阶段:
SQL86---->SQL89----->SQL92------->SQL99
一.SQL语言的主要特点
(1)综合统一
SQL语言包括了数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)
数据定义语言提供完整定义数据库所必需的所有内容,数据定义语言是SQL中用来生成、修改、删除数据库基本要素的部分。
数据操纵语言是SQL中对数据库进行运算的部分,可以对数据库中的数据的输入、修改和提取。
数据控制语言为数据库提供的安全性是必须的,为数据库应用系统的开发提供了良好的环境支持。
(2)高度非过程化
使用SQL语言进行各种操作,只要说明“做什么”,不需说明“怎么做”。
(3)面向集合的操作方式
SQL语言中,一次插入、删除、更新操作的对象可以是元组的集合,一次查询的结果也是元组的集合。
(4)以同一种语法结构提供两种使用方式
SQL语言既是自含式语言,又是嵌入式语言。即既可以独立地应用于联机交互的使用方式,又可以嵌入到其它的高级语言中,在程序中使用。
(5)语言简洁、易学易用
SQL语言中只使用了几个简单的命令动词,就完成了数据定义、数据操纵、数据控制和数据查询的所有功能。
二.VFP支持的SQL命令
1.CREATE CURSOR:创建临时表。临时表中每个字段由字段名、字段类型、精度、比例,是否允许取空值和参照完整性规则来定义。
2.CREATE TABLE:创建表。表中每个字段由字段名、字段类型、精度、比例,是否允许取空值和参照完整性规则来定义。
3.ALTER TABLE:修改自由表的结构。可以对表中的每一个字段的字段名、字段类型、精度、比例,是否允许取空值和参照完整性规则进行修改。
4.DELETE:给表中的记录加删除标记。
5.INSERT:在已经存在的尾部追加一条新的记录。新增记录的数据可以是INSERT命令自带的,也可以来自一个数组。
6.UPDATE:更新表中的记录,可以基于SELECT语句的结果来更新记录。
7.SELECT:指定查询条件并执行查询命令,VFP解释该查询,并且从表中检索出指定的数据。
SELECT-SQL命令的格式
SELECT 表名1. 字段名1 [AS 标题名1], 表名1. 字段名2 [AS 标题名2], …
FROM 数据库名! 表名1 [,数据库名! 表名2 ] [, 数据库名! 表名3 ]
[TO FILE 文本文件名 | into table | into cursor 表文件名 ]
[ WHERE 选定条件]
[GROUP BY 分组字段名 ]
[HAVING 分组中的满足条件 ]
[ORDER BY 排序字段名1 [ASC | DESC] [, 排序字段名2 [ASC | DESC] ...]]
其中各子句的含义为:
SELECT子句用于指明查询输出的项目,可以是字段或表达式。
FROM子句指明被查询的表或视图。
INTO 子句用来指明查询结果保存到何处,可以是数组、临时表或表。TO子句也可以指明查询结果输出到何处,可以是文件、打印机、主窗口 。若SELECT语句中INTO子句和TO子句同时出现,则TO子句不起作用。
WHERE子句说明查询的条件。DISTINCT选项用来消除查询结果中重复出现的记录或字段,TOP n为输出前面n条记录。PERCENT选项指定输出的百分率。
GROUP BY子句按列的值分组,列值相同的为一组。HAVING子句选择满足条件的组进行输出。
ORDER BY子句对查询结果按指定的列的值进行排序,ASC为升序,DESC为降序。
UNION子句把两个或多个SELECT语句的查询结果组合起来,默认删除重复行,保留要加ALL选项。
SELECT语句的应用
(1)简单查询:不涉及多个表的查询。
如:SELECT * FROM XS
SELECT DISTINCT * FROM XS
对已建的学生表执行该命令:
消除查询结果中的重复行
SELECT XS.XH, XS.XM, YEAR(XS.NIANLING) AS 年龄 FROM XS
对已建的学生表执行该命令格式:
输出的项目可以是字段或表达式或常数,AS指定查询结果中列的标题。
SELECT * FROM XS WHERE NIANLING>20 ORDER BY XH ASC
对查询的结果进行排序,ASC为升序,DESC为降序,WHERE筛选记录,有多个条件时用AND、OR进行连结。
SELECT * FROM XS WHERE NIANLING BETWEEN 18 AND 22
对已建的学生表执行该命令格式:
要求某列的数值在某个区间内,使用BETWEEN ... AND...,要求某列的数值不在某个区间内
使用NOT BETWEEN ... AND...。
SELECT * FROM XS ORDER NIANLING IN(18,19,20,21,22)
要求列的取值是某几个值中的一个,用IN,要求列的取值是不是某几个值中,用NOT IN
SELECT * FROM XS ORDER BY XH LIKE "99%"
对已建的学生表执行该命令格式:
用LIKE实现匹配查询(用于字符型数据),匹配时有两种方式,“_”匹配任一西文或中文字符,用%匹配0个或多个字符。
(2)连接查询
在两个表之间,按照指定列的相同值将一个表中的一行与另一表中的行连接起来,形成了连结查询。
如:SELECT XS.XH, XS.XM, KC.KCM, CJ.CJ
FROM XS JX!INNER JOIN JX!CJ INNER JOIN JX!KC;
ON CJ.KCDH=KC.KCDH;
ON XS.XH=CJ.XH;
ORDER BY XS.XH
(3)字段函数
SELECT语句可以对满足条件的数据进行统计、计数等运算。字段函数可以在SELECT子句中与选定的列一起使用。
MIN 求列的最小值
MAX 求列的最大值
COUNT 计算一列中值的个数
SUM 计算列中数值的总和
AVG 计算数值列的平均值
例如:
SELECT SUM(*) FROM XS
(4)嵌套查询
在一个SELECT语句的WHERE子句中出现另一个SELECT语句,称为嵌套查询。WHERE子句中出现的SELECT语句叫做子查询,子查询必须用括号括起来。WHERE子句中最多可以有两个同一级的字查询。
如:
SELECT * FROM XS WHERE XH IN (SELECT XH FROM XS WHERE XH LIKE "99%")
(5)联合查询将两个或多个查询结果进行并操作(UNION),自动消除重复的记录。
如:
SELECT * FROM XS WHERE XH LIKE "99%" UNION SELECT * FROM XS WHERE XH LIKE "98%"
9.2 查询的创建和使用
查询:就是向一个数据库发出检索信息的请求,从中提取符合特定条件的记录。
查询文件:
即保存实现查询的SELECT-SQL命令的文件。查询文件保存时,系统自动给出扩展名 .qpr;查询被运行后,系统还会生成一个编译后的查询文件,扩展名为 .qpx。
查询结果:
通过运行查询文件得到的一个基于表和视图的动态的数据集合。查询结果可以用不同的形式来保存。查询中的数据是只读的。
查询的数据源:创建查询必须基于特定的数据源可以是一张或多张相关的自由表、数据库表、视图。
创建查询有三种方法:1.查询设计器;2.查询向导;3.直接使用SELECT语句
1. 用查询设计器创建查询
基本步骤:
打开查询设计器 → 添加创建查询所基于的数据表 → 定义输出内容 → 设置联接、筛选、排序、分组条件 → 选择查询结果的输出形式 → 保存查询文件 →运行查询。
(1)打开查询设计器
方法1:
从文件菜单或工具栏上单击新建 → 查询 → 新建文件 → 进入查询设计器。
方法2:
当所用到的数据表已在项目中时,从项目管理器窗口中单击数据 → 查询 → 新建 → 新建查询 → 进入查询设计器。
如图示:
方法3:从命令窗口中输入命令:
create query 查询文件名 & 创建新查询
modify query 查询文件名 & 修改已存在的查询
(2) 定义查询的输出内容
单击字段选项卡 → 从可用字段列表框中单击所需字段(当输出的列不是直接来源于表中的字段时,单击函数和表达式框边的… 按钮,打开表达式生成器,构造出所需的表达式)→ 单击添加按钮 → 所需字段自动出现在选定字段框中。(如上图所示)
(3) 设置查询的筛选条件
筛选条件决定将哪些记录显示出来。
在筛选框中构造筛选条件表达式时,要注意在实例框中输入不同数据类型时的格式:
1) 字符串可以不带引号(当与源表中的字段名相同时才用引号);
2) 日期型数值要用{ }括起来;
3) 逻辑型数据两侧要带 .号,如 .T.,.F.
(4) 设置查询结果的排序依据
排序决定查询输出结果中记录显示的顺序。
设置方法:
单击排序依据 → 从选定字段框选中字段 → 选择升序或降序 → 单击添加。
(5)运行查询
在查询设计器打开的状态下,单击常用工具栏上的 !按钮或从查询菜单中选择运行查询。其他情况下,可从项目管理器中选中查询文件并单击运行按钮,或从程序菜单中选择执行命令,或从命令窗口中输入: DO 查询文件名。
(6) 设置查询结果的分组依据
分组是指将一组类似的记录压缩成一个结果记录,目的是为了完成基于该组记录的计算,比如:求平均值、总和、统计个数、其中的最大值、最小值等。
用于分组的字段不一定是选定输出的字段,但分组字段不能是一个计算字段。
可以用 满足条件… 来对分组结果进行进一步筛选。
(7) 对查询结果的其他设置
可以排除查询结果中所有重复的行,并设置结果的记录范围。
(8) 选择查询结果的输出去向
默认情况下,查询结果将输出在浏览窗口中,且其中的数据是只读的。
设置其他输出类型的方法:
打开查询菜单 → 选择查询去向 → 在查询去向对话框中选择一种。一般多选择表或报表。
浏览:将查询结果显示在浏览窗口。
临时表:将查询结果存储在一张只读临时表中。
表:将查询结果存储在一张表中。
图形:查询结果输出到GRAPH程序。
屏幕:查询结果输出到VFP主窗口或当前活动窗口中。
报表:查询结果输出到报表文件。
标签:查询结果输出到标签文件
SQL输出去向表
输出去向 | 对应的SQL子句 |
浏览 | 缺省 |
临时表 | INTO CURSOR 临时表名 |
表 | INTO TABLE 表名 |
屏幕 | TO SCREEN |
文本文件 | TO FILE 文本文件名 |
打印机打印 | TO PRINT |
(9) 查看SQL语句
查询设计器或查询向导都生成一条SELECT语句。可以选择“查询/查看 SQL”或查询设计器工具栏上的SQL按钮。
或:
(10) 生成查询文件
查询创建完成后,单击常用工具栏上的“保存”按钮或文件菜单中的“保存”命令。可以生成查询文件,扩展名为.QPR
(11)创建多表查询
定义数据环境
在查询设计器中使用“查询设计器”工具栏的“添加表”按钮或“查询”菜单的“添加表”项,或快捷菜单中的“添加表”项,在“添加表或视图”对话框中添加表或视图。
或:
设置连结条件
在“查询设计器”中添加表或视图之后,需要在这些表或视图之间建立连结,若添加到数据环境中的表存在数据库中的永久性关系,则连结条件自动建立。否则,弹出“连结条件”对话框,且以两张表的同名字段作为默认连结条件。
如图:
连结的类型
连结类型
说明
内部连结
两个表中的字段都满足连结条件,记录选入查询结果(INNER JOIN)
左连结
连结中左边的表中记录都包含在查询结果,右边的表中记录只有满足连结条件时,才选入查询结果。 (LEFT OUTER JOIN)
右连结
连结中右边的表中记录都包含在查询结果,左边的表中记录只有满足连结条件时,才选入查询结果。 (RIGHT OUTER JOIN)
完全连结
两个表中的记录不论是否满足连结条件,都选入查询结果。(FULL JOIN)
已存在于查询中的表或视图,可拖动一张表中的字段到另一张表中的对应字段上来建立连结。在“查询设计器”对话框的“连接”选项卡中可以修改、添加或删除连结条件。连结条件中字段的匹配规则不必完全相等,可使用“LIKE”、“==”、“〉”、“〈”。
设计查询时,要确保连结条件应该是链式的,即第一个连结条件右边表的别名与第二个连结条件左边的表的别名。在数据环境中添加表时就要按照这个顺序添加表,否则,连结有问题。或者不使用连结而使用筛选条件来实现。
3. 用查询向导创建查询
查询向导类型 | 功能说明 |
查询向导 | 创建标准查询,在浏览窗口中显示查询结果 |
交叉表向导 | 按电子表格的形式显示查询结果 |
图形向导 | 以图形的形式显示查询结果 |
(1)创建标准查询:
打开待查询的数据表,从文件菜单中单击新建,在新建对话框中选择查询并单击向导,选择查询向导,从单张或多张相关的表中进行字段选取,设置筛选条件,设置排序次序,选择查询结果的保存方式,给出查询文件名并选择保存位置,查询结果显示在查询窗口中。
注意:查询此时得到的数据是只读的,不可以更新。
(2)交叉表查询
打开待查询的数据表,从文件菜单中单击新建,在新建对话框中选择查询并单击向导。
字段选取:从一张表中选择要显示的三个字段。
布局:把行对应字段拖到“行”框中,把列对应字段拖到“列”框中,把数据对应字段拖到“数据”框中。
加入总和信息:为查询结果指定一个总计列,并加入到查询结果的最右侧。
完成:给出查询文件名并选择保存位置。
运行结果:
注意:不是任何表都适宜采用交叉表的形式。
9.3 视图的创建和使用
视图的实质
视图是存在于数据库中的一个虚表,不以独立的文件形式保存;视图中的数据是可以更改的,它不仅具有查询的功能,且可以把更新结果反映到源数据表中;视图打开时,其基表自动打开,但视图关闭时,其基表并不随之自动关闭;视图的数据源可以是自由表、数据库表或另一个视图。
视图的特点:1.视图可以提供附加的安全层;2.视图可以隐蔽数据的复杂性;3.视图有助于命名简洁;4.视图带来更改灵活性;5.使用视图可以修改数据库。
创建本地视图
VFP中视图有两种类型,本地视图能够更新本地计算机上的表,远程视图能够更新远程服务器上的表。本地视图的创建方式有三种:
(1)使用向导创建视图
选定"新建/视图"菜单,单击“向导”,然后逐个步骤进行设置。
(2)使用视图设计器创建视图
创建视图的过程与创建查询的过程类似,使用视图设计器进行设计,上方用来设置数据环境,下方的选项卡设置视图选择数据的条件。其中只有一个选项卡与查询设计器不同,即更新条件选项卡。
视图设计器对话框:
(3)命令方式创建视图
可以在某个数据库中使用命令创建一个视图,首先打开一个数据库,然后使用命令创建视图
OPEN DATABASE 数据库名
CREATE SQL VIEW 视图名 AS SELECT语句
如:
OPEN DATABASE 教学
CREATE SQL VIEW myview AS SELECT * FROM 教学!班级
(4)运行视图
如果视图包含在项目中,可在项目管理器中选择要运行的视图,然后单击浏览按钮。
在视图设计器中运行视图有两种方法,打开视图后,在“查询”菜单中选择“运行查询”;在视图设计器中单击鼠标右键,在弹出的快捷菜单中选择“运行查询”。
创建远程视图
远程视图使用远程数据库的SQL语法从远程ODBC数据源表中选择信息ODBC是一种访问数据库的标准。只要该数据库提供了ODBC支持,就可以用标准的ODBC协议进行访问。
创建远程视图之前,首先要连结一个数据源。
(1)连结远程数据源:远程数据源对应远程服务器上的一个数据库。可在操作系统中为此服务器建立一个数据源,连结时可使用此数据源。
(2)定义连结:除了数据源之外,还可以使用数据库中的连接进行与远程数据库的连接,连结有命名连结和无名连结,创建命名连结的方法有:
1.在项目管理器中,选择“数据库”列表中的“连接”,然后单击“新建”按钮,打开“连结设计器”,进行连结的设计。
2.打开数据库,使用 CREATE CONNECTION 命令打开连结设计器。
3.打开数据库,使用 CREATE CONNECTION 命名连结名 命令。
“连结设计器”对话框
4.也可使用数据源建立连接,如:
OPEN DATABASE 教学
CREATE CONNECTION myconn DATASOURCE vfp6 USERID PASSWORD
创建连接后还没有激活,只有在使用远程视图时,才激活连结,连结到数据源。视图中可以指定一个有数据源名称而没有连结名称的无名连结,关闭视图时,连结也关闭。
(3)命名连结与数据源的优先使用权
使用有CONNECTION子句的CREATE SQL VIEW 命令时,命名连结和数据源都可以使用,但优先使用命名连结。
(4)使用一个视图时,若没有给定全部必须信息时,系统将显示一个ODBC注册提示对话框。
(5)显示已有连结:打开数据库后,使用DISPLAY CONNECTIONS或在项目管理器中选择数据选项卡中数据库项列表中的连接。
(6)创建远程视图:
1.在项目管理器中,选择一个数据库,然后选择“远程视图”,单击“新建”按钮。
2.使用带REMOTE或CONNECTION子句的CREATE SQL VIEW 命令。
其中REMOTE子句与CONNECTION子句有一即可。若在远程视图设计器中有两个表或多个表,设计器将连接条件加入视图对应SQL语句的WHERE子句中,并使用内部连结。
使用视图更新基表数据
视图除了可以显示数据,还可以更新基表中的数据,在视图设计器中的“更新条件”选项卡中设置关键字段和设置“发送SQL更新”选项即可。
可在视图设计器的更新条件页面中进行如下设置来实现对源表数据的更新:
1)从表框中选择想要更新的源表;
2)在字段名框中单击一个字段前关键列B 和更新列 ! ,使其作为主关键字和可更新字段;
3)将发送SQL 更新复选框选中
创建参数化视图
通过给视图的筛选条件设置参数,运行视图时,VFP将提示输入参数值,系统将根据参数的值来找出符合条件的记录。
在视图设计器的“筛选”选项卡中,在“字段名”框中选定字段,在“条件”框中选择比较的类型为“=”,在“实例”框中输入“?”和字段名。保存视图后,选定视图,然后浏览该视图时,将显示视图参数对话框。
视图的操作
1)视图的打开和浏览:使用USE命令打开视图,使用BROWSE来浏览。
如:OPEN DATABASE 教学
USE MYVIEW
BROWSE
若视图使用了本地表,则视图作为临时表在自己的工作区中打开,而视图的基表在另外一个工作区中打开。若视图基于远程表,则基表不在工作区中打开,而只在“数据工作期”窗口中显示远程视图名。
2)打开视图的多个实例
在多个工作区中打开同一视图时,每次要返回新的数据集合。
在“项目管理器”中选择视图后选择浏览。重复此过程即可。
在“数据工作期”窗口中选择“打开”,然后选择视图名,重复此过程在另外的工作区中打开视图。如图示:
使用USE命令以编程方式访问视图。可以使用NOREQUERY子句或AGAIN子句来指定生成一个视图时不下载数据。
OPEN DATABASE 教学
USE MYVIEW
BROWSE
USE MYVIEW NOREQUERY
BROWSE
3)关闭视图与基表
关闭视图与关闭表的命令相同.如:“数据工作期”窗口,USE,CLOSE TABLE,CLOSE
DATABASES,CLOSE ALL。但关闭视图后,视图自动打开的基表不会自动关闭,需另外关闭。
4)显示视图结构
使用带NODATA子句的USE命令,仅打开视图并显示视图结构。
如:OPEN DATABASE 教学
USE MYVIEW NODATA IN 0
BROWSE
5)创建视图索引
可以使用INDEX ON 命令,为视图创建本地索引,创建过程与表一样,视图的索引不会保存到数据库中,随着视图的关闭而自动消失。
6)创建视图的临时关系
使用SET RELATION命令可以在视图索引之间或视图索引与表索引之间建立临时关系。在视图索引与表索引之间建立临时关系时,视图为主表,表为子表。
7)集成视图
可以在其他视图的基础上创建视图,基于视图或集成了本地表、本地视图或远程视图的视图叫做多级视图。
8)定制视图
在视图设计器对话框中的字段选项卡中,选定要设置属性的字段,然后修改字段的标题、注释、默认值和规则。可以用DBSETPROP()函数设置字段的属性。
9)修改视图
在项目管理器中,选择视图名,然后选择“修改”按钮,打开“视图设计器”;
打开数据库后,使用MODIFY VIEW 视图名 命令。
10)重新命名视图
在“项目管理器”中选择要改名的视图,然后从“项目”菜单中选择“重命名文件”。
使用RENAME VIEW命令。
11)删除视图
在“项目管理器”中选择要删除的视图,然后选择移去,或使用RENAME VIEW命令,或DROP VIEW命令。