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