SQL在Excel中的应用方法.docx

SQL在Excel中的应用方法.docx

《SQL在Excel中的应用方法.docx》由会员分享，可在线阅读，更多相关《SQL在Excel中的应用方法.docx（20页珍藏版）》请在冰点文库上搜索。

SQL在Excel中的应用方法

SQL在Excel中的应用方法

iamlaosng文

Excel中使用SQL的主要目的是连接数据库（或Excel工作表）导入数据或者对这些数据进行统计汇总，要达到这个目的，需要好好学习SQL语句的使用。

本文主要说明在Excel中如何使用SQL，至于SQL语句本身就不多作介绍了。

一、简单的查询

1、建立查询

数据选项卡—现有连接—浏览更多或者按快捷键Alt+D+D+D

选择要查询的Excel文件和文件中的的工作表，就可以将相应工作表的数据取过来。

表现形式可以是表，也可以是数据透视表等。

2、SQL查询语句

如果是挑选部分列数据，就需要用SQL语句（取所有数据也可以用SQL语句）。

∙ 建立查询时，选择工作表后不要点击“确定”按钮，而是先点击“属性”按钮，弹出窗口中选择“定义”选项卡，在命令文本框中输入SQL查询语句（原来的工作表名称，表示所有数据，可以认为是取所有数据的SQL的一种特殊写法）：

Select字段列表from[工作表名$]

--其中字段列表就是需要选择的字段，数据源用工作表名称加“$“再用中括号括起来，例如：

selectprov_name,city_name,xs_mc,xs_codefrom[Sheet1$]

select*from[Sheet1$]   --取所有数据

∙偶然发现，字段名不能用no，估计是保留字，如需要，用中括号括起来，例如：

select[no],prov_name,city_name,xs_mc,xs_codefrom[Sheet1$]

字段名中含有特殊字符的也要用中括号括起来，如/?

空格等

Excel查询没有伪表概念，对于表达式的计算直接用select既可，例如

Select23+45   --返回68

Selectdate（）   --返回当前日期

3、修改查询语句

∙ 方法：

点击右键—弹出菜单—表格—编辑查询

通过修改SQL语句可以变更所取的数据，也可以将建立查询时的简单SQL语句改成复杂的SQL语句。

∙字段名更换：

如果想换个字段名，用“as新字段名”既可，例如：

selectprov_nameas省,city_nameas城市,xs_mcas县市,xs_codeas编码from[Sheet1$]

∙非正常表格：

数据区域（含字段名）不在第一行

需要在工作表名称后面指定数据范围，例如：

selectprov_name,city_name,xs_mc,xs_codefrom[Sheet1$B2:

G2000]

或者，将数据块定义为一个名称，假设定义为mydata，SQL语句如下：

selectprov_name,city_name,xs_mc,xs_codefrommydata

注意：

使用名称时没有$符号，也没有方括号了。

∙数据更新：

数据源发生变化，需要更新数据，方法：

点击右键—弹出菜单—刷新

∙意外：

如果打开Excel文件后弹出不是选择工作表的窗口而是一个“数据连接属性”窗口，可以关闭这个窗口，然后将Excel应用极小化再极大化方式消除，或者在弹出选择文件的窗口时，退回上一级文件夹，删除那个Queries文件夹，就行了。

4、外部数据属性

修改SQL语句后，如显示格式不是预想的那样，需要去掉“外部数据属性”中“保留列属性”前面的勾选。

方法：

点击右键—弹出菜单—表格—外部数据属性，弹出窗口如下：

二、复杂的查询

1、多表联合

∙相同结构的多个表合并到一起，用union连接SQL语句，例如：

Select*from [财务部$]unionallSelect*from[市场部$]

Union是去重复的，即相同的记录保留一个（类似distinct），Unionall则是直接相加两个结果，不去重复。

∙增加一个部门字段可以将查询结果中的区分开来，以便知道数据来自哪个表。

Union的三个一致，即：

字段的数量、类型和顺序。

例如：

Select“财务部”as部门,*from [财务部$]unionallSelect“市场部”as部门,*from[市场部$]

∙多表联合查询

Select*from [部门$] bm,  [员工$] ygwherebm.部门编码=yg.部门编码

∙跨工作簿查询

如果数据不仅来自不同的工作表，还来自不同的文件，一样可以用union联合，例如：

Select“分公司1”as公司,“财务部”as部门,*from[F:

\SQL之Excel应用\分公司1.xlsx].[财务部$]unionall

Select“分公司1”as公司,“市场部”as部门,*from[F:

\SQL之Excel应用\分公司1.xlsx].[市场部$]unionall

