mysql的一些小知识点

1、select a.id from Weather a,Weather b where

a.recordDate=DATE_ADD(b.recordDate,INTERVAL 1 DAY) and a.Temperature > b.Temperature

注：DATE_ADD(b.recordDate,INTERVAL 1 DAY) 前一天

2、select s.user_id,round(avg(if(c.action = 'confirmed',1,0)),2) confirmation_rate

from Signups s left join Confirmations c on s.user_id=c.user_id group by s.user_id;

注：round(avg(if(c.action = 'confirmed',1,0)),2)     

确认率（confirmed占所有action的比率）四舍五入保留两位小数

3、（1）SELECT COALESCE(column_name, 0) FROM table_name;

（2）SELECT IFNULL(column_name, 0) FROM table_name;

（3）SELECT 
            CASE 
                WHEN column_name IS NULL THEN 0 
                ELSE column_name 
            END
        FROM table_name;

注：将null值替换成0，（2）不适合多表或group by 

4、（1）select * from cinema where mod(id,2) = 1 and description !="boring" order by rating desc

（2）select * from employees where emp_no % 2 = 1 and last_name <> 'Mary'  order by hire_date desc;

（3）select * from employees where emp_no & 1 = 1 and last_name <> 'Mary' order by hire_date desc;
注：（1）mod(a,b) 在sql中的意思是 a / b 的余数；mod(id, 2)=1 是指id是奇数。mod(id, 2)=0 是指id是偶数。

（2）id是奇数：(id % 2) = 1; id是偶数：(id % 2) = 0;

（3）id是奇数：(id & 1) = 1; id是偶数：(id & 1) = 0;