Luke Harrison

Web Developer & Writer

In this guide, you’ll find a useful cheat sheet that documents some of the more commonly used elements of SQL, and even a few of the less common. Hopefully, it will help developers – both beginner and experienced level – become more proficient in their understanding of the SQL language.

Use this as a quick reference during development, a learning aid, or even print it out and bind it if you’d prefer (whatever works!).

But before we get to the cheat sheet itself, for developers who may not be familiar with SQL, let’s start with…

PDF Version of SQL Cheat Sheet

SQL Cheat Sheet (Download PDF)

Infographic Version of SQL Cheat Sheet (PNG)

SQL Cheat Sheet (Download PNG)

SQL Cheat Sheet

What is SQL

SQL stands for Structured Query Language. It’s the language of choice on today’s web for storing, manipulating and retrieving data within relational databases. Most, if not all of the websites you visit will use it in some way, including this one.

Here’s what a basic relational database looks like. This example in particular stores e-commerce information, specifically the products on sale, the users who buy them, and records of these orders which link these 2 entities.

A basic relational database

 

Using SQL, you are able to interact with the database by writing queries, which when executed, return any results which meet its criteria.

Here’s an example query:-

SELECT * FROM users;

Using this SELECT statement, the query selects all data from all columns in the user’s table. It would then return data like the below, which is typically called a results set:-

Example users table

 

If we were to replace the asterisk wildcard character (*) with specific column names instead,  only the data from these columns would be returned from the query.

SELECT first_name, last_name FROM users;

Example users table with reduced columns

We can add a bit of complexity to a standard SELECT statement by adding a WHERE clause, which allows you to filter what gets returned.

SELECT * FROM products WHERE stock_count <= 10 ORDER BY stock_count ASC;

This query would return all data from the products table with a stock_count value of less than 10 in its results set.

The use of the ORDER BY keyword means the results will be ordered using the stock_count column, lowest values to highest.

Example products table

Using the INSERT INTO statement, we can add new data to a table. Here’s a basic example adding a new user to the users table:-

INSERT INTO users (first_name, last_name, address, email)
VALUES ('Tester', 'Jester', '123 Fake Street, Sheffield, United Kingdom', 'test@lukeharrison.dev');

Then if you were to rerun the query to return all data from the user’s table, the results set would look like this:

Example table with new row

Of course, these examples demonstrate only a very small selection of what the SQL language is capable of.

SQL vs MySQL

You may have heard of MySQL before. It’s important that you don’t confuse this with SQL itself, as there’s a clear difference.

SQL vs MySQLSQL is the language. It outlines syntax that allows you to write queries that manage relational databases. Nothing more.

MySQL meanwhile is a database system that runs on a server. It implements the SQL language, allowing you to write queries using its syntax to manage MySQL databases.

In addition to MySQL, there are other systems that implement SQL. Some of the more popular ones include:

  • PostgreSQL
  • SQLite
  • Oracle Database
  • Microsoft SQL Server

Installing MySQL

Windows

The recommended way to install MySQL on Windows is by using the installer you can download from the MySQL website.

Install MySQL Windows

MacOS

On macOS, the recommended way to install MySQL is using native packages, which sounds a lot more complicated than it actually is. Essentially, it also involves just downloading an installer.

Install MySQL Mac

Alternatively, If you prefer to use package managers such as Homebrew, you can install MySQL like so:

brew install mysql

Whilst if you need to install the older MySQL version 5.7, which is still widely used today on the web, you can:

brew install mysql@5.7

Using MySQL

With MySQL now installed on your system, to get up and going as quickly as possible writing SQL queries, it’s recommended that you use an SQL management application to make managing your databases a much simpler, easier process.

There are lots of apps to choose from which largely do the same job, so it’s down to your own personal preference on which one to use:

  • MySQL Workbench is developed by Oracle, the owner of MySQL.
  • HeidiSQL (Recommended Windows) is a free, open-source app for Windows. For macOS and Linux users, Wine is first required as a prerequisite.
  • phpMyAdmin is a very popular alternative that operates in the web browser.
  • Sequel Pro (Recommended macOS) is a macOS’ only alternative and our favorite thanks to its clear and easy to use interface.

When you’re ready to start writing your own SQL queries, rather than spending time creating your own database, consider importing dummy data instead.

The MySQL website provides a number of dummy databases that you can download free of charge and then import into your SQL app.

Dummy Database MySQL

Our favorite of these is the world database, which provides some interesting data to practice writing SQL queries for. Here’s a screenshot of its country table within Sequel Pro.

Sequel Pro Example

This example query returns all countries with Queen Elizabeth II as their head of state.

Sequel Pro Example

Whilst this one returns all European countries with a population of over 50million along with their capital city and its population.

Sequel Pro Example

And this final one returns the average percentage of French speakers in countries where the total number of French speakers is higher than 10%.

Sequel Pro Example

Cheat Sheet

Keywords

A collection of keywords used in SQL statements, a description, and where appropriate an example. Some of the more advanced keywords have their own dedicated section later in the cheat sheet.

Where MySQL is mentioned next to an example, this means this example is only applicable to MySQL databases (as opposed to any other database system).

SQL Keywords
KeywordDescription
ADDAdds a new column to an existing table.

Example: Adds a new column named ‘email_address’ to a table named ‘users’.

ALTER TABLE users
ADD email_address varchar(255);
ADD CONSTRAINTIt creates a new constraint on an existing table, which is used to specify rules for any data in the table.

Example: Adds a new PRIMARY KEY constraint named ‘user’ on columns ID and SURNAME.

ALTER TABLE users
ADD CONSTRAINT user PRIMARY KEY (ID, SURNAME);
ALTER TABLEAdds, deletes or edits columns in a table. It can also be used to add and delete constraints in a table, as per the above.

Example: Adds a new boolean column called ‘approved’ to a table named ‘deals’.

ALTER TABLE deals
ADD approved boolean;

Example 2: Deletes the ‘approved’ column from the ‘deals’ table

ALTER TABLE deals
DROP COLUMN approved;
ALTER COLUMNChanges the data type of a table’s column.

Example: In the ‘users’ table, make the column ‘incept_date’ into a ‘datetime’ type.

ALTER TABLE users
ALTER COLUMN incept_date datetime;
ALLReturns true if all of the subquery values meet the passed condition.

Example: Returns the users with a higher number of tasks than the user with the highest number of tasks in the HR department (id 2)

SELECT first_name, surname, tasks_no
FROM users
WHERE tasks_no > ALL (SELECT tasks FROM user WHERE department_id = 2);
ANDUsed to join separate conditions within a WHERE clause.

Example: Returns events located in London, United Kingdom

SELECT * FROM events
WHERE host_country='United Kingdom' AND host_city='London';
ANYReturns true if any of the subquery values meet the given condition.

Example: Returns products from the products table which have received orders – stored in the orders table – with a quantity of more than 5.

SELECT name
FROM products
WHERE productId = ANY (SELECT productId FROM orders WHERE quantity > 5);
ASRenames a table or column with an alias value which only exists for the duration of the query.

Example: Aliases north_east_user_subscriptions column

SELECT north_east_user_subscriptions AS ne_subs
FROM users
WHERE ne_subs > 5;
ASCUsed with ORDER BY to return the data in ascending order.

Example: Apples, Bananas, Peaches, Raddish

BETWEENSelects values within the given range.

Example 1: Selects stock with a quantity between 100 and 150.

SELECT * FROM stock
WHERE quantity BETWEEN 100 AND 150;

Example 2: Selects stock with a quantity NOT between 100 and 150. Alternatively, using the NOT keyword here reverses the logic and selects values outside the given range.

SELECT * FROM stock
WHERE quantity NOT BETWEEN 100 AND 150;
CASEChange query output depending on conditions.

Example: Returns users and their subscriptions, along with a new column called activity_levels that makes a judgement based on the number of subscriptions.

SELECT first_name, surname, subscriptions
CASE WHEN subscriptions > 10 THEN 'Very active'
WHEN Quantity BETWEEN 3 AND 10 THEN 'Active'
ELSE 'Inactive'
END AS activity_levels
FROM users;
CHECKAdds a constraint that limits the value which can be added to a column.

Example 1 (MySQL): Makes sure any users added to the users table are 18 or over.

CREATE TABLE users (
  first_name varchar(255),
  age int,
  CHECK (age>=18)
);

Example 2 (MySQL): Adds a check after the table has already been created.

ALTER TABLE users
ADD CHECK (age>=18);
CREATE DATABASECreates a new database.

Example: Creates a new database named ‘websitesetup’.

CREATE DATABASE websitesetup;
CREATE TABLECreates a new table .

Example: Creates a new table called ‘users’ in the ‘websitesetup’ database.

