跟我学SQL in Excel

ctp_119

附件中word文档2个，应文件过大，只能分开压缩上传了，一个是辅助excel文件。

跟我学SQLin Excel

    当我们接触Excel越来越密切，数据量越来越大，函数和公式越来越复杂的时候，你是否感觉到Excel处理数据越来越吃力，运行速度越来越慢，数据处理效率越来越低呢？如果你感觉到了，那么恭喜你，你Excel技能又得提升了，呵呵，那就请你跟着我，慢慢的学习SQL在Excel中的基本应用吧。

    通过本帖你可以初步了解数据库的基本结构和要素，简单的SQL语句，Excel中如何应用SQL语句以及一些运用SQL实例。

    一、什么是SQL？

    简单说，SQL是标准的结构化数据库查询语言。

    注意：所谓标准指的是应用多种数据库且广为应用。结构化，指的是格式固定(这点非常重要，深刻理解),其后会不断强化对结构化的理解。

    二、为什么要学习SQL？

    在Excel中应用SQL，就是把excel当作数据库来处理，运用SQL语句的强大功能来处理大量的Excel数据，以便解决Excel处理大量数据效率低下的瓶颈，或用Excel作为前台数据处理，数据库(为了更好的兼容，这里的数据库通指Access数据库)作为后台数据存储仓库，联合一起应用，将会使Excel和SQL发挥的淋漓尽致，至善至美。

    三、要怎么学习SQL？

    上面已经说过了，SQL是结构化的数据库查询语言，所谓结构化，就像电脑硬件一样，要什么功能，在相应的接口上插上相应的硬件，系统自动安装驱动或自行安装驱动之后即可使用。如：U盘，摄像头等。SQL也一样，要分类汇总用group by子句，要排序用orderby子句，要唯一用关键字Distinct，要条件筛选用Where子句等等都是结构化的，SQL语句写好之后，也要相应的驱动，如ADO,RDO,DAO等等。总而言之，学习SQL就像搭积木，需要什么配什么。

跟我学SQL in Excel.zip (44.19 KB, 下载次数: 4535)

2013-10-18 14:00 上传

点击文件名下载附件

       跟我学SQL in Excel2.zip (1.68 MB, 下载次数: 6586)

2013-10-18 14:09 上传

点击文件名下载附件

          跟我学SQL in Excel3.zip (1.25 MB, 下载次数: 6443)

2013-10-18 14:13 上传

点击文件名下载附件

ctp_119

    四、在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

                        后期绑定法                        

ctp_119

ctp_119

ctp_119

所谓的前期绑定是指手工勾选添加引用文件，具体操作步骤是选择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

ctp_119

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
    

复制代码

ctp_119

    五、认识数据库及数据库要素

    1.什么是数据库(这里指的数据库是关系数据库)？

    顾名思义，所谓的数据库就是存储数据的仓库。当然要按照一定的规则存储，以便于查找，添加，删除和修改数据等操作。数据库不仅仅包含数据本身，也包含数据内部是如何组织的，即数据库结构。

    2.数据库要素

    表是关系数据库的载体，存储着数据。表不仅存储数据，还包含表结构，即数据是如何组织的。

    表由行和列组成。每一行描述实体的一个实例；每一列描述实体的一个特征或属性。一行表示一条记录(元组)，一列表示一个字段(列标题叫字段名)。

    注：实体可视为对象，我个人认为概念差不多，不同的地方叫法不同。

主键，唯一能表示每一条记录字段或字段组合。主键有两条规则：其一，主键列不可以缺失(Null)；其二，主键列不可重复。一句话，即不可以不填，也不可以重复填。

拓展：如果你会listview控件编程，我相信理解表和表的主键会更加深刻。

ctp_119

六、简单的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子句是必须的；字母大小写无关紧要。

ctp_119

    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操作过程，以防冗余。切记一定要自己动手操作，查看效果，认真思考，多提假设，实际操练，才能巩固和深刻理解)

ctp_119

    实例一：导入数据

    查询语句：select* from [北京$]

    解析：通配符“*”在这里表示所有字段，作用有三，其一，可以把隐藏的列全部显示出来；其二，忽略一些非法字段名；其三，简写作用。

    延伸①：select 品名,金额/元 from [北京$]      这条语句得到的结论是什么？为什么会出现这样的结果？要怎么处理才能实现我们预想的结果?

    延伸②：如何引用表中的某一区域？

延伸③：如果单元格区域定名了名称，作为表的引用，又如何表示？