mysql 注意事项

1.where name = 'zhangsan' or name  = '李四' and price >= 10
有优先级的 and优先级大于or
正解:where (name = 'zhangsan' or name  = '李四') and price >= 10


2.order name desc,price asc
多条件排序


3.like模糊查询区分大小写 like BINARY 'Z%' 
[]多个
_单个字符
[^]不包含多个


4. not between and 
   not  
   
5. LPAD(day(CURRENT_DATE()),4,0) 自动补零
   date_format(CURRENT_DATE(),'%Y-%m')
   select AVG(DISTINCT id) from t_user 去掉重复值然后取平均
   
6. 
select id, `status`, count(*) as number from t_user 
where id  >= 2
GROUP BY id
having number >=1
order by status desc


7.union all包括重复项 union去掉重复项


8.insert into user_(username_) (select username_ from user_)
  insert into user_(username_) values(),()
  
9.复合主键(两个字段必须有一个不同)
  联合主键(两个字段拼接起来不同)
  
10. select ROUND(2.509,2)


11. select FIND_IN_SET('3','1,2,6,8')


12.存储函数
BEGIN
DECLARE sTemp VARCHAR(400);
DECLARE sTempChd VARCHAR(400);


SET sTempChd = '';
SET sTemp = cast(areaId as char);


WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(child_id) INTO sTempChd FROM group_c where FIND_IN_SET(parent_id,sTemp)>0;
END WHILE;
return sTemp;
END