VB中使用SQL语言基本.docx

VB中使用SQL语言基本.docx

《VB中使用SQL语言基本.docx》由会员分享，可在线阅读，更多相关《VB中使用SQL语言基本.docx（13页珍藏版）》请在冰豆网上搜索。

VB中使用SQL语言基本

VB中使用SQL语言基本教程

SQL语言基本教程

（一）

   数据库查询语言（SQL）就是使用于关系数据库的标准语言,被很多类型的数据库所支持。

本文将以VB加DAO为例,来介绍

基本的SQL语法以及使用。

本章将介绍的就是SQL语言中的基本查询语句,并带有范例。

   要更好的阅读本文,读者需要对数据库的基本结构以及术语有一个基本的了解,而且最好能有一定的VB数据库编程经验。

除非特别说明,本文将使用的数据库就是VB中附带的BIBLIO、MDB数据库。

一、SELECT、、、FROM、、、WHERE语句

   语句的语法如下:

SELECT[all|*|distinctcolumn1,column2]

FROMtable1[,table2]

WHERE[condition1|expression1][ANDcondition2|rxpression2]

   在上面的语法中,[]外的语句就是必须的,而[]内的就是可选的,对于以|分割的操作符,则表明语法中必须从|分割的

操作符中选择一个。

下面的语法描述同上。

   在SELECT、、、FROM、、、WHERE语句中其中SELECT指定需要检索的字段,FROM指定要查询的表,WHERE指定选择纪录的条件,

另外还可以包含ORDERBY语句来制定排序纪录。

语法如下:

ORDERBYcolumn1|Integer[ASC|DESC]

   其中column1制定排序的字段,也可以使用Integer指定的字段索引来排序,ASC为升序、DESC为降序。

范例一:

找到Titles表中所有出版日期在1990年以后以及包含文字Beginner的书名

建立一个新工程,加入DAO定义库（点击菜单中的Project|References项,在列表中选择MicrosoftDAO3、51ObjectLibrary

（也可以就是更高版本的3、60,如果安装了的话）,然后在Form中加入一个ListBox控件。

在Form1的代码窗口中加入以下代码:

PrivateSubForm_Load（）

   DimrsTempAsRecordset

   DimdbTempAsDatabase

   DimastrAsString

   SetdbTemp=DBEngine（0）、OpenDatabase（"e:

\programfiles\microsoftvisualstudio\vb98\biblio、mdb",_

dbOpenSnapshot）

   astr="SELECT[Title]FROM[Titles]WHERE[YearPublished]>1990"&_

           "ANDTitleLIKE'*Beginner*'ORDERBYTitleDESC"

   SetrsTemp=dbTemp、OpenRecordset（astr）

   IfrsTemp、RecordCount>0Then

       rsTemp、MoveFirst

       DoUntilrsTemp、EOF

           List1、AddItemrsTemp!

[Title]

           rsTemp、MoveNext

       Loop

   EndIf

EndSub

   运行程序,List1中就会列出所有出版日期在1990年以后以及包含文字Beginner的书的书名并将书名按照降序进行排列。

   在上面的SELECT语句的WHERE中,我们使用了操作符号>来比较大小以及使用LIKE进行匹配。

在WHERE语句中,可以使

用的操作符有以下几类:

1、比较操作符

   包含=、<>、>、<、>=、<=等若干类

2、逻辑操作符

   包含以下若干类:

   LIKE、IN、BETWEEN、UNIQUE、ISNULL、EXISTS、ALL与ANY

   LIKE操作符通过使用通配符将一个值同其它值比较,在VB的SQL查询中,可以使用的通配符就是*与?

其中*代表任

意的若干个字符,而?

代表一个字符,例如上面语句中的LIKE'*Beginner*代表查找所有包含字符串Beginner的值。

不

过要注意的就是使用*与?

作为通配符就是VB的专利。

真正的SQL语言的通配符就是%与_,其中%代表任意多个字符,_

代表一个字符例如VB中的DataEnvironment使用的就就是%与_作为查询通配符。

另外MicrosoftJetEngine对于LIKE

操作符还提供了其它一些的过滤选项,具体的内容可参见MSDN索引中的LikeOperator条目。

   IN操作符用于比较某一个值就是否等于几个值中的一个值,例如下面的语句:

   "SELECT[Title],[YearPublished]FROM[Titles]WHERE[YearPublished]IN（1990,1991,1995）"

   将从表Titles中选择所有在1990、1991、1995年出版的书籍纪录。

   BETWEEN操作符勇于在两个值之间搜索,例如WHERE[YearPublished]BETWEEN1989AND1991指定出版日期必须

