一:字符类常用函数:
1.CONCAT()函数,用于连接多个字符:
mysql> select concat('aa','bb','cc');
+------------------------+
| concat('aa','bb','cc') |
+------------------------+
| aabbcc |
+------------------------+
2.CONCAT_WS(),用于按照指定分隔符连接多个字符,第一个参数为指定的分隔符:
mysql> select concat_ws('-','aa','bb','cc');
+-------------------------------+
| concat_ws('-','aa','bb','cc') |
+-------------------------------+
| aa-bb-cc |
+-------------------------------+
3.FORMAT()函数,用于数字格式化,第一个参数为待格式化的数字,第二个参数为保留几位小数,返回的是格式化后的字符串:
mysql> select format(14620.564,2);
+---------------------+
| format(14620.564,2) |
+---------------------+
| 14,620.56 |
+---------------------+
4.LOWER()函数,用于将字符串全部变成小写:
mysql> select lower('ABcDef');
+-----------------+
| lower('ABcDef') |
+-----------------+
| abcdef |
+-----------------+
5.UPPER()函数,用于将字符串全部变为大写:
mysql> select upper('abcDef');
+-----------------+
| upper('abcDef') |
+-----------------+
| ABCDEF |
+-----------------+
6.LEFT()函数,用于从左边截取字符串,第一个参数为待截取字符串,第二个参数为截取字符个数,只能为正数:
mysql> select left('abcdef',2);
+------------------+
| left('abcdef',2) |
+------------------+
| ab |
+------------------+
6.RIGHT()函数,用于从右边截取字符串,第一个参数为待截取字符串,第二个参数为截取字符个数,只能为正数:
mysql> select right('abcdef',2);
+-------------------+
| right('abcdef',2) |
+-------------------+
| ef |
+-------------------+
7.LENGTH()函数,用于返回字符串的长度,包括前后中间空格:
mysql> select length('abc def');
+-------------------+
| length('abc def') |
+-------------------+
| 7 |
+-------------------+
8.LTRIM()函数,用于删除字符串左边的空格:
mysql> select ltrim(' abc');
+------------------+
| ltrim(' abc') |
+------------------+
| abc |
+------------------+
9.RTRIM()函数,用于删除字符串右边的空格:
mysql> select rtrim('abc ');
+-----------------+
| rtrim('abc ') |
+-----------------+
| abc |
+-----------------+
10.TRIM()函数,用于删除字符串左右空格,不能删除中间的:
mysql> select trim(' ab cdef ');
+------------------------+
| trim(' ab cdef ') |
+------------------------+
| ab cdef |
+------------------------+
11.TRIM(LEADING ? FROM ?)函数,用于删除字符串左边特定的字符,第一个?为要删除的字符,区分大小写,第二个?为待删除的字符串:
mysql> select trim(leading 'x' from 'xxabc');
+--------------------------------+
| trim(leading 'x' from 'xxabc') |
+--------------------------------+
| abc |
+--------------------------------+
12.TRIM(TRAILING ? FROM ?)函数,用于删除字符串右边特定的字符,第一个?为要删除的字符,区分大小写,第二个?为待删除的字符串:
mysql> select trim(trailing 'X' from 'abcXXXX');
+-----------------------------------+
| trim(trailing 'X' from 'abcXXXX') |
+-----------------------------------+
| abc |
+-----------------------------------+
13.TRIM(BOTH ? FROM ?)函数,用于删除字符串左右特定的字符,第一个?为要删除的字符,区分大小写,第二个?为待删除的字符串:
mysql> select trim(both 'X' from 'XXXabcXXX');
+---------------------------------+
| trim(both 'X' from 'XXXabcXXX') |
+---------------------------------+
| abc |
+---------------------------------+
14.REPLACE()函数,用于替换字符,第一个参数为传入的字符串,第二个参数为待替换的字符,第三个参数为替换后的字符:
mysql> select replace('-abc-def','-','!');
+-----------------------------+
| replace('-abc-def','-','!') |
+-----------------------------+
| !abc!def |
+-----------------------------+
15.SUBSTRING()函数,字符串截取函数,第一个参数为传入的字符串,第二个参数为从第几位截取(注意下标从1开始),第三个参数为截取几个,如果第二个参数为负,则表示从右边第几位截取(还是往右截取):
mysql> select substring('abcdefg',1,2);
+--------------------------+
| substring('abcdefg',1,2) |
+--------------------------+
| ab |
+--------------------------+
下面表示从第二位截取到字符串结尾
mysql> select substring('abcdefg',2);
+------------------------+
| substring('abcdefg',2) |
+------------------------+
| bcdefg |
+------------------------+
下面表示从右边第三位截取到最后:
mysql> select substring('abcdefg',-3);
+-------------------------+
| substring('abcdefg',-3) |
+-------------------------+
| efg |
+-------------------------+
下面表示从右边第三位截取,截取2位:
mysql> select substring('abcdefg',-3,2);
+---------------------------+
| substring('abcdefg',-3,2) |
+---------------------------+
| ef |
+---------------------------+
16.LIKE的更深层次用法,不同寻常哦:
如果让你从下面这张表中筛选出username中包含‘%’的数据,你会咋做?
mysql> select * from users;
+----+----------+----------------------------------+------+------+
| id | username | password | sex | age |
+----+----------+----------------------------------+------+------+
| 1 | tom | 123456 | 0 | 26 |
| 3 | jack | 111111 | 0 | 27 |
| 4 | jean | admin | 0 | 34 |
| 5 | john | 96e79218965eb72c92a549dd5a330112 | 0 | 31 |
| 6 | rose | e10adc3949ba59abbe56e057f20f883e | 0 | 33 |
| 8 | admin | 111111 | 1 | 25 |
| 9 | %jack | 1234560 | 0 | 28 |
+----+----------+----------------------------------+------+------+
如果你这样操作:
mysql> select * from users where username like '%%%';
+----+----------+----------------------------------+------+------+
| id | username | password | sex | age |
+----+----------+----------------------------------+------+------+
| 1 | tom | 123456 | 0 | 26 |
| 3 | jack | 111111 | 0 | 27 |
| 4 | jean | admin | 0 | 34 |
| 5 | john | 96e79218965eb72c92a549dd5a330112 | 0 | 31 |
| 6 | rose | e10adc3949ba59abbe56e057f20f883e | 0 | 33 |
| 8 | admin | 111111 | 1 | 25 |
| 9 | %jack | 1234560 | 0 | 28 |
+----+----------+----------------------------------+------+------+
返回的结果明显不是我们想要的!!!
此时应该这样做:
ESCAPE '1' 表示 1后面的%不需要解析,当然你可以把1替换成任意字符
mysql> select * from users where username like '%1%%' escape '1';
+----+----------+----------+------+------+
| id | username | password | sex | age |
+----+----------+----------+------+------+
| 9 | %jack | 1234560 | 0 | 28 |
+----+----------+----------+------+------+
二:数值运算符与函数:
1.CEIL()函数,表示向上取整:
mysql> select ceil('152.44');
+----------------+
| ceil(152.44) |
+----------------+
| 153 |
+----------------+
2.FLOOR()函数,表示向下取整:
mysql> select floor('125.99');
+-----------------+
| floor(125.99) |
+-----------------+
| 125 |
+-----------------+
3.ROUND()函数,四舍五入:
mysql> select round(152.4);select round(152.5);
+--------------+
| round(152.4) |
+--------------+
| 152 |
+--------------+
1 row in set (0.00 sec)
+--------------+
| round(152.5) |
+--------------+
| 153 |
+--------------+
1 row in set (0.01 sec)
mysql> select round(152.362,2);
+------------------+
| round(152.362,2) |
+------------------+
| 152.36 |
+------------------+
4.DIV,整数除法:
mysql> select 5/2;
+--------+
| 5/2 |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)
mysql> select 5 div 2;
+---------+
| 5 div 2 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
5.MOD,取余数:
mysql> select 5 mod 2;
+---------+
| 5 mod 2 |
+---------+
| 1 |
+---------+
mysql> select 5.3 mod 2;
+-----------+
| 5.3 mod 2 |
+-----------+
| 1.3 |
+-----------+
1 row in set (0.00 sec)
6.POW()函数,幂运算:
表示2的3次幂
mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
| 8 |
+----------+
7.TRUNCATE(),数字截断:
mysql> select truncate(125.29,1);
+--------------------+
| truncate(125.29,1) |
+--------------------+
| 125.2 |
+--------------------+
mysql> select truncate(125.19,0);
+--------------------+
| truncate(125.19,0) |
+--------------------+
| 125 |
+--------------------+
mysql> select truncate(125.19,-1);
+---------------------+
| truncate(125.19,-1) |
+---------------------+
| 120 |
+---------------------+
三:日期时间函数:
1.NOW(),返回当前日期时间:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-12-29 10:28:10 |
+---------------------+
2.CURDATE(),返回当前日期:
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2017-12-29 |
+------------+
3.CURTIME(),返回当前时间:
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:30:32 |
+-----------+
4.DATE_ADD()函数,日期变化函数:
mysql> select date_add('2017-12-29',interval 365 day);
+-----------------------------------------+
| date_add('2017-12-29',interval 365 day) |
+-----------------------------------------+
| 2018-12-29 |
+-----------------------------------------+
1 row in set (0.01 sec)
mysql> select date_add('2017-12-29',interval -365 day);
+------------------------------------------+
| date_add('2017-12-29',interval -365 day) |
+------------------------------------------+
| 2016-12-29 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval 1 week);
+----------------------------------------+
| date_add('2017-12-29',interval 1 week) |
+----------------------------------------+
| 2018-01-05 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval -1 week);
+-----------------------------------------+
| date_add('2017-12-29',interval -1 week) |
+-----------------------------------------+
| 2017-12-22 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval 1 month);
+-----------------------------------------+
| date_add('2017-12-29',interval 1 month) |
+-----------------------------------------+
| 2018-01-29 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval -1 month);
+------------------------------------------+
| date_add('2017-12-29',interval -1 month) |
+------------------------------------------+
| 2017-11-29 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval 4 hour);
+----------------------------------------+
| date_add('2017-12-29',interval 4 hour) |
+----------------------------------------+
| 2017-12-29 04:00:00 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval -4 hour);
+-----------------------------------------+
| date_add('2017-12-29',interval -4 hour) |
+-----------------------------------------+
| 2017-12-28 20:00:00 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval 4 minute);
+------------------------------------------+
| date_add('2017-12-29',interval 4 minute) |
+------------------------------------------+
| 2017-12-29 00:04:00 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-12-29',interval -4 minute);
+-------------------------------------------+
| date_add('2017-12-29',interval -4 minute) |
+-------------------------------------------+
| 2017-12-28 23:56:00 |
+-------------------------------------------+
1 row in set (0.00 sec)
5.DATEDIFF(),得到两个时间之间的差值,前面减去后面,返回的是天数:
mysql> select datediff('2017-12-29','2017-11-29');
+-------------------------------------+
| datediff('2017-12-29','2017-11-29') |
+-------------------------------------+
| 30 |
+-------------------------------------+
6.DATE_FORMAT(),用于时间格式化:
mysql> select date_format('2017/12/29 20/30/23','%Y-%m-%d %H:%i:%s');
+--------------------------------------------------------+
| date_format('2017/12/29 20/30/23','%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2017-12-29 20:30:23 |
+--------------------------------------------------------+
四:信息函数:
1.CONNECTION_ID(),返回当前连接的id:
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 1 |
+-----------------+
2.DATABASE(),返回当前数据库:
mysql> select database();
+-------------+
| database() |
+-------------+
| imooc_mysql |
+-------------+
3.USER(),返回当前用户:
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
4.VERSION(),返回当前数据库版本信息:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.38 |
+-----------+
五:聚合函数:
mysql> select * from tdb_goods;
+----------+--------------------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
| goods_id | goods_name | cate_id | brand_id | goods_price | is_show | is_saleoff |
+----------+--------------------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
| 1 | R510VC 15.6英寸笔记本 | 5 | 2 | 3399.000 | 1 | 0 |
| 2 | Y400N 14.0英寸笔记本电脑 | 5 | 7 | 4899.000 | 1 | 0 |
| 3 | G150TH 15.6英寸游戏本 | 4 | 9 | 8499.000 | 1 | 0 |
| 4 | X550CC 15.6英寸笔记本 | 5 | 2 | 2799.000 | 1 | 0 |
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 7 | 7 | 4999.000 | 1 | 0 |
| 6 | U330P 13.3英寸超极本 | 7 | 7 | 4299.000 | 1 | 0 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7 | 6 | 7999.000 | 1 | 0 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 2 | 8 | 1998.000 | 1 | 0 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版) | 2 | 8 | 3388.000 | 1 | 0 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版) | 2 | 8 | 2788.000 | 1 | 0 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 1 | 7 | 3499.000 | 1 | 0 |
| 12 | Vostro 3800-R1206 台式电脑 | 1 | 5 | 2899.000 | 1 | 0 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 1 | 8 | 9188.000 | 1 | 0 |
| 14 | AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ) | 1 | 3 | 3699.000 | 1 | 0 |
| 15 | Z220SFF F4F06PA工作站 | 3 | 4 | 4288.000 | 1 | 0 |
| 16 | PowerEdge T110 II服务器 | 3 | 5 | 5388.000 | 1 | 0 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 3 | 8 | 28888.000 | 1 | 0 |
| 18 | HMZ-T3W 头戴显示设备 | 6 | 6 | 6999.000 | 1 | 0 |
| 19 | 商务双肩背包 | 6 | 6 | 99.000 | 1 | 0 |
| 20 | X3250 M4机架式服务器 2583i14 | 3 | 1 | 6888.000 | 1 | 0 |
| 21 | HMZ-T3W 头戴显示设备 | 6 | 6 | 6999.000 | 1 | 0 |
| 22 | 商务双肩背包 | 6 | 6 | 99.000 | 1 | 0 |
+----------+--------------------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
1.AVG(),返回平均值:
mysql> select avg(goods_price) from tdb_goods;
+------------------+
| avg(goods_price) |
+------------------+
| 5636.3636364 |
+------------------+
2.COUNT(),返回个数:
mysql> select count(1) from tdb_goods;
+----------+
| count(1) |
+----------+
| 22 |
+----------+
3.MAX(),返回最大值:
mysql> select max(goods_price) from tdb_goods;
+------------------+
| max(goods_price) |
+------------------+
| 28888.000 |
+------------------+
4.MIN(),返回最小值:
mysql> select min(goods_price) from tdb_goods;
+------------------+
| min(goods_price) |
+------------------+
| 99.000 |
+------------------+
5.SUM(),返回求和:
mysql> select sum(goods_price) from tdb_goods;
+------------------+
| sum(goods_price) |
+------------------+
| 124000.000 |
+------------------+
六:加密函数:
1.MD5(),严格来说MD5不属于加密算法,只是信息散列算法:
mysql> select md5('123456');
+----------------------------------+
| md5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
2.PASSWORD():一般用于设置mysql登录密码
mysql> select password('123456');
+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+