CREATE TABLE users (
    id int,
    first_name varchar(255),
    surname varchar(255),
    address varchar(255),
    contact_number int
);
DEFAULTSets a default value for a column;

Example 1 (MySQL): Creates a new table called Products which has a name column with a default value of ‘Placeholder Name’ and an available_from column with a default value of today’s date.

CREATE TABLE products (
    id int,
    name varchar(255) DEFAULT 'Placeholder Name',
    available_from date DEFAULT GETDATE()
);

Example 2 (MySQL): The same as above, but editing an existing table.

ALTER TABLE products
ALTER name SET DEFAULT 'Placeholder Name',
ALTER available_from SET DEFAULT GETDATE();
DELETEDelete data from a table.

Example: Removes a user with a user_id of 674.

DELETE FROM users WHERE user_id = 674;
DESCUsed with ORDER BY to return the data in descending order.

Example: Raddish, Peaches, Bananas, Apples

DROP COLUMNDeletes a column from a table.

Example: Removes the first_name column from the users table.

ALTER TABLE users
DROP COLUMN first_name
DROP DATABASEDeletes the entire database.

Example: Deletes a database named ‘websitesetup’.

DROP DATABASE websitesetup;
DROP DEFAULTRemoves a default value for a column.

Example (MySQL): Removes the default value from the ‘name’ column in the ‘products’ table.

ALTER TABLE products
ALTER COLUMN name DROP DEFAULT;
DROP TABLEDeletes a table from a database.

Example: Removes the users table.

DROP TABLE users;
EXISTSChecks for the existence of any record within the subquery, returning true if one or more records are returned.

Example: Lists any dealerships with a deal finance percentage less than 10.

SELECT dealership_name
FROM dealerships
WHERE EXISTS (SELECT deal_name FROM deals WHERE dealership_id = deals.dealership_id AND finance_percentage < 10);
FROMSpecifies which table to select or delete data from.

Example: Selects data from the users table.

SELECT area_manager
FROM area_managers
WHERE EXISTS (SELECT ProductName FROM Products WHERE area_manager_id = deals.area_manager_id AND Price < 20);
INUsed alongside a WHERE clause as a shorthand for multiple OR conditions.

So instead of:-

SELECT * FROM users
WHERE country = 'USA' OR country = 'United Kingdom' OR country = 'Russia' OR country = 'Australia';

You can use:-

SELECT * FROM users
WHERE country IN ('USA', 'United Kingdom', 'Russia', 'Australia');
INSERT INTOAdd new rows to a table.

Example: Adds a new vehicle.

INSERT INTO cars (make, model, mileage, year)
VALUES ('Audi', 'A3', 30000, 2016);
IS NULLTests for empty (NULL) values.

Example: Returns users that haven’t given a contact number.

SELECT * FROM users
WHERE contact_number IS NULL;
IS NOT NULLThe reverse of NULL. Tests for values that aren’t empty / NULL.
LIKEReturns true if the operand value matches a pattern.

Example: Returns true if the user’s first_name ends with ‘son’.

SELECT * FROM users
WHERE first_name LIKE '%son';
NOTReturns true if a record DOESN’T meet the condition.

Example: Returns true if the user’s first_name doesn’t end with ‘son’.

SELECT * FROM users
WHERE first_name NOT LIKE '%son';
ORUsed alongside WHERE to include data when either condition is true.

Example: Returns users that live in either Sheffield or Manchester.

SELECT * FROM users
WHERE city = 'Sheffield' OR 'Manchester';
ORDER BYUsed to sort the result data in ascending (default) or descending order through the use of ASC or DESC keywords.

Example: Returns countries in alphabetical order.

SELECT * FROM countries
ORDER BY name;
ROWNUMReturns results where the row number meets the passed condition.

Example: Returns the top 10 countries from the countries table.

SELECT * FROM countries
WHERE ROWNUM <= 10;
SELECTUsed to select data from a database, which is then returned in a results set.

Example  1: Selects all columns from all users.

SELECT * FROM users;

Example  2: Selects the first_name and surname columns from all users.xx

SELECT first_name, surname FROM users;
SELECT DISTINCTSames as SELECT, except duplicate values are excluded.

Example: Creates a backup table using data from the users table.

SELECT * INTO usersBackup2020
FROM users;
SELECT INTOCopies data from one table and inserts it into another.

Example: Returns all countries from the users table, removing any duplicate values (which would be highly likely)

SELECT DISTINCT country from users;
SELECT TOPAllows you to return a set number of records to return from a table.

Example: Returns the top 3 cars from the cars table.

SELECT TOP 3 * FROM cars;
SETUsed alongside UPDATE to update existing data in a table.

Example: Updates the value and quantity values for an order with an id of 642 in the orders table.

UPDATE orders
SET value = 19.49, quantity = 2
WHERE id = 642;
SOMEIdentical to ANY.
TOPUsed alongside SELECT to return a set number of records from a table.

Example: Returns the top 5 users from the users table.

SELECT TOP 5 * FROM users;
TRUNCATE TABLESimilar to DROP, but instead of deleting the table and its data, this deletes only the data.

Example: Empties the sessions table, but leaves the table itself intact.

TRUNCATE TABLE sessions;
UNIONCombines the results from 2 or more SELECT statements and returns only distinct values.

Example: Returns the cities from the events and subscribers tables.

SELECT city FROM events
UNION
SELECT city from subscribers;
UNION ALLThe same as UNION, but includes duplicate values.
UNIQUEThis constraint ensures all values in a column are unique.

Example 1 (MySQL): Adds a unique constraint to the id column when creating a new users table.

CREATE TABLE users (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    UNIQUE (id)
);

Example 2 (MySQL): Alters an existing column to add a UNIQUE constraint.

ALTER TABLE users
ADD UNIQUE (id);

 

UPDATEUpdates existing data in a table.

Example: Updates the mileage and serviceDue values for a vehicle with an id of 45 in the cars table.

UPDATE cars
SET mileage = 23500, serviceDue = 0
WHERE id = 45;
VALUESUsed alongside the INSERT INTO keyword to add new values to a table.

Example: Adds a new car to the cars table.

INSERT INTO cars (name, model, year)
VALUES ('Ford', 'Fiesta', 2010);
WHEREFilters results to only include data which meets the given condition.

Example: Returns orders with a quantity of more than 1 item.

SELECT * FROM orders
WHERE quantity > 1;

Comments

Comments allow you to explain sections of your SQL statements, or to comment out code and prevent its execution.

In SQL, there are 2 types of comments, single line and multiline.

Single Line Comments

Single line comments start with –. Any text after these 2 characters to the end of the line will be ignored.

 – My Select query
SELECT * FROM users;

Multiline Comments

Multiline comments start with /* and end with */. They stretch across multiple lines until the closing characters have been found.

/*
This is my select query.
It grabs all rows of data from the users table
*/
SELECT * FROM users;

/*
This is another select query, which I don't want to execute yet

SELECT * FROM tasks;
*/

MySQL Data Types

When creating a new table or editing an existing one, you must specify the type of data that each column accepts.

In the below example, data passed to the id column must be an int, whilst the first_name column has a VARCHAR data type with a maximum of 255 characters.

CREATE TABLE users (
    id int,
    first_name varchar(255)
);

String Data Types

String Data Types
Data TypeDescription
CHAR(size)Fixed length string which can contain letters, numbers and special characters. The size parameter sets the maximum string length, from 0 – 255 with a default of 1.
VARCHAR(size)Variable length string similar to CHAR(), but with a maximum string length range from 0 to 65535.
BINARY(size)Similar to CHAR() but stores binary byte strings.
VARBINARY(size)Similar to VARCHAR() but for binary byte strings.
TINYBLOBHolds Binary Large Objects (BLOBs) with a max length of 255 bytes.
TINYTEXTHolds a string with a maximum length of 255 characters. Use VARCHAR() instead, as it’s fetched much faster.
TEXT(size)Holds a string with a maximum length of 65535 bytes. Again, better to use VARCHAR().
BLOB(size)Holds Binary Large Objects (BLOBs) with a max length of 65535 bytes.
MEDIUMTEXTHolds a string with a maximum length of 16,777,215 characters.
MEDIUMBLOBHolds Binary Large Objects (BLOBs) with a max length of 16,777,215 bytes.
LONGTEXTHolds a string with a maximum length of 4,294,967,295 characters.
LONGBLOBHolds Binary Large Objects (BLOBs) with a max length of 4,294,967,295 bytes.
ENUM(a, b, c, etc…)A string object that only has one value, which is chosen from a list of values which you define, up to a maximum of 65535 values. If a value is added which isn’t on this list, it’s replaced with a blank value instead. Think of ENUM being similar to HTML radio boxes in this regard.
CREATE TABLE tshirts (color ENUM(‘red’, ‘green’, ‘blue’, ‘yellow’, ‘purple’));
SET(a, b, c, etc…)A string object that can have 0 or more values, which is chosen from a list of values which you define, up to a maximum of 64 values. Think of SET being similar to HTML checkboxes in this regard.

