SQL命令和查询技术-基础必看

来源：互联网发布：淘宝买家具组装很烦编辑：程序博客网时间：2024/05/16 18:53

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更新”选项即可。