1、MySQL
?
-----运算符和函数-----
?
字符函数，数值运算符，比较运算等
?
----字符函数
?
---
?
CONCAT()--字符连接
?
CONCAT_WS()--使用指定的分隔符进行字符连接
?
mysql> SELECT CONCAT('imooc','-','MySQL');
+-----------------------------+
| CONCAT('imooc','-','MySQL') |
+-----------------------------+
| imooc-MySQL |
+-----------------------------+
?
mysql> USE TEST;
Database changed
mysql> SELECT * FROM TEST;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | Ben |
+----+----------+
2 rows in set (0.02 sec)
?
mysql> SELECT CONCAT(id,username) AS fullname FROM test;
+----------+
| fullname |
+----------+
| 1Tom |
| 2Ben |
+----------+
?
以上是CONCAT()的实例，而CONCAT_WS()如下
?
mysql> SELECT CONCAT_WS('-',id,username) FROM test;
+----------------------------+
| CONCAT_WS('-',id,username) |
+----------------------------+
| 1-Tom |
| 2-Ben |
+----------------------------+
?
CONCAT_WS()需要至少三个参数，第一个是分隔符，后面才是需要连接的东西
?
---
?
FORMAT()，数字格式化
?
mysql> SELECT FORMAT(234234.23423,2);
+------------------------+
| FORMAT(234234.23423,2) |
+------------------------+
| 234,234.23 |
+------------------------+
?
就是标准化，当然是外国人那一套
?
---
?
LOWER()&UPPER()，大小写变换
?
---
?
LEFT()&RIGHT()，获取左右侧字符
?
需要两位参数,从哪个字段取，从第几位开始的左右侧
?
mysql> SELECT UPPER(LEFT('andy',1));
+-----------------------+
| UPPER(LEFT('andy',1)) |
+-----------------------+
| A |
+-----------------------+
?
---
?
LENGTH()，获取字符串长度。注意，空格也要算在内
?
---
?
LTRIM(),RTRIM(),TRIM()，删除前/后/前后的空格
?
TRIM还能删除其他字符
?
mysql> SELECT TRIM(LEADING '!' FROM '!!!ANDY!!');
+------------------------------------+
| TRIM(LEADING '!' FROM '!!!ANDY!!') |
+------------------------------------+
| ANDY!! |
+------------------------------------+
1 row in set (0.00 sec)
?
mysql> SELECT TRIM(BOTH '!' FROM '!!!ANDY!!');
+---------------------------------+
| TRIM(BOTH '!' FROM '!!!ANDY!!') |
+---------------------------------+
| ANDY |
+---------------------------------+
1 row in set (0.00 sec)
?
mysql> SELECT TRIM(TRAILING '!' FROM '!!!ANDY!!');
+-------------------------------------+
| TRIM(TRAILING '!' FROM '!!!ANDY!!') |
+-------------------------------------+
| !!!ANDY |
+-------------------------------------+
1 row in set (0.00 sec)
?
---
?
REPLACE()
?
mysql> SELECT REPLACE('!!ANDY!SDL!!','!','');
+--------------------------------+
| REPLACE('!!ANDY!SDL!!','!','') |
+--------------------------------+
| ANDYSDL |
+--------------------------------+
1 row in set (0.00 sec)
?
mysql> SELECT REPLACE('!!ANDY!SDL!!','!','LALAL');
+-------------------------------------+
| REPLACE('!!ANDY!SDL!!','!','LALAL') |
+-------------------------------------+
| LALALLALALANDYLALALSDLLALALLALAL |
+-------------------------------------+
1 row in set (0.00 sec)
?
可以看到，替换是比较灵活的
?
---
?
SUBSTRING()
?
mysql> SELECT SUBSTRING('MYSQL','1','2');
+----------------------------+
| SUBSTRING('MYSQL','1','2') |
+----------------------------+
| MY |
+---------------