Numeric Data Types

String Data Types
Data TypeDescription
BIT(size)A bit-value type with a default of 1. The allowed number of bits in a value is set via the size parameter, which can hold values from 1 to 64.
TINYINT(size)A very small integer with a signed range of -128 to 127, and an unsigned range of 0 to 255. Here, the size parameter specifies the maximum allowed display width, which is 255.
BOOLEssentially a quick way of setting the column to TINYINT with a size of 1. 0 is considered false, whilst 1 is considered true.
BOOLEANSame as BOOL.
SMALLINT(size)A small integer with a signed range of -32768 to 32767, and an unsigned range from 0 to 65535. Here, the size parameter specifies the maximum allowed display width, which is 255.
MEDIUMINT(size)A medium integer with a signed range of -8388608 to 8388607, and an unsigned range from 0 to 16777215. Here, the size parameter specifies the maximum allowed display width, which is 255.
INT(size)A medium integer with a signed range of -2147483648 to 2147483647, and an unsigned range from 0 to 4294967295. Here, the size parameter specifies the maximum allowed display width, which is 255.
INTEGER(size)Same as INT.
BIGINT(size)A medium integer with a signed range of -9223372036854775808 to 9223372036854775807, and an unsigned range from 0 to 18446744073709551615. Here, the size parameter specifies the maximum allowed display width, which is 255.
FLOAT(p)A floating point number value. If the precision (p) parameter is between 0 to 24, then the data type is set to FLOAT(), whilst if its from 25 to 53, the data type is set to DOUBLE(). This behaviour is to make the storage of values more efficient.
DOUBLE(size, d)A floating point number value where the total digits are set by the size parameter, and the number of digits after the decimal point is set by the d parameter.
DECIMAL(size, d)An exact fixed point number where the total number of digits is set by the size parameters, and the total number of digits after the decimal point is set by the d parameter.

For size, the maximum number is 65 and the default is 10, whilst for d, the maximum number is 30 and the default is 10.

DEC(size, d)Same as DECIMAL.

Date / Time Data Types

Date / Time Data Types
Data TypeDescription
DATEA simple date in YYYY-MM–DD format, with a supported range from ‘1000-01-01’ to ‘9999-12-31’.
DATETIME(fsp)A date time in YYYY-MM-DD hh:mm:ss format, with a supported range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

By adding DEFAULT and ON UPDATE to the column definition, it automatically sets to the current date/time.

TIMESTAMP(fsp)A Unix Timestamp, which is a value relative to the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). This has a supported range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.

By adding DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT TIMESTAMP to the column definition, it automatically sets to current date/time.

TIME(fsp)A time in hh:mm:ss format, with a supported range from ‘-838:59:59’ to ‘838:59:59’.
YEARA year, with a supported range of ‘1901’ to ‘2155’.

Operators

Arithmetic Operators

Arithmetic Operators
OperatorDescription
+Add
Subtract
*Multiply
/Divide
%Modulo

Bitwise Operator

Bitwise Operators
OperatorDescription
&Bitwise AND
|Bitwise OR
^Bitwise exclusive OR

Comparison Operators

Comparison Operators
OperatorDescription
=Equal to
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
<>Not equal to

Compound Operators

Compound Operators
OperatorDescription
+=Add equals
-=Subtract equals
*=Multiply equals
/=Divide equals
%=Modulo equals
&=Bitwise AND equals
^-=Bitwise exclusive equals
|*=Bitwise OR equals

Functions

String Functions

