一些Python脚本保存数据库中,今天需要将MySQL数据库中,“Python XXXX.py”修改为绝对路径,即“/usr/bin/python XXXX.py”,这里就需要用到MySQL的字符串连接函数CONCAT
CONCAT(str1,str2)
返回字符串str1,str2连接后的结果,
例如原表:
mysql> select * from student;
+----+---------+--------+------+--------+-------+
| id | sid | name | cid | cname | score |
+----+---------+--------+------+--------+-------+
| 1 | 2005001 | 张三 | 0001 | 数学 | 69 |
| 2 | 2005002 | 李四 | 0001 | 数学 | 89 |
| 3 | 2005001 | 张三 | 0001 | 数学 | 69 |
+----+---------+--------+------+--------+-------+
使用CONCAT函数查询
mysql> select CONCAT('H',name) from student;
+------------------+
| CONCAT('H',name) |
+------------------+
| H张三 |
| H李四 |
| H张三 |
+------------------+
使用CONCAT函数更新
mysql> update student set name =CONCAT('A',name) where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+---------+---------+------+--------+-------+
| id | sid | name | cid | cname | score |
+----+---------+---------+------+--------+-------+
| 1 | 2005001 | A张三 | 0001 | 数学 | 69 |
| 2 | 2005002 | 李四 | 0001 | 数学 | 89 |
| 3 | 2005001 | 张三 | 0001 | 数学 | 69 |
+----+---------+---------+------+--------+-------+
MySQL还内置了一些非常又有的字符串截取函数:
1、left(str, length) 截取左边的字符串
说明:left(被截取字段,截取长度)
mysql> select Left(name,2) from student where id=1;
+--------------+
| Left(name,2) |
+--------------+
| A张 |
+--------------+
2、right(str, length) 截取右边的字符串
说明:right(被截取字段,截取长度)
mysql> select right(name,2) from student where id=1;
+---------------+
| right(name,2) |
+---------------+
| 张三 |
+---------------+
3、 substring(str, pos, length) 从指定位置截取指定长度的字符串
说明:substring(被截取字段,从第几位开始截取)
mysql> select substring(name,2,1) from student where id=1;
+---------------------+
| substring(name,2,1) |
+---------------------+
| 张 |
+---------------------+
CONCAT(str1,str2)
返回字符串str1,str2连接后的结果,
例如原表:
mysql> select * from student;
+----+---------+--------+------+--------+-------+
| id | sid | name | cid | cname | score |
+----+---------+--------+------+--------+-------+
| 1 | 2005001 | 张三 | 0001 | 数学 | 69 |
| 2 | 2005002 | 李四 | 0001 | 数学 | 89 |
| 3 | 2005001 | 张三 | 0001 | 数学 | 69 |
+----+---------+--------+------+--------+-------+
使用CONCAT函数查询
mysql> select CONCAT('H',name) from student;
+------------------+
| CONCAT('H',name) |
+------------------+
| H张三 |
| H李四 |
| H张三 |
+------------------+
使用CONCAT函数更新
mysql> update student set name =CONCAT('A',name) where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+---------+---------+------+--------+-------+
| id | sid | name | cid | cname | score |
+----+---------+---------+------+--------+-------+
| 1 | 2005001 | A张三 | 0001 | 数学 | 69 |
| 2 | 2005002 | 李四 | 0001 | 数学 | 89 |
| 3 | 2005001 | 张三 | 0001 | 数学 | 69 |
+----+---------+---------+------+--------+-------+
MySQL还内置了一些非常又有的字符串截取函数:
1、left(str, length) 截取左边的字符串
说明:left(被截取字段,截取长度)
mysql> select Left(name,2) from student where id=1;
+--------------+
| Left(name,2) |
+--------------+
| A张 |
+--------------+
2、right(str, length) 截取右边的字符串
说明:right(被截取字段,截取长度)
mysql> select right(name,2) from student where id=1;
+---------------+
| right(name,2) |
+---------------+
| 张三 |
+---------------+
3、 substring(str, pos, length) 从指定位置截取指定长度的字符串
说明:substring(被截取字段,从第几位开始截取)
mysql> select substring(name,2,1) from student where id=1;
+---------------------+
| substring(name,2,1) |
+---------------------+
| 张 |
+---------------------+