WEB數(shù)據(jù)庫(kù)原理與應(yīng)用-學(xué)習(xí)情境

1、任務(wù)3 SQL Server 2005數(shù)據(jù)庫(kù)及其應(yīng)用掌握SQL Server 2005、安裝、基本操作，及其建表、數(shù)據(jù)庫(kù)關(guān)系圖、查詢(xún)、視圖、存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器等操作。按照網(wǎng)絡(luò)商店數(shù)據(jù)庫(kù)結(jié)構(gòu)完成相關(guān)練習(xí)操作?；顒?dòng)1 認(rèn)識(shí)SQL Server 2005 SQL Server 2005 旨在幫助企業(yè)迎接這些挑戰(zhàn)。這個(gè)下一代數(shù)據(jù)管理和分析解決方案將給企業(yè)數(shù)據(jù)和分析應(yīng)用程序帶來(lái)增強(qiáng)的安全性、可伸縮性和可用性，使得它們更易于創(chuàng)建、部署和管理。SQL Server 2005 基于 SQL Server 2000 的強(qiáng)大功能之上，提供了一個(gè)完整的數(shù)據(jù)管理和分析解決方案，它將會(huì)給不同規(guī)模的組織帶來(lái)幫助：u

2、構(gòu)建、部署和管理企業(yè)應(yīng)用程序，使其更加安全、伸縮性更強(qiáng)和更可靠。 u 降低開(kāi)發(fā)和支持?jǐn)?shù)據(jù)庫(kù)應(yīng)用程序的復(fù)雜性，實(shí)現(xiàn)了 IT 生產(chǎn)力的最大化。 u 能夠在多個(gè)平臺(tái)、應(yīng)用程序和設(shè)備之間共享數(shù)據(jù)，更易于連接內(nèi)部和外部系統(tǒng)。 u 在不犧牲性能、可用性、可伸縮性或安全性的前提下有效控制成本。SQL Server 2005 在以下三個(gè)重要方面改善了您的數(shù)據(jù)基礎(chǔ)架構(gòu)：企業(yè)數(shù)據(jù)管理、開(kāi)發(fā)人員效率和商業(yè)智能 (BI)。它在可接受的價(jià)格和許可費(fèi)用以及升級(jí)到 SQL Server 2005 和 Microsoft Windows Server System 的途徑方面也有新的突破。活動(dòng)2 SQL Server 200

3、5安裝u SQL Server2005 數(shù)據(jù)庫(kù)安裝的前提條件 操作系統(tǒng)需要是Windows Server 2003 Sp1及其以上版本。 安裝IIS 6.0 以上版本。 安裝.NET Framework 2.0以上框架(SQL Server2005 自帶框架，可以一起安裝)。u 具體安裝步驟 打開(kāi)安裝文件，選擇x86（32位）操作系統(tǒng)進(jìn)行安裝，如圖3-17所示。圖 3-17 SQL Server2005 安裝程序首頁(yè)面1 選擇“安裝服務(wù)器組建、工具、聯(lián)機(jī)叢書(shū)和示例”選項(xiàng)，如圖3-18所示。圖 3-18 SQL Server2005 安裝程序安裝界面2 勾選“接受許可條例后”，選擇下一步，安裝不

4、要的軟件組織，如圖3-19所示。圖 3-19 SQL Server2005 安裝必備組件界面 然后進(jìn)入系統(tǒng)配置檢查，如圖3-20所示：圖 3-20 SQL Server2005 系統(tǒng)配置檢查 選擇注冊(cè)信息后，下一步，選擇需要安裝的組建，這里全部勾選上，然后下一步，如圖3-21所示。圖 3-21 SQL Server2005 安裝組件選擇界面具體的安裝組件說(shuō)明如下：安裝組件說(shuō)明SQL Server Database Services數(shù)據(jù)庫(kù)引擎、復(fù)制、全文檢索Integration Services數(shù)據(jù)轉(zhuǎn)換Analysis Services在線分析和數(shù)據(jù)挖掘Notification Service