Select“分公司2”as公司,“财务部”as部门,*from[F:

\SQL之Excel应用\分公司2.xlsx].[财务部$]unionall

Select“分公司2”as公司,“市场部”as部门,*from[F:

\SQL之Excel应用\分公司2.xlsx].[市场部$]

因为SQL中已经指定了文件名和表名，所以建立连接时连接谁并不重要，这种情况下，建立连接的时候就连接自己，然后再改写SQL语句。

2、子查询和多表连接

所谓子查询就是将一个查询结果作为数据源放在主查询语句中，多表连接则是将两个有关联的表通过关键字段连接在一起查询，这都是SQL知识，不再赘述，需要注意的是，不同的数据库系统SQL都有些微小的差别，Excel中的SQL也有其自己的一些特点，关于多表查询的写法，见本文附录。

3、常用运算符

∙有条件的查询

条件是where引导的，用and、or等连接，例如：

selectprov_name,city_name,xs_mc,xs_codefrom[Sheet1$]whereprov_name=’安徽’orprov_name=’江苏’

 --虽然字符串可以用双引号，但建议用单引号，因为oracle、SQLserver都是用单引号。

∙常用运算符：

in、notin、between…and…、isnull、isnotnull、&（连字符）、like、notlike，注意：

null和任何字段运算的结果都是null。

∙通配符：

%（所有字符或无字符）、_（单个字符）、[]（区间，如[1-9]、[!

a-f]、[1,3,5]），例如：

select*from[Sheet1$]whereEmaillike‘[h-m]%’  --h-m开头的电子邮件

select*from[Sheet1$]wherexs_codelike'%[!

1,3,5]'–和notlike'%[1,3,5]'效果相同

select*from[Sheet1$]where户籍&’-’&工作地like'%合肥%' --中间加个“-”防止误差

∙筛选查询结果：

Distinct去重复、topn取前n条记录

∙聚合函数：

count、sum、min、max、avg 排序：

orderby、分组：

groupby、分组后筛选：

having

∙SQL中关键字的执行顺序：

from=1where=2groupby=3having=4orderby=5select=6，因为select在最后，所以其它关键字后面不能用字段别名，不过，表的别名是可以用的，因为from排在第一。

4、常用函数

除了聚合函数，还有很多其他函数，这些函数有的是所有数据库系统都有的，有的是数据库系统特有的。

Excel中工作表中使用的函数基本都能在SQL中使用，例如：

∙数学：

abs、int、fix、round、mod、rnd、……

∙文本：

left、right、mid、len、instr、string、replace、format、……

∙条件：

iif、switch、choose、……

∙日期：

date/now、year/month/day、weekday、dateserial、……

有些函数用法和工作表中略有不同，如date可以取当前日期，但是不能合成日期，合成日期用dateserial（这个函数只能在SQL中使用）

5、交叉查询

交叉查询产生一个透视表，相当于一个矩形二维表，这是Excel特有的查询，格式如下：

Transform聚合函数select行标签from[数据表$]groupby行标签pivot列标签，例如：

Transformsum（工资）select部门名称from[员工$]groupby部门名称pivot职务

这个语句产生的结果与数据透视表差不多，相当于一个语句产生一个数据透视表，当然这个透视表是固定的，和语句对应的。

其中的select语句，相当于数据透视表的行字段，其中的聚合函数的参数相当于拖到数据透视表数据区域的值字段，使用的聚合函数即值字段的汇总方式。

其中的pivot字段相当于数据透视表的列字段，后面的IN（value1[,value2[,...]]）]，相当列字段中的项的排序和筛选，摆弄过数据透视表，将transform/pivot语句与数据透视表对照，可以轻松掌握这个MSJET新增SQL语句。

看一下效果：

列标签筛选

Transformsum（工资）select部门名称from[员工$]groupby部门名称pivot职务in（‘主管’,‘经理’）

多个行标签

Transformsum（工资）select职务,性别from[员工$]groupby职务,性别pivot部门名称

如需要添加总计，则需要先构造一个子查询结果，这个结果由正常的查询和统计查询联合在一起，再以这个结果作为数据源，构成上面的二维表。

例如：

Transformsum（工资）select部门名称from（

Select部门名称,职务,工资from[员工$] unionall

Select部门名称,’总计’,sum（工资）from[员工$]groupby部门名称

 ）groupby部门名称pivot职务in （‘主管’,‘经理,’职员’,’总计’）

6、文本型数字

SQL查询时字段类型是由前8行数据决定的（这个数字是Excel定的），如果前8行都是数值型，后面有文本型数字，则查询结果中这些数字变成为空；前8行是文本型，后面是数值型则不影响，似乎查询结果偏向文本。

