user表:
id | name | age |
---|---|---|
1 | jyjin | 22 |
2 | steven | 28 |
3 | linda | 18 |
4 | monica | null |
1.case when
设置同一字段在不同条件下的值
注意:不设置ELSE, 会把其他记录所有字段值置空
UPDATE table SET age = (
CASE
WHEN name = 'jyjin' THEN 18
WHEN name = 'steven' THEN 32
ELSE name
END
)
2.NULLIF(a, b)
判断两个值是否相等, 相等返回null,反之返回1;
SELECT NULLIF(1,1) -> null
SELECT NULLIF(1,0) -> 1
3. <=> 和 =
<=>比=更安全,可以判断null值
SELECT 'a' <=> 'a' -> 1
SELECT 'a' <=> NULL -> 0
SELECT 'a' = NULL -> NULL
SELECT NULL = NULL -> NULL
4.IS NULL 和 IS NOT NULL
SELECT name FROM user WHERE age IS NULL; -> monica
SELECT name FROM user WHERE age IS NOT NULL; -> jyjin、steven、linda