1.mysql show命令
# 查看所有引擎show engines;-- 如果要格式化显示show enginesG# 查看默认引擎show variables like "%engine%"# 查看所有数据库show databases;# 查看所有表show tabales;# 查看表结构desc tableName;show (full 查看更多信息) columns from tableName;SHOW (full 查看更多信息) FIELDS from users;-- 显示表的索引。show index from table_name; show keys from table_name; show variables; -- 显示系统变量的名称和值。 show table statusG; -- 显示当前数据库所有表的信息alter table table_name engine=innodb;-- 设置表的存储引擎select version(); -- 显示数据库版本。#锁表情况SHOW STATUS like "Table_locks%"#查看事物隔离级别SHOW VARIABLES like "tx%"
2.输出查询结果到文件
mysql -u用户名 -p密码 -e “ 查询语句” >文件路径;
3.显示数据库一些信息
## mysql> status;C:Program FilesMySQLMySQL Server 5.7binmysql.exe Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)Connection id: 3Current database: jfinalCurrent user: root@localhostSSL: Not in useUsing delimiter: ;Server version: 5.7.17-log MySQL Community Server (GPL)Protocol version: 10Connection: localhost via TCP/IPServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8TCP port: 3306Uptime: 1 hour 2 min 32 secThreads: 1 Questions: 17 Slow queries: 0 Opens: 111 Flush tables: 1 Open tbles: 104 Queries per second avg: 0.004
4.查看当前所处的数据库
5.查看建表语句
6.显示一个表的索引
7.优化查询
-- 慢sql日志记录是否开启SHOW variables like "%slow_query_log%"-- 默认十秒以上的sql才会写入慢sql日志SHOW VARIABLES LIKE "long_query_time"SET GLOBAL long_query_time=1SELECT SLEEP(2);-- log中有多少条慢sqlSHOW GLOBAL STATUS LIKE "Slow_queries"
8.查询数据库的表个数
SELECT COUNT( * ) FROM information_schema.tables WHERE TABLE_SCHEMA = 'activiti521'
文章会一直更新,感觉有用的可以点击关注我或者收藏文章。以后有新的用法我会更新到此文章。