筆記——《SQL從入門到提高》

本筆記整理了楊中科老師在傳智播客.Net培訓的《SQL從入門到提高》視頻講座。視頻相關連結：http://www.rupeng.com/forum/tj-16325-2477.html 資料庫相關概念   1、什麼是索引？優缺點是什嗎？索引是對資料庫表中一列或多列的值進行排序的一種單獨的、物理的資料庫結構。
   優點：
　1) 大大加快資料的檢索速度；
　2) 建立唯一性索引，保證資料庫表中每一行資料的唯一性；
　3) 加速表和表之間的串連；
　4) 在使用分組和排序子句進行資料檢索時，可以顯著減少查詢中分組和排序的時間。
   缺點：
　1) 索引需要佔物理空間；
　2) 當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，降低了資料的維護速度。 2、業務主鍵和邏輯主鍵業務主鍵是使用有業務意義的欄位做主鍵，比如社會安全號碼，銀行帳號等；邏輯主鍵是使用沒有任何業務意義的欄位做主鍵。因為很難保證業務主鍵不會重複（社會安全號碼重複）、不會變化（帳號升位），因此推薦使用邏輯主鍵。 SQL Server 的使用   1、SQL Server 兩種常用的主鍵資料類型  1) int(或 bigint) + 識別欄位（又稱自動成長欄位）
     用識別欄位實現欄位自增可以避免並發等問題，不要開發人員控制自增。用識別欄位的欄位在Insert的時候不用指定主鍵的值。
     優點：佔用空間小、無需開發人員幹預、易讀；
     缺點：效率低，資料匯入匯出的時候很痛苦。
     設定：“修改表”->選定主鍵->“列屬性”->“標識規範”選擇“是”  2) uniqueidentifier(又稱GUID、UUID)
     GUID演算法是一種可以產生唯一表示的高效演算法，它使用網卡MAC、地址、納秒級時  間、晶片ID碼等算出來的，這樣保證每次產生的GUID永遠不會重複，無論是同一電腦還是不同電腦。在公元3400年前產生的GUID與任何其他產生過的GUID都不相同。SQL Server中產生GUID的函數newid()。
     優點：效率高、資料匯入匯出方便；
     缺點：佔用空間大、不易讀。  業界主流傾向於使用GUID。 SQL 基礎   1、DDL(資料定義語言 (Data Definition Language))    1) 建立資料表：

--建立表，SQL Server 的資料類型.CREATE TABLE T_Person(    Id int NOT NULL,     Name nvarchar(50),    Age int NULL,    PRIMARY KEY(Id));--建立表，添加外鍵.CREATE TABLE T_Users(    StudentNo CHAR(4),     CourseNo CHAR(4),    Score INT,    PRIMARY KEY(StudentNo),    FOREIGN KEY(CourseNo) REFERENCES T_Course(CourseNo));

  2) 修改表結構：

--修改表結構，添加欄位ALTER TABLE T_Person ADD NickName nvarchar(50) NULL;--修改表結構，刪除欄位ALTER TABLE T_Person DROP NickName;

  3) 刪除資料表：

DROP TABLE T_Person;

 4) 建立索引：

CREATE [UNIQUE] INDEX <索引名> ON <基本表名>(<列名序列>);

  2、DML(資料操縱語言)    1) 插入語句：

INSERT INTO T_Person(Id, Name, Age) VALUES(1, 'Jim', 20);

  2) 更新語句：

UPDATE T_Person SET Age=Age+1 where Name='tom' or Age<25;

  3) 刪除語句：

--刪除表中所有記錄.DELETE FROM T_Person;--刪除表中指定記錄.DELETE FROM T_Person WHERE Age>20;

  4) 查詢語句： 

--簡單的資料查詢.SELECT * FROM T_Employee;--只查詢需要的列.SELECT FNumber FROM T_Employee;--給列取別名.SELECT FNumber AS 編號, FName AS 姓名 FROM T_Employee;--使用 WHERE 查詢合格記錄.SELECT FName FROM T_Employee WHERE FSalary<5000;--對錶記錄進行排序.SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC;--ORDER BY 子句要放在 WHERE 子句之後.SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC;--WHERE 中可以使用的邏輯運算子：or、and、not、<、>、=、>=、<=、!=、<>等.--查詢不與任何錶關聯的資料.SELECT 1+1;SELECT newid();SELECT getdate();--模糊比對，首字母未知.SELECT * FROM T_Employee WHERE FName LIKE '_arry';--模糊比對，前後多個字元未知.SELECT * FROM T_Employee WHERE FName LIKE '%n%';--NULL 表示“不知道”，有 NULL 參與的運算結果一般都為 NULL.--查詢資料是否為 NULL，不能用 = 、!= 或 <>.SELECT * FROM T_Employee WHERE FName IS NULL;SELECT * FROM T_Employee WHERE FName IS NOT NULL;--查詢在某個範圍內的資料，IN 表示包含於.SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28);--下面兩句等價。SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30;SELECT * FROM T_Employee WHERE FAge BETWEEN 23 AND 30;

