项目中常用的mysql函数

项目中经常的查询:

join
GROUP BY
order by
经常的流程控制:

case when '' then '' else '' end

经常用的函数:

IFNULL
LEFT
SUM
replace

CURDATE() - INTERVAL 15 DAY

ROUND

DATE_FORMAT

TO_DAYS

ROUND

UNIX_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 '' end

mysql> 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);