數(shù)據(jù)庫技術(shù)三：索引，視圖，存儲過程，觸發(fā)器，數(shù)據(jù)控制，數(shù)據(jù)備份與恢復(fù)

MySQL 索引

  • 什么是索引？

-- ???索引就是排好序的,幫助我們進(jìn)行快速查找的數(shù)據(jù)結(jié)構(gòu).
-- ???簡單來講，索引就是一種將數(shù)據(jù)庫中的記錄按照特殊形式存儲的數(shù)據(jù)結(jié)構(gòu)。通過索引，能夠顯著地提高數(shù)據(jù)查詢的效率，從而提升服務(wù)器的性能.
-- ???專業(yè)一點(diǎn)來說呢，索引是一個排好序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址。在數(shù)據(jù)庫十分龐大的時候，索引可以大大加快查詢的速度，這是因?yàn)槭褂盟饕罂梢圆挥脪呙枞韥矶ㄎ荒承械臄?shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應(yīng)的物理地址然后訪問相應(yīng)的數(shù)據(jù)。
-- ???MySQL 將一個表的索引都保存在同一個索引文件中，如果對其中的數(shù)據(jù)進(jìn)行增刪改操作，MySQL 都會自動的更新索引。

  • 常見索引分類

Primary Key -- 主鍵索引
???主鍵是一種唯一性索引，每個表只能有一個主鍵，用于標(biāo)識數(shù)據(jù)表中的每一條記錄。

???一個表可以沒有主鍵，但最多只能有一個主鍵，并且主鍵值不能包含 NULL。

-- 為 demo1 表添加主鍵索引
-- 創(chuàng)建 demo01 表
CREATE TABLE demo01(
    did INT,
    dname VARCHAR(20),
    hobby VARCHAR(30)
);
ALTER TABLE demo01 ADD PRIMARY KEY (did);

-- 刪除表
DROP TABLE demo01;

-- 創(chuàng)建表的時候直接添加主鍵索引 (最常用)
CREATE TABLE demo01(
    did INT PRIMARY KEY,
    dname VARCHAR(20),
    hobby VARCHAR(30)
);

Unique -- 唯一索引
???唯一索引指的是索引列的所有值都只能出現(xiàn)一次，必須唯一。

???唯一索引可以保證數(shù)據(jù)記錄的唯一性，而且索引的效率也提升了。事實(shí)上，創(chuàng)建唯一索引的目的往往不是為了提高訪問速度，而只是為了避免數(shù)據(jù)出現(xiàn)重復(fù)。

-- 使用 create 語句添加唯一索引
CREATE UNIQUE INDEX index_hobby ON demo01(hobby)

-- 創(chuàng)建表的時候直接添加主鍵索引
CREATE TABLE demo02(  
    did INT PRIMARY KEY,
    dname VARCHAR(20),
    hobby VARCHAR(30),
   UNIQUE index_hobby (hobby)
);

-- 通過表結(jié)構(gòu)刪除索引
ALTER TABLE demo02 
DROP INDEX index_hobby;

-- 通過表結(jié)構(gòu)添加索引
-- 索引名字可省略，會自動生成名字
ALTER TABLE demo02 
ADD UNIQUE (hobby);

-- 向表中插入數(shù)據(jù)
INSERT INTO demo01 VALUES(1,'張三','運(yùn)動');
-- 報錯：Duplicate entry 'DBJ' for key 'hobby'
INSERT INTO demo01 VALUES(2,'李四','運(yùn)動');

index -- 普通索引
???最常見的索引，作用就是加快對數(shù)據(jù)的訪問速度。

???只為那些最經(jīng)常出現(xiàn)在查詢條件或排序條件中的數(shù)據(jù)列創(chuàng)建普通索引。

-- 使用 create 語句添加唯一索引
CREATE INDEX dname_index ON demo01(dname);

-- 通過表結(jié)構(gòu)刪除索引
ALTER TABLE demo01 
DROP INDEX dname_index;

-- 通過表結(jié)構(gòu)添加索引
ALTER TABLE demo01 
ADD INDEX dname_index(dname);

刪除索引
???由于索引會占用一定的磁盤空間，因此，為了避免影響數(shù)據(jù)庫的性能，應(yīng)該及時刪除不再使用的索引

-- 刪除  demo01 表中名為  dname_indx 的普通索引。
ALTER TABLE demo01 DROP INDEX dname_indx;

