ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 40600|回复: 178

[原创] 跟我学SQL in Excel

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-10-18 13:41 | 显示全部楼层 |阅读模式
本帖最后由 ctp_119 于 2013-10-18 14:35 编辑

附件中word文档2个,应文件过大,只能分开压缩上传了,一个是辅助excel文件。
跟我学SQLin Excel
    当我们接触Excel越来越密切,数据量越来越大,函数和公式越来越复杂的时候,你是否感觉到Excel处理数据越来越吃力,运行速度越来越慢,数据处理效率越来越低呢?如果你感觉到了,那么恭喜你,你Excel技能又得提升了,呵呵,那就请你跟着我,慢慢的学习SQLExcel中的基本应用吧。
    通过本帖你可以初步了解数据库的基本结构和要素,简单的SQL语句,Excel中如何应用SQL语句以及一些运用SQL实例。
    一、什么是SQL
    简单说,SQL是标准的结构化数据库查询语言。
    注意:所谓标准指的是应用多种数据库且广为应用。结构化,指的是格式固定(这点非常重要,深刻理解),其后会不断强化对结构化的理解。
    二、为什么要学习SQL
    Excel中应用SQL,就是把excel当作数据库来处理,运用SQL语句的强大功能来处理大量的Excel数据,以便解决Excel处理大量数据效率低下的瓶颈,或用Excel作为前台数据处理,数据库(为了更好的兼容,这里的数据库通指Access数据库)作为后台数据存储仓库,联合一起应用,将会使ExcelSQL发挥的淋漓尽致,至善至美。
    三、要怎么学习SQL
    上面已经说过了,SQL是结构化的数据库查询语言,所谓结构化,就像电脑硬件一样,要什么功能,在相应的接口上插上相应的硬件,系统自动安装驱动或自行安装驱动之后即可使用。如:U盘,摄像头等。SQL也一样,要分类汇总用group by子句,要排序用orderby子句,要唯一用关键字Distinct,要条件筛选用Where子句等等都是结构化的,SQL语句写好之后,也要相应的驱动,如ADO,RDO,DAO等等。总而言之,学习SQL就像搭积木,需要什么配什么。
跟我学SQL in Excel.zip (44.19 KB, 下载次数: 3290)

评分

参与人数 16财富 +20 鲜花 +31 技术 +2 收起 理由
daydaynew + 2 太强大了
hongye89 + 2 感谢帮助
龙城飞将III + 3 历时近一周,终于粗学通读完一遍。
bajifeng + 3 写的简洁精炼,都是干货,很强大。
tianqinglei + 2 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:42 | 显示全部楼层
本帖最后由 ctp_119 于 2013-10-18 13:46 编辑

    四、在Excel中如何使用SQL语句?

                   OLE DB
法(请看动画操作)
    1.操作法file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/msohtmlclip1/01/clip_image001.gif
                   MS Query法(请看动画操作)
   

                        前
期绑定法
    2.vba代码file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/msohtmlclip1/01/clip_image001.gif
                        后期绑定法                        

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:43 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:44 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:48 | 显示全部楼层
本帖最后由 ctp_119 于 2013-10-21 09:58 编辑

所谓的前期绑定是指手工勾选添加引用文件,具体操作步骤是选择VBE菜单中的工具->引用,勾选“MicrosoftActiveX Data Object 2.8 Library”。而后期绑定是指用CreateObject函数来创建对象实例,如:Set Cnn=CreateObject("adodb.connection");
Set  rst=CreateObject("adodb.recordset")
优缺点:
前期绑定优点是绑定后,在编写代码时,自动列示该对象的方法和属性;能检查更多的代码语法错误和拼写错误;执行效率更快。缺点是不可更新COM类型库的版本。相反,后期绑定可以自动检测当前版本,自动创建。二者相互补充,选择哪种绑定,看各自需求。
①  期绑定法(实例)
Option Explicit
Sub 前期绑定()
Dim cnn As NewADODB.Connection
Dim rst As NewADODB.Recordset
Dim i As Integer
Dim sql As String
Columns("F:I").Clear
cnn.Open"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel12.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullName
sql = "select* from [Sheet1$] where 客户名称='韩正'"
Set rst =cnn.Execute(sql)
[F2].CopyFromRecordsetrst
For i = 0 Torst.Fields.Count - 1
    Cells(1, i + 6) = rst.Fields(i).Name
Next i
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:49 | 显示全部楼层
本帖最后由 ctp_119 于 2013-10-21 10:04 编辑
  1. ④        后期绑定
  2. Sub 后期绑定()
  3. Dim cnn As Object
  4. Dim rst As Object
  5. Dim sql As String
  6. Dim i As Integer
  7. Set cnn = CreateObject("adodb.connection")
  8. Columns("F:I").Clear
  9. cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullName
  10. sql = "select * from [Sheet1$] where 销量>50"
  11. Set rst = cnn.Execute(sql)
  12. [F2].CopyFromRecordset rst
  13. For i = 0 To rst.Fields.Count - 1
  14.     Cells(1, i + 6) = rst.Fields(i).Name
  15. Next i
  16. End Sub