在1989年与1991年之间。

   对于逻辑操作符,可以结合NOT操作符实现改变查询条件的方向。

例如WHERE[YearPublished]NOTIN（1990,1991,1995）

3、连接符

   WHERE中的连接符包括AND与OR,使用AND时,所有查询都必须就是True时,条件才成立,而使用OR时,只要连接的

一个查询为True,条件就成立。

象上面的范例中,只有书名中包含"Beginner"同时又就是在1990年以后出版的纪录才会被

查询到。

   在使用比较操作符时要注意,在VB操作数据库引擎时对于查找的字符串,要使用单引号或者双引号将字符串括起来,例

如WHERE[Name]='LiGang'。

而对于日期类型的数据,要使用#号将日期括起来,例如WHERE[BirthDay]>#19980-10-01#

而在使用通配符与LIKE操作符时,需要使用引号而不就是#号,例如WHERE[BirthDay]LIKE"1990-01-*"。

引号告诉数据库引

擎将日期当字符串处理,而#号告诉数据库引擎将日期当数字处理。

对于WHERE语句所要使用的逻辑操作符,在下面的文章中还

要提到。

范例二:

如何在数据表中加入统计等功能

   SQL当中提供了一定数量的统计以及计算功能,其中统计函数主要有如下一些:

COUNT计算字段中的纪录数

SUM计算字段中的所有值的与

MAX获得字段中所有值中的最大值

MIN获得字段中所有值中的最小值

AVG计算字段中所有值的平均值

   计算符有+、-、*、/4种。

这些计算、统计函数的使用方法如下:

   functionnamefieldnameASoutfieldname

   其中functionname定义函数名、fieldname定义要操作的字段、outfieldname定义保存输出结果字段名称,下面就是范例

程序,首先建立一个数据库,将数据库保存为c:

\db4、mdb。

然后在数据库中加入一个名称为db1的表,表的结构如下:

字段名称产品编号成本  出厂价  销售数量

  A00020  ￥89、95  ￥95、00  900

  A00056  ￥54、00  ￥89、00  2400

  A00021  ￥2,300、00￥2,896、00600

  A10916  ￥120、00￥209、001200

  A00987  ￥907、00￥1,090、001000

   其中产品编号为文本类型、成本与出厂价字段为货币类型、销售数量为长整形数据。

保存表,在VB工程中加入DAO定义

库,在Form1中加入一个ListBox控件,在Form1代码窗口中加入以下代码:

PrivateSubForm_Load（）

   DimrsTempAsRecordset

   DimdbTempAsDatabase

   DimastrAsString

   SetdbTemp=DBEngine（0）、OpenDatabase（"c:

\db4、mdb",dbOpenSnapshot）

   astr="SELECTdb1、产品编号,（（db1、出厂价-db1、成本）/db1、成本）ASdRate,"&_

   "（db1、出厂价*db1、销售数量）ASeTotalFROMdb1ORDERBYdb1、产品编号"

   SetrsTemp=dbTemp、OpenRecordset（astr）

   IfrsTemp、RecordCount>0Then

       rsTemp、MoveFirst

       DoUntilrsTemp、EOF

           List1、AddItemrsTemp!

[产品编号]&"   "&rsTemp!

[dRate]&_

               "   "&rsTemp!

[eTotal]

           rsTemp、MoveNext

       Loop

   EndIf

EndSub

   在上面的程序段中,通过计算字符操作表中以有字段,然后再将结果输出到输出字段中保存。

二、JOIN、、、ON、、、语句

   JOIN、、、ON、、、语句就是SQL查询中用于连接多个表的语句,该语句的语法为:

   FROMtable1[INNER|OUTER|LEFT|RIGHT]JOINtable2ONtable1、field1compoprtable2、field2

   其中tabel1指定要查询的表,tabel2指定连接到tabel1的表,field1,field2指定连接字段名称,

compopr指定关系比较符,它可以就是大于、小于、等于、不等于等。

   范例三:

列出所有书籍以及它的作者

   在BIBLIO中,书籍的名称位于Titles表的Title字段中,而书籍的作者位于Authors表的Autoor字段中,而这两个表之间没

有相关联的字段,我们这时需要联合数据库中的TitleAuthor表,利用该表的Au_ID字段同Authors表中的Au_ID字段的关联以及

ISBN字段同Titles表中的ISBN字段的关联,范例如下:

