Update dengan limit Mysql

UPDATE table_name SET name='test'
     WHERE id IN (
         SELECT id FROM (
             SELECT id FROM table_name 
             ORDER BY id ASC  
             LIMIT 0, 10
         ) tmp
     );

Menghilangkan Data Duplikat

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]  FROM table

Mengambil id terakhir pada field AUTO INCREMENT Mysql

SELECT LAST_INSERT_ID();

Membuat User Baru Pada Mysql Server

mysql> use mysql;
mysql> insert into user (host,user,password,select_priv) values('localhost','kopkar', password('123123'),'Y');
mysql> flush privileges;

Mengubah NULL menjadi 0 pada Mysql

IFNULL(field,0)

Format Tanggal pada Mysql

DATE_FORMAT(date,format)


Format   | Description
%a  | Abbreviated weekday name
%b  | Abbreviated month name
%c  | Month, numeric
%D  | Day of month with English suffix
%d  | Day of month, numeric (00-31)
%e  | Day of month, numeric (0-31)
%f  | Microseconds
%H  | Hour (00-23)
%h  | Hour (01-12)
%I  | Hour (01-12)
%i  | Minutes, numeric (00-59)
%j  | Day of year (001-366)
%k  | Hour (0-23)
%l  | Hour (1-12)
%M  | Month name
%m  | Month, numeric (00-12)
%p  | AM or PM
%r  | Time, 12-hour (hh:mm:ss AM or PM)
%S  | Seconds (00-59)
%s  | Seconds (00-59)
%T  | Time, 24-hour (hh:mm:ss)
%U  | Week (00-53) where Sunday is the first day of week
%u  | Week (00-53) where Monday is the first day of week
%V  | Week (01-53) where Sunday is the first day of week, used with %X
%v  | Week (01-53) where Monday is the first day of week, used with %x
%W  | Weekday name
%w  | Day of the week (0=Sunday, 6=Saturday)
%X  | Year of the week where Sunday is the first day of week, four digits, used with %V
%x  | Year of the week where Monday is the first day of week, four digits, used with %v
%Y  | Year, four digits
%y  | Year, two digits

Date and Time Functions

mysql> SELECT something FROM tbl_name

    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

=============================================================

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');

        -> 0, 0

=============================================================

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);

        -> NULL

mysql> SELECT DAYNAME('2006-05-00');

        -> NULL

=============================================================

mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);

        -> '2008-02-02'

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);

        -> '2008-02-02'

=============================================================

mysql> SELECT ADDDATE('2008-01-02', 31);

        -> '2008-02-02'

=============================================================

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

        -> '2008-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

        -> '03:00:01.999997'

=============================================================

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

        -> '2004-01-01 22:00:00'

=============================================================

mysql> SELECT CURDATE();

        -> '2008-06-13'

mysql> SELECT CURDATE() + 0;

        -> 20080613

=============================================================