索引的優(yōu)缺點(diǎn)總結(jié)

  • 添加索引首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
  • 優(yōu)點(diǎn)：1. 大大的提高查詢速度；2. 可以顯著的減少查詢中分組和排序的時間。
  • 缺點(diǎn)：1. 創(chuàng)建索引和維護(hù)索引需要時間，而且數(shù)據(jù)量越大時間越長。2. 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、修改、刪除的時候，索引也要同時進(jìn)行維護(hù)，降低了數(shù)據(jù)的維護(hù)速度。

MySQL 視圖

  • 什么是視圖

1 . 視圖是一種虛擬表。
2 . 視圖建立在已有表的基礎(chǔ)上，視圖賴以建立的這些表稱為基表。
3 . 向視圖提供數(shù)據(jù)內(nèi)容的語句為 SELECT 語句，可以將視圖理解為存儲起來的 SELECT 語句。
4 . 視圖向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式。

  • 視圖的作用

???視圖可以在權(quán)限控制時使用。比如，某幾個列可以運(yùn)行用戶查詢，其他列不允許，可以開通視圖查詢特定的列，起到權(quán)限控制的作用。

???視圖可以簡化復(fù)雜的多表查詢。視圖本身就是一條查詢 SQL，可以將一次復(fù)雜的查詢構(gòu)建成一張視圖，用戶只要查詢視圖就可以獲取想要得到的信息（不需要再編寫復(fù)雜的 SQL）。

  • 視圖的使用

???創(chuàng)建視圖

  • 語法格式：--> create view 視圖名 [column_list] as select語句;
    -- view: 表示視圖；
    -- column_list: 可選參數(shù)，表示屬性清單，指定視圖中各個屬性的名稱，默認(rèn)情況下，與SELECT語句中查詢 的屬性相同 ；
    -- as : 表示視圖要執(zhí)行的操作 ；
    -- select語句: 向視圖提供數(shù)據(jù)內(nèi)容；
-- 創(chuàng)建視圖
-- 先編寫查詢語句
-- 查詢所有商品和商品的對應(yīng)分類信息
SELECT * 
FROM 
products p 
    LEFT JOIN 
category c 
    ON 
    p.`category_id` = c.`cid`;

-- 基于上面的查詢語句，創(chuàng)建視圖
CREATE VIEW products_category_view 
AS 
    SELECT * 
    FROM 
        products p 
        LEFT JOIN 
        category c 
        ON 
        p.`category_id` = c.`cid`;

-- 查詢視圖，當(dāng)做一張只讀的表來操作
SELECT * FROM products_category_view;

通過視圖進(jìn)行查詢

-- 需求1：查詢各個分類下的商品平均價格
-- 方式一：通過多表查詢
SELECT 
    cname AS '分類名稱',
    AVG(p.`price`) AS '平均價格'
FROM 
products p 
    LEFT JOIN 
category c 
    ON 
    p.`category_id` = c.`cid`
GROUP BY c.`cname`;
-- 方式二：通過視圖查詢省略了連接表的操作
SELECT 
    cname AS '分類名稱',
    AVG(price) AS '平均價格'
FROM products_category_view 
GROUP BY cname;


-- 需求2：查詢鞋服分類下最貴的商品的全部信息
-- 方式一：通過連表查詢
SELECT * 
FROM 
products p 
    LEFT JOIN 
category c 
    ON 
    p.`category_id` = c.`cid`
WHERE 
    c.`cname` = '鞋服' 
    AND 
    p.`price` = (
        SELECT 
            MAX(price) AS maxPrice
        FROM 
        products p 
            LEFT JOIN 
        category c 
            ON 
            p.`category_id` = c.`cid`
        WHERE c.`cname` = '鞋服');
-- 方式二：通過視圖查詢
SELECT * 
FROM products_category_view pcv 
WHERE 
    pcv.`cname` = '鞋服'
    AND 
    pcv.`price` = (
        SELECT MAX(price) 
        FROM products_category_view 
        WHERE cname = '鞋服'
    );

視圖與表的區(qū)別
-- 視圖是建立在表的基礎(chǔ)上，表存儲數(shù)據(jù)庫中的數(shù)據(jù)，而視圖只是做一個數(shù)據(jù)的展示。

-- 通過視圖不能改變表中數(shù)據(jù)（一般情況下視圖中的數(shù)據(jù)都是表中的列經(jīng)過計算得到的結(jié)果，不允許更新）。

-- 刪除視圖，表不受影響，而刪除表，視圖不再起作用。

MySQL 存儲過程

  • 什么是存儲過程

MySQL 5.0 版本開始支持存儲過程。

