优化SQL语句

优化SQL语句（精选10篇）

优化SQL语句 篇1

摘要：数据库性能问题一直是决策者及技术人员共同关注的焦点,影响数据库性能的一个重要因素就是SQL查询语句的低效率。为了提高数据库应用系统的执行效率,在此从分析关系数据库查询处理过程入手讨论查询优化技巧,指出关系数据库查询优化的一般原则。通过几种优化策略的研究,在时间和空间上提高了系统的性能,在一定程度上提高了查询效率。

关键词：SQL,优化策略,数据库性能,谓词

0 引言

查询是数据库中最基本、最常用、最复杂的操作。在数据库的管理信息系统中,查询操作是所有数据库操作中所占据比重最大的操作。当数据库系统积累到一定程度,若查询时采用单条顺序扫描,那么扫描一遍所有的记录可能就得花上几十分钟,甚至几小时,这样的系统就失去了现实的使用价值。采取什么样的查询策略,使查询时间降为几分钟或者几秒钟,就是这里需要研究的查询优化问题。

1 优化原理[1]

查询优化力图找出给定表达式等价,但执行效率更高的一个表达式,一个查询往往会有许多实现方法,关键是如何找出一个与之等价的且操作时间又少的表达式,查询优化关注的问题是怎样省时、省空间以及效率高。优化的核心问题是尽可能减少查询中各表的参与加工的数据量,从而达到优化时间和空间的目的。

2 SQL优化的方法

2.1 模糊匹配的避免

LIKE关键字支持通配符匹配,技术上称为正则表达式。但这种匹配特别耗费时间,尽量避免使用模糊匹配。例:

即使在rx score字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。

可改写为:

这样,在执行查询时就会利用索引来查询,显然会大大提高速度。

2.2 逻辑表达式的等价变换

由于执行引擎对各种谓词的处理方法不同,因此把逻辑表达式重写成等价的且效率较高的表达式是提高查询效率的有效方法,同时也是切实可行的[2]。通过查阅大量的文献资料以及大量的实验,分析了RDBMS执行引擎对各种谓词执行效率的不同,总结出以下几种逻辑表达式转换规则:

2.2.1 将多个OR连接的表达式转化为ANY表达式

当条件表达式中同层次上出现由连接词OR连接的表达式,并且OR所连接的表达式的左表达式相同且谓词符号也相同时,那么可以将这些表达式合并为一个右表达式用ANY来描述的表达式。例:

可改写为:

2.2.2 将ANY或ALL转化为简单的比较表达式

当谓词的右表达式为ANY或ALL的形式,并且ANY(ALL)包含的各表达式均有固定值,并且可以比较大小,则可根据谓词符号(仅限于比较大小的操作符)将ANY(ALL)重写为简单的比较表达式。例:

x>ANY(100,200,300) 可改写为:x>100;

x>ALL(100,200,300) 可改写为:x>300

2.2.3 将BETWEEN…AND转化为AND连接的表达式

可以把由BETWEEN expr1 AND expr2的形式重写为用AND连接的两个表达式,效率往往有一定的提高。例:

可改写为:

2.2.4 将IN谓词表达式转换为OR连接的谓词表达式

例:年龄IN(20,30,40)

可写为:年龄=20 OR年龄=30 OR年龄=40

以上提到的4类谓词重写规则均有其特定的条件,在条件满足的情况下才可以使用。对于简单谓词的重写,每条规则提高的效率可能不太明显,但如果查询语句的WHERE条件同时使用多条规则进行重写时,效率的提高将非常可观。

2.3 子查询合并

子查询合并是将某些特定的子查询重写为等价的多个表的连接操作。子查询合并的作用在于能使查询语句的层次尽可能地减少,从而可提高查询的效率。子查询合并的一般规则为:

(1)如果外层查询的结果没有重复,即SELECT子句中包含主码,则可以合并其子查询,并且合并后的SELECT子句前应加上DISTINCT标志;

(2)如果外层查询的SELECT子句中有DISTINCT标志,那么可以直接进行子查询合并;

(3)如果内部子查询结果没有重复元组,则可以合并。

例:查询选修002号课程的学生基本信息。

用子查询的方法如下所示,例:

可改写为:

2.4 用集合运算来代替逻辑运算

0R在嵌套查询中,表的顺序存取对查询效率可能产生致命的影响,避免这种情况的方法就是对连接的列进行索引。例如两个表:student(sno,sname,age)和sc(sno,cno,score)。如果两个表要做连接,就要在“sno”这个连接字段上建立索引。还可以使用并集来避免顺序存取,尽管在所有检查列上都有索引,但某些形式的WHERE子句强迫优化器使用顺序存取。下面的查询将强迫对student表执行顺序操作:

OR xb=′计算机系′

虽然在sno和系别名上都建有索引,但是在上面的语句中优化器还是使用顺序存取的方法扫描整个表因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:

UNION SELECT*FROM student WHERE xb=′计算机系′

2.5 多表连接优化

最能体现查询复杂性的就是多表连接,多表连接操作往往要耗费大量的CPU时间和内存,因此多表连接查询性能优化往往是SQL优化的重点与难点[3]。

2.5.1 充分利用连接条件

在某种情况下,两个表之间可能不只一个的连接条件,这时在WHERE子句中将连接条件完整的写上,有可能大大提高查询速度。例:

这里,第二句将比第一句执行快得多。

2.5.2 先筛选后连接

当查询多个数据表时,要先过滤后再连接。例:

它们的执行效率相差很大。第一个查询语句首先将两个数据表按照用户ID进行连接,然后再将符合条件的记录筛选。由于两个数据表进行连接时记录有些是以后还要筛选掉的,这显然会占用更多的时间,且多个数据表连接是笛卡儿积运算,消耗的时间会随着记录个数的增加很快地增长。第二个查询语句克服了这个缺点,首先筛选出符合条件的记录,减少了进行连接的记录个数,然后再执行连接查询,大大提高了查询效率。

3 结语

查询优化要抓住关键问题,对于数据库应用程序,重点在于如何提高SQL的执行效率。在数据库的开发和维护过程中,查询的优化设计可以提高系统性能,对于数据量大的数据库系统尤为重要。以上介绍的几种优化策略使查询在时间和空间上提高了系统的性能,在一定程度上提高了查询效率。

参考文献

[1]王珊,萨师煊.数据库系统概论[M].4版.北京:高等教育出版社,2006.

[2]丁宝康,董健全.数据库实用教程[M].2版.北京:清华大学出版社,2003.

[3]范剑波,张晓云.网络数据库技术与应用[M].西安:西安电子科技大学出版社,2004.

[4]刘志成,彭勇.数据库系统原理与应用[M].北京:机械工业出版社,2007.

[5]罗运模.SQL Server数据库系统基础[M].北京:高等教育出版社,2006.

[6]宋瀚涛,李新社.数据库编程与应用[M].北京:电子工业出版社,1998.

[7]史嘉权.数据库系统概论[M].北京:清华大学出版社,2006.

[8]马李明,王守桃,徐艳蕾.SQL语句的优化在提高数据查询中的应用[J].电脑知识与技术,2008(20):200,223.

[9]华蓓,杨柳.SQL Server数据库性能优化探究[J].实践与经验,2008(7):98-100.

优化SQL语句 篇2

