一、 名称解释
常量:将某些查询的结果集当成一个变量,结果出来后直接参与其他计算,不需要再次查询的结果。
二、非常实用的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;
相关推荐
Linux运维-运维课程d2-MySQL基本SQL语句(下)-08-SQL查询语句之SQL五子句.mp4
总的来说,这个工具对于那些需要在Oracle和MySQL之间迁移数据库应用的开发者来说,是一个非常实用的资源。它通过自动化处理减少了手动转换的工作量,降低了错误率,有助于提高项目迁移的效率和质量。
它定义了Java程序与各种关系型数据库之间的交互方法,包括建立和关闭连接、执行SQL语句、处理结果集等。MySQL Connector/J实现了这些接口,使得Java开发者无需关心底层数据库的实现细节,只需关注业务逻辑。 2. **...
2. **创建Statement或PreparedStatement**:连接建立后,可以创建Statement对象用于执行静态SQL语句,或者PreparedStatement对象用于预编译和执行参数化的SQL语句。 3. **执行SQL操作**:通过Statement或...
mysql-全国五级省市县镇村sql语句,直接在mysql数据库中执行sql语句即可。
Linux运维-运维课程d2-MySQL基本SQL语句(上)-01-客户端工具mysql使用.mp4
1. `mysql-connector-java-5.1.37.jar`:这是主驱动文件,包含了所有必要的类和方法,使得Java应用程序能够与MySQL数据库建立连接,执行SQL语句,并处理结果。例如,使用`DriverManager.getConnection()`方法创建...
Linux运维-运维课程d2-MySQL基本SQL语句(下)-24-别名机制.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(下)-18-ORDER BY子句.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(上)-10-删除数据表.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(下)-14-DISTINCT去重操作.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(下)-18-ORDER BY子句.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(上)-09-重命名与移动操作.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(上)-03-客户端工具mysqladmin使用.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(上)-12-数据的修改操作.mp4
或者使用`PreparedStatement`来预编译SQL语句,提高性能和安全性。 4. **结果集处理**:`ResultSet`对象包含了SQL查询的结果,可以通过迭代遍历每一行数据。 5. **事务管理**:JDBC支持事务的概念,可以使用`...
一旦连接建立,就可以通过`Statement`或`PreparedStatement`对象执行SQL语句,并获取`ResultSet`来处理查询结果。 **关键类和接口** - `java.sql.DriverManager`: 管理数据库连接,负责加载JDBC驱动并创建数据库...
Linux运维-运维课程d2-MySQL基本SQL语句(上)-02-系统默认的数据库信息.mp4
Linux运维-运维课程d2-MySQL基本SQL语句(下)-05-数据类型之文本类型.mp4