工作中常用-必会的MySQL常用查询

目录

MySQL获取各个时间段

查询重复数据

删除表中的重复记录:（根据单列删除且保留ID最小的一条）

查询每张表的行数

MySQL复制表数据

mysql查看当前实例包含多少张表

mysql查询数据库中所有空表

MySQL创建存储过程实现循环insert

MySQL锁表与解锁

---

MySQL获取各个时间段

-- 今天
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';

-- 昨天
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';

-- 上周
SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';

-- 本周
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
-- 上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';

-- 上月
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';

-- 本月
SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

查询重复数据

SELECT * FROM testdelete 
WHERE ONE IN (SELECT ONE FROM testdelete GROUP BY ONE HAVING COUNT(ONE) > 1) 
testdelete 为表名，ONE为判断重复的列名
---one换成自己需要查询的字段

删除表中的重复记录:（根据单列删除且保留ID最小的一条）

DELETE FROM testdelete WHERE ONE IN(SELECT
 ONE FROM testdelete GROUP BY ONE HAVING COUNT(ONE) > 1)
 AND id NOT IN(SELECT MIN(id) FROM testdelete GROUP BY ONE HAVING COUNT(ONE) > 1) 
 ---one换成自己需要查询的字段

查询每张表的行数

use information_schema; #如果使用数据库工具查看那么切换到该库即可
select table_name,table_rows from tables
where TABLE_SCHEMA = '数据库名'
order by table_rows desc;

MySQL复制表数据

方法一

首先创建表
create table 新表名 like 需要复制的表;
再将数据插入
insert into 新表名  select * from 需要复制的表;

方法二

create table 新表名 as select * from 需要复制的表;

mysql查看当前实例包含多少张表

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   WHERE table_schema = '实例名'   GROUP BY table_schema;

mysql查询数据库中所有空表

-- Mysql 一个数据库所有有数据的表
SELECT table_schema,table_name,table_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库的名称'  AND table_rows != 0 ORDER BY table_rows DESC;
-- Mysql 一个数据库中所有为空的表
SELECT table_schema,table_name,table_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库的名称'  AND table_rows = 0 ORDER BY table_name ;

MySQL创建存储过程实现循环insert

delimiter //     //以delimiter来标记用//表示存储过程结束
create procedure proc_ip_flow_cache()		//创建proc_ip_flow_cache()存储方法
begin
declare a int;		//定义a变量
set a=0;
while a<24 do		//对a的值配置
	INSERT into ip_flow_cache 
	SELECT
    SUM(i.in_flow),
    SUM(i.out_flow),
    i.date,
    i.time
FROM
    i_flow_hour_count i
WHERE
    i.time=a;
set a=a+1;		//自增循环
end while;
end
//		//存储过程结束

MySQL锁表与解锁

第一种：
1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程(如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程)

show processlist

3.杀死进程id(就是上面命令的id列)

kill id

第二种：
1.查看在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id(就是上面命令的trx_mysql_thread_id列)

kill 线程ID

例子：

查出死锁进程：SHOW PROCESSLIST
杀掉进程 KILL 420821;

其它关于查看死锁的命令：

1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;