复制代码

评分

参与人数 1鲜花 +1 收起 理由
gezizaifei + 1 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:50 | 显示全部楼层
本帖最后由 ctp_119 于 2013-10-18 13:53 编辑

    五、认识数据库及数据库要素
    1.什么是数据库(这里指的数据库是关系数据库)
    顾名思义,所谓的数据库就是存储数据的仓库。当然要按照一定的规则存储,以便于查找,添加,删除和修改数据等操作。数据库不仅仅包含数据本身,也包含数据内部是如何组织的,即数据库结构。
    2.数据库要素
    表是关系数据库的载体,存储着数据。表不仅存储数据,还包含表结构,即数据是如何组织的。
    表由行和列组成。每一行描述实体的一个实例;每一列描述实体的一个特征或属性。一行表示一条记录(元组),一列表示一个字段(列标题叫字段名)
    注:实体可视为对象,我个人认为概念差不多,不同的地方叫法不同。
主键,唯一能表示每一条记录字段或字段组合。主键有两条规则:其一,主键列不可以缺失(Null);其二,主键列不可重复。一句话,即不可以不填,也不可以重复填。
拓展:如果你会listview控件编程,我相信理解表和表的主键会更加深刻。

评分

参与人数 2财富 +50 鲜花 +1 技术 +2 收起 理由
songzi2000 + 1 优秀作品
zhaogang1960 + 50 + 2 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:52 | 显示全部楼层
本帖最后由 ctp_119 于 2013-10-18 13:54 编辑

六、简单的select查询语句
1.Select语句的语法格式
Select [All|Distinct] 字段列表 From 表名列表[JION 连接条件] [Where 查询条件][Group by 分组关键字列表] [Having 分组条件][Order by 排序关键字[ASC|DESC]
解析:
All 返回要查询的所有记录(默认值)
Distinct 返回查询过程中去掉所有重复值的记录
字段列表 当查询所有字段时可以用“*”号代替,当列举多个字段名时,字段名与字段名之间用半角字符“,”号分开。
JION 连接查询关键字,连接查询有自然连接,内连接,外左连接,外右连接,全连接(以后会一一讲解)
Group by 用于在聚合函数的查询分组(即分类汇总)
Having 用于向Group by查询子句的结果中添加查询条件
Order by 对查询结果进行排序
ASC 对查询结果升排序(默认值,可省略)
DESC 对查询结果降排序
注释:用[]括起来的都是可选的,可见只有Select子句和From子句是必须的;字母大小写无关紧要。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:53 | 显示全部楼层
本帖最后由 ctp_119 于 2013-11-7 13:28 编辑

    2.Select语句的执行步骤
    由于Select语句的子句比较多,明白Select语句的执行步骤对理解Select语句是十分必要的,有益的。
    (1)执行From子句,根据from子句中的一个或多个表创建工作表;
    (2)如果有Where子句,数据库管理系统将where子句中的查询条件作用于步骤(1)生成的工作表,数据库管理系统将筛选出那些满足条件的数据;
    (3)如果有Group by子句,数据库管理系统将步骤(2)生成的结果表进行分组(group by后面的字段进行分组),而后将其添加到新的结果表中;
    (4)如果有Having子句,数据库管理系统将having子句中的查询条件作用于步骤(3),筛选出那些满足条件的数据;
    (5)Select子句作用于结果表,过滤掉那些不在字段列表中的字段,如果select子句中有Distinct关键字,数据库管理系统将删除重复数据;
    (注意:请大家想想distinct通常情况下不可能和Group by一起使用,为什么?特殊情况除外。)
    (6)如果有order by子句,则按指定的排序规则对结果表进行排序。
    以上是Select语句执行过程,下面将用实例一一讲解select语句应用,以加深对select语句理解和运用。
(注:在以下讲解操作演示中,将以OLE DB操作SQL语句为基准,并省略了OLEDB的操作步骤,因为前面已经完整讲解过OLE DB操作过程,以防冗余。切记一定要自己动手操作,查看效果,认真思考,多提假设,实际操练,才能巩固和深刻理解)

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-10-18 13:54 | 显示全部楼层
本帖最后由 ctp_119 于 2013-10-18 14:18 编辑

    实例一:导入数据
    查询语句:select* from [北京$]
    解析:通配符“*”在这里表示所有字段,作用有三,其一,可以把隐藏的列全部显示出来;其二,忽略一些非法字段名;其三,简写作用。
    延伸①:select 品名,金额/ from [北京$]      这条语句得到的结论是什么?为什么会出现这样的结果?要怎么处理才能实现我们预想的结果?
    延伸②:如何引用表中的某一区域?
延伸③:如果单元格区域定名了名称,作为表的引用,又如何表示?
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-8-24 06:39 , Processed in 0.148434 second(s), 18 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表