數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用課件(2)

1、數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用 第八講 上節(jié)課的回顧E-R模型的設(shè)計及向關(guān)系模型的轉(zhuǎn)換E-R模型中的三個組織成分 ：實體、屬性、聯(lián)系實體之間的三種聯(lián)系把E-R圖轉(zhuǎn)換成關(guān)系模式第十二章 視圖12.1 創(chuàng)建和刪除視圖12.2 使用視圖12.3 視圖定義的修改和信息查看12.1 創(chuàng)建視圖和刪除視圖 視圖在數(shù)據(jù)庫中是作為一個獨立的對象進行存儲的。視圖和查詢很相似，但也有區(qū)別：存儲方式：視圖存儲為數(shù)據(jù)庫設(shè)計的一部分，而查詢則不是更新結(jié)果：對視圖和查詢的結(jié)果集更新限制是不同的排序結(jié)果：可以排序任何查詢結(jié)果，而視圖只有包括TOP子句時才能排序參數(shù)設(shè)置：可以為查詢創(chuàng)建參數(shù)，但不能為視圖創(chuàng)建參數(shù)加密：可以加密視圖，但不能

2、加密查詢。12.1 創(chuàng)建視圖和刪除視圖一、創(chuàng)建視圖SQL Server2005提供了2種方法來創(chuàng)建視圖：使用SQL Server控制管理器使用CREATE VIEW語句12.1 創(chuàng)建視圖和刪除視圖CREATE VIEW view_name ( column , n ) WITH view_attribute ,n AS select_statement WITH CHECK OPTION其中WITH CHECK OPTION是強制視圖上執(zhí)行的所有數(shù)據(jù)修改都必須符合select_statement設(shè)置的準則。view_attribute:ENCRYPTION，防止將視圖作為SQL Server復(fù)

3、制的一部分發(fā)布；SCHEMABINDING，把視圖綁定到架構(gòu)上；VIEW_METADATA，指定為引用視圖的查詢請求瀏覽模式的元數(shù)據(jù)時，返回有關(guān)視圖的元數(shù)據(jù)信息，而不是基表或表。12.1 創(chuàng)建視圖和刪除視圖書上例12.2創(chuàng)建名為st1_degree的視圖，包括所有學(xué)生的姓名、課程和成績。12.1 創(chuàng)建視圖和刪除視圖二、刪除視圖SQL Server2005提供了2種方法來刪除視圖：使用SQL Server控制管理器使用DROP VIEW語句DROP VIEW view_name ,n可以一次刪除一個或多個視圖12.2 使用視圖1.使用視圖進行數(shù)據(jù)查詢SELECT * FROM st1_degre

4、e2.通過視圖向基表中插入數(shù)據(jù) 一般只針對簡單的視圖。要符合下面條件：需要有插入數(shù)據(jù)的權(quán)限對于視圖中未引用的字段，數(shù)據(jù)表應(yīng)該允許為空或有默認值視圖不能包含多個字段值的組合以及統(tǒng)計函數(shù)視圖不能包含DISTINCT或GROUP BY不能在一個語句中對多個基表使用數(shù)據(jù)修改語句12.2 使用視圖書上例12.512.2 使用視圖3.通過視圖修改基表中的數(shù)據(jù)12.2 使用視圖4.通過視圖刪除基表中的數(shù)據(jù)12.3 視圖定義的修改和信息查看一、視圖定義的修改SQL Server2005提供了2種方法來修改視圖：使用SQL Server控制管理器使用ALTER VIEW語句ALTER VIEW view_nam

5、e ( column , n ) WITH view_attribute ,n AS select_statement WITH CHECK OPTION12.3 視圖定義的修改和信息查看二、視圖重命名SQL Server2005提供了2種方法來重命名視圖：使用SQL Server控制管理器使用存儲過程sp_renamesp_rename objname= object_name, newname= new_name , objtype= object_type其中：object_name為視圖的當(dāng)前名稱new_name為視圖的新名稱object_type為重命名對象的類型，默認值為NULL1

