數(shù)據(jù)庫開發(fā)及

北京傳智播客教育數(shù)據(jù)庫開發(fā)及ADO.Net講師：楊中科數(shù)據(jù)庫概述用自定義文件格式保存數(shù)據(jù)的劣勢DBMS（DataBase

ManagementSystem，數(shù)據(jù)庫管理系統(tǒng)）和數(shù)據(jù)庫。平時談到“數(shù)據(jù)庫”可能有兩種含義：

MSSQLServer、Oracle等某種DBMS；存放一堆數(shù)據(jù)表的一個分類（

Catalog

）。不同品牌的DBMS有自己的不同的特點(diǎn)：MYSQL、MSSQLServer、DB2、Oracle、Access、Sybase等。對于開發(fā)人員來講，大同小異SQL<>SQLServer<>MSSQLServer。最常見的錯誤。除了Access、SQLServerCE等文件型數(shù)據(jù)庫之外，大部分?jǐn)?shù)據(jù)庫都需要數(shù)據(jù)庫服務(wù)器才能運(yùn)行。學(xué)習(xí)、開發(fā)時是連接本機(jī)的數(shù)據(jù)庫，上線運(yùn)行時是數(shù)據(jù)庫運(yùn)行在單獨(dú)的服務(wù)器。數(shù)據(jù)庫中的概念Catalog（分類）（又叫數(shù)據(jù)庫DataBase、表空間TableSpace），不同類的數(shù)據(jù)應(yīng)該放到不同的數(shù)據(jù)庫中便于對各個Catalog進(jìn)行個性化管理避免命名沖突安全性更高Table（表）：書都放到書架上，碗都放到櫥柜中，不同類型的資料放到不同的

“格子”中，將這種區(qū)域叫做“表”（Table）。不同的表根據(jù)放的數(shù)據(jù)不同進(jìn)行空間的優(yōu)化，找起來也方便。列（Column）、字段（Field）2003年5月入職，是產(chǎn)品開發(fā)部的，姓名馬小虎王二小，技術(shù)支持部，入職是2005年7月姓名馬小虎部門開發(fā)部入職時間2008.06.06姓名部門入職時間主鍵（PrimaryKey）工號姓名部門入職時間001風(fēng)姐員工培訓(xùn)部2010年7月5日002瘦瘦公關(guān)部2010年8月2日003憨憨開發(fā)部2009年3月5日主鍵就是數(shù)據(jù)行的唯一標(biāo)識。不會重復(fù)的列才能當(dāng)主鍵。一個表可以沒有主鍵，但是會非常難以處理，因此沒有特殊理由表都要設(shè)定主鍵主鍵有兩種選用策略：業(yè)務(wù)主鍵和邏輯主鍵。業(yè)務(wù)主鍵是使用有業(yè)務(wù)意義的字段做主鍵，比如身份證號、銀行賬號等；邏輯主鍵是使用沒有任何業(yè)務(wù)意義的字段做主鍵，完全給程序看的，業(yè)務(wù)人員不會看的數(shù)據(jù)。因?yàn)楹茈y保證業(yè)務(wù)主鍵不會重復(fù)（身份證號重復(fù)）、不會變化（帳號升位），因此推薦用邏輯主鍵。表間關(guān)聯(lián)、外鍵（ForeignKey）商品名價格廠家編號大大香瓜子5.00001大大開心果15.00001苦咖啡2002隨變3002冰工廠1002商品名價格生產(chǎn)廠家廠家地址廠家電話大大香瓜子5.00大大食品廠恰恰大街300號010-123456大大開心果15.00大大食品廠恰恰大街300號010-123456苦咖啡2伊利食品廠內(nèi)蒙古伊利路1號400400400隨變3伊利食品廠內(nèi)蒙古伊利路1號400400400冰工廠1伊利食品廠內(nèi)蒙古伊利路1號400400400編號名稱地址電話001大大食品廠恰恰大街300號010-123456002伊利食品廠內(nèi)蒙古伊利路1號400400400SQLServer的管理需要安裝SQLServer2005或者SQLServer2008，若要使用SQLServer管理工具進(jìn)行開發(fā)還要安裝SQL

Server

Management

Studio，還可以使用VisualStudio進(jìn)行管理使用免費(fèi)的SQLServerExpress版本，Express版本的服務(wù)器名稱.\SQLEXPRESS，對于開發(fā)人員來講和其他版本沒有區(qū)別。SQLServer的兩種驗(yàn)證方式：用戶名驗(yàn)證和Windows驗(yàn)證，開發(fā)時用

Windows驗(yàn)證就行。開發(fā)人員關(guān)注點(diǎn)在開發(fā)上，而不是配置、備份等之上，那是DBA做的事情。創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表，設(shè)置主鍵SQLServer2008ManagementStudio中：編輯200行。05中：打開表。常用字段類型：bit(可選值0、1)、datetime、int、varchar、nvarchar（可能含有中文用nvarchar）Nvarchar(50)、Nvarchar(MAX)varchar、nvarchar和char(n)的區(qū)別：char(n)不足長度n的部分用空格填充。Var：Variable，可變的。SQL語句入門SQL語句是和DBMS“交談”專用的語句，不同DBMS都認(rèn)SQL語法。SQL語句中字符串用單引號。SQL語句是大小寫不敏感的，不敏感指的是SQL關(guān)鍵字，字符串值還是大小寫敏感的創(chuàng)建表、刪除表不僅可以手工完成，還可以執(zhí)行SQL語句完成，在自動化部署、數(shù)據(jù)導(dǎo)入中用的很多，CREATE

TABLET_Person(Idint

NOT

NULL,Name

nvarchar(50),Age

int

NULL)、Drop

