RAND()

产生0到1之间的一个随机数，也可用于order by rand() 来获取乱序的排列

FLOOR(x)

返回不大于x的最大整数，向下取整

使用

获取随机的n条数据

select *  from table 
where 
    id>( 
        select floor(rand()*(
                                select max(id) from table
                            )
                    )
        ) 
    and table.t="其他条件" 
    limit 4;

也可以写为

select * from table
where table.t="其他条件"
order by rand() limit 4;

但是测试发现效率没有第一种好

CEILING(x)

返回不小于x的最小整数，向上取整。同CEIL(X)

concat

字符串拼接

mysql> select concat('1','2','3') from test ;
+---------------------+
| concat('1','2','3') |
+---------------------+
| 123 |
+---------------------+

如果连接串中存在NULL，则返回结果为NULL：

mysql> select concat('1','2',NULL,'3') from test ;
+--------------------------+
| concat('1','2',NULL,'3') |
+--------------------------+
| NULL |
+--------------------------+

concat_ws(separator,str1,str2,...) 代表 concat with separator ，是concat()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。

mysql> select concat_ws(':','1','2','3') from test ;
+----------------------------+
| concat_ws(':','1','2','3') |
+----------------------------+
| 1:2:3 |
+----------------------------+ 分隔符为NULL，则返回结果为NULL：

mysql> select concat_ws(NULL,'1','2','3') from test;
+-----------------------------+
| concat_ws(NULL,'1','2','3') |
+-----------------------------+
| NULL |
+-----------------------------+
如果参数中存在NULL，则会被忽略：

mysql> select concat_ws(':','1','2',NULL,NULL,NULL,'3') from test ;
+-------------------------------------------+
| concat_ws(':','1','2',NULL,NULL,NULL,'3') |
+-------------------------------------------+
| 1:2:3 |
+-------------------------------------------+ 可以对NULL进行判断，并用其它值进行替换：

mysql> select concat_ws(':','1','2',ifNULL(NULL,'0'),'3') from bank limit 1;
+---------------------------------------------+
| concat_ws(':','1','2',ifNULL(NULL,'0'),'3') |
+---------------------------------------------+
| 1:2:0:3 |
+---------------------------------------------+

有条件统计

select COUNT(IF(id > 0 , id, null)) from user group by id;

USING

用于连接查询，类似于on，当两个表连接字段相同时入tableA.id=tableB.id可以替换为USING(id),查询字段和查询条件中的tableA.id和tableB.id都可以用id代替

查询优化

IN()

一般数据库将IN()转化为多个or查询条件，查询是否在IN()中的复杂的为O(n)。MySQL将IN中的数据排序，之后采用二分查找,查询效率为O($\log$n)

小结果集驱动大结果集

例: user表10000条数据，class表20条数据 select * from user u left join class c u.userid=c.userid 这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来 select * from class c left join user u c.userid=u.userid

分拆关联查询

例：

select * from A 
join B on A.id = B.uid 
join C on C.rid = B.id
where A.name='tom'

可以拆分为

select * from A where A.name='tom'
select * from B where B.uid=5
select * from C where C.rid in(1,9,5,4)

• 让缓存更高效 单表查询更有几率命中缓存，如果in中之前已经缓存了某些数据，那么in的查询数据就会减少。某个表的数据的改变不会使得其他表的缓存失效。
• 单个表的查询减少锁竞争
• 在应用层做关联而不是数据库，容易对数据库进行拆分，提高性能和扩展性
• in本身也有排序优化，比随机的关联效率高
• 减少冗余记录的查询 在应用层做关联，对某条记录只用查询一次，而在数据库中做关联查询可能会重复访问一部分数据

#

锁

数据库中查找Innodb Status，在Innodb Status中会记录上一次死锁的信息 输入命令：SHOW ENGINE INNODB STATUS

SQL

递归查询

