SQL數(shù)據(jù)庫(kù)學(xué)習(xí)筆記與心得

SQL數(shù)據(jù)庫(kù)學(xué)習(xí)筆記與心得

0961140107

SQL是市場(chǎng)占有率最大的數(shù)據(jù)庫(kù)之一，是信息化社會(huì)的產(chǎn)物，是整理、查詢、分析數(shù)據(jù)的強(qiáng)有力工具。我對(duì)于SQL數(shù)據(jù)庫(kù)的學(xué)習(xí)是在實(shí)驗(yàn)與探索之中度過(guò)的。

下面將我本學(xué)期所學(xué)的主要知識(shí)簡(jiǎn)單總結(jié)如下：

共分四大塊：一、數(shù)據(jù)庫(kù)基本原理；二、SQL標(biāo)準(zhǔn)語(yǔ)言；三、數(shù)據(jù)庫(kù)設(shè)計(jì)；四、數(shù)據(jù)庫(kù)安全。

一、數(shù)據(jù)庫(kù)基本原理

數(shù)據(jù)庫(kù)技術(shù)是計(jì)算機(jī)領(lǐng)域中發(fā)展最快的技術(shù)之一。數(shù)據(jù)模型是數(shù)據(jù)庫(kù)系統(tǒng)的核心和基礎(chǔ)。常用的數(shù)據(jù)模型有層次模型、網(wǎng)狀模型、關(guān)系模型、面向?qū)ο竽Ｐ�、�?duì)象關(guān)系模型。其中關(guān)系模型是目前最重要的一種數(shù)據(jù)模型。關(guān)系數(shù)據(jù)庫(kù)是我們需要掌握的重點(diǎn)。

關(guān)系模型中常用的關(guān)系操作包括：查詢操作和插入、刪除、修改操作兩大部分。關(guān)系的查詢表達(dá)能力很強(qiáng)，是關(guān)系操作中最主要的部分。

二、SQL標(biāo)準(zhǔn)語(yǔ)言

SQL（StructuredQueryLanguage），即結(jié)構(gòu)化查詢語(yǔ)言，是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。SQL標(biāo)準(zhǔn)語(yǔ)言從1986年公布以來(lái)隨著數(shù)據(jù)庫(kù)技術(shù)的發(fā)展不斷發(fā)展，不斷豐富。

SQL功能動(dòng)詞

數(shù)據(jù)定義CREATE，DROP，ALTER數(shù)據(jù)查詢SELECT

數(shù)據(jù)操縱INSERT，UPDATE，DELETE數(shù)據(jù)控制GRANT，REVOKE

SQL之所以能夠?yàn)橛脩艉蜆I(yè)界所接受，并成為國(guó)際標(biāo)準(zhǔn)，是因?yàn)樗�是一個(gè)綜合的、功能極強(qiáng)同時(shí)又簡(jiǎn)潔易學(xué)的語(yǔ)言。SQL集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義

林巧和數(shù)據(jù)控制功能于一體。有如下特點(diǎn)：1）綜合統(tǒng)一；2）高度過(guò)程化；3)面向集合的操作方式；4）以同一種語(yǔ)法結(jié)構(gòu)提供多種使用方式；5）語(yǔ)言簡(jiǎn)潔，易學(xué)易用。

SQL的數(shù)據(jù)定義語(yǔ)句：

創(chuàng)建刪除修改表CREATETABLEDROPTABLEALTERTABLE視圖CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX

數(shù)據(jù)庫(kù)查詢是數(shù)據(jù)庫(kù)的核心操作。也是數(shù)據(jù)庫(kù)學(xué)習(xí)的重點(diǎn)和難點(diǎn)。SQL提供了SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)的查詢,該語(yǔ)句具有靈活的使用方式和豐富的功能。其一般格式為：

SELECT[ALL|DISTINCT]FROM[，]…[WHERE]

[GROUPBY[HAVING]][ORDERBY[ASC|DESC]]；

SELECT語(yǔ)句既可以完成簡(jiǎn)單的單表查詢，也可以完成復(fù)雜的連接查詢和嵌套查詢。

(一)、單表查詢

1、選擇表中的若干列：2、選擇表中的若干元組;

2）查詢滿足條件的元組。WHERE子句常用的查詢條件有3、ORDERBY子句。4、聚集函數(shù)。5、ORDERBY子句(二)、連接查詢

1、等值與非等值連接查詢；2、自然連接；3、外連接；4、復(fù)合條件連接。(三)、嵌套查詢1、帶有IN謂語(yǔ)的子查詢；2、帶有比較運(yùn)算符的子查詢；3、帶有ANY(SOME)或ALL謂語(yǔ)的子查詢；4、帶有EXISTS謂詞的子查詢。

(四)、集合查詢。

數(shù)據(jù)的更新操作有三種：向表中添加若干行數(shù)據(jù)、修改表中的數(shù)據(jù)和刪除表中的若干行數(shù)據(jù)。

三、數(shù)據(jù)庫(kù)設(shè)計(jì)

數(shù)據(jù)庫(kù)設(shè)計(jì)是指對(duì)于一個(gè)給定的應(yīng)用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)庫(kù)模式，建立數(shù)據(jù)庫(kù)及其應(yīng)用系統(tǒng)，使之能夠有效地存儲(chǔ)數(shù)據(jù)，滿足各種用戶的應(yīng)用需求（信息要求和處理要求

數(shù)據(jù)庫(kù)設(shè)計(jì)的基本步驟：

