鼠…鼠标放错地方了!

常用的sql:

1、SELECT uid,email,REVERSE( left(REVERSE(email),LOCATE('.', REVERSE(email))-1) ) as t FROM `tb_user_profile` 对与email,111111@qq.com,截取为com

 

(1)length用法

 length 计算长度字段
 SELECT * FROM admin WHERE LENGTH(username) < 6  

(2)DATE_FORMAT、DISTINCT 用法

1、DATE_FORMAT:以不同的格式显示日期/时间数据; 
2、DISTINCT:去重复

SELECT 
    DATE_FORMAT(trade_time,'%Y%m') as mon, 
    sum(order_amount) as rmb,
    count(*) as totalRecharge ,
    count(DISTINCT(uid)) as totalUser
FROM tb_pay
WHERE trade_status=1
GROUP BY mon

 

(3)FROM_UNIXTIME、IF、UNIX_TIMESTAMP用法

 1、FROM_UNIXTIME:时间戳时间以"YYYY-MM-DD H:i:s"格式来示。
 2、IF: 格式:IF(Condition,A,B)  结果:当Condition为TRUE时,返回A;当Condition为FALSE时,返回B。
 3、UNIX_TIMESTAMP:转换成unix时间戳

SELECT 
    FROM_UNIXTIME(a.in_time,'%Y年%m月%d日') as date,
    b.phone,
    c.real_name,
    a.uid,
    a.ip,
    IF(a.type=0,'login','logout') as login_status,
    FROM_UNIXTIME(a.in_time,'%H:%i:%s') as time
FROM tb_user_login_out a
LEFT JOIN tb_user b on a.uid = b.id 
LEFT JOIN tb_open_account c ON a.uid = c.uid
WHERE a.in_time BETWEEN UNIX_TIMESTAMP('2017-02-21 08:00:00') AND UNIX_TIMESTAMP('2017-02-23 23:00:00') and c.real_name !='';

 

(4)select 的二次查询SELECT 

SELECT
DISTINCT(uid)
as uid, login_out_time FROM tb_user_login_out WHERE uid in(    SELECT id from (        select a.id, a.phone, b.real_name from tb_user a LEFT JOIN tb_open_account b ON a.id = b.uid       LEFT JOIN tb_user_profile c ON a.id = c.uid       where a.phone_verify = 1 and c.isbind = 1 and b.real_name <>'' ORDER BY a.id         )as c
)
AND login_out_time='20170225'

 (5)CASE...WHEN...THEN、having用法

      1、case: 语句 WHEN  xxxx THEN xxx 最后 END

     2、order by:多个字段 直接 filed1 desc, field2 desc, field3 desc

     3、having:子查询,已经查询的结果再次过滤   

        SELECT
        a.uid,
count( if (a.total_sell_balance>a.total_buy_balance,true,null)) / count(*) as num1,
count(*) as num2,
count( if (a.total_sell_balance>a.total_buy_balance,true,null)) as profitnum, CASE WHEN b.trade_days <= 20 THEN b.trade_days WHEN b.trade_days > 20 THEN (b.trade_num / b.trade_days) * 20 END as avgnum, b.trade_days as trade_days FROM ".$this->tableName()." as a LEFT JOIN tb_stat_month_trade as b on a.uid=b.uid WHERE a.amount = 0 GROUP BY a.uid HAVING num2>4 AND num1>0 ORDER BY num1 DESC,avgnum DESC,trade_days DESC LIMIT 0,10

 (6)字符串处理

 1、CONCAT()函数:用于将多个字符串连接成一个字符串。
使用数据表Info作为示例,
例子:SELECT id,name
FROM info LIMIT 1
+----+--------+ | id | name | +----+--------+ | 1 | BioCyc | +----+--------+ CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。可以有一个或多个参数
2、CONCAT_WS()参数之间的分隔符
语法为:CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。
第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
SELECT CONCAT_WS(
'_',id,name) AS con_ws FROM info LIMIT 1;
返回结果为
+----------+ | con_ws | +----------+ | 1_BioCyc | +----------+ SELECT CONCAT_WS(',','First name',NULL,'Last Name');
返回结果为
+----------------------------------------------+ | CONCAT_WS(',','First name',NULL,'Last Name') | +----------------------------------------------+ | First name,Last Name | +----------------------------------------------+
3、GROUP_CONCAT()函数--合并记录
GROUP_CONCAT函数返回一个字符串结果,该结果由分组中的值连接组合而成。
使用表info作为示例
SELECT locus,id,journal
FROM info WHERE locus IN('AB086827','AF040764');
返回结果为
+----------+----+--------------------------+ | locus | id | journal | +----------+----+--------------------------+ | AB086827 | 1 | Unpublished | | AB086827 | 2 | Submitted (20-JUN-2002) | | AF040764 | 23 | Unpublished | | AF040764 | 24 | Submitted (31-DEC-1997) | +----------+----+--------------------------+
使用语法及特点: GROUP_CONCAT(
[DISTINCT] expr [,expr ...] [ORDER BY ASC | DESC]
[SEPARATOR str_val]
) group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])使用示例: SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;
返回结果为
+----------+------------------+ | locus | GROUP_CONCAT(id) | +----------+------------------+ | AB086827 | 1,2 | | AF040764 | 23,24 | +----------+------------------+ SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;
返回结果为
+----------+----------------------------------------------------------+ | locus | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') | +----------+----------------------------------------------------------+ | AB086827 | 2_1 | | AF040764 | 24_23 | +----------+----------------------------------------------------------+ SELECT locus,GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;
返回结果为
+----------+--------------------------------------------------------------------------+ | locus | GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') | +----------+--------------------------------------------------------------------------+ | AB086827 | 2, Submitted (20-JUN-2002). 1, Unpublished | | AF040764 | 24, Submitted (31-DEC-1997) . 23, Unpublished | +----------+--------------------------------------------------------------------------+
 posted on 2017-08-04 18:01  xiao韩de博客  阅读(248)  评论(0编辑  收藏  举报