項目五

MySQL數(shù)據(jù)庫中的視圖和索引《數(shù)據(jù)庫應用與數(shù)據(jù)分析（MySQL）》課程目標目錄CONTENTS任務1認識和創(chuàng)建視圖任務2對視圖進行管理任務3創(chuàng)建和使用索引知識回顧項目五

MySQL數(shù)據(jù)庫中的視圖和索引課程目標項目五

MySQL數(shù)據(jù)庫中的視圖和索引知識目標：理解視圖的概念及其作用理解索引的含義與特征能力目標：掌握多種創(chuàng)建視圖的方法熟悉通過視圖進行數(shù)據(jù)查詢、修改和刪除等操作方法掌握在命令行中創(chuàng)建索引的方法熟練使用和管理索引素質(zhì)目標：培養(yǎng)學習者發(fā)現(xiàn)和解決實際問題的能力有效提升學習者的動手實踐能力，助其養(yǎng)成良好的職業(yè)習慣項目五

MySQL數(shù)據(jù)庫中的視圖和索引任務1認識和創(chuàng)建視圖項目五

MySQL數(shù)據(jù)庫中的視圖和索引

數(shù)據(jù)庫中視圖是一個極其重要的工具，它能簡化復雜查詢、增強數(shù)據(jù)安全性，并為開發(fā)人員提供靈活的數(shù)據(jù)訪問方式。本項目將詳細介紹如何創(chuàng)建視圖，并結(jié)合實際場景探討其應用范圍。視圖的創(chuàng)建任務分析項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】一、視圖的概念、特點、注意事項視圖是由一個或者多個表（或視圖）中導出的虛擬表，并沒有存放實際的數(shù)據(jù)視圖的概念1、簡化復雜的查詢；2、提高查詢性能；3、保證數(shù)據(jù)安全；視圖的特點1、創(chuàng)建視圖要有足夠的訪問權(quán)限；2、在創(chuàng)建視圖時，用到的表或者其他視圖必須已經(jīng)存在并在當前數(shù)據(jù)庫中，如果引用的表或視圖不在當前數(shù)據(jù)庫，需要在表或視圖前加上數(shù)據(jù)庫的名字；3、創(chuàng)建的視圖可以嵌套；使用視圖的注意事項項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】二、創(chuàng)建視圖的語法格式SQL語法格式如下：CREARTE[ORREPLACE]VIEW視圖名[字段列表]ASSELECT語句;語句說明：（1）VIEW：創(chuàng)建視圖的關(guān)鍵字。（2）視圖名：按規(guī)則命名，不能與數(shù)據(jù)表或其他視圖重名。（3）字段列表：可選項，定義視圖字段名，多個名稱用逗號分隔。若視圖字段名和源表字段名相同，可省略。（4）SELECT語句：查詢語句，可以是單表查詢，也可以是多表查詢。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】三、查看視圖及視圖的結(jié)構(gòu)2.查看數(shù)據(jù)庫中視圖的命令showtables;1．查看視圖結(jié)構(gòu)的命令describe視圖名;3.查看視圖的記錄數(shù)據(jù)select*from視圖名;項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】任務操作1：使用SQL語句創(chuàng)建單源視圖任務要求：

使用SQL語句創(chuàng)建一個名為“VIEW_01”的單表視圖，該視圖包括“商品”表中供應商編號為“G003”的所有商品信息，包括商品編號、商品名稱、生產(chǎn)日期、商品單價和庫存數(shù)量，并查看視圖的結(jié)構(gòu)定義和視圖中的記錄數(shù)據(jù)。

主要SQL語句：（1）創(chuàng)建查詢：CREATEORREPLACEVIEWview_01ASSELECT商品編號,商品名稱,生產(chǎn)日期,商品單價,庫存數(shù)量FROM商品WHERE供應商編號='G003';（2）descview_01；查看視圖的SQL命令（3）select*

fromview_01;

查看視圖的數(shù)據(jù)項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】任務操作2：使用圖形化界面創(chuàng)建多源視圖任務要求：

在NavicatPremium中使用視圖創(chuàng)建工具，創(chuàng)建一個名為view_02的多表視圖，該視圖包括客戶姓名為“劉佳”的客戶所購買商品的商品名稱，生產(chǎn)日期，銷售單價和庫存數(shù)量。主要SQL語句：

（1）啟動連接，打開good數(shù)據(jù)庫

（2）單擊“新建視圖”按鈕，顯示“視圖創(chuàng)建工具”界面

（3）單擊“視圖創(chuàng)建工具”按鈕，打開“視圖創(chuàng)建工具”窗口。添加數(shù)據(jù)來源，選擇所需字段，設置查詢條件。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】靈活使用圖形化界面或者SQL語句完成以下操作：1.創(chuàng)建視圖“view_客戶”，該視圖包括客戶編號，客戶姓名，性別和聯(lián)系電話字段。2.創(chuàng)建視圖“view_員工入庫單”，該視圖包括員工姓名，業(yè)務類別，商品編號，入庫時間，入庫單價，入庫數(shù)量。3.使用視圖“view_員工入庫單”，查詢員工姓名為“李天天”的員工登記了那些入庫記錄單。4.查看視圖“view_客戶”和視圖“view_員工入庫單”的定義結(jié)構(gòu)。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務實訓】任務2對視圖進行管理項目五

MySQL數(shù)據(jù)庫中的視圖和索引當已經(jīng)創(chuàng)建好的視圖不符合使用需求時，可以使用AlterView語句對其修改。同時還可以使用創(chuàng)建好的視圖來查詢、修改和更新源表的數(shù)據(jù)。視圖創(chuàng)建完成后如果不需要，還可以刪除。任務分析項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】修改和刪除視圖修改視圖SQL語法格式如下：ALTERVIEW視圖名[（字段列表）]ASSELECT語句；CREATEORREPLACEVIEW視圖名[（字段列表）]ASSELECT語句;刪除視圖SQL語法格式如下：DROPVIEW[IFEXISTS]視圖名1[,視圖名2]>[RESTRICT|CASCADE];0102項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】任務操作1：修改視圖使用SQL語句修改視圖“view_01”，使該視圖包括供應商編號為“G003”的供應商提供商品單價在4000元以上的商品的“商品編號”，“商品名稱”，“生產(chǎn)日期”，“商品單價”和“庫存數(shù)量”。任務要求：alterviewview_01asselect商品編號,商品名稱,生產(chǎn)日期,商品單價,庫存數(shù)量from商品where供應商編號='G003'and商品單價>4000;主要SQL語句：項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】任務操作2：利用視圖查詢修改、更新和刪除數(shù)據(jù)表中的數(shù)據(jù)。SQL語句格式：1.原理講解：視圖是源表的“窗口”，對視圖的操作會直接映射到源表（前提：視圖包含源表的必要字段，如主鍵）。2.操作類型及語法和操作表的語法格式一樣：

查詢：SELECT字段FROM視圖名WHERE條件;（與查詢表語法一致）。

修改：UPDATE視圖名SET字段=值WHERE條件;（需保證視圖包含被修改字段）。

插入：INSERTINTO視圖名(字段列表)VALUES(值列表);（需包含源表必填字段）。

