SQLServer2000

數據庫入門12/22/20221數據庫入門十萬個為什么SQLServer2000

數據庫入門12/16/2SQLServer2000MS數據庫管理系統組成： 程序：用戶界面，工具數據結構：管理和存儲數據系統databaseMasterPubstempDB12/22/20222數據庫入門十萬個為什么SQLServer2000MS數據庫管理系統12/16SQLServer數據庫管理、存儲和訪問數據Database對應文件：.mdf,.ldf包含：Table,View,Index,StoreProcedure,Trigger,etc.12/22/20223數據庫入門十萬個為什么SQLServer數據庫管理、存儲和訪問數據12/16/2SQL2000管理工具12/22/20224數據庫入門十萬個為什么SQL2000管理工具12/16/20224數據庫入門十萬創建database右鍵-〉新建數據庫輸入數據庫名:test包含：數據文件:test_Data事務日志文件:test_Log及對應物理文件：.mdf,.ldf12/22/20225數據庫入門十萬個為什么創建database右鍵-〉新建數據庫12/16/20225表TableTable:表是包含數據庫中所有數據的數據庫對象。表結構：Column列（字段）的集合每行是一條記錄。表的定義：列名,數據類型,初始值，是否允許空約束規則，索引，主鍵，外鍵12/22/20226數據庫入門十萬個為什么表TableTable:12/16/20226數據庫入門建新表右鍵->新建表列名，數據類型，長度，允許空類型：整數：bigInt:64bitsInt:32bits，dec,Numeric，MoneyDatetimeChar,VarChar,NvarcharText,Image默認值自動增長(+1)RowGUID12/22/20227數據庫入門十萬個為什么建新表右鍵->新建表12/16/20227數據庫入門十萬個主鍵、外鍵、主鍵(PK):唯一標示數據，數據完整性，主鍵索引，查找快速關系:引用外鍵（FK）：表與表之間的鏈接，數據完整性、一致性索引：Index，查找快速

例如：部門，職員12/22/20228數據庫入門十萬個為什么主鍵、外鍵、主鍵(PK):唯一標示數據，數據完整性，主鍵視圖View虛擬表，由查詢定義，包含行、列，View數據并不真正存在在View中，通過查詢其它數據源（數據表、視圖、其他數據庫、其他服務器）而來。12/22/20229數據庫入門十萬個為什么視圖View虛擬表，由查詢定義，包含行、列，View數存儲過程StoredProcedureStoredProcedure運行在SQLServer服務器方類似子程序模塊可以輸入、輸出參數包含數據庫操作語句（orcallSP)Returnvalue編程語言：Transact-SQL優點：模塊化設計、創建一次，重復使用，后臺運行，速度更快網絡流量減少，安全/*----StoredProcedure:sp_backupmessage----Getbackupfilemessage----byYoujianshe2003-2-14----*/CREATEPROCsp_backupmessage@as_filenameVARCHAR(200)asRestoreHEADERONLYFROMDISK=@as_filenameGO12/22/202210數據庫入門十萬個為什么存儲過程StoredProcedureStoredPr觸發器Trigger一種特殊SP,在指定表的數據發生變化時自動生效，即被調用、被執行。(Insert,Update,Delete時）目的：強制業務規則和數據完整性Trigger:Transact-SQL語法，（簡單或復雜）邏輯處理Tables:Inserted,Deleted12/22/202211數據庫入門十萬個為什么觸發器Trigger一種特殊SP,在指定表的數據發生變化自定義類型、函數自定義類型一般不需要自定義函數12/22/202212數據庫入門十萬個為什么自定義類型、函數自定義類型12/16/202212數據庫入門數據庫設計需求分析邏輯模型E-R圖---實體關系數據流圖---業務處理物理模型數據庫分析、優化12/22/202213數據庫入門十萬個為什么數據庫設計需求分析12/16/202213數據庫入門十萬個為數據庫設計Steps⒈需求分析階段需求收集和分析，結果得到數據字典描述的數據需求（和數據流圖描述的處理需求）。⒉概念結構設計階段通過對用戶需求進行綜合、歸納與抽象，形成一個獨立于具體DBMS的概念模型，可以用E-R圖表示。⒊邏輯結構設計階段將概念結構轉換為某個DBMS所支持的數據模型（例如關系模型），并對其進行優化。⒋數據庫物理設計階段為邏輯數據模型選取一個最適合應用環境的物理結構（包括存儲結構和存取方法）。⒌數據庫實施階段運用DBMS提供的數據語言（例如SQL）及其宿主語言（例如C），根據邏輯設計和物理設計的結果建立數據庫，編制與調試應用程序，組織數據入庫，并進行試運行。⒍數據庫運行和維護階段數據庫應用系統經過試運行后即可投入正式運行。在數據庫系統運行過程中必須不斷地對其進行評價、調整與修改。12/22/202214數據庫入門十萬個為什么數據庫設計Steps⒈需求分析階段12/16/202214數數據庫設計實例項目工作管理，包含數據表：

職員花名冊，公司部門系統功能，權力角色，職員角色項目（Project)、項目成員（項目-職員）項目任務分解（WBS，分級，包括任務包、工作量估計，計劃，進度，人員安排，權重，任務包父親)項目任務執行跟蹤（工作日志）: 項目-職員–時間–對應任務包–實際工作量

