mysql數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用索引、視圖，觸發(fā)器的詳細(xì)介紹

下文主要給大家?guī)?lái)mysql數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用索引、視圖，觸發(fā)器的詳細(xì)介紹，希望這些內(nèi)容能夠帶給大家實(shí)際用處，這也是我編輯mysql數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用索引、視圖，觸發(fā)器的詳細(xì)介紹這篇文章的主要目的。好了，廢話不多說(shuō)，大家直接看下文吧。

索引優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

加快訪問(wèn)速度

加強(qiáng)行的唯一性

缺點(diǎn)

帶索引的表在數(shù)據(jù)庫(kù)中需要更多的存儲(chǔ)空間

操縱數(shù)據(jù)的命令需要更長(zhǎng)的處理時(shí)間，因?yàn)樗鼈冃枰獙?duì)索引進(jìn)行更新

按照下列標(biāo)準(zhǔn)選擇建立索引的列

頻繁搜索的列

經(jīng)常用作查詢選擇的列

經(jīng)常排序、分組的列

經(jīng)常用作聯(lián)接的列（主鍵/外鍵）

請(qǐng)不要使用下面的列創(chuàng)建索引

僅包含幾個(gè)不同值的列

表中僅包含幾行

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

索引應(yīng)該盡量小，在字節(jié)數(shù)小的列上建立索引

WHERE子句中有多個(gè)條件表達(dá)式時(shí)，包含索引列的表達(dá)式應(yīng)置于其他條件表達(dá)式之前

避免在ORDER BY子句中使用表達(dá)式

根據(jù)業(yè)務(wù)數(shù)據(jù)發(fā)生頻率，定期重新生成或重新組織索引，進(jìn)行碎片整理 

索引的分類(lèi)

普通索引

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

全文索引

單列索引

多列索引

空間索引

準(zhǔn)備實(shí)驗(yàn)環(huán)境

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

Mysql>Create procedure autoinsert1()   //創(chuàng)建存儲(chǔ)過(guò)程

Begin

Declare i int default 1;

While (1<200000)do

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

Set i=i+1;

End while;

End$$

Mysql>delimiter;//把分隔符還原回來(lái)

Mysql>Call autoinsert1();  //調(diào)用存儲(chǔ)過(guò)程

創(chuàng)建索引(創(chuàng)建表時(shí))注意事項(xiàng)：一開(kāi)始就有索引會(huì)有個(gè)如果你批量導(dǎo)入數(shù)據(jù)的時(shí)候，因?yàn)槟硞€(gè)字段上存在索引，所以速度會(huì)非常慢。

語(yǔ)法：create table 表名(

字段一 數(shù)據(jù)類(lèi)型 [完整性約束條件.....],

字段一 數(shù)據(jù)類(lèi)型 [完整性約束條件.....],

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

); 

創(chuàng)建普通索引創(chuàng)建表時(shí)實(shí)例：

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

創(chuàng)建唯一索引創(chuàng)建表時(shí)實(shí)例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Unique Index index_dept_name (dept_name)

);

創(chuàng)建全文索引創(chuàng)建表時(shí)實(shí)例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

fulltext Index index_dept_name (dept_name)

)engin=myisam; 

創(chuàng)建多列索引創(chuàng)建表時(shí)實(shí)例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Index index_dept_name (dept_name,commnet)

);

模擬查詢分析查詢語(yǔ)法

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

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

復(fù)制表的內(nèi)容和結(jié)構(gòu)  將t2表的所有數(shù)據(jù)復(fù)制到t4表上

Create table t4 select * from t2;

只復(fù)制表的結(jié)構(gòu)  將t2表的結(jié)構(gòu)復(fù)制到t4表上

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

或者create table t5 like t2;

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

創(chuàng)建普通索引實(shí)例：

Create index index_dept_name on departmnet (dept_name);

創(chuàng)建唯一索引實(shí)例：

Create unique index index_dept_name on departmnet (dept_name);

創(chuàng)建全文索引實(shí)例：

