Mysql常用语句

1. 查询表的所有列名。tableName为表名，dataBaseName为数据库名

select GROUP_CONCAT(COLUMN_NAME) from information_schema.columns where TABLE_NAME='tableName' and TABLE_SCHEMA ='dataBaseName';

2. 根据条件dump数据

mysqldump --single-transaction=TRUE -uroot -p db_name table_name --where="phone like '1381234%'" >/tmp/1.sql

3. 打开慢查询

set global slow_query_log='ON';

然后可以查看慢查询记录的文件

show variables like 'slow_query%';

+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/master-slow.log |
+---------------------+--------------------------------+

/var/lib/mysql/master-slow.log里面记录的就是慢查询语句
4. 导出表结构

SELECT
COLUMN_NAME 字段名,
COLUMN_TYPE 字段类型,
IS_NULLABLE 是否为空,
COlUMN_DEFAULT 默认值,
COLUMN_COMMENT 说明
FROM
INFORMATION_SCHEMA.COLUMNS
where
table_schema ='Finance' and table_name = 'Finance_Table'
order by ORDINAL_POSITION;

Finance为数据库名称，Finance_Table为表名
6.