MySQL用法
查询数据库表
通过查询INFORMATION_SCHEMA.TABLES查询数据库表的结构信息
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME =’table_name’ and TABLE_SCHEMA = \’database_name\’
— 查询所有表所有列名
SELECT COLUMN_NAME,TABLE_NAME, TABLE_SCHEMA, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA like \’ykee%\’;
查询结果导出文件
在查询语句后面加上into outfile 文件路径，可将查询结果导出到文件，文件格式自定
SELECT CONCAT(\’delete from \’,TABLE_NAME,\’;\’) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like \’dep_system_doc%\’ and TABLE_SCHEMA = \’acm_gaj\’
into outfile “d://clear_dep_system_doc_.xls”;
连表修改
UPDATE trc_track t, cst_customer c
SET t.org_id = c.org_id
WHERE t.org_id is null and t.customer_id = c.id
— 清除前缀空格
select dic_key from kn_bc_dictionary where dic_key like\’% \’;
select SUBSTR(dic_key FROM 1 FOR LENGTH(dic_key)-1) from kn_bc_dictionary where dic_key like\’% \’;
— 清除后缀空格
select dic_key from kn_bc_dictionary where dic_key like\’ %\’;
select SUBSTR(dic_key FROM 2 FOR LENGTH(dic_key)) from kn_bc_dictionary where dic_key like\’ %\’;
— 中间空格改为下划线
select dic_key from kn_bc_dictionary where dic_key like\’% %\’;
select dic_key, REPLACE(dic_key,\’ \’,\’_\’) from kn_bc_dictionary where dic_key like\’% %\’;