MySQL BETWEEN Operator

     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;

Demo Database

Below is a selection from the “ProductSales” table in the sample database:

BETWEEN Example

The following SQL statement selects all products with a price between 5000 and 10000:

Example

SELECT * FROM ProductSales
WHERE ProductPrice BETWEEN 5000 AND 10000;

NOT BETWEEN Example

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

Example

SELECT * FROM ProductSales
WHERE ProductPrice NOT BETWEEN 5000 AND 10000;

BETWEEN with IN Example

The following SQL statement selects all products with a price between 5000 and 10000. In addition; do not show products A,B, or C:

Example

SELECT * FROM ProductSales
WHERE ProductPrice BETWEEN 5000 AND 10000
AND Product NOT IN (A,B,C);

BETWEEN Text Values Example

The following SQL statement selects all products with a Product between “A” and “C”:

Example

SELECT * FROM ProductSales
WHERE Product BETWEEN 'Bill' AND 'Joe'
ORDER BY Product;

NOT BETWEEN Text Values Example

The following SQL statement selects all products with a Product not between “A” and “C”:

Example

SELECT * FROM ProductSales
WHERE Product NOT BETWEEN 'A' AND 'C'
ORDER BY Product;

MySQL IN Operator

                    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);

Demo Database

The table below shows the complete “Employees” table from the database:

IN Operator Examples

The following SQL statement selects all Employees that are located in “London”, “Seattle” or “Kent”:

Example

SELECT * FROM Employees
WHERE City IN ('London', 'Seattle', 'Kent');

The following SQL statement selects all Employees that are NOT located in “London”, “Seattle” or “Kent”:

Example

SELECT * FROM Employees
WHERE City NOT IN ('London','Seattle', 'Kent');

MySQL LIKE Operator

                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:

• The percent sign (%) represents zero, one, or multiple characters
• 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;

Tip: You can also combine any number of conditions using AND or OR operators.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE Operator and their Description

WHERE LastName LIKE ‘a%’ : Finds any values that start with “a”

WHERE LastName LIKE ‘%a’ : Finds any values that end with “a”

WHERE LastName LIKE ‘%or%’ : Finds any values that have “or” in any position

WHERE LastName LIKE ‘_r%’ : Finds any values that have “r” in the second position

WHERE LastName LIKE ‘a_%’ : Finds any values that start with “a” and are at least 2 characters in length

WHERE LastName LIKE ‘a__%’ : Finds any values that start with “a” and are at least 3 characters in length

WHERE LastName LIKE ‘a%o’ : Finds any values that start with “a” and ends with “o”

Demo Database

The table below shows the complete “Employees” table from the sample database:

SQL LIKE Examples

The following SQL statement selects all Employees with a LastName starting with “a”:

Example

SELECT * FROM Employees
WHERE LastName LIKE 'a%';

The following SQL statement selects all Employees with a LastName ending with “a”:

Example

SELECT * FROM Employees
WHERE LastName LIKE '%a';

The following SQL statement selects all Employees with a LastName that have “or” in any position:

Example

SELECT * FROM Employees
WHERE LastName LIKE '%or%';

The following SQL statement selects all Employees with a LastName that have “r” in the second position:

Example

SELECT * FROM Employees
WHERE LastName LIKE '_r%';

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

 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;

The AVG() function returns the average value of a numeric column.

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;

Demo Database

Below is a selection from the “ProductSales” table in the sample database:

COUNT() Example

The following SQL statement finds the number of ProductSales:

SELECT COUNT(Product)
FROM ProductSales;

Note: NULL values are not counted.

AVG() Example

The following SQL statement finds the average price of all products:

Example

SELECT AVG(ProductPrice)
FROM ProductSales;

Note: NULL values are ignored.

SUM() Example

The following SQL statement finds the sum of the “Quantity” fields in the “ProductSales” table:

Example

SELECT SUM(Quantity)
FROM OrderDetails;

Note: NULL values are ignored.

MySQL MIN() and MAX() Functions

 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;

Demo Database

Below is a selection from the “ProductSales” table in the sample database:

MIN() Example

The following SQL statement finds the price of the cheapest product:

Example

SELECT MIN(Price) AS SmallestPrice
FROM ProductSales;

MAX() Example

The following SQL statement finds the price of the most expensive product:

Example

SELECT MAX(Price) AS LargestPrice
FROM ProductSales;

MySQL LIMIT Clause

             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;

Demo Database

Below is a selection from the “Employees” table in the sample database:

MySQL LIMIT Examples

The following SQL statement selects the first three records from the “Employees” table:

Example

SELECT * FROM Employees
LIMIT 3;

ADD a WHERE CLAUSE

The following SQL statement selects the first three records from the “Employeess” table, where the City is “London”:

Example

select * from Employees
where City = 'London'
LIMIT 3;

MySQL DELETE Statement

      MySQL DELETE Statement

The MySQL DELETE Statement

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

DELETE Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

Demo Database

Below is a selection from the “Employees” table in the sample database:

SQL DELETE Example

The following SQL statement deletes the LastName “Anne” from the “Employees” table:

Example

DELETE FROM Employees WHERE LastName='Anne';

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

The following SQL statement deletes all rows in the “Employees” table, without deleting the table:

Example

DELETE FROM Employees;