《創(chuàng)建與管理數(shù)據(jù)庫(kù)》PPT幻燈片

1、1,SQL Server 2005T-SQL數(shù)據(jù)庫(kù)設(shè)計(jì),主講：毛嘉莉 e-mail: qq:646194029,2,第1章內(nèi)容回顧,數(shù)據(jù)庫(kù)的基本概念 數(shù)據(jù)、表、數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)管理系統(tǒng)、DBA SQL Server 2005的主要版本 企業(yè)版 軟硬件要求 安裝過(guò)程 預(yù)安裝 正式安裝 SQL Server 的管理工具 SQL Server 數(shù)據(jù)存儲(chǔ)結(jié)構(gòu),3,第2章 管理數(shù)據(jù)庫(kù),4,本章目標(biāo),掌握創(chuàng)建和修改數(shù)據(jù)庫(kù)的方法 掌握創(chuàng)建和修改表的方法 掌握插入、修改、刪除和查詢數(shù)據(jù)的方法 備份、還原數(shù)據(jù)庫(kù),5,本章結(jié)構(gòu),管理數(shù)據(jù)庫(kù),分離和附加數(shù)據(jù)庫(kù),收縮數(shù)據(jù)庫(kù),擴(kuò)展數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù),修改數(shù)據(jù)庫(kù),刪除數(shù)據(jù)庫(kù)

2、,操作數(shù)據(jù),更改數(shù)據(jù),查詢數(shù)據(jù),新建表,表的維護(hù),新建用戶數(shù)據(jù)庫(kù),6,新建數(shù)據(jù)庫(kù),新建用戶數(shù)據(jù)庫(kù) 所有者 一個(gè)數(shù)據(jù)文件 一個(gè)日志文件 初始大小 自動(dòng)增長(zhǎng) 默認(rèn)路徑,主文件,事務(wù)日志文件,初始大小,自動(dòng)增長(zhǎng),數(shù)據(jù)庫(kù)名稱,所有者,7,修改數(shù)據(jù)庫(kù),擴(kuò)展數(shù)據(jù)庫(kù) 收縮數(shù)據(jù)庫(kù) 分離附加數(shù)據(jù)庫(kù) 刪除數(shù)據(jù)庫(kù),SQL Server,8,擴(kuò)展數(shù)據(jù)庫(kù),當(dāng)數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件或日志文件的空間被占滿 自動(dòng)擴(kuò)展 設(shè)置文件自動(dòng)增長(zhǎng) 指定允許文件增長(zhǎng)到的最大大小 手動(dòng)擴(kuò)展 數(shù)據(jù)庫(kù)的大小 添加數(shù)據(jù)庫(kù)文件,添加數(shù)據(jù)文件,自動(dòng)增長(zhǎng),最大文件大小,啟動(dòng)自動(dòng)增長(zhǎng),輔助文件,9,收縮數(shù)據(jù)庫(kù),收縮數(shù)據(jù)庫(kù)可以把不使用的空間釋放出來(lái) 自動(dòng)數(shù)據(jù)庫(kù)

3、收縮 將自動(dòng)收縮選項(xiàng)設(shè)置為 TRUE 手動(dòng)數(shù)據(jù)庫(kù)收縮 收縮數(shù)據(jù)庫(kù) 收縮某個(gè)數(shù)據(jù)庫(kù)文件,當(dāng)前分配的空間,選擇收縮的文件,選擇文件類型,收縮的選項(xiàng),可用空間,手動(dòng)收縮數(shù)據(jù)庫(kù),10,分離和附加數(shù)據(jù)庫(kù),用于移動(dòng)數(shù)據(jù)庫(kù) 分離數(shù)據(jù)庫(kù) 將數(shù)據(jù)庫(kù)從 SQL Server 實(shí)例中移除 除了系統(tǒng)數(shù)據(jù)庫(kù)之外 保持?jǐn)?shù)據(jù)文件和事務(wù)日志文件的完整性和一致性 附加數(shù)據(jù)庫(kù) 分離后的數(shù)據(jù)庫(kù)的數(shù)據(jù)和事務(wù)日志文件 同一個(gè) SQL Server 實(shí)例 不同的 SQL Server 實(shí)例,要分離的數(shù)據(jù)庫(kù),添加文件,要附加的數(shù)據(jù)庫(kù),分離和附加數(shù)據(jù)庫(kù),11,刪除文件和數(shù)據(jù)庫(kù),刪除文件 文件必須完全為空，才能夠刪除 刪除數(shù)據(jù)庫(kù) 文件及其數(shù)據(jù)

