實驗九視圖索引

設計性實驗

1. 創建視圖V_A包括學號，姓名，性別，課程號，課程名、成績；

一個語句把學號103 課程號3-105 的姓名改為陸君茹1，性別為女，然后查看學生表的信息變化，再把上述數據改為原值

-- 創建視圖 V_A_065
CREATE VIEW V_A_065 AS
SELECT s.Sno 學號, s.Sname 姓名, s.Ssex 性別, sc.Cno 課程號, c.Cname 課程名, sc.Degree 成績
FROM student065 s
JOIN Score065 sc ON sc.Sno = s.Sno
JOIN Course065 c ON c.Cno = sc.Cno;-- 修改數據
UPDATE V_A_065
SET 姓名 = '陸君茹1', 性別 = '女'
WHERE 學號 = '103' AND 課程號 = '3-105';-- 還原
UPDATE V_A_065
SET 姓名 = '陸君茹', 性別 = '男'
WHERE 學號 = '103' AND 課程號 = '3-105';
-- 驗證視圖結構和數據
SELECT * FROM V_A_065;

在這里插入圖片描述

一個語句把學號101 課程號4-101 的姓名改為李君帥1，課程名為[馬克思主義基本原理]概論]？如何才能完成修改，再把上述數據改為原值

由于課程名是只讀字段（來自 Course065 表），不能通過視圖直接修改。
只能更新允許更新的字段（如學生姓名）
-- 只修改姓名
UPDATE V_A_065
SET 姓名 = '李君帥1'
WHERE 學號 = '101' AND 課程號 = '4-101';
查看修改后的視圖數據 SELECT * FROM V_A_065 WHERE 學號 = '101' AND 課程號 = '4-101';
查看原始學生表是否更新 SELECT * FROM student065 WHERE Sno = '101';

在這里插入圖片描述

– 添加一行數據 --視圖涉及多張表連接，默認不允許直接插入。需創建 INSTEAD OF INSERT 觸發器或直接插入基礎表

向基礎表中插入
INSERT INTO student065(Sno, Sname, Ssex, Sbirthday, Class)
VALUES ('110', '張小紅', '女', NULL, NULL);INSERT INTO Course065(Cno, Cname, Tno)
VALUES ('C001', '高等數學', 'T001');INSERT INTO Score065(Sno, Cno, Degree)
VALUES ('110', 'C001', 88);
-- 查看新增記錄是否出現在視圖中
SELECT * FROM V_A_065 WHERE 學號 = '110';

2. 創建視圖V_B, 優秀學生（所有成績不低于80），包括姓名，課程名、成績（WITH ENCRYPTION）

檢查WITH ENCRYPTION 效果 
創建加密視圖
CREATE VIEW V_B1_065 WITH ENCRYPTION AS
SELECT s.Sname 姓名, c.Cname 課程名, sc.Degree 成績
FROM student065 s
JOIN Score065 sc ON sc.Sno = s.Sno
JOIN Course065 c ON c.Cno = sc.Cno
WHERE sc.Degree >= 80;
GO
-- 查看系統表中視圖定義
SELECT name, text
FROM sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE name = 'V_B1_065';--加密成功時，text 字段為空或亂碼

在這里插入圖片描述

把王麗娜的計算機導論成績（91）改為80，并查看成績表和V_B中的數據

修改成績
UPDATE Score065
SET Degree = 80
WHERE Sno = '107' AND Cno = '3-105'; 
-- 查看視圖中是否包含該記錄
SELECT * FROM V_B1_065 WHERE 姓名 = '王麗娜' AND 課程名 = '計算機導論';

在這里插入圖片描述

3.創建視圖V_C, 優秀學生（所有成績不低于80），包括姓名，課程名、成績（WITH check option）

檢查WITH check option效果
把王麗娜的計算機導論 成績（91）改為85，并查看成績表和V_C中的數據
--創建帶 CHECK OPTION 的視圖
CREATE VIEW V_C1_065 AS
SELECT s.Sname 姓名, c.Cname 課程名, sc.Degree 成績
FROM student065 s
JOIN Score065 sc ON sc.Sno = s.Sno
JOIN Course065 c ON c.Cno = sc.Cno
WHERE sc.Degree >= 80
WITH CHECK OPTION;
GO
-- 成功修改
UPDATE V_C1_065
SET 成績 = 85
WHERE 姓名 = '王麗娜' AND 課程名 = '計算機導論';
-- 查看最終成績
SELECT * FROM Score065 WHERE Sno = '107' AND Cno = '3-105';----再把王麗娜  的計算機導論 成績改為78，并查看成績表和V_C中的數據    ???
UPDATE V_C1_065
SET 成績 = 78
WHERE 姓名 = '王麗娜' AND 課程名 = '計算機導論';

在這里插入圖片描述

4.對教師表按姓名創建唯一性索引；

創建唯一索引
CREATE UNIQUE INDEX tname_UNI_ind_065 ON teacher065 (Tname);
查看索引信息
EXEC sp_helpindex 'teacher065';

在這里插入圖片描述

5.對課程表按課程名創建聚集索引；？？？

– 創建聚集索引
CREATE CLUSTERED INDEX cname_cl_ind_065 ON Course065 (Cname);
– 查看索引信息
EXEC sp_helpindex ‘Course065’;
在這里插入圖片描述

6.創建索引視圖V_D，包括學號，姓名，性別，課程號，課程名、成績,并按姓名創建索引

創建帶 SCHEMABINDING 的視圖
CREATE VIEW V_D_065 WITH SCHEMABINDING AS
SELECT s.Sno, s.Sname, s.Ssex, sc.Cno, c.Cname, sc.Degree
FROM dbo.student065 s
JOIN dbo.Score065 sc ON sc.Sno = s.Sno
JOIN dbo.Course065 c ON c.Cno = sc.Cno;
GO
-- 創建聚集索引
CREATE UNIQUE CLUSTERED INDEX sname_ind_065 ON V_D_065 (Sname);
-- 查看索引信息
EXEC sp_helpindex 'V_D_065';

7.刪除上述建的全部索引

-- 刪除索引
DROP INDEX IF EXISTS tname_UNI_ind_065 ON teacher065;
DROP INDEX IF EXISTS cname_cl_ind_065 ON Course065;
DROP INDEX IF EXISTS sname_ind_065 ON V_D_065;-- 查看剩余索引
EXEC sp_helpindex 'teacher065';
EXEC sp_helpindex 'Course065';
EXEC sp_helpindex 'V_D_065';

思考題

（1）視圖的作用是什么？
簡化復雜查詢：視圖可以將復雜的多表查詢、連接等操作封裝起來，提供一個簡單的接口給用戶。
提高安全性：通過視圖限制用戶只能訪問特定的數據列或行，保護敏感數據不被未授權用戶查看。
實現邏輯獨立性：當底層數據庫結構發生變化時（如表結構修改），如果應用使用的是視圖而不是直接操作表，只需調整視圖定義即可，不影響前端應用。
支持虛擬表操作：例如聚合、分組等操作可以直接在視圖中定義，簡化了數據分析過程。
（2）什么是索引視圖？如何創建
索引視圖：是指帶有聚集索引的視圖。與普通視圖不同，索引視圖的數據是物理存儲的，這使得它能夠顯著提高查詢性能，特別是對于那些頻繁使用的復雜查詢。
創建方法：
必須使用 WITH SCHEMABINDING 創建視圖，以確保視圖定義不會因為基礎表結構的變化而失效。
（3）舉例說明什么是聚集索引，非聚集索引
聚集索引(Clustered Index)：決定了表中數據的物理存儲順序。每個表只能有一個聚集索引。例如，在學生表中按照學號（Sno）排序，則學號上的索引就是聚集索引。
CREATE CLUSTERED INDEX idx_student_Sno ON student065(Sno);
非聚集索引(Nonclustered Index)：并不改變數據的實際存儲順序，而是創建一個包含指向實際數據頁指針的索引結構。一張表可以有多個非聚集索引。例如，在學生表中按姓名（Sname）建立非聚集索引。
CREATE NONCLUSTERED INDEX idx_student_Sname ON student065(Sname);
（4）簡述使用聚集索引，非聚集索引檢索數據的過程？
聚集索引檢索過程：當你根據聚集索引鍵（如學號）進行查詢時，數據庫可以直接定位到數據所在的位置，因為它反映了數據的實際存儲順序。
非聚集索引檢索過程：首先查找非聚集索引找到對應記錄的指針，然后根據這個指針再去讀取實際的數據頁。這意味著非聚集索引通常需要兩次I/O操作（一次用于讀取索引頁，另一次用于讀取數據頁）。
（5）什么是全文索引？如何創建和使用？（有興趣的同學選做）
全文索引(Full-Text Index)：是一種特殊的索引類型，用于對文本類型字段（如VARCHAR, NVARCHAR, TEXT等）進行高效的搜索。它支持更高級的搜索功能，比如全文搜索、布爾運算符、近似詞匹配等。

實驗小結（實驗中遇到的問題及解決過程、實驗中產生的錯誤及原因分析、實驗體會和收獲）

在這次數據庫實驗中，我學習了視圖和索引的功能及其應用，讓我對數據庫設計有了更深的理解。整個過程充滿了挑戰，但正是這些挑戰促使我在解決問題的過程中不斷進步。
創建視圖時，我最初遇到了一些困惑，例如試圖在一個批處理中同時執行 CREATE VIEW 和 SELECT * FROM view_name 的命令，結果系統提示錯誤信息：“不能在一個批處理中創建視圖并查詢它。”經過查閱資料和反復嘗試，我發現每個DDL操作（如 CREATE, ALTER）必須單獨作為一個批處理來執行，中間需要用 GO 分隔開。
在使用視圖進行數據更新時，我又遭遇了新的問題。當試圖通過多表連接的視圖修改某些字段時，發現有些字段能夠成功更新，而另一些則會失敗。比如，修改學生姓名可以順利完成，但嘗試更改課程名稱卻無法實現。這是因為課程名稱來源于另一張表，SQL Server不允許直接通過視圖修改多個基表的數據。這個問題教會了我區分哪些字段是可更新的，如果確實需要修改，可以通過觸發器（INSTEAD OF UPDATE）間接完成，或者直接操作基礎表。
此外，我嘗試向視圖中插入新記錄，卻收到了“視圖不可更新”的錯誤提示。這表明該視圖涉及多張表的連接，SQL Server默認情況下不允許這樣的操作。解決方法包括創建 INSTEAD OF INSERT 觸發器，或直接操作基礎表進行插入。
在為視圖添加索引的過程中，我也遇到了不少困難，比如提示“不能為未綁定架構的視圖創建索引”、“必須先創建聚集索引”等。原來，索引視圖有著嚴格的限制條件。為了克服這些問題，我學會了使用 WITH SCHEMABINDING 創建視圖，并確保所有引用的表和列都加上了 dbo. 前綴，同時還必須首先創建唯一的聚集索引，保證視圖中沒有聚合函數或復雜的表達式。通過這次實踐，我對索引的本質和用途有了更加直觀的認識，了解到正確的索引設計對于提升查詢效率至關重要。
總的來說，這次實驗不僅讓我掌握了視圖和索引的基本操作，更讓我體會到數據庫系統的強大功能與靈活性。雖然過程中遇到了許多障礙，但正是這些挑戰幫助我在解決問題的同時不斷提升自我。這次寶貴的經驗將成為我未來學習道路上的重要財富，激勵我繼續探索數據庫領域的更多奧秘，也希望自己可以花更多時間學習數據庫。