刪除：DELETEFROM視圖名WHERE條件;（不可刪除源表中被其他表依賴的記錄）。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】任務操作2：利用視圖查詢修改、更新和刪除數(shù)據(jù)表中的數(shù)據(jù)。任務要求：1.創(chuàng)建視圖view_供應商，查詢供應商表中的數(shù)據(jù)；2.查詢供應商的地址在“北京”的供應商編號，供應商名稱，聯(lián)系人姓名，地址；3.使用視圖“view_供應商”修改供應商編號為“G001”的供應商的聯(lián)系人電話修改為“11111111000”；4.新增一個供應商，供應商編號為“G011”，供應商名稱為“深圳明鑫科技公司”，聯(lián)系人為“張三”，地址為“科技大道110號”5.刪除編號G011的供應商記錄。主要SQL語句：1.createorreplaceviewview_供應商asselect*from供應商;2.select供應商編號,供應商名稱,聯(lián)系人姓名,地址fromview_供應商where地址like“北京%”;3.updateview_供應商set聯(lián)系人電話='11111111000'where供應商編號='G001';4.insertintoview_供應商(供應商編號,供應商名稱,聯(lián)系人姓名,地址)values("G011","深圳明鑫科技公司","張三","科技大道110號");5.deletefromview_供應商where供應商編號='G011';項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】刪除任務操作2中創(chuàng)建的視圖“view_01”和“view_供應商”。任務要求：任務操作3：刪除視圖方法一：SQL語句：dropviewifexistsview_01，view_供應商；也可以在圖形化界面中，選擇視圖中對應的對象名（view_01或者view_供應商），右鍵單擊選擇“刪除視圖”。方法二：圖形化操作：項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】1.修改視圖“view_客戶”，該視圖包括性別為“男”的客戶編號，客戶姓名，性別和聯(lián)系電話字段。2.利用視圖“view_客戶”查詢電話號碼是的客戶信息。3.利用視圖“view_客戶”，新增一個客戶，客戶編號為“K009”，客戶姓名為“李四”，性別為“男”，聯(lián)系電話為“12345645612”。4.利用視圖“view_客戶”修改客戶編號為“K003”的客戶的郵政編碼為“45000”5.刪除視圖“view_客戶”。實訓內(nèi)容項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務實訓】任務3創(chuàng)建和使用索引項目五

