SQL在Excel中的应用方法.docx

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工作表名$-其中字段列表就是需要选择的字段，数据源用工作表名称加“$“再用中括号括起来，例如：selectprov_name,city_name,xs_mc,xs_codefromSheet1$select*fromSheet1$-取所有数据 偶然发现，字段名不能用no，估计是保留字，如需要，用中括号括起来，例如：selectno,prov_name,city_name,xs_mc,xs_cod

3、efromSheet1$字段名中含有特殊字符的也要用中括号括起来，如/空格等Excel查询没有伪表概念，对于表达式的计算直接用select既可，例如Select23+45-返回68Selectdate()-返回当前日期3、修改查询语句 方法：点击右键弹出菜单表格编辑查询通过修改SQL语句可以变更所取的数据，也可以将建立查询时的简单SQL语句改成复杂的SQL语句。 字段名更换：如果想换个字段名，用“as新字段名”既可，例如：selectprov_nameas省,city_nameas城市,xs_mcas县市,xs_codeas编码fromSheet1$ 非正常表格：数据区域（含字段名）不在第一行

4、需要在工作表名称后面指定数据范围，例如：selectprov_name,city_name,xs_mc,xs_codefromSheet1$B2:G2000或者，将数据块定义为一个名称，假设定义为mydata，SQL语句如下：selectprov_name,city_name,xs_mc,xs_codefrommydata注意：使用名称时没有$符号，也没有方括号了。 数据更新：数据源发生变化，需要更新数据，方法：点击右键弹出菜单刷新 意外：如果打开Excel文件后弹出不是选择工作表的窗口而是一个“数据连接属性”窗口，可以关闭这个窗口，然后将Excel应用极小化再极大化方式消除，或者在弹出选择文

5、件的窗口时，退回上一级文件夹，删除那个Queries文件夹，就行了。4、外部数据属性修改SQL语句后，如显示格式不是预想的那样，需要去掉“外部数据属性”中“保留列属性”前面的勾选。方法：点击右键弹出菜单表格外部数据属性，弹出窗口如下：二、复杂的查询1、多表联合 相同结构的多个表合并到一起，用union连接SQL语句，例如：Select*from财务部$unionallSelect*from市场部$Union是去重复的，即相同的记录保留一个（类似distinct），Unionall则是直接相加两个结果，不去重复。 增加一个部门字段可以将查询结果中的区分开来，以便知道数据来自哪个表。Union的三

6、个一致，即：字段的数量、类型和顺序。例如：Select“财务部”as部门,*from财务部$unionallSelect“市场部”as部门,*from市场部$ 多表联合查询Select*from部门$bm,员工$ygwherebm.部门编码=yg.部门编码 跨工作簿查询如果数据不仅来自不同的工作表，还来自不同的文件，一样可以用union联合，例如：Select“分公司1”as公司,“财务部”as部门,*fromF:SQL之Excel应用分公司.财务部$unionallSelect“分公司1”as公司,“市场部”as部门,*fromF:SQL之Excel应用分公司.市场部$unionallSel

7、ect“分公司2”as公司,“财务部”as部门,*fromF:SQL之Excel应用分公司.财务部$unionallSelect“分公司2”as公司,“市场部”as部门,*fromF:SQL之Excel应用分公司.市场部$因为SQL中已经指定了文件名和表名，所以建立连接时连接谁并不重要，这种情况下，建立连接的时候就连接自己，然后再改写SQL语句。2、子查询和多表连接所谓子查询就是将一个查询结果作为数据源放在主查询语句中，多表连接则是将两个有关联的表通过关键字段连接在一起查询，这都是SQL知识，不再赘述，需要注意的是，不同的数据库系统SQL都有些微小的差别，Excel中的SQL也有其自己的一些特