4、都從服務(wù)器上的磁盤中刪除 一旦刪除數(shù)據(jù)庫(kù)，即被永久刪除 不能刪除系統(tǒng)數(shù)據(jù)庫(kù),要?jiǎng)h除的數(shù)據(jù)庫(kù),刪除,12,階段總結(jié),新建數(shù)據(jù)庫(kù) 更改數(shù)據(jù)庫(kù) 擴(kuò)展數(shù)據(jù)庫(kù) 收縮數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 刪除數(shù)據(jù)庫(kù),13,階段練習(xí),背景 Benet公司已建立一個(gè)TestDB數(shù)據(jù)庫(kù)用于應(yīng)用開(kāi)發(fā)，TestDB包含一個(gè)數(shù)據(jù)文件和一個(gè)日志文件?，F(xiàn)在要： 設(shè)置TestDB數(shù)據(jù)文件初始大小為5M，自動(dòng)增長(zhǎng)，最大大小為50M 設(shè)置TestDB日志文件初始大小為5M，自動(dòng)增長(zhǎng)，限制文件增長(zhǎng)為20M 設(shè)置TestDB為自動(dòng)收縮 目標(biāo) 擴(kuò)展數(shù)據(jù)庫(kù) 收縮數(shù)據(jù)庫(kù),14,新建表,表和列的名稱必須遵守標(biāo)識(shí)符的規(guī)定 列名在表中必須是唯一的 必須為

5、每列指定數(shù)據(jù)類型,列名,選擇數(shù)據(jù)類型,是否允許為空,15,常用數(shù)據(jù)類型,精確數(shù)字 如：int -231 到 231-1 日期和時(shí)間 如：datetime 1753年1月1日到9999年12月31日 字符串 如：Varchar 最大存儲(chǔ)大小是 231-1 個(gè)字節(jié) Unicode 字符串 如：nvarchar 最大存儲(chǔ)大小為 231-1 字節(jié),16,向表添加數(shù)據(jù),插入新行,查詢?cè)O(shè)計(jì)器,17,SQL概述,SQL ：（Structured Query Language結(jié)構(gòu)化查詢化語(yǔ)言），不僅僅具有查詢功能。它是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)操作語(yǔ)言。 1974年Boyce和Chamberlin提出，

6、首先在IBM 公司的關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)System R上實(shí)現(xiàn) 1986年10月ANSI（美國(guó)國(guó)家標(biāo)準(zhǔn)局）的數(shù)據(jù)庫(kù)委員會(huì)批準(zhǔn)了SQL作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn) 1987年ISO（國(guó)際標(biāo)準(zhǔn)化組織）也通過(guò)了這一標(biāo)準(zhǔn)。目前的大中型數(shù)據(jù)庫(kù)基本都支持于1992年發(fā)布的SQL-92標(biāo)準(zhǔn),18,標(biāo)準(zhǔn)化,有關(guān)組織 ANSI(American Natural Standard Institute) ISO(International Organization for Standardization) 有關(guān)標(biāo)準(zhǔn) SQL-86 “數(shù)據(jù)庫(kù)語(yǔ)言SQL” SQL-89 “具有完整性增強(qiáng)的數(shù)據(jù)庫(kù)語(yǔ)言SQL”，增加了對(duì)完整性約束

