詳解Mysql事務、索引、視圖

標籤：訪問   zha   依賴   資料庫   資料表   出現   邏輯   into   column   

索引簡介

索引是為了加速對錶中資料行的檢索而建立的一種分散的儲存結構。索引是針對錶而建立的，它是由資料頁面以外的索引頁面組成的，每個索引頁面中的行都會含有邏輯指標，以便加速檢索物理資料。

索引作用

1、設定了合適的索引之後，資料庫利用各種快速的定位技術，能夠大大加快查詢速率
2、特別是當表很大時，或者查詢設計到多表時，使用索引可使查詢加快千倍
3、可以降低資料庫的IO（輸入輸出）成本，並且索引還可以降低資料庫的排序成本，注意讀的時候會消耗記憶體
4、通過建立唯一性索引保證資料表資料的唯一性
5、可以加快表與表之間的串連，注意可以把相關的欄位當做一個索引
6、在使用分組的排序時，可大大減少分組和排序時間

索引的分類普通索引

這是最基本的索引類型，而且它沒有唯一性之類的限制
唯一性索引
這種索引和前面"普通索引"基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須唯一，允許為空白，只能有一個值為空白

主鍵索引

這是一種唯一性索引，但它必須指定為"PRIMARY KEY"

全文索引

MySQL3.23.23版開始支援全文索引和全文檢索索引。在MySQL中，全文索引的索引類型為FULLTEXT,全文索引可以再VARCHAR或者TEXT類型的列上建立

單列索引與多列索引

索引可以是單列上建立的索引，也可以是在多列上建立的索引

事務簡介

事務是一種機制、一個操作序列,包含了一組資料庫操作命令,並且把所有的命令作為一個整體一起向系統提交或撤銷操作請求,即這一組資料庫命令要麼都執行,要麼都不執行。事務是一個不可分割的工作邏輯單元,在資料庫系統上執行並行作業時,事務是最小的控制單元，適用於多使用者同時操作的資料庫系統的情境，如銀行、保險公司及證券交易系統等等，通過事務的整體性以保證資料的一致性。

事務特點

1、原子性
事務是一個完整的操作，事物的各元素是不可分的，事務中的所有元素必須作為一個整體提交或復原，如果事務中的任何元素失敗，則整個事務將失敗
2、一致性
當事務完成時，資料必須處於一致狀態：在事務開始之前，資料庫中儲存的資料處於一致狀態；在進行中的事務中，資料可能處於不一致的狀態；當事務成功完成時，資料必須再次回到已知的一致性，注意是前後結果保持一致。
3、隔離性
對資料進行修改的所有並發事務時彼此隔離的，這表明事務必須是獨立的，它不應以任何方式依賴於或影響其他事務。修改資料的事務可以在另一個使用相同資料的事務開始之前訪問這些資料，或者在另一個使用相同資料的事務結束之後訪問這些資料，注意事務之間彼此隔離
4、持久性
事務的持久性不管系統是否發生了故障，交易處理的結果都是永久的，一旦事務被提交，事務的效果會被永久地保留在資料庫中，也就是寫入系統磁碟中

視圖簡介

視圖是資料庫中的一張虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行資料。但是，視圖並不在資料庫中以儲存的資料值集形式存在。行和列資料來自由定義視圖的查詢所引用的表，並且在引用視圖時動態產生。可以理解為軟連結

視圖作用

1、為使用者集中資料，簡化使用者的資料查詢和處理。
2、屏蔽資料庫的複雜性，使用者不必瞭解資料庫的複雜性。
3、簡化使用者權限的管理，只授予使用者使用視圖的許可權。
4、便於資料共用，多個使用者不必都定義所需的資料。
5、可以重新組織資料，以便關聯到其他應用中。

命令步驟一、索引1、普通索引

mysql> create table student
(id int(3),
name varchar(10), #姓名
score decimal(5,2), #分數
adrress varchar(50), #地址
hobbyid int(3) #關聯hobby(興趣愛好表)
); #建立一張student表

mysql> create index id_index on student(id); #將student表中id欄位建立成普通索引

