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);