《MySQL數(shù)據(jù)庫項目式教程》項目六 索引與視圖

MySQL項目六索引與視圖2019年7月掌握索引與視圖的創(chuàng)建與使用方法學習使用索引與視圖的優(yōu)點學習目標MySQL了解索引與視圖的概念具有使用Navicat工具操作索引與視圖的能力重點、難點MySQL索引一一、索引MySQL1.索引的分類普通索引唯一索引普通索引是最基本的索引類型，允許在定義索引的字段中插入重復值或空值唯一索引指索引字段的值必須唯一，但允許有空值全文索引多列索引多列索引指在表中多個字段上創(chuàng)建的索引全文索引指在定義索引的字段上支持值的全文查找一、索引MySQL2.索引的設計原則04在不同值較少的字段上不要建立索引02避免對經(jīng)常更新的表建立太多索引05為經(jīng)常需要進行排序、分組和連接查詢的字段建立索引03數(shù)據(jù)量小的表最好不要建立索引01索引并非越多越好一、索引MySQL3.創(chuàng)建索引createtable表名(字段名數(shù)據(jù)類型[約束條件]，字段名數(shù)據(jù)類型[約束條件]，……[unique][fulltext]index|key[別名](字段名[長度][asc|desc]));語法格式：一、索引MySQL3.創(chuàng)建索引如果不加可選項參數(shù)unique或fulltext則默認表示創(chuàng)建普通索引。unique：表示創(chuàng)建唯一索引，在索引字段中不能有相同的值存在。fulltext：表示創(chuàng)建全文索引。[別名](字段名[長度])：指需要創(chuàng)建索引的字段。asc|desc：表示創(chuàng)建索引時的排序方式。其中asc表示升序排列，desc表示降序排列。默認為升序排列。參數(shù)說明：一、索引MySQL4.創(chuàng)建表時創(chuàng)建普通索引createtabletb_student(stuNochar(10)primarykey,namevarchar(50)notnull,sexchar(2)notnullcheck(sexin('男','女')),birthdaydate,specvarchar(30),phonevarchar(11),addressvarchar(255)default'地址不詳',indexname(name));在學生成績管理數(shù)據(jù)庫mystudent中，創(chuàng)建表tb_student（該表的結構與student表一致），同時在表的name字段上建立普通索引：一、索引MySQL4.創(chuàng)建表時建立唯一索引droptabletb_student;createtabletb_student(stuNochar(10)primarykey,namevarchar(50)notnull,sexchar(2)notnullcheck(sexin('男','女')),birthdaydate,specvarchar(30),phonevarchar(11),addressvarchar(255)default'地址不詳',uniqueindexuq_name(namedesc));在學生成績管理數(shù)據(jù)庫mystudent中，創(chuàng)建表tb_student（該表的結構與student表一致），同時在表的name字段上建立唯一索引，并且按姓名降序排序：一、索引MySQL5.刪除索引dropindex索引名on表名;語法格式：dropindexuq_nameontb_student;在學生成績管理數(shù)據(jù)庫mystudent中，將表tb_student中名為“uq_name”的索引刪除：一、索引MySQL6.為已經(jīng)存在的表添加索引create[unique][fulltext]index索引名on表名(字段名[長度][asc|desc]);使用createindex語句創(chuàng)建索引語法格式：createfulltextindexstu_nameontb_student(name);在學生成績管理數(shù)據(jù)庫mystudent中，在表tb_student的name字段上創(chuàng)建名為stu_name的全文索引：一、索引MySQL6.為已經(jīng)存在的表添加索引altertable表名add[unique][fulltext]index索引名(字段名[長度][asc|desc]);使用altertable語句創(chuàng)建索引語法格式：altertabletb_studentaddindexstu_stuNo_name(stuNo,name);在學生成績管理數(shù)據(jù)庫mystudent中，在表tb_student的stuNo和name字段上創(chuàng)建名為stu_stuNo_name的多列索引：MySQL視圖二二、視圖MySQL1.視圖的優(yōu)點04便于數(shù)據(jù)共享02屏蔽數(shù)據(jù)庫的復雜性05可以重新組織數(shù)據(jù)，以便輸出到其他應用程序中03簡化用戶權限的管理01為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理二、視圖MySQL2.創(chuàng)建視圖create[orreplace]view視圖名[(字段名列表)]asselect語句[with[cascaded|local]checkoption];語法格式：參數(shù)說明：orreplace：當具有同名視圖時，將使用新創(chuàng)建的視圖覆蓋原視圖。字段名列表：指定視圖查詢結果的字段名。select語句：是指用來創(chuàng)建視圖的select語句，可在select語句查詢多個表或視圖。withcheckoption：指出在可更新視圖上所進行的修改都要符合select語句所指定的限制條件。二、視圖MySQL2.創(chuàng)建視圖createviewstudent_viewasselectstuNo,name,sex,phonefromstudent;在學生成績管理數(shù)據(jù)庫mystudent中，創(chuàng)建一個基于student表的student_view的視圖，要求查詢并輸出所有學生的stuNo（學號）、name（姓名）、sex（性別）、phone（聯(lián)系電話）字段：二、視圖MySQL3.查看視圖desc視圖名;使用desc語句查看視圖語法格式：二、視圖MySQL3.查看視圖showtablestatuslike'視圖名';使用showtablestatus語句查看視圖語法格式：參數(shù)說明：“l(fā)ike”：表示后面是匹配字符串?！耙晥D名”：表示要查看的視圖名稱，可以是一個具體的視圖名，也可以是包含通配符，代表要查看的多個視圖二、視圖MySQL3.查看視圖showcreateview視圖名;使用showcreateview語句查看視圖語法格式：二、視圖MySQL4.修改視圖alterview視圖名[(字段名列表)]asselect語句[with[cascaded|local]checkoption];語法格式：參數(shù)說明：上述語句中的參數(shù)含義和創(chuàng)建視圖語句中的參數(shù)含義一樣二、視圖MySQL4.修改視圖alterviewstudent_viewasselectstuNo,name,sex,addressfromstudentwheresex='男';在學生成績管理數(shù)據(jù)庫mystudent中，修改已經(jīng)創(chuàng)建好的名為student_view視圖，查詢輸出所有男生的stuNo、name、sex、address字段信息：二、視圖MySQL5.使用Insert更新視圖insertintostudent_viewvalues('190010','李志杰','男','天津市和平區(qū)');在學生成績管理數(shù)據(jù)庫mystudent中，向視圖student_view中插入一條記錄，stuNo字段值為“190010”，name字段值為“李志杰”，sex字段值為“男”，address字段值為“

