mysql數據庫的高級應用索引、視圖，觸發器的詳細介紹

下文主要給大家帶來mysql數據庫的高級應用索引、視圖，觸發器的詳細介紹，希望這些內容能夠帶給大家實際用處，這也是我編輯mysql數據庫的高級應用索引、視圖，觸發器的詳細介紹這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

索引優缺點

優點

加快訪問速度

加強行的唯一性

缺點

帶索引的表在數據庫中需要更多的存儲空間

操縱數據的命令需要更長的處理時間，因為它們需要對索引進行更新

按照下列標準選擇建立索引的列

頻繁搜索的列

經常用作查詢選擇的列

經常排序、分組的列

經常用作聯接的列（主鍵/外鍵）

請不要使用下面的列創建索引

僅包含幾個不同值的列

表中僅包含幾行

查詢時減少使用*返回全部列，不要返回不需要的列

索引應該盡量小，在字節數小的列上建立索引

WHERE子句中有多個條件表達式時，包含索引列的表達式應置于其他條件表達式之前

避免在ORDER BY子句中使用表達式

根據業務數據發生頻率，定期重新生成或重新組織索引，進行碎片整理 

索引的分類

普通索引

唯一索引 ：主鍵本身就是一鐘特殊的唯一索引

全文索引

單列索引

多列索引

空間索引

準備實驗環境

批量插入記錄：mysql>delimiter $$  //定義分隔符

Mysql>Create procedure autoinsert1()   //創建存儲過程

Begin

Declare i int default 1;

While (1<200000)do

Insert into school.t2 values(i,’ccc’); //這個表必須是事先存在的，

Set i=i+1;

End while;

End$$

Mysql>delimiter;//把分隔符還原回來

Mysql>Call autoinsert1();  //調用存儲過程

創建索引(創建表時)注意事項：一開始就有索引會有個如果你批量導入數據的時候，因為某個字段上存在索引，所以速度會非常慢。

語法：create table 表名(

字段一 數據類型 [完整性約束條件.....],

字段一 數據類型 [完整性約束條件.....],

[unique|fulltext|spatial] index |key [索引名] (字段名[長度] [asc |desc])

); 

創建普通索引創建表時實例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Index index_dept_name (dept_name)

);

查看索引：desc 表名或者show create table表名 \G

創建唯一索引創建表時實例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Unique Index index_dept_name (dept_name)

);

創建全文索引創建表時實例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

fulltext Index index_dept_name (dept_name)

)engin=myisam; 

創建多列索引創建表時實例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Index index_dept_name (dept_name,commnet)

);

模擬查詢分析查詢語法

Explain select * from dept 4 where commnet=’sale’ \G

創建索引(已存在的表create)

復制表的內容和結構  將t2表的所有數據復制到t4表上

Create table t4 select * from t2;

只復制表的結構  將t2表的結構復制到t4表上

Create table t4 select * from t2 where 1=2;

或者create table t5 like t2;

語法：create [unique|fullext|spatial] index 索引名 on 表名(字段名[(長度)][asc|desc]);

創建普通索引實例：

Create index index_dept_name on departmnet (dept_name);

創建唯一索引實例：

Create unique index index_dept_name on departmnet (dept_name);

創建全文索引實例：

Create fulltext index index_dept_name on departmnet (dept_name);

創建多列索引實例：

Create index index_dept_name on departmnet (dept_name,comment);

創建索引(已存在的表alter table)

語法：alter table 表名 add [unique|fullext|spatial] index 索引名 (字段名[(長度)][asc|desc]);

創建普通索引實例：

Alter table department add  index index_dept_name (dept_name); 

創建唯一索引實例：

Alter table department add  unique index index_dept_name (dept_name);

創建全文索引實例：

Alter table department add  fulltext index index_dept_name (dept_name);

創建多列索引實例：

Alter table department add index index_dept_name (dept_name,comment);

管理索引

查看索引

Show create table 表名\G

測試示例

EXPLAIN select * from department where dept_name=’hr’;

刪除索引

Drop index 索引名 on 表名

視圖簡介：安全和簡化操作

Mysql視圖是一張虛擬表，其內容由查詢定義，同真實的表一樣，視圖包括一系列帶有名稱的列和行數據，但是視圖并不在數據庫中以存儲的數據值集形式存在，行和列數據來自由定義視圖的查詢所引用的表，并且在引用視圖的動態生成，對其中所引用的基礎表來說，mysql視圖的作用類似于篩選。定義視圖的篩選可以來自當前或其他數據庫的一個或多個表，或者其他視圖，通過視圖進行查詢沒有任何限制，通過它們進行數據修改時的限制也很少。

視圖是存儲在數據庫中的sql查詢語句，它主要出于兩種原因，安全原因，視圖可以隱藏一些數據，如一些敏感的信息，另一原因是可以使復雜的查詢便于理解和使用

創建視圖

語法一

Create [algorithm={undefined |merge|temptable}]

View 視圖名[(字段1,字段2.....)]  as select語句

[with [caseaded|local] check option]; 

語法二

Create view 視圖名 as select 語句;

實例：

Create database view; //創建一個數據庫，建議創建

Use view

Create view view_user

As select user,host,password from mysql.user;

Select * from view_user;

查看視圖

Show tables 查看視圖名

Use view;

Show tables;

Show tables status

實例：查看view數據庫中視圖以及所有表詳細信息

Show table status from view\G

實例：查看view數據庫中視圖名view_user詳細信息

Show table status from view like “view_user” \G 

查看視圖定義信息

