MySQL学习笔记

45. MySQL运算符的使用

（1）算术运算符

①加减乘数

<span style="font-size:18px;">SELECT 1+2,3*7,1/3,23-88;</span>

②特殊的用法

<span style="font-size:18px;">SELECT 1/3MAIZI;</span>

②特殊的用法

<span style="font-size:18px;">SELECT 3/0;</span>

<pre name="code" class="sql"><span style="font-size:18px;">SELECT 3 DIV 0;</span>

<pre name="code" class="sql"><span style="font-size:18px;">SELECT 3%0;</span>

<span style="font-size:18px;">SELECT 3 MOD 0;</span>

+------+

| 3/0  |

+------+

| NULL |

+------+

（2）比较运算符

①等于

<span style="font-size:18px;">SELECT 1=1,1='1',1=2;</span>

<span style="font-size:18px;">--真为1，假为0</span>

<span style="font-size:18px;">SELECT username,username='king' FROM student;</span>

<p class="p1"><span class="s1"><span style="font-size:18px;">+------------+-----------------+</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| username   | username='king' |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">+------------+-----------------+</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei1 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei2 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei3 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei4 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei5 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei6 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| wanweiwei7 |               0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">+------------+-----------------+</span></span></p><span style="font-size: 18px;">
</span>

②不等于 <> != 比较数字，字符串

③null的比较，sex<=>null

④>=, <=

<span style="font-size:18px;">mysql> SELECT id,username,score,score>=70 from student;
+----+------------+-------+-----------+
| id | username   | score | score>=70 |
+----+------------+-------+-----------+
|  1 | wanweiwei1 |    88 |         1 |
|  2 | wanweiwei2 |    13 |         0 |
|  3 | wanweiwei3 |    77 |         1 |
|  4 | wanweiwei4 |    66 |         0 |
|  5 | wanweiwei5 |    55 |         0 |
|  6 | wanweiwei6 |    99 |         1 |
|  7 | wanweiwei7 |   100 |         1 |
+----+------------+-------+-----------+</span>

⑤IS NULL, IS NOT NULL

⑥BETWEEN AND

⑦IN, NOT IN

⑧LIKE，NOT LIKE

<span style="font-size:18px;">SELECT 's' LIKE '_';</span>

<span style="font-size:18px;">SELECT id,username,username LIKE '_____' FROM cms_user;</span>

<p class="p1"><span class="s1"><span style="font-size:18px;">+----+------------+-----------------------+</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| id | username   | username LIKE '_____' |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">+----+------------+-----------------------+</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  8 | blek       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| 11 | john       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  7 | king       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| 10 | lily       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| 14 | lll        |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  4 | long       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| 13 | Mao Zedong |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  6 | queen      |                     1 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  5 | ring       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  9 | rose       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">| 12 | 万伟伟     |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  1 | 张三       |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  2 | 张三丰     |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">|  3 | 章子怡     |                     0 |</span></span></p><p class="p1"><span class="s1"><span style="font-size:18px;">+----+------------+-----------------------+</span></span></p>

⑨正则REGEXP

^表示开头，$表示结尾，

<span style="font-size:18px;">SELECT id,username,username REGEXP '^b' FROM cms_user;
</span>

（3）逻辑运算符

①带NULL的时候要注意

SELECT 1&&1,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL;

+------+------+---------+------+------+---------+---------+
| 1&&1 | 2&&0 | 2&&NULL | 1||1 | 1||0 | 1||NULL | 0||NULL |
+------+------+---------+------+------+---------+---------+
|    1 |    0 |    NULL |    1 |    1 |       1 |    NULL |
+------+------+---------+------+------+---------+---------+

②取反！

③抑或 XOR

（4）运算符的优先级