SQL Cheat Sheet with Commands & Description (2023)

ByRichard Peterson UpdatedApril 11, 2023

Create Database and table commands

Command	Description
`CREATE DATABASE DATABASE;`	Create database
`CREATE DATABASE IF NOT EXISTS database1;`	IF NOT EXISTS let you to instruct MySQL server to check the existence of a database with a similar name prior to creating database.
`CREATE DATABASE IF NOT EXISTS database1 CHARACTER SET latin1 COLLATE latin1_swedish_ci`	the Latin1 character set uses the latin1_swedish_ci collation which is the Swedish case insensitive order.
`SHOW DATABASES`	You can see list of existing databases by running following SQL command.
`CREATE TABLE [IF NOT EXISTS] TableName (fieldname dataType [optional parameters]) ENGINE = storage Engine;`	Create table syntax

SQL Data Types Cheat Sheet

Numeric Data types

Command	Description
`TINYINT( )`	-128 to 127 normal 0 to 255 UNSIGNED.
`SMALLINT( )`	-32768 to 32767 normal 0 to 65535 UNSIGNED.
`MEDIUMINT( )`	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
`INT( )`	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
`BIGINT( )`	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
`FLOAT`	A small approximate number with a floating decimal point.
`DOUBLE( , )`	A large number with a floating decimal point.
`DECIMAL( , )`	A DOUBLE stored as a string , allowing for a fixed decimal point. Choice for storing currency values.

Text Data Types

Command	Description
`CHAR( )`	A fixed section from 0 to 255 characters long.
`VARCHAR( )`	A variable section from 0 to 255 characters long.
`TINYTEXT`	A string with a maximum length of 255 characters.
`TEXT`	A string with a maximum length of 65535 characters.
`BLOB`	A string with a maximum length of 65535 characters.
`MEDIUMTEXT`	A string with a maximum length of 16777215 characters.
`MEDIUMBLOB`	A string with a maximum length of 16777215 characters.
`LONGTEXT`	A string with a maximum length of 4294967295 characters.
`LONGBLOB`	A string with a maximum length of 4294967295 characters.

Date / Time data types

Command	Description
`DATE`	YYYY-MM-DD
`DATETIME`	YYYY-MM-DD HH:MM:SS
`TIMESTAMP`	YYYYMMDDHHMMSS
`TIME`	HH:MM:SS

Other data types

Command	Description
`ENUM`	To store text value chosen from a list of predefined text values.
`SET`	This is also used for storing text values chosen from a list of predefined text values. It can have multiple values.
`BOOL`	Synonym for TINYINT(1), used to store Boolean values
`BINARY`	Similar to CHAR, difference is texts are stored in binary format.
`VARBINARY`	Similar to VARCHAR, difference is texts are stored in binary format.

SQL SELECT statement command

Command	Description
`SELECT * FROM table1;`	select the table
`SELECT t1,t2,t3, t4 FROM table1;`	we are only interested in getting only the t1, t2, t3 and t4 fields only.
`SELECT Concat(t1, (, t3, )) , t4 FROM table2;`	Getting table2 listing
`SELECT column_name\|value\|expression [AS] alias_name;`	Alias field names syntax

SQL WHERE clause with AND, OR, IN, NOT IN commands

Command	Description
`SELECT * FROM tableName WHERE condition;`	WHERE clause Syntax
`SELECT * FROM table1 WHERE t1 = 2 AND t2 = 2008;`	WHERE clause combined with – AND LOGICAL Operator
`SELECT * FROM table1 WHERE t1 = 1 OR t1 = 2;`	WHERE clause combined with – OR LOGICAL Operator
`SELECT * FROM table2 WHERE t1 IN (1,2,3);`	WHERE clause combined with – IN Keyword
`SELECT * FROM table2 WHERE t1 NOT IN (1,2,3);`	WHERE clause combined with – NOT IN Keyword
`SELECT * FROM table2 WHERE t3 = Female;`	WHERE clause combined with Equal(=) to COMPARISON OPERATORS
`SELECT * FROM table3 WHERE t3 > 2000;`	WHERE clause combined with greater than(>) to COMPARISON OPERATORS
`SELECT * FROM table1 WHERE t1<> 1;`	WHERE clause combined with Not Equal to (<>)COMPARISON OPERATORS

SQL Command INSERT INTO Table

