How to Use SQL The following SQL statement selects all the records in the "Customers" table:

SELECT * FROM Customers;

SELECT column1, column2, ...

FROM table_name;

SELECT * FROM table_name;

Example

SELECT CustomerName, City, Country FROM Customers;

SELECT * Example

SELECT * FROM Customers;

The MySQL SELECT DISTINCT Statement

SELECT DISTINCT column1, column2, ...

FROM table_name;

Example

SELECT Country FROM Customers;

SELECT DISTINCT Examples

The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

SELECT DISTINCT Country FROM Customers;

The MySQL WHERE Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...

FROM table_name

WHERE condition;

WHERE Clause Example

The following SQL statement selects all the customers from "Mexico":

SELECT * FROM Customers

WHERE Country = 'Mexico';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

SELECT * FROM Customers

WHERE CustomerID = 1;

SELECT * FROM Customers

WHERE CustomerID = 1;

The MySQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

1. The AND operator displays a record if all the conditions separated by AND are TRUE.
2. The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...

FROM table_name

WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...

FROM table_name

WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax

SELECT column1, column2, ...

FROM table_name

WHERE NOT condition;

AND Example

SELECT * FROM Customers

WHERE Country = 'Germany' AND City = 'Berlin';

OR Example

SELECT * FROM Customers

WHERE City = 'Berlin' OR City = 'Stuttgart';

NOT Example

SELECT * FROM Customers

WHERE NOT Country = 'Germany';

Combining AND, OR and NOT

Example

SELECT * FROM Customers

WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');

Example

SELECT * FROM Customers

WHERE NOT Country = 'Germany' AND NOT Country = 'USA';

The MySQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ... ASC|DESC;

ORDER BY Example

Example

SELECT * FROM Customers

ORDER BY Country;

ORDER BY DESC Example

Example

SELECT * FROM Customers

ORDER BY Country DESC;

ORDER BY Several Columns Example

Example

SELECT * FROM Customers

ORDER BY Country, CustomerName;

ORDER BY Several Columns Example 2

Example

SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

The MySQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

INSERT INTO Example

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Insert Data Only in Specified Columns

Example

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names

FROM table_name

WHERE column_name IS NULL;

The IS NULL Operator

SELECT CustomerName, ContactName, Address

FROM Customers

WHERE Address IS NULL;

The IS NOT NULL Operator

Example

SELECT CustomerName, ContactName, Address

FROM Customers

WHERE Address IS NOT NULL;

The MySQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

UPDATE Table

Example

UPDATE Customers

SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'

WHERE CustomerID = 1;

UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

Example

UPDATE Customers

SET PostalCode = 00000

WHERE Country = 'Mexico';

Example

UPDATE Customers

SET PostalCode = 00000;

The MySQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

SQL DELETE Example

Example

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Records

DELETE FROM table_name;

Example

DELETE FROM Customers;

The MySQL LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

LIMIT Syntax

SELECT column_name(s)

FROM table_name

WHERE condition

LIMIT number;

MySQL LIMIT Examples

Example

SELECT * FROM Customers

LIMIT 3;

Example

SELECT * FROM Customers

LIMIT 3 OFFSET 3;

ADD a WHERE CLAUSE

Example

SELECT * FROM Customers

WHERE Country='Germany'

LIMIT 3;

ADD an ORDER BY CLAUSE

Example

SELECT * FROM Customers

ORDER BY Country

LIMIT 3;

MySQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax

SELECT MIN(column_name)

FROM table_name

WHERE condition;

MAX() Syntax

SELECT MAX(column_name)

FROM table_name

WHERE condition;

MIN() Example

SELECT MIN(Price) AS SmallestPrice

FROM Products;

MAX() Example

SELECT MAX(Price) AS LargestPrice

FROM Products;

MySQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows that matches a specified criterion.

COUNT() Syntax

SELECT COUNT(column_name)

FROM table_name

WHERE condition;

AVG() Syntax

SELECT AVG(column_name)

FROM table_name

WHERE condition;