Create fulltext index index_dept_name on departmnet (dept_name);

創(chuàng)建多列索引實(shí)例：

Create index index_dept_name on departmnet (dept_name,comment);

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

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

創(chuàng)建普通索引實(shí)例：

Alter table department add  index index_dept_name (dept_name); 

創(chuàng)建唯一索引實(shí)例：

Alter table department add  unique index index_dept_name (dept_name);

創(chuàng)建全文索引實(shí)例：

Alter table department add  fulltext index index_dept_name (dept_name);

創(chuàng)建多列索引實(shí)例：

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

管理索引

查看索引

Show create table 表名\G

測(cè)試示例

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

刪除索引

Drop index 索引名 on 表名

視圖簡(jiǎn)介：安全和簡(jiǎn)化操作

Mysql視圖是一張?zhí)摂M表，其內(nèi)容由查詢定義，同真實(shí)的表一樣，視圖包括一系列帶有名稱的列和行數(shù)據(jù)，但是視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在，行和列數(shù)據(jù)來(lái)自由定義視圖的查詢所引用的表，并且在引用視圖的動(dòng)態(tài)生成，對(duì)其中所引用的基礎(chǔ)表來(lái)說(shuō)，mysql視圖的作用類(lèi)似于篩選。定義視圖的篩選可以來(lái)自當(dāng)前或其他數(shù)據(jù)庫(kù)的一個(gè)或多個(gè)表，或者其他視圖，通過(guò)視圖進(jìn)行查詢沒(méi)有任何限制，通過(guò)它們進(jìn)行數(shù)據(jù)修改時(shí)的限制也很少。

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

創(chuàng)建視圖

語(yǔ)法一

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

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

[with [caseaded|local] check option]; 

語(yǔ)法二

Create view 視圖名 as select 語(yǔ)句;

實(shí)例：

Create database view; //創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，建議創(chuàng)建

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

實(shí)例：查看view數(shù)據(jù)庫(kù)中視圖以及所有表詳細(xì)信息

Show table status from view\G

實(shí)例：查看view數(shù)據(jù)庫(kù)中視圖名view_user詳細(xì)信息

Show table status from view like “view_user” \G 

查看視圖定義信息

Use view;

Show create view view_user \G

查看視圖結(jié)構(gòu)

Use view;

Desc view_user;

修改視圖

方法一刪除后創(chuàng)建

Use view;

Drop view view_user;

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

Select * from view_user;

方法二：替換原有視圖

語(yǔ)法：create or replace view 視圖名 as select 語(yǔ)句;

實(shí)例：use view;

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

方法三：alter 修改視圖

語(yǔ)法：alter view 視圖名 as select 語(yǔ)句;

實(shí)例：use view;

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

通過(guò)視圖操作基表

查詢數(shù)據(jù) select    通常都是查詢操作

Select * from view_user;

更新數(shù)據(jù)update

刪除數(shù)據(jù)delect

刪除視圖

Drop view 視圖名

實(shí)例：

Use view;

Drop view view_user;

觸發(fā)器簡(jiǎn)介

觸發(fā)器(triggers)是一個(gè)特殊的存儲(chǔ)過(guò)程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由時(shí)間來(lái)觸發(fā)，比如當(dāng)對(duì)一個(gè)表進(jìn)行操作(insert,delect,update)時(shí)就會(huì)激活它執(zhí)行,觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整約束和業(yè)務(wù)規(guī)則等 

例如：當(dāng)學(xué)生表中增加一個(gè)學(xué)生的信息時(shí)，學(xué)生的總數(shù)就應(yīng)該同時(shí)改變，因此可以針對(duì)學(xué)生表創(chuàng)建一個(gè)觸發(fā)器，每次增加一個(gè)學(xué)生記錄時(shí)，就執(zhí)行一次學(xué)生總數(shù)的計(jì)算操作，從而保障學(xué)生總數(shù)和記錄數(shù)一致性。 

創(chuàng)建觸發(fā)器:同一張表最多可以創(chuàng)建六個(gè)觸發(fā)器

