Common sql statement

SQLs

* Change the column property
    ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(50);

* Create table from exist table
    CREATE TABLE table_name LIKE exist_table;  //empty table with same structure
    CREATE TABLE table_name AS SELECT * FROM exist_table;  //copy the whole table

* Estimate rough row number of a database
    SELECT TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=table_schema; // this is only an estimate,not the exact one!!!!!

* Backup database
    mysqldump -u root -p episode38 > episode38-`date +%F`.sql

* Restore database
    vi episode38-2014-10-07.sql
    create database episode38;
    use episode38;
    mysql -u root -p < episode38-2014-10-07.sql 

* Create user
    CREATE USER 'wiki'@'localhost' IDENTIFIED BY '$$72!2534Ef3';
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON `wiki`.* TO 'wiki'@'localhost'; 

* show table in oracle
    select table_name from all_tables