Excel-VBA+ADO+SQL

Excel VBA ADO SQL 入门教程 001 认识 SQL In Excel 那就先说 SQL 吧 SQL 是一种结构化查询语言 Structured Query Language 是一种声明式语言 敲黑板 划重点 结构化和声明式 SQL 的核心是对表的引用 声明你想从数据源中获取什么样 的结果 而不用告诉计算机如何才能够得到结果 后面这句话似乎很难理解 举例来说 倘若我们需要获取上图所示表格 Sheet1 成绩大于等于80 分的人员名单 如果用命令式程序语言 比如 VBA 是这样的 Sub MyFind Dim arr brr iExtended Properties Excel 12 0 HDR yes IMEX 0 Data Source Extended Properties Excel 8 0 Data Source Extended Properties Excel 12 0 Data Source 间隔 例如 关 键字 1 值 1 关键字 2 值 2 关键字 3 值 3 另外 任何包含分 号 单引号或双引号的 值必须 用双引号引起来 由于在VBA 中连接 字符串的外层已经存在了一个双引号 因此通常使用英文单引号进行转 义 例如上例中的Extended Properties Excel 12 0 HDR yes IMEX 2 抄写时 千万别漏了英文单引号哦 3 3 星光俺掐指一算 算出相当一部分童鞋英语水平堪忧 想来拼写这 段英文连接字符串错漏百出是很有可能的 因此特呈上锦囊一份 参见 下图 别问我这图是哪来的 如果不几道 佛山无银脚 出门右拐重 看第一章吧 如果这锦囊您也不想用 其实收藏本帖 用到时打开帖子复制粘贴 相关代码就可以了 嘿嘿 木错 这才是最常用的一招 4 聊完了如何绑定ADO 以及建立与数据源的链接 最后说下如何使用ADO 执行 SQL 语句 别走开 喝杯水先我 咕咚咕咚 我们可以使用 ADO 的 Connection 对象或 Recordset Commannd 执行 SQL 语句 详细内 容我们放到 ADO 部分再讲 这里大家只需要先了解Connection 对象 的 Execute 方法就可以了 这是一个最常用的VBA ADO SQL 套路化查询代码 通常 我们只需 要修改 SQL 语言以及放置查询结果的单元格位置 Sub DoSql Execute1 Dim cnn As Object rst As Object Dim Mypath As String Str cnn As String Sql As String Dim i As Long Set cnn CreateObject adodb connection 以以上上是是第第一一步步 后后期期绑绑定定ADO Mypath ThisWorkbook FullName If Application Version 80 Sql 语句 查询 Sheet1 表成绩 大于 80 姓名和成绩的记录 Set rst cnn Execute Sql cnn Execute 执行 SQL 语句 始终得到一个新的记录集rst 以以上上是是第第三三步步 编编写写并并使使用用SQL 语语句句 d e ClearContents 清空 d e 区域的值 For i 0 To rst Fields Count 1 利用 fields 属性获取所有字段名 fields 包含了当 前记录 有关 的所有字段 fields count 得到字段的数量 由于 Fields Count 下标为 0 又从 0 开始遍历 因此总数 1 Cells 1 i 4 rst Fields i Name Next Range d2 CopyFromRecordset rst 使用单元格对象的CopyFromRecordset 方法将 rst 内容复制到 D2 单元格为左上角的单元格区域 以以上上是是第第四四步步 将将SQL 查查询询结结果果和和字字段段名名写写入入表表格格指指定定区区域域 cnn Close 关闭链接 Set cnn Nothing 释放内存 End Sub Excel VBA ADO SQL 入门教程 003 字段的查询 还是需要对一些名词做出解释 这些名词是SQL 语句释义中经常使用到的 所以请先有一个大概的了解 1 1 数数据据库库 数据库是按照数据结构来组织 储存和管理数据的仓库 例如最基础 的 ACCESS 软件等 通常情况下 我们将一个Excel 工作簿视为一个 数据库 是的 如您所知 Excel 并不是数据库 但这并不妨碍我们把 它看成一个数据库 进而使用SQL 语言的某些功能处理数据 这就 好比尽管男人不是女人 但一样可以织毛衣 做月嫂 不过 得承 认 生孩子之类的就确实太富有挑战性了 1 2 数数据据表表 数据表 或称之为表 是数据库最重要的组成部分之一 一个数据库由 一个或一组数据表组成 如果我们把这句话的 数据库 替换成 Excel 工作簿 就变成这样 表是Excel 工作簿最重要的组成部分之 一 一个 Excel 工作簿由一个或一组表组成 你看 同样也是成立的 因此 可以先将Excel 工作表视为数据库中的 表 至于异同 以后再聊 1 3 记记录录和和字字段段 如上图所示 和Excel 表格一样 数据库的表由行和列组成 只是不存 在行号和列标 相关值自然就不能使用A6 F4 之类的 Excel 单元格 地址来表述 表的每一行描述实体的一个实例 称之为记记录录 每一列 描述实体的一个特征或属性 称之为字字段段 列的标题称之为 字字段段名名或或 列列标标题题 例如上图中的 姓名 主键和外键 以后用到再说 没了 2 SQL 的数据查询和操纵语句包括 SELECT UPDATE INSERT DELETE 等 也就是所谓的查改增删 其中最重要 使用最频繁的是SELECT 查询语句 查询按照复杂性划 分 可以从一个简单的语句返回所有表中所有的记录 到用一个语句链 接多个表并定义多种搜索条件 现在 就让我们从最简单的部分走起吧 走你 少年 SELECT 语法如下 简化版 SELECT 字段名 FROM 表名 SELECT 关键字指明了要查询的字段名称 FROM 关键字指明了要获 取字段信息的表的名称 倘若数据源是 Excel 表格 需要在表名后增加美元符号 并用中括 号包起来 例如 Sheet1 另外需要说明的是 SQL 语言对关键字大小写不敏感 SELECT select Select 都可以使用 作用等同 基于书写规范化的 原因 可能有些老师会要求关键字统一大写 嗯 我不是老师 我还小 我的意见是新手 期怎么 喜欢怎么顺眼怎么来 你好就好 宠 溺脸 3 分享一下 SQL In Excel 常用字段查询语句以及常见问题的解决方法 如下图所示 是一份Excel 表格 表名为学生表 内容是一些看见星光 之类小学生的信息数据 3 1 单单个个字字段段查查询询 假设我们需要查询上图所示表格的姓名列数据 语句如下 SELECT 姓名 FROM 学生表 结果如下 3 2 多多个个字字段段查查询询 假设我们需要查询字段为姓名 性别 爱好的记录 语句如下 SELECT 姓名 性别 爱好 FROM 学生表 不同字段名之间使用英文逗号间隔 但最后一个字段名不用 另外需要 格外注意的是 英文逗号千万别写成中文逗号 当然 初学阶段 别说 千万 亿万 万也没办法阻止咱们错输中文逗号的情况发生 反复切 换输入法的中英文状态真的是很烦的一匹 上述语句查询结果如下 3 3 查查询询所所有有字字段段的的快快捷捷方方式式 如果我们需要提取所有字段的数据呢 一种方法是将所有字段名写入 SELECT 语句中 SELECT ID 姓名 性别 年龄 爱好 得分 FROM 学生表 另外一种方法是 使用星号 选取所有列 SELECT FROM 学生表 两者的区别在于 前者只选取指定字段的数据 后者包括了指定表的所 有数据 对于 Excel 表格而言 如果字段名确定 出于安全考虑 通常 使用前者更靠谱点儿 3 4 别别名名的的使使用用 如果我们需要更改查询结果中的字段名称 例如我不喜欢字段名 爱 好 我想把它改成 特长 可以使用关键字as as 是英文 alias 的 缩写 也就是 别名 的意思 语句如下 SELECT 姓名 爱好 AS 特长 FROM 学生表 3 5 特特殊殊字字段段名名的的处处理理 当字段名存在某些特殊字符 例如空格 或者当字段名存在SQL 语 言的保留字 例如AS 该字段名需要使用中括号括起来 避免因语法 识别混乱 系统产生错误信息的提示 举个例子 如上图所示 Excel 表名为调查表 该表字段名中有的存在 空格 姓 名 有的使用了SQL 的保留字 as 如果需要查询该表格 姓名和地址的信息 SQL 语句如下 select 姓 名 as from 调查表 查询结果如下 3 6 无无字字段段名名情情况况的的处处理理 之前讲过 Excel 并不是数据库 对数据结构并没有强制性要求 因为 这样的缘故 在使用SQL 处理数据时 难免会碰上各种有趣的现象 例如 数据表 纯数据 没有标题行 再或者 标题行存在合并单元格等 以至于字段名无法使用 此时 倘若需要获取字段数据 对字段名通常 使用 f 加序列号表示 引用表的第1 列字段名就是 f1 第 2 列字段名 是 f2 其余依次类推 如上图所示 该表格没有标题栏 如果我们需要查询姓名和性别 代码 如下 select f2 as 姓名 f3 as 性别 from 学生表 查询结果如下 砸桌子 还记得大明湖 畔使用 VBA ADO 执行 SQL 语句时 如果引用 表不存在标题栏 需要修改链接字符串的哪个参数的项吗 出门 右拐请重 看第 2 章吧 Excel VBA ADO SQL 入门教程 004 SQL 中的 Excel 表 1 上期我们聊了 SQL 常用查询语句中的字段查询 其简化版语法如下 SELECT 字段名 FROM 表名 当时我们说 FROM 子句指明了要获取字段信息的表的名称 倘若数据 源是 Excel 表格 则需要在表名后增加美元符号 并用中括号包起来 例如 Sheet1 事实上 上述例子是SQL In Excel 对工作表引用最简单的一种情况 也就是整表引用 此外还有单元格区域引用 跨工作簿引用表等 所以我们今天就再来 聊一下 SQL 语句中的 Excel 表 2 区区域域成成表表 Excel 工作表和数据库的数据表有很多不同之处 最显著的地方在于 数 据库的数据表是由行列构成的 而Excel 工作表则是由一个又一个单 元格构成的 且这些单元格拥有独特的地址表述方法 A1 和 R1C1 它们还可以构成数据相连的单元格区域 例如A2 H8 于是问题来了 如果我们只需要使用SQL 语言计算某张 Excel 工作 表的部分区域该怎么表述呢 这种问题是很常见的 比如 很多人的Excel 标题行并不是处于表格的第一行 而是第2 行 如下图 此时 我们希望SQL 可以使用 A2 F 列的单元格区域作为表 而不是 整个 Excel 工作表 这样我们更容易使用字段名处理数据 对吧 我转头看看书柜 书柜不说话 再比如 一张表里存在两个或更多个 表 这句话什么 意思呢 见下图 图中所示的表格中 既存在一份 教师表 又存在一份 学生表 如 果我们只希望 SQL 引用计算 A2 D8 的教师表数据 呃 请把刀放下 君子动手不动刀 好伐 Excel 中的 SQL 其 实是支持将工作表的单元格区域作为 表 使用的 上图所示的问题 SQL 可以写成 SELECT 姓姓名名 学学科科 FROM 数数据据表表 A2 D8 查询结果如下 而第 1 种情况 我们知道数据开始于A2 单元格 但不知道结束于F 列的哪个单元格 SQL 可以写成 SELECT 姓姓名名 爱爱好好 FROM 学学生生表表 A2 F 另外 如果我们需要SQL 引用计算表格 D G 整列的数据 SELECT FROM 学学生生表表 D G 总结以上几种 Excel 工作表区域的表述方式 也就是 工作表名称 美金符号 相对引用状态下的单元格地址 最后使用中括号包起来 本本节节小小贴贴士士 学生表 A2 F 我们说该语句可以引用从A2 至 F 列 最后存在数据的单元格区域 但这是有一个限制条件的 即非自连接状 态 所谓自连接是指SQL 应用于链接自身的工作簿 自链接状态下 A2 F 的表达方式最多是A2 F65536 行 倘若此时需要的引用 行超过 65536 行 请使用整表模式 3 跨跨工工作作簿簿的的表表 一个众所周知的问题是 Excel 函数在处理 跨工作薄数据 时很是疲态 除了个别几个查找引用类函数 例如VLOOKUP 绝大部分函数都 需要打开相关工作簿后才可以使用计算 是的 VLOOKUP 函数并不需要打开相关工作簿也可以跨工作薄 使用 而且在 VLOOKUP 公式书写完成后 即便你把它所引用的工作簿给删了 也不妨碍它计算 这是因为它已经把相关数据缓存到了公式所在的工作 簿中 不过 VLOOKUP 这种模式并不支持函数嵌套使用 打个响指 关于这一点 如果你感兴趣 我们改天单独聊一下 说回 SQL 我觉得我今天精神老是不集中 码个字也老是跑题 且 慢的像蜗牛 也许有人在想我 也许有人在骂我 不管了 我先去刷 会微博 哦 对了 星光俺 的微博是 EXCELers 没事记得关注 下 说回 SQL 我们之前分享的SQL 语句都是处理当前工 作簿的表格 如果我们所需要处理的数据位于其它工作簿时 SQL 该怎么表述呢 例如 获取位于计算机D 盘盘的的 EH 小小学学 文文件件夹夹下下的的 学学生生表表 xlsx 工工 作作簿簿中中的的 成成绩绩表表 的所有数据 一口气读完这话的 送你一脸坏笑 暴击点 赞 如果是 OLE DB 法 该方法参考第1 章 使用 SQL 语句如下 SELECT FROM D EH 小学 学生表 xlsx 成绩表 FROM 后的指定表字符串有两个部分构成 第一个中括号内是指定工作 簿的存放路径 带后缀的完整工作簿名称 后一个中括号内是工作表名 称 两个中括号之间使用英文句号 相连 如果是通过 VBA ADO 使用 SQL 语句 敲敲书书柜柜前前方方预预警警 VBA 基基础础差差的的童童鞋鞋请请自自行行跳跳过过以以下下内内容容 相比于 OLE DB 法 VBA ADO 的方法要灵活的多 它可以使用 ADO 直接创建并打开和指定工作簿的链接 因此SQL 语句便无需再 指定工作簿完整名称等 代码参考如下 Sub ADO Sql 适用于除 2003 版以外的高版本Excel Dim cnn As Object rst As Object Dim Mypath As String Str cnn As String Sql As String Dim i As Long Set cnn CreateObject adodb connection Mypath D EH 小学 学生表 xlsx 指定工作簿 Str cnn Provider Microsoft ACE OLEDB 12 0 Extended Properties Excel 12 0 Data Source Extended Properties Excel 12 0 Data Source DATABASE D EH 小小 学学 学学生生表表 xlsm 成成绩绩表表 Sql 语句 查询成绩表的所有数据 Set rst cnn Execute Sql 执行 SQL Cells ClearContents For i 0 To rst Fields Count 1 Cells 1 i 1 rst Fields i Name Next Range a2 CopyFromRecordset rst cnn Close Set cnn Nothing End Sub 代码详细注释及适用于03 16 版本 Excel 的 VBA 代码模 版请参考 第 2 章 回复关键词SQL 可以获取过往系列文章 代码中的 SQL 语句是 SELECT FROM Excel 12 0 DATABASE D EH 小小学学 学学生生表表 xlsx 成成绩绩表表 FROM 指定表的字符串有两部分组成 第一个中括号中 Excel 12 0 是目标工作簿的版本号 第2 章时我们讲过 Excel 12 0 适用于除了 2003 以外的所有 Excel 版本 DATABASE 指定的是数据源工作簿的 路径和名称 第2 个中括号内是工作表名 两个中括号之间使用英文句 号相连 看起来似乎 VBA ADO 方法的 SQL 语句比 OLE DB 法更复杂 确实 如此 不过前者的功能也更强大 比如 它可以通过VBA 对象的属 性和方法及循环 判断语句等 有条件的筛选工作簿和工作表等 相比之下 OLE DB 中的 SQL 语句就是纯手工原始模式了 当然 更 重要的是 前者不但可以查数据 还可以增改删数据 后者却只限于 查 Excel VBA ADO SQL 入门教程 005 SQL 之字段去 重 1 今天周末 明天 是苦逼的 周一 所以我们今就聊点简单的 就一个 单词 DISTINCT 2 在数据分析处理过程中 我们经常需要去除查询结果中的重复值 保留 截然不同的唯一值 对于SQL 这类问题 我们通常使用DISTINCT 关键字处理 其语法如下 SELECT DISTINCT 字段名 FROM 表名 举个简单栗子 如上图所示 Excel 工作表的表名为 学生表 A 列是姓名 B 列是学 科 其中 A 列的姓名存在重复 比如 看见星光 现在需要使用SQL 语句提取不重复的学生名单 代码如下 SELECT DISTINCT 姓姓名名 FROM 学学生生表表 查询结果如下 从查询结果可以看出 重复的人名已被剔除 但数据排列顺序也和数据 源不同了 事实上 系统的操作过程是先对查询结果排序 然后从中剔 除重复值 在 Excel 中 当查询结果是非中文时 比如数字和字母 DISTINCT 处理后的结果明显为升序排列 当查询结果是中文呢 其实 中文也是按升序排列的 只是排序规则不是我们平常所熟知的拼音或 笔划顺序 如果需要去重的数据是多列 在DISTINCT 关键字后指定多个字段名 彼此间使用英文逗号间隔即可 依然使用上述例子 查询去除姓名和学科同时重复的值 语句如下 SELECT DISTINCT 姓姓名名 学学科科 FROM 学生表 查询结果 从查询结果可以看出 只有姓名和学科同时都重复的 看见星光 文言 文研究 才被删除了 3 今天的分享系不系很简单呢 但需要说明的是DISTINCT 可以处理的字符串最大长度为255 当字 符串长度超过 255 时 会造成计算错误 同时查询结果也只返回255 长度的记录 Excel VBA ADO SQL 入门教程 006 排序的应用 本章主要内容 1 常规排序 2 自定义排序 3 排序的扩展应用 提取前n 大或后 n 小的记录 1 排序是我们使用Excel 处理数据经常面对的问题 Excel 甚至专门 对此内置了 排序 功能 今天 我们就来 聊一下 SQL 如何对查询结 果进行排序操作 也就是ORDER BY 语句 其语法如下 SELECT 字字段段名名 FROM 表表名名 ORDEY BY 字字段段名名 该语句默认对记录进行升序排序 如果需要降序排序 可以使用关键字 DESC SELECT 字字段段名名 FROM 表表名名 ORDEY BY 字字段段名名 DESC 照例举个小例子 如下图所示 工作表名称为 销售表 A 列是月份 B 列是产品 C 列是生产量 D 列是销售量 数据纯属虚拟 如有雷同 实属雷人 如果我们需要查询该表月份和销售量两个字段的数据 并将销售量作升 序排列 代码如下 SELECT 月份 销售量 FROM 销售表 ORDER BY 销售量 如果我们需要查询月份 生产量 销售量三个字段的数据 其中生生产产 量量优优先先排排序序 且降序排列 销售量升序排列 代码如下 SELECT 月份 生产量 销售量 FROM 销售表 ORDER BY 生生产产量量 DESC 销销售售量量 ASC 也就是说 ORDER BY 语句中 优先排序的字段放在前面 不同字段 可以指定不同的排序规则 如果没有指定排序规则 则默认为升序 ASC 排列 代码结果如下 2 依然使用上一节的例子 倘若我们需要对查询结果按月份进行降序排列 可能有些朋友代码会写成如下这般 SELECT 月份 销售量 FROM 销售表 ORDER BY 月份 DESC 但发现结果和我们想的并不一样 月份的排列看起来完全 是乱态的 既不是升序也不是降序 上一章我们提过 SQL In Excel 对简体中文排序的规则不走寻常路 既不按拼音字母排序 也不按笔画排序 而是可能和其它数据库一样 采用的 Chinese PRC 针对大陆简体字UNICODE 的排序规则 但和 其它数据库所不同的是 我们没有修改该规则参数的权限 以达到拼音 或笔划排序的目的 不过 事实上 即便是使用Excel 自带的排序功能 默认拼音字母排序 排序结果也并非是 五四三二一 月 此时我们需要自定义排序规则 也就是使用SQL 中的 iif 或者 instr 函数 IF 函数类似于工作表的IF 函数 语法如下 IIF 条条件件表表达达式式 真真值值结结果果 假假值值结结果果 使用 IIF 自定义排序规则的SQL 语句如下 SELECT 月份 销售量 FROM 销售表 ORDER BY IIF 月月份份 五五月月 1 IIF 月月份份 四四月月 2 IIF 月月份份 三三月月 3 IIF 月月份份 二二月月 4 IIF 月月份份 一一月月 5 代码长的吓人 但意思其实很简单 如果月份等于五月 就返回1 否则如果月份等于四月 就返回 2 再否则如果月份等于三月 以此类推 最后 ORDER BY 语句 按 IIF 返回的结果进行升序排序 看了这个代码 是不是瞬间找回当年嵌套N 层 IF 函数的 青葱小 岁 月 再说下 INSTR 函数 INSTR 函数有些类似于工作表函数FIND 查找一个字符串在另一个字 符串中的位置 和FIND 不同的是 当找不到相关值时 结果返回 0 而非错误值 INSTR str substr 返返回回 substr 在在 str 中中的的位位置置 若若不不存存在在 则则 返返回回 0 使用 INSTR 函数自定义排序规则的SQL 语句如下 SELECT 月份 销售量 FROM 销售表 ORDER BY INSTR 五月 四 月 三月 二月 一月 月份 查询结果如下 INSTR 函数的处理语句比起IIF 函数来明显要简洁清爽的多 因因此此我我们们通通常常使使用用该该函函数数处处理理自自定定义义排排序序的的问问题题 3 提一个问题 如下 如果使用 SQL 语句查询上述示例中 销售量前三名的月份和销售量 该 怎么表述呢 不要想的太复杂 其实很简单 在原本对销量进行降序排序的结果上 提取前三行的记录就OK 如何提取前 3 行记录呢 可以使用关键词TOP TOP n 指指定定检检索索结结果果集集中中的的第第n 行行前前的的记记录录 完整 SQL 代码如下 SELECT TOP 3 月份 销售量 FROM 销售表 ORDER BY 销售量 DESC 查询结果如下 那么如果查询销售量后二名的月份和销量呢 4 我们今天分享了 一个语句 ORDER BY 一个关键字 TOP 两个函数 IIF INSTR 耸肩 摊手 你看 相比于函数 SQL 语言在处理去重复 排序 自定义排序 提取前n 名数据 的问题上 是不是简单太多了 不要困在 Excel 函数那一个小天地里 它的性能决定了它的功能很有限 更不要迷恋数组公式 除了预防老年痴呆它别无用处 只学一点 VBA 循环 或学一点SQL SELECT 子句 处理实际 问题的能力也强过n 多所谓 的函数高手 Excel VBA ADO SQL 入门教程 007 条件查询 上 主要内容 单条件查询 多条件查询 模糊匹配查询 1 Where Where 我们上期聊了 SQL 排序 这期来聊筛选 或者说条件查询 在第一期的时候 我们说SQL 是声明式语言 当时还举了个小例子 不知道您是否还有印象 那个栗子中的 SQL 代码我们使用了where 子句 它定义了筛选条件 成绩 80 从而对 FROM 子句返回的结果集进行筛选 将不符合条件 的剔除 只保留符合条件的记录 事实上 where 子句的筛选条件可 以是简单的单条件 如上述示例 也可以是复杂的多条件 可以精确 匹配筛选 也可以搭配通配符等进行模糊匹配筛选 SELECT 字字段段名名 FROM 表表名名 WHERE 筛筛选选条条件件 这是它的语法 下面我们就通过几个栗子来了解下where 子句的常用套路 2 一一个个栗栗子子 如下图所示 是一份名为学生表的Excel 工作表 A 列是班级 B 列 是姓名 C E 列分别是性别 年龄 爱好 3 单单条条件件查查询询 倘若需要查询上述示例中班级为 插班生 的学生名单 SQL 代码如下 SELECT 班班级级 姓姓名名 FROM 学学生生表表 WHERE 班班级级 插插班班生生 查询结果如下 班级 插班生 就是 where 子句的筛选条件 它使用了等号来判断班级字 段值和条件值是否相等 需要说明的是 和Excel 一样 当条件值是 文本时 应该使用引号包起来 数数值值则则不不用用 例如 SELECT 班级 姓名 FROM 学生表 WHERE 年年龄龄 14 昨个公众号后台有朋友问了个和下面的问题类似的问题 为什么班级 插班生 使用的是单引号 而不是双引号 这个问题我们之前有解释过 这里再说一下 在OLE DB 法使用的 SQL 语句里 单双引号都可以 只是在VBA 中 通常 SQL 语句本身 就是作为字符串存在的 外围已经存在了一对双引号 因此SQL 语 句内一般就使用单引号 4 多多条条件件查查询询 先先说说一一下下 与与 关关系系 也也就就是是并并且且关关系系 的的多多条条件件筛筛选选 和 Excel 一样 在 SQL 中 这类问题通常使用AND 运算符 比如 我们需要查找班级为插班生 同时年龄大于等于12 岁的学员 名单 SQL 代码如下 SELECT 班级 姓名 年龄 FROM 学生表 WHERE 班班级级 插插班班生生 AND 年年龄龄 12 如语句所示 不同条件之间使用AND 运算符相连 只有当每一个条件 都成立时 才符合WHERE 子句的筛选条件 查询结果如下 再再说说一一下下 或或 关关系系的的多多条条件件筛筛选选 也也就就是是只只要要符符合合指指定定筛筛选选条条件件中中的的任任 一一条条即即 OK 的的 where 筛筛选选子子句句 在 SQL 中 这类问题一般使用OR 运算符处理 比如 需要查找班级是插班生或或者者年龄大于等于 12 岁的学员名单 SQL 代码如下 SELECT 班级 姓名 年龄 FROM 学生表 WHERE 班班级级 插插班班生生 OR 年年龄龄 12 查询结果如下 当 或 关系的查询条件较少时 我们使用OR 运算符 但当查询条件较 多时 OR 运算符的书写便变得臃肿烦琐 此时我们可以使用其它运算 符 例如 BETWEEN 和 IN IN 运算符可以指定一个到多个值 每个值之间使用英文逗号间隔 最后 以括号 包括起来 当查询值和括号中的任一值匹配时 则结果即为 True 其语法如下 Text expression IN First value last value 举个例子 倘若我们需要查询姓名为 看见星光 老祝 美女空 大红花四个人的 班级和爱好数据 SQL 语句如下 SELECT 班级 姓名 爱好 FROM 学生表 WHERE 姓名 IN 看看见见 星星光光 老老祝祝 美美女女空空 大大红红花花 查询结果如下 between and 语句可以选取介于两个值范围之间的数据 这些值可 以是数值 日期和文本 中文还是慎用吧 例如我们需要查询年 龄在 13 16 岁之间的学员名单 SQL 代码如下 SELECT 班级 姓名 年龄 FROM 学生表 WHERE 年年龄龄 BETWEEN 13 AND 16 而反过来 如果我们需要查询年龄不不在在 13 16 岁之间的学员名单 可 以使用 NOT 运算符搭配 between 代码如下 SELECT 班级 姓名 年龄 FROM 学生表 WHERE 年龄 NOT BETWEEN 13 AND 16 5 模模糊糊匹匹配配查查询询 1 上节我们分享了Where 子句的单条件和多条件查询 今天就把当时未 讲的模糊匹配查询说一下 我们知道 在 Excel 中有两个很重要的通配符 星号 和问号 在字符串中 星号可以代替0 到多个字符 而问号 一个 则 只能代表一个字符 在SQL in Excel 中也有两个与之类似的通配符 一个是 类似星号 可代替任意数量字符 一个是 类似问号 一 个 只能代替一个字符 在 SQL 查询语句中使用通配符 必须借助LIKE 运算符 举个例子 依然使用上一节的荔枝 倘若我们需要获取姓名中包含 光 字的学生名单 SQL 代码如下 SELECT 姓名 FROM 学生表 WHERE 姓姓名名 LIKE 光光 倘若我们需要获取姓名长度为2 个字符的学生名单 SQL 代码如下 SELECT 姓名 FROM 学生表 WHERE 姓姓名名 LIKE 倘若我们需要获取姓名以 美女 开头 啧啧 好自恋的人啊 居然以美 女为姓 此处必须手动 芬子 空空 同时年龄小于18 岁的学生 名单 SQL 代码如下 SELECT 姓名 年龄 FROM 学生表 WHERE 姓姓名名 LIKE 美美女女 AND 年年龄龄 18 当 Excel 单元格存在星号 而我们又需要批量查找或替换星号时 通 常使用 进行强制转义 那么在 SQL 中 又使用什么符号可以取消 和 的通配符能力 归为 普通字符呢 不知你是否还记得当字段名出现特殊字符 例如空格 时 我们是怎么 处理的 使用中括号 将字段名包括起来 这里同样如此 还是举个栗子 如上图所示 是一份名为学生表的Excel 工作表 倘若我们需要查询 备注 字段包含特殊字符 的学生名单 代码如下 SELECT 姓名 备注 FROM 学生表 WHERE 备备注注 LIKE 小小贴贴士士 在 SQL IN Excel 中 不支持使用通配符 和 只能使用通配 符 和 但在 ACCESS 数据库中 不支持使用通配符 和 只支持 和 2 举一个对新手而言可能稍微复杂的VBA ADO SQL 的实例 示例文 件可以点击文末的 阅读原文 下载 该实例在工作中是较为常见也较为实用的 在一个工作簿里 有两个工作表 一个是学生表 一个是查询表 上图是学生表 记录了学生信息的明细 上图是查询表 第一行是标题栏 有四个字段名 分别是班级 姓名 性别 爱好 要要求求 在查询表字段名对应的第二行的单元格输入关键值后 点击 查询 按钮 从 学生表 获取符合查询条件的学生信息 效效果果动动画画示示意意 VBA 代码如下 Sub SqlFindData Dim cnn As Object rst As Object Dim Mypath As String Str cnn As String Sql As String Dim i As Long j As Long Set cnn CreateObject adodb connection Mypath ThisWorkbook FullName If Application Version 60 更多关于 WHERE 子句的应用 单条件 多条件 模糊条件等 参考前文 Excel VBA ADO SQL 入门教程 007 条件查询 上 Excel VBA ADO SQL 入门教程 008 条件查询 下 这里需要说明的是逻辑值 TRUE 和 FALSE 在 SQL 中的一种应用 例如 以下语句可以计算三科成绩均及格的人数 SELECT COUNT AS 三科均及格人数 FROM 成绩表 WHERE 语文 60 AND 数学 60 AND 英语 60 但上述 SQL 语句也可以写成 SELECT COUNT AS 三科均及格人数 FROM 成绩表 WHERE 语文 60 数学 60 英语 60 3 负负 3 是是什什么么鬼鬼 我们知道在 EXCEL 中 当进行数学运算时 逻辑值TRUE 等于 1 FALSE 等于 0 但在 SQL In Excel 中并非如此 在 SQL 中 逻辑值 FALSE 依然等于 0 但 TRUE 则等于 1 当三个 条件均为真时 也就是 1 1 1 其结果必然等于 3 以此即可判断 三条件是否均成立 知道这个知识点有什么用处 例如下面这个问题 计计算算三三科科成成绩绩中中至至少少有有两两科科及及格格的的人人数数 使用逻辑值运算技巧 如下即可 SELECT COUNT AS 至少两科及格人数 FROM 成绩表 WHERE 语文 60 数学 60 英语 60 60 数学 60 英语 60 250 需要注意的是 HAVING SUM 成绩 250 不能写成 HAVING 总分 250 至于缘故 我们下期会讲到 HAVING 子句通常都是搭配GROUP BY 分组语句出现的 用于在后者 分组的基础上搜索相关条件 但这并不是说它只能依靠GROUP BY 才能生存 它也可以单独存在 例如以下语句计算了 插班生 的总分数 SELECT SUM 成绩 AS 总分 FROM 成绩表 HAVING 班级 插 班生 当然 尽管 HAVING 可以单独生存 但在实际情况中却很少见 一方面 它单独出现时 能力非常有限 另一方面 我们还有WHERE 子句 打打个个响响指指 问问题题来来了了 同样都是用于条件筛选 既生瑜何生亮 HAVING 子句和 WHERE 子 句有什么相同和不同之处 相同之处在于 两则都可以定义搜索条件 比如 前面讲的查询各个班级学学科科为为语语文文 的总分 既可以先分组后 HAVING 筛选 也可以先WHERE 筛选后分组 SELECT 班级 学科 SUM 成绩 AS 总分 FROM 成绩表 GROUP BY 班级 学科 HAVING 学学科科 语语文文 也也可可以以写写成成 SELECT 班级 学科 SUM 成绩 AS 总分 FROM 成绩表 WHERE 学学科科 语语文文 GROUP BY 班级 学科 此外 前面章节所介绍的WHERE 子句的所有操作技巧 比如使用连 接符 通配符 函数等 HAVING 函数也都可以使用 相关技巧参考 Excel VBA ADO SQL 入门教程 007 条件查询 上 Excel VBA ADO SQL 入门教程 008 条件查询 下 不同之处 首先 运算顺序不同 WHERE 优先于 HAVING 其次 作用对象不同 WHERE 只作用于表 而HAVING 作用于 GROUP BY 子句的分组结果 如果不存在GROUP BY 子句 则作用 于 WHERE 子句的搜索结果 如果WHERE 子句也不存在 则直接作 用于表 最后 最重要的是 计算对象不同 HAVING 是用于组 的计算 WHERE 则计算指定字段的每条记录 例如我们需要查询成绩大于90 分的学员名单 语句可以写成 SELECT 班级 姓名 学科 成绩 FROM 成绩表 WHERE 成成绩绩 90 但不能写成 SELECT 班级 姓名 学科 成绩 FROM 成绩表 HAVING 成成绩绩 90 后者会出现错误提示 同样的道理 我们要查询班级总分大于250 的班级名单 语句可以写成 SELECT 班级 SUM 成绩 AS 总分 FROM 成绩表 GROUP BY 班级 HAVING SUM 成成绩绩 250 但不能写成 SELECT 班级 SUM 成绩 AS 总分 FROM 成绩表 WHERE SUM 成成绩绩 250 GROUP BY 班级 后者会出现错误提示 ExcelExcel VBA ADO SQLVBA ADO SQL 入门教程入门教程 011011 SELECTSELECT 语法结构和运算顺序语法结构和运算顺序 1 听说鲁迅先生说过 学习一门语言 英语也好 计算机语言也罢 重要 的是掌握它的结构 对于计算机语言而言 所谓结构 当然不只是指语 法结构 也包含了数据结构 思维和运算结构等 不过 对于初学 者而言 紧要的是先掌握语法结构 第 3 章的时候 Excel VBA ADO SQL 入门教程 003 字段查询 我 们说过 SELECT 语句超 简版的语法如下 SELECT 字字段段名名 FROM 表表名名 经过这一段时间的分享 我们又陆续学习了WHERE GROUP BY HAVING ORDER BY 等子句 因此 我们今就来说下SELECT 语句完整的语法结构 如下 SELECT 字字段段名名 FROM 表表名名 WHERE 条条件件筛筛选选 GROUP BY 分分组组 HAVING 分分组组筛筛选选 ORDER BY 排排序序 被中括号包括的 都是可选的 2 了解了 SELECT 完整的语法结构 下一个问题是 它的运算顺序是怎 么样的 我们接触到的语言 有些是按语句的先后顺序运算的 比如VBA 有 些不是 比如 Excel 函数 多层 Excel 函数是由里到外的运算顺序 那 么 SQL 呢 一个完整的 SELECT 语句运算顺序如下 1 首先运算的是 FROM 子句 根据 FROM 子句中指定的一个或多个表创建工作表 2 如果存在 WHERE 子句 则 WHERE 子句对步骤 1 获得的工 作表进行条件筛选 删除不符合条件的记录 3 如果存在 GROUP BY 子句 则对步骤2 生成的结果表按指定 字段进行分组 生成一份新的结果表 4 如果存在 HAVING 子句 则对步骤3 的结果表按指定条件进 行筛选 删除掉不满足筛选条件的记录 5 此时 步骤4 生成的结果表 如果有字段名称不包含在 SELECT 字字段段名名 所指定的字段名中 则不取 另外 如果SELECT 子句中包含关键字DISTINCT 则执行去重复行 6 最后 如果有ORDER BY 子句 则按指定的排序规则对结果表 进行排序操作 你看 SQL 的运算顺序 既不是语句的先后顺序 也不是由内到外的顺 序 比如 尽管ORDER BY 的运算顺序处于最后 但最先出现的 SELECT 字字段段名名 并非首先运算的 它的运算顺序处在HAVING 子句 之后和 ORDER BY 子句之前 3 举一个小栗子 有一条 SQL 语句 如下 SELECT 商品购买日期 AS 日期 姓名 FROM 数据表 WHERE 日日期期 2018 1 10 语句的意思是查询数据表中 商品购买日期 的值大于 2018 1 10 的日 期和姓名两个字段的数据 它使用了别名 将原字段名 商品购买日期 重命名为 日期 商商品品购购买买日日期期 AS 日日期期 该语句运算后 系统会发出以下错误提示 问题出在哪儿哩 WHERE 日期 2018 1 10 WHERE 子句里的 日期 是什么呢 数据表内并没有日期这个字段名呀 有朋友会想 笨蛋 日期是我作的别名呀 商品购买日期 AS 日 期 别名是没错的 但问题在于 SELECT 语句的运算顺序 WHERE 子 句是先于 SELECT 字字段段名名 部分的 也就是说当WHERE 子句运算时 别名还没起呢 Excel VBA ADO SQL 入门教程 012 UNION 多表数据合并 1 SQL 的 UNION 运算符可以合并两个或多个的SELECT 结果集 因 此 我们通常使用该运算符来合并多表数据 例如 在一个工作簿中存在两个表 一个表名为 一班 另一个表名 为 二班 现在我们需要将两个表的数据合并成一张表 SQL 语句如下 SELECT FROM 一班 UNION SELECT FROM 二班 结果如下 需要说明的是 UNION 运算符会对结果集去重复 只保留唯一值 前 面讲去重复的时候提过 SQL 去重复的过程 是先对记录排序 后再 去重复 因此 UNION 的的运运算算结结果果通通常常是是默默认认升升序序排排列列的的不不重重复复记记录录 例如 汇总一班和二班两个班级的学生名单 SELECT 姓名 FROM 一班 UNION SELECT 姓名 FROM 二班 结果如下 由于使用了 UNION 运算符 一 二班都存在的 看见星光 只保留了 一个 如如果果不不需需要要去去重重复复的的操操作作 可可以以使使用用关关键键字字ALL 也也就就是是 UNION ALL 同样汇总一班和二班两个班级