存儲過程是一種在數(shù)據(jù)庫中存儲復(fù)雜程序，以便外部程序調(diào)用的一種數(shù)據(jù)庫對象。存儲過程是為了完成特定功能的 SQL 語句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中，用戶在需要時可通過指定存儲過程的名字并給定參數(shù)來調(diào)用執(zhí)行。

簡單來說，存儲過程其實(shí)就是一堆 SQL 語句的合并，中間加入了一些邏輯控制。

  • 存儲過程的優(yōu)缺點(diǎn)

優(yōu)點(diǎn):
???存儲過程一旦調(diào)試完成后，就可以穩(wěn)定運(yùn)行。前提是業(yè)務(wù)需求要相對穩(wěn)定沒有變化。
???存儲過程減少業(yè)務(wù)系統(tǒng)與數(shù)據(jù)庫的交互，降低耦合，使數(shù)據(jù)庫交互更加快捷，適合應(yīng)用服務(wù)器與數(shù)據(jù)庫服務(wù)器不在同一個地區(qū)的情況。

缺點(diǎn):
???互聯(lián)網(wǎng)行業(yè)需求變化較快，MySQL 的存儲過程與 Oracle 相比較弱，所以較少使用。
???存儲過程可以在簡單的邏輯中使用，但是移植十分困難。特別是在數(shù)據(jù)庫集群環(huán)境，要保證各個庫之間存儲過程變更一致也十分困難。阿里巴巴的代碼規(guī)范里也提出了禁止使用存儲過程，因?yàn)榇鎯^程維護(hù)起來的確麻煩。

  • 存儲過程的創(chuàng)建方式

-- 創(chuàng)建商品表與訂單表
-- 商品表，num 為庫存
CREATE TABLE goods(
    gid INT,
    NAME VARCHAR(20),
    num INT
);
-- 訂單表，price 為訂單價格
CREATE TABLE orders(
    oid INT,
    gid INT,
    price INT
);
-- 向商品表中添加3條數(shù)據(jù)
INSERT INTO goods VALUES(1,'奶茶',20);
INSERT INTO goods VALUES(2,'綠茶',100);
INSERT INTO goods VALUES(3,'花茶',25);


-- 方式一：編寫簡單存儲過程
-- 查詢所有商品數(shù)據(jù)
-- 一般使用 $$ 為語句結(jié)束符
DELIMITER $$   
CREATE 
    PROCEDURE 
goods_proc()  
BEGIN   
    select * from goods; 
END $$

-- 調(diào)用存儲過程查詢 goods 表數(shù)據(jù)
call goods_proc;

-- 方式二：創(chuàng)建接收參數(shù)的存儲過程
-- 接收一個商品 id，根據(jù) id 刪除數(shù)據(jù)
DELIMITER $$  
CREATE 
    PROCEDURE 
goods_proc02(
        IN goods_id INT
    )  
BEGIN 
    DELETE FROM goods 
    WHERE gid = goods_id;
END $$

-- 調(diào)用存儲過程傳遞參數(shù)，刪除對應(yīng)的商品
CALL goods_proc02(2);

-- 方式三：使用輸出參數(shù)創(chuàng)建存儲過程
-- 接收參數(shù)插入數(shù)據(jù), 并返回受影響的行數(shù)
DELIMITER $$
CREATE 
    PROCEDURE 
orders_proc(
        IN o_oid INT,
        IN o_gid INT,
        IN o_price INT,
        OUT out_num INT
    )
BEGIN
   -- 執(zhí)行插入操作
    INSERT INTO orders 
    VALUES(o_oid,o_gid,o_price);
   -- 設(shè)置 num 的值為 1
    SET @out_num = 1;
   -- 返回 out_num的值
    SELECT @out_num;
END $$

-- 調(diào)用存儲過程插入數(shù)據(jù)，獲取返回值
CALL orders_proc(1,2,30,@out_num);

MySQL觸發(fā)器

  • 什么是觸發(fā)器

觸發(fā)器是 MySQL 提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比如當(dāng)對一個表進(jìn)行增刪改操作時就會激活它執(zhí)行。

可以把觸發(fā)器理解為：當(dāng)執(zhí)行一條 SQL 語句的時候，這條 SQL 語句的執(zhí)行會自動去觸發(fā)執(zhí)行其他的 SQL 語句。

  • 觸發(fā)器創(chuàng)建的四個要素

