MySQL Cheat Sheet & Commonly Used Commands

This tutorial describes most popular SQL commands and cheets.
Provided by Leumas Naypoka / www.apphp.com

Content:


MySQL Command-Line

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.

Important:
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
- or -
mysql > SHOW DATABASES

Basic MySQL Statements

What How Example(s)
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;
DESCRIBE Animals;
SHOW COLUMNS FROM Animals;
Delete a database (Be Careful!) DROP DATABASE database; DROP DATABASE UsersDB;

Modifying MySQL Statements

What How Example(s)
Create table CREATE TABLE table (
        column1 type [[NOT] NULL]
                [AUTO_INCREMENT],
        column2 type [[NOT] NULL]
                [AUTO_INCREMENT],
        ...
        other options,
        PRIMARY KEY (column(s))    );
CREATE TABLE Students (
        LastName varchar(30) NOT NULL,
        FirstName varchar(30) NOT NULL,
        StudentID int NOT NULL,
        Major varchar(20),
        Dorm varchar(20),
        PRIMARY KEY (StudentID)     );
Insert data INSERT INTO table VALUES
        (list of values);
INSERT INTO table SET
        column1=value1,
        column2=value2,
        ...
        columnk=valuek;
INSERT INTO table (column1,column2,...)
        VALUES (value1,value2...);
INSERT INTO Students VALUES
('Smith','John',123456789,'Math','Selleck');
INSERT INTO Students SET
        FirstName='John',
        LastName='Smith',
        StudentID=123456789,
        Major='Math';
INSERT INTO Students
        (StudentID,FirstName,LastName)
        VALUES (123456789,'John','Smith');
Insert/Select INSERT INTO table (column1,column2,...)
        SELECT statement;
        (See below)
INSERT INTO Students
        (StudentID,FirstName,LastName)
        SELECT StudentID,FirstName,LastName
        FROM OtherStudentTable;
        WHERE LastName like '%son';
Delete data DELETE FROM table
        [WHERE condition(s)];



(Omit WHERE to delete all data)
DELETE FROM Students
        WHERE LastName='Smith';
DELETE FROM Students
        WHERE LastName like '%Smith%';
        AND FirstName='John';
DELETE FROM Students;
Updating Data UPDATE table SET
        column1=value1,
        column2=value2,
        ...
        columnk=valuek
        [WHERE condition(s)];
UPDATE Students SET
        LastName='Jones' WHERE
        StudentID=987654321;
UPDATE Students SET
        LastName='Jones', Major='Theatre'
        WHERE StudentID=987654321 OR
        (MAJOR='Art' AND FirstName='Pete');
Insert column ALTER TABLE table ADD COLUMN
        column type options;
ALTER TABLE Students ADD COLUMN
        Hometown varchar(20);
Delete column ALTER TABLE table
        DROP COLUMN column;
ALTER TABLE Students
        DROP COLUMN Dorm;
Delete table (Be Careful!) DROP TABLE [IF EXISTS] table; DROP TABLE Animals;

Querying MySQL Statements

What How Example(s)
All columns SELECT * FROM table; SELECT * FROM Students;
Some columns SELECT column1,column2,... FROM table; SELECT LastName, FirstName FROM Students;
Some rows/
columns
SELECT column1,column2,...
        FROM table
        [WHERE condition(s)];
SELECT LastName,FirstName
        FROM Students
        WHERE StudentID LIKE '%123%';
No Repeats SELECT [DISTINCT] column(s)
        FROM table;
SELECT DISTINCT LastName
        FROM Students;
Ordering SELECT column1,column2,...
        FROM table
        [ORDER BY column(s) [DESC]];
SELECT LastName,FirstName
        FROM Students
        ORDER BY LastName, FirstName DESC;
Column
Aliases
SELECT column1 [AS alias1],
        column2 [AS alias2], ...
        FROM table1;
SELECT LastName,FirstName AS First
        FROM Students;
Grouping SELECT column1,column2,...
        FROM table
        [GROUP BY column(s)];
SELECT LastName,COUNT(*)
        FROM Students
        GROUP BY LastName;
Group Filtering SELECT column1,column2,...
        FROM table
        [GROUP BY column(s)]
        [HAVING condition(s)];
SELECT LastName,COUNT(*)
        FROM Students
        GROUP BY LastName
        HAVING LastName like '%son';
Joins SELECT column1,column2,...
        FROM table1,table2,...
        [WHERE condition(s)];
SELECT LastName,Points
        FROM Students,Assignments
        WHERE AssignmentID=12 AND
        Students.StudentID=Assignments.StudentID;
Table
Aliases
SELECT column1,column2,...
        FROM table1 [alias1],
        table2 [alias2],...
        [WHERE condition(s)];
SELECT LastName,Points
        FROM Students S,Assignments A
        WHERE S.StudentID=A.StudentID AND
        A.AssignmentID=12;
Everything SELECT [DISTINCT]
        column1 [AS alias1],
        column2 [AS alias2], ...
        FROM table1 [alias1],
        table2 [alias2],...
        [WHERE condition(s)]
        [GROUP BY column(s)]
        [HAVING condition(s)]
        [ORDER BY column(s) [DESC]];
SELECT Points, COUNT(*) AS Cnt
        FROM Students S,Assignments A
        WHERE S.StudentID=A.StudentID AND
        A.AssignmentID=12
        GROUP BY Points
        HAVING Points > 10
        ORDER BY Cnt, Points DESC;

MySQL String Functions

What How
Compare strings strcmp(string1,string2)
Convert to lower case lower(string)
Convert to upper case upper(string)
Left-trim whitespace (similar right) ltrim(string)
Substring of string substring(string,index1,index2)
Encrypt password password(string)
Encode string encode(string,key)
Decode string decode(string,key)
Get date curdate()
Get time curtime()
Extract day name from date string dayname(string)
Extract day number from date string dayofweek(string)
Extract month from date string monthname(string)

MySQL Mathematical Functions

What How
Count rows per group COUNT(column | *)
Average value of group AVG(column)
Minumum value of group MIN(column)
Maximum value of group MAX(column)
Sum values in a group SUM(column)
Absolute value abs(number)
Rounding numbers round(number)
Largest integer not greater floor(number)
Smallest integer not smaller ceiling(number)
Square root sqrt(number)
nth power pow(base,exponent)
random number n, 0<n < 1 rand()
sin (similar cos, etc.) sin(number)

Comments


Please post only comments related to the original tutorial. Be polite and helpful, do not spam or offend others.
Create Your Free Account
Please remember that this information is essential to use our services correctly.
After creating the account you will be able to download all of our FREE products.
Fields marked with * are mandatory






Please send me information about updates, new products, specials and discounts from ApPHP!
We recommend that your password should be at least 6 characters long and should be different from your username/email. Please use only letters of the English alphabet to enter your name.

Your e-mail address must be valid. We use e-mail for communication purposes (order notifications, etc). Therefore, it is essential to provide a valid e-mail address to be able to use our services correctly.

All your private data is confidential. We will never sell, exchange or market it in any way. Please refer to Privacy Policy.

By clicking "Create Account", you are indicating that you have read and agree to the ApPHP Terms & Conditions.

Quick Registration with: Facebook / Google