sql如何使用where子句筛选符合条件的数据 sqlwhere子句筛选数据的基础教程

<p>where子句用于筛选符合条件的数据行，其核心是通过比较运算符（=、、>、=、</p> <p><img src=”https://img.php.cn/upload/article/001/503/042/175507662292876.jpeg” alt=”sql如何使用where子句筛选符合条件的数据 sqlwhere子句筛选数据的基础教程”></p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句在SQL中是你的数据筛选器，它让你能够精确地从数据库表中挑选出符合特定条件的行。简单来说，它就是用来定义你想要什么数据的条件表达式。</p> <p>在使用SQL查询数据时，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句是你的核心<a >工具</a>，它允许你根据指定的条件来筛选记录。它的基本用法很简单：紧跟在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM</pre><div class=”contentsignin”></div></div>子句之后，然后定义你的筛选条件。</p> <p>例如，如果你想从一个名为<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Products</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的表中找出所有价格高于50的商品，你会这样写：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName, Price FROM Products WHERE Price > 50;</pre><div class=”contentsignin”></div></div><p>这里，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Price > 50</pre><div class=”contentsignin”></div></div>就是筛选条件。SQL会遍历<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Products</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>表中的每一行，只保留那些<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Price</pre><div class=”contentsignin”></div></div>列的值大于50的记录。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句支持多种比较运算符：</p> <ul> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> (等于)</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><></pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 或 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>!=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> (不等于)</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>></pre><div class=”contentsignin”></div></div> (大于)</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div> (小于)</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>>=</pre><div class=”contentsignin”></div></div> (大于等于)</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><=</pre><div class=”contentsignin”></div></div> (小于等于)</li> </ul> <p>除了数值，你也可以筛选文本或日期。比如，查找特定名称的产品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM Customers WHERE City = ‘New York’;</pre><div class=”contentsignin”></div></div><p>或者找出某个日期之后的所有订单：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT OrderID, OrderDate FROM Orders WHERE OrderDate > ‘2023-01-01′;</pre><div class=”contentsignin”></div></div><p>理解这些基础操作是玩转SQL数据筛选的第一步，它们构成了所有复杂查询的基石。</p> <h3>SQL WHERE子句如何组合多个筛选条件？</h3> <p>在实际的数据分析中，我们很少只需要一个单一的筛选条件。数据往往复杂得多，需要我们同时考虑多个维度。这时候，SQL的逻辑运算符就派上用场了：<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NOT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。它们让你能够构建更精细、更符合业务需求的筛选逻辑。</p> <p>我个人觉得，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的运用是SQL查询能力的一个分水岭。一开始，你可能只是简单地筛选“A”，但很快你就会发现需要“A且B”，甚至“A或B，但不是C”这样的组合。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>运算符要求所有连接的条件都必须为真，记录才会被选中。比如说，你想找所有价格高于50<strong>并且</strong>库存量低于10的商品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName, Price, Stock FROM Products WHERE Price > 50 AND Stock < 10;</pre><div class=”contentsignin”></div></div><p>这就像你在购物时，不仅要看价格是否符合预算，还要看有没有足够的库存。两个条件都得满足。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>运算符则宽松得多，只要连接的条件中有一个为真，记录就会被选中。如果你想找出居住在“New York”<strong>或者</strong>“London”的客户：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT CustomerName, City FROM Customers WHERE City = ‘New York’ OR City = ‘London’;</pre><div class=”contentsignin”></div></div><p>这里，只要客户的城市是这两个中的任意一个，他们都会出现在结果集里。</p> <p>而<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NOT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>运算符，顾名思义，是用来否定一个条件的。它会返回不符合指定条件的记录。比如，你想列出所有<strong>不是</strong>来自“USA”的供应商：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT SupplierName, Country FROM Suppliers WHERE NOT Country = ‘USA’;</pre><div class=”contentsignin”></div></div><p>或者更简洁的写法，用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><></pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>!=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>来表示不等于。但<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NOT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>在某些复杂场景下，比如与<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>等结合时，会显得更清晰。</p> <p>当<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>混合使用时，优先级就变得很重要了。SQL会优先处理<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件，然后才是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。这和数学中的乘法优先于加法有点像。如果你不确定或者想明确指定执行顺序，使用括号<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>( )</pre><div class=”contentsignin”></div></div>是最好的习惯。比如：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName, Price, Category FROM Products WHERE (Category = ‘Electronics’ AND Price > 100) OR (Category = ‘Books’ AND Price < 50);</pre><div class=”contentsignin”></div></div><p>这个查询会找出电子产品中价格高于100的，<strong>或者</strong>书籍中价格低于50的。没有括号的话，逻辑可能会变得混乱，导致意想不到的结果。所以，我个人的经验是，只要涉及多个逻辑运算符，就大胆地用括号，这能极大地提升查询的可读性和准确性。</p> <h3>SQL WHERE子句有哪些高级筛选技巧？</h3> <p>除了基本的比较和逻辑运算符，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句还提供了一些非常实用的高级筛选操作符，它们能让你在处理特定类型的数据或更复杂的模式匹配时事半功倍。这些操作符包括<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>以及<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>/<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NOT NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>操作符，我觉得它简直是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的语法糖，尤其当你需要匹配多个离散值时，它能让你的SQL语句简洁很多。想象一下，你要查询产品分类是“Electronics”、“Books”或“Clothing”的商品。用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>写会很长：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName, Category FROM Products WHERE Category = ‘Electronics’ OR Category = ‘Books’ OR Category = ‘Clothing’;</pre><div class=”contentsignin”></div></div><p>而用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>则优雅得多：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName, Category FROM Products WHERE Category IN (‘Electronics’, ‘Books’, ‘Clothing’);</pre><div class=”contentsignin”></div></div><p>代码量减少了，可读性也大大提升。当列表很长时，这种优势尤其明显。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>操作符用于筛选某个范围内的值，包括范围的起始和结束值。这在处理数值或日期范围时非常方便。比如，查找价格在20到100之间（含20和100）的商品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName, Price FROM Products WHERE Price BETWEEN 20 AND 100;</pre><div class=”contentsignin”></div></div><p>这等同于<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Price >= 20 AND Price <= 100</pre><div class=”contentsignin”></div></div>，但显然<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>更直观。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>操作符则是字符串模式匹配的利器。它配合通配符<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>%</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>（匹配任意长度的任意字符）和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>_</pre><div class=”contentsignin”></div></div>（匹配单个任意字符）使用。这在模糊搜索或查找特定模式的文本时非常有用。比如，查找所有名称以“Smart”开头的商品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName FROM Products WHERE ProductName LIKE ‘Smart%’;</pre><div class=”contentsignin”></div></div><p>或者查找名称中包含“phone”的商品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName FROM Products WHERE ProductName LIKE ‘%phone%’;</pre><div class=”contentsignin”></div></div><p>甚至可以查找第二个字母是’a’的商品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT ProductName FROM Products WHERE ProductName LIKE ‘_a%’;</pre><div class=”contentsignin”></div></div><p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的强大在于它的灵活性，但也要注意，在处理大量数据时，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>（尤其是以<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>%</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>开头的模式）可能会影响查询性能，因为它通常无法有效利用索引。</p> <p>最后是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NOT NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。SQL中，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>表示未知或缺失的值，它不是零，也不是空字符串。所以，你不能用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>!=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>来判断一个字段是否为<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。必须使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NOT NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。</p> <p>查找所有没有填写电子邮件的客户：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT CustomerName FROM Customers WHERE Email IS NULL;</pre><div class=”contentsignin”></div></div><p>查找所有已填写地址的客户：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT CustomerName FROM Customers WHERE Address IS NOT NULL;</pre><div class=”contentsignin”></div></div><p>理解<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的特殊性，并正确使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>IS NOT NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，是避免数据筛选逻辑错误的关键。这些高级技巧，一旦掌握，能让你的SQL查询能力提升一个档次，应对各种复杂的筛选需求。</p> <h3>SQL WHERE子句的性能优化与常见陷阱？</h3> <p>虽然<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句是数据筛选的核心，但它的使用方式对查询性能有着举足轻重的影响。一个看似简单的筛选条件，在面对亿级数据量时，可能导致查询耗时数秒甚至数分钟。所以，除了学会如何写，更要学会如何写得高效，并避开一些常见的陷阱。</p> <p>我个人在工作中，常常会因为一个<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句写得不够“聪明”而掉入性能陷阱。最常见的性能杀手，我认为，是<strong>索引失效</strong>。数据库在执行查询时，如果能通过索引快速定位到符合条件的行，那速度会非常快。但某些<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句的写法，会让索引变得无用。</p> <p>例如，对列进行函数操作：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 糟糕的写法：对列使用函数，可能导致索引失效 SELECT OrderID FROM Orders WHERE YEAR(OrderDate) = 2023;</pre><div class=”contentsignin”></div></div><p>如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OrderDate</pre><div class=”contentsignin”></div></div>列上有索引，上面这种写法会导致数据库对每一行都计算<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>YEAR(OrderDate)</pre><div class=”contentsignin”></div></div>，然后才能比较，这会跳过索引。更好的做法是：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 更好的写法：避免对列使用函数，让索引生效 SELECT OrderID FROM Orders WHERE OrderDate BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;</pre><div class=”contentsignin”></div></div><p>或者，对<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>操作符来说，以通配符<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>%</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>开头的模式，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE ProductName LIKE ‘%phone%'</pre><div class=”contentsignin”></div></div>，通常也无法利用索引，因为它需要扫描整个字符串。而<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE ProductName LIKE ‘Smart%'</pre><div class=”contentsignin”></div></div>则通常可以利用索引。</p> <p>另一个常见的陷阱是<strong>数据类型不匹配</strong>。虽然有些数据库会自动进行类型转换，但这会消耗额外的资源，并且也可能导致索引失效。比如，数字列与字符串进行比较：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 潜在的性能问题：数字列与字符串比较 SELECT ProductID FROM Products WHERE ProductCode = ‘12345’; — 如果ProductCode是整数类型</pre><div class=”contentsignin”></div></div><p>确保你的比较值与列的数据类型一致，这是一个小细节，但很重要。</p> <p>再有，<strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件的滥用</strong>。虽然<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>在逻辑上很方便，但在某些情况下，尤其当<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>连接的列没有复合索引时，它可能导致全表扫描。有时，将一个复杂的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件拆分成多个<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>UNION ALL</pre><div class=”contentsignin”></div></div>的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SELECT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>语句，反而能获得更好的性能，因为它允许数据库独立地利用每个子查询的索引。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 考虑性能：如果索引不佳，复杂的OR可能导致全表扫描 SELECT CustomerID FROM Customers WHERE City = ‘New York’ OR State = ‘CA’; — 优化思路（有时更优）： SELECT CustomerID FROM Customers WHERE City = ‘New York’ UNION ALL SELECT CustomerID FROM Customers WHERE State = ‘CA’ AND City <> ‘New York’; — 避免重复</pre><div class=”contentsignin”></div></div><p>这需要根据具体的数据库和索引情况来判断，但至少提供了一个优化的思路。</p> <p>最后，一个简单但常常被忽视的点：<strong>选择合适的列</strong>。在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SELECT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句中只选择你真正需要的列，而不是无脑地<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SELECT *</pre><div class=”contentsignin”></div></div>。虽然这不直接影响<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句的筛选逻辑，但它减少了数据传输量，从而间接提升了整体查询效率。</p> <p>总而言之，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句的优化是一个持续学习和实践的过程。多观察执行计划（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN ANALYZE</pre><div class=”contentsignin”></div></div>命令），理解索引的工作原理，并避免那些会使索引失效的操作，是写出高性能SQL查询的关键。它不是一蹴而就的，而是需要你在每次写查询时都多思考一步。</p>