常用Mysql语句

数据库相关SQL

显示哪些线程正在运行

show full PROCESSLIST;

报告TCP/IP连接的主机名称（采用host_name:client_port格式），以方便地判定哪个客户端正在做什么。

如果得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。

查看指定IP连接

SELECT id, db, user, host, command, time, state, info 
from information_schema.PROCESSLIST 
WHERE 1=1 
-- and command != 'Sleep' 
 AND HOST LIKE '%localhost%' 
order by time desc 

查看Mysql数据库最大连接数

show variales like '%connection%';

这里指的是整个数据库

查看线程数

show global status like 'Thread%';

查看整个数据库的表个数、视图个数、触发器个数

-- 从上到下分别为 表个数,视图个数,触发器个数
set @db_name = 'database_Name';
select * from 
(SELECT count(TABLE_NAME) as tableNum FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db_name
union 
SELECT count(TABLE_NAME) as viewNum FROM information_schema.VIEWS WHERE TABLE_SCHEMA=@db_name
union 
SELECT count(TRIGGER_NAME) as triggerNum FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA=@db_name) as temp

show语句

查看当前连接的所有数据库

SHOW DATABASES;   -- 查看连接的所有数据库

查看当前数据库的所有表

SHOW TABLES;   -- 查看当前数据库所有表

查看表的创建语句

show create table table_name;

查看表的字段

show full columns from table_name;    -- 含注释
{describe|desc} table_name [col_name|wild]  -- 不含注释

查看mysql的数据文件

show variables like 'datadir'

定位SQL

根据字段名称查找表位置

SELECT
    COLUMN_NAME,
    table_name,
    DATA_TYPE,
    COLUMN_COMMENT 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = '数据库名称' 
    AND COLUMN_NAME LIKE '%字段名称%';

根据表注释查找表位置

select * from information_schema.`TABLES` where TABLE_COMMENT like '%表注释%'

全库查找值

DELIMITER //
DROP PROCEDURE IF EXISTS `proc_FindStrInAllDataBase`;
# CALL `proc_FindStrInAllDataBase` ('db_name','val');
CREATE PROCEDURE `proc_FindStrInAllDataBase` 
(
 IN para_databasename VARCHAR(128),
 IN para_finstr VARCHAR(128) 
)
BEGIN
 -- 需要定义接收游标数据的变量 
 DECLARE tmp_dbname VARCHAR(128);
 DECLARE tmp_tbname VARCHAR(128);
 DECLARE tmp_colname VARCHAR(128); 
 -- 遍历数据结束标志
 DECLARE done INT DEFAULT FALSE;
  
   
 -- 游标
 DECLARE cur_db_tb CURSOR 
 FOR 
 SELECT  
  #*,
  c.table_schema,c.table_name,c.COLUMN_NAME
 FROM 
  information_schema.`COLUMNS` C
  INNER JOIN information_schema.`TABLES` t ON c.`TABLE_NAME`=t.`TABLE_NAME` 
 WHERE
  T.`TABLE_TYPE`='BASE TABLE' 
 AND 
  (c.data_type  LIKE '%char%'  OR c.data_type  LIKE '%text%')
 AND 
  (C.TABLE_SCHEMA=para_databasename OR IFNULL(para_databasename,'') ='') AND IFNULL(para_finstr,'')<>'';
  
 -- 将结束标志绑定到游标
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 CREATE TEMPORARY TABLE IF NOT EXISTS rstb(dbname VARCHAR(128),tbname VARCHAR(128),colname VARCHAR(128),cnt INT); 
 -- 打开游标
 OPEN cur_db_tb;
   -- 开始循环
   read_loop: LOOP
   -- 提取游标里的数据，这里只有一个，多个的话也一样；
   FETCH cur_db_tb INTO  tmp_dbname,tmp_tbname,tmp_colname;
   -- 声明结束的时候
   IF done THEN
   LEAVE read_loop;
   END IF;
   -- 这里做你想做的循环的事件
   SET @sqlstr=CONCAT('select count(1) into @rn from ',tmp_dbname,'.',tmp_tbname,' where ',tmp_colname,' like ''%',para_finstr,'%''');
   
   PREPARE str FROM @sqlstr;  
   EXECUTE str;   
   DEALLOCATE PREPARE str;
   IF IFNULL(@rn,0)>0
    THEN
    INSERT INTO rstb VALUES(tmp_dbname,tmp_tbname,tmp_colname,@rn);
   END IF;

   END LOOP;
 -- 关闭游标
 CLOSE cur_db_tb;
 
 SELECT * FROM rstb;
 DROP TABLE rstb;
 