tableT_Person1簡單的Insert語句。INSERT

INTO

T_Person(Id,Name,Age)VALUES(1,'Jim',20)（*）

SQL主要分DDL（數(shù)據(jù)定義語言）和DML（數(shù)據(jù)操作語言）兩類。Create

Table、Drop

Table、Alter

Table等屬于DDL，Select、Insert、Update、Delete等屬于DML主鍵選擇SQLServer中兩種常用的主鍵數(shù)據(jù)類型：int（或bigint）+標(biāo)識列（又稱自動增長字段）；uniqueidentifier（又稱Guid、UUID）用標(biāo)識列實(shí)現(xiàn)字段自增可以避免并發(fā)等問題，不要開發(fā)人員控制自增。用標(biāo)識列的字段在Insert的時候不用指定主鍵的值。將字段的“是標(biāo)識列”設(shè)置為“是”，一個表只能有一個標(biāo)識列。Guid算法是一種可以產(chǎn)生唯一標(biāo)識的高效算法，它使用網(wǎng)卡MAC、地址、納秒級時間、芯片ID碼等算出來的，這樣保證每次生成的GUID永遠(yuǎn)不會重復(fù)，無論是同一個計算機(jī)上還是不同的計算機(jī)。在公元3400年以前產(chǎn)生的GUID與任何其他產(chǎn)生過的GUID都不相同。SQLServer中生成

GUID的函數(shù)newid()，.Net中生成Guid的方法：Guid.NewGuid()，返回是Guid類型。（*）Int自增字段的優(yōu)點(diǎn)：占用空間小、無需開發(fā)人員干預(yù)、易讀；缺點(diǎn)：效率低；數(shù)據(jù)導(dǎo)入導(dǎo)出的時候很痛苦。（*）Guid的優(yōu)點(diǎn)：效率高、數(shù)據(jù)導(dǎo)入導(dǎo)出方便；缺點(diǎn)占用空間大、不易讀。業(yè)界主流傾向于使用Guid。數(shù)據(jù)插入Insert語句可以省略表名后的列名，但是不推薦。如果插入的行中有些字段的值不確定，那么Insert的時候不指定那些列即可。給可以給字段默認(rèn)值，如果Guid類型主鍵的默認(rèn)值設(shè)定為newid()就會自動生成，很少這么干。主鍵：insert

into

Person3(Name,Age)values('lily',38);insert

intoPerson4(Id,Name,Age)values(newid(),'tom',30);數(shù)據(jù)更新更新一個列：UPDATE

T_Person

Set

Age=30更新多個列：UPDATE

T_Person

SetAge=30,Name=‘tom’更新一部分?jǐn)?shù)據(jù)：UPDATE

T_Person

Set

Age=30

whereName=‘tom’，用where語句表示只更新Name是’tom’的行，注意SQL中等于判斷用單個=，而不是==。Where中還可以使用復(fù)雜的邏輯判斷UPDATE

T_PersonSet

Age=30whereName=‘tom’

or

Age<25，or相當(dāng)于C#中的||（或者）update

Person1

set

NickName=N'二十歲'where

(Age>20

and

Age<30)

or(Age=80)Where中可以使用的其他邏輯運(yùn)算符：or、and、not、<、>、>=、<=、!=（或<>）等數(shù)據(jù)刪除刪除表中全部數(shù)據(jù)：DELETE

FROM

T_Person。Delete只是刪除數(shù)據(jù)，表還在，和Drop

Table不同。Delete也可以帶where子句來刪除一部分?jǐn)?shù)據(jù)：DELETEFROM

T_Person

WHERE

FAge>20數(shù)據(jù)檢索執(zhí)行備注中的代碼創(chuàng)建測試數(shù)據(jù)表。簡單的數(shù)據(jù)檢索：SELECT

*FROM

T_Employee只檢索需要的列：SELECT

FNumber

FROMT_Employee

、SELECT

FName,FAge

FROMT_Employee列別名：SELECT

FNumber

AS

編號,FName

AS

姓名,FAge

AS

Age111

FROM

T_Employee使用where檢索符合條件的數(shù)據(jù)：SELECT

FName

FROMT_EmployeeWHEREFSalary<5000。故事：新員工的數(shù)據(jù)檢索噩夢。還可以檢索不與任何表關(guān)聯(lián)的數(shù)據(jù)：select

1+1;selectnewid();select

getdate();數(shù)據(jù)匯總SQL聚合函數(shù)：MAX（最大值）、MIN（最小值）、AVG（平均值）、SUM（和）、COUNT（數(shù)量）大于25歲的員工的最高工資：SELECT

MAX(FSalary)FROM

T_Employee

WHERE

FAge>25最低工資和最高工資：SELECTMIN(FSalary),MAX(FSalary)

FROM

T_Employee數(shù)據(jù)排序ORDERBY子句位于SELECT語句的末尾，它允許指定按照一個列或者多個列進(jìn)行排序，還可以指定排序方式是升序（從小到大排列，ASC）還是降序（從大到小排列，

DESC）。按照年齡升序排序所有員工信息的列表：SELECT

*FROM T_Employee

ORDER

BY

FAge

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

：SELECT

*FROM T_Employee

ORDER

BYFAge

DESC,FSalary

DESCORDER

BY子句要放到WHERE子句之后：SELECT

*FROM

T_Employee

WHERE

FAge>23

ORDER

BY

FAgeDESC,FSalary

DESC通配符過濾通配符過濾使用LIKE

。單字符匹配的通配符為半角下劃線“_”，它匹配單個出現(xiàn)的字符。以任意字符開頭，剩余部分為“erry”：SELECT

*

FROM

