【基礎4】SQL基礎索引與視圖的建立

1、4SQL基礎索引與視圖的建1. SQL Server中的數(shù)據(jù)也是按頁（頁是4kb）存放。2. 索引：是SQL Sever 編排數(shù)據(jù)的內部法。它為SQL提供種法來編排查詢數(shù)據(jù)3. 索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁；索引頁類似于漢語字典中按拼或筆畫排序成的錄頁4. 索引的作：通過使索引，可以提數(shù)據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能唯索引：不允許兩列具有相同的索引值，創(chuàng)建唯約束時動創(chuàng)建唯索引聚集索引(Clustered)：表中各的物理順序與鍵值的邏輯(索引)順序相同，每個表只能有個。聚集索引(Non-clustered)：聚集索引指定的邏輯順序。數(shù)據(jù)存儲在個位置，索引存儲在另個位置，索引中包含指向數(shù)據(jù)存儲

2、位置的指針，每個表可以有多個聚集索引，最多：249個單列索引：對表中單個字段建的索引復合索引：對表中兩個或兩個以上字段建的索引，最多可以組合16個字段，但所有字段必須在同個表中。注意：通過主鍵約束和唯約束建的聚集索引和唯索引是不能刪除的，該索引于約束的強制執(zhí)。唯 聚集 聚集索引名表名 列名（如果是多列，列明中間,分隔）-fillfactor ：填充因 ，指定個0100之間的值，表索引頁填充的百分-使是否存在查詢判斷是否有存在索引：ix_姓名-設置執(zhí)計劃，可以看到先使哪個索引set showplan_all on -顯所有的執(zhí)計劃set showplan_all off -關閉，只顯查詢結果-不

3、指定 select * from Employees where 姓名=李圓圓-指定索引的 索引跟在表的后，如果是多表，則看索引建在哪個表上，索引就跟在這個表后select *from Employees with(indexix_) where =exec sp_helpindex Employees-ix_姓名-查看這個表中所有的索引 列名后有(-)的是降序，沒有的是升序姓名(-)nonclustered, unique located on PRIMARY-pk_編號 clustered, unique, primary key located on PRIMARY編號-索引重命名 注意

4、原索引名前要加上表名.exec sp_rename Employees.ix_,ix_namedrop index Employees.ix_name-1、查看索引碎DBCCshowcontig(Employees,ix_name)-需要到表名和索引名，中間,分隔-掃描密度越，碎信息越少-2、整理索引碎dbcc indexdefrag(Sales,Employees,ix_name) -需要到庫名、表名和索引名，中間,分隔-3、重建索引dbcc dbreindex(Employees,ix_name,60) -填充因改為60%-作：提查詢速度-創(chuàng)建索引原則（什么樣的列適合建索引）：-(1)頻繁

5、查詢的列-(2)主鍵和外鍵列-6、使命令Update statistics 數(shù)據(jù)表名 更新數(shù)據(jù)表Course的索引信息。update statistics Course-2、查詢“徐鳳”教師所任課程的選課數(shù)。（查看執(zhí)計劃，了解索引的優(yōu)化）-3使命令DBCC Showcontig（表名，索引名）掃描索引的碎信息。dbcc showcontig(Course,IX_Teacher)-4、使命令DBCC indexdefrag（數(shù)據(jù)庫名，數(shù)據(jù)表名，索引名）整理索引碎。dbcc indexdefrag(,Course,IX_Teacher)-5、使命令DBCC dbreindex（數(shù)據(jù)表名，索引名，新

6、的填充因）重建索引-將索引IX_Teacher的填充因修改為60。dbcc dbreindex(Course ,IX_Teacher,60)-6、使命令Update statistics 數(shù)據(jù)表名 更新數(shù)據(jù)表Course的索引信息。update statistics Course-7、查看Course表的索引。exec sp_helpindex Course-8、重命名Course表的索引“IX_Teacher”“IX_教師”。exec sp_rename Course.IX_Teacher,IX_-9、刪除索引Course表的”IX_教師”索引drop index Course.IX_教師視

7、圖是張?zhí)摂M表，他表張表的部分數(shù)據(jù)或多張表的綜合數(shù)據(jù)，其結構和數(shù)據(jù)都是建在對表的查詢的基礎上的數(shù)據(jù)并不存放在視圖中，是存放在視圖所引的原始表(基表)中，視圖是基表數(shù)據(jù)的個映射同張原始表，根據(jù)戶的不同需求，可以創(chuàng)建不同的視圖基表數(shù)據(jù)發(fā)變化，視圖中的數(shù)據(jù)也發(fā)變化，通過視圖可以對及表中的數(shù)據(jù)進增、刪、查、改定制數(shù)據(jù)，通過視圖，使不同的戶以不同的式看到不同/相同的數(shù)據(jù)保護數(shù)據(jù)，防未經許可的戶訪問敏感數(shù)據(jù)簡化操作，視圖中保存的是復雜的查詢語句，通過調查詢視圖語句，降低數(shù)據(jù)庫訪問的復雜程度合并與分割數(shù)據(jù)，將多個物理數(shù)據(jù)表抽象為個邏輯數(shù)據(jù)表，或將個的數(shù)據(jù)表進分隔成多張邏輯數(shù)據(jù)表，不進數(shù)據(jù)庫表結構的修改。視圖名