6、2.3 視圖定義的修改和信息查看三、查看視圖的信息SQL Server2005提供了2種方法來查看視圖信息：使用SQL Server控制管理器使用存儲過程sp_helptextsp_helptext objname= object_name,第十三章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性是確保數(shù)據(jù)庫中的數(shù)據(jù)的一致性和正確性13.1 約束13.2 默認值13.3 規(guī)則13.1 約束SQL Server2005提供了多種強制數(shù)據(jù)完整性的機制PRIMARY KEY約束FOREIGN KEY約束UNIQUE約束CHECK約束NOT NULL約束13.1 約束1.PRIMARY KEY約束 可以約束標(biāo)識列或列集，這

7、些列或列集的值唯一地標(biāo)識表中的行。CREATE TABLE department /*部門表*/ ( dno int PRIMARY KEY, /*部門號為主鍵*/ dname char (20), /*部門名*/ )13.1 約束如在創(chuàng)建表時指定了主鍵，SQL Server會自動創(chuàng)建一個“PK_”且后跟表名的主鍵索引，這個索引默認是聚集索引，同時也是一個唯一索引。13.1 約束13.1 約束2.FOREIGN KEY約束 可以約束表之間的關(guān)系，為表的一列或多列數(shù)據(jù)提供參照完整性。CREATE TABLE worker /*職工表*/ ( no int PRIMARY KEY, /*編號為主鍵

8、*/ name char (8), /*姓名*/ sex char (2), /*性別*/ dno int /*部門號*/ FOREIGN KEY REFERENCES department (dno) ON DELETE NO ACTION, address char (30), /*地址*/ )ON ACTION:指定刪除因錯誤而失敗。另一個選項CASCADE:指定刪除已刪除行的外鍵的所有行。13.1 約束13.1 約束3.UNIQUE約束 可以列集內(nèi)強制執(zhí)行值的唯一性。如果列有UNIQUE約束，SQL Serevr將確保數(shù)據(jù)除NULL外的所有值均唯一。CREATE TABLE table

9、5 ( c1 int UNIQUE, c2 int)13.1 約束13.1 約束4.CHECK約束 通過限制用戶輸入的值來加強域完整性。指定應(yīng)用于列中輸入的所有值的布爾條件，拒絕所有不為TRUE的值。CREATE TABLE table6(f1 int , f2 int NOT NULL CHECK(f2=0 and f2=100)限定f2只能為0到10013.1 約束13.1 約束5.列約束和表約束列約束被指定為列定義的一部分，且僅適用于那個列表約束的聲明與列的定義無關(guān)，適用于表中一個以上的列當(dāng)一個約束中必須包含一個以上的列時，必須使用表約束13.2 默認值 SQL Server2005提供

10、了兩種使用默認值的方法創(chuàng)建表時指定默認值使用CREATE DEFAULT創(chuàng)建默認對象，然后使用存儲過程sp_bindefault來綁定到指定列上13.2 默認值1.創(chuàng)建表時指定默認列 1)使用SQL Server管理控制器性別默認為男13.2 默認值2)使用T-SQL語言默認為10默認為當(dāng)前日期也可以通過ALTER TABLE來給列加上默認值：ALTER TABLE table8 ADD CONSTRAINT con1 DEFAULT getdate() FOR C313.2 默認值2.使用默認對象 默認對象是單獨存儲的，刪除表時DEFAULT約束會自動刪除，但默認對象不會刪除。13.3 規(guī)則

11、 規(guī)則限制了可以存儲在表中或者用戶定義數(shù)據(jù)類型的值。 規(guī)則與CHECK約束功能基本相同，但一個列只能應(yīng)用一個規(guī)則，卻可以應(yīng)用多個CHECK約束。CHECK可作為CREATE TABLE語句的一部分進行指定，而規(guī)則以單獨的對象創(chuàng)建，然后綁定到列上。13.3 規(guī)則第十四章 存儲過程14.1 概述14.2 創(chuàng)建存儲過程14.3 執(zhí)行存儲過程14.4 存儲過程的參數(shù)14.5 存儲過程的管理14.1 概述 存儲過程是在數(shù)據(jù)庫服務(wù)器端執(zhí)行的一組T-SQL語句的集合，經(jīng)編譯后存放在數(shù)據(jù)庫服務(wù)器端。SQL Server 2005提供3種存儲過程：用戶存儲過程：用戶編寫的可重復(fù)用的T-SQL功能模塊系統(tǒng)存儲過程