String Functions
NameDescription
ASCIIReturns the equivalent ASCII value for a specific character.
CHAR_LENGTHReturns the character length of a string.
CHARACTER_LENGTHSame as CHAR_LENGTH.
CONCATAdds expressions together, with a minimum of 2.
CONCAT_WSAdds expressions together, but with a separator between each value.
FIELDReturns an index value relative to the position of a value within a list of values.
FIND IN SETReturns the position of a string in a list of strings.
FORMATWhen passed a number, returns that number formatted to include commas (eg 3,400,000).
INSERTAllows you to insert one string into another at a certain point, for a certain number of characters.
INSTRReturns the position of the first time one string appears within another.
LCASEConvert a string to lowercase.
LEFTStarting from the left, extract the given number of characters from a string and return them as another.
LENGTHReturns the length of a string, but in bytes.
LOCATEReturns the first occurrence of one string within another,
LOWERSame as LCASE.
LPADLeft pads one string with another, to a specific length.
LTRIMRemove any leading spaces from the given string.
MIDExtracts one string from another, starting from any position.
POSITIONReturns the position of the first time one substring appears within another.
REPEATAllows you to repeat a string
REPLACEAllows you to replace any instances of a substring within a string, with a new substring.
REVERSEReverses the string.
RIGHTStarting from the right, extract the given number of characters from a string and return them as another.
RPADRight pads one string with another, to a specific length.
RTRIMRemoves any trailing spaces from the given string.
SPACEReturns a string full of spaces equal to the amount you pass it.
STRCMPCompares 2 strings for differences
SUBSTRExtracts one substring from another, starting from any position.
SUBSTRINGSame as SUBSTR
SUBSTRING_INDEXReturns a substring from a string before the passed substring is found the number of times equals to the passed number.
TRIMRemoves trailing and leading spaces from the given string. Same as if you were to run LTRIM and RTRIM together.
UCASEConvert a string to uppercase.
UPPERSame as UCASE.

Numeric Functions

Numeric Functions
NameDescription
ABSReturns the absolute value of the given number.
ACOSReturns the arc cosine of the given number.
ASINReturns the arc sine of the given number.
ATANReturns the arc tangent of one or 2 given numbers.
ATAN2Return the arc tangent of 2 given numbers.
AVGReturns the average value of the given expression.
CEILReturns the closest whole number (integer) upwards from a given decimal point number.
CEILINGSame as CEIL.
COSReturns the cosine of a given number.
COTReturns the cotangent of a given number.
COUNTReturns the amount of records that are returned by a SELECT query.
DEGREESConverts a radians value to degrees.
DIVAllows you to divide integers.
EXPReturns e to the power of the given number.
FLOORReturns the closest whole number (integer) downwards from a given decimal point number.
GREATESTReturns the highest value in a list of arguments.
LEASTReturns the smallest value in a list of arguments.
LNReturns the natural logarithm of the given number
LOGReturns the natural logarithm of the given number, or the logarithm of the given number to the given base
LOG10Does the same as LOG, but to base 10.
LOG2Does the same as LOG, but to base 2.
MAXReturns the highest value from a set of values.
MINReturns the lowest value from a set of values.
MODReturns the remainder of the given number divided by the other given number.
PIReturns PI.
POWReturns the value of the given number raised to the power of the other given number.
POWERSame as POW.
RADIANSConverts a degrees value to radians.
RANDReturns a random number.
ROUNDRound the given number to the given amount of decimal places.
SIGNReturns the sign of the given number.
SINReturns the sine of the given number.
SQRTReturns the square root of the given number.
SUMReturns the value of the given set of values combined.
TANReturns the tangent of the given number.
TRUNCATEReturns a number truncated to the given number of decimal places.

Date Functions

