SQL在Excel中的应用方法

1、SQL在Excel中的应用方法iamlaosng 文Excel中使用SQL的主要目的是连接数据库（或Excel工作表）导入数据或者对这些数据 进行统计汇总，要达到这个目的，需要好好学习SQL语句的使用。本文主要说明在 Excel中如何使用SQL至于SQL语句本身就不多作介绍了。一、简单的查询1、建立查询数据选项卡一现有连接一浏览更多 或者按快捷键Alt+D+D+D选择要查询的Excel文件和文件中的的工作表，就可以将相应工作表的数据取过来。表现 形式可以是表，也可以是数据透视表等。2、SQL查询语句如果是挑选部分列数据，就需要用 SQL语句（取所有数据也可以用 SQL语句）。?建立查询时，选择

2、工作表后不要点击“确定”按钮，而是先点击“属性”按钮， 弹出窗口中选择“定义”选项卡，在命令文本框中输入SQL查询语句（原来的工作表名称，表示所有数据，可以认为是取所有数据的SQL的一种特殊写法）：Select字段列表from 工作表名$-其中字段列表就是需要选择的字段，数据源用工作表名称加“ $ “再用中括号括起来，例如：select prov_ name, city_ name, xs_mc, xs_code from Sheet1$select * from Sheet1$?-取所有数据偶然发现，字段名不能用no,估计是保留字，如需要，用中括号括起来，例如: select no ,pro

3、v_ name,city_ name, xs_mc, xs_code from Sheet1$字段名中含有特殊字符的也要用中括号括起来，如/ ?空格等Excel查询没有伪表概念，对于表达式的计算直接用select既可，例如Select 23+45?- 返回 68Select date()?- 返回当前日期3、修改查询语句?方法：点击右键一弹出菜单一表格一编辑查询通过修改SQL语句可以变更所取的数据，也可以将建立查询时的简单SQL语句改成复杂的SQL语句。字段名更换：如果想换个字段名，用“ as新字段名”既可，例如：select prov_ name as 省,city_ name as城市,x

4、s_mc as 县市,xs_code as 编码 fromSheet1$非正常表格：数据区域(含字段名)不在第一行需要在工作表名称后面指定数据范围，例如：select prov_ name, city_ name, xs_mc, xs_code from Sheet1$B2:G2000或者，将数据块定义为一个名称，假设定义为mydata，SQL语句如下：select prov_ name, city_ name, xs_mc, xs_code from mydata注意：使用名称时没有$符号，也没有方括号了。数据更新：数据源发生变化，需要更新数据，方法：点击右键一弹出菜单一刷新意外：如果打开E

5、xcel文件后弹出不是选择工作表的窗口而是一个“数据连接属性” 窗口，可以关闭这个窗口，然后将 Excel应用极小化再极大化方式消除，或者在弹 出选择文件的窗口时，退回上一级文件夹，删除那个 Queries文件夹，就行了。4、外部数据属性修改SQL语句后，如显示格式不是预想的那样，需要去掉“外部数据属性”中“保留列属 性”前面的勾选。方法：点击右键一弹出菜单一表格一外部数据属性，弹出窗口如下：二、复杂的查询1、多表联合相同结构的多个表合并到一起，用 union连接SQL语句，例如：Select * from ?财务部 $ un io n all Select * from 市场部 $Union

6、是去重复的，即相同的记录保留一个（类似 dist inct ） ， Un ion all则是直接相加 两个结果，不去重复。增加一个部门字段可以将查询结果中的区分开来，以便知道数据来自哪个表。Union的三个一致，即：字段的数量、类型和顺序。例如：Select “财务部” as 部门,* from ?财务部$ union all Select “市场部” as 部门,* from 市场部$多表联合查询Select * from ? 部门$ ?bm,?员工$ ?yg where bm.部门编码=yg.部门编码跨工作簿查询如果数据不仅来自不同的工作表，还来自不同的文件，一样可以用union联合，例如

7、：Select“分公司1” as公司，“财务部” as部门,* from F:SQL 之Excel应用分公司 1.xlsx.财务部 $ un io n allSelect “分公司1” as公司， 公司 1.xlsx. 市场部 $ union all“市场部”as部门,* from F:SQL之Excel应用分Select“分公司2” as公司，“财务部”as部门,* from F:SQL之 Excel应用分公司 2.xlsx. 财务部 $ union allSelect“分公司2” as公司，“市场部”as部门,* from F:SQL之 Excel应用分公司2.xlsx. 市场部$因为SQ

