VB中使用SQL语言基本

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

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

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

4、r As String Set dbTemp = DBEngine(0).OpenDatabase(e:program filesmicrosoft visual studiovb98biblio.mdb, _dbOpenSnapshot) astr = SELECT Title FROM Titles WHERE Year Published 1990 & _ AND Title LIKE *Beginner* ORDER BY Title DESC Set rsTemp = dbTemp.OpenRecordset(astr) If rsTemp.RecordCount 0 Then rs

5、Temp.MoveFirst Do Until rsTemp.EOF List1.AddItem rsTemp!Title rsTemp.MoveNext Loop End IfEnd Sub 运行程序，List1中就会列出所有出版日期在1990年以后以及包含文字Beginner的书的书名并将书名按照降序进行排列。 在上面的SELECT语句的WHERE中，我们使用了操作符号 来比较大小以及使用LIKE进行匹配。在WHERE语句中，可以使用的操作符有以下几类：1、比较操作符 包含 =、=、 #19980-10-01#而在使用通配符和LIKE操作符时，需要使用引号而不是#号，例如 WHERE Bi

6、rthDay LIKE 1990-01-*。引号告诉数据库引擎将日期当字符串处理，而#号告诉数据库引擎将日期当数字处理。对于WHERE语句所要使用的逻辑操作符，在下面的文章中还要提到。范例二：如何在数据表中加入统计等功能 SQL当中提供了一定数量的统计以及计算功能，其中统计函数主要有如下一些：COUNT 计算字段中的纪录数SUM 计算字段中的所有值的和MAX 获得字段中所有值中的最大值MIN 获得字段中所有值中的最小值AVG 计算字段中所有值的平均值 计算符有 +、-、*、/ 4种。这些计算、统计函数的使用方法如下： functionname fieldname AS outfieldname

7、 其中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.00 600 A10916 ￥120.00 ￥209.00 1200 A00987 ￥907.00 ￥1,090.00 1000 其中产品编号为文本类

8、型、成本和出厂价字段为货币类型、销售数量为长整形数据。保存表，在VB工程中加入DAO定义库，在Form1中加入一个ListBox控件，在Form1代码窗口中加入以下代码：Private Sub Form_Load() Dim rsTemp As Recordset Dim dbTemp As Database Dim astr As String Set dbTemp = DBEngine(0).OpenDatabase(c:db4.mdb, dbOpenSnapshot) astr = SELECT db1.产品编号, (db1.出厂价 - db1.成本)/db1.成本) AS dRate,

9、 & _ (db1.出厂价 * db1.销售数量)AS eTotal FROM db1 ORDER BY db1.产品编号 Set rsTemp = dbTemp.OpenRecordset(astr) If rsTemp.RecordCount 0 Then rsTemp.MoveFirst Do Until rsTemp.EOF List1.AddItem rsTemp!产品编号 & & rsTemp!dRate & _ & rsTemp!eTotal rsTemp.MoveNext Loop End IfEnd Sub 在上面的程序段中，通过计算字符操作表中以有字段，然后再将结果输出到输

10、出字段中保存。二、JOIN.ON.语句 JOIN.ON. 语句是SQL查询中用于连接多个表的语句，该语句的语法为： FROM table1 INNER|OUTER|LEFT|RIGHT JOIN table2 ON table1.field1 compopr table2.field2 其中tabel1指定要查询的表，tabel2指定连接到tabel1的表，field1, field2指定连接字段名称，compopr指定关系比较符，它可以是大于、小于、等于、不等于等。 范例三： 列出所有书籍以及它的作者 在BIBLIO中，书籍的名称位于Titles表的Title字段中，而书籍的作者位于Auth

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

12、.mdb, dbOpenSnapshot) astr = SELECT Authors.Author, Titles.Title FROM & _ (Title Author INNER JOIN Titles ON Title Author.ISBN = Titles.ISBN) & _ INNER JOIN Authors ON Title Author.Au_ID = Authors.Au_ID & _ WHERE Titles.Title LIKE *Beginner* Set rsTemp = dbTemp.OpenRecordset(astr) If rsTemp.RecordCo

13、unt 0 Then rsTemp.MoveFirst Do Until rsTemp.EOF List1.AddItem rsTemp!Title & & rsTemp!Author rsTemp.MoveNext Loop End IfEnd Sub 在上面的范例中，我们使用两个INNER JION联合将Authors表中的Au_ID字段 和Titles表中的ISBN字段连接到Title Author表中，然后在Title Author表中查找Title字段中包含字符串“Beginner”的纪录并将Title字段和Author字段输出。SQL查询语言基本教程（2）三、GROUP BY.语句

