Mysql Cheat Sheet

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