一起学mysql 09.mysql 运算符

最新推荐文章于 2024-08-09 17:54:25 发布

隔壁小翔

最新推荐文章于 2024-08-09 17:54:25 发布

阅读量135

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/tangwei0928/article/details/98664090

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

mysql 运算符

算术运算符

符号	表达式的形式	作用
+	x1+x2+…+xn	加法运算
-	x1-x2-…-xn	减法运算
*	x1x2…*xn	乘法运算
/	x1/x2	除法运算，返回商
DIV	x1 DIV x2	除法运算，返回商，同"/"
%	x1%x2	求余运算，返回余数
MOD	x1 MOD x2	求余运算，返回余数,同"%"

mysql> create table t1(a int(5),b int(5),c int(5),d int(5),e int(5),f int(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(24,15,20,25,31,35);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+------+------+------+------+
| a    | b    | c    | d    | e    | f    |
+------+------+------+------+------+------+
|   24 |   15 |   20 |   25 |   31 |   35 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select a,a+5+2,a-5-2,a*5*2 from t1;
+------+-------+-------+-------+
| a    | a+5+2 | a-5-2 | a*5*2 |
+------+-------+-------+-------+
|   24 |    31 |    17 |   240 |
+------+-------+-------+-------+
1 row in set (0.00 sec)

mysql> select a,a/3,a DIV 3,a%3,a MOD 3 from t1;
+------+--------+---------+------+---------+
| a    | a/3    | a DIV 3 | a%3  | a MOD 3 |
+------+--------+---------+------+---------+
|   24 | 8.0000 |       8 |    0 |       0 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

mysql> select 5/0,5 DIV 0,5%0,MOD(5,0) from t1;
+------+---------+------+----------+
| 5/0  | 5 DIV 0 | 5%0  | MOD(5,0) |
+------+---------+------+----------+
| NULL |    NULL | NULL |     NULL |
+------+---------+------+----------+
1 row in set, 4 warnings (0.00 sec)

比较运算符

符号	表达式的形式	作用
=	x1=x2	判断x1是否等于x2
<>或!=	x1<>x2或x1!=x2	判断x1是否不等于x2
<=>	x1<=>x2	判断x1是否等于x2
>	x1>x2	判断x1是否大于x2
>=	x1>=x2	判断x1是否大于等于x2
<	x1<x2	判断x1是否小于x2
<=	x1<=x2	判断x1是否小于等于x2
IS NULL	x1 IS NULL	判断x1 是否等于 NULL
IS NOT NULL	x1 IS NOT NULL	判断x1 是否不等于 NULL
BETWEEN AND	x1 BETWEEN m AND n	判断x1的取值是否落在m和n之间
IN	x1 IN (值1,值2…值n)	判断x1的取值是否值1到值n中的一个
LIKE	x1 LIKE 表达式	判断x1是否与表达式匹配
REGEXP	x1 REGEXP 正则表达式	判断x1是否与正则表达式匹配

运算符"="

mysql> select a,a=24,a=20 from t1;
+------+------+------+
| a    | a=24 | a=20 |
+------+------+------+
|   24 |    1 |    0 |
+------+------+------+
1 row in set (0.34 sec)

mysql> select b = b,b = c,NULL = NULL from t1;
+-------+-------+-------------+
| b = b | b = c | NULL = NULL |
+-------+-------+-------------+
|     1 |     0 |        NULL |
+-------+-------+-------------+
1 row in set (0.00 sec)

运算符"<>“和”!="

mysql> select a,a<>23,a!=23,a!=24,a!=NULL from t1;
+------+-------+-------+-------+---------+
| a    | a<>23 | a!=23 | a!=24 | a!=NULL |
+------+-------+-------+-------+---------+
|   24 |     1 |     1 |     0 |    NULL |
+------+-------+-------+-------+---------+
1 row in set (0.00 sec)

mysql> select b<>c,b!=c from t1;
+------+------+
| b<>c | b!=c |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select b<>b,b!=c from t1;
+------+------+
| b<>b | b!=c |
+------+------+
|    0 |    1 |
+------+------+
1 row in set (0.00 sec)

运算符"<=>"

mysql> select a,a<=>24,a<=>20 from t1;
+------+--------+--------+
| a    | a<=>24 | a<=>20 |
+------+--------+--------+
|   24 |      1 |      0 |
+------+--------+--------+
1 row in set (0.00 sec)

mysql> select b<=>b,b<=>c,NULL<=>NULL,NULL<>NULL from t1;
+-------+-------+-------------+------------+
| b<=>b | b<=>c | NULL<=>NULL | NULL<>NULL |
+-------+-------+-------------+------------+
|     1 |     0 |           1 |       NULL |
+-------+-------+-------------+------------+
1 row in set (0.00 sec)

运算符">"

mysql> select a,a>24,a>23 from t1;
+------+------+------+
| a    | a>24 | a>23 |
+------+------+------+
|   24 |    0 |    1 |
+------+------+------+
1 row in set (0.00 sec)

mysql> select b>c,b*c>b*b,NULL>NULL from t1;
+------+---------+-----------+
| b>c  | b*c>b*b | NULL>NULL |
+------+---------+-----------+
|    0 |       1 |      NULL |
+------+---------+-----------+
1 row in set (0.00 sec)

运算符">="

mysql> select a,a>=25,a>=23 from t1;
+------+-------+-------+
| a    | a>=25 | a>=23 |
+------+-------+-------+
|   24 |     0 |     1 |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> select b>=c,b*c>=b*b,NULL>=NULL from t1;
+------+----------+------------+
| b>=c | b*c>=b*b | NULL>=NULL |
+------+----------+------------+
|    0 |        1 |       NULL |
+------+----------+------------+
1 row in set (0.00 sec)

运算符"<"

mysql> select a,a<25,a<23 from t1;
+------+------+------+
| a    | a<25 | a<23 |
+------+------+------+
|   24 |    1 |    0 |
+------+------+------+
1 row in set (0.00 sec)

mysql> select b<c,b*c<b*b,NULL<NULL from t1;
+------+---------+-----------+
| b<c  | b*c<b*b | NULL<NULL |
+------+---------+-----------+
|    1 |       0 |      NULL |
+------+---------+-----------+
1 row in set (0.00 sec)

运算符"<="

mysql> select b<=c,b*c<=b*b,NULL<=NULL from t1;
+------+----------+------------+
| b<=c | b*c<=b*b | NULL<=NULL |
+------+----------+------------+
|    1 |        0 |       NULL |
+------+----------+------------+
1 row in set (0.00 sec)

mysql> select a,a<=25,a<=23 from t1;
+------+-------+-------+
| a    | a<=25 | a<=23 |
+------+-------+-------+
|   24 |     1 |     0 |
+------+-------+-------+
1 row in set (0.00 sec)

运算符"IS NULL"

mysql> select a,a IS NULL,a IS NOT NULL from t1;
+------+-----------+---------------+
| a    | a IS NULL | a IS NOT NULL |
+------+-----------+---------------+
|   24 |         0 |             1 |
+------+-----------+---------------+
1 row in set (0.00 sec)

运算符"BETWEEN AND"

mysql> select a,a BETWEEN 20 AND 28, a BETWEEN 25 AND 29 from t1;
+------+---------------------+---------------------+
| a    | a BETWEEN 20 AND 28 | a BETWEEN 25 AND 29 |
+------+---------------------+---------------------+
|   24 |                   1 |                   0 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select 'b' BETWEEN 'a' AND 'd', 'b' BETWEEN 'e' AND 'z';
+-------------------------+-------------------------+
| 'b' BETWEEN 'a' AND 'd' | 'b' BETWEEN 'e' AND 'z' |
+-------------------------+-------------------------+
|                       1 |                       0 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

运算符"IN"

mysql> select a,a IN(2,20,24,25),a IN(3,5,7) from t1;
+------+------------------+-------------+
| a    | a IN(2,20,24,25) | a IN(3,5,7) |
+------+------------------+-------------+
|   24 |                1 |           0 |
+------+------------------+-------------+
1 row in set (0.00 sec)

mysql> select 'a' IN('a','b','c'),'a' IN('c','d');
+---------------------+-----------------+
| 'a' IN('a','b','c') | 'a' IN('c','d') |
+---------------------+-----------------+
|                   1 |               0 |
+---------------------+-----------------+
1 row in set (0.00 sec)

运算符"LIKE"

mysql> select a,a like '2%',a like '2_',a like '__',a like '3_' from t1;
+------+-------------+-------------+-------------+-------------+
| a    | a like '2%' | a like '2_' | a like '__' | a like '3_' |
+------+-------------+-------------+-------------+-------------+
|   24 |           1 |           1 |           1 |           0 |
+------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

运算符"REGEXP"

mysql> select a,a REGEXP '2.',a REGEXP '.*',a REGEXP '^2',a REGEXP '4$',a REGEXP '3.' from t1;
+------+---------------+---------------+---------------+---------------+---------------+
| a    | a REGEXP '2.' | a REGEXP '.*' | a REGEXP '^2' | a REGEXP '4$' | a REGEXP '3.' |
+------+---------------+---------------+---------------+---------------+---------------+
|   24 |             1 |             1 |             1 |             1 |             0 |
+------+---------------+---------------+---------------+---------------+---------------+
1 row in set (0.00 sec)

逻辑运算符

符号	名称	符号	名称
&&或者AND	与	!或者NOT	非
\|\|或者OR	或	XOR	异或

与运算

mysql> select -1&&2&&3, 0&&3, 0&&NULL, 3&&NULL;
+----------+------+---------+---------+
| -1&&2&&3 | 0&&3 | 0&&NULL | 3&&NULL |
+----------+------+---------+---------+
|        1 |    0 |       0 |    NULL |
+----------+------+---------+---------+
1 row in set (0.00 sec)

mysql> select -1 AND 2 AND 3, 0 AND 3, 0 AND NULL, 3 AND NULL;
+----------------+---------+------------+------------+
| -1 AND 2 AND 3 | 0 AND 3 | 0 AND NULL | 3 AND NULL |
+----------------+---------+------------+------------+
|              1 |       0 |          0 |       NULL |
+----------------+---------+------------+------------+
1 row in set (0.00 sec)

或运算

mysql> select 1||-1||NULL||0, 3||NULL, 0||NULL, NULL||NULL, 0||0;
+----------------+---------+---------+------------+------+
| 1||-1||NULL||0 | 3||NULL | 0||NULL | NULL||NULL | 0||0 |
+----------------+---------+---------+------------+------+
|              1 |       1 |    NULL |       NULL |    0 |
+----------------+---------+---------+------------+------+
1 row in set (0.00 sec)

mysql> select 1 OR -1 OR NULL OR 0, 3 OR NULL, 0 OR NULL, NULL OR NULL, 0 OR 0;
+----------------------+-----------+-----------+--------------+--------+
| 1 OR -1 OR NULL OR 0 | 3 OR NULL | 0 OR NULL | NULL OR NULL | 0 OR 0 |
+----------------------+-----------+-----------+--------------+--------+
|                    1 |         1 |      NULL |         NULL |      0 |
+----------------------+-----------+-----------+--------------+--------+
1 row in set (0.00 sec)

非运算

mysql> select !1,!-3,!0,!NULL;
+----+-----+----+-------+
| !1 | !-3 | !0 | !NULL |
+----+-----+----+-------+
|  0 |   0 |  1 |  NULL |
+----+-----+----+-------+
1 row in set (0.00 sec)

mysql> select NOT 1,NOT -3,NOT 0,NOT NULL;
+-------+--------+-------+----------+
| NOT 1 | NOT -3 | NOT 0 | NOT NULL |
+-------+--------+-------+----------+
|     0 |      0 |     1 |     NULL |
+-------+--------+-------+----------+
1 row in set (0.00 sec)

异或运算

mysql> select NULL XOR 1, NULL XOR 0, 3 XOR 1, 1 XOR 0, 0 XOR 0, 3 XOR 2 XOR 0 XOR 1;
+------------+------------+---------+---------+---------+---------------------+
| NULL XOR 1 | NULL XOR 0 | 3 XOR 1 | 1 XOR 0 | 0 XOR 0 | 3 XOR 2 XOR 0 XOR 1 |
+------------+------------+---------+---------+---------+---------------------+
|       NULL |       NULL |       0 |       1 |       0 |                   1 |
+------------+------------+---------+---------+---------+---------------------+
1 row in set (0.00 sec)

位运算符

符号	名称	符号	名称
&	按位与	^	按位异或
\|	按位或	<<	按位左移
~	按位取反	>>	按位右移

按位与

mysql> select 5&6, 5&6&7;
+-----+-------+
| 5&6 | 5&6&7 |
+-----+-------+
|   4 |     4 |
+-----+-------+
1 row in set (0.00 sec)

按位或

mysql> select 5|6, 5|6|7;
+-----+-------+
| 5|6 | 5|6|7 |
+-----+-------+
|   7 |     7 |
+-----+-------+
1 row in set (0.00 sec)

按位取反

mysql> select ~1;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)

mysql> select BIN(~1);
+------------------------------------------------------------------+
| BIN(~1)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

按位异或

mysql> select 5^6;
+-----+
| 5^6 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

按位左移

mysql> select 5<<2;
+------+
| 5<<2 |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

按位右移

mysql> select 5>>2;
+------+
| 5>>2 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

隔壁小翔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一起学mysql 09.mysql 运算符

mysql 运算符算术运算符符号表达式的形式作用+x1+x2+…+xn加法运算-x1-x2-…-xn减法运算*x1*x2*…*xn乘法运算/x1/x2除法运算，返回商DIVx1 DIV x2除法运算，返回商，同"/"%x1%x2求余运算，返回余数MODx1 MOD x2求余运算，返回余数,同"%"mys...
复制链接

扫一扫

专栏目录