轉載自： http://blog.csdn.net/u011001084/article/details/51318434

一、關係型資料庫和SQL

實際上準確的講，SQL是一門語言，而不是一個資料庫。

什麼是SQL呢？簡而言之，SQL就是維護和使用關係型資料庫中的的數據的一種標準的電腦語言。

1.1 SQL語言主要有3個主要的組成部分。

1. DML(Data Manipulation Language)數據操縱語言。這個模塊可以讓我們檢索、修改、增加、刪除資料庫中的數據。
2. DDL(Data Definition Language)數據定義語言。是的我們能夠創建和修改資料庫本身。如：DDL提供ALTER語句，他讓我們可以修改資料庫中表的設計。
3. DCL(Data Control Language)數據控制語言，用於維護資料庫的安全。

在SQL術語中，記錄(record)和欄位(field)實際上就稱為行(row)和列(column)。

1.2 主鍵和外鍵

主鍵之所以有必要：

• 首先使你唯一標識表中單獨的一行。主鍵確保了唯一性。
• 可以很容易的將一個表和另一個表關聯。
• 主鍵一般就會自動預設創建索引，提高了查詢速度。

外鍵就是說A表中的某個欄位，同時是B中的主鍵，那麼這個欄位就是A表中的外鍵。希望A表中的這個外鍵的值必須是B中已經存在的值。

1.3 數據類型

一般來講，有3中重要的數據類型：

1. 數字(Numeric)
2. 字元(Character)
3. 以及日期/時間(Date/Time)

bit是數字型，它只允許兩個值，0和1。

字元類型區別^1：

• 1位元組=8位
• bit就是位，也叫比特位，是電腦表示數據最小的單位。
• byte就是位元組，1byte=8bit，1byte就是1B；
• 一個字元=2位元組；

1.3 空值

空值不等於空格或空白。使用NULL表示空值。

二、簡單增刪改查

2.1 查（列名有空格的情況）

1 SELECT [ last name]
2 FROM Customers

