Mysql 常用语句Sucha

1、IF expression THEN

语法：

IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
  ...
  ELSE
else-statements;
  END IF;

可以有多个ELSEIF块

示例：

 DELIMITER $$

 CREATE PROCEDURE GetCustomerLevel(
  in  p_customerNumber int(11),
  out p_customerLevel  varchar(10))
 BEGIN
  DECLARE creditlim double;

  SELECT creditlimit INTO creditlim
  FROM customers
  WHERE customerNumber = p_customerNumber;

  IF creditlim > 50000 THEN
  SET p_customerLevel = 'PLATINUM';
  ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
   SET p_customerLevel = 'GOLD';
  ELSEIF creditlim < 10000 THEN
   SET p_customerLevel = 'SILVER';
  END IF;

 END$$

2、IF(condition, value_if_true, value_if_false)

示例:

SELECT IF(500<1000, "YES", "NO");  

3、CASE语法

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

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

#示例：
CASE Statement
  CASE
    WHEN a < b THEN 1
    WHEN supplier_type = 'clothing' THEN 2
    ELSE 3
END

#示例：
 SELECT customerName, state, country
 FROM customers
 ORDER BY (CASE
  WHEN state IS NULL THEN country
  ELSE state
 END);

示例：
SELECT
    SUM(CASE
        WHEN status = 'Shipped' THEN 1
        ELSE 0
    END) AS 'Shipped',
    ……
    SUM(CASE
        WHEN status = 'Disputed' THEN 1
        ELSE 0
    END) AS 'Disputed',
    COUNT(*) AS Total
FROM
    orders;

4、插入时自动生成id，uuid

LOWER(REPLACE(MD5(UUID()),'-',''))

5、mysql update select 从查询结果中更新数据

UPDATE USER u
INNER JOIN (
	SELECT
	us.no 'no',
	CASE		
	WHEN us.balance IS NULL THEN
	0.00 ELSE us.balance 
	END as 'balance',
	CASE		
	WHEN us.self_balance IS NULL THEN
	0.00 ELSE us.self_balance 
	END as 'self_balance',
	CASE	
	WHEN us.corp_balance IS NULL THEN
	0.00 ELSE us.corp_balance 
	END as 'corp_balance' 
	FROM
	user_copy_0831_old_balance us
) uold on u.no=uold.no
 SET u.balance =uold.balance,
 u.self_balance=uold.self_balance, 
 u.corp_balance =uold.corp_balance