數(shù)據(jù)庫期末復習資料

數(shù)據(jù)庫復習資料第一講數(shù)據(jù)庫概述數(shù)據(jù)與信息的概念1.數(shù)據(jù)：是實現(xiàn)對象的數(shù)據(jù)模型，表達對象的特征〔屬性〕，是對現(xiàn)實對象的抽象與簡化，是人們用于記錄事物情況的物理符號。2.信息：是經(jīng)過加工處理并對人類社會實踐和生產(chǎn)活動產(chǎn)生決策影響的數(shù)據(jù)。3.數(shù)據(jù)處理：是指將數(shù)據(jù)轉(zhuǎn)換為信息的過程。4.數(shù)據(jù)處理的過程：收集、存儲、分類、計算、加工、檢索、傳輸。5.數(shù)據(jù)類型：〔1〕數(shù)值類型：能參與數(shù)值計算的數(shù)據(jù)類型，如整數(shù)、浮點數(shù)?！?〕非數(shù)值型數(shù)據(jù)：不能參與數(shù)值計算的數(shù)據(jù)類型，如整數(shù)。浮點數(shù)?！?〕日起型數(shù)據(jù)：表達日期和時間的數(shù)據(jù)類型。二、數(shù)據(jù)管理技術的開展階段三、數(shù)據(jù)庫1.定義：指數(shù)據(jù)庫系統(tǒng)中按照一定的方式組織的，存儲在外部存儲設備上的，能為多個用戶共享的、與應用程序相互獨立的相關數(shù)據(jù)集合。2.類型：〔1〕關系數(shù)據(jù)庫：以關系模型為數(shù)據(jù)存儲機制的數(shù)據(jù)庫。〔2〕層次數(shù)據(jù)庫：以層次模型為數(shù)據(jù)存儲機制的數(shù)據(jù)庫?！?〕網(wǎng)絡數(shù)據(jù)庫：以網(wǎng)絡模型為根底的數(shù)據(jù)庫。第二講關系模型根底一、數(shù)據(jù)模型1.定義：客觀事物對象某些特征的數(shù)據(jù)抽象和模擬，是嚴格定義的一組概念的集合。2.數(shù)據(jù)模型的要求：〔1〕比擬真實地模擬或抽象表示現(xiàn)實對象。〔2〕容易被人們理解和接受?！?〕便于在計算機上實現(xiàn)。二、數(shù)據(jù)模型類型〔1〕層次數(shù)據(jù)模型〔2〕網(wǎng)絡數(shù)據(jù)模型〔3〕關系數(shù)據(jù)模型三、關系數(shù)據(jù)模型〔1〕概念模型：不關聯(lián)任何DBMS的簡化關系模型，只考慮對象、屬性、聯(lián)系，不考慮實現(xiàn)技術細節(jié)?！?〕邏輯模型：關聯(lián)到具體的DBMS產(chǎn)品，與技術細節(jié)有關。〔3〕物理模型：是關系模型的物理有貯結(jié)構，與DBMS和OS有關。〔1〕關系表：一個關系就是一個二維表，由行和列組成，每個行表達一個實體對象或聯(lián)系對象，行不能重復，即沒有兩個行的值完全相同。列表達實體對象的屬性。每個關系都有一個關系名?！?〕元組〔記錄〕：關系的每個行稱為一個元組，也稱為記錄或行。每個元組表達現(xiàn)實世界的一個對象或?qū)ο笾g的一個聯(lián)系。元組是不重復的。〔3〕屬性：二維表的每個列稱為屬性。每個屬性都有屬性名和數(shù)據(jù)類型。每個關系中列是不能重復的。〔4〕關系的度：關系中屬性的個數(shù)稱為關系的度。〔5〕域：屬性的取值范圍?！?〕模式：將關系二維表的表頭稱為關系的模式，表達對象的屬性結(jié)構。〔7〕主鍵〔Primarykey〕：關系中能唯一區(qū)分確定不同元組的屬性和屬性集合?！?〕外鍵〔Foreignkey〕：關系中的某個屬性或?qū)傩缘慕M合不是此關系的關鍵字，但卻是另一個關系的主關鍵字?！?〕關系中每個元組的分量必須是原始的?！?〕每一列的值只能取自同一個域?！?〕列的次序可以任意交換。〔4〕行的次序可以任意交換。〔5〕關系中不能出現(xiàn)完全相同的兩行?！?〕數(shù)據(jù)結(jié)構：二維表〔2〕操作集合：增加元組、修改元組、刪除元組、遍歷元組〔3〕關系模型約束：完整性約束、引用性約束、自定義約束第三講數(shù)據(jù)庫管理和維護一、SQLServer數(shù)據(jù)庫的組成1.組織結(jié)構：<1>數(shù)據(jù)庫的邏輯結(jié)構：SQLServer把數(shù)據(jù)庫及其各種對象使用邏輯組件表示，通過SQLServer提供的專門工具。<2>數(shù)據(jù)庫的物理結(jié)構：是DBMS管理磁盤上的物理文件，是從操作系統(tǒng)角度看到的數(shù)據(jù)庫的文件對象，可以通過OS提供的資源器等工具查看數(shù)據(jù)庫的物理對象。二、數(shù)據(jù)文件1.數(shù)據(jù)庫數(shù)據(jù)文件是存放數(shù)據(jù)庫對象和數(shù)據(jù)的文件，保存在文件系統(tǒng)中。2.一個數(shù)據(jù)庫可以有多個數(shù)據(jù)文件。3.每個數(shù)據(jù)文件只能屬于一個數(shù)據(jù)庫。4.當有多個數(shù)據(jù)文件時，一個數(shù)據(jù)文件被定義為主文件，擴張名為mdf，主數(shù)據(jù)文件用于存儲數(shù)據(jù)庫啟動信息或局部或全部數(shù)據(jù)庫。5.每個數(shù)據(jù)庫只能有1個主數(shù)據(jù)文件。6.其他數(shù)據(jù)文件稱為次數(shù)據(jù)文件，擴展名為ndf，用于存儲主數(shù)據(jù)文件沒有存儲的其他數(shù)據(jù)庫信息。三、數(shù)據(jù)庫日志文件〔logfile〕1.日志文件用于保存對數(shù)據(jù)的更新操作，保存數(shù)據(jù)的增加，修改，刪除。2.事務日志文件最小為512kb，擴展名為.ldf。3.每個數(shù)據(jù)庫至少有1個日志文件，也可以有多個日志文件。四、使用Transact-SQL語句創(chuàng)立數(shù)據(jù)庫數(shù)據(jù)庫名稱：ERPDB數(shù)據(jù)文件：(1)名稱：erpdata01；存儲：d:\erp\data\；大?。?MB；最大：2MB；增量：5％；文件組：主文件組。(2)名稱：erpdata02；存儲：d:\erp\data\；大?。?MB；最大：4MB；增量：1M；文件組：GP001。 日志文件：名稱：erplog01；存儲：d:\erp\data\；大?。?MB；最大：4MB；增量：1M；createdatabaseERPDBon(name=erpdata01,filename='d:\erp\data\erpdata01.mdf',size=1MB,maxsize=2MB,filegrowth=5%),filegroupGP001(name=erpdata02,filename='d:\erp\data\erpdata02.ndf',size=1MB,maxsize=4MB,filegrowth=1MB)logon(name=erplog01,filename='d:\erp\data\erplog01.ldf',size=1MB,maxsize=4MB,filegrowth=1MB)五、使用Transact-SQL語句修改數(shù)據(jù)庫1.修改數(shù)據(jù)庫ERPDB，增加數(shù)據(jù)文件文件名：erpdata04；存儲：d:\erp\data\；大?。?MB；最大：4MB；增量：500KBalterdatabaseERPDBaddfile(name=erpdata04,filename='d:\erp\data\erpdata04.ndf',size=2MB,maxsize=4MB,filegrowth=500KB)2.修改數(shù)據(jù)庫ERPDB，增加日志文件文件名：erplog04；存儲：d:\erp\data\；大?。?MB；最大：3MB；增量：8%alterdatabaseERPDBaddlogfile(name=erplog04,filename='d:\erp\data\erplog04.ldf',size=1MB,maxsize=3MB,filegrowth=8%)3.修改數(shù)據(jù)庫ERPDB，增加文件組，名稱為：ERPFG并為此文件組增加一個數(shù)據(jù)文件文件名：erpdata05；存儲：d:\erp\data\；大?。?MB；最大：2MB；增量：2MBalterdatabaseERPDBaddfilegroupERPFGgoalterdatabaseERPDBaddfile(name=erplog05,filename='d:\erp\data\erplog05.ndf',size=1MB,maxsize=2MB,filegrowth=2MB)4.刪除數(shù)據(jù)文件：erpdata04和刪除日志文件：erplog04alterdatabaseERPDBremovefileerpdata04alterdatabaseERPDBremovefileerplog045.刪除數(shù)據(jù)庫ERPDBDropdatabaseERPDB第四講關系代數(shù)和集合運算一、關系運算1.關系運算：指操作對象和結(jié)果都是關系。2.關系代數(shù)：對關系進行操作的科學。二、關系運算的類型1.根本運算：并、交、差。2.選擇運算：對表的行的選擇〔〕。3.投影運算：對表的列的選擇，從關系中選取局部屬性的運算〔Π〕。4.笛卡爾積運算：〔1〕定義：設D1,D2,D3,…,Dn為屬性的值域集合，將每個集合的元素進行關聯(lián)組合，形成笛卡爾積，記為 D1XD2XD3X…XDn={d1,d2,d3,…,dn|di∈Dii=1,2,3,…,n}如：A={1,2}B={a,b}那么AxB={(1,a),(2,a),(1,b),(2,b)}5.廣義笛卡爾的定義：當普通的笛卡爾積的集合是任意的關系集合時，這些關系集合的笛卡爾積稱為廣義笛卡爾積。記為 R1XR2X…XRn={(r1,r2,…,rn)|ri∈Ri}6．連接：〔1〕等值連接：某些屬性相等?！?〕條件連接：R?S。〔3〕自然連接：有公共屬性的關系，在公共屬性上等值連接，且結(jié)果集去除重復屬性。三、關系的定義1.任意集合D1,D2,…,Dn的笛卡爾積D1xD2x…xDn的任何一個子集稱為D1,D2,…,Dn上的一個n元關系。記為:R(D1,D2,…,Dn)?D1xD2x…xDnR(D1,D2,…,Dn)={(d1,d2,…,dn)|di∈Di}一般將R(D1,D2,..,Dn)稱為關系的模式，即二維表的表頭。2.關系不是元素的集合，而是元組的集合。3．關系代數(shù)的常見運算符〔1〕傳統(tǒng)的集合運算符，包括并〔∪〕、差〔—〕、交〔∩〕、笛卡爾積〔×〕；〔2〕專門的關系運算符，包括選擇〔〕、投影〔Π〕、連接〔?〕和除〔÷〕運算；〔3〕算術比擬運算符，包括大于〔＞〕、大于等于〔≥〕、小于〔＜〕、小于等于〔≤〕、等于〔＝〕、不等于〔≠〕；〔4〕邏輯運算符，包括與〔∧〕、或〔∨〕、非〔￢〕。第五講SQLServer表管理和約束條件一、表〔table〕的概念1.表是關系模型的邏輯存儲結(jié)構。2.實體和關系都保存在數(shù)據(jù)庫表中。3.表由模式、字段和記錄組成。4.模式表達表的結(jié)構，即字段及其類型。5.字段表達實體的屬性，字段有類型，表達取值范圍。6.記錄表達一個具體的實體，即現(xiàn)實對象。二、創(chuàng)立表的根本信息：表名、字段名、字段數(shù)據(jù)類型、表的字段的約束、字段的默認值和規(guī)那么。三、SQLServer2000的數(shù)據(jù)類型1.字符類型：存儲漢字，英文符號，數(shù)字符號等?！?〕char<1>用于存儲定長字符串。<2>語法：char(n)n取值：1—8000，缺乏補空格。<3>優(yōu)點：查詢速度快。<4>缺點：浪費磁盤存儲空間。〔2〕varchar<1>存儲可變長度字符串。<2>語法：varchar(n)n=1-8000〔3〕Text<1>存儲可變長度超長字符串，最大2^31-1個字符<2>Text類型不能用于變量和存儲過程參數(shù)?！?〕Nchar(n)n=1—4000字符〔2〕Nvarchar(n)n=1—4000字符〔3〕Ntext2^30-1字符〔1〕整數(shù)類型<1>int:存儲4字節(jié)，-2^31~2^31-1<2>Smallint:存儲2字節(jié)-2^15~2^15-1<3>Tinyint:存儲1字節(jié)0~255<4>bigint:存儲8個字節(jié)-2^63~2^63-1〔2〕浮點類型<1>Float:存儲8個字節(jié),精確到小數(shù)點15位，表達-1.79x10^308~1.79x10^308,可以通過float(n)指定小數(shù)位數(shù)n=1-53<2>Real:存儲4個字節(jié)，精確到小數(shù)點7位，表達-3.04x10^38~3.04x10^38，real不能指定小數(shù)點位數(shù)?！?〕精確數(shù)值類型<1>decimal(p,s)p:有效位數(shù)，s:小數(shù)位數(shù)<2>numeric(p,s)p:有效位數(shù)，s:小數(shù)位數(shù)〔4〕貨幣數(shù)值類型<1>Money存儲8個字節(jié)，-2^63~2^63-1<2>Smallmoney存儲4個字節(jié)，-2^31~2^31-1〔1〕DATETIME：存儲日期和時間，精度為3/100秒，存儲2個4字節(jié)的空間，分別存儲日期和時間。〔2〕SmallDatetime：精確到分，占用4個字節(jié)的存儲空間。時間值按小時和分鐘存儲〔3〕Timestamp：時間戳數(shù)據(jù)類型，用于自動記錄的操作時間，此類型的字段值由系統(tǒng)自動提供，不允許用戶手動設置該字段值?！?〕Binary：定長二進制類型，binary(n),n=1-8000字節(jié)默認為1，可以為十進制數(shù)，自動轉(zhuǎn)換為十六進制，或使用0x開頭的十六進制數(shù)表示。〔2〕Varbinary：可變長二進制類型，varbinary(n),n=1-8000字節(jié)，〔3〕Image：可變長大二進制類型，存儲2^31-1字節(jié)的二進制數(shù)據(jù)。四、使用T-SQl語句創(chuàng)立表〔1〕創(chuàng)立系統(tǒng)功能表 表名 字段名類型默認值約束OA_FUNCTIONFUNNOIntPKFUNNAMEVarchar(50)非空UPFUNNOIntFK->PK(本表PK)OA_DEPARTMENTDEPTNOIntPKDEPTCODEVarchar(10)非空唯一DEPTNAMEVarchar(50)非空唯一LOCATIONVarchar(200)POSTCODECHAR(6)OA_EMPLOYEEEMPNOIntPKEMPIDVarchar(20)非空唯一PASSWORDVarchar(20)非空NAMEVarchar(50)非空SEXChar(2)‘男’只能是’男’或’女’AGEInt20在18到60之間WORKAGEintAge>workage+18HIREDATEDatetime要在’2000-10-10’之后LeaveDateDatetime要在Hiredate之后Salarydecimal(10,2)在1000到9000之間BONUSDecimal(10,2)Salary>bonusMailVarchar(100)PhotoImageJobDescTextDEPTNOintOA_SKILLSKILLNOIntPKSKILLNAMEVarchar(50)非空唯一OA_EMPSKILLEMPNOIntSKILLNOInt2.6員工功能關聯(lián)表OA_EMPFUNCTIONEMPNOIntFUNNOIntcreatetableOA_FUNCTION(FUNNOintprimarykey,FUNNAMEvarchar(50)notnull,UPFUNNOInt,foreignkey(UPFUNNO)referencesOA_FUNCTION(FUNNO))createtableOA_DEPARTMENT(DEPTNOintprimarykey,DEPTCODEvarchar(10)notnullunique,DEPTNAMEvarchar(50)notnullunique,LOCATIONVarchar(200),POSTCODECHAR(6))createtableOA_EMPLOYEE(EMPNOintprimarykey,EMPIDvarchar(20)notnullunique,EPASSWORDvarchar(20)notnull,ENAMEVarchar(50)notnull,SEXChar(2)default'男'check(sex='男'orsex='女'),AGEIntdefault'20'check(18<=AGEandAGE<=60),WORKAGE int,HIREDATEDatetimecheck(HIREDATE>'2000-10-10'),LeaveDateDatetime,check(LeaveDate>HIREDATE),Salarydecimal(10,2)check(1000<=SalaryandSalary<=9000),BONUS Decimal(10,2),Mail Varchar(100),Photo Image,JobDesc Text,DEPTNO int,foreignkey(DEPTNO)referencesOA_Department(DEPTNO),check(LeaveDate>HIREDATE),check(Salary>bonus),check(Age>workage+18),)CREATETABLEOA_SKILL(SKILLNOintprimarykey,SKILLNAMEVarchar(50)NOTNULLUNIQUE,)CREATETABLEOA_EMPSKILL(EMPNOint,SKILLNOInt,primarykey(EMPNO,SKILLNO),foreignkey(EMPNO)REFERENCESOA_EMPLOYEE(EMPNO),foreignkey(SKILLNO)REFERENCESOA_SKILL(SKILLNO),)createtableOA_EMPFUNCTION(EMPNO Int,FUNNO Int,primarykey(EMPNO,FUNNO),foreignkey(EMPNO)referencesOA_EMPLOYEE(EMPNO),foreignkey(FUNNO)referencesOA_FUNCTION(FUNNO))五、使用T-SQL語句修改表〔1〕為員工表OA_EMPLOYEE增加列：MobilePhone，類型為varchar(50),不能為空altertableOA_EMPLOYEEaddMobilePhonevarchar(50)GO〔2〕將員工表OA_EMPLOYEE的Mail列修改為Varchar(200)，且不能為空altertableOA_EMPLOYEEaltercolumnMailVarchar(200)nullGO〔3〕將員工表OA_EMPLOYEE的JobDesc列刪除altertableOA_EMPLOYEEdropcolumnJobDescGO〔4〕刪除員工表droptableOA_EMPLOYEE六．表設計范式的實現(xiàn)----約束1.定義：規(guī)定表記錄字段的取值范圍和限定。2.類型：〔1〕主鍵約束〔2〕非空約束〔3〕唯一約束〔4〕外鍵約束〔5〕檢查約束3.主鍵約束〔primarykey〕〔1〕唯一標識表中的所有行的一個列或一組列，主鍵不允許空值。不能存在具有相同的主鍵值的兩個行，因此主鍵值總是唯一標識單個行。表中可以有不止一個鍵唯一標識行，每個鍵都稱作候選鍵。只有一個候選鍵可以作表的主鍵。〔2〕分類<1>列級主鍵約束：在定義表的列字段時，直接定義本列是表的主鍵。<2>表級主鍵約束：在創(chuàng)立表時，單獨創(chuàng)立表的主鍵約束。當表中有多個字段組合起來作為表的主鍵時，不能單獨在列上定義主鍵，只能在表的級別上創(chuàng)立這類主鍵約束。4.非空約束〔notnull〕〔1〕非空約束：字段值不能為null，只有列級定義，無表級定義?！?〕分類：<1>自定義約束名：字段名類型constraint約束名notnull<2>默認約束名：字段名類型notnull5.唯一性約束〔unqiue〕〔1〕唯一性約束：約束確保未輸入重復值，并創(chuàng)立一個索引以增強性能，可以為空。6.外鍵約束〔foreignkey〕〔1〕定義：由一列或多列構成的，它用來建立和強制兩個表間的關聯(lián)，這種關聯(lián)是通過將一個表中的組成主鍵的列或組合列參加到另一個表中形成的，這個列或組合列就成了第二個表中的外鍵?！?〕定義類型：<1>列級外鍵：列名類型reference表名〔列名〕<2>表級外鍵：foreignkey〔列名1，列名2….〕reference表名〔列名1，列名2…..〕7.檢查約束〔check〕〔1〕定義：在設定欄數(shù)據(jù)類型之上更進了一步。它們使用一個決定數(shù)據(jù)是否有效的布爾表達式限制一個欄可以接受的實際值?！?〕類型：<1>列級檢查約束：列名類型check〔邏輯表達式〕<2>表級檢查約束：check〔邏輯表達式〕七、表的修改〔1〕修改列類型〔2〕增加列〔3〕刪除列〔4〕增加約束〔5〕刪除約束第六講關系代數(shù)和關系運算一、關系的專門運算1.關系有特定的運算，這些運算是關系特有的，與集合運算不同。2.專門運算分類：(1)單目運算：只操作一個關系，結(jié)果是新的關系，有選擇，投影(2)多目運算:操作2個或2個以上關系，產(chǎn)生新的關系，有連接運算二、選擇運算1.選擇運算是針對一個關系，對指定的關系的元組(記錄)按照指定的條件進行篩選。2.選擇的語法標識：σ條件(關系)條件包含：〔1〕關系的屬性名稱〔2〕比擬運算符:><>=<=<>〔3〕邏輯運算符:∧與∨或￢非例：關系：學生(學號,姓名,年齡,生日,性別)(1)選擇年齡大于22的學生σage>22(Student)(2)選擇1980前出生的所有男同學σ生日<‘1980-01-10’∧sex=‘男’(Student)三、投影運算〔Project〕1.投影運算是對指定的關系只查詢局部屬性的運算。投影運算也是單目運算，針對一個關系。2.投影的語法標識：∏屬性名，屬性名，屬性名〔關系名〕例：設關系:Student(Id,Name,Age,birthday,sex)(1)查詢學生關系的學號和姓名∏id,name(Student)(2)查詢學生的所有性別∏sex(Student)(1)先選擇后投影∏name,sex(σage>22∧sex=‘男’(Student))(2)先投影后選擇σage>22∧sex=‘男’(∏name,age,sex(Student))四、關系的鏈接運算〔join〕1.當用戶檢索的數(shù)據(jù)分別存儲在不同的關系中時，需要將多個關系連接起來，形成一個包含所有關系的屬性的新的關系，這就需要關系的連接運算?！?〕條件連接：使用條件表達式如<><=>=<>等連接兩個關系屬性的連接?！?〕等式連接：使用等式=關聯(lián)多個關系屬性的連接。〔3〕自然連接：如果兩個關系中進行比擬的分量必須是相同的屬性組，并且在結(jié)果中把重復的屬性列去掉。3．外連接：兩個關系R和S進行自然連接時，選擇兩個關系R和S公共屬性上相等的元組，去掉重復的屬性列構成新關系。這樣，關系R中的某些元組有可能在關系S中不存在公共屬性值上相等的元組，造成關系R中這些元組的值在運算時舍棄了；同樣關系S中的某些元組也可能舍棄?！?〕左外連接：R和S進行自然連接時，只把R中舍棄的元組放到新關系中?！?〕右外連接：R和S進行自然連接時，只把S中舍棄的元組放到新關系中。〔3〕完全外連接：R和S進行自然連接時，只把R和S中舍棄的元組都放到新關系中。R關系S關系A1A2A3A1A2A4abcazabadbahcddcdddfgdscR和S的自然連接R和S的左外連接：A1A2A3A4abcnullbadhcddddfgnullA1A2A3A4badhcdddA1A2A3A4aznullabadhcddddsnullcA1A2A3A4abcnullbadhcddddfgnullaznulladsnullcR和S的右外連接R和S的完全外連接第七講數(shù)據(jù)管理和事物管理數(shù)據(jù)管理1.功能：對數(shù)據(jù)庫的數(shù)據(jù)管理功能CRUD，即增刪改查四大根本功能。2.分類：〔1〕C(Create)：將數(shù)據(jù)增加到數(shù)據(jù)庫中，即將數(shù)據(jù)增加到表中?！?〕U(Update)：將數(shù)據(jù)庫中的數(shù)據(jù)進行更新?！?〕D(Delete)：將數(shù)據(jù)庫中的數(shù)據(jù)刪除?！?〕R(Retrive)：從數(shù)據(jù)庫檢索出存儲的數(shù)據(jù)。3.CUD功能通過SQL的DML語句完成，R功能通過SQL的DQL語句完成。二、SQL的DML語句1.對數(shù)據(jù)的增加，修改和刪除通過DML語句完成。2.DML主要有3個SQL語句(1)Insertinto語句：完成數(shù)據(jù)的增加(2)Update語句：完成數(shù)據(jù)的修改(3)Delete語句：完成數(shù)據(jù)的刪除。三、增加數(shù)據(jù)1.語句：insertinto2.分類：〔1〕單記錄增加：每次執(zhí)行只能增加一個記錄。格式1：insertinto表名values(值1,值2,…) 全字段增加，沒有列出字段名，值的個數(shù)必須與字段個數(shù)相同。格式2：insertinto表名(列1,列2,…)values(值1,值2,…) 局部字段的記錄增加，只給出局部字段的值，未列出字段按默認值指定，無默認值那么為null。所有非空字段，且無默認值時，不能省略該字段。例：insertintodeptvalues(1,’001’,’財務部’);增加新的部門，所有字段均賦值。insertintodept(deptno,deptcode)values(2,’002’) 增加新的部門，只賦值部門編號和編碼，而名稱字段沒有賦值，取默認值，假設沒有默認值那么為null。如果部門名稱字段不允許為空，那么出現(xiàn)錯誤?！?〕多記錄增加格式1：Insertinto表名Select語句將select語句結(jié)果集的記錄增加到表中。要求select的工程個數(shù)和數(shù)據(jù)類型與表的字段個數(shù)和類型相符。格式2：insertinto表名(列1,列2,…)select語句將select結(jié)果集增加到指定列的表中。要求select個數(shù)與指定的列數(shù)相同，且數(shù)據(jù)類型相符。例：格式1：insertintodeptSelectno,code,nameFromdepartmentwherenamelike‘%產(chǎn)’格式2：insertintodept(deptno,deptcode)Selectno,codeFromdepartmentWhereno<10四、修改數(shù)據(jù)1.功能：對數(shù)據(jù)表中指定的記錄字段值進行修改。2.語法：〔1〕通過給定值修改Updata表名set列名=值，列名=值…..〔2〕通過查詢修改Updata表名set列名=select語句，列名=表達式….From表名where條件表達式例：UPDATEOrdersSETOrderAmount=OrderAmount*1.1,Freight=Freight*WHEREShipCountry='UK';五、刪除數(shù)據(jù)1.功能：刪除表中的指定記錄或全部記錄。2.語法：〔1〕deletefrom表名[where條件]〔2〕truncatetable表名該語句與delete的區(qū)別是此語句不支持事務處理，無法進行回滾，不能撤銷。六、事務1.定義：事務(Transaction)是將多條DML數(shù)據(jù)操作語句的執(zhí)行作為一個邏輯整體，要么全部執(zhí)行成功，要么都不執(zhí)行。2.事務的特點：〔1〕原子性(Atomic)：事務中的所有語句作為一個整體，不能別離，要成功都成功，要失敗都失敗。〔2〕一致性(Consistence)：事務完成后所有的數(shù)據(jù)都保持一致，所有的數(shù)據(jù)庫約束和規(guī)那么都必須遵守?！?〕隔離性(Isolation)多個事務之間必須不能相互影響，每個事務都是獨立的?！?〕永久性(Duobility)：事務結(jié)束后，事務的執(zhí)行結(jié)果要永久保存。3.實現(xiàn)事務的方式：〔1〕自動提交事務模式：每個SQL語句自己作為一個事務，執(zhí)行成功后自動提交，如果執(zhí)行失敗，自動回滾該語句?！?〕顯式事務處理模式;使用事務管理語句完成事務的開始，事務的提交和事務的回滾，可以將多條DML語句作為一個事務管理。〔3〕隱身事務處理模式：此模式下一個事務結(jié)束后，新的事務自動開始，不需要使用begintransaction標識事務的開始，但要使用committransaction和rollbacktransaction結(jié)束事務。例創(chuàng)立城市銀行數(shù)據(jù)庫：CITYBANK，使用默認位置和大小即可。創(chuàng)立如下銀行業(yè)務表：表名字段名類型約束說明//賬戶表Bank_AccountAccountIDPASSWORDANAMEcardidOpenDateBalanceAccountStatusATYPEChar(6)Varchar(10)Varchar(20)Varchar(18)DatetimeDecimal(20,2)Char(4)Char(4)PKNOTNULLNOTNULLUNIQUE默認值：0默認值:’激活’默認值:’普通’賬戶ID賬戶密碼姓名身份證號碼開戶日期帳戶余額賬戶狀態(tài)賬戶類型//存款單表Bank_DepositNoteNoteNoAccountIDAmountNoteDateIntChar(6)Decimal(10,2)datetimePK存款單序號帳戶ID存款金額存款日期//取款單表Bank_WithdrawNoteNoteNoAccountIDAmountNoteDateIntChar(6)Decimal(10,2)datetimePK取款單序號帳戶ID取款金額取款日期//轉(zhuǎn)帳單表Bank_TransferNoteNoteNoFROMAccountIDTOAccountIDAmountNOTEDATEIntChar(6)Char(6)Decimal(10,2)datetimePKFK->Bank_AccountPK取款單序號轉(zhuǎn)出帳戶ID轉(zhuǎn)入帳戶ID轉(zhuǎn)帳金額轉(zhuǎn)帳日期//注銷申請單表Bank_DestoryNoteNoteNoAccountIDNoteDateNoteCommentIntChar(6)DatetimeVarchar(500)PK注銷單序號帳戶ID申請日期申請理由1.createdatabaseCITYBANK2.createtableBank_Account(AccountIDChar(6)primarykey,EPASSWORDVarchar(10)notnull,ANAMEVarchar(20)notnull,cardidVarchar(18)UNIQUE,OpenDateDatetime,BalanceDecimal(20,2)default0,AccountStatusChar(4)default'激活',ATYPEChar(4)default'普通')createtableBank_DepositNote(NoteNoIntprimarykey,AccountIDChar(6)foreignkey(AccountID)referencesBank_Account(AccountID),AmountDecimal(10,2),NoteDatedatetime)createtableBank_WithdrawNote(NoteNoIntprimarykey,AccountIDChar(6)foreignkey(AccountID)referencesBank_Account(AccountID),AmountDecimal(10,2),NoteDatedatetime)createtableBank_TransferNote(NoteNoIntprimarykey,FROMAccountIDChar(6)foreignkey(FROMAccountID)referencesBank_Account(AccountID),TOAccountIDChar(6)foreignkey(TOAccountID)referencesBank_Account(AccountID),AmountDecimal(10,2),NOTEDATEdatetime,)createtableBank_DestoryNote(NoteNoIntprimarykey,AccountIDChar(6)foreignkey(AccountID)referencesBank_Account(AccountID),NoteDateDatetime,NoteCommentVarchar(500))賬號密碼姓名身份證號碼開戶日期開戶金額狀態(tài)類型001001001李銘2010-01-200激活金卡001002002張林2009-03-010激活銀卡001003003劉鑫2008-10-10激活普通(1)setxact_abortonbegintransactioninsertintoBank_Account(AccountID,EPASSWORD,ANAME,cardid,OpenDate,Balance,AccountStatus,ATYPE)2010-01-20',0,'激活','金卡')committransaction(2)setxact_abortonbegintransactioninsertintoBank_Account(AccountID,EPASSWORD,ANAME,cardid,OpenDate,Balance,AccountStatus,ATYPE)2009-03-01',0,'激活','銀卡')committransaction(3)setxact_abortonbegintransactioninsertintoBank_Account(AccountID,EPASSWORD,ANAME,cardid,OpenDate,Balance,AccountStatus,ATYPE)2008-10-10',0,'激活','普通')committransaction4.存款業(yè)務：<1>李銘2010-02-20存入2000元。setxact_abortonbegintransactionupdateBank_AccountsetBalance=2300where(AccountID=001001)insertintoBank_DepositNote(NoteNo,AccountID,Amount,NoteDate)values(222,'001001',2000,'2010-02-20')committransaction5．取款業(yè)務：<1>李銘2010-04-10取出1670.78元。setxact_abortonbegintransactionupdateBank_AccountsetBalance=Balance-1670.78where(AccountID=001001)insertintoBank_WithdrawNote(NoteNo,AccountID,Amount,NoteDate)values(111,'001001',1670.78,'2010-04-10')committransaction6.轉(zhuǎn)賬業(yè)務：<1>李銘2010-04-10轉(zhuǎn)帳500到張林。setxact_abortonbegintransactionupdateBank_AccountsetBalance=Balance-500where(AccountID=001001)updateBank_AccountsetBalance=Balance+500where(AccountID=001003)insertintoBank_TransferNote(NoteNo,FROMAccountID,TOAccountID,Amount,NoteDate)values(111,'001001','001002',500,'2009-07-14')committransaction7.注銷業(yè)務：<1>李銘2010-04-10日，因出國定居申請帳戶注銷。setxact_abortonbegintransactioninsertintoBank_DestoryNote(NoteNo,AccountID,NoteDate,NoteComment)values(111,'001001','2010-04-10','出國定居')updateBank_AccountsetAccountStatus='注銷'whereAccountID=001001committransaction7.查詢業(yè)務：<1>查詢所有帳戶的余額。selectBank_Account.BalancefromBank_Account<2>查詢劉鑫的所有轉(zhuǎn)帳記錄。select*fromBank_TransferNotewhereFROMAccountID=001003orTOAccountID=001003第八講關系模型和函數(shù)依賴〔1〕一、數(shù)據(jù)庫標準設計理論體系1.函數(shù)依賴：研究表字段之間的關系。2.范式：關系模式的設計的標準。3.模式分解：將不符合范式的關系模式分解為多個符合范式的關系模式。二、函數(shù)依賴〔FD〕1.定義：設有關系模式R(U)，其中U是R的屬性，U={A1,A2,A3,…,An},X,Y是U的子集，X∈U,Y∈U,設u和v的任意元組，如果u[X]=v[y],那么必有存在u[Y]=v[Y],那么稱屬性X函數(shù)決定屬性Y，或?qū)傩訷依賴屬性X，記為：X->YX稱為決定因子，Y稱為依賴因子。2.函數(shù)依賴集：關系R的所有函數(shù)依賴構成函數(shù)依賴集，記為F。3.類型：〔1〕平凡函數(shù)依賴：如果XY，但Y?X，那么稱XY是平凡的?！?〕非平凡函數(shù)依賴：如果XY，但Y!?X，那么稱XY是非平凡的〔3〕完全函數(shù)依賴：如果XY，有X的真子集X‘，不存在X’->Y.那么稱X->Y是完全依賴?！?〕局部函數(shù)依賴：如果XY，有X的真子集X‘，存在X’->Y.那么稱X->Y是完全依賴?！?〕傳遞函數(shù)依賴：如果XY且YZ，那么XZ是傳遞的。四、函數(shù)依賴蘊含1.定義：設有關系模式R(U,F),對于U的子集X和Y，如果F中使得X->Y成立，那么稱F蘊含X->Y。例：關系模式：員工(學號,姓名,專業(yè),系,分院) F={學號->姓名，學號->專業(yè)，專業(yè)->系，系->分院} 那么F蘊含：{學號->系}，{學號->分院}，{專業(yè)->分院}，{學號->姓名，專業(yè)，系，分院}這些依賴都蘊含在F中五、FD的邏輯推理1.自反律：如果Y?X，那么XY。2.傳遞律：如果XY且YZ，那么XZ。3.合成律：如果XY且XZ，那么X(Y,Z)成立。4.分解律：如果X(Y,Z)，那么XY且XZ。5.曾廣律：如果XY，那么XZYZ。六、函數(shù)依賴中得屬性的類型〔1〕主屬性〔2〕非屬性〔3〕左屬性〔4〕右屬性〔5〕雙重屬性〔6〕孤立屬性七、〔1〕候選鍵：有關系模式R(U，F(xiàn))，存在屬性集X，有XU，且有X的真子集X’，且X’U不成立，稱X是R的候選鍵?！?〕主鍵：R中有多個候選鍵時，選中一個作為主鍵?！?〕超鍵：包含候選鍵的屬性集稱為超鍵。第九講簡單查詢一、查詢語句select語句1.數(shù)據(jù)庫最主要功能是數(shù)據(jù)查詢。2.按功能分類：〔1〕簡單查詢〔2〕關聯(lián)查詢〔3〕分類匯總查詢〔4〕子查詢3.簡單查詢是指從一個表中進行數(shù)據(jù)查詢，查詢的數(shù)據(jù)只是簡單的字段或簡單的函數(shù)。二、select子句1.功能：用于確定查詢的數(shù)據(jù)項〔投影〕。2.語法：〔1〕selectall表示輸出所有記錄，包括重復記錄?！?〕selecttopn查詢前n條記錄〔3〕selecttopnpercent選擇查詢的前ｎ％的記錄。三、from子句1.功能：指定查詢數(shù)據(jù)的來源。2.語法：〔1〕from表名別名，表名別名…〔2〕from表名別名innerjoin表名別名on條件四、where子句1.功能：按指定的條件表達式對記錄進行篩選。2.語法：where條件表達式3.常見的表達式：(1)比擬運算符:>,<,>=,<=,<>(2)邏輯運算符:andornot(3)區(qū)間運算符:[not]betweenxandY(4)屬于運算符:in,notin(結(jié)果集合)(5)Null判斷運算符:isnull,isnotnull(6)模糊匹配符:like‘匹配符’,notlike‘匹配符’五、orderby子句1.功能：將查詢結(jié)果重新排列。2.語法：orderby工程[ASC/DESC]，工程[ASC/DESC]。六、模糊匹配運算符(1)%包含零個或更多字符的任意字符串。 WHEREtitleLIKE'%computer%'

