记录我总是忘记语法得sql

1. case when

• 简单判断

CASE [col_name] 
WHEN [value1] THEN [result1]
WHEN [value1] THEN [result2]
....
ELSE [default] 
END [new_col_name]

• 表达式判断

CASE 
WHEN [expr] THEN [result1]
WHEN [expr] THEN [result2]
....
ELSE [default] 
END [new_col_name]

2. 将tableB得数据批量添加到tableA

INSERT INTO [table_A] (col1, col2 ...)
SELECT (col1, col2 ...) FROM [table_B] WHERE [condition]

3. 生成不带横杆[-]并且不重复得uuid

SELECT REPLACE (MD5(uuid()), '-', '')

4. 批量更新

UPDATE [table_name] SET [col_name] = [value] WHERE [condition]

或者

UPDATE [table_A] SET [col_name] = (SELECT [col_name] FROM [table_B] WHERE [condition])

5. 新增列

ALTER TABLE [table_name] ADD [new_col_name] [data_type] COMMENT [comment]

6. 修改列名

ALTER TABLE [table_name] CHANGE [old_col_name] [new_col_name] [data_type] COMMENT [comment]

7. 修改列得数据类型

ALTER TABLE [table_name] MODIFY COLUMN [col_name] [data_type] DEFAULT NULL;

8. 新建一张表A顺便从表B把数据拷过来（A和B结构相同）

CREATE TABLE [table_A] SELECT * FROM [table_B]

9.批量更新不重复的uuid

UPDATE [table_name] SET [id_column_name] = (SELECT MD5(FLOOR(RAND() * 999999)))