Sql语句

常用的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                            |
+----------+--------------------------------------------------------------------------+