8、L中已经指定了文件名和表名，所以建立连接时连接谁并不重要，这种情况下，建 立连接的时候就连接自己，然后再改写SQL语句。2、子查询和多表连接所谓子查询就是将一个查询结果作为数据源放在主查询语句中，多表连接则是将两个有关 联的表通过关键字段连接在一起查询，这都是SQL知识，不再赘述，需要注意的是，不同的数据库系统SQL都有些微小的差别，Excel中的SQL也有其自己的一些特点，关于多表 查询的写法，见本文附录。3、常用运算符有条件的查询条件是where引导的，用and、or等连接，例如：select pr ov_n ame, city_ name, xs_mc, xs_codefrom Shee

9、t1$ where prov_ name=安徽 or prov_ name=江苏?-虽然字符串可以用双引号，但建议用单引号，因为oracle 、SQLserver都是用单引号。常用运算符：in、not in、between and 、is null 、is not null 、& （连字 符） like、not like ，注意：null和任何字段运算的结果都是 null。通配符：（所有字符或无字符）、（单个字符）、（区间，如1-9、!a-f1,3,5），例如：select * fromSheet1$ where Email likeh - m% ? -h-m 开头的电子邮件select *

10、fromSheet1$ where xs_code like %!1,3,5-和 not like %1,3,5效果相同select * fromSheet1$ where 户籍& - &工作地 like % 合肥%?-中间加个“-”防止误差筛选查询结果：Distinct去重复、top n取前n条记录聚合函数：count、sum min、max avg?排序：order by、分组：group by、分组后筛选： havingSQL中关键字的执行顺序：from=1 where=2 group by=3 having=4 order by=5select=6 ，因为 select 在最后，所以其

11、它关键字后面不能用字段别名，不过，表的别名是可以用的，因为from排在第一。4、常用函数除了聚合函数，还有很多其他函数，这些函数有的是所有数据库系统都有的，有的是数据 库系统特有的。Excel中工作表中使用的函数基本都能在 SQL中使用，例如：数学：abs、int、fix、round、mod rnd、文本：left、right、mid、len、in str、string、replace、format、条件：iif 、switch、choose、日期：date/now、year/month/day、weekday、dateserial 、有些函数用法和工作表中略有不同，如date可以取当前日期，

12、但是不能合成日期，合成日期用dateserial（这个函数只能在SQL中使用）5、交叉查询交叉查询产生一个透视表，相当于一个矩形二维表，这是Excel特有的查询，格式如下：Transform 聚合函数select 行标签from 数据表$ groupby行标签pivot 列标签，例 如：Transform sum（工资）select部门名称from 员工$ group by部门名称pivot 职务这个语句产生的结果与数据透视表差不多，相当于一个语句产生一个数据透视表，当然这 个透视表是固定的，和语句对应的。其中的 select语句，相当于数据透视表的行字段，其 中的聚合函数的参数相当于拖到数据

13、透视表数据区域的值字段，使用的聚合函数即值字段 的汇总方式。其中的pivot字段相当于数据透视表的列字段，后面的IN （value1, value2,），相当列字段中的项的排序和筛选，摆弄过数据透视表，将transform/pivot语句与数据透视表对照，可以轻松掌握这个MS JET新增SQL语句。看一下效果： 列标签筛选Transform sum（工资）select 部门名称from 员工$ group by部门名称pivot 职务in （主管，经理）多个行标签Transform sum（工资）select职务，性别from 员工$ group by 职务，性别pivot 部门名称 如需要添

14、加总计，则需要先构造一个子查询结果，这个结果由正常的查询和统计查询联合 在一起，再以这个结果作为数据源，构成上面的二维表。例如：Transform sum（工资）select咅部门名称 from （Select部门名称,职务,工资from 员工$ ?union allSelect部门名称,总计,sum（工资）from 员工$ group by 部门名称?） group by 部门名称pivot职务in?（ 主管，经理,职员，总计）6文本型数字SQL查询时字段类型是由前8行数据决定的（这个数字是 Excel定的），如果前8行都是 数值型，后面有文本型数字，则查询结果中这些数字变成为空；前8行是文

15、本型，后面是数值型则不影响，似乎查询结果偏向文本。如果前8行中类型不一致，有数值型，也有文本型数字，可以通过在连接字符串中加入 IMEX=1则后面有文本型字符也没关系，但是，如 果前8行都是数值型，加了这个也不管用，因为前 8行已经决定是数值型了。加IMEX位置 如下：Provider=Microsoft.ACE.OLEDB.12.0;UserlD=Admi n;Data Source=C:Docume nts and桌面 tb_city_zd.xls;Mode=ShareDe ny Write;Exte nded Properties=HDR=YES; IMEX=T;Jet OLEDB:Sy

