《SQL-電子工業(yè)-耿文蘭.ppt》由會員分享，可在線閱讀，更多相關《SQL-電子工業(yè)-耿文蘭.ppt（564頁珍藏版）》請在裝配圖網上搜索。

SQLServer2000數據庫管理與開發(fā),電子工業(yè)出版社,課程要求,考試成績評定考勤及紀律20%課堂測驗及作業(yè)30%期末考試50%本課程學習技巧加強記憶充分思考加強上機實踐理論聯系實際,2,3,目錄,第01章SQLServer2000概述第02章數據庫管理第03章表及其應用第04章數據的查詢與更新第05章使用視圖和索引第06章Transact-SQL程序設計第07章使用規(guī)則、默認值與觸發(fā)器第08章SQLServer中的安全管理第09章備份和還原第10章不同數據格式的轉換,第01章SQLServer2000概述,1.1SQLServer2000簡介1.2SQLServer2000的安裝1.3啟動和停止SQLServer服務1.4注冊服務器和創(chuàng)建服務器組1.5SQLServer2000管理工具簡介,總結,5,回憶以前學習的知識,生活中遇到的大量數據，需要解決哪些問題？數據保存、查詢、排序、篩選、刪除……在Execl中學習的相關內容？數據清單、自動篩選和高級篩選、分類匯總、數據透視圖和透視表Execl數據庫,6,計算機化數據庫的好處,數據結構化數據能夠共享數據冗余度小，易擴充數據與程序的獨立性較高對數據實行集中統(tǒng)一控制可以建立和遵循某些標準便于維護數據完整性能夠實現數據的安全性,7,數據庫的發(fā)展史,萌芽階段——文件系統(tǒng)使用磁盤文件來存儲數據初級階段——第一代數據庫出現了網狀模型、層次模型的數據庫中級階段——第二代數據庫關系型數據庫和結構化查詢語言高級階段——新一代數據庫“關系-對象”型數據庫,8,SQLServer2000,MSSQLServer2000是一個大型的關系型數據庫系統(tǒng)是一個多層的客戶/服務器數據庫系統(tǒng)采用請求-應答工作方式,9,SQLServer2000的版本,企業(yè)版最大支持32個CPU、64G內存的多處理器系統(tǒng)，適合大規(guī)模商業(yè)應用標準版最大支持4個CPU、2G內存的多處理器系統(tǒng)，適合中小規(guī)模商業(yè)應用個人版支持兩個CPU的處理器系統(tǒng)，適合個人或小型工作組的實驗應用開發(fā)版適合于開發(fā)人員應用評估版適合于開發(fā)人員體驗SQLServer2000的新功能,安裝SQLServer2000企業(yè)版,11,各版本關系,,,無縫遷移，100%的兼容性,12,SQLServer2000的新特性,與Internet集成可伸縮性和易用性企業(yè)級數據庫功能易于安裝、部署和使用數據倉庫,13,安裝SQLServer20004-1,以MicrosoftSQLServer2000Personal版本為例1.插入光盤SQLServer2000自動安裝或雙擊光盤上的AUTORUN.EXE文件2.點擊【安裝SQLServer2000組件（C）】3.點擊【安裝數據庫服務器（S）】,,14,安裝SQLServer20004-2,4.選中【本地計算機】選項。5.選擇【創(chuàng)建新的SQLSERVER實例】項,,15,安裝SQLServer20004-3,6.在姓名后輸入：“王斌”，在公司名稱后輸入：“**計算機工程有限公司”7.接受許可協議8.選中【服務器和客戶端工具[S]】,16,安裝SQLServer20004-4,9.選中【典型】安裝方式10.選擇【使用本地系統(tǒng)帳戶】11.選擇【混合模式（Windows身份驗證和SQL身份驗證）】，輸入用戶名“sa”和密碼“sa”12.至此安裝SQLSERVER2000全部結束,17,停止和啟動SQLServer服務,在對SQLServer數據庫進行任何操作之前，必須啟動本地或遠程SQLServer服務器使用服務管理器使用命令服務管理運行：NetStartmssqlserver暫停：Netpausemssqlserver繼續(xù)：Netcontinuemssqlserver停止：Netstopmssqlserver,18,注冊一個SQLServer4-1,,在SQLServer企業(yè)管理器的菜單中選擇操作，從菜單中選擇【新建SQLServer注冊】，進入到【歡迎使用注冊SQLServer向導】對話框,19,注冊一個SQLServer4-2,選擇復選框中你要使用的一個或多個服務器。選擇使用的身份認證方式—SQLServer身份認證,,20,注冊一個SQLServer4-3,,在SQLServer身份認證對話框中輸入登錄名和密碼選擇服務器組或創(chuàng)建服務器組,21,注冊一個SQLServer4-4,整個注冊過程完成,22,管理工具簡介,服務管理器服務啟動、暫停和停止管理企業(yè)管理器可以完成幾乎所有管理工作查詢分析器允許輸入、執(zhí)行SQL語句并返回結果客戶端網絡實用工具客戶端數據庫連接工具事件探查器數據庫事件管理工具數據導入/導出工具用于數據的傳遞與轉換,結構化查詢語言SQL——StructuredQueryLanguageSQL是RDBMS的標準語言，遵循ANSI(美國國家標準協會)所制定的標準。SQL分類數據查詢語言（DQL）：Select*FROMABC數據操作語言（DML）：UpdateABCSETA=“CH”數據控制語言（DCL）：RevokeCreateTableFROMU1數據定義語言（DDL）：CreateTableABC通用命令語言（CCL）：DeclareCSCursorFor…,SQL的分類,24,第01章總結,掌握概念關系數據庫SQLServer2000的版本兩種身份驗證模式管理工具的使用服務管理器企業(yè)管理器查詢分析器,25,早期數據保存方式—計算機文件,可以以不同的文件格式進行數據保存今天人們依然采用文件方式保存一些數據,26,數據庫模型,模型：事物抽象特征或者過程的描述數據模型描述用來存儲數據的容器在該容器中存儲和檢索數據的過程數據庫模型分類層次模型網狀模型關系模型,27,75,3,86,2,98,1,成績,學號,,,,,,,,,劉三陽,3,李東,2,張明,1,姓名,學號,,,,,,,,,,關系模型,記錄和記錄之間通過屬性之間的關系來進行連接保證數據獨立性，并形成數據集之間的關系,28,基于關系模型的數據庫,目前較流行的關系型數據庫桌面數據庫MSAccess大型網絡數據庫MSSQLServer、Oracle和Sybase、DB2等,29,請求-應答工作方式,,,數據庫服務器,響應和提供數據,應用程序,數據庫,操作和查詢,30,SQLServer實例,安裝SQLServer2000數據服務器就是安裝SQLServer2000數據庫引擎實例一臺計算機上可安裝多個SQLServer數據庫引擎實例在單臺計算機上可以運行的實例數目取決于可用資源，SQLServer2000最多支持16個實例SQLServer實例類型默認實例一臺計算機上只能有一個默認實例默認實例可以是SQLServer的任何版本命名實例,31,SQLServer服務SQLServer代理服務MSDTC服務,每個SQLServer實例都有一個SQLServer服務，默認實例名為MSSQLServer,服務管理器,32,服務管理器的啟動方法,托盤圖標開始菜單程序組執(zhí)行文件Sqlmangr.exe,33,企業(yè)管理器,,在服務器節(jié)點上指示燈是綠色的，表示服務器連接成功,34,登錄查詢分析器,,,,35,使用查詢分析器,,,,,第02章數據庫管理,2.1數據庫系統(tǒng)概述2.2SQLServer數據庫概念2.3系統(tǒng)數據庫2.4創(chuàng)建用戶數據庫2.5查看數據庫信息2.6設置數據庫選項2.7修改數據庫2.8刪除數據庫2.9數據庫的分離與附加,總結,37,數據庫概述,數據庫就是按照一定的組織結構存儲在計算機存儲介質上的相關數據的集合數據庫具有以下的特點結構化數據庫文件及文件內部數據都有一定的結構獨立性數據的邏輯組織與物理存儲方式無關數據共享，減少冗余數據集中存儲，統(tǒng)一管理安全性與完整性保證數據的正確性和可靠性,38,什么是DBMS？,數據庫管理系統(tǒng)是用來幫助用戶建立、使用和管理數據庫的軟件系統(tǒng)，簡稱DBMS數據庫管理系統(tǒng)的功能建立：建立數據庫結構，并存儲用戶數據操作：插入、修改、刪除、檢索、統(tǒng)計匯總、打印等其他：數據安全性、數據完整性、并發(fā)控制（在網絡環(huán)境下多個用戶同時對同一數據庫中的數據進行存取的操作）等管理,39,數據庫系統(tǒng),數據庫系統(tǒng)狹義地講是由數據庫管理系統(tǒng)和用戶構成廣義地講是由計算機硬件、操作系統(tǒng)、數據庫管理系統(tǒng)以及在它支持下建立起來的數據庫、應用程序、用戶和維護人員組成的一個整體,40,數據庫系統(tǒng)的組成,計算機基本系統(tǒng)數據庫管理系統(tǒng)數據庫物理數據庫有關應用所需要的工作數據的集合，是數據庫的主體描述數據庫關于各級數據結構的描述數據，通常由一個數據詞典系統(tǒng)管理,41,SQLServer數據庫,SQLServer2000是一種采用SQL語言的關系數據庫管理系統(tǒng)，是以文件的形式存儲在磁盤上的(以文件組形式進行組織管理)，數據庫文件(使用便于管理的規(guī)則命名)根據作用不同分為三種,42,采用多個數據庫文件來存儲數據的優(yōu)點,數據庫文件可以不斷擴充而不受操作系統(tǒng)文件大小的限制可以將數據庫文件存儲在不同的硬盤中，這樣可以同時對幾個硬盤做數據存取提高了數據處理的效率，對于服務器型的計算機尤為有用,43,數據庫對象,數據庫中的數據按不同形式組織在一起，構成了不同的數據庫對象數據庫對象在磁盤上沒有對應的文件SQLServer2000中有以下數據庫對象：表、視圖、存儲過程、觸發(fā)器、用戶自定義數據類型、用戶自定義函數、索引、規(guī)則、默認、約束等,44,數據庫文件組,將多個數據文件存放在不同的磁盤上并把多個數據文件組成一個或多個文件組數據庫根據組內數據文件的大小，按比例地寫入組內所有數據文件，使多個磁盤同時并行工作、提高讀寫速度在一個數據庫中可以創(chuàng)建多個文件組，而一個數據文件只能屬于一個文件組事務日志文件不能屬于任何文件組一個數據文件或文件組只被一個數據庫使用,45,數據庫文件組示例,,,,,46,系統(tǒng)數據庫2-1,,,系統(tǒng)數據庫,示例數據庫,47,系統(tǒng)數據庫2-2,,臨時數據庫，臨時數據可被自動刪除，不需要特殊權限，系統(tǒng)中僅有一個，空間不足可自動擴展,系統(tǒng)數據庫,用戶數據庫,示例數據庫,主控數據庫，用于管理其他數據庫和保存SQLServer系統(tǒng)信息,模板數據庫，當創(chuàng)建一個用戶數據庫時其內容自動復制到新數據庫中,來存儲計劃信息以及與備份和恢復相關的信息，SQLServer代理使用其安排報警、作業(yè)并記錄操作員,48,系統(tǒng)表,SQLServer2000中的每個數據庫都包含系統(tǒng)表系統(tǒng)表在創(chuàng)建數據庫時自動產生系統(tǒng)表用來記錄SQLServer組件所需的數據SQLServer的操作能否成功，取決于系統(tǒng)表中信息的完整性任何用戶都不應直接修改系統(tǒng)表,49,master數據庫中的系統(tǒng)表,50,常用數據庫系統(tǒng)表,51,創(chuàng)建用戶數據庫,一個數據庫是包含表、視圖、存儲過程及觸發(fā)器等數據庫對象的容器，在數據庫中建立的各種數據庫對象都保存在數據庫文件中創(chuàng)建數據庫之前應考慮好數據庫的擁有者、數據庫的初始容量、最大容量、增長量及數據庫文件存放的路徑等因素要創(chuàng)建數據庫，用戶必須具有相應的權限可以使用企業(yè)管理器、CREATEDATABASE語句和向導三種方法,52,使用企業(yè)管理器創(chuàng)建數據庫,,數據文件的邏輯名,數據文件的物理名,文件組,日志文件不屬于任何文件組,53,使用語句創(chuàng)建數據庫,CREATEDATABASE數據庫名[ON[PRIMARY]{([NAME=數據文件的邏輯名稱,]FILENAME=‘數據文件的物理名稱’,[SIZE=數據文件的初始大小,][MAXSIZE=數據文件的最大容量,][FILEGROWTH=數據文件的增長量])}[,…n]LOGON{([NAME=事務日志文件的邏輯名稱,]FILENAME=‘事務日志文件的物理名稱’,[SIZE=事務日志文件的初始大小,][MAXSIZE=事務日志文件的最大容量,][FILEGROWTH=事務日志文件的增長量])}[,…n],例2.1,示例,54,使用向導創(chuàng)建數據庫,,完成后會詢問“是否馬上為新創(chuàng)建的數據庫創(chuàng)建維護計劃”。維護計劃就是設置何時自動定期對數據庫進行維護。,55,查看數據庫信息,數據庫信息主要包括三方面基本信息維護信息空間使用情況使用企業(yè)管理器查看數據庫信息演示使用企業(yè)管理器查看數據庫信息…使用sp_helpdb查看數據庫信息語法：[execute]sp_helpdb[數據庫名]若省略數據庫名，與“select*fromsysdatabases”語句的功能完全相同,56,使用語句查看數據庫信息,使用sp_databases查看所有可用數據庫信息語法：[EXECUTE]sp_databases使用sp_helpfile查看當前數據庫中某個文件信息語法：[EXECUTE]sp_helpfile[文件名]省略文件名則顯示當前數據庫中所有文件的信息使用sp_helpfilegroup查看當前數據庫中文件組信息語法：[EXECUTE]sp_helpfilegroup[文件組名]省略文件組名則顯示當前數據庫中所有文件組信息，用法同sp_helpfile,57,設置數據庫選項,,,,僅允許特殊用戶訪問數據庫,數據庫只能讀取，不能修改,,,當最后一個用戶退出，SQLServer將自動關閉該數據庫并釋放其所占的系統(tǒng)資源,數據庫文件中未使用空間超過文件大小的25%，將自動縮小空間，縮小后數據庫文件的大小不能小于初始大小。設置只讀時，此選項無效,對數據庫訪問權限進行設置和修改,58,使用存儲過程配置數據庫特性,[execute]sp_dboption‘數據庫名’[,‘選項名’,‘選項值],設置數據庫為只讀,EXECsp_dboptionpubs,readonly,True,EXECsp_dboptionpubs,autoshrink,true,EXECsp_dboptionstudents,singleuser,true,設置數據庫為自動壓縮,設置數據庫為單用戶(例2.3),EXECsp_dboptionstudents,查看數據庫students所有設置為true的選項(例2.4),使用企業(yè)管理器修改數據庫,操作方法和創(chuàng)建數據庫大體相同注意問題在修改文件的“分配的空間”項時，所改動的值必須大于現有的空間值若要通過刪除未用空間來縮小數據庫文件的容量，先選擇要縮小的數據庫，然后在“收縮數據庫”對話框中改變其容量值,59,60,使用語句修改數據庫,ALTERDATABASE數據庫名ADDFILE[TOFILEGROUP文件組]|ADDLOGFILE|REMOVEFILE邏輯文件名|ADDFILEGROUP文件組名|REMOVEFILEGROUP文件組名|MODIFYFILE|MODIFYFILEGROUP文件組名，文件組屬性,例2.5,示例,61,刪除數據庫,刪除一個數據庫會刪除該數據庫中的所有對象，從而釋放出該數據庫所占用的磁盤空間注意當數據庫處于正在使用、正在被恢復和正在參與復制三種狀態(tài)之一時，不能刪除該數據庫若要刪除的數據庫正在被其他用戶所使用，則可以先斷開服務器與該用戶的連接，然后刪除該數據庫系統(tǒng)數據庫中的master、model和tempdb不能被刪除，msdb雖然可以被刪除，但刪除msdb后很多服務(比如SQLServer代理服務)將無法使用，因為這些服務在運行時會用到msdb,62,使用企業(yè)管理器刪除數據庫,,63,使用語句刪除數據庫,,64,關于數據庫的命令小結,,創(chuàng)建數據庫,createdatabase,alterdatabase,dropdatabase,修改數據庫,刪除數據庫,sp_helpdb,查看數據庫定義信息,sp_dboption,查看/修改數據庫設置,65,數據庫與系統(tǒng)的分離,分離數據庫實際上是從SQLServer系統(tǒng)中刪除數據庫組成該數據庫的數據文件和事務日志文件依然完好無損的保存在磁盤上注意只有固定服務器角色成員sysadmin才可執(zhí)行分離操作系統(tǒng)數據庫master、model和tempdb數據庫無法從系統(tǒng)分離出去,若要執(zhí)行分離操作，必須先單擊“清除”按鈕清除連接,66,將數據庫附加到系統(tǒng),使用這些數據文件和事務日志文件可以將數據庫再附加到任何SQLServer系統(tǒng)中，而且數據庫在新系統(tǒng)中的使用狀態(tài)與它分離時的狀態(tài)完全相同附加數據庫只需要指定要附加到SQLServer系統(tǒng)的數據庫主數據文件的路徑與文件名,67,語句實現數據庫的分離與附加,分離sp_detach_db數據庫名稱[,TRUE|FALSE]附加sp_attach_db數據庫名稱,數據庫文件名[，…16]dbname必須是唯一的filename數據庫文件的物理名稱，包括路徑最多可以指定16個文件名文件名列表至少必須包括主文件可使用此方法對現有數據庫制作多個副本，也可以實現在不同SQLServer服務器之間移動傳遞數據的目的,68,第02章總結,掌握概念數據庫數據庫管理系統(tǒng)數據庫系統(tǒng)數據庫文件數據庫對象文件組系統(tǒng)數據庫應用技能用戶數據庫的創(chuàng)建、修改、刪除數據庫信息的查看、數據庫參數配置數據庫與系統(tǒng)的分離與附加,課后作業(yè),69,例2.1,用語句創(chuàng)建一個名為students的數據庫，包含一個主數據文件和一個事務日志文件。這兩個文件都放在‘d:\data’目錄中主數據文件邏輯文件名為‘studentdata’，物理文件名為‘student_data.mdf’，初始容量大小為10MB，最大容量為50MB，每次增長量為25%事務日志文件的邏輯文件名為‘studentlog’，物理文件名為‘student_log.ldf’，初始容量大小為10MB，最大容量為不受限制，每次增長量為2MB,CREATEDATABASEstudentsONPRIMARY(NAME=studentdata,FILENAME=d:\data\student_data.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=25%)LOGON(NAME=studentlog,FILENAME=d:\data\student_log.ldf,SIZE=10MB,MAXSIZE=unlimited,FILEGROWTH=2MB),70,示例,用T-SQL語句在C:\DATA\與D:\DATA\文件夾中創(chuàng)建一個教師信息數據庫teacher，該數據庫包含一個主數據文件邏輯名‘teacherdata1’，物理名‘C:\DATA\tdata1.mdf’,初始容量1MB，最大容量10MB，每次增長量為15%一個輔助數據文件邏輯名‘teacherdata2’物理名‘D:\DATA\tdata2.ndf’,初始容量2MB，最大容量15MB，每次增長量為2MB兩個數據文件不單獨創(chuàng)建文件組，即使用默認的PRIMARY組一個事務日志文件邏輯名‘teacherlog’物理名‘D:\DATA\teacherlog.ldf’，初始容量512KB，最大容量不受限制，每次增長量為512KB,CREATEDATABASEteacherONPRIMARY(NAME=teacherdata1,FILENAME=C:\DATA\tdata1.mdf,SIZE=1,MAXSIZE=10,FILEGROWTH=15%),(NAME=teacherdata2,FILENAME=D:\DATA\tdata2.mdf,SIZE=2,MAXSIZE=15,FILEGROWTH=2)LOGON(NAME=teacherlog,FILENAME=D:\DATA\teacherlog.ldf,SIZE=512kb,FILEGROWTH=512kb),SQL語句在書寫時不區(qū)分大小寫，一條語句可以寫在多行上，但不能將多條語句寫在一行上。,71,文件格式,(NAME=數據文件的邏輯名稱[,FILENAME=‘數據文件的物理名稱’][,SIZE=數據文件的初始大小][,MAXSIZE=數據文件的最大容量|unlimited][,FILEGROWTH=數據文件的增長量]),在修改文件的“分配的空間”項時，所改動的值必須大于現有的空間值文件的物理文件名不允許進行修改，所以請勿指定物理名稱,72,例2.5,用語句向students數據庫添加一個名為studentsfilegroup的文件組，并將student_dataadd.ndf添加到此文件組中,--添加文件組ALTERDATABASEstudentsADDFILEGROUPstudentsfilegroupgo--添加數據文件，并將其加入新文件組ALTERDATABASEstudentsADDFILE(NAME=studentdataadd,FILENAME=d:\data\student_dataadd.ndf)TOFILEGROUPstudentsfilegroupgo--查看數據庫信息executesp_helpdbstudentsgo,73,示例,修改students數據庫原有主數據文件studentsdata初始大小改為2MB按2MB自動增長最大容量20MB,ALTERDATABASEstudentsMODIFYFILE(NAME=studentsdata,SIZE=2,MAXSIZE=20,FILEGROWTH=2),74,,CREATEDATABASEtestONPRIMARY(NAME=testdat1,FILENAME=d:\data\testdat1.mdf,SIZE=1,MAXSIZE=10,FILEGROWTH=1),(NAME=testdat2,FILENAME=d:\data\testdat2.ndf,SIZE=1,MAXSIZE=10,FILEGROWTH=1)LOGON(NAME=testlog1,FILENAME=d:\data\testlog1.ldf,SIZE=512KB,MAXSIZE=5,FILEGROWTH=512KB),2.8,第03章表及其應用,3.1表概述3.2創(chuàng)建表3.3查看表信息3.4修改表3.5刪除表,總結,76,表的概念,表是相關聯的行列集合表是按照行列結構存儲數據的描述一個個體屬性的總和稱為一條記錄描述個體的一個屬性稱為一個字段，也稱數據項結構和數據記錄是表的兩大組成部分,77,表的示例,,,,行/記錄,列/屬性/字段/數據項,78,創(chuàng)建表的限制,每個數據庫里最多有20億個表每個表上最多可創(chuàng)建一個聚集索引，249個非聚集索引每個表最多可以設置1,024個字段每條記錄最多占8,060B，但不包括text字段和image字段,79,創(chuàng)建表前的考慮,數據庫中要存放哪些數據，這些數據如何劃分到表中確定每個表需要哪些列，即決定表所包含的屬性有哪些,80,數據類型,數據類型是用來表現數據的特性的，表中的每列數據必須是同一種類型的數據，列的數據類型確定了該列數據的取值范圍SQLServer的數據類型包括兩類系統(tǒng)定義的數據類型用戶定義的數據類型(第07章介紹),81,數據的完整性,,數據完整性,正確性,+,一致性,=,表中所有行唯一，即所有記錄都是可以區(qū)分的,表中指定列的數據具有正確的數據類型、格式和有效的數據范圍,有關聯的兩個或兩個以上表之間數據的一致性?？梢酝ㄟ^建立主鍵和外鍵約束來實現。,82,約束,對輸入數據取值范圍和格式的限制稱為約束約束是用來保證數據完整性的六種約束主鍵約束(PRIMARYKEY)唯一約束(UNIQUE)外鍵約束(FOREIGNKEY)檢查約束(CHECK)默認值約束(DEFAULT)空值約束(NULL),83,數據庫完整性的實現,84,創(chuàng)建表,每個表必須有一個主鍵字段同一表中不允許重名字段每個表必須描述的是一個實體的信息字段必須符合命名規(guī)則表必須是符合數據規(guī)范化要求的,85,使用企業(yè)管理器創(chuàng)建表,,,,,,,,86,customers表,87,goods表,88,orders表,89,設置約束,主鍵約束唯一約束外鍵約束檢查約束,90,使用語句創(chuàng)建表,CREATETABLE表名({列名列屬性列約束}[,…n]|字段名AS計算列),CREATETABLECategories(CategoryIdintIDENTITY(1,1)primarykey,CategoryNamenvarchar(15)NOTNULL,DescriptionntextNULL,PictureimageNULL),例3.1,例3.2,例3.3,表級約束,練習,91,查看表信息,查看表定義信息即查看系統(tǒng)表sysobjects和syscolumns中記錄查看方法通過企業(yè)管理器的“表屬性”窗口[execute]sp_help[表名]查看表約束通過企業(yè)管理器的“表屬性”窗口查看表中的數據查看表之間的依賴關系使用‘企業(yè)管理器’：‘所有任務’→‘顯示相關性’,92,修改表,ALTERTABLE表名ADD列名數據類型[(長度)][NULL|NOTNULL]|ALTERCOLUMN列名數據類型[(長度)][NULL|NOTNULL]|DROPCOLUMN字段名[,…n]|ADDCONSTRAINT約束定義[,…n]|DROPCONSTRAINT約束名[,…n]|NOCHECKCONSTRAINT約束名|CHECKCONSTRAINT約束名,93,使用ADD子句添加列,ALTERTABLE表名ADD列名數據類型[(長度)][NULL|NOTNULL]若向已存在記錄的表中添加列，新添加的列可以設置允許為空；若不允許為空，則需指定默認值,例3.4,94,使用ADDCONSTRAINT子句添加約束,ALTERTABLE表名ADDCONSTRAINT約束定義[,……],例3.5,級聯引用完整性,95,使用DROPCONSTRAINT子句刪除約束,ALTERTABLE表名DROPCONSTRAINT約束名[,……],例3.6,96,使用DROPCOLUMN子句刪除列,ALTERTABLE表名DROPCOLUMN字段名[,……]在刪除列時，必須先刪除基于該列的索引和約束后，才能刪除該列,例3.7,97,使用ALTERCOLUMN子句修改列屬性,ALTERTABLE表名ALTERCOLUMN列名數據類型[(長度)][NULL|NOTNULL]在默認狀態(tài)下，列可以被設置為空值，將一個原來允許為空值的列改為不允許為空值時，必須滿足列中沒有存放空值的記錄以及在列上沒有創(chuàng)建索引,例3.8,98,使用NOCHECKCONSTRAINT子句使約束無效,ALTERTABLE表名NOCHECKCONSTRAINT約束名|CHECKCONSTRAINT約束名該子句只對外鍵約束和檢查約束起作用,例3.9,99,刪除表,DROPTABLE表名[,…n]droptable語句不能刪除系統(tǒng)表如果一個表被其他表通過外鍵約束引用，那么必須先刪除設置了外鍵約束的表，或刪除其外鍵約束,100,第03章總結,掌握概念表數據類型數據完整性約束應用企業(yè)管理器操作創(chuàng)建表、修改表結構、刪除表查看表數據查看表約束命令CreatetableAltertableDroptable,課后作業(yè),上機作業(yè),101,系統(tǒng)定義的數據類型,,統(tǒng)一字符型,精確小數,近似數值,char(2)nchar(2),decimal(5,2),0/1以外的任何值都會作為1來處理,字符型需用引號引起來,需用引號引起來,存儲圖像等數據,帶有4位小數的decimal類型的數據,102,主鍵約束,每個表僅能定義一個主鍵主鍵值是表中記錄的標識主鍵列可以有一個或多個列組合而成主鍵值不可為空(null)主鍵值不能重復image和text類型的列不能做主鍵,103,唯一約束,是一列或幾列的組合用于保證非主鍵的不可重復字段可以為表定義多個唯一約束只能為表定義一個主鍵約束該列允許為空值唯一約束可以定義在允許空值的列上主鍵約束只能定義在不允許為空值的列上唯一約束不改變字段的物理存儲位置記錄按主鍵值的指定順序存儲,104,外鍵約束,設置外鍵約束字段的表稱為子表，它所引用的表稱為父表外鍵可以是單一字段，也可以是多個字段的組合外鍵所引用父表中的字段必須是創(chuàng)建了主鍵約束或唯一約束的列子表中外鍵字段添加的新數據，必須在父表存在子表中引用父表數據的記錄未刪除，父表中被引用數據不能被刪除,105,外鍵約束示例,,,,,子表,記錄,如果數據項不在父表中,添加,父表,,,如果導致子表產生孤立記錄,刪除,,106,檢查約束,用指定的條件檢查限制輸入數據的取值范圍是否正確用于保證數據的參照完整性和域完整性如：成績必須>0,107,默認值約束,默認值約束是給某個字段綁定一個默認的初始值輸入記錄時若沒有給出該字段的數據，則自動填入默認值以保證數據的域完整性對設置了默認值約束的字段若輸入數據時則以輸入的數據為準可為常量、內置函數或表達式,108,空值約束,空值NULL是不知道或不能確定的特殊數據不等同于數值0和字符的空格空值約束就是設置某個字段是否允許為空用以保證數據的實體完整性和域完整性,109,數據規(guī)范化,從關系型數據庫的表中除去冗余數據的過程規(guī)范化是得到高效的關系型數據庫表的邏輯結構最好和最容易的方法規(guī)范化數據時，應執(zhí)行以下操作將數據庫的結構精簡為最簡單的形式從表中刪除冗余列標識所有依賴于其他數據的數據,110,范式,構造數據庫必須遵循一定的規(guī)則，在關系數據庫中，這種規(guī)則就是范式公認的規(guī)范化等級有五種1NF2NF3NF等只要符合第三范式的數據就可以稱為完全規(guī)范化的數據,111,1NF,所有屬性必須只有一個值修正方法示例：運動隊（標識、隊名、隊員）,112,2NF,任何非主屬性都必須依賴于完整的主屬性特別適用于復合主屬性的情況修正方法示例：產品供應情況（供應商編號、產品編號、購買價格、供應商名稱）,113,3NF,非主屬性不能依賴于另一非主屬性修正方法示例：城市（城市標識、城市名稱、所在省份、省長姓名）,114,列屬性,數據類型[(長度)][NULL|NOTNULL][IDENTITY(初始值,步長值)],115,列約束,[CONSTRAINT約束名]PRIMARYKEY[(列名)][CONSTRAINT約束名]UNIQUE[(列名)][CONSTRAINT約束名][FOREIGNKEY[(外鍵列)]]REFERENCES引用表名(引用列)[ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}][CONSTRAINT約束名]CHECK(檢查表達式)[CONSTRAINT約束名]DEFAULT默認值,116,列約束說明,約束名為以后修改管理時使用，省略為系統(tǒng)默認的約束名字段約束也可以在創(chuàng)建表結構以后另外單獨設置定義字段時同時定義所綁定的約束時，可以省略列名,117,計算列示例,createtableorders1(訂單號intidentity(1,1)primarykey,貨品名稱varchar(20)notnullreferencesgoods(貨品名稱),客戶編號intforeignkeyreferencescustomers(客戶編號),數量intnotnullcheck(數量>0),價格money,總金額as數量*價格,訂貨日期datetimedefaultgetdate()),118,例3.1,用語句創(chuàng)建customers表，記錄客戶的基本信息表中包含如下內容：客戶編號、姓名、地址、電話和出生日期設置客戶編號字段為主鍵約束，電話字段為唯一約束，姓名字段不能為空,CREATETABLEcustomers(客戶編號intPRIMARYKEY,姓名varchar(20)NOTNULL,地址varchar(50),電話varchar(20)UNIQUE,出生日期datetime),119,例3.2,用語句創(chuàng)建goods表，記錄貨品的基本信息表中包含如下內容：貨品名稱、庫存量、供應商、狀態(tài)和價格設置貨品名稱字段為主鍵約束設置狀態(tài)字段為bit類型,CREATETABLEgoods(貨品名稱varchar(20)PRIMARYKEY,庫存量int,供應商varchar(50),狀態(tài)bitDEFAULT0,價格money),120,例3.3,用語句創(chuàng)建orders表，記錄客戶訂購貨品的訂單信息表中包含內容：訂單號、貨品名稱、客戶編號、數量、總金額和訂貨日期設置訂單號字段為自動編號，初始值為1，遞增量為1，同時設置為主鍵客戶編號字段為外鍵約束數量字段檢查約束為數量大于0，同時不能為空訂貨日期默認值，為系統(tǒng)日期,CREATETABLEorders(訂單號intIDENTITY(1,1)PRIMARYKEY,貨品名稱varchar(20)NOTNULLREFERENCESgoods(貨品名稱),客戶編號intFOREIGNKEYREFERENCEScustomers(客戶編號),數量intNOTNULLCHECK(數量>0),總金額money,訂貨日期datetimeDEFAULTgetdate()),121,約束的兩種定義方式,列級約束列約束內嵌在列的聲明中定義，作為列定義的一部分只作用于此列本身放在列名和數據類型之后的位置，即逗號之前表級約束表級約束作為表定義的一部分，可以作用于多個列，且必須將多列約束定義為單獨的表元素作為獨立的由逗號分隔的語句數據庫服務器以同樣的方式處理列級和表級約束，涉及到多列的約束必須作為表級約束處理,122,列級約束和表級約束,CREATETABLEorders(訂單號intIDENTITY(1,1),貨品名稱varchar(20)NOTNULLREFERENCESgoods(貨品名稱),客戶編號intFOREIGNKEYREFERENCEScustomers(客戶編號),數量intNOTNULLCHECK(數量>0),總金額money,訂貨日期datetimeDEFAULTgetdate(),CONSTRAINTpk_ordersPRIMARYKEY(訂單號)),123,圖書信息管理數據庫,,124,創(chuàng)建借閱信息表,Createtable借閱信息表(圖書編號char(10)notnullforeignkeyreferences圖書信息表(圖書編號),讀者編號char(8)notnullreferences讀者信息表(讀者編號,借閱時間datetimeconstraintdf_datedefaultgetdate(),歸還時間datetimenull,是否超期bitdefault0,超期罰款moneynull,constraintpk_jieyueprimarykey(圖書編號,讀者編號)),列級約束,表級約束,125,設置主鍵約束,,,,126,設置唯一約束,,127,設置外鍵約束,,,級聯引用完整性,128,設置檢查約束,,129,例3.4,向customers表中添加“電子郵件”列，其數據類型為varchar，長度為50，不允許空,USEmarketALTERTABLEcustomersADD電子郵件varchar(50)NOTNULLDEFAULT‘’,130,例3.5,將customers表中“電子郵件”列的默認值設置為cqdx@,USEmarketALTERTABLEcustomersADDCONSTRAINTdef_eDEFAULT‘cqdx@’FOR電子郵件,,131,DEFAULT約束,132,CHECK約束,133,PRIMARYKEY約束,134,UNIQUE約束,135,FOREIGNKEY約束,136,級聯引用完整性,可以在定義FOREIGNKEY約束時的REFERENCES子句中使用ONDELETE和ONUPDATE子句，這些子句用于指定CASCADE或NOACTION選項CASCADE選項允許對已定義UNIQUE或PRIMARYKEY約束的列值進行任何更改，列值會自動把這些更改傳送給外鍵值NOACTION（默認選項）選項則限制任何試圖刪除或更新由其他表的外鍵引用的關鍵字的操作,137,級聯引用完整性示例,重新創(chuàng)建orders表當customers表的客戶編號改變時，自動修改orders表中記錄的客戶編號，但不允許刪除客戶當goods表中的貨品刪除時，自動刪除orders表中對應貨品的訂貨記錄，但不允許修改貨品名稱,CREATETABLEorders(訂單號intIDENTITY(1,1)PRIMARYKEY,貨品名稱varchar(20)NOTNULLREFERENCESgoods(貨品名稱)ONDELETECASCADE,客戶編號intFOREIGNKEYREFERENCEScustomers(客戶編號)ONUPDATECASCADE,數量intNOTNULLCHECK(數量>0),總金額money,訂貨日期datetimeDEFAULTgetdate()),138,例3.6,刪除customers表中“電子郵件”列上創(chuàng)建的默認值約束def_e,USEmarketALTERTABLEcustomersDROPCONSTRAINTdef_e,139,例3.7,刪除customers表中的“電子郵件”字段,USEmarketALTERTABLEcustomersDROPCOLUMN電子郵件,140,例3.8,將customers表的“電子郵件”列改為最大長度為50的char型數據，且允許為空值,USEmarketALTERTABLEcustomersALTERCOLUMN電子郵件char(50)NULL,141,例3.9,使customers表的“電子郵件”列創(chuàng)建的約束無效或重新生效,USEmarketALTERTABLEcustomersADDCONSTRAINTchk_eCHECK(電子郵件LIKE‘%@%’)ALTERTABLEcustomersNOCHECKCONSTRAINTchk_eALTERTABLEcustomersCHECKCONSTRAINTchk_e,142,3.93.10-0,創(chuàng)建“學生檔案”數據庫,CREATEDATABASE學生檔案ONPRIMARY(NAME=studentdata,FILENAME=d:\data\studentdata.mdf,SIZE=1,MAXSIZE=5,FILEGROWTH=10%)LOGON(NAME=studentlog,FILENAME=d:\data\studentlog.ldf,SIZE=1,MAXSIZE=5,FILEGROWTH=10%),143,3.9,CREATETABLE人事表(學號INTIDENTITY(2001,1),姓名CHAR(8),性別CHAR(2),電話號碼VARCHAR(20),家庭住址VARCHAR(50))CREATETABLE成績表(學號INT,選修課程VARCHAR(20),成績DECIMAL(4,1)),144,3.10,ALTERTABLE成績表ADDCONSTRAINTfk_xuehaoFOREIGNKEY(學號)REFERENCES人事表(學號),CONSTRAINTck_checkCHECK(成績>=0AND成績=0and成績>==!=!>!，>=，，!=，!>，!=100,207,例4.8,查詢goods表中庫存量不在100~500之間的貨品記錄,USEmarketSELECT*FROMgoodsWHERE庫存量NOTBETWEEN100AND500,208,例4.9,查詢customers表中姓名是‘王蘭’、‘李紅’的客戶信息,USEmarketSELECT*FROMcustomersWHERE姓名IN(‘王蘭’,‘李紅’),209,例4.10,查詢customers表中姓李的客戶信息,USEmarketSELECT*FROMcustomersWHERE姓名LIKE李%,210,例4.11,查詢goods表中不知道供應商名稱的貨品信息，即供應商列為空值,USEmarketSELECT*FROMgoodsWHERE供應商ISNULL,211,例4.12,用兩種方法查詢goods表中庫存量在100~1000之間的貨品記錄,USEmarketSELECT*FROMgoodsWHERE庫存量BETWEEN100AND1000SELECT*FROMgoodsWHERE庫存量>=100AND庫存量=30,,218,例4.17,統(tǒng)計orders表各種貨品訂貨總數量，并給出明細表,USEmarketSELECT*FROMordersORDERBY貨品名稱COMPUTESUM(數量)BY貨品名稱,219,例4.18,將customers表和orders表進行交叉連接，觀察結果,USEmarketSELECT*FROMcustomersCROSSJOINordersSELECT*FROMcustomers,orders,220,交叉連接示例,,,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyers,sales,221,例4.19,用內連接方法連接customers和orders兩個表，觀察連接后的結果,SELECTc.客戶編號,c.姓名,o.數量,o.總金額FROMcustomersAScINNERJOINordersASoONc.客戶編號=o.客戶編號SELECTc.客戶編號,c.姓名,o.數量,o.總金額FROMcustomersASc,ordersASoWHEREc.客戶編號=o.客戶編號,222,內連接示例,,,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_id,223,例4.20,使用左外連接查詢goods表和orders表，獲取存在訂貨的貨品名稱、訂貨日期和訂貨數量,USEmarketSELECTgoods.貨品名稱,訂貨日期,數量FROMgoodsLEFTJOINordersONgoods.貨品名稱=orders.貨品名稱,224,左外連接示例,,,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_id,225,例4.21,使用右外連接查詢goods表和orders表，獲取存在訂貨的貨品名稱、訂貨日期和訂貨數量,USEmarketSELECTgoods.貨品名稱,訂貨日期,數量FROMordersRIGHTJOINgoodsONgoods.貨品名稱=orders.貨品名稱,226,例4.22,使用全外連接查詢goods表和orders表，獲取存在訂貨的貨品名稱、訂貨日期和訂貨數量,USEmarketSELECTgoods.貨品名稱,訂貨日期,數量FROMgoodsFULLOUTERJOINordersONgoods.貨品名稱=orders.貨品名稱,227,例4.24,查詢orders表中訂購了兩種或兩種以上貨品的客戶編號和他所訂購的貨品名稱,USEmarketSELECTa.客戶編號,a.貨品名稱FROMordersASaJOINordersbONa.客戶編號=b.客戶編號ANDa.訂單號b.訂單號,228,自連接示例,,USEjoindbSELECTa.buyer_idASbuyer1,a.prod_id,b.buyer_idASbuyer2FROMsalesASaJOINsalesASbONa.prod_id=b.prod_idWHEREa.buyer_id>b.buyer_id,,229,例4.25,從表customers中檢索出地址為上海的客戶輸出到表shangh，地址為北京的客戶輸出到beij，然后將兩表合并為一個結果集,SELECT*INTOshanghFROMcustomersWHERE地址LIKE‘上海%SELECT*INTObeijFROMcustomersWHERE地址LIKE北京%SELECT*FROMshanghUNIONSELECT*FROMbeij,230,嵌套子查詢和相關子查詢,嵌套子查詢總是最先執(zhí)行最內層查詢相關子查詢的執(zhí)行過程依賴于值的外部查詢。相關子查詢的WHERE子句引用外部查詢的FROM子句中的表。這就是說子查詢是以循環(huán)的方式進行處理的——對外部查詢選擇的每行都執(zhí)行一次,231,例4.26,從goods表中檢索數據，列出高于平均價格的貨品信息,SELECT*FROMgoodsWHERE價格>(SELECTAVG(價格)FROMgoods),232,例4.27,從orders表中檢索數據，以查詢有訂單的客戶信息,SELECT*FROMcustomersWHERE客戶編號IN(SELECT客戶編號FROMorders),233,例4.28,從orders表中檢索數據，以查詢有訂單的客戶信息,SELECT*FROMcustomersWHEREEXISTS(SELECT*FROMordersWHEREcustomers.客戶編號=orders.客戶編號),234,例4.29,用IN子句完成上例的操作,SELECT*FROMcustomersWHERE客戶編號IN(SELECT客戶編號FROMorders),235,例4.30,從customers表中檢索數據，以查詢一次訂貨數量大于等于20的客戶編號和姓名,SELECTa.客戶編號,a.姓名FROMcustomersaWHERE20=ALL(SELECT價格FROMgoods),237,例4.32,向orders表添加一條新記錄，給所有列提供列值,INSERTordersVALUES(book,1,10,250,1/23/2003)INSERTordersVALUES(book,1,10,250,DEFAULT)由于‘訂貨日期’設定了默認值，所有可以使用默認值,字符型和日期型數據需要用單引號括起來,238,例4.33,創(chuàng)建一個與customers表結構一樣的表cq，使該表中僅有重慶客戶的信息,SELECT*INTOcqFROMcustomersWHERE2=4INSERTcqSELECT*FROMcustomersWHERE地址LIKE重慶%,239,例4.34,將goods表中的庫存量大于100的貨品的定價降低10%,UPDATEgoodsSET價格=價格*0.9WHERE庫存量>=100,240,例4.35,把orders表中的“總金額”用該貨品在goods表中的“價格”與在orders表中的訂貨“數量”的乘積代替，并顯示修改后的記錄,UPDATEordersSET總金額=價格*數量FROMgoodsWHEREorders.貨品名稱=goods.貨品名稱SELECTa.貨品名稱,a.價格,b.數量,b.總金額FROMgoodsASa,ordersASbWHEREa.貨品名稱=b.貨品名稱,241,例4.36,刪除goods表中的所有記錄,DELETEgoods,242,例4.37,刪除goods表中的未知定價的記錄,DELETEgoodsWHERE價格ISNULL,243,例4.38,刪除orders表中地址為重慶的客戶訂貨記錄,DELETEordersFROMcustomersWHEREorders.客戶編號=customers.客戶編號AND地址LIKE重慶%,244,例4.39,刪除orders表中的所有訂單,TRUNCATETABLEorders,245,刪除表和刪除表數據的區(qū)別,DROPTABLE、DELETE和TRUNCATETABLE語句的區(qū)別DROPTABLE語句不僅刪除表數據，還將刪除表結構DELETE和TRUNCATETABLE語句只刪除表數據不含WHERE子句的DELETE語句的作用與TRUNCATETABLE語句一樣，將刪除表中所有數據，但TRUNCATETABLE語句的執(zhí)行速度更快DELETE語句可通過WHERE子句實現部分表數據刪除，而TRUNCATETABLE語句不能包含WHERE子句，即不能刪除部分數據,246,學生信息管理數據庫,,247,查詢條件2-1,檢索“基本信息表”中