14、 GROUP BY. 语句实现纪录分组功能，它通常需要和上面我们提到过的统计函数 SUM、COUNT 等联合使用，它的语法为：SELECT column1, column2 FROM table1,table2 WHERE conditions GROUP BY column1, column2ORDER BY column1, column2范例四：计算学生成绩及总成绩 我们依然使用上一章使用的数据库 c:db4.mdb，在其中加入一个名字为 db2 的表，表的结构以及数据如下：字段名 学生 科目 成绩张严 语文 86.5李永 语文 93王为 语文 91张严 数学 96.5李永 数学 90王

15、为 数学 87张严 英语 80.5李永 英语 94王为 英语 98 建立新工程，加入DAO定义库。在Form1中加入一个ListBox控件，然后在Form_load中加入以下代码：Private Sub Form_Load() Dim rsTemp As Recordset Dim dbTemp As Database Dim astr As String Set dbTemp = DBEngine(0).OpenDatabase(c:db4.mdb, dbOpenSnapshot) astr = SELECT SUM(db2.成绩)AS rTotal, FORMAT(AVG(db2.成绩),

16、#.#) AS rAVG, & _ (db2.学生) AS Student FROM db2 GROUP BY db2.学生 Set rsTemp = dbTemp.OpenRecordset(astr) If rsTemp.RecordCount 0 Then rsTemp.MoveFirst Do Until rsTemp.EOF List1.AddItem rsTemp!Student & Chr(5) & rsTemp!rTotal & _ & rsTemp!rAVG rsTemp.MoveNext Loop End IfEnd Sub 在上面的代码中，我们利用GROUP BY将纪录根

17、据学生姓名分组，再建立了两个统计字段rTotal和rAvg并分别利用统计函数SUM和AVG分别统计各个分组的总成绩以及平均成绩。要注意的是，在SELECT语句中出现的字段，如果没有包含在统计函数内的话，都要包含在GROUP BY子句中。 另外在上面的SQL查询中我们还使用了FORMAT子句，这是SQL中的转换和格式化语句中的一个，该语句的语法同VB中的Format语句是一样的，相似的语句还有FIX语句。需要注意的一点是，虽然在Microsoft JET Engine 中的SQL语法和ANSI决大部分是一样的，但是有一些还是保留了“微软特色”，特别是象这一类的转换和格式化语句，例如FORMAT就

18、是ANSI中没有的。而象其它数据库，诸如oracle也有各自的SQL语法扩展。在使用不同数据库进行SQL查询时要注意这一点。 在GROUP BY 语句中还可以连接使用HAVING子句。该语句同GROUP BY的关系就如同WHERE子句同SELECT的关系类似，WHERE子语句为SELECT所选择的列设置条件，而HAVING子语句是给由GROUP BY创建的组设置条件。例如如果将上面的范例中的astr改变为如下的字符串：astr = SELECT SUM(db2.成绩)AS rTotal, FORMAT(AVG(db2.成绩),#.#) & _ AS rAVG, (db2.学生) AS Stud

19、ent FROM db2 GROUP BY db2.学生 & _ HAVING (AVG(db2.成绩)=90则在List中就将只会列出平均成绩大于90分的学生的成绩和名字。范例五：获得分数高于总平均分数的学生及科目 我们仍然使用上面建立的db2表。建立新工程，加入DAO定义库。在Form1中加入一个ListBox控件和一个Label控件然后在Form_load中加入以下代码：Private Sub Form_Load() Dim rsTemp As Recordset Dim dbTemp As Database Dim astr As String Set dbTemp = DBEngin

20、e(0).OpenDatabase(c:db4.mdb, dbOpenSnapshot) astr = SELECT FORMAT(AVG(db2.成绩),#.#) AS tAVG FROM db2 Set rsTemp = dbTemp.OpenRecordset(astr) Label1.Caption = 总平均成绩： & rsTemp!tAVG rsTemp.Close Set rsTemp = Nothing astr = SELECT db2.成绩, db2.学生,db2.科目 FROM db2 WHERE db2.成绩 & _ (SELECT AVG(db2.成绩) FROM d

21、b2) GROUP BY db2.学生,db2.成绩,db2.科目 & _ ORDER BY db2.学生 Set rsTemp = dbTemp.OpenRecordset(astr) If rsTemp.RecordCount 0 Then rsTemp.MoveFirst Do Until rsTemp.EOF List1.AddItem rsTemp!学生 & & rsTemp!科目 & & rsTemp!成绩 rsTemp.MoveNext Loop End IfEnd Sub 运行程序，在Lable1中列出总平均分数。在List1中列出了学生姓名、获得高于平均分数的科目以及科目成绩

22、。在上面的查询中，我们使用了一个嵌套查询，首先在子查询中获得所有科目总的平均分数，然后在查询中查询成绩字段值大于平均分数的纪录。四、TRANSFORM.PIVOT. 语句 这是Microsoft JET Engine 3.5以上版本所特有的SQL查询语句，该语句的特点是可以建立一个交叉表格式的查询，一个交叉表同电子表相类似。该语句可以将表中的某些数据作为行，某些数据作为列建立交叉表。该语句的语法如下：TRANSFORM condition select opreation PIVOT column 其中condition是在交叉表中要显示的数据，select opreation 是一个SELE

23、CT.FROM. 查询，该查询形成交叉表的航信息，PIVOT recordset中column为表中的一个字段，PIVOT子句使用该字段形成交叉表的列。范例六：建立学生成绩表 我们还是使用上面已经建立的db4.mdb中的db2表。首先建立一个新的工程，然后在Form1中加入一个DataGrid控件，然后向工程中加入一个DataEnvironment，在Connection1上点击鼠标右键，在菜单中选择 properties. ,在属性窗口的 提供者页面中选择 Microsoft JET 4.0 OLE DB Provider ，在 连接 页面的数据库名称输入框中输入 c:db4.mdb ，然后

24、点击 测试连接 按钮，如果正常，点击确定退出。再在Connection1上点击鼠标右键，在菜单中选择 Add command 建立一个名为Command1的命令，点击Command1右键菜单，选择 Properties. 项，然后在Command1属性窗口的General页面中选择 SQL Statement，在SQL查询语句输入框中输入下面的查询：Transform SUM(db2.成绩)AS iRes SELECT db2.学生 FROM db2 GROUP BY db2.学生 Pivot db2.科目 注意文本框回自动换行，不要输入回车。然后点击确定按钮。 回到Form1，将DataGr

25、id1的DataSource设置为DataEnvironment1，将DataMember设置为Command1，然后运行程序，可以看到在DataGrid1中以表的形式列出了学生成绩，以学生为行，以成绩为列。运行后得到的表格效果如下：学生 数学 英语 语文李永 90 94 93王为 87 98 91张严 96.5 80.5 86.5 再回到DataEnvironment界面，双击Command1就可以看到查询建立的数据列，在上面的查询共建立了4个数据列，其中三个分别是科目分类，列中的数据为科目成绩，第一列为学生的名字，列中的数据为学生的名字。 上面的查询中还使用了SUM子语句，这时因为对于GR

26、OUP BY来说，没有包含在统计函数内的列都要包含在GROUP BY中，如果将字段db2.成绩包含在 GROUP BY 子语句中，就会使最终结果出现9行而不是3行。由于每个学生的每科成绩只有一个，所以可以使用SUM函数将字段db2.成绩排除在GROUP BY外面。 在下一章内，将向大家介绍SQL语言中的数据库结构定义部分以及数据操纵部分。SQL查询语言基本教程（3）四、CREATE TABLE 语句 CREATE TABLE 语句的语法为： CREATE TABLE table (field1 type (size) NOT NULL index1 , field2 type (size) N

27、OT NULL index2 , . , CONSTRAINT multifieldindex , .)table 新建立的表的名称field1 type (size) NOT NULL index1 field1为字段名称，Type为字段数据类型，size 为字段宽度，下面表详细描述了Type 的取值以及描述Type Jet数据类型 描述BIT Yes/No 逻辑类型 BYTE Numberic-Byte 字节数字COUNTER Counter 自动编号CURRENCY Currency 货币数字DATETIME Date/Time 日期、时间DOUBLE Numberic-Double 双

28、精度浮点数字LONG Numberic-Long 长整数LONGBINARY Ole Object OLE object类型LONGTEXT Memo 备注类型SHORT Numberic-Integer 整数SINGLE Numberic-Single 单精度浮点数字TEXT Text 文本NOT NULL 该字段下的值不能为空index1 定义字段约束范例七：建立学生数据表 我们还是使用前面提到的db4.mdb文件，建立一个新的工程，加入DAO定义库，然后在Form1的Load事件中加入以下代码：Private Sub Form_Load() Dim astr As String Dim

29、dbAdd As Database Screen.MousePointer = vbHourglass Set dbAdd = DBEngine.Workspaces(0).OpenDatabase(c:db4.mdb) astr = CREATE TABLE tbl_students & _ (stdID COUNTER PRIMARY KEY, & _ stdName TEXT(12) NOT NULL, & _ stdAge SHORT, & _ stdBir DATETIME, & _ stdSex BIT) Debug.Print astr dbAdd.Execute astr Sc

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

31、数据库中的表，只要使用语句 DROP TABLE 就可以实现表的删除，使用范例如下： DROP TABLE tblname 其中tblname为表格的名称。五：INSERT INTO 语句 INSERT INTO语句实现向表中插入数据，该函数可以将一个已存在的表中的数据插入新表，也可以将自定义的值插入新表。 插入已有的表中的数据的函数语法如下： INSERT INTO target (field1, field2, .) IN externaldatabase SELECT source.sfield1, sfield2, . FROM tableexpression target添加数据的表

32、的名称 field1, field2需要添加数据的字段名称 externaldatabase附加的数据库名称 source拷贝数据的表的名称 sfield1 sfield2拷贝数据的字段的名称 插入新数据的语法如下： INSERT INTO target (field1, field2, .) VALUES (value1, value2, .) value1, value2插入的值，value1将插入field1，value2将插入field2。 范例八：向表中插入新数据和其它表中的数据 我们在前面的范例中，已经向 c:db4.mdb 中加入了一个名为 db2的表和tbl_students的

33、表，其中db2中包含三个学生的信息，tbl_students没有包含纪录，现在我们要将db2表中的学生纪录中的学生姓名值附加到tbl_students表的stdName字段中。下面是具体的添加范例： 建立一个新的工程，加入DAO定义库，然后在Form1的Form_Load事件中加入以下代码：Private Sub Form_Load() Dim astr As String Dim dbAdd As Database Screen.MousePointer = vbHourglass Set dbAdd = DBEngine.Workspaces(0).OpenDatabase(c:db4.m

34、db) astr = INSERT INTO tbl_students (stdName) & _ SELECT DISTINCT db2.学生 FROM db2 Debug.Print astr dbAdd.Execute astr Screen.MousePointer = vbDefault MsgBox 数据添加成功. dbAdd.Close Set dbAdd = NothingEnd Sub 运行程序，会弹出数据添加成功的消息框。打开db4.mdb ，可以看到db2表中的学生姓名已经添加到tbl_students中了。 将astr改变为下面的命令字符串就可以增加一条新纪录： ast

35、r = INSERT INTO tbl_students (stdName,stdAge,stdBir,stdSex) & _ VALUES (李想, 15, #1985-10-10#, 1)六、DELETE 语句 DELETE 语句从表中删除纪录（数据行）该语句的语法如下： DELETE FROM table WHERE criteria table要删除纪录的表的名称 criteria删除条件 DELETE语句将表table中符合条件criteria的级路删除。例如下面的语句将删除表 tbl中count字段大于10的纪录： dbfText.Execute(DELETE FROM tbl W

36、HERE tbl.count10)七、ALTER TABLE 语句 ALTER TABLE 语句执行改变数据库结构的工作，它可以向表中添加或者删除一列。函数的语法如下： ALTER TABLE table ADD COLUMN field type(size) NOT NULL CONSTRAINT index | CONSTRAINT multifieldindex | DROP COLUMN field I CONSTRAINT indexname ALTER TABLE 语句中包含两个子语句：ADD COLUMN或者DROP COLUMN，其中ADD COLUMN执行向表中添加列的工作，DROP COLUMN执行删除表中列的工作。另外CONSTRAINT子语句执行建立数据索引的工作。语法中的其它要素解释如下： table要改变结构的表的名称 field要添加/删除的列的名称 type(size)添加列的数据类型以及数据长度 index索引的名称 multifieldindex多字段索引名称范例九：向tbl_students中添加一列 建立一个新的工程，加入DAO定义库，然后在Form1的Form_