The SUM() function returns the total sum of a numeric column.

SUM() Syntax

SELECT SUM(column_name)

FROM table_name

WHERE condition;

COUNT() Example

The following SQL statement finds the number of products:

SELECT COUNT(ProductID)

FROM Products;

AVG() Example

SELECT AVG(Price)

FROM Products;

SUM() Example

SELECT SUM(Quantity)

FROM OrderDetails;

The MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

1. The percent sign (%) represents zero, one, or multiple characters
2. The underscore sign (_) represents one, single character

The percent sign and the underscore can also be used in combinations!

LIKE Syntax

SELECT column1, column2, ...

FROM table_name

WHERE columnN LIKE pattern;

SQL LIKE Examples

The following SQL statement selects all customers with a CustomerName starting with "a":

Example

SELECT * FROM Customers

WHERE CustomerName LIKE 'a%';

The following SQL statement selects all customers with a CustomerName ending with "a":

Example

SELECT * FROM Customers

WHERE CustomerName LIKE '%a';

The following SQL statement selects all customers with a CustomerName that have "or" in any position:

Example

SELECT * FROM Customers

WHERE CustomerName LIKE '%or%';

The following SQL statement selects all customers with a CustomerName that have "r" in the second position:

Example

SELECT * FROM Customers

WHERE CustomerName LIKE '_r%';

The following SQL statement selects all customers with a CustomerName that starts with "a" and are at least 3 characters in length:

Example

SELECT * FROM Customers

WHERE CustomerName LIKE 'a__%';

The following SQL statement selects all customers with a ContactName that starts with "a" and ends with "o":

Example

SELECT * FROM Customers

WHERE ContactName LIKE 'a%o';

Example

SELECT * FROM Customers

WHERE CustomerName NOT LIKE 'a%';

The following SQL statement selects all customers with a CustomerName that does NOT start with "a":

MySQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Using the % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

Example

SELECT * FROM Customers

WHERE City LIKE 'ber%';

The following SQL statement selects all customers with a City containing the pattern "es":

SELECT * FROM Customers

WHERE City LIKE '%es%';

The MySQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, ...);

or:

SELECT column_name(s)

FROM table_name

WHERE column_name IN (SELECT STATEMENT);

IN Operator Examples

The following SQL statement selects all customers that are located in "Germany", "France" or "UK":

Example

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

SELECT * FROM Customers

WHERE Country NOT IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are from the same countries as the suppliers:

Example

SELECT * FROM Customers

WHERE Country IN (SELECT Country FROM Suppliers);

The MySQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

BETWEEN Example

The following SQL statement selects all products with a price between 10 and 20:

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Example

To display the products outside the range of the previous example, use NOT BETWEEN:

SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with IN Example

The following SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

Example

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20

AND CategoryID NOT IN (1,2,3);

BETWEEN Text Values Example

The following SQL statement selects all products with a ProductName between "Carnarvon Tigers" and "Mozzarella di Giovanni":

Example

SELECT * FROM Products

WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'

ORDER BY ProductName;

The following SQL statement selects all products with a ProductName between "Carnarvon Tigers" and "Chef Anton's Cajun Seasoning":

Example

SELECT * FROM Products

WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"

ORDER BY ProductName;

NOT BETWEEN Text Values Example

The following SQL statement selects all products with a ProductName not between "Carnarvon Tigers" and "Mozzarella di Giovanni":

Example

SELECT * FROM Products

WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'

ORDER BY ProductName;

BETWEEN Dates Example

The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':

Example

SELECT * FROM Orders

WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Alias Column Syntax

SELECT column_name AS alias_name

FROM table_name;

Alias Table Syntax

SELECT column_name(s)

FROM table_name AS alias_name;

Alias for Columns Examples

The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

Example

SELECT CustomerID AS ID, CustomerName AS Customer

FROM Customers;

Example

SELECT CustomerName AS Customer, ContactName AS "Contact Person"

FROM Customers;

The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country)

Example

SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address

FROM Customers;

