0 0 0

计算字段及数据处理函数-运算符

ZAESKY
2020-1-29 2188

一、算数运算符

算术运算符是MySQL中最基本的运算符,主要用于执行数值运算

算术运算符及其作用

MySQL中算术运算符的运算法则与数学中的运算法则完全相同。

实例1:执行SQL语句,获取各种算术运算结果,执行结果如下

mysql> SELECT 4+2,4-2,4*2,4/2,4%3;
+--------------+--------------+--------------+-------------+--------------+
| 4+2  | 4-2  | 4*2  | 4/2     | 4%3   |
+--------------+--------------+-------------+--------------+--------------+
|  6   |   2  |  8  | 2.0000  |   1   |
+--------------+--------------+-------------+--------------+--------------+
1 row in set (0.02 sec)

在除法运算和求余运算中,如果除数为0将是非法运算,返回结果为NULL,如下所示。

mysql> SELECT 6/0,9%0;
+--------+--------+
| 6/0     | 9%0  |
+--------+--------+
| NULL | NULL |
+--------+--------+
1 row in set (0.00 sec)

运算符不仅可以直接操作数值,还可以操作表中的字段。

实例2:执行SQL语句,根据月薪值,计算8.1节创建的staff表中员工的年薪。

步骤1 执行以下SQL语句,选择数据库staff

USE staff;

步骤2 执行SQL语句,根据月薪值,计算staff表中员工的年薪值。执行结果如下

mysql> SELECT name AS 员工姓名,money AS 薪资,money*12 AS 年薪 FROM staff;
+--------------+--------------+----------------+
| 员工姓名  | 薪资  | 年薪     |
+--------------+--------------+----------------+
| 刘长生     | 20000.00  | 240000.00  |
| 赵霞         | 10000.00   | 120000.00  |
| 季庆奇     | 15000.00   | 180000.00  |
| 李星宇     | 15000.00   | 180000.00  |
| 张向阳     | 15000.00   | 180000.00  |
| 张旭         | 10000.00   | 120000.00  |
+--------------+--------------+----------------+
6 rows in set (0.03 sec)


二、比较运算符

比较运算符的作用是将表达式中的两个操作数进行比较,比较结果为真,则返回1,为假则返回0,结果不确定则返回NULL

比较运算符及其作用

常用比较运算符包括实现相等比较的运算符“=”和“<=>”,实现不相等比较的运算符“<>”和“!=”,实现大于和大于等于比较的运算符“>”和“>=”,以及实现小于和小于等于比较的运算符“<”和“<=”

2.1 等于运算符

实例3:执行使用“=”和“<=>”比较运算符的SQL语句,了解这些运算符的作用。

mysql> SELECT 0=1,1=1,0.1=1,1='1','a'='a',(1+2)=(2+1),NULL=NULL,NULL<=>NULL;
+-----+--------+--------+--------+--------+-----------------+----------------------+----------------------+
|0=1 | 1=1   | 0.1=1 | 1='1‘  | 'a'='a' | (1+2)=(2+1) | NULL=NULL      | NULL<=>NULL  |
+-----+--------+--------+--------+--------+-----------------+----------------------+----------------------+
|  0   |  1   |    0     |    1    |    1     |        1     |      NULL              |         1     |
+-----+--------+--------+--------+--------+-----------------+----------------------+----------------------+
1 row in set (0.07 sec)

提示:如果两个操作数中有一个或两个值为NULL(空值),结果为空;如果两个操作数分别为字符串和数值,系统会首先将字符串转换成数值,然后再进行比较。


2.2 等于运算符

<>”和“!=”用于判断数字、字符串和表达式是否不相等,如果不相等则返回1,否则返回0。其用法与等于运算符用法相同,但不能操作NULL(空值)

实例4:执行使用“<>”和“!=”比较运算符的SQL语句,了解这些运算符的作用。

mysql> SELECT 1<>2,2!=2,1.5<>1,'abc'<>'ab',(1+2)!=(1+1);
+--------------+--------------+--------------+----------------+---------------------------+
| 1<>2  | 2!=2  | 1.5<>1  | 'abc'<>'ab‘   | (1+2)!=(1+1)  |
+--------------+--------------+--------------+----------------+---------------------------+
|  1    |    0  |      1  |          1     |            1  |
+--------------+--------------+--------------+----------------+---------------------------+
1 row in set (0.00 sec)


2.3 其他常用比较运算符

<”“>”“<=”和“>=”4种运算符用于比较数字、字符串和表达式,如果比较结果为真则返回1,否则返回0。这些运算符也不能操作NULL(空值)。

实例5:执行使用“<”“>”“<=”和“>=”比较运算符的SQL语句,了解这些运算符的作用。

mysql> SELECT 1<1,2>1,1.5<2,'a'<'aaa',(1+2)<=(1+2);
+--------------+--------------+--------------+--------------+-----------------------------+
| 1<1  | 2>1  | 1.5<2  | 'a'<'aaa'   | (1+2)<=(1+2)   |
+--------------+--------------+--------------+--------------+-----------------------------+
|   0  |   1  |    1  |      1  |          1     |
+--------------+--------------+--------------+--------------+-----------------------------+