16、stem database=;JetOLEDB:Registry Path=;Jet OLEDB:E ngi ne Type=35;Jet OLEDB:DatabaseLocki ng Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global BulkTransactions=1;JetOLEDB:NewDatabase Password=;Jet OLEDB:CreateSystem Database=False;Jet OLEDB:E ncrypt Database=False;Jet OLEDB:D ont CopyLocal

17、e on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;JetOLEDB:SFP=False;Jet OLEDB:Support Complex Data=False7、删除无用的数据源随着我们建立的查询越来越多，打开现有连接时会出现很多我们原来建立的连接，这些连 接是Windows自动保存以便于我们再次使用的，如要删除，可进入“我的文档”下面的“我 的数据源”文件夹，删除这些无用的数据源或者直接删除“我的数据源”文件夹。删除这些连接不会影响原来建立的那些查询。8、Microsoft Query 工具可以利用MC工具建

18、立查询，对于不熟悉 SQL语言的可以用这个调试 SQL语句。MQ向导会 提供可视化工具，一步一步引导我们得到所需的数据。查询生成后，可以点击“SQL按钮进一步修改SQL语句。打开方法：数据选项卡一自其它来源一来自MicrosoftQuery 工具一Excel files ,选择文件后确定，进入工具。如果不能选择xlsx文件，是因为数据源版本驱动太低，进入控制面板-管理工具一数据源（ODBC，点击配置，数据库版本选择 Excel 12.0版本（office2007以上）；如果找不 到12.012.0以上版本，就删除原来的数据源 Excel files ，重新添加一个，注意要选择带 有xlsx的驱

19、动程序。office 版本和版本号：office97 : 8.0、office2000 : 9.0、officeXP（2002） : 10.0、office2003 : 11.0、office2007: 12.0、office2010 : 14.0、office2013: 15.0选择文件并确定后，如果提示“数据源中没有包含可见的表格”，点击确定，在随后弹出 的向导窗口中点击“选项”按钮，勾选“系统表”，确定后就可以看到表了，如下图：MC工具通过可视化工具生成所需的SQL查询语句，如添加条件、分组等等。点击“ SQI”按钮查看生成的语句，可以看到文件名和表名都是用单引号括起来，和中括号效果 一

20、样。MQX具不仅可以编写SQL查询语句，也可以写insert、delete、update等SQL语 句，例如：Insert into 员工$（姓名,性别,工资）values （宋定才,男,5000）三、VBA中使用SQL语句1、连接数据库的工具ADOADO是个类，有三个工具：conn ection （连接）、comma n（命令）和recordset （记 录集）使用前先引用，进入VBE点击菜单“工具”下面的“引用”，勾选最高版本的ADO 然后就可以用new在VBA过程中创建对象了。弓I用窗口如下图：2、连接Access数据库连接字符串：连接数据库的关键是连接串的写法，可以参考建立查询时系统自

21、动生 成的连接串，方法是：数据选项卡一自Access，在弹出窗口选择数据文件和表后，点击属性，弹出窗口中点击定义选项卡，其中的连接字符串就是连接access的字符 串，内容如下：Provider=Microsoft.ACE.OLEDB.12.0;Password=;UserlD=Admi n;DataSource=D:Lcli nk.mdb;Mode=Share Deny Write;Exte ndedProperties=;JetOLEDB:System database=;JetOLEDB:Registry Path=;Jet OLEDB:DatabasePassword=;JetOLED

22、B:E ngi ne Type=5;Jet OLEDB:Database Locki ng Mode=0;JetOLEDB:Global PartialBulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;JetOLEDB:NewDatabasePassword=;Jet OLEDB:Create System Database=False;JetOLEDB:E ncryptDatabase=False;Jet OLEDB:D ont Copy Locale on Compact=False;Jet OLEDB:CompactWithout Repli

23、ca Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support ComplexData=False根据上面的连接串可以写出下面的VBA弋码。连接串中大部分是默认值，VBA代码中可以不写，例如，下面的代码是连接access数据库：vb ?view plain ?copy1. ?更新工作表数据，无返回数据？2. Subado_test1()?3. ?Dim?cnn?As?ADODB.Connection?4. ?新建一个连接对象？5. ?Set?cnn?=?NeW?ADODB.Connection?6. ?建立连接？7. ?With ?cnn?8. ?.