資料分組

--下面語句操作順序：先按年齡相同的進行分組，然後顯式每組年齡，並統計出每組行數.--注意：彙總函式是對分組操作結果進行運算.SELECT FAge,COUNT(*) FROM T_Employee GROUP BY FAge;--下面語句錯誤，因為一組中各行姓名 FName 是不同的，--不能使用沒有在 GROUP BY 中出現的欄位，彙總函式除外.SELECT FAge,FName,COUNT(*) FROM T_Employee GROUP BY FAge;--可以像下面這樣寫，因為一組中最大工資只有一個.SELECT FAge,MAX(FSalary),COUNT(*) FROM T_Employee GROUP BY FAge;--GROUP BY 應該放在 WHERE 的後面，彙總函式是不能出現在 WHERE 語句中的，--WHERE 是對原始表記錄進行條件操作，ORDER BY 是在條件操作之後進行分組操作，--HAVING 放在 ORDER BY 後面，用來對分組操作的結果集進行篩選操作，--HAVING 是 ORDER BY 的子句，不能代替 WHERE，HAVING 中的欄位必須出現在 GROUP BY 中.--下面語句操作順序：先按年齡相同的進行分組，然後篩選出行數大於 1 的分組。SELECT FAge,COUNT(*) FROM T_Employee     GROUP BY FAge        HAVING COUNT(*)>1;--下面語句是錯誤的，因為 FSalary 在每個分組中不是唯一的，不能用來標識分組.--HAVING能用的列和 SELECT 中能用的列是一樣的。SELECT FAge,COUNT(*) FROM T_Employee     GROUP BY FAge        HAVING FSalary>2000;

限制結果集

--限制結果集的行數：SELECT TOP 5 * FROM T_Employee ORDER BY FSalary DESC;--下面語句意圖：按照工資從高到低排序，檢索從第六名開始一共三個人的資訊。--操作順序：從內層到外層--先查詢倒序前五名的編號，然後排除前五名編號，--得到的資料從第六名開始，最後按倒序從第六名開始取前三名。SELECT TOP 3 * FROM T_Employee    WHERE FNumber NOT IN (        SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)    ORDER BY FSalary DESC;--去除重複資料，所去除的是完全重複的記錄，即所有欄位都相同者去掉。--查詢所有員工的部門資訊，部門資訊重複.SELECT FDepartment FROM T_Employee;--想要查詢公司包含多少個部門，結果集中部門資訊重複，加關鍵字 DISTINCT 可以消除重複。SELECT DISTINCT FDepartment FROM T_Employee;

聯合結果集

--把 UNION 前後的查詢結果集合為一個結果集，返回的結果欄位數必須相同，各欄位類型必須相容。--預設情況下聯合結果集會自動去除重複。如果不想去除重複資料，需要在 UNION 後加上 ALL。SELECT FName,FAge,'臨時工，無部門' FROM T_TempEmployeeUNIONSELECT FName,FAge,FDepartment FROM T_Employee;--不去除重複的聯合結果集.SELECT FName,FAge,'臨時工，無部門' FROM T_TempEmployeeUNION ALLSELECT FName,FAge,FDepartment FROM T_Employee;--使用聯合結果集做報表.SELECT '正式員工最高年齡',MAX(FAge) FROM T_EmployeeUNION ALLSELECT '正式員工最低年齡',MIN(FAge) FROM T_EmployeeUNION ALLSELECT '臨時工最高年齡',MAX(FAge) FROM T_TempEmployeeUNION ALLSELECT '臨時工最低年齡',MIN(FAge) FROM T_TempEmployee;--使用聯合結果集查詢所用員工工資合計.SELECT FNumber, FSalary FROM T_EmployeeUNIONSELECT '工資合計',SUM(FSalary) FROM T_Employee;