对于等值连接和自然连接，在WHERE子句中使用等于比较运算符，二者的区别在于等值连接的查询结果中列出所连接表中的所有列，包括它们之间的重复列，而自然连接的选择列表中删除被连接表间的重复列。

例：

(3)不等连接

不等连接使用除等于运算符以外的其他比较运算符，这些运算符包括>,>=,<=,<,!>,!<和>等。

(4)自连接

自连接中，使用同一个表的相同列进行比较，这时，对于同一个表应给出不同的别名。

例：使用自连接列出合著的图书标识及其作者姓名

2.外连接

内连接中，查询结果中所显示的仅是符合查询条件的行，而采用外连接时，它不仅包含符合连接条件的行，而且还包括左表或右表连接中的所有行，

外连接操作符有*=和=*两种，采用*=连接时，查询结果中将包含第一个表中的所有行，而采用=*连接时，查询结果将包含=*操作符后面表中所有数据行。

在进行一些统计时，需要使用外连接。例如，假定有两个表，一个表(PERSON)包含人员的姓名（NAME）及其标识（ID），另一个表（DESC）包含人员标识（ID）及其受奖惩情况（MEMO）。在统计单位的所有人员及其奖惩情况时，使用外连接书写查询语句就特别简单，即：

SELECT name,memo

FROM person,desc

WHERE person.id*=desc.id

优化SQL语句 篇3

关键词：SQL；Oracle；优化

中图分类号：TP311 文献标识码：A文章编号：1007-9599 (2011) 08-0000-02

SQL Optimization&Analyze in Oracle Database

Wang Yue

(CNOOC Gas&Power Group,Beijing100027,China)

Abstract:Executing each SQL statement,Oracle have to implement many steps.Each stepmay be physically retrieve data from the database row or rows of data in some way prepared.The statement where the Oracle used to execute those statements of combination of these steps is called an execution plan.execution plan is the most complex and most critical part in optimization of SQL,only know how Oracleinternally execute SQL statement,we can confirm that the execution plan where the optimizationr selected as suitable or not.

Keywords:SQL;Oracle;Optimization

一、引言

执行每个SQL语句，Oracle需要实现很多步骤。Oracle用来执行语句的这些步骤的组合被称之为执行计划。执行计划是SQL优化中最为复杂也是最为关键的部分，只有知道了Oracle在内部到底是如何执行该SQL语句后，我们才能知道优化器选择的执行计划是否为最优的。如何分析执行计划，从而找出影响性能的主要问题。下面先从分析SQL语句执行步骤开始介绍，再介绍如何分析执行计划。优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，优化器是所有关系数据库引擎中的最神秘、最富挑战性的部件之一，从性能的角度看也是最重要的部分，它性能的高低直接关系到数据库性能的好坏。

二、Oracle的优化规则

（一）什么是优化

优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT，INSERT，UPDATE或DELETE）中的一个重要步骤。对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。由于一系列因素都会影响语句的执行，优化器综合权衡各个因素，在众多的执行计划中选择认为是最佳的执行计划。然而，应用设计人员通常比优化器更知道关于特定应用的数据特点。这是需要人工干预数据库优化的主要原因。

看下面这个SQL（pro*c）：

EXEC SQL UPDATE employees

SET salary=1.10*salary

WHERE department-id=var-department-i

Oracle把它分成下列步骤来执行：

第1步：Create a Cursor

第2步：Parse the Statement

第3步：Describe Results of a Query

第4步：Define Output of a Query

第5步：Bind Any Variables

第6步：Parallelize the Statement

第7步：Run the Statement

第8步：Fetch Rows of a Query

第9步：Close the Cursor

下面来详细分析这些步骤：

第1步：Create a Cursor

由程序接口调用创建一个游标。任何SQL语句都会创建它，特别在运行DML语句时，都是自动创建游标的，不需要开发人员干预。多数应用中，游标的创建是自动的。而在预编译程序（pro*c）中游标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。

第2步：Parse the Statement

语法分析分别执行下列操作：翻译SQL语句，验证它是合法的语句，即书写正确。实现数据字典的查找，以验证是否符合表和列的定义，在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义，验证为存取所涉及的模式对象所需的权限是否满足。

第3步：Describe Results of a Query

描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征（数据类型，长度和名字）。

第4步：Define Output of a Query

在查询的定义阶段，你指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样我们通过接收变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。

第5步：Bind Any Variables

在该例中，Oracle需要得到对department-id列进行限定的值。得到这个值的过程就叫绑定变量（binding variables）决定此语句最佳的执行计划将它装入共享SQL区对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点

第6步：并行执行语句（Parallelize the Statement）

并行化可以导致多个服务器进程（oracle server processes）为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗费更多的资源，所以除非很有必要，否则不要使用并行查询。

第7步：Run the Statement

Oracle拥有所有需要的信息与资源，因此可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句，则不需要锁定任何行，因为没有数据需要被改变。

第8步：Fetch Rows of a Query

在fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出来。上面提到过，批量的fetch是优化的技巧之一。

第9步：Close the Cursor

SQL语句处理的最后一个阶段就是关闭游标

（二）Oracle的优化器

优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，不要以为只有SELECT语句是查询。实际上，带有任何WHERE条件的DML语句中都包含查询要求。在ORACLE的发展过程中，一共开发过2种类型的优化器：基于规则的优化器和基于代价的优化器。这2种优化器的不同之处关键在于：取得代价的方法与衡量代价的大小不同。现对每种优化器做一下简单的介绍：RBO（rule base optimization）基于规则和CBO（cost base optimization）基于成本。

三、SQL执行计划

（一）分析执行计划

假定A、B、C都是不是小表，且在A表上一个组合索引：A（a.col1，a.col2），注意a.col1列为索引的引导列。考虑下面的查询：

select A.col4

from A，B，C

where B.col3=10 and A.col1=B.col1 and A.col2=C.col2 and C.col3=5

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimization=CHOOSE

1 0 MERGE JOIN

2 1 SORT（JOIN）

3 2 NESTED LOOPS

4 3 TABLE ACCESS（FULL）OF'B'

5 3 TABLE ACCESS（BY INDEX ROWID）OF'A'

6 5 INDEX（RANGE SCAN）OF'INX_COL12A'（NON-UNIQUE）

7 1 SORT（JOIN）

8 7 TABLE ACCESS（FULL）OF'C'

Statistics

----------------------------------------------------------

0 recursive calls

8 db block gets

6 consistent gets

0 physical reads

0 redo size

551 bytes sent via SQL*Net to client

430 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts（memory）

0 sorts（disk）

6 rows processed

执行计划：

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimization=CHOOSE

1 0 MERGE JOIN

2 1 SORT（JOIN）

3 2 NESTED LOOPS

4 3 TABLE ACCESS（FULL）OF'B'

5 3 TABLE ACCESS（BY INDEX ROWID）OF'A'

7 1 SORT（JOIN）

8 7 TABLE ACCESS（FULL）OF'C'

看执行计划时，我们的关键不是看哪个操作先执行，哪个操作后执行，而是关键看表之间连接的顺序（如得知哪个为驱动表，这需要从操作的顺序进行判断）、使用了何种类型的关联及具体的存取路径（如判断是否利用了索引）在从执行计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表（就像上面判断ABC表那样）是否合适，如果不合适，对SQL语句进行更改，使优化器可以选择正确的驱动表。

