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

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

0961140107

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

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

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

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

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

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

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

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

SQL功能動詞

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

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

SQL之所以能夠為用戶和業(yè)界所接受，并成為國際標(biāo)準(zhǔn)，是因為它是一個綜合的、功能極強同時又簡潔易學(xué)的語言。SQL集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義

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

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

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

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

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

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

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

(一)、單表查詢

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

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

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

(四)、集合查詢。

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

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

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

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

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

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

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

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

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

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

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

數(shù)據(jù)庫

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

1、發(fā)展階段

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

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

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

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

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

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

手動收縮和自動收縮

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

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

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

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

一、T_SQL

1、運算符算術(shù)運算符：+、-、*、/，%賦值運算符：=比較運算符：，=，!=，=邏輯運算符：AND，OR，NOT2、表操作插入：一般插入一行，INSERTINTOGrade[GradeName]VALUES("實習(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ù)據(jù)刪除掉：DELETEFROMTraineeWHERETraineeName=‘張慶艷’，TRUNCATETABLE表名，數(shù)據(jù)不能恢復(fù)，效率更高，刪除所有行數(shù)，但不刪除表結(jié)構(gòu)、索引、關(guān)系等，不能刪除有外鍵的表3、數(shù)據(jù)查詢（1）簡單的查詢語句：全部查詢：SELECT*FROM表名（盡量少使用，效率比較低）；條件查詢：SELECT[字段名，字段名，字段名]FROM表名[WHERE查詢條件]使用別名：SELECT列名AS列的別名FROM表名；SELECT列名列的別名FROM表名；SELECT列的別名=列名FROM表名（使用中文別名時，可不用引號，但不能使用全角引號；使用的英文別名超過兩個單詞時，必須使用引號將別名括起來）查詢中使用常量列：SELECT姓名=TraineeName，地址=Address，‘河北新龍’AS實習(xí)地點FROMTrainee查詢空值：SELECT[字段名]FROM表名WHERE字段名IS(NOT)NULL限制查詢返回的行數(shù)：SELECTTOP數(shù)量字段名FROM表名WHERE條件；按百分比SELECTTOP數(shù)量PERCENT字段名FROM表名WHERE條件順序排列查詢結(jié)果：查詢語句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實習(xí)生姓名FROMTraineeWHEREAddressIN（‘北京市海淀區(qū)’，‘廣州’，‘上海虹橋’）

聚合函數(shù)：基于列進行計算SUM：求和，用于數(shù)字類型的列AVG：求平均值，所有數(shù)字的平均值MAX()和MIN():最值COUNT():求行數(shù)；COUNT(*)，檢索所有列；COUNT(1),檢所使用列；COUNT(列名)檢索指定列非空的行數(shù)分組查詢：使用GROUPBY子句實現(xiàn)分組統(tǒng)計，HAVING，對分組進行篩選SELECTSubjectNo，AVG（TraineeResult）AS課程平均成績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)接：從兩個或兩個以上的表組合中挑選出符合聯(lián)接條件的數(shù)據(jù)，如果數(shù)據(jù)無法滿足聯(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é)果集中；對那些不符合聯(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合并多個查詢結(jié)果所有查詢的列數(shù)和列的順序必須相同；要合并的數(shù)據(jù)類型必須相同，至少也要可以轉(zhuǎn)換合并的查詢結(jié)果集的字段名稱以第一個查詢結(jié)果的字段名稱為名，其他的查詢集的字段名稱會被忽略SELECT查詢語句1UNION[ALL]SELECT查詢語句2三、數(shù)據(jù)查詢安全性和性能優(yōu)化1、SQL注入：將SQL代碼插入到應(yīng)用程序的輸入?yún)��?shù)中，之后，SQL代碼將被傳遞到數(shù)據(jù)庫執(zhí)行，從而達(dá)到對應(yīng)用程序的攻擊目的。原理：檢測注入點判斷數(shù)據(jù)庫類型傳遞惡意代碼上傳木馬，盜取賬號，獲取管理員權(quán)限，發(fā)現(xiàn)Web目錄防范：限制錯誤信息的輸出；使用參數(shù)命令傳遞參數(shù)；使用存儲過程；限制輸入長度；URL重寫技術(shù)；傳遞參數(shù)盡量不用字符串2、SQL優(yōu)化：低效SQL危害：系統(tǒng)響應(yīng)變慢（8秒定律）；死鎖；客戶失去信心，軟件失敗根源：硬件原因；沒有建索引，或者SQL沒有走索引；SQL過于復(fù)雜；頻繁訪問數(shù)據(jù)庫SQL執(zhí)行原理：解釋解析優(yōu)化編譯執(zhí)行優(yōu)化SQL語句：完善開發(fā)管理；檢測SQL查詢的效率查詢SQL語句查詢時間

SETSTATISTICSIOONSETSTATASTICSTIMEON

清楚緩存

DBCCDROPCLEANBUFFERSDBCCFREEPROCCACHE

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

{Sql_statement}END

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

{sql_statement}ELSE

{sql_statement}

CASE

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

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

}

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

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

SELECTTraineeNameFROMTraineeWHERETraineeNoIN(SELECTTraineeNoFROMResultWHERESubjectNo=

(

SELECTSubjectNoFROMSubjectWHERESubjectName=‘SQLServer’)ANDTraineeResult)

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

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

SELECTAVG(ClassHour)FROMSubjectGROUPBYGradeId)

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

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

SELECTAVG(ClassHour)FROMSubjectGROUPBYGradeId)

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

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

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

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

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

--無參數(shù)

Createprocusp_query_book2as

Select*fromBooksgo

--有參數(shù)

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

執(zhí)行存儲過程：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"----該級別下開設(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注意事項：在存儲過程中，可以包含CREATETABLE語句，但不能包含CREATEPROCEDURE和CREATEVIEW語句；在存儲過程中可以聲明和使用局部變量；在存儲過程中，可以創(chuàng)建和使用臨時表；存儲過程可以嵌套調(diào)用，被調(diào)用的存儲過程可以使用調(diào)用的存儲過程中的對象。存儲過程的安全與性能優(yōu)化主要是擴展存儲過程，如xp_cmdshell存儲過程，可以執(zhí)行系統(tǒng)命令編寫安全擴展存儲過程新建類庫程序

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

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

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

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

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

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

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

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

銷毀實例

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

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

Sp_configure‘showadvancedoptions’,1Go

ReconfigureGo

Sp_configure‘oleautomationprocedures’,1Go

Reconfigurego

創(chuàng)建加密存儲過程語法：createprocwithencryptionas

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

createtriggerEmployee_Delete

onEmployeefordeleteasinsertintoEmployeeOLDselect*fromdeleted

例2、更新：CreatetriggerBank_UpdateOnBankForupdateAs

(ifupdate()可以檢測列是否被修改)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語句例：createtriggervw_Book_Borrow_Insteadof_DeleteOnvw_Book_BorrowInsteadofdeleteAsDeletefromBorrowBookIdin(selectBookIdfromdeleted)3、DML觸發(fā)器加密：createtrigger觸發(fā)器名onwithencryption表名4、DDL觸發(fā)器：特殊觸發(fā)器，他是在響應(yīng)數(shù)據(jù)定義語言（DDL）語句時觸發(fā)，僅在DDL語句后才會觸發(fā)，無法作為insteadof觸發(fā)器使用DDL語句：create、alter、drop語法：createtrigger觸發(fā)器名on(allserver或database)for表名assql語句

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

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

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

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

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

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