12/22/202215數據庫入門十萬個為什么數據庫設計實例項目工作管理，包含數據表：12/16/202E-R圖實例12/22/202216數據庫入門十萬個為什么E-R圖實例12/16/202216數據庫入門十萬個為什么編程接口API ADO,OLEDB,ODBC(JDBC),EmbeddedSQL,URL,XML(Internet)使用數據庫打開到數據庫的連接。設置控制某些行為的選項執行Transact-SQL語句將返回代碼的數據值、輸出參數和結果集移入程序變量中，并通過程序變量由應用程序邏輯使用。在完成數據庫中的工作后斷開連接。12/22/202217數據庫入門十萬個為什么編程接口API ADO,OLEDB,ODBC(連接數據庫-PBTransactionSQLCA//事務對象sqlca.DBMS=ProfileString(gs_inifile,"DATABASE","dbms","MSS")sqlca.DATABASE=ProfileString(gs_inifile,"DATABASE","DATABASE","KF")sqlca.userid=ProfileString(gs_inifile,"DATABASE","userid","dbo")sqlca.dbpass=ProfileString(gs_inifile,"DATABASE","dbpass","")sqlca.logid=ProfileString(gs_inifile,"DATABASE","logid","sa")sqlca.logpass=ProfileString(gs_inifile,"DATABASE","LogPassWord","")sqlca.servername=ProfileString(gs_inifile,"DATABASE","ServerName","(local)")sqlca.dbparm=ProfileString(gs_inifile,"DATABASE","Dbparm","")sqlca.AutoCommit=FALSEconnectUsingSQLCA;//打開連接DisconnectUsingSQLCA;//切斷連接//ProfilesssSQLCA.DBMS="ODBC"SQLCA.AutoCommit=FalseSQLCA.DBParm="ConnectString='DSN=ExcelFiles;UID=;PWD='"連接Access//ProfileAccessSQLCA.DBMS="ODBC"SQLCA.AutoCommit=FalseSQLCA.DBParm="ConnectString='DSN=MSAccessDatabase;UID=;PWD='“//VB-1cn.Open"Driver={SQLServer};Server=Server1;Uid=SA;Pwd=;Database=northwind"http://VB–2cn.Provider="sqloledb“ProvStr="Server=MyServer;Database=northwind;Trusted_Connection=yes"cn.OpenprovStr12/22/202218數據庫入門十萬個為什么連接數據庫-PBTransactionSQLCA/獲取數據-SQL語句Stringls_empl_name//變量定義Selectempl_nameINTO:ls_empl_nameFROMbse_employeeWhereempl_no=‘system’UsingSQLCA;ifsqlca.sqlcode<>0then//…失敗Else//….成功EndifLongld_id=0Stringas_table=“tableXXX”//updateUPDATEsys_system_idSETid_counter=:ld_id WHEREid_table_name=:as_tableUsingSQLCA;12/22/202219數據庫入門十萬個為什么獲取數據-SQL語句Stringls_empl_name獲取數據-Datawindow//Dw_listDatawindow//d_employee_editDatawindowObjectdw_list.SetTransobject(SQLCA)dw_list.Retrieve(g_userinfo.is_dep_no,g_userinfo.is_empl_cls)….//保存數據ll_ret=dw_list.update(TRUE,TRUE) //判斷Ifll_ret=1thenCommit;//usingSQLCAElse Rollback;//usingSQLCA MessageBox("提示","保存時出錯!") Endif12/22/202220數據庫入門十萬個為什么獲取數據-Datawindow//Dw_listD與數據庫連接Datawindow12/22/202221數據庫入門與數據庫連接Datawindow12/16/202221數據Transact-SQL語言簡介標示符：createTableemployee(empl_nochar(10)PRIMARYKEY,empl_namevarchar(20),ageintdefault0,….）數據類型：函數：表達式：運算符：注釋關鍵字：12/22/202222數據庫入門十萬個為什么Transact-SQL語言簡介標示符：12/16/202創建與刪除表CreateTableCREATETABLE[dbo].[bse_department]( [dep_no][varchar](10)notNULL, [dep_tycode][char](1)NULL, [dep_name][char](10)NULL, [dep_no1][char](4)NULL, [dep_name1][char](10)NULL, [dep_bld_date][datetime]NULL, [dep_vendor_shared][smallint]NULL, [dep_telno][char](20)NULL)ON[PRIMARY]GOdroptabledroptable[dbo].[bse_department]GO12/22/202223數據庫入門十萬個為什么創建與刪除表CreateTable12/16/20222更改數據Insert

INSERTINTObse_department(dep_no,dep_name)VALUES(‘KFB’,‘開發部’)GOUpdate:UPDATEbse_departmentSETdep_name=‘development’WHEREdep_no=‘KFB’GODeleteDeletefrombse_departmentWHEREdep_no=‘KFB’12/22/202224數據庫入門十萬個為什么更改數據Insert12/16/202224數據庫入門十萬簡單查詢Select<列名,…>from<表…>Where<條件>…SELECT*FROMbse_departmentWHERE(dep_tycode='1')查詢統計：SELECTdep_noAS部門編號,COUNT(*)AS部門人數FROMbse_employeeGROUPBYdep_no12/22/202225數據庫入門十萬個為什么簡單查詢Select<列名,…>from<表…聯合查詢聯合查詢JOIN(INNER,LEFT,RIGHT)SELECTbse_department.dep_name,COUNT(*)AS部門人數FROMbse_employeeINNERJOINbse_departmentONbse_employee.dep_no=bse_department.dep_noGROUPBYbse_department.dep_name,bse_employee.dep_no12/22/202226數據庫入門十萬個為什么聯合查詢聯合查詢JOIN(INNER,LEFT,R聚合函數SUM(…)AVG(…)COUNT(…)MAX(…)MIN(…)SelectCOUNT(*)fromtable1Selectsum(column1)fromtable212/22/202227數據庫入門十萬個為什么聚合函數SUM(…)12/16/202227數據庫入門十結果集組合UNIONSelect*fromtableAUNIONSelect*fromtableBUNIONSelect*fromtableC

Note:結果集結構必須相同。12/22/202228數據庫入門十萬個為什么結果集組合UNION12/16/202228數據庫入門十萬個子查詢WHEREexp[NOT]IN(subquery)WHEREexpcomparison_op(subquery)WHERENOTEXISTS(subquery)例：SELECT*FROMbse_employeeWHERE(dep_noNOTIN(SELECTdep_noFROMbse_departmentWHEREdep_no<>'0000'))12/22/202229數據庫入門十萬個為什么子查詢WHEREexp[NOT]IN(subque多維匯總數據CUBESELECTbse_department.dep_name,bse_employee.empl_cls,COUNT(*)AS部門人數FROMbse_employeeINNERJOINbse_departmentONbse_employee.dep_no=bse_department.dep_noGROUPBYbse_department.dep_name,bse_employee.empl_clsWITHCUBERollUpSELECTbse_department.dep_name,bse_employee.empl_cls,COUNT(*)AS部門人數FROMbse_employeeINNERJOINbse_departmentONbse_employee.dep_no=bse_department.dep_noGROUPBYbse_department.dep_name,bse_employee.empl_clsWITHrollup12/22/202230數據庫入門十萬個為什么多維匯總數據CUBE12/16/202230數據庫入門十萬個事務處理Transaction:作為單個邏輯工作單元執行的一系列操作。一個邏輯工作單元4個屬性：原子性，一致性，隔離性，持久性BEGINTransaction-啟動事務Commit-提交，結束事務RollBack-回滾，結束事務必須一次提交或全部退回要么成功，要么失敗！確保數據完整性和一致性12/22/202231數據庫入門十萬個為什么事務處理Transaction:作為單個邏輯工作單元執行的事務處理編寫原則 事務保持盡量短。在多用戶下，必須事務保持盡量短，減少資源鎖定爭奪不要在事務處理期間要求用戶輸入瀏覽數據時，盡量不要打開事務必須修改數據時，啟動事務，執行修改，然后馬上提交(commit)或失敗后回滾(rollback)事務。靈活使用游(cursor)標并發選項，如樂觀并發(OPTIMISTIC)在事務中，盡量訪問最小量數據，減少鎖定數據行數。避免并發（Lock)12/22/202232數據庫入門十萬個為什么事務處理編寫原則 事務保持盡量短。在多用戶下，必須事務保持結束語歡迎批評指正，并提出寶貴意見！謝謝!12/22/202233數據庫入門十萬個為什么結束語歡迎批評指正，12/16/202233數據庫入門十萬個SQLServer2000

數據庫入門12/22/202234數據庫入門十萬個為什么SQLServer2000

數據庫入門12/16/2SQLServer2000MS數據庫管理系統組成： 程序：用戶界面，工具數據結構：管理和存儲數據系統databaseMasterPubstempDB12/22/202235數據庫入門十萬個為什么SQLServer2000MS數據庫管理系統12/16SQLServer數據庫管理、存儲和訪問數據Database對應文件：.mdf,.ldf包含：Table,View,Index,StoreProcedure,Trigger,etc.12/22/202236數據庫入門十萬個為什么SQLServer數據庫管理、存儲和訪問數據12/16/2SQL2000管理工具12/22/202237數據庫入門十萬個為什么SQL2000管理工具12/16/20224數據庫入門十萬創建database右鍵-〉新建數據庫輸入數據庫名:test包含：數據文件:test_Data事務日志文件:test_Log及對應物理文件：.mdf,.ldf12/22/202238數據庫入門十萬個為什么創建database右鍵-〉新建數據庫12/16/20225表TableTable:表是包含數據庫中所有數據的數據庫對象。表結構：Column列（字段）的集合每行是一條記錄。表的定義：列名,數據類型,初始值，是否允許空約束規則，索引，主鍵，外鍵12/22/202239數據庫入門十萬個為什么表TableTable:12/16/20226數據庫入門建新表右鍵->新建表列名，數據類型，長度，允許空類型：整數：bigInt:64bitsInt:32bits，dec,Numeric，MoneyDatetimeChar,VarChar,NvarcharText,Image默認值自動增長(+1)RowGUID12/22/202240數據庫入門十萬個為什么建新表右鍵->新建表12/16/20227數據庫入門十萬個主鍵、外鍵、主鍵(PK):唯一標示數據，數據完整性，主鍵索引，查找快速關系:引用外鍵（FK）：表與表之間的鏈接，數據完整性、一致性索引：Index，查找快速

例如：部門，職員12/22/202241數據庫入門十萬個為什么主鍵、外鍵、主鍵(PK):唯一標示數據，數據完整性，主鍵視圖View虛擬表，由查詢定義，包含行、列，View數據并不真正存在在View中，通過查詢其它數據源（數據表、視圖、其他數據庫、其他服務器）而來。12/22/202242數據庫入門十萬個為什么視圖View虛擬表，由查詢定義，包含行、列，View數存儲過程StoredProcedureStoredProcedure運行在SQLServer服務器方類似子程序模塊可以輸入、輸出參數包含數據庫操作語句（orcallSP)Returnvalue編程語言：Transact-SQL優點：模塊化設計、創建一次，重復使用，后臺運行，速度更快網絡流量減少，安全/*----StoredProcedure:sp_backupmessage----Getbackupfilemessage----byYoujianshe2003-2-14----*/CREATEPROCsp_backupmessage@as_filenameVARCHAR(200)asRestoreHEADERONLYFROMDISK=@as_filenameGO12/22/202243數據庫入門十萬個為什么存儲過程StoredProcedureStoredPr觸發器Trigger一種特殊SP,在指定表的數據發生變化時自動生效，即被調用、被執行。(Insert,Update,Delete時）目的：強制業務規則和數據完整性Trigger:Transact-SQL語法，（簡單或復雜）邏輯處理Tables:Inserted,Deleted12/22/202244數據庫入門十萬個為什么觸發器Trigger一種特殊SP,在指定表的數據發生變化自定義類型、函數自定義類型一般不需要自定義函數12/22/202245數據庫入門十萬個為什么自定義類型、函數自定義類型12/16/202212數據庫入門數據庫設計需求分析邏輯模型E-R圖---實體關系數據流圖---業務處理物理模型數據庫分析、優化12/22/202246數據庫入門十萬個為什么數據庫設計需求分析12/16/202213數據庫入門十萬個為數據庫設計Steps⒈需求分析階段需求收集和分析，結果得到數據字典描述的數據需求（和數據流圖描述的處理需求）。⒉概念結構設計階段通過對用戶需求進行綜合、歸納與抽象，形成一個獨立于具體DBMS的概念模型，可以用E-R圖表示。⒊邏輯結構設計階段將概念結構轉換為某個DBMS所支持的數據模型（例如關系模型），并對其進行優化。⒋數據庫物理設計階段為邏輯數據模型選取一個最適合應用環境的物理結構（包括存儲結構和存取方法）。⒌數據庫實施階段運用DBMS提供的數據語言（例如SQL）及其宿主語言（例如C），根據邏輯設計和物理設計的結果建立數據庫，編制與調試應用程序，組織數據入庫，并進行試運行。⒍數據庫運行和維護階段數據庫應用系統經過試運行后即可投入正式運行。在數據庫系統運行過程中必須不斷地對其進行評價、調整與修改。12/22/202247數據庫入門十萬個為什么數據庫設計Steps⒈需求分析階段12/16/202214數數據庫設計實例項目工作管理，包含數據表：

職員花名冊，公司部門系統功能，權力角色，職員角色項目（Project)、項目成員（項目-職員）項目任務分解（WBS，分級，包括任務包、工作量估計，計劃，進度，人員安排，權重，任務包父親)項目任務執行跟蹤（工作日志）: 項目-職員–時間–對應任務包–實際工作量

