我一直觉得做ADO编程的人如果SQL底子不牢后面每写一个功能都会觉得哪儿哪儿都不顺。看代码、调连接、改参数最后发现错的全是SQL语句本身这种滋味我太熟悉了。这篇“SQL语句与ADO编程学习总结”系列的第一篇我就专门把SQL语句这块掰开揉碎聊一遍。我在刚开始做数据库相关开发的时候接过一个在页面上拉取报表的小活。数据库连得通Connection也写得没错结果查询出来的数据和我预期差了一大截。折腾了大半天最后发现不是ADO的问题是SQL语句写得有问题——日期条件匹配不上、查出来一堆多余字段、统计结果还有重复项。那时候我才真正意识到ADO编程的地基根本不在ADO而在SQL。这篇总结就围绕SQL语句本身来讲适合三类人刚学ADO但SQL比较薄弱的写程序时经常写SQL但从来没系统整理过的还有准备系统复习一遍数据库基础的人。1. 为什么ADO编程必须从SQL语句开始1.1 从一次单表查询翻车说起先讲个具体的事。当时我手里有一张订单表结构大概是订单编号、客户ID、下单时间、订单金额。界面上的需求很简单默认显示当天的订单用户可以选日期范围再查。我第一次写的查询条件是这样的SELECT * FROM 订单 WHERE 下单日期 2023-05-20结果查出来的记录要么是空的要么偶尔漏掉几单。原因一句话就能说透这张表的下单时间字段是DATETIME类型真实存储值是2023-05-20 14:32:10拿一个纯日期去等值匹配怎么可能完全相等后来改成范围查询问题才解决SELECT * FROM 订单 WHERE 下单时间 2023-05-20 00:00:00 AND 下单时间 2023-05-21 00:00:00这件事让我印象特别深。它和ADO一点关系都没有纯粹是SQL的时间条件写法问题。你ADO对象用得再熟练SQL语句一错前面全是无用功。1.2 ADO开发里SQL语句到底扮演什么角色先理清楚ADO开发的基本链路。在传统ADO编程里我们通常会用到三个核心对象Connection负责建立数据库连接Command负责执行命令Recordset负责接收查询结果集。那SQL语句在哪一层在Command.CommandText里在用Connection.Execute传进去的字符串里在Recordset.Open的Source里。Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset conn.Open ProviderMicrosoft.Jet.OLEDB.4.0;Data SourceC:\test.mdb 这里的SQL语句就是整个操作的核心 rs.Open SELECT 客户ID, SUM(金额) AS 总金额 FROM 订单 GROUP BY 客户ID, conn可以这么理解ADO是管道管道铺得再好里面流的还是SQL。SQL写得好不好直接决定功能好不好用、数据查询快不快、代码稳不稳定。特别是后续涉及统计报表、分页查询、多表关联的时候SQL语句的水平高低一眼就能看出来。1.3 这个系列的学习顺序是怎么安排的这个系列我计划分成几篇第一篇就是本篇集中讲SQL语句本身从分类、查询、增删改、表结构到常见坑后续篇再讲ADO对象模型、参数化查询、事务控制、Recordset的进阶用法和实战项目。之所以把SQL语句放在系列第一篇是因为它是数据操作的最小单位。你在ADO里无论做什么操作最终都要翻译成一条SQL。先把SQL学扎实后面学ADO的对象、属性、方法时就能把精力放在“怎样调用ADO执行SQL”上而不是一边学ADO一边补SQL。效果完全不一样。2. 先把SQL语句的家族谱理顺2.1 三类核心语句的边界感很多人一上来就写SELECT写了很久对SQL的全貌还是模糊的。我建议先建立一张全景图不然学到后面容易混。类别全称代表语句作用ADO中的典型使用方式DDL数据定义语言CREATE TABLE、ALTER TABLE、DROP TABLE定义和管理表、索引等结构Connection.Execute执行建表语句DML数据操纵语言INSERT、UPDATE、DELETE对表内数据进行增删改Command配合参数执行DQL数据查询语言SELECT查询数据Recordset.Open或Command.Execute返回结果集还有一类DCL数据控制语言比如GRANT、REVOKE主要是管理用户权限的在传统单机版MDB或小规模数据库应用的开发里几乎用不到大多由DBA负责所以初学阶段可以先忽略。这么分类有什么好处我看很多新手写SQL遇到“建表”“改表”时会觉得很陌生甚至完全不知道有这回事。实际开发里程序刚起步时可能要动态建临时表项目上线后要加字段、加索引。这些都是DDL的工作。分清楚三类语句至少你看到问题能判断出用什么类型的SQL。2.2 实际项目里每类语句的使用频率以我这些年经手的进销存类、后台管理类的项目为例使用频率大致是这个比例SELECT系列占70%以上包括单表查询、多表关联、分组统计、子查询等INSERT/UPDATE/DELETE加起来占25%左右其中INSERT用得最多UPDATE次之DELETE反而要谨慎使用DDL类语句占比不高但建库建表、加索引时一次性写很多。经常听到有人说“SQL没啥好学的不就是增删改查”。这话我不同意。增删改查是四个动作没错但SELECT的水深程度远超想象。同一个统计需求不同水平的人写出来的SQL性能可能差几十倍而在ADO的循环里执行低效SQL程序卡到直接让用户暴躁。2.3 放回ADO场景里看SQL语句对应哪些API在ADO里执行SQL大体有两条路直接用Connection.Execute执行不需要返回结果集的语句比如建表、INSERT、UPDATE、DELETE用Command对象执行SQL可以带上参数适合执行带条件的增删改查查询结果可以放进Recordset里逐行处理。 不需要结果集的SQL直接用 Connection.Execute 执行 conn.Execute UPDATE 库存 SET 数量 数量 - 1 WHERE 商品ID 1001 需要返回结果集的查询通常配合 Recordset rs.Open SELECT * FROM 商品 WHERE 分类 饮料, conn, adOpenStatic, adLockReadOnly这条路径分清楚之后你再看SQL语句就不会觉得它是孤立的语法了。它总得落在某个ADO调用里产生实际效果。3. 查询语句从SELECT开始但别只会SELECT3.1 SELECT的基本骨架与执行顺序SELECT是SQL里最核心的一类语句也是ADO编程中最常用到的。基本骨架是SELECT 列1, 列2, 聚合函数(...) FROM 表名 WHERE 行级过滤条件 GROUP BY 分组字段 HAVING 分组后的过滤条件 ORDER BY 排序字段语法看起来简单但很多坑都出在“没搞懂执行顺序”上。SQL并不是按你写的顺序执行的真正的逻辑顺序大致是FROM先确定数据来自哪张表WHERE对每一行做过滤GROUP BY把过滤后的行分组HAVING对分组结果再过滤SELECT决定最后输出的列和计算ORDER BY对最终结果排序。这个顺序不是死知识它直接决定你写SQL会不会报错。比如WHERE子句里不能使用SELECT里起的别名因为WHERE执行在SELECT之前。经常有人写SELECT 客户ID, SUM(金额) AS 总金额 FROM 订单 WHERE 总金额 1000 GROUP BY 客户ID在大部分数据库里这样直接报“无效列名”。正确写法是用HAVINGSELECT 客户ID, SUM(金额) AS 总金额 FROM 订单 GROUP BY 客户ID HAVING SUM(金额) 1000我遇到过很多次把WHERE和HAVING用混的人不在少数。记住这句话WHERE过滤的是原始行HAVING过滤的是分组后的结果。这个区分在实际写统计报表时是逃不掉的。3.2 WHERE条件的细节NULL、运算符、字符串匹配开发过程中我在WHERE条件上吃过很多次亏挑几个重点说。第一个是NULL的判断。NULL不等于空字符串也不等于0它是一个“未知”状态。如果你写SELECT * FROM 客户 WHERE 备注 NULL这条语句查不到任何记录因为NULL不能用等号比较。必须写成SELECT * FROM 客户 WHERE 备注 IS NULL同理想要“排除掉备注为空”的记录得写IS NOT NULL。第二个是字符串匹配。Access和SQL Server通配符有差异这一点在ADO编程里特别容易踩。传统AccessJet/ACE引擎里通配符是*和?在SQL Server里通配符是%和_。如果你用ADO连接Access数据库却写了WHERE 姓名 LIKE %张%在Access老版本里可能查不到数据或报错应该写LIKE *张*。我当年就是因为这个在Access和SQL Server之间来回切结果两边的写法不一样排查了半天。第三个是范围查询。前面提到日期范围推荐用和不要用BETWEEN。BETWEEN AND在SQL Server里是包含边界的如果日期列带时分秒很容易把边界那天的数据重复算进去。比如SELECT * FROM 订单 WHERE 下单时间 BETWEEN 2023-05-20 00:00:00 AND 2023-05-20 23:59:59看起来没问题但如果你拿到的边界只有日期写成BETWEEN 2023-05-20 AND 2023-05-21那么5月21日零点也会被包含进去。稳妥的做法是WHERE 下单时间 2023-05-20 AND 下单时间 2023-05-213.3 JOIN与子查询什么时候该用哪种写法多表查询是SQL里避不开的核心。最常见的关联类型有INNER JOIN、LEFT JOIN、RIGHT JOIN。初学阶段我建议先把INNER JOIN和LEFT JOIN练透RIGHT JOIN用得少理解了LEFTRIGHT就是镜像。聊一个我实际写过的场景查询“每个客户的订单总金额”同时要显示客户姓名。客户信息在客户表订单在订单表关联字段是客户ID。新手容易先查客户表拿到客户ID再逐条去查订单表——这就是经典的在循环里查数据库性能极差。正确姿势SELECT c.客户姓名, SUM(o.金额) AS 总金额 FROM 客户 c INNER JOIN 订单 o ON c.客户ID o.客户ID GROUP BY c.客户姓名再比如“列出所有客户及其最近一次下单时间没有下单的也要显示”这时候如果用INNER JOIN没下过单的客户就丢了应该用LEFT JOINSELECT c.客户ID, c.客户姓名, MAX(o.下单时间) AS 最近下单时间 FROM 客户 c LEFT JOIN 订单 o ON c.客户ID o.客户ID GROUP BY c.客户ID, c.客户姓名那什么时候用子查询我总结一个实用经验如果只是“为了拿一个聚合值”或“判断是否存在”子查询很容易读懂如果要把多张表的数据平铺到同一行返回JOIN更合适。例如找出“有下单但从未退货的客户”用NOT EXISTS子查询就比外连接简洁SELECT 客户ID, 客户姓名 FROM 客户 c WHERE EXISTS ( SELECT 1 FROM 订单 o WHERE o.客户ID c.客户ID ) AND NOT EXISTS ( SELECT 1 FROM 退货 r WHERE r.客户ID c.客户ID )3.4 分组聚合与去重统计统计报表是ADO编程里最常见的需求。用到的聚合函数就那么几个COUNT、SUM、AVG、MAX、MIN。单独用不难难在跟GROUP BY结合时的细节。先说COUNT(*)和COUNT(列名)的区别。COUNT(*)统计行数包括某列为NULL的行COUNT(列名)只统计该列不为NULL的行。如果统计“订单数”和“有备注的订单数”用两个写法得到的结果完全不同。再说DISTINCT。比如统计“一共有多少个客户下过单”如果直接SELECT COUNT(客户ID) FROM 订单这统计的是订单数不是客户数。要写成SELECT COUNT(DISTINCT 客户ID) FROM 订单DISTINCT放在COUNT里表示去重统计。这个细节我在教别人写报表时经常强调出错率极高。还有一个经验GROUP BY后面跟的字段如果没有被聚合通常也要出现在GROUP BY里。不同数据库严格程度不一样写代码时要尽量规范免得换数据库后程序直接崩掉。4. 数据操作语句INSERT/UPDATE/DELETE的实战要点4.1 三种语句的基本语法与ADO参数构造SELECT负责看数据INSERT、UPDATE、DELETE则负责改数据。这三条语句在ADO里执行方式类似但风险等级完全不同。INSERT的基本语法INSERT INTO 订单(客户ID, 商品ID, 数量, 金额) VALUES(1001, 5002, 2, 96.50)UDATE的基本语法UPDATE 库存 SET 数量 数量 - 2 WHERE 商品ID 5002DELETE的基本语法DELETE FROM 退货记录 WHERE 退货ID 2023052001在ADO里如果这些语句包含用户输入的值强烈建议使用参数化写法不要拼字符串。比如用Command对象Dim cmd As New ADODB.Command Set cmd.ActiveConnection conn cmd.CommandText UPDATE 库存 SET 数量 数量 - ? WHERE 商品ID ? cmd.Parameters.Append cmd.CreateParameter(p1, adInteger, adParamInput, , 2) cmd.Parameters.Append cmd.CreateParameter(p2, adInteger, adParamInput, , 5002) cmd.Execute这样做有两个好处一是避免SQL注入二是避免引号和特殊字符转义问题。后面避坑清单里我还会细说。4.2 事务处理为什么要把多条DML包在事务里DML操作最怕的是“做到一半失败”。比如一个出库操作需要同时做两件事给库存表减数量往出库流水表插一条记录。如果第一步成功、第二步失败库存对不上账后续全乱套。ADO里处理这种情况就是用事务核心是三个方法conn.BeginTrans 执行多条DML语句 conn.Execute UPDATE 库存 SET 数量 数量 - 2 WHERE 商品ID 5002 conn.Execute INSERT INTO 出库流水(商品ID, 数量, 出库时间) VALUES(5002, 2, NOW()) conn.CommitTrans一旦中间某条出错就回滚conn.RollbackTrans事务的使用有几条经验事务越短越好不要在事务里做耗时的查询或大量循环一旦调用了BeginTrans后面不管哪一步失败都要保证有RollbackTrans否则锁一直占着程序会卡死执行完所有DML后要CommitTrans不要忘了。4.3 批量写入的优化从逐条执行到批量提交刚开始写ADO程序时我干过一件蠢事用一个循环往数据库里插上万条记录每条都执行一次INSERT。程序跑了一个多小时慢到怀疑人生。后来优化分了几步走。第一步是“分批提交”。等我意识到要用事务才明白每一条INSERT默认都是单独提交事务数据库要刷新磁盘一次成本极高。把所有插入包在同一个事务里磁盘写入大幅减少。第二步是“多条VALUES拼接”。某些数据库一次INSERT可以插多行比如INSERT INTO 订单(客户ID, 商品ID, 数量) VALUES(1, 100, 2), (1, 101, 1), (2, 102, 3)这样一次执行就插了3行比循环3次效率高得多。不过要注意Access的Jet引擎老版本不支持这种多VALUES语法SQL Server支持ADO连接不同数据库时写法也要跟着数据库特性走。第三步是用Recordset直接批量插入ADODB.Recordset配合AddNew方法在某些场景下比字符串拼接更可控、更清晰rs.AddNew rs(客户ID) 1 rs(商品ID) 100 rs(数量) 2 rs.Update批量写入优化的核心思路都是同一个减少数据库的往返次数。每执行一次SQL就要产生一次网络往返、一次解析、一次事务提交。把这些开销合并起来性能自然上去。5. DDL与索引设计SQL的学习没有止步于查询5.1 CREATE/ALTER/DROP 的常用场景我见过不少只写SELECT的开发者一提到建表就发怵。但实际开发中尤其在一个自动化运维系统或灵活的配置系统里动态建临时表、动态加字段都是很常见的需求。建表最基础的写法CREATE TABLE 员工 ( 员工ID INT PRIMARY KEY, 姓名 VARCHAR(50) NOT NULL, 入职日期 DATETIME, 部门ID INT )ALTER TABLE用来修改表结构比如给表加一个字段ALTER TABLE 员工 ADD 联系电话 VARCHAR(20)DROP TABLE是删表执行前要非常谨慎一旦删除表里的数据就没了。在写了DROP的语句之后我通常会额外加一句注释或者再加一次逻辑判断防止误触发。DDL语句在ADO里执行也很简单直接Connection.Execute就行不需要返回结果集。但要注意DDL通常隐式提交事务执行后立即生效没有后悔药。5.2 索引为什么重要从一次慢查询说起有一次线上系统报表页加载很慢用户查一次要等十几秒。我打开那张经常被查询的表一看几万条记录查询条件里的关键字段一个索引都没建数据库只能全表扫描一行一行去比对。后来在查询条件字段上建了索引CREATE INDEX IX_订单_下单时间 ON 订单(下单时间)再执行同样的查询秒出结果。索引的原理说起来不复杂没有索引数据库要全表扫一遍有索引就能像查字典一样先定位到大致位置再找到目标行。这就把O(n)的复杂度降到了近似O(log n)。不过索引不是越多越好。每建一个索引插入、更新、删除时都要额外维护这个索引结构写操作会变慢。我见过一张表上建了几十个索引的查询没快多少写入倒是明显变卡了。索引设计要“给查询需要的字段建”而不是“能建就建”。5.3 别犯的几个索引误区第一个误区在索引列上做运算或函数。比如SELECT * FROM 订单 WHERE YEAR(下单时间) 2023如果下单时间有索引这种写法通常会让索引失效因为数据库需要对每一行的下单时间先算函数再比较结果。更好的写法是SELECT * FROM 订单 WHERE 下单时间 2023-01-01 AND 下单时间 2024-01-01第二个误区不知道复合索引最左前缀。复合索引(客户ID, 下单时间)通常能高效支持的条件组合是“客户ID单独”“客户ID下单时间”但如果你只拿下单时间做条件可能就用不上这个复合索引。设计复合索引时把等值查询的字段放前面范围查询的字段放后面是一个比较常规的套路。第三个误区认为主键就是索引。主键确实是索引而且通常是聚簇索引但它只覆盖主键字段。如果按其他字段查还得建额外索引。别因为有了主键就忽略了其他查询条件的索引需求。6. SQL学习中藏着的那些深坑避坑清单6.1 字符串拼接与SQL注入SQL注入这个话题在ADO编程中尤其绕不开因为早期很多ASP/VB程序都是用字符串直接拼接SQL。比如用户输入用户名和密码程序写出sql SELECT * FROM 用户 WHERE 用户名 userName AND 密码 password 如果用户在用户名里输入 OR 11拼接出来的SQL就变成了SELECT * FROM 用户 WHERE 用户名 OR 11 AND 密码 这个查询在多数情况下能直接把整张用户表查出来等于绕过了登录校验。这种攻击我身边真实发生过教训惨痛。解决方案只有一个核心原则用户输入永远不要直接拼进SQL语句。不管你是用ADO.Command的参数还是用存储过程的参数都不要用字符串拼接。6.2 日期格式与区域设置日期时间的格式化是SQL语句中最容易翻车的地方之一。不同数据库对日期字符串的解析规则不一样SQL Server通常能识别2023-05-20Access里对日期字符串的要求更严格推荐用#2023-05-20#这种格式rs.Open SELECT * FROM 订单 WHERE 下单时间 #2023-05-20# AND 下单时间 #2023-05-21#, conn在多语言操作系统中系统区域设置还可能导致2023-05-20被解析成20-05-2023或05-20-2023一本正经地查不到数据。我学到的教训是尽量使用标准的YYYY-MM-DD格式并且在SQL里避免使用区域相关的日期缩写。如果能在参数化查询里把日期当参数传进去是最好的方案。6.3 参数顺序、数据类型与引号转义即使你用了参数化查询也有几个小细节要注意。第一个是参数顺序。用ADO的Command对象时参数是按顺序绑定的必须和SQL里的?占位符一一对应。我在Access和SQL Server之间切换时因为SQL方言在参数占位符上的差异踩了不少坑Access的Jet引擎用?占位符SQL Server里也可以用?但命名参数更清晰。第二个是参数类型。比如某字段在表里是INT参数化时却传了字符串数据库虽然可能自动转换但一旦遇到无法转换的值就报错。更严重的是类型不匹配可能导致数据库放弃索引查询性能骤降。创建参数时要尽量指定数据类型cmd.Parameters.Append cmd.CreateParameter(p1, adInteger, adParamInput, , 5002)第三个是字符串里的单引号转义。如果不用参数化那么SQL里要插入一个包含单引号的字符串就必须把单引号双写INSERT INTO 客户(姓名) VALUES(OBrien)这个双单引号规则在拼字符串时特别容易漏漏了就是完整的SQL语法错误。6.4 分页写法与关键字冲突分页查询在不同数据库里写法差异很大也是初学ADO时常见的“拦路虎”。在SQL Server里经典做法是ROW_NUMBER()SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY 订单ID) AS RowNum FROM 订单 ) AS t WHERE RowNum BETWEEN 11 AND 20在MySQL里是LIMITSELECT * FROM 订单 ORDER BY 订单ID LIMIT 10 OFFSET 10在Access里则经常用TOP加子查询配合的方式。如果你的ADO程序要兼容多种数据库分页逻辑往往要单独写分支处理不能一套SQL走天下。关键字冲突是另一个很隐蔽的坑。比如表名或字段名取了User、Order、Group、Desc这类单词在SQL Server里要用方括号包起来SELECT * FROM [Order]如果不包语句很可能直接报语法错误或者在特定数据库里被解析成关键字。最彻底的办法是建表时避开这些保留字取表名字段名之前先查一下保留字列表。但遇到旧系统改不了的情况就只能靠方括号或反引号解决。6.5 空值、空串和“找不到记录”的判断最后说一个处理结果集时经常搞混的概念。SQL里的空值是NULL但有些业务场景里“空字符串”是另一个意思。比如客户联系方式既可能没填NULL也可能填了空字符串。如果你写WHERE 联系电话 它只匹配空字符串不会匹配NULL。要两者都排除得同时写两个条件WHERE 联系电话 IS NOT NULL AND 联系电话 在ADO的Recordset里判断“有没有记录”时新手经常先执行查询再判断rs.EOF或rs.Fields.Count我的习惯是先rs.Open然后立刻检查是否EOF避免在空记录集里访问字段引发错误。配合上面这些SQL层面的判断才能把边界条件处理干净。写在最后的一点学习建议SQL语句学起来很容易陷入“背语法”的误区。语法背得再熟不写几段完整的需求不踩几个真实的坑是记不牢的。我自己的体会是把SQL学习放到项目里反复练拿到一张真实业务表写出十几个统计查询再配合ADO去实现一两个增删改查页面一个循环下来对SQL的把握要比死记硬背高得多。这一篇讲的是SQL语句本身下一篇我再专门聊ADO对象模型Connection、Command、Recordset怎么配合参数化查询怎么写事务怎么控以及Recordset游标和锁定类型怎么选。这些内容都是我在实际开发中反复用过、也反复吃过亏的地方到时候再一条一条给你拆开讲。