8、 對查詢語句的加密 條查詢語句 檢查對數(shù)據(jù)的修改增加刪除是否滿select中的條件，滿-可以修改 不滿-法修改if exists(select *from sysobjects where name=V_) -sysobjects 所有的視圖表看是否存在這個視圖drop view V_采購 -如果存在，刪除這個視圖-視圖名后的括號來重命名列，可以省略不寫，省略表-列名使原列名as-視圖名后的括號來重命名列，可以省略不寫，省略表-列名使原列名with encryption -加密創(chuàng)建視圖的本asselect ,數(shù)量 from Employees,Goods where Employees.Goo

9、ds.進貨員編號go-查看視圖創(chuàng)建的本exec sp_helptext V_采購exec sp_helptext V_銷售-對視圖信息的增加刪除修改select *from V_采購delete from V_采購 where = -視圖或函數(shù)V_采購 不可更新，因為修改會影響多個基表。update V_采購 set = where =-由于此修改只影響Employees(單個表)的信息，所以可以修改-這種修改式會同步到基表中(原數(shù)據(jù)表)，也就是說修改了原數(shù)據(jù)表中的數(shù)據(jù)update V_采購 set = , 100 where =-視圖或函數(shù)V_采購 不可更新，因為修改會影響多個基表。修改兩個

10、表的信息，個員表個商品表。所以出錯select *from Employeesupdate Employees set = where =-總結：通過視圖進數(shù)據(jù)的增加、刪除、修改時，如果影響多個基表，則不能修改；如果只影響單個表的信息，才可以修改成功-創(chuàng)建視圖：男if exists(select *from sysobjects where name=V_)drop view V_男go-視圖名后的括號來重命名列，可以省略不寫，省略表-列名使原列名as-對視圖進數(shù)據(jù)插-試圖進的插或更新已失敗，原因是標視圖或者標視圖所跨越的某視圖指定ITH CHECK OPTION，該操作的個或多個結果不符合C

11、HECK OPTION 約束。-在視圖的基礎上可以再創(chuàng)建個視圖drop view V_銷售總數(shù)量 -如果存在，刪除這個視圖-視圖名后的括號來重命名列，可以省略不寫，省略表-列名使原列名asinsert into V_(,) values (,30) -插出錯-V_銷售總數(shù)量 的更新或插失敗，因其包含派域或常量域。delete from V_銷售總數(shù)量 where = -視圖或函數(shù)V_銷售總數(shù)量 不可更新，因為修改會影響多個基表。update V_銷售總數(shù)量 set 10 where =-V_銷售總數(shù)量 的更新或插失敗，因其包含派域或常量域。drop view 視圖名-重命名視圖exec sp_

12、rename V_StuCou,exec sp_rename V_number,視圖練習-1、創(chuàng)建視圖V_StuCou,顯學選修課程信息 包括：學號、姓名、課程號、課程名、志愿號if exists(select *from sysobjects where name=V_StuCou)drop view V_StuCougocreate view V_StuCouasselect .StuNo,StuName,.CouNo,CouName,WillOrder from Student S,StuCou SC,Course C where .StuNo.StuNo and .CouNo.CouN

13、o and Stselect StuNo ,StuName 姓名 from V_StuCou group by StuNo,StuName having count(CouNo)5-4、統(tǒng)計選課門數(shù)于等于5門的學數(shù)select count() 5門的數(shù) from (select StuNo ,StuName 姓名 from V_StuCou group by StuNo,StuName having count(CouNo)5 ) 選課數(shù)于select count() 5門的數(shù) from (select StuNo ,StuName 姓名 from V_StuCou group by StuN

14、o,StuName having count(CouNo)5 ) 選課數(shù)于5的-5、創(chuàng)建V_number 顯每個學的選課門數(shù)-視圖不能包含order by語句 要排序的話，建好視圖后，利視圖查詢時再排序asselect .StuNo ,StuName 姓名 ,count(CouNo) 選課門數(shù) from Student S left join StuCou SC on .StuNo.StuNo group by .StuNo,StuName-按選課門數(shù)升序排序-6、利V_number 查找沒選課的學信息select , 姓名 from V_number where 0-7、通過V_number 刪除沒有選課的學信息delete from V_number where 0-視圖或函數(shù)V_number 不可更新，因為修改會影響多個基表。-8、修改視圖V_number，對其進加密asselect .StuNo ,