加字段
ALTER TABLE `table_name` ADD `file_name` varchar(50) COMMENT '备注';
删字段
ALTER TABLE `table_name` DROP `file_name`;
修改字段
ALTER TABLE `table_name` CHANGE `old_name` `new_name` varchar(20);
查询未提交的事务
select t.trx_mysql_thread_id from information_schema.innodb_trx t;
关联表更新
UPDATE table_01 t1,
table_02 t2
SET
t1.name = t2.name
WHERE
t1.id = t2.id
通过身份证号更新性别
UPDATE c_employee
SET gender = ( CASE SUBSTRING( id_card, 17, 1 )& 1 WHEN 1 THEN '男' ELSE '女' END )
WHERE
id_card IS NOT NULL
AND id_card != ''
创建数据库
CREATE DATABASE `database_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
模糊查询表名【查询mysql以xxx开头的表】
SELECT table_name from information_schema.columns
where table_name like '%***%'
group by table_name limit 2;
SELECT table_name from information_schema.columns
where table_name like '%tb_%' group by table_name;
