黑馬程式員——SQL入門

------- Windows Phone 7手機開發、 .Net教育訓練、期待與您交流！ -------

SQLServer的管理

• 需要安裝 SQLServer2005 或者 SQLServer2008 ，若要使用 SQLServer 管理工具進行開發還要安裝 SQL Server Management Studio ，還可以使用 VisualStudio 進行管理。
• 使用免費的 SQLServerExpress 版本， Express 版本的伺服器名稱 .\SQLEXPRESS ，對于開發人員來講和其他版本沒有差別。
• SQLServer 的兩種驗證方式：使用者名驗證和 Windows 驗證，開發時用 Windows 驗證就行了。
• 開發人員關注點在開發上，而不是配置、備份等之上，那是 DBA （資料庫管理者）做的事情。

常用字段類型：bit(可選值0,1)、datetime、int、varchar、nvarchar(可能含有中文的用nvarchar)

Varchar、nvarchar和char(n)的差別：char(n)不足長度n的部分用空格填充。

• SQL 語句是和 DBMS “交談”專用的語句，不同 DBMS 都認 SQL 文法。
• SQL 語句中字元串用單引号。
• SQL 語句是大小寫不敏感的，不敏感指的是 SQL 關鍵字，字元串值還是大小寫敏感的。
• SQL 主要分 DDL （資料定義語言）和 DML （資料操作語言）兩類。 Create Table 、 Drop Table 、 Alter Table 等屬于 DDL ， Select,Insert,Update,Delete 等屬于 DML 。
• SQLServer 中兩種常用的主鍵資料類型： int （或 bigint ） + 辨別列（又稱自動增長字段）； uniqueidentifier( 又稱 Guid ， UUID)
• 用辨別列實作字段自增可以避免并發等問題，不要開發人員控制自增。用辨別列的字段在 Insert 的時候不要指定主鍵的值。
• Guid 算法是一種産生唯一辨別的高效算法，它使用網卡 MAC ，位址，納秒級世界，晶片 ID 碼等算出來。保證不會重複。 SQLServer 中生成 GUID 的函數 newid() ， .net 中生成 Guid 的方法： Guid.NewGuid(), 傳回是 Guid 類型。
• Int 自增字段的優點 : 占用空間小，無需開發人員幹預，易讀；缺點：效率低：資料導入導出的時候很痛苦（兩個資料庫合并的時候， id 的重複使得合并過程很痛苦）。
• Guid 的優點：效率高，資料導入導出友善；缺點占用空間大，不易讀。

建立表：

create table T_Person(Id int identity(1,2) not null primary key, Name nvarchar(50),Age int null default 20)

其中primary key把Id設定為主鍵。

identity(1,2)把主鍵Id設定為自動增長起始值為1，步長為2。

删除表：

drop table T_Person

插入資料：

insert into T_Person(Name,Age) values(’Jim’,20)

其中主鍵Id已設定為自動增長，是以不需要指派。

• Insert 語句可以省略表名後的列名，但是不推薦。
• 如果插入的行中有些字段的值不确定，那麼 insert 的時候不指定那些列即可。

更新一列資料：update T_Person set Age=45

更新多列資料：update T_Person set Age=30,Name=’tom’

更新一部分資料：update T_Person set Age=42 where Name=’tom’

用where語句表示隻更新Name是’tom’的行，注意SQL中等于判斷用單個=，而不是==。

Where中還可以使用複雜的邏輯判斷

update T_Person set Age=40 where Name=’tom’ or Age>25

其中or想到于C#中的||（或者）。

Where中可以使用的其他邏輯運算符：or,and,not,<,>,>=,<=,!=(或<>)等。

資料删除：

Delete from T_Person

Delete隻是删除資料，表還在，和drop Table不同。

Delete 也可以帶where子句來删除一部分資料：

Delete from T_Person where Age>20

資料檢索

簡單的檢索：Select * from T_Person

隻檢索需要的列：

select Name from T_Person