提示:如果使用上述4种运算符进行比较的两个操作数为字符串,系统会比较两个字符串的长度,但两个操作数不能一个为数值,一个为字符串。

实现特殊功能的比较运算符主要包括“BETWEEN AND”“IN”“IS NULL”“LIKE”和“REGEXP”,前面几种已经在7.2节进行了介绍,本节主要介绍实现正则表达式匹配的REGEXP运算符的应用

REGEXP通配符及其作用

实例6:使用运算符REGEXP进行字符串匹配运算,执行结果如下所示:

mysql> SELECT 'abc' REGEXP '^a','abc' REGEXP 'c$','abc' REGEXP '.bc','abc' REGEXP '[xy]';
+-------------------------+--------------------------+----------------------------+-------------------------+
| 'abc' regexp '^a‘    | 'abc' regexp 'c$'      | 'abc' regexp '.bc'         | 'abc' regexp '[xy]'     |
+-------------------------+--------------------------+----------------------------+-------------------------+
|   1               |   1           |   1            |   0    |
+-------------------------+--------------------------+----------------------------+-------------------------+
1 row in set (0.00 sec)

实例7:使用通配符“*”和“+”匹配符号前面的字母出现的次数,SQL语句及其执行结果如下:

mysql> SELECT 'bcd' REGEXP 'a*d','bcd' REGEXP 'cc*d','bcd' REGEXP 'c+d','bcd' REGEXP 'b+d';
+------------------------------+-----------------------------+------------------------------+-----------------------------+
| 'bcd' regexp 'a*d‘  | 'bcd' regexp 'cc*d'   | 'bcd' regexp 'c+d'  | 'bcd' regexp 'b+d'  |
+------------------------------+-----------------------------+------------------------------+-----------------------------+
|             1   |              1  |        1  |             0  |
+------------------------------+-----------------------------+------------------------------+-----------------------------+
1 row in set (0.00 sec)


三、逻辑运算符

逻辑运算符又称为布尔运算符,用于确定表达式的真和假。表9-4列出了MySQL中可以使用的逻辑运算符。

逻辑运算符

&&”和“AND”表示逻辑与运算,当所有操作数均为非零值,并且不为NULL时,返回值为1;当一个或多个操作数为0时,返回值为0;当任何一个操作数为NULL,其他操作数为非零值时,返回值为NULL

实例8:使用“&&”或“AND”运算符进行逻辑判断,理解其应用。SQL语句及其执行结果如下:

mysql> SELECT 1 && 1,1 AND 0,1 AND NULL,0 AND NULL;
+--------------+--------------+-----------------+--------------------------+
| 1 && 1  | 1 and 0  | 1 and NULL  | 0 and NULL  |
+--------------+--------------+-----------------+--------------------------+
|      1  |     0  |   NULL      |          0           |
+--------------+--------------+-----------------+--------------------------+
1 row in set (0.05 sec)

提示:使用“&&”和“AND”运算符可以有多个操作数同时进行与运算。例如,1 && 2 && 3


||”和“OR”表示逻辑或运算,当所有操作数均为非NULL值时,如有任意一个操作数为非零值,返回值为1;当一个操作数为非零值,另外的操作数为NULL时,返回值为1;当所有操作数为NULL时,返回值为NULL;当所有操作数均为0时,返回值为0

实例9:使用“||”或“OR”运算符进行逻辑判断,理解其应用。SQL语句及其执行结果如下:

mysql> SELECT 1 || 1,1 OR 0,0 OR 0,1 OR NULL,0 OR NULL,NULL OR NULL;
+--------------+--------------+--------------+----------------+----------------+--------------------------+
| 1 || 1  | 1 OR 0  | 0 OR 0  | 1 OR NULL | 0 OR NULL | NULL OR NULL   |
+--------------+--------------+--------------+----------------+----------------+--------------------------+
|      1  |      1  |      0  |         1     |      NULL     |       NULL   |
+--------------+--------------+--------------+----------------+----------------+--------------------------+
1 row in set (0.01 sec)


!”和“NOT”表示逻辑非运算,返回和操作数相反的结果。当操作数为0时,返回值为1;当操作数为非零值时,返回值为0;当操作数为NULL时,返回值为NULL

实例10:使用“!”或“NOT”运算符进行逻辑判断,理解其应用。SQL语句及其执行结果如下:

mysql> SELECT !0,NOT 1,NOT NULL;
+--------------+--------------+-----------------------------+
| !0  | NOT 1  | NOT NULL  |
+--------------+--------------+-----------------------------+
|  1  |     0  |     NULL    |
+--------------+--------------+-----------------------------+
1 row in set (0.01 sec)


XOR”表示逻辑异或运算,当两个操作数同为0或者同为非零值时,返回值为0;当两个操作数一个为非零值,一个为0时,返回值为1;当任意一个操作数为NULL时,返回值为NULL

使用“XOR”运算符进行逻辑判断,理解其应用。SQL语句及其执行结果如下:

