mysql常用sql语句

　　　　　　　　mysql常用sql语句

show variables like '%max_connections%'; //查看数据库的最大连接数;

SET GLOBAL max_connections=2000;//设置最大连接数，重启后失效，永久生效需要在配置文件中修改。

show  PROCESSLIST;//连接详细信息

show status like 'Threads%';//查看连接线程数

show procedure status;//查看存储过程

select count(*) as total from table_name;//查看表有多少条数据

desc table_name; //查看表结构

show table status where comment='view';//查看视图

show index from table_name;// 查看索引

show variables like 'slow_query%';//查看慢查询的日志路径，以及是否开启慢查询。OFF为未开启

show variables like '%version%';查看数据库的版本信息

查看正在执行的事务:
SELECT
    trx_id as '事务ID',
    trx_state as '事务状态',
    trx_requested_lock_id as '事务需要等待的资源',
    trx_wait_started as '事务开始等待时间',
    trx_tables_in_use as '事务使用表',
    trx_tables_locked as '事务拥有锁',
    trx_rows_locked as'事务锁定行',
    trx_rows_modified  as '事务更改行'  
FROM information_schema.INNODB_TRX;

查看正在锁的事务：
SELECT
    lock_id as '锁ID',
    lock_trx_id as '拥有锁的事务ID',
    lock_mode as '锁模式',
    lock_type as '锁类型',
    lock_table as '被锁的表',
    lock_index as '被锁的索引',
    lock_space as '被锁的表空间号',
    lock_page as '被锁的页号',
    lock_rec as '被锁的记录号',
    lock_data as '被锁的数据'
FROM information_schema.INNODB_LOCKS;

查看正在等待的事务：
SELECT
  requesting_trx_id as'请求锁的事务ID',
  requested_lock_id as '请求锁的锁ID',
  blocking_trx_id as '当前拥有锁的事务ID',
  blocking_lock_id as '当前拥有锁的锁ID'
FROM information_schema.INNODB_LOCK_WAITS;