Skip to content

Instantly share code, notes, and snippets.

@sniperwolf

sniperwolf/MySQL_basic_and_simple_cheatsheet.md

Last active September 6, 2022 04:02
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save sniperwolf/ae3f84fbfe243a5c9c02 to your computer and use it in GitHub Desktop.
Save sniperwolf/ae3f84fbfe243a5c9c02 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL Basic and Simple Cheatsheet
Raw
MySQL_basic_and_simple_cheatsheet.md

MySQL Basic Simple Cheatsheet

Table of contents

MySQL Mathematical Functions

(back to Table of contents ↑)

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)

MySQL String Functions

(back to Table of contents ↑)

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)

Basic MySQL Commands

(back to Table of contents ↑)

Official reference of SHOW syntax, DROP DATABASE and DROP TABLE.

What How Examples
List all databases SHOW DATABASES; SHOW DATABASES;
Create database CREATE DATABASE database; CREATE DATABASE PhoneDB;
Use a database USE database; USE PhonDB;
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 (Careful!) DROP DATABASE database; DROP DATABASE PhoneDB;

SQL Commands

(back to Table of contents ↑)

Querying

Official reference of SELECT syntax.

Click on the What entry to see the How's and Examples's code better formatted.

What How Examples
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;
Subquery SELECT column1, column2 FROM ( SELECT some1 AS column1, some2 AS column2,... FROM table ) AS alias; SELECT LastName FROM ( SELECT Books.LastName AS LastName FROM BOOKS GROUP BY LastName ) AS t;

Modifying

Official reference of INSERT ... SELECT syntax, UPDATE syntax, DELETE syntax, INSERT syntax, CREATE TABLE syntax and ALTER TABLE syntax.

Click on the What entry to see the How's and Examples's code better formatted.

What How Examples
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/Select INSERT INTO table (column1,column2,...) SELECT statement; 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 like '%Smith%' AND FirstName='John';
Updating Data UPDATE table SET column1=value1, column2=value2, ... columnk=valuek [WHERE condition(s)]; UPDATE Students SET LastName='Jones' WHERE StudentID=987654321;
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 (Careful!) DROP TABLE [IF EXISTS] table; DROP TABLE Animals;

Common MySQL Column Types

Official reference of MySQL Column Types.

(back to Table of contents ↑)

Purpose Data Type Example
Integers int(M) int(5)
Floating-point (real) numbers float(M,D) float(12,3)
Double-precision Floating-point double(M,D) double(20,3)
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')

MySQL Command-Line

(back to Table of contents ↑)

What How Examples
Running MySQL mysql -uusername -ppassword mysql -ucusack2RO -pegbdf5s
Importing mysql -uusername -ppassword < filename mysql -usomeDB -pblah < myNewDB.sql
Dumping (Saving) mysqldump -uusername -ppassword database [tables] > filename mysqldump -ume -pblah myDB > My.sql, mysqldump -ume -pblah myDB table1 table2 > my.sql

Rapid Link to MySQL.com

(back to Table of contents ↑)

Well formatted Examples

SQL Commands: Querying

All columns (up ↑)

How (up ↑)
SELECT *
FROM table;
Example (up ↑)
SELECT *
FROM Students;

Some columns (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM table;
Example (up ↑)
SELECT LastName, FirstName
FROM Students;

Some rows/columns (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM table
[WHERE condition(s)];
Example (up ↑)
SELECT LastName,FirstName
FROM Students
WHERE StudentID LIKE '%123%';

No Repeats (up ↑)

How (up ↑)
SELECT [DISTINCT] column(s)
FROM table;
Example (up ↑)
SELECT DISTINCT LastName
FROM Students;

Ordering (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM table
[ORDER BY column(s) [DESC]];
Example (up ↑)
SELECT LastName,FirstName
FROM Students
ORDER BY LastName, FirstName DESC;

Column Aliases (up ↑)

How (up ↑)
SELECT column1 [ AS *alias1*], column2 [ AS alias2], ...
FROM table1;
Example (up ↑)
SELECT LastName,FirstName AS First
FROM Students;

Grouping (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM TABLE
[GROUP BY * COLUMN(s)*];
Example (up ↑)
SELECT LastName, COUNT(*)
FROM Students
GROUP BY LastName;

Group Filtering (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM TABLE [GROUP BY * COLUMN(s)*]
[HAVING CONDITION(s)];
Example (up ↑)
SELECT LastName, COUNT(*)
FROM Students
GROUP BY LastName
HAVING LastName LIKE '%son';

Joins (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM table1,table2,...
[WHERE CONDITION(s)];
Example (up ↑)
SELECT LastName,Points
FROM Students,Assignments
WHERE AssignmentID=12 AND Students.StudentID=Assignments.StudentID;

Table Aliases (up ↑)

How (up ↑)
SELECT column1,column2,...
FROM table1 [alias1], table2 [alias2],...
[WHERE CONDITION(s)];
Example (up ↑)
SELECT LastName,Points
FROM Students S,Assignments A
WHERE S.StudentID=A.StudentID AND A.AssignmentID=12;

Everything (up ↑)

How (up ↑)
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]];
Example (up ↑)
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;

Subquery (up ↑)

How (up ↑)
SELECT column1, column2
FROM (
	SELECT some1 AS column1, some2 AS column2,...
	FROM table
) AS alias;
Example (up ↑)
SELECT LastName
FROM (
	SELECT Books.LastName AS LastName
	FROM Books
	GROUP BY LastName
) AS t;

SQL Commands: Modifying

Create table (up ↑)

How (up ↑)
CREATE TABLE TABLE (
    column1 TYPE [[ NOT] NULL] [ AUTO_INCREMENT],
    column2 TYPE [[ NOT] NULL] [ AUTO_INCREMENT],
    ... other options,
    PRIMARY KEY (COLUMN(s))
);
Example (up ↑)
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 (up ↑)

How (up ↑)
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...);
Example (up ↑)
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 (up ↑)

How (up ↑)
INSERT INTO TABLE (column1,column2,...)
SELECT statement;
Example (up ↑)
INSERT INTO Students (StudentID,FirstName,LastName)
SELECT StudentID,FirstName,LastName
FROM OtherStudentTable
WHERE LastName LIKE '%son';

Delete data (up ↑)

How (up ↑)
DELETE FROM table
[WHERE condition(s)];

(Omit WHERE to delete all data)

Example (up ↑)
DELETE
FROM Students
WHERE LastName='Smith';
DELETE
FROM Students
WHERE LastName LIKE '%Smith%' AND FirstName='John';
DELETE
FROM Students;

Updating Data (up ↑)

How (up ↑)
UPDATE TABLE SET column1=value1, column2=value2, ... columnk=valuek
[WHERE CONDITION(s)];
Example (up ↑)
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 (up ↑)

How (up ↑)
ALTER TABLE table ADD COLUMN column type options;
Example (up ↑)
ALTER TABLE Students ADD COLUMN Hometown varchar(20);

Delete column (up ↑)

How (up ↑)
ALTER TABLE table
DROP COLUMN column;
Example (up ↑)
ALTER TABLE Students
DROP COLUMN Dorm;

Delete table (up ↑)

How (up ↑)
DROP TABLE [IF EXISTS] table;
Example (up ↑)
DROP TABLE Animals;

Credits

Partial Code and Examples come from Stephen D. Scott.

@taliwalt
Copy link

taliwalt commented Apr 8, 2016

Super helpful. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment