SQL在Excel中的应用方法

/ I /jfTk / /J/ V/ / Z,/J!C/jfjri/ / /TkSQL在Excel中的应用方法(总17页)r/ 1/JrJr Jr JrJr/JFJF jr/JrTLJr / JF /jf/ / Z-CAL-FENGHAI.-(YICAI)-Company Onel-CAL-本页仅作为文档封面，使用请直接删除SQL在Excel中的应用方法iamlaosng 文Excel中使用SQL的主要目的是连接数据库（或Excel工作表）导入数据或者对这 些数据进行统计汇总，要达到这个目的，需要好好学习SQL语句的使用。本文主要 说明在Excel中如何使用SQL，至于SQL语句本身就不多作介绍了。一、简单的查询1、建立查询数据选项卡一现有连接一浏览更多或者按快捷键Alt+D+D+D选择要查询的Excel文件和文件中的的工作表，就可以将相应工作表的数据取过来。表现形式可以是表， 也可以是数据透视表等。2、SQL查询语句如果是挑选部分列数据，就需要用SQL语句（取所有数据也可以用SQL语句）。建立查询时，选择工作表后不要点击“确定”按钮，而是先点击“属性”按钮，弹 出窗口中选择“定义”选项卡，在命令文本框中输入SQL查询语句（原来的工作表名 称，表示所有数据，可以认为是取所有数据的SQL的一种特殊写法）：Select字段列表from 工作表名$其中字段列表就是需要选择的字段，数据源用工作表名称加“$ “再用中括号括起来，例如：select prov_name, city_name, xs_mc, xs_code from Sheet1$select * from Sheet1$ -取所有数据偶然发现，字段名不能用no，估计是保留字，如需要，用中括号括起来，例如：select no,prov_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 &城市，xs_mc as 县市，xs_code as编码 from Sheet1$非正常表格：数据区域(含字段名)不在第一行需要在工作表名称后面指定数据范围，例如：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注意：使用名称时没有$符号，也没有方括号了。数据更新：数据源发生变化，需要更新数据，方法：点击右键一弹出菜单一刷新 意外：如果打开Excel文件后弹出不是选择工作表的窗口而是一个“数据连接属性” 窗口，可以关闭这个窗口，然后将Excel应用极小化再极大化方式消除，或者在弹出 选择文件的窗口时，退回上一级文件夹，删除那个Queries文件夹，就行了。4、外部数据属性修改SQL语句后，如显示格式不是预想的那样，需要去掉“外部数据属性”中“保留列属性”前面的勾 选。方法：点击右键一弹出菜单一表格一外部数据属性，弹出窗口如下：连接名称：数据格式和布局包含行号 保留列排序/筋迷/布局（L）叵调整列宽睡）回1茉凿.宰完蓿宿无（乏T如果数据区域的行数在刷新时更改：为新数据插入单元格,珊隙没有愤用的单元格O为新数据插入整行，清陈没有便用的单元裕电）O用新数据辑盖现有单元榕涛除没有便用的单元格（QJi，嘛底： 取捎二、复杂的查询1、多表联合相同结构的多个表合并到一起，用union连接SQL语句，例如：Select * from 财务部$ union all Select * from 市场部$Union是去重复的，即相同的记录保留一个（类似distinct），Union all则是直接相加两个结果，不去 重复。增加一个部门字段可以将查询结果中的区分开来，以便知道数据来自哪个表。Union 的三个一致，即：字段的数量、类型和顺序。例如：Select “财务部” as部门* from 财务部$ union all Select “市场部” as部门from市 场部$多表联合查询Select * from 部门$ bm,员工$yg where bm.部 门编码=yg .部门编码跨工作簿查询如果数据不仅来自不同的工作表，还来自不同的文件，一样可以用union联合，例如：Select “分公司1” as公司，“财务部” as部门* from F:SQL之 Excel应用分公司.财务部$ union allSelect “分公司1” as公司，“市场部” as部门* from F:SQL之 Excel应用分公司.市场部$ union allSelect “分公司2” as公司，“财务部” as部门* from F:SQL之 Excel应用分公司.财务部$ union allSelect “分公司2” as公司，“市场部” as部门* from F:SQL之 Excel应用分公司.市场部$因为SQL中已经指定了文件名和表名，所以建立连接时连接谁并不重要，这种情况下，建立连接的时候就 连接自己，然后再改写SQL语句。2、子查询和多表连接所谓子查询就是将一个查询结果作为数据源放在主查询语句中，多表连接则是将两个有关联的表通过关键 字段连接在一起查询，这都是SQL知识，不再赘述，需要注意的是，不同的数据库系统SQL都有些微小的 差别，Excel中的SQL也有其自己的一些特点，关于多表查询的写法，见本文附录。3、常用运算符有条件的查询条件是where引导的，用and、or等连接，例如：select prov_name, city_name, xs_mc, xs_codefrom Sheet1$ where prov_name=安 徽 or prov_name= 江苏虽然字符串可以用双引号，但建议用单引号，因为oracle、SQL server都是用单引 号。常用运算符：in、not in-between and 、is null、is not null、& （连字 符）、like、not like，注意：null和任何字段运算的结果都是null。通配符：% （所有字符或无字符）、（单个字符）、（区间，如1-9、!a-f、 1,3,5），例如:select * fromSheet1$ where Email like -h%一h-m开头的电子邮件select * fromSheet1$ where xs_code like %!1,3,5和not like %1,3,5效果相同select * fromSheet1$ wher户籍&-& 工作地 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 在最后，所以其 它关键字后面不能用字段别名，不过，表的别名是可以用的，因为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列标签，例如：Transform sum工资）select部门名称from员工$ group by部门名称pivot职务这个语句产生的结果与数据透视表差不多，相当于一个语句产生一个数据透视表，当然这个透视表是固定 的，和语句对应的。其中的select语句，相当于数据透视表的行字段，其中的聚合函数的参数相当于拖 到数据透视表数据区域的值字段，使用的聚合函数即值字段的汇总方式。其中的pivot字段相当于数据透 视表的列字段，后面的IN （value1, value2, .），相当列字段中的项的排序和筛选，摆弄过数据 透视表，将transform/pivot语句与数据透视表对照，可以轻松掌握这个MS JET新增SQL语句。看一下 效果：外部数据屠性连接名称：iU数据格式和布局包含行号 保留列排序/筋迷/布局（L）叵调整列览（A）它快筐.半兀如果数据区域的行数在刷新时更改：臼为新数据插穴单行格,珊隙没有使用的单元格冬io为新数据插走:整行，清阵没有使用的单元格 忒O用新数据辑盖现有单元榕唐院没有使用的单元格（Q）浦希I 取消列标签筛选Transform sum工资）select部门名称from员工$ group by部门名称pivot职务in （主管经理）多个行标签Transform sum工资）select职务，性别from员工$ group by职务性别pivot部门名称如需要添加总计，则需要先构造一个子查询结果，这个结果由正常的查询和统计查询联合在一起，再以这 个结果作为数据源，构成上面的二维表。例如：Transform sum工资）select 部门名称 from （Select部门名称职务工资from员工$union allSelect部门名称，总计，sum（工资from员工$ group by部门名称）group by部门名称pivot职务in （主管，经理，职员，总计）6、文本型数字SQL查询时字段类型是由前8行数据决定的（这个数字是Excel定的），如果前8行都是数值型，后面有 文本型数字，则查询结果中这些数字变成为空；前8行是文本型，后面是数值型则不影响，似乎查询结果 偏向文本。如果前8行中类型不一致，有数值型，也有文本型数字，可以通过在连接字符串中加入【MEX=1 则后面有文本型字符也没关系，但是，如果前8行都是数值型，加了这个也不管用，因为前8行已经决定 是数值型了。加IMEX位置如下：Provider= Source=C:Documents and SettingsAdministrator面、;Mode=ShareDeny Write;Extended Properties=HDR=YES;IMEX=1”;Jet OLEDB:System database=；JetOLEDB:Registry Path=；Jet OLEDB:Engine Type=35;Jet OLEDB:DatabaseLocking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global BulkTransactions=1;Jet OLEDB:New Database Password=；Jet OLEDB:CreateSystem Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Dont CopyLocale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;JetOLEDB:SFP=False;Jet OLEDB:Support Complex Data=False7、删除无用的数据源随着我们建立的查询越来越多，打开现有连接时会出现很多我们原来建立的连接，这些连接是Windows自 动保存以便于我们再次使用的，如要删除，可进入“我的文档”下面的“我的数据源”文件夹，删除这些 无用的数据源或者直接删除“我的数据源”文件夹。删除这些连接不会影响原来建立的那些查询。8、Microsoft Query 工具可以利用MQ工具建立查询，对于不熟悉SQL语言的可以用这个调试SQL语句。MQ向导会提供可视化工 具，一步一步引导我们得到所需的数据。查询生成后，可以点击“SQL”按钮进一步修改SQL语句。打开方法：数据选项卡一自其它来源一来自MicrosoftQuery工具一Excel files，选 择文件后确定，进入工具。如果不能选择xlsx文件，是因为数据源版本驱动太低，进入控制面板一管理工具一数据源（ODBC），点 击配置，数据库版本选择Excel版本（office2007以上）；如果找不到以上版本，就删除原来的数据源 Excel files，重新添加一个，注意要选择带有xlsx的驱动程序。office 版本和版本号：office97 :、office2000 :、officeXP（2002） :、office2003 :、office2007: 、 office2010 : 、 office2013: 选择文件并确定后，如果提示“数据源中没有包含可见的表格”，点击确定，在随后弹出的向导窗口中点 击“选项”按钮，勾选“系统表”，确定后就可以看到表了，如下图：殊部舞据霜蛙FT僚连接名称:数据格式和布局包含行号 保留列排序/筋画布局（L）回调整列览）它供世单兀路宿无gf如果数据区域的行数在刷新时更改：为新数据插入单元格,珊隙没有使用的单元格 位O为新数据插宠整行，清除没有使用的单元格您O用新数据霜盖现有单元榕唐除没有使用的单元格，嘛定n 取消MQ工具通过可视化工具生成所需的SQL查询语句，如添加条件、分组等等。点击“SQL”按钮查看生成的语句，可以看到文件名和表名都是用单引号括起来，和中括号 效果一样。MQ工具不仅可以编写SQL查询语句，也可以写insert、delete、update等SQL语 句，例如：Insert into员工$（姓名，性别，工资）values （宋定才，男，5000）三、VBA中使用SQL语句1、连接数据库的工具ADO外部数据属性连接数据格式和布局包含行号 保留列排序/筋因怖局（L）叵调整列览）pr保沮.半兀路措武您如果数据区域的行数在刷新时更改：为制数据插入单元格,珊隙没有使用的单元格蒐i o为新数据插宠整行，清除没有使用的单元格疝 o用新数据霜盖现有单元榕唐除没有使用的单元格取消ADO是个类，有三个工具：connection （连接）、command （命令）和recordset （记 录集）使用前先引用，进入VBE,点击菜单“工具”下面的“引用”，勾选最高版本的ADO, 然后就可以用new在VBA过程中创建对象了。引用窗口如下图：外部数据属性连接名粮数据格式和布局包含行号 保留列排序/筋迷/布局（L）叵调整列宽睡）pfi天泪.半兀桁措武您如果数据区域的行数在刷新时更改：为制数据插入单元格,珊隙没有使用的单元格:敏O为新数据插走整行，清陈没有便用的单元裕疝O用新数据辑盖现有单元榕涛除没有便用的单元格取捎2、连接Access数据库连接字符串：连接数据库的关键是连接串的写法，可以参考建立查询时系统自动生成 的连接串，方法是：数据选项卡一自Access，在弹出窗口选择数据文件和表后，点击 属性，弹出窗口中点击定义选项卡，其中的连接字符串就是连接access的字符串，内 容如下：Provider=；UserID=Admin;Data Source=D:Lc;Mode=Share Deny Write;ExtendedProperties=；JetOLEDB:System database=；JetOLEDB:Registry Path=；Jet OLEDB:DatabasePassword=；JetOLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:GlobalPartialBulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New DatabasePassword=；JetOLEDB:Create System Database=False;Jet OLEDB:EncryptDatabase=False;Jet OLEDB:Dont CopyLocale on Compact=False;Jet OLEDB:CompactWithout Replica Repair=False;JetOLEDB:SFP=False;Jet OLEDB:Support ComplexData=False根据上面的连接串可以写出下面的VBA代码。连接串中大部分是默认值，VBA代码中 可以不写，例如，下面的代码是连接access数据库：vb view plain copy1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.更新工作表数据，无返回数据Subado_test1()Dim cnn As新建一个连接对象Set cnn = New建立连接With cnn.Provider =当前文件的路径可以用.Open & 员工.accdb”End With使用SQL语句操作数据库Dim sql As Stringsql = update 职工 set 年龄=20 where 姓名=张丽sql 执行SQL命令，无需返回值关闭连接Set cnn = Nothing 释放对象MsgBox 操作成功！21. End Sub查询表，有返回记录，注意下面例子中定义和连接的不同写法:vb view plain copyS31.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.查询数据库表数据Subado_test2()Dim cnn As New建立连接，当前文件的路径可以用Provider = Data Source = & 员工.accdb使用SQL语句操作数据库Dim sqls As StringDim rst As Newsqls = select * from 职工Set rst = (sqls) 执行SQL命令用循环获取字段名Dim i As IntegerFor i = 0 ToCells(1, i + 1) = (i).nameNext i保存查询记录Range(a2”).CopyFromRecordset rst关闭记录集Setrst=Nothing释放对象关闭连接Setcnn=Nothing释放对象MsgBox ”操作成功！End Sub将工作表中的数据保存到数据库表中方法是更新记录集，再调用记录集update方法， 例如：vb view plain copyS3将工作表数据保存到数据库3.Dim cnn As2. Subado_test3()4.5.Dim rst AsDim sqls, mytable As String6.Dim i, j, n As Integer7.8.建立连接，当前文件的路径可以用9.Set cnn = New10.Provider = Data Source = & 员工.accdb”11.mytable =职X”12.n =Range(a1). End (xlDown).Row 当前工作表有效彳丁数13.使用SQL语句操作数据库14.For i = 2 To n15.sqls = select * from &mytable & where 编号=,& Cells(i, 1).Value & ”16.Set rst = New17.用记录集对象执行SQL语句18., cnn, adOpenKeyset,adLockOptimistic19.If = 0找不到，增加一条空记录20.For j = 1 To21.(j - 1) = Cells(i,j).Value22.Next j23.24.Next i25.26.关闭记录集27.Set rst = Nothing 释放对象28.关闭连接29.Set cnn = Nothing 释放对象30.31.MsgBox ”操作成功！32. EndSub3、连接Excel工作表连接Excel,注意连接串（增加一个ExtendedProperties二excel ）和SQL语句的写 法：vb view plain copy1 连接Excel X作表2.Subado_test4()3.Dim cnn As4.Dim rst As155.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24. End1.2.3.4.5.6.7.8.9.Dim sqls As String建立连接，注意连接串和SQL语句的写法Set cnn = NewWith cnn.Provider = Properties=excel ”.Open &End With使用SQL语句操作数据库sqls = select * from sheet1$”Set rst = (sqls)Sheets(sheet6”).Range(A1”).CopyFromRecordsetrst关闭记录集Set rst = Nothing释放对象关闭连接Set cnn = Nothing释放对象MsgBox ”操作成功！Sub同时连接Excel和Access数据库，主要看连接串和SQL语句的写法:vb view plain copy 连接Excel工作表和Access数据库Sub ado_test5()Dim cnn AsDim rst AsDim sqls As String建立连接，注意连接串和SQL语句的写法Set cnn = NewWith cnn10. .Provider = Properties=excel 11. .Open12. End With13. 使用SQL语句操作数据库14. sqls = select a.部门，count(*) from 部门 $A:A a left join database15. & 员工.accdb.职工 b on a.部门=b.部门 group bya.部门16. Set rst = (sqls)17.Sheets(部门”).Range(b2”).CopyFromRecordset18.19.关闭记录集20.Set rst = Nothing释放对象21.关闭连接22.Set cnn = Nothing释放对象23.24.MsgBox ”操作成功！25. EndSubrst4、注意事项关于ADO控件，有两种创建方式，一种是如前述的那样，先加引用，然后在代码中就 可以定义这种类型的对象，再通过New的方式建立对象。另一种方式直接创建，代码 如下：Dim cnnAs Object, rst As ObjectSet cnn =CreateObject()Set rst =CreateObject()其实这种方法更实用，因为加引用必须是熟悉系统的人才能操作，如果将写好的程序给一般人使用，难道 每次你还指导他去加引用？执行SQL语句有三种方式，一种是用connection，即上面的，这种方式比较适合无返 回记录的语句，即DML语句。如果执行有返回记录的SQL语句，也可以取到记录，只 是RecordCount总是反馈-1。这种情况下可以根据判断有无查询结果，如果二true就 表示查询结果为空。另一种方式是用RecordSet，即上面的，这个适合有返回记录的 语句，即select语句，因为这种方式能够返回记录数RecordCount。当然还有第三种 方式，就是用command，这个比较适合执行存储过程，因为这种方式可以传递参数。三种方式command方式功能最强，用起来也最麻烦，connection最弱，用起来也最简 单。取值除了前面说的CopyFromRecordset，还可以用循环的方式逐个取值，例如：vb view plain copyFori=1 toForNextrst. RecordCountj= 1 ToCells(i+1, j) =(j-1).Value jNextADO也可也连接其他数据库，只是连接串不同，其它操作一样，例如Oracle，连接语 句如下：Provider=msdaora;DataSource=dl580;User Id=username;Password=userpasswd;其中dl580是Oracle 客户端配置的连接名称，后面是Oracle用户名和密码。附录：SQL多表查询语句的写法1、嵌套查询嵌套查询是将一个SELECT语句包含在另一个SELECT语句的WHERE子句中，也称为子查询。子查询（内层 查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果必须有确定的值。利用嵌套查 询可以将几个简单查询组成一个复杂查询，从而增强SQL的查询能力。1、查询“张三”选修的课程和成绩 select学号，课程，成绩from 课程$ where学号=（select学号from 学生$ where姓名=张三） 2、查询“张三”选修的语文课和成绩 select学号，课程，成绩from 课程$ where学号=（select学号from 学生$ where姓名=张三 and课程=语文） 3、查询所有考试学生的成绩 select * FROM 课程$ where 成绩 not in (select distinct 学号 from 学生$)2、合并查询合并查询想必大家都知道了，数据透视表多表查询，一般都使用的是合并查询，它合并的是两个或两个以 上查询的结果。参加合并查询的列数要相同，对应列的数据类型必须兼容，各语句中对应的结果集列出现 的顺序必须相同。与连接查询相比，联合查询增加记录的行数，连接查询则是增加记录的列数。联合查询语句如下：select * from union all其中ALL选项保留结果集中的重复记录，默认时系统自动删除记录。如，依据学号查询语文和物理成绩：select学号,成绩，课程from 课程$ where课程=语文 union select学号，成绩，课程from 课程$ where课程=物理3、多表查询多表查询亦称连接查询，它同时涉及两个或两个以上的公共字段或语义相同的字段，也就是说数据表是通 过表的列（字段）来体现的。是数据透视表中最重要的的一种查询。连接操作的目的就是通过加在连接字 段的条件将多个表连接在一起，以便在多个表中查询数据。多表查询，需要有相同的两个表的联接条件，该条件放在WHERE子句中，格式为：select 目标列from表明1,表名2 where表名1.字段名1=表名2.字段名21、依据学号条件查询学生的各门成绩：select * from 学生$,课程$ where 学生$.学号=课程$.学号为了简化输入，在SELECT命令中允许使用表的别名。为此，可以在FROM子句中定义一个临时别名，以 便查询使用。其格式如下：SELECT 目标列 FROM表名1别名1，表名2别名2WHERE别名1字段名1=别名2.字段名 2 2、依据学号条件查询学生的各门成绩大于85分select kc.学号，姓名，课程，成绩from 学生$ xs ,课程$kc where xs.学号=kc.学号and成绩85在数据透视表中对多表查询，还可以使用另一种连接格式，就是内连接查询，也叫等值连接查询。它是组 合两个（或多个以上）表，最常使用的方法。其语句如下：SELECT 目标列 FROM表名1 inner join表名2 on表名1.字段名1=表名2.字段名23、依据学号条件查询学生的各门成绩大于85分select kc.学号，姓名，课程，成绩from 学生$xs inner join 课程$ kc on xs.学号=kc.学号4、外连接查询在内连接查询中，只有在两表中同时匹配的行才才能在结果集中选出，而在外连接中可以只限制一个表， 而不限制另一个表，其所有的行都都出现在结果集中。外连接分为左外连接，右外连接和全部链接。左连接是对连接条件中左边的表不加限制；右连接是对右边的表不加限制；全部连接是对两个表都不加限 制。其语法如下：select选择列数from表名 1 lift I right I fullouter jion表名 2 on表名 1.列 名=表名2.列名1、以学生$中记录为准，课程$中不存在的学号也可以列出：select kc.学号，姓名，课程，成绩from 学生$xs left join 课程$ kc on xs.学号=kc.学号2、以课程$中记录为准，学生$中不存在的学号也可以列出：select kc.学号，姓名，课程，成绩from 学生$xs right join 课程$ kc on xs.学号=kc.学号