用方括弧將有空格的列名括起來。
PS: MySql中用重音符`（~）按鍵。Oracle用雙引號。

查詢順序,SQL執行順序

1 Select -1>選擇列,-2>distinct,-3>top
2   1>…From 表
3   2>…Where 條件
4   3>…Group by 列
5   4>…Having 篩選條件
6   6>…Order by 列

2.2 增

1 INSERT INTO tablename
2 (columnlist)
3 VALUES
4 (RowValues1)
5 (RowValues2)
6 (repeat any number of times)

2.3 改

1 UPDATE  table
2 SET column1=expression1,column2=expression2(repeat any number of times)
3 WHERE condition

2.4 刪

1 DELETE
2 FROM table
3 WHERE condition

刪除前可以驗證一下：

1 SELECT 
2 COUNT（*）
3 FROM table
4 WHERE condition

如果想要刪除所有的行，可以：

 1 DELETE FROM table 

或者

 1 TRUNCATE TABLE table 

TRUNCATE TABLE優勢在於速度更快，但是不提供記錄事務的結果。
另外一個不同點是，TRUNCATE TABLE重新設置了用於自增型的列的當前值，DELETE不會。

三、別名

關鍵字：AS

3.1 計算欄位

使用計算欄位可以做如下的事情：

• 選擇特定的單詞或者數值
• 對單個或者多個列進行計算
• 把列和直接量組合在一起。

3.2 直接量

這個直接量和表中的數據沒有任何關係，就是為了說明所用，下麵這種類型的表達式就叫做直接量(literal value)。

 1 SELECT '直接量' AS `類型`,firstname,lastname 2 FROM `customers` ; 

如圖，結果中直接量就在一列中了。

3.3 算數運算

例子1：

 1 SELECT num*price AS total 2 FROM orders 

例子2：

1 SELECT  firstname+' '+lastname AS 'fullname'
2 FROM users

在MySql中連接要是用CONCAT函數：

1 SELECT OrderID,FirstName,LastName,
2 CONCAT(FirstName,' ',LastName) AS 'fullname'
3 FROM orders

3.4 別名

1）列的別名

 1 SELECT firstname AS fn 2 FROM customers 

2） 表的別名

 1 SELECT firstname 2 FROM customers AS cu 

說明：

1. 列的別名是為了顯示用的，別名會作為查詢結果的表頭，不能在WHERE中使用列的別名，會出錯！！！
2. 表的別名確實是為了方便操作用的，可以在WHERE中使用列的別名進行！

四、使用函數

函數要有一組圓括弧跟在關鍵字後邊，圓括弧告訴我們，這是一個函數！

4.1 字元函數

LEFT&RIGHT

LEFT(CharacterValue,NumberOfCharacters)
含義：選擇CharacterValue欄位的左邊NumberOfCharacters幾個字元。
ps:RIGHT是右邊幾個字元。

LTRIM&RTRIM

LTRIM(CharacterValue)
可以刪除左邊開始的空格。RTRIM作用類似。

SUBSTRING

SUBSTRING(CharacterValue,StartPositon,NumberOfCharacters)
含義：選擇從開始位置（包括），N個長度的字元。

1 SELECT 
2 SUBSTRING('thewhitegoat',4,5) AS 'The Answer'

返回：white

4.2 日期/時間函數

GETDATE

 1 DATEPART(datepart,DateValue) 

datepart可以是許多不同的值，如下都是有效值：

• year
• quarter
• month
• dayofyear
• day
• week
• weekday
• hour
• minute
• second

DATEDIFF

可以讓我們得到任意兩個日期之間相差的天數（或周數、月數等）。

 1 DATEDIFF(datepart1,startdate1,startdate2) 

PS:MySql中，DATEDIFF函數只允許我們計算兩個日期之間的天數，如果想要得到一個正數，結束的日期通常要作為第一個參數:

 1 DATEDIFF(enddate,startdate) 

Oracle中沒有等價函數

4.3 數值函數

ROUND

允許我們四捨五入。

 1 ROUND(numericvalue,decimalpalaces) 

RAND

用來產生隨機數

 1 RAND([seed]) 

沒有參數時，它會返回0-1之間的一個隨機數。

 1 SELECT RAND() AS 'Random Value' 

可選參數seed有的情況下，每次將返回相同的值。這讓我想起了Python中的Random包。看來很多時候，一些東西是共通的啊。

PI

PI()函數
如果想要對它保留兩位小數，可以通過複合函數進行：

 1 SELECT ROUND(PI(),2) 

將會返回：3.14

4.4 轉換函數

CAST函數

允許我們把數據從一種類型轉換成另一種類型。

 1 CAST(expression AS DateType) 

例子：

1 SELECT 
2 '2009-04-11' AS 'Original Date',
3 CAST('2009--04-11' AS DATETIME) AS 'Converted Date'

ISNULL函數，很有用

可以把NULL值轉換成一個有意義的值。

1 SELECT Description,
2 ISNULL(Color,'Unknown') AS 'Color'
3 FROM Products

五、排序函數

5.1 添加排序

 1 SELECT columnlist 2 FROM tablelist 3 ORDER BY columnlist 

預設是升序，ASC，因此，上面等價於：

 1 SELECT columnlist 2 FROM tablelist 3 ORDER BY columnlist ASC 

5.2 降序

使用DESC關鍵字：

 1 SELECT columnlist 2 FROM tablelist 3 ORDER BY columnlist DESC 

5.3 根據多列

1 SELECT 
2 FirstName,
3 LastName
4 FROM Customers
5 ORDER BY LastName, FirstName

註意：列的順序很重要，首先按照LastName排序，然後按照FirstName排序。

5.4 根據計算欄位

1 SELECT LastName+','+FirstName AS 'Name'
2 FROM Customers
3 ORDER BY Name

因此，從這兒可以知道，列別名不可以用在WHERE中，但可以用在ORDER BY中。
例子

1 SELECT FirstName,LastName
2 FROM Customers
3 ORDER BY LastName+FirstName AS 'Name'

5.5 排序補充內容

當數據升序時，出現順序是如下：

NULL->數字->字元
註意：此時，該列中的數字其實是按照字元來算的，因此，升序時，23也是排在5之前的。

六、基於列的邏輯-CASE

6.1 IF-THEN-ELSE邏輯

包含列和CASE表達式的SELECT語句，大概如下：

1 SELECT 
2 column1,
3 column2,
4 CaseExpression
5 FROM table

6.2 CASE-簡單格式

1 SELECT 
2 CASE ColumnOrExpression
3 WHEN value1 THEN result1
4 WHEN value2 THEN result2
5 (repeat WHEN-THEN any number of times)
6 [ELSE  DefaultResult]
7 END

CASE表達式對於把不好理解的值轉換成有意義的描述是很有用的。

1 SELECT 
2 CASE CategoryCode
3 WHEN 'F' THEN 'Fruit'
4 WHEN 'V' THEN 'Vegetable'
5 ELSE 'other'
6 END AS 'Category',
7 ProductDescription As 'Description'
8 FROM Products

6.3 CASE-查詢格式

1 SELECT 
2 CASE 
3 WHEN condition1 THEN result1
4 WHEN condition2 THEN result2
5 (repeat WHEN-THEN any number of times)
6 [ELSE  DefaultResult]
7 END

這種格式允許在關鍵字WHEN後邊放置較為複雜的條件表達式。

相關問題：

• StackOverFlow-SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported

七、基於行的邏輯

7.1 應用查詢條件

終於派到WHERE出場了，註意寫法順序，再寫一遍：

1 Select -1>選擇列,-2>distinct,-3>top
2   1>…From 表
3   2>…Where 條件
4   3>…Group by 列
5   4>…Having 篩選條件
6   6>…Order by 列

7.2 限制行-TOP

 1 SELECT 2 TOP Number 3 Columnlist 4 FROM table 

7.3 TOP和ORDER BY結合

關鍵字TOP的另一個用途是，把它和ORDER BY子句結合起來，基於特定分類，得到帶有最大值的一定數量的行。

假設你想看到Shakespeare所著的銷量最多的書。

SELECT 
TOP1
Title AS 'Book Title',
CurrentMonthSales AS 'Quantuty Sold'
FROM Books
WHERE Author='Shakespeare'
ORDER BY CurrentMonthSales DESC

ps: 學會利用google搜索，例如，我想要知道oracle中類似top作用的關鍵字是什麼，可以：

八、布爾邏輯

關鍵字：AND/OR/NOT/BETWEEN/IN/IS/NULL

8.1 OR

OR子句意味著，如果確定任意條件為真，那麼就該選中該行。

1 SELECT userid,name,phone
2 FROM users
3 WHERE age<18
4 OR age>60

8.2 使用圓括弧

1 SELECT CustomerName,
2 Sate,
3 QuantityPurchased
4 FROM Orders
5 WHERE State ='IL'
6 OR State='CA'
7 AND QuantityPurchased>8

本來想要的結果是對來自IL或者CA的客戶，同時，只看數量大於8的訂單。但是上面執行的結果不是這樣的，因為，SQL總是會先處理AND操作符！！！然後才會處理OR操作符。所以，上述語句中，先看到AND並執行如下的條件

 1 State= 'CA' 2 AND QuantityPurchased>8 

因此，要用括弧來規定順序：

1 SELECT CustomerName,
2 Sate,
3 QuantityPurchased
4 FROM Orders
5 WHERE (State ='IL'
6 OR State='CA')
7 AND QuantityPurchased>8

8.3 NOT操作符

NOT操作符表示對後邊的內容否定或者取反。

1 SELECT CustomerName,State
2 FROM Orders
3 WHERE NOT (State='IL' OR Sate='NY')

這個其實可以用AND改寫的！！！
NOT操作符在邏輯上不是必須的。

8.4 BETWEEN操作符

1 SELECT CustomerName,
2 Sate,
3 QuantityPurchased
4 FROM Orders
5 WHERE QuantityPurchased BETWEEN 8 AND 10

8.5 IN操作符

假設你想看到IL或者NY的行：

1 SELECT *
2 FROM Orders
3 WHERE State='IL'
4 OR State='CA'

可以改寫成：

1 SELECT *
2 FROM Orders
3 WHERE State IN ('IL','CA')

8.9 布爾邏輯-IS NULL

為了將某欄位NULL值的行或0的行包括進來：

1 SELECT *
2 FROM Products
3 WHERE weight=0
4 OR weight IS NULL

或者

1 SELECT *
2 FROM Products
3 WHERE ISNULL(weight,0)=0

九、模糊匹配

9.1 LIKE和%搭配

%通配符可以表示任意的字元，它可以表示0個，1個，任意多個字元。

9.2 通配符

除了%以外，還有下劃線（_）、方括弧起來的characterlist，以及用方括弧括起來的脫字元號（^）加上characterlist。

• 下劃線表示一個字元
• [characterlist]表示括弧中字元的任意一個
• [^characterlist]表示不能是括弧中字元的任意一個
  例子：

1 SELECT 
2 FirstName,
3 LastName
4 FROM Actors
5 WHERE FirstName LIKE '[CM]ARY'

檢索以C或者M開頭並以ARY結尾的所有行。

9.3 按照讀音匹配

SOUNDEX和DIFFERENCE

十、彙總數據

10.1消除重覆

使用DISTINCT

 1 SELECT DISTINCE name,age 2 FROM users 

如果age不同，即使name相同，那麼這一行就不會被刪除重覆。

10.2 聚合函數

COUNT\SUM\AVG\MIN\MAX，他們提供了對分組數據進行計數、求和、取平均值、取最小值和最大值等方法。

1 SELECT 
2 AVG(Grade) AS 'Average Quiz Score'
3 MIN(Grade) AS 'Minimum Quiz Score'
4 FROM Grades
5 WHERE GradeType='Quiz'

COUNT函數可以有3中不同方式使用它。

1.COUNT函數可以用來返回所有選中行的數目，而不管任何特定列的值。
例如：下麵語句返回GradeType為’HomeWork’的所有行的數目：

1 SELECT 
2 COUNT(*) AS 'Count of Homework Rows'
3 FROM Grades
4 WHERE GradeType='HomeWork'

這種方式，會計數所有行的個數，即使其中有*NULL。

2.第二種方式指定具體的列

1 SELECT 
2 COUNT(Grades) AS 'Count of Homework Rows'
3 FROM Grades
4 WHERE GradeType='HomeWork'

第一種方式返回3，這一種方式返回2，為什麼？？？因為，這種方式要滿足Grades這一列有值，NULL值的行不會計數。

3.使用關鍵字DISTINCT。

1 SELECT 
2 COUNT(DISTINCT FeeType) AS 'Number of Fee Types'
3 FROM Fees

這條語句計數了FeeType列唯一值的個數。

10.3 分組數據-GROUP BY

1 SELECT
2 GradeType AS 'Grade Type',
3 AVG(Grade)AS 'Average Grade'
4 FROM Grades
5 GROUP BY GradeType
6 ORDER BY GradeType

感覺像EXCEL中的分類彙總功能。
如果想把Grade為NULL值的當做0，那麼可以用：

1 SELECT
2 GradeType AS 'Grade Type',
3 AVG(ISNULL(Grade,0))AS 'Average Grade'
4 FROM Grades
5 GROUP BY GradeType
6 ORDER BY GradeType

• GROUP BY子句中的列的順序是沒有意義的；
• ORDER BY子句中的列的順序是有意義的。

10.4 基於聚合查詢條件-HAVING

當針對帶GROUP BY的一條SELECT語句應用任何查詢條件時，人們必須要問查詢條件是應用於單獨的行還是整個組。

實際上，WHERE子句是單獨的執行查詢條件。SQL提供了一個名為HAVING的關鍵字，它允許對組級別使用查詢條件。
例子：
查看選修了類型為選修“A”，平均成績在70分以上的學生姓名，平均成績。

1 SELECT 
2 Name,
3 AVG(ISNULL(Grades,0)) AS 'Average Grades'
4 FROM Grades
5 WHERE GradeType='A'
6 GROUP BY Name
7 HAVING AVG(ISNULL(Grades,0))>70
8 ORDER BY Name

修要修類型為A，那麼，這是這對行的查詢，因此這裡要用WHERE。
但是，還要篩選平均成績，那麼，這是一個平均值，建立在聚合函數上的，並不是單獨的行，這就需要用到關鍵字HAVING。需要先將Student分組，然後把查詢結果應用到基於全組的一個聚合統計上。

WHERE只保證我們選擇了GradeType是A的行，HAVING保證平均成績至少70分以上。

註意：如果想要在結果中添加GradeType的值，如果直接在SELECT後邊添加這個列，將會出錯。這是因為，所有列都必須要麼出現在GROUP BY中，要麼包含在一個聚合函數中。

1 SELECT 
2 Name,
3 GradeType,
4 AVG(ISNULL(Grades,0)) AS 'Average Grades'
5 FROM Grades
6 WHERE GradeType='A'
7 GROUP BY Name,GradeType
8 HAVING AVG(ISNULL(Grades,0))>70
9 ORDER BY Name

十一、組合表

11.1 內連接來組合表-Inner Join

通過書中的描述，我感覺內連接更像是用來將主鍵表、外鍵表連接起來的工具。
例如：
A表：

B表：

如上表格，那麼要連接這兩個表格，查詢訂單1的客戶姓名，年齡，訂單號：
方式一：

1 SELECT name,age,orderid
2 FROM A,B
3 WHERE A.userid=B.userid
4 AND orderid=1

方式二，使用現在的內連接實現：

1 SELECT name,age,orderid
2 FROM A
3 INNER JOIN B
4 ON A.userid=B.userid
5 AND orderid=1

ON關鍵字指定兩個表如何準確的連接。

內連接中表的順序：FROM 子句指定了A表，INNER JOIN 子句指定B表，我們調換A,B順序，所得到的結果相同的!只是顯示列的順序可能會不同而已。

不建議使用方式一的格式。關鍵字INNER JOIN ON的優點在於顯示地表示了連接的邏輯，那是它們唯一的用途。WEHERE的含義不夠明顯。因為它是條件的意思啊，不是連接的！

11.2 外連接

外連接分為左連接（LEFT OUTER JOIN)、右連接(RIGHT OUTER JOIN)、全連接（FULL OUTER JOIN)。

OUTER是可以省略的。

左連接（LEFT JOIN)

1 SELECT name,age,orderid
2 FROM A
3 LEFT JOIN B
4 ON A.userid=B.userid
5 AND orderid=1

外連接的強大之處在於，主表中的數據必然都會保留，從表中列沒有值的情況，用NULL補充。

LEFT JOIN 左邊的表為主表，右邊的表為從表。

11.3 自連接

自連接必然用到表的別名。

1 SELECT A.name,B.name as ManagerName
2 FROM worker as A
3 LEFT JOIN worker as B
4 ON A.managerid=B.id

11.4 創建視圖

 1 CREATE VIEW ViewName AS 2 SelectStatement 3 [WITH CHECK OPTION] 

視圖中不能包含ORDER BY子句。

[WITH CHECK OPTION]表示對視圖進行UPDATE,INSERT,DELETE操作時任然保證了視圖定義時的條件表達式。

刪除視圖：

 1 DROP VIEW ViewName 

修改視圖：

 1 ALTER VIEW ViewName AS 2 SelectStatement 

視圖的優點

1. 簡化用戶的操作
2. 使用戶以多角度看待同一數據
3. 對重構資料庫提供了一定程度的邏輯獨立性
4. 對機密數據提供安全保護

十二、補充

12.1 子查詢

可以用3種主要的方式來指定子查詢，總結如下：

• 當子查詢是tablelist的一部分時，它指定了一個數據源。
• 當子查詢是condition的一部分時，它成為查詢條件的一部分。
• 當子查詢是columnlist的一部分時，它創建了一個單個的計算的列。

12.2 索引

索引是一種物理結構，可以為資料庫表中任意的列添加索引。

索引的目的是，當SQL語句中包含該列的是偶，可以加速數據的檢索。

索引的缺點是，在資料庫中，索引需要更多的存儲硬碟。另一個負面因素是，索引通常會降低相關的列數據更新速度。這是因為，任何時候插入或者修改一行記錄時，索引都必須重新計算該列中的值的正確的排列順序。

可以對任意的列進行索引，但是只能指定一個列作為主鍵。指定一個列作為主鍵意味著兩件事情：首先這個列成為了索引，其次保證這列包含唯一的值。

 1 CREATE INDEX Index2 2 ON MyTable (ColumnFour) 

刪除一個索引：

 1 DROP INDX Index2 2 ON MyTable 

參考：

• SQL總結（一）基本查詢
• SQL Server 常用高級語法筆記
• 史上最全的SQL Server複習筆記一

2018-03-09    21:26:41