8、点，关于多表查询的写法，见本文附录。3、常用运算符 有条件的查询条件是where引导的，用and、or等连接，例如：selectprov_name,city_name,xs_mc,xs_codefromSheet1$whereprov_name=安徽orprov_name=江苏-虽然字符串可以用双引号，但建议用单引号，因为oracle、SQLserver都是用单引号。 常用运算符：in、notin、betweenand、isnull、isnotnull、&（连字符）、like、notlike，注意：null和任何字段运算的结果都是null。 通配符：%（所有字符或无字符）、_（单个字符）、（区

9、间，如1-9、!a-f、1,3,5），例如：select*fromSheet1$whereEmaillikeh-m%-h-m开头的电子邮件select*fromSheet1$wherexs_codelike%!1,3,5和notlike%1,3,5效果相同select*fromSheet1$where户籍&-&工作地like%合肥%-中间加个“-”防止误差 筛选查询结果：Distinct去重复、topn取前n条记录 聚合函数：count、sum、min、max、avg排序：orderby、分组：groupby、分组后筛选：having SQL中关键字的执行顺序：from=1where=2gro

10、upby=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、we

11、ekday、dateserial、有些函数用法和工作表中略有不同，如date可以取当前日期，但是不能合成日期，合成日期用dateserial（这个函数只能在SQL中使用）5、交叉查询交叉查询产生一个透视表，相当于一个矩形二维表，这是Excel特有的查询，格式如下：Transform聚合函数select行标签from数据表$groupby行标签pivot列标签，例如：Transformsum(工资)select部门名称from员工$groupby部门名称pivot职务这个语句产生的结果与数据透视表差不多，相当于一个语句产生一个数据透视表，当然这个透视表是固定的，和语句对应的。其中的select语

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

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

14、数值型则不影响，似乎查询结果偏向文本。如果前8行中类型不一致，有数值型，也有文本型数字，可以通过在连接字符串中加入IMEX=1则后面有文本型字符也没关系，但是，如果前8行都是数值型，加了这个也不管用，因为前8行已经决定是数值型了。加IMEX位置如下：桌面;Mode=ShareDenyWrite;ExtendedProperties=HDR=YES;IMEX=1;JetOLEDB:Systemdatabase=;JetOLEDB:RegistryPath=;JetOLEDB:EngineType=35;JetOLEDB:DatabaseLockingMode=0;JetOLEDB:GlobalP

15、artialBulkOps=2;JetOLEDB:GlobalBulkTransactions=1;JetOLEDB:NewDatabasePassword=;JetOLEDB:CreateSystemDatabase=False;JetOLEDB:EncryptDatabase=False;JetOLEDB:DontCopyLocaleonCompact=False;JetOLEDB:CompactWithoutReplicaRepair=False;JetOLEDB:SFP=False;JetOLEDB:SupportComplexData=False7、删除无用的数据源随着我们建立的查询

16、越来越多，打开现有连接时会出现很多我们原来建立的连接，这些连接是Windows自动保存以便于我们再次使用的，如要删除，可进入“我的文档”下面的“我的数据源”文件夹，删除这些无用的数据源或者直接删除“我的数据源”文件夹。删除这些连接不会影响原来建立的那些查询。8、MicrosoftQuery工具可以利用MQ工具建立查询，对于不熟悉SQL语言的可以用这个调试SQL语句。MQ向导会提供可视化工具，一步一步引导我们得到所需的数据。查询生成后，可以点击“SQL”按钮进一步修改SQL语句。 打开方法：数据选项卡自其它来源来自MicrosoftQuery工具Excelfiles，选择文件后确定，进入工具。如

17、果不能选择xlsx文件，是因为数据源版本驱动太低，进入控制面板-管理工具数据源（ODBC），点击配置，数据库版本选择版本（office2007以上）；如果找不到以上版本，就删除原来的数据源Excelfiles，重新添加一个，注意要选择带有xlsx的驱动程序。office版本和版本号：office97:、office2000:、officeXP(2002):、office2003:、office2007:、office2010:、office2013:选择文件并确定后，如果提示“数据源中没有包含可见的表格”，点击确定，在随后弹出的向导窗口中点击“选项”按钮，勾选“系统表”，确定后就可以看到表了，

