项目中经常的查询:
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);
项目中常用的mysql函数
最新推荐文章于 2022-03-26 10:13:16 发布