MySQL < BETA

Tags: view all tags
---++Useful SQL
---+++Getting information
   * Get table information
      * describe MY_DB_NAME;
   * Get the column names from your table
      * select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='MY_DB_NAME';
   * See what processes are running
      * SHOW [FULL] PROCESSLIST;
   * Get size of all databases
      * SELECT table_schema "Database Name", sum( data_length + index_length) / 1024 / 1024 "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
   * Get size breakdown for a single database named "my_database"
      * SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "my_database";
   * Get all columns of a table are enums
      * select COLUMN_NAME from information_schema.COLUMNS WHERE table_schema = "MODEL_DB" and TABLE_NAME='CPLEX_CONFIGURATION_12_1_MILP' and DATA_TYPE = 'enum';

---+++Backing up and moving
   * Copy a table
      * CREATE TABLE NEW_TABLE SELECT * FROM OLD_TABLE ;
   * Copying distinct lines from a table
      * CREATE TABLE DISTINCT_METRICS AS (SELECT DISTINCT * FROM METRICS_JMALT);
Raw edit | More topic actions
Topic revision: r4 - 2010-11-09 - FrankHutter