7、的支持 SQL-92 “數(shù)據(jù)庫(kù)語(yǔ)言SQL”，是SQL-89的超集，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強(qiáng)的完整性、安全性支持等。 SQL-3 正在討論中的新的標(biāo)準(zhǔn)，將增加對(duì)面向?qū)ο竽Ｐ偷闹С?19,SQL對(duì)關(guān)系數(shù)據(jù)庫(kù)模式的支持,用戶1,用戶2,用戶4,視圖V1,視圖V1,用戶3,基本表B1,基本表B2,基本表B4,基本表B3,存儲(chǔ)文件S1,存儲(chǔ)文件S2,存儲(chǔ)文件S4,存儲(chǔ)文件S3,SQL用戶,VIEW,Base Table,Stored file,SQL數(shù)據(jù)庫(kù)體系結(jié)構(gòu),20,SQL的特點(diǎn),面向集合操作 一次一集合 高度非過(guò)程化 用戶只需提出“做什么”，無(wú)須告訴“怎么做”，不必

8、了解存取路徑 集數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)控制功能于一體 統(tǒng)一語(yǔ)法結(jié)構(gòu)的兩種使用方式，簡(jiǎn)單易學(xué),21,SQL語(yǔ)言的動(dòng)詞,22,SQL語(yǔ)言書(shū)寫注意事項(xiàng),1.大小寫不敏感 2.sql語(yǔ)句中出現(xiàn)的所有單雙引號(hào)，逗號(hào)，分號(hào)必須為半角字符。 3.字符常量要用引號(hào)引起 4.數(shù)字常量不用加引號(hào) 5.不同具體RDBMS中，命令可能有差異，以該RDBMS命令手冊(cè)為準(zhǔn),23,Transact-SQL語(yǔ)句,可以實(shí)現(xiàn)數(shù)據(jù)的查詢和數(shù)據(jù)的更改等操作 SELECT INSERT UPDATE DELETE,24,使用查詢編輯器,查詢窗口,執(zhí)行按鈕,查詢結(jié)果,打開(kāi)查詢編輯器,25,查詢語(yǔ)句,命令格式,SELECT select

9、_list FROM table_list WHERE search_conditions ORDER BY order_list ASC | DESC ,26,查詢示例3-1,例1：查詢所有員工信息 SELECT * FROM Employees *表示對(duì)所有列的引用 例2 ：如果只是想要得到某幾列的值 SELECT Name, Title FROM Employees 明確地列出指定的列內(nèi)容,27,查詢示例3-2,例3：查詢所有工資大于4000的員工 SELECT * FROM Employees WHERE Salary 4000 Where 子句-按指定的條件返回?cái)?shù)據(jù)行 操作符：=、B

10、ETWEEN AND、NOT、LIKE等,例4：查詢工資在3000到4000的員工 SELECT * FROM Employees WHERE Salary Between 3000 and 4000 BETWEEN AND ：指定值的范圍,例5：查找雇員表中姓張的員工 SELECT * FROM Employees WHERE Name LIKE 張% ; LIKE ：字符串進(jìn)行模式匹配,28,查詢示例3-3,例6：返回按工資降序排序的結(jié)果 SELECT * FROM Employees ORDER BY Salary DESC ORDER BY： 對(duì)行進(jìn)行排序 關(guān)鍵字DESC/ASC：指降

11、序/升序排列,29,插入數(shù)據(jù),例7：在雇員信息表中加入一個(gè)新員工 INSERT INTO Employees(lastname,firstname,title,birthdate,extension) VALUES (王,燕, 銷售員, 1986-11-30 , 4500) 向表中插入一行值 例8：把Employees表內(nèi)容導(dǎo)入到EmployeesNew新表中 SELECT * INTO EmployeesNew FROM Employees 從另一個(gè)表插入行,插入數(shù)據(jù),30,更新數(shù)據(jù),例9：將所有銷售員的工資提高 10% UPDATE Employees SET Salary = Salar