天津市和平區(qū)”：二、視圖MySQL5.使用update更新視圖updatestudent_viewsetname='李志豪'wherestuNo='190010';學生成績管理數(shù)據(jù)庫mystudent中，將視圖student_view中學號為“190010”，姓名為“李志杰”的學生的名字修改為“李志豪”：二、視圖MySQL5.使用delete刪除視圖中的數(shù)據(jù)insertintostudent_viewvalues('190011','張宇','男','山東省濟南市');deletefromstudent_viewwherestuNo='190011';在學生成績管理數(shù)據(jù)庫mystudent中，將在視圖student_view中臨時插入一條記錄，stuNo字段值為“190011”，name字段值為“張宇”，sex字段值為“男”，address字段值為“

山東省濟南市”。再使用刪除命令刪除視圖中學號為“190011”的記錄：MySQL使用Navicat工具操作索引與視圖三三、使用Navicat工具操作索引與視圖MySQL在左側“連接樹”工具欄中右擊student表，在彈出的菜單中選擇“設計表”；圖1

選擇設計表菜單1.使用Navicat新建索引三、使用Navicat工具操作索引與視圖MySQL在工作區(qū)中選擇“索引”選項卡，分別輸入或選擇相應的“索引名”、“欄位”、“索引類型”，再點擊“保存”按鈕圖2

通過Navicat創(chuàng)建索引1.使用Navicat新建索引三、使用Navicat工具操作索引與視圖MySQL在左側“連接樹”工具欄中選擇并展開mystudent數(shù)據(jù)庫，在工作區(qū)主界面上點擊“新建視圖”按鈕：圖3

通過Navicat創(chuàng)建視圖2.使用Navicat新建視圖三、使用Navicat工具操作索引與視圖MySQL在“視圖創(chuàng)建工具”選項卡中將需要創(chuàng)建視圖的表拖動至工作區(qū)中，在表的結構窗口中選擇要創(chuàng)建的視圖的字段等信息：圖4

通過Navicat創(chuàng)建視圖2.使用Navicat新建視圖三、使用Navicat工具操作索引與視圖MySQL在“SQL預覽”選項卡中可以看到視圖對應的SQL語句代碼，點擊“保存”按鈕，即可保存新創(chuàng)建的視圖：圖5

在Navicat工具中保存視圖2.使用Navicat新建視圖MySQL任務實施四四、任務實施MySQL1.在score表的stuNo字段和couNo字段上添加一個多列索引，索引名為idx_stuNo_couNo，SQL語句如下：altertablescoreaddindexidx_stuNo_couNo(stuNo,couNo);四、任務實施MySQL2.創(chuàng)建一個基于student表、course表、score表的視圖，視圖名為nopass_view，要求輸出學號、姓名、課程名和成績，SQL語句如下：createviewnopass_viewasselectstu.stuNo,,c.couName,sc