Mysql一运算符、类型转换、流程控制语句

1. 表达式中的类型转换 官网文档地址

1.1 数字和字符串的拼接

SELECT 1+'1'; -- '2'
SELECT CONCAT(2,'test'); -- '2test'

1.2 CAST()强制转换

SELECT 38.8, CAST(38.8 AS CHAR); -- 38.8, '38.8'
SELECT 38.8, CONCAT(38.8);-- 38.8, '38.8'
-- concate() 函数需要字符串，会隐示转换

1.3 比较操作的转换

• 如果一个或两个参数均为NULL，则比较的结果为NULL，但NULL-safe <=> 相等比较运算符除外。对于NULL <=> NULL，结果为true。无需转换。
  
• 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。
  
• 如果两个参数都是整数，则将它们作为整数进行比较。
  
• 如果不与数字比较，则将十六进制值视为二进制字符串。
  
• 如果参数之一是a TIMESTAMP或 DATETIMEcolumn，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳。这样做是为了使ODBC更友好。对于的参数，此操作未完成 IN()。为了安全起见，在进行比较时请始终使用完整的日期时间，日期或时间字符串。例如，要在BETWEEN与日期或时间值一起使用时获得最佳结果 ，请使用CAST()将值显式转换为所需的数据类型。
  一个或多个表中的单行子查询不被视为常量。例如，如果子查询返回要与DATETIME 值进行比较的整数，则比较将作为两个整数进行。整数不转换为时间值。要将操作数作为DATETIME值进行比较 ，请使用 CAST()将子查询值显式转换为DATETIME。
  
• 如果参数之一是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较；如果另一个参数是浮点值，则将参数作为浮点值进行比较。
  
• 在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较。
  

0代表false，1代表true

SELECT 1 > '6x'; -- 0
SELECT 7 > '6x'; -- 1
SELECT 0 > 'x6'; -- 0
SELECT 0 = 'x6'; -- 1

2. 运算符

2.1 运算符优先级

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

-- 显示的覆盖使用括号()
SELECT 1+2*3;
-- 7
SELECT (1+2)*3;
-- 9

2.2 比较函数和运算符

-- 以下关系比较运算符不仅可以用于比较标量操作数，还可以用于比较行操作数：
=  >  <  >=  <=  <>  !=

-- <=> NULL-安全相等。
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
1, 1, 0

-- 如果用=则双方如果有一个操作数是NULL，则会返回NULL
SELECT 1 = 1, NULL = NULL, 1 = NULL;
1, NULL, NULL

-- 不等于 <> 和 != 作用是一样的

-- LEAST(value1,value2,...)
-- 返回最小值，如果存在NULL则返回NULL

SELECT LEAST(1,2,3,4,5,'6');
-- 1
SELECT LEAST(1,2,3,4,5,6,NULL);
-- 空

-- GREATEST(value1,value2,...)
-- 返回最大值，如果存在NULL则返回NULL

2.3 逻辑运算符

AND， &&	逻辑与
NOT， !	取反值
OR， ||	逻辑或
XOR	逻辑异或

-- AND 和 && 作用一样
-- OR  和 || 作用一样

2.4 赋值运算符

:=	赋值
=	分配值（作为SET 语句的一部分 ，或作为语句的SET子句的 一部分UPDATE）

SELECT @var1, @var2;
-- NULL, NULL

SELECT @var1 := 1, @var2;
-- 1, NULL

SELECT @var1, @var2;
-- 1, NULL

SELECT @var1, @var2 := @var1;
-- 1, 1

SELECT @var1, @var2;
-- 1, 1

3. 流程控制

CASE	案例运算符
IF()	如果/其他构造
IFNULL()	空if / else构造
NULLIF()	如果expr1 = expr2，则返回NULL

3.1 CASE

CASE 的标准语法

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE 的使用例子

-- CASE WHEN THEN

-- 可以将要比较的字段放在 CASE FIELD
SELECT
    emp_no,
    (
        CASE gender
        WHEN 'M' THEN
            '男'
        WHEN 'F' THEN
            '女'
        ELSE
            '不知道'
        END
    ) AS 'sex'
FROM
    employees

-- 也可以放在 WHEN FIELD 后面
SELECT
    emp_no,
    (
        CASE 
        WHEN gender = 'M' THEN
            '男'
        WHEN gender = 'F' THEN
            '女'
        ELSE
            '不知道'
        END
    ) AS 'sex'
FROM
    employees

3.2 IF()

IF()的标准语法

IF(expr1, expr2, expr3)

-- 如果expr1 is TRUE (expr1 <> 0和expr1 <> NULL),则IF()返回expr2. 否则,返回expr3.
-- 要求表达式的值不能为NULL和0
-- 表达式的值为true,返回第二个表达式,为假,则返回第三个表达式的值
-- 理解,作用类似于编程语言中的三目运算符

IF()的使用例子

SELECT IF (1 > 2, 2, 3);
-- 3

SELECT IF (1 < 2, 'yes', 'no');
-- yes 

SELECT IF(1 > 2 , NULL , 'NO');
-- NO

SELECT IF(1 < 2 , NULL , 'NO');
-- NULL

3.3 IF ELSEIF ELSE 结构

-- 这种类似于CASE 的结构常用于定义函数和存储过程
-- MYSQL 中的IF结构是不支持查询的 CASE可以，我自己也更加倾向使用CASE

-- 如使用IF 结构定义一个存储过程
create procedure dbname.proc_getGrade  
(stu_no varchar(20),cour_no varchar(10))  
BEGIN 
declare stu_grade float;  
select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no;  
if stu_grade>=90 then 
    select stu_grade,'A';  
elseif stu_grade<90 and stu_grade>=80 then 
    select stu_grade,'B';  
elseif stu_grade<80 and stu_grade>=70 then 
    select stu_grade,'C';  
elseif stu_grade70 and stu_grade>=60 then 
    select stu_grade,'D';  
else 
    select stu_grade,'E';  
end if;  
END

3.4 IFNULL()

IFNULL()语法

-- IFNULL(expr1,expr2)
-- 如果expr1不是 NULL,则IFNULL()返回expr1; 否则返回 expr2.
-- 如果第一个表达式的值就返回第一个表达式的值,否则返回第二个表达式的值.

IFNULL()例子

SELECT IFNULL(1, 0);
-- 1

SELECT IFNULL(NULL, 10);
-- 10

SELECT IFNULL(1/0, 10);
-- 10

SELECT IFNULL(1/0,'yes');
-- 'yes'

3.5 NULLIF()

NULLIF() 语法

-- NULLIF(expr1, expr2)
-- 如果 expr1 = expr2,则返回NULL,如果两个表达式的值不同则返回 expre1的值.

SELECT NULLIF(1,1);
-- NULL

SELECT NULLIF(1,2);
-- 1