mysql> select database();+------------+| database() |+------------+| test       |+------------+1 row in set (0.00 sec)

mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1             |+----------------+1 row in set (0.00 sec)

//复制表结构mysql> create table t2 like t1;Query OK, 0 rows affected (0.03 sec)mysql> select * from t1;+------+| id   |+------+|    1 ||    2 ||    3 ||    4 ||    5 ||    6 |+------+6 rows in set (0.01 sec)//复制表数据mysql> insert into t2 select * from t1;Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> select * from t2;+------+| id   |+------+|    1 ||    2 ||    3 ||    4 ||    5 ||    6 |+------+6 rows in set (0.00 sec)

//添加主键索引mysql> alter table t1 add primary key(id);Query OK, 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0//添加唯一索引mysql> alter table t1 add column name varchar(30) not null; //给t1表添加一个name列Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0//查看表信息mysql> desc t1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | 0       |       || name  | varchar(30) | NO   |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)//清空表数据mysql> truncate t1;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1;Empty set (0.00 sec)//添加唯一索引mysql> alter table t1 add unique index t1_name_unique(name);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0//查看索引mysql> show index from t1;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| t1    |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         || t1    |          0 | t1_name_unique |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+2 rows in set (0.00 sec)//添加普通索引mysql> alter table t1 add column age int not null default 0;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc t1    -> ;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | 0       |       || name  | varchar(30) | NO   | UNI | NULL    |       || age   | int(11)     | NO   |     | 0       |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> alter table t1 add index t1_in_age(age);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from t1;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| t1    |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         || t1    |          0 | t1_name_unique |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         || t1    |          1 | t1_in_age      |            1 | age         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+3 rows in set (0.00 sec)

mysql> alter table t1 drop primary key;mysql> show index from t1;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| t1    |          0 | t1_name_unique |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         || t1    |          1 | t1_in_age      |            1 | age         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+mysql> alter table t1 drop index t1_in_age;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t1 drop index t1_name_unique;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from t1;Empty set (0.00 sec)

mysql> alter table t1 modify id int not null primary key auto_increment;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc t1;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(11)     | NO   | PRI | NULL    | auto_increment || name  | varchar(30) | NO   |     | NULL    |                || age   | int(11)     | NO   |     | 0       |                |+-------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

mysql> insert into t1(name,age) values("aaa",20),("bbb",30),("cc",18),("abc",23);Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from t1;+----+------+-----+| id | name | age |+----+------+-----+|  1 | aaa  |  20 ||  2 | bbb  |  30 ||  3 | cc   |  18 ||  4 | abc  |  23 |+----+------+-----+4 rows in set (0.00 sec)

mysql> select name,age from t1 into outfile "/tmp/t1.txt";ERROR 1086 (HY000): File '/tmp/t1.txt' already existsmysql> select name,age from t1 into outfile "/tmp/t1.txt";Query OK, 32 rows affected (0.00 sec)
[root@localhost tmp]# pwd
/tmp
[root@localhost tmp]# ls
ssh-TkEopz2496  ssh-zMKSLp2473  t1.txt  test.sql

mysql> delete from t1;Query OK, 32 rows affected (0.00 sec)mysql> select * from t1;Empty set (0.00 sec)//导入数据mysql> load data infile '/tmp/t1.txt' into table t1(name,age);Query OK, 32 rows affected, 64 warnings (0.00 sec)Records: 32  Deleted: 0  Skipped: 0  Warnings: 32//清空表mysql> truncate t1;Query OK, 0 rows affected (0.00 sec)//两种清空表的方式在原理上不一样，我们可以看出delete方式的影响行数为32，而truncate则是0，那么也就是说delete是一行一行的删除的，
所以truncate在清楚数据上面比delete方式更高效，并且truncate会是auto_increment的值重置为1

mysql> delete from t1 where id > 20;Query OK, 12 rows affected (0.00 sec)mysql> alter table t1 auto_increment=1;Query OK, 20 rows affected (0.01 sec)Records: 20  Duplicates: 0  Warnings: 0

[root@localhost tmp]# cat t1.txt aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23aaa     20bbb     30cc      18abc     23

mysql> load data infile '/tmp/t1.txt' into table t1(name,age);Query OK, 32 rows affected, 64 warnings (0.00 sec)Records: 32  Deleted: 0  Skipped: 0  Warnings: 32