18、如下图： MQ工具通过可视化工具生成所需的SQL查询语句，如添加条件、分组等等。点击“SQL”按钮查看生成的语句，可以看到文件名和表名都是用单引号括起来，和中括号效果一样。 MQ工具不仅可以编写SQL查询语句，也可以写insert、delete、update等SQL语句，例如：Insertinto员工$(姓名,性别,工资)values(宋定才,男,5000)三、VBA中使用SQL语句1、连接数据库的工具ADO ADO是个类，有三个工具：connection（连接）、command（命令）和recordset（记录集） 使用前先引用，进入VBE，点击菜单“工具”下面的“引用”，勾选最高版本的AD

19、O，然后就可以用new在VBA过程中创建对象了。引用窗口如下图：2、连接Access数据库 连接字符串：连接数据库的关键是连接串的写法，可以参考建立查询时系统自动生成的连接串，方法是：数据选项卡自Access，在弹出窗口选择数据文件和表后，点击属性，弹出窗口中点击定义选项卡，其中的连接字符串就是连接access的字符串，内容如下： 根据上面的连接串可以写出下面的VBA代码。连接串中大部分是默认值，VBA代码中可以不写，例如，下面的代码是连接access数据库：vb1. 更新工作表数据，无返回数据2. Subado_test1()3. DimcnnAs4. 新建一个连接对象5. Setcnn=N

20、ew6. 建立连接7. Withcnn8. .Provider=9. 当前文件的路径可以用10. .Open&员工.accdb11. EndWith12. 使用SQL语句操作数据库13. DimsqlAsString14. sql=update职工set年龄=20where姓名=张丽15. sql执行SQL命令，无需返回值16. 关闭连接17. Setcnn=Nothing释放对象18. MsgBox操作成功！19. EndSub 查询表，有返回记录，注意下面例子中定义和连接的不同写法：vb1. 查询数据库表数据2. Subado_test2()3. DimcnnAsNew4. 建立连接,当前

21、文件的路径可以用5. &员工.accdb6. 使用SQL语句操作数据库7. DimsqlsAsString8. DimrstAsNew9. sqls=select*from职工10. Setrst=(sqls)执行SQL命令11. 用循环获取字段名12. DimiAsInteger13. Fori=0To14. Cells(1,i+1)=(i).name15. Nexti16. 保存查询记录17. Range(a2).CopyFromRecordsetrst18. 关闭记录集19. Setrst=Nothing释放对象20. 关闭连接21. Setcnn=Nothing释放对象22. MsgB

22、ox操作成功！23. EndSub 将工作表中的数据保存到数据库表中方法是更新记录集，再调用记录集update方法，例如：vb1. 将工作表数据保存到数据库2. Subado_test3()3. DimcnnAs4. DimrstAs5. Dimsqls,mytableAsString6. Dimi,j,nAsInteger7. 建立连接,当前文件的路径可以用8. Setcnn=New9. &员工.accdb10. mytable=职工11. n=Range(a1).End(xlDown).Row当前工作表有效行数12. 使用SQL语句操作数据库13. Fori=2Ton14. sqls=se

23、lect*from&mytable&where编号=&Cells(i,1).Value&15. Setrst=New16. 用记录集对象执行SQL语句17. ,cnn,adOpenKeyset,adLockOptimistic18. If=0找不到，增加一条空记录19. Forj=1To20. (j-1)=Cells(i,j).Value21. Nextj22. 23. Nexti24. 关闭记录集25. Setrst=Nothing释放对象26. 关闭连接27. Setcnn=Nothing释放对象28. MsgBox操作成功！29. EndSub3、连接Excel工作表 连接Excel，注

24、意连接串（增加一个ExtendedProperties=）和SQL语句的写法：vb1. 连接Excel工作表2. Subado_test4()3. DimcnnAs4. DimrstAs5. DimsqlsAsString6. 建立连接，注意连接串和SQL语句的写法7. Setcnn=New8. Withcnn9. .Provider=10. .Open&11. EndWith12. 使用SQL语句操作数据库13. sqls=select*fromsheet1$14. Setrst=(sqls)15. Sheets(sheet6).Range(A1).CopyFromRecordsetrst1