12、y * 1.1 WHERE Title = 銷售員；,更新數(shù)據(jù),31,刪除數(shù)據(jù),例10：當(dāng)銷售員李芳離職后，將其從表中刪除 DELETE FROM Employees WHERE name = 李芳 刪除符合條件的行 例11：從 EmployeesNew 表中刪除所有信息 DELETE FROM EmployeesNew 刪除所有行,刪除數(shù)據(jù),32,表的維護(hù),修改表 使用表設(shè)計(jì)器 增加列 刪除列 刪除表,添加新列,刪除列,確認(rèn)要?jiǎng)h除的表,33,階段總結(jié),新建表 查詢數(shù)據(jù) 篩選列 篩選行 排序 更改數(shù)據(jù)庫(kù) 插入數(shù)據(jù) 更新數(shù)據(jù) 刪除數(shù)據(jù),34,階段練習(xí),背景 Benet公司在應(yīng)用開(kāi)發(fā)數(shù)據(jù)庫(kù)Test

13、DB上建立了一個(gè)銷售部門人員信息表，現(xiàn)在要： 由于銷售經(jīng)理調(diào)職，刪除其記錄 將銷售員提升為銷售經(jīng)理，更改其職位信息 查詢年齡在30-40歲的員工信息 更改銷售人員信息 目標(biāo) 刪除記錄 更改記錄 查詢記錄,35,本章總結(jié),管理數(shù)據(jù)庫(kù),分離和附加數(shù)據(jù)庫(kù),收縮數(shù)據(jù)庫(kù),擴(kuò)展數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù),修改數(shù)據(jù)庫(kù),刪除數(shù)據(jù)庫(kù),操作數(shù)據(jù),更改數(shù)據(jù),查詢數(shù)據(jù),新建表,表的維護(hù),新建用戶數(shù)據(jù)庫(kù),36,實(shí)驗(yàn),任務(wù)1 創(chuàng)建和修改數(shù)據(jù)庫(kù) 任務(wù)2 操作數(shù)據(jù),37,任務(wù)1 創(chuàng)建和修改數(shù)據(jù)庫(kù),背景 Benet公司已經(jīng)安裝好一臺(tái)SQLServer服務(wù)器，現(xiàn)在需要建立1個(gè)數(shù)據(jù)庫(kù)，具體要求如下： 新建數(shù)據(jù)庫(kù)名為Benet 設(shè)置數(shù)據(jù)文件

14、初始文件大小為10M，不自動(dòng)增長(zhǎng) 設(shè)置日志文件初始文件大小為10M，不自動(dòng)增長(zhǎng) 添加數(shù)據(jù)文件Data2，初始文件大小為25M，不自動(dòng)增長(zhǎng) 驗(yàn)證新建的數(shù)據(jù)庫(kù)。 設(shè)置數(shù)據(jù)庫(kù)為自動(dòng)收縮 完成標(biāo)準(zhǔn) 根據(jù)要求新建一個(gè)用戶數(shù)據(jù)庫(kù) 根據(jù)要求配置用戶數(shù)據(jù)庫(kù)參數(shù),38,任務(wù)2 操作數(shù)據(jù),背景 已建立好Benet數(shù)據(jù)庫(kù)，現(xiàn)在需要 創(chuàng)建一個(gè)人員信息表叫Employee 使用表設(shè)計(jì)器添加員工信息記錄 查詢所有人員的信息， 查詢所有在1993年1月到1994年6月入職的人員記錄。 查詢銷售部男性員工的姓名 由于員工“金鵬”離職，刪除其記錄信息。 銷售部新招聘一名男銷售員叫林青，需插入其一條記錄 新建一個(gè)表只包括“姓名”