1、數(shù)據(jù)庫(kù)設(shè)計(jì)的準(zhǔn)備工作；2、數(shù)據(jù)庫(kù)設(shè)計(jì)的過(guò)程(六個(gè)階段)：1）需求分析階段；2）概念結(jié)構(gòu)設(shè)計(jì)階段；3）邏輯結(jié)構(gòu)設(shè)計(jì)階段；4）數(shù)據(jù)庫(kù)物理設(shè)計(jì)階段；5）數(shù)據(jù)庫(kù)實(shí)施階段；6）數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)階段．

四、數(shù)據(jù)庫(kù)安全

數(shù)據(jù)庫(kù)的安全性是指保護(hù)數(shù)據(jù)庫(kù)，防止因用戶非法使用數(shù)據(jù)庫(kù)造成數(shù)據(jù)泄露、更改或破壞。課本中講述了三類計(jì)算機(jī)系統(tǒng)安全性問(wèn)題：技術(shù)安全類、管理安全類和政策法律類。

如何控制數(shù)據(jù)庫(kù)的安全性?其常用的方法有:1)用戶標(biāo)識(shí)和鑒定2）存取控制;3)視圖;4)審計(jì);5)密碼存儲(chǔ)．

數(shù)據(jù)庫(kù)的完整性：即數(shù)據(jù)的正確性和相容性防止不合語(yǔ)義的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)。例如:學(xué)生的年齡必須是整數(shù)，取值范圍為14--29；學(xué)生的性別只能是男或女；學(xué)生的學(xué)號(hào)一定是唯一的；學(xué)生所在的系必須是學(xué)校開設(shè)的系；

以上是我對(duì)SQL數(shù)據(jù)庫(kù)重點(diǎn)知識(shí)的理解。通過(guò)學(xué)習(xí)本課程和上機(jī)實(shí)驗(yàn)，我了解了數(shù)據(jù)庫(kù)的概念及其重要性，還有其今后的發(fā)展方向，這將有利于我今后更好地把握時(shí)代之脈搏。

擴(kuò)展閱讀：SQLServer數(shù)據(jù)庫(kù)學(xué)習(xí)筆記

數(shù)據(jù)庫(kù)

一、數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)

1、發(fā)展階段

萌芽期（文件系統(tǒng)）初級(jí)階段（層次模型和網(wǎng)狀模型）主要代表為IBM公司的IMS（信息管理系統(tǒng)）中級(jí)階段（關(guān)系型數(shù)據(jù)庫(kù)）主要代表為甲骨文公司Oracle，微軟SQLServer和IBM的DB2高級(jí)階段（關(guān)系對(duì)象型數(shù)據(jù)庫(kù)）2、數(shù)據(jù)模型

層次模型：結(jié)構(gòu)型數(shù)據(jù)庫(kù)主要數(shù)據(jù)模型，代表數(shù)據(jù)庫(kù)為IMS

網(wǎng)狀模型：構(gòu)成網(wǎng)狀數(shù)據(jù)庫(kù)的主要數(shù)據(jù)模型

關(guān)系模型：采用二維表結(jié)構(gòu)采用關(guān)系模型組織數(shù)據(jù)查詢效率有時(shí)不高面向?qū)ο竽Ｐ停?、數(shù)據(jù)庫(kù)系統(tǒng)結(jié)構(gòu)（1）用戶級(jí)數(shù)據(jù)庫(kù)：外模式（2）概念機(jī)數(shù)據(jù)庫(kù)：模式（3）烏力吉數(shù)據(jù)庫(kù)：內(nèi)模式，最接近數(shù)據(jù)的物理存儲(chǔ)與組織級(jí)別4、數(shù)據(jù)庫(kù)三模式（1）外模式：模式的子集，應(yīng)用程序都是和外模式打交道（2）模式：（3）內(nèi)模式：只有一個(gè)內(nèi)模式

5、數(shù)據(jù)庫(kù)的二級(jí)映像（1）外模式/模式映像：保障物理獨(dú)立性（2）模式/內(nèi)模式映像：保證邏輯獨(dú)立性6、關(guān)系模型完整性約束實(shí)體完整性（所有主屬性都不能取空值）；參照完整性（多個(gè)表之間）；用戶自定義的完整性7、SQL：（結(jié)構(gòu)化查詢語(yǔ)言）T-SQL：SQLServer，包括數(shù)據(jù)定義語(yǔ)言，數(shù)據(jù)操作語(yǔ)言，數(shù)據(jù)查詢語(yǔ)言，數(shù)據(jù)控制語(yǔ)言PL_SQL：Oracle，聲明，執(zhí)行體開始，異常處理，執(zhí)行體結(jié)束。二、SQLServer數(shù)據(jù)庫(kù)基礎(chǔ)1、系統(tǒng)數(shù)據(jù)庫(kù)：包括Resource數(shù)據(jù)庫(kù)（只讀數(shù)據(jù)庫(kù)）、master數(shù)據(jù)庫(kù)、msdb數(shù)據(jù)庫(kù)、tempdb數(shù)據(jù)庫(kù)、model數(shù)據(jù)庫(kù)（數(shù)據(jù)庫(kù)模板）；系統(tǒng)數(shù)據(jù)庫(kù)存儲(chǔ)在隱藏的系統(tǒng)表中2、用戶數(shù)據(jù)庫(kù)：數(shù)據(jù)庫(kù)的存儲(chǔ)機(jī)構(gòu)包括邏輯存儲(chǔ)結(jié)構(gòu)和物理存儲(chǔ)結(jié)構(gòu)SQLServer數(shù)據(jù)文件類型：主數(shù)據(jù)文件（mdf文件，只能有一個(gè)）、輔助數(shù)據(jù)文件（ndf，可以沒(méi)有，也可以有多個(gè)）文件、日志文件（ldf，記錄對(duì)數(shù)據(jù)庫(kù)記錄的操作，至少有一個(gè)，可以有多個(gè)）；數(shù)據(jù)存儲(chǔ)的基本單位是“頁(yè)”，一個(gè)頁(yè)的大小為8Kb，一個(gè)區(qū)有8個(gè)頁(yè)。（1）創(chuàng)建數(shù)據(jù)庫(kù)名稱遵循規(guī)則：第一個(gè)字符必須是字母或“_”“@”“#”；數(shù)據(jù)庫(kù)名稱不能是Transaction-SQL的保留字；不允許嵌入空格或其他特殊字符。

