需求:需要检查一个很长的列表中所有的不重复的元素
解决:select distinct <col_name> from <table_name>;
需求:查看一个表中前 k 行数据
解决:select * from <table_name> where <seach_condition> limite 0,k;
需求:从一个表中向另外一个表插入数据
解决:insert into table_name(col_name) select(col_name) from table_name;
需求:计算datetime格式的时间间隔
解决:TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Examples:
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')
-> 3
需求:根据一张表中的起始日期和终止日期,将时间间隔写入另一张表中
解决:mysql> insert into test(title,id,date) select title,emp_no,timestampdiff(month,from_date,to_date) from titles;
Query OK, 443308 rows affected (8.93 sec)
Records: 443308 Duplicates: 0 Warnings: 0
需求:同一张表中,增加一列为另外两列之差
解决:
mysql> update test set date=timestampdiff(month,from_date,to_date);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
转载于:https://my.oschina.net/u/2423823/blog/513206