set_include_path(".:/home/epassage/domains/epassages.net/includes");
require_once "page_elements.inc";
$styles = array("standard");
$page = new page("Mysql Cheat Sheet",$styles);
$page->begin_page();
?>
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
$page->left_tips_menu();
$page->left_ad();
?>
$page->end_page();
?>