USEmaster

ifexists(select*fromsysdatabaseswherename="mytest")dropdatabasemytestcreatedatabasemytestonprimary(

name="mytest",

filename="D:\\ProgramFiles\\SQLServerData\\SQLServerData\\Data\\mytest.mdf",size=5MB,maxsize=100MB,filegrowth=15%),(

name="mytest1",

filename="D:\\ProgramFiles\\SQLServerData\\SQLServerData\\Data\\mytest1.mdf",size=5MB,maxsize=100MB,filegrowth=15%)logon(name="mytest_log",

filename="D:\\ProgramFiles\\SQLServerData\\SQLServerData\\Data\\mytest_log.ldf",size=2MB,filegrowth=1MB)

Go

（2）刪除數(shù)據(jù)庫(kù)

DROPDATABASE數(shù)據(jù)庫(kù)名（3）收縮數(shù)據(jù)庫(kù)

手動(dòng)收縮和自動(dòng)收縮

3、數(shù)據(jù)表數(shù)據(jù)完整性：實(shí)體完整性約束，表中不能存在相同的數(shù)據(jù)項(xiàng)；域完整性，給定列的輸入內(nèi)容具有有效性；引用完整性，保存表之間的定義關(guān)系；自定義完整性主鍵：一個(gè)表只能有一個(gè)主鍵，可以沒(méi)有主鍵，選擇時(shí)，要確保最少性和穩(wěn)定性外鍵：確保子表中的數(shù)據(jù)對(duì)應(yīng)主表中的主鍵或者唯一鍵常用數(shù)據(jù)類型：

數(shù)字類型：int，float，decimal（必須制定范圍和精度）文本類型：char，varchar，nvarchar，textBit數(shù)據(jù)類型：bool

日期時(shí)間類型：datatime貨幣型數(shù)據(jù)：money（1）創(chuàng)建數(shù)據(jù)表（2）刪除數(shù)據(jù)表USETrainingBase

go

ifexists(select*fromsysobjectswherename="Trainee")droptableTraineecreatetableTrainee()

TraineeNointNOTNULL,

TraineeNamenvarchar(50)notnull,Sexbitnotnull,GradeIDintnotnull,Phonenvarchar(50)null,Addressnvarchar(255)null,BornDatedatetimenotnull,Emailnvarchar(50)null,

IdentityCardvarchar(18)notnull

（3）創(chuàng)建刪除約束主鍵約束：主鍵值必須唯一ALTERTABLETraineeADDCONSTRANINTPK_TraineePRIMARYKEY(TraineeNo)

非空約束：

ALTERTABLETrainee

ADDCONSTRANINTPK_TraineePRIMARYKEY(TraineeNo)唯一約束：

ALTERTABLETrainee

ADDCONSTRANINTUQ_IdentityCardUNIQUE(IdentityCard)檢查約束：

ALTERTABLETrainee

ADDCONSTRANINTCK_BornDateCHECK(BornDate>’1980-1-1’)

默認(rèn)約束：

ALTERTABLETrainee

ADDCONSTRANINTDF_AddressDEFAULT(‘地址不詳’)forAddress外鍵約束：

ALTERTABLETrainee

ADDCONSTRANINTFK_GradeFOREIGNKEY(GradeId)REFERENCEGradeId4、SQLServer身份驗(yàn)證（1）Windows身份驗(yàn)證（更安全）（2）SQLServer身份驗(yàn)證（Windows身份驗(yàn)證改為SQLServer身份驗(yàn)證的方式：首先，屬性更改，然后改安全性中的內(nèi)容）5、訪問(wèn)權(quán)限對(duì)數(shù)據(jù)庫(kù)服務(wù)器控制權(quán)限對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)控制權(quán)限添加用戶6、導(dǎo)入導(dǎo)出數(shù)據(jù)7、數(shù)據(jù)庫(kù)優(yōu)化（1）調(diào)整數(shù)據(jù)庫(kù)結(jié)構(gòu)的設(shè)計(jì)（2）調(diào)整應(yīng)用程序機(jī)構(gòu)設(shè)計(jì)（3）調(diào)整數(shù)據(jù)庫(kù)SQL語(yǔ)句（4）調(diào)整服務(wù)器內(nèi)存分配避免多表連接查詢使用同一的SQL語(yǔ)句規(guī)范

批量導(dǎo)入數(shù)據(jù)時(shí)，設(shè)置恢復(fù)模式為“大容量日志恢復(fù)模式”，導(dǎo)入前禁用索引。

一、T_SQL

1、運(yùn)算符算術(shù)運(yùn)算符：+、-、*、/，%賦值運(yùn)算符：=比較運(yùn)算符：，=，!=，=邏輯運(yùn)算符：AND，OR，NOT2、表操作插入：一般插入一行，INSERTINTOGrade[GradeName]VALUES("實(shí)習(xí)階段")，

