mysql 字符串函数 数值函数 日期时间函数 枚举类型 set类型

1.CONCAT(S1，S2，...SN)函数：把传入的参数连接成一个字符串

 select concat('a',null);

输出：

+------------------+
| concat('a',null) |
+------------------+
| NULL             |
+------------------+
1 row in set (0.00 sec)

任意字符串与NULL连接的结果都是NULL

2.INSERT(str1,x,y,str2)函数：把str1从位置开始以后的y个字符串替换成str2

mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.00 sec)

3.LEFT(str1,x),RIGHT(str2,y)函数：返回str1左边x个字符，str2右边y个字符

mysql> select left('beijing2008',7),left('beijing2008',null),right('beijing2008'
,4);
+-----------------------+--------------------------+------------------------+
| left('beijing2008',7) | left('beijing2008',null) | right('beijing2008',4) |
+-----------------------+--------------------------+------------------------+
| beijing               | NULL                     | 2008                   |
+-----------------------+--------------------------+------------------------+
1 row in set (0.00 sec)

4.LPAD(str,n,pad)和RPAD(str,n,pad)函数：对字符串str最左边和最右边进行填充，直到长度为n个字符串长度

mysql> select lpad('a',5,'b');
+-----------------+
| lpad('a',5,'b') |
+-----------------+
| bbbba           |
+-----------------+
1 row in set (0.00 sec)
mysql> select rpad('c','5','d');
+-------------------+
| rpad('c','5','d') |
+-------------------+
| cdddd             |
+-------------------+
1 row in set (0.00 sec)

5.LTRIM(str)和RTRIM(str)去掉str左边和右边的空格

mysql> select ltrim('   |beijing');
+----------------------+
| ltrim('   |beijing') |
+----------------------+
| |beijing             |
+----------------------+
1 row in set (0.01 sec)
mysql> select rtrim('beijing|   ');
+----------------------+
| rtrim('beijing|   ') |
+----------------------+
| beijing|             |
+----------------------+
1 row in set (0.00 sec)

6.REPEAT(str,x)函数，讲str重复x次

mysql> select repeat ('mysql',3);
+--------------------+
| repeat ('mysql',3) |
+--------------------+
| mysqlmysqlmysql    |
+--------------------+
1 row in set (0.00 sec)

7.REPLACE(str,a,b)函数：用字符串b替换str中所有出现的字符串a

mysql> select replace('beijing_2008','_2008','_2020');
+-----------------------------------------+
| replace('beijing_2008','_2008','_2020') |
+-----------------------------------------+
| beijing_2020                            |
+-----------------------------------------+
1 row in set (0.01 sec)

8.SUBSTRING(str,x,y)函数：返回str字符串中x位置起y个字符串

mysql> select substring('beijing2008',8,4);
+------------------------------+
| substring('beijing2008',8,4) |
+------------------------------+
| 2008                         |
+------------------------------+
1 row in set (0.00 sec)

一个返回当前日期后31天和1年2个月后的SQL：

mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,date_ad
(now(),INTERVAL '1_2' year_month) after2year;
+---------------------+---------------------+---------------------+
| current             | after31days         | after2year          |
+---------------------+---------------------+---------------------+
| 2014-10-06 22:50:13 | 2014-11-06 22:50:13 | 2015-12-06 22:50:13 |
+---------------------+---------------------+---------------------+
1 row in set (0.02 sec)

返回当前日期前31天和前1年2个月的SQL：

mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_ad
d(now(),INTERVAL '-1_-2' year_month) after2year;
+---------------------+---------------------+---------------------+
| current             | after31days         | after2year          |
+---------------------+---------------------+---------------------+
| 2014-10-06 22:52:23 | 2014-09-05 22:52:23 | 2013-08-06 22:52:23 |
+---------------------+---------------------+---------------------+
1 row in set (0.02 sec)

计算某年某月某日到当前日期相差多少天的SQL：

mysql> select datediff('1990-08-01',now());
+------------------------------+
| datediff('1990-08-01',now()) |
+------------------------------+
|                        -8832 |
+------------------------------+
1 row in set (0.00 sec)

9.关于varchar与char的一点区别：

char做检索的时候把空格去掉了，而varchar则保留了这些空格

mysql> create table vc (v varchar(4),c char(4));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into vc values('ab  ','ab  ');
Query OK, 1 row affected (0.09 sec)
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

10.关于枚举类型：

mysql> create table tt(gender enum('M','F'));
Query OK, 0 rows affected (0.08 sec)

插入数据：

mysql> insert into tt values('M'),('1'),('m'),(NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

插入枚举类型的时候，大小写不区分，1代表第一个枚举

mysql> SELECT * FROM TT;
+--------+
| gender |
+--------+
| M      |
| M      |
| M      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

enum一次只可以从集合中选取一个值.

11.关于set类型：

mysql> create table ttt(col set('a','b','c','d'));
Query OK, 0 rows affected (0.08 sec)

插入数据：

mysql> insert into ttt values ('a,b'),('a,d,a'),('a,b'),('a');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

查询：

mysql> select * from ttt;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a,b  |
| a    |
+------+
4 rows in set (0.00 sec)

set可以同时插入多个值，但是重复的值只会插一次，对于插入不在set里面的值，会报错：

mysql> insert into ttt values('e');
ERROR 1265 (01000): Data truncated for column 'col' at row 1