5、s應(yīng)用程序發(fā)送通知Reporting Services整理和發(fā)布報(bào)告客戶(hù)端組件、文檔、工具工具和文檔 選擇默認(rèn)實(shí)例，然后下一步，選擇服務(wù)賬號(hào)，這里選擇“使用內(nèi)置系統(tǒng)帳戶(hù)”，其余默認(rèn)，下一步，如圖3-22所示。圖 3-22 SQL Server2005 安服務(wù)賬號(hào) 然后進(jìn)行身份驗(yàn)證選項(xiàng)，選擇“混合模式”，并指定“sa”登錄密碼，這里我們將登錄密碼也設(shè)置為“sa”，如圖3-23所示。圖 3-23 SQL Server2005 身份驗(yàn)證模式選擇 然后依次下一步，最后進(jìn)入安裝界面，如圖3-24所示。圖 3-24 SQL Server2005 安裝組件界面 最后完成安裝。活動(dòng)3 SQL Server 2

6、005應(yīng)用 創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)表根據(jù)學(xué)習(xí)情境2，任務(wù)6 數(shù)據(jù)庫(kù)的設(shè)計(jì)舉例里面的活動(dòng)2 E-R圖的設(shè)計(jì)，完成數(shù)據(jù)表的創(chuàng)建工作。數(shù)據(jù)庫(kù)名稱(chēng)：Shopping表名： 客戶(hù)信息表(bClient)字段名稱(chēng)英文名稱(chēng)類(lèi)型長(zhǎng)度客戶(hù)編號(hào)(GUID)User_ID字符串類(lèi)型50客戶(hù)姓名User_Name字符串類(lèi)型30性別User_Sex字符串類(lèi)型4出生日期User_Birthday日期類(lèi)型VIP客戶(hù)類(lèi)型User_Type字符串類(lèi)型50位置User_Address字符串類(lèi)型100了解電話User_Tel字符串類(lèi)型20表名： 客戶(hù)訂單表(bOrder)字段名稱(chēng)英文名稱(chēng)類(lèi)型長(zhǎng)度訂單編號(hào)Order_ID字符串類(lèi)型50下單

7、日期Order_Date日期類(lèi)型客戶(hù)編號(hào)User_ID字符串類(lèi)型50商品編號(hào)Goods_ID字符串類(lèi)型50訂單金額Order_Money雙精度數(shù)值12,2表名： 商品信息表(bGoods)字段名稱(chēng)英文名稱(chēng)類(lèi)型長(zhǎng)度商品編號(hào)Goods_ID字符串類(lèi)型50商品名稱(chēng)Goods_Name字符串類(lèi)型50價(jià)格Goods_Price雙精度數(shù)值12,2u 創(chuàng)建打開(kāi)SQL Server2005數(shù)據(jù)庫(kù)，如圖3-25所示：圖 3-25 SQL Server2005 打開(kāi)菜單u 新建數(shù)據(jù)庫(kù)首頁(yè)面，如下圖3-26所示 圖 3-26 SQL Server2005 登錄界面u 用戶(hù)名密碼輸入成功以后進(jìn)入數(shù)據(jù)庫(kù)管理系統(tǒng)首頁(yè)面，