PrivateSubForm_Load（）

   DimrsTempAsRecordset

   DimdbTempAsDatabase

   DimastrAsString

   SetdbTemp=DBEngine（0）、OpenDatabase（"e:

\programfiles\microsoftvisualstudio\vb98\biblio、mdb",dbOpenSnapshot）

   astr="SELECTAuthors、Author,Titles、TitleFROM"&_

   "（[TitleAuthor]INNERJOINTitlesON[TitleAuthor]、ISBN=Titles、ISBN）"&_

   "INNERJOINAuthorsON[TitleAuthor]、Au_ID=Authors、Au_ID"&_

   "WHERETitles、TitleLIKE'*Beginner*'"

   SetrsTemp=dbTemp、OpenRecordset（astr）

   IfrsTemp、RecordCount>0Then

       rsTemp、MoveFirst

       DoUntilrsTemp、EOF

           List1、AddItemrsTemp!

[Title]&"   "&rsTemp!

[Author]

           rsTemp、MoveNext

       Loop

   EndIf

EndSub

   在上面的范例中,我们使用两个INNERJION联合将Authors表中的Au_ID字段与Titles表中的ISBN字段连接到TitleAuthor

表中,然后在TitleAuthor表中查找Title字段中包含字符串“Beginner”的纪录并将Title字段与Author字段输出。

SQL查询语言基本教程

（2）

三、GROUPBY、、、语句

   GROUPBY、、、语句实现纪录分组功能,它通常需要与上面我们提到过的统计函数SUM、COUNT等联合使用,它的语法为:

SELECTcolumn1[,column2]

FROMtable1[,table2]

WHEREconditions

GROUPBYcolumn1[,column2]

ORDERBYcolumn1[,column2]

范例四:

计算学生成绩及总成绩

   我们依然使用上一章使用的数据库c:

\db4、mdb,在其中加入一个名字为db2的表,表的结构以及数据如下:

字段名学生科目成绩

张严语文86、5

李永语文93

王为语文91

张严数学96、5

李永数学90

王为数学87

张严英语80、5

李永英语94

王为英语98

   建立新工程,加入DAO定义库。

在Form1中加入一个ListBox控件,然后在Form_load中加入以下代码:

PrivateSubForm_Load（）

   DimrsTempAsRecordset

   DimdbTempAsDatabase

   DimastrAsString

   SetdbTemp=DBEngine（0）、OpenDatabase（"c:

\db4、mdb",dbOpenSnapshot）

   astr="SELECTSUM（db2、成绩）ASrTotal,FORMAT（（AVG（db2、成绩））,'###、#'）ASrAVG,"&_

           "（db2、学生）ASStudentFROMdb2GROUPBYdb2、学生"

   SetrsTemp=dbTemp、OpenRecordset（astr）

   IfrsTemp、RecordCount>0Then

       rsTemp、MoveFirst

       DoUntilrsTemp、EOF

           List1、AddItemrsTemp!

[Student]&Chr（5）&rsTemp!

[rTotal]&_

               "      "&rsTemp!

[rAVG]

           rsTemp、MoveNext

       Loop

   EndIf

EndSub

   在上面的代码中,我们利用GROUPBY将纪录根据学生姓名分组,再建立了两个统计字段rTotal与rAvg并分别利用

统计函数SUM与AVG分别统计各个分组的总成绩以及平均成绩。

要注意的就是,在SELECT语句中出现的字段,如果没有包含

在统计函数内的话,都要包含在GROUPBY子句中。

   另外在上面的SQL查询中我们还使用了FORMAT子句,这就是SQL中的转换与格式化语句中的一个,该语句的语法同VB中

的Format语句就是一样的,相似的语句还有FIX语句。

需要注意的一点就是,虽然在MicrosoftJETEngine中的SQL语法与

ANSI决大部分就是一样的,但就是有一些还就是保留了“微软特色”,特别就是象这一类的转换与格式化语句,例如FORMAT就就是

ANSI中没有的。

而象其它数据库,诸如oracle也有各自的SQL语法扩展。

在使用不同数据库进行SQL查询时要注意这一点。

   在GROUPBY语句中还可以连接使用HAVING子句。

该语句同GROUPBY的关系就如同WHERE子句同SELECT的关系类似,

WHERE子语句为SELECT所选择的列设置条件,而HAVING子语句就是给由GROUPBY创建的组设置条件。

例如如果将上面的范例

中的astr改变为如下的字符串:

astr="SELECTSUM（db2、成绩）ASrTotal,FORMAT（（AVG（db2、成绩））,'###、#'）"&_

           "ASrAVG,（db2、学生）ASStudentFROMdb2GROUPBYdb2、学生"&_

           "HAVING（AVG（db2、成绩））>=90"

