Mysql-字符拼接、转换大小写

mysql> select *from stu;
+----+-----------+----------+---------+-------------+
| id | firstname | lastname | address | phone       |
+----+-----------+----------+---------+-------------+
|  4 | Wu        | BaiTao   | Hunan   | 12345678912 |
|  9 | Wang      | Tao      | Hunan   | 12345678912 |
| 10 | Zhao      | Gao      | Hunan   | 12345678912 |
| 11 | Li        | Mazi     | Hunan   | 12345678912 |
| 20 | Li        | Feng     | GuangXi | 12345678998 |
+----+-----------+----------+---------+-------------+

1、concat()方法
使用concat(String1,String2…StringN)函数将firstname 和lastname拼接起来

mysql> select concat(firstname,lastname) as name from stu;
    -> $$
+----------+
| name     |
+----------+
| WuBaiTao |
| WangTao  |
| ZhaoGao  |
| LiMazi   |
| LiFeng   |
+----------+

在SQLServer中使用的是下面的语句

select (firstname||"'"||last_name) as name from stu;

2、upper()方法
2、使用upper函数将所有的firstname装换成大写

mysql> select upper(firstname)as firstname from stu;
+-----------+
| firstname |
+-----------+
| WU        |
| WANG      |
| ZHAO      |
| LI        |
| LI        |
+-----------+

3、lower()方法
使用lower函数将所有lastname 转换成小写

mysql> select lower(lastname) as lastname from stu;
+----------+
| lastname |
+----------+
| baitao   |
| tao      |
| gao      |
| mazi     |
| feng     |
+----------+

4、like模糊
like结合通配符%和_执行模糊查询
查找所有包含字符a的lastname

mysql> select lastname from stu where lastname like '%a%';
    -> $$
+----------+
| lastname |
+----------+
| BaiTao   |
| Tao      |
| Gao      |
| Mazi     |
+----------+

使用_符号代替一位查找所有以ao结尾的位的字符串。

mysql> select lastname from stu where lastname like '_ao';
    -> $$
+----------+
| lastname |
+----------+
| Tao      |
| Gao      |
+----------+