可用關(guān)鍵字DEFAULT表示用默認(rèn)值

修改：可更新一行數(shù)據(jù)，也可更新多行數(shù)據(jù)，也可能一行數(shù)據(jù)都不更新，UPDATE表明SET（字段名）=更新值[WHERE更新條件]UPDATEResultSETTraineeResult=TraineeResult+5WHERETraineeResult<90ANDSubjectNO=2ANDExamDate=‘201*-2-15’

刪除：DELETE[FROM]表名WHERE，刪除數(shù)據(jù)行數(shù)與WHERE刪除條件確定的行數(shù)相關(guān)，刪除有外鍵關(guān)系的數(shù)據(jù)時(shí)，必須先把外鍵表中的數(shù)據(jù)刪除掉：DELETEFROMTraineeWHERETraineeName=‘張慶艷’，TRUNCATETABLE表名，數(shù)據(jù)不能恢復(fù)，效率更高，刪除所有行數(shù)，但不刪除表結(jié)構(gòu)、索引、關(guān)系等，不能刪除有外鍵的表3、數(shù)據(jù)查詢（1）簡(jiǎn)單的查詢語(yǔ)句：全部查詢：SELECT*FROM表名（盡量少使用，效率比較低）；條件查詢：SELECT[字段名，字段名，字段名]FROM表名[WHERE查詢條件]使用別名：SELECT列名AS列的別名FROM表名；SELECT列名列的別名FROM表名；SELECT列的別名=列名FROM表名（使用中文別名時(shí)，可不用引號(hào)，但不能使用全角引號(hào)；使用的英文別名超過(guò)兩個(gè)單詞時(shí)，必須使用引號(hào)將別名括起來(lái)）查詢中使用常量列：SELECT姓名=TraineeName，地址=Address，‘河北新龍’AS實(shí)習(xí)地點(diǎn)FROMTrainee查詢空值：SELECT[字段名]FROM表名WHERE字段名IS(NOT)NULL限制查詢返回的行數(shù)：SELECTTOP數(shù)量字段名FROM表名WHERE條件；按百分比SELECTTOP數(shù)量PERCENT字段名FROM表名WHERE條件順序排列查詢結(jié)果：查詢語(yǔ)句ORDERBY字段名DESD/ASC

（2）查詢中使用函數(shù)：字符串函數(shù)：

日期函數(shù)：

數(shù)學(xué)函數(shù)：

系統(tǒng)函數(shù)：

（3）模糊查詢：通配符：替代字符，通配符必須加上LIKE

LIKE：僅與字符數(shù)據(jù)類型聯(lián)合使用

BETWEEN…AND：包含起始值和終止值，起始值不能大于終止值IN（NOTIN）：查詢匹配的記錄，SELECTTraineeNameAS實(shí)習(xí)生姓名FROMTraineeWHEREAddressIN（‘北京市海淀區(qū)’，‘廣州’，‘上海虹橋’）

聚合函數(shù)：基于列進(jìn)行計(jì)算SUM：求和，用于數(shù)字類型的列AVG：求平均值，所有數(shù)字的平均值MAX()和MIN():最值COUNT():求行數(shù)；COUNT(*)，檢索所有列；COUNT(1),檢所使用列；COUNT(列名)檢索指定列非空的行數(shù)分組查詢：使用GROUPBY子句實(shí)現(xiàn)分組統(tǒng)計(jì)，HAVING，對(duì)分組進(jìn)行篩選SELECTSubjectNo，AVG（TraineeResult）AS課程平均成績(jī)FROMResultGROUPBYSubjectNoHAVINGAVG(TraineeResult)>=60

（4）創(chuàng)建新表：SELECTINTO,新表必須不存在SELECTTrainee.TraineeName,Trainee.Address,Trainee.EmailINTONewAddressListFROMTraineeWHERE11（使查詢條件不成立）查詢示例：

SELECT*FROMTraineeASAWHEREA.TraineeNoIN(SELECTB.TraineeNoFROMResultBWHEREB.TraineeResult>80

ANDB.Subject=(SELECTC.SubjectNoFROMSubjectCWHEREUPPER(C.SubjectName)=’SQLSERVER’))

SELECT*FROMTraineeA

JOINResultBONA.TraineeNo=B.TraineeNoJOINSubjectCONC.SubjectNo=B.SubjectNoWHEREUPPER(C.SubjectName)=‘SQLServer’ANDB.TraineeResult>80

4、多表查詢（1）內(nèi)聯(lián)接：從兩個(gè)或兩個(gè)以上的表組合中挑選出符合聯(lián)接條件的數(shù)據(jù)，如果數(shù)據(jù)無(wú)法滿足聯(lián)接條件則將其丟棄，在內(nèi)聯(lián)接中，參與聯(lián)接的表的地位是平等的。

SELECT表名1.字段名，…，表名2.字段名，…FROM表名1，表名2WHERE表名1.字段名=表名2.字段名

SELECTTrainee.TraineeName,Result.SubjectNo,Result.ExamDate,Result.TraineeResultFROMTrainee,ResultWHERETrainee.TraineeNo=Result.TraineeNoSELECT表名1.字段名，…，表名2.字段名，…FROM表名1，表名2INNERJOIN表名2ON表名1.字段名=表名2.字段名

SELECTTrainee.TraineeName,Result.SubjectNo,Result.ExamDate,Result.TraineeResultFROMTraineeINNERJIONResultON(Trainee.TraineeNo=Result.TraineeNo)