MySQL數(shù)據(jù)庫中的視圖和索引任務分析索引是根據(jù)數(shù)據(jù)表中一個或多個字段按一定順序建立的映射結(jié)構(gòu)，用于快速定位記錄行。創(chuàng)建索引后，查詢時可基于索引字段直接定位記錄行的位置，從而加速數(shù)據(jù)檢索。在創(chuàng)建索引時，需確保目標表所在的數(shù)據(jù)庫處于可訪問狀態(tài)，且操作應在正確的表對象上執(zhí)行。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】一、索引的功能和類型按照功能來劃分，主要的索引類型有普通索引，唯一索引，主索引，全文索引，組合索引。1.普通索引（NormalIndex）：普通索引沒有任何的限制，允許有重復值和空值，主要用于提高查詢速度。2.主鍵索引（PrimaryKeyIndex）：專門為主鍵字段創(chuàng)建的索引，字段值必須唯一且不能為空，每個數(shù)據(jù)表僅能有一個主鍵，查詢的速度非常快。3.唯一索引（UniqueIndex）：要求字段值唯一，不允許重復，但允許為空值（NULL）。創(chuàng)建唯一索引的關(guān)鍵字是Unique。唯一索引可以防止數(shù)據(jù)中出現(xiàn)重復行。4.全文索引（FulltextIndex）：全文索引是MySQL中特殊的索引類型，只能在Char,Varchar或Text類型的字段上創(chuàng)建。主要用于全文搜索，在大量文本數(shù)據(jù)中進行搜索時非常有用。只有MyISAM存儲引擎支持全文索引。5.組合索引：在多個列上同時創(chuàng)建的索引。適用于需要同時查詢多個列的情況。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】二、創(chuàng)建索引的SQL語句格式1．在修改數(shù)據(jù)表結(jié)構(gòu)時添加索引SQL語法格式如下：Altertable<表名>add[Unique|Fulltext|Spatial]index<索引名>（字段名[Asc|Desc]）;2．在已經(jīng)存在的數(shù)據(jù)表中創(chuàng)建索引SQL語法格式如下：CREATE[Unique|PrimaryKey|Fulltext]INDEX索引名ON表名（字段名[Asc|Desc]）;3．在創(chuàng)建數(shù)據(jù)表的時候創(chuàng)建索引SQL語法格式如下：CREATETABLE表名（(字段名數(shù)據(jù)類型……[Unique|PrimaryKey|Fulltext]INDEX索引名（字段名[Asc|Desc]））;。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】三、索引的查看SQL語法格式如下：Showindexfrom<表名>;語句說明：（1）表名：要建立索引的表的名字。（2）索引類型：Unique唯一索引、PrimaryKey主鍵索引、Fulltext全文索引。（3）索引名：索引的名稱，必須符合MySQL的標示符命名規(guī)范，一個數(shù)據(jù)表的索引名稱必須是唯一的。（4）字段名：表示創(chuàng)建索引的字段。（5）Asc表示升序，Desc表示降序。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】四、索引的管理查看1.查看索引SHOWINDEXFROM<表名>;2.修改索引名使用命令修改索引名稱，語法格式如下：ALTERTABLE表名RENAMEINDEX原始索引名TO新索引名;3.刪除索引（1）使用DROP語句刪除索引的格式：DROPINDEX<索引名>ON<表名>;（2）使用ALTER語句刪除索引的格式：刪除普通索引：ALTERTABLE<表名>DROPINDEX<索引名>;刪除主鍵索引:ALTERTABLE<表名>DROPPRIMARYKEY;一張數(shù)據(jù)表只有一個主鍵，刪除時無需指定主鍵名。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識解析】1.altertable商品adduniqueindexspmc(商品名稱desc);2.altertable商品addindexspdj(商品單價);3.createindexix_gjson供應商(聯(lián)系人姓名desc);4.createuniqueindexix_lxrdhon供應商(聯(lián)系人電話);比較兩種方法，一種是在修改表結(jié)構(gòu)時添加索引，一種是直接建立索引。5.createindexix_aaon商品(類別,商品名稱);建立組合索引6.showindexfrom商品;7.dropindexspmcon商品;主要SQL語句：任務操作1：使用SQL語句創(chuàng)建、查看、刪除索引使用SQL語句在“商品”表中的“商品名稱”字段建立唯一索引，在“商品單價”字段建立普通索引，并查看和刪除索引。任務要求：項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】主要操作步驟：任務操作2：使用圖形界面創(chuàng)建、查看、刪除索引使用navicat在“客戶”表中“客戶編號”字段建立主鍵索引，在“客戶姓名”上建立普通索引，在“聯(lián)系電話”上建立唯一索引。任務要求：（1）打開表設計窗口：選中表→右鍵“設計表”→切換到“索引”選項卡。（2）創(chuàng)建不同類型索引：主鍵索引：選中字段（如“客戶編號”）→勾選“主鍵”。普通索引：點擊“添加索引”→輸入索引名（如khxm）→選擇字段（如“客戶姓名”）→索引類型選“NORMAL”。唯一索引：索引類型選“UNIQUE”→選擇字段（如“聯(lián)系電話”）。項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】任務操作3：創(chuàng)建表、添加數(shù)據(jù)和索引使用SQL語句創(chuàng)建“職工”表，該表的結(jié)構(gòu)如表5-1所示；將“員工”表中的數(shù)據(jù)添加到“職工”表中，并查看職工表中數(shù)據(jù)；查看“職工”表中創(chuàng)建好的索引；修改“職工”表中“姓名”字段的普通索引名為“姓名”任務要求：字段名稱數(shù)據(jù)類型字段長度是否允許NULL值索引員工IDvarchar10否主鍵姓名Varchar20否普通索引性別Varchar2是

聯(lián)系電話Varchar15是項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】1.createtable職工(員工IDvarchar(10)primarykeynotnull,姓名varchar(20)notnull,性別varchar(2),聯(lián)系電話varchar(15),indexxm(姓名asc));2.insertinto職工select員工編號,姓名,性別,聯(lián)系電話from員工;3.select*from職工;4.showindexfrom職工;5.altertable職工renameindexxmto姓名;主要SQL語句：任務操作3：創(chuàng)建表、添加數(shù)據(jù)和索引項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務操作】【實訓1】：在“入庫記錄”表的“商品編號”字段建立唯一索引，在“入庫單價”字段建立普通索引。查看該表建立的所有索引，刪除“商品編號”字段上的唯一索引?！緦嵱?】：在“銷售記錄”表的“銷售單價”字段建立普通索引。在“銷售編號”字段建立主鍵索引。使用SQL語句或者圖形化操作界面完成下面的實訓：項目五

MySQL數(shù)據(jù)庫中的視圖和索引【任務實訓】知識回顧項目五

MySQL數(shù)據(jù)庫中的視圖和索引1.視圖的創(chuàng)建視圖在數(shù)據(jù)庫中作為一個對象來存儲,是從一個或者多個表或者視圖中導出的虛擬表。視圖是在查詢時動態(tài)生成的數(shù)據(jù)，會根據(jù)源表數(shù)據(jù)的變化而變化，并不存儲實際數(shù)據(jù)。視圖具有簡化查詢、提高查詢性能、保證數(shù)據(jù)安全的優(yōu)點。視圖的相關(guān)概念11、使用圖形化界面創(chuàng)建視圖2、使用SQL語句創(chuàng)建視圖CREATEORREPLACEVIEW視圖名[（字段列表）]ASSELECT語句;創(chuàng)建視圖的方法21、簡化復雜查詢2、提高查詢性能3、保證數(shù)據(jù)安全性視圖的特點3項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識回顧】視圖的修改1、使用SQL語句ALTERVIEW視圖名[（字段列表）]ASSELECT語句；CREATEORREPLACEVIEW視圖名[（字段列表）]ASSELECT語句;2、使用圖形化界面修改視圖使用視圖查詢、更新和刪除數(shù)據(jù)SQL語句的語法格式和表的操作一樣。視圖的刪除2.管理和使用視圖1、使用SQL語句DROPVIEW[IFEXISTS]視圖名1[,視圖名2]>[RESTRICT|CASCADE];2、使用圖形化界面刪除視圖項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識回顧】普通索引、主鍵索引、唯一索引、全文索引、組合索引索引的類型1、使用SQL語句創(chuàng)建索引（分為三種情況）2、使用圖形化管理界面創(chuàng)建索引索引的創(chuàng)建使用SQL語句或者圖形化界面完成下列操作：1、索引的查看2、索引名的修改3、刪除索引索引的管理3.創(chuàng)建和查看索引項目五

MySQL數(shù)據(jù)庫中的視圖和索引【知識回顧】感謝您的觀看THANKYOUFORREADING！項目六

MySQL數(shù)據(jù)庫中的程序設計《數(shù)據(jù)庫應用與數(shù)據(jù)分析（MySQL）》課程目標目錄CONTENTS任務1MySQL程序設計語法任務2存儲過程和自定義函數(shù)任務3創(chuàng)建和使用觸發(fā)器知識回顧項目六

MySQL數(shù)據(jù)庫中的程序設計課程目標項目六

MySQL數(shù)據(jù)庫中的程序設計知識目標：能力目標：素質(zhì)目標：掌握MySQL中程序設計語言的基礎要素熟練掌握MySQL內(nèi)置函數(shù)的使用理解程序流程控制語句三種結(jié)構(gòu)的語法及其使用方法掌握存儲過程、觸發(fā)器的相關(guān)概念和應用場景具備編寫MySQL存儲過程和函數(shù)的能力具備應用程序流程控制語句常見結(jié)構(gòu)的能力培養(yǎng)學習者的邏輯思維和分析問題與解決問題的能力激發(fā)學習者的主動探索精神，增強其創(chuàng)新意識和獨立思考能力項目六

MySQL數(shù)據(jù)庫中的程序設計任務1MySQL程序設計語法項目六

MySQL數(shù)據(jù)庫中的程序設計MySQL程序設計中會用到常量、變量和運算符，并通過分支語句、循環(huán)語句等控制語句來控制程序的執(zhí)行，以完成具體任務。本任務通過具體案例，幫助學習者掌握程序設計的基礎知識和程序控制語句，為學習更復雜的數(shù)據(jù)庫編程和數(shù)據(jù)處理奠定基礎。任務分析項目六

MySQL數(shù)據(jù)庫中的程序設計一、常量２.

數(shù)值常量直接表示數(shù)值的常量，包括整數(shù)和浮點數(shù)，如整數(shù)123、浮點數(shù)3.14等。數(shù)值常量可以進行各種算術(shù)運算。3.

時間日期常量用單引號或雙引號括起來表示日期時間的字符串。如包含年月日的'2022-02-22'，包含時間的'11:30:45'等。用單引號或雙引號括起來的字符序列，如'hello'、"world"。如果字符串中包含引號本身，需要使用轉(zhuǎn)義字符反斜杠“\”進行轉(zhuǎn)義，例如'It\'sabeautifulday.'。布爾常量是關(guān)系運算或邏輯運算的結(jié)果，只有兩個值：TRUE或FALSE。TRUE表示真，表示數(shù)值為1，F(xiàn)ALSE表示假，表示數(shù)值為0。常量指程序執(zhí)行過程中保持固定不變的值。１.

字符串常量4.

布爾常量【知識解析】項目六

MySQL數(shù)據(jù)庫中的程序設計二、變量變量是指程序運行過程中其值可以改變的量，變量是表達式中的基本元素。MySQL中的變量分為局部變量、用戶變量和系統(tǒng)變量。變量的生存周期【知識解析】項目六

MySQL數(shù)據(jù)庫中的程序設計二、變量【知識解析】01用戶變量用戶自己定義并使用的變量稱為用戶變量。用戶在用戶變量中保存值，然后在之后的語句中使用該值。用戶變量在使用前必須定義和初始化，如果使用沒有初始化的變量，其值為NULL。用戶變量與當前連接有關(guān)，用戶變量以“@”為前綴，表示為“@變量名”，以便于與字段名進行區(qū)別。變量名可以由字母、數(shù)字和下劃線組成，但不能以數(shù)字開頭，作用域為整個會話，在會話結(jié)束時自動釋放。（1）用SET語句定義和初始化用戶變量。SET@變量名1=變量值1[,@變量名2=變量值2,......];（2）查看用戶變量的值SELECT@變量名;項目六

MySQL數(shù)據(jù)庫中的程序設計二、變量【知識解析】01用戶變量用戶自己定義并使用的變量稱為用戶變量。用戶在用戶變量中保存值，然后在之后的語句中使用該值。用戶變量在使用前必須定義和初始化，如果使用沒有初始化的變量，其值為NULL。用戶變量與當前連接有關(guān)，用戶變量以“@”為前綴，表示為“@變量名”，以便于與字段名進行區(qū)別。變量名可以由字母、數(shù)字和下劃線組成，但不能以數(shù)字開頭，作用域為整個會話，在會話結(jié)束時自動釋放。（1）用SET語句定義和初始化用戶變量。SET@變量名1=變量值1[,@變量名2=變量值2,......];（2）查看用戶變量的值SELECT@變量名;項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】02系統(tǒng)變量MYSQL中有一些特定的設置，當MYSQL服務器啟動的時候，這些設置被讀取出來，這些設置就是系統(tǒng)變量。

系統(tǒng)變量由MYSQL系統(tǒng)自動維護，用于配置服務器。系統(tǒng)變量包括“全局變量”“會話變量”，全局變量對服務中的所有連接有效，會影響整個服務器，而會話變量只對當前連接有效，不適用于其它連接。

系統(tǒng)變量一般以“@@”為前綴，某些特定系統(tǒng)變量可以省略“@@”，如系統(tǒng)版本號（VERSION）、系統(tǒng)日期（Current_Date)、系統(tǒng)時間(Current_Time)、當前用戶名(Current_User)，這些系統(tǒng)變量值不可以改變，有些系統(tǒng)變量值可以進行修改。項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】03局部變量

