秋夜雨巷

常用Mysql语句

2018-10-30 0条评论 521次阅读 0人点赞

数据库相关SQL

显示哪些线程正在运行

show full PROCESSLIST;

《常用Mysql语句》

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

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

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

查看Mysql数据库最大连接数

show variales like '%max%';

《常用Mysql语句》

这里指的是整个数据库

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

-- 从上到下分别为 表个数,视图个数,触发器个数
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)

《常用Mysql语句》

本月数据

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');
结果如下图

《常用Mysql语句》

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  -- 执行查询语句

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;

 

点赞
知识共享许可协议
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可

Leave a Reply

Your email address will not be published. Required fields are marked *