T_EmployeeWHEREFNameLIKE'_erry'多字符匹配的通配符為半角百分號“%”，它匹配任意次數(shù)（零或多個）出現(xiàn)的任意字符。

“k%”匹配以“k”開頭、任意長度的字符串。檢索姓名中包含字母“n”的員工信息：SELECT

*FROM

T_EmployeeWHEREFNameLIKE'%n%'空值處理數(shù)據(jù)庫中，一個列如果沒有指定值，那么值就為null，這個

null和C#中的null，數(shù)據(jù)庫中的null表示“不知道”，而不是表示沒有。因此select

null+1結(jié)果是null，因?yàn)椤安恢?/p>

”加1的結(jié)果還是“不知道”。SELECT

*FROM

T_EmployeeWHEREFNAME=null

；SELECT

*

FROM

T_Employee

WHERE

FNAME!=null

；都沒有任何返回結(jié)果，因?yàn)閿?shù)據(jù)庫也“不知道”。SQL中使用is

null、is

not

null來進(jìn)行空值判斷：SELECT

*FROM

T_Employee

WHERE

FNAME

isnull

；SELECT

*FROM

T_Employee

WHERE

FNAME

is

not

null

；多值匹配SELECT

FAge,FNumber,FName

FROMT_Employee

WHERE

FAge

IN

(23,25,28)范圍值：SELECT

*

FROM

T_Employee

WHEREFAGE>=23

AND

FAGE

<=27

；SELECT

*

FROMT_Employee

WHERE

FAGE

BETWEEN

23

AND27數(shù)據(jù)分組GROUP

BY

FAge按照年齡進(jìn)行分組統(tǒng)計各個年齡段的人數(shù)：

SELECT

FAge,Count(*)FROM

T_EmployeeGROUP

BY

FageGROUPBY子句必須放到WHERE語句的之后沒有出現(xiàn)在GROUP

BY子句中的列是不能放到

SELECT語句后的列名列表中的（聚合函數(shù)中除外）錯誤：SELECT

FAge,FSalary

FROM

T_Employee正確：SELECT

FAge,AVG(FSalary)

FROMT_EmployeeGROUP

BY

FAgeHaving語句在Where中不能使用聚合函數(shù)，必須使用

Having，Having要位于Group

By之后，

SELECT

FAge,COUNT(*)

AS

人數(shù)FROMT_EmployeeGROUP

BY

FAgeHAVING

COUNT(*)>1注意Having中不能使用未參與分組的列，

Having不能替代where。作用不一樣，Having是對組進(jìn)行過濾。限制結(jié)果集行數(shù)select

top5*from

T_Employee

orderbyFSalary

Desc（*）檢索按照工資從高到低排序檢索從第六名開始一共三個人的信息：SELECT

top3*FROM

T_Employee

WHERE

FNumber

NOTIN

(SELECT

TOP

5

FNumber

FROMT_Employee

ORDER

BYFSalary

DESC)ORDER

BYFSalary

DESCSQLServer2005后增加了Row_Number函數(shù)簡化實(shí)現(xiàn)，后面會講。去掉數(shù)據(jù)重復(fù)執(zhí)行備注中的SQL語句，Alter和Insert單獨(dú)執(zhí)行。SELECT

FDepartment

FROM

T_Employee→SELECT

DISTINCTFDepartment

FROMT_EmployeeDISTINCT是對整個結(jié)果集進(jìn)行數(shù)據(jù)重復(fù)處理的，而不是針對每一個列，因此下面的語句并不會只保留Fdepartment進(jìn)行重復(fù)值處理：

SELECT

DISTINCTFDepartment,FSubCompany

FROMT_Employee常見問題1、SQLServer2008

Management

Studio中點(diǎn)擊【執(zhí)行】按鈕，而不是綠色箭頭的調(diào)試按鈕。2、如果機(jī)器上安裝了VisualStudio2010或者

SQLServer2008，需要安裝SQLServer2005Management

Studio3、SQLServer2008

Management

Studio中是“修改前200條”4、Alter增加字段之后要關(guān)閉窗口重新打開才能看到新增加的列。聯(lián)合結(jié)果集執(zhí)行備注中的代碼簡單的結(jié)果集聯(lián)合：SELECT

FNumber,FName,FAgeFROM

T_EmployeeUNION SELECT

FIdCardNumber,FName,FAgeFROM

T_TempEmployee基本的原則：每個結(jié)果集必須有相同的列數(shù)；每個結(jié)果集的列必須類型相容。SELECT

FNumber,FName,FAge,FDepartmentFROM

T_Employee

UNION

SELECTFIdCardNumber,FName,FAge,‘臨時工，無部門

'FROM

T_TempEmployeeUnion

allSELECT

FName

FROM

T_Employee

UNIONSELECT

FName

FROM

T_TempEmployee。UNION合并兩個查詢結(jié)果集，并且將其中完全重復(fù)的數(shù)據(jù)行合并為一條SELECT

FName

FROM

T_EmployeeUNION

ALLSELECT

FName

FROM

T_TempEmployeeUnion因?yàn)橐M(jìn)行重復(fù)值掃描，所以效率低，因此如果不是確定要合并重復(fù)行，那么就用UNION

ALL案例1要求查詢員工的最低年齡和最高年齡，臨時工和正式員工要分別查詢SELECT

'正式員工最高年齡',MAX(FAge)

FROMT_EmployeeUNION

ALLSELECT

'正式員工最低年齡',MIN(FAge)

FROMT_EmployeeUNION

ALLSELECT

'臨時工最高年齡',MAX(FAge)

FROMT_TempEmployeeUNION

ALLSELECT

'臨時工最低年齡',MIN(FAge)