END
//
DELIMITER ;

时间SQL

UNIX_TIMESTAMP和FROM_UNIXTIME

UNIX_TIMESTAMP为把时间(年月日或者年月日时分秒)转为时间戳,FROM_UNIXTIME相反

SELECT UNIX_TIMESTAMP("2016-07-17 23:59:59"),FROM_UNIXTIME(1468771199)

本月数据

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月数据

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

昨天数据

-- DATEDIFF函数忽略time部分，只做日期是天数差 =1 为昨天数据，>=1为昨天到以前的数据, <=为昨天到今天的数据
SELECT * FROM 表名 WHERE DATEDIFF(now(),日期时间字段名) = 1

DATEDIFF函数只做日期差，执行

SELECT DATEDIFF(now(),'2019-01-01 01:01:01');

结果如下图

24小时内数据

where time >= (NOW() - interval 24 hour)

根据yyyy-mm-dd获取某天最晚最晚时间

select DATE_SUB( DATE_ADD( '2019-04-03', INTERVAL 1 DAY ), INTERVAL 1 SECOND )   -- 先加一天,在减一秒

根据yyyy-mm-01获取近三个月每个月的起始时间

set @startDate = '2019-06-01';  

/*当日起始*/
set @enDate =DATE_SUB(DATE_ADD(@startDate,INTERVAL  1 day),INTERVAL  1 SECOND);  -- 当天晚上23:59:59

/*当月起始*/
set @endMonthDateTime =DATE_SUB(DATE_ADD(@startDate,INTERVAL  1 month),INTERVAL  1 SECOND);   -- 当月最后一天23:59:59

/*下个月起始*/
set @nextMonthStartDate = DATE_ADD(@startDate,INTERVAL  1 month);  -- 下个月第一天
set @nextMonthEndDate = DATE_SUB(DATE_ADD(@startDate,INTERVAL  2 month),INTERVAL  1 SECOND);   -- 下个月最后一天

/*上个月起始*/
set @lastMonthStartDate = DATE_SUB(DATE_SUB(@startDate,INTERVAL  1 month),INTERVAL  0 SECOND);;  -- 上个月第一天
set @lastMonthEndDate = DATE_SUB(@startDate,INTERVAL  1 SECOND);   -- 上个月最后一天

根据日期分组

根据day分组

GROUP BY date_format( createTime, '%Y%m%d' )    --其中createTime为timestamp(格式2019-03-21 18:39:36)

sql打印指定时间段内所有日期

方案一：

根据起始日期查询

set @i = -1;
set @sql = repeat(" select 1 union all",-datediff('2019-04-01','2019-04-30')+1);   -- 建立30条数据
set @sql = left(@sql,length(@sql)-length(" union all"));  -- 去掉最后一个多余的union all
set @sql = concat("select date_add('2019-04-01',interval @i:=@i+1 day) as date from (",@sql,") as tmp");
prepare stmt from @sql;   -- 将@sql变量变为prepare语句
execute  stmt  -- 执行查询语句

方案二(推荐)：

根据某一天查询当月所有日期，这里不能用?只能用java的replaceAll方法。

SELECT ADDDATE(y.first, x.d - 1) as sysDate
FROM
(
SELECT 1 AS d UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31
) x,
(
SELECT '2020-05-15' - INTERVAL DAY('2020-05-15') - 1 DAY AS first,
DAY(LAST_DAY('2020-05-15')) AS last
) y
WHERE x.d <= y.last

datetime查询为某日上午或下午

DATE_FORMAT(sysDate,'%Y-%m-%d %p') as date

函数

函数有字符串函数、数字函数、日期函数、高级函数等。

通常利用case、sum等函数配合group by写报表

报表中的计数

SELECT
    code,
    name,
    sum( IF ( table2.chargeType = 2, 1, 0 ) ) AS icCount,  --计数
    FORMAT( sum( IF ( table2.chargeType = 2, table2.stopTime, 0 ) ) / 3600, 2 ) AS icStopTime,  --计算总时长
    sum( IF ( table2.chargeType = 2, table2.receiveAmount, 0 ) ) / 100 AS icAmount   --计算总金额
FROM
    org_chargepost  --分组的表
    LEFT JOIN ( --一个组对多条记录的表
   select table2 ...
        ) ON code= code
GROUP BY
    code
ORDER BY
    code

cast转换类型

将123456789转换为decimal，12代表小数点左侧数字加右侧数字。2为小数点个数。

select cast(sum(123456789)/100 as decimal(12,2)) 

sum与case结合 - 实现分段统计统计一个字段某个类型值的记录条数

