Intro

SQL = Structured Query Language
SQL lets you access and manipulate databases

Syntax

SQL keywords are NOT case sensitive : select is the same as SELECT.

-- 抓出所有資料
SELECT * FROM table_name;

Some of The Most Important SQL Commands :

Commands	Discription
SELECT	extracts data from a database
UPDATE	updates data in a database
DELETE	deletes data from a database
INSERT INTO	inserts new data into a database
CREATE DATABASE	creates a new database
ALTER DATABASE	modifies a database
CREATE TABLE	creates a new table
ALTER TABLE	modifies a table
DROP TABLE	deletes a table
CREATE INDEX	creates an index (search key)
DROP INDEX	deletes an index

SELECT

-- 抓出相對應column的資料
SELECT column1, column2, ...
FROM table_name

-- 只抓出不同的資料
SELECT DISTINCT column1, column2, ...
FROM table_name

WHERE

The WHERE clause is used to extract only those records that fulfill a specified condition.

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

Operators in The WHERE Clause :

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less then or equal
<>	Not equal. (In some version of SQL this operator may be written as !=)
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

AND, OR and NOT Operators

/*
The WHERE clause can be combined with AND, OR and NOT operators.
*/
SELECT column1, solumn2, ...
FROM table_name
WHERE condition1 AND condition2 OR NOT condition3 ...;

ORDER BY Keyword

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

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC

/*
多重排序，依 **ORDER BY** 順序做更詳細的排序。 
如下：依column1遞增排序完，根據column1相同值再對column2做遞減排序。
*/
SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC;

INSERT INTO

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

-- Method 1, value 存到相對應的 column
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- Method 2, value 存到 table 相對應順序的 column
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

NULL Values

A field with a NULL value is a field with no value.
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.

-- 從 table_name 中取出 column_name 「是空」的資料
SELECT column_name FROM table_name
WHERE column_name IS NULL

-- 從 table_name 中取出 column_name 「不是」空的資料
SELECT column_name FROM table_name
WHERE column_name IS NOT NULL

Update

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

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Caution : 此處的condition很重要，若忽略它，整個table的資料都會被改掉！！
Caution : 此處的condition很重要，若忽略它，整個table的資料都會被改掉！！
Caution : 此處的condition很重要，若忽略它，整個table的資料都會被改掉！！

DELETE

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

DELETE FROM table_name WHERE condition;

Caution : 此處的condition很重要，若忽略它，整個table的資料都會被刪掉！！
Caution : 此處的condition很重要，若忽略它，整個table的資料都會被刪掉！！
Caution : 此處的condition很重要，若忽略它，整個table的資料都會被刪掉！！

-- 相當於如下，
DELETE FROM table_name

TOP, LIMIT or ROWNUM clause

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

Note : Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

-- SQL sServer / MS Access Syntax :
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

--MySQL Syntax :
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

--Oracle Syntax :
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

-- 從 table_name 中抓取前三筆資料的三個範例
SELECT TOP 3 * FROM table_name

SELECT * FROM table_name
LIMIT 3;

SELECT * FROM table_name
WHERE ROWNUM <= 3;

-- 從 table_name 中抓取前 50% 筆資料
SELECT TOP 50 PERCENT * FROM table_name

-- 也可從條件中取出前三筆資料的三個範例
SELECT TOP 3 * FROM table_name
WHERE condition;

SELECT * FROM table_name
WHERE condition
LIMIT 3;

SELECT * FROM table_name
WHERE condition AND ROWNUM <= 3;

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.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

COUNT(), AVG() and SUM() functions

The COUNT() function returns the number of rows that matches a specified criteria.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT SUM(column_name)
FROM table_name
WHERE condition;

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 represents a single character

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

LIKE Operator	Description
WHERE Coulumn_name LIKE ‘a%’	Finds any values that start with “a”
WHERE Coulumn_name LIKE ‘%a’	Finds any values that end with “a”
WHERE Coulumn_name LIKE ‘%or%’	Finds any values that have “or” in any position
WHERE Coulumn_name LIKE ‘_r%’	Finds any values that have “r” in the second position
WHERE Coulumn_name LIKE ‘a_%_%’	Finds any values that start with “a” and are at least 3 characters in length
WHERE Coulumn_name LIKE ‘a%o’	Finds any values that start with “a” and ends with “o”

Wildcards

A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters in MS Access

Symbol	Description	Example
*	Represents zero or more characters	bl* finds bl, black, blue, and blob
?	Represents a single character	h?t finds hot, hat, and hit
[]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not hit
!	Represents any character not in the brackets	h[!oa]t finds hit, but not hot and hat
-	Represents a range of characters	c[a-b]t finds cat and cbt
#	Represents any single numeric character	2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard Characters in SQL Server

Symbol	Description	Example
%	Represents zero or more characters	bl% finds bl, black, blue, and blob
_	Represents a single character	h_t finds hot, hat, and hit
[]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not hit
^	Represents any character not in the brackets	h[^oa]t finds hit, but not hot and hat
-	Represents a range of characters	c[a-b]t finds cat and cbt

IN Operator

The IN operator is a shorthand for multiple OR conditions.

-- Method 1
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

--Method 2
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

-- selects all customers that are from the same countries as the suppliers
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

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.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

-- selects all products with a price BETWEEN 10 and 20
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

-- display the products outside the range of the previous example, use NOT BETWEEN:
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

-- selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1, 2, 3);

-- selects all products with a ProductName BETWEEN Carnarvon Tigers and Mozzarella di Giovanni:
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName

-- selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':
SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Aliases

SQL 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 the query.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name

-- creates two aliases, one for the CustomerID column and one for the CustomerName column:
SELECT CustomerID AS ID, CustomerName AS Customers
FROM Customers;

-- It requires double quotation marks or square brackets if the alias name contains spaces:
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

-- creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
-- SQL
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
-- MySQL
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

-- Alias for Tables
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;

JOIN

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

Different Types of SQL JOINs :

INNER JOIN
inner_join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN
right_join

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL OUTER JOIN
full_outer_join

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Self JOIN

-- matches customers that are from the same city:
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

UNION Operator

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

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

-- The UNION operator selects only distinct values by defaul :
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

-- To allow duplicate values, use UNION ALL :
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;