Command	Description
`INSERT INTO table_name(column_1,column_2,...) VALUES (value_1,value_2,...);`	basic syntax of the SQL INSERT command
`INSERT INTO table1 (t1,t2,t3,t4) VALUES (X1,X2,X3,X4);`	INSERT data into table
`INSERT INTO table_1 SELECT * FROM table_2;`	Inserting into a Table from another Table

SQL DELETE command

Command	Description
`DELETE FROM table_name [WHERE condition];`	Delete a row in MySQL

SQL Update Command

Command	Description
`UPDATE table_name SET column_name = new_value [WHERE condition];`	update command syntax

ORDER BY in SQL: DESC & ASC command

Command	Description
`SELECT statement... [WHERE condition \| GROUP BY field_name(s) HAVING condition] ORDER BY field_name(s) [ASC \| DESC];`	Order by clause basic syntax
`SELECT {fieldName(s) \| *} FROM tableName(s) [WHERE condition] ORDER BY fieldname(s) ASC /DESC [LIMIT N]`	DESC and ASC syntax

SQL GROUP BY and HAVING Clause command

Group by

Command	Description
`SELECT statements... GROUP BY column_name1[,column_name2,...] [HAVING condition];`	GROUP BY Syntax

Grouping and aggregate functions

Command	Description
`SELECT t2,COUNT(t1) FROM table1 GROUP BY t2;`	Suppose we want the total number of t2 column values in our database.

HAVING clause

Command	Description
`SELECT * FROM table2 GROUP BY t1_id,t4 HAVING t1_id = x1;`	all the t4 for table2 t1 id x1. We would use the following script to achieve our results.

SQL Wildcards commands for Like, NOT Like, Escape, ( % ), ( _ )

% the percentage wildcards commmand in MySQL

Command	Description
`SELECT statements... WHERE fieldname LIKE xxx%;`	basic syntax for % percentage wildcard

_ underscore wildcard command

Command	Description
`SELECT * FROM table1 WHERE t3 LIKE x2_;`	all the table1 that were t3 in the year “x2”

NOT Like wildcard command

Command	Description
`SELECT * FROM table1 WHERE t3 NOT LIKE X2_;`	Suppose we want to get table1 that were not t3 in the year X2_

Escape keyword wildcard command

Command	Description
`LIKE 67#%% ESCAPE #;`	we want to check for the string “67%”

SQL Regular Expressions (REGEXP)

Command	Description
`SELECT statements... WHERE fieldname REGEXP pattern;`	basic syntax of Regular Expression

Regular expression Metacharacters

Command	Description
`*`	The asterisk (*) metacharacter is used to match zero (0) or more instances of the strings preceding it
`+`	The plus (+) metacharacter is used to match one or more instances of strings preceding it.
`?`	The question(?) metacharacter is used to match zero (0) or one instances of the strings preceding it.
`.`	The dot (.) metacharacter is used to match any single character in exception of a new line.
`[abc]`	The charlist [abc] is used to match any of the enclosed characters.
`[^abc]`	The charlist [^abc] is used to match any characters excluding the ones enclosed.
`[A-Z]`	The [A-Z] is used to match any upper case letter
`[a-z]`	The [a-z] is used to match any lower case letter
`[0-9]`	The [0-9] is used to match any digit from 0 through to 9.
`^`	The caret (^) is used to start the match at beginning.
`\|`	The vertical bar (\|) is used to isolate alternatives.
`[[:<:]]`	The[[:<:]] matches the beginning of words.
`[[:>:]]`	The [[:>:]] matches the end of words.
`[:class:]`	The [:class:] matches a character class i.e. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters.

SQL Functions commands

String functions

Command	Description
`SELECT t1_id,t2, UCASE(t2) FROM table1;`	the “UCASE” function to do that. It takes a string as a parameter and converts all the letters to upper case.

Numeric functions

Command	Description	Example
`DIV`	Integer division	SELECT 23 DIV 6;
`/`	Division	SELECT 23 / 6 ;
`-`	Subtraction	SELECT 23 – 6 ;
`+`	Addition	SELECT 23 + 6 ;
`*`	Multiplication	SELECT 23 * 6 AS multiplication_result;
`% or MOD`	Modulus	SELECT 23 % 6 ; or SELECT 23 MOD 6;
`Floor`	this function removes decimals places from a number and rounds it to the nearest lowest number.	SELECT FLOOR(23 / 6) AS floor_result;
`Round`	this function rounds a number with decimal places to the nearest whole number.	SELECT ROUND(23 / 6) AS round_result;