局部變量是可保存單個特定類型數(shù)據(jù)值的變量，在存儲過程和BEGIN...END中語句塊中使用。在語句塊執(zhí)行完畢后，局部變量就會被釋放。其它語句塊中不可以使用該局部變量。（1）使用DECLARE語句定義局部變量DECLARE變量名

數(shù)據(jù)類型[DEFAULT默認值];（2）給局部變量賦值SET變量名=變量值;（3）將查詢結(jié)果賦值給局部變量SELECT字段名INTO局部變量名FROM表名[WHERE條件表達式];項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】項目六

MySQL數(shù)據(jù)庫中的程序設計變量類型作用域前綴默認值規(guī)則賦值語句示例局部變量BEGIN...END塊內(nèi)無必須顯式聲明默認值DECLAREvarINTDEFAULT0;用戶變量當前會話@默認為NULLSET@var='值';會話系統(tǒng)變量當前會話無繼承全局變量或配置文件SETSESSIONsort_buffer_size=1M;全局系統(tǒng)變量所有新會話無由配置文件或編譯選項決定SETGLOBALmax_connections=200;補充說明：局部變量需在存儲過程/函數(shù)中使用DECLARE聲明用戶變量通過SELECT@var可跨語句使用查看系統(tǒng)變量：SHOW[GLOBAL|SESSION]VARIABLESLIKE'var_name'變量對比表三、流程控制之條件分支語句IF...ELSE語句IF...ELSE語句根據(jù)邏輯表達式選擇性執(zhí)行代碼塊，THEN后接滿足條件的語句，可選ELSE處理否定情況，適用于簡單的條件判斷場景。項目六

MySQL數(shù)據(jù)庫中的程序設計IF條件表達式THEN語句1[ELSE語句2]ENDIF;【知識解析】條件：通常為條件表達式或邏輯表示式。[ELSE語句2]：可選項，語句2中還可以包含IF語句，形成IF語句的嵌套。ENDIF：IF語句的結(jié)束標志。三、流程控制之條件分支語句CASE語句CASE語句分為簡單CASE和搜索CASE兩種形式，簡單CASE按表達式值匹配，搜索CASE按布爾條件匹配，支持ELSE默認分支，適用于多分支條件判斷。項目六

MySQL數(shù)據(jù)庫中的程序設計簡單CASE語句將表達式與一組簡單表達式進行比較，以確定其結(jié)果。CASE表達式WHEN值1THEN語句1[WHEN值2THEN語句2]...[WHEN值NTHEN語句N][ELSE語句N+1]ENDCASE;【知識解析】搜索CASE語句用于計算一組條件表達式的值以確定其相應的操作：CASEWHEN條件表達式1THEN語句1[WHEN條件表達式2THEN語句2]...[WHEN條件表達式NTHEN語句N][ELSE語句N+1]ENDCASE;三、流程控制之循環(huán)語句WHILE循環(huán)WHILE循環(huán)先判斷條件，條件為真時執(zhí)行循環(huán)體，適合已知條件的場景，如循環(huán)處理一組數(shù)據(jù)，直到滿足特定條件為止。REPEAT循環(huán)REPEAT循環(huán)先執(zhí)行循環(huán)體，再判斷UNTIL條件，至少執(zhí)行一次，適用于需要至少執(zhí)行一次的循環(huán)場景，如日期遍歷。LOOP循環(huán)LOOP循環(huán)需配合LEAVE語句手動退出，適用于復雜的循環(huán)邏輯，如嵌套循環(huán)或多出口控制，提供更大的靈活性。項目六

MySQL數(shù)據(jù)庫中的程序設計[標簽名:]WHILE條件表達式DO

循環(huán)體ENDWHILE[標簽名];[標簽名:]REPEAT循環(huán)體UNTIL條件表達式ENDREPEAT[標簽名];[標簽名:]LOOP循環(huán)體ENDLOOP[標簽名];【知識解析】任務操作1：使用局部變量計算任務要求：

使用SQL語句，通過局部變量計算“商品”表中所有商品的平均庫存數(shù)量，并顯示結(jié)果。

主要SQL語句：SET@avg_stock=0;SELECTAVG(庫存數(shù)量)INTO@avg_stockFROM商品;SELECT@avg_stockAS平均庫存數(shù)量；【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計任務操作2：使用用戶自定義變量任務要求：

使用SQL語句，通過用戶定義變量和表達式計算指定訂單的處理時間（從訂單創(chuàng)建日期到當前日期的天數(shù)），并將結(jié)果存儲到一個用戶定義變量中。主要SQL語句：SET@order_id=1;SET@processing_time=0;SELECTDATEDIFF(CURDATE(),銷售時間)INTO@processing_timeFROM銷售記錄WHERE銷售編號=@order_id;SELECT@processing_timeAS處理時間；【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計任務操作3：使用條件分支語句任務要求：

使用SQL語句判斷“銷售記錄”表中銷售單價值是否大于或等于1000，如果是則打九折，否則不打折，顯示原銷售單價和折后單價。主要SQL語句：SELECT

銷售單價,CASEWHEN銷售單價>=1000THEN銷售單價*0.9ELSE銷售單價ENDAS折后單價FROM銷售記錄;【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計任務2存儲過程和自定義函數(shù)項目六

MySQL數(shù)據(jù)庫中的程序設計

存儲過程和自定義函數(shù)是數(shù)據(jù)庫中定義的一些能夠?qū)崿F(xiàn)特定功能與操作的SQL語句的集合，這些語句經(jīng)過編譯后被作為整體存儲于數(shù)據(jù)庫服務器中，需要時則可以通過專門的語句或者其他應用程序來調(diào)用。任務分析項目六

