狂神聲明 : 文章均為自己的學習筆記 , 轉載一定註明出處 ; 編輯不易 , 防君子不防小人~共勉 ! mysql學習【第5篇】：事務索引備份視圖 MySQL事務事務就是將一組SQL語句放在同一批次內去執行如果一個SQL語句出錯,則該批次內的所有SQL都將被取消執行 MySQL事務處理只支持In ...

狂神聲明 : 文章均為自己的學習筆記 , 轉載一定註明出處 ; 編輯不易 , 防君子不防小人~共勉 !

mysql學習【第5篇】：事務索引備份視圖

MySQL事務

事務就是將一組SQL語句放在同一批次內去執行
如果一個SQL語句出錯,則該批次內的所有SQL都將被取消執行
MySQL事務處理只支持InnoDB和BDB數據表類型

事務的ACID原則

原子性(Atomic)
- 整個事務中的所有操作，要麼全部完成，要麼全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（ROLLBACK）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
一致性(Consist)
- 一個事務可以封裝狀態改變（除非它是一個只讀的）。事務必須始終保持系統處於一致的狀態，不管在任何給定的時間併發事務有多少。
  也就是說：如果事務是併發多個，系統也必須如同串列事務一樣操作。其主要特征是保護性和不變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶餘額是100元，那麼五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論併發多少個，比如在A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該還是500元，這就是保護性和不變性。
隔離性(Isolated)
- 隔離狀態執行事務，使它們好像是系統在給定時間內執行的唯一操作。如果有兩個事務，運行在相同的時間內，執行相同的功能，事務的隔離性將確保每一事務在系統中認為只有該事務在使用系統。這種屬性有時稱為串列化，為了防止事務操作間的混淆，必須串列化或序列化請求，使得在同一時間僅有一個請求用於同一數據。
持久性(Durable)
- 在事務完成以後，該事務對資料庫所作的更改便持久的保存在資料庫之中，並不會被回滾。

Mysql事務實現方法

/* 使用set語句來改變自動提交模式 */
SET autocommit = 0;   /*關閉*/
SET autocommit = 1;   /*開啟*/

/* 註意
    1.MySQL中預設是自動提交
    2.使用事務時應先關閉自動提交
*/

/*開始一個事務,標記事務的起始點*/
START TRANSACTION   

/*提交一個事務給資料庫*/
COMMIT

/*將事務回滾,數據回到本次事務的初始狀態*/
ROLLBACK

/*還原MySQL資料庫的自動提交*/
SET autocommit =1;

-- 保存點
    SAVEPOINT 保存點名稱 -- 設置一個事務保存點
    ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
    RELEASE SAVEPOINT 保存點名稱 -- 刪除保存點

Mysql事務處理步驟

/*
課堂測試題目

A線上買一款價格為500元商品,網上銀行轉賬.
A的銀行卡餘額為2000,然後給商家B支付500.
商家B一開始的銀行卡餘額為10000

創建資料庫shop和創建表account並插入2條數據
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; 
USE `shop`; 

CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

# 轉賬實現
SET autocommit = 0;
START TRANSACTION;
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT;
# rollback;
SET autocommit = 1;

資料庫索引

作用 :

提高查詢速度
確保數據的唯一性
可以加速表和表之間的連接 , 實現表與表之間的參照完整性
使用分組和排序子句進行數據檢索時 , 可以顯著減少分組和排序的時間
全文檢索欄位進行搜索優化.

分類 :

主鍵索引 (Primary Key)
唯一索引 (Unique)
常規索引 (Index)
全文索引 (FullText)

主鍵索引

主鍵 : 某一個屬性組能唯一標識一條記錄

特點 :

最常見的索引類型
確保數據記錄的唯一性
確定特定數據記錄在資料庫中的位置

唯一索引

作用 : 避免同一個表中某數據列中的值重覆

與主鍵索引的區別

主鍵索引只能有一個
唯一索引可能有多個

常規索引

作用 : 快速定位特定數據

註意 :

index 和 key 關鍵字都可以設置常規索引
應加在查詢找條件的欄位
不宜添加太多常規索引,影響數據的插入,刪除和修改操作

全文索引

作用 : 快速定位特定數據

註意 :

只能用於MyISAM類型的數據表
只能用於CHAR , VARCHAR , TEXT數據列類型
適合大型數據集

創建/刪除索引

/*
#方法一：創建表時
    　　CREATE TABLE 表名 (
                欄位名1  數據類型 [完整性約束條件…],
                欄位名2  數據類型 [完整性約束條件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (欄位名[(長度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上創建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (欄位名[(長度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上創建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (欄位名[(長度)]  [ASC |DESC]) ;
                             
                             
#刪除索引：DROP INDEX 索引名 ON 表名字;
#刪除主鍵索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#顯示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`); 

/*EXPLAIN : 分析SQL語句執行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

索引的兩大類型hash與btree

#我們可以在創建上述索引的時候，為其指定索引類型，分兩類
hash類型的索引：查詢單條快，範圍查詢慢
btree類型的索引：b+樹，層數越多，數據量指數級增長（我們就用它，因為innodb預設支持它）

#不同的存儲引擎支持的索引類型也不一樣
InnoDB 支持事務，支持行級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事務，支持表級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事務，支持表級別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事務，支持行級別鎖定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事務，支持表級別鎖定，不支持 B-tree、Hash、Full-text 等索引；

索引準則

索引不是越多越好
不要對經常變動的數據加索引
小數據量的表建議不要加索引
索引一般應加在查找條件的欄位

MySQL備份

資料庫備份必要性
- 保證重要數據不丟失
- 數據轉移
MySQL資料庫備份方法
- mysqldump備份工具
- 資料庫管理工具,如SQLyog
- 直接拷貝資料庫文件和相關配置文件

mysqldump客戶端

作用 :

轉儲資料庫
搜集資料庫進行備份
將數據轉移到另一個SQL伺服器,不一定是MySQL伺服器

語法 :

-- 導出
1. 導出一張表
　　mysqldump -u用戶名 -p密碼 庫名 表名 > 文件名(D:/a.sql)
2. 導出多張表
　　mysqldump -u用戶名 -p密碼 庫名 表1 表2 表3 > 文件名(D:/a.sql)
3. 導出所有表
　　mysqldump -u用戶名 -p密碼 庫名 > 文件名(D:/a.sql)
4. 導出一個庫 
　　mysqldump -u用戶名 -p密碼 -B 庫名 > 文件名(D:/a.sql)

可以-w攜帶備份條件

-- 導入
1. 在登錄mysql的情況下：
　　source  備份文件
2. 在不登錄的情況下
　　mysql -u用戶名 -p密碼 庫名 < 備份文件

視圖

/* 視圖 */ ------------------
什麼是視圖：
    視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。但是，視圖並不在資料庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表，並且在引用視圖時動態生成。
    視圖具有表結構文件，但不存在數據文件。
    對其中所引用的基礎表來說，視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其它資料庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制，通過它們進行數據修改時的限制也很少。
    視圖是存儲在資料庫中的查詢的sql語句，它主要出於兩種原因：安全原因，視圖可以隱藏一些數據，如：社會保險基金錶，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數等，另一原因是可使複雜的查詢易於理解和使用。