（二）干预执行计划

基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在ORACLE中，是通过为语句添加hints（提示）来实现干预优化器优化的目的。

hints是Oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：

1.使用的优化器的类型。

2.基于代价的优化器的优化目标，是all-rows还是first-rows。

3.表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。

4.表之间的连接类型

5.表之间的连接顺序

6.语句的并行程度

除了“RULE”提示外，一旦使用别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。

Hints只应用在它们所在sql语句块（statement block，由select、update、delete关键字标识）上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。我们可以使用注释（comment）来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT，UPDATE，DELETE关键字的后面，如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。

参考文献：

[1]盖国强,冯春培,叶梁等.Oracle数据库性能优化(第4版)[M].北京:人民邮电出版社,2005

[2]阿弗尤尼,吴越胜,张耀辉等.Oracle 9i数据库性能调整与优化[M].北京:清华大学出版社,2005

优化SQL语句 篇4

1 SQL语句处理流程

1.1 打开游标

从上图可以看出处理SQL语句的第一步就是要打开一个游标, 事实上, 一个完整的SQL处理过程就是一个游标的生命周期。

1.2 共享SQL

O r a c l e内存中有一个区叫S H A-RED_POOL, 这个区的主要作用就是将SQL语句存放在这个区内, 当客户发出一个新的SQL语句, 数据库引擎首先会到这个区查找是否有相同的SQL, 如果有, 则避免了解析、分析索引、制定执行计划等一系列的动作。如果没有则需要进行hard parse。

1.3 绑定变量

如果在SQL中指定了绑定变量, 需要在这个阶段给SQL附上绑定变量的值。

1.4 并行处理

如果SQL需要进行并行处理, 在这一阶段需要把整个SQL分割成多个并行的部分。

1.5 执行查询

Oracle按照执行计划指定的方式执行SQL, 执行UPDATE和DELETE语句时, 必须将行锁定, 以免其他用户修改。Oracle先从数据库缓冲区中寻找是否存在所要的数据块, 如果存在, 就直接读或修改, 否则从物理文件中读到数据库缓冲区中。

1.6 返回结果

对SELECT语句需要返回结果的语句, 首先看是否需要排序, 需要, 则排序后返回给用户, 然后根据内存的大小不同, 可以一次取出一行数据, 也可以一次取一组数据。这时, 可能要用到数据结构中的外部排序, 并归排序等算法, 所以如内存允许的话, 会在很大程度上提高性能。

2 影响SQL性能的原因

影响SQL性能的因素很多, 如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序 (CBO) 的正确判断等, 这些往往和DBA密切相关。纯粹从SQL语句出发, 笔者认为影响SQL性能不外乎以下四个重要原因。

(1) 在大记录集上进行高成本操作, 如使用了引起排序的谓词等。

(2) 过多的I/O操作 (含物理I/O与逻辑I/O) , 最典型的就是未建立恰当的索引, 导致对查询表进行全表扫描。

(3) 处理了太多的无用记录, 如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。

(4) 未充分利用数据库提供的功能, 如查询的并行化处理等。

3 SQL优化解决方案

通常, 一个关系数据库服务器上的SQL进程要使用该服务器60%～90%的资源。一般来说, 数据库应用上60%的性能问题是因为运行着效率很低的SQL语句, 而其中30%SQL语句的性能是可以被显著改进提升的。

3.1 避免使用<>和!=

<>和!=都是不等于的意思, 但在SQL开发过程中尽量不要使用, 因为这个关键字会造成索引失效, 降低查询效率。下面是使用表client_info (客户信息表) 进行测试的情况, 其中在cl_no (客户编号) 列上建立了索引。

3.2 避免隐式转换

隐式转换是最容易被开发人员忽视的一点, 因为它并不影响查询结果, 但它却影响了查询效率, 在查询过程中, 由于数据类型的不符合, 造成隐式的调用to_char或to_number函数。下面采用USER_INFO (用户信息表) 来测试这一过程, 如表1所示。

◆查询用户名称为101的客户信息, 下面是没有隐式转换的查询:

从测试结果很容易看出:第二个查询中索引失效了, 这是因为ORACLE数据库在解析过程中后台进行了隐式转换user_name=to_char (101) 造成的。

3.3 选择最有效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名, 因此FROM子句中写在最后的表 (基础表) 将被最先处理, 在FROM子句中包含多个表的情况下, 必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时, 会运用排序及合并的方式连接首先, 扫描第一张表 (FROM子句中最后的那个表) 并对记录进行排序, 然后扫描第二张表 (FROM子句中最后第二个表) , 最后将所有从第二张表中检索出的记录与第一个表中合适记录进行合并。下面是对这一理论的验证, 其中用户信息表user_info有999条数据, 合同信息表cont_info有32000条数据。

这一结果表明, 合理的表的连接顺序对数据库性能的提高是很有帮助的。

4 结语

SQL语句练习 篇5

（1）按课本第99-100页表结构要求建立以上六张表。

（2）在学生表中插入一个学生记录：（”99082901”,”程丽”,”女”,1981-2-10,”群众”，”200704”）。

（3）使选修CZ004号课的同学的成绩为NULL值。

（4）为课程表加一”教师”列。

（5）修改课程表，使“肖贵”老师教CJ001号课。

（6）列出“王勇”老师所授课程的课号和课名。

（7）列出年龄不小于20的男生的学号和姓名。

（8）统计学生所选课程的门数。

（9）列出选修CJ001号课的学生的平均年龄。

（10）列出选修“计算机基础”课的学生的平均成绩、最高分和最低分。

（11）列出所有姓“李”的同学的姓名、年龄和性别。

（12）列出成绩表中成绩为空值的学生的姓名、课号。

（13）列出年龄大于女生平均年龄的男生的姓名和年龄。

（14）删除成绩表中无成绩的记录。

（15）把低于CJ001号课平均成绩的所有同学的所有课程的成绩提高5分。

（16）按学号升序，成绩降序列出每个同学的选课情况（包括姓名、课名、成绩）。

（17）按课号和成绩降序列出每门课的选课情况包括课名、姓名、成绩。

（18）按成绩降序列出每个同学的姓名、总成绩。

（19）求出学习全部课程的所有同学的名单。

（20）列出平均成绩大与80分同学的学号和平均成绩，按平均成绩降序排列。

（25）查询平均成绩排在前5名的学生姓名及平均成绩。

（26）统计选修课程在3门以上（含3门）的学生的学号、姓名和平均成绩。

（27）查询学生年龄超过21岁的学生信息。

（28）列出所有姓“李”的同学的学号、姓名、年龄和性别。

优化SQL语句降低时间复杂度 篇6

无论是传统的客户端/服务器程序还是现在流行的浏览器/服务器程序,功能与性能时程序的重要指标,完善的功能,突出的性能是程序开发所追求的目标[2]。程序性能的内在表现是较小的空间复杂度和较低的时间复杂度,时间复杂度控制得好,程序的运行效率就高,完成一项工作的时间就越短,因此人们总希望自己设计的程序的时间复杂度尽量低一些。从业务逻辑层入手还是从数据操作层上入手,对于新手来说是一件比较难于掌握的事情,我们知道对于数据的操作一般是查询、插入、更新、删除等,它们所选择的程序模型基本上都是固定的,那么如何才能设计出时间复杂度较理想的数据处理程序呢,改进算法有很多种思路,除了对数据库本身进行优化外,还可以从改进在业务逻辑层上的时间耗费,从而降低程序的时间复杂度。

