MySQL 函数和操作符

MySQL 函数和操作符

一、实验介绍

1.1 实验内容

本次实验主要介绍了 MySQL 参考手册中的一些基本函数和操作符的用法。

1.2 实验知识点

• 基本函数
• 操作符

1.3 实验环境

课程使用的实验环境为 Ubuntu Linux 14.04 64 位版本。实验中会用到程序：

• Mysql 5.5.50
• Xfce终端

二、实验步骤

2.1 操作符

2.1.1 操作符优先级

以下列表显示了操作符优先级的由低到高的顺序。排列在同一行的操作符具有相同的优先级。

其中，部分操作符的优先级取决于SQL的模式：

• 默认情况下，||是逻辑运算符OR。当启用PIPES_AS_CONCAT模式时，||就是一个字符串连接符，优先级处于^与一元运算符之间。
• 默认情况下，!的优先级高于NOT。但当启用HIGH_NOT_PRECEDENCE模式时，!和NOT拥有相同的优先级。

2.1.2 圆括号( ... )

使用括弧来规定表达式的运算顺序，例如：

mysql> SELECT 1+2*3;

mysql> SELECT (1+2)*3;

2.1.3 比较函数和操作符

比较运算产生的结果为1(TRUE)、0 (FALSE)或NULL。这些运算可用于数字和字符串。如果必要的话，字符串可自动转换为数字，而数字也可自动转换为字符串。

本节中的一些函数（如LEAST()和GREATEST()）的返回值并不会返回1(TRUE)、 0 (FALSE)和NULL这样的结果。但是按照下述规则函数进行比较运算后其返回值可以为以上结果：

MySQL按照以下规则进行数值比较：

• 若函数中有一个或两个参数都是NULL，则比较运算的结果为NULL，除非是等号比较运算符<=>。
• 若同一个比较运算中的两个参数都是字符串类型，则作为字符串进行比较。
• 若两个参数均为整数，则按照整数进行比较。
• 十六进制值在不作为数字进行比较时，则按照二进制字符串处理。
• 假如参数中的一个为TIMESTAMP或DATETIME数据类型，而其它参数均为常数，则在进行比较前应该将常数转为timestamp类型。这样做的目的是为了使ODBC的进行更加顺利。注意：这不用于IN()中的参数!为了更加可靠，在进行对比时通常使用完整的datetime/date/time字符串。
• 在其它情况下，参数作为浮点数（实数）进行比较。

在默认状态下，字符串比较不区分大小写，并使用现有字符集(默认为cp1252 Latin1，同时也适用于英语)。

为了达到比较的目的，可使用CAST()函数将某个值转为另外一种类型。使用CONVERT()可以将字符串值转为不同的字符集。

下面对各类操作符的使用进行示例：

• = 等于：

  mysql> SELECT 1 = 0;
  
  mysql> SELECT '0' = 0;
  
  mysql> SELECT '0.01' = 0;
  

  

  对于行比较，(a, b) = (x, y)相当于：(a = x) AND (b = y)。

• <=> 空值安全的等号：

  这个操作符与=操作符执行相同的比较操作，不过在两个操作码均为NULL时，其返回至为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。

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

  

  对于行比较，(a, b) <=> (x, y)相当于：(a <=> x) AND (b <=> y)。

• <> 或 != 不等于：

  mysql> SELECT '.01' <> '0.01';
  
  mysql> SELECT .01 <> '0.01';
  
  mysql> SELECT 'zapp' <> 'zappp';
  

  

  对于行比较，(a, b) <> (x, y)相当于：(a <> x) OR (b <> y)。

• <= 小于等于：

  mysql> SELECT 0.1 <= 2;
  

  对于行比较，(a, b) <= (x, y)相当于：(a <= x) AND (b <= y)。

• > 大于：

  mysql> SELECT 2 > 2;
  

  对于行比较，(a, b) > (x, y)相当于：(a > x) AND (b > y)。

• IS boolean_value和IS NOT boolean_value：根据一个布尔值来检验一个值，在这里，布尔值可以是TRUE、FALSE或UNKNOWN。

  mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
  
  mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
  
  # IS NULL 和 IS NOT NULL 检验一个值是否为 NULL。
  
  mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
  
  mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
  

  

• expr BETWEEN min AND max 假如expr大于或等于min且expr小于或等于max, 则BETWEEN的返回值为1，否则是0。若所有参数都是同一类型，则上述关系相当于表达式 ：min <= expr AND expr <= max。其它类型的转换 根据本章开篇所述规律进行，且适用于3种参数中任意一种。

  mysql> SELECT 1 BETWEEN 2 AND 3;
  
  mysql> SELECT 'b' BETWEEN 'a' AND 'c';
  
  mysql> SELECT 2 BETWEEN 2 AND '3';
  

  

  expr NOT BETWEEN min AND max这相当于NOT(expr BETWEEN min AND max)。

• COALESCE(value,...) 返回参数列表当中的第一个非NULL值，在没有非NULL值的情况下返回值为NULL。

  mysql> SELECT COALESCE(NULL,1);
  
  mysql> SELECT COALESCE(NULL,NULL,NULL);
  

  

• GREATEST(value1,value2,...)当有2个或2个以上参数时，返回值为最大(最大值的)参数。比较参数所依据的规律同LEAST()相同。

  mysql> SELECT GREATEST(2,0);
  
  mysql> SELECT GREATEST('B','A','C');
  

  

  在所有参数为NULL的情况下，GREATEST()的返回值为NULL。