Stored functions

Command	Description
`CREATE FUNCTION sf_name ([parameter(s)])`	basic syntax for creating a stored function
`CREATE FUNCTION sf_name ([parameter(s)])`	Mandatory and tells MySQL server to create a function named `sf_name’ with optional parameters defined in the parenthesis.
`RETURNS data type`	Mandatory and specifies the data type that the function should return.
`DETERMINISTIC`	The function will return the same values if the same arguments are supplied to it.
`STATEMENTS`	The procedural code that the function executes.

SQL Aggregate function commands

Command	Description
`SELECT COUNT(t1_id) FROM table1 WHERE t1_id = 2;`	COUNT Function
`SELECT MIN(t3) FROM table2;`	MIN function
`SELECT MAX(t3) FROM table2;`	MAX function
`SELECT SUM(t4) FROM table3;`	SUM function
`SELECT AVG(t4) FROM table3;`	AVG function

SQL IS NULL & IS NOT NULL commands

Command	Description
`SELECT COUNT(t3) FROM table1;`	Null as a Value
`CREATE TABLE table2( t1_number int NOT NULL, t2_names varchar(255) , t3 varchar(6) );`	NOT NULL Values
`comlumn_name IS NULL` `comlumn_name NOT NULL`	NULL Keywords Basic syntax
`SELECT * FROM table1 WHERE t2_number IS NULL;`	Example of IS NULL
`SELECT * FROM table1 WHERE t2_number IS NOT NULL;`	Example of IS NOT NULL

SQL AUTO_INCREMENT commands

Command	Description
`CREATE TABLE table1 ( t1_id int(11) AUTO_INCREMENT, t2_name varchar(150) DEFAULT NULL, t3 varchar(500) DEFAULT NULL, PRIMARY KEY (t1_id) );`	Auto increment syntax

SQL – ALTER, DROP, RENAME, MODIFY

Command	Description
`ALTER TABLE table_name ADD COLUMN column_name data_type;`	Alter- syntax
`DROP TABLE sample_table;`	DROP TABLE syntax
`RENAME TABLE current_table_name TO new_table_name;`	RENAME COMMAND syntax
`ALTER TABLE table1 CHANGE COLUMN t1_names t1name char(250) NOT NULL;`	CHANGE KEYWORD
`ALTER TABLE table1MODIFY t1name char(50) NOT NULL;`	MODIFY KEYWORD
`ALTER TABLE table1 ADD t4 date NULL AFTER t3;`	AFTER KEYWORD

SQL LIMIT & OFFSET

Command	Description
`SELECT {fieldname(s) \| *} FROM tableName(s) [WHERE condition] LIMIT N;`	LIMIT keyword syntax
`SELECT * FROM table1 LIMIT 1, 2;`	OFF SET in the LIMIT query

SQL SubQuery commands :

Command	Description
`SELECT t1_name FROM table1 WHERE category_id =( SELECT MIN(t1_id) from table2);`	sub queries

SQL JOINS commands

Command	Description
`SELECT * FROM table1 CROSS JOIN table2`	Cross JOIN
`SELECT table1.t1 , table1.t2 , table2.t1 FROM table1 ,table2 WHERE table2.id = table1.table2_id`	INNER JOIN
`SELECT A.t1 , B.t2 , B.t3 FROM table2 AS A LEFT JOIN table1 AS B ON B.table2_id = A.id`	LEFT JOIN
`SELECT A.t1 , A.t2, B.t3 FROM table1 AS A RIGHT JOIN table2 AS B ON B.id = A.table2_id`	RIGHT JOIN
`SELECT A.t1 , B.t2 , B.t3 FROM table2 AS A LEFT JOIN table1 AS B USING ( table2_id )`	“ON” and “USING” clauses

SQL UNION commands

Command	Description
`SELECT column1, column2 FROM table1`	UNION syntax
`SELECT column1,column2 FROM table2;`	UNION DISTINCT

SQL in Views commands

Command	Description
`CREATE VIEW view_name AS SELECT statement;`	Views syntax
`DROP VIEW general_v_movie_rentals;`	Dropping views

SQL Index commands

Command	Description
`CREATE INDEX id_index ON table_name(column_name);`	Add index basic syntax
`DROP INDEX index_id ON table_name;`	Drop index basic syntax