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)))
