(My) SQL Cheat Sheet: Mysql Command-Line What How Example (S)

(My)SQL Cheat Sheet
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.
MySQL Command-Line
What
How
Example(s)
Running
mysql -uusername -ppassword
mysql -ucusack2RO -pegbdf5s
MySQL
Importing
mysql -uusername -ppassword < filename
mysql -usomeDB -pblah <
myNewDB.sql
Dumping
mysqldump -uusername -ppassword database mysqldump -ume -pblah myDB
(Saving)
[tables] > filename
> My.sql
mysqldump -ume -pblah myDB
table1
table2 > my.sql
Common MySQL Column Types
Purpose
Data Type
Example
Integers
int(M)
int(5)
Floating-point (real)
float(M,D)
float(12,3)
numbers
Double-precision floating- double(M,D)
double(20,3)
point
Dates and times
timestamp(M)
timestamp(8) (for YYYYMMDD)
timestamp(12) (for
YYYYMMDDHHMMSS)
Fixed-length strings
char(M)
char(10)
Variable-length strings
varchar(M)
varchar(20)
A large amount of text
blob
blob
Values chosen from a list enum('value1',value2',...) enum('apples','oranges','bananas')
M is maximum to display, and D is precision to the right of the decimal.
MySQL Mathematical Functions
MySQL String Functions
What
How
What
How
Count rows per COUNT(column | *) Compare strings
strcmp(string1,string2)
group
Convert to lower
lower(string)
Average value of AVG(column)
case
group
Convert to upper
upper(string)
Minumum value MIN(column)
case
of group
Left-trim whitespace ltrim(string)
Maximum value MAX(column)
(similar right)
of group
Sum values in a
group
Absolute value
Rounding
numbers
Largest integer
not greater
Smallest integer
not smaller
Square root
nth power
random number
n, 0<n < 1
sin (similar cos,
etc.)
SUM(column)
abs(number)
round(number)
floor(number)
ceiling(number)
sqrt(number)
pow(base,exponent)
rand()
Substring of string
Encrypt password
Encode string
Decode string
Get date
Get time
Extract day name
from date string
Extract day number
from date string
Extract month from
date string
substring(string,index1,index2)
password(string)
encode(string,key)
decode(string,key)
curdate()
curtime()
dayname(string)
dayofweek(string)
monthname(string)
sin(number)
Basic MySQL Commands

What
How
Example(s)
List all databases
SHOW DATABASES;
SHOW DATABASES;
Create database
CREATE DATABASE
CREATE DATABASE PhoneDB;
database;
Use a database
USE database;
USE PhonDB;
List tables in the database SHOW TABLES;
SHOW TABLES;
Show the structure of a
DESCRIBE table;
DESCRIBE Animals;
table
SHOW COLUMNS FROM
SHOW COLUMNS FROM
table;
Animals;
Delete a database
DROP DATABASE database; DROP DATABASE PhoneDB;
(Careful!)
SQL Commands: Modifying
What
How
Example(s)
Create table CREATE TABLE table (
CREATE TABLE Students (
column1 type [[NOT] NULL]
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL,
StudentID int NOT NULL,
[AUTO_INCREMENT],
Major varchar(20),
column2 type [[NOT] NULL]
Dorm varchar(20),
PRIMARY KEY (StudentID) );
Insert data
[AUTO_INCREMENT],
...
other options,
PRIMARY KEY (column(s))
);
INSERT INTO table VALUES
INSERT INTO Students VALUES
Insert/Select
Delete data
Updating
Data
Insert
column
Delete
column
Delete table
(Careful!)
What
All columns
Some
columns
Some rows/
columns
No Repeats
(list of values);
INSERT INTO table SET
('Smith','John',123456789,'Math','Selleck');
column1=value1,
INSERT INTO Students SET
column2=value2,
FirstName='John',
...
LastName='Smith',
columnk=valuek;
StudentID=123456789,
INSERT INTO table
Major='Math';
(column1,column2,...)
INSERT INTO Students
VALUES (value1,value2...);
(StudentID,FirstName,LastName)
VALUES (123456789,'John','Smith');
INSERT INTO table
INSERT INTO Students
(column1,column2,...)
(StudentID,FirstName,LastName)
SELECT statement;
SELECT StudentID,FirstName,LastName
(See below)
FROM OtherStudentTable;
WHERE LastName like '%son';
DELETE FROM table
DELETE FROM Students
[WHERE condition(s)];
WHERE LastName='Smith';
DELETE FROM Students
WHERE LastName like '%Smith%';
AND FirstName='John';
(Omit WHERE to delete all data) DELETE FROM Students;
UPDATE table SET
UPDATE Students SET
column1=value1,
LastName='Jones' WHERE
column2=value2,
StudentID=987654321;
...
UPDATE Students SET
columnk=valuek
LastName='Jones', Major='Theatre'
WHERE StudentID=987654321 OR
(MAJOR='Art' AND FirstName='Pete');
ALTER TABLE table ADD
ALTER TABLE Students ADD COLUMN
COLUMN
Hometown varchar(20);
column type options;
ALTER TABLE table
ALTER TABLE Students
DROP COLUMN column;
DROP COLUMN Dorm;
DROP TABLE [IF EXISTS]
DROP TABLE Animals;
table;
SQL Commands: Querying
How
Example(s)
SELECT * FROM table;
SELECT * FROM Students;
SELECT column1,column2,... SELECT LastName, FirstName FROM Students;
FROM table;
SELECT column1,column2,... SELECT LastName,FirstName
FROM table
FROM Students
WHERE StudentID LIKE '%123%';
SELECT [DISTINCT]
SELECT DISTINCT LastName
column(s)
FROM Students;
Ordering
Column
Aliases
Grouping
Group
Filtering
Joins
Table
Aliases
Everything
FROM table;
SELECT column1,column2,... SELECT LastName,FirstName
FROM table
FROM Students
[ORDER BY column(s)
ORDER BY LastName, FirstName DESC;
[DESC]];
SELECT column1 [AS
SELECT LastName,FirstName AS First
alias1],
FROM Students;
column2 [AS alias2], ...
FROM table1;
SELECT column1,column2,... SELECT LastName,COUNT(*)
FROM table
FROM Students
[GROUP BY column(s)];
GROUP BY LastName;
SELECT column1,column2,... SELECT LastName,COUNT(*)
FROM table
FROM Students
[GROUP BY column(s)]
GROUP BY LastName
[HAVING condition(s)];
HAVING LastName like '%son';
SELECT column1,column2,... SELECT LastName,Points
FROM table1,table2,...
FROM Students,Assignments
WHERE AssignmentID=12 AND
Students.StudentID=Assignments.StudentID;
SELECT column1,column2,... SELECT LastName,Points
FROM table1 [alias1],
FROM Students S,Assignments A
table2 [alias2],...
WHERE S.StudentID=A.StudentID AND
A.AssignmentID=12;
SELECT [DISTINCT]
SELECT Points, COUNT(*) AS Cnt
column1 [AS alias1],
FROM Students S,Assignments A
column2 [AS alias2], ...
WHERE S.StudentID=A.StudentID AND
FROM table1 [alias1],
A.AssignmentID=12
table2 [alias2],...
GROUP BY Points
[WHERE condition(s)]
HAVING Points > 10
[GROUP BY column(s)]
ORDER BY Cnt, Points DESC;
[HAVING condition(s)]
[ORDER BY column(s)
[DESC]];
For more details, see the following pages from MySQL.com.
MySQL Reference Manual

MySQL Column Types
SHOW syntax
CREATE TABLE syntax
ALTER TABLE syntax
INSERT syntax
DELETE syntax
UPDATE syntax
SELECT syntax
INSERT ... SELECT syntax
MySQL Functions

(My) SQL Cheat Sheet: Mysql Command-Line What How Example (S)

Uploaded by

Copyright:

Available Formats

(My) SQL Cheat Sheet: Mysql Command-Line What How Example (S)

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

(My) SQL Cheat Sheet: Mysql Command-Line What How Example (S)

Uploaded by

Copyright:

Available Formats

(My)SQL Cheat Sheet

Basic MySQL Commands

For more details, see the following pages from MySQL.com.

MySQL Reference Manual

You might also like