mysql> select id,name,age,case when age >= 30 then 'a' when age <=18 then 'b' else 'c' end as ddd from t1;+----+------+-----+-----+| id | name | age | ddd |+----+------+-----+-----+|  1 | aaa  |  20 | c   ||  2 | bbb  |  30 | a   ||  3 | cc   |  18 | b   ||  4 | abc  |  23 | c   ||  5 | aaa  |  20 | c   ||  6 | bbb  |  30 | a   ||  7 | cc   |  18 | b   ||  8 | abc  |  23 | c   ||  9 | aaa  |  20 | c   || 10 | bbb  |  30 | a   || 11 | cc   |  18 | b   || 12 | abc  |  23 | c   || 13 | aaa  |  20 | c   || 14 | bbb  |  30 | a   || 15 | cc   |  18 | b   || 16 | abc  |  23 | c   || 17 | aaa  |  20 | c   || 18 | bbb  |  30 | a   || 19 | cc   |  18 | b   || 20 | abc  |  23 | c   |+----+------+-----+-----+20 rows in set (0.00 sec)

//字符串组合函数mysql> select concat("hello","mysql") as title;+------------+| title      |+------------+| hellomysql |+------------+1 row in set (0.00 sec)mysql> select concat("hello","mysql") as title;+------------+| title      |+------------+| hellomysql |+------------+1 row in set (0.00 sec)mysql> select concat("hello","mysql","aaaa") as title;+----------------+| title          |+----------------+| hellomysqlaaaa |+----------------+1 row in set (0.00 sec)//字符串大小写转换mysql> select lcase('HELLO MYSQL') as title;+-------------+| title       |+-------------+| hello mysql |+-------------+1 row in set (0.00 sec)mysql> select ucase('hello mysql') as title;+-------------+| title       |+-------------+| HELLO MYSQL |+-------------+1 row in set (0.00 sec)//返回字符的长度mysql> select length("hello mysql") as length;+--------+| length |+--------+|     11 |+--------+1 row in set (0.00 sec)//将字符重复N次mysql> select repeat('hello mysql,',3);+--------------------------------------+| repeat('hello mysql,',3)             |+--------------------------------------+| hello mysql,hello mysql,hello mysql, |+--------------------------------------+1 row in set (0.00 sec)//替换字符串mysql> select replace("hello mysql","mysql","php") as rp;+-----------+| rp        |+-----------+| hello php |+-----------+1 row in set (0.00 sec)//截取字符串，注意索引是从1开始mysql> select substring("hello mysql",1,5) as sub;+-------+| sub   |+-------+| hello |+-------+1 row in set (0.00 sec)//返回字符在列表中的位置mysql> select find_in_set("a","a,b,c,d");+----------------------------+| find_in_set("a","a,b,c,d") |+----------------------------+|                          1 |+----------------------------+1 row in set (0.00 sec)

//10进制转2进制mysql> select bin(2);+--------+| bin(2) |+--------+| 10     |+--------+1 row in set (0.00 sec)//向上取整mysql> select ceiling(1.2);+--------------+| ceiling(1.2) |+--------------+|            2 |+--------------+1 row in set (0.00 sec)//向下取整mysql> select floor(1.2);+------------+| floor(1.2) |+------------+|          1 |+------------+1 row in set (0.00 sec)//获取最大值mysql> select *,max(age) from t1 ;+----+------+-----+----------+| id | name | age | max(age) |+----+------+-----+----------+|  1 | aaa  |  20 |       30 |+----+------+-----+----------+1 row in set (0.00 sec)//获取最小值mysql> select *,min(age) from t1;+----+------+-----+----------+| id | name | age | min(age) |+----+------+-----+----------+|  1 | aaa  |  20 |       18 |+----+------+-----+----------+1 row in set (0.00 sec)//获取一个0到1之间的随机数mysql> select rand();+-------------------+| rand()            |+-------------------+| 0.635864053513728 |+-------------------+1 row in set (0.00 sec)

//获取当前时间的日期部分mysql> select curdate();+------------+| curdate()  |+------------+| 2015-02-10 |+------------+1 row in set (0.00 sec)//获取当前时间的小时部分mysql> select curtime();+-----------+| curtime() |+-----------+| 02:43:08  |+-----------+1 row in set (0.00 sec)//获取当前时间mysql> select now();+---------------------+| now()               |+---------------------+| 2015-02-10 02:43:15 |+---------------------+1 row in set (0.00 sec)//mysql> select unix_timestamp();+------------------+| unix_timestamp() |+------------------+|       1423507660 |+------------------+1 row in set (0.00 sec)//获取当前时间戳mysql> select unix_timestamp();+------------------+| unix_timestamp() |+------------------+|       1423507660 |+------------------+1 row in set (0.00 sec)//时间戳转化为日期mysql> select from_unixtime(unix_timestamp());+---------------------------------+| from_unixtime(unix_timestamp()) |+---------------------------------+| 2015-02-10 02:49:37             |+---------------------------------+1 row in set (0.00 sec)//获取时间中的年月日mysql> select year(now());+-------------+| year(now()) |+-------------+|        2015 |+-------------+1 row in set (0.00 sec)mysql> select month(now());+--------------+| month(now()) |+--------------+|            2 |+--------------+1 row in set (0.00 sec)mysql> select day(now());+------------+| day(now()) |+------------+|         10 |+------------+1 row in set (0.00 sec)