12.MySQL运算符

目录

1.算术运算符

2.比较运算符

3.逻辑运算符

4.赋值运算符

5.位运算符

6.运算符优先级

---

1.算术运算符

算术运算符适用于数值类型的数据，通常应用在SELECT查询结果的字段中使用，在WHERE条件表达式中应用较少。

运算符	描述	示例
+	加运算	SELECT 5+2;
-	减运算	SELECT 5-2;
*	乘运算	SELECT 5*2;
/	除运算	SELECT 5/2;
%	取模(余)运算	SELECT 5%2;

运算符两端的数据可以是真实的数据（如5），或数据表中的字段（如price）。

参与运算的数据一般称之为操作数，操作数与运算符组合在一起统称为表达式（如5+2）。

在MySQL中可以直接利用SELECT查看数据的运算结果。

在算术运算中，NULL是一个特殊的值，它参与的算术运算结果均为NULL

CREATE DATABASE mahaiwuji;
USE mahaiwuji;
CREATE TABLE goods (
	id INT,
	name VARCHAR (32),
	price INT
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO goods VALUES (1,'书',10);
INSERT INTO goods VALUES (2,'键盘',11);
INSERT INTO goods VALUES (3,'鼠标',15);
INSERT INTO goods VALUES (4,'手机',20);

SELECT id,name,price,price*10 AS '10个的价格' from goods;

关于算术运算，除了算术运算符外，MySQL还提供了很多进行数学运算的函数。

函数名	描述
CEIL(x)	返回大于等于x的最小整数
FLOOR(x)	返回小于等于x的最大整数
FORMAT(x,y)	返回小数点后保留y位的x（进行四舍五入）
ROUND(x[,y])	计算离x最近的整数；若设置参数y，与FORMAT(x,y)功能相同
TRUNCATE(x,y)	返回小数点后保留y位的x（舍弃多余小数位，不进行四舍五入）
ABS(x)	获取x的绝对值
MOD(x,y)	求模运算，与x%y的功能相同
PI()	计算圆周率
SQRT(x)	求x的平方根
POW(x,y)	幂运算函数，计算x的y次方，与POWER(x,y)功能相同
RAND()	默认返回0到1之间的随机数，包括0和1

CREATE TABLE circular (
	id INT,
	radius INT
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO circular VALUES (1,2);
INSERT INTO circular VALUES (2,5);
INSERT INTO circular VALUES (3,8);

SELECT radius '半径',2*radius*PI() '周长' FROM circular;

2.比较运算符

比较运算符是MySQL常用运算符之一，通常应用在条件表达式中对结果进行限定。MySQL中比较运算符的结果值有3种：

• 1 (TRUE，表示为真)

• 0 (FALSE，表示为假)

• NULL

运算符	示例
=	用于相等比较
<=>	可以进行NULL值比较的相等运算符
>	表示大于比较
<	表示小于比较
>=	表示大于等于比较
<=	表示小于等于比较
<>、!=	表示不等于比较

所有比较运算符都可以对数字和字符串进行比较，若参与比较的操作数的数据类型不同，则MySQL会自动将其转换为同类型的数据后再进行比较。

SELECT 5>='5', 3.0<>3;

比较运算符=、>、<、>=、<=、<>、!=在与NULL进行比较时，结果均为NULL。

SELECT 0 = NULL, NULL<1, NULL<>2;

运算符=与<=>均可以用于比较数据是否相等，两者的区别在于后者可以对NULL值进行比较。

SELECT NULL=NULL, NULL=1, NULL <=> NULL, NULL<=>1;

关于比较运算，除了比较运算符外，MySQL还提供了很多进行比较运算的函数。

函数名	描述
GREATEST()	返回最大的一个参数值，至少两个参数
LEAST()	返回最小的一个参数值，至少两个参数
ISNULL()	测试参数是否为空
COALESCE()	返回第一个非空参数
STRCMP()	比较两个字符串

SELECT GREATEST(1,5);
SELECT LEAST(1,5);
SELECT ISNULL(1);
SELECT ISNULL(NULL);
SELECT COALESCE(NULL,3,5);
SELECT STRCMP('aa','aa');
SELECT STRCMP('aa','ab');

3.逻辑运算符

逻辑运算符也是MySQL常用运算符之一，通常应用在条件表达式中的逻辑判断，与比较运算符结合使用。参与逻辑运算的操作数以及逻辑判断的结果只有3种，分别为1 (TRUE，表示为真)、0 (FALSE，表示为假)或NULL。

运算符	描述
AND或 &&	逻辑与，操作数全部为真，则结果为1，否则为0
OR或 ||	逻辑或，操作数中只要有一个为真，则结果为1，否则为0
NOT或 !	逻辑非，操作数为0，则结果为1，操作数为1，则结果为0
XOR	逻辑异或，操作数一个为真，一个为假，则结果为1；若操作数全部 为真或全部为假，则结果为0

一元运算符有1个操作数，二元运算符有2个操作数。

仅有逻辑非（NOT或!）是一元运算符，其余均为二元运算符。

NOT和“!” 虽然功能相同，但是在一个表达式中同时出现时，先运算“!”，再运算“NOT”。

逻辑与：只有操作数全部为真时，运算的结果才为真。

在进行逻辑与操作时，若操作数中含有NULL，而另一个操作数若为1（真），则结果为NULL，若另一个操作数为0（假），则结果为0。

SELECT * FROM goods WHERE name = '书' && price = 10;
-- 等价于
SELECT * FROM goods WHERE name = '书' AND price = 10;

SELECT 1&&NULL, NULL&&1, 0&&NULL, NULL&&0;

逻辑或：只要操作数有一个为真，则运算的结果就为真。

在进行逻辑或操作时，若操作数中含有NULL，而另一个操作数若为1（真），则结果为1；若另一个操作数为0（假），则结果为NULL。

SELECT * FROM goods WHERE name = '书' || price = 11;
-- 等价于
SELECT * FROM goods WHERE name = '书' OR price = 11;

SELECT 1||NULL, NULL||1, 0||NULL, NULL||0;

逻辑非的操作数仅有一个，当操作数为0（假）时，则运算结果为1；当操作数为1（真）时，则运算结果为0（假）；当操作数为NULL时，运算结果为NULL。

SELECT NOT 10, NOT 0, NOT NULL, NOT 0 + !0, !0 + !0;

逻辑异或操作，表示两个操作数同时都为1或0，则结果为0；若两个操作数一个为1，一个为0，则结果为1；当操作数为NULL时，则结果为NULL。

SELECT 1 XOR 2, 0 XOR 0, 0 XOR 2, NULL XOR 2;

4.赋值运算符

MySQL中“=”是一个比较特殊的运算符，既可以用于比较数据是否相等，又可以表示赋值。因此，MySQL为了避免系统分不清楚运算符“=”表示赋值还是比较的含义，特意增加一个符号“:=”用于表示赋值运算。

# “=”作为比较运算符和赋值运算符使用
UPDATE goods SET price = 1000 WHERE price = 10;
# “=”作为比较运算符使用，“:=”作为赋值运算符使用
UPDATE goods SET price := 1000 WHERE price = 11;

5.位运算符

位运算符是针对二进制数的每一位进行运算的符号，运算的结果类型为BIGINT，最大范围可以是64位。

进制转换：https://tool.oschina.net/hexconvert/

运算符	描述	示例
&	按位与	SELECT b'1001'& b'1011'; 结果为9
|	按位或	SELECT b'1001'| b'1011'; 结果为11
^	按位异或	SELECT b'1001'^ b'1011'; 结果为2
<<	按位左移	SELECT b'1001'<<2; 结果为36
>>	按位右移	SELECT b'1001'>>2; 结果为2
~	按位取反	SELECT ~b'1001'& b'1011'; 结果为2

关于位运算，除了位运算符外，MySQL还提供了很多进行位运算的函数。

函数名	描述	示例
BIT_COUNT(N)	返回在参数N中设置的比特 位（二进制位为1）的数量	SELECT BIT_COUNT(b'1011');结果为3
BIT_AND()	按位返回与的结果	SELECT BIT_AND(b1) FROM btable; 结果为0
BIT_OR()	按位返回或的结果	SELECT BIT_OR(b1) FROM btable;结果为7
BIT_XOR()	按位返回异或的结果	SELECT BIT_XOR(b1) FROM btable; 结果为5

6.运算符优先级

运算符优先级可以理解为运算符在一个表达式中参与运算的先后顺序，优先级别越高，则越早参与运算；优先级别越低，则越晚参与运算。以下是优先级排序（从高到低）：

• !

• -(一元，负号)、~(一元，按位取反)

• ^

• *、/、%、MOD

• -（相减运算符号）、+

• <<、>>

• &

• |

• =(比较运算符)、<=>、>=、>、<=、<、<>、!=

• NOT

• AND、&&

• XOR

• OR、||

• =(赋值运算符)、:=

同行的运算符具有相同的优先级，除赋值运算符从右到左运算外，其余相同级别的运算符，在同一个表达式中出现时，运算的顺序为从左到右依次进行。

若要提升运算符的优先级别，可以使用圆括号“()”，当表达式中同时出现多个圆括号时，最内层的圆括号中的表达式优先级最高。

SELECT 2+3*5, (2+3)*5;