(2)_〔下劃線〕任何單個字符 WHEREau_fnameLIKE'_ean'

(3)[]指定范圍([a-f])或集合([abcdef])中的任何單個字符 WHEREau_lnameLIKE'[C-P]arsen'

(4)[^]不屬于指定范圍([a-f])或集合([abcdef])的任何單個字符。 WHEREau_lnameLIKE'de[^l]%'

七、SQLServer的函數(shù)<1>Datalength(char):取得字符串的字節(jié)長度<2>Len(char):取得字符串的字符個數(shù)<3>Upper(char):將字符串轉(zhuǎn)換為大寫字母<4>Lower(char):將字符串轉(zhuǎn)換為小寫<1>Abs(x):取得x的絕對值<2>Sign(x):符號函數(shù)<3>Ceiling(x):大于等于x的最小整數(shù)<4>Floor(x):小于等于x的最大整數(shù)<1>GETDate():返回系統(tǒng)當前的時間(日期和時間)<2>DateName(part,date):以字符串的返回日期date的指定的局部，如年，月，日，時，分，秒等。<3>DatePart(part,date):以數(shù)值類型返回Date的指定局部，如年，月，日，時，分，秒等。<4>Year(date):返回date的年份<5>Month(date):返回date的月份<6>Day(Date):返回date的日期對于數(shù)據(jù)庫Northwind的簡單查詢‘1992年09月10日之后參加公司日期的員工姓名，參加公司日期，出生日期。selectLastName+FirstNameasneme,HireDate,BirthDatefromEmployeeswhereCountry='USA'andHireDate>'1992-9-10'2.查詢產(chǎn)品單價在20和30之間的產(chǎn)品的列表〔產(chǎn)品名稱，單價，庫存數(shù)量〕selectProductName,UnitPrice,UnitsInStockfromProductswhereUnitPricebetween20and303.查詢不是在1992年01月01日和1993年10月01日之間出生的員工列表(員工姓名，出生日期)。selectLastName+FirstNamenamee,BirthDatefromEmployeeswhereBirthDatenotbetween'1992-01-01'and'1993-10-01'4.查詢價格最高的10個產(chǎn)品的列表(產(chǎn)品名稱，單價)selecttop10ProductName,UnitPricefromProductsorderbyUnitPricedesc5.查詢員工姓或名中有字母N(n)的員工列表(員工姓名)(不區(qū)分大小寫)selectLastName+FirstNameasLastNameFirstNamefromEmployeeswhereLastName+FirstNamelike'%n%'6.查詢員工姓名只含有2個A的員工列表〔員工姓名〕〔不區(qū)分大小寫〕selectLastName+FirstNameasLastNameFirstNamefromEmployeeswhereLastName+FirstNamelike'%a%a%'andLastName+FirstNamenotlike'%a%a%a%'7.查詢姓名中第2個字母是’A’且倒數(shù)第2個字母是’C’的員工列表〔所有字段〕〔不區(qū)分大小寫〕selectLastName+FirstNameasLastNameFirstNamefromEmployeeswhereLastName+FirstNamelike'_A%'andLastName+FirstNamelike'%C_'8.查詢姓名中第三個字符是A或V或X或Y的員工列表〔員工姓名，職位，參加公司日期〕〔不區(qū)分大小寫〕selectLastName+FirstNameasLastNameFirstName,Title,HireDatefromEmployeeswhereLastName+FirstNamelike'__[AVXY]%'9.查詢員工姓名中第二個字母不是A且以Y，T，E字符結(jié)尾的員工列表〔員工姓名，出生日期〕selectLastName+FirstNameasLastNameFirstName,HireDatefromEmployeeswhereLastName+FirstNamelike'_[^A]%'andLastName+FirstNamelike'%[YTE]'14.查詢每個員工的姓名和參加公司的年份。select(LastName+FirstName)asname,datepart(yy,HireDate)asHireDateyyfromEmployees第十講關系模型-函數(shù)依賴一、函數(shù)依賴邏輯蘊含1.定義：設有關系模式R(U,F),X，Y是U的子集，如果X->Y的函數(shù)依賴可以從函數(shù)依賴集F推導出來，那么稱F邏輯蘊含X->Y。二、函數(shù)依賴集的閉包1.定義：在關系模式R(U,F)中，被函數(shù)依賴F所蘊含的函數(shù)依賴所構成的集合稱為函數(shù)依賴集F的閉包，記為F+。〔1〕定義：設關系模式R(U,F),屬性集合U={A1，A2，A3...An}，F(xiàn)是一個函數(shù)依賴集合，存在屬性集合X，Y，X?U，Y?U，通過函數(shù)依賴集合F，推導出所有X->Y的屬性集合Y，稱為X的閉包，記為X+X+={Y|X->A∈F+} X+稱為屬性集合X在F上的閉包。 X+實際包含屬性集合X在F+中決定的所有屬性集合?！?〕函數(shù)依賴集屬性閉包的求解例：模式R(U,F)，屬性集合U={A,B,C,D,E}F={B->A,D->A,A->E,AC->B,D->C}，求屬性AD的閉包。解：設V={A,D},求{A,D}+在函數(shù)依賴集F中，找到A->E,D->C,得V1={A,C,D,E}在V1中找到關系，有AC->B,得到V2={ABCDE}所以{AD}+={ABCDE}〔3〕根據(jù)F判斷指定的函數(shù)依賴是否成立例：關系R(U,F),U={A,B,C,D,E,F}F={AB->C,BC->AD,D->E,CF->B}判斷{AB}->{D}是否成立解：{AB}+={ABCDE}，所以AB->D成立三、最小函數(shù)依賴集F’’滿足的3個條件〔1〕F任何一個函數(shù)依賴的右邊僅有一個屬性?！?〕無冗余函數(shù)依賴，即F中不存在這樣的函數(shù)依賴X->A使得F與F—{X->A}等價?！?〕函數(shù)依賴左邊無冗余屬性，即F中不存在這樣的函數(shù)依賴集X->A,X有真子集X’，使得(F—{X->A})U{X’->A}與F等價?！蠼狻?〕右邊拆分成單屬性?！?〕依次檢查每個函數(shù)是否多余G=F—{X->Y},求X+如X+包含Y,X->Y多余〔3〕依次檢查非單屬性，看某屬性是否多余。例：關系R(U,F),U={A,B,C,D,E,F}F={AB->C,C->A,BC->D,ACD->B,BE->C,CE->FA,CF->BD,D->EF}解：1〕F={AB->C,C->A,BC->D,ACD->B,BE->C,CE->F,CE->A,CF->BCF->D,D->E,D->F}2)去掉AB->C，求{AB}+，假設{AB}+能推導AB->C那么F中去掉AB->C依次檢查得ACD->B,CE->A,CF->D是冗余的函數(shù)依賴3〕去掉F中得所有依賴左邊的冗余屬性，先去掉屬性中得一個屬性，如AB->C,先去掉A，看B->C是否依然成立，假設B->C成立，那么屬性A冗余，依次檢驗可得：F’={AB->C,C->A,BC->D,BE->C,CE->F,CF->B,D->E,D->F}四、關系模式候選鍵的求解〔1〕左屬性和孤立屬性一定在候選鍵中〔2〕右屬性不會出現(xiàn)子啊候選鍵中，即右屬性對候選鍵沒有奉獻〔3〕步驟：1〕求最小函數(shù)依賴F’2〕查找左屬性集合L，孤立屬性S，雙重屬性D3〕求(LS)+，如果(LS)+=U，那么LS是R的唯一候選鍵4〕假設(LS)+!=U，那么求D的子集D’，計算(LSD’)+，假設(LSD’)+=U，那么LSD’是候選鍵例：關系R(U,F)，U={A,B,C,D,E}F={AB->C,AC->E,DE->B}求候選鍵解：左屬性L={A,D}孤立屬性S=Φ(AD)+={AD}!=U,雙重屬性D={B,C,E}所以(ADB)+=(ADC)+=(ADE)+=(ABCDE)=U候選鍵集合為{ADB，ADC，ADE}第十一講關聯(lián)查詢和分類匯總查詢連接查詢1.定義：從兩個或兩個以上數(shù)據(jù)源查詢。2.類型：〔1〕內(nèi)連接〔2〕外連接〔3〕交叉連接3.條件：〔1〕兩表有公共屬性〔2〕合法關聯(lián)條件：FK->PK二、內(nèi)連接查詢1.定義：使用比擬運算符進行表間某(些)列數(shù)據(jù)的比擬操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行。2.功能：用于查詢多個表中同時滿足關聯(lián)條件的記錄的數(shù)據(jù)。3.類型：〔1〕等值連接：連接條件是相等關系，在連接條件中使用等號運算符比擬連接列的列值，按對應列的共同值將一個表中的記錄與另一個表中的記錄相連接，包括其中的重復列。語法1：from中指定等值條件Select....F語法2：where中指定等值條件Select….From表1a，表2b，表3cW〔2〕不等值連接：當關聯(lián)的條件使用費等號就稱為不等值連接?！?〕自然連接：連接條件使用等號，使用選擇列表方式來指定查詢結(jié)果集合中所包含的列，刪除連接表中的重復列。三、外連接查詢〔outerjoin〕1.定義：當將多表連接查詢時，外連接不只列出與連接條件相匹配的行，而且列出左表(左外連接時)、右表(右外連接時)或兩個表(全外連接時)中所有符合搜索條件的數(shù)據(jù)行。2.類型：〔1〕左外連接查詢：使用leftouterjoin關聯(lián)詞連接多表，顯示左表的全部記錄，如果右表有關聯(lián)記錄那么顯示，無關聯(lián)記錄那么顯示null〔2〕右外連接查詢：使用rightouterjoin關聯(lián)詞連接多表.與左外關聯(lián)相反，顯示右表的所有記錄，如果左表有關聯(lián)記錄那么顯示，否那么顯示顯示null?！?〕全外關聯(lián)查詢：使用fullouterjoin關聯(lián)多表。同時實現(xiàn)左關聯(lián)和右關聯(lián)的功能。四、交叉連接〔crossjoin〕1．定義：交叉連接不帶WHERE子句，它返回被連接的兩個表所有數(shù)據(jù)行的笛卡爾積，返回到結(jié)果集合中的數(shù)據(jù)行數(shù)等于第一個表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個表中符合查詢條件的數(shù)據(jù)行數(shù)。2.語法：select…fromtable1crossjointable2五、分類匯總查詢1.數(shù)據(jù)庫中保存的原始單據(jù)數(shù)據(jù)。2.SQL的匯總函數(shù)：〔1〕count(x)：可用于字符、數(shù)值和日期型數(shù)據(jù)。count(*)計算表中行總數(shù)，count(column)計算列不為null的行數(shù)，count(distinctcolumn)計算指定列中相異值的數(shù)量〔2〕max(x)：列中最大值?？捎糜谧址?shù)值和日期型數(shù)據(jù)〔3〕min(x)：列中最小值?？捎糜谧址?、數(shù)值和日期型數(shù)據(jù)〔4〕avg(x)：計算所有值的平均數(shù)。只用于數(shù)值型數(shù)據(jù)，忽略null?！?〕sum(x)：計算所有值的總和。只用于數(shù)值型數(shù)據(jù)例：(1).查詢產(chǎn)品類型為2的產(chǎn)品的平均單價。〔平均單價〕selectavg(Products.UnitPrice)namefromProductswhereProducts.CategoryID=2(2).查詢最低和最高的產(chǎn)品的銷售單價?！伯a(chǎn)品名稱，最低銷售單價，最高銷售單價〕selecta.ProductName,min(b.UnitPrice)min,max(b.UnitPrice)maxfromProductsainnerjoin[OrderDetails]b(3)查詢每個訂單的訂單號，訂單日期，員工姓名，客戶名稱。fromOrdersainnerjoinEmployeesbinnerjoinCustomersc(4)查詢1997年下半年的訂單號，訂單日期，客戶名稱，員工名稱。fromOrdersainnerjoinEmployeesbinnerjoinCustomerscwhereyear(a.OrderDate)=1997anddatename(qq,a.OrderDate)in(3,4)(5)查詢每個客戶的歷年的銷售統(tǒng)計信息〔客戶名稱，年份，銷售金額〕。selecta.ContactName,year(b.OrderDate)yy,sum(c.UnitPrice)sumfromCustomersainnerjoinOrdersbinnerjoin[OrderDetails]cgroupbya.ContactName,year(b.OrderDate)(6)查詢每個產(chǎn)品的歷年銷售信息〔產(chǎn)品名稱，年份，銷售額〕selecta.ProductName,year(c.OrderDate)yy,sum(b.UnitPrice*b.Quantity)sumfromProductsainnerjoin[OrderDetails]binnerjoinOrderscgroupbya.ProductName,year(c.OrderDate)(7)查詢每個1997年每個產(chǎn)品各個季度的銷售信息〔產(chǎn)品名稱，季度，銷售金額〕selecta.ProductName,datepart(qq,c.OrderDate)qq,sum(b.UnitPrice*b.Quantity)sumfromProductsainnerjoin[OrderDetails]binnerjoinOrderscwheredatepart(yy,c.OrderDate)=1997groupbya.ProductName,datepart(qq,c.OrderDate)(8)查詢每個員工和他的上級的名稱〔員工姓名，上級姓名〕。selecta.LastName+a.FirstNameReportsTo,b.LastName+b.FirstNamenamefromEmployeesarightjoinEmployeesb第十二講關系模式的標準化標準化1.定義：把一組有異常的關系分解成更小的，結(jié)構良好的關系的過程。2.理論體系：〔1〕函數(shù)依賴〔2〕范式〔3〕模式分解3.原那么：〔1〕不能喪失標準化前原有模式的任何信息。〔2〕當重構原始模式時，不能包含偽信息，即多于原始信息的額外信息?！?〕保存原有模式中的函數(shù)依賴，即原有關系模式的函數(shù)依賴都要保持。4.關系模式設計中存在的問題：〔1〕數(shù)據(jù)大量冗余〔2〕更新異常：執(zhí)行updata后產(chǎn)生數(shù)據(jù)不一致〔3〕數(shù)據(jù)插入異常〔4〕刪除異常：刪除一個信息會刪除其他信息二、范式1.定義：是關系的一種狀態(tài)，是將函數(shù)依賴的簡單規(guī)那么應用到關系的結(jié)果，是關系標準化的一種度量，說明關系標準化的程度。2.類型：1NF，2NF，3NF，BCNF，4NF，5NF三、第一范式〔1NF〕1.定義：如果關系中所有的屬性域的值都是原子的，屬性不可再分。S#C#S1{C1，C2，C3}S#C#S1C1S1C2S1C3不符合1NF符合1NF四、第二范式〔2NF〕1.定義：如果關系中R屬于1NF，并且R中的每個非屬性都完全函數(shù)依賴于R的候選鍵。2.2NF中關系的屬性都不函數(shù)依賴于復合候選鍵的一局部。符合2NF：學生選課關系 選課(課程號，學生號，成績)不符合2NF：選課(學生號,學生名,課程號,課程名,成績) 函數(shù)依賴:學生號->學生名課程號->課程名學生號,課程號->成績 主鍵:{學生號，課程號} 存在:非主屬性局部依賴主屬性，不是完全依賴主屬性。不符合2NF。五、第三范式〔3NF〕1.定義：如果R屬于2NF，且不存在非主屬性傳遞依賴于候選鍵。2.特征：(1)首先要滿足第二范式， (2)其次非主屬性之間不存在函數(shù)依賴。由于滿足了第二范式，表示每個非主屬性都函數(shù)依賴于主鍵。如果非主屬性之間存在了函數(shù)依賴，就會存在傳遞依賴，這樣就不滿足第三范式。符合3NF客戶關系模式: Customer(CustomerID，CustomerName，CustomerAddr，CustomerCity)不符合3NF的關系模式訂單表【Order】〔OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity〕 主鍵是〔OrderID〕。

