MySQL运算符

运算符简介

运算符是用来连接表达式中各个操作数的符号

算术运算符,比较运算符,逻辑运算符和位运算符

算术运算符:[加减乘除和求余]

 

/        x1/x2         除法运算,返回x1除以x2的商

DIV    X1 DIV x2  除法运算,返回x1除以x2的商

%      x1%x2       求余运算,返回x1除以x2的余数

MOD   MOD(x1,x2)  求余运算,返回x1除以x2的余数

 

eg:

mysql> select * from t1;

+------+------+------+

| a    | b   | c    |

+------+------+------+

|  30 |   40 |   50 |

+------+------+------+

1 row in set (0.00 sec)

 

mysql> selecta,a+5+2,a-5-2,a*5*2 from t1;

+------+-------+-------+-------+

| a    | a+5+2 | a-5-2 | a*5*2 |

+------+-------+-------+-------+

|  30 |    37 |    23 |  300 |

+------+-------+-------+-------+

1 row in set (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的取值是否落在mn之间

IN    x1 IN (1,值2,3,n)       判断x1的取值是否是值1到值n中的一个

LIKEx1 LIKE 表达式              判断x1是否与表达式匹配

REGEXP   x1 REGEXP 正则表达式判断x1是否与正则表达式匹配

mysql> select a,a=30,a=20from t1;

+------+------+------+

| a    | a=30 | a=20 |

+------+------+------+

|  30 |    1 |    0 |

+------+------+------+

1 row in set (0.01 sec)

 

mysql> select a,a=29,NULL=NULLfrom t1;     //不能用来判断空值

+------+------+-----------+

| a    | a=29 | NULL=NULL |

+------+------+-----------+

|  30 |    0 |      NULL |

+------+------+-----------+

1 row in set (0.01 sec)

 

mysql> select a ,a<>20,a!=23,a!=NULLFROM t1;     //不能用来判断空值

+------+-------+-------+---------+

| a    | a<>20 | a!=23 | a!=NULL |

+------+-------+-------+---------+

|  30 |     1 |     1 |   NULL |

+------+-------+-------+---------+

1 row in set (0.00 sec)

 

mysql> selecta,a<=>30,NULL<=>NULL from t1;    //  <=>可以用来判断空值

+------+--------+-------------+

| a    | a<=>30 | NULL<=>NULL |

+------+--------+-------------+

|   30 |     1 |           1 |

+------+--------+-------------+

1 row in set (0.01 sec)

 

mysql> select a,a>20,null>nullfrom t1;  //不能用来判断空值

+------+------+-----------+

| a    | a>20 | null>null |

+------+------+-----------+

|   30 |   1 |      NULL |

+------+------+-----------+

1 row in set (0.01 sec)

 

mysql> select a,a>=30 ,null>=nullfrom t1;  //不能用来判断空值

+------+-------+------------+

| a    | a>=30 | null>=null |

+------+-------+------------+

|   30 |    1 |       NULL |

+------+-------+------------+

1 row in set (0.01 sec)

 

mysql> select a,a<=40,null<=nullfrom t1;  //不能用来判断空值

+------+-------+------------+

| a    | a<=40 | null<=null |

+------+-------+------------+

|   30 |    1 |       NULL |

+------+-------+------------+

1 row in set (0.00 sec)

 

mysql> select a,a is null,ais not null from t1;

+------+-----------+---------------+

| a    | a is null | a is not null |

+------+-----------+---------------+

|   30 |        0 |             1 |

+------+-----------+---------------+

1 row in set (0.00 sec)

 

mysql> select a,a between 10and 40, a between 40 and 50 from t1;

+------+---------------------+---------------------+

| a    | a between 10 and 40 | a between 40 and 50|

+------+---------------------+---------------------+

|   30 |                   1 |                   0 |

+------+---------------------+---------------------+

1 row in set (0.00 sec)

 

mysql> select a ,a in(0,20,30)from t1;

+------+---------------+

| a    | a in(0,20,30) |

+------+---------------+

|   30 |             1 |

+------+---------------+

1 row in set (0.00 sec)

 

mysql> select a,a like'beijing' from t2;   //

+---------+------------------+

| a       | a like 'beijing' |

+---------+------------------+

| beijing |                1 |

+---------+------------------+

1 row in set (0.00 sec)

 

mysql> select a,a regexp '^b'from t2;

+---------+---------------+

| a       | a regexp '^b' |

+---------+---------------+

| beijing |             1 |

+---------+---------------+

1 row in set (0.01 sec)

逻辑运算符

&& 或者 AND    可以有多个操作数进行与运算

||  或者 OR   

!   或者 NOT 

XOR             异或 //只要其中任何一个操作数为null时,结果返回null,如果x1x2都是非0的数字或者都是0时,结果返回0,如果x1x2中一个是非0,另一个是0时,结果返回1 ,所有大于-1小于1的数字都被视为逻辑0,其他数字视为逻辑1

mysql> select -1 and 2,3&&2,0 and null,3 and null;

+----------+------+------------+------------+

| -1 and 2 | 3&&2 | 0 andnull | 3 and null |

+----------+------+------------+------------+

|        1 |   1 |          0 |       NULL |

+----------+------+------------+------------+

1 row in set (0.00 sec)

 

mysql> select1||-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,!0.3,!-3,!0,!null;

+----+------+-----+----+-------+

| !1 | !0.3 | !-3 | !0 | !null |

+----+------+-----+----+-------+

| 0 |    0 |   0 |  1|  NULL |

+----+------+-----+----+-------+

1 row in set (0.01 sec)

位运算符

位运算符是在二进制数上进行计算的运算符,位运算会先将操作数变成二进制数,然后进行位运算,最后再将计算结果从二进制数变回十进制数

按位与 &,按位或|

按位取反~
      
将操作数化为二进制数后,每位都进行取反运算,1取反后变成0, 0取反后变成1

按位异或^

       每位都进行异或,相同的数异或之后结果是0,不同的数异或之后结果为1

接位左移<<和按位右移>>

 

mysql> select5&6,5&6&7;

+-----+-------+

| 5&6 | 5&6&7 |

+-----+-------+

|   4 |    4 |

+-----+-------+

1 row in set (0.00 sec)

5转换成二进制 101

6转换成二进制 110

56相与为     100   

100十进制数为 4

 

 

 

mysql> select 5|6;

+-----+

| 5|6 |

+-----+

|   7 |

+-----+

1 row in set (0.00 sec)

5转换成二进制 101

6转换成二进制 110

56相或为     111

十进制数为    7

 

 

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> select5<<2,5>>2;

+------+------+

| 5<<2 | 5>>2 |

+------+------+

|   20 |   1 |

+------+------+

1 row in set (0.01 sec)

5转换成二进制 101

左移2位变成10100

右移2位变成001

运算符的优先级

优先极              运算符                 

1            !

2            ~

3            ^

4            *,/,DIV,%,MOD

5            +,-

6            >>,<<

7            &

8            |

9            =,<=>,<,<=,>,>=,!=,<>,IN,ISNULL,LIKE,REGEXP

10           BETWEENAND ,CASE,WHEN,THEN,ELSE

11           NOT

12           &&,AND

13           ||,OR,XOR

14           :=

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

分割线
感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS