實驗目的
- 熟練掌握使用圖形用戶界面和SQL語言創建、操作和洗掉視圖，
- 熟練掌握使用圖形用戶界面和SQL語言創建和洗掉索引，
- 理解與驗證索引的作用，
實驗內容
- 在Student資料庫中，用SQL陳述句創建一個選修了“資料庫”課程并且是2001年出生的學生的視圖，視圖中包括學號，姓名，性別，成績，（5分)
CREATE VIEW STUDENT_2001(SNO,SNAME,SGENDER,SBIRTH)
AS
SELECT S.SNO,SNAME,SGENDER,GRADE FROM S,SC,C
WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND CNAME = '資料庫' AND SBIRTH LIKE '2001%';
- 在SPJ_MNG資料庫中，用兩種不同的SQL陳述句為工程專案名是“三建”的工程專案建立一個供應情況視圖，包括供應商代碼（SNO），零件代碼（PNO），供應數量（QTY）創建以下視圖（視圖名：V_SPJ），（10分)
方法一
CREATE VIEW V_SPJ
AS
SELECT SNO,PNO,QTY FROM SPJ
WHERE JNO IN
(
SELECT JNO FROM J
WHERE JNAME = '三建'
);
方法二
CREATE VIEW V_SPJ
AS
SELECT SNO,PNO,QTY FROM SPJ,J
WHERE SPJ.JNO = J.JNO AND JNAME = '三建';
- 用SQL陳述句針對第2小題的V_SPJ完成下面的視圖查詢，（10分)
(1) 找出“三建”工程專案使用的各種零件代碼及其數量，
SELECT PNO,SUM(QTY)
FROM V_SPJ
GROUP BY PNO;
(2) 找出供應商S1的供應情況，
SELECT * FROM V_SPJ
WHERE SNO = 'S1';
- 用SQL陳述句針對第2小題的V_SPJ完成視圖的資料更新，(15分)
(1) 給視圖V_SPJ中增加一條資料，
根據提示資訊或無法顯示或需要觸發器，因此不需要實作
(2) 修改視圖V_SPJ中的任意一條資料的供應數量，
UPDATE V_SPJ
SET QTY = 400
WHERE SNO = 'S3' AND PNO = 'P1';
(3) 洗掉視圖V_SPJ中的任意一條資料，
DELETE
FROM V_SPJ
WHERE SNO = 'S3' AND PNO = 'P1';
- 用圖形用戶界面對Student資料庫中C表的Cno欄位創建一個降序排列的索引，索引名稱IX_CNo，(5分)
索引創建位置如圖所示
- 使用SQL陳述句對Student資料庫完成以下的索引操作，（15分）
(1) 在C表的CName屬性上創建一個非唯一性的普通索引，索引名IX_CName，
CREATE INDEX IX_CName
ON C(CNAME);
(2) 在S表上創建一個名為IX_ngd的復合索引，該索引是針對sname, sgender, sdept屬性集建立的升序索引，
CREATE INDEX IX_ngd
ON S(SNAME ASC,SGENDER ASC,SDEPT ASC);
(3) 洗掉C表的索引IX_CNo，
DROP INDEX IX_CNo ON C;
(4) 基于以上的索引（c表：cno的主鍵索引，cname的普通索引；S表：sno的主鍵索引，IX_nga復合索引），用explain得到的查詢計劃觀察每個查詢陳述句中索引的使用情況，
DROP INDEX IX_CNo ON C;
① explain select * from c;
未使用索引
② explain select * from c where cno = ‘1’;
使用主鍵索引
③ explain select * from c where cname=’資料庫’ ;
使用主鍵索引
④ explain select * from c where cname like ‘%資料庫%’;
未使用索引
⑤ explain select * from c where cname like ‘資料庫%’;
未使用索引
⑥ explain select * from s where sname =‘張立’ and sno=‘2001’;
使用復合索引
⑦ explain select * from s where sname =‘張立’ and sgender=‘男’ and sdept=‘IS’;
使用復合索引
⑧ explain select * from s where sname =‘張立’ and sgender=‘男’;
使用復合索引
⑨ explain select * from s where sname =‘張立’;
使用復合索引
⑩ explain select * from s where sgender =‘男’;
未使用索引
? explain select * from s where sgender =‘男’ and sdept=‘IS’;
未使用索引
- 假設有一個如下的基本表userinfo，自己設計一個實驗驗證索引對資料庫查詢效率的提升作用，（40分）
create table userinfo
(
user_id int primary key, //用戶ID
username varchar(10), //用戶名
gender char(1), //性別
age int, //年齡
c_id int //學院編號
)
(1) 驗證有索引和無索引的查詢效率差異，
在age上創建一個索引，并按照如下陳述句進行查詢
EXPLAIN SELECT *
FROM userinfo
WHERE age = 25;
(2) 驗證單欄位窄索引和多欄位構成的寬索引的查詢效率異，注意理解寬索引中的最左匹配原則，
首先在age上創建一個窄索引
然后查詢
EXPLAIN SELECT username,age,gender,c_id
FROM userinfo
WHERE gender = 'm' AND age = 25 AND c_id = 1;
然后在age,gender,c_id創建一個寬索引
然后同上查詢即可知道查詢效率的差異，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/319663.html
標籤：其他