其中

OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity

等非主鍵列都完全依賴于主鍵〔OrderID〕，所以符合

2NF。不過問題是

CustomerName，CustomerAddr，CustomerCity

直接依賴的是CustomerID〔非主鍵列〕，而不是直接依賴于主鍵，它是通過傳遞才依賴于主鍵，所以不符合

3NF。六、BCNF1，定義：假設R屬于3NF，且不存在主屬性局部依賴于候選鍵，即任何一個函數(shù)依賴X->Y，X一定是候選鍵。2.BCNF的實質(zhì)是所有的函數(shù)依賴左邊都是超鍵。即函數(shù)依賴左邊都包含候選鍵?！?〕符合BCNF的關系模式學生(學號，姓名，性別)函數(shù)依賴：學號->姓名，性別候選鍵：學號分析：函數(shù)依賴左邊是候選鍵。 符合:BCNF不符合BCNF的關系模式地址關系：CSZ(CITY城市，ST街道，ZIP)，F(xiàn)＝｛(CITY，ST)→ZIP，ZIP→CITY｝。 候選鍵：{(CITY,ST)，(ST,ZIP)}(CITY,ST)和(ST,ZIP)是兩個候選碼，沒有非主屬性，自然CSZ∈3NF。但存在傳遞依賴(CITY,ST)->ZIP->CITY,所以CSZ?BCNF第十三講子查詢一、子查詢1.定義：將嵌入在其他SQL語句的SELECT語句稱為子查詢，也稱嵌套查詢。2.子查詢經(jīng)常嵌入的語句：(1)select(2)insert(3)updata(4)delete3.類型;〔1〕按SQL語句類型分：1)select中子查詢2)insert中子查詢3)updata中子查詢4)delete中子查詢〔2〕按子查詢返回結(jié)果分：1)單行單列子查詢2)多行單列子查詢3)多行多列子查詢〔3〕按位置分：1)select中子查詢2)from中子查詢3)where中子查詢4)having中子查詢〔4〕按與主SQL語句相關與否分：1)無關子查詢2)相關子查詢二、select中的子查詢1.功能：select子查詢作為一個數(shù)據(jù)項。2.限制：必須是單行單列，可以是相關也可以無關。三、from中的子查詢1.功能:提供查詢用的數(shù)據(jù)來源，將子查詢作為一個中間表。2.限制：一般是多行單列，或多行多列子查詢，但必須是無關子查詢。四、where中子查詢1.功能：給篩選條件提供判斷結(jié)果。2.類型：〔1〕單行單列子查詢：可使用比擬運算符：>,>=,<,<=,<>,!=,between…and〔2〕多行單列子查詢：可使用in，notin或結(jié)果轉(zhuǎn)換符all，any，some轉(zhuǎn)換為單值?！?〕相關子查詢的判斷exists五、having中子查詢1.功能：where為每條記錄進行篩選，而having子句那么為匯總后的結(jié)果提供篩選。例：1.查詢參加公司最晚的員工〔員工姓名，參加公司日期〕selectLastName+FirstNameasNames,HireDatefromEmployeesawhere(a.HireDate=(selectmax(HireDate)fromEmployees))2.查詢產(chǎn)品單價小于所有產(chǎn)品平均單價的產(chǎn)品及庫存數(shù)量(產(chǎn)品名稱，單價，所有產(chǎn)品平均單價)。selecta.ProductNameasmm,UnitPrice,avg(UnitPrice)asaafromProductsawhere(a.UnitPrice<(selectavg(b.UnitPrice)fromProductsb))3.查詢產(chǎn)品單價小于所屬類別平均單價的產(chǎn)品和單價信息(產(chǎn)品名稱，單價，所屬類別平均單價)。selectProductName,UnitPrice,(selectAvg(UnitPrice)fromProductsbwherea.CategoryID=b.CategoryID)avgunitprice2fromProductsawherea.unitprice<(selectAvg(UnitPrice)fromProductsbwherea.CategoryID=b.CategoryID)4.查詢1996年各個季度的銷售額，且大于1996年中各季度的平均銷售。〔季度，銷售額〕selectmonth(a.orderdate)mm,sum(b.unitprice*b.quantity)amountfromOrdersainnerjoin[orderdetails]bwhereyear(a.orderdate)=1997groupbymonth(a.