監(jiān)視地點(diǎn) -- table
監(jiān)視事件 -- insert/update/delete
觸發(fā)時間 -- before/after
觸發(fā)事件 -- insert/update/delete

  • 創(chuàng)建觸發(fā)器

在一個數(shù)據(jù)庫中觸發(fā)器名是唯一的。

-- 向商品中添加一條數(shù)據(jù)
INSERT INTO goods VALUES(1,'書本',40);

-- 編寫觸發(fā)器
-- 賣出商品之后減少庫存量
-- 修改結(jié)束標(biāo)識，避免執(zhí)行出現(xiàn)錯誤
DELIMITER $
-- 創(chuàng)建觸發(fā)器 t1
CREATE TRIGGER t1
-- 指定觸發(fā)的時機(jī)，和要監(jiān)聽的表 
AFTER INSERT ON orders 
-- 行觸發(fā)器 固定寫法
FOR EACH ROW
# 觸發(fā)后具體要執(zhí)行的事件
BEGIN
    # 訂單+1，庫存-1
    UPDATE goods 
    SET num = num - 1 
    WHERE gid = 1;
END$

-- 向訂單表中添加一條數(shù)據(jù)
# 添加后，觸發(fā)器執(zhí)行，庫存自動減一
INSERT INTO orders VALUES(1,1,25);

DCL(數(shù)據(jù)控制語言)

MySQL 默認(rèn)使用的都是 root 用戶，超級管理員，擁有全部的權(quán)限。除了 root 用戶以外，我們還可以通過 DCL 來定義一些權(quán)限較小的用戶，分配不同的權(quán)限來管理和維護(hù)數(shù)據(jù)庫。

創(chuàng)建用戶

格式：create user '用戶名'@'主機(jī)名' identified by '密碼';

-- 創(chuàng)建 admin1 用戶
   --只能在 localhost 這個服務(wù)器登錄 mySQL 服務(wù)器，密碼為 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';

-- 創(chuàng)建 admin2 用戶
   --可以在任何電腦上登錄 mySQL 服務(wù)器，密碼為 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';

用戶授權(quán)

格式：grant 權(quán)限1，權(quán)限2..... on 數(shù)據(jù)庫名.表名 to '用戶名'@'主機(jī)名'；

-- 給 admin1 用戶分配對 db4 數(shù)據(jù)庫中 products 表的查詢權(quán)限
GRANT SELECT ON db4.products TO 'admin1'@'localhost';

-- 給 admin2 用戶分配所有權(quán)限和操作所有數(shù)據(jù)庫的所有表
GRANT ALL ON *.* TO 'admin2'@'%';

查看用戶權(quán)限

格式：show grant for '用戶名'@'主機(jī)名';

-- 查看 root 用戶的權(quán)限
   -- GRANT ALL PRIVILEGES 是表示所有權(quán)限
SHOW GRANTS FOR 'root'@'localhost';

刪除用戶&查詢用戶

-- 刪除 admin1 用戶
DROP USER 'admin1'@'localhost';

-- 查詢所有用戶
SELECT * FROM USER;

數(shù)據(jù)庫備份&還原

備份的應(yīng)用場景：在服務(wù)器進(jìn)行數(shù)據(jù)傳輸、數(shù)據(jù)存儲、數(shù)據(jù)交換時，就有可能產(chǎn)生數(shù)據(jù)故障。比如，發(fā)生意外停機(jī)或存儲介質(zhì)損壞。 此時，如果沒有采取數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)手段與措施，就會導(dǎo)致數(shù)據(jù)的丟失，造成的損失是無法彌補(bǔ)與估量的。

  • SQLYog 數(shù)據(jù)備份

首先，選中要備份的數(shù)據(jù)庫，右鍵選擇“備份/導(dǎo)出”，選擇“備份數(shù)據(jù)庫”；然后指定文件位置，選擇導(dǎo)出即可。

  • SQLYog 數(shù)據(jù)恢復(fù)

首先，導(dǎo)入備份的 SQL 文件，選中用戶名，右鍵選擇“執(zhí)行 SQL 腳本”；然后選擇文件位置，點(diǎn)擊執(zhí)行即可。

  • 命令行備份

執(zhí)行備份，備份 db1 中的數(shù)據(jù)到 D 盤的 db1.sql 文件中：

mysqldump -uroot -proot db1 > D:/db1.sql

  • 命令行恢復(fù)

還原 db1 數(shù)據(jù)庫中的數(shù)據(jù)（注意：還原的時候需要先創(chuàng)建一個 db1 數(shù)據(jù)庫 ）：

use db1;
source 文件地址
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容