MySQL常用函数

-- 针对一个别名
with temp as (select * from aTable)
select * from temp
-- 针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
....
tempn as (select * from tb_namen)
select * from tmp,tmp2 where tmp.id=tmp2.id

-- find_in_set(搜索字段，被搜索字段)用来查询被搜索字段以逗号分隔的数据
select find_in_set('a', 'a,b,c,d,e')

SELECT name, (CASE
             WHEN sex=1 THEN '男'
             WHEN sex=2 THEN '女'
             WHEN sex=3 THEN '神仙'
             ELSE '性别出现异常'
            END
        ) AS 性别 FROM person;

-- 如果成绩为空则显示为0
SELECT ifnull(score, 0) FROM student;

-- 如果分数大于90分则为几个，否则为不及格
SELECT IF(score>90,'及格','不及格') FROM student;

IF search_condition THEN
  statement_list

ELSEIF search_condition THEN
  statement_list

ELSE
  statement_list

END IF;

select date_format(NOW(),'%Y-%m-%d')

UPSERT INTO table_name (字段1, 字段2，……) VALUES (字段1值, 字段2值, ……);

-- 说明
INSERT INTO table_name (字段1, 字段2, ……)
values (字段1值, 字段2值, ……)
ON DUPLICATE KEY UPDATE
-- 示例
INSERT INTO employee (id, name, email)
VALUES (2, 'Danny', 'danny.liu@krem.corp')
ON DUPLICATE KEY UPDATE;

-- 说明
INSERT INTO table_name (字段1, 字段2, ……)
values (字段1值, 字段2值, ……)
ON CONFLICT 冲突字段 冲突动作;
-- 示例
INSERT INTO employees (id, name, email)
VALUES (2, 'Danny', 'danny.liu@krem.corp')
ON CONFLICT (id) DO UPDATE;

SELECT DATE_ADD(now(), INTERVAL 1 DAY) AS tomorrow;

START TRANSACTION;
SELECT value FROM serial_number FOR UPDATE;
UPDATE serial_number SET value = value + 1;
COMMIT;