MySQL數(shù)據(jù)庫中的程序設計一、存儲過程概念與優(yōu)點項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】存儲過程的概念存儲過程是一個完成特定功能與操作的SQL語句的集合，通過存儲過程可以將完成特定功能的SQL語句封裝起來，根據(jù)需要可以多次調(diào)用，避免重復編寫相同的語句。存儲過程經(jīng)編譯后保存在數(shù)據(jù)庫中，因此執(zhí)行效率更高。存儲過程的優(yōu)點（1）執(zhí)行速度快；（2）可以使數(shù)據(jù)獨立：（3）有很強的靈活性：（4）提高安全性；0102二、創(chuàng)建與調(diào)用存儲過程創(chuàng)建存儲過程使用CREATEPROCEDURE定義存儲過程，參數(shù)列表需注明方向與數(shù)據(jù)類型，過程體置于BEGIN...END中，支持復雜邏輯。調(diào)用存儲過程通過CALL語句調(diào)用存儲過程，傳遞實參，支持獲取OUT參數(shù)返回值，實現(xiàn)業(yè)務邏輯的封裝和復用。項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】CREATEPROCEDURE存儲過程名([參數(shù)列表])過程體；CALL存儲過程名([參數(shù)列表]);三、存儲過程的管理查看存儲過程SHOWPROCEDURESTATUS[LIKE‘存儲過程名’];[LIKE‘存儲過程名’]：可選項，用來匹配存儲過程的名稱，LIKE不能省略。修改存儲過程可以使用ALTER語句修改存儲過程的某些特征，不能修改存儲過程的內(nèi)容，如果要修改存儲過程的內(nèi)容，只能刪除該存儲過程并重新進行創(chuàng)建。刪除存儲過程DROPPROCEDURE[IFEXISTS]存儲過程名;項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】SHOWCREATEPROCEDURE存儲過程名;ALTERPROCEDURE存儲過程名[存儲過程的特征];[IFEXISTS]：可選項，此參數(shù)在執(zhí)行刪除操作時，先判斷該存儲過程是否存在：如果存在，直接刪除；如果不存在，刪除操作也不會報錯，可以避免系統(tǒng)錯誤。四、自定義函數(shù)1創(chuàng)建自定義函數(shù)使用CREATEFUNCTION定義自定義函數(shù)，必須聲明RETURNS類型且函數(shù)體包含RETURN語句，支持單一值返回。2函數(shù)特點自定義函數(shù)不支持OUT參數(shù)，但可在SQL語句中直接調(diào)用，實現(xiàn)表達式級復用，適用于簡單的業(yè)務邏輯計算。3函數(shù)的調(diào)用項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】CREATEFUNCTION函數(shù)名[(參數(shù)列表)]RETURNS返回值類型函數(shù)體;自定義函數(shù)的調(diào)用不能使用CALL語句，使用SELECT調(diào)用。SELECT函數(shù)名([參數(shù)]);對自定義函數(shù)的查看、修改、刪除等操作與存儲過程類似。項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】存儲過程和函數(shù)的對比表對比維度??存儲過程(StoredProcedure)??函數(shù)(Function)??返回值特性?可返回多個值（通過OUT/INOUT參數(shù)）或結(jié)果集必須返回單一值（標量或表對象）?調(diào)用方式?獨立調(diào)用：CALLproc_name()嵌入SQL語句：SELECTfunc_name()?參數(shù)類型?支持IN、OUT、INOUT三種參數(shù)類型僅支持IN參數(shù)（輸入?yún)?shù)）?事務控制?支持完整事務（COMMIT/ROLLBACK）禁止事務操作?數(shù)據(jù)庫操作權(quán)限?允許執(zhí)行DDL/DML（增刪改表結(jié)構(gòu)及數(shù)據(jù)）禁止修改數(shù)據(jù)庫狀態(tài)（只讀計算）?確定性要求?可非確定性（相同輸入可能不同輸出）通常需聲明DETERMINISTIC（相同輸入必得相同輸出）?觸發(fā)器支持??不可在觸發(fā)器中直接調(diào)用?可在觸發(fā)器中調(diào)用?典型應用場景?批量數(shù)據(jù)處理、業(yè)務規(guī)則封裝、數(shù)據(jù)庫維護任務數(shù)據(jù)轉(zhuǎn)換、計算字段、查詢條件優(yōu)化五、注釋符與定界符1注釋符MySQL支持#、--與/**/三種注釋，推薦在復雜過程與函數(shù)中使用多行注釋說明參數(shù)、返回值與業(yè)務邏輯，提升代碼可讀性。項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】（1）“#注釋內(nèi)容”:單行注釋，#后直接跟注釋內(nèi)容。（2）“--注釋內(nèi)容”:單選注釋，--（空格）后面跟注釋內(nèi)容。（3）“/*注釋內(nèi)容*/:多行注釋，/*在注釋內(nèi)容的開頭，*/在注釋內(nèi)容的結(jié)尾，中間為注釋內(nèi)容。五、注釋符與定界符2定界符使用項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】MYSQL中語句的默認結(jié)束符為半角分號（;），在命令行中，一行語句以分號結(jié)束，命令會立即執(zhí)行，但在創(chuàng)建存儲過程或自定義函數(shù)時，需要編輯多條語句而不立即執(zhí)行，因此可以將結(jié)束符先修改為其它符號，存儲過程或自定義函數(shù)結(jié)束再將結(jié)束符恢復。修改結(jié)束符的語法格式如下：DELIMITER自定義的結(jié)束符；自定義的結(jié)束符可以使用一些特殊的符號，通常使用“//”、“$$”、“##”等。存儲過程或自定義函數(shù)語句完成后，將結(jié)束符恢復為分號：DELIMITER;任務操作1：創(chuàng)建過程并調(diào)用【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計DELIMITER//CREATEPROCEDUREPROC001()BEGIN

DECLAREnum1INTDEFAULT0;

SELECTCOUNT(*)INTOnum1FROM銷售記錄

WHERE商品編號='501002';

IFnum1>0THEN

SELECTnum1AS銷售記錄數(shù),'有銷售'AS銷售情況;

ELSE

SELECT0AS銷售記錄數(shù),'沒有銷售'AS銷售情況;

ENDIF;END//DELIMITER;--調(diào)用存儲過程：CALLPROC001();任務要求：

使用SQL語句創(chuàng)建存儲過程PROC001，功能是從“銷售記錄”表中查看商品編號為“501002”的商品是否有銷售記錄。如果有銷售記錄，顯示銷售記錄數(shù)和“有銷售”；如果沒有銷售，銷售記錄數(shù)為0，并顯示“沒有銷售”主要SQL語句：任務操作2：創(chuàng)建自定義函數(shù)使用任務要求：

使用SQL語句創(chuàng)建自定義函數(shù)，根據(jù)“客戶表”中的積分情況對客戶進行等級分類，大于10000為鉆石，大于8000為鉑金，大于6000為黃金，大于4000為白銀，低于4000為青銅，調(diào)用函數(shù)，顯示客戶的等級。主要SQL語句：DELIMITER//CREATEFUNCTIONdengji(in_bianhaoCHAR(5))RETURNSVARCHAR(20)DETERMINISTICBEGIN

DECLAREv_pointsINTDEFAULT0;

SELECT積分INTOv_pointsFROM客戶WHERE客戶編號=in_bianhao;

IFv_pointsISNULLTHEN

SETv_points=0;

ENDIF;【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計--確定積極等級

RETURNCASE

WHENv_points>=10000THEN'鉆石'

WHENv_points>=8000THEN'鉑金'

WHENv_points>=6000THEN'黃金'

WHENv_points>=4000THEN'白銀'

ELSE'青銅'

END;

END//DELIMITER;任務操作3：存儲過程的應用任務要求：

使用SQL語句創(chuàng)建存儲過程，使用WHILE循環(huán)計算“銷售記錄”表中從指定日期到當前日期的累計銷售額，并顯示出來。主要SQL語句：DELIMITER//DROPPROCEDUREIFEXISTSCalculateSales;CREATEPROCEDURECalculateSales(INstart_dateDATE)BEGIN

--聲明變量必須放在存儲過程的開頭

DECLAREcur_dateDATE;

DECLAREtotal_salesDECIMAL(10,2)DEFAULT0.00;

DECLAREsale_amountDECIMAL(10,2);

--初始化當前日期為開始日期

SETcur_date=start_date;

--使用WHILE循環(huán)遍歷每一天

WHILEcur_date<=CURDATE()DO

--查詢當天的銷售金額

【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計

SELECTSUM(銷售單價*數(shù)量)INTOsale_amount

FROM銷售記錄

WHERE銷售時間=cur_date;

--如果當天有銷售記錄，則累加到總銷售額

IFsale_amountISNOTNULLTHEN

SETtotal_sales=total_sales+sale_amount;

ENDIF;

--日期加1天

SETcur_date=DATE_ADD(cur_date,INTERVAL1DAY);

ENDWHILE;

--輸出累計銷售額

SELECTtotal_salesAS累計銷售額;END//DELIMITER;--調(diào)用存儲過程：CALLCalculateSales('2022-01-01');任務3創(chuàng)建和使用觸發(fā)器項目六