SELECT * FROM discover_comment
WHERE id IN (
    SELECT id FROM (
        SELECT t1.id,
            IF ( find_in_set( comment_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild
        FROM
        ( SELECT id, comment_id FROM discover_comment t WHERE t.type = 'comment' ORDER BY gmt_create DESC ) t1,
        ( SELECT @pids := #{quoteId} ) t2
    ) t3
    WHERE ischild != 0
)

一些SQL

按自然周分组

           select DATE_FORMAT(date_add('1900-01-01',interval floor(datediff(pb.startdate,'1900-01-01')/7)*7 day),'%Y%m%d') as date
           ...
           group by date

两个日期之间最大的连续登录天数

http://www.word666.com/shujuku/78391.html

SELECT *
FROM (SELECT *
   FROM (
       SELECT
        uid,
        max(days)   lianxu_days,
        min(login_day) start_date,
        max(login_day) end_date
       FROM (SELECT
           uid,
           @cont_day :=
           (CASE
           WHEN (@last_uid = uid AND DATEDIFF(created_ts, @last_dt) = 1)
            THEN
             (@cont_day + 1)
           WHEN (@last_uid = uid AND DATEDIFF(created_ts, @last_dt) < 1)
            THEN
             (@cont_day + 0)
           ELSE
            1
           END)                       AS days,
           (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
           @last_uid := uid,
           @last_dt := created_ts                login_day
          FROM (SELECT
              uid,
              from_unixtime(dp.gmt_create) created_ts
             FROM youxk_app_discover_prise dp
             WHERE uid =8
             ORDER BY uid, created_ts) AS t,
           (SELECT
            @last_uid := '',
            @last_dt := '',
            @cont_ix := 0,
            @cont_day := 0) AS t1
         ) AS t2
       GROUP BY uid, cont_ix
       HAVING lianxu_days > 1
      ) tmp
   ORDER BY lianxu_days DESC) ntmp
GROUP BY uid;

使用group by不按id排序

 select t.id
        from (
            SELECT @rownum := @rownum + 1 AS rownum,r.id ,rc.name
            FROM reward r
            INNER  JOIN  (
                        SELECT   reward_type_rel.rid ,rand() random  FROM reward_type_rel
                        INNER JOIN reward_category  ON  reward_type_rel.task_type_id = reward_category.id and reward_category.name = "每日任务"
                        ) AS rd
            ON  r.id = rd.rid AND r.`status`=1 AND r.is_delete=0
            INNER JOIN  reward_category rc
            ON   r.type_id = rc.id AND rc.type=1 AND rc.`name` IN
            <foreach collection="pickTypes" item="type" open="(" close=")" separator=",">
                #{type}
            </foreach>,
            (SELECT @rownum := 0) rr
            order by random 	) as t
            group  by t.name

行转列

多行转单列

group_concat(value Separator ';') 使用分号分隔 group_concat(value) ,默认逗号分隔，等同于group_concat(value Separator ',') group_concat(type,'分数为:',value Separator '; ') 拼接后分隔

多行转多列

SELECT name ,
MAX(CASE type WHEN '数学' THEN score ELSE 0 END ) math,
MAX(CASE type WHEN '英语' THEN score ELSE 0 END ) English ,
MAX(CASE type WHEN '语文' THEN score ELSE 0 END ) Chinese 
FROM stu  
GROUP BY name

单行转单列

select groupid certType, grouptitle certTitle
from usergroup
where groupid in (
    SELECT
    distinct SUBSTRING_INDEX(SUBSTRING_INDEX(@otherGroup,',',help_topic_id+1),',',-1) AS num
    FROM
    mysql.help_topic ,
    (select @otherGroup :=(select concat( yujian_group, ',' , shequ_group,',',sheying_group,',',taidu_group,',',other_group)other_group from  user_group_all where uid =#{uid})) as t
    WHERE
    help_topic_id < LENGTH(@otherGroup)-LENGTH(REPLACE(@otherGroup,',',''))+1
)

零散汇总

数据库特殊字符

创建数据库时使用utf8mb4-UTF-8 Unicode，可以存储用户名等出现的特殊字符

运行null值的问题

（1）负向比较（例如：!=）会引发全表扫描； （2）如果允许空值，不等于 (!=) 的查询，不会将空值行 (row) 包含进来，此时的结果集往往是不符合预期的，此时往往要加上一个 or 条件，把空值 (is null) 结果包含进来； （3）or 可能会导致全表扫描，此时可以优化为 union 查询；

explain select * from user where id=1 or id is null;
explain select * from user where id=1 union select * from user where id is null;