SQL在Excel中的应用方法.docx
- 文档编号:6711933
- 上传时间:2023-01-09
- 格式:DOCX
- 页数:13
- 大小:24.27KB
SQL在Excel中的应用方法.docx
《SQL在Excel中的应用方法.docx》由会员分享,可在线阅读,更多相关《SQL在Excel中的应用方法.docx(13页珍藏版)》请在冰豆网上搜索。
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应用\分公司].[财务部$]unionall
Select“分公司1”as公司,“市场部”as部门,*from[F:
\SQL之Excel应用\分公司].[市场部$]unionall
Select“分公司2”as公司,“财务部”as部门,*from[F:
\SQL之Excel应用\分公司].[财务部$]unionall
Select“分公司2”as公司,“市场部”as部门,*from[F:
\SQL之Excel应用\分公司].[市场部$]
因为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位置如下:
桌面\;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),点击配置,数据库版本选择版本(office2007以上);如果找不到以上版本,就删除原来的数据源Excelfiles,重新添加一个,注意要选择带有xlsx的驱动程序。
office版本和版本号:
office97:
、office2000:
、officeXP(2002):
、office2003:
、office2007:
、office2010:
、office2013:
选择文件并确定后,如果提示“数据源中没有包含可见的表格”,点击确定,在随后弹出的向导窗口中点击“选项”按钮,勾选“系统表”,确定后就可以看到表了,如下图:
∙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.DimcnnAs
4.'新建一个连接对象
5.Setcnn=New
6.'建立连接
7.Withcnn
8..Provider=
9.'当前文件的路径可以用
10..Open&"\员工.accdb"
11.EndWith
12.'使用SQL语句操作数据库
13.DimsqlAsString
14.sql="update职工set年龄=20where姓名='张丽'"
15.sql'执行SQL命令,无需返回值
16.'关闭连接
17.Setcnn=Nothing'释放对象
18.MsgBox"操作成功!
"
19.EndSub
∙查询表,有返回记录,注意下面例子中定义和连接的不同写法:
[vb]
1.'查询数据库表数据
2.Subado_test2()
3.DimcnnAsNew
4.'建立连接,当前文件的路径可以用
5.&&"\员工.accdb"
6.'使用SQL语句操作数据库
7.DimsqlsAsString
8.DimrstAsNew
9.sqls="select*from职工"
10.Setrst=(sqls)'执行SQL命令
11.'用循环获取字段名
12.DimiAsInteger
13.Fori=0To
14.Cells(1,i+1)=(i).name
15.Nexti
16.'保存查询记录
17.Range("a2").CopyFromRecordsetrst
18.'关闭记录集
19.Setrst=Nothing'释放对象
20.'关闭连接
21.Setcnn=Nothing'释放对象
22.MsgBox"操作成功!
"
23.EndSub
∙将工作表中的数据保存到数据库表中方法是更新记录集,再调用记录集update方法,例如:
[vb]
1.'将工作表数据保存到数据库
2.Subado_test3()
3.DimcnnAs
4.DimrstAs
5.Dimsqls,mytableAsString
6.Dimi,j,nAsInteger
7.'建立连接,当前文件的路径可以用
8.Setcnn=New
9.&&"\员工.accdb"
10.mytable="职工"
11.n=Range("a1").End(xlDown).Row'当前工作表有效行数
12.'使用SQL语句操作数据库
13.Fori=2Ton
14.sqls="select*from"&mytable&"where编号='"&Cells(i,1).Value&"'"
15.Setrst=New
16.'用记录集对象执行SQL语句
17.,cnn,adOpenKeyset,adLockOptimistic
18.If=0'找不到,增加一条空记录
19.Forj=1To
20.(j-1)=Cells(i,j).Value
21.Nextj
22.
23.Nexti
24.'关闭记录集
25.Setrst=Nothing'释放对象
26.'关闭连接
27.Setcnn=Nothing'释放对象
28.MsgBox"操作成功!
"
29.EndSub
3、连接Excel工作表
∙连接Excel,注意连接串(增加一个ExtendedProperties=)和SQL语句的写法:
[vb]
1.'连接Excel工作表
2.Subado_test4()
3.DimcnnAs
4.DimrstAs
5.DimsqlsAsString
6.'建立连接,注意连接串和SQL语句的写法
7.Setcnn=New
8.Withcnn
9..Provider=
10..Open&"\"
11.EndWith
12.'使用SQL语句操作数据库
13.sqls="select*from[sheet1$]"
14.Setrst=(sqls)
15.Sheets("sheet6").Range("A1").CopyFromRecordsetrst
16.'关闭记录集
17.Setrst=Nothing'释放对象
18.'关闭连接
19.Setcnn=Nothing'释放对象
20.MsgBox"操作成功!
"
21.EndSub
∙同时连接Excel和Access数据库,主要看连接串和SQL语句的写法:
[vb]
1.'连接Excel工作表和Access数据库
2.Subado_test5()
3.DimcnnAs
4.DimrstAs
5.DimsqlsAsString
6.'建立连接,注意连接串和SQL语句的写法
7.Setcnn=New
8.Withcnn
9..Provider=
10..Open
11.EndWith
12.'使用SQL语句操作数据库
13.sqls="selecta.部门,count(*)from[部门$A:
A]aleftjoin[database="&_
14.&"\员工.accdb].职工bona.部门=b.部门groupbya.部门"
15.Setrst=(sqls)
16.Sheets("部门").Range("b2").CopyFromRecordsetrst
17.'关闭记录集
18.Setrst=Nothing'释放对象
19.'关闭连接
20.Setcnn=Nothing'释放对象
21.MsgBox"操作成功!
"
22.EndSub
4、注意事项
∙关于ADO控件,有两种创建方式,一种是如前述的那样,先加引用,然后在代码中就可以定义这种类型的对象,再通过New的方式建立对象。
另一种方式直接创建,代码如下:
DimcnnAsObject,rstAsObject
Setcnn=CreateObject("")
Setrst=CreateObject("")
其实这种方法更实用,因为加引用必须是熟悉系统的人才能操作,如果将写好的程序给一般人使用,难道每次你还指导他去加引用
∙执行SQL语句有三种方式,一种是用connection,即上面的,这种方式比较适合无返回记录的语句,即DML语句。
如果执行有返回记录的SQL语句,也可以取到记录,只是RecordCount总是反馈-1。
这种情况下可以根据判断有无查询结果,如果=true就表示查询结果为空。
另一种方式是用RecordSet,即上面的,这个适合有返回记录的语句,即select语句,因为这种方式能够返回记录数RecordCount。
当然还有第三种方式,就是用command,这个比较适合执行存储过程,因为这种方式可以传递参数。
三种方式command方式功能最强,用起来也最麻烦,connection最弱,用起来也最简单。
∙取值除了前面说的CopyFromRecordset,还可以用循环的方式逐个取值,例如:
[vb]
1.Fori=1torst.RecordCount
2.Forj=1To
3.Cells(i+1,j)=(j-1).Value
4.Nextj
5.
6.Nexti
∙ADO也可也连接其他数据库,只是连接串不同,其它操作一样,例如Oracle,连接语句如下:
"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、查询所有考试学生的成绩
select*FROM[课程$]where成绩notin(selectdistinct学号from[学生$])
2、合并查询
合并查询想必大家都知道了,数据透视表多表查询,一般都使用的是合并查询,它合并的是两个或两个以上查询的结果。
参加合并查询的列数要相同,对应列的数据类型必须兼容,各语句中对应的结果集列出现的顺序必须相同。
与连接查询相比,联合查询增加记录的行数,连接查询则是增加记录的列数。
联合查询语句如下:
select*from[]union[all]
其中ALL选项保留结果集中的重复记录,默认时系统自动删除记录。
如,依据学号查询语文和物
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Excel 中的 应用 方法