mysql> SELECT 1 XOR 1,0 XOR 0,1 XOR 0,1 XOR NULL;
+----------------+---------------+--------------+--------------------------+
| 1 XOR 1    | 0 XOR 0    | 1 XOR 0    | 1 XOR NULL  |
+----------------+---------------+--------------+--------------------------+
|      0    |       0      |       1       |       NULL  |
+----------------+---------------+--------------+--------------------------+
1 row in set (0.05 sec)


四、位运算符

位运算符是将给定的操作数转换为二进制数,然后对各个操作数的每一位进行指定的逻辑运算,最后将二进制结果转换为十进制数,得到位运算的结果。表9-5列出了MySQL中可以使用的位运算符。

位运算符

位与运算是将操作数转换为二进制数后进行按位与运算。在这种运算中,如果对应的二进制位全部为1,则该位的运算结果为1,其他情况运算结果为0

实例12:使用“&”运算符进行逻辑运算,理解其应用。SQL语句及其执行结果如下

mysql> SELECT 5&6,2&3&6;
+--------------+-----------------+
| 5&6   | 2&3&6      |
+--------------+-----------------+
|   4   |     2      |
+--------------+-----------------+
1 row in set (0.03 sec)

由执行结果可知,5进行二进制转换后是01016进行二进制转换后是0110,运算结果是0100,转换为十进制数是42进行二进制转换后是00103进行二进制转换后是00116进行二进制转换后是0110,运算结果是0010,转换为十进制数是2


位或运算是将操作数转换为二进制数后进行按位或运算。在这种运算中,如果对应的二进制位有一个或多个为1,则该位的运算结果为1,其他情况运算结果为0

实例13:使用“|”运算符进行逻辑运算,理解其应用。SQL语句及其执行结果如下

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

由执行结果可知,5进行二进制转换后是01016进行二进制转换后是0110,运算结果是0111,转换为十进制数是72进行二进制转换后是00103进行二进制转换后是00116进行二进制转换后是0110,运算结果是0111,转换为十进制数是7


位异或运算是将操作数转换为二进制数后进行按位异或运算。在这种运算中,如果对应的二进制位不相同,则该位的运算结果为1,否则为0

实例14:使用“^”运算符进行逻辑运算,理解其应用。SQL语句及其执行结果如下

mysql> SELECT 10^15,2^2;
+--------------+--------------+
| 10^15  | 2^2  |
+--------------+--------------+
|     5  |   0  |
+--------------+--------------+
1 row in set (0.00 sec)

由执行结果可知,10进行二进制转换后是101015进行二进制转换后是1111,运算结果是0101,转换为十进制数是52进行二进制转换后是0010,运算结果是0000,转换为十进制数是0


位左移和位右移运算是将操作数转换为二进制数后,使二进制位全部左移或右移指定的位数,如果向左移则右边补0,如果向右移则左边补0,移出的位数将被抛弃,最后将移动后的结果转换成十进制数即可。

实例15:使用“<<”和“>>”运算符进行逻辑运算,理解其应用。SQL语句及其执行结果如下

mysql> SELECT 1<<2,5<<1,2>>1,5>>1;
+----------+----------+-----------+-------+
| 1<<2  | 5<<1   | 2>>1  | 5>>1 |
+----------+----------+-----------+-------+
|    4  |   10  |    1  |    2 |
+----------+----------+-----------+-------+
1 row in set (0.03 sec)

由结果可知,1进行二进制转换后是0001,左移两位后是0100,转换为十进制数是45进行二进制转换后是0101,左移一位后是1010,转换为十进制数是10

2进行二进制转换后是0010,右移一位后是0001,转换为十进制数是15进行二进制转换后是0101,右移一位后是0010,转换为十进制数是2

位取反运算是将操作数转换为二进制数后,对二进制数进行逐位反转,即1取反后变00取反后变1

实例16:使用“~”运算符进行逻辑运算,理解其应用。SQL语句及其执行结果如下

mysql> SELECT~1,BIN(~1);
+---------------------------------------------+----------------------------------------------------------------------------------------------+
|     ~1  |BIN(~1)                                    |
+---------------------------------------------+----------------------------------------------------------------------------------------------+
|18446744073709551614  |1111111111111111111111111111111111111111111111111111111111111110 |
+---------------------------------------------+----------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

看到这个结果读者可能会很吃惊,对1进行位取反怎么会是这么大的数字呢?在MySQL中,常量数字默认会用8个字节来表示,8个字节就是64位。也就是说,常量1的二进制数是由63个“0”1个“1”组成,可以简写成0001,但在计算中64位会全部取反,所以运算结果由63个“1”1个“0”组成,转换为十进制数后就是18446744073709551614

提示:BIN(n)函数返回n的二进制值的字符串表示,其中n是一个长整型(BIGINT)数。


五、运算符的优先级

在实际应用中,经常会使用多个运算符进行混合运算,那么应该先执行哪些运算符的操作呢?MySQL制定的运算符优先级决定了运算符在表达式中执行的先后顺序。表9-6按照优先级由低到高的顺序,列出了所有的运算符,同一级别中的运算符优先级相同。

运算符优先级

请先登录后发表评论!

最新回复 (0)

    暂无评论

返回
请先登录后发表评论!