12、：由系統(tǒng)提供，常用sp_為前綴擴展存儲過程：用戶使用編程語言創(chuàng)建的外部例程14.2 創(chuàng)建存儲過程1.使用SQL Server管理控制器創(chuàng)建14.2 創(chuàng)建存儲過程2.使用CREATE PROCEDURE創(chuàng)建CREATE PROCEDURE 存儲過程名 ;numberparameter data_typeVARYING =default OUTPUT,nWITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION FOR REPLICATIONAS sql_statement n 14.2 創(chuàng)建存儲過程例：創(chuàng)建存儲過程stud_degree用于檢索所有學(xué)生的

13、成績記錄14.3 執(zhí)行存儲過程EXECUTE return_status = 存儲過程名 ;number| procedure_name_varparameter=value | variable OUTPUT | DEFAULT ,nWITH RECOMPILE 其中OUTPUT指定存儲過程必須返回一個參數(shù)DEFAULT根據(jù)過程的定義提供參數(shù)的默認值WITH RECOMPILE強制編譯新的計劃14.3 執(zhí)行存儲過程例：執(zhí)行存儲過程stud_degree14.4 存儲過程的參數(shù)1.在存儲過程中使用參數(shù)例：設(shè)計存儲過程maxno，以學(xué)號為參數(shù)，輸出指定學(xué)號學(xué)生的所有課程中最高分和對應(yīng)的課程名。1

14、4.4 存儲過程的參數(shù)執(zhí)行存儲過程方式1：EXEC maxno 105方式2：EXEC maxno no=10514.4 存儲過程的參數(shù)2.在存儲過程中使用默認參數(shù)例：設(shè)計存儲過程maxno1，以學(xué)號為參數(shù)，學(xué)號默認為101，輸出指定學(xué)號學(xué)生的所有課程中最高分和對應(yīng)的課程名。14.4 存儲過程的參數(shù)3.在存儲過程中使用返回參數(shù)例：設(shè)計存儲過程average，返回指定學(xué)號的代表姓名和平均分的2個參數(shù)st_name、st_avg表示返回參數(shù)14.4 存儲過程的參數(shù)例：設(shè)計存儲過程stud1_degree，能根據(jù)學(xué)號和課程號來返回成績等級。14.4 存儲過程的參數(shù)4.存儲過程的返回值存儲過程在執(zhí)行后

15、會返回一個整型值，如為0則表示執(zhí)行成功，如為負則表示出錯。例：EXEC i = stud1_degree 101, 3-105, dj OUTPUT14.5 存儲過程的管理1.查看存儲過程使用SQL Server管理控制器使用系統(tǒng)存儲過程sp_help name 顯示存儲過程的參數(shù)及數(shù)據(jù)類型sp_helptext name 顯示存儲過程的源代碼sp_depends name 顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象sp_stored_procedures 返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表14.5 存儲過程的管理2.修改存儲過程 ALTER PROCEDURE 存儲過程名參數(shù)列表 AS sql_statem

16、ent3.重命名存儲過程 sp_rename 原存儲過程名,新存儲過程名4.刪除存儲過程 DROP PROCEDURE 存儲過程列表自學(xué)要求與上課安排自學(xué)與上機內(nèi)容： 自學(xué)第15章 上機內(nèi)容：完成上機實習(xí)：做完上機練習(xí)7的第七部分。做的快的同學(xué)可做上機練習(xí)7的第八部分。上機練習(xí)514. 編寫一個程序交叉表查詢的程序，生成下圖的成績大表查詢。方法1:利用SLELECT中PIVOT用于將列值旋轉(zhuǎn)為列名（即行轉(zhuǎn)列）語法：table_sourcePIVOT(聚合函數(shù)（value_column）FOR pivot_columnIN()結(jié)果方法2:利用游標(biāo)和臨時表來完成.1.按學(xué)生表中的學(xué)號和姓名生成一個臨時表2.按選課表中的課程號,分別對臨時表增加字段3.按選課表中的每一條記錄,分別修改臨時表特定學(xué)生的特定課程成績相關(guān)SQL命令:機號、學(xué)號、姓名。 你上機的完成情況，各部分做出幾小題，沒完