mysql> show index from student\G; #查看索引

mysql> drop index id_index on student; #刪除索引

2、主鍵索引

mysql> alter table student add primary key(id); #使用alter命令修改表結構，將id更改為主鍵
mysql> show index from student\G; #查看索引

mysql> alter table student drop primary key; #刪除student表主鍵

mysql> create table info(id int not null primary key auto_increment,name varchar(10)); #通過建立表的時候建立主鍵列對應的也是主鍵索引

mysql> show index from info\G;

mysql> alter table info drop primary key; #刪除student表主鍵

mysql> drop table info; #刪除info表

3、唯一索引

mysql> create unique index name_index on student(name); #將student表中name欄位建立成唯一索引
mysql> show index from student\G;

mysql> drop index name_index on student; ##刪除唯一索引

mysql> create table info(id int not null,name varchar(10) unique); #通過建立表的時候建立唯一鍵對應的也是唯一索引

mysql> show index from info\G;

mysql> alter table info drop index name; #刪除索引

mysql> show index from info\G; #刪除info表

4、全文索引

mysql> alter table student add column descript TEXT; #向student表中添加descript欄位，類型為TEXT
mysql> desc student;

mysql> alter table student add FULLTEXT index desc_index(descript);#將student表中descript欄位建立成全文索引
mysql> show index from student\G;

mysql> drop index desc_index on student; #刪除student表全文索引

mysql> create table info(descript text,FULLTEXT(descript));#通過建立表的時候建立全文索引

mysql> alter table info drop index descript; #刪除info表全文索引

mysql> drop table info; #刪除info表

5、單列索引、多列索引

關於單列索引，以上例子都是單列索引，略……
mysql> create index duo_index on student(id,hobbyid); #將student表中id、hobbyid兩欄位建立成多列索引

mysql> drop index duo_index on student; #刪除student表多列索引

mysql> create table info(id int,name varchar(10),index duo_index(id,name));#通過建立表的時候將id,name欄位設定為多列索引

mysql> show index from info\G;

mysql> drop index duo_index on student; #刪除student表多列索引

mysql> drop index duo_index on info; #刪除多列索引

mysql> drop table info; #刪除info表

二、事務1、提交事務

mysql> select * from student;

mysql> begin; #開啟事務
mysql> insert into student values(1,‘zhangsan‘,50,‘gz‘,1,‘good‘);
mysql> insert into student values(2,‘lisi‘,40,‘zz‘,1,‘good‘);
mysql> insert into student values(3,‘wangwu‘,40,‘zz‘,1,‘good‘);
mysql> commit; #提交事務

mysql> select * from student;

2、復原事務

mysql> begin;
mysql> insert into student values(4,‘zhaoliu‘,50,‘gz‘,1,‘good‘);
mysql> insert into student values(5,‘tianqi‘,50,‘gz‘,1,‘good‘);
mysql> insert into student values(6,‘qianba‘,50,‘gz‘,1,‘good‘);
mysql> rollback; #復原事務

mysql> select * from student;

3、指定復原點

mysql> begin;
mysql> insert into student values(4,‘t01‘,30,‘gz‘,1,‘good‘);
mysql> savepoint s1;
mysql> insert into student values(5,‘t02‘,20,‘gz‘,1,‘good‘);
mysql> savepoint s2;
mysql> insert into student values(6,‘t03‘,50,‘gz‘,1,‘good‘);
mysql> savepoint s3;
mysql> rollback to savepoint s2; #將交易回復至s2標記下
mysql> commit; #提交

mysql> select * from student;

三、視圖1、建立視圖

mysql> create view stu_view as select * from student where score > 30;

2、查看視圖詳細資料

mysql> show create view stu_view\G;

3、查看視圖資料資訊

mysql> select * from stu_view;

4、修改視圖

mysql> alter view stu_view as select from student where score > 40;
mysql> select from stu_view;

5、修改資料

mysql> update stu_view set score=100 where id=1; #修改視圖，score為100
mysql> select * from stu_view;

mysql> select * from student;

6、刪除視圖

mysql> drop view stu_view;

詳解Mysql事務、索引、視圖