Alias for Tables Example

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):

SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Customers AS c, Orders AS o

WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

The following SQL statement is the same as above, but without aliases:

Example

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName

FROM Customers, Orders

WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

MySQL Joining Tables

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

MySQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

MySQL INNER JOIN Example

The following SQL statement selects all orders with customer information:

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

JOIN Three Tables

The following SQL statement selects all orders with customer and shipper information:

Example

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName

FROM ((Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)

INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

MySQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).

LEFT JOIN Syntax

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

MySQL LEFT JOIN Example

The following SQL statement will select all customers, and any orders they might have:

Example

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerName;

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders

MySQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).

RIGHT JOIN Syntax

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

MySQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they might have placed:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName

FROM Orders

RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

ORDER BY Orders.OrderID;

Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

SQL CROSS JOIN Keyword

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

CROSS JOIN Syntax

SELECT column_name(s)

FROM table1

CROSS JOIN table2;

MySQL CROSS JOIN Example

The following SQL statement selects all customers, and all orders:

Example

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

CROSS JOIN Orders;

Note: The CROSS JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:

Example

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

CROSS JOIN Orders

WHERE Customers.CustomerID=Orders.CustomerID;

MySQL Self Join

A self join is a regular join, but the table is joined with itself.

Self Join Syntax

SELECT column_name(s)

FROM table1 T1, table1 T2

WHERE condition;

MySQL Self Join Example

The following SQL statement matches customers that are from the same city:

Example

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City

FROM Customers A, Customers B

WHERE A.CustomerID <> B.CustomerID

AND A.City = B.City

ORDER BY A.City;

The MySQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

1. Every SELECT statement within UNION must have the same number of columns
2. The columns must also have similar data types
3. The columns in every SELECT statement must also be in the same order

UNION Syntax

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

SQL UNION Example

The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

Example

SELECT City FROM Customers

UNION

SELECT City FROM Suppliers

ORDER BY City;

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL UNION ALL Example

The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

Example

SELECT City FROM Customers

UNION ALL

SELECT City FROM Suppliers

ORDER BY City;

SQL UNION With WHERE

The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:

Example

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

Example

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION ALL

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

Another UNION Example

The following SQL statement lists all customers and suppliers:

Example

SELECT 'Customer' AS Type, ContactName, City, Country

FROM Customers

UNION

SELECT 'Supplier', ContactName, City, Country

FROM Suppliers;

The MySQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

GROUP BY Syntax

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

ORDER BY column_name(s);

MySQL GROUP BY Examples

The following SQL statement lists the number of customers in each country:

Example

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country;

The following SQL statement lists the number of customers in each country, sorted high to low:

Example

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

ORDER BY COUNT(CustomerID) DESC;

GROUP BY With JOIN Example

The following SQL statement lists the number of orders sent by each shipper:

Example

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders

LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID

GROUP BY ShipperName;

The MySQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

CREATE DATABASE databasename;

CREATE DATABASE Example

The following SQL statement creates a database called "testDB":

Example

CREATE DATABASE testDB;

The MySQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.

Syntax

DROP DATABASE databasename;

Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!

DROP DATABASE Example

The following SQL statement drops the existing database "testDB":

Example

DROP DATABASE testDB;

Tip: Make sure you have admin privilege before dropping any database. Once a database is dropped, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

The MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

....

);

MySQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

Example

CREATE TABLE Persons (

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax

CREATE TABLE new_table_name AS

SELECT column1, column2,...

FROM existing_table_name

WHERE ....;

The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table):

Example

CREATE TABLE TestTable AS

SELECT customername, contactname

FROM customers;

The MySQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE table_name;

Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!

MySQL DROP TABLE Example

The following SQL statement drops the existing table "Shippers":

Example

DROP TABLE Shippers;

MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax

TRUNCATE TABLE table_name;

MySQL Data Types (Version 8.0)

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

In MySQL there are three main data types: string, numeric, and date and time.

String Data Types

Numeric Data Types

Date and Time Data Types