語(yǔ)法：create trigger 觸發(fā)器名稱 before|after 觸發(fā)事件

On 表名 for each row

Begin

 觸發(fā)器程序體;

End

觸發(fā)器名稱：//最多64字符

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

Insert|update|delete//觸發(fā)的事件

On表名//在哪張表上建立觸發(fā)器

觸發(fā)器程序體：//觸發(fā)器所觸發(fā)的sql語(yǔ)句，語(yǔ)句可以使用順序，判斷，循環(huán)等語(yǔ)句，實(shí)現(xiàn)一般程序需要的邏輯功能。 

實(shí)例：

創(chuàng)建表

Create table student(

Id int unsigned auto_incremnet primary key not null,

Name varchar(50)

);

unsigned //無(wú)符號(hào)修飾符

往表里插入數(shù)據(jù)

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

創(chuàng)建表2總數(shù)表

Create table student_total (total int); 

往數(shù)表中插入數(shù)據(jù)

Insert into student_total values(1);

創(chuàng)建觸發(fā)器實(shí)例一student_insert_trigger

Mysql>Delimiter && //定義結(jié)束符

Mysql>Create trigger student_insert_trigger after insert 

On student for each now

Begin

Upadte student_total set total=total+1;

End&&

Mysql>Delimiter ; 

創(chuàng)建觸發(fā)器實(shí)例二

Mysql>Delimiter && //定義結(jié)束符

Mysql>Create trigger student_insert_trigger after delect

On student for each now

Begin

Upadte student_total set total=total-1;

End&&

Mysql>Delimiter ;

測(cè)試效果，往學(xué)生表中插入數(shù)據(jù)，查看總數(shù)表結(jié)果，

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

Select * from student_total ;

查看觸發(fā)器

方法1通過(guò)show triggers語(yǔ)句查看

案例：show triggers\G

方法2通過(guò)系統(tǒng)表triggers查看

案例：use information_schema

Select * from triggers\G

SELECT * FROM TRIGGERS WHERE TGIGGER_NAME=’觸發(fā)器名稱’\G

刪除觸發(fā)器

Drop trigger 觸發(fā)器名稱

實(shí)例：Drop trigger student_insert_trigger;

觸發(fā)器的案例

案例一：增加tab1表記錄后自動(dòng)將記錄增加到tab2中,能同步的字段的數(shù)據(jù)類(lèi)型肯定要必須一致才行。

創(chuàng)建tab1

Drop table if exists tab1;

Create table tab1(

Tab1_id varchar(11)

); 

創(chuàng)建tab2

Drop table if exists tab2;

Create table tab2(

Tab2_id varchar(11)

);

觸發(fā)器：after_tab1_trigger

作用：增加tab1表記錄后自動(dòng)將記錄增加到tab2中

Mysql>Delimiter && //定義結(jié)束符

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關(guān)鍵字指的是tab1插入以后的新增值，在刪除的時(shí)候就應(yīng)該是用old關(guān)鍵字

End&&

Mysql>Delimiter ; 

案例三：

當(dāng)我們更新學(xué)生表學(xué)生信息的時(shí)候，我們學(xué)生更新表也對(duì)應(yīng)的改變。

創(chuàng)建表

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’

);

插入數(shù)據(jù)：

Insert into student values

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

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

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

創(chuàng)建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

);

插入數(shù)據(jù)：

Insert into update_student values

(1,1,now()),

(2,2,now()),

(3,3,now()); 

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

Mysql>Delimiter && //定義結(jié)束符

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值，這句話表示學(xué)生id被修改后，就會(huì)把學(xué)生id新值也會(huì)賦值給update_student表

刪除同步操作案例

Mysql>Delimiter && //定義結(jié)束符

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 ;

對(duì)于以上關(guān)于mysql數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用索引、視圖，觸發(fā)器的詳細(xì)介紹，大家是不是覺(jué)得非常有幫助。如果需要了解更多內(nèi)容，請(qǐng)繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會(huì)喜歡上這些內(nèi)容的。