项目中常用的mysql函数

项目中经常的查询:joinGROUP BYorder by经常的流程控制:case when '' then '' else '' end经常用的函数:IFNULLLEFTSUMreplaceCURDATE() - INTERVAL 15 DAYROUNDDATE_FORMATTO_DAYSROUNDUNIX_TIMESTAMP最实用的编程语句(mysql 基本操作)学习本章目的1.掌握mysql数据库操作2.掌握mysql数据表操作3.掌握mysql语句操作删除操作delete a.*,b.* from student a,score b where a.id=b.s_id;程序的灵魂(mysql函数)mysql 的内置函数不但可以在select查询语句中使用,同样可以在insert,update,delete等语句中使用.数学常用函数:floor(x):返回不大于x的最大整数值mysql> select floor(1.5),floor(-2);+------------+-----------+| floor(1.5) | floor(-2) |+------------+-----------+|          1 |        -2 |+------------+-----------+1 row in set (0.03 sec)ceil(x):返回不小于x的最小整数值mysql> select ceil(1.5),(-2);+-----------+------+| ceil(1.5) | (-2) |+-----------+------+|         2 |   -2 |+-----------+------+1 row in set (0.00 sec)rand(x):返回0~1的随机数mysql> select rand(),rand()*10;+--------------------+-------------------+| rand()             | rand()*10         |+--------------------+-------------------+| 0.3073231729747012 | 6.903188691384335 |+--------------------+-------------------+1 row in set (0.01 sec)round(x,y):返回x后面y为小数点,四舍五入mysql> select round(1.345,2);+----------------+| round(1.345,2) |+----------------+|           1.35 |+----------------+1 row in set (0.00 sec)字符串常用函数concat(s1,s2);mysql> select concat('select',' * ',' from ',' lisheng ');+---------------------------------------------+| concat('select',' * ',' from ',' lisheng ') |+---------------------------------------------+| select *  from  lisheng                     |+---------------------------------------------+1 row in set (0.00 sec)left(s,n);mysql> select left('lisheng',2);+-------------------+| left('lisheng',2) |+-------------------+| li                |+-------------------+1 row in set (0.02 sec)repeat(s,n);mysql> select repeat('li',2);+----------------+| repeat('li',2) |+----------------+| lili           |+----------------+1 row in set (0.00 sec)replace(s,s1,s2);mysql> select replace('li sheng','li','zhang');+----------------------------------+| replace('li sheng','li','zhang') |+----------------------------------+| zhang sheng                      |+----------------------------------+1 row in set (0.01 sec)常用的日期时间函数:curdate()UNIX_TIMESTAMP()+----------------------------+| CURDATE() - INTERVAL 2 DAY |+----------------------------+| 2015-11-02                 |+----------------------------+1 row in set (0.04 sec)mysql> select curdate();+------------+| curdate()  |+------------+| 2015-11-04 |+------------+1 row in set (0.00 sec)mysql> select current_time();+----------------+| current_time() |+----------------+| 15:21:52       |+----------------+1 row in set (0.00 sec)mysql> select current_time() - interval 2 hour;+----------------------------------+| current_time() - interval 2 hour |+----------------------------------+| 13:22:08                         |+----------------------------------+1 row in set (0.00 sec)条件判断函数:if(true,yes,no);mysql> select if(1>2,'yes','no');+--------------------+| if(1>2,'yes','no') |+--------------------+| no                 |+--------------------+1 row in set (0.00 sec)ifnull(one,two);mysql> select ifnull(1,2);+-------------+| ifnull(1,2) |+-------------+|           1 |+-------------+1 row in set (0.02 sec)mysql> select ifnull(null,3)    -> ;+----------------+| ifnull(null,3) |+----------------+|              3 |+----------------+1 row in set (0.00 sec)case when '' then '' else '' endmysql> select case when 1 then 'lisheng' else 'nobody' end;+----------------------------------------------+| case when 1 then 'lisheng' else 'nobody' end |+----------------------------------------------+| lisheng                                      |+----------------------------------------------+1 row in set (0.00 sec)其他函数:cast(x as type);