FROMT_TempEmployee案例2查詢每位正式員工的信息，包括工號、工資，并且在最后一行加上所有員工工資額合計。SELECT

FNumber,FSalary

FROMT_EmployeeUNION

ALLSELECT

'工資合計',SUM(FSalary)FROM

T_Employee數(shù)字函數(shù)（*）執(zhí)行備注中的代碼ABS()：求絕對值。CEILING()：舍入到最大整數(shù)。3.33將被舍入為

4、2.89將被舍入為3、-3.61將被舍入為-3。Ceiling→天花板FLOOR()：舍入到最小整數(shù)。3.33將被舍入為3、2.89將被舍入為2、-3.61將被舍入為-4。Floor→地板。ROUND()：四舍五入。舍入到“離我半徑最近的數(shù)”。Round→“半徑”。Round(3.1425,2)。字符串函數(shù)（*）LEN()：計算字符串長度LOWER()、UPPER()：轉(zhuǎn)小寫、大寫LTRIM()：字符串左側(cè)的空格去掉RTRIM

()：字符串右側(cè)的空格去掉LTRIM(RTRIM('

bb

'))SUBSTRING(string,start_position,length)參數(shù)string為主字符串，start_position為子字符串在主字符串中的起始位置，length為子字符串的最大長度。SELECTSUBSTRING('abcdef111',2,3)日期函數(shù)GETDATE()

：取得當(dāng)前日期時間DATEADD

(datepart,number,date)，計算增加以后的日期。參數(shù)date為待計算的日期；參數(shù)number為增量；參數(shù)datepart為計量單位，可選值見備注。DATEADD(DAY,3,date)為計算日期date的3天后的日期，而

DATEADD(MONTH,-8,date)為計算日期date的8個月之前的日期DATEDIFF(datepart,startdate,enddate)：計算兩個日期之間的差額。datepart

為計量單位，可取值參考DateAdd。統(tǒng)計不同工齡的員工的個數(shù)：selectDateDiff(year,FInDate,getdate()),count(*)

from

T_Employeegroup

by

DateDiff(year,FInDate,getdate())DATEPART

(datepart,date)：返回一個日期的特定部分統(tǒng)計員工的入職年份個數(shù)：selectDatePart(year,FInDate),count(*)from

T_Employeegroup

by

DatePart(year,FInDate)類型轉(zhuǎn)換函數(shù)CAST

(

expression

AS

data_type)CONVERT

(

data_type,expression)SELECT

FIdNumber,RIGHT(FIdNumber,3)

as

后三位,CAST(RIGHT(FIdNumber,3)

AS

INTEGER)as

后三位的整數(shù)形式,CAST(RIGHT(FIdNumber,3)

AS

INTEGER)+1

as

后三位加1,CONVERT(INTEGER,RIGHT(FIdNumber,3))/2as后三位除以2FROM

T_Person空值處理函數(shù)執(zhí)行備注中的代碼ISNULL(expression,value)：如果

expression不為空則返回expression，否則返回value。SELECT

ISNULL(FName,'佚名')as

姓名FROM

T_EmployeeCASE函數(shù)用法1單值判斷，相當(dāng)于switchcaseCASE

expressionWHEN

value1

THEN

returnvalue1WHEN

value2

THEN

returnvalue2WHEN

value3

THEN

returnvalue3ELSE

defaultreturnvalueEND例子SELECTSELECTFName,(CASE

FLevel WHEN1

THEN'VIP客戶'WHEN

2

THEN'高級客戶'WHEN

3

THEN'普通客戶'ELSE'客戶類型錯誤'END)

as

FLevelNameFROM

T_CustomerCASE函數(shù)用法2測試數(shù)據(jù)在備注中CASEWHEN

condition1

THEN

returnvalue1WHEN

condition

2

THEN

returnvalue2WHEN

condition

3

THEN

returnvalue3ELSE

defaultreturnvalueEND相當(dāng)于if…else…else….例子：SELECTFName,FWeight,(CASEWHEN

FWeight<40

THEN‘瘦瘦'WHEN

FWeight>50

THEN‘肥肥'ELSE

'ok'END)

as

isnormalFROM

T_Person練習(xí)1表中有A

BC三列,用SQL語句實(shí)現(xiàn)：當(dāng)A列大于B列時選擇A列否則選擇B列，當(dāng)B列大于C列時選擇B列否則選擇C列。select(case

when

a>bthenaelsebend),(case

whenb>c

thenbelse

cend)from

t練習(xí)2單號金額Rk110Rk220Rk3-30Rk4-10將上面的表輸出為如下的格式：單號收入支出Rk1100Rk2200Rk3030Rk4010練習(xí)3有一張表T_Scores，記錄比賽成績reDateNameSco2008-8-8拜仁勝2008-8-9奇才勝2008-8-9湖人勝2008-8-10拜仁負(fù)2008-8-8拜仁負(fù)2008-8-12奇才勝要求輸出下面的格式：Name

勝

負(fù)拜仁12湖人10奇才20數(shù)據(jù)和參考答案見備注注意：在中文字符串前加N，比如N’勝’練習(xí)創(chuàng)建一張表，記錄電話呼叫員的工作流水，記錄呼叫員編號、對方號碼、通話開始時間、通話結(jié)束時間。建表、插數(shù)據(jù)等最后都自己寫SQL語句。要求：輸出所有數(shù)據(jù)中通話時間最長的5條記錄。orderby

datediff輸出所有數(shù)據(jù)中撥打長途號碼（對方號碼以0開頭）的總時長。like、sum輸出本月通話總時長最多的前三個呼叫員的編號。datediff(month....),sum,order

by輸出本月?lián)艽螂娫挻螖?shù)最多的前三個呼叫員的編號.group

