MySQL常用命令

1. 密码更新操作

1. 更新已有的用户名密码

UPDATE mysql.user set authentication_string = PASSWORD ("123") WHERE User = "root" and Host="%";
flush privileges;

2. 重新设置现在正在登录的用户的账号密码

SET PASSWORD = PASSWORD ("123");
flush privileges;

2. 数据库当前慢sql查询指令

select * from information_schema.processlist where command != 'Sleep' order by time desc;

3.基本查询操作示例

1、查询dbA库大小

SELECT 
    SUM(data_length + index_length) / 1024 / 1024 AS 'Database Size in MB'
FROM 
    information_schema.TABLES
WHERE 
    table_schema = 'dbA';

2、查询表数量
SELECT 
    COUNT(*) AS 'Number of Tables'
FROM 
    information_schema.TABLES
WHERE 
    table_schema = 'dbA';
		
3、查询视图数量
SELECT 
    COUNT(*) AS 'Number of Views'
FROM 
    information_schema.TABLES
WHERE 
    table_schema = 'dbA' AND table_type = 'VIEW';	
	
4、查询字段数量
Sql
SELECT 
    COUNT(*) AS 'Field Count'
FROM 
    information_schema.COLUMNS
WHERE 
    table_schema = 'dba' AND table_name = 'tabA';
	
5、查询数据行记录数量
Sql
SELECT 
    COUNT(*) AS 'Row Count'
FROM 
    dba.tabA;