（2）外聯(lián)結(jié)：外聯(lián)結(jié)中參與聯(lián)接的表有主從之分，以主表的每行數(shù)據(jù)匹配從表的數(shù)據(jù)列，將符合聯(lián)接條件的數(shù)據(jù)直接返回到結(jié)果集中；對(duì)那些不符合聯(lián)接條件的列，將被填上NULL值后再返回到結(jié)果集中。

左外聯(lián)接LEFTJOIN==LEFTOUTERJOIN

SELECTT.TraineeName,R.SubjectNo,R.TraineeResultFROMTrainee（主表）ASTLEFTJOINResultASRONT.TraineeNo=R.TraineeNo

右外聯(lián)接RIGHTJOIN==RIGHTOUTERJOIN

SELECTT.TraineeName,R.SubjectNo,R.TraineeResultFROMTraineeASTRIGHTJOINResult（主表）ASRONT.TraineeNo=R.TraineeNo（3）交叉連接5、使用Union合并多個(gè)查詢結(jié)果所有查詢的列數(shù)和列的順序必須相同；要合并的數(shù)據(jù)類型必須相同，至少也要可以轉(zhuǎn)換合并的查詢結(jié)果集的字段名稱以第一個(gè)查詢結(jié)果的字段名稱為名，其他的查詢集的字段名稱會(huì)被忽略SELECT查詢語(yǔ)句1UNION[ALL]SELECT查詢語(yǔ)句2三、數(shù)據(jù)查詢安全性和性能優(yōu)化1、SQL注入：將SQL代碼插入到應(yīng)用程序的輸入?yún)��?shù)中，之后，SQL代碼將被傳遞到數(shù)據(jù)庫(kù)執(zhí)行，從而達(dá)到對(duì)應(yīng)用程序的攻擊目的。原理：檢測(cè)注入點(diǎn)判斷數(shù)據(jù)庫(kù)類型傳遞惡意代碼上傳木馬，盜取賬號(hào)，獲取管理員權(quán)限，發(fā)現(xiàn)Web目錄防范：限制錯(cuò)誤信息的輸出；使用參數(shù)命令傳遞參數(shù)；使用存儲(chǔ)過(guò)程；限制輸入長(zhǎng)度；URL重寫技術(shù)；傳遞參數(shù)盡量不用字符串2、SQL優(yōu)化：低效SQL危害：系統(tǒng)響應(yīng)變慢（8秒定律）；死鎖；客戶失去信心，軟件失敗根源：硬件原因；沒(méi)有建索引，或者SQL沒(méi)有走索引；SQL過(guò)于復(fù)雜；頻繁訪問(wèn)數(shù)據(jù)庫(kù)SQL執(zhí)行原理：解釋解析優(yōu)化編譯執(zhí)行優(yōu)化SQL語(yǔ)句：完善開發(fā)管理；檢測(cè)SQL查詢的效率查詢SQL語(yǔ)句查詢時(shí)間

SETSTATISTICSIOONSETSTATASTICSTIMEON

清楚緩存

DBCCDROPCLEANBUFFERSDBCCFREEPROCCACHE

SQL優(yōu)化：避免對(duì)索引字段進(jìn)行數(shù)值操作；使用Top語(yǔ)句限制返回的數(shù)據(jù)集；SELECT字段需要多少提取多少；ORDERBY后跟的盡量是索引字段；使用存儲(chǔ)過(guò)程優(yōu)化。四、T-SQL程序1、變量全局變量：先聲明，后賦值，必須以@作為標(biāo)記前綴：DECLARE@variable_nameDateType;使用SET或者SELECT為變量賦值，SET一般用于賦給變量指定的數(shù)據(jù)常量，不支持多個(gè)變量賦值和表達(dá)式返回多個(gè)值，表達(dá)式未返回值時(shí)，變量被賦NULL值；SELECT從表中查詢數(shù)據(jù)，然后賦給變量，支持多個(gè)變量賦值，在返回多個(gè)值時(shí)，將返回最后一個(gè)值賦給變量，在表達(dá)式未返回值時(shí)，變量保持原值。SET語(yǔ)句執(zhí)行效率比SELECT語(yǔ)句要高局部變量：必須與@@標(biāo)志作為前綴

2、數(shù)據(jù)類型轉(zhuǎn)換使用CAST轉(zhuǎn)換數(shù)據(jù)類型：CAST（expression(有效表達(dá)式)ASdata_type[(length)]）SELECTGradeIdAS‘階段’,CAST(COUNT(GradeId)ASCHAR(2))+‘人’AS人數(shù)FROMTraineeGROUPBYGradeId使用CONVERT轉(zhuǎn)換數(shù)據(jù)類型：CONVERT(data_type[(length)],expression,[style])3、邏輯控制語(yǔ)句：順序結(jié)構(gòu)控制語(yǔ)句：BEGIN-END語(yǔ)句BEGIN

{Sql_statement}END

分支結(jié)構(gòu)控制語(yǔ)句：IF-ELSE語(yǔ)句和CASE-END語(yǔ)句IF(Boolean-expression)

{sql_statement}ELSE

{sql_statement}

CASE

WHEN條件1THEN結(jié)果1WHEN條件2THEN結(jié)果2[ELSE其他結(jié)果]END

循環(huán)控制語(yǔ)句：WHILE語(yǔ)句WHILE(Boolean_expression)BEGIN{Sql_statement|statement_blockBREAK|CONTINUE

}

END4、批處理：包含一個(gè)或多個(gè)SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行，GO是批處理的標(biāo)志，GO命令和SQL語(yǔ)句不能在同一行里，必須單起一行，表示SQLServer將這些T-SQL語(yǔ)句編譯為一個(gè)執(zhí)行單元，提高執(zhí)行效率：SQLServer規(guī)定，如果是建庫(kù)、建表語(yǔ)句、以及存儲(chǔ)過(guò)程和視圖等，必須在語(yǔ)句末尾添加添加GO批處理標(biāo)志5、聯(lián)合查詢

