if函数,case运算符

if

/*IF(expression, first, second)
#如果expression为真返回first否则返回second
#可以返回任何值，字符串，空值，数字，日期*/
SELECT
	order_id,
    order_date,
    IF(
		YEAR(order_date) = YEAR(NOW()),
        'Active', 
        'Archived') AS a 
FROM orders
#订单是否在今年下单

 

case

/*
CASE
	WHEN expression1 THEN result1#如果expression1为真，返回result1
    WHEN expression2 THEN result2#如果expression2为真，返回result2
    ELSE可加可不加
END
*/
SELECT 
	order_id,
    CASE
		WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active'
        WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'Last year'
		WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'Archived'
		ELSE 'Future'
	END AS catefory
FROM orders

SELECT 
	CONCAT(first_name, ' ', last_name) AS customer,
    points,
    CASE
		WHEN points > 3000 THEN 'Gold'
		WHEN points >=2000 THEN 'Silver'   #第一个为假就落到了第二个，即【2000，3000】
        ELSE 'Bronze'
	END AS category
FROM customers
ORDER BY points DESC

if endif

/*我们一般使用参数为存储过程传递值
也可以使用参数为调用程序赋值*/
#获取州名，返回位于那个州的顾客 
DELIMITER $$
CREATE PROCEDURE get_clients
(
	state CHAR(2)
)#类型设置为char，括号里写上所有参数，如果有很多个参数用逗号分割
BEGIN
     IF state IS NULL THEN
		SET state = 'CA'
	END IF;
	SELECT * FROM clients c
    WHERE c.state = state;
END$$
DELIMITER ;

if else endif

DELIMITER $$
CREATE PROCEDURE get_clients
(
	state CHAR(2)
)
BEGIN
     IF state IS NULL THEN
		SELECT * FROM clients;
	 ELSE 
		SELECT * FROM clients c
		WHERE c.state = state;
	 END IF;	
END$$
DELIMITER ;