6、MYSQL运算符
mysql运算符！
一、算数运算符
+ - * / %（求余运算）
mysql> CREATE TABLE test3(num INT); /*创建一个表，表中有一个字段，类型为INT*/
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO test3 VALUES(40); /*字段中插入一个数字40*/
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test3;
+------+
| num |
+------+
| 40 |
+------+
1 row in set (0.00 sec)
/*使用算数运算符来操作表中的INT字段*/
mysql> SELECT num,num+10,num-100,num*3,num/3,num%3 FROM test3;
/*使用算数运算符查看test3的num字段，以及字段中的值+10的结果，值-100的结果，值*3的结果，值/3的结果，值除以3之后的余数*/
+------+--------+---------+-------+---------+-------+
| num | num+10 | num-100 | num*3 | num/3 | num%3 |
+------+--------+---------+-------+---------+-------+
| 40 | 50 | -60 | 120 | 13.3333 | 1 |
+------+--------+---------+-------+---------+-------+
1 row in set (0.01 sec)
mysql> ALTER TABLE test3 MODIFY num DECIMAL(4,1); /*修改数据类型为定点数据类型保留小数一位*/
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test3 VALUES(40.2); /*插入带有小数点的一个值40.2*/
Query OK, 1 row affected (0.00 sec)
mysql> SELECT num%3 FROM test3;
/*小数点后的数字不参与取余的运算，直接拿余数+小数位的数字，也就是40.2中40除以3余1，在加上剩下的0.2*/
+-------+
| num%3 |
+-------+
| 1.0 |
| 1.2 |
+-------+二、比较运算符（用来判断真假命题，也就是返回值为1（真）或者0（假）
= 等于
<=> 安全等于
<>,!= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
IS NULL 判断一个值是否为NULL
ISNULL 同上
IS NOT NULL 判断一个值是否不为null
LEAST 多个参数取最小值
GREATEST 多个参数取最大值
BETWEEN AND 判断一个值是否介于两个值之间
IN 判断一个值是否是IN列表中的值
NOT IN 判断一个值是否不是IN列表中的值
LIKE 通配符查询
REGEXP 正则表达式查询
判断 1 真 0 假 返回值
=：等于号的使用，判断真假命题
mysql> SELECT 1=1,'2'=2,(1+1)=(2+0),1=NULL; /*只要一个值为NULL，则返回值为NULL，NULL不参与判断*/
+-----+-------+-------------+--------+
| 1=1 | '2'=2 | (1+1)=(2+0) | 1=NULL |
+-----+-------+-------------+--------+
| 1 | 1 | 1 | NULL |
+-----+-------+-------------+--------+<=>：安全等于号的使用
安全等于相当于安全判断会将一切的值进行正常的判断
mysql> SELECT NULL<=>NULL;（空=空） /*安全等于将NULL也会正常判断，比较=来说相对安全，也就是能看到返回的真假命题的值*/
+-------------+
| NULL<=>NULL |
+-------------+
| 1 |
+-------------+<>以及!=的使用
mysql> SELECT 1<>1;
+------+
| 1<>1 |
+------+
| 0 |
+------+
mysql> SELECT 50!=10,(1+2)<>(2+1),1<>NULL; /*<>也不能判断NULL*/
+--------+--------------+---------+
| 50!=10 | (1+2)<>(2+1) | 1<>NULL |
+--------+--------------+---------+
| 1 | 0 | NULL |
+--------+--------------+---------+IS NULL以及ISNULL的使用
两个运算符，用法不同，用来判断值是否非空，只有当值为NULL才会返回1，反之全为0
IS NULL：判断值 IS NULL
ISNULL：ISNULL(判断值)
mysql> SELECT 10 IS NULL,NULL IS NULL,0 IS NULL;
+------------+--------------+-----------+
| 10 IS NULL | NULL IS NULL | 0 IS NULL |
+------------+--------------+-----------+
| 0 | 1 | 0 |
+------------+--------------+-----------+
mysql> SELECT ISNULL(10),ISNULL(NULL),ISNULL(0);
+------------+--------------+-----------+
| ISNULL(10) | ISNULL(NULL) | ISNULL(0) |
+------------+--------------+-----------+
| 0 | 1 | 0 |
+------------+--------------+-----------+IS NOT NULL的使用
mysql> SELECT 10 IS NOT NULL,NULL IS NOT NULL,2 IS NOT NULL;
+----------------+------------------+---------------+
| 10 IS NOT NULL | NULL IS NOT NULL | 2 IS NOT NULL |
+----------------+------------------+---------------+
| 1 | 0 | 1 |
+----------------+------------------+---------------+IFNULL的使用
+-------+--------+-----------+------+------------+---------+---------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-06-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> SELECT ENAME,12*(SAL+COMM) FROM emp; /*发现comm为null的全部值都是NULL*/
+--------+---------------+
| ENAME | 12*(SAL+COMM) |
+--------+---------------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+---------------+
mysql> select ename, 12*(sal+ifnull(comm, 0)) from emp;
/*将comm字段中，如果是null的改为0*/
+--------+--------------------------+
| ename | 12*(sal+ifnull(comm, 0)) |
+--------+--------------------------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+--------------------------+LEAST判断最小值的使用
mysql> SELECT LEAST('a','b','c','d','e'),LEAST(100,50,80,120);
+----------------------------+----------------------+
| LEAST('a','b','c','d','e') | LEAST(100,50,80,120) |
+----------------------------+----------------------+
| a | 50 |
+----------------------------+----------------------+GREATEST判断最大值的使用
同“least”相反，在给出的范围中，判断一个最大值返回
mysql> SELECT GREATEST('a','b','c','d','e'),GREATEST(100,50,80,120);
+-------------------------------+-------------------------+
| GREATEST('a','b','c','d','e') | GREATEST(100,50,80,120) |
+-------------------------------+-------------------------+
| e | 120 |
+-------------------------------+-------------------------+
mysql> ALTER TABLE test3 ADD id INT;
mysql> UPDATE test3 SET id=100;
mysql> SELECT * FROM test3;
+------+------+
| num | id |
+------+------+
| 40.0 | 100 |
| 40.2 | 100 |
+------+------+
mysql> UPDATE test3 SET id=20 WHERE num=40.2;
mysql> SELECT *,GREATEST(num,id) FROM test3; /*判断test3表中的num字段和id字段中的最大值*/
+------+------+------------------+
| num | id | GREATEST(num,id) |
+------+------+------------------+
| 40.0 | 100 | 100.0 |
| 40.2 | 20 | 40.2 |
+------+------+------------------+BETWEEN AND介于两者之间
between 值 and 值：这两个值代表的是给定的范围。（判断值是否在给定的范围值之间，在返回1，不在返回0）
语法：判断值 between 范围值 and 范围值
如：between 10 and 20代表10-20之间的所有数字，包括10和20
如果between后的值大于and后的值，则代表除了10-20之间的数字
如：between 20 and 10代表大于等于20的值，和小于等于10的值
mysql> SELECT 10 BETWEEN 10 AND 20,20 BETWEEN 10 AND 20,30 BETWEEN 10 AND 20;
/*判断10是否在10-20之间，判断20是否在10-20之间，判断30是否在10-20*/
+----------------------+----------------------+----------------------+
| 10 BETWEEN 10 AND 20 | 20 BETWEEN 10 AND 20 | 30 BETWEEN 10 AND 20 |
+----------------------+----------------------+----------------------+
| 1 | 1 | 0 |
+----------------------+----------------------+----------------------+
mysql> SELECT 50 BETWEEN 60 AND 30;
/*判断的值的范围是大于等于AND前的值，且小于等于AND后面的值，被判定为介于两者之间，否则不在两者之间*/
+----------------------+
| 50 BETWEEN 60 AND 30 |
+----------------------+
| 0 | /*50不大于60也不小于30，所有为假命题，返回0*/
+----------------------+
mysql> SELECT * FROM test3 WHERE id BETWEEN 50 AND 100;
/*在实际的应用中，判断字段中的值是否介于50-100之间*/
+------+------+
| num | id |
+------+------+
| 40.0 | 100 |
+------+------+IN判断值是否存在给出的列表值中
判断值如果在给出的列表中，则返回1，反之为0
mysql> SELECT 10 IN (1,20,2,3,4);/*判断10是否在1,20,2,3,4这几个值中，可以看到并不在，所以返回0*/
+--------------+
| 10 IN (1,20) |
+--------------+
| 0 |
+--------------+
mysql> SELECT * FROM test3 WHERE num IN(40.2);
/*实际应用中，判断某个字段中的数值是否在给定的列表中，用来查询指定数据的一条记录*/
+------+------+
| num | id |
+------+------+
| 40.2 | 20 |
+------+------+
NOT IN
判断值是否不再给出的列表值中
mysql> SELECT 10 NOT IN (1,20,2,3,5);/*判断10是否不再1,20,2,3,5这几个数值中，可以看到确实不再，所以返回1*/
+------------------------+
| 10 NOT IN (1,20,2,3,5) |
+------------------------+
| 1 |
+------------------------+LIKE通配符查询(模糊查询)
_：匹配任意单个字符
%：匹配任意数量字符
mysql> SELECT 'tfhz' LIKE 'tf__','tfhz' LIKE '__z_','tfhz' LIKE 't%','tfhz' LIKE '%z';
+--------------------+--------------------+------------------+------------------+
| 'tfhz' LIKE 'tf__' | 'tfhz' LIKE '__z_' | 'tfhz' LIKE 't%' | 'tfhz' LIKE '%z' |
+--------------------+--------------------+------------------+------------------+
| 1 | 0 | 1 | 1 |
+--------------------+--------------------+------------------+------------------+REGEXP正则表达式
^ 匹配以什么开头的数据
$ 匹配以什么结尾的数据
. 匹配任意单个字符
[] 匹配括号内任意字符
| 匹配管道符前后任意字符串
“*” 匹配符号前后数据0次或者多次
mysql> SELECT 'tfhz' REGEXP '^t', 'tfhz' REGEXP 'z$','tfhz' REGEXP '[azb]','tfhz' REGEXP 'fz|zh','tfhz' REGEXP 't..z';
/*判断tfhz是否匹配以t开头，判断tfhz是否匹配以z结尾，判断tfhz字符串中是否有a或者z或者b其中一个*/
+--------------------+--------------------+-----------------------+-----------------------+----------------------+
| 'tfhz' REGEXP '^t' | 'tfhz' REGEXP 'z$' | 'tfhz' REGEXP '[azb]' | 'tfhz' REGEXP 'fz|zh' | 'tfhz' REGEXP 't..z' |
+--------------------+--------------------+-----------------------+-----------------------+----------------------+
| 1 | 1 | 1 | 0 | 1 |
+--------------------+--------------------+-----------------------+-----------------------+----------------------+
mysql> UPDATE test3 SET num=num*3,id=id+10 WHERE id REGEXP '^1';
/*修改test3中id以1开头中的num字段乘以3，id字段+10*/
mysql> SELECT * FROM test3;
+-------+------+
| num | id |
+-------+------+
| 120.0 | 110 |
| 40.2 | 20 |
+-------+------+
mysql> SELECT 'pjjjjmupeifeiyi' REGEXP '^pj*m.pei[sdf]ei[yi|asd]';
/*判断pjjjjmupeifeiyi是否匹配以p开头（^p),有0个或者多个j(j*)，m后面有一个未知字符（m.），
pei后面是否有sdf其中一个字符，ei后是否有yi字符串或者asd字符串*/
+-----------------------------------------------------+
| 'pjjjjmupeifeiyi' REGEXP '^pj*m.pei[sdf]ei[yi|asd]' |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+三、逻辑运算符（布尔运算，要么是，要么非）
1）逻辑非 NOT或者！
（1）当操作数为0，返回1
（2）当操作数不为0，返回0
（3）当操作数为NULL时，返回NULL
只要值为0就返回1，反之除了NULL以外全为1
mysql> SELECT NOT 0,NOT 10,! 10, ! (1+1),! NULL;
+-------+--------+------+---------+--------+
| NOT 0 | NOT 10 | ! 10 | ! (1+1) | ! NULL |
+-------+--------+------+---------+--------+
| 1 | 0 | 0 | 0 | NULL |
+-------+--------+------+---------+--------+2）逻辑与 AND或者&&
（1）当所有操作数均不为0（都不是错的），且均不为NULL返回1
（2）当一个或者多个为0，返回0（只要有一条为假的，则这个条都为错的）
（3）其余情况返回NULL
mysql> SELECT 1 AND -1,1 && 0,0 AND NULL,1 AND NULL;
/*1和-1之间不为0，也不是NULL，则返回1，1和0之间，有一个0，则返回0,
0和NULL之间，有一个0，虽然有NULL，但也返回0,1和NULL之间，1位真，但是null不能判断，所以返回null*/
+----------+--------+------------+------------+
| 1 AND -1 | 1 && 0 | 0 AND NULL | 1 AND NULL |
+----------+--------+------------+------------+
| 1 | 0 | 0 | NULL |
+----------+--------+------------+------------+3）逻辑或 OR或者||（管道符）
（1）当两个操作数均不为NULL，且任意一个操作数不为0时，返回1，否则为0
0 or 1 返回1， 0 or 0 返回0
（2）当有一个操作数为NULL，且另外一个操作数不为0，返回1，否则为NULL
NULL or 1 返回1 ，NULL or 0 返回NULL
（3）当两个操作数都为NULL，返回NULL
NULL or NULL
4）逻辑异或 XOR
（1）a XOR b 相当于 （（NOT a）AND b） or （a AND (NOT b) ）
100 XOR 50 == （（NOT 50）AND 100） or （50 AND (NOT 100) ）
not 50在逻辑非运算中，不为0，则返回0，同理not 100也返回0
逻辑与中AND前后的两个操作数只要有一个0或者多个0，则返回0，也就是只要有一条是假的，则都是错的
所以：
100 XOR 50 =（（NOT 50）AND 100） or （50 AND (NOT 100) ）
=（0 AND 100）or （50 AND 0)
= 0 or 0
= 0
（2）如果一个操作数为NULL，返回NULL
（3）如果均不为NULL，如果两个操作数都为0或者都不为0，返回0
（4）如果一个为0，另一个不为0，返回1
结论：同真同假为假（0），一真一假为真（1）
mysql> SELECT 100 XOR 50;
+------------+
| 100 XOR 50 |
+------------+
| 0 |
+------------+四、位操作运算符
运算符 说明
| 位或
& 位与
^ 位异或
>> 位右移
<< 位右移
~ 位取反1、位或（|）：
对应的二进制位只要有一个或者两个为1，则该位为1，否则为0（只有两个都为0时，结果为0，其余都为1）
例：
5 | 9
0101 1001
将5和9进行位或运算
0101
1001
按照规则可以看出结果为1101=13
mysql> SELECT 5 | 9 ;
+-------+
| 5 | 9 |
+-------+
| 13 |
+-------+27 | 19 =27
11011 10011 = 11011=27
mysql> SELECT 27 | 19;
+---------+
| 27 | 19 |
+---------+
| 27 |
+---------+2、位与（&）：
对应的二进制位都为1则为1，否则为0
例：
27 & 19 = 19
11011 & 10011 = 10011 = 19
mysql> SELECT 27 & 19;
+---------+
| 27 & 19 |
+---------+
| 19 |
+---------+47 & 13 = 13
101111 & 1101 = 1101 =13
mysql> SELECT 47 & 13;
+---------+
| 47 & 13 |
+---------+
| 13 |
+---------+23 & 8 = 0
10111 & 1000 = 0000 = 0
mysql> SELECT 23 & 8;
+--------+
| 23 & 8 |
+--------+
| 0 |
+--------+3、位异或（^）
对应的二进制位不相同则为1，相同为0
例：
30 ^ 15 = 17
11110 ^ 01111 = 10001 = 17
mysql> SELECT 30 ^ 15;
+---------+
| 30 ^ 15 |
+---------+
| 17 |
+---------+4、位右移（>>）数字会越来越小
将对应的二进制位向右移动指定的位数，右移之后，右边低位将被移除并丢弃，左边高位用0补齐
向右移几位则在该数值的二进制中在最后删除几个数值
例：
31 >> 2 将31的二进制位向右移动两位
11111 >> 右移两位 = 111 = 7
mysql> select 31 >> 2;
+---------+
| 31 >> 2 |
+---------+
| 7 |
+---------+5、位左移（<<）数字会愈来愈来大
将对应的二进制位向左移动指定的位数，左移之后，左边高位将被移除并丢弃，右边低位用0补齐
向左移几位则在该数值的二进制中在最后添加几个0
17 << 2 = 68
10001 << 2 = 1000100 = 68
mysql> select 17 << 2;
+---------+
| 17 << 2 |
+---------+
| 68 |
+---------+6、位取反（~）
将对应的二进制位逐位取反，即0变1,1变0，(mysql中最多支持64位)
例：
mysql> SELECT ~ 1; 相当于 有63位1，最后一位为0
+----------------------+
| ~ 1 |
+----------------------+
| 18446744073709551614 |
+----------------------+
mysql> SELECT ~ 1 & 5; /*简化后相当于1110 & 0101 = 0100 = 4 8*/
+---------+
| ~ 1 & 5 |
+---------+
| 4 |
+---------+五、运算顺序（优先级） 由低到高
括号会影响运算的优先级
=（赋值运算）
|| OR
XOR
&& AND
NOT
BETWEEN, =（比较运算）,<=>,>=,<=,>,<,IS NULL,LIKE,IN ,REGEXP
|
&
<< >>
^
-（减号）+
*/%
-（负号）~
博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议
本文永久链接是：https://www.feiyiblog.com/2019/12/06/6%E3%80%81mysql%E8%BF%90%E7%AE%97%E7%AC%A6/
