MySQL学习笔记(二)

6. 用通配符进行过滤

6.1 LIKE操作符

通配符：用来匹配部分值的特殊字符

6.1.1 百分号通配符%（%匹配任意数量的任意字符，包括0，但是不能匹配NULL）

mysql> select m_FN,m_LN,m_info from member
    -> where m_info like 'Goo%';
+-------+------+---------+
| m_FN  | m_LN | m_info  |
+-------+------+---------+
| Halen | Park | GoodMan |
+-------+------+---------+
1 row in set (0.00 sec)

6.1.2 下划线通配符_（只能匹配单个字符）

mysql> select m_FN,m_LN from member
    -> where m_LN like '_ar_';
+-------+------+
| m_FN  | m_LN |
+-------+------+
| Halen | Park |
+-------+------+
1 row in set (0.00 sec)

6.2 正则表达式（regexp）

6.2.1 基本字符匹配（.可以匹配任意一个字符）

mysql> select m_FN,m_id from member
    -> where m_id regexp '[1-9]' order by m_id desc;
+--------+------+
| m_FN   | m_id |
+--------+------+
| Samuel |    2 |
| Halen  |    1 |
+--------+------+
2 rows in set (0.00 sec)

LIKE和REGEXP的区别：LIKE匹配整个列，即如果匹配的只是其中的子串，那么则不会返回，而REGEXP则是匹配整个串的子串。

6.2.2 进行OR匹配

mysql> select m_id,m_LN from member
    -> where m_id regexp '1|2';
+------+-------+
| m_id | m_LN  |
+------+-------+
|    1 | Park  |
|    2 | Green |
+------+-------+
2 rows in set (0.00 sec)

6.2.3 匹配特殊字符

[\\f   换页]、[\\n  换行]  、[\\r  回车]、  [\\t 制表]、 [\\v 纵向制表]

mysql> select m_id,m_FN from member
    -> where m_FN regexp '..muel';
+------+--------+
| m_id | m_FN   |
+------+--------+
|    2 | Samuel |
+------+--------+
1 row in set (0.00 sec)

匹配字符集：

[:alnum:]任意字母和数字

[:alpha:]任意字符

[:blank:]空格和制表

[:lower:]和[:upper:]小大写字母

[:space:]包括空格在内的任意空白字符

[:digit:]任意数字

...

6.2.3 匹配多个实例

*：0个或者多个匹配

+：1个或多个匹配

？：0个或者1个匹配

{n}:n个匹配

{n,}：至少n个匹配

{n,m}：n-m个匹配

mysql> select m_id,m_FN from member
    -> where m_FN regexp '[[:alnum:]]*';
+------+--------+
| m_id | m_FN   |
+------+--------+
|    1 | Halen  |
|    2 | Samuel |
+------+--------+
2 rows in set (0.00 sec)

6.2.4 定位符

^:文本的开始

$:文本的结尾

[[:<:]]:词的开始

[[:>:]]:词的结尾

mysql> select m_id,m_LN from member
    -> where m_LN regexp '^[[:upper:]]?[[:alpha:]]{2,}';
+------+-------+
| m_id | m_LN  |
+------+-------+
|    1 | Park  |
|    2 | Green |
+------+-------+
2 rows in set (0.00 sec)

^放在[]外面表示文本的开始位置，放在[]里面表示非得概念，即NOT。 

mysql> select m_id,m_LN from member
    -> where m_id regexp '^[^1,3]$';
+------+-------+
| m_id | m_LN  |
+------+-------+
|    2 | Green |
+------+-------+
1 row in set (0.00 sec)

7. 创建计算字段

7.1 拼接字段

拼接：将值连接到一起构成的单个值。（MySQL一般用concat）

mysql> select concat(m_id,'(',m_FN,')') from data.member
    -> order by m_id;
+---------------------------+
| concat(m_id,'(',m_FN,')') |
+---------------------------+
| 1(Halen)                  |
| 2(Samuel)                 |
+---------------------------+
2 rows in set (0.00 sec)

*可以用RTrim、LTrim、Trim来去掉串左右两边的空格。

使用别名：（以便于客户机可以直接引用它）

mysql> select concat(m_id,'-',m_LN) as m_extra from member
    -> order by m_id;
+---------+
| m_extra |
+---------+
| 1-Park  |
| 2-Green |
+---------+
2 rows in set (0.00 sec)

7.2 执行算数运算（+,-,*,/）

mysql> select m_id,m_FN,m_id*1.25 as expanded_id
    -> from member order by m_LN;
+------+--------+-------------+
| m_id | m_FN   | expanded_id |
+------+--------+-------------+
|    2 | Samuel |        2.50 |
|    1 | Halen  |        1.25 |
+------+--------+-------------+
2 rows in set (0.05 sec)

同样，我们可以选择select直接调用Now()来获取系统时间

mysql> select now() as time
    -> ;
+---------------------+
| time                |
+---------------------+
| 2019-04-19 21:25:27 |
+---------------------+
1 row in set (0.00 sec)