Date Functions
NameDescription
ADDDATEAdd a date interval (eg: 10 DAY) to a date (eg: 20/01/20) and return the result (eg: 20/01/30).
ADDTIMEAdd a time interval (eg: 02:00) to a time or datetime (05:00) and return the result (07:00).
CURDATEGet the current date.
CURRENT_DATESame as CURDATE.
CURRENT_TIMEGet the current time.
CURRENT_TIMESTAMPGet the current date and time.
CURTIMESame as CURRENT_TIME.
DATEExtracts the date from a datetime expression.
DATEDIFFReturns the number of days between the 2 given dates.
DATE_ADDSame as ADDDATE.
DATE_FORMATFormats the date to the given pattern.
DATE_SUBSubtract a date interval (eg: 10 DAY) to a date (eg: 20/01/20) and return the result (eg: 20/01/10).
DAYReturns the day for the given date.
DAYNAMEReturns the weekday name for the given date.
DAYOFWEEKReturns the index for the weekday for the given date.
DAYOFYEARReturns the day of the year for the given date.
EXTRACTExtract from the date the given part (eg MONTH for 20/01/20 = 01).
FROM DAYSReturn the date from the given numeric date value.
HOURReturn the hour from the given date.
LAST DAYGet the last day of the month for the given date.
LOCALTIMEGets the current local date and time.
LOCALTIMESTAMPSame as LOCALTIME.
MAKEDATECreates a date and returns it, based on the given year and number of days values.
MAKETIMECreates a time and returns it, based on the given hour, minute and second values.
MICROSECONDReturns the microsecond of a given time or datetime.
MINUTEReturns the minute of the given time or datetime.
MONTHReturns the month of the given date.
MONTHNAMEReturns the name of the month of the given date.
NOWSame as LOCALTIME.
PERIOD_ADDAdds the given number of months to the given period.
PERIOD_DIFFReturns the difference between 2 given periods.
QUARTERReturns the year quarter for the given date.
SECONDReturns the second of a given time or datetime.
SEC_TO_TIMEReturns a time based on the given seconds.
STR_TO_DATECreates a date and returns it based on the given string and format.
SUBDATESame as DATE_SUB.
SUBTIMESubtracts a time interval (eg: 02:00) to a time or datetime (05:00) and return the result (03:00).
SYSDATESame as LOCALTIME.
TIMEReturns the time from a given time or datetime.
TIME_FORMATReturns the given time in the given format.
TIME_TO_SECConverts and returns a time into seconds.
TIMEDIFFReturns the difference between 2 given time/datetime expressions.
TIMESTAMPReturns the datetime value of the given date or datetime.
TO_DAYSReturns the total number of days that have passed from ‘00-00-0000’ to the given date.
WEEKReturns the week number for the given date.
WEEKDAYReturns the weekday number for the given date.
WEEKOFYEARReturns the week number for the given date.
YEARReturns the year from the given date.
YEARWEEKReturns the year and week number for the given date.

Misc Functions

Misc Functions
NameDescription
BINReturns the given number in binary.
BINARYReturns the given value as a binary string.
CASTConvert one type into another.
COALESCEFrom a list of values, return the first non-null value.
CONNECTION_IDFor the current connection, return the unique connection ID.
CONVConvert the given number from one numeric base system into another.
CONVERTConvert the given value into the given datatype or character set.
CURRENT_USERReturn the user and hostname which was used to authenticate with the server.
DATABASEGet the name of the current database.
GROUP BYUsed alongside aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the results.

Example: Lists the number of users with active orders.

SELECT COUNT(user_id), active_orders
FROM users
GROUP BY active_orders;
HAVINGIt’s used in the place of WHERE with aggregate functions.

Example: Lists the number of users with active orders, but only include users with more than 3 active orders.

SELECT COUNT(user_id), active_orders
FROM users
GROUP BY active_orders
HAVING COUNT(user_id) > 3;
IFIf the condition is true return a value, otherwise return another value.
IFNULLIf the given expression equates to null, return the given value.
ISNULLIf the expression is null, return 1, otherwise return 0.
LAST_INSERT_IDFor the last row which was added or updated in a table, return the auto increment ID.
NULLIFCompares the 2 given expressions. If they are equal, NULL is returned, otherwise the first expression is returned.
SESSION_USERReturn the current user and hostnames.
SYSTEM_USERSame as SESSION_USER.
USERSame as SESSION_USER.
VERSIONReturns the current version of the MySQL powering the database.

Wildcard Characters

In SQL, Wildcards are special characters used with the LIKE and NOT LIKE keywords which allow us to search data with sophisticated patterns much more efficiently

Wildcards
NameDescription
%Equates to zero or more characters.

Example 1: Find all users with surnames ending in ‘son’.

SELECT * FROM users
WHERE surname LIKE '%son';

Example 2: Find all users living in cities containing the pattern ‘che’

SELECT * FROM users
WHERE city LIKE '%che%';
_Equates to any single character.

Example: Find all users living in cities beginning with any 3 characters, followed by ‘chester’.

SELECT * FROM users
WHERE city LIKE '___chester';
[charlist]Equates to any single character in the list.

Example 1: Find all users with first names beginning with J, H or M.

SELECT * FROM users
WHERE first_name LIKE '[jhm]%';

Example 2: Find all users with first names beginning letters between A – L.

SELECT * FROM users
WHERE first_name LIKE '[a-l]%';

Example 3: Find all users with first names not ending with letters between n – s.

SELECT * FROM users
WHERE first_name LIKE '%[!n-s]';

Keys

In relational databases, there is a concept of primary and foreign keys. In SQL tables, these are included as constraints, where a table can have a primary key, a foreign key, or both.

