vba sql查询

sql一般用在多条件查询，对数据库读写等。

1. VBA如何建立sql连接

A．在VBA Project 窗口中，“工具—引用—Microsoft ActiveX Data Objects 2.0 Library”，如下图所示

B． VBA语句

Private Sub CommandButton1_Click()

Dim cnn As ADODB.Connection’定义变量

Dim sql$, Stext$, i As Integer

Worksheets(3).Cells.Clear

Stext = IIf(TextBox1.Value = "", "*", TextBox1.Value)

Set cnn = CreateObject("adodb.connection")’生成对象

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0Extended Properties=Excel 8.0Data Source=" &ThisWorkbook.FullName’打开数据库

If TextBox1 = "" Then

sql = "select * from [sheet2$]"’sql语句

Else

sql = "select * from [sheet2$] where 姓名 like '" &TextBox1.Value &"'"

End If

Worksheets(3).[a1].CopyFromRecordset cnn.Execute(sql)’执行sql语句

cnn.Close

Set cnn = Nothing

End Sub

2. sub text()

Dim cnn As ADODB.Connection’定义变量

Dim rs as adodb.recordset

Set cnn = CreateObject("adodb.connection")’生成对象,若定义变量时为Dim cnn As New ADODB.Connection，此句可省略

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0Extended Properties=Excel 8.0Data Source=" &ThisWorkbook.FullName’打开数据库

sql = "select * from [sheet2$] where 姓名 like '" &TextBox1.Value &"'"

rs.open sql,cnn,adOpenKeyset.adLockOptimistic’生成查询记录，为后面出书数据准备

Worksheets(3).[a1].CopyFromRecordset rs

end sub

________________________________________________________________________

以下根据网络整理

2 Select SQL语句

SELECT * FROM table_name WHERE column1 = xxx [AND column2 >yyy] [OR column3 <>zzz]

'*'表示全部的栏位都列出来，如果不全部列出，则可以写字段名称如 学号，姓名……；WHERE 之后是接条件式，把符合条件的资料列出来，table_name如果为工作表名称，书写格式[工作表名$]，下同。

SELECT column1,AVG(column2) FROM table_name GROUP BY column1 HAVING AVG(column2) >xxx

说明： 1.GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM等整合性查询的关键字一起使用。2.HAVING : 必须和 GROUP BY 一起使用作为整合性的限制。

复合性的查询：SELECT * FROM table_name1 WHERE EXISTS (SELECT * FROM table_name2 WHERE conditions )

说明：1.WHERE 的 conditions 可以是另外一个的 query。2. 在此是指存在与否。

SELECT column1,columns2,...FROM table_name

说明：把table_name 的特定栏位资料全部列出来

SELECT COUNT (*) FROM table_name WHERE column_name = xxx

说明：查询符合条件的资料共有几笔。

1.计算出总和，所选的栏位必须是可数的数字形态。2.除此以外还有 AVG() 是计算

SELECT SUM(column1) FROM table_name

说明：平均、MAX()、MIN()计算最大最小值的整合

SELECT * FROM table_name1 WHERE column1 BETWEEN xx AND yy

说明：BETWEEN 表示 column1 的值介於 xx 和 yy 之间。

3. update SQL语句

UPDATE table_name SET column1='xxx' WHERE conditoins

说明：1.更改某个栏位设定其值为'xxx'。

2.conditions 是所要符合的条件、若没有 WHERE 则整个 table 的那个栏位都会全部被更改。

4. deletesql语句

删除资料： DELETE FROM table_name WHERE conditions

说明：删除符合条件的资料。

说明：关于WHERE条件后面如果包含有日期的比较，不同数据库有不同的表达式

5．SQL中通配符用

• "%" 表示零个或多个，

• "_"表示任何单一字符

• [charlist] 字符列中的任何单一字符

• [^charlist] [!charlist] 不在字符列中的任何单一字符

• select * from databasename where fieldname like '%XX%'

• SELECT * FROM Persons WHERE City LIKE '[AOF]%'

• 从上面的 "Persons" 表中选取居住的城市以 "A" 或 "O" 或 "F" 开头的人

• SELECT * FROM Persons WHERE City LIKE '[!AOF]%'

• 从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "O" 或 "F" 开头的人

sub test()\x0d\x0a \x0d\x0aDim Conn As New ADODB.Connection\x0d\x0aDim strConn As String\x0d\x0aDim strSQL As String\x0d\x0aDim rs As New ADODB.Recordset\x0d\x0a \x0d\x0astrConn = "Provider=sqloledbServer=192.168.1.111Database=db2014Uid=用户名Pwd=密码"\x0d\x0aConn.Open strConn\x0d\x0a \x0d\x0aMax_row = Range("A1").CurrentRegion.Rows.Count\x0d\x0a \x0d\x0afor i=1 to Max_row\x0d\x0astrSQL = "select name from sales2014 where id='" &range("A" &i).value &"'"\x0d\x0aConn.Execute strSQL\x0d\x0ars.Open strSQL, Conn, 1, 1\x0d\x0arange("B" &i).value= rs(0) \x0d\x0ars.Close\x0d\x0aSet rs = Nothing\x0d\x0anext i\x0d\x0a \x0d\x0a'关闭数据库\x0d\x0aConn.Close\x0d\x0aend sub

1、基本上所有的数据库都有对应的写法（有些数据库是要装连接驱动才能连），比如"Provider=Microsoft.Jet.OLEDB.4.0（用来连接access）或者provider =MSDASQL，而且每种数据库也可能会有多种连接方法（Provider意即数据库引擎），常见的有下面这些

Provider 代码 Provider

ADSDSOObject Active Directory Services

Microsoft.Jet.OLEDB.4.0 Microsoft Jet databases

MSDAIPP.DSO.1 Microsoft Internet Publishing

MSDAORA Oracle databases

MSDAOSP Simple text files

MSDASQL Microsoft OLE DB provider for ODBC

MSDataShape Microsoft Data Shape

MSPersist Locally saved files

SQLOLEDB Microsoft SQL Server

2、参见http://www.w3school.com.cn/ado/met_conn_open.asp#connectoptionenum

3、这样就可以对book1.xlsx中的数据使用sql语法进行 *** 作

4、当你需要返回结果的，比如说你要查询数据库符合某个条件的有几条记录，或者说取出数据库中符合条件的一批记录的时候就要用数据集，单一的conn.execute只能执行 *** 作，不能返回数据，必须配合结果集使用才可取回数据。

---