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;