Primary Key

A primary key allows each record in a table to be uniquely identified. There can only be one primary key per table, and you can assign this constraint to any single or combination of columns. However, this means each value within this column(s) must be unique.

Typically in a table, the primary key is an ID column, and is usually paired with the AUTO_INCREMENT keyword. This means the value increases automatically as new records are created.

Example 1 (MySQL)

Create a new table and set the primary key to the ID column.

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT,
    first_name varchar(255),
    last_name varchar(255) NOT NULL,
    address varchar(255),
    email varchar(255),
    PRIMARY KEY (id)
);

Example 2 (MySQL)

Alter an existing table and set the primary key to the first_name column.

ALTER TABLE users
ADD PRIMARY KEY (first_name);

Foreign Key

A foreign key can be applied to one column or many and is used to link 2 tables together in a relational database.

As seen in the diagram below, the table containing the foreign key is called the child key, whilst the table which contains the referenced key, or candidate key, is called the parent table.

MySQL Keys

This essentially means that the column data is shared between 2 tables, as a foreign key also prevents invalid data from being inserted which isn’t also present in the parent table.

Example 1 (MySQL)

Create a new table and turn any columns that reference IDs in other tables into foreign keys.

CREATE TABLE orders (
    id int NOT NULL,
    user_id int,
    product_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Example 2 (MySQL)

Alter an existing table and create a foreign key.

ALTER TABLE orders
ADD FOREIGN KEY (user_id) REFERENCES users(id);

Indexes

Indexes are attributes that can be assigned to columns that are frequently searched against to make data retrieval a quicker and more efficient process.

This doesn’t mean each column should be made into an index though, as it takes longer for a column with an index to be updated than a column without. This is because when indexed columns are updated, the index itself must also be updated.

Indexes
NameDescription
CREATE INDEXCreates an index named ‘idx_test’ on the first_name and surname columns of the users table. In this instance, duplicate values are allowed.
CREATE INDEX idx_test
ON users (first_name, surname);
CREATE UNIQUE INDEXThe same as the above, but no duplicate values.
CREATE UNIQUE INDEX idx_test
ON users (first_name, surname);
DROP INDEXRemoves an index.
ALTER TABLE users
DROP INDEX idx_test;

Joins

In SQL, a JOIN clause is used to return a results set which combines data from multiple tables, based on a common column which is featured in both of them

There are a number of different joins available for you to use:-

  • Inner Join (Default): Returns any records which have matching values in both tables.
  • Left Join: Returns all of the records from the first table, along with any matching records from the second table.
  • Right Join: Returns all of the records from the second table, along with any matching records from the first.
  • Full Join: Returns all records from both tables when there is a match.

A common way of visualising how joins work is like this:

MySQL Joins

In the following example, an inner join will be used to create a new unifying view combining the orders table and then 3 different tables

We’ll replace the user_id and product_id with the first_name and surname columns of the user who placed the order, along with the name of the item which was purchased.

MySQL Example Table

SELECT orders.id, users.first_name, users.surname, products.name as 'product name'
FROM orders
INNER JOIN users on orders.user_id = users.id
INNER JOIN products on orders.product_id = products.id;

Would return a results set which looks like:

MySQL Example Table

View

A view is essentially a SQL results set that get stored in the database under a label, so you can return to it later, without having to rerun the query. These are especially useful when you have a costly SQL query which may be needed a number of times, so instead of running it over and over to generate the same results set, you can just do it once and save it as a view.

Creating Views

To create a view, you can do so like this:

CREATE VIEW priority_users AS
SELECT * FROM users
WHERE country = 'United Kingdom';

Then in future, if you need to access the stored result set, you can do so like this:

SELECT * FROM [priority_users];

Replacing Views

With the CREATE OR REPLACE command, a view can be updated.

CREATE OR REPLACE VIEW [priority_users] AS
SELECT * FROM users
WHERE country = 'United Kingdom' OR country='USA';

Deleting Views

To delete a view, simply use the DROP VIEW command.

DROP VIEW priority_users;

Conclusion

The majority of the websites on today’s web use relational databases in some way. This makes SQL a valuable language to know, as it allows you to create more complex, functional websites, and systems.

Make sure to bookmark this page, so in the future, if you’re working with SQL and can’t quite remember a specific operator, how to write a certain query, or are just confused about how joins work, then you’ll have a cheat sheet on hand which is ready, willing and able to help.