MySQL 中常用函数使用

IF函数根据判断条件是否成立进行选择执行，成立时执行一条语句，不成立时执行另一条语句

语法结构：

IF(condition, value_if_true, value_if_false)

参数说明

• condition: 判断条件
• value_if_true: 如果 condition 的结果为 TRUE，返回该值
• value_if_false: 如果 condition 的结果为 FALSE，返回该值

举例：

SELECT id, name,
       IF(age<18,'未成年','成年') AS '是否成年'
FROM student;

IFNULL()

IFNULL(expr1,expr2)，如果 expr1 的值为 null，则返回 expr2 的值，如果 expr1 的值不为null，则返回 expr1 的值。

SELECT   IFNULL(1,0);  -- 输出结果：1   
SELECT   IFNULL(NULL,10); -- 输出结果：10   
SELECT   IFNULL(1/0,10);  -- 输出结果：10   
SELECT   IFNULL(1/0,'yes'); -- 输出结果：'yes' 
SELECT   IFNULL(NULL,'B');    -- 输出结果：B
SELECT   IFNULL('Hello','B'); -- 输出结果：Hello

NULLIF()

NULLIF(expr1,expr2)，如果 expr1=expr2 成立，那么返回值为 null，否则返回值为 expr1 的值。

SELECT NULLIF('A','A');     -- 输出结果：null
SELECT NULLIF('A','B');     -- 输出结果：A

ISNULL()

ISNULL(expr)，如果 expr 的值为 null，则返回 1，如果 expr1 的值不为 null，则返回 0。

SELECT ISNULL(NULL);        -- 输出结果：1
SELECT ISNULL('HELLO');     -- 输出结果：0

CASE

CASE表示函数开始，END表示函数结束。如果 expr1 成立，则返回 value1，如果 expr2 成立，则返回value2，当全部不成立则返回 valueN，而当有一个成立之后，后面的就不执行了。

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
#输出结果：1 > 0

如果表达式 expr 的值等于 e1，返回 value1；如果等于 e2,则返回 value2。否则返回 valueN。

SELECT CASE xfflag 
　　WHEN 1 THEN '结算账目'
　　WHEN 2 THEN '消费账目'
ELSE '类型不对'

Insert 

insert into

插入数据：插入数据时数据库会检查主键，如果出现重复会报错。

适用场景：适用于直接数据插入。

# 第一种方式
insert into 表名(字段名1,字段名2,...) values(值1,值2,...);
 
# 第二种方式：按照表中所有字段进行插入数据，一定要与字段在表中定义的顺序一致
insert into  表名 values(值1,值2,...);

replace into

插入替换数据：数据表中有 PrimaryKey，或者 unique 索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和 insert into 一样。

适用场景：适用于第一次数据插入时有些字段的数据出现了丢失，再次插入的时候希望通过唯一键更新数据而不再重复插入新数据。

# 第一种方式
replace into 表名(字段名1,字段名2,...) values(值1,值2,...);
 
# 第二种方式：按照表中所有字段进行插入数据，一定要与字段在表中定义的顺序一致
replace into 表名 values(值1,值2,...);

insert ignore

插入忽略数据：如果已经存在相同的记录，则忽略当前新数据；

适用场景：适用于数据已经插入，再次插入的时候希望通过唯一键信息，跳过已经插入的数据。

# 第一种方式
insert ignore into 表名(字段名1,字段名2,...) values(值1,值2,...);
 
# 第二种方式：按照表中所有字段进行插入数据，一定要与字段在表中定义的顺序一致
insert ignore into 表名 values(值1,值2,...);

注：对于以上三种方法，如果表中没有设置主键或唯一索引；则效果都是一样的，即直接插入数据

UPSERT

INSERT INTO … ON DUPLICATE KEY UPDATE 是 MySQL 中一种用于插入数据并处理重复键冲突的语法。

这个语法适用于在 insert 的时候，如果 insert 的数据会引起唯一索引(包括主键索引)的冲突，即唯一值重复了，则不会执行 insert 操作，而执行后面的 update 操作，使用给定的新值来更新冲突行中的列。

一般 Update子句可以使用 VALUES(col_name) 获取 insert 部分的值。也是项目中使用最多的方式。

注意：VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL。

INSERT INTO student(Sno, Sname, Ssex, Sage, Sdept)
VALUES("201215121", "李勇", "男", 30, "CS")
on DUPLICATE key UPDATE Sage=values(Sage);

mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 201215121 | 李勇   | 男   |   18 | CS    |
| 201215122 | 刘晨   | 女   |   19 | CS    |
| 201215123 | 王敏   | 女   |   18 | MA    |
| 201215125 | 张立   | 男   |   19 | IS    |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)
 
mysql> INSERT INTO student(Sno, Sname, Ssex, Sage, Sdept)
    -> VALUES("201215121", "李勇", "男", 30, "CS")
    -> on DUPLICATE key UPDATE Sage=values(Sage);
Query OK, 2 rows affected (0.01 sec)
 
mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 201215121 | 李勇   | 男   |   30 | CS    |
| 201215122 | 刘晨   | 女   |   19 | CS    |
| 201215123 | 王敏   | 女   |   18 | MA    |
| 201215125 | 张立   | 男   |   19 | IS    |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)