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_codefromSheet1$
selectfromSheet1$--取所有数据
偶然发现,字段名不能用no,估计是保留字,如需要,用中括号括起来,例如：
selectno,prov_name,city_name,xs_mc,xs_codefromSheet1$
字段名中含有特殊字符的也要用中括号括起来,如/空格等
Excel查询没有伪表概念,对于表达式的计算直接用select既可,例如
Select23+45--返回68
Selectdate--返回当前日期
3、修改查询语句
方法：点击右键—弹出菜单—表格—编辑查询
通过修改SQL语句可以变更所取的数据,也可以将建立查询时的简单SQL语句改成复杂的SQL语句;
字段名更换：如果想换个字段名,用“as新字段名”既可,例如：
selectprov_nameas省,city_nameas城市,xs_mcas县市,xs_codeas编码fromSheet1$
非正常表格：数据区域含字段名不在第一行
需要在工作表名称后面指定数据范围,例如：
selectprov_name,city_name,xs_mc,xs_codefromSheet1$B2:G2000
或者,将数据块定义为一个名称,假设定义为mydata,SQL语句如下：
selectprov_name,city_name,xs_mc,xs_codefrommydata
注意：使用名称时没有$符号,也没有方括号了;
数据更新：数据源发生变化,需要更新数据,方法：点击右键—弹出菜单—刷新意外：如果打开Excel文件后弹出不是选择工作表的窗口而是一个“数据连接属性”窗口,可以关闭这个窗口,然后将Excel应用极小化再极大化方式消除,或者在弹出选择文件的窗口时,退回上一级文件夹,删除那个Queries文件夹,就行了;
4、外部数据属性
修改SQL语句后,如显示格式不是预想的那样,需要去掉“外部数据属性”中“保留列属性”前面的勾选;方法：点击右键—弹出菜单—表格—外部数据属性,弹出窗口如下：
二、复杂的查询
1、多表联合
相同结构的多个表合并到一起,用union连接SQL语句,例如：
Selectfrom财务部$unionallSelectfrom市场部$
Union是去重复的,即相同的记录保留一个类似distinct,Unionall则是直接相加两个结果,不去重复;
增加一个部门字段可以将查询结果中的区分开来,以便知道数据来自哪个
表;Union的三个一致,即：字段的数量、类型和顺序;例如：
Select“财务部”as部门,from财务部$unionallSelect“市场部”as部门,from市场部$
多表联合查询
Selectfrom部门$bm,员工$ygwherebm.部门编码=yg.部门编码
跨工作簿查询
如果数据不仅来自不同的工作表,还来自不同的文件,一样可以用union联合,例如：
Select“分公司1”as公司,“财务部”as部门,fromF:\SQL之Excel应用\分公司1.xlsx.财务部$unionall Select“分公司1”as公司,“市场部”as部门,fromF:\SQL之Excel应用\分公司1.xlsx.市场部$unionall Select“分公司2”as公司,“财务部”as部门,fromF:\SQL之Excel应用\分公司2.xlsx.财务部$unionall Select“分公司2”as公司,“市场部”as部门,fromF:\SQL之Excel应用\分公司2.xlsx.市场部$
因为SQL中已经指定了文件名和表名,所以建立连接时连接谁并不重要,这种情况下,建立连接的时候就连接自己,然后再改写SQL语句;
2、子查询和多表连接
所谓子查询就是将一个查询结果作为数据源放在主查询语句中,多表连接则是将两个有关联的表通过关键字段连接在一起查询,这都是SQL知识,不再赘述,需要注意的是,不同的数据库系统SQL都有些微小的差别,Excel中的SQL也有其自己的一些特点,关于多表查询的写法,见本文附录;
3、常用运算符
有条件的查询
条件是where引导的,用and、or等连接,例如：
selectprov_name,city_name,xs_mc,xs_codefromSheet1$whereprov_name=’安
徽’orprov_name=’江苏’
--虽然字符串可以用双引号,但建议用单引号,因为oracle、SQLserver都是用单引号;
常用运算符：in、notin、between…and…、isnull、isnotnull、&连字符、like、notlike,注意：null和任何字段运算的结果都是null;
通配符：%所有字符或无字符、_单个字符、区间,如1-9、a-f、1,3,5,例如：
selectfromSheet1$whereEmaillike‘h-m%’--h-m开头的电子邮件
selectfromSheet1$wherexs_codelike'%1,3,5'–和notlike'%1,3,5'效果相同
selectfromSheet1$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字段相当于数据透视表的列字段,后面的INvalue1,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位置如下：
桌面
\tb_city_zd.xls;Mode=ShareDenyWrite;ExtendedProperties="HDR=YES;IMEX=1";JetOLEDB:Systemdatabase=" ";JetOLEDB:RegistryPath="";JetOLEDB:EngineType=35;JetOLEDB:DatabaseLockingMode=0;JetOLEDB:GlobalP artialBulkOps=2;JetOLEDB:GlobalBulkTransactions=1;JetOLEDB:NewDatabasePassword="";JetOLEDB:Create SystemDatabase=False;JetOLEDB:EncryptDatabase=False;JetOLEDB:Don'tCopyLocaleonCompact=False;JetOL EDB: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、officeXP2002: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的字符串,内容如下：
根据上面的连接串可以写出下面的VBA代码;连接串中大部分是默认值,VBA代码中可以不写,例如,下面的代码是连接access数据库：
vb
1.'更新工作表数据,无返回数据
2.Subado_test1
3.Dim cnn As ADODB.Connection
4.'
5.新建一个连接对象
6.Set cnn=New ADODB.Connection
7.'建立连接
8.With cnn
9..Provider=
10.'当前文件的路径可以用ThisWorkbook.Path
11..OpenThisWorkbook.Path&"\员工.accdb"
12.EndWith
13.'使用SQL语句操作数据库
14.Dim sql AsString
15.sql="update职工set年龄=20where姓名='张丽'"
n.Executesql'
17.执行SQL命令,无需返回值
n.Close'
19.关闭连接
20.
21.Set cnn=Nothing'
22.释放对象
23.MsgBox"操作成功"
24.EndSub
查询表,有返回记录,注意下面例子中定义和连接的不同写法：vb
1.'查询数据库表数据
2.Subado_test2
3.Dim cnn AsNew ADODB.Connection
4.'建立连接,当前文件的路径可以用ThisWorkbook.Path
n.Open&ThisWorkbook.Path&"\员工.accdb"
6.'使用SQL语句操作数据库
7.Dim sqls AsString
8.Dim rst AsNew ADODB.Recordset
9.sqls="selectfrom职工"
10.Set rst=cnn.Executesqls'
11.执行SQL命令
12.
13.'用循环获取字段名
14.Dim i AsInteger
15.For i=0To
16.Cells1,i+1=
17.Next i
18.'保存查询记录
19.Range"a2".CopyFromRecordsetrst
20.rst.Close'
21.关闭记录集
22.
23.Set rst=Nothing'
24.释放对象
n.Close'
26.关闭连接
27.
28.Set cnn=Nothing'
29.释放对象
30.MsgBox"操作成功"
31.EndSub
将工作表中的数据保存到数据库表中方法是更新记录集,再调用记录集update 方法,例如：
vb
1.'将工作表数据保存到数据库
2.Subado_test3
3.Dim cnn As ADODB.Connection
4.Dim rst As ADODB.Recordset
5.Dim sqls,mytable AsString
6.Dim i,j,n AsInteger
7.'建立连接,当前文件的路径可以用ThisWorkbook.Path
8.Set cnn=New ADODB.Connection
n.Open&ThisWorkbook.Path&"\员工.accdb"
10.mytable="职工"
11.n=Range"a1".End xlDown.Row '当前工作表有效行数
12.'使用SQL语句操作数据库
13.For i=2To n
14.sqls="selectfrom"&mytable&"where编号='"&Cellsi,1.Value&"'"
15.Set rst=New ADODB.Recordset
16.'用记录集对象执行SQL语句
17.rst.Open,cnn,adOpenKeyset,adLockOptimistic
18.If rst.RecordCount=0Thenrst.AddNew'找不到,增加一条空记录
19.For j=1To
20.rst.Fieldsj-1=Cellsi,j.Value
21.Next j
22.rst.Update
23.Next i
24.rst.Close'
25.关闭记录集
26.
27.Set rst=Nothing'
28.释放对象
n.Close'
30.关闭连接
31.
32.Set cnn=Nothing'
33.释放对象
34.MsgBox"操作成功"
35.EndSub
3、连接Excel工作表
连接Excel,注意连接串增加一个ExtendedProperties=excel12.0和SQL语句的写法：
vb
1.'连接Excel工作表
2.Subado_test4
3.Dim cnn As ADODB.Connection
4.Dim rst As ADODB.Recordset
5.Dim sqls AsString
6.'建立连接,注意连接串和SQL语句的写法
7.Set cnn=New ADODB.Connection
8.With cnn
9..Provider=
10..OpenThisWorkbook.Path&"\tb_city_zd.xls"
11.EndWith
12.'使用SQL语句操作数据库
13.sqls="selectfromsheet1$"
14.Set rst=cnn.Executesqls
15.Sheets"sheet6".Range"A1".CopyFromRecordsetrst
16.
17.rst.Close'
18.关闭记录集
19.
20.Set rst=Nothing'
21.释放对象
n.Close'
23.关闭连接
24.
25.Set cnn=Nothing'
26.释放对象
27.MsgBox"操作成功"
28.EndSub
同时连接Excel和Access数据库,主要看连接串和SQL语句的写法：vb
1.'连接Excel工作表和Access数据库
2.Sub ado_test5
3.Dim cnn As ADODB.Connection
4.Dim rst As ADODB.Recordset
5.Dim sqls AsString
6.'建立连接,注意连接串和SQL语句的写法
7.Set cnn=New ADODB.Connection
8.With cnn
9..Provider=
10..OpenThisWorkbook.FullName
11.EndWith
12.'使用SQL语句操作数据库
13.sqls="selecta.部门,countfrom部门$A:Aaleftjoindatabase="&_
14.ThisWorkbook.Path&"\员工.accdb.职工bona.部门=b.部门groupbya.部门"
15.Set rst=cnn.Executesqls
16.Sheets"部门".Range"b2".CopyFromRecordsetrst
17.rst.Close'
18.关闭记录集
19.
20.Set rst=Nothing'
21.释放对象
n.Close'
23.关闭连接
24.
25.Set cnn=Nothing'
26.释放对象
27.MsgBox"操作成功"
28.EndSub
4、注意事项
关于ADO控件,有两种创建方式,一种是如前述的那样,先加引用,然后在代码中就可以定义这种类型的对象,再通过New的方式建立对象;另一种方式直接创建,代码如下：
DimcnnAsObject,rstAsObject
Setcnn=CreateObject"ADODB.Connection"
Setrst=CreateObject"ADODB.Recordset"
其实这种方法更实用,因为加引用必须是熟悉系统的人才能操作,如果将写好的程序给一般人使用,难道每次你还指导他去加引用
执行SQL语句有三种方式,一种是用connection,即上面的cnn.Execute,这种方式比较适合无返回记录的语句,即DML语句;如果执行有返回记录的SQL语句,也可以取到记录,只是RecordCount总是反馈-1;这种情况下可以根据rst.eof 判断有无查询结果,如果rst.eof=true就表示查询结果为空;另一种方式是用RecordSet,即上面的rst.Open,这个适合有返回记录的语句,即select语句,
因为这种方式能够返回记录数RecordCount;当然还有第三种方式,就是用
command,这个比较适合执行存储过程,因为这种方式可以传递参数;三种方式
command方式功能最强,用起来也最麻烦,connection最弱,用起来也最简单;
取值除了前面说的CopyFromRecordset,还可以用循环的方式逐个取值,例如：vb
1.For i=1torst.RecordCount
2.For j=1To
3.Cellsi+1,j=rst.Fieldsj-1.Value
4.Next j
5.rst.MoveNext
6.Next i
ADO也可也连接其他数据库,只是连接串不同,其它操作一样,例如Oracle,连接语句如下：
cnn.Open"Provider=msdaora;DataSource=dl580;UserId=username;Password=userpasswd;"
其中dl580是客户端配置的连接名称,后面是Oracle用户名和密码;
附录：SQL多表查询语句的写法
1、嵌套查询
嵌套查询是将一个SELECT语句包含在另一个SELECT语句的WHERE子句中,也称为子查询;子查询内层查询的结果用作建立其父查询外层查询的条件,因此,子查询的结果必须有确定的值;利用嵌套查询可以将几个简单查询组成一个复杂查询,从而增强SQL的查询能力;
1、查询“张三”选修的课程和成绩
select学号,课程,成绩from课程$where学号=select学号from学生$where姓名="张三"
2、查询“张三”选修的语文课和成绩
select学号,课程,成绩from课程$where学号=select学号from学生$where姓名="张三"and课程="语文"
3、查询所有考试学生的成绩
selectFROM课程$where成绩notinselectdistinct学号from学生$
2、合并查询
合并查询想必大家都知道了,数据透视表多表查询,一般都使用的是合并查询,它合并的是两个或两个以上查询的结果;参加合并查询的列数要相同,对应列的数据类型必须兼容,各语句中对应的结果集列出现的顺序必须相同;与连接查询相比,联合查询增加记录的行数,连接查询则是增加记录的列数;联合查询语句如下：selectfromunionall
其中ALL选项保留结果集中的重复记录,默认时系统自动删除记录;如,依据学号查询语文和物理成绩：
select学号,成绩,课程from课程$where课程="语文"union select学号,成绩,课程from课程$where课程="物理"
3、多表查询
多表查询亦称连接查询,它同时涉及两个或两个以上的公共字段或语义相同的字段,也就是说数据表是通过表的列字段来体现的;是数据透视表中最重要的的一种查询;连接操作的目的就是通过加在连接字段的条件将多个表连接在一起,以便在多个表中查询数据;
多表查询,需要有相同的两个表的联接条件,该条件放在WHERE子句中,格式为：
select<目标列>from<表明1>,<表名2>where<表名1>.<字段名1>=<表名2>.<字段名2>
1、依据学号条件查询学生的各门成绩：
selectfrom学生$,课程$where学生$.学号=课程$.学号
为了简化输入,在SELECT命令中允许使用表的别名;为此,可以在FROM子句中定义一个临时别名,以便查询使用;其格式如下：
SELECT<目标列>FROM<表名1><别名1>,<表名2><别名2>WHERE<别名1><字段名1>=<别名2>.<字段名2>
2、依据学号条件查询学生的各门成绩大于85分
selectkc.学号,姓名,课程,成绩from学生$xs,课程$kcwherexs.学号=kc.学号and成绩>85
在数据透视表中对多表查询,还可以使用另一种连接格式,就是内连接查询,也叫等值连接查询;它是组合两个或多个以上表,最常使用的方法;其语句如下：
SELECT<目标列>FROM<表名1>innerjoin<表名2>on<表名1>.<字段名1>=<表名2>.<字段名2>
3、依据学号条件查询学生的各门成绩大于85分
selectkc.学号,姓名,课程,成绩from学生$xsinnerjoin课程$kconxs.学号=kc.学号
4、外连接查询
在内连接查询中,只有在两表中同时匹配的行才才能在结果集中选出,而在外连接中可以只限制一个表,而不限制另一个表,其所有的行都都出现在结果集中;外连接分为左外连接,右外连接和全部链接;
左连接是对连接条件中左边的表不加限制；右连接是对右边的表不加限制；全部连接是对两个表都不加限制;其语法如下：
select<选择列数>from<表名1><lift︳right︳fullouter>jion<表名2>on<表名1>.<列名>=<表名2>.<列名> 1、以学生$中记录为准,课程$中不存在的学号也可以列出：
selectkc.学号,姓名,课程,成绩from学生$xsleftjoin课程$kconxs.学号=kc.学号
2、以课程$中记录为准,学生$中不存在的学号也可以列出：
selectkc.学号,姓名,课程,成绩from学生$xsrightjoin课程$kconxs.学号=kc.学号。