如果前8行中类型不一致，有数值型，也有文本型数字，可以通过在连接字符串中加入IMEX=1则后面有文本型字符也没关系，但是，如果前8行都是数值型，加了这个也不管用，因为前8行已经决定是数值型了。

加IMEX位置如下：

Provider=Microsoft.ACE.OLEDB.12.0;UserID=Admin;DataSource=C:

\DocumentsandSettings\Administrator\桌面\tb_city_zd.xls;Mode=ShareDenyWrite;ExtendedProperties="HDR=YES;IMEX=1";JetOLEDB:

Systemdatabase="";JetOLEDB:

RegistryPath="";JetOLEDB:

EngineType=35;JetOLEDB:

DatabaseLockingMode=0;JetOLEDB:

GlobalPartialBulkOps=2;JetOLEDB:

GlobalBulkTransactions=1;JetOLEDB:

NewDatabasePassword="";JetOLEDB:

CreateSystemDatabase=False;JetOLEDB:

EncryptDatabase=False;JetOLEDB:

Don'tCopyLocaleonCompact=False;JetOLEDB:

CompactWithoutReplicaRepair=False;JetOLEDB:

SFP=False;JetOLEDB:

SupportComplexData=False

7、删除无用的数据源

随着我们建立的查询越来越多，打开现有连接时会出现很多我们原来建立的连接，这些连接是Windows自动保存以便于我们再次使用的，如要删除，可进入“我的文档”下面的“我的数据源”文件夹，删除这些无用的数据源或者直接删除“我的数据源”文件夹。

删除这些连接不会影响原来建立的那些查询。

8、MicrosoftQuery工具

可以利用MQ工具建立查询，对于不熟悉SQL语言的可以用这个调试SQL语句。

MQ向导会提供可视化工具，一步一步引导我们得到所需的数据。

查询生成后，可以点击“SQL”按钮进一步修改SQL语句。

∙打开方法：

数据选项卡—自其它来源—来自MicrosoftQuery工具—Excelfiles，选择文件后确定，进入工具。

如果不能选择xlsx文件，是因为数据源版本驱动太低，进入控制面板--管理工具—数据源（ODBC），点击配置，数据库版本选择Excel12.0版本（office2007以上）；如果找不到12.012.0以上版本，就删除原来的数据源Excelfiles，重新添加一个，注意要选择带有xlsx的驱动程序。

office版本和版本号：

office97:

8.0、office2000:

9.0、officeXP（2002）:

10.0、office2003:

11.0、office2007:

12.0、office2010:

14.0、office2013:

15.0

选择文件并确定后，如果提示“数据源中没有包含可见的表格”，点击确定，在随后弹出的向导窗口中点击“选项”按钮，勾选“系统表”，确定后就可以看到表了，如下图：

∙MQ工具通过可视化工具生成所需的SQL查询语句，如添加条件、分组等等。

点击“SQL”按钮查看生成的语句，可以看到文件名和表名都是用单引号括起来，和中括号效果一样。

∙MQ工具不仅可以编写SQL查询语句，也可以写insert、delete、update等SQL语句，例如：

Insertinto[员工$]（姓名,性别,工资）values（‘宋定才’,’男’,5000）

三、VBA中使用SQL语句

1、连接数据库的工具ADO

∙ADO是个类，有三个工具：

connection（连接）、command（命令）和recordset（记录集）

∙使用前先引用，进入VBE，点击菜单“工具”下面的“引用”，勾选最高版本的ADO，然后就可以用new在VBA过程中创建对象了。

引用窗口如下图：

2、连接Access数据库

∙连接字符串：

连接数据库的关键是连接串的写法，可以参考建立查询时系统自动生成的连接串，方法是：

数据选项卡—自Access，在弹出窗口选择数据文件和表后，点击属性，弹出窗口中点击定义选项卡，其中的连接字符串就是连接access的字符串，内容如下：

Provider=Microsoft.ACE.OLEDB.12.0;Password="";UserID=Admin;DataSource=D:

\Lc\link.mdb;Mode=ShareDenyWrite;ExtendedProperties="";JetOLEDB:

Systemdatabase="";JetOLEDB:

RegistryPath="";JetOLEDB:

DatabasePassword="";JetOLEDB:

EngineType=5;JetOLEDB:

DatabaseLockingMode=0;JetOLEDB:

GlobalPartialBulkOps=2;JetOLEDB:

GlobalBulkTransactions=1;JetOLEDB:

NewDatabasePassword="";JetOLEDB:

CreateSystemDatabase=False;JetOLEDB:

