MySQL的一些SQL语句

今天继续分享一些实用的MySQL语句

1.查看当前数据库的业务表

SELECT
	schema_name AS db_name
FROM
	information_schema.schemata
WHERE
	schema_name NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' );

注意这里information_schema展示的是非实时的数据表,实时的数据，需要先set一下。

SET GLOBAL information_schema_stats_expiry = 0;

2. 查找业务库中的非 InnoDB 的表

SELECT
	table_schema,
	table_name,
ENGINE
FROM
	information_schema.TABLES
WHERE
	table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
	AND ENGINE <> 'InnoDB';

结果：

+--------------+----------------------------+--------+
| table_schema | table_name                 | ENGINE |
+--------------+----------------------------+--------+
| jeecms       | test_webmagic              | MyISAM |

3. 获取每张表数据量，并按数据量排序

SELECT
	table_schema,
	table_name,
	table_rows
FROM
	information_schema.TABLES
WHERE
	table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
ORDER BY
	table_rows DESC;

前10名加个limit

SELECT
	table_schema,
	table_name,
	table_rows
FROM
	information_schema.TABLES
WHERE
	table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
ORDER BY
	table_rows DESC
limit 10;

4. 某个库所有表的字段详情

SELECT
	table_schema,
	table_name,
	column_name,
	column_type,
	collation_name
FROM
	information_schema.COLUMNS
WHERE
	table_schema = '具体的数据库名';

某个库某个表的字段详情

SELECT
	table_schema,
	table_name,
	column_name,
	column_type,
	collation_name
FROM
	information_schema.COLUMNS
WHERE
	table_schema = '具体的数据库名'
and table_name= '具体的表名';

当然你也可以直接使用以下命令，可以看到非空值、备注信息。

show create table 具体的数据库名.具体的表名

5. 查看 MySQL 所有用户

SELECT DISTINCT concat( "'", USER, '''@''', HOST, "';" ) AS USER  FROM mysql.user;

6. 查询指定数据库所占空间大小

SELECT concat( round( sum( data_length )/( 1024 * 1024 ), 2 ) + round( sum( index_length )/( 1024 * 1024 ),
		2
		),
	'MB'
) AS 'DB Size'
FROM
information_schema.TABLES
WHERE
	table_schema = '具体的数据库名';

结果:

+----------+
| DB Size  |
+----------+
| 869.09MB |
+----------+

7. 查询指定数据库索引所占的空间大小

SELECT concat( round( sum( index_length )/( 1024 * 1024 ),
		2
		),
	'MB'
) AS 'DB Size'
FROM
information_schema.TABLES
WHERE
	table_schema = '具体的数据库名';

结果:

+---------+
| DB Size |
+---------+
| 5.11MB  |
+---------+

8. 查询数据库的连接数

SHOW VARIABLES LIKE '%max_connections%';

9. 查询数据库的当前连接数，并发数

mysql> SHOW STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 14    |
| Threads_created   | 274   |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.01 sec)

变量名	说明
Threads_cached	代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected	当前处于连接状态的线程个数。
Threads_created	代表从最近一次服务启动，已创建线程的数量。
Threads_running	代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于 sleep 状态，这里相对应的线程也是 sleep 状态。

10. 查询数据文件存放路径

SHOW VARIABLES LIKE '%datadir%';

11. 修改表的自增长值

ALTER TABLE 数据库名.表名
AUTO_INCREMENT=1000;

12. 批量删除某些前缀的表

比如想批量删除某个数据库的temp开头的表名

select concat('drop table ',table_name,';')
from information_schema.tables
where table_schema='wodamonitor' and table_schema = '具体数据库名' AND table_name LIKE 'temp_%';

得到sql，可以写个shell脚本进行执行。

本文没有授权给任何组织、企业和个人转载，未经作者允许禁止转载！

欢迎关注我的公众号testerzhang，原创技术文章第一时间推送。

公众号二维码

MySQL的一些SQL语句

相关文章：