select Name,Age from T_Person

給列取别名：

Select Name as 姓名，Age as 年齡from T_Person

使用where檢索符合條件的資料：

Select Name from T_Person where Age>45

還可以檢索不與表關聯的資料：

Select 1+1;select newid();select getdate();select @@version

Select count(*)from T_Person

Select max(Age) from T_Person

Select min(Age) fromT_Person

Select avg(Age) from T_Person

Select sum(Age) from T_Person

資料排序：order by

Order by子句位于select語句的末尾，它允許指定按照一個列或者多個列進行排序，還可以指定排序方式是升序（從小到大排列，ASC），還是降序（從大到小排序，DESC）。

按照年齡升序排序：

Select * from T_Person order by Age ASC

按照年齡從大到小排序，如果年齡相同則按照工資從大到小排序：

Select * from T_Person order by Age DESC, Salary DESC

Order by子句要放到where子句之後：

Select * from T_Person

where Age>23

Order by Age DESC, Salary DESC;

通配符過濾

• 通配符過濾使用Like 。
• “_”比對單個字元。以任意字元開頭，剩餘部分為“ erry ”：

Select * from T_Person where Name like ‘_erry’

• “%”比對任意字元（零個或多個）。檢索姓名中包含字母”n”的員工資訊：

Select * from T_Person where Name like ‘%n%’

空值處理：

• 資料庫中，一個列如果沒有指定值，那麼值就為 null, 資料庫中的 null 表示“不知道”，而不是表示沒有。是以 select null+1 結果是

null，因為“不知道”加1的結果還是“不知道”。

• SQL 中使用is null ,is not null來進行空值判斷：

Select * from T_Person where Name is null;

Select * from T_Person where Name is not null;

多值比對：

Select * from T_Person where Age in(23,24,26)

範圍值：

Select * from T_Person where Age>=23 and Age<=27;

Select * from T_Person where Age between 23 and 27;

資料分組：

按照年齡進行分組統計各個年齡段的人數：

Select Age,Count(*) from T_Person group by Age

Group by 子句必須放到where語句之後。

沒有出現group by子句中的列是不能放到select語句後的列名清單中的（聚會函數除外）

錯誤：select Name,Age from T_Person group by Age

正确：select Age from T_Person group by Age

Having語句

• 在 where 中不能使用聚合函數，必須使用 Having ，Having要位于 Group by 之後，

select Age，count(*) as人數 from T_Person

Group by Age

Having count(*)>1;

• Having 不能取代 where 。作用不一樣， Having 是對組進行過濾。

限制結果集行數：

Select top 5 * from T_Person order by Age DESC。

（*）檢索按照年齡高低排序檢索從第六名開始一共三個人的資訊：

Select top 3 * from T_Person

Where Id not in(select top 5 Id from T_Person order by Age DESC)

Order by Age DESC

SQLServer2005後增加了Row_Number函數簡化實作，在本文最後會提到。

去掉重複資料：

Select distinct Name from T_Person

• Distinct 是對整個結果集進行資料重複處理的，而不是針對每一個列，

Select distinct Name,Age from T_Person（結果中就會出現同名的但年齡不會相同，或者年齡相同但名字絕對不會相同）

聯合結果集：union

• Union 合并兩個查詢結果集，并且将其中完全重複的資料行合并為一條。
• Union 因為要進行重複值掃描，是以效率低。
• 是以如果不是确定要合并重複行，那麼就用Union all

數字函數（*）

• ABS(): 求絕對值
• Ceiling(): 舍入到最大整數。 3.33 将被舍入為 4 。Ceiling--> 天花闆
• Floor(): 舍入到最小整數。 3.33 将舍入為 3 。Floorà地闆
• ROUND(): 四舍五入。舍入到“離我半徑最近的數”。 Round—> “半徑”。Round(3.144,2)=3.14

字元串函數（*）