2 时间复杂度对程序运行的影响

由于程序设计的算法直接决定了程序的时间复杂度,所以我们要求尽量选用时间复杂度较低的算法来实现业务逻辑上的业务处理。引用个简单的例子:一台巨型机做插入排序和一台微型机做合并排序,它们的输入都是一个长度为100万的数组。假设巨型机每秒执行1亿条指令,微型机每秒仅仅做100万条指令。为了使差别更加明显,假使世界上最优秀的程序员用机器代码在巨型机上实现插入排序,编出的程序需要执行2n2条巨型机指令来排序n个数,另一方面,让一个一般的程序员在微机上用高级语言编写合并排序,产生的代码需要花50nlgn条微型机指令。为排序100万个数,巨型机耗费的时间为:2.(106)2条指令/108条指令/秒=20000秒≈5.56小时,微型机耗费的时间为:50.106lg106条指令/106条指令/秒≈1000秒≈16.67分钟[3]。从这个例子可以看出时间复杂度对程序的执行效率的影响,对基于服务器/浏览器模式的程序来说还受到网络传输,与服务器会话时间等限制等因素的影响,如果不控制好时间复杂度,那么简直不敢想象。

3 SQL语句对程序时间复杂度及网络带宽的影响和改进

在教务管理系统中,对学生课程实施分配的大致情况是根据已有的学生信息和各班开设课程的情况进行分配,所进行的操作就是从学生表中读取学生的基本信息如学号、在校情况等,然后判断学生课程库中是否已经存在该学生的对应课程的相关信息,如果不存在则插入该信息,如果存在那么不进行操作。

第一步:读取课程中对该年级该学段开设的课程。

第二步:使用循环来读取学生的信息,判断学生课程记录是否已经存在,如果存在则跳过,否则插入学生课程记录。

不难看出,如果要完成全部课程的分配,则将有两个循环存在,那么粗略计算一下程序的时间复杂度为T(n2)级的,可见这样的程序执行的效率很低,并且存在不断和服务器交换数据的问题,因此对网络带宽也有较大的影响。

3.1 使用SQL组合语句降低时间复杂度

为了降低时间复杂度,就需要打破多重循环,因此需要在数据操作上进行改进,对循环判断学生课程分配情况的SQL语句进行组合,得到这样的一条SQL操作语句:"insert into xfscore(kid,stuid,kclass)select,课程号,stuid,sclass from student where sclass in(开课班)and stuid not in(select stuid from xfscore where kid=课程",可见用这么一条SQL语句来代替用程序循环来判断课程重复的操作大大地降低了程序的时间复杂度,减少和数据服务器的对话次数和等待时间,这样组合后的程序时间复杂度为T(n)级别的,执行效率明显高于前面的操作方式。

有人会这样质问,虽然把程序上的时间复杂度降低了,但是数据库上的操作时间复杂度仍然没有降低,SQL数据执行时还是循环执行的,看上去并没有降低总体的时间复杂度,但是实际的执行效率却高了很多,为什么呢?原因很简单,这样用数据库内部执行循环的方式并不需要和客户端进行对话和交换数据,而是根据指令直接完成,减少了对话时间,也即是提高了执行的效率。

3.2 使用存储过程降低时间复杂度

存储过程是创建的数据库程序模块,由数据库服务器上的DBMS来存储和执行,实际上它们可以是函数或过程[4]。使用存储过程的主导思想是把主要的数据操作的SQL语句封装在一个存储过程中,在设计程序的时候只需要考虑调用相应的存储过程即可完成对数据的操作,不需要考虑数据的内部操作,减少了程序的时间复杂度,把数据操作和程序执行分别有不同的服务分担。因存储过程主要在SQL服务器上执行,数据提交后直接在服务器上处理,不需要过多地和客户端进行数据交换对话,对于基于B/S的程序能较好分担由于交互而带来的带宽问题。一般情况下使用存储过程来完成较为复杂的数据操作,如多表同步关联更新等。

4 两种使用SQL组合语句提高效率的情况

4.1 新增学生时,学号自动生成情况

因为是多用户操作不能先生成学生学号再进行新增学生的操作,而且也避免人工填写导致重号的情况,因此对学号的生成一般采用这样的方式来完成,在提交新增数据前先获取最大的学生学号,然后根据学号编写规则相应的加一作为新的学号,然后再写入到数据库中去,经过这样两步完成工作。

第一步select isnull(max([systemid]),0)+1 as newid from students

第二步insert into students(stuid,stuname,stupasswd)values(newid,’姓名’,’pwd’)

优化一下则可以将SQL语句变成这样的一句来完成上面的操作:

insert into students(stuid,stuname,stupasswd)select isnull(max(stuid),0)+1,’姓名’,’pwd’from students

4.2 删除或审核多条选中记录的操作

有时遇到这样情况需要对一些信息进行集中管理,比如删除或者是审核,那么我们一般是使用复选框的模式来维护数据,如果按照常规的先判断数据是否被选中,选中了那么执行删除或更新操作,然后判读下一条数据是否被选中,然后继续进行相应的操作。这样来回和服务器进行数据操作,如果使用SQL组合语句那么就不需要如此频繁的操作数据库了,组合在这里做文章,先把要操作数据的关键字段值获取到组成一个集合,然后在删除条件中修改为“in(在)”集合中,那么只需要对数据库操作一次即可完成多条数据的删除或者审核工作。

5 结束语

以上只是在开发新课程教务管理系统中的一些对SQL语句的优化,通过组合SQL语句能减少对数据库的操作次数和对话次数,有效控制程序的时间复杂度,提高程序的内在性能。我们都知道程序性能的优化不是简单的事情,需要各种经验的积累和采用多种方法来不断改进,借用一句广告语“没有最好,只有更好”来表达对程序性能优化的感慨,可能还存在一些不足的地方,需要在实践中不断学习改进。

摘要：随着学校新课改的实施,教务管理系统的开发提上日程,学校在校生人数和课程数目都在增加,导致操作的数据增加,因此开发此系统时需要注意控制程序的时间复杂度,若时间复杂度过高,往往影响用户的使用体验,有时甚至无法完成预定的目标,再则基于服务器浏览器模式的数据操作更要考虑网络带宽等因素。除了数据库优化[1]外,通过对SQL语句的优化,切实改善了程序的时间复杂度,提高了程序执行效率,节约了网络带宽,可满足基于浏览器服务器模式的海量数据操作。

关键词：时间复杂度,SQL语句优化,存储过程

参考文献

[1]徐鑫涛.浅谈数据库优化[J].中国科技信息,2008(4):111.

[2]武俊峰,朱喜梅.基于电子商务的网站设计与性能优化研究[J].自动化技术与应用,2008(27):26.

[3]潘金贵,顾铁成.现代计算机常用数据结构和算法[M].南京:南京大学出版社,1994.

优化SQL语句 篇7

1 对SQL语句进行优化的好处

