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;

4、MySQL单库单表授权（可用于后续移除部分表授权脚本,MySQL不支持 库名.* 后移除部分表权限）

#!/bin/bash

# 脚本配置
USER='yw4'
HOST='%'
DATABASE='jcs18'
ROOT_USER='root'
ROOT_PASSWORD='123456'

# 构建查询语句，获取表名
query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DATABASE'"

# 执行查询并将结果存储在数组中
readarray -t TABLES < <(mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -Bse "$query")

# 遍历数组中的表名并赋予权限
for TABLE in "${TABLES[@]}"; do
    echo "为 $DATABASE.$TABLE 上的 $USER@$HOST 授予权限中..."
    mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "GRANT ALL PRIVILEGES ON $DATABASE.$TABLE TO '$USER'@'$HOST';"
done

echo "权限授予成功"