MySQL SQL

SQL DROP TABLE IF EXISTS

• ★ The DROP TABLE IF EXISTS statement is used to drop or delete a table from a database, if the table exists. If the table does not exist, then the statement responds with a warning.
• ★ Syntax:

				DROP TABLE IF EXISTS table_name;
				
				DROP TABLE IF EXISTS database_name.table_name;
			

SQL SELECT DISTINCT Statement

• ★ The SELECT DISTINCT statement is used to return only distinct (different) values.
• ★ Syntax:

				SELECT DISTINCT column1, column2, ...
				FROM table_name;
			

SELECT DISTINCT Example

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

					SELECT DISTINCT Country FROM Customers;
			

• ★The following SQL statement lists the number of different (distinct) customer countries:

					SELECT COUNT(DISTINCT Country) FROM Customers;
			

• ★ Note: The example above will not work in Firefox!

Demo Database

• ★ Customers: customers.sql
• ★ Products: products.sql

SQL WHERE Clause

• ★ The WHERE clause is used to filter records.
• ★ It is used to extract only those records that fulfill a specified condition.
• ★ The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!
• ★ WHERE Syntax:

			SELECT column1, column2, ...
			FROM table_name
			WHERE condition;
			

WHERE Clause Example

• ★ The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

			SELECT * FROM Customers
			WHERE Country='Mexico';
			

Operators in The WHERE Clause

• ★ The following operators can be used in the WHERE clause:



Operator	Description	Example
=	Equal	SELECT * FROM Products WHERE Price = 18;
>	Greater than	SELECT * FROM Products WHERE Price > 30;
<	Less than	SELECT * FROM Products WHERE Price < 30;
<=	Less than or equal	SELECT * FROM Products W HERE Price <= 30;
<>	Not equal.	SELECT * FROM Products WHERE Price <> 18;
BETWEEN	Between a certain range	SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
LIKE	Search for a pattern	SELECT * FROM Customers WHERE City LIKE 's%';
IN	To specify multiple possible values for a column	SELECT * FROM Customers WHERE City IN ('Paris','London');

SQL AND, OR and NOT Operators

• ★ The WHERE clause can be combined with AND, OR, and NOT operators.
• ★ The AND operator displays a record if all the conditions separated by AND are TRUE.

        SELECT * FROM Customers
        WHERE Country='Germany' AND City='Berlin';
			

• ★ The OR operator displays a record if any of the conditions separated by OR is TRUE.

       SELECT * FROM Customers
       WHERE City='Berlin' OR City='München';
			

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

       SELECT * FROM Customers
       WHERE NOT Country='Spain';
			

SQL 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

• ★ The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

      SELECT * FROM Customers
      ORDER BY Country;
		

ORDER BY DESC Example

• ★ The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

			SELECT * FROM Customers
			ORDER BY Country DESC;
		

ORDER BY Several Columns Example 1

• ★ The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column.
• ★ This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

			SELECT * FROM Customers
			ORDER BY Country, CustomerName;
		

ORDER BY Several Columns Example 2

• ★ The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

			SELECT * FROM Customers
			ORDER BY Country ASC, CustomerName DESC;
		

SQL SELECT LIMIT Clause

• ★ MySQL supports the LIMIT clause to select a limited number of records
• ★ LIMIT Syntax:

				SELECT column_name(s)
				FROM table_name
				WHERE condition
				LIMIT number;
			

• ★ The following SQL statement shows the equivalent example for MySQL:

				SELECT * FROM Customers
				LIMIT 3;
			

SQL MIN() and MAX() Functions

• ★ The MIN() function returns the smallest value of the selected column.
• ★ The following SQL statement finds the price of the cheapest product:

        SELECT MIN(Price) AS SmallestPrice
        FROM Products;
			

• ★ The MAX() function returns the largest value of the selected column.
• ★ The following SQL statement finds the price of the most expensive product:

				SELECT MAX(Price) AS LargestPrice
				FROM Products;
			

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

• ★ The COUNT() function returns the number of rows that matches a specified criterion.
• ★ The following SQL statement finds the number of products: (Note: NULL values are not counted.)

				SELECT COUNT(ProductID)
				FROM Products;
			

• ★ The AVG() function returns the average value of a numeric column.
• ★ The following SQL statement finds the average price of all products:(Note: NULL values are ignored.)

					SELECT AVG(Price)
					FROM Products;
				

• ★ The SUM() function returns the total sum of a numeric column.
• ★ The following SQL statement finds the sum of the "Price" fields in the "Products" table:

        SELECT SUM(Price)
        FROM Products;
			

How to Test for NULL Values?

• ★ We will have to use the IS NULL and IS NOT NULL operators.
• ★ The IS NULL operator is used to test for empty values (NULL values).
• ★ The following SQL lists all customers with a NULL value in the "Address" field:

        SELECT * FROM Customers
        WHERE Address IS NULL;
			

• ★ The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
• ★ The following SQL lists all customers with a value in the "Address" field:

				SELECT * FROM Customers
				WHERE Address IS NOT NULL;
			

Reference

• W3schools MySQL/