• Len(): 計算字元串長度
• Lower(),upper(): 轉小寫，大寫。
• Ltrim(): 字元串左側的空格去掉
• Rtrim(): 字元串右側的空格去掉
• Ltrim(rtrim(‘ bb ’)) 結果就是’bb’
• Substring(string,start_position,length)

參數string為父字元串，start_position為字元串在父字元串中的起始位置，length為子字元串的長度。

Select substring(‘abcdef111’,2,3)結果為’bcd’

日期函數：

• Getdate(): 取得目前日期時間
• Dateadd(datepart,number,date), 計算增加以後的日期。參數 date 為計算的日期；參數 number 為增量；參數 datepart 為計量機關。

Dateadd(day,3,date)為計算日期date的3天後的日期。

Dateadd(month,-8,date)為計算日期date的8個月前的日期。

• Datediff(datepart,startdate,enddate): 計算兩個日期之間的差額。
• Datepart(datepart,date): 傳回一個日期的特定部分
• 統計員工的入職年份個數：

Select datepart(year,InDate),count(*)

From T_Employee

Group by DatePart(year,InDate)

類型轉換函數：

• Cast(expression as date_type)
• Convert(date_type,expression)
• Select Age,right(Age,1)as 最後一位

空值處理函數

• Isnull(expression,value): 如果 expression 不為空則傳回 expression ，否則傳回 value 。

Select isnull(Name,’匿名’)as姓名from T_Person

Case函數用法

單值判斷，相當于switch case

Case expression

When value1 then returnvalue1

When value2 then returnvalue2

When value3 then returnvalue3

Else defaultreturnvalue

End

例子：

Select Name

(case Level

when 1 then ‘VIP客戶’

when 2 then ‘進階客戶’

when 3 then ‘普通客戶’

else ‘客戶類型錯誤’

)as LevelName

From T_Customer

索引

• 全表掃描：對資料進行檢索 (select) 效率最差的是全表掃描，就是一條條的找
• 如果沒有目錄，查漢語字典就要一頁頁的翻，而有了目錄隻要查詢目錄即可。為了提高檢索的速度，可以為經常進行檢索的列添加索引，相當于建立目錄。
• 建立索引的方法，在表設計器中點選右鍵，選擇“索引 / 鍵”à添加à在列中選擇索引包含的列。
• 使用索引能提高查詢的效率，但是索引也是非常占據空間的。而且添加，更新，删除資料的時候也需要同步更新索引，是以會降低 insert,update,delete 的速度。是以隻在經常索引的字段上建立索引。
• （ * ）即使建立了索引，仍然有可能全表掃描，比如 like, 函數，類型轉換等。

表連接配接Join

• 有客戶表（ T_Customers ）和訂單表（ T_Orders ）兩個表，客戶表字段為： Id,Name,Age. 訂單表地段為： Id,BillNo,CustomerId. 訂單表通過 CustomerId 關聯客戶表。

Select o.BIllNo,c.Name,c.Age

From T_Order as o Join T_Customers as c on o.CustomerId=c.Id

子查詢：

• 将一個查詢語句作為一個結果集供其他 SQL 語句使用，就像使用普通的表一樣。所有可以使用表的地方幾乎都可以使用子查詢來代替。

最簡單的子查詢：select * from(select * from T_Person where age>30)

• 單值作為子查詢： select 1 as column1, 2 as column2,

(select min(Age) from T_Person) as column 3,

(select max(Age) from T_Book) as column 4

• 隻有傳回且僅傳回一行，一列資料的子查詢才能當成單值子查詢。

下面是錯誤的：

Select 1 as f1, (select Age from T_Person)

• 如果子查詢是多行單列的子查詢，這樣的子查詢的結果集其實是一個集合。

Select * from T_Reader

Where YearOfJoin in

(select YearPublished from T_Book)

• 限制結果集。傳回第三行和第五行的資料：

Select * from

(

Select row_number() over(order by Salary DESC)as rownum, Number,Name,Salary,Age from T_Employee

)as a

Where a.rownum in(3,5)