常用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
批量删除日志表
获取DROP TABLE 语句
SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') FROM information_schema.tables WHERE table_schema = 'DBNAME' AND table_name LIKE 'TBALE_PREFIX%';
show语句
查看当前连接的所有数据库
SHOW DATABASES; -- 查看连接的所有数据库
查看当前数据库的所有表
SHOW TABLES; -- 查看当前数据库所有表
查看表的创建语句
show create table table_name;
查看表的字段
show full columns from table_name; -- 含注释 {describe|desc} table_name [col_name|wild] -- 不含注释
用sql代替
SELECT COLUMN_NAME,COLUMN_COMMENT FROM information_schema.columns WHERE table_schema = 'DBNAME' AND TABLE_NAME LIKE 'TABLENAME%'
查看mysql的表大小
#查看表大小等信息 SHOW TABLE STATUS LIKE 'your_table_name'; #查看详细索引大小信息 SHOW INDEX FROM your_table_name;
查看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 table_schema,table_name, ENGINE,create_time,table_collation,table_comment FROM information_schema.`TABLES` WHERE table_schema='DBName' AND table_name LIKE '%tableName%'
根据表注释查找表
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
当天数据
SELECT * from 表名 where 列名 > DATE_ADD(CURDATE(), INTERVAL 0 HOUR);
昨天数据
-- 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;
:=赋值
查询TABLE_NAME的数据时带行号,
=只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用.
:=不只在set和update时时赋值的作用,在select也是赋值的作用.
SELECT (@rownum := @rownum+1) AS rownum,TABLE_NAME.* FROM TABLE_NAME,(SELECT @rownum:=0) AS rn
多次update更新字段
再一个表中,每次图片接口吐一次都要求在字段后面用逗号分割append。
更新时用函数处理,如果(if函数)原有字段值为null(isnull函数)或者长度小于1(length和trim函数),则直接把值赋值给字段,否则用CONCAT函数逗号拼接。
if(ISNULL(licensePic)||LENGTH(trim(licensePic))<1,?, CONCAT(licensePic,',',?))
例如:
update bus_arrivingrecord set vehPic = if(ISNULL(licensePic)||LENGTH(trim(licensePic))<1,?, CONCAT(licensePic1,',',?)) where arrivingId = ?
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)