SQL Cheat Sheet with Commands & Description (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( |
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 ( |
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 |
sub queries |
SQL JOINS commands
Command | Description |
---|---|
SELECT * FROM table1 CROSS JOIN table2 |
Cross JOIN |
SELECT table1.t1 , table1.t2 , table2.t1 |
INNER JOIN |
SELECT A.t1 , B.t2 , B.t3 |
LEFT JOIN |
SELECT A.t1 , A.t2, B.t3 |
RIGHT JOIN |
SELECT A.t1 , B.t2 , B.t3 |
“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 |