6、子查詢：SELECT（UPDATE、INSERT、DELETE）FROM表1WHERE列1運(yùn)算符(子查詢)，嵌套查詢先從最內(nèi)層子查詢開始分析,子查詢可以嵌套在SQL語(yǔ)句中任何表達(dá)式出現(xiàn)的位置子查詢語(yǔ)句必須放置在括號(hào)內(nèi)，將子查詢與比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個(gè)SELECTTraineeNo,TraineeName,Sex,BornDate,AddressFromTraineeWHEREBornDate>(SELECTBornDateFROMTraineeWHERETraineeName=‘Lily’)IN、NOTIN子查詢：IN后面的子查詢可以返回多條記錄，常用IN替換等于（=）的比較子查詢

SELECTTraineeNameFROMTraineeWHERETraineeNoIN(SELECTTraineeNoFROMResultWHERESubjectNo=

(

SELECTSubjectNoFROMSubjectWHERESubjectName=‘SQLServer’)ANDTraineeResult)

EXISTS：常用與IFEXISTS（子查詢），結(jié)果非空則EXISTS返回true,否則返回false相關(guān)子查詢：父查詢對(duì)子查詢產(chǎn)生影響ALL、ANY、SOME子查詢：ALL:父查詢中大于子查詢中所有記錄

SELECTSubjectName科目名稱,ClassHour學(xué)時(shí)FROMSubjectWHEREClassHuor>ALL(

SELECTAVG(ClassHour)FROMSubjectGROUPBYGradeId)

SOME、ANY：父查詢中大于子查詢中任意一條記錄即可

SELECTSubjectName科目名稱,ClassHour學(xué)時(shí)FROMSubjectWHEREClassHuor>ALL(

SELECTAVG(ClassHour)FROMSubjectGROUPBYGradeId)

8、事務(wù)處理事務(wù)：一系列的人物組成的邏輯工作單元，這個(gè)邏輯單元中的所有任務(wù)必須作為一個(gè)整體要么全部完成，要么全部失敗，保證數(shù)據(jù)完整性和數(shù)據(jù)可恢復(fù)性。事務(wù)特性：原子性，事務(wù)的各步操作是不可分的；一致性，但事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致狀態(tài)；隔離性，事務(wù)必須是獨(dú)立的，不應(yīng)以任何方式依賴于或影響其他事務(wù)；永久性，事務(wù)完成后，他對(duì)數(shù)據(jù)庫(kù)的修改被永久保持，事務(wù)日志能夠保持事務(wù)的永久性；開始事務(wù)：BEGINTRANSACTION；提交事務(wù)：COMMITTRANSACTION；回滾事務(wù)：ROLLBACKTRANSACTION；編寫事務(wù)原則：事務(wù)盡量簡(jiǎn)短；事務(wù)訪問(wèn)的數(shù)據(jù)量盡量最少；查詢數(shù)據(jù)時(shí)盡量不要使用事務(wù)；在事務(wù)處理過(guò)程中盡量不要出現(xiàn)等待用戶輸入的操作嵌套事務(wù)：事務(wù)的內(nèi)部可以包含其他事務(wù)；忽略內(nèi)層事務(wù)的COMMIT語(yǔ)句，只提交外層事務(wù)的COMMIT語(yǔ)句；事務(wù)的分類：顯式事務(wù)；隱式事務(wù)；自動(dòng)提交事務(wù)鎖與事務(wù)：

五、視圖與索引1、視圖：將多個(gè)物理數(shù)據(jù)表抽象為一個(gè)邏輯數(shù)據(jù)表，命名規(guī)范：V_視圖名，創(chuàng)建視圖名：CREATEVIEW視圖名ASSELECTstudentNo，stuNameFROMWHEREGrade=1[WITHCHECKOPTION]，強(qiáng)制針對(duì)視圖執(zhí)行的修改都必須符合查詢語(yǔ)句中設(shè)置的條件。刪除視圖，DROPVIEW視圖名；更新視圖，ALTERVIEW視圖名；加密視圖：CREATEVIEW視圖名WITHENCRYPTIONASSELECTstudentNo，stuNameFROMWHEREGrade=1[WITHCHECKOPTION]基本原則：2、索引：提高查詢性能，但是影響插入操作性能聚集索引：索引的鍵值的邏輯順序決定了表中相應(yīng)行的物理順序，一個(gè)表中只能有一個(gè)聚集索引；適用于范圍查詢，使用運(yùn)算符（如BETWEEN、>、<等）返回一系列的值，查詢連續(xù)的值，返回大型結(jié)果集，在Orderby或Groupby子句中指定的列的索引非聚集索引：一個(gè)表中有多個(gè)非聚集索引，不返回大型結(jié)果集的查詢，經(jīng)常包含在查詢的搜索條件中的列邏輯分類：主鍵索引，系統(tǒng)為主鍵自動(dòng)創(chuàng)建的索引，是聚集索引；唯一索引，不允許具有索引值相同的行，禁止重復(fù)的索引或鍵值；組合索引，多個(gè)列組合在一起作為索引，適用于多列經(jīng)常在一起作為查詢條件；全文索引，一般為文本數(shù)據(jù)創(chuàng)建索引，主要用于在大量文本文字中搜索字符串，效率大大高于Like關(guān)鍵字的效率（全文索引需開啟SQLFull_textFilterDaemonLauncher服務(wù)，且必須有唯一的列，只能用于字符型或Image類型的列，全文目錄用于存儲(chǔ)全文索引，全文索引）創(chuàng)建索引：唯一索引，CREATEUNIQUENONCLUSTEREDINDEXidx_idCardONTrainee(IdentityCard)；組合索引,CREATENONCLUSTEREDINDEXinx_computeredONRESULT(TrainNo,SubjectNo)