面对传统的数据库应用模式下查询作业效率低且信息资源消耗大的情况, 如何在保证SQL数据库服务的质量的同时, 通过采取一定的技术手段与相关措施, 减少在原来的数据库应用模式下进行查询作业时对巨量信息资源的消耗, 进而提升数据库应用系统的运行效率, 使终端使用用户的查询工作更快捷, 已成为计算机应用系统发展进程中亟需解决的问题, 而对SQL语句进行优化就是解决这一问题的有效方法。这是因为, 对于很多以数据库为基础的应用程序来说, 大部分都是B/S或者C/S架构, 通过客户端嵌入的SQL语句或者调用数据库上的过程来实现与数据库的联系。因此SQL语句的质量如何会对整个系统造成很大的影响, 所以对SQL语句进行优化有这几个方面的好处:一是减少系统对硬件资源的消耗, 节约投资成本;二是提升系统运行的效率, 降低数据库死锁的风险;三是加强系统源代码的可读性, 减轻技术员对程序进行修改的劳动强度。

2 SQL语句优化的原理

从理论的方面来说, 结构化查询语言 (Structured Query Language) 简称SQL, 是一种数据库查询和程序设计语言, 用来对数据进行存取、查询并对关系数据库系统进行更新和管理, 在本质上它是一种负责连接数据服务器和客户终端的工具, 用户可以在SQL数据库中经过高层数据结构的支持进行连续性的加工作业。另外, 用户在利用SQL数据库进行数据处理时不需要对数据的存放方式进行明确的指定, 因此在底层数据结果完全不一样的的数据库系统当中, 有关的技术人员可以利用一样的SQL语言承担起数据输入和管理的端口。尤其要强调的是:在SQL数据库应用系统中, 不管是什么类型的SQL语句都可以利用接收集合实现输入, 利用返回集合实现输出。我们可以利用SQL数据库应用系统的这个特点将随便一条SQL的输入动作当作是另一条SQL语言的输出动作。

关于SQL语句的优化, 从计算机应用系统和与其相关的计算机技术的角度来说, 我们可以这样理解SQL语句的优化:通过一些相关的技术手段和处理方法, 把原来的SQL语句转化成语句语意都和原来一样并且在数据处理上效率更高的新型的SQL语句。通常认为, SQL语句的优化原理就是最大限度的减少终端用户在利用数据库应用系统进行查询工作时的各表参与加工的数据量, 从而更好的对时间和空间进行优化, 具体的说, 优化查询的最终目的就是帮助终端用户在最短的系统反应时间之内更快更好的找到与给定表达式相等价的数据。大量的实践和研究显示了一条规律:一个对数据的查询在SQL数据库处理正常运行的状态下会有两种或者更多种的实现方法, SQL语句对一切工作进行优化的关键就是找到一个等价于查询目标并且操作时间更短的语句表达式。

3 优化SQL语句的方法

关于优化SQL语句提高数据库效率的方法, 本文从SQL语句优化中的视图优化、SQL语句优化中的语句优化、SQL语句优化中的索引优化这三个方面进行具体的阐述

3.1 SQL语句优化中的视图优化

在SQL数据库中, 视图是其中一大关键对象, 它从本质上来说是一种数据表虚拟化的表现形式。通常情况下, SQL数据库下的视图分为三种形式, 即分区视图、标准视图和索引视图。其中分区视图对于分布式数据表查询效率的提高有着重要的作用, 在对整个SQL数据库的效率进行提高的过程中应该格外注重这一点, 可以在预先各个区域的服务器成寻中存储代表其区域仓库信息的Warehouse表, 从而促使此区域的查询业务不会受到外部区域服务器的信号影响, 并且有效的提升此区域仓库信息的查询效率。尤其要强调的是, 随着现阶段数据库处理系统逐步向多元化、集成化发展的情况下, 终端用户在对一些数据库进行信息查询时, 通常需要对包括此区域仓库信息在内的两个或者多个仓库信息进行访问, 因此在把仓库划分为多个区域的作业过程中, 要对各个仓库区域的ID信息进行差异性的定义, 帮助用户在查询分区的时候可以根据ID进行判定, 从而达到高效整合的动态合并查询的目的。

3.2 SQL语句优化中的语句优化

在对整个SQL数据库的性能进行优化作业的过程中, 语句优化是其中最关键、最核心的部分。通常来说, SQL语句优化可以分为两个方面, 即模糊查询技术的优化和子查询展开技术的优化。本文从对子查询展开技术的优化作业来对语句优化进行详细的分析, 从实质上来说, SQL数据库应用系统中的子查询展开技术就是通过吧子查询信号转变成链接从而对查询作业进行优化的技术。以某省查询各市企业资产总额由资产超过一千万的企业名称查询SQL数据库的查询作业为例子, 通常来说, 在原来的SQL数据库子查询展开技术的运用当中, 有关的技术工人人员一般采用:Select企业名From企业Where企业代号In (Select企业代号From企业Where资产>一千万) 的方式进行查询。很明显, 在利用这种查询方式进行查询的时候, 数据库查询会对每个市的每行数据中满足子查询要求的企业记录进行地毯搜索式的查询, 虽然查询的结果会很准确, 但是查询的效率却很低。而如果对SQL语句进行优化, 可以提前把企业表设置成SQL数据库查询作业中的链接内表, 在具体的查询过程中应该对语句进行分组从而首先对企业表进行企业代号的查询和删选工作, 进而可以最大限度的在对所有满足条件的企业进行查询之前消除多余的不必要的企业代号, 大大提高数据库的效率, 缩短查询作业的时间。具体的说, 经过语句优化之后, SQL数据库的查询语句是:Select D.企业名From (Select代号From企业Where资产>一千万Group by企业代号) E, 企业D Where E.企业代号=D.企业代号。

3.3 SQL语句优化中的索引优化

我们需要认识到的是:在现阶段的SQL数据库应用系统的作业过程中, 索引是最为常用的一种数据库操作对象。可以这样讲, 索引设置和使用的情况对于整个SQL数据库应用处理系统甚至是数据库整体性能的发挥都有着直接的影响。通常来说, 相关的技术工作人员在查询一些还没有建立索引的数据表时, 执行的大多数是全表的查询作业。更具体的说, 这种全表查询作业就是在对磁盘上的数据表的全部数据页进行读取的基础上, 对读取到的数据经过一定的整理、分析和加工, 进而实现对数据信息的获取处理。很明显, 如果在进行全表查询作业时, 其要读取的数据表具有大批量、大规模的数据量, 那么在SQL数据库数据处理的过程中需要消耗巨大的信息资源, 因此, 对SQL语句中的索引进行优化对于提高数据库的效率有着非常重要的意义。通常来说, 索引分为簇索引、非簇索引以及复合索引这三种实现的方式, 其中使用最为广泛的索引方式是簇索引。本文就以簇索引作为切入点对于SQL数据库的索引优化进行详细的说明。具体的说, 簇索引索引方式就是通过对磁盘上的各种实际数据进行一定的组合和整理, 从而使其可以按照制定的列值进行排列的过程。根据研究发现, 按照物理表现方式的不同将磁盘上的这些数据进行新一轮的排列组合之后, 系统在进行查询作业的时候, 如果搜索到符合搜索条件的第一条记录之后, 就不需要再对该列的其他数据进行充分的查询, 从而使查询范围大大的缩小, 提升了索引方式下SQL数据库的查询的效率。

4 结束语

