mysql常用查询语句

查询表的大小及更新时间

SELECT
    TABLE_NAME as `name`,
    TABLE_TYPE as `type`,
    round((DATA_LENGTH + INDEX_LENGTH) / 1024) as `size(k)`,
    TABLE_ROWS as `cnt`,
    UPDATE_TIME as `updateTime`,
    CREATE_TIME as `createTime`
FROM
    information_schema.`TABLES`
where
    TABLE_SCHEMA = '数据库名'
order by TABLE_ROWS desc

查看数据库大小

SELECT
    TABLE_SCHEMA,
    SUM(round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024)) as `size(m)`
FROM
    information_schema.`TABLES`
GROUP BY
    TABLE_SCHEMA
order by `size(m)` desc

设置环境变量

set old_passwords=0

查看所用用户

select concat('\'',User,'\'','@','\'',Host,'\'') as Username,Password from mysql.user;

创建用户

grant all privileges on cacti.* to cactiuser@'localhost' identified by 'cactiuser';

查看当前用户权限

show grants

查看某个用户权限

show grants for 'jack'@'%';

删除用户

drop user 'jack'@'localhost';

对账户重命名

rename user 'jack'@'%' to 'jim'@'%';

修改密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';
flush privileges;

查看计划任务

SELECT * FROM information_schema.EVENTS;