SQL視圖的定義與操縱
第1關：創(chuàng)建行列子集視圖
任務描述
本關任務：創(chuàng)建計算機系的學生信息的視圖 student_cs。
相關知識
行列子集視圖是指視圖的結果集來源于基本表，沒有經(jīng)過二次計算。 #####創(chuàng)建視圖
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]
參數(shù)說明：
-
OR REPLACE：表示替換已有視圖； -
ALGORITHM：表示視圖選擇算法，默認算法是UNDEFINED(未定義的)：MySQL自動選擇要使用的算法 ；merge合并；temptable臨時表； -
column_list：可選參數(shù)，指定視圖中各個屬性的名詞，默認情況下與select語句中查詢的屬性相同； -
select_statement：表示select語句； -
[WITH [CASCADED | LOCAL] CHECK OPTION]：表示視圖在更新時保證在視圖的權限范圍之內；cascade是默認值，表示更新視圖的時候，要滿足視圖和表的相關條件；local表示更新視圖的時候，要滿足該視圖定義的一個條件即可。
編程要求
根據(jù)提示，在右側編輯器補充代碼：
創(chuàng)建計算機系的學生信息視圖 student_cs。
代碼如下：
?create view student_cs
?as select *
?from student
?where sdept='計算機';
第2關：創(chuàng)建帶聚合函數(shù)的視圖
任務描述
本關任務： 創(chuàng)建由學號和平均成績兩個字段組成的視圖 v_grade_avg。
相關知識
創(chuàng)建帶聚合函數(shù)的視圖時，一定要給聚合函數(shù)列取別名。
編程要求
根據(jù)提示，在右側編輯器補充代碼， 創(chuàng)建由學號和平均成績兩個字段組成的視圖 v_grade_avg。
代碼如下：
create view v_grade_avg
as select sno'學號',AVG(grade)'平均成績'
from score
group by sno;
第3關：在視圖上查詢平均成績
任務描述
本關任務：在視圖 v_grade_avg 上查詢平均成績大于90分的學生信息。
相關知識
為了完成本關任務，你需要掌握：視圖的使用。
視圖的使用
查詢視圖數(shù)據(jù)
對視圖進行查詢，這個是非常簡單的（和使用SQL語句去查詢數(shù)據(jù)表一樣的語法）。雖然視圖是虛擬表，但它依舊是一張表，可通過Select語句進行查詢數(shù)據(jù)。
例：在視圖 V_AVG_S_G 上，查詢平均成績?yōu)?0分及以上的學生學號、姓名和成績；
SELECT sno,sname,grade from V_AVG_S_G WHRER 平均成績>=90;
編程要求
在視圖 v_grade_avg 上查詢平均成績大于90分的學生信息，并將代碼補充在右側編輯器中。
代碼如下：
? select *
? from v_grade_avg
? where 平均成績>=90;
第4關：創(chuàng)建信息系學生的視圖
任務描述
本關任務：建立信息系學生的視圖 v_information。
相關知識
為了完成本關任務，你需要掌握：如何創(chuàng)建視圖。
創(chuàng)建視圖
基本語法
可以使用 CREATE VIEW 語句來創(chuàng)建視圖。若要求該視圖在修改和插入數(shù)時要符合WHERE中的條件，創(chuàng)建時需加with check option選項。
編程要求
建立信息系學生的視圖 v_information，并要求進行修改和插入操作時仍需保證該視圖只有信息系的學生,并將代碼補充在右側編輯器中。
代碼如下：
?create view v_information
? as
? select *
? from student
? where sdept='信息'
? with check option;
第5關：修改信息系學生的視圖
任務描述
本關任務：修改信息系學生的視圖 v_information。
相關知識
為了完成本關任務，你需要掌握：如何修改視圖。
修改視圖
1.CREATE OR REPLACE VIEW 語句
語法結構：
CREATE OR REPLACEVIEW view_name [{column_list}]AS SELECT_STATEMENTWITH CHECK OPTION
釋義： CREATE OR REPLACE ： 【創(chuàng)建或替換已創(chuàng)建的】視圖 view_name ： 視圖名稱； column_list ： 屬性列； SELECT_STATEMENT ：SELECT語句； WITH CHECK OPTION 表示視圖在更新時保證在視圖的權限范圍內。
編程要求
修改信息系學生的視圖 v_information，并要求進行修改和插入操作時仍需保證該視圖只有信息系的學生，并且只返回學號、姓名和專業(yè)三個字段的值，并將代碼補充在右側編輯器中。
代碼如下：
?create or replace
?view v_information
? as
? select sno,sname,sdept
? from student
? where sdept='信息'
? with check option;
第6關：向信息系學生的視圖中插入數(shù)據(jù)
任務描述
本關任務：使用 INSERT 語句向視圖 v_information 中插入一條數(shù)據(jù)。
相關知識
為了完成本關任務，你需要掌握：如何向視圖添加數(shù)據(jù)。
視圖添加數(shù)據(jù)
在視圖上使用INSERT語句添加數(shù)據(jù)時，要符合以下規(guī)則：
- 使用INSERT語句向數(shù)據(jù)表中插入數(shù)據(jù)時，用戶必須有插入數(shù)據(jù)的權利；
- 由于視圖只引用表中的部分字段，所以通過視圖插入數(shù)據(jù)時只能明確指定視圖中引用的字段的取值。而那些表中并未引用的字段，必須知道在沒有指定取值的情況下如何填充數(shù)據(jù)，因此視圖中未引用的字段必須具備下列條件之一；
（1）該字段允許空值； （2）該字段設有默認值； （3）該字段是標識字段，可根據(jù)標識種子和標識增量自動填充數(shù)據(jù)； （4）該字段的數(shù)據(jù)類型為 timestamp 或 uniqueidentifier。 3. 視圖中不能包含多個字段值的組合，或者包含使用統(tǒng)計函數(shù)的結果； 4. 視圖中不能包含 DISTINCT 或 GROUP BY子句； 5. 如果視圖中使用了 WITH CHECK OPTION，那么該子句將檢查插入的數(shù)據(jù)是否符合視圖定義中 SELECT 語句所設置的條件。如果插入的數(shù)據(jù)不符合該條件，SQL Server 會拒絕插入數(shù)據(jù)； 6. 不能在一個語句中對多個基礎表使用數(shù)據(jù)修改語句。因此，如果要向一個引用了多個數(shù)據(jù)表的視圖添加數(shù)據(jù)時，必須使用多個 INSERT 語句進行添加。
編程要求
使用 INSERT 語句向視圖 v_information 中插入一條數(shù)據(jù)('98001','王立紅','信息','02')，并將代碼補充在右側編輯器補充代碼中。
代碼如下：
? insert ?
? into v_information(sno,sname,sdept,sclass)
? values ?('98001','王立紅','信息','02');
索引與數(shù)據(jù)庫完整性
第1關：創(chuàng)建一般索引
任務描述
本關任務：為 student 表按姓名升序建立索引，索引名為 idx_sname。
相關知識
為了完成本關任務，你需要掌握：
-
索引是什么；
-
索引的分類；
-
索引的創(chuàng)建和刪除；
-
查詢索引。
索引是什么
數(shù)據(jù)庫索引是一種提高數(shù)據(jù)庫系統(tǒng)性能的方法。索引能讓數(shù)據(jù)庫服務器更快地查找和獲取表中指定的行。
例如，為了方便讀者快速查找書中的術語，很多書籍在最后附加了索引頁，術語按字母排序，同時給出頁碼。這樣讀者可以根據(jù)術語名，快速獲取頁碼，而不用翻閱整本書。
但是索引也給數(shù)據(jù)庫系統(tǒng)帶來了一定的開銷，所以我們應該謹慎地使用它們。
索引的分類
索引大體可分為單列索引（普通索引，唯一索引，主鍵索引）、組合索引、全文索引、空間索引四類。本實訓我們主要介紹單例索引和組合索引：
-
單例索引：一個索引只包含單個列，但一個表中可以有多個單列索引；
- 普通索引：僅加速查詢 最基本的索引，沒有任何限制，是我們大多數(shù)情況下使用到的索引；
- 唯一索引：索引列中的值必須是唯一的，但允許為空值；
- 主鍵索引：是一種特殊的唯一索引，不允許有空值。
-
組合索引：在表的多個字段上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，使用組合索引時遵循最左前綴集合。
索引的創(chuàng)建和刪除
創(chuàng)建索引
創(chuàng)建索引有兩種方式，一種是在建表時創(chuàng)建，另一種是建表后創(chuàng)建：
- 普通索引：
-
創(chuàng)表時創(chuàng)建普通索引：
CREATE table mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,INDEX [indexName] (username));
-
建表后創(chuàng)建普通索引：
create INDEX 索引名稱 on 表名(字段名 desc/asc);#或者ALTER TABLE 表名 ADD INDEX 索引名稱 (字段名);
asc 代表升序索引，desc 代表降序，mysql 默認升序索引。
-
唯一索引：
CREATE UNIQUE INDEX 索引名稱 ON 表名(字段名);#或者ALTER TABLE 表名 ADD UNIQUE (字段名);
-
主鍵索引：主鍵索引一般在建表時創(chuàng)建，會設為
int而且是AUTO_INCREMENT自增類型的，例如一般表的id字段。CREATE TABLE mytable (id int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id));
-
組合索引：組合索引就是在多個字段上創(chuàng)建一個索引。（應用場景：當表的行數(shù)遠遠大于索引鍵的數(shù)目時，使用這種方式可以明顯加快表的查詢速度）
CREATE INDEX 索引名稱 ON 表名(字段1,字段2，字段3);#或者ALTER TABLE 表名 ADD INDEX 索引名稱(字段1，字段2，字段3);
查詢表中索引
查詢索引 SQL：
show index from 表名;
編程要求
在右側編輯器補充代碼，要求如下：
為 student 表按姓名升序建立索引，索引名為 idx_sname。
代碼如下：create index idx_sname on student(sname asc);
第2關：刪除索引-練習
任務描述
本關任務：刪除索引 idx_sname。
相關知識
刪除索引
同樣，刪除索引也有兩種方式。
#使用drop刪除索引drop index index_name on table_name ;#使用alter刪除索引alter table table_name drop index index_name ;alter table table_name drop primary key ; #刪除主鍵索引
編程要求
根據(jù)提示，在右側編輯器補充代碼，刪除索引 idx_sname。
代碼如下：drop index idx_sname on student;
第3關：創(chuàng)建聯(lián)合索引
任務描述
本關任務：創(chuàng)建聯(lián)合索引 idx_sname_sdept。
相關知識
為了完成本關任務，你需要掌握：如何創(chuàng)建聯(lián)合索引。
創(chuàng)建聯(lián)合索引
-
語法：
create index 索引名稱 on 表名(字段名稱)
-
示例：
create index firstIndex on student(id, name, address)；
-
注意：
1.索引名稱、表名、字段名稱 都不要用引號括起來；
2.對于聯(lián)合索引而言，字段名稱可以有多個，中間用英文逗號分隔即可； 3..普通索引數(shù)據(jù)可以重復。
編程要求
在 student 表 sname 字段和 sdept 字段上創(chuàng)建聯(lián)合索引 idx_sname_sdept，并將代碼補充在右側編輯器中。
代碼如下：? create index idx_sname_sdept on student(sname,sdept);
第4關：創(chuàng)建唯一索引
任務描述
本關任務：創(chuàng)建唯一索引 uk_cname。
相關知識
為了完成本關任務，你需要掌握：如何創(chuàng)建唯一索引。
如何創(chuàng)建唯一索引
-
語法：
create unique index 索引名稱 on 表名(字段名稱)；
-
示例：
create unique index secondIndex on student(id, name, address);
-
注意：
- 索引名稱、表名、字段名稱 都不要用引號括起來；
- 對于聯(lián)合索引而言，字段名稱可以有多個，中間用英文逗號分隔即可；
- 唯一索引數(shù)據(jù)不可重復。
編程要求
在 course 表的 cname 字段上創(chuàng)建唯一索引 uk_cname ，并將代碼補充在右側編輯器中。
代碼如下：?create unique index uk_cname on course(cname);
第5關：創(chuàng)建前綴索引
任務描述
本關任務：創(chuàng)建4個字符的前綴索引 pf_cname。
相關知識
為了完成本關任務，你需要掌握：如何創(chuàng)建前綴索引。
創(chuàng)建前綴索引
創(chuàng)建前綴索引的兩種方式
//1.使用CREATE INDEX語句CREATE INDEX index_name ON table_name (column_name (length));//2.使用ALTER TABLE語句ALTER TABLE table_name ADD INDEX (column_name (length));
編程要求
在 course 表的 cname 字段上創(chuàng)建4個字符的前綴索引 pf_cname，并將代碼補充在右側編輯器中。
代碼如下：?create index pf_cname on course(cname(4));
第6關：創(chuàng)建全文索引
任務描述
本關任務：創(chuàng)建全文索引 ft_cname 。
相關知識
為了完成本關任務，你需要掌握：如何創(chuàng)建全文索引。
創(chuàng)建全文索引
創(chuàng)建全文索引的兩種方式
//1.ALTER添加ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column1,column2,...);//2.CRATE INDEX添加CREATE FULLTEXT INDEX index_name ON table_name (column1,column2,...);
編程要求
在 course 表的 cname 字段上創(chuàng)建全文索引 ft_cname ，并將代碼補充在右側編輯器中。
代碼如下：? create fulltext index ?ft_cname on course(cname);文章來源：http://www.zghlxwxcb.cn/news/detail-759085.html
如有錯誤，歡迎指正。文章來源地址http://www.zghlxwxcb.cn/news/detail-759085.html
到了這里，關于數(shù)據(jù)庫原理 頭歌實訓 數(shù)據(jù)庫常用對象的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