SQL Server 部分函數 1、數學函數ABS()：求絕對值
CEILING()：舍入到最大整數（上限）。CEILING(3.33)=4，CEILING(-3.33)=-3
FLOOR()：舍入到最小整數（下限）。FLOOR(3.33)=3，FLOOR(-3.33)=-4
ROUND()：四捨五入。舍入到“離我半徑最近的數”。ROUND(3.1415,3)=3.142 2、字串函數LEN()：求字串長度。LEN('abc')=3
LOWER()、UPPER()：轉小寫、大寫
LTRIM()：去掉字串左側空格
RTRIM()：去掉字串右側空格
SUBSTRING(string,start_position,length)：取子串，參數 string 為主字串，start_position 為子字串在主字串中的起始位置，length 為子字串的最大長度。 3、日期函數GETDATE()：取當前日期時間
DATEADD(datepart,number,date)：計算增加以後的日期。參數 date 為待計算的日期，參數 datepart 為計量單位（YEAR,YY,MONTH,MM,DAY,DD 等）DATEADD(DAY,3,date)為計算日期 date 的 3 天后的日期，DATEADD(MONTH,-8,date)為計算日期 date 的 8 個月前的日期
DATEDIFF(datepart,startdate,enddate)：計算兩個日期之間的差額。datepart 為計量單位
DATEPART(datepart,date)：返回一個日期的特定的部分

--查詢員工入職年數.SELECT FName,FInDate,DATEDIFF(YEAR,FInDate,GETDATE()) FROM T_Employee;--查詢各入職年數的員工個數.SELECT DATEDIFF(YEAR,FInDate,GETDATE()),COUNT(*) FROM T_Employee    GROUP BY DATEDIFF(YEAR,FInDate,GETDATE());

4、類型轉換函式CAST(expression AS date_type)
CONVERT(date_type,expression)

--字串轉換為整數SELECT CAST('123' AS INTEGER);--字串轉換成日期時間類型SELECT CAST('2008-08-08' AS DATETIME), CONVERT(DATETIME,'2008-08-08');SELECT DATEPART(YEAR,CAST('2008-08-08' AS DATETIME));--數字轉換成字串SELECT CONVERT(varchar(50),123);SELECT FIdNumber,    RIGHT(FIdNumber,3) AS 後三位,    CAST(RIGHT(FIdNumber,3) AS INTEGER) AS 後三位的整數形式,    CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 AS 後三位加一,    CONVERT(INTEGER,RIGHT(FIdNumber,3))/2 AS 後三位除以2    FROM T_Person;

5、空值處理函數ISNULL(expression,value)：如果 expression 不為空白則返回 expression，否則返回 value

--當記錄中 FName 欄位為 NULL 時顯式“佚名”SELECT ISNULL(FName,'佚名') AS 姓名 FROM T_Employee;

5、流量控制函數單值判斷，相當於 switch-case
CASE expression
WHEN value1 THEN return_value1
WHEN value2 THEN return_value2
WHEN value3 THEN return_value3
ELSE default_return_value
END

SELECT FName,(    CASE FLevel    WHEN 1 THEN 'VIP客戶'    WHEN 2 THEN '進階客戶'    WHEN 3 THEN '普通客戶'    ELSE '客戶類型錯誤'    END) AS FLevelName    FROM T_Customer;--範圍判斷SELECT FName,(    CASE    WHEN FSalary<2000 THEN '低收入'    WHEN FSalary>=2000 AND FSalary<=5000 THEN '中等收入'      ELSE '高收入'    END)    FROM T_Employee;

練習

--有一張表T_Scroes，記錄比賽成績：--Date       Name   Scroe--2008-8-8   拜仁   勝--2008-8-9   奇才   勝--2008-8-8   湖人   勝--2008-8-10  拜仁   負--2008-8-8   拜仁   負--2008-8-12  奇才   勝--要求輸出下面格式：--Name  勝  負--拜仁  1   2--湖人  1   0--奇才  2   0--注意：在中文字串前加 N，比如 N'勝'--下面運算順序：--先執行內層查詢，得到如下中間結果集：--Name   勝  負--拜仁   1   0--奇才   1   0--湖人   1   0--拜仁   0   1--拜仁   0   1--奇才   1   0--然後對中間結果集按隊名 Name 進行分組，最後計算出每組“勝”欄位的和與“負”欄位的和。SELECT Name,    SUM       (CASE Score        WHEN N'勝' THEN 1        ELSE 0        END) AS 勝,    SUM       (CASE Score        WHEN N'負' THEN 1        ELSE 0        END) AS 負    FROM T_Scroes    GROUP BY Name;