随着社会经济的快速发展和现代科学技术的不断更新, 人民在物质文化和精神文化方面的需求也在持续的提高, 这对现代的计算机网络应用系统以及与之相关的软件技术提出了更高的要求。在繁荣发展的社会主义市场经济下, SQL数据库是计算机网络技术应用行业发展的必然趋势, 其运行的性能和效率将对整个数据库应用系统运行的安全性与稳定性产生直接的影响与决定性的作用。优化SQL语句可以在保证SQL数据库服务的质量的同时, 节约在传统数据库应用模式下进行查询作业消耗的巨量的信息资源, 提升数据库应用系统的运行效率, 使终端使用用户的查询工作更快捷, 从而加快计算机应用系统及其相关技术的发展。

参考文献

[1]王爱军, 刘风华, 张萌萌.基于数据库查询过程优化设计闭[J].电子科技大学学报, 2009 (10) .

[2]韩朝军, 梁冰, 刘莹.SQL Server管理与开发技术大全[M].北京:人民邮电出版社, 2009 (13) .

[3]李克洪, 王大玲.实用密码学与计算机数据安全[M].沈阳:东北大学社出版, 2011 (22) .

优化SQL语句 篇8

随着信息化进程的不断加快,数据库的应用越来越广泛,信息系统的优劣与数据库系统的性能有着直接的关联。随着数据库规模的不断扩大,如何保持数据库应用系统高效地运行,受到人们越来越多的关注。

2 优化器

对于信息管理系统来说,其最关键的核心是数据库系统,对于众多的应用系统来说,查询操作在整个系统中占据着相当大的比重,也就是说,查询速度的快慢直接影响着信息管理系统的性能。当数据库的规模越大这个特性表现的越明显,良好的查询语句对系统性能的提高起着积极的作用。

2.1 Oracle 优化器

无论SQL语句的性能如何, 最终都要在Oracle数据库中执行,Oracle数据库在执行SQL语句之前, 首先通过优化器利用初始化的参数,并利用指定的优化方法对执行计划进行分析,并执行。

当前,Oracle优化器主要有RBO和CBO两种优化方式。其中RBO是基于规则的优化器,根据访问的路径和访问路径的等级去选择SQL语句的执行计划,假如一条SQL语句有多个路径可以通过,Oralce会自动选择等级最低的访问路径。RBO优化器仅含有几条在小表上低效利用的索引,无形之中增加了I/O,该优化方式效率相对较低;CBO是基于代价的优化器, 其成本主要由可用访问路径、嵌入的提示、对象的统计信息等组成,CBO会选择成本代价最低的执行计划。当前,CBO优化器成为Oracle数据库优化的主要组成,CBO的构成如图1所示

利用Oracle优化器对SQL语句进行分析, 在所有的查询中,有一半经过RBO优化之后,执行的速度会快一点,而另一半则由CBO优化后,执行的速度最快。从Oracle8i版本以后,RBO优化已经不再发展RBO。

2.2 影响执行计划的因素

影响执行计划的因素较多,总结起来主要有几种。

(1)连接顺序。当前 ,数据库进行数据查询 ,大部分情况下都需要由若干表连接,一般采取将查询结果只有一行记录的表优先。

(2)访问路径。对于路径的扫描,Oracle优化器的方式有簇扫描、索引扫描、行ID扫描、全表扫描、散列扫描等。由于Oracel数据库对I/O的评估原则是“块”在整个表中所占的比例来确定选取何种扫描路径。

(3)连接方式。查询优化的重点是连接操作 ,内表与外表之间进行连接的算法主要有归并连接、散列连接及嵌套循环连接,每种连接都有其自己的优点。

(4)成本估算。其成本的代价主要从I/O、CPU和通信三方面进行考虑,其中I/O是最主要的。在成本估算中占的比重最大。

3 优化 SQL 语句

3.1 可优化的 SQL 语句类型

优化器可以优化SQL语句的类型主要有几种。

(1)简单语句。对于数据表的操作 ,主要的操作动作有select、update、insert及delete语句, 这种类型的语句主要包括from和where。一般来说,可以优化的地方主要在where中。其影响效率的问题主要集中在几个方面:分组或排序过程中包含了过多的中间结果集、对索引的列使用了全表扫描。

(2)连接语句。利用多个表相结合的方式查找相关的数据信息,这也是数据库最常用的方法,由from子句实现多个表的连接, 利用where将相关的条件进行关联。其影响效率的问题主要集中在几个方面:表连接顺序不是最优、分组或排序过程包含了过多的中间结果集、索引列使用了全表扫描等。

(3)外部连接。该方式与连接语句有相同之处 ,同样涉及到多表连接的问题。其影响效率的问题也与连接语句相近。

(4)复杂语句。对于select、update、insert及delete语句中的子查询以select形式存在。该方式的问题是内部查询的效率对外部查询的效率有影响。

(5)复杂查询。利用组操作符将若干简单语句结合起来形成的语句,一般情况下,将语句拆分为上述的四种类型的语句之后再进行优化处理。

对于查询问题的分析,主要是要尽可能地减少子查询或者使用子查询返回的结果集要尽量地减少。

3.2 优化的规则

(1) 在索引列字段上尽可能地避免使用“! = ”“NULL”、“<>”及“not”等符号 , 尽可能地不要使用隐式类型的转换。这些符号的使用可能会对索引信息造成影响,进行转变为全表扫描,影响了数据库的性能。

(2)尽可能地不要使用“select * from表名”。“*”符号代表需要返回所有列,就意味着要扫描所有的返回记录,收取所有的列名与列值。使Oracle不断地进行磁盘的读取及交换。如果需要,将“*”换成具体的列名。

(3)避免在索引列字段中使用改变列的函数。当函数改变了索引列的类型及内容时,可能使原来可以使用的索引值变得无法继续使用,从而影响了系统的效率。

(4)绑定变量进行传值。绑定变量进行传值与数据库收到语句解析后的内容是一致的,Oracle可以在下一条语句到来时直接存入缓存并执行,不必再进行解析和生成执行过程。

(5)合理建立索引。良好的索引机制 ,可以使系统查询速度更快。

(6)利用where代替having子句。where子句是在分组之前对条件进行筛选, 而Having是分组后进行筛选分组前筛选可以有效减少分组的时间和资源的消耗。

比较下面两个SQL语句:

语句A:select name , num from department wherenum not in (select num from school);

语句B:select name,num from department where notexists (select num from school where department.num=school.num)。

通过在数据库中执行这两条语句, 其结果是相同的, 执行语句A时,Oracle首先对school表进行整个扫描,没有在school上建立num索引,语句B使用了联合查询, 对school表进行部分扫描, 利用了num列的索引。语句B的效率要高于语句A。

4 结束语

常用SQL语句举例分析 篇9

1.select语句由多个子句构成, 其基本表达式如下:select[all∣distinct]*或者column as alias[, column2] from table[, table2]

[where“conditions1”]

[group by“column-list1”]

[order by“column-list”[asc∣desc]]

在查询语句中, 被[]括起来的是可选项。最基本的结构是select-from-where语句。如果没有查询条件的话, where语句也是可以没有的。其中select子句是数据查询的核心语句, 通过select子句, 可以指定所要查询的字段。

2.as子句设置字段别名

select customerID, companyTITLE as公司名称from客户表

