Mysql 表达式

如果display的列的值为1 则值变成禁止使用不是的话就还是使用原来列的值

<span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"><span class="cm-keyword" style="color: #d73a49;">SELECT</span> IF<span class="cm-bracket" style="color: #999977;">(</span>name <span class="cm-keyword" style="color: #d73a49;">like</span> <span class="cm-string" style="color: #669900;">"%PHP%"</span> <span class="cm-keyword" style="color: #d73a49;">and</span> name <span class="cm-keyword" style="color: #d73a49;">LIKE</span> <span class="cm-string" style="color: #669900;">"%JAVA%"</span><span class="cm-punctuation" style="color: #595959;">,</span>CONCAT<span class="cm-bracket" style="color: #999977;">(</span><span class="cm-string" style="color: #669900;">"java"</span><span class="cm-punctuation" style="color: #595959;">,</span>name<span class="cm-bracket" style="color: #999977;">)</span><span class="cm-punctuation" style="color: #595959;">,</span>id<span class="cm-bracket" style="color: #999977;">)</span> <span class="cm-keyword" style="color: #d73a49;">from</span> jobs <span class="cm-keyword" style="color: #d73a49;">LIMIT</span> <span class="cm-number" style="color: #005cc5;">10</span></span></span>

模糊匹配表达式

可以在where 表达式添加的都可以塞进这个if 表达式中

IFNULL

<span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"><span class="cm-keyword" style="color: #d73a49;">SELECT</span> id<span class="cm-punctuation" style="color: #595959;">,</span>IFNULL<span class="cm-bracket" style="color: #999977;">(</span>deleted_at<span class="cm-punctuation" style="color: #595959;">,</span><span class="cm-string" style="color: #669900;">"已经删除了"</span><span class="cm-bracket" style="color: #999977;">)</span> <span class="cm-keyword" style="color: #d73a49;">from</span> jobs  <span class="cm-keyword" style="color: #d73a49;">LIMIT</span> <span class="cm-number" style="color: #005cc5;">10</span>
</span></span>

判断列的值是否为null 为null 就替换成其他值

其他这个表达式也可以替换成

SELECT id,IF(deleted_at is null,"已经删除了",deleted_at)  from jobs LIMIT 10

CASE

这个就相当于逻辑语言的 switch case default

<span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"><span class="cm-keyword" style="color: #d73a49;">SELECT</span> id<span class="cm-punctuation" style="color: #595959;">,</span>case
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;">    WHEN wage<span class="cm-operator" style="color: #d73a49;">=</span><span class="cm-number" style="color: #005cc5;">56</span> THEN
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"> <span class="cm-string" style="color: #669900;">"小时工"</span>
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"> WHen wage<span class="cm-operator" style="color: #d73a49;">=</span><span class="cm-number" style="color: #005cc5;">20</span> THEN
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"> <span class="cm-string" style="color: #669900;">"正式工"</span>
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;">    ELSE
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"> <span class="cm-string" style="color: #669900;">"临时工"</span>
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;">END
</span></span><span class="lake-preview-line" style="color: #595959;"><span class="lake-preview-codeblock-content" style="color: #595959;"> <span class="cm-keyword" style="color: #d73a49;">from</span> jobs <span class="cm-keyword" style="color: #d73a49;">LIMIT</span> <span class="cm-number" style="color: #005cc5;">10</span></span></span>

根据这个wage 字段不同值返回不同的信息,最后的else 就相当于default的意思,这里面 每一个when匹配成功之后,后面的when 就不在匹配,相当于自己加上brek了,当然也可以同时匹配多个列,而且where 可以使用的表达式也可以塞进去

Mysql 表达式

标签：str   替换   over   span   ace   java   表达式   cdb   nbsp