MySQL怎样使用条件查询 WHERE子句的多种用法演示

<p><a style="color:#f60; text-decoration:underline;" title="mysql" href="//m.sbmmt.com/zt/15713.html" target="_blank">mysql</a>中使用where子句进行条件查询的核心是通过精确条件筛选数据行，其主要用法包括：1. 使用基本比较运算符（如=、>、ain分析执行计划，并注意防范sql注入，推荐使用参数化查询、输入验证、最小权限原则等安全措施。此外，可在where中使用case语句实现多条件分支判断，提升查询灵活性，但需注意其对性能的影响。掌握这些方法可有效提升数据查询效率与安全性。</p> <p><img src="https://img.php.cn/upload/article/001/503/042/175436856553201.jpeg" alt="MySQL怎样使用条件查询 WHERE子句的多种用法演示"></p> <p>MySQL中使用WHERE子句进行条件查询，核心在于精确筛选出符合特定标准的数据行。它允许你基于一个或多个条件过滤数据，是数据分析和报表生成的基础。</p> <p>WHERE子句的多样用法演示：</p> <p>条件查询是数据库操作中最基础，也是最重要的组成部分之一。在MySQL中，<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;"><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">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="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="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;"><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">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="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="contentsignin">登录后复制</div></div>子句的多种用法，并结合实际案例进行演示。</p> <p><strong>1. 基本比较运算符：等于、不等于、大于、小于</strong></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">employees</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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>大于50000的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE salary > 50000;</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;"><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="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>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">>=</pre><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> <p><strong>2. 范围查询：BETWEEN AND</strong></p> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">BETWEEN AND</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;"><pre class="brush:php;toolbar:false">employees</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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>在60000到80000之间的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE salary BETWEEN 60000 AND 80000;</pre><div class="contentsignin">登录后复制</div></div><p><strong>3. 集合查询：IN 和 NOT IN</strong></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">IN</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;"><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">department_id</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>为10、20或30的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE department_id IN (10, 20, 30);</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">NOT IN</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div> 则相反，用于排除集合中的值。</p> <p><strong>4. 模糊查询：LIKE</strong></p> <p><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="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">%</pre><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>（匹配单个字符）一起使用。比如，查找所有名字以"A"开头的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE first_name LIKE 'A%';</pre><div class="contentsignin">登录后复制</div></div><p>要查找名字中包含"an"的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE first_name LIKE '%an%';</pre><div class="contentsignin">登录后复制</div></div><p><strong>5. 空值查询：IS NULL 和 IS NOT NULL</strong></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">NULL</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;"><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;"><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="contentsignin">登录后复制</div></div>来判断。我们需要使用<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="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="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">commission_pct</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;'>SELECT * FROM employees WHERE commission_pct IS NULL;</pre><div class="contentsignin">登录后复制</div></div><p><strong>6. 逻辑运算符：AND、OR、NOT</strong></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">AND</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">OR</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">NOT</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;"><pre class="brush:php;toolbar:false">department_id</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>为10，且<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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>大于60000的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE department_id = 10 AND salary > 60000;</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;"><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">department_id</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>为10或<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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>大于60000的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE department_id = 10 OR salary > 60000;</pre><div class="contentsignin">登录后复制</div></div><p><strong>7. 子查询：在WHERE子句中使用SELECT语句</strong></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;"><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">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="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="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;"><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">salary</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="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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><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:sql;toolbar:false;'>SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><p><strong>8. EXISTS 和 NOT EXISTS</strong></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">EXISTS</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;"><pre class="brush:php;toolbar:false">EXISTS</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>为真。例如，查找至少有一个员工的部门：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM departments WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><p><strong>9. 结合函数使用</strong></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;"><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">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="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="contentsignin">登录后复制</div></div>子句可以与MySQL的内置函数结合使用，以实现更复杂的过滤。例如，使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">YEAR()</pre><div class="contentsignin">登录后复制</div></div>函数查找所有在2000年之后入职的员工：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE YEAR(hire_date) > 2000;</pre><div class="contentsignin">登录后复制</div></div><p><strong>实际案例：复杂条件查询</strong></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">employees</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;"><pre class="brush:php;toolbar:false">department_id</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>为50或80，且<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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>在50000到70000之间，且<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">commission_pct</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;'>SELECT * FROM employees WHERE (department_id = 50 OR department_id = 80) AND salary BETWEEN 50000 AND 70000 AND commission_pct 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;"><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;"><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="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="contentsignin">登录后复制</div></div>子句，我们可以精确地从数据库中提取所需的数据，为数据分析、报表生成和业务决策提供支持。掌握这些用法，是成为一名合格的MySQL开发者的基础。</p> <p>MySQL WHERE子句的性能优化技巧有哪些？</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;"><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">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="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="contentsignin">登录后复制</div></div>子句的性能，主要围绕索引的使用和查询条件的编写。以下是一些关键技巧：</p> <ol> <li> <p><strong>利用索引：</strong></p> <ul> <li> <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;"><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;"><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="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="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;"><pre class="brush:php;toolbar:false">department_id</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="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">department_id</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>列上创建索引。</li> <li> <strong>避免在索引列上使用函数或计算：</strong> 在索引列上使用函数（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">YEAR(hire_date)</pre><div class="contentsignin">登录后复制</div></div>）会导致索引失效，应尽量避免。如果必须使用函数，考虑创建函数索引（MySQL 5.7及以上版本支持）。</li> <li> <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;"><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;"><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="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="contentsignin">登录后复制</div></div>子句中的条件应尽可能匹配索引的顺序。例如，如果有一个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">(department_id, salary)</pre><div class="contentsignin">登录后复制</div></div>的复合索引，那么<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE department_id = 10 AND salary > 50000</pre><div class="contentsignin">登录后复制</div></div>会比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE salary > 50000 AND department_id = 10</pre><div class="contentsignin">登录后复制</div></div>更有效率。</li> </ul> </li> <li> <p><strong>优化查询条件：</strong></p> <ul> <li> <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;"><pre class="brush:php;toolbar:false">OR</pre><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;"><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="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">UNION</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">IN</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 department_id = 10 OR department_id = 20</pre><div class="contentsignin">登录后复制</div></div>可以改写为<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE department_id IN (10, 20)</pre><div class="contentsignin">登录后复制</div></div>。</li> <li> <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;"><pre class="brush:php;toolbar:false">NOT 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;"><pre class="brush:php;toolbar:false">!=</pre><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;"><pre class="brush:php;toolbar:false">NOT 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;"><pre class="brush:php;toolbar:false">!=</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;"><pre class="brush:php;toolbar:false">LEFT JOIN</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>或子查询来替代。</li> <li>*<em>使用<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">EXISTS</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>代替`COUNT(</em>)<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">：** 如果只需要判断是否存在满足条件的记录，使用</pre><div class="contentsignin">登录后复制</div></div>EXISTS<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">比</pre><div class="contentsignin">登录后复制</div></div>COUNT(*)`更有效率。</li> <li> <strong>缩小查询范围：</strong> 尽量使用范围更窄的条件。例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE date BETWEEN '2023-01-01' AND '2023-01-31'</pre><div class="contentsignin">登录后复制</div></div>比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE date >= '2023-01-01'</pre><div class="contentsignin">登录后复制</div></div>更有效率。</li> <li> <strong>避免<a style="color:#f60; text-decoration:underline;" title="隐式类型转换" href="//m.sbmmt.com/zt/63333.html" target="_blank">隐式类型转换</a>：</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;"><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">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="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="contentsignin">登录后复制</div></div>子句中的数据类型与列的数据类型一致，避免MySQL进行隐式类型转换，这会导致索引失效。</li> </ul> </li> <li> <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;"><pre class="brush:php;toolbar:false">EXPLAIN</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>分析查询：</strong></p> <ul><li>使用<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">EXPLAIN</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;"><pre class="brush:php;toolbar:false">EXPLAIN</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>的结果，可以调整索引和查询条件，以提高查询效率。</li></ul> </li> <li> <p><strong>考虑数据量和硬件：</strong></p> <ul> <li> <strong>数据量：</strong> 对于小表，全表扫描可能比使用索引更快。</li> <li> <strong>硬件：</strong> 硬件性能也会影响查询效率。例如，使用SSD硬盘可以显著提高查询速度。</li> </ul> </li> <li><p><strong>查询重写：</strong></p></li> </ol> <ul> <li> <strong>提前过滤：</strong> 如果可能，在连接操作之前先对单个表进行过滤，减少连接的数据量。</li> <li> <strong>子查询优化：</strong> 将某些子查询转换为连接操作，特别是当子查询返回大量数据时。</li> </ul> <p><strong>实际案例：索引优化</strong></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">employees</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;"><pre class="brush:php;toolbar:false">hire_date</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">hire_date</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">hire_date</pre><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;'>CREATE INDEX idx_hire_date ON employees (hire_date);</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;"><pre class="brush:php;toolbar:false">EXPLAIN</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>分析查询：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>EXPLAIN SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-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;"><pre class="brush:php;toolbar:false">EXPLAIN</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;"><pre class="brush:php;toolbar:false">idx_hire_date</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;"><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;"><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="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="contentsignin">登录后复制</div></div>子句的查询效率，从而提升整个数据库的性能。但需要注意的是，索引并非越多越好，过多的索引会增加写操作的负担，因此需要根据实际情况进行权衡。</p> <p>如何在WHERE子句中使用子查询和连接查询？</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;"><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">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="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="contentsignin">登录后复制</div></div>子句中使用子查询和连接查询，可以实现更复杂的条件过滤，从不同的表或同一张表中提取数据，并将其作为筛选条件。</p> <p><strong>1. 子查询：</strong></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;"><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">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="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="contentsignin">登录后复制</div></div>子句中使用子查询，可以将子查询的结果作为条件来过滤主查询的数据。</p> <ul> <li> <p><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;"><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">salary</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="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;"><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">salary</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><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:sql;toolbar:false;'>SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div></li> <li> <p><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;"><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="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">NOT 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;"><pre class="brush:php;toolbar:false">EXISTS</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;"><pre class="brush:php;toolbar:false">NOT EXISTS</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;"><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">department_id</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="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">departments</pre><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 * FROM employees WHERE department_id IN (SELECT department_id FROM departments);</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;"><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">department_id</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="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">departments</pre><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 * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments);</pre><div class="contentsignin">登录后复制</div></div></li> <li> <p><strong>行子查询：</strong> 返回一行的子查询。通常用于比较多个列的值。例如：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM employees WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);</pre><div class="contentsignin">登录后复制</div></div></li> <li> <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;"><pre class="brush:php;toolbar:false">EXISTS</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;"><pre class="brush:php;toolbar:false">NOT EXISTS</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>子查询：</strong> 用于判断子查询是否返回任何行。例如，查找至少有一个员工的部门：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM departments WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div></li> </ul> <p><strong>2. 连接查询：</strong></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;"><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">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="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="contentsignin">登录后复制</div></div>子句中使用连接查询，可以将连接的结果作为条件来过滤数据。</p> <ul> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">INNER JOIN</pre><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;"><pre class="brush:php;toolbar:false">employee</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;"><pre class="brush:php;toolbar:false">first_name</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;"><pre class="brush:php;toolbar:false">department_name</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;"><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">department</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;"><pre class="brush:php;toolbar:false">employee</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>：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700;</pre><div class="contentsignin">登录后复制</div></div></li> <li> <p><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">LEFT JOIN</pre><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;"><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="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;"><pre class="brush:php;toolbar:false">employee</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;"><pre class="brush:php;toolbar:false">first_name</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;"><pre class="brush:php;toolbar:false">department_name</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;"><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">employee</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;"><pre class="brush:php;toolbar:false">department</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>：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700 OR d.location_id IS NULL;</pre><div class="contentsignin">登录后复制</div></div></li> <li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">RIGHT JOIN</pre><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;"><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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>。</p></li> <li> <p><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">FULL JOIN</pre><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;"><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="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">NULL</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>。MySQL本身不支持<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">FULL JOIN</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">UNION</pre><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 e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id UNION SELECT e.first_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;</pre><div class="contentsignin">登录后复制</div></div></li> </ul> <p><strong>实际案例：复杂条件查询</strong></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;"><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">salary</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="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">department</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;"><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">salary</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="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">department</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;"><pre class="brush:php;toolbar:false">location_id</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>为1700。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT e.* FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AND d.location_id = 1700;</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">JOIN</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;"><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">employees</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;"><pre class="brush:php;toolbar:false">departments</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;"><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;"><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="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="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">department</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;"><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">salary</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="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;"><pre class="brush:php;toolbar:false">employee</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;"><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">salary</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="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;"><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">salary</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="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">AND</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;"><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">department</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;"><pre class="brush:php;toolbar:false">location_id</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>为1700。</p> <p>通过灵活运用子查询和连接查询，我们可以实现非常复杂的条件过滤，从多个表中提取相关数据，并进行组合和比较。这对于数据分析、报表生成和业务决策非常有用。需要注意的是，复杂的查询可能会影响性能，因此需要进行优化，例如使用索引、避免全表扫描等。</p> <p>MySQL WHERE子句中的安全问题，如何防范SQL注入？</p> <p>SQL注入是Web应用程序中常见的安全漏洞，它允许攻击者通过在输入字段中插入恶意的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;"><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;"><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="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="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;"><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;"><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="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="contentsignin">登录后复制</div></div>子句中的条件来绕过身份验证、访问<a style="color:#f60; text-decoration:underline;" title="敏感数据" href="//m.sbmmt.com/zt/36496.html" target="_blank">敏感数据</a>或执行恶意操作。</p> <p><strong>SQL注入攻击原理：</strong></p> <p>攻击者通过在用户可控的输入点（例如，表单字段、URL参数等）中注入恶意的SQL代码，这些代码会被应用程序拼接成SQL查询语句，并发送给数据库执行。如果应用程序没有对输入进行充分的验证和过滤，攻击者就可以利用这些注入的SQL代码来执行未经授权的操作。</p> <p><strong>防范SQL注入的措施：</strong></p> <ol> <li> <p><strong>使用参数化查询（Prepared Statements）：</strong></p> <p>参数化查询是防范SQL注入的最有效方法。它将SQL查询语句和参数分开处理，数据库会先编译SQL语句，然后再将参数传递给数据库。这样可以防止攻击者通过注入SQL代码来修改查询语句的结构。</p> <ul> <li><p><strong>原理：</strong> 参数化查询使用占位符（例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">?</pre><div class="contentsignin">登录后复制</div></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">:name</pre><div class="contentsignin">登录后复制</div></div>）来代替SQL语句中的变量。在执行查询时，将实际的参数值传递给占位符。数据库会将这些参数值视为数据，而不是SQL代码，从而防止SQL注入。</p></li> <li> <p><strong>示例（PHP）：</strong></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;'>$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?"); $stmt->execute([$username, $password]); $user = $stmt->fetch();</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div></li> <li> <p><strong>优势：</strong></p> <ul> <li> <strong>安全性：</strong> 参数化查询可以有效地防止SQL注入攻击。</li> <li> <strong>性能：</strong> 参数化查询可以提高查询性能，因为数据库可以缓存编译后的SQL语句。</li> <li> <strong>可读性：</strong> 参数化查询可以提高代码的可读性，因为SQL语句和参数是分开的。</li> </ul> </li> </ul> </li> <li> <p><strong>输入验证和过滤：</strong></p> <p>对所有用户输入进行严格的验证和过滤，确保输入的数据符合预期的格式和类型。</p> <ul> <li><p><strong>白名单：</strong> 只允许输入白名单中的字符或模式。</p></li> <li><p><strong>黑名单：</strong> 禁止输入黑名单中的字符或模式（例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">'</pre><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>,<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">--</pre><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>,<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">*/</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">UNION</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">SELECT</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>等）。</p></li> <li><p><strong>转义特殊字符：</strong> 使用数据库提供的转义函数（例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">mysqli_real_escape_string()</pre><div class="contentsignin">登录后复制</div></div>）来转义SQL语句中的特殊字符。</p></li> <li> <p><strong>示例（PHP）：</strong></p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>$username = mysqli_real_escape_string($conn, $_POST['username']); $password = mysqli_real_escape_string($conn, $_POST['password']);</pre><div class="contentsignin">登录后复制</div></div></li> <li> <p><strong>注意事项：</strong></p> <ul> <li>输入验证和过滤只能作为辅助手段，不能完全依赖。</li> <li>应该在服务器端进行输入验证和过滤，而不是在客户端。</li> <li>应该对所有用户输入进行验证和过滤，包括表单字段、URL参数、Cookie等。</li> </ul> </li> </ul> </li> <li> <p><strong>最小权限原则：</strong></p> <p>为数据库用户分配最小的权限，只允许用户执行必要的操作。</p> <ul> <li><p><strong>原理：</strong> 如果攻击者成功注入了SQL代码，但数据库用户没有执行该操作的权限，攻击就会失败。</p></li> <li> <p><strong>示例：</strong></p> <ul> <li>不要使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">root</pre><div class="contentsignin">登录后复制</div></div>用户连接数据库。</li> <li>为每个应用程序创建单独的数据库用户，并分配必要的权限。</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">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">INSERT</pre><div class="contentsignin">登录后复制</div></div>操作。</li> </ul> </li> </ul> </li> <li> <p><strong>定期更新数据库和应用程序：</strong></p> <p>及时安装数据库和应用程序的安全补丁，修复已知的安全漏洞。</p> </li> <li> <p><strong>使用Web应用程序防火墙（WAF）：</strong></p> <p>WAF可以检测和阻止SQL注入攻击。</p> </li> </ol> <p><strong>SQL注入示例和防范：</strong></p> <p>假设有一个登录页面，用户需要输入用户名和密码。应用程序使用以下SQL查询语句来验证用户：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM users WHERE username = '$username' AND password = '$password';</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">username</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:php;toolbar:false;'>' OR '1'='1</pre><div class="contentsignin">登录后复制</div></div><p>那么SQL查询语句会变成：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '$password';</pre><div class="contentsignin">登录后复制</div></div><p>由于<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">'1'='1'</pre><div class="contentsignin">登录后复制</div></div>永远为真，攻击者就可以绕过身份验证，直接登录系统。</p> <p><strong>使用参数化查询防范：</strong></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;'>$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?"); $stmt->execute([$username, $password]); $user = $stmt->fetch();</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:php;toolbar:false">' OR '1'='1'</pre><div class="contentsignin">登录后复制</div></div>会被视为字符串，而不是SQL代码，从而防止SQL注入。</p> <p><strong>总结：</strong></p> <p>防范SQL注入需要多方面的措施，其中最重要的是使用参数化查询。同时，还需要进行输入验证和过滤、遵循最小权限原则、定期更新数据库和应用程序，以及使用WAF等安全<a style="color:#f60; text-decoration:underline;" title="工具" href="//m.sbmmt.com/zt/16887.html" target="_blank">工具</a>。只有综合运用这些措施，才能有效地保护数据库的安全。</p> <p>如何使用MySQL的CASE语句在WHERE子句中进行条件判断？</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;"><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">CASE</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>语句在MySQL中是一种强大的控制流工具，允许你在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;"><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;"><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="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="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;"><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">CASE</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>语句，可以实现更灵活的条件过滤，根据不同的情况应用不同的筛选规则。</p> <p><strong>基本语法：</strong></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;"><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">CASE</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="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;'>CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END</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;"><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">CASE</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="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">WHEN</pre><div class="contentsignin">登录后复制</div></div>子句中的条件，如果某个条件为真，则返回对应的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">THEN</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">ELSE</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;"><pre class="brush:php;toolbar:false">ELSE</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;"><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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>。</p> <p><strong>在WHERE子句中使用CASE语句：</strong></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;"><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">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="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="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;"><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">CASE</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="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;"><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">CASE</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>语句的结果作为条件来过滤数据。</p> <p><strong>示例1：根据不同的条件筛选不同的数据</strong></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>表，其中包含<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">product_id</pre><div class="contentsignin">登录后复制</div></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">product_name</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;"><pre class="brush:php;toolbar:false">category</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">price</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">category</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>的不同，应用不同的价格范围筛选规则。</p> <ul> <li>如果<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">category</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>为'Electronics'，则筛选<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">price</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>大于1000的产品。</li> <li>如果<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">category</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>为'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;"><pre class="brush:php;toolbar:false">price</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>小于100的产品。</li> <li>否则，筛选<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">price</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>在500到800之间的产品。</li> </ul> <p>可以使用以下SQL查询语句：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM products WHERE CASE WHEN category = 'Electronics' THEN price > 1000 WHEN category = 'Clothing' THEN price < 100 ELSE price BETWEEN 500 AND 800 END;</pre><div class="contentsignin">登录后复制</div></div><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;"><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">CASE</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="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;"><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">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="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="contentsignin">登录后复制</div></div>子句会根据这个布尔值来过滤数据。</p> <p><strong>示例2：根据不同的条件使用不同的列进行筛选</strong></p> <p>假设我们有一个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">users</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">user_id</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">username</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;"><pre class="brush:php;toolbar:false">email</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;"><pre class="brush:php;toolbar:false">phone</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">user_id</pre><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;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">user_id</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>小于100，则使用<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">email</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">phone</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>列进行筛选。</li> </ul> <p>可以使用以下SQL查询语句：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM users WHERE CASE WHEN user_id < 100 THEN email LIKE '%@example.com' ELSE phone LIKE '138%' END;</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;"><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">CASE</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="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;"><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">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="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="contentsignin">登录后复制</div></div>子句会根据这个布尔表达式来过滤数据。</p> <p><strong>示例3：结合其他条件使用CASE语句</strong></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;"><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">CASE</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="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;"><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;"><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">category</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>为'Electronics'且<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">price</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>大于1000，或者<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">category</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>为'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;"><pre class="brush:php;toolbar:false">price</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>小于100的产品。</p> <p>可以使用以下SQL查询语句：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM products WHERE (category = 'Electronics' AND price > 1000) OR (category = 'Clothing' AND price < 100) OR CASE WHEN category NOT IN ('Electronics', 'Clothing') THEN price BETWEEN 500 AND 800 END;</pre><div class="contentsignin">登录后复制</div></div><p>或者使用CASE语句简化：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM products WHERE CASE WHEN category = 'Electronics' THEN price > 1000 WHEN category = 'Clothing' THEN price < 100 ELSE price BETWEEN 500 AND 800 END;</pre><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><p><strong>注意事项：</strong></p> <ul> <li><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;"><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">CASE</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="contentsignin">登录后复制</div></div><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;"><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;"><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">CASE</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="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;"><pre class="brush:php;toolbar:false">JOIN</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">GROUP BY</pre><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;"><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;"><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">CASE</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>语句可能会影响查询性能，因此需要进行优化。</li> </ul> <p><strong>总结：</strong></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;"><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">CASE</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="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;"><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">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="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="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;"><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">CASE</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="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;"><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">CASE</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="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div><div class="contentsignin">登录后复制</div></div>语句可能会影响查询性能，因此需要进行优化。</p>

以上就是MySQL怎样使用条件查询 WHERE子句的多种用法演示的详细内容，更多请关注php中文网其它相关文章！