MySQL：第05章-运算符

1.算数运算符

算数运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加、减、乘、除、取模（%）运算

1.1 加法和减法运算

-- DUAL是伪表，可直接使用
SELECT 100,100+500,100-50,100+20-200,100+25.5,100-25.5
FROM DUAL;

运行结果：

由运算结果可以得出如下结论：

• 一个整数类型的值对整数进行相应的加法和减法运算，结果还是一个整数
• 一个整数类型的值对浮点数进行相应的加法和减法运算，结果是一个浮点数
• 加法和减法的优先级相同，进行先加法后减法和先减法后加法的运算结果是一样的
• 在MySQL中，"+"只表示数值相加。如果遇到非数值类型的数据，会先尝试转换成数值，如果转失败，就按0计算。
• MySQL中字符串的拼接要使用字符串函数CONCAT()实现

SELECT 100+'1','10'+'20','1'+100,'a'+100,'a'+'A','a'+'A'+100
FROM DUAL;

 运行结果：

 1.2 乘法和除法运算

SELECT 100*1,100*1.0,100/1,100/1.0,100+2*5/2
FROM DUAL;

运行结果：

 由运算结果可以得出如下结论：

• 一个数乘以整数1和除以整数1后仍得原数
• 一个数乘以浮点数1.0和除以浮点数1.0，数值与原数相等
• 一个数除以整数后，不管是否能除尽，结果都为一个浮点数
• 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留小数点后4位
• 乘法和除法的优先级相同，进行先乘后除与先除后乘操作，得出的结果相同
• 在数学计算中，0不能作除数，在MySQL中，一个数除以0为NULL

1.3 求模（求余）运算

SELECT 12%3,12%5,12%3.0,12.0%3
FROM DUAL;

运行结果：

 2.比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他的情况返回NULL

2.1 等号运算符

• 等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等，则返回字符串，不相等则返回0
• 在使用等号运算符时，遵循以下规则：
  • 如果等号两边的、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等
  • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小
  • 如果等号两边的值是一个整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较
  • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL
• 对比

• SELECT 1=1,1='1',1=0,'a'='a',(5+3)=(2+6),0='abc',1='abc',''=NULL,NULL=NULL
  FROM DUAL;

• 运行结果：
• 

 2.2 安全等于运算符

• 安全等于运算符（<=>）与等于运算符（=）的作用是相似的
• 区别在于，<=>是可以用来对NULL进行判断：
  • 在两个操作数均为NULL时，其返回值为1，而不为NULL
  • 当一个操作数为NULL时，其返回值为0，而不为NULL

  • SELECT 1<=>'1',1<=>0,'a'<=>'a',(5+3)<=>(2+6),''<=>NULL,NULL<=>NULL
    FROM DUAL;

  • 
• 使用安全等于运算符（<=>）时，两边的操作数均为NULL时，返回的结果为1，而不是NULL，其他返回的结果与等于运算符相同

2.3 不等于运算符

• 不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式是否不相等，如果不相等，则返回1，相等则返回0.
• 不等于运算符不能判断NULL值，如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL

• SELECT 1<>1,1!=2,'a'!='b',(3+4)<>(2+6),'a'!=NULL,NULL<>NULL
  FROM DUAL;

• 运行结果：

• 

• 此外，还有非符号类型的运算符：
• 

 2.4 空运算符

• 空运算符（IS NULL或ISNULL）判断一个值是否为NULL，如果为NULL，则返回1，否则返回0

• SELECT NULL IS NULL,ISNULL(NULL),ISNULL('a'),1 IS NULL
  FROM DUAL;

• 运行结果：

• 

 2.5 非空运算符

• 非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0

• SELECT NULL IS NOT NULL,'a' IS NOT NULL,1 IS NOT NULL
  FROM DUAL;

• 

2.6 最小值运算符

• 语法格式：

• LEAST(值1,值2,...)

• 在有两个或者多个参数的情况下，返回最小值

• SELECT LEAST(0,1,2),LEAST('b','a','c'),LEAST(1,NULL,2)
  FROM DUAL;

• 运行结果

• 

2.7 最大值运算符

• 语法格式

• GRETEST(值1,值2,...)

• 在两个或多个参数的情况下，返回最大值

• SELECT GREATEST(0,1,2),GREATEST('b','a','c'),GREATEST(1,NULL,2)
  FROM DUAL;

  运行结果

• 

2.8 BETWEEN AND运算符

•  语法格式

• SELECT D FROM TABLE WHERE C BETWEEN A AND B

  当C≥A且C≤B时，结果为1，否则为0

• SELECT 1 BETWEEN 0 AND 1,10 BETWEEN 11 AND 12,'b' BETWEEN 'a' AND 'c'
  FROM DUAL;

  运行结果

• 

2.9 IN运算符

• IN运算符用于判断给定的值是否为IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，不管IN列表中是否存在NULL，结果均为NULL

• SELECT 'a' IN ('a','b','c'),1 IN (2,3,'1'),NULL IN ('a','b'),NULL IN ('a',NULL),'a' IN ('a',NULL)
  FROM DUAL;

  运行结果：

• 

 2.10 NOT IN运算符

• NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是，则返回1，否则返回0

• SELECT 'a' NOT IN ('a','b','c'),1 NOT IN (2,3),NULL NOT IN ('a','b'),NULL NOT IN (1,NULL)
  FROM DUAL;

  运行结果

