VB中使用SQL语言基本

1、VB中使用SQL语言基本教程SQL语言基本教程（一）数据库查询语言（SQL是使用于关系数据库的标准语言，被很多类型的数据库所支持。本文将 以VB加DAO为例，来介绍 基本的SQL语法以及使用。本章将介绍的是 SQL语言中的基本查询语句，并带有范例。要更好的阅读本文，读者需要对数据库的基本结构以及术语有一个基本的了解，而且最好能有 一定的VB数据库编程经验。除非特别说明，本文将使用的数据库是 VB中附带的数据库。、SELECT.FROM.WHE语句语句的语法如下：SELECT all | * | dist inct colu mn1, colu mn2FROM table1, table2WHE

2、RE condition1 | expression1 AND condition2 | rxpression2在上面的语法中， 外的语句是必须的，而 内的是可选的，对于以 | 分割的操作符，则表明语 法中必须从 | 分割的 操作符中选择一个。下面的语法描述同上。在SELECT.FROM.WHERE句中其中SELEC指定需要检索的字段，FROM指定要查询的表,WHERE指定选择纪录的条件，另外还可以包含ORDER B语句来制定排序纪录。语法如下:ORDER BY column1 | Integer ASC | DESCASC为升序、DESC其中 column1 制定排序的字段，也可以使用 In

3、teger 指定的字段索引来排序， 为降序。范例一：找到Titles表中所有出版日期在1990年以后以及包含文字Beginner的书名 建立一个新工程，加入DAO定义库（点击菜单中的 Project | References项，在列表中选择 Microsoft DAO Object Library（也可以是更高版本的，如果安装了的话），然后在Form中加入一个ListBox控件。在Form1的代码窗口中加入以下代码：Private Sub Form_Load()Dim rsTemp As RecordsetDim dbTemp As DatabaseDim astr As StringSet

4、dbTemp = DBEngine(0).OpenDatabase(e:program filesmicrosoft visual studiovb98, _ dbOpenSnapshot)astr = SELECT Title FROM Titles WHERE Year Published 1990 & _AND Title LIKE *Beginner* ORDER BY Title DESCSet rsTemp = (astr)If 0 ThenDo UntilrsTemp!TitleLoopEnd IfEnd Sub运行程序，Listi中就会列出所有出版日期在1990年以后以及包含文

5、字Beginner的书的书名并 将书名按照降序进行排列。在上面的SELEC语句的WHERE中，我们使用了操作符号 来比较大小以及使用LIKE进行匹配。 在WHERE语句中，可以使 用的操作符有以下几类：1、比较操作符包含 =、 、 、 =、 = 等若干类2、逻辑操作符包含以下若干类：LIKE IN、BETWEEN UNIQUE IS NULL EXISTS ALL和 ANYLIKE操作符通过使用通配符将一个值同其它值比较，在VB的SQL查询中，可以使用的通配符是 * 和 其中 * 代表任意的若干个字符，而 代表一个字符，例如上面语句中的 LIKE *Beginner* 代表查找所有包含字符串B

6、eginner的值。不过要注意的是使用*和 作为通配符是VB的专利。真正的SQL语言的通配符是 %和_，其中% 代表任意多个字符， _代表一个字符例如 VB中的Data Environment使用的就是 Jet Engine对于 LIKE% 和 _ 作为查询通配符。另外 Microsoft操作符还提供了其它一些的过滤选项，具体的内容可参见MSDN索引中的Like Operator条目。IN 操作符用于比较某一个值是否等于几个值中的一个值，例如下面的语句：SELECT Title,Year Published FROM Titles WHERE Year Published IN (1990,1

7、991,1995)将从表Titles中选择所有在1990、1991、1995年出版的书籍纪录。BETWEEN操作符勇于在两个值之间搜索，例如 WHERE Year Published BETWEEN 1989 AND 1991 指定出版日期必须在 1989 年和 1991 年之间。对于逻辑操作符，可以结合NOT操作符实现改变查询条件的方向。 例如 WHERE Year PublishedNOT IN (1990,1991,1995)3、连接符WHERE中的连接符包括 AND和OR，使用AND时，所有查询都必须是True时，条件才成立, 而使用OR时，只要连接的一个查询为True，条件就成立。象

