MySQL-Plus QueryWrapper函数与SQL语句对比表

本是古典何须时尚 2022-09-09 06:23 169阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>例子</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>eq</code></td> 
   <td>等于=</td> 
   <td><code>eq("name", "张三")</code> -&gt; <code>name = '张三'</code></td> 
  </tr> 
  <tr> 
   <td><code>ne</code></td> 
   <td>不等于&lt;&gt;</td> 
   <td><code>ne("name", "张三")</code> -&gt; <code>name &lt;&gt; '张三'</code></td> 
  </tr> 
  <tr> 
   <td><code>gt</code></td> 
   <td>大于 &gt;</td> 
   <td><code>gt('age', 18)</code> -&gt; <code>age &gt; 18</code></td> 
  </tr> 
  <tr> 
   <td><code>ge</code></td> 
   <td>大于等于 &gt;=</td> 
   <td><code>ge('age', 18)</code> -&gt; <code>age &gt;= 18</code></td> 
  </tr> 
  <tr> 
   <td><code>lt</code></td> 
   <td>小于 &lt;</td> 
   <td><code>lt('age', 18)</code> -&gt; <code>age &lt; 18</code></td> 
  </tr> 
  <tr> 
   <td><code>le</code></td> 
   <td>小于等于 &lt;=</td> 
   <td><code>le('age', 18)</code> -&gt; <code>age &lt;= 18</code></td> 
  </tr> 
  <tr> 
   <td><code>between</code></td> 
   <td>BETWEEN 值1 AND 值2</td> 
   <td><code>between("age", 18, 30)</code> -&gt; <code>age between 18 and 30</code></td> 
  </tr> 
  <tr> 
   <td><code>notBetween</code></td> 
   <td>NOT BETWEEN 值1 AND 值2</td> 
   <td><code>notBetween("age", 18, 30)</code> -&gt; <code>age not between 18 and 30</code></td> 
  </tr> 
  <tr> 
   <td><code>like</code></td> 
   <td>LIKE ‘%值%’</td> 
   <td><code>like("name", "王")</code> -&gt; <code>name like '%王%'</code></td> 
  </tr> 
  <tr> 
   <td><code>notLike</code></td> 
   <td>NOT LIKE ‘%值%’</td> 
   <td><code>notLike("name", "王")</code> -&gt; <code>name not like '%王%'</code></td> 
  </tr> 
  <tr> 
   <td><code>likeLeft</code></td> 
   <td>LIKE ‘%值’</td> 
   <td><code>likeLeft("name", "王")</code> -&gt; <code>name like '%王'</code></td> 
  </tr> 
  <tr> 
   <td><code>likeRight</code></td> 
   <td>LIKE ‘值%’</td> 
   <td><code>likeRight("name", "王")</code> -&gt; <code>name like '王%'</code></td> 
  </tr> 
  <tr> 
   <td><code>isNull</code></td> 
   <td>字段 IS NULL</td> 
   <td><code>isNull("name")</code> -&gt; <code>name is null</code></td> 
  </tr> 
  <tr> 
   <td><code>isNotNull</code></td> 
   <td>字段 IS NOT NULL</td> 
   <td><code>isNotNull("name")</code> -&gt; <code>name is not null</code></td> 
  </tr> 
  <tr> 
   <td><code>in</code></td> 
   <td>字段 IN (v1,v2,…)</td> 
   <td><code>in("age", {1,2,3})</code> -&gt; <code>age in (1,2,3)</code></td> 
  </tr> 
  <tr> 
   <td><code>notIn</code></td> 
   <td>字段 NOT IN (v1,v2,…)</td> 
   <td><code>notIn("age", {1,2,3})</code> -&gt; <code>age not in (1,2,3)</code></td> 
  </tr> 
  <tr> 
   <td><code>inSql</code></td> 
   <td>字段 IN (SQL语句)</td> 
   <td><code>inSql("id", "select id from table where id &lt; 10")</code> -&gt; <code>age in (select id from table where id &lt; 10)</code></td> 
  </tr> 
  <tr> 
   <td><code>notInSql</code></td> 
   <td>字段 NOT IN (SQL语句)</td> 
   <td><code>notInSql("id", "select id from table where id &lt; 10")</code> -&gt; <code>age not in (select id from table where id &lt; 10)</code></td> 
  </tr> 
  <tr> 
   <td><code>groupBy</code></td> 
   <td>分组: GROUP BY 字段,…</td> 
   <td><code>groupBy("id", "name")</code> -&gt; <code>group by id,name</code></td> 
  </tr> 
  <tr> 
   <td><code>orderByAsc</code></td> 
   <td>排序: ORGER BY 字段,… ASC</td> 
   <td><code>orderByAsc("id", "name")</code> -&gt; <code>order by id ASC, name ASC</code></td> 
  </tr> 
  <tr> 
   <td><code>orderByDesc</code></td> 
   <td>排序: ORGER BY 字段,… DESC</td> 
   <td><code>orderByDesc("id", "name")</code> -&gt; <code>order by id DESC, name DESC</code></td> 
  </tr> 
  <tr> 
   <td><code>orderBy</code></td> 
   <td>排序: ORDER BY 字段,…</td> 
   <td><code>orderBy(true, true, "id", "name")</code> -&gt; <code>order by id ASC, name ASC</code></td> 
  </tr> 
  <tr> 
   <td><code>having</code></td> 
   <td>HAVING (SQL语句)</td> 
   <td><code>having("sum(age) &gt; {0}", 11)</code> -&gt; <code>having sum(age) &gt; 11</code></td> 
  </tr> 
  <tr> 
   <td><code>or</code></td> 
   <td>拼接</td> 
   <td><code>eq("id", 1).or().eq("name", "张三")</code> -&gt; <code>id=1 OR name='张三'</code>, 如果不掉用，默认是AND连接</td> 
  </tr> 
  <tr> 
   <td><code>and</code></td> 
   <td>AND 嵌套</td> 
   <td><code>and(i -&gt; i.eq("name", "张三").ne("status", "1"))</code> -&gt; <code>AND (name='张三' AND status &lt;&gt; '1')</code></td> 
  </tr> 
  <tr> 
   <td><code>apply</code></td> 
   <td>拼接 SQL</td> 
   <td>该方法可用于数据库函数，<code>apply("date_format(dataColumn, '%Y-%m-%d') = {0}", "2008-08-08")</code> -&gt; <code>date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'</code></td> 
  </tr> 
  <tr> 
   <td><code>last</code></td> 
   <td>不进行SQL优化</td> 
   <td>只能调用一次，多次调用最后一次为准，无视优化规则直接拼接到SQL最后，有SQL注入风险，<code>last("limit", 1)</code></td> 
  </tr> 
  <tr> 
   <td><code>exists</code></td> 
   <td>拼接 EXISTS (SQL语句)</td> 
   <td><code>exists("select id frome table where age = 1")</code> -&gt; <code>exists (select id from table where age = 1)</code></td> 
  </tr> 
  <tr> 
   <td><code>notExists</code></td> 
   <td>拼接 NOT EXISTS (SQL语句)</td> 
   <td><code>notExists("select id frome table where age = 1")</code> -&gt; <code>not exists (select id from table where age = 1)</code></td> 
  </tr> 
  <tr> 
   <td><code>nested</code></td> 
   <td>正常嵌套不带AND或者OR</td> 
   <td><code>nested(i -&gt; id.eq("name", "张三").ne("status", "1"))</code> -&gt; <code>(name = '张三' AND status &lt;&gt; '1')</code></td> 
  </tr> 
 </tbody> 
</table>