• expr IN (value,...)若expr为IN列表中的任意一个值，则其返回值为1, 否则返回值为0。假如所有的值都是常数，则其计算和分类根据 expr的类型进行。这时，使用二分搜索来搜索信息。如果IN值列表全部由常数组成，则意味着IN的速度非常快。如果expr是一个区分大小写的字符串表达式，则字符串比较也按照区分大小写的方式进行。

  mysql> SELECT 2 IN (0,3,5,'wefwf');
  
  mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
  

  尝试输入上述语句并分析结果，思考第二条语句的可行性。

  IN列表中所列值的个数仅受限于max_allowed_packet值。

  为了同SQL标准相一致，在左侧表达式为NULL的情况下，或是表中找不到匹配项或是表中一个表达式为NULL的情况下，IN的返回值均为NULL。

  IN()语法也可用于书写某些类型的子查询。

• expr NOT IN (value,...)这与NOT (expr IN (value,...))相同。

  ISNULL(expr)如果expr为NULL，那么ISNULL()的返回值为1，否则返回值为0。

  mysql> SELECT ISNULL(1+1);
  
  mysql> SELECT ISNULL(1/0);
  

  通常使用ISNULL()来判断一个值是否为NULL。（使用=比较符对比一个值与NULL值是错误的）。

• INTERVAL(N,N1,N2,N3,...)假如N < N1，则返回值为0；假如N < N2 等，则返回值为1；假如N为NULL，则返回值为-1。所有的参数均按照整数处理。为了这个函数的正确运行，必须满足N1 < N2 < N3 < ……< Nn 。其原因是使用了二分查找(极快速)。

  mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
  
  mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
  
  mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
  

2.1.4 逻辑操作符

在SQL中，所有逻辑操作符的计算所得结果均为TRUE、FALSE或NULL(UNKNOWN)。在MySQL中，它们的表达形式为1 (TRUE)、 0 (FALSE)和NULL，这在不同SQL数据库服务器上都是通用的，然而有一些服务器对TRUE的返回值可能是任意一个非零值。

• NOT，! ：逻辑NOT。

  当操作数为0时，所得值为1 ；当操作数为非零值时，所得值为0，而当操作数为NOT NULL时，所得的返回值为NULL。

  mysql> SELECT NOT 10;
  
  mysql> SELECT NOT 0;
  
  mysql> SELECT NOT NULL;
  
  mysql> SELECT ! (1+1);
  
  mysql> SELECT ! 1+1;
  

  尝试运行上面的语句，发现最后一个例子产生的结果为 1，原因是表达式的计算方式和(!1)+1相同。

• AND，&&：逻辑AND。

  当所有操作数均为非零值、并且不为NULL时，计算所得结果为1，当一个或多个操作数为0时，所得结果为0，其余情况返回值为NULL。

  mysql> SELECT 1 && 1;
  
  mysql> SELECT 1 && 0;
  
  mysql> SELECT 1 && NULL;
  
  mysql> SELECT 0 && NULL;
  

• OR，||：逻辑OR。

  当两个操作数均为非NULL值时，如有任意一个操作数为非零值，则结果为1，否则结果为0。当有一个操作数为NULL时，如另一个操作数为非零值，则结果为1，否则结果为NULL。假如两个操作数均为NULL，则所得结果为NULL。

  mysql> SELECT 1 || 1;
  
  mysql> SELECT 1 || 0;
  
  mysql> SELECT 0 || 0;
  
  mysql> SELECT 0 || NULL;
  
  mysql> SELECT 1 || NULL;
  

• XOR：逻辑XOR。

  当任意一个操作数为NULL时，返回值为NULL。对于非NULL的操作数，假如有奇数个操作数为非零值，则计算所得结果为 1 ，否则为 0 。

  mysql> SELECT 1 XOR 1;
  
  mysql> SELECT 1 XOR 0;
  
  mysql> SELECT 1 XOR NULL;
  
  mysql> SELECT 1 XOR 1 XOR 1;
  

  a XOR b的计算等同于(a AND (NOT b)) OR ((NOT a)和 b)。

2.2 控制流程函数

• CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END

  CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

  在上面第一条语句返回的是value=compare-value的结果。而第二条语句的返回结果是第一条语句的真正的结果。如果没有匹配的结果值，则返回结果为ELSE语句后的结果，如果没有ELSE部分，则返回值为NULL。

  mysql> SELECT CASE 1 WHEN 1 THEN 'one'
   -> WHEN 2 THEN 'two' ELSE 'more' END;
  
  mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
  
  mysql> SELECT CASE BINARY 'B'
   -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
  

  一个CASE表达式的默认返回值类型是任何返回值的兼容类型，但具体情况视其所在语境而定。如果用在字符串语境中，则返回结果为字符串类型。如果用在数字语境中，则返回结果为十进制值、实数值或整数值。

• IF(expr1,expr2,expr3)

  如果expr1是TRUE(expr1 <> 0 and expr1 <> NULL)，则IF()的返回值为expr2; 否则返回值则为expr3。IF()的返回值是否为数字值或字符串值，具体情况视其所在语境而定。

  mysql> SELECT IF(1>2,2,3);
  
  mysql> SELECT IF(1<2,'yes ','no');
  
  mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
  

  如果expr2或expr3中只有一个表达式是NULL值，则IF()函数的结果类型 为非NULL表达式的结果类型。

  expr1必须作为一个整数值进行评估，也就是说，假如你正在验证浮点值或字符串值，那么应该使用比较运算进行检验。

  mysql> SELECT IF(