刪除索引：DROPINDEX表名.索引名;一般先刪除非聚集索引，再刪除聚集索引查看索引：[exec]sp_helpindex表名稱；使用系統(tǒng)視圖SELECT*FROMsys.indexesWHEREobject_id=OBJECT_ID(獲取表的ID)(‘Trainee’)維護(hù)索引：ALTERINDEX索引名ON表名REBUILD|REORGANISE;使用系統(tǒng)函數(shù)查看索引碎片程度，sys.dm_db_index_physical_stats；邏輯碎片百分比

六、存儲(chǔ)過(guò)程優(yōu)點(diǎn)：模塊化程序設(shè)計(jì)；執(zhí)行速度快、效率高；減少網(wǎng)絡(luò)流量；具有良好的安全性；分類：系統(tǒng)存儲(chǔ)過(guò)程，以“SP_”開頭；擴(kuò)展存儲(chǔ)過(guò)程，以“XP_”開頭；用戶自定義存儲(chǔ)過(guò)程，分為T_SQL存儲(chǔ)過(guò)程，CLR存儲(chǔ)過(guò)程（創(chuàng)建類庫(kù)項(xiàng)目，輸出簡(jiǎn)單字符串，編譯文件，登錄到數(shù)據(jù)庫(kù)，創(chuàng)建存儲(chǔ)過(guò)程，以“usp_”開頭）常用系統(tǒng)存儲(chǔ)過(guò)程：

擴(kuò)展存儲(chǔ)過(guò)程，只能添加在master數(shù)據(jù)庫(kù)中用戶自定義存儲(chǔ)過(guò)程Ifexists(select*fromsysobjectswherename=‘usp_query_book’)Dropprocusp_query_bookGo

--無(wú)參數(shù)

Createprocusp_query_book2as

Select*fromBooksgo

--有參數(shù)

Createprousp_query_book@numoutput(輸出參數(shù)),@total(輸出參數(shù))asgo

執(zhí)行存儲(chǔ)過(guò)程：execusp_query_book

declare@numexecusp_query_book@num,100示例：

ifexists(select*fromsysobjectswherename="usp_query_subject")go

createprocusp_query_subject(

@CourseNumintoutput,@HourNumintoutput,@GradeNamevarchar(50))as

ifLEN(@GradeName)>0begin

print"----該級(jí)別下開設(shè)科目信息如下----"selectGradeName,SubjectName,ClassHourfromGrade

leftjoinsubjectonGrade.GradeId=Subject.GradeIDwhereGradeName=@GradeName

select@CourseNum=COUNT(SubjectNo),@HourNum=SUM(ClassHour)fromGrade

innerjoinSubjectonGrade.GradeID=Subject.GradeIDwhereGradeName=@GradeName

return1

dropprocusp_query_subject

endelsego

return注意事項(xiàng)：在存儲(chǔ)過(guò)程中，可以包含CREATETABLE語(yǔ)句，但不能包含CREATEPROCEDURE和CREATEVIEW語(yǔ)句；在存儲(chǔ)過(guò)程中可以聲明和使用局部變量；在存儲(chǔ)過(guò)程中，可以創(chuàng)建和使用臨時(shí)表；存儲(chǔ)過(guò)程可以嵌套調(diào)用，被調(diào)用的存儲(chǔ)過(guò)程可以使用調(diào)用的存儲(chǔ)過(guò)程中的對(duì)象。存儲(chǔ)過(guò)程的安全與性能優(yōu)化主要是擴(kuò)展存儲(chǔ)過(guò)程，如xp_cmdshell存儲(chǔ)過(guò)程，可以執(zhí)行系統(tǒng)命令編寫安全擴(kuò)展存儲(chǔ)過(guò)程新建類庫(kù)程序

然后，將類庫(kù)文件拷貝到安裝目錄的VC目錄下,使用命令提示生成密鑰文件輸入：snkhelperkey.snk(密鑰名)

編譯類庫(kù)文件，生成動(dòng)態(tài)鏈接庫(kù)，輸入：csc/t:library/keyfile:helperkey.snkExtendPro.cs（類庫(kù)名稱）

注冊(cè)動(dòng)態(tài)鏈接庫(kù)，輸入：regasm/tlb:ExtendPro.tlbExtendePro.dll/codebase生成.tlb文件

在SQLServer中利用OLE自動(dòng)化存儲(chǔ)過(guò)程調(diào)用dll文件首先，創(chuàng)建對(duì)象，

Declare@Objectint--返回創(chuàng)建的對(duì)象Declare@hrint--過(guò)程返回值

Declare@retrurnvarchar(255)---dll方法返回值

Declare@srcvarchar(255),@descvarchar(255)---過(guò)程的錯(cuò)誤原因、描述

Exec@hr=sp_OACreate‘命名空間.類名’,@ObjectoutputIf@hr0BeginExecsp_OAGetErrorInfo@Object,@srcoutput,@descoutputSelecthr=convert(varbinary(4),@hr),Source=@src,Description=@descreturnend

調(diào)用方法，

Exec@hr=sp_OAMethod@Object,’方法名’,@descoutputIf@hr0BeginExecsp_OAGetErrorInfo@Object,@srcoutput,@descoutputSelecthr=convert(varbinary(4),@hr),Source=@src,Description=@descreturnend

