【16】拼接字段Concat、日期Date

1.Concat（）拼接

mysql> select * from pet;
+----------+-------------+---------+------+------------+------------+
| name     | owner       | species | sex  | birth      | death      |
+----------+-------------+---------+------+------------+------------+
| Puffball | Diane       | hamster | f    | 2000-09-22 | NULL       |
| Cuihua   | ZhouXingChi | DOG     | M    | 1999-09-21 | NULL       |
| DNN      | Huimin      | Cat     | f    | 2018-07-07 | NULL       |
| Claws    | Gwen        | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold      | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny       | dog     | m    | 1909-08-27 | NULL       |
| Bowser   | Diane       | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen        | bird    | f    | 1989-09-01 | NULL       |
| Slim     | Benny       | snake   | m    | 2006-04-21 | NULL       |
| Puffball | Diane       | hamster | f    | 2009-10-12 | NULL       |
+----------+-------------+---------+------+------------+------------+

拼接name与species列：

mysql> SELECT Concat(name,'(',species,')') as info FROM pet order by name;
+-------------------+
| info              |
+-------------------+
| Bowser(dog)       |
| Buffy(dog)        |
| Chirpy(bird)      |
| Claws(cat)        |
| Cuihua(DOG)       |
| DNN(Cat)          |
| Fang(dog)         |
| Puffball(hamster) |
| Puffball(hamster) |
| Slim(snake)       |
+-------------------+

2.SELECT简单测试

mysql> select 3*2;
+-----+
| 3*2 |
+-----+
|   6 |
+-----+

Trim（）去空格：

mysql> select Rtrim(' abc');
+---------------+
| Rtrim(' abc') |
+---------------+
|  abc          |
+---------------+

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-04-25 09:33:10 |
+---------------------+

mysql> SELECT name,upper(species) FROM pet order by name;
+----------+----------------+
| name     | upper(species) |
+----------+----------------+
| Bowser   | DOG            |
| Buffy    | DOG            |
| Chirpy   | BIRD           |
| Claws    | CAT            |
| Cuihua   | DOG            |
| DNN      | CAT            |
| Fang     | DOG            |
| Puffball | HAMSTER        |
| Puffball | HAMSTER        |
| Slim     | SNAKE          |
+----------+----------------+

3.SOUNDEX（）：

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | Lily  |
|  2 | Lily  |
|  3 | Y.Lee |
+----+-------+

mysql> select id,name from user where name = 'Y.Lie';
Empty set (0.04 sec)

mysql> select id,name from user where soundex(name) = soundex('Y.Lie');
+----+-------+
| id | name  |
+----+-------+
|  3 | Y.Lee |
+----+-------+

4.Date（）：日期

mysql> SELECT birth FROM pet;
+------------+
| birth      |
+------------+
| 2000-09-22 |
| 1999-09-21 |
| 2018-07-07 |
| 1994-03-17 |
| 1989-05-13 |
| 1909-08-27 |
| 1979-08-31 |
| 1989-09-01 |
| 2006-04-21 |
| 2009-10-12 |
+------------+

如果你想要的仅仅是日期，使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。

这样，由于某种原因表中以后有日期和时间值，代码也不用改变，Time()在你只想用时间时使用它。

mysql> SELECT Date(birth) FROM pet;
+-------------+
| Date(birth) |
+-------------+
| 2000-09-22  |
| 1999-09-21  |
| 2018-07-07  |
| 1994-03-17  |
| 1989-05-13  |
| 1909-08-27  |
| 1979-08-31  |
| 1989-09-01  |
| 2006-04-21  |
| 2009-10-12  |
+-------------+

 筛选：

mysql> select name,birth from pet where date(birth) between '1994-05-01' and '2018-09-30';
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 2000-09-22 |
| Cuihua   | 1999-09-21 |
| DNN      | 2018-07-07 |
| Slim     | 2006-04-21 |
| Puffball | 2009-10-12 |
+----------+------------+

mysql> select name,birth from pet where year(birth) between 1994 and 2018
    -> and month(birth) between 5 and 9;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 2000-09-22 |
| Cuihua   | 1999-09-21 |
| DNN      | 2018-07-07 |
+----------+------------+