-- 創建視圖
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
    - 視圖名必須唯一，同時不能與表重名。
    - 視圖可以使用select語句查詢到的列名，也可以自己指定相應的列名。
    - 可以指定視圖執行的演算法，通過ALGORITHM指定。
    - column_list如果存在，則數目必須等於SELECT語句檢索的列數

-- 查看結構
    SHOW CREATE VIEW view_name 

-- 刪除視圖
    - 刪除視圖後，數據依然存在。
    - 可同時刪除多個視圖。
    DROP VIEW [IF EXISTS] view_name ...

-- 修改視圖結構
    - 一般不修改視圖，因為不是所有的更新視圖都會映射到表上。
    ALTER VIEW view_name [(column_list)] AS select_statement

-- 視圖作用
    1. 簡化業務邏輯
    2. 對客戶端隱藏真實的表結構

-- 視圖演算法(ALGORITHM)
    MERGE        合併
        將視圖的查詢語句，與外部查詢需要先合併再執行！
    TEMPTABLE    臨時表
        將視圖執行完畢後，形成臨時表，再做外層查詢！
    UNDEFINED    未定義(預設)，指的是MySQL自主去選擇相應的演算法。

觸發器

/* 鎖表 */
表鎖定只用於防止其它客戶端進行不正當地讀取和寫入
MyISAM 支持表鎖，InnoDB 支持行鎖
-- 鎖定
    LOCK TABLES tbl_name [AS alias]
-- 解鎖
    UNLOCK TABLES


/* 觸發器 */ ------------------
    觸發程式是與表有關的命名資料庫對象，當該表出現特定事件時，將激活該對象
    監聽：記錄的增加、修改、刪除。

-- 創建觸發器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
    參數：
    trigger_time是觸發程式的動作時間。它可以是 before 或 after，以指明觸發程式是在激活它的語句之前或之後觸發。
    trigger_event指明瞭激活觸發程式的語句的類型
        INSERT：將新行插入表時激活觸發程式
        UPDATE：更改某一行時激活觸發程式
        DELETE：從表中刪除某一行時激活觸發程式
    tbl_name：監聽的表，必須是永久性的表，不能將觸發程式與TEMPORARY表或視圖關聯起來。
    trigger_stmt：當觸發程式激活時執行的語句。執行多個語句，可使用BEGIN...END複合語句結構

-- 刪除
DROP TRIGGER [schema_name.]trigger_name

可以使用old和new代替舊的和新的數據
    更新操作，更新前是old，更新後是new.
    刪除操作，只有old.
    增加操作，只有new.

-- 註意
    1. 對於具有相同觸發程式動作時間和事件的給定表，不能有兩個觸發程式。


-- 字元連接函數
concat(str1[, str2,...])

-- 分支語句
if 條件 then
    執行語句
elseif 條件 then
    執行語句
else
    執行語句
end if;

-- 修改最外層語句結束符
delimiter 自定義結束符號
    SQL語句
自定義結束符號

delimiter ;        -- 修改回原來的分號

-- 語句塊包裹
begin
    語句塊
end

-- 特殊的執行
1. 只要添加記錄，就會觸發程式。
2. Insert into on duplicate key update 語法會觸發：
    如果沒有重覆記錄，會觸發 before insert, after insert;
    如果有重覆記錄並更新，會觸發 before insert, before update, after update;
    如果有重覆記錄但是沒有發生更新，則觸發 before insert, before update
3. Replace 語法 如果有記錄，則執行 before insert, before delete, after delete, after insert