SQL基础--没事儿补补基础，相信会有新的收获物

SQL

• OFFSET-FETCH 组合轻松实现分页

	SELECT column-names
  	FROM table-name
 	ORDER BY column-names
	OFFSET n ROWS
 	FETCH NEXT m ROWS ONLY

This will return only record (n + 1) to (n + 1 + m).

* AND OR NOT

SELECT Id, OrderDate, CustomerId, TotalAmount
FROM [Order]
WHERE NOT (TotalAmount >= 50 AND TotalAmount <= 15000)
ORDER BY TotalAmount DESC

• BETWEEN AND

	
	SELECT COUNT(Id), SUM(TotalAmount)
 	FROM [Order]
 	WHERE OrderDate BETWEEN '1/1/2013' AND '1/31/2013'

`1 WHERE BETWEEN returns values that fall within a given range.`
`2 WHERE BETWEEN is a shorthand for >= AND <=.`
`3 BETWEEN operator is inclusive: begin and end values are included.`

* LIKE

Problem: List all products that start with 'Cha' or 'Chan' and have one more character.

Problem: List all products that start with 'Cha' or 'Chan' and have one more character.
SELECT Id, ProductName, UnitPrice, Package
FROM Product
WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'

• IS NULL

  • NULL is a special value that signifies 'no value'.
  • Comparing a column to NULL using the = operator is undefined.
  • Instead, use WHERE IS NULL or WHERE IS NOT NULL.

• GROUP BY

  • The GROUP BY clause groups records into summary rows.
  • GROUP BY returns one records for each group.
  • GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc.
  • GROUP BY can group by one or more columns.

	SELECT SUM(O.TotalPrice), C.FirstName, C.LastName
  	FROM [Order] O JOIN Customer C 
    ON O.CustomerId = C.Id
 	GROUP BY C.FirstName, C.LastName
 	ORDER BY SUM(O.TotalPrice) DESC

Problem: List the total amount ordered for each customer 

* JOIN

	* (INNER) JOIN: Select records that have matching values in both tables.
	* LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
	* RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
	* FULL (OUTER) JOIN: Selects all records that match either left or right table records.

* SELF JOIN 可以看成是多表查询最普通的一种。
	* Problem: Match customers that are from the same city and country 

SELECT B.FirstName AS FirstName1, B.LastName AS LastName1, 
   A.FirstName AS FirstName2, A.LastName AS LastName2, 
   B.City, B.Country
FROM Customer A, Customer B
WHERE A.Id <> B.Id
AND A.City = B.City 
AND A.Country = B.Country
ORDER BY A.Country

• ANY ALL

Problem: Which products were sold by the unit (i.e. quantity = 1)

	SELECT ProductName
  	FROM Product
 	WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         WHERE Quantity = 1)
> 对于any只要满足任何一个即可,比如>any()即大于里面任意一个即可不，不必大于每一个，而all则需要集里面的都满足，如>all()大于里面所有的，这两个从字面可窥其意思。
Problem: List customers who placed orders that are 
larger than the average of each customer order

SELECT DISTINCT FirstName + ' ' + LastName as CustomerName
FROM Customer, [Order]
WHERE Customer.Id = [Order].CustomerId
AND TotalAmount > ALL 
   (SELECT AVG(TotalAmount)
      FROM [Order]
     GROUP BY CustomerId)

• EXISTS

  • IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
  • 返回结果等同于=any() 与 in()。

• SELECT INTO

  • copy 数据到新表中

	SELECT * INTO SupplierUSA
  	FROM Supplier
 	WHERE Country = 'USA'
> www.dofactory.com