12/22/202248數據庫入門十萬個為什么數據庫設計實例項目工作管理，包含數據表：12/16/202E-R圖實例12/22/202249數據庫入門十萬個為什么E-R圖實例12/16/202216數據庫入門十萬個為什么編程接口API ADO,OLEDB,ODBC(JDBC),EmbeddedSQL,URL,XML(Internet)使用數據庫打開到數據庫的連接。設置控制某些行為的選項執行Transact-SQL語句將返回代碼的數據值、輸出參數和結果集移入程序變量中，并通過程序變量由應用程序邏輯使用。在完成數據庫中的工作后斷開連接。12/22/202250數據庫入門十萬個為什么編程接口API ADO,OLEDB,ODBC(連接數據庫-PBTransactionSQLCA//事務對象sqlca.DBMS=ProfileString(gs_inifile,"DATABASE","dbms","MSS")sqlca.DATABASE=ProfileString(gs_inifile,"DATABASE","DATABASE","KF")sqlca.userid=ProfileString(gs_inifile,"DATABASE","userid","dbo")sqlca.dbpass=ProfileString(gs_inifile,"DATABASE","dbpass","")sqlca.logid=ProfileString(gs_inifile,"DATABASE","logid","sa")sqlca.logpass=ProfileString(gs_inifile,"DATABASE","LogPassWord","")sqlca.servername=ProfileString(gs_inifile,"DATABASE","ServerName","(local)")sqlca.dbparm=ProfileString(gs_inifile,"DATABASE","Dbparm","")sqlca.AutoCommit=FALSEconnectUsingSQLCA;//打開連接DisconnectUsingSQLCA;//切斷連接//ProfilesssSQLCA.DBMS="ODBC"SQLCA.AutoCommit=FalseSQLCA.DBParm="ConnectString='DSN=ExcelFiles;UID=;PWD='"連接Access//ProfileAccessSQLCA.DBMS="ODBC"SQLCA.AutoCommit=FalseSQLCA.DBParm="ConnectString='DSN=MSAccessDatabase;UID=;PWD='“//VB-1cn.Open"Driver={SQLServer};Server=Server1;Uid=SA;Pwd=;Database=northwind"http://VB–2cn.Provider="sqloledb“ProvStr="Server=MyServer;Database=northwind;Trusted_Connection=yes"cn.OpenprovStr12/22/202251數據庫入門十萬個為什么連接數據庫-PBTransactionSQLCA/獲取數據-SQL語句Stringls_empl_name//變量定義Selectempl_nameINTO:ls_empl_nameFROMbse_employeeWhereempl_no=‘system’UsingSQLCA;ifsqlca.sqlcode<>0then//…失敗Else//….成功EndifLongld_id=0Stringas_table=“tableXXX”//updateUPDATEsys_system_idSETid_counter=:ld_id WHEREid_table_name=:as_tableUsingSQLCA;12/22/202252數據庫入門十萬個為什么獲取數據-SQL語句Stringls_empl_name獲取數據-Datawindow//Dw_listDatawindow//d_employee_editDatawindowObjectdw_list.SetTransobject(SQLCA)dw_list.Retrieve(g_userinfo.is_dep_no,g_userinfo.is_empl_cls)….//保存數據ll_ret=dw_list.update(TRUE,TRUE) //判斷Ifll_ret=1thenCommit;//usingSQLCAElse Rollback;//usingSQLCA MessageBox("提示","保存時出錯!") Endif12/22/202253數據庫入門十萬個為什么獲取數據-Datawindow//Dw_listD與數據庫連接Datawindow12/22/202254數據庫入門與數據庫連接Datawindow12/16/202221數據Transact-SQL語言簡介標示符：createTableemployee(empl_nochar(10)PRIMARYKEY,empl_namevarchar(20),ageintdefault0,….）數據類型：函數：表達式：運算符：注釋關鍵字：12/22/202255數據庫入門十萬個為什么Transact-SQL語言簡介標示符：12/16/202創建與刪除表CreateTableCREATETABLE[dbo].[bse_department]( [dep_no][varchar](10)notNULL, [dep_tycode][char](1)NULL, [dep_name][char](10)NULL, [dep_no1][char](4)NULL, [dep_name1][char](10)NULL, [dep_bld_date][datetime]NULL, [dep_vendor_shared][smallint]NULL, [dep_telno][char](20)NULL)ON[PRIMARY]GOdroptabledroptable[dbo].[bse_department]GO12/22/202256數據庫入門十萬個為什么創建與刪除表CreateTable12/16/20222更改數據Insert

INSERTINTObse_department(dep_no,dep_name)VALUES(‘KFB’,‘開發部’)GOUpdate:UPDATEbse_departmentSETdep_name=‘development’WHEREdep_no=‘KFB’GODeleteDeletefrombse_departmentWHEREdep_no=‘KFB’12/22/202257數據庫入門十萬個為什么更改數據Insert12/16/202224數據庫入門十萬簡單查詢Select<列名,…>from<表…>Where<條件>…SELECT*FROMbse_departmentWHERE(dep_tycode='1')查詢統計：SELECTdep_noAS部門編號,COUNT(*)AS部門人數FROMbse_employeeGROUPBYdep_no12/22/202258數據庫入門十萬個為什么簡單查詢Select<列名,…>from<表…聯合查詢聯合查詢JOIN(INNER,LEFT,RIGHT)SELECTbse_department.dep_name,COUNT(*)AS部門人數FROMbse_employeeINNERJOINbse_departmentONbse_employee.dep_no=bse_department.dep_noGROUPBYbse_department.dep_name,bse_employee.dep_no12/22/202259數據庫入門十萬個為什么聯合查詢聯合查詢JOIN(INNER,LEFT,R聚合函數SUM(…)AVG(…)COU