则在List中就将只会列出平均成绩大于90分的学生的成绩与名字。

范例五:

获得分数高于总平均分数的学生及科目

   我们仍然使用上面建立的db2表。

建立新工程,加入DAO定义库。

在Form1中加入一个ListBox控件与一个Label控件

然后在Form_load中加入以下代码:

PrivateSubForm_Load（）

   DimrsTempAsRecordset

   DimdbTempAsDatabase

   DimastrAsString

   SetdbTemp=DBEngine（0）、OpenDatabase（"c:

\db4、mdb",dbOpenSnapshot）

   astr="SELECTFORMAT（AVG（db2、成绩）,'###、#'）AStAVGFROMdb2"

   SetrsTemp=dbTemp、OpenRecordset（astr）

   Label1、Caption="总平均成绩:

"&rsTemp!

[tAVG]

   rsTemp、Close

   SetrsTemp=Nothing

   astr="SELECTdb2、成绩,db2、学生,db2、科目FROMdb2WHEREdb2、成绩>"&_

           "（SELECTAVG（db2、成绩）FROMdb2）GROUPBYdb2、学生,db2、成绩,db2、科目"&_

           "ORDERBYdb2、学生"

   SetrsTemp=dbTemp、OpenRecordset（astr）

   IfrsTemp、RecordCount>0Then

       rsTemp、MoveFirst

       DoUntilrsTemp、EOF

           List1、AddItemrsTemp!

[学生]&"   "&rsTemp!

[科目]&"   "&rsTemp!

[成绩]

           rsTemp、MoveNext

       Loop

   EndIf

EndSub

   运行程序,在Lable1中列出总平均分数。

在List1中列出了学生姓名、获得高于平均分数的科目以及科目成绩。

在上面的查询中,我们使用了一个嵌套查询,首先在子查询中获得所有科目总的平均分数,然后在查询中查询成绩字段

值大于平均分数的纪录。

四、TRANSFORM、、、PIVOT、、、语句

   这就是MicrosoftJETEngine3、5以上版本所特有的SQL查询语句,该语句的特点就是可以建立一个交叉表格式的查询,

一个交叉表同电子表相类似。

该语句可以将表中的某些数据作为行,某些数据作为列建立交叉表。

该语句的语法如下:

TRANSFORMcondition[selectopreation]PIVOTcolumn

   其中condition就是在交叉表中要显示的数据,selectopreation就是一个SELECT、、、FROM、、、查询,该查询形成交叉表的

航信息,PIVOTrecordset中column为表中的一个字段,PIVOT子句使用该字段形成交叉表的列。

范例六:

建立学生成绩表

   我们还就是使用上面已经建立的db4、mdb中的db2表。

首先建立一个新的工程,然后在Form1中加入一个DataGrid控件,然后

向工程中加入一个DataEnvironment,在Connection1上点击鼠标右键,在菜单中选择properties、、、,在属性窗口的提供者

页面中选择MicrosoftJET4、0OLEDBProvider,在连接页面的数据库名称输入框中输入c:

\db4、mdb,然后点击测试

连接按钮,如果正常,点击确定退出。

再在Connection1上点击鼠标右键,在菜单中选择Addcommand建立一个名为Command1

的命令,点击Command1右键菜单,选择Properties、、、项,然后在Command1属性窗口的General页面中选择SQLStatement,

在SQL查询语句输入框中输入下面的查询:

TransformSUM（db2、成绩）ASiResSELECTdb2、学生FROMdb2GROUPBYdb2、学生Pivotdb2、科目

   注意文本框回自动换行,不要输入回车。

然后点击确定按钮。

   回到Form1,将DataGrid1的DataSource设置为DataEnvironment1,将DataMember设置为Command1,然后运行程序,可以瞧

到在DataGrid1中以表的形式列出了学生成绩,以学生为行,以成绩为列。

运行后得到的表格效果如下:

学生数学英语语文

李永909493

王为879891

张严96、580、586、5

   再回到DataEnvironment界面,双击Command1就可以瞧到查询建立的数据列,在上面的查询共建立了4个数据列,其中三个

分别就是科目分类,列中的数据为科目成绩,第一列为学生的名字,列中的数据为学生的名字。

   上面的查询中还使用了SUM子语句,这时因为对于GROUPBY来说,没有包含在统计函数内的列都要包含在GROUPBY中,如果

将字段db2、成绩包含在GROUPBY子语