MySQL數(shù)據(jù)庫中的程序設計任務分析觸發(fā)器是由事件來觸發(fā)某個操作，這些事件包括INSERT語句，UPDATE語句和DELETE語句。當數(shù)據(jù)庫系統(tǒng)執(zhí)行這些事件時，就會激活觸發(fā)器，執(zhí)行相應的操作，使用觸發(fā)器可對表實施復雜的完整性約束，保持數(shù)據(jù)的一致性。本任務通過案例，讓學生掌握MySQL中的觸發(fā)器的創(chuàng)建和使用方法?！局R解析】一、觸發(fā)器概述項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】

觸發(fā)器是一種特殊的存儲過程，它與數(shù)據(jù)表緊密相連，用于對數(shù)據(jù)表實施完整性約束。觸發(fā)器中包含了一系列SQL語句，用來執(zhí)行相應的操作，從而確保數(shù)據(jù)的完整性。觸發(fā)器建立在觸發(fā)事件上，對數(shù)據(jù)表執(zhí)行Insert、Update或者Delete等操作時，MySQL就會自動執(zhí)行建立在這些操作上的觸發(fā)器。

觸發(fā)器的調(diào)用和存儲過程不一樣，觸發(fā)器只能由數(shù)據(jù)庫的特定事件來觸發(fā)，并且不能接收參數(shù)，當滿足觸發(fā)器的觸發(fā)條件時，就會執(zhí)行觸發(fā)器中定義的程序語句。觸發(fā)時機\DML操作INSERT（新增）UPDATE（修改）DELETE（刪除）BEFOREBEFOREINSERTBEFOREUPDATEBEFOREDELETEAFTERAFTERINSERTAFTERUPDATEAFTERDELETE二、觸發(fā)器的特點01當在表上執(zhí)行指定操作時，觸發(fā)器會自動激活。02觸發(fā)器與特定的表相關(guān)聯(lián)，并且只能在該表上定義的觸發(fā)事件上激活。03觸發(fā)器可以包含復雜的SQL語句，這些語句可以執(zhí)行數(shù)據(jù)驗證、更新其他表等。04觸發(fā)器可以在事件之前（BEFORE）或之后（AFTER）激活。項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】自動執(zhí)行與表關(guān)聯(lián)復雜邏輯定義觸發(fā)時間05觸發(fā)時間INSERT、UPDATE、DELETE執(zhí)行前后，共6種觸發(fā)事件。三、

觸發(fā)器的使用場景1.數(shù)據(jù)驗證：在插入或更新數(shù)據(jù)時，自動驗證數(shù)據(jù)的合法性。5.權(quán)限控制：根據(jù)特定條件，自動限制數(shù)據(jù)的插入、更新或刪除。觸發(fā)器一般以下場景中使用。2.自動更新字段：如自動填充創(chuàng)建時間、更新時間等字段。4.級聯(lián)更新：在更新某個表的數(shù)據(jù)時，自動更新相關(guān)聯(lián)的表。3.記錄日志：在數(shù)據(jù)變更時，自動記錄變更日志。項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】四、創(chuàng)建觸發(fā)器項目六

MySQL數(shù)據(jù)庫中的程序設計創(chuàng)建一個觸發(fā)器，當觸發(fā)事件發(fā)生時，執(zhí)行應用的語句。語法格式如下：CREATETRIGGER觸發(fā)器名

觸發(fā)時機

觸發(fā)事件ON表名FOREACHROW執(zhí)行語句;語句說明：（1）觸發(fā)器名：創(chuàng)建的觸發(fā)器的名稱，在當前數(shù)據(jù)庫中是唯一的。（2）觸發(fā)時機：BEFORE或AFTER，表示觸發(fā)器在操作之前或之后執(zhí)行。（3）觸發(fā)事件：INSERT、UPDATE或DELETE，表示觸發(fā)器在哪些操作上觸發(fā)。（4）FOREACHROW：表示數(shù)據(jù)表中任意一條記錄滿足觸發(fā)事件都會激活觸發(fā)器。（5）執(zhí)行語句：觸發(fā)器激活后將要執(zhí)行的程序語句，如果是多條語句可以使用BEGIN...END復合語句?！局R解析】五、管理觸發(fā)器01查看觸發(fā)器查看觸發(fā)器是指查看數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器以及觸發(fā)器的定義、狀態(tài)和語法等信息，語法格式如下：SHOWTRIGGERS;只能查看所有觸發(fā)器的信息，不能查看指定觸發(fā)器的信息02刪除觸發(fā)器可以將數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器刪除，語法格式如下：DROPTRIGGER觸發(fā)器名；項目六

MySQL數(shù)據(jù)庫中的程序設計【知識解析】任務操作1：創(chuàng)建AFTERINSERT觸發(fā)器【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計DELIMITER//DROPTRIGGERIFEXISTSsale_upd_stock//CREATETRIGGERsale_upd_stockAFTERINSERTON銷售記錄FOREACHROWBEGINUPDATE商品

SET庫存數(shù)量=庫存數(shù)量-NEW.數(shù)量WHERE商品編號=NEW.商品編號;--用NEW關(guān)鍵字訪問新記錄END//DELIMITER;任務要求：使用SQL語句創(chuàng)建AFTERINSERT觸發(fā)器，當向銷售記錄表中插入新記錄時，自動將商品表中的庫存數(shù)據(jù)進行更新。主要SQL語句：銷售后自動扣減庫存1觸發(fā)器sale_upd_stocksale_upd_stock觸發(fā)器在INSERT銷售記錄后執(zhí)行，利用NEW.商品編號與NEW.數(shù)量更新商品表庫存數(shù)量，實現(xiàn)銷售即扣減的業(yè)務閉環(huán)。2實時庫存更新該模式保證庫存實時準確，避免超賣風險，同時減少應用層重復代碼，提高開發(fā)效率和系統(tǒng)性能。3事務一致性觸發(fā)器操作與原數(shù)據(jù)操作在同一事務中，失敗即整體回滾，確保數(shù)據(jù)一致性，無需額外事務控制。項目六

MySQL數(shù)據(jù)庫中的程序設計任務操作2：創(chuàng)建BEFOREUPDATE觸發(fā)器任務要求：

使用SQL語句創(chuàng)建BEFOREUPDATE觸發(fā)器。當更新員工表中的記錄時，自動校驗工資是否超過10000，如果超過則將工資設置為10000。主要SQL語句：DELIMITER//DROPTRIGGERIFEXISTSbef_emp_update//CREATETRIGGERbef_emp_updateBEFOREUPDATEON員工FOREACHROWBEGINIFNEW.工資>10000THENSETNEW.工資=10000;ENDIF;END//DELIMITER;【任務操作】項目六

MySQL數(shù)據(jù)庫中的程序設計更新前校驗工資上限01觸發(fā)器bef_emp_updatebef_emp_update觸發(fā)器在UPDATE員工表前觸發(fā)，若新工資超過10000則強制設為10000，實現(xiàn)統(tǒng)一薪酬封頂策略。02BEFORE時機通過BEFORE時機攔截非法值，避免事后回滾，提高性能，適用于價格、折扣、積分等字段的上限或下限保護。03業(yè)務規(guī)則強制觸發(fā)器將業(yè)務規(guī)則下沉至數(shù)據(jù)層，確保無論通過何種渠道訪問數(shù)據(jù)，規(guī)則均被強制執(zhí)行，杜絕繞過應用直庫修改帶來的風險。項目六

MySQL數(shù)據(jù)庫中的程序設計知識回顧項目六