15、和“部門”列 銷售員孫林調(diào)到市場(chǎng)部做市場(chǎng)專員，需要更改其記錄信息 由于公司需要，給表中添加一列為分機(jī)號(hào) 完成標(biāo)準(zhǔn) 根據(jù)要求新建一個(gè)表 根據(jù)要求對(duì)表數(shù)據(jù)進(jìn)行查詢、更改,39,備份和還原,40,備份和還原,數(shù)據(jù)丟失的原因 程序錯(cuò)誤 人為錯(cuò)誤 計(jì)算機(jī)失敗 磁盤失敗 災(zāi)難（如火災(zāi)、地震）和偷竊 什么是備份 數(shù)據(jù)的副本 用于在系統(tǒng)發(fā)生故障后還原和恢復(fù)數(shù)據(jù) 文件復(fù)制備份,41,備份類型,42,43,44,點(diǎn)擊“確定”,45,46,恢復(fù)模式2-1,47,回顧數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí),數(shù)據(jù)庫(kù)文件的組成： 主數(shù)據(jù)文件：*.mdf 次要數(shù)據(jù)文件：*.ndf 日志文件：*.ldf 數(shù)據(jù)庫(kù)的其他屬性： 文件存放位置，分配的初

16、始空間，屬于哪個(gè)文件組 文件增長(zhǎng)：可以按百分比或?qū)嶋H大小指定增長(zhǎng)速度 文件容量設(shè)置：可以指定文件增長(zhǎng)的最大值或不受限,48,數(shù)據(jù)庫(kù)的建立與撤消-SQL語(yǔ)句,建立一個(gè)新數(shù)據(jù)庫(kù) create database 數(shù)據(jù)庫(kù)名 撤消一個(gè)數(shù)據(jù)庫(kù) drop database 數(shù)據(jù)庫(kù)名,舉例:創(chuàng)建sales數(shù)據(jù)庫(kù),Use master Go Create database sales On (name=sales_data, filename=d:saledat.mdf, size=10, maxsize=50, filegrowth=5) Log on (name=sales_log, filename=d:

17、saledat.ldf, size=5MB, maxsize=25MB, filegrowth=5MB) Go,50,創(chuàng)建數(shù)據(jù)庫(kù),CREATE DATABASE 數(shù)據(jù)庫(kù)名 ON PRIMARY ( ，n ) LOG ON ( ，n ),T-SQL創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)法：,主文件組，可選參數(shù)，默認(rèn),51,創(chuàng)建數(shù)據(jù)庫(kù)示例 1,CREATE DATABASE stuDB ON PRIMARY -默認(rèn)就屬于PRIMARY主文件組，可省略 ( NAME=stuDB_data, -主數(shù)據(jù)文件的邏輯名 FILENAME=D:projectstuDB_data.mdf, -主數(shù)據(jù)文件的物理名 SIZE=5mb, -

18、主數(shù)據(jù)文件初始大小 MAXSIZE=100mb, -主數(shù)據(jù)文件增長(zhǎng)的最大值 FILEGROWTH=15% -主數(shù)據(jù)文件的增長(zhǎng)率 ) LOG ON ( NAME=stuDB_log, FILENAME=D:projectstuDB_log.ldf, SIZE=2mb, FILEGROWTH=1MB ) GO,數(shù)據(jù)文件的具體描述,日志文件的具體描述,演示創(chuàng)建數(shù)據(jù)庫(kù)1：一個(gè)數(shù)據(jù)文件和一個(gè)日志文件,52,創(chuàng)建數(shù)據(jù)庫(kù)示例 2,CREATE DATABASE employees ON ( /*-主數(shù)據(jù)文件的具體描述-*/ NAME = employee1, FILENAME = D:projectempl

19、oyee1_Data.mdf , SIZE = 10, FILEGROWTH = 10% ), ( /*-次要數(shù)據(jù)文件的具體描述-*/ NAME = employee2, FILENAME = D:projectemployee2_Data.ndf , SIZE = 20, MAXSIZE = 100, FILEGROWTH = 1 ),LOG ON ( /*-日志文件1的具體描述-*/ NAME = employeelog1, FILENAME = D:projectemployeelog1_Log.ldf , SIZE = 10, FILEGROWTH = 1 ), ( /*-日志文件2的

20、具體描述-*/ NAME = employeelog2, FILENAME = D:projectemployeelog2_Log.ldf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 1 ) GO,演示創(chuàng)建數(shù)據(jù)庫(kù)2：多個(gè)數(shù)據(jù)文件和多個(gè)日志文件,53,確認(rèn)數(shù)據(jù)庫(kù)的當(dāng)前狀態(tài),要確認(rèn)數(shù)據(jù)庫(kù)的當(dāng)前狀態(tài)，通過(guò)“數(shù)據(jù)庫(kù)屬性”窗口的“常規(guī)”選項(xiàng)卡查看數(shù)據(jù)庫(kù)屬性以外，還可以選擇 sys.databases 目錄視圖中的 state_desc 列。在查詢?cè)O(shè)計(jì)器窗口中輸入如下代碼并執(zhí)行，如圖所示。 Select name,state,state_desc From sys.da