sum( CASE WHEN type = 1 THEN 1 [WHEN type = 2 THEN 1] ELSE 0 END ) 

计算当type值为1[和2]的记录的总数

sum与case结合 - 统计不同类型的总金额

sum( CASE WHEN type = 1 THEN money [WHEN type = 2 THEN money] ELSE 0 END )

计算当type值为1[和2]的总金额

统计一个字段各个类型的百分率

CONCAT(    format(
    sum( CASE WHEN type = 2 THEN 1 ELSE 0 END ) / count( id ) * 100,2),'%' ) AS Type2percent,

注意统计类型字段用的是sum()，统计总共的记录用的是count() 。

用case找出符合对应类型的记录，sum(符合case条件)/count(所有)；

format(数值，小数位)返回指定位数的小数值；

concat(‘’，‘’)连接字符。

group by后根据一个字段类型把另一字段分成多列

用到group by统计后，统计的数字金额一般要用sum、cout之类的，不然只是取group组中的第一条。

SELECT
    id,
    name,
    -- sum(if(TRIM(type='现金'),money,0)) as '现金',
    -- sum(if(TRIM(type='支付宝'),money,0)) as '支付宝',
    -- sum(if(TRIM(type='微信'),money,0)) as '微信',
  sum(case TRIM(type) when '现金' then money else 0 end) as '现金',    -- 一定要用sum,不然查出来的金额是这一天中三种金额的第一条
  sum(case TRIM(type) when '支付宝' then money else 0 end) as '支付宝',
  sum(case TRIM(type) when '微信' then money else 0 end) as '微信',
    date
FROM
    table(每一天都有支付宝微信现金三条记录)
group by date  -- 根据日期group by

where条件中null值处理

if和isnull联合使用，等价于ifnull

where if(isnull(dish),h,dish) = h
/*等价于*/
where ifnull(dish,h) = h

truncate 清表数据

truncate table tbl_name;

将某一字段设为指定范围的随机值

update tbl_name set col = floor(1 + rand()*100)   --rand()代表0-1，*100代表0-100，floor代表小于参数的最大整数(为了取整)

根据某一个表（类别表）显示各种统计数据的报表

写法一（推荐）

这种先用类别表(a)关联已分组且过滤的记录表（b），然后在where，group by。这样虽然a的某一类别对应的b的数据没有一条，但是a的类别没有被过滤掉，即使没有数据（对应b的数据）的类别a依然可以显示。

SELECT
    a.CODE,
    b.* 
FROM
    a  -- 类别表a
    LEFT JOIN (
    SELECT  -- 先把b的数据分组好
    count( b.id ) AS count,
    sum( b.money ) AS money 
FROM
    b 
    WHERE  -- 先过滤条件，过滤掉不符合条件的数据
    b.time >= UNIX_TIMESTAMP( '2018-11-13 00:00:00' ) 
    AND b.time <= UNIX_TIMESTAMP( '2018-12-03 23:59:59' ) 
    GROUP BY  -- 然后分组（即使没有对应一些a的也没关系）
    b.cat_code 
    ) b ON a.CODE = b.cat_code -- 用符合条件的数据（b）关联a
    WHERE  -- 过滤，单纯的过滤需要显示的类别（a），即使b没有数据依然可以显示
    a.type = 0 
GROUP BY
    a.CODE

写法二（不推荐）

这种先用类别表(a)关联记录表（b），然后在where，group by。会导致只显示有数据（b）的类别（a），没有数据的类别不显示。

原因：

先关联a和b，然后在where过滤掉了所有不符合条件的b，但是同时也把a的数据一起过滤掉。

SELECT
    a.CODE,
    count( b.id ) AS count,
    sum( b.money ) AS money 
FROM
    a
    LEFT JOIN ( SELECT * FROM b ) b ON a.CODE = b.cat_code -- 先关联b的数据
WHERE
    a.type = 0 
    AND b.time >= UNIX_TIMESTAMP( '2018-11-13 00:00:00' ) -- 再过滤不符合条件的b，这里同时也会把a的数据一起过滤掉，因为已经关联成一个表了
    AND b.time <= UNIX_TIMESTAMP( '2018-12-03 23:59:59' ) 
GROUP BY
    a.CODE -- 然后再分组，最后只会显示一部分的a的类别，另一部分因为b数据不符合不显示

Code查重

这里count(*)，不用count(code)是因为count(*)统计的是行数，统计code为到null值的列，速度比较快。

count(code)统计的code是不为null值的列，要先判断code是否为null在进行判断。

select code,count(*) as repeat from A group by code having count(*)>1;