24、Provider?=Microsoft.ACE.OLEDB.12.0?9. ?当前文件的路径可以用 ThisWorkbook.Path ?10. 员工.accdb?11. ?End?With ?12. ?使用SQL语句操作数据库？13. ?Dim?sql? As?String ?14. ?sql?=?update?职工？set?年龄=20?where?生名=张丽?15. ?cnn.Execute?sql? ?执行 SQL命令，无需返回值？16. ?cnn.Close?关闭连接?17. ?Set?cnn?=?Nothing ?释放对象？18. ?MsgBox?操作成功！ ?19. End?Sub

25、?查询表，有返回记录，注意下面例子中定义和连接的不同写法：vb ?view plain ?copy1. ?查询数据库表数据？2. Subado_test2()?3. ?Dim?cnn?As?NeW?ADODB.Connection?4. ?建立连接，当前文件的路径可以用 ThisWorkbook.Path ?5. ?cnn.Open?Provider?=Microsoft.ACE.OLEDB.12.0;?Data?Source?=?&?This员工.accdb?6. ?使用SQL语句操作数据库？7. ?Dim?sqls? As?String ?8. ?Dim?rst? As?Nev?ADODB

26、.Recordset?9. ?sqls?=?select?*?from?职工?10. ?Set?rst?=?cnn.Execute(sqls)?执行 SQL命令？11. ?用循环获取字段名？12. ?Dim?i? As?Integer ?13. ?For?i?=?0?To?rst.fields.Cou nt-1?14. ?Cells(1,?i?+?1)?=?rst.Fields(i).name?15. ?Next?i?16. ?保存查询记录？17. ?Range(a2).CopyFromRecordset?rst?18. ?rst.Close? ?关闭记录集?19. ?Set?rst?=? N

27、othing ?释放对象？20. ?cnn.Close?关闭连接?21. ?Set?cnn?=?Nothing ?释放对象？22. ?MsgBox?操作成功！ ?23. End?Sub?将工作表中的数据保存到数据库表中方法是更新记录集，再调用记录集update方法例如：vb ?view plain ?copy1. ?将工作表数据保存到数据库？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

28、,?n? As?Integer ?7. ?建立连接，当前文件的路径可以用 ThisWorkbook.Path ?8. ?Set?cnn?=?NeW?ADODB.Connection?9. ?cnn.Open?Provider?=Microsoft.ACE.OLEDB.12.0;?Data?Source?二？&?This员工.accdb?10. ?mytable?=?职工?11. ?n?=Range(a1). End(xlDown).Row?当前工作表有效行数?12. ?使用SQL语句操作数据库？13. ?For?i?=?2?To?n?14. ?sqls?=?select?*?from? &my

29、table & ?where?编号=?&?Cells(i,?1).Value?&?15. ?Set?rst?=? NeW?ADODB.Recordset?16. ?用记录集对象执行SQL语句？17. ?rst.Open?,?cnn,?adOpenKeyset,adLockOptimistic?18. ?f ?rst.RecordCount?=?0?Thenrst.AddNew?找不到，增加一条空记录?19. ?For?j?=?1? To?rst.Fields.Count?20. ?rst.Fields(j?-?1)?=?Cells(i,j).Value?21. ?Next?j?22. ?rst

30、.Update?23. ?Next?i?24. ?rst.Close? ?关闭记录集?25. ?Set?rst?=? Nothing ?释放对象？26. ?cnn.Close?关闭连接?27. ?Set?cnn?=?Nothing ?释放对象？28. ?MsgBox?操作成功！ ?29. End?Sub?3、连接Excel工作表连接 Excel，注意连接串(增加一个 ExtendedProperties=excel12.0 )和 SQL语句的写法：vb ?view plain ?copy1. ?连接Excel工作表?2. Subado_test4()?3. ?Dim?cnn?As?ADODB.

31、Connection?4. ?Dim?rst? As?ADODB.Recordset?5. ?Dim?sqls? As?String ?6. ?建立连接，注意连接串和 SQL语句的写法？7. ?Set?cnn?=?NeW?ADODB.Connection?8. ?With ?cnn?9. ?.Provider?=Microsoft.ACE.OLEDB.12.O;Extended?Properties=excel?1 2.0?10. ?.Ope n?ThisWorkbook.Path?&tb_city_zd.xls?11. ?End?With ?12. ?使用SQL语句操作数据库？13. ?sq