Use view;

Show create view view_user \G

查看視圖結構

Use view;

Desc view_user;

修改視圖

方法一刪除后創建

Use view;

Drop view view_user;

Create view view_user as select user,host from mysqk.user;

Select * from view_user;

方法二：替換原有視圖

語法：create or replace view 視圖名 as select 語句;

實例：use view;

Create or replace view view_user as select user,password from mysql.user;

方法三：alter 修改視圖

語法：alter view 視圖名 as select 語句;

實例：use view;

Alter view view_user as select user,password from mysql.user;

通過視圖操作基表

查詢數據 select    通常都是查詢操作

Select * from view_user;

更新數據update

刪除數據delect

刪除視圖

Drop view 視圖名

實例：

Use view;

Drop view view_user;

觸發器簡介

觸發器(triggers)是一個特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由時間來觸發，比如當對一個表進行操作(insert,delect,update)時就會激活它執行,觸發器經常用于加強數據的完整約束和業務規則等 

例如：當學生表中增加一個學生的信息時，學生的總數就應該同時改變，因此可以針對學生表創建一個觸發器，每次增加一個學生記錄時，就執行一次學生總數的計算操作，從而保障學生總數和記錄數一致性。 

創建觸發器:同一張表最多可以創建六個觸發器

語法：create trigger 觸發器名稱 before|after 觸發事件

On 表名 for each row

Begin

 觸發器程序體;

End

觸發器名稱：//最多64字符

for each row //觸發器的執行間隔,這句話的意思是指的是針對每一行，如果不加就是針對整張表

Insert|update|delete//觸發的事件

On表名//在哪張表上建立觸發器

觸發器程序體：//觸發器所觸發的sql語句，語句可以使用順序，判斷，循環等語句，實現一般程序需要的邏輯功能。 

實例：

創建表

Create table student(

Id int unsigned auto_incremnet primary key not null,

Name varchar(50)

);

unsigned //無符號修飾符

往表里插入數據

Insert into student values(1,’jack’);

創建表2總數表

Create table student_total (total int); 

往數表中插入數據

Insert into student_total values(1);

創建觸發器實例一student_insert_trigger

Mysql>Delimiter && //定義結束符

Mysql>Create trigger student_insert_trigger after insert 

On student for each now

Begin

Upadte student_total set total=total+1;

End&&

Mysql>Delimiter ; 

創建觸發器實例二

Mysql>Delimiter && //定義結束符

Mysql>Create trigger student_insert_trigger after delect

On student for each now

Begin

Upadte student_total set total=total-1;

End&&

Mysql>Delimiter ;

測試效果，往學生表中插入數據，查看總數表結果，

Insert into student values(2,’Alice’);

Select * from student_total ;

查看觸發器

方法1通過show triggers語句查看

案例：show triggers\G

方法2通過系統表triggers查看

案例：use information_schema

Select * from triggers\G

SELECT * FROM TRIGGERS WHERE TGIGGER_NAME=’觸發器名稱’\G

刪除觸發器

Drop trigger 觸發器名稱

實例：Drop trigger student_insert_trigger;

觸發器的案例

案例一：增加tab1表記錄后自動將記錄增加到tab2中,能同步的字段的數據類型肯定要必須一致才行。

創建tab1

Drop table if exists tab1;

Create table tab1(

Tab1_id varchar(11)

); 

創建tab2

Drop table if exists tab2;

Create table tab2(

Tab2_id varchar(11)

);

觸發器：after_tab1_trigger

作用：增加tab1表記錄后自動將記錄增加到tab2中

Mysql>Delimiter && //定義結束符

Mysql>drop trigger if exists tab1_after_trigger;

Mysql>Create trigger tab1_after_trigger after insert

On tab1 for each now

Begin

Insert into tab2(tab2_id) values (new tab1_id);  new關鍵字指的是tab1插入以后的新增值，在刪除的時候就應該是用old關鍵字

End&&

Mysql>Delimiter ; 

案例三：

當我們更新學生表學生信息的時候，我們學生更新表也對應的改變。

創建表

Drop table if exists student;

Create table student(

student_id int auto_incremnet primary key not null,

Student varchar(30) not null,

Student_sex enum(‘m’,’f’) default ‘m’

);

插入數據：

Insert into student values

(1,’jack’,’m’),

(2,’robin’,’m’),

(3,’alice’,’f’); 

創建tab2

Drop table if exists update_student;

Create table update_student(

Update_record int auto_incremnet primary key not null,

Student_id int not null,

Update_date date

);

插入數據：

Insert into update_student values

(1,1,now()),

(2,2,now()),

(3,3,now()); 

創建觸發器ustudent_trigger

Mysql>Delimiter && //定義結束符

Mysql>drop trigger if exists student_update_trigger;

Mysql>Create trigger student_update_trigger before update

On studnet for each now

Begin

If new.student_id!=old.student_id then

Update update_student

Set student_id=new.student_id

Where student_id=old.student_id;

End if;

End&&

Mysql>Delimiter ;

改后的值叫new值，改之前叫做old值，這句話表示學生id被修改后，就會把學生id新值也會賦值給update_student表

刪除同步操作案例

Mysql>Delimiter && //定義結束符

Mysql>drop trigger if exists student_delete_trigger;

Mysql>Create trigger student_delete_trigger before delete

On studnet for each now

Begin

Delete from update_student

Where student_id=old.student_id;

End&&

Mysql>Delimiter ;

對于以上關于mysql數據庫的高級應用索引、視圖，觸發器的詳細介紹，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。