Mysql 常用的查询

查看版本

select version();

select @@version;

SHOW VARIABLES LIKE "version";

查看当前会话隔离级别

select @@tx_isolation;

查看系统当前隔离级别

select @@global.tx_isolation;

查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

启用标准InnoDB监视器

SET GLOBAL innodb_status_output=ON;

启用InnoDB锁定监视器

SET GLOBAL innodb_status_output_locks=ON;

显示加锁信息

SHOW ENGINE INNODB STATUS;

新建用户

CREATE USER 'test1'@'127.0.0.1' IDENTIFIED BY '123456';

创建数据库并指定字符集

CREATE DATABASE `test_dadabase` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改用户

RENAME USER 'test1'@'127.0.0.1' TO 'test1'@'127.0.0.2';

SET PASSWORD FOR 'test1'@'127.0.0.2' = PASSWORD('1234567');
update user set password = password('123456') where user = 'test1';
grant all privileges on test_dadabase.* to 'test1'@'127.0.0.2' identified by '1234567';

flush privileges;

格式化时间戳按天、周、月统计

select DATE_FORMAT(start_at,'%Y%m%d') days,count(*) count from test group by days;

select DATE_FORMAT(start_at,'%Y%u') weeks,count(*) count from test group by weeks;

select DATE_FORMAT(start_at,'%Y%m') months,count(*) count from test group by months;

分区

SELECT PARTITION_NAME,TABLE_ROWS,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'test';

alter table test truncate partition p1;

alter table test drop partition p1;