EncryptDatabase=False;JetOLEDB:

Don'tCopyLocaleonCompact=False;JetOLEDB:

CompactWithoutReplicaRepair=False;JetOLEDB:

SFP=False;JetOLEDB:

SupportComplexData=False

∙根据上面的连接串可以写出下面的VBA代码。

连接串中大部分是默认值，VBA代码中可以不写，例如，下面的代码是连接access数据库：

[vb] viewplain copy

1.' 更新工作表数据，无返回数据  

2.Subado_test1（）  

3.    Dim cnn As ADODB.Connection  

4.     

5.    ' 新建一个连接对象  

6.    Set cnn = New ADODB.Connection  

7.    '建立连接  

8.    With cnn  

9.        .Provider ="Microsoft.ACE.OLEDB.12.0"  

10.        '当前文件的路径可以用ThisWorkbook.Path  

11.        .Open ThisWorkbook.Path & "\员工.accdb"  

12.    End With  

13.    '使用SQL语句操作数据库  

14.    Dim sql As String  

15.    sql = "update 职工 set 年龄=20 where 姓名='张丽'"  

16.    cnn.Execute sql   ' 执行SQL命令，无需返回值  

17.    cnn.Close         ' 关闭连接  

18.    Set cnn = Nothing ' 释放对象  

19.     

20.    MsgBox "操作成功！

"  

21.End Sub  

∙查询表，有返回记录，注意下面例子中定义和连接的不同写法：

[vb] viewplain copy

1.' 查询数据库表数据  

2.Subado_test2（）  

3.    Dim cnn As New ADODB.Connection  

4.     

5.    '建立连接,当前文件的路径可以用ThisWorkbook.Path  

6.    cnn.Open "Provider =Microsoft.ACE.OLEDB.12.0; Data Source = " & ThisWorkbook.Path &"\员工.accdb"  

7.    '使用SQL语句操作数据库  

8.    Dim sqls As String  

9.    Dim rst As New ADODB.Recordset  

10.    sqls = "select * from 职工"  

11.    Set rst = cnn.Execute（sqls）  ' 执行SQL命令  

12.     

13.    '用循环获取字段名  

14.    Dim i As Integer  

15.    For i = 0 To rst.fields.Count-1  

16.        Cells（1, i + 1） = rst.Fields（i）.name  

17.    Next i  

18.    '保存查询记录  

19.    Range（"a2"）.CopyFromRecordset rst  

20.     

21.    rst.Close         ' 关闭记录集  

22.    Set rst = Nothing  ' 释放对象  

23.    cnn.Close         ' 关闭连接  

24.    Set cnn = Nothing  ' 释放对象  

25.     

26.    MsgBox "操作成功！

"  

27.End Sub  

∙将工作表中的数据保存到数据库表中方法是更新记录集，再调用记录集update方法，例如：

[vb] viewplain copy

1.' 将工作表数据保存到数据库  

2.Subado_test3（）  

3.    Dim cnn As ADODB.Connection  

4.    Dim rst As ADODB.Recordset  

5.    Dim sqls, mytable As String  

6.    Dim i, j, n As Integer  

7.     

8.    '建立连接,当前文件的路径可以用ThisWorkbook.Path  

9.    Set cnn = New ADODB.Connection  

10.    cnn.Open "Provider =Microsoft.ACE.OLEDB.12.0; Data Source = " & ThisWorkbook.Path &"\员工.accdb"  

11.    mytable = "职工"  

12.    n =Range（"a1"）.End（xlDown）.Row   '当前工作表有效行数  

13.    '使用SQL语句操作数据库  

14.    For i = 2 To n  

15.        sqls = "select * from " &mytable & " where 编号='" & Cells（i, 1）.Value & "'"  

16.        Set rst = New ADODB.Recordset  

17.        '用记录集对象执行SQL语句  

18.        rst.Open , cnn, adOpenKeyset,adLockOptimistic  

19.        If rst.RecordCount = 0 Thenrst.AddNew   '找不到，增加一条空记录  

20.        For j = 1 To rst.Fields.Count  

21.            rst.Fields（j - 1） = Cells（i,j）.Value  

22.        Next j  

23.        rst.Update  

24.    Next i  

25.     

26.    rst.Close         ' 关闭记录集  

27.    Set rst = Nothing ' 释放对象  

28.    cnn.Close         ' 关闭连接  

29.    Set cnn = Nothing ' 释放对象  

30.     

31.    MsgBox "操作成功！

"  

32.End Sub  

3、连接Excel工作表

∙连接Excel，注意连接串（增加一个ExtendedProperties=excel12.0）和SQL