SQLServer數(shù)據(jù)庫基礎(chǔ)課程(總復(fù)習(xí)).ppt

SQLServer數(shù)據(jù)庫教程從基礎(chǔ)到應(yīng)用 2015 09 05 學(xué)習(xí)目標 掌握數(shù)據(jù)庫相關(guān)基礎(chǔ)知識掌握簡單的T SQL語句 第0章數(shù)據(jù)庫基礎(chǔ) 什么是數(shù)據(jù)庫數(shù)據(jù)庫是指長期儲存在計算機內(nèi)的 有組織的 可共享的數(shù)據(jù)集合 數(shù)據(jù)庫中的數(shù)據(jù)按一定的數(shù)據(jù)模型組織 描述和儲存 具有較小的冗余度 較高的數(shù)據(jù)獨立性和易擴展性 并可為各種用戶共享 數(shù)據(jù)庫由數(shù)據(jù)庫管理系統(tǒng) DBMS 統(tǒng)一管理 數(shù)據(jù)的插入 修改和檢索均要通過數(shù)據(jù)庫管理系統(tǒng)進行 數(shù)據(jù)管理員 DBA 負責(zé)創(chuàng)建 監(jiān)控和維護整個數(shù)據(jù)庫 數(shù)據(jù)庫模型 層次模型網(wǎng)狀模型關(guān)系模型關(guān)系模型是目前最為流行的一種數(shù)據(jù)模型 用二維表格結(jié)構(gòu)表示實體集 數(shù)據(jù)庫對象 表 由行和列組成每列又稱為一個字段 每列的標題稱為字段名一行數(shù)據(jù)稱為一個或一條記錄一個數(shù)據(jù)庫表由一條或多條記錄組成 沒有記錄的表稱為空表 每個表中通常都有一個主關(guān)鍵字 也叫主鍵 用于唯一地確定一條記錄 主鍵不允許空值 不能存在具有相同的主鍵值的兩個行 字段名 記錄 主鍵 概念數(shù)據(jù)模型與邏輯數(shù)據(jù)模型轉(zhuǎn)換 E R圖同關(guān)系模式轉(zhuǎn)換1 1的聯(lián)系轉(zhuǎn)換為關(guān)系模式 bjb 班級編號 院系 專業(yè) 人數(shù) bzb 學(xué)號 姓名 syb 學(xué)號 班級編號 注意 聯(lián)系關(guān)系模式的屬性包括包括聯(lián)系屬性 以及各實體的主碼 主碼選實體集任意一方主碼 1 1 聯(lián)系 實體 屬性 概念數(shù)據(jù)模型與邏輯數(shù)據(jù)數(shù)據(jù)模型轉(zhuǎn)換 1 n聯(lián)系E R圖轉(zhuǎn)換為關(guān)系模式 bjb 班級編號 專業(yè) 人數(shù) xsb 學(xué)號 姓名 性別 專業(yè) syb 學(xué)號 班級編號 n 1 概念數(shù)據(jù)模型邏輯數(shù)據(jù)數(shù)據(jù)模型轉(zhuǎn)換 n n聯(lián)系E R圖轉(zhuǎn)換為關(guān)系模式 xsb 學(xué)號 姓名 性別 專業(yè) kcb 課程編號 課名 學(xué)期 學(xué)時 學(xué)分學(xué)分 學(xué)時 cjb 學(xué)號 課程編號 成績 1 1 n n 成績 使用SQL語句創(chuàng)建數(shù)據(jù)庫 一 創(chuàng)建數(shù)據(jù)庫1 利用對象資源管理器創(chuàng)建用戶數(shù)據(jù)庫 界面方式 1 選擇 開始 程序 MicrosoftSQLServer2012 打開SQLServerManagementStudio 2 使用 Windows身份驗證 連接到SQLServer2012數(shù)據(jù)庫實例 3 展開SQLServer實例 右擊 數(shù)據(jù)庫 然后在彈出的快捷菜單中選擇 新建數(shù)據(jù)庫存 命令 打開 新建數(shù)據(jù)庫 對話框 4 在 新建數(shù)據(jù)庫 對話框中 可以定義數(shù)據(jù)庫的名稱 數(shù)據(jù)文件和日志文件的邏輯名稱和路徑 文件組 初始大小和增長方式等 輸入數(shù)據(jù)庫名稱 pxscj student P 29 圖 使用SQL語句創(chuàng)建數(shù)據(jù)庫 2 使用T SQL語句創(chuàng)建數(shù)據(jù)庫 命令方式 在SQLServerManagementStudio中 單擊標準工具欄的 新建查詢 按鈕 啟動SQL編輯器窗口 在光標處輸入T SQL語句 單擊 執(zhí)行 按鈕 SQL編輯器就提交用戶輸入的T SQL語句 然后發(fā)送到服務(wù)器執(zhí)行 并返回執(zhí)行結(jié)果 P 35 頁例1 創(chuàng)建一個只含一個數(shù)據(jù)文件和一個事務(wù)日志文件的數(shù)據(jù)庫 createdatabasestudent1on name student1 dat d student dat mdf size 10 maxsize 50 20 logon name student1 log d student log ldf size 4 maxsize 25 5 回顧課本36頁例2 7 創(chuàng)建數(shù)據(jù)庫 練習(xí) 1 寫出創(chuàng)建產(chǎn)品銷售數(shù)據(jù)庫cpxs 初始大小為10MB 最大為50MB 增長方式為20 日志文件名初始為4MB 最大為25MB 增長方式為5MB 存儲路徑為 D SQLServer 2012 data createdatabasecpxson name cpxs dat D SQLServer 2012 cpxs dat mdf size 10MB maxsize 50MB logon name cpxs log D SQLServer 2012 cpxs log ldf size 4MB maxsize 25MB 數(shù)據(jù)庫修改 刪除 對數(shù)據(jù)庫可修改以下幾項 增加或刪除數(shù)據(jù)文件增加或刪除日志文件增加或刪除文件組改變數(shù)據(jù)文件的大小和增長方式改變?nèi)罩疚募拇笮『驮鲩L方式重命名數(shù)據(jù)庫 數(shù)據(jù)庫修改 刪除 界面方式 對象資源管理器 窗口 點擊所要修改的數(shù)據(jù)庫 右擊鼠標選擇 屬性 打開 數(shù)據(jù)庫屬性 窗口1 修改文件屬性 數(shù)據(jù)庫修改 刪除 界面方式 數(shù)據(jù)庫修改 刪除 界面方式 2 增加輔助數(shù)據(jù)文件 數(shù)據(jù)庫修改 刪除 界面方式 3 增加文件組 輸入文件組名稱 使用SQL語句修改 刪除數(shù)據(jù)庫 命令方式ALTERDATABASE修改數(shù)據(jù)庫ADD 增加文件 文件組 MODIFYFILE更改文件屬性REMOVEFILE文件名刪除數(shù)據(jù)文件DROPDATABASE刪除數(shù)據(jù)庫 使用SQL語句修改 刪除數(shù)據(jù)庫 課本例子 P38 40頁例2 8例2 10例2 11例2 12例2 13 練習(xí) 1 修改數(shù)據(jù)庫pxscj的屬性 將日志文件的初始大小改為15MB 增長方式改為15 2 為數(shù)據(jù)庫pxscj添加文件組pgroup 并為此文件組添加一個數(shù)據(jù)文件 大小為25MB 存儲路徑為 D data 表的創(chuàng)建和操作 表結(jié)構(gòu) 表創(chuàng)建和操作 界面方式 表創(chuàng)建和操作 界面方式 表創(chuàng)建和操作 命令方式 用SQL語句創(chuàng)建如下兩個基本表 學(xué)生表 Student 課程表 Course 結(jié)構(gòu)如下所示Student表 表創(chuàng)建和操作 CreatetableStudent Snochar 7 primarykey Snamechar 10 notnull Ssexchar 2 check Ssex 男 orSsex 女 Sageintcheck Sagebetween15and45 Sdeptvarchar 20 default 計算機系 表創(chuàng)建和操作 Course表結(jié)構(gòu) 表創(chuàng)建和操作 Createtablecourse Cnochar 10 primarykey Cnamevarchar 20 notnull Ccreditintcheck Scteditbetween0and50 Semesterintcheck Semester 0 Periodintcheck Period 0 表創(chuàng)建和操作 修改表結(jié)構(gòu) P57頁例3 3例3 4P59頁圖操作表數(shù)據(jù) 界面方式 表創(chuàng)建和操作 命令方式操作表數(shù)據(jù) 向表xsb中插入記錄INSERTINTOxsbVALUERS 201502 王海 1 19990501 電子商務(wù) 50 NULL 201504 李華 1 19990604 電子商務(wù) NULL NULL INSERTINTOxsb 學(xué)號 姓名 性別 專業(yè) VALUERS 201502 王海 1 電子商務(wù) 表創(chuàng)建和操作 在xsb1中生成計算機專業(yè)學(xué)生的xsb2 1 INSERTINTOxsb2SELECT FROMxsb1WHERE專業(yè) 計算機 2 SELECT INTOxsb2FROMxsb1WHERE專業(yè) 計算機 表創(chuàng)建和操作 練習(xí) 1 由cjb生成成績表大于90分的cjb11 INSERTINTOcjb1SELECT FROMcjbWHERE成績 90分2 SELECT INTOcjb1FROMcjbWHERE成績 90分 表創(chuàng)建和操作 刪除符合條件記錄 DELETEFROMxsbWHERE總學(xué)分 0GO 數(shù)據(jù)庫的查詢 常用術(shù)語 關(guān)鍵碼 超鍵在關(guān)系模式中 能唯一標識元組的屬性集稱為超鍵 SuperKey 候選鍵如果一個屬性集能唯一標識元組 且又不含有多于屬性 那么這個屬性集被稱為候選鍵 CandidateKey 主鍵關(guān)系模式中用戶正在使用的候選鍵稱之為主鍵 PrimaryKey 外鍵如果關(guān)系模式R中某屬性集是其他模式的候選鍵 那么該屬性對模型R而言是外鍵 數(shù)據(jù)庫對象 外鍵 存在于B表中的A表的主鍵 數(shù)據(jù)庫對象 索引 Index 索引是根據(jù)指定的數(shù)據(jù)庫表列建立起來的順序 它提供了快速訪問數(shù)據(jù)的途徑 并且可監(jiān)督表的數(shù)據(jù) 使其索引所指向的列中的數(shù)據(jù)不重復(fù) 視圖 View 是一個虛擬的表 在數(shù)據(jù)庫中并不實際存 視圖是由查詢數(shù)據(jù)庫表產(chǎn)生的 視圖可以用來控制用戶對數(shù)據(jù)的訪問 并能簡化數(shù)據(jù)的顯示 即通過視圖只顯示那些需要的數(shù)據(jù)信息 觸發(fā)器 Trigger 觸發(fā)器是一個用戶定義的SQL事務(wù)命令的集合 當對一個表進行插入 更改 刪除時 這組命令就會自動執(zhí)行 存儲過程 StoredProcedure 存儲過程是為完成特定的功能而匯集在一起的一組SQL程序語句 經(jīng)編譯后存儲在數(shù)據(jù)庫中的SQL程序 SQLServer關(guān)系數(shù)據(jù)庫簡介 SQLServer是由Microsoft開發(fā)和推廣的關(guān)系數(shù)據(jù)庫管理系統(tǒng) 最初是由Microsoft Sybase和Ashton Tate三家公司共同開發(fā)的 并于1988年推出了第一個OS 2版本 SQLServer近年來不斷更新版本 1996年 Microsoft推出了SQLServer6 5版本 1998年 SQLServer7 0版本和用戶見面 2000年 SQLServer2000推出 2005年 微軟發(fā)布了SQLServer20052008年 微軟發(fā)布了最新的SQLServer2008 SQLServer常見版本 企業(yè)版 EnterpriseEdition 標準版 StandardEdition 個人版 PersonalEdition 開發(fā)者版 DeveloperEdition 安裝 運行SQLServer2000的軟件需求 SQLServer2000企業(yè)版必須運行于安裝WindowsNTServerEnterpriseEdition4 0或者Windows2000AdvancedServer以及更高版本的操作系統(tǒng)下 SQLServer2000標準版必須運行于安裝WindowsNTServerEnterpriseEdition4 0 WindowsNTServer4 0 Windows2000Server以及更高版本的操作系統(tǒng)下 SQLServer2000個人版可在多種操作系統(tǒng)下運行 如可運行于Windows9x WindowsNT4 0或Windows2000的服務(wù)器版或工作站版的操作系統(tǒng)下 SQLServer2000開發(fā)者版可運行于上述Windows9x以外的所有操作系統(tǒng)下 SQLServer安裝 SQLServer系統(tǒng)數(shù)據(jù)庫簡介 Master數(shù)據(jù)庫是SQLServer系統(tǒng)最重要的數(shù)據(jù)庫 它記錄了SQLServer系統(tǒng)的所有系統(tǒng)信息 這些系統(tǒng)信息包括所有的登錄信息 系統(tǒng)設(shè)置信息 SQLServer的初始化信息和其它系統(tǒng)數(shù)據(jù)庫及用戶數(shù)據(jù)庫的相關(guān)信息 Model數(shù)據(jù)庫是所有用戶數(shù)據(jù)庫和Tempdb數(shù)據(jù)庫的模板數(shù)據(jù)庫 它含有Master數(shù)據(jù)庫所有系統(tǒng)表的子集 這些系統(tǒng)數(shù)據(jù)庫是每個用戶定義數(shù)據(jù)庫需要的 Msdb數(shù)據(jù)庫是代理服務(wù)數(shù)據(jù)庫 為其警報 任務(wù)調(diào)度和記錄操作員的操作提供存儲空間 Tempdb是一個臨時數(shù)據(jù)庫 默認大小是8M 它為所有的臨時表 臨時存儲過程及其它臨時操作提供存儲空間 Pubs和Northwind數(shù)據(jù)庫是兩個示例數(shù)據(jù)庫 SQLServer系統(tǒng)表簡介 1 sysobjects表SQLServer的主系統(tǒng)表sysobjects出現(xiàn)在每個數(shù)據(jù)庫中 它對每個數(shù)據(jù)庫對象含有一行記錄 2 syscolumns表系統(tǒng)表syscolumns出現(xiàn)在master數(shù)據(jù)庫和每個用戶自定義的數(shù)據(jù)庫中 它對基表或者視圖的每個列和存儲過程中的每個參數(shù)含有一行記錄 3 sysindexes表系統(tǒng)表sysindexes出現(xiàn)在master數(shù)據(jù)庫和每個用戶自定義的數(shù)據(jù)庫中 它對每個索引和沒有聚簇索引的每個表含有一行記錄 它還對包括文本 圖像數(shù)據(jù)的每個表含有一行記錄 SQLServer系統(tǒng)表簡介 4 sysusers表系統(tǒng)表sysusers出現(xiàn)在master數(shù)據(jù)庫和每個用戶自定義的數(shù)據(jù)庫中 它對整個數(shù)據(jù)庫中的每個WindowsNT用戶 WindowsNT用戶組 SQLServer用戶或者SQLServer角色含有一行記錄 5 sysdatabases表系統(tǒng)表sysdatabases對SQLServer系統(tǒng)上的每個系統(tǒng)數(shù)據(jù)庫和用戶自定義的數(shù)據(jù)庫含有一行記錄 它只出現(xiàn)在master數(shù)據(jù)庫中 6 sysdepends表系統(tǒng)表Sysdepends對表 視圖和存儲過程之間的每個依賴關(guān)系含有一行記錄 它出現(xiàn)在master數(shù)據(jù)庫和每個用戶自定義的數(shù)據(jù)庫中 7 sysconstraints表系統(tǒng)表sysconstraints對使用CREATETABLE或者ALTERTABLE語句為數(shù)據(jù)庫對象定義的每個完整性約束含有一行記錄 它出現(xiàn)在master數(shù)據(jù)庫和每個用戶自定義的數(shù)據(jù)庫中 SQLServer工具和實用程序 企業(yè)管理器服務(wù)管理器查詢分析器事件探查器導(dǎo)入和導(dǎo)出數(shù)據(jù)聯(lián)機幫助文檔 企業(yè)管理器 EnterpriseManager 服務(wù)管理器 ServiceManagerManager 查詢分析器 QueryAnalyzer 事件探查器 導(dǎo)入和導(dǎo)出數(shù)據(jù) 聯(lián)機幫助文檔 注冊一個新的數(shù)據(jù)庫 鏈接服務(wù)器 備份數(shù)據(jù)庫 還原數(shù)據(jù)庫 分離數(shù)據(jù)庫 附加數(shù)據(jù)庫 數(shù)據(jù)庫維護計劃 生成數(shù)據(jù)庫結(jié)構(gòu)腳本 標準SQL語言 SQL StructuredQueryLanguage 語言最早是由Boyce和Chamberlin在1974年提出 起初稱之為SEQUEL StructuredEnglishQueryLanguage 設(shè)計目的是為了便于對SystemR數(shù)據(jù)庫系統(tǒng)存儲數(shù)據(jù)進行操作 1986年ANSI批準SQL語言作為關(guān)系型數(shù)據(jù)庫語言的標準 1987年ISO也通過了這一標準 目前 幾乎所有的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)都支持SQL語言 Transact SQL語言 數(shù)據(jù)定義語言 DDL DataDefinitionLanguage 包括創(chuàng)建數(shù)據(jù)庫表結(jié)構(gòu) 創(chuàng)建視圖和創(chuàng)建表的索引 以及刪除已經(jīng)存在的表 視圖和索引等 數(shù)據(jù)定義語句的SQL動詞是CREATE DROP和ALTER 數(shù)據(jù)操縱語言 DML DatamanipulationLanguage 主要用于創(chuàng)建數(shù)據(jù)庫表后 查詢 輸入 刪除和修改表中的數(shù)據(jù) SQL動詞是SELECT INSERT DELETE和UPDATE 數(shù)據(jù)控制語言 DCL DataControlLanguage 包括數(shù)據(jù)庫事務(wù)管理功能和數(shù)據(jù)保護功能 對數(shù)據(jù)庫提供完整性約束控制 控制數(shù)據(jù)庫的安全性 提供了多用戶并發(fā)控制和恢復(fù)的功能 支持事務(wù)提交和回滾等 SQL動詞是GRANT和REVOKE 系統(tǒng)存儲過程 SystemStoredProcedure 存儲過程是SQL語句和流程控制語句的預(yù)編譯集合 是封裝重復(fù)任務(wù)操作的方法 SQLServer的對象命名約定 三段式名字標識對象 前兩者可以省略 數(shù)據(jù)庫名默認是當前數(shù)據(jù)庫 所有者默認式數(shù)據(jù)庫的所有者dbo 如 Pubs dbo authorsNorthwind dbo scNorthwind dbo sc grade 創(chuàng)建練習(xí)數(shù)據(jù)庫和表 使用企業(yè)管理器添加數(shù)據(jù)庫 創(chuàng)建練習(xí)數(shù)據(jù)庫和表 使用企業(yè)管理器分別添加表 c s sc 創(chuàng)建練習(xí)數(shù)據(jù)庫和表 使用企業(yè)管理器添加各個表的記錄 c s sc Select語句 查詢課程表 查詢學(xué)生表查詢課程表 只顯示課程名和教師名 別名AS 使用別名是增加select語句的可讀性 語法是 數(shù)據(jù)表名as數(shù)據(jù)表別名如 Selecte 員工編號 e 員工姓名from員工表aseSame p teacherfromcasp Union 將兩個或兩個以上的查詢結(jié)果合并為一個結(jié)果集 語法 Select語句Union all Select語句默認情況下 union將從最終結(jié)果集中刪除重復(fù)的記錄 如果希望保留所有記錄 使用all關(guān)鍵字 條件查詢 Select from表名where查詢條件例如 Select fromswheresname wang 模糊查詢 Like關(guān)鍵字的提供使我們的模糊查詢有了基礎(chǔ) Like關(guān)鍵字后提供了 下劃線和 百分號這兩種通配符 多表查詢 聯(lián)接查詢 Select froms sc得到一個非常大的結(jié)果集 s表和sc表的笛卡爾積Select froms scwheres s sc s Select froms sc cwheres s sc s andc c sc c Selectsname cname gradefroms sc cwheres s sc s andc c sc c 查詢參加了 缺陷管理 課程考試的同學(xué) Selectsnamefroms sc cwheres s sc s andc c sc c andcname 缺陷管理 多表查詢 嵌套查詢 查詢wang同學(xué)的考試成績 Select fromscwheres selects fromswheresname wang 查詢參加了 缺陷管理 課程考試的同學(xué) Select fromscwherec selectc fromcwherecname 缺陷管理 Selectsnamefromswheres in Selects fromscwherec selectc fromcwherecname 缺陷管理 聯(lián)接查詢vs 嵌套查詢 Groupby SelectsexfromsgroupbysexSelectcount sexfromsgroupbysexSelectsname sum grade count froms scwheres s sc s groupbysname Having 幫助我們對需要分組的數(shù)據(jù)做一個過濾 例如 Selectsname sum grade count froms scwheres s sc s groupbysnamehavingsname chen 這樣我們就可以把chen的記錄過濾掉 Orderby ORDERBY是一個可選的子句 它允許你根據(jù)指定要orderby的列來以上升或者下降的順序來顯示查詢的結(jié)果 例如 ASC AscendingOrder 這個是缺省的DESC DescendingOrderSelect fromscorderbygradedescSelecttop3 fromscorderbygradedesc Join 內(nèi)聯(lián)接 INNERJOIN 左聯(lián)接 LEFTJOIN 右聯(lián)接 RIGHTJOIN 全聯(lián)接 FULLJOIN 內(nèi)聯(lián)接 INNERJOIN 內(nèi)聯(lián)接會返回兩個表中所匹配的所有的行 假如Employees中的某些行與Orders中的行不匹配 那些行就不會被列出來 左聯(lián)接 LEFTJOIN 左聯(lián)接會從第一個表 Employees 那里返回所有的行 即使在第二個表 Orders 中沒有匹配的行 假如Employees中的某些行沒有在Orders中可匹配的行 這些行也會被列出來 右聯(lián)接 RIGHTJOIN 右聯(lián)接會從第二個表格 Orders 那里返回所有的行 即使在第一個表格中沒有可匹配的行 假如Orders中的某些行沒有在Employees可匹配的行 那些行也會被列出 全聯(lián)接 FULLJOIN 全聯(lián)接會從兩個表格 Orders 那里返回所有的行 即使在第一個表格或第二個中沒有可匹配的行 Insert語句 INSERTINTOs s sname age sex VALUES S101 jiang 39 M Insertintostudent s sname age sex selects sname age sexfroms Delete語句 DELETEFROMstudentdeletefromstudentwheresname jiang Update語句 UPDATEstudentSETsname chennengji WHEREsname chen andage 22 UPDATEstudentSETage age 1WHEREsname chennengji 事務(wù) 事務(wù)是單個的工作單元 如果某一事務(wù)成功 則在該事務(wù)中進行的所有數(shù)據(jù)更改均會提交 成為數(shù)據(jù)庫中的永久組成部分 如果事務(wù)遇到錯誤且必須取消或回滾 則所有數(shù)據(jù)更改均被清除 事務(wù)操作的語法 BEGINTRANSACTIONCOMMITTRANSACTIONROLLBACKTRANSACTIONSAVETRANSACTION 表設(shè)計 主鍵 表中經(jīng)常有一個列或列的組合 其值能唯