Here's a few of my favorite queries that I always forgot when I learned MySQL a few years ago:

  • SHOW CREATE TABLE tablename;
  • SHOW INDEX FROM tablename;
  • To rename the table from t1 to t2: mysql> ALTER TABLE t1 RENAME t2;
  • To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c: mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
  • To add a new TIMESTAMP column named d: mysql> ALTER TABLE t2 ADD d TIMESTAMP;
  • To add an index on column d, and make column a the primary key: mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
  • To remove column c: mysql> ALTER TABLE t2 DROP COLUMN c;
  • To add a new AUTO_INCREMENT integer column named c: mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
  • SHOW TABLES LIKE "my_table%";
  • Dump just 1 table: mysqldump database tablename -u username -p > tablename.sql