sql语句mysql_mysql-非常实用的sql语句

一、 名称解释

常量：将某些查询的结果集当成一个变量，结果出来后直接参与其他计算，不需要再次查询的结果。

二、非常实用的sql语句

1、迭代查询

表结构

CREATE TABLE `product` (

`id` int(11) NOT NULL auto_increment COMMENT 'id',

`productName` varchar(100) NOT NULL COMMENT '产品名称',

`productPrice` int(11) NOT NULL COMMENT '产品价格',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

测试数据

insert into `product` (`id`, `productName`, `productPrice`) values('1','产品1','23');

insert into `product` (`id`, `productName`, `productPrice`) values('2','产品2','34');

insert into `product` (`id`, `productName`, `productPrice`) values('3','产品3','38');

insert into `product` (`id`, `productName`, `productPrice`) values('4','产品4','45');

insert into `product` (`id`, `productName`, `productPrice`) values('5','产品5','78');

insert into `product` (`id`, `productName`, `productPrice`) values('6','产品1','65');

效果如图：

图1

图2

从上面的查询结果可以看出，totalprice字段值的规则是从第1条记录到当前记录的totalprice之和，不过这种方式效率并不高，尤其在记录非常多的情况。

从图2的查询结果分析可知，这个结果仍然是求和的操作，只是并不是对所有的记录求和，也不是分组求和，而是使用迭代的方式进行求和，求和的公式如下：当前 记录的totalprice值= 当前记录的productPrice值+ 上一条记录的totalprice值，上一条记录的totalprice值也可看成是当前记录以前所有记录的productPrice值之和。因此，可以 对每一条记录进行求和(使用sum函数)，不过要求出当前记录及以前的记录的productPrice之和，如下面的SQL语句：

SELECT a.id,a.productPrice,(SELECT SUM(productPrice) FROM product b WHERE b.id <= a.id ) AS totalprice FROM product a;

2、sql语句中常量的应用

创建表

CREATE TABLE `r_statistics_hour` (

`id` bigint(50) NOT NULL auto_increment,

`createDate` date default NULL,

`modifyDate` date default NULL,

`timePatternString` varchar(20) default NULL COMMENT '统计时间类型',

`salesAmount` double default NULL COMMENT '销售额',

`shoppersTraffic` double default NULL COMMENT '客流量',

`empWorkHoursPer` double default NULL COMMENT '员工工时比',

`avgSalesAmount` double default NULL COMMENT '平均交易额',

`turnoverAmountRate` double default NULL COMMENT '成交率',

`storeid` int(11) default NULL,

`regionId` int(11) default NULL,

`turnoverAmount` int(11) default NULL COMMENT '成交量',

`empWorkTime` double default NULL COMMENT '员工工时',

`businessOpportunities` int(11) default NULL COMMENT '商机(店铺外的人数)',

`giveUpRate` double default NULL COMMENT '放弃率',

`residenceTime` double default NULL COMMENT '平均停留时间',

`transactionScale` double default NULL COMMENT '交易规模',

`estimatedVisits` int(11) default NULL COMMENT '预估销售额',

`timestring` varchar(32) default NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `unique_statistics` (`timestring`,`storeid`)

) ENGINE=InnoDB AUTO_INCREMENT=30970 DEFAULT CHARSET=utf8

参考sql语句

SELECT

DATE_FORMAT(

CONCAT(timePatternString, '0000'),

'%H'

) hours,

SUM(salesAmount) salesAmount,

AVG(b.salesAmount12) salesAmount12

FROM

r_statistics_hour a,

-- 有一个变量的时候该调语句可以作为一个常量，添加到上面的查询语句中

(SELECT SUM(salesAmount) salesAmount12 FROM r_statistics_hour WHERE LEFT(timePatternString, 8) >= 20150830 AND LEFT(timePatternString, 8) <= 20150926 AND (storeid = 47 OR regionId = 47)) b

WHERE LEFT(timePatternString, 8) >= 20150830

AND LEFT(timePatternString, 8) <= 20150926

AND (storeid = 47 OR regionId = 47)

GROUP BY DATE_FORMAT(

CONCAT(timePatternString, '0000'),

'%H'

)

3、在sql语句中使用截取字符串(LOCATE(CONCAT(store_id,','),CONCAT(ca.`STOREIDS`,','))>0)和ifnull

SELECT *,

-- 计算两个日期之间相差的天数

(TO_DAYS(end_time) - TO_DAYS(start_time) + 1) dayNum,

-- 获取店铺家数(字段长度-分隔符长度)+ 1

LENGTH(STOREIDS) - LENGTH(REPLACE(STOREIDS,',',''))+1 storeNum,

-- 当期客流量除以环比客流量*100保留两位小数，在判断是否为空，如果为空则设置为零

IFNULL(FORMAT(((enters - entersHb) / entersHb)*100,2),0) rateHb,

IFNULL(FORMAT(((enters - entersTb) / entersTb)*100,2),0) rateTb,

FORMAT((SPEND /(enters - entersHb)),2) costAvgHb,

FORMAT((SPEND /(enters - entersTb)),2) costAvgTb

FROM (

SELECT

*,

-- LOCATE(CONCAT(store_id,','),CONCAT(ca.`STOREIDS`,','))>0 判断store_id 的值是否在ca.`STOREIDS`中; DATE_FORMAT(ca.START_TIME, '%Y%m%d') 日期格式化

IFNULL((SELECT SUM(ENTERS) FROM t_statistic_day WHERE LOCATE(store_id,CONCAT(ca.`STOREIDS`,','))>0 AND DATE >= DATE_FORMAT(ca.START_TIME, '%Y%m%d') AND DATE <= DATE_FORMAT(ca.END_TIME, '%Y%m%d')),0) enters,

IFNULL((SELECT SUM(ENTERS) FROM t_statistic_day WHERE LOCATE(store_id,CONCAT(ca.`STOREIDS`,','))>0 AND DATE >= DATE_FORMAT(ca.START_HB_TIME, '%Y%m%d') AND DATE <= DATE_FORMAT(ca.END_HB_TIME, '%Y%m%d')),0) entersHb,

IFNULL((SELECT SUM(ENTERS) FROM t_statistic_day WHERE LOCATE(store_id,CONCAT(ca.`STOREIDS`,','))>0 AND DATE >= DATE_FORMAT(ca.START_TB_TIME, '%Y%m%d') AND DATE <= DATE_FORMAT(ca.END_TB_TIME, '%Y%m%d')),0) entersTb

FROM

t_campaigns ca

WHERE STAFF_ID = 1) tempA

说明：LOCATE(CONCAT(store_id,','),CONCAT(ca.`STOREIDS`,','))>0为store_id存在ca.`STOREIDS`字符串中。

4、用时间叔来当条件时间来比较，把当前时间数据和对比时间数据来比较

-- sql的意识是得到当前日期、当前日期总客流量、当前日期个店平均客流量，对比日期客流量、客流变化率

SELECT DATE_FORMAT(dq.date,'%Y/%m/%d') currentDate,dq.enters enters,dq.entersAvg entersAvg,DATE_FORMAT(tb.date,'%Y/%m/%d') contrastDate,tb.enters entersTb,

IFNULL(FORMAT(((dq.enters - tb.enters)/tb.enters)*100,1),0) rateTb FROM

(SELECT st.date,SUM(enters) enters,FORMAT(SUM(enters)/ca.storeNum,0) entersAvg FROM

t_statistic_day st,

(SELECT *,LENGTH(STOREIDS) - LENGTH(REPLACE(STOREIDS,',',''))+1 storeNum FROM t_campaigns WHERE id = 1) ca

WHERE LOCATE(st.store_id,CONCAT(ca.storeids,',')) > 0 AND st.date >= DATE_FORMAT(ca.START_TIME,'%Y%m%d')

AND st.date <= DATE_FORMAT(ca.END_TIME,'%Y%m%d') GROUP BY DATE) dq

LEFT JOIN

(SELECT st.date,SUM(enters) enters FROM

t_statistic_day st,

(SELECT * FROM t_campaigns WHERE id = 1) ca

WHERE LOCATE(st.store_id,CONCAT(ca.storeids,',')) > 0

AND st.date >= DATE_FORMAT(ca.START_TB_TIME,'%Y%m%d')

AND st.date <= DATE_FORMAT(ca.END_TB_TIME,'%Y%m%d')

GROUP BY DATE) tb

-- 当前日期与对比日期之间相差的天数为一个固定值，

-- 364是拿当前开始时间和对比开始时间计算得出的相隔天数

ON DATEDIFF(dq.date,tb.date) = 364

5、mysql类似rownum的查询语句

SELECT @rownum:=@rownum+1 rownum,s.* FROM (SELECT @rownum:=0) r,t_deal_sum s

6 获取时间差、在时间基础上加上一个时间

6.1 TIMESTAMPDIFF

语法：

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)。

说明：

返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由       interval 参数给出。该参数必须是以下值的其中一个：

参数：

FRAC_SECOND 表示间隔是毫秒

SECOND  秒

MINUTE  分钟

HOUR  小时

DAY  天

WEEK  星期

MONTH  月

QUARTER  季度

YEAR  年

mysql> select TIMESTAMPDIFF(day,'2012-08-24','2012-08-30');

+----------------------------------------------+

| TIMESTAMPDIFF(day,'2012-08-24','2012-08-30') |

+----------------------------------------------+

| 6 |

+----------------------------------------------+

1 row in set (0.00 sec)

6.2 TIMESTAMPADD

语法：

TIMESTAMPADD(interval,int_expr,datetime_expr)

说明：

将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。式中的interval和上文中列举的取值是一样的。

mysql> select TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00');

+-------------------------------------------------+

| TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00') |

+-------------------------------------------------+

| 2012-08-30 12:00:00 |

+-------------------------------------------------+

1 row in set (0.00 sec)

例1：

-- 获取时间差

SELECT CONCAT(TIMESTAMPDIFF(DAY,'2016-09-21 10:17:57','2016-12-21 11:17:57'),'天',TIMEDIFF('11:17:57','10:17:57')) day1;