25、6. 关闭记录集17. Setrst=Nothing释放对象18. 关闭连接19. Setcnn=Nothing释放对象20. MsgBox操作成功！21. EndSub 同时连接Excel和Access数据库，主要看连接串和SQL语句的写法：vb1. 连接Excel工作表和Access数据库2. Subado_test5()3. DimcnnAs4. DimrstAs5. DimsqlsAsString6. 建立连接，注意连接串和SQL语句的写法7. Setcnn=New8. Withcnn9. .Provider=10. .Open11. EndWith12. 使用SQL语句操作数据库13

26、. sqls=selecta.部门,count(*)from部门$A:Aaleftjoindatabase=&_14. &员工.accdb.职工bona.部门=b.部门groupbya.部门15. Setrst=(sqls)16. Sheets(部门).Range(b2).CopyFromRecordsetrst17. 关闭记录集18. Setrst=Nothing释放对象19. 关闭连接20. Setcnn=Nothing释放对象21. MsgBox操作成功！22. EndSub4、注意事项 关于ADO控件，有两种创建方式，一种是如前述的那样，先加引用，然后在代码中就可以定义这种类型的对象，

27、再通过New的方式建立对象。另一种方式直接创建，代码如下：DimcnnAsObject,rstAsObjectSetcnn=CreateObject()Setrst=CreateObject()其实这种方法更实用，因为加引用必须是熟悉系统的人才能操作，如果将写好的程序给一般人使用，难道每次你还指导他去加引用 执行SQL语句有三种方式，一种是用connection，即上面的，这种方式比较适合无返回记录的语句，即DML语句。如果执行有返回记录的SQL语句，也可以取到记录，只是RecordCount总是反馈-1。这种情况下可以根据判断有无查询结果，如果=true就表示查询结果为空。另一种方式是用Re

28、cordSet，即上面的，这个适合有返回记录的语句，即select语句，因为这种方式能够返回记录数RecordCount。当然还有第三种方式，就是用command，这个比较适合执行存储过程，因为这种方式可以传递参数。三种方式command方式功能最强，用起来也最麻烦，connection最弱，用起来也最简单。 取值除了前面说的CopyFromRecordset，还可以用循环的方式逐个取值，例如：vb1. Fori=1torst.RecordCount2. Forj=1To3. Cells(i+1,j)=(j-1).Value4. Nextj5. 6. Nexti ADO也可也连接其他数据库，只

29、是连接串不同，其它操作一样，例如Oracle，连接语句如下：Provider=msdaora;DataSource=dl580;UserId=username;Password=userpasswd;其中dl580是客户端配置的连接名称，后面是Oracle用户名和密码。附录：SQL多表查询语句的写法1、嵌套查询嵌套查询是将一个SELECT语句包含在另一个SELECT语句的WHERE子句中，也称为子查询。子查询（内层查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果必须有确定的值。利用嵌套查询可以将几个简单查询组成一个复杂查询，从而增强SQL的查询能力。1、查询“张三”选修的课程

30、和成绩select学号,课程,成绩from课程$where学号=(select学号from学生$where姓名=张三)2、查询“张三”选修的语文课和成绩select学号,课程,成绩from课程$where学号=(select学号from学生$where姓名=张三and课程=语文)3、查询所有考试学生的成绩select*FROM课程$where成绩notin(selectdistinct学号from学生$)2、合并查询合并查询想必大家都知道了，数据透视表多表查询，一般都使用的是合并查询，它合并的是两个或两个以上查询的结果。参加合并查询的列数要相同，对应列的数据类型必须兼容，各语句中对应的结果集列出现的顺序必须相同。与连接查询相比，联合查询增加记录的行数，连接查询则是增加记录的列数。联合查询语句如下：select*fromunionall其中ALL选项保留结果集中的重复记录，默认时系统自动删除记录。如，依据学号查询语文和物