MySQL函数总结
CRUD:增删改查 create read update delete
插入语句：insert into 表名（字段名）value(相对应的值)；
插入多条语句：insert into 表面（字段名）values（相对应的值），（相对应的值）；
修改： update 表名 set 字段名=值 where 条件列表；
删除：delete from 表名 where 条件列表；
查询：
查询所有：select * from 表名 where 条件列表；
查询某些字段：select 字段，字段 from 表名 where 条件列表；
数据查询：
1、select userName as "用户名"，password “密码” from users;
则返回： 用户名 密码
2、select userName as "用户名"，“软院”as “学校名称”from users;
则返回：用户名 学校名称
aa 软院
bb 软院
3、select userName ,"软院" from users;
则返回 ： userName 软院
aa 软院
bb 软院
4、select userName from users limit 0,4 ;
(分页 ) 从第一条（下标为0）开始取四条
5、查询排序：
select * from users order by height;//默认为升序
select * from users order by height asc;
select * from users order by height desc;
MySql中的常用函数：
（一）字符串函数
1、concat（s1,s2,s3,...,sn）连接字符串；
2、insert（str，pos , len ,newstr）;
如：insert（“abcd”，1,2，“xp” ）; xpcd
3、lower（str） lcase( str ) 把str所有字符变成小写；
upper（str）ucase（str） 把str所有字符变成大写；
4、left ( str,len)
如：left（abcd，3）; abc
5、right（str，len）; bcd
6、lpad(str,len,padstr)
如：mysql>select lpad("abc",5,"***"); **abc// 长度大于字符串长度 则补入*补成len长度的字符串
mysql>select lpad("abc",2,"**"); ab //长度小于字符串长度，则从左裁剪等长
7、rpad(str,len,padstr)
8、replace（str ，from_str,to_str）
如：mysql> select replace("abcd","ab","guanxin");
+--------------------------------+
| replace("abcd","ab","guanxin") |
+--------------------------------+
| guanxincd |
+--------------------------------+
9、trim（str） //去掉字符串前后空格
10、substring（str，pos,len）
如：mysql> select substring("abcdef",1,5);
+-------------------------+
| substring("abcdef",1,5) |
+-------------------------+
| abcde |
+-------------------------+
(二) 数值函数
1、abs(x) //返回x的绝对值
如：mysql> select abs(-3.14);
+------------+
| abs(-3.14) |
+------------+
| 3.14 |
+------------+
2、rand()//返回0-1之间的随机数
如：mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.38327023888311373 |
+---------------------+
1 row in set
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.6705344940261305 |
+--------------------+
1 row in set
Rand(x);//返回为一样的0-1之间的随机数
如：mysql> select rand(1);
+---------------------+
| rand(1) |
+---------------------+
| 0.40540353712197724 |
+---------------------+
1 row in set
mysql> select rand(1);
+---------------------+
| rand(1) |
+---------------------+
| 0.40540353712197724 |
+---------------------+
1 row in set
mysql> select rand(11);
+-------------------+
| rand(11) |
+-------------------+
| 0.907234631392392 |
+-------------------+
1 row in set
3、truncate（x,y）//x:小数 y:取小数点几位
如：mysql> select truncate(3.14566,3);
+---------------------+
| truncate(3.14566,3) |
+---------------------+
| 3.145 |
+---------------------+
1 row in set
mysql> select truncate(3.14566,0);
+---------------------+
| truncate(3.14566,0) |
+---------------------+
| 3 |
+---------------------+
1 row in set
（三）日期函数：
1、curdate() //当前日期
如：mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2013-09-23 |
+------------+
1 row in set
2、curtime()//当前时间
如：mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 22:54:52 |
+-----------+
1 row in set
3、now()、 localtime()、localtime、sysdate()、
如：返回的都是：
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-09-23 22:56:27 |
+---------------------+
1 row in set
mysql> select localtime();
+---------------------+
| localtime() |
+---------------------+
| 2013-09-23 22:56:54 |
+---------------------+
1 row in set
mysql> select localtime;
+---------------------+
| localtime |
+---------------------+
| 2013-09-23 22:57:26 |
+---------------------+
1 row in set
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2013-09-23 22:58:04 |
+---------------------+
1 row in set
4、year(date)//返回年份
如：mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2013 |
+-------------+
1 row in set
mysql> select(localtime());
+---------------------+
| (localtime()) |
+---------------------+
| 2013-09-23 22:59:40 |
+---------------------+
1 row in set
5、month(date)//返回月份
如：mysql> select month(localtime);
+------------------+
| month(localtime) |
+------------------+
| 9 |
+------------------+
1 row in set
mysql> select month(sysdate());
+------------------+
| month(sysdate()) |
+------------------+
| 9 |
+------------------+
1 row in set
6、monthname(date)//返回月份的英文名
如：mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| September |
+------------------+
1 row in set
（四）流程函数
1、if（expr1，expr2，expr3）；expr1如果为true 则返回expr2的值，如果为false 则返回expr3的值
mysql> select if(age=20,100,age) from student;
+--------------------+
| if(age=20,100,age) |
+--------------------+
| 21 |
| 22 |
| 100 |
| 28 |
| 26 |
+--------------------+
5 rows in se
2、ifnull(expr1,expr2) 如果expr1不为空，则返回expr1的值，如果expr1为空则返回expr2的值
mysql> select userName from student;
+----------+
| userName |
+----------+
| NULL |
| 李四 |
| 张海天 |
| 张欣 |
| 张传 |
+----------+
5 rows in set
mysql> select ifnull(userName,"空");
mysql> select ifnull(userName,"空")from student;
+-----------------------+
| ifnull(userName,"空") |
+-----------------------+
| 空 |
| 李四 |
| 张海天 |
| 张欣 |
| 张传 |
+-----------------------+
5 rows in set
3、case when(value) then (result) else (defailt) end
mysql> select age from student;
+-----+
| age |
+-----+
| 21 |
| 22 |
| 20 |
| 28 |
| 26 |
+-----+
5 rows in set
mysql> select case when age>27 then "最大" else age end
from student;
+-------------------------------------------+
| case when age>27 then "最大" else age end |
+-------------------------------------------+
| 21 |
| 22 |
| 20 |
| 最大 |
| 26 |
+-------------------------------------------+
5 rows in set
4、case (expr) when (value) then (result) end
mysql> select age from student;
+-----+
| age |
+-----+
| 21 |
| 22 |
| 20 |
| 28 |
| 26 |
+-----+
5 rows in set
mysql> select case age when 28 then "最大" when 20 then "最小" else age end from student;
+---------------------------------------------------------------+
| case age when 28 then "最大" when 20 then "最小" else age end |
+---------------------------------------------------------------+
| 21 |
| 22 |
| 最小 |
| 最大 |
| 26 |
+---------------------------------------------------------------+
5 rows in set
(五)系统函数
1、返回当前数据库名称：
mysql> select database();
+--------------+
| database() |
+--------------+
| student_test |
+--------------+
1 row in set
2、返回当前数据库版本信息
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25 |
+-----------+
1 row in set
3、返回当前登录用户信息：
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set
4、加密后返回字符串
mysql> select password(userNumber) from student where userId=1;
+-------------------------------------------+
| password(userNumber) |
+-------------------------------------------+
| *6FECC218350DB1E056A9D5DC8A42CD3948AFEB6D |
+-------------------------------------------+
1 row in set
mysql> select md5(userNumber) from student where userId=1;
+----------------------------------+
| md5(userNumber) |
+----------------------------------+
| 3785ef83d0f3ea3541f3a1cb49f75b0e |
+----------------------------------+
1 row in set
