mysql高级扩展
有关网址：
1、时间比较
mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
2、时间戳比较
select * from ds_order where order_state = 10 and (unix_timestamp(now()) - add_time)>600
3、在大数据的情况下，内连接是比外连接要快很多
join 处理出来13000左右，大概是7秒
left join == LEFT OUTER JOIN 处理出来23000左右，大概是49秒
4、mysql查最近的值，比如18查询最近的值，17，18，19
SELECT
*
FROM
(
SELECT DISTINCT
(user_id),
rob_prize
FROM
ds_purchase_record
WHERE
rob_prize <> 18
OR rob_prize = 18
ORDER BY
ABS(rob_prize-18) ASC
) AS one
GROUP BY
user_id
order by ABS(rob_prize-18) ASC;
5、用到and or
or 的写法要写()
select * from message where (typeid=31) or (typeid=30) or (typeid=32) or (typeid=33) order by id desc;
6、存储过程与创建函数的使用
存储过程与自定义函数的不同，存储过程可以返回多个字段，自定义函数只能返回一个字段
（1）存储过程：
# 删除过程
DROP PROCEDURE IF EXISTS getOrderInfo;
# 创建过程，只有IN的
delimiter //
CREATE PROCEDURE getOrderInfo(IN oid int(11))
BEGIN
select * from ds_order where order_id=oid;
END //
delimiter ;
call getOrderInfo(21);
# 删除过程
DROP PROCEDURE IF EXISTS getOrderField;
#创建过程，有IN（条件字段）的，也有OUT（输出字段）的
delimiter //
CREATE PROCEDURE getOrderField(IN oid int(11),OUT sn VARCHAR(255))
BEGIN
select order_sn INTO sn from ds_order where order_id=oid;
END //
delimiter ;
call getOrderField(21,@sn);
SELECT @sn;
# 删除过程
DROP PROCEDURE IF EXISTS getInfo;
delimiter //
CREATE PROCEDURE getInfo(IN oid int(11),IN dtable VARCHAR(25))
BEGIN
set @s = concat("select * from ",dtable," WHERE order_id = ",oid);
prepare stmt from @s; #预定义SQL
execute stmt; #填充SQL
DEALLOCATE prepare stmt; #
END //
delimiter;
call getInfo(21,'ds_order');
7、根据条件排序
SELECT
fg.*, a.uid,
a.if_boss,
CASE
WHEN (a.if_boss = 0 && uid = 179150) THEN
1
WHEN (a.if_boss is null) THEN
2
END AS num
FROM
ds_fight_group AS fg
LEFT JOIN ds_assemble AS a ON fg.fg_id = a.fg_id
ORDER BY
num ASC
SELECT
count(
CASE
WHEN da.if_boss = a.id THEN
1
ELSE
NULL
END
) as `_member`,
count(case when o.order_state>10 then 1 else null end) as `_order_count`,
a.uid,
a.id,
a.a_type
FROM
ds_assemble AS a
LEFT JOIN ds_assemble AS da ON da.if_boss = a.id
LEFT JOIN ds_order as o on o.order_sn=da.order_sn
WHERE
a.id > 0
AND a.if_boss = 0
GROUP BY
a.id
注意点：如果要判断null的话，这里要写 is null
SELECT
count(
CASE
WHEN da.if_boss = a.id THEN
1
ELSE
NULL
END
) AS fnum,
count(
CASE
WHEN o.order_state > 10 THEN
1
ELSE
NULL
END
) AS onum,
a.*, wx.avatar,
wx.mobile
FROM
ds_assemble AS a
LEFT JOIN ds_assemble AS da ON da.if_boss = a.id
LEFT JOIN ds_order AS o ON o.pay_sn = da.order_sn
LEFT JOIN ds_member AS m ON a.uid = m.member_id
LEFT JOIN ds_wxtempuser AS wx ON m.member_name = wx.mobile
LEFT JOIN ds_order AS orr ON a.order_sn = orr.pay_sn
WHERE
1 = 1
AND a.id > 0
AND a.if_boss = 0
AND a.fg_id = 7
AND (
a.add_time + 62400 > 1557133850
)
AND orr.order_state > 10
GROUP BY
a.id
HAVING
a.a_type > (onum + 1)
LIMIT 0,10
全部竞猜记录
SELECT
m.member_name,
gr.`name`,
gr.gj_id,
pr.rob_prize,
pr.addtime,
pr.pay_sn
FROM
ds_purchase_record AS pr
LEFT JOIN ds_member AS m ON pr.user_id = m.member_id
LEFT JOIN ds_grabjade AS gr ON pr.gid = gr.gj_id
ORDER BY
addtime DESC
根据一条纪录的维度查已售数量
SELECT
count(
CASE
WHEN da.if_boss = a.id THEN
1
ELSE
NULL
END
) AS fnum,
count(
CASE
WHEN da.a_status=1 && da.uid>0 THEN
1
ELSE
NULL
END
) AS onum,
a.*
FROM
ds_assemble AS a
LEFT JOIN ds_assemble AS da ON da.if_boss = a.id
WHERE
1 = 1
AND a.id > 0
AND a.if_boss = 0
AND a.fg_id = 16
and a.a_status=1
GROUP BY
a.id
这是保存的时间格式
<!-- 按日查询 --> SELECT DATE_FORMAT(created_date,'%Y-%m-%d') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time <!-- 按月查询 --> SELECT DATE_FORMAT(created_date,'%Y-%m') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time <!-- 按年查询 --> SELECT DATE_FORMAT(created_date,'%Y') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time <!-- 按周查询 --> SELECT DATE_FORMAT(created_date,'%Y-%u') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time
使用 FROM_UNIXTIME 可以把时间戳转换为日期：
select FROM_UNIXTIME(invest_time,'%Y年%m月%d') from crm_invest_apply
按指定排序
select * from fc_image where i_id in (50,42,58,41,43,44,60) ORDER BY field(i_id,50,42,58,41,43,44,60)
mysql也可以用IF来做判断
SELECT
age,
IF (
age = 10, // 条件
'你好', // 为true时的答案
'你不好' // 为false时的答案
) cn
FROM
fc_optimization
☐ HAVING cn = '你好' //最后的条件，不能用where去查条件，会报错