--建立一張表T_Callers，記錄撥打電話員的工作流程水，記錄呼叫員編號、對方號碼、通話開始時間、通話結束時間。建表、插資料等最後都自己寫SQL語句。--要求：--    1) 輸出所有資料中通話時間最長的5條記錄。--    2) 輸出所有資料中撥打長途號碼（對方號碼以0開頭）的總時間長度。--    3) 輸出本月通話總時間長度最多的前三個呼叫員的編號。--    4) 輸出本月撥打到電話次數最多的前三個呼叫員的編號。--    5) 輸出所有資料的撥號流水，並且在最後一行添加總呼叫時間長度。--          記錄呼叫員編號、對方號碼、通話時間長度--          ......--          匯總[市內號碼總時間長度][長途號碼總時間長度]--Id   CallerNumber   TellNumber    StartDateTime     EndDateTime--1    001            02088888888   2010-7-10 10:01   2010-7-10 10:05--2    001            02088888888   2010-7-11 13:41   2010-7-11 13:52--3    001            89898989      2010-7-11 14:42   2010-7-11 14:49  --4    002            02188368981   2010-7-13 21:04   2010-7-13 21:18--5    002            76767676      2010-6-29 20:15   2010-6-29 20:30--6    001            02288878243   2010-7-15 13:40   2010-7-15 13:56--7    003            67254686      2010-7-13 11:06   2010-7-13 11:19--8    003            86231445      2010-6-19 19:19   2010-6-19 19:25--9    001            87422368      2010-6-19 19:25   2010-6-19 19:36--10   004            40045862245   2010-6-19 19:50   2010-6-19 19:59--建立資料表T_Callers.CREATE TABLE T_Callers   (Id int NOT NULL,    CallerNumber varchar(3),    TellNumber varchar(13),    StartDateTime datetime,    EndDateTime datetime,    PRIMARY KEY(Id));--插入資料.INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (1,'001','02088888888','2010-7-10 10:01','2010-7-10 10:05');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (2,'002','02088888888', '2010-7-11 13:41','2010-7-11 13:52');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (3,'003','89898989',      '2010-7-11 14:42',   '2010-7-11 14:49');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (4,'004','02188368981',   '2010-7-13 21:04',   '2010-7-13 21:18');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (5,'005','76767676',      '2010-6-29 20:15',   '2010-6-29 20:30');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (6,'006','02288878243',   '2010-7-15 13:40',   '2010-7-15 13:56');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (7,'007','67254686',      '2010-7-13 11:06',   '2010-7-13 11:19');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (8,'008','86231445',      '2010-6-19 19:19',   '2010-6-19 19:25');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (9,'009','87422368',      '2010-6-19 19:25',   '2010-6-19 19:36');INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)    VALUES (10,'010','40045862245',   '2010-6-19 19:50',   '2010-6-19 19:59');--修改呼叫員編號.UPDATE T_Callers SET CallerNumber='001' WHERE Id IN (1,2,3,6,9);UPDATE T_Callers SET CallerNumber='002' WHERE Id IN (4,5);UPDATE T_Callers SET CallerNumber='003' WHERE Id IN (7,8);UPDATE T_Callers SET CallerNumber='004' WHERE Id=10;--題 1)：--@計算通話時間；--@按通話時間降序排列；--@取前5條記錄。SELECT TOP 5 * FROM T_Callers     ORDER BY DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC;--題 2)：--@查詢撥打長途號碼的記錄；--@計算各撥打長途號碼的通話時間長度；--@對各撥打長途號碼的通話時間長度進行求和。SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS 長途總時間長度     FROM T_Callers     WHERE TellNumber LIKE '0%';--題 3)：輸出本月通話總時間長度最多的前三個呼叫員的編號.--@按呼叫員編號進行分組；--@計算各呼叫員通話總時間長度；--@按通話總時間長度進行降序排列；--@查詢前3條記錄中呼叫員的編號。SELECT TOP 3 CallerNumber FROM T_Callers     GROUP BY CallerNumber    ORDER BY SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC;--題 4) 輸出本月撥打到電話次數最多的前三個呼叫員的編號.--@按呼叫員編號進行分組；--@計算個呼叫員撥打到電話的次數；--@按呼叫員撥打到電話的次數進行降序排序；--@查詢前3條記錄中呼叫員的編號。SELECT TOP 3 CallerNumber FROM T_Callers     GROUP BY CallerNumber    ORDER BY COUNT(*) DESC;--題5) 輸出所有資料的撥號流水，並且在最後一行添加總呼叫時間長度：--        記錄呼叫員編號、對方號碼、通話時間長度--        ......--        匯總[市內號碼總時間長度][長途號碼總時間長度]--@計算每條記錄中通話時間長度；--@查詢包含不加 0 號碼，即市內號碼的記錄；--@計算市內號碼通話總時間長度；--@查詢包含加 0 號碼，即長途號碼的記錄；--@計算長途號碼通話總時間長度；--@聯集查詢。(SELECT     CallerNumber AS 呼叫員編號,    TellNumber AS 對方號碼,    DATEDIFF(SECOND,StartDateTime,EndDateTime) AS 通話時間長度    FROM T_Callers)UNION(SELECT     '匯總',    CAST(SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS varchar),    CAST(       (SELECT            SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))            FROM T_Callers            WHERE TellNumber LIKE '0%')         AS varchar)    FROM T_Callers    WHERE TellNumber NOT LIKE '0%');--另一種查詢匯總的方法：SELECT '匯總',    SUM(        CASE        WHEN TellNumber NOT LIKE '0%'             THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)        ELSE 0        END),    SUM(CASE        WHEN TellNumber LIKE '0%'             THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)        ELSE 0        END)    FROM T_Callers;--題 1)：輸出所有資料中通話時間最長的5條記錄.--@計算通話時間；--@按通話時間降序排列；--@取前5條記錄。SELECT TOP 5 * FROM T_Callers ORDER BY DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC;--題 2)：輸出所有資料中撥打長途號碼（對方號碼以0開頭）的總時間長度.--@查詢撥打長途號碼的結果集；--@計算各撥打長途號碼的通話時間長度；--@對各撥打長途號碼的通話時間長度進行求和。SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS 長途總時間長度     FROM T_Callers     WHERE TellNumber LIKE '0%';--題 3)：輸出本月通話總時間長度最多的前三個呼叫員的編號.--@按呼叫員編號進行分組；--@計算各呼叫員通話總時間長度；--@按通話總時間長度進行降序排列；--@查詢前3條記錄中呼叫員的編號。SELECT TOP 3 CallerNumber FROM T_Callers     GROUP BY CallerNumber    ORDER BY SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC;--題 4) 輸出本月撥打到電話次數最多的前三個呼叫員的編號.--@按呼叫員編號進行分組；--@計算個呼叫員撥打到電話的次數；--@按呼叫員撥打到電話的次數進行降序排序；--@查詢前3條記錄中呼叫員的編號。SELECT TOP 3 CallerNumber FROM T_Callers     GROUP BY CallerNumber    ORDER BY COUNT(*) DESC;--題5) 輸出所有資料的撥號流水，並且在最後一行添加總呼叫時間長度：--        記錄呼叫員編號、對方號碼、通話時間長度--        ......--        匯總[市內號碼總時間長度][長途號碼總時間長度]--@計算每條記錄中通話時間長度；--@查詢包含不加 0 號碼，即市內號碼的記錄；--@計算市內號碼通話總時間長度；--@查詢包含加 0 號碼，即長途號碼的記錄；--@計算長途號碼通話總時間長度；--@聯集查詢。(SELECT     CallerNumber AS 呼叫員編號,    TellNumber AS 對方號碼,    DATEDIFF(SECOND,StartDateTime,EndDateTime) AS 通話時間長度    FROM T_Callers)UNION(SELECT     '匯總',    CAST(SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS varchar),    CAST(       (SELECT            SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))            FROM T_Callers            WHERE TellNumber LIKE '0%')         AS varchar)    FROM T_Callers    WHERE TellNumber NOT LIKE '0%');--另一種查詢匯總的方法：SELECT '匯總',    SUM(        CASE        WHEN TellNumber NOT LIKE '0%'             THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)        ELSE 0        END),    SUM(CASE        WHEN TellNumber LIKE '0%'             THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)        ELSE 0        END)    FROM T_Callers;