MySQL Cheat Sheet

By Bahae Eddine HALIM

SELECT Query Modifying Data Create Table
SELECT col1, col2
FROM table Insert INSERT INTO tablename Create Table CREATE TABLE tablename (
JOIN table2 ON table1.col = table2.col (col1, col2...) column_name data_type
WHERE condition VALUES (val1, val2); );
GROUP BY column_name Insert from a
HAVING condition INSERT INTO tablename
Table
ORDER BY col1 ASC|DESC; (col1, col2...) Create Table with Constraints
SELECT col1, col2...

CREATE TABLE tablename (

SELECT Keywords Insert Multiple
Rows
INSERT INTO tablename (col1,
col2…) column_name data_type NOT NULL,
VALUES CONSTRAINT pkname PRIMARY KEY (col),
DISTINCT: Removes SELECT DISTINCT product_name (valA1, valB1), CONSTRAINT fkname FOREIGN KEY (col)
duplicate results FROM product; (valA2, valB2), REFERENCES other_table(col_in_other_table),
(valA3, valB3); CONSTRAINT ucname UNIQUE (col),
CONSTRAINT ckname CHECK (conditions)
BETWEEN: Matches a SELECT product_name
Update UPDATE tablename );
value between two FROM product
other values (inclusive) WHERE price BETWEEN 50 AND 100; SET col1 = val1
WHERE condition;
Create Temporary CREATE TEMPORARY TABLE
SELECT product_name Table tablename (
IN: Matches to any of
FROM product Update with UPDATE t colname datatype
the values in a list
WHERE category IN a Join SET col1 = val1 );
('Electronics', 'Furniture'); FROM tablename t
INNER JOIN table x Drop Table DROP TABLE tablename;
LIKE: Performs SELECT product_name ON t.id = x.tid
wildcard matches using FROM product WHERE condition;
_ or % WHERE product_name
LIKE '%Desk%'; Delete DELETE FROM tablename
Alter Table
WHERE condition;
Add Column ALTER TABLE tablename
ADD columnname datatype;
Joins Indexes
Drop Column ALTER TABLE tablename
Create Index CREATE INDEX indexname DROP COLUMN columnname;
SELECT t1.*, t2.*
ON tablename (cols);
FROM t1
join_type t2 ON t1.col = t2.col; Modify Column ALTER TABLE tablename CHANGE
Drop Index DROP INDEX indexname; columnname newcolumnname newdatatype;
Table 1 Table 2

A A
Set Operators Rename Column ALTER TABLE tablename CHANGE
COLUMN currentname TO newname;
B B

C D UNION: Shows unique ALTER TABLE tablename ADD

Add Constraint
rows from two result sets. CONSTRAINT constraintname
INNER JOIN: show all matching A
constrainttype (columns);
A
records in both tables. UNION ALL: Shows all
B B rows from two result sets.
Drop Constraint ALTER TABLE tablename DROP
constraint_type constraintname;
LEFT JOIN: show all records from left A A
INTERSECT: Shows rows that
table, and any matching records from ALTER TABLE tablename
B B exist in both result sets. Rename Table
right table. RENAME TO newtablename;
C

MINUS is not recognised in MySQL

RIGHT JOIN: show all records from
Window/Analytic Functions
A A
right table, and any matching records
B B
from left table.
D function_name ( arguments ) OVER (

Aggregate Functions [query_partition_clause]

[ORDER BY order_by_clause
FULL JOIN: show all records from A
A [windowing_clause] ] )
both tables, whether there is a match
SUM: Finds a total of the numbers provided
or not. B B
COUNT: Finds the number of records Example using RANK, showing the student details and their rank
C AVG: Finds the average of the numbers provided according to the fees_paid, grouped by gender:
MIN: Finds the lowest of the numbers provided
D SELECT
MAX: Finds the highest of the numbers provided
student_id, first_name, last_name, gender, fees_paid,

CASE Statement
RANK() OVER (
PARTITION BY gender ORDER BY fees_paid
Common Functions ) AS rank_val
Simple Case CASE name FROM student;
WHEN 'John' THEN 'Name John' LENGTH(string): Returns the length of the provided string
WHEN 'Steve' THEN 'Name Steve' INSTR(string, substring): Returns the position of the substring
ELSE 'Unknown' within the specified string.
END CAST(expression AS datatype): Converts an expression into the Subqueries
specified data type.
ADDDATE(input_date, days): Adds a number of days to a Single Row SELECT id, last_name, salary
Searched Case CASE FROM employee
specified date.
WHEN name='John' THEN 'Name John' WHERE salary = (
NOW: Returns the current date, including time.
WHEN name='Steve' THEN 'Name Steve' SELECT MAX(salary)
CEILING(input_val): Returns the smallest integer greater than
ELSE 'Unknown' FROM employee
the provided number.
END );
FLOOR(input_val): Returns the largest integer less than the
provided number.
Common Table Expression ROUND(input_val, [round_to]): Rounds a number to a specified
number of decimal places.
Multi Row SELECT id, last_name, salary
FROM employee
TRUNCATE(input_value, num_decimals): Truncates a number to WHERE salary IN (
WITH queryname AS ( SELECT salary
a number of decimals.
SELECT col1, col2 FROM employee
REPLACE(whole_string, string_to_replace, replacement_string):
FROM firsttable) WHERE last_name LIKE 'C%'
Replaces one string inside the whole string with another string.
SELECT col1, col2.. );
SUBSTRING(string, start_position): Returns part of a value,
FROM queryname...;
based on a position and length.
 MySQL Data Types
