數據庫系統(tǒng)原理與應用課件(2)

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

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í)行的所有數據修改都必須符合select_statement設置的準則。view_attribute:ENCRYPTION，防止將視圖作為SQL Server復

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

4、e2.通過視圖向基表中插入數據 一般只針對簡單的視圖。要符合下面條件：需要有插入數據的權限對于視圖中未引用的字段，數據表應該允許為空或有默認值視圖不能包含多個字段值的組合以及統(tǒng)計函數視圖不能包含DISTINCT或GROUP BY不能在一個語句中對多個基表使用數據修改語句12.2 使用視圖書上例12.512.2 使用視圖3.通過視圖修改基表中的數據12.2 使用視圖4.通過視圖刪除基表中的數據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為視圖的當前名稱new_name為視圖的新名稱object_type為重命名對象的類型，默認值為NULL1

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

7、些列或列集的值唯一地標識表中的行。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約束 可以約束表之間的關系，為表的一列或多列數據提供參照完整性。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約束 可以列集內強制執(zhí)行值的唯一性。如果列有UNIQUE約束，SQL Serevr將確保數據除NULL外的所有值均唯一。CREATE TABLE table

9、5 ( c1 int UNIQUE, c2 int)13.1 約束13.1 約束4.CHECK約束 通過限制用戶輸入的值來加強域完整性。指定應用于列中輸入的所有值的布爾條件，拒絕所有不為TRUE的值。CREATE TABLE table6(f1 int , f2 int NOT NULL CHECK(f2=0 and f2=100)限定f2只能為0到10013.1 約束13.1 約束5.列約束和表約束列約束被指定為列定義的一部分，且僅適用于那個列表約束的聲明與列的定義無關，適用于表中一個以上的列當一個約束中必須包含一個以上的列時，必須使用表約束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默認為當前日期也可以通過ALTER TABLE來給列加上默認值：ALTER TABLE table8 ADD CONSTRAINT con1 DEFAULT getdate() FOR C313.2 默認值2.使用默認對象 默認對象是單獨存儲的，刪除表時DEFAULT約束會自動刪除，但默認對象不會刪除。13.3 規(guī)則

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

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

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

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

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