by,count(*)輸出所有數(shù)據(jù)的撥號流水，并且在最后一行添加總呼叫時長呼叫員編號、對方號碼、通話時長.......匯總[市內(nèi)號碼總時長][長途號碼總時長]索引Index全表掃描：對數(shù)據(jù)進(jìn)行檢索（select）效率最差的是全表掃描，就是一條條的找。如果沒有目錄，查漢語字典就要一頁頁的翻，而有了目錄只要查詢目錄即可。為了提高檢索的速度，可以為經(jīng)常進(jìn)行檢索的列添加索引，相當(dāng)于創(chuàng)建目錄。創(chuàng)建索引的方式，在表設(shè)計器中點(diǎn)擊右鍵，選擇“索引/鍵”→添加→在列中選擇索引包含的列。使用索引能提高查詢效率，但是索引也是占據(jù)空間的，而且添加、更新、刪除數(shù)據(jù)的時候也需要同步更新索引，因此會降低

Insert、Update、Delete的速度。只在經(jīng)常檢索的字段上(Where)創(chuàng)建索引。（*）即使創(chuàng)建了索引，仍然有可能全表掃描，比如like、函數(shù)、類型轉(zhuǎn)換等。表連接Join有客戶表（T_Customers）和訂單表（T_Orders）兩個表，客戶表字段為：Id、Name、Age，訂單表字段為：Id、BillNo、CustomerId，訂單表通過CustomerId關(guān)聯(lián)客戶表。測試數(shù)據(jù)見備注。select

o.BillNo,c.Name,c.Agefrom

T_Orders

as

ojoin

T_Customers

as

c

on

o.CustomerId=c.Idjoin是和哪個表連接，on后是連接的關(guān)系是什么。要求顯示所有年齡大于15歲的顧客購買的訂單號、客戶姓名、客戶年齡。要求顯示年齡大于平均年齡的顧客購買的訂單（*）InnerJoin、Left

Join、Right

Join子查詢將一個查詢語句做為一個結(jié)果集供其他SQL語句使用，就像使用普通的表一樣，被當(dāng)作結(jié)果集的查詢語句被稱為子查詢。所有可以使用表的地方幾乎都可以使用子查詢來代替。SELECT

*

FROM(SELECT

*

FROMT2

where

FAge<30)執(zhí)行備注中的SQL。單值做為子查詢：SELECT

1

AS

f1,2,(SELECT

MIN(FYearPublished)FROM

T_Book),(SELECT

MAX(FYearPublished)FROM

T_Book)ASf4只有返回且僅返回一行、一列數(shù)據(jù)的子查詢才能當(dāng)成單值子查詢。下面的是錯誤的：SELECT

1

AS

f1,2,(SELECT

FYearPublished

FROMT_Book)SELECT

*

FROM

T_ReaderFavorite

WHERE

FCategoryId=(SELECTFId

FROM

T_Category

WHERE

FName='Story')子查詢?nèi)绻硬樵兪嵌嘈袉瘟械淖硬樵?，這樣的子查詢的結(jié)果集其實(shí)是一個集合。SELECT

*

FROM

T_ReaderWHERE

FYearOfJoin

IN(select

FYearPublished

FROM

T_Book)限制結(jié)果集。返回第3行到第5行的數(shù)據(jù)（

ROW_NUMBER不能用在where子句中，所以將帶行號的執(zhí)行結(jié)果作為子查詢，就可以將結(jié)果當(dāng)成表一樣用了）：SELECT

*

FROM(SELECT

ROW_NUMBER()

OVER(ORDER

BY

FSalary

DESC)

AS

rownum,FNumber,FName,FSalary,FAge

FROM

T_Employee)

AS

aWHERE

a.rownum>=3

ANDa.rownum<=5ADO.Net基礎(chǔ)程序要和數(shù)據(jù)庫交互要通過ADO.Net進(jìn)行，通過ADO.Net就能在程序中執(zhí)行SQL了。ADO.Net中提供了對各種不同數(shù)據(jù)庫的統(tǒng)一操作接口。直接在項(xiàng)目中內(nèi)嵌mdf文件的方式使用SQLServer數(shù)據(jù)庫（新建→數(shù)據(jù)→基于服務(wù)的數(shù)據(jù)庫）。mdf文件隨著項(xiàng)目走，用起來方便，和在數(shù)據(jù)庫服務(wù)器上創(chuàng)建數(shù)據(jù)庫沒什么區(qū)別，運(yùn)行的時候會自動附加（Attach）雙擊mdf文件會在“服務(wù)器資源管理器”中打開，管理方式和在

ManagementStudio沒有什么本質(zhì)不同。要拷貝mdf文件需要關(guān)閉所有指向mdf文件的連接。正式生產(chǎn)運(yùn)行的時候附加到SQLServer上、修改連接字符串即可，除此之外沒有任何的區(qū)別，在“數(shù)據(jù)庫”節(jié)點(diǎn)上點(diǎn)右鍵“附加”；在數(shù)據(jù)庫節(jié)點(diǎn)上→任務(wù)→分離就可以得到可以拷來拷去mdf文件。用的時候要在控制臺、WinForm項(xiàng)目中在Main函數(shù)最開始的位置加入備注中的代碼。ASP.Net項(xiàng)目中不需要?？赡苡龅降腻e誤1、由于啟動用戶實(shí)例的進(jìn)程時出錯，導(dǎo)致無法生成SQL

Server

的用戶實(shí)例2、版本太低，只支持2005及以下數(shù)據(jù)庫。解決：安裝VisualStudio

2008