By Bahae Eddine HALIM
Numeric Character

Bit-value type. Parameter “n” indicates the number of bits per CHAR (n) A fixed-length string. Right-padded with spaces up to the
BIT (n)
value, from 1 to 64. Default 1. specified length of “n”. Up to 255 bytes.

TINYINT (n) A very small integer. Can be signed or unsigned. VARCHAR (n) A variable-length string. The length parameter of “n” can be from
[UNSIGNED] Signed: -128 to 127, Unsigned: 0 to 255. 0 to 65,535. Up to 65,535 bytes.

SMALLINT (n) A small integer. Can be signed or unsigned. BINARY (n) Similar to CHAR but stores binary byte strings rather than
[UNSIGNED] Signed: -32,768 to 32,767, Unsigned: 0 to 65,535 nonbinary strings. Parameter “n” is the number of bytes.

MEDIUMINT (n) A medium-sized integer. Can be signed or unsigned. VARBINARY (n) Similar to VARCHAR but stores binary byte strings rather than
[UNSIGNED] Signed: -8,388,608 to 8,388,607,Unsigned: 0 to 16,777,215 nonbinary strings. Parameter “n” is the number of bytes.

INT (n) A normal-sized integer. Can be signed or unsigned. BLOB (n) A BLOB column that can store a value up to “n” bytes. Up to
[UNSIGNED] Signed: -2,147,483,648 to 2,147,483,647,Unsigned: 0 to 65,535 bytes.
4,294,967,295
TINYBLOB A BLOB column with a smaller maximum length, up to 255 bytes.
INTEGER (n) A synonym for INT. Can be signed or unsigned.
[UNSIGNED] Signed: -2,147,483,648 to 2,147,483,647, Unsigned: 0 to TEXT (n) A text column, and parameter “n” is the maximum number of
4,294,967,295 bytes. Up to 65,535 bytes bytes.

BIGINT (n) A large integer. Can be signed or unsigned. TINYTEXT A text column with a smaller maximum length, up to 255 bytes.
[UNSIGNED] Signed: -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807 MEDIUMBLOB A BLOB with a higher maximum length than BLOB. Up to
Unsigned: 0 to 18,446,744,073,709,551,615 16,777,215 (2^24 − 1) bytes

DECIMAL (n [, d]) A number with decimal places. Parameter “n” is the precision or MEDIUMTEXT A text column with a higher maximum length than TEXT. Up to
[UNSIGNED] number of digits, and “d” is the number of digits after the decimal 16,777,215 (2^24 − 1) bytes
point (the scale). Maximum for n is 65 and maximum for d is 30.
(Note the UNSIGNED parameter is deprecated) LONGBLOB A BLOB column with a high maximum length. Up to
4,294,967,295 or 4GB (2^32 − 1) bytes
DEC (n [,d]) Synonym for DECIMAL
LONGTEXT A text column with a high maximum length. Up to 4,294,967,295
NUMERIC (n [,d]) Synonym for DECIMAL
or 4GB (2^32 − 1) bytes
FIXED (n [,d]) Synonym for DECIMAL
ENUM (value_list) A string object that can have only one value from the list of
FLOAT (n [,d]) A small single-precision floating-point number. Uses values specified, or NULL. Can have up to 65,535 items in its list.
parameters of “n” for number of digits and “d” for number of
digits after the decimal place. Note: the parameters of SET (value_list) A string object that can have zero or more values from the list of
FLOAT are deprecated as of v8.0.17 values specified. Can have up to 64 items in its list.
Range: -3.402823466E+38 to -1.175494351E-38, 0, and
JSON Stores JSON Data
1.175494351E-38 to 3.402823466E+38

DOUBLE (n [,d]) A normal-sized double-precision floating-point number. Uses

parameters of “n” for number of digits and “d” for number of
digits after the decimal place. Note: the parameters of FLOAT are
deprecated as of v8.0.17
Range: -1.7976931348623157E+308 to Other
-2.2250738585072014E-308, 0, and 2.2250738585072014E-
308 to 1.7976931348623157E+308 GEOMETRY Stores geometry values of any type

DOUBLE PRECISION Synonym for DOUBLE

POINT Stores a point in geometry
REAL Synonym for DOUBLE

LINESTRING Stores a line shape

Date
DATE A date value (no time). POLYGON Stores a polygon shape
Range 1000-01-01 to 9999-12-31

DATETIME (fsp) A date and time value. The parameter “fsp” is fractional seconds MULTIPOINT Stores a collection of points
precision or the number of fractional seconds that can be stored.
Range: 1000-01-01 00:00:00.000000 to 9999-12-31
23:59:59.999999 MULTILINESTRING Stores a collection of lines

TIMESTAMP (fsp) A timestamp value, stores date and time. Has a smaller range
than DATETIME. The parameter “fsp” is fractional seconds MULTIPOLYGON Stores a collection of polygons
precision or the number of fractional seconds that can be
stored.
Range: 1970-01-01 00:00:01.000000 UTC to 2038-01-19 GEOMETRYCOLLECTION Store a collection of geometry objects
03:14:07.999999

TIME (fsp) A time value. The parameter “fsp” is fractional seconds precision BOOL Synonym for TINYINT(1). Zero is false,
or the number of fractional seconds that can be stored nonzero values are true.
Range: -838:59:59.000000 to 838:59:59.000000
BOOLEAN
Synonym for TINYINT(1). Zero is false,
YEAR A year in a 4-digit format. nonzero values are true.
Range: 1901 to 2155