• 

 2.11 LIKE运算符

• LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件，则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL
• LIKE运算符通常是使用如下通配符
  • %：匹配0个或多个字符
  • _：只能匹配一个字符

• SELECT NULL LIKE NULL,NULL LIKE 'abc','abcd' LIKE 'abc%','abc' LIKE NULL
  FROM DUAL;

  运行结果：

• 
•  ESCAPE
  •  回避特殊符号的使用转义符

  • -- 方式一
    SELECT job_id
    FROM jobs
    WHERE job_id LIKE 'IT\_%';

  •  或

  • -- 方式二
    SELECT job_id
    FROM jobs
    WHERE job_id LIKE 'IT$_%' ESCAPE '$';

    两种方式运行结果均为：

  • 

2.12 REGEXP运算符

• REGEXP运算符用来匹配字符串，语法格式为：

• EXPR REGEXP 匹配条件

  如果expr满足匹配条件，返回1，否则返回0。如果expr或匹配条件任意一个为NULL，则结果为NULL

• REGEXP运算符在进行匹配时，常用字符匹配列

• 

1. ^：查询以特定字符或字符串开头的记录
   在fruits表中，查询f_name字段以字母'b'开头的记录：

   SELECT * 
   FROM fruits
   WHERE f_name REGEXP '^b';

2. $：查询以特定字符或字符串结束的记录
   在fruits表中，查询f_name字段以字母'y'结尾的记录：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP 'y$';

3. .：替代字符串中的任意一个字符
   在fruits表中，查询f_name字段值包含字母'a'和'g'且两个字母之间只有一个字母的记录：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP 'a.g';

4. *+：用来匹配多个字符
   *用来匹配前面的字符任意多次，包括0次
   +匹配前面的字符至少一次
   
   在fruits表中，查询f_name字段值以字母'b'开头且'b'后面出现字母'a'的记录（即：字母a出现0次或多次的情况）：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP '^ba*';

   在fruits表中，查询f_name字段值以字母'b'开头且'b'后面出现字母'a'至少一次的记录：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP '^ba+';

5. 匹配指定字符串
   方括号[]指定一个字符串集合，只匹配其中任何一个字符，即为所查找的文本
   在fruits表中，查找f_name字段中包含字母'o'或't'的记录：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP '[ot]';

   在fruits表中，查找s_id字段中包含4、5、6的记录：

   SELECT * 
   FROM fruits
   WHERE s_id REGEXP '[456]';

6. 匹配指定字符意外的字符
   "[^字符集合]"匹配不在指定集合中的任意字符
   在fruits表中，查询f_id字段中包含字母a~e和数字1~2意外字符的记录：

   SELECT f_id
   FROM fruits
   WHERE f_id REGEXP '[^a-e1-2]';

7. 使用{n,}或者{n,m}来指定字符串连续出现的次数
   "字符串{n,}"表示至少匹配n次前面的字符
   "字符串{n,m}"表示匹配前面的字符串至少n次，至多m次
   
   在fruits表中，查询f_name字段值出现字母'x'至少2次的记录：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP 'x{2,}';

   在fruits表中，查询f_name字段值出现字符串'ba'最少1次、最多3次的记录：

   SELECT *
   FROM fruits
   WHERE f_name REGEXP 'ba{1,3}';

3.逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1,0或者NULL

MySQL中支持4种逻辑运算符如下：

 3.1 逻辑非运算符

•         逻辑非（NOT 或 !）运算符表示：
•                 当给定的值为0时，返回1；
•                 当给定的值为非0时，返回0；
•                 给定的值为NULL时，返回NULL;

• SELECT NOT 1,NOT 0,NOT (1+1),NOT !1,NOT NULL
  FROM DUAL;

  运行结果

• 

3.2 逻辑与运算符

逻辑与（AND 或 &&）运算符表示：

• 当给定的值均为非0值，并且都不为NULL时，返回1
• 当给定的一个值或多个值为0时，返回0
• 当给定的值为非0值，且存在一个值或多个值为NULL时，返回NULL

• SELECT 1 AND -1,0 AND 1,0 AND NULL,1 AND NULL
  FROM DUAL;

  运行结果：

• 

3.3 逻辑或运算符

逻辑或（OR 或 ||）运算符是表示：

• 当给定的值不为NULL，并且任何一个值为非0值时，返回1，否则返回0
• 当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL
• 当两个值都为NULL时，返回NULL

• SELECT 1 OR -1,1 OR 0,1 OR NULL,0 || NULL,NULL || NULL
  FROM DUAL;

  运行结果：

• 

   注意：OR可以和AND一起使用，但在使用时，AND的优先级高于OR

3.4 逻辑异或运算符

逻辑异或（XOR）运算符表示：

• 当给定的值中任意一个值为NULL时，则返回NULL；
• 如果两个非NULL的值都为0或者都不等于0时，返回结果0；
• 如果一个值为0，另一个值不为0时，返回结果1

• SELECT 1 XOR -1,1 XOR 0,0 XOR 0,1 XOR NULL,1 XOR 1 XOR 1,0 XOR 0 XOR 0
  FROM DUAL;

  运行结果：

• 

4 位运算符

（略，使用频率较低，暂不做笔记）