3.from子句用以指出查询目标所涉及的所有表。可以指定当前的数据库, 也可以指定一个外部数据库或一个数据源。From子句由关键字from后跟一组用逗号分开的表名组成, 每个表名都表示一个包括该查询要检索数据的表。这些表称为此SQL语句的表源, 因为查询结果都源于它们。在FROM子句中最多可指定256表或视图, 它们之间用逗号分隔。

4.where子句指出查询目标必须满足的条件, 系统根据条件进行选择运算, 输出符合条件的记录集合。Where子句设置查询条件, 过滤掉不需要的数据行。例如下面语句可查询价格高于1000的记录。Select product, perprice from产品表 where perprice>1000。

5.order by子句对记录排序。Order by是可选的子句, order by子句将查询结果按一列或多列中的数据排序。可以通过指定ASC或DESC按照升序或降充排列查询的结果。

6.group by子句进行分组查询

group by子句将所有的行在一起, 它包含了指定列的数列以及允许合计函数来计算一个或者多个列。

7.like和in子句进行通配查询

in运算符指定某几个项, 用以查询符合这几个项的所有记录, 如同“=”与“or”的组合。Like匹配符, 通配字符有以下几种:百分号%可匹配任意类型和长度的字符, 如果是中文, 应使用两个百分号。下划线:匹配单个任意字符。方括号[]指定一个字符、字符串或范围, 要求所匹配的对象为它们中的任一个。

8.sql函数进行统计查询

sql提供了事个作用在列值集合上的内置函数:count (计算元素的个数) 、max (找出某一列元素的最大值) 、min (找出某一列元素的最小值) 、sum (对某一列的数值进行求和) 、avg (对某一列的数值进行求平均数) , 除count外, 这些集合函数都必须作用在由简单值组成的集合上, 也就是, 数字集合或字符串集合。

还有一些命令, 由于篇幅限制, 不再一一列举, 请各位读者可以逐一尝试;一些基本的sql命令可以帮助我们在日常的生活工作中解决一些实际性的问题。

摘要：本文对日常工作中要经常用到SQL语句进行分析, 希望能与感兴趣的朋友一起探讨, 共同挖掘新的功能, 借此提高工作效率, 方便日常工作。

VFP中SQL语句的灵活应用 篇10

VFP是一个功能较强的小型关系型数据库管理系统，可以非常方便地对数据进行存储、编辑、显示和查询等操作。VFP提供了基本的数据显示、编辑和查询命令，如:LIST、DISPLAY、BROWSE、EDIT、CHANGE等命令，可以对表中的数据按指定的范围或指定的条件进行显示、编辑或查询。另外，VFP还提供了SQL结构化查询语言，它们可以更加方便、灵活地对单表或多表中的数据进行查询或编辑。VFP提供的SQL结构化查询语言主要有四条语句，分别是：SELECT（数据查询语句）、INSERT（数据插入语句）、UPDATE（数据更新语句）和DELETE（数据删除语句），其中功能最强使用最广的是SELECT语句，它构成了SQL结构化查询语言的核心。

2、表结构与数据的基本组成

为了方便举例，使用了下面的三张表：

(1) 职工.DBF（存放职工信息）

职工.DBF (职工号C (6) , 姓名C (8) , 性别C (2) , 婚否L (1) , 出生日期D (8) , 基本工资N (8.2) , 部门C (6) , 简历M (4) , 照片G (4) )

199701李长江男T 05/12/75 1000.00直销

199702张伟男F 06/23/76 2300.00零售

199801李四方男T 06/18/77 2000.00零售

……

(2) 销售.DBF（存放职工销售信息）

销售.DBF (职工号C (6) , 商品号C (4) , 数量N (8.2) , 金额N (12.2) )

199701 1001 80.00 4000.00

199702 1001 30.00 1500.00

199803 2003 15.00 645.00

……

(3) 商品.DBF（存放销售商品的信息）

商品.DBF (商品号C (4) , 商品名称C (20) , 类别C (4) , 库存量N (8.2) , 单价N (8.2) , 单位C (4) )

1001海飞丝洗涤700.00 50.00瓶

1002潘婷洗涤580.00 40.00瓶

1003沙宣洗涤360.00 47.00瓶

……

3、应用实例

下面分别介绍四条语句的格式及应用。

3.1 SELECT查询语句的使用

SELECT查询语句的基本格式：

SELECT表名1.字段名1[AS标题名1], 表名1.字段名2[AS标题名2], …

FROM数据库名!表名1[，数据库名!表名2][, 数据库名!表名3]

[WHERE选定条件]

[GROUP BY分组字段名]

[HAVING分组中的满足条件]

[ORDER BY排序字段名1[ASC|DESC][, 排序字段名2[ASC|DESC]...]]

[TO FILE文本文件名|INTO TABLE|INTO CURSOR表文件名]

其中：

功能:从一个表或多个表中筛选出满足给定条件的记录。

(1) 单表查询

SELECT语句针对一张表中的数据进行查询。

例1:查询1975年以后出生的职工信息。

SELECT*FROM职工WHERE YEAR (出生日期) >=1975

【注】：通过"WHERE"子句，可以查询符合条件的数据。"WHERE YEAR (出生日期) >=1975"表示出生日期在1975年以后。

例2：查询"直销"和"零售"部门的职工信息，以部门与职工号排序。

SELECT*FROM职工WHERE部门in ("直销", "零售") ;

ORDER BY部门, 职工号

【注】：子句"WHERE部门in ("直销", "零售") "表示符合条件"部门="直销"或部门="零售"。对数据进行排序，则可使用"ORDER BY"子句。其中的"ORDER BY部门, 职工号"，表示数据先按部门排序，当部门相同时再按职工号排序。

例3：查询单价在30～50间的商品信息。

SELECT*FROM商品WHERE单价BETWEEN 30 AND50

【注】：子句"WHERE单价BETWEEN 30 AND 50"表示符合"单价在30至50间"的条件。

(2) 多表查询

SELECT语句可以通过连接其它表的方式方便地进行多张表中数据的查询。

例4：查询销售金额>=2000的人员信息，按销售金额从高到低排序。

SELECT职工.职工号, 姓名, 金额as[销售金额]FROM销售, 职工;

WHERE职工.职工号=销售.职工号AND金额>=2000;

ORDER BY金额DESC

【注】：可以通过子句"WHERE职工.职工号=销售.职工号"将职工表与销售表连接。子句"ORDER BY金额DESC"按金额从高到低排序。

例5：查询各位职工的销售数据。

SELECT姓名, 部门, 商品名称, 单价, 数量, 金额FROM职工, 销售, 商品;

WHERE职工.职工号=销售.职工号and销售.商品号=商品.商品号;

ORDER BY职工.职工号

也可采用下面的语句形式，查询的结果一样。

SELECT姓名, 部门, 商品名称, 单价, 数量, 金额FROM职工;

INNER JOIN销售ON职工.职工号=销售.职工号;

INNER JOIN商品on销售.商品号=商品.商品号;

ORDER BY职工.职工号

例6：查询各类商品的最高销售数量。

SELECT商品名称, 类别, MAX (数量) as[最高销售数量]FROM销售;

INNER JOIN商品on销售.商品号=商品.商品号;

GROUP BY类别

【注】：可以在SELECT语句中使用如下参数对数据进行统计：

MAX () 确定分组中的最大值。

MIN () 确定分组中的最小值。

SUM () 统计数值型数据分组的总和。