21、tabases,54,查看數(shù)據(jù)庫(kù)狀態(tài)信息,可以在查詢?cè)O(shè)計(jì)器窗口中輸入如下代碼并執(zhí)行，即可查看到相關(guān)數(shù)據(jù)文件的狀態(tài)信息。 Select name,physical_name,type,type_desc,state, state_desc From sys.master_file,55,IF EXISTS (select * from sys.databases WHERE name =stuDB) DROP DATABASE stuDB CREATE DATABASE stuDB ON ( . ) LOG ON ( ) GO,刪除數(shù)據(jù)庫(kù),刪除數(shù)據(jù)庫(kù)的語(yǔ)法：,DROP DATABASE 數(shù)據(jù)庫(kù)名

22、,EXISTS（）語(yǔ)句： 檢測(cè)是否存在stuDB數(shù)據(jù)庫(kù) 如果存在stuDB數(shù)據(jù)庫(kù)，則刪除,56,數(shù)據(jù)庫(kù)更名,兩種方法： 一種方法是SQL ServerManagement Studio中選中此數(shù)據(jù)庫(kù)，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“重命名”。 另一種方法是使用系統(tǒng)存儲(chǔ)過(guò)程sp_renamedb更改數(shù)據(jù)庫(kù)的名稱。 系統(tǒng)存儲(chǔ)過(guò)程sp_renamedb語(yǔ)法如下： sp_renamedb dbname=old_name , newname=new_name sp_renamedb sales,saler,例 使用ALTER DATABASE改名 程序代碼如下： ALTER DATABASE sale

23、r MODIFY NAME=salesdb,57,限制用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn),在SQL Server 2005 的運(yùn)行過(guò)程中，有時(shí)需要限制用戶的訪問(wèn)，例如，管理員要維護(hù)數(shù)據(jù)庫(kù)，系統(tǒng)需要升級(jí)等。這時(shí)可以設(shè)置限定只能由特定用戶訪問(wèn)數(shù)據(jù)庫(kù)。,58,Multiple： 數(shù)據(jù)庫(kù)處于正常生產(chǎn)狀態(tài)，允許多個(gè)用戶同時(shí)訪問(wèn)數(shù)據(jù)庫(kù)。 Single：指定一次只能一個(gè)用戶訪問(wèn)。 Restricted：限制除db_ower（數(shù)據(jù)庫(kù)所有者）、 dbcreator （數(shù)據(jù)庫(kù)創(chuàng)建者）和sysadmin（系統(tǒng)管理員）以外的角色成員訪問(wèn)數(shù)據(jù)庫(kù)。,59,修改數(shù)據(jù)庫(kù)的排序規(guī)則,（1）了解排序規(guī)則的含義。以Chinese_PRC_CS_AI_WS為例，該規(guī)則可以