SP13、啟動超時。多試幾次連接SQLServer連接字符串：程序通過連接字符串指定要連哪臺服務(wù)器上的、哪個實(shí)例的哪個數(shù)據(jù)庫、用什么用戶名密碼等。項(xiàng)目內(nèi)嵌mdf文件形式的連接字符串"DataSource=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated

Security=True;User

Instance=True"?！?\SQLEXPRESS”表示“本機(jī)上的SQLEXPRESS實(shí)例”，如果數(shù)據(jù)庫實(shí)例名不是SQLEXPRESS，則需要修改。

“Database1.mdf”為mdf的文件名。ADO.Net中通過SqlConnection類創(chuàng)建到SQLServer的連接，

SqlConnection代表一個數(shù)據(jù)庫連接，ADO.Net中的連接等資源都實(shí)現(xiàn)了IDisposable接口，可以使用using進(jìn)行資源管理。執(zhí)行備注中的代碼如果成功了就ok。執(zhí)行簡單的Insert語句SqlCommand表示向服務(wù)器提交的一個命令（SQL語句等）

,CommandText屬性為要執(zhí)行的SQL語句，ExecuteNonQuery方法執(zhí)行一個非查詢語句（Update、Insert、Delete等）using

(SqlCommand

cmd

=

conn.CreateCommand()){cmd.CommandText

=

"Insert

intoT_Users(UserName,Password)

values('admin','888888')";cmd.ExecuteNonQuery();}ExecuteNonQuery返回值是執(zhí)行的影響行數(shù)常犯錯：stringusername='test';....cmd.CommandText

=

"Insert

into

T_Users(UserName,Password)values(username,'888888')";ExecuteScalarSqlCommand的ExecuteScalar方法用于執(zhí)行查詢，并返回查詢所返回的結(jié)果集中第一行的第一列，因?yàn)椴荒艽_定返回值的類型，所以返回值是object類型。cmd.CommandText

=

"select

count(*)

from

T_Users";int

i

=Convert.ToInt32(cmd.ExecuteScalar())cmd.CommandText

=

"select

getdate()";

DateTime

dt

=Convert.ToDateTime(cmd.ExecuteScalar());得到自動增長字段的主鍵值，在values關(guān)鍵詞前加上outputinserted.Id，其中Id為主鍵字段名。執(zhí)行結(jié)果就試插入的主鍵值，用

ExecuteScalar執(zhí)行最方便。cmd.CommandText

=

"Insert

into

T_Users(UserName,Password)output

inserted.Id

values('admin','888888')";int

i

=

Convert.ToInt32(cmd.ExecuteScalar());執(zhí)行查詢執(zhí)行有多行結(jié)果集的用ExecuteReader SqlDataReader

reader=cmd.ExecuteReader();... while

(reader.Read()){

Console.WriteLine(reader.GetString(1));}reader的GetString、GetInt32等方法只接受整數(shù)參數(shù)，也就是序號，用GetOrdinal方法根據(jù)列名動態(tài)得到序號練習(xí)：控制臺登陸程序。為什么用using。Close：關(guān)閉以后還能打開。

Dispose：直接銷毀，不能再次使用。using在出了作用域以后調(diào)用Dispose，SqlConnection、FileStream等的Dispose內(nèi)部都會做這樣的判斷：判斷有沒有

close，如果沒有Close就先Close再Dispose。第一條之前最后一條之后SQL注入漏洞攻擊登錄判斷：select

*

from

T_Users

where

UserName=...andPassword=...，將參數(shù)拼到SQL語句中。構(gòu)造惡意的Password：'or

'1'='1if

(reader.Read()){Console.WriteLine("登錄成功");}else{Console.WriteLine("登錄失敗");}防范注入漏洞攻擊的方法：不使用SQL語句拼接，通過參數(shù)賦值查詢參數(shù)SQL語句使用@UserName表示“此處用參數(shù)代替”，向

SqlCommand的Parameters中添加參數(shù)cmd.CommandText

=

"select

*

from

T_Users

whereUserName=@UserName

and

Password=@Password";cmd.Parameters.Add(newSqlParameter("UserName","admin"));cmd.Parameters.Add(newSqlParameter("Password",password));參數(shù)在SQLServer內(nèi)部不是簡單的字符串替換，

SQLServer直接用添加的值進(jìn)行數(shù)據(jù)比較，因此不會有注入漏洞攻擊。案例用戶界面中進(jìn)行登錄判斷。輸錯三次禁止登陸，用數(shù)據(jù)庫記錄ErrorTimes。數(shù)據(jù)導(dǎo)入：從文本文件導(dǎo)入用戶信息。易錯點(diǎn)：Parameter的重復(fù)添加數(shù)據(jù)導(dǎo)出：將用戶信息導(dǎo)出到文本文件。案例省市選擇程序，數(shù)據(jù)全部來自于數(shù)據(jù)庫：把createtable中的varchar改為nvarchar，在Insert語句的漢字前面加上N（查找“,'”替換為“,N'”）ComboBox的顯示值：Items.Add的參數(shù)是Object類型，也就是可以放任意數(shù)據(jù)類型的數(shù)據(jù)，可以設(shè)置DisplayMember屬性設(shè)定顯示的屬性，通過SelectedItem屬性取得到就是選擇的條目對應(yīng)的對象。例子。疑問：取出來的是Object，怎么能轉(zhuǎn)換為對應(yīng)的類型？變量名只是“標(biāo)簽”。顯示的值和實(shí)際的對象不一樣，在ASP.Net中也有相同的東西創(chuàng)建一個ProvinceItem類，將數(shù)據(jù)填充在這個對象中添加到ComboBox中。將連接字符串寫在代碼中的缺點(diǎn)：多次重復(fù)，違反了DRY（Don't

Repeat

Yourself）原則；如果要修改連接字符串就要修改代碼。將連接字符串寫在App.Config中：添加App.config文件（文件名不能改）：添加→新建項(xiàng)→常規(guī)→應(yīng)用程序配置文件。App.config是.Net的通用配置文件，在ASP.Net中也能同樣使用。在App.config中添加connectionStrings段，添加一個add項(xiàng)，用name屬性起一個名字（比如DbConnStr），connectionString屬性指定連接字符串。在“引用”節(jié)點(diǎn)上點(diǎn)右鍵“添加引用”，找到System.configuration。不是所有.Net中的類都能直接調(diào)用，類所在的Assembly要被添加到項(xiàng)目的引用中才可以。ConfigurationManager.ConnectionStrings["

DbConnStr

"].ConnectionString得到連接字符串。如何在部署的程序中修改配置。案例IP地址歸屬地查詢。查詢結(jié)果：山東移動[菏澤]。打開文件文件夾選擇對話框FolderBrowserDialog；按照通配符搜索目錄下的文件string[]

Directory.GetFiles(stringpath,string

searchPattern,SearchOption

searchOption)Path.GetFileNameWithoutExtension(filename)，得到文件的文件名（不要擴(kuò)展名）；Path.Combine(stringpath1,string

path2)，將兩個路徑合并；Path.

GetExtension(string

path)，得到文件的后綴；Path.

GetFileName(string

path)，得到文件的文件名；

Path.GetFullPath(string

path)：得到文件的全路徑。導(dǎo)入前先清除舊數(shù)據(jù)。如果看不到數(shù)據(jù)可能是沒放Main中的代碼到上找手機(jī)號測試。DataSet每次讀取數(shù)據(jù)都創(chuàng)建連接、執(zhí)行Command得到SqlDataReader太麻煩，讓我們封裝一個方法吧！SqlDataReader是連接相關(guān)的，SqlDataReader中的查詢結(jié)果并不是放到程序中的，而是放在數(shù)據(jù)庫服務(wù)器中，SqlDataReader只是相當(dāng)于放了一個指針（游標(biāo)），只能讀取當(dāng)前游標(biāo)指向的行，一旦連接斷開就不能再讀取。這樣做的好處就是無論查詢結(jié)果有多少條，對程序占用的內(nèi)存都幾乎沒有影響。SqlDataReader對于小數(shù)據(jù)量的數(shù)據(jù)來說帶來的只有麻煩，優(yōu)點(diǎn)可以忽略不計。ADO.Net中提供了數(shù)據(jù)集的機(jī)制，將查詢結(jié)果填充到本地內(nèi)存中，這樣連接斷開、服務(wù)器斷開都不影響數(shù)據(jù)的讀取。DataSet

dataset

=

new

DataSet();

SqlDataAdapter

adapter

=

newSqlDataAdapter(cmd);

adapter.Fill(dataset);SqlDataAdapter是DataSet和數(shù)據(jù)庫之間溝通的橋梁。數(shù)據(jù)集DataSet包含若干表DataTable，DataTable包含若干行DataRow。foreach(DataRow

row

in

dataset.Tables[0].Rows)row["Name"]。SQLHelper封裝一個SQLHelper類方便使用，提供ExecuteDataTable(stringsql,params

SqlParameter[]

parameters)、ExecuteNonQuery(string

sql,paramsSqlParameter[]parameters)、ExecuteScalar(string

sql,params

SqlParameter[]parameters)等方法。網(wǎng)上有微軟提供的最全的SQLHelper類，是Enterprise

Library中的一部分。用SQLHelper重寫登錄程序練習(xí)：用SQLHelper重寫省市選擇程序、IP地址歸屬地查詢new

SqlParameter("e",0)的陷阱sqlconnection在程序中一直保持它open可以嗎？對于數(shù)據(jù)庫來說，連接是非常寶貴的資源，一定要用完了就close、dispose。DataSet的更新可以更新行row["Name"]="yzk"、刪除行datatable.Rows.Remove()、新增行datatable.NewRow()。這一切都是修改的內(nèi)存中的DataSet，沒有修改數(shù)據(jù)庫。可以調(diào)用SqlDataAdapter的Update方法將對DataSet的修改提交到數(shù)據(jù)庫，Update方法有很多重載方法，可以提交整個DataSet、DataTable或者若干DataRow。但是需要為SqlDataAdapter提供DeleteCommand、

UpdateCommand、InsertCommand它才知道如何將對DataSet的修改提交到數(shù)據(jù)庫，由于這幾個Command要求的格式非常苛刻，因此開發(fā)人員自己寫非常困難，可以用SqlCommandBuilder自動生成這幾個

Command，用法很簡單：new

SqlCommandBuilder(adapter)。查看生成的Command（沒有直接賦值給SqlDataAdapter

，看

SqlCommandBuilder的）。SqlCommandBuilder要求表必須有主鍵。(*)通過DataRow的RowState可以獲得行的狀態(tài)（刪除、修改、新增等）；調(diào)用DataSet的GetChanges()方法得到變化的結(jié)果集，降低傳遞的資源占用。可空數(shù)據(jù)類型C#中值類型（int、Guid、bool等）是不可以為空的，inti=null是錯誤的，因此int、bool等這些類型不能表示數(shù)據(jù)庫中的“Null”

。因此C#提供了“可空類型”這種語法，只要在類型后加?就構(gòu)成了可空的數(shù)據(jù)類型，比如int?、bool?，這樣int?

i=null

就可以了。解決數(shù)據(jù)庫中int可以為null，而

C#中int不能為null的問題。判斷可空類型是否為空，i==null或者i.HasValue；得到可空變量的值，int

i1=(int)i.Value或者int

i1=i.Value。類型轉(zhuǎn)換：不可空類型賦值給可空類型無需顯式轉(zhuǎn)換（一定成功），可空類型賦值給不可空類型則需顯式轉(zhuǎn)換（不一定成功）。弱類型DataSet的缺點(diǎn)只能通過列名引用，dataset.Tables[0].Rows[0][“Age”]，如果寫錯了列名編譯時不會發(fā)現(xiàn)錯誤，因此開發(fā)時必須要記著列名。intage=Convert.ToInt32(dataset.Rows[0][“Age”])，取到的字段的值是object類型，必須小心翼翼的進(jìn)行類型轉(zhuǎn)換，不僅麻煩，而且容易出錯。將DataSet傳遞給其他使用者，使用者很難識別出有哪些列可以供使用運(yùn)行時才能知道所有列名，數(shù)據(jù)綁定麻煩，無法使用Winform、ASP.Net的快速開發(fā)功能。自己動手寫強(qiáng)類型DataSet(類型化DataSet，TypedDataSet)，創(chuàng)建繼承自DataSet的PersonDataSet類，封裝出int?Age等屬性和bool

IsAgeNull等方法，向PersonDataSet中填充。VS自動生成強(qiáng)類型DataSet添加→新建項(xiàng)→數(shù)據(jù)集將表從服務(wù)器資源管理器拖放到DataSet中。注意拖放過程是自動根據(jù)表結(jié)構(gòu)生成強(qiáng)類型DataSet等類，沒有把數(shù)據(jù)也拖過來，程序還是連的那個數(shù)據(jù)庫，自動將數(shù)據(jù)庫連接字符串寫在了App.Config中。代碼中使用DataSet示例：CC_RecordTableAdapter

adapter=newCC_RecordTableAdapter();如何得知Adapter的類名？選中DataSet中下半部分的Adapter，Name屬性就是類名。需要右鍵點(diǎn)擊類名→解析取得所有的數(shù)據(jù)：adapter.GetData()，例子程序：遍歷顯示所有數(shù)據(jù)，i<adapter.GetData().Count;adapter.GetData()[i].Age。常見問題：類名敲不對，表名+TableAdapter，表名+DataTable，表名+Row，然后用“解析”來填充類名，別照著我的代碼敲。常見問題：類的內(nèi)部定義的類要通過包含namespace的全名來引用，不能省略。類的內(nèi)部定義的類就能避免同一個namespace下類不能重名的問題。強(qiáng)類型DataSet其實(shí)就是一種代碼生成器的實(shí)現(xiàn)機(jī)制（DataSetPersons.Designer.cs），調(diào)用的***TableAdapter等類都是VS自動生成的，可以看到的，不要手動改生成的類代碼，改xsd即可。GetData和Fill的區(qū)別。強(qiáng)類型強(qiáng)在哪？像使用類的屬性一樣使用列名，dsPerson[0].Age，可以使用VS的自動提示功能，絕對不會寫錯列名，寫錯了編譯通不過。將強(qiáng)類型DataSet傳遞給其他人，使用者可以輕松確定有哪些列intage=dsPerson[0].Age，列名的類型是明確的，避免類型轉(zhuǎn)換的麻煩。編譯時就可以確定名詞：強(qiáng)類型DataSet（類型化DataSet），英文：TypedDataSet。DataSet包含DataTable、DataTable包含DataRow，強(qiáng)類型

DataSet同樣如此。查看源代碼看看VS幫我們做了什么GetData返回是什么類型？每一行是什么類型？看類型定義即可得知。一般規(guī)律：表類型名：表名+DataTable，行類型名：表名+row，忘了也沒關(guān)系：“轉(zhuǎn)到定義”。更新DataSet調(diào)用Adapter的Update方法就可以將DataSet的改變保存到數(shù)據(jù)庫。adapter.Update(datatable)要調(diào)用Update方法更新必須設(shè)置數(shù)據(jù)庫主鍵，后面的Delete也是如此。常見錯誤：“當(dāng)傳遞具有已修改行的DataRow集合時，更新要求有效的UpdateCommand”，要為表設(shè)置主鍵?！罢l都變了，唯有主鍵不會變”，程序要通過主鍵來定位要更新的行。忘了設(shè)主鍵怎么辦？先到數(shù)據(jù)庫中設(shè)置主鍵，然后在DataSet的對應(yīng)DataTable上點(diǎn)右鍵，選擇“配置”，在對話框中點(diǎn)擊【完成】。好習(xí)慣：所有表都要設(shè)置主鍵?。?！看看為什么會自動幫我們GetData、Update、Delete。其他問題插入新行，調(diào)用Insert方法。增加字段怎么辦？DataSet設(shè)計器中點(diǎn)【配置】，對話框中點(diǎn)【查詢生成器】，勾選新增加的字段即可。刪除字段同樣如此。如果是高手也可以直接手改SQL語句。要修改字段就要重新配置生成，這就是強(qiáng)類型DataSet的弱點(diǎn)，因此強(qiáng)類型DataSet不一定真的就是“強(qiáng)”，還是叫“類型化

DataSet”(Typed

DataSet)吧常見錯誤：報錯：數(shù)據(jù)為空。判斷列的值為空的方法：Is**Null為什么Select方法會填充、Update方法會更新，Insert方法會插入？沒有多么神奇，看看Adapter的SelectCommand等屬性，是那些SQL語句在起作用，如果有需要完全可以手工調(diào)整。增加新的SQL語句設(shè)計器的Adapter中點(diǎn)右鍵，選擇“添加查詢”