AVG () 计算数值型数据分组的平均值。

COUNT () 统计分组中行的数目。

例7：查询各销售人员的合计销售金额。

SELECT姓名, 部门, SUM (金额) as[销售金额合计]FROM职工;

INNER JOIN销售on职工.职工号=销售.职工号;

GROUP BY职工.职工号

例8：查询"零售"部门各职工销售的商品种数和销售的合计金额

SELECT姓名, 性别, count (销售.商品号) as[销售商品种数], ;

SUM (金额) as[销售金额合计]FROM职工, 销售;

WHERE职工.职工号=销售.职工号AND部门="零售";

GROUP BY职工.职工号ORDER BY职工.职工号

例9：查询销售金额合计前3名的职工信息。

SELECT TOP 3姓名, 性别, 部门, count (销售.商品号) as[销售商品种数], ;

SUM (金额) as[销售金额合计]FROM职工, 销售;

WHERE职工.职工号=销售.职工号;

GROUP BY职工.职工号;

ORDER BY销售金额合计DESC

【注】参数"TOP 3"表示查询只显示前三行数据，使用时必须与"ORDER BY"子句相结合。

例10：查询各职工销售的相关数据并保存到一张表中。

SELECT姓名, 性别, 部门, 商品名称, 类别, 数量, 金额FROM职工;

INNER JOIN销售ON职工.职工号=销售.职工号;

INNER JOIN商品ON销售.商品号=商品.商品号;

ORDER BY职工.职工号;

INTO TABLE XSXX

【注】：为了将查询的数据保存到表中，可以在查询语句中加入"INTO TABLE"子句，上例的"INTO TABLE XSXX"将查询数据保存到名为"XSXX"的表中，该表会自动创建。

例11：查询与职工"李长江"同性别的职工的相关信息。

SELECT姓名, 性别, 部门, 商品名称, 类别, 数量, 金额FROM职工;

INNER JOIN销售ON职工.职工号=销售.职工号;

INNER JOIN商品ON销售.商品号=商品.商品号

ORDER BY职工.职工号;

WHERE性别IN;

(SELECT性别FROM职工WHERE姓名="李长江")

【注】：本例采用了嵌套查询，即SELECT查询语句中又嵌套了一个或多个SELECT语句。

嵌套查询可以通过在<WHERE子句>中包含子查询，使用子查询必须有如下限制：

子查询必须用圆括号括起来。

子查询的<SELECT子句>（即返回列）只能有一个返回列。

只能在主查询中使用UNION合并查询和<ORDER BY子句>，而不能在子查询中使用它们。

例12：查询所有与职工"李长江"销售同样商品的职工信息。

SELECT姓名, 商品.商品号, 商品名称, 单价, 数量, 金额FROM职工;

INNER JOIN销售ON职工.职工号=销售.职工号;

INNER JOIN商品ON销售.商品号=商品.商品号;

WHERE销售.商品号IN;

(SELECT销售.商品号FROM职工, 销售;

WHERE职工.职工号=销售.职工号AND姓名="李长江")

【注】：本例通过一个内嵌的SELECT查询语句：

" (SELECT销售.商品号FROM职工, 销售WHERE职工.职工号=销售.职工号AND姓名="李长江") "获得职工"李长江"销售的商品的商品号。

3.2 INSERT数据插入语句

INSERT语句的基本格式：

INSERT INTO表名（字段列表）VALUE（取值列表）

功能：将数据值<VALUES子句>添加到目标表<INSERT子句>中。

例13：将一条销售数据加入销售表。

INSERT INTO销售 (职工号, 商品号, 数量, 金额) VALUES ("199803", "1001", 10, 500)

可以使用INSERT插入数据语句把查询结果插入到其它表。

例14：将销售金额>=2000的职工作为业绩优秀员工。

CREATE TABLE优秀员工 (职工号C (6) , 姓名C (8) , 部门C (4) , 销售金额N (8, 2) )

INSERT INTO优秀员工 (职工号, 姓名, 部门, 销售金额) ;

SELECT职工.职工号, 姓名, 部门, 金额FROM职工, 销售;

WHERE职工.职工号=销售.职工号AND金额>=2000

【注】：先使用语句"CREATE TABLE优秀员工 (姓名C (8) , 部门C (4) , 金额N (8, 2) ) "

新建一个名为"优秀员工"的表，其中有职工号（字符型6位长度）、姓名（字符型8位长度）、部门（字符型4位长度）和销售金额（数值型8位长度2位小数）等四个字段。

再使用INSERT INTO语句将业绩优秀员工的信息插入到刚建的"优秀员工"表中，而"优秀员工"的信息则是借助查询子句：

"SELECT职工.职工号, 姓名, 部门, 金额FROM职工, 销售;

WHERE职工.职工号=销售.职工号AND金额>=2000"

得到。语句执行后，实际将在"优秀员工"表中插入多行相关数据。

上面的INSERT语句也可写成：

INSERT INTO优秀员工 (职工号, 姓名, 部门, 销售金额) ;

SELECT职工.职工号, 姓名, 部门, 金额FROM职工;

INNER JOIN销售ON职工.职工号=销售.职工号;

WHERE金额>=2000

3.3 UPDATE更新数据语句

UPDATE语句的基本格式：

UPDATE表名SET字段名=<表达式>, 字段名=<表达式>, …

功能：更新<UPDATE子句>中满足条件<WHERE子句>的所有记录，修改为<SET子句>中指定的值。

例15：将职工"张伟"的婚否状态改为"已婚"。因职工表中婚否字段为逻辑型，所以，设置婚否=.T.。

UPDATE职工SET婚否=.T.WHERE姓名="张伟"

例16：将销售金额>=2000的职工的基本工资增加10%。

UPDATE职工SET基本工资=基本工资*1.1;

WHERE职工号IN;

(SELECT销售.职工号FROM销售WHERE金额>=2000)

3.4 DELETE数据删除语句

DELETE语句的基本格式：

DELETE[数据库表名]FROM数据表名WHERE条件

功能：删除表<DELETE子句>中满足条件<FROM子句和WHERE子句>的所有记录。

例17：将职工"张伟"的销售数据删除。

DELETE销售from职工;

where职工.职工号=销售.职工号AND姓名="张伟"

【注】：此语句执行时，职工表与销售表按职工号自动关联，实现删除"张伟"的销售数据。

例18：删除"张伟"的相关数据。

DELETE FROM销售WHERE职工号;

IN (SELECT职工号FROM职工WHERE姓名="张伟")

DELETE FROM职工WHERE姓名="张伟"

【注】：职工"张伟"的数据包括销售表中"张伟"的销售数据和职工表中"张伟"的职工数据。所以在删除"张伟"的相关数据时，使用了两个DELETE语句，第一个DELETE是在"销售"表中删除"张伟"的销售数据，第二个DELETE语句是在"职工"表中删除"张伟"的职工数据。

DELETE数据删除语句只是对记录作了逻辑删除，并没有真正删除记录，还可以使用VFP中的RECALL命令恢复删除的记录；如果要彻底删除，则可在DELETE语句执行后，再执行VFP的PACK命令。

参考文献

[1]王衍主编.数据库应用基础[M].北京:电子工业出版社, 2009.

[2]朱珍主编.Visual FoxPro数据库程序设计[M].北京:中国铁道出版社, 2005.