數(shù)據(jù)庫技術(shù)簡介 1 本章內(nèi)容 應(yīng)用背景數(shù)據(jù)庫應(yīng)用程序設(shè)計(jì)方案簡介SQLSERVER簡介SQLSERVE組件SQLSERVER版本SQLSERVER數(shù)據(jù)庫中的對(duì)象 應(yīng)用背景 隨著計(jì)算機(jī)技術(shù)的蓬勃發(fā)展 計(jì)算機(jī)應(yīng)用已經(jīng)涉及人們?nèi)粘Ｉ?工作的各個(gè)領(lǐng)域 數(shù)據(jù)處理是計(jì)算機(jī)四大應(yīng)用 科學(xué)計(jì)算 過程控制 數(shù)據(jù)處理和輔助設(shè)計(jì) 的一個(gè)主要方面 而且已經(jīng)滲透到許多其他應(yīng)用領(lǐng)域 1 1數(shù)據(jù)庫系統(tǒng)應(yīng)用程序設(shè)計(jì)方案簡介 數(shù)據(jù)庫和數(shù)據(jù)庫系統(tǒng)的基本概念幾種數(shù)據(jù)庫系統(tǒng)應(yīng)用程序模型常用的數(shù)據(jù)庫訪問接口 數(shù)據(jù)庫及數(shù)據(jù)庫系統(tǒng)概念 數(shù)據(jù)庫 顧名思義 是存放數(shù)據(jù)的倉庫數(shù)據(jù)庫系統(tǒng)是指在計(jì)算機(jī)系統(tǒng)中引入數(shù)據(jù)庫后的系統(tǒng) 一般由數(shù)據(jù)庫 數(shù)據(jù)庫管理系統(tǒng) 應(yīng)用程序系統(tǒng)和數(shù)據(jù)庫用戶幾個(gè)部分組成 數(shù)據(jù)庫系統(tǒng)應(yīng)用程序模型 單層模型 基于早期的文件和程序集成的一種開發(fā)模型兩層模型 又稱C S模型 將數(shù)據(jù)和程序代碼分離 采用客戶端請(qǐng)求和服務(wù)端響應(yīng)方式實(shí)現(xiàn)數(shù)據(jù)共享三層 N層模型 又稱B S模型 將代碼中的界面表示 表示層 和程序邏輯 商業(yè)規(guī)則 中間層 分離 數(shù)據(jù)訪問更加靈活 常用數(shù)據(jù)庫訪問接口 開放數(shù)據(jù)庫互聯(lián) ODBC 和DB庫內(nèi)嵌SQL數(shù)據(jù)訪問對(duì)象 DataAccessObject DAO 遠(yuǎn)程數(shù)據(jù)庫對(duì)象 RemoteDataObject RDO OLEDBADO ActiveDataObject ADO Net ADO 1 2SQLServer簡介 SQLServer起源SQLServer不是一個(gè)數(shù)據(jù)庫 它是一種客戶機(jī) 服務(wù)器關(guān)系型數(shù)據(jù)庫管理系統(tǒng) 簡稱RDBMSSQLServer是一個(gè)作為服務(wù)運(yùn)行的Windows應(yīng)用程序SQLServer是一個(gè)全面的 集成的 端到端的數(shù)據(jù)解決方案 SQLServer的特點(diǎn) 真正的客戶機(jī) 服務(wù)器體系結(jié)構(gòu) 圖形化用戶界面豐富的編程接口工具SQLServer與WindowsNT完全集成具有很好的伸縮性對(duì)Web技術(shù)的支持SQLServer提供數(shù)據(jù)倉庫功能 SQLServer2005具有的新特性 實(shí)現(xiàn)了與MicrosoftVisualStudio MicrosoftOfficeSystem和新的開發(fā)工具包的緊密集成增強(qiáng)的報(bào)表服務(wù)增強(qiáng)的數(shù)據(jù)訪問接口增強(qiáng)的數(shù)據(jù)分析服務(wù)增強(qiáng)的數(shù)據(jù)復(fù)制服務(wù)改進(jìn)的開發(fā)工具可以為不同規(guī)模的企業(yè)構(gòu)建和部署經(jīng)濟(jì)有效的BI解決方案 1 3SQLServer的組件 數(shù)據(jù)庫引擎 DatabaseEngine 分析服務(wù) AnalysisServices 報(bào)表服務(wù) ReportingService 集成服務(wù) IntegrationServiceSQLServer2005管理平臺(tái)商業(yè)智能開發(fā)平臺(tái)配置管理器SQLServer聯(lián)機(jī)叢書 1 4SQLServer2005的版本 SQLServer2005企業(yè)版SQLServer2005標(biāo)準(zhǔn)版SQLServer2005工作組版SQLServer2005學(xué)習(xí)版SQLServer2005開發(fā)版SQLServer2005移動(dòng)版 1 5SQLServer中常用的數(shù)據(jù)對(duì)象 表數(shù)據(jù)類型視圖存儲(chǔ)過程觸發(fā)器約束索引 數(shù)據(jù)庫對(duì)象 數(shù)據(jù)庫對(duì)象的引用方法 全限定名稱 SQLServer對(duì)象的完整名稱包括4個(gè)標(biāo)識(shí)符 服務(wù)器名稱 數(shù)據(jù)庫名稱 對(duì)象的所有者名稱和對(duì)象名稱 格式如下 Server database owner object Server database objectServer owner objectServer objectdatabase owner objectdatabase objectowner objectobject 部分指定的名稱 管理數(shù)據(jù)庫 介紹數(shù)據(jù)庫管理員最核心的工作 創(chuàng)建和管理SQLServer數(shù)據(jù)庫 介紹如何使用SQLServer管理器和系統(tǒng)存儲(chǔ)過程實(shí)現(xiàn)大多數(shù)與數(shù)據(jù)庫相關(guān)的任務(wù) 內(nèi)容提要 創(chuàng)建數(shù)據(jù)庫修改數(shù)據(jù)庫刪除數(shù)據(jù)庫數(shù)據(jù)庫備份數(shù)據(jù)庫還原系統(tǒng)數(shù)據(jù)庫介紹 4 1創(chuàng)建數(shù)據(jù)庫 SQLServer的數(shù)據(jù)庫 一般至少包括兩個(gè)文件 數(shù)據(jù)文件 用來存儲(chǔ)數(shù)據(jù)庫的數(shù)據(jù)和對(duì)象 如表 索引 存儲(chǔ)過程和視圖等 默認(rèn)的擴(kuò)展名為 mdf 日志文件 用來存儲(chǔ)日志的文件 包括恢復(fù)數(shù)據(jù)中的所有事務(wù)所需的信息 擴(kuò)展名為 ldf 創(chuàng)建新數(shù)據(jù)庫的注意事項(xiàng) 默認(rèn)情況下 只有系統(tǒng)管理員可以創(chuàng)建新數(shù)據(jù)庫 給數(shù)據(jù)庫指定的名字必須遵循SQLServer命名規(guī)范 所有的新數(shù)據(jù)庫都是model數(shù)據(jù)庫的備份 這意味著新數(shù)據(jù)庫不可能比model數(shù)據(jù)庫當(dāng)前的容量更小 單個(gè)數(shù)據(jù)庫可以存儲(chǔ)在單個(gè)文件上 也可以跨越多個(gè)文件存儲(chǔ) 數(shù)據(jù)庫的大小可以被擴(kuò)展或者收縮 當(dāng)新的數(shù)據(jù)庫創(chuàng)建時(shí) SQLServer自動(dòng)地更新master數(shù)據(jù)庫的sysdatabases系統(tǒng)表 文件與文件組 數(shù)據(jù)庫文件 存放數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對(duì)象的文件主數(shù)據(jù)庫文件 MDF文件次數(shù)據(jù)庫文件 NDF文件日志文件 是用來記錄數(shù)據(jù)庫更新情況的文件主日志文件次日志文件文件組 將多個(gè)數(shù)據(jù)庫文件集合起來形成的一個(gè)整體主文件組次文件組 使用管理器創(chuàng)建數(shù)據(jù)庫 1 首先確保已經(jīng)成功連接到服務(wù)器2 選擇 對(duì)象資源管理器 面板中的 數(shù)據(jù)庫 選項(xiàng)并右擊 在彈出的快捷菜單中選擇 新建數(shù)據(jù)庫 選項(xiàng) 在彈出窗口的 數(shù)據(jù)庫名稱 中輸入數(shù)據(jù)庫名 Demo 所有者使用默認(rèn)值 設(shè)置好參數(shù)之后 單擊 確定 按鈕即可創(chuàng)建一個(gè)以Demo命名的數(shù)據(jù)庫 使用Transact SQL語句創(chuàng)建數(shù)據(jù)庫 CREATEDATABASE語句的語法 CREATEDATABASEdatabase name ON PRIMARY n n LOGON n FORLOAD FORATTACH 創(chuàng)建50MB數(shù)據(jù)和15MB日志的Demo數(shù)據(jù)庫 CREATEDATABASEDemoONPRIMARY NAME Demo data FILENAME c Demo mdf SIZE 50MB MAXSIZE 70MB FILEGROWTH 1MB LOGON NAME Demo log FILENAME c Demo ldf SIZE 15MB MAXSIZE 20MB FILEGROWTH 1MB 數(shù)據(jù)庫對(duì)象屬性設(shè)置 4 2修改數(shù)據(jù)庫 使用SQLServer管理器修改數(shù)據(jù)庫打開數(shù)據(jù)庫屬性對(duì)話框修改數(shù)據(jù)文件大小修改日志文件大小按確定保存 使用Transact SQL語句修改數(shù)據(jù)庫 語法 ALTERDATABASEdatabase ADDFILE n TOFILEGROUPfilegroup name ADDLOGFILE n REMOVEFILElogical file name ADDFILEGROUPfilegroup name REMOVEFILEGROUPfilegroup name MODIFYFILE MODIFYFILEGROUPfilegroup namefilegroup property 修改數(shù)據(jù)庫文件和日志文件 例如 USEmasterALTERDATABASEDemoMODIFYFILE NAME Demo log MAXSIZE 25MB GOUSEmasterALTERDATABASEDemoMODIFYFILE NAME Demo log SIZE 20MB GO 4 3刪除數(shù)據(jù)庫 DROPDATABASE語句的語法 例如 表示刪除當(dāng)前的MyDB DROPDATABASEDemo DROPDATABASEdatabase name n 4 4數(shù)據(jù)庫備份和恢復(fù) 備份備份就是指對(duì)SQLServer數(shù)據(jù)庫或事務(wù)日志進(jìn)行復(fù)制 數(shù)據(jù)庫備份記錄了在進(jìn)行備份這一操作時(shí)數(shù)據(jù)庫中所有數(shù)據(jù)的狀態(tài) 如果數(shù)據(jù)庫因意外而損壞 這些備份文件將在數(shù)據(jù)庫恢復(fù)時(shí)被用來恢復(fù)數(shù)據(jù)庫 恢復(fù)恢復(fù)就是把遭受破壞 丟失數(shù)據(jù)或出現(xiàn)錯(cuò)誤的數(shù)據(jù)庫恢復(fù)到原來的正常狀態(tài) 這一狀態(tài)是由備份決定的 但是為了維護(hù)數(shù)據(jù)庫的一致性 在備份中未完成的事務(wù)并不進(jìn)行恢復(fù) 數(shù)據(jù)庫備份類型 數(shù)據(jù)庫備份數(shù)據(jù)庫備份是指對(duì)數(shù)據(jù)庫的完整備份 包括所有的數(shù)據(jù)及數(shù)據(jù)庫對(duì)象 事務(wù)日志備份事務(wù)日志備份是指對(duì)數(shù)據(jù)庫發(fā)生的事務(wù)進(jìn)行備份 包括從上次進(jìn)行事務(wù)日志備份 差異備份和數(shù)據(jù)庫完全備份之后 所有已經(jīng)完成的事務(wù) 差異備份差異備份是指將最近一次數(shù)據(jù)庫備份以來發(fā)生的數(shù)據(jù)變化備份起來 因此差異備份實(shí)際上是一種增量數(shù)據(jù)庫備份 文件和文件組備份文件或文件組備份是指對(duì)數(shù)據(jù)庫文件或文件夾進(jìn)行備份 但其不像完整的數(shù)據(jù)庫備份那樣同時(shí)也進(jìn)行事務(wù)日志備份 創(chuàng)建和刪除備份設(shè)備 使用SQLServer管理器創(chuàng)建備份設(shè)備使用sp addumpdevice創(chuàng)建備份設(shè)備usemasterexecsp addumpdevice disk pubss c backdev backdevpubs bak 使用SQLServer管理器刪除備份設(shè)備使用sp dropdevice刪除備份設(shè)備sp dropdevice pubss c backdev backdevpubs bak 使用管理器備份數(shù)據(jù)庫 用SQLServer管理器備份數(shù)據(jù)庫 用Transact SQL語句備份數(shù)據(jù)庫 創(chuàng)建備份設(shè)備 usemasterexecsp addumpdevice disk pubss c mssql7 backup pubss dat 備份數(shù)據(jù)庫 backupdatabasepubstopubss 創(chuàng)建事務(wù)日志備份設(shè)備 usemasterexecsp addumpdevice disk pubsslog c mssql7 backup pubsslog dat 備份事務(wù)日志 backuplogpubstopubsslog 4 5還原數(shù)據(jù)庫 使用管理器還原數(shù)據(jù)庫 使用RESTORE命令還原數(shù)據(jù)庫 例如 還原數(shù)據(jù)庫pubs至2009年4月1日前的狀態(tài) Restoredatabasepubsfrompubss1 pubss2withnorecoveryrestorelogpubsfrompubsslog1withnorecoveryrestorelogpubsfrompubslog2withrecovery stopat Apr1200915 00am 4 6SQLServer系統(tǒng)數(shù)據(jù)庫介紹 數(shù)據(jù)庫目錄系統(tǒng)表每個(gè)數(shù)據(jù)庫都使用數(shù)據(jù)庫目錄系統(tǒng)表來記錄它的設(shè)計(jì)和使用情況 每一個(gè)SQLServer創(chuàng)建的數(shù)據(jù)庫都包含下列19個(gè)數(shù)據(jù)庫目錄系統(tǒng)表master數(shù)據(jù)庫master數(shù)據(jù)庫是SQLServer中最重要的數(shù)據(jù)庫 它存儲(chǔ)的信息包括可用的數(shù)據(jù)庫 為每一個(gè)數(shù)據(jù)庫分配的空間 使用中的進(jìn)程 用戶賬戶 活動(dòng)的鎖 系統(tǒng)錯(cuò)誤信息和系統(tǒng)存儲(chǔ)過程等 master數(shù)據(jù)存儲(chǔ)在master mdf中 事務(wù)日志存儲(chǔ)在mastlog ldf中 由于這個(gè)數(shù)據(jù)庫非常重要 所以不允許用戶直接修改它 msdb數(shù)據(jù)庫msdb數(shù)據(jù)庫由SQLServerAgent服務(wù)使用 用來管理警報(bào)和任務(wù) 它還存儲(chǔ)由SQLServer管理的數(shù)據(jù)庫的每一次備份和恢復(fù)的歷史信息 msdb數(shù)據(jù)存儲(chǔ)在msdbdata mdf中 它的事務(wù)日志存儲(chǔ)在msdblog ldf中 model數(shù)據(jù)庫任何一個(gè)新創(chuàng)建的數(shù)據(jù)庫都包含前面描述的19個(gè)系統(tǒng)表 這19個(gè)系統(tǒng)表是在創(chuàng)建新數(shù)據(jù)庫時(shí)從model數(shù)據(jù)庫復(fù)制而來的 model數(shù)據(jù)庫的主要作用是為新的數(shù)據(jù)庫充當(dāng)模板 tempdb數(shù)據(jù)庫tempdb數(shù)據(jù)庫是被所有SQLServer數(shù)據(jù)庫和數(shù)據(jù)庫用戶共享的數(shù)據(jù)庫 它用來存儲(chǔ)臨時(shí)信息 如對(duì)一個(gè)未建索引的表查詢時(shí)創(chuàng)建的臨時(shí)索引的排序信息 管理數(shù)據(jù)庫及其完整性 內(nèi)容提要 表是SQLServer2005中最基本的數(shù)據(jù)庫對(duì)象 它包含了數(shù)據(jù)庫中所有的數(shù)據(jù) 表設(shè)計(jì)的好壞直接決定著一個(gè)數(shù)據(jù)庫中的好壞 從而決定這個(gè)整個(gè)數(shù)據(jù)庫應(yīng)用系統(tǒng)的成敗 在數(shù)據(jù)庫中 表表現(xiàn)為列的集合 與電子表格相似 數(shù)據(jù)在表中是按行和列的格式組織排列的 每行代表唯一的一條記錄 而每列代表記錄中的一個(gè)域 要點(diǎn) 創(chuàng)建表修改表刪除表創(chuàng)建和使用規(guī)則創(chuàng)建和使用默認(rèn) 5 1創(chuàng)建表 表的設(shè)計(jì)過程中需要完成的主要任務(wù) 設(shè)計(jì)表的各列及每一列的數(shù)據(jù)類型 盡量使表中不出現(xiàn)冗余信息 使其達(dá)到三級(jí)范式要求 決定哪些列允許空值 某些列必須有值數(shù)據(jù)庫才能正確運(yùn)行 決定是否要在表中使用以及何時(shí)使用約束 默認(rèn)設(shè)置或規(guī)則 所需索引的類型 哪里需要索引 哪些列是主鍵 哪些是外鍵 使用SQLServer管理器創(chuàng)建表 1展開對(duì)象資源管理器中的 數(shù)據(jù)庫 Demo 選項(xiàng) 右擊 表 對(duì)象 在彈出的菜單中選擇 新建表 選項(xiàng)2進(jìn)入表設(shè)計(jì)界面 切換到 屬性 面板 輸入表名 在 列名 和 數(shù)據(jù)類型 中填入相應(yīng)的數(shù)據(jù)信息 設(shè)置好主鍵 單擊 保存 使用Transact SQL語句創(chuàng)建表 創(chuàng)建表的Transact SQL語句為CREATETABLE CREATETABLE的語法格式 CREATETABLE database name owner owner table name column nameAScomputed column expression CONSTRAINTconstraint name PRIMARYKEY UNIQUE n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT 創(chuàng)建學(xué)生信息表Student CREATETABLEStudent IDchar 7 notnull Namechar 10 notnull Sexchar 2 notnull Birthdaydatetimenotnull Birth placevarchar 50 notnull Classchar 10 notnull 創(chuàng)建和使用5種基本約束 PRIMARYKEY 主鍵約束 FOREIGNKEY 外鍵約束 UNIQUE 唯一約束 CHECK 檢查約束 DEFAULT 默認(rèn)值約束 數(shù)據(jù)完整性分類 實(shí)體完整性域完整性參照完整性 PRIMARYKEY約束 列級(jí)PRIMARYKEY約束的定義格式 表級(jí)PRIMARYKEY約束的定義格式 CONSTRAINTconstraint name PRIMARYKEY CLUSTERED NONCLUSTERED WITHFILLFACTOR fillfactor ON filegroup DEFAULT CONSTRAINTconstraint name PRIMARYKEY CLUSTERED NONCLUSTERED column n WITHFILLFACTOR fillfactor ON filegroup DEFAULT FOREIGNKEY約束 表級(jí)FOREIGNKEY約束的定義格式 列級(jí)FOREIGNKEY約束的定義格式 CONSTRAINTconstraint name FOREIGNKEY column n REFERENCESref table ref column NOTFORREPLICATION CONSTRAINTconstraint name FOREIGNKEY REFERENCESref table ref column NOTFORREPLICATION UNIQUE約束 列級(jí)UNIQUE約束的定義格式 表級(jí)UNIQUE約束的定義格式 CONSTRAINTconstraint name UNIQUE CLUSTERED NONCLUSTERED WITHFILLFACTOR fillfactor ON filegroup DEFAULT CONSTRAINTconstraint name UNIQUE CLUSTERED NONCLUSTERED column n WITHFILLFACTOR fillfactor ON filegroup DEFAULT CHECK約束 表級(jí)CHECK約束定義格式 列級(jí)CHECK約束定義格式 CONSTRAINTconstraint name CHECK NOTFORREPLICATION 搜索條件 CONSTRAINTconstraint name CHECK NOTFORREPLICATION 邏輯表達(dá)式 DEFAULT約束 constraint name 指出所建立的DEFAULT約束的名稱 constant expression表達(dá)式為列提供默認(rèn)值 CONSTRAINTconstraint name DEFAULTconstant expression 5 2修改表 ALTERTABLE語句的語法ALTERTABLEtable ALTERCOLUMNcolumn name new data type precision scale COLLATE NULL NOTNULL ADD DROP ROWGUIDCOL ADD column nameAScomputed column expression n WITHCHECK WITHNOCHECK ADD n DROP CONSTRAINT constraint name COLUMNcolumn n CHECK NOCHECK CONSTRAINT ALL constraint name n ENABLE DISABLE TRIGGER ALL trigger name n CREATETABLEdoc exa column aINT GOALTERTABLEdoc exaADDcolumn bVARCHAR 20 NULLALTERTABLEdoc excADDcolumn bVARCHAR 20 NULLCONSTRAINTexb uniqueUNIQUEALTERTABLEdoc exdWITHNOCHECKADDCONSTRAINTexd checkCHECK column a 1 GOEXECsp helpdoc exaGODROPTABLEdoc exaGO 5 3查看表 sp help的語法格式 其中 name參數(shù)說明所檢索表的名稱 sp helpname 5 4刪除表 執(zhí)行DROPTABLE語句刪除數(shù)據(jù)表 DROPTABLE的語法格式 其中 table name為待刪除表的名稱 DROPTABLEtable name 5 5創(chuàng)建和使用規(guī)則 創(chuàng)建規(guī)則在Transact SQL中 執(zhí)行CREATERULE語句創(chuàng)建規(guī)則 CREATERULE的語法格式 CREATERULEruleAScondition expression例如 CREATERULErange ruleAS range 1000AND range 20050 規(guī)則應(yīng)用 sp bindrule的語法格式 sp bindrule rulename rule objname object name futureonly futureonly flag 例如 USEmasterEXECsp bindrulerange rule employees Salary 解除規(guī)則 解除綁定規(guī)則 sp unbindrule sp unbindrule objname object name futureonly futureonly flag 例如 EXECsp unbindrule employees salary 刪除規(guī)則 刪除規(guī)則 DROPRULE rule n 例如 DropRulerange rule 5 6創(chuàng)建和使用默認(rèn) 創(chuàng)建默認(rèn) CREATEDEFAULT語句 語法 CREATEDEFAULTdefaultASconstant expression例如 CREATEDEFAULTphonedfltAS unknown 綁定默認(rèn) 綁定默認(rèn)值 sp bindefault語法 sp bindefault defname default objname object name futureonly futureonly flag 例如 EXECsp bindefault today employees hiredate 解除默認(rèn)綁定 解除綁定 sp unbindefault語法 sp unbindefault objname object name futureonly futureonly flag 例如 EXECsp unbindefault employees hiredate 刪除默認(rèn) 刪除默認(rèn) DropDefault語法 DROPDEFAULT default n 例如 DROPDEFAULTphonedflt