銷毀實(shí)例

Exec@hr=sp_OADestroy@ObjectIf@hr0BeginExecsp_OAGetErrorInfo@Object,@srcoutput,@descoutputSelecthr=convert(varbinary(4),@hr),Source=@src,Description=@descreturnend

啟用與禁用ole自動(dòng)化存儲(chǔ)過(guò)程的方法(第二個(gè)參數(shù)為1時(shí)啟用，為0時(shí)禁用，禁用時(shí)，要先保持Sp_configure‘showadvancedoptions’,1的狀態(tài)，禁用Sp_configure‘oleautomationprocedures’,0;)

Sp_configure‘showadvancedoptions’,1Go

ReconfigureGo

Sp_configure‘oleautomationprocedures’,1Go

Reconfigurego

創(chuàng)建加密存儲(chǔ)過(guò)程語(yǔ)法：createprocwithencryptionas

七、觸發(fā)器觸發(fā)器時(shí)數(shù)據(jù)庫(kù)服務(wù)器發(fā)生事件時(shí)，自動(dòng)執(zhí)行的特殊存儲(chǔ)過(guò)程。作用：強(qiáng)制業(yè)務(wù)規(guī)則；強(qiáng)化約束；跟蹤變化；級(jí)聯(lián)運(yùn)行種類：DML觸發(fā)器，包括對(duì)表或視圖發(fā)出update、insert或delete語(yǔ)句，又分為after觸發(fā)器和insteadof觸發(fā)器；DDL觸發(fā)器，主要是以create、alter和drop開頭的語(yǔ)句修改和刪除：alter語(yǔ)句和drop語(yǔ)句1、創(chuàng)建after觸發(fā)器：createtrigger觸發(fā)器名稱on表名for（after）delete，insert，updateassql語(yǔ)句例1、刪除：

createtriggerEmployee_Delete

onEmployeefordeleteasinsertintoEmployeeOLDselect*fromdeleted

例2、更新：CreatetriggerBank_UpdateOnBankForupdateAs

(ifupdate()可以檢測(cè)列是否被修改)Declare@beforeMoneyMoney,@afterMoneyMoneySelect@beforeMoney=CurrentMoneyfromdeletedSelect@afterMoney=CurrentMoneyfrominsertedIfABS(@afterMoney-@beforeMoney)>201*0BeginRollbacktranEnd例3、插入：CreatetriggerBorrow_InsertOnBorrowForinsertAs

Declare@countint

Select@count=BookCountfromBook

WhereBookId=(selectBookIdfromInserted)If@count>0UpdateBooksetBookCount=BookCount-1WhereBookId=(selectBookIdfrominserted)ElseRollbacktran

2、創(chuàng)建insteadof觸發(fā)器，適用于表或視圖：createtrigger觸發(fā)器名on表名

insteadofdelete，insert，updateassql語(yǔ)句例：createtriggervw_Book_Borrow_Insteadof_DeleteOnvw_Book_BorrowInsteadofdeleteAsDeletefromBorrowBookIdin(selectBookIdfromdeleted)3、DML觸發(fā)器加密：createtrigger觸發(fā)器名onwithencryption表名4、DDL觸發(fā)器：特殊觸發(fā)器，他是在響應(yīng)數(shù)據(jù)定義語(yǔ)言（DDL）語(yǔ)句時(shí)觸發(fā)，僅在DDL語(yǔ)句后才會(huì)觸發(fā)，無(wú)法作為insteadof觸發(fā)器使用DDL語(yǔ)句：create、alter、drop語(yǔ)法：createtrigger觸發(fā)器名on(allserver或database)for表名assql語(yǔ)句

九、數(shù)據(jù)庫(kù)設(shè)計(jì)設(shè)計(jì)步驟：需求分析階段；概要設(shè)計(jì)階段；詳細(xì)設(shè)計(jì)階段實(shí)體關(guān)系模型：實(shí)體、屬性、關(guān)系、映射基數(shù)（一對(duì)一、一對(duì)多、多對(duì)多）、實(shí)體關(guān)系圖（E-R圖）

三大范式：第一范式：確保每列的原子性，每列都是不可再分的最小數(shù)據(jù)單元第二范式：必須滿足第一范式，目標(biāo)是確保表中的每列都和主鍵相關(guān)第三范式：滿足第二范式，主鍵外的其他列和主鍵直接相關(guān)優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)：分離用戶數(shù)據(jù)庫(kù)與系統(tǒng)數(shù)據(jù)庫(kù)；創(chuàng)建數(shù)據(jù)庫(kù)表分區(qū)；創(chuàng)建垂直分表（分離大文本字段）；創(chuàng)建水平分表

友情提示：本文中關(guān)于《SQL數(shù)據(jù)庫(kù)學(xué)習(xí)筆記與心得》給出的范例僅供您參考拓展思維使用，SQL數(shù)據(jù)庫(kù)學(xué)習(xí)筆記與心得：該篇文章建議您自主創(chuàng)作。

來(lái)源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問(wèn)題，請(qǐng)聯(lián)系我們及時(shí)刪除。

《SQL數(shù)據(jù)庫(kù)學(xué)習(xí)筆記與心得》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請(qǐng)保留原作者信息,謝謝!
鏈接地址：http://www.weilaioem.com/gongwen/672270.html

• 上一篇：輔導(dǎo)員經(jīng)驗(yàn)交流會(huì)議心得體會(huì)
• 下一篇：VB實(shí)訓(xùn)心得