8、點(diǎn)擊右鍵，選擇新建數(shù)據(jù)庫(kù)，如圖3-27所示。圖 3-27 SQL Server2005 主界面u 創(chuàng)建數(shù)據(jù)庫(kù)Shopping，修改數(shù)據(jù)庫(kù)文件路徑、所有者，如圖3-28所示。圖 3-28 SQL Server2005 新建數(shù)據(jù)庫(kù)u 數(shù)據(jù)庫(kù)Shopping新建完成以后，在D:Shopping 目錄下出現(xiàn)2個(gè)文件，分別是數(shù)據(jù)文件和日志文件，如圖3-29所示。圖 3-29 SQL Server2005 數(shù)據(jù)文件位置u 數(shù)據(jù)庫(kù)創(chuàng)建完成，在Shopping數(shù)據(jù)庫(kù)下面新建數(shù)據(jù)表，以后如下圖3-30顯示。圖 3-30 SQL Server2005 創(chuàng)建數(shù)據(jù)庫(kù)u 按照前面的數(shù)據(jù)表結(jié)構(gòu)所示，首先創(chuàng)建客戶(hù)信息表(b

9、Client)，創(chuàng)建如下圖3-31所示。圖 3-31 SQL Server2005 創(chuàng)建表u 數(shù)據(jù)表保存名稱(chēng)為：bClient ?？蛻?hù)訂單表(bOrder)、商品信息表(bGoods)創(chuàng)建過(guò)程與客戶(hù)信息表相同，這里不再詳細(xì)敘述。u 客戶(hù)信息表(bClient) 、客戶(hù)訂單表(bOrder)、商品信息表(bGoods) SQL腳本如下：-客戶(hù)信息表(bClient)CREATE TABLE bClient(User_ID nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT DF_bClient_User_ID DEFAULT (n

10、ewid(),User_Name nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,User_Sex nvarchar(4) COLLATE Chinese_PRC_CI_AS NULL,User_Birthday datetime NULL,User_Type nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,User_Address nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,User_Tel nvarchar(20) COLLATE Chinese_PRC_CI_AS

11、 NULL, CONSTRAINT PK_bClient PRIMARY KEY CLUSTERED (User_ID ASC) ON PRIMARY-客戶(hù)訂單表(bOrder)CREATE TABLE bOrder(Order_ID nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT DF_bOrder_Order_ID DEFAULT (newid(),Order_Date datetime NULL CONSTRAINT DF_bOrder_Order_Date DEFAULT (getdate(),User_ID nva

12、rchar(50) COLLATE Chinese_PRC_CI_AS NULL,Goods_ID nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,Order_Money decimal(12, 2) NULL, CONSTRAINT PK_bOrder PRIMARY KEY CLUSTERED (Order_ID ASC) ON PRIMARY-商品信息表(bGoods)CREATE TABLE dbo.bGoods(Goods_ID nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT

13、 DF_bGoods_Goods_ID DEFAULT (newid(),Goods_Name nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,Goods_Price decimal(12, 2) NULL, CONSTRAINT PK_bGoods PRIMARY KEY CLUSTERED (Goods_ID ASC) ON PRIMARYu 創(chuàng)建數(shù)據(jù)庫(kù)關(guān)系圖SQL SERVERSSMS圖形界面中有一種很實(shí)用的工具。它可以將表和表間關(guān)系以及其他對(duì)象以圖形方式表現(xiàn)出來(lái)，并且我們也可以通過(guò)它以圖形的方式來(lái)增加、修改表和表間關(guān)系等數(shù)據(jù)庫(kù)對(duì)象。選擇數(shù)據(jù)庫(kù)關(guān)系

14、圖->選擇新建，可以在數(shù)據(jù)庫(kù)關(guān)系圖里面設(shè)置客戶(hù)信息表(bClient)、客戶(hù)訂單表(bOrder)、商品信息表(bGoods) 3者之間的關(guān)系.如圖3-32所示。圖 3-32 SQL Server2005 創(chuàng)建數(shù)據(jù)庫(kù)關(guān)系圖 創(chuàng)建視圖和查詢(xún)u 視圖是一個(gè)命名的虛擬表（virtual table），它由一個(gè)查詢(xún)來(lái)定義，可以當(dāng)作表使用。與持久表（permanent table）不同的是，視圖中的數(shù)據(jù)沒(méi)有物理表現(xiàn)形式。例如：創(chuàng)建用戶(hù)訂單視圖訂單視圖結(jié)構(gòu)，需要顯示 訂單號(hào)碼、下單日期、用戶(hù)編號(hào)、用戶(hù)姓名、用戶(hù)電話、用戶(hù)位置、產(chǎn)品名稱(chēng)、訂單金額視圖創(chuàng)建過(guò)程如圖3-33所示：圖 3-33 SQL Se

15、rver2005 創(chuàng)建視圖創(chuàng)建視圖腳本如下：CREATE VIEW 用戶(hù)訂單ASSELECT bOrder.Order_ID AS 訂單號(hào)碼, bOrder.Order_Date AS 下單日期, bOrder.User_ID AS 用戶(hù)編號(hào), bClient.User_Name AS 用戶(hù)姓名, bClient.User_Tel AS 用戶(hù)電話, bClient.User_Address AS 用戶(hù)位置, bGoods.Goods_Name AS 商品名稱(chēng), bOrder.Order_Money AS 訂單金額FROM bClient RIGHT OUTER JOIN bOrder ON b

16、Client.User_ID = bOrder.User_ID LEFT OUTER JOIN bGoods ON bOrder.Goods_ID = bGoods.Goods_IDGO 數(shù)據(jù)表基本操作u 用戶(hù)表插入新用戶(hù)Insert into bClient(User_ID,User_Name,User_Sex,User_Birthday,User_Type,User_Address,User_Tel)values ('2009010220','張三','男',1980-01-01,'VIP1','蘇州國(guó)際教育園北區(qū)學(xué)府

17、路號(hào)','#39;)u 商品表增加商品Insert into bGoods(Goods_ID,Goods_Name,Goods_Price)values ('001','聯(lián)想筆記本',2800)Insert into bGoods(Goods_ID,Goods_Name,Goods_Price)values ('002','蘋(píng)果MP3',120)u 用戶(hù)訂單表增加用戶(hù)新的訂單Insert into bOrder(Order_ID,Order_Date,User_ID,Goods_ID,Ord

18、er_Money)values ('DD001',2010-04-29,'2009010220','001',160)u 修改用戶(hù)訂單表里面訂單編號(hào)為”DD001”的商品的價(jià)格為180元update bOrderset Order_Money=180where Order_ID='DD001'u 刪除用戶(hù)訂單表里面訂單編號(hào)為”DD001”的訂單信息delete from bOrder where Order_ID='DD001'u 查詢(xún)視圖” 用戶(hù)訂單”，里面訂單號(hào)碼為”DD001”的訂單Select * from

19、 用戶(hù)訂單where 訂單號(hào)碼='DD001'查詢(xún)結(jié)果如下圖所示：圖 3-34 SQL Server2005 查詢(xún)結(jié)果 創(chuàng)建存儲(chǔ)過(guò)程將常用的或很復(fù)雜的工作，預(yù)先用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定的名稱(chēng)存儲(chǔ)起來(lái), 那么以后要叫數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí),只需調(diào)用execute,即可自動(dòng)完成命令。u 修改用戶(hù)編號(hào)為”2009010220”的姓名為”李四”，同時(shí)把該用戶(hù)的所有訂單在現(xiàn)在的訂單費(fèi)用的基礎(chǔ)上減少10%，并顯示訂單視圖中用戶(hù)編號(hào)是” 2009010220”的信息。CREATE procedure update_Fun-用戶(hù)編號(hào)User_ID nvarchar

20、(50),UserName nvarchar(50)asbeginbegin transactionupdate bClient set User_Name=UserName where User_ID=User_IDupdate bOrder set Order_Money=Order_Money*0.9 where User_ID=User_IDSelect 用戶(hù)編號(hào),用戶(hù)姓名,sum(cast(訂單金額as decimal) as 訂單總金額from 用戶(hù)訂單where 用戶(hù)姓名=UserNamegroup by 用戶(hù)編號(hào),用戶(hù)姓名IF ERROR <> 0 ROLLBACK

21、 transaction ELSECOMMIT transactionend執(zhí)行存儲(chǔ)過(guò)程如下：EXEC update_Fun '2009010220','李四' 創(chuàng)建觸發(fā)器觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它在插入，刪除或修改特定表中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行，它比數(shù)據(jù)庫(kù)本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。u 刪除用戶(hù)表，用戶(hù)表用戶(hù)被刪除的時(shí)候，同時(shí)刪除所有該用戶(hù)的所有訂單CREATE trigger trdbClient_Delete On bClient for Delete-Name:trdbOrder_Delete-func:同時(shí)刪除BorrowRecord 的數(shù)據(jù)-Use :None-User:jack.jia-Author: -Date : 2010-04-24-Memo : 用戶(hù)表用戶(hù)被刪