MySQL數(shù)據(jù)庫中的程序設計常量與變量常量類型：字符串、數(shù)值、時間日期、布爾；變量分類：用戶變量、系統(tǒng)變量、局部變量，掌握其使用方法和作用域。局部變量在存儲過程中使用DECLARE語句定義?？刂普Z句條件分支：IF...ELSE、CASE；循環(huán)：WHILE、REPEAT、LOOP，理解其在數(shù)據(jù)庫編程中的應用，提升邏輯思維能力。存儲過程與函數(shù)存儲過程由一組SQL語句構(gòu)成，用于執(zhí)行特定任務，通過調(diào)用名稱來執(zhí)行。創(chuàng)建存儲過程的關(guān)鍵字是CREATEPROCEDURE。觸發(fā)器在特定的數(shù)據(jù)庫操作發(fā)生時自動執(zhí)行的存儲過程，創(chuàng)建觸發(fā)器的語句關(guān)鍵字是CREATETRIGGER，可以使用NEW和OLD關(guān)鍵字。觸發(fā)器的創(chuàng)建、管理與應用場景，理解其在數(shù)據(jù)一致性、業(yè)務規(guī)則強制方面的關(guān)鍵作用，提升數(shù)據(jù)庫設計能力。項目六

MySQL數(shù)據(jù)庫中的程序設計知識回顧感謝您的觀看THANKYOUFORREADING！項目七

MySQL數(shù)據(jù)庫的安全管理《數(shù)據(jù)庫應用與數(shù)據(jù)分析（MySQL）》課程目標目錄CONTENTS任務1MySQL用戶管理任務2MySQL權(quán)限管理任務3MySQL數(shù)據(jù)備份與恢復知識回顧項目七

MySQL數(shù)據(jù)庫的安全管理課程目標項目七

MySQL數(shù)據(jù)庫的安全管理知識目標：能力目標：素質(zhì)目標：理解MySQL用戶管理的基本概念掌握MySQL權(quán)限管理的分層機制與操作規(guī)范熟悉MySQL數(shù)據(jù)備份與恢復的操作具備MySQL用戶管理操作的能力具備MySQL權(quán)限管理操作的能力具備MySQL數(shù)據(jù)備份與還原操作的能力提高學習者的數(shù)據(jù)安全意識，建立數(shù)據(jù)安全思維培養(yǎng)學習者嚴謹?shù)臄?shù)據(jù)保護與隱私保護習慣項目七

MySQL數(shù)據(jù)庫的安全管理任務1MySQL用戶管理項目七

MySQL數(shù)據(jù)庫的安全管理MySQL的用戶管理是數(shù)據(jù)庫安全的第一道防線，通過創(chuàng)建唯一標識的用戶賬戶、配置強密碼策略和動態(tài)管理用戶權(quán)限，實現(xiàn)對數(shù)據(jù)庫訪問的精準控制。MySQL用戶管理是實現(xiàn)訪問控制、保障數(shù)據(jù)庫安全的關(guān)鍵環(huán)節(jié)，通過合理設置用戶賬戶和密碼策略，有效防止未授權(quán)訪問。用戶管理的重要性任務分析項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】一、用戶賬戶的組成用戶名是用戶身份的標識，格式需符合MySQL規(guī)定，大小寫敏感，限制長度，確保唯一性。其長度受限于權(quán)限表中字段的長度，默認最多為32字符。用戶名主機名表示用戶訪問數(shù)據(jù)庫的來源主機，與用戶名結(jié)合使用，可限制用戶訪問范圍，增強安全性。在創(chuàng)建用戶時，通常把用戶名與主機名結(jié)合起來使用，以此指定用戶能夠從哪些主機連接到MySQL服務器。主機名密碼是用戶身份驗證的重要憑證，MySQL采用加密存儲方式，密碼策略可設置復雜度、有效期等，保障賬戶安全。密碼例如，'username'@'localhost'項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】二、密碼策略密碼有效期是指設置密碼的最長使用期限。設置密碼有效期可定期提醒用戶更換密碼，降低密碼泄露風險，過期后需重新設置密碼才能訪問數(shù)據(jù)庫。密碼有效期密碼加密是將用戶密碼通過特定的加密算法轉(zhuǎn)換為不可逆的密文形式存儲的過程。密碼加密是保障存儲安全的關(guān)鍵，MySQL支持多種加密插件，加密原理是將密碼轉(zhuǎn)換為不可逆的哈希值。密碼加密密碼復雜度要求包括長度、字符種類等，MySQL通過內(nèi)置策略或插件實現(xiàn)，確保密碼難以破解。密碼復雜度通常包括以下規(guī)則：最小長度、混合字符類型（必須包含大小寫字母、數(shù)字和特殊字符）、避免常見密碼（如“123456”或“password”）、避免與用戶名或個人信息相關(guān)。項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】三、使用SQL語句管理用戶SQL語法格式：CREATEUSER用戶名@主機地址[IDENTIFIEDBY'密碼'];（1）可以同時創(chuàng)建多個用戶，IDENTIFIEDBY語句用于在MySQL中為用戶設置或更改密碼，將指定的明文密碼加密后存儲到系統(tǒng)表中。（2）用戶名@主機地址：主機地址不區(qū)分大小寫。主機地址指的是創(chuàng)建的用戶從哪個地址連接服務器?？梢允褂肐P地址，如果是本機使用LOCALHOST，IP地址是

。1.添加用戶SQL語法格式：RENAMEUSER舊用戶名@主機地址TO新用戶名@主機地址;（1）可以對多個用戶名重命名，中間使用“,”隔開。（2）如果舊用戶名不存在，或新用戶名已經(jīng)存在，則重命名不會成功。2.修改用戶項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】SQL語法格式：ALTERUSER用戶名@主機地址[IDENTIFIEDBY'密碼'];3.修改用戶密碼SQL語法格式：SELECT*FORMMYSQL.USER;4.查看用戶4.刪除用戶SQL語法格式：DROPUSER用戶名@主機名;項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】任務操作1：創(chuàng)建用戶2.圖形化界面操作步驟在Navicat17中，打開用戶管理界面，新建用戶，填寫用戶名、主機名、密碼等信息，查看SQL預覽，確保操作正確。1.使用SQL語句主要SQL語句：使用CREATEUSER語句創(chuàng)建用戶，格式為：CREATEUSERtest@localhostIDENTIFIEDBY'123456';任務要求：

使用SQL語句創(chuàng)建用戶test@localhost，密碼為123456，使用圖形化工具創(chuàng)建用戶test1@localhost，密碼為654321。

項目七

MySQL數(shù)據(jù)庫的安全管理【任務操作】在用戶管理界面選擇目標用戶，編輯用戶名、主機名、密碼等信息，查看SQL預覽，完成修改操作。任務操作2：修改用戶2.圖形化界面操作步驟主要SQL語句如下：RENAMEUSER'test1'@'localhost'TO'test2'@'localhost';ALTERUSER'test2'@'localhost'IDENTIFIEDBY'111111';1.使用SQL語句任務要求：

修改用戶test1@localhost為test2@localhost，密碼修改為111111。

項目七

MySQL數(shù)據(jù)庫的安全管理【任務操作】任務操作3：刪除用戶1.圖形化界面操作在Navicat17中，打開用戶管理界面，可以直接刪除用戶。2.使用SQL語句主要SQL語句：DROPUSERtest@localhost；任務要求：

使用圖形化工具NavicatPremium，刪除test2用戶。

項目七

MySQL數(shù)據(jù)庫的安全管理【任務操作】【任務實訓】【實訓1】使用圖形化操作界面管理用戶。實訓要求：

（1）使用圖形化操作界面創(chuàng)建一個名為“ceshi1”的用戶，密碼為“666666”。（2）使用圖形化操作界面修改用戶名為“ceshi2”，主機為“0”。202（3）使用圖形化操作界面刪除用戶“ceshi1”?！緦嵱?】使用SQL語句管理用戶。實訓要求：