8、上面的范例中，只有书名中包含Beginner同时又是在1990年以后出版的纪录才会被查询到。在使用比较操作符时要注意，在VB操作数据库引擎时对于查找的字符串， 要使用单引号或者双 引号将字符串括起来，例如 WHERE Name=LiGang。 而对于日期类型的数据，要使用 #号将日期括起来，例如 WHEREBirthDay #19980-10-01#而在使用通配符和LIKE操作符时，需要使用引号而不是#号，例如 WHERE BirthDay LIKE 1990-01-* 。引号告诉数据库引擎将日期当字符串处理，而#号告诉数据库引擎将日期当数字处理。对于 WHERE语句所要使用的 逻辑操作符，在

9、下面的文章中还要提到。范例二：如何在数据表中加入统计等功能SQL当中提供了一定数量的统计以及计算功能，其中统计函数主要有如下一些:COUNT 计算字段中的纪录数SUM 计算字段中的所有值的和MAX 获得字段中所有值中的最大值MIN 获得字段中所有值中的最小值AVG 计算字段中所有值的平均值计算符有+、*、/ 4 种。这些计算、统计函数的使用方法如下:functionname fieldname AS outfieldname其中 functionname 定义函数名、 fieldname 定义要操作的字段、 outfieldname 定义保存输出结果 字段名称，下面是范例程序，首先建立一个数据

10、库，将数据库保存为的结构如下:。然后在数据库中加入一个名称为 db1 的表，表字段名称成本 出厂价 销售数量A00020Y Y 900A00056Y Y 2400A0002iY 2, Y 2, 600Ai09i6Y Y i200A00987Y Y i, i000产品编号其中产品编号为文本类型、成本和出厂价字段为货币类型、销售数量为长整形数据。保存表， 在 VB 工程中加入 DAO 定义 库，在Formi中加入一个ListBox控件，在Formi代码窗口中加入以下代码:Private Sub Form_Load()Dim rsTemp As RecordsetDim dbTemp As Data

11、baseDim astr As StringSet dbTemp = DBEngine(0).OpenDatabase(c:, dbOpenSnapshot)astr = SELECT db产品编号,(db1.出厂价-db1.成本)/db1.成本)AS dRate, & _(db1.出厂价 * db1.销售数量)AS eTotal FROM db1 ORDER BY d产品编号Set rsTemp = (astr)If 0 ThenDo UntilrsTe mp!产品编号& & rsTem p!dRate & _ & rsTemp!eTotalLoopEnd IfEnd Sub在上面的程序段中

12、，通过计算字符操作表中以有字段，然后再将结果输出到输出字段中保存。、JOIN.ON.语. 句JOIN.ON.语句是SQL查询中用于连接多个表的语句，该语句的语法为:FROM table1 INNER|OUTER|LEFT|RIGHT JOIN table2 ONcompopr其中tabel1指定要查询的表，tabel2指定连接到tabel1的表，field1, field2指定连接字段名称, compopr 指定关系比较符，它可以是大于、小于、等于、不等于等。范例三: 列出所有书籍以及它的作者在BIBLIO中，书籍的名称位于 Titles表的Title字段中，而书籍的作者位于 Authors表

