SQL Syntax 入门（二）

本文是SQL Syntax入门系列第二篇。本文将介绍 SELECT TOP, FUNCTIONS, JOIN, GROUP BY等语法格式。
特别提醒： SQL 是 Case Insensitive的，也就是说使用大写语法和使用小写语法对于执行没有任何影响。但是为了代码的可读性和理解、维护，我们一般将默认语法大写，数据库中的 Column名首字母大写。

Syntax

SELECT TOP

当数据库过大时，如果一次性调用所有数据，会导致 performance降低。因此，我们可以仅仅调用前几项数据，来满足需求。

-- 语法格式
-- SQL Server / MS Access
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
-- MySQL
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
-- Oracle Syntax
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
-- 例子
-- 选取最上面三行记录
SELECT TOP 3 * FROM Customers;
-- 等价于
SELECT * FROM Customers
LIMIT 3;
-- 等价于 
SELECT * FROM Customers
WHERE ROWNUM <= 3;
-- 使用 percent的例子
SELECT TOP 50 PERCENT * FROM Customers;
-- 当然，可以加上某些限制条件：
-- 选取 Country为 'Germany'的首三行记录
SELECT TOP 3 * FROM Customers
WHERE Country='Germany';

FUNCTIONS: MIN, MAX, COUNT, AVG, SUM

-- MIN/MAX 语法格式
SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;
-- 例子
SELECT MIN(Price) AS SmallestPrice
FROM Products;

-- 语法格式
-- COUNT
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
-- AVG
SELECT AVG(column_name)
FROM table_name
WHERE condition;
-- SUM
SELECT SUM(column_name)
FROM table_name
WHERE condition;
-- 实例
-- Find the total number of products
SELECT COUNT(ProductID)
FROM Products;
-- Find the average price
SELECT AVG(Price)
FROM Products;
-- Find the sum of all quantities
SELECT SUM(Quantity)
FROM OrderDetails;

LIKE & WILDCARDS

在使用 WHERE的时候，使用 LIKE作为 Condition往往可以达到事倍功半的效果。（WILDCARDS这个有点像正则表达式）

-- 语法格式
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

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