（1）使用SQL語句創(chuàng)建兩個用戶，分別為“user10”和“user11”，密碼均為“123456”。（2）使用SQL語句將兩個用戶名修改為“test1”和“test2”，密碼均為“888888”。（3）使用SQL語句刪除用戶“test1”。項目七

MySQL數(shù)據(jù)庫的安全管理任務2MySQL權(quán)限管理項目七

MySQL數(shù)據(jù)庫的安全管理權(quán)限管理是數(shù)據(jù)庫安全的核心，通過合理分配和回收權(quán)限，控制用戶對數(shù)據(jù)庫資源的訪問，遵循最小權(quán)限原則和動態(tài)管理原則，確保數(shù)據(jù)安全。用戶管理的核心機制任務分析項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】一、權(quán)限的概念與范圍權(quán)限概念常見權(quán)限包括全局權(quán)限、數(shù)據(jù)庫權(quán)限、表權(quán)限、列權(quán)限及存儲過程和函數(shù)權(quán)限，每種權(quán)限對應不同的操作權(quán)限。權(quán)限范圍全局權(quán)限作用于整個數(shù)據(jù)庫服務器；數(shù)據(jù)庫權(quán)限作用于特定數(shù)據(jù)庫；表權(quán)限作用于特定表；列權(quán)限作用于特定列；存儲過程和函數(shù)權(quán)限作用于特定存儲過程或函數(shù)。0102項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】最小權(quán)限原則動態(tài)管理根據(jù)用戶角色和職責的變化，動態(tài)調(diào)整權(quán)限，確保權(quán)限與用戶職責相匹配，適應組織架構(gòu)和業(yè)務流程的變化。0102二、權(quán)限管理的原則最小權(quán)限原則是指僅授予用戶完成工作所需的最小權(quán)限，避免過度授權(quán)，降低安全風險。項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】三、授予用戶權(quán)限SQL語法格式：GRANT權(quán)限名ONTABLE數(shù)據(jù)庫.表名TO用戶名@主機名;（1）權(quán)限名：權(quán)限的名稱，常見的有SELECT、INSERT、UPDATE、DELETE等，且可以用半角逗號隔開，實現(xiàn)多個權(quán)限一次添加。（2）ON語句：指定表名，也可使用“數(shù)據(jù)庫名.*”來進行數(shù)據(jù)庫權(quán)限的添加。（3）TO語句：指定用戶，也可使用“,”隔開，進行多個用戶權(quán)限的同時添加。1.給數(shù)據(jù)表添加權(quán)限SQL語法格式：GRANT權(quán)限名(字段名)ONTABLE數(shù)據(jù)庫.表名TO用戶@主機名;（1）權(quán)限名：權(quán)限的名稱，“字段名”為要添加權(quán)限的字段，且可以用“,”隔開，進行多列一次添加。（2）單獨添加列權(quán)限后，指定用戶只能進行符合權(quán)限的列的操作。2.給數(shù)據(jù)表的某列添加權(quán)限項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】SQL語法格式：REVOKE權(quán)限名ONTABLE數(shù)據(jù)庫.表名FROM用戶名@主機名;REVOKE權(quán)限名（字段名）ONTABLE數(shù)據(jù)庫.表名FROM用戶名@主機名;3.撤銷用戶權(quán)限SQL語法格式：SHOWGRANTS;4.查看用戶4.權(quán)限立即生效SQL語法格式：FLUSHPRIVILEGES;項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】任務操作1：授予權(quán)限在Navicat17中，選擇目標用戶，指定權(quán)限對象（如表、列等），查看SQL預覽，完成權(quán)限分配。圖形化界面操作步驟使用GRANT語句授予權(quán)限，格式為GRANTprivilegesONobjectTO'username'@'host'，示例：GRANTINSERT('E-amil')ONTABLEGOODS.供應商TO'test'@'localhost'。SQL語句12任務要求：

授予用戶test在“供應商”表中的ALTER、DELETE、DROP、INDEX等權(quán)限，并為“E-mail”字段添加INSERT權(quán)限。

項目七

MySQL數(shù)據(jù)庫的安全管理【任務操作】任務操作2：權(quán)限撤銷圖形化界面操作步驟在權(quán)限管理界面選擇要撤銷的權(quán)限，點擊刪除按鈕，查看對應的SQL預覽，完成權(quán)限撤銷。SQL語句使用REVOKE語句撤銷權(quán)限，格式為：REVOKEprivilegesONobjectFROM'username'@'host'，示例：GRANTINSERT('E-amil')ONTABLEGOODS.供應商TO'test'@'localhost'。0102在命令行輸入“SHOWGRANTS;”語句查看當前用戶權(quán)限項目七

MySQL數(shù)據(jù)庫的安全管理【任務操作】【任務實訓】【實訓】使用圖形化操作界面創(chuàng)建用戶“test10”，并為其添加goods數(shù)據(jù)庫中“入庫記錄”表的查詢和刪除權(quán)限。實訓要求：

（1）使用圖形化操作界面創(chuàng)建一個名為test10的用戶。（2）使用圖形化操作界面為用戶“test10”添加SELECT、DELETE權(quán)限。（3）使用用戶“test10”連接數(shù)據(jù)庫并查詢“入庫記錄”表的內(nèi)容。項目七

MySQL數(shù)據(jù)庫的安全管理任務3MySQL數(shù)據(jù)備份與還原項目七

MySQL數(shù)據(jù)庫的安全管理任務分析數(shù)據(jù)備份與恢復的重要性數(shù)據(jù)備份與恢復是防止數(shù)據(jù)丟失、確保業(yè)務連續(xù)性、應對災難恢復的重要手段，保障數(shù)據(jù)的完整性和可用性。項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】一、數(shù)據(jù)備份數(shù)據(jù)備份分為邏輯備份和物理備份，邏輯備份通過導出SQL文件實現(xiàn)，物理備份直接備份數(shù)據(jù)庫文件，備份工具如mysqldump，備份文件用于數(shù)據(jù)恢復。數(shù)據(jù)備份（1）備份單個數(shù)據(jù)庫，命令格式如下。mysqldump-u用戶名-p密碼

數(shù)據(jù)庫名>存儲路徑\備份文件名.sql（2）備份多個數(shù)據(jù)庫，命令格式如下。mysqldump-u用戶名-p密碼--databases數(shù)據(jù)庫名1[數(shù)據(jù)庫名2......]>存儲路徑\備份文件名.sql（3）備份所有數(shù)據(jù)庫，命令格式如下。mysqldump-u用戶名-p密碼--all-databases>存儲路徑\備份文件名.sql語句說明如下。（1）用戶名/密碼：MySQL數(shù)據(jù)庫的用戶名/密碼。（2）>存儲路徑\備份文件名.sql：將結(jié)果輸出到“備份文件名.sql”文件，便于恢復。項目七

MySQL數(shù)據(jù)庫的安全管理【知識解析】二、數(shù)據(jù)還原數(shù)據(jù)還原是指在數(shù)據(jù)丟失或損壞時，將備份文件重新導入數(shù)據(jù)庫，以還原數(shù)據(jù)的過程。數(shù)據(jù)還原數(shù)據(jù)備份和還原都可以用兩種方式實現(xiàn)：一是使用圖形化管理工具NavicatPremium二是使用mysqldump工具，該程序是MySQL數(shù)據(jù)庫所提供的一個WINDOWS備份程序。命令格式如下。Mysql-u用戶名-p密碼[數(shù)據(jù)庫名]<備份文件名.sql語句說明如下。（1）[數(shù)據(jù)庫名]：可選項，導入單個數(shù)據(jù)庫的備份，需要指定數(shù)據(jù)庫名；導入多個數(shù)據(jù)庫的備份，無須指定數(shù)據(jù)庫名。（2）<備份文件名