常用函数
CHARSET(s) | 返回字符串s的字符集 |
---|---|
CONCAT(s1,s2…sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 |
UCASE(s) | 将字符串转换为大写 |
LCASE(s) | 将字符串 s 的所有字母变成小写字母 |
LENGTH(s) | 返回字符串s的长度(按照字节) |
REPLACE(s,s1,s2) | 用字符串 s2 替代字符串 s 中的字符串 s1 |
SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
TRIM(s) | 去掉字符串 s 开始和结尾处的空格 |
快速入门案例
mysql> select * from student;
+----+--------+---------+---------+-------+
| id | name | chinese | english | math |
+----+--------+---------+---------+-------+
| 3 | 鲁智深 | 33.00 | 32.00 | 12.00 |
| 2 | 林冲 | 95.00 | 98.00 | 91.00 |
| 1 | 李逵 | 18.00 | 25.00 | 11.00 |
+----+--------+---------+---------+-------+
3 rows in set (0.04 sec)
//CHARSET(s) 返回字符串s的字符集
mysql> select charset(name) from student;
+---------------+
| charset(name) |
+---------------+
| utf8 |
| utf8 |
| utf8 |
+---------------+
3 rows in set (0.00 sec)
//CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串
mysql> select concat(name,'语文',chinese) from student;
+-----------------------------+
| concat(name,'语文',chinese) |
+-----------------------------+
| 鲁智深语文33.00 |
| 林冲语文95.00 |
| 李逵语文18.00 |
+-----------------------------+
3 rows in set (0.00 sec)
//UCASE(s) 将字符串转换为大写
//LCASE(s) 将字符串 s 的所有字母变成小写字母
mysql> select ucase('abc'),lcase('DEF');
+--------------+--------------+
| ucase('abc') | lcase('DEF') |
+--------------+--------------+
| ABC | def |
+--------------+--------------+
1 row in set (0.00 sec)
//LENGTH(s) 返回字符串s的长度(按照字节)
mysql> select name,length(name),chinese,length(chinese) from student;
+--------+--------------+---------+-----------------+
| name | length(name) | chinese | length(chinese) |
+--------+--------------+---------+-----------------+
| 鲁智深 | 9 | 33.00 | 5 |
| 林冲 | 6 | 95.00 | 5 |
| 李逵 | 6 | 18.00 | 5 |
+--------+--------------+---------+-----------------+
3 rows in set (0.00 sec)
//REPLACE(s,s1,s2) 用字符串 s2 替代字符串 s 中的字符串 s1
mysql> select replace(name,'智深','*') from student;
+--------------------------+
| replace(name,'智深','*') |
+--------------------------+
| 鲁* |
| 林冲 |
| 李逵 |
+--------------------------+
3 rows in set (0.00 sec)
//SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
//注意substring下标从1开始
mysql> select chinese,substring(chinese,1,2) from student;
+---------+------------------------+
| chinese | substring(chinese,1,2) |
+---------+------------------------+
| 33.00 | 33 |
| 95.00 | 95 |
| 18.00 | 18 |
+---------+------------------------+
3 rows in set (0.00 sec)
综合练习
将ename的首字母显示为大写(数据库中数据并没改变)
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
mysql> select ename,concat(ucase(substring(ename,1,1)),substring(ename,2,length(ename)-1)) from emp;
+--------+------------------------------------------------------------------------+
| ename | concat(ucase(substring(ename,1,1)),substring(ename,2,length(ename)-1)) |
+--------+------------------------------------------------------------------------+
| smith | Smith |
| allen | Allen |
| ward | Ward |
| jones | Jones |
| martin | Martin |
| blake | Blake |
| clark | Clark |
| scott | Scott |
| king | King |
| iurner | Iurner |
| james | James |
| ford | Ford |
| miller | Miller |
+--------+------------------------------------------------------------------------+
13 rows in set (0.03 sec)