数据库相关SQL
显示哪些线程正在运行
show full PROCESSLIST;
报告TCP/IP连接的主机名称（采用host_name:client_port格式），以方便地判定哪个客户端正在做什么。
如果得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
查看Mysql数据库最大连接数
show variales like '%max%';
这里指的是整个数据库
查看整个数据库的表个数、视图个数、触发器个数
-- 从上到下分别为 表个数,视图个数,触发器个数 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 -- 执行查询语句
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;
