Mysql日常查询

1.查看当前用户

select user()

2.查看当前数据库

select database()
select schema()

3.查看所有数据库

show databases
show schemas

4.查看用户所有表

show tables
show tables from db_name

5.查看表的状态

show table status

6.查看表的列

desc
desc select * from t
show columns from table_name

7.查看创建表的语句

show create table t\G

8.查看数据库引擎

select @@default_storage_engine;
show variables like 'storage%';
show engines;
show table status like 't1';
select table_schema,table_name,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

9.查看存储过程

show procedure status
show create procedure pro_name
select name from mysql.proc where db='' and type='PROCEDURE';

10.查看函数

show function stauts
show create function func_name
select name from mysql.proc where db='' and type='FUNCTION';

11.查看视图及表

select * from information_schema.views
select * from information_schema.tables
select table_schema,table_name,table_type,engine,table_rows,avg_row_length,index_length from information_schema.tables where table_schema='mydb';

12.查看索引

desc t1;
show index from t1;

13.查看触发器

show triggers [FROM db_name][LIKE expr]
show triggers/G

14.查看引擎

show engines;
show engine innodb status\G

15.查看二进制日志

show binary logs
show binlog events in ''

16.查看二进制主从状态

show master status;
show slave status;

17.查看中继日志的事件信息

show relaylog events in ''

18.查看数据库状态

show status;

19.查看数据库版本和日期

select version(),current_date;

20.查看auto_increment当前插入的最后一个数值

select last_insert_id();

21.显示所有库下面表的信息

select table_schema,group_concat(table_name) from information_schema.tables group by table_schema;

22.查看所有innodb引擎的表

select table_schema,table_name from information_schema.tables where engine='innodb';

23.查看数据库中表的大小

select table_schema,table_name,(avg_row_length*table_rows+index_length)/1024/1024 'size(M)' from information_schema.tables where table_schema='mydb';

select table_schema,table_name,(data_length/1024/1024) 'size(M)' from information_schema.tables where table_schema='mydb';