MySQL Cheat Sheet
Provided by Leumas Naypoka / www.apphp.com
Here are the most commonly used SQL commands and the most commonly used options for each. There are many more commands and options than listed here. In other words, the syntaxes as I have listed them are far from complete. See the links at the bottom for more complete syntaxes and more commands.
If you use the short option form (-p), you cannot have a space between the option and the password.
This sample demonstrates how to do a backup of your database.
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql
This sample shows how to create a database structure without data.
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql
If you want to dump only one or few tables.
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql
Create a backup and archive it.
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz
Creation of a backup with the indication of its date.
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`
Load backup into the database.
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql
Create a new database.
mysqladmin -u USER -pPASSWORD create NEWDATABASE
It is always convenient to make backup with additional options, like: -Q -c -e, i.e., where
- -Q wraps names in back quotes
- -c makes full insertion, including column names
- -e makes an extended insert. The resulting file is smaller and it's a little faster
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql
To view the list of exisitng databases, you can use following command:
mysqlshow -u USER -pPASSWORD
To see the list of database tables:
mysqlshow -u USER -pPASSWORD DATABASE
Basic MySQL Statements
|List all databases||SHOW DATABASES;||SHOW DATABASES;|
|Create database||CREATE DATABASE database;||CREATE DATABASE UsersDB;|
|Use a database||USE database;||USE UsersDB;|
|List tables in the database||SHOW TABLES;||SHOW TABLES;|
|Show the structure of a table||DESCRIBE table;
SHOW COLUMNS FROM table;
SHOW COLUMNS FROM Animals;
|Delete a database (Be Careful!)||DROP DATABASE database;||DROP DATABASE UsersDB;|