MySQL cheat sheet
=================

Public domain
********************************************************************************
### Table Repair

    CHECK TABLE radacct EXTENDED;
    REPAIR TABLE radacct;

********************************************************************************
### Add a column to an existing table

    ALTER TABLE table_name ADD new_column INT NOT NULL;
    ALTER TABLE table_name ADD new_column VARCHAR(50) AFTER existing_column;
    ALTER TABLE table_name ADD new_column VARCHAR(50) FIRST;

********************************************************************************
### Change a column name

    ALTER TABLE table_name CHANGE old_name new_name VARCHAR(50);

********************************************************************************
### Add unique to an existing column 

    ALTER TABLE table_name ADD UNIQUE (existing_column);

********************************************************************************
### Remove a column

    ALTER TABLE table_name DROP COLUMN column_name;

********************************************************************************
### Export database schema

    mysqldump -uroot -p database -d > databse-schema.sql

********************************************************************************
### Export entire database

    mysqldump -uroot -p database > databse-backup.sql

********************************************************************************
### Export full databases
    mysqldump -u root -p  --all-databases --routines --max_allowed_packet=512M > mysql_full_backup.sql
    
********************************************************************************
### Sotring IP

    mysql> SELECT INET_ATON('192.168.10.50');
    +----------------------------+
    | INET_ATON('192.168.10.50') |
    +----------------------------+
    |                 3232238130 | 
    +----------------------------+
    1 row in set (0.00 sec)

    mysql> SELECT INET_NTOA('3232238130');
    +-------------------------+
    | INET_NTOA('3232238130') |
    +-------------------------+
    | 192.168.10.50           | 
    +-------------------------+
    1 row in set (0.00 sec)
********************************************************************************
### Replace a string 
    
    UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://oldlink.com', 'http://newlink.com');

********************************************************************************
### Datediff and TIME_TO_SEC
    SELECT datediff( pr_endtime, pr_starttime ) AS date_diff FROM problem_reports;

    SELECT TIME_TO_SEC(TIMEDIFF(pr_endtime,pr_starttime))/86400 FROM problem_reports;

    SELECT TIME_TO_SEC( TIMEDIFF( pr_endtime, pr_starttime))/3600 FROM problem_reports;
********************************************************************************
_BY: Pejman Moghadam, Farid Ahmadian_  
_TAG: mysql, wordpress_  
_DATE: 2011-06-08 10:36:58_