13、的Autoor 字段中，而这两个表之间没 有相关联的字段，我们这时需要联合数据库中的 Title Author表，利用该表的Au_ID字段同Authors 表中的Au_ID字段的关联以及ISBN字段同Titles表中的ISBN字段的关联，范例如下:Private Sub Form_Load()Dim rsTemp As RecordsetDim dbTemp As DatabaseDim astr As StringSet dbTemp = DBEngine(0).OpenDatabase(e:program filesmicrosoft visual studiovb98, dbOpenSn

14、apshot)astr = SELECT , FROM & _(Title Author INNER JOIN Titles ON Title Author.ISBN = & _INNER JOIN Authors ON Title Author.Au_ID = & _WHERE LIKE *Beginner*Set rsTemp = (astr)If 0 ThenDo UntilrsTemp!Title & & rsTemp!AuthorLoopEnd IfEnd Sub在上面的范例中，我们使用两个INNER JlOh联合将Authors表中的Au_ID字段 和Titles表中的 ISBN字

15、段连接到Title Author表中，然后在Title Author表中查找Title字段中包含字符串“Beginne的纪录并将Title字段和Author 字段输出。SQL查询语言基本教程（2）三、GROUP BY语句SUM、COUNTGROUP BY.语句实现纪录分组功能，它通常需要和上面我们提到过的统计函数 等联合使用，它的语法为：SELECT column1, column2FROM table1,table2WHERE conditionsGROUP BY column1, column2ORDER BY column1, column2范例四：计算学生成绩及总成绩我们依然使用上一章

16、使用的数据库如下：，在其中加入一个名字为 db2 的表，表的结构以及数据字段名 学生 科目 成绩张严语文李永语文93王为语文91张严数学李永数学90王为数学87张严英语李永英语94王为英语98建立新工程，加入DAO定义库。在Form1中加入一个ListBox控件，然后在Form_load中加入以 下代码：Private Sub Form_Load()Dim rsTemp As RecordsetDim dbTemp As DatabaseDim astr As StringSet dbTemp = DBEngine(0).OpenDatabase(c:, dbOpenSnapshot)astr

17、 = SELECT SUM(db成绩)AS rTotal, FORMAT(AVG(db成 绩),#) AS rAVG, & _(db2.学生)AS Stude nt FROM db2 GROU P BY d学生Set rsTemp = (astr)If 0 ThenDo UntilrsTemp!Student & Chr(5) & rsTemp!rTotal & _ & rsTemp!rAVGLoopEnd IfEnd Sub在上面的代码中，我们利用 GROU P BY!纪录根据学生姓名分组，再建立了两个统计字段rTotal和 rAvg 并分别利用SELECTS 句中统计函数SUM和AVG分别

18、统计各个分组的总成绩以及平均成绩。要注意的是，在 出现的字段，如果没有包含 在统计函数内的话，都要包含在 GROU P BY子句中。另外在上面的SQL查询中我们还使用了 FORMAT子句，这是SQL中的转换和格式化语句中的一 个，该语句的语法同VB中 的Format语句是一样的，相似的语句还有FIX语句。需要注意的一点是，虽然在Microsoft JET Engine 中的SQL语法和ANSI决大部分是一样的，但是有一些还是保留了微软特色”特别是象这一类的转换和格式化语句，例如FORMAT就是ANSI中没有的。而象其它数据库，诸如 oracle也有各自的SQL语法扩展。在使用不同数据库进行 S

19、QL查询时要注意这一点。在GROUP BY语句中还可以连接使用 HAVING子句。该语句同GROUP BY勺关系就如同 WHERE 子句同SELECT勺关系类似，WHERE子语句为SELECT所选择的列设置条件，而 HAVING子语句是给由GROUP BY建的组设置 条件。例如如果将上面的范例 中的 astr 改变为如下的字符串：astr = SELECT SUM(db成 绩)AS rTotal, FORMAT(AVG(db 成绩),#) & _AS rAVG, (db2学 生)AS Stude nt FROM db2 GROU P BY d!学生& _HAVING (AVG(db2成绩)=9

20、0则在List中就将只会列出平均成绩大于 90分的学生的成绩和名字。范例五：获得分数高于总平均分数的学生及科目我们仍然使用上面建立的db2表。建立新工程，加入DAO定义库。在Form1中加入一个ListBox 控件和一个Label控件 然后在 Form_load 中加入以下代码：Private Sub Form_Load()Dim rsTemp As RecordsetDim dbTemp As DatabaseDim astr As StringSet dbTemp = DBEngine(0).OpenDatabase(c:, dbOpenSnapshot)astr = SELECT FOR

21、MAT(AVG(d成绩),#) AS tAVG FROM db2Set rsTemp = (astr)= 总平均成绩： & rsTemp!tAVGSet rsTemp = Nothingastr = SELECT db成绩,db2学生,db2.科目 FROM db2 WHERE db成绩 & _(SELECT AVG(db成绩)FROM db2) GROUP BY db学生,db2.成绩,db2.科目& _ ORDER BY db学生Set rsTemp = (astr)If 0 ThenDo UntilrsTemp!学生& & rsTemp!科目& & rsTemp!成绩LoopEnd If

22、End Sub然后在运行程序，在 Lable1 中列出总平均分数。在 List1 中列出了学生姓名、获得高于平均分数的科目 以及科目成绩。在上面的查询中， 我们使用了一个嵌套查询， 首先在子查询中获得所有科目总的平均分数，查询中查询成绩字段 值大于平均分数的纪录。四、 TRANSFORM.PIVOT.语. 句这是Microsoft JET Engine以上版本所特有的SQL查询语句，该语句的特点是可以建立一个交叉 表格式的查询, 一个交叉表同电子表相类似。该语句可以将表中的某些数据作为行，某些数据作为列建立交叉表。 该语句的语法如下:TRANSFORM condition select opr

23、eation PIVOT column其中 condition 是在交叉表中要显示的数据， select opreation 是一个 SELECT.FROM.查. 询，该 查询形成交叉表的 航信息，PIVOT recordset中column为表中的一个字段，PIVOT子句使用该字段形成交叉表的列。范例六：建立学生成绩表我们还是使用上面已经建立的中的 db2 表。首先建立一个新的工程，然后在 Form1 中加入一个DataGrid 控件，然后 向工程中加入一个 DataEnvironment,在Connection1上点击鼠标右键，在菜单中选择 properties., 在属性窗口的 提供者页

24、面中选择 Microsoft JET OLE DB Provider，在 连接 页面的数据库名称输入框中输入c:，然后点击 测试 连接 按钮，如果正常，点击确定退出。再在 Connection1 上点击鼠标右键，在菜单中选择 Addcommand 建立一个名为 Command1的命令，点击 Command1 右键菜单，选择 Properties. 项，然后在 Command1 属性窗口的 General 页面中选择 SQL Statemen，t在SQL查询语句输入框中输入下面的查询:Transform SUM(db2成绩)AS iRes SELECT d学生 FROM db2 GROUP BY

25、 dl学生 Pivot db2科目注意文本框回自动换行，不要输入回车。然后点击确定按钮。回到 Formi，将 DataGridl 的 DataSource设置为 DataEnvironment1，将 DataMember 设置为Comma nd1，然后运行程序，可以看到在 DataGrid1 中以表的形式列出了学生成绩，以学生为行，以成绩为列。运行后得到的表格效果 如下:学生数学 英语 语文李永90 94 93王为87 98 91张严再回到DataEnvironment界面，双击Command1就可以看到查询建立的数据列，在上面的查询 共建立了 4 个数据列，其中三个 分别是科目分类，列中的数

26、据为科目成绩，第一列为学生的名字，列中的数据为学生的名字。上面的查询中还使用了 SUM子语句，这时因为对于GROU P B侏说，没有包含在统计函数内的 列都要包含在 GROUP BYK 如果 将字段db2.成绩包含在GROUP BY子语句中，就会使最终结果出现 9行而不是3行。由于每个学 生的每科成绩只有一个，所以可以 使用SUM函数将字段db2.成绩排除在GROUP BY外面。在下一章内，将向大家介绍SQL语言中的数据库结构定义部分以及数据操纵部分。SQL查询语言基本教程（3）四、CREATE TABL语句CREATE TABL语句的语法为:CREATE TABLE table (field

27、1 type (size) NOT NULL index1 , field2 type (size) NOT NULL index2 , . , CONSTRAINT multifieldindex , .) table新建立的表的名称field1 type (size) NOT NULL index1field1 为字段名称， Type 为字段数据类型， size 为字段宽度，下面表详细 描述了 Type 的取值以及描述Type Jet数据类型 描述BIT Yes/No 逻辑类型BYTE Numberic-Byte 字节数字COUNTER Counter 自动编号CURRENCY Curre

28、 ncy 货币数字DATETIME Date/Time 日期、时间DOUBLE Numberic-Double 双精度浮点数字LONG Numberic-Long 长整数LONGBINARY Ole Object OLE object 类型LONGTEXT Memo 备注类型SHORT Numberic-Integer 整数SINGLE Numberic-Single 单精度浮点数字TEXT Text 文本NOT NULL该字段下的值不能为空index1定义字段约束 范例七：建立学生数据表DAO定义库，然后在Form1的Load我们还是使用前面提到的文件，建立一个新的工程，加入 事件中加入以下

29、代码：Private Sub Form_Load()Dim astr As StringDim dbAdd As Database= vbHourglassSet dbAdd = (0).OpenDatabase(c:)astr = CREATE TABLE tbl_students & _(stdID COUNTER PRIMARY KEY, & _stdName TEXT(12) NOT NULL, & _stdAge SHORT, & _stdBir DATETIME, & _stdSex BIT)astrastr= vbDefaultMsgBox 数据库建立成功 .Set dbAdd

30、= NothingEnd Sub运行程序，这时会弹出数据库建立成功的消息框，使用Access打开，可以看到表tbl_students已经加入到数据库中了。在上面的范例中，我们加入一个名称为动编号类型，并作为主键；tbl_students的表并加入5个字段：stdID:学号，自stdName：学生姓名，长度为12的文本类型,出生日期，日期类型；并且不能为空；stdAge：学生年龄，整数类型；stdBir：stdSex：性别，逻辑类型。如果要删除数据库中的表，只要使用语句DROP TABLES可以实现表的删除，使用范例如下:DROP TABLE tblname其中 tblname 为表格的名称。五

31、：INSERT INTO语句INSERT INTO语句实现向表中插入数据，该函数可以将一个已存在的表中的数据插入新表，也可 以将自定义的值插入新表。插入已有的表中的数据的函数语法如下：INSERT INTO target (field1, field2, .) IN externaldatabaseSELECT source.sfield1, sfield2, .FROM tableexpressiontarget添加数据的表的名称field1, field2需要添加数据的字段名称externaldatabase附加的数据库名称source拷贝数据的表的名称sfield1 sfield2拷贝数

32、据的字段的名称插入新数据的语法如下：INSERT INTO target (field1, field2, .)VALUES (value1, value2, .)value1, value2插入的值， value1 将插入 field1，value2 将插入 field2 。范例八：向表中插入新数据和其它表中的数据我们在前面的范例中，已经向 c: 中加入了一个名为 db2 的表和 tbl_students 的表，其中 db2 中包含三个学生 的信息，tbl_students没有包含纪录，现在我们要将 db2表中的学生纪录中的学生姓名值附加到 tbl_students 表的 stdName 字

33、段中。下面是具体的添加范例：建立一个新的工程，加入 DAO定义库，然后在Formi的Form_Load事件中加入以下代码:Private Sub Form_Load()Dim astr As StringDim dbAdd As Database= vbHourglassSet dbAdd = (0).OpenDatabase(c:)astr = INSERT INTO tbl_students (stdName) & _SELECT DISTINCT d学生 FROM db2 astrastr= vbDefaultMsgBox 数据添加成功 .Set dbAdd = NothingEnd S

34、ub运行程序，会弹出数据添加成功的消息框。打开，可以看到db2表中的学生姓名已经添加到tbl_students 中了。将 astr 改变为下面的命令字符串就可以增加一条新纪录:astr = INSERT INTO tbl_students (stdName,stdAge,stdBir,stdSex) & _VALUES 李想，15, #1985-10-10#, 1)六、DELETE语句DELETE语句从表中删除纪录（数据行）该语句的语法如下:DELETE FROM tableWHERE criteriatable要删除纪录的表的名称criteria删除条件DELETES句将表table中符合条

35、件criteria的级路删除。例如下面的语句将删除表tbi中count字段大于 10 的纪录：(DELETE FROM tbl WHERE 10)七、ALTER TABL语句ALTER TABL语句执行改变数据库结构的工作，它可以向表中添加或者删除一列。函数的语法如 下：ALTER TABLE table ADD COLUMN field type(size) NOT NULL CONSTRAINT index |CONSTRAINT multifieldindex |DROP COLUMN field I CONSTRAINT indexname ALTER TABL语句中包含两个子语句：ADD COLUMN或者 DROP COLUMN 其中 ADD COLUMN执 行向表中添加列的工作，DROP COLUMN执行删除表中列的工作。