32、ls?=?select?*?from?sheet1$?14. ?Set?rst?=?cnn.Execute(sqls)?15. ?Sheets(sheet6).Range(A1).CopyFromRecordsetrst?16. ?rst.Close? ?关闭记录集?17. ?Set?rst?=? Nothing ?释放对象？18. ?cnn.Close?关闭连接?19. ?Set?cnn?=?Nothing ?释放对象？20. ?MsgBox?操作成功！ ?21. End?Sub?同时连接Excel和Access数据库，主要看连接串和 SQL语句的写法：vb ?view plain ?cop

33、y1. ?连接Excel工作表和 Access数据库？2. Sub?ado_test5()?3. ?Dim?cnn?As?ADODB.Connection?4. ?Dim?rst? As?ADODB.Recordset?5. ?Dim?sqls? As?String ?6. ?建立连接，注意连接串和 SQL语句的写法？7. ?Set?cnn?=?Nev?ADODB.Connection?8. ?With ?cnn?9. ?.Provider?=Microsoft.ACE.OLEDB.12.0;Extended?Properties=excel?1 2.0?10. ?.Ope n? ThisWo

34、rkbook.FullName?11. ?End?With ?12. ?使用SQL语句操作数据库？13. ?sqls?=?select?a. 部门,count(*)?from? 部门 $A:A?a?left?joi n?database?=?&?_?14. 员工.accdb.职工?b?on?a.部门=b.部门?group?bya.部门？15. ?Set?rst?=?cnn.Execute(sqls)?16. ?Sheets(部门).Range(b2).CopyFromRecordset?rst?17. ?rst.Close? ?关闭记录集?18. ?Set?rst?=? Nothing ?释放

35、对象？19. ?cnn.Close?关闭连接？20. ?Set?cnn?=?Nothing ?释放对象?21. ?MsgBox?操作成功！ ?22. End?Sub?4、注意事项关于ADO空件，有两种创建方式，一种是如前述的那样，先加引用，然后在代码中 就可以定义这种类型的对象，再通过 New/的方式建立对象。另一种方式直接创建,代码如下:Dim cnnAs Object, rst As ObjectSet cn n =CreateObject(ADODB.Co nn ectio n)Set rst =CreateObject(ADODB.Recordset)其实这种方法更实用，因为加引用必须

36、是熟悉系统的人才能操作，如果将写好的程序给一 般人使用，难道每次你还指导他去加引用？执行SQL语句有三种方式，一种是用 conn ection，即上面的cnn .Execute，这种方 式比较适合无返回记录的语句，即 DML语句。如果执行有返回记录的 SQL语句，也 可以取到记录，只是 RecordCount总是反馈-1。这种情况下可以根据rst.eof判断 有无查询结果，如果rst.eof= true就表示查询结果为空。另一种方式是用RecordSet 即上面的rst.Open，这个适合有返回记录的语句，即 select语句，因为这种方式 能够返回记录数RecordCou nt。当然还有第三

37、种方式，就是用comma nd这个比较适 合执行存储过程，因为这种方式可以传递参数。三种方式comma n(方式功能最强，用起来也最麻烦，conn ection最弱，用起来也最简单。取值除了前面说的CopyFromRecordset,还可以用循环的方式逐个取值，例如：vb ?view plain ?copy1. For?i=1?to?rst.?RecordCount?2. ?For?j=?1? To?rst.fields.Count?3. ?Cells(i+1,?j)?=rst.Fields(j-1).Value?4. ?Next?j?5. ?rst.MoveNext?6. Next?i?AD

38、O也可也连接其他数据库，只是连接串不同，其它操作一样，例如Oracle，连接语句如下:? cnn. Ope n Provider=msdaora;DataSource=dl580;UserId=user name;Password=userpasswd;其中dl580是Oracle客户端配置的连接名称，后面是 Oracle用户名和密码。附录：SQL多表查询语句的写法1、嵌套查询嵌套查询是将一个 SELECTS句包含在另一个 SELECTS句的 WHER子句中，也称为子查询。 子查询（内层查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果 必须有确定的值。利用嵌套查询可以将几个简单查询组成一个复杂查询，从而增强SQL的查询能力。1、查询“张三”选修的课程和成绩学号from 学生$ where学号from 学生$ where学号from 学生$）select 学号,课程,成绩from 课程$ where 学号=（select 姓名=张三）2、查询“张三”选修的语文课和成绩 select 学号,课程,成绩from 课程$ where 学号=（select 姓名=张三and课程=语文）3、查询所有考试学生的成绩 select * FROM 课程 $ where 成绩 not in (select distinet2、合并查询合并查询想必大家都知道了，数据透视表多表查询，