1.視圖

概述：視圖就相當于一個臨時表，但是只定義了對應關系，用這個視圖的時候，就會自行執行視圖里定義好的sql語句，視圖是只能去查原表的數據，不能去修改原表里面的數據的；

視圖的增刪改查：

1.創建視圖：

語法：CREATE VIEW 視圖名稱 AS SQL語句

示例：

CREATE VIEW v1 AS

SELECT nid, name FROM user

WHERE nid > 3;

2.刪除視圖：

語法：DROP 視圖名稱

3.修改視圖

語法：ALTER VIEW 視圖名稱 AS sQL語句

示例：

ALTER VIEW v1 AS

SELECT sex, birthday FROM user

WHERE nid > 1

4.使用視圖：

示例：

select nid, name from v1;

2.觸發器

概述：對某個表進行【增/刪/改】操作的前后如果希望觸發某個特定的行為時，可以使用觸發器，觸發器用于定制用戶對表的行進行【增/刪/改】前后的行為。

觸發器的創建

# 插入前

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

...

END

# 插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

...

END

# 刪除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW

BEGIN

...

END

# 刪除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW

BEGIN

...

END

# 更新前

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

# 更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

示例1：

delimiter //

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

IF NEW. NAME == 'alex' THEN

INSERT INTO tb2 (NAME)

VALUES

('aa')

END

END//

delimiter ;

示例2：

delimiter //

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

IF NEW. NAME == 'alex' THEN

INSERT INTO tb2 (NAME)

VALUES

('aa')

END

END//

delimiter ;

注意：delimiter // 把默認以“；”作為結束符改為了以//作為結束符，NEW表示即將插入的數據行，OLD表示即將被刪除的數據行

觸發器的刪除

語法：DROP TRIGGER 觸發器的名稱;

使用觸發器

觸發器無法由用戶直接調用，而知由于對表的【增/刪/改】操作被動引發的

3.存儲過程

概述：存儲過程是一個SQL語句集合，當主動去調用存儲過程時，其中內部的SQL語句會按照邏輯執行

創建存儲過程

1.無參數的存儲過程

示例：

-- 創建存儲過程

delimiter //

create procedure p1()

BEGIN

select * from t1;

END//

delimiter ;

-- 執行存儲過程

call p1()

2.有參數的存儲過程

對于存儲過程，可以接收三類參數：

in:僅用于傳入參數用

out：僅用于返回值用

inout：既可以傳入又可以當作返回值

創建有參數的存儲過程示例：

-- 創建存儲過程

delimiter \\

create procedure p1(

in i1 int,

in i2 int,

inout i3 int,

out r1 int

)

BEGIN

DECLARE temp1 int;

DECLARE temp2 int default 0;

set temp1 = 1;

set r1 = i1 + i2 + temp1 + temp2;

set i3 = i3 + 100;

end\\

delimiter ;

-- 執行存儲過程

set @t1 =4;

set @t2 = 0;

CALL p1 (1, 2 ,@t1, @t2);

SELECT @t1,@t2;

事務型存儲過程示例：

delimiter \\

create PROCEDURE p1(

OUT p_return_code tinyint

)

BEGIN

DECLARE exit handler for sqlexception

BEGIN

-- ERROR

set p_return_code = 1;

rollback;

END;

DECLARE exit handler for sqlwarning

BEGIN

-- WARNING

set p_return_code = 2;

rollback;

END;

START TRANSACTION;

DELETE from tb1;

insert into tb2(name)values('seven');

COMMIT;

-- SUCCESS

set p_return_code = 0;

END\\

delimiter ;

刪除存儲過程

drop procedure 存儲過程名稱

4.函數

MySQL中的內置函數

INSTR(str, len):返回字符串str從開始的len位置的子序列字符

LEFT(str, len):返回字符串str從開始的len位置的子序列字符

LOWER(str)：變小寫

UPPER(str)：變大寫

...

自定義函數

1.定義一個函數：

delimiter \\

create function f1(

i1 int,

i2 int)

returns int

BEGIN

declare num int;

set num = i1 + i2;

return(num);

END \\

delimiter ;

-- 執行函數：

f1(1, 3);

2.刪除函數：

drop function 函數名稱

5.事務

概述：事務用于將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可回滾到原來的狀態，從而保證數據庫數據完整性

6.索引

概述：索引是數據庫中專門用于幫助用戶快速查詢數據的一種數據結構，類似于字典中的目錄，查找字典內容時可以根據目錄查找找到數據的存放位置，然后直接獲取即可

MySQL中常見索引有：

1.普通索引：僅有一個功能，那就是加快查詢

create index 索引名 ob 表名(列名)

2.唯一索引：加速查詢和唯一約束(可為null)

create unique index 索引名 on 表名(列名)

3.主鍵索引：加速查詢和唯一約束(不可為null)

alter table 表名 add primary key(列名);

4.聯合索引：將n個列組合成一個索引

create index 索引名 on 表名(列名1,列名2。。。);

聯合索引需要注意的點：

聯合索引的最左匹配原則，即最左優先，以最左邊的為起點任何連續的索引都能匹配上。同時遇到范圍查詢(>、

多字段的聯合索引在查詢單個字段時是否可以用到索引：如下面的聯合索引的語法所示，如果你查詢單個字段時，查詢的是列名1和列名2或者查詢列名1的話，就能命中索引，如果只查詢列名2就中不了索引

索引相關命令

查看表結構：

desc 表名

查看生成表的sql

show create table 表名

查看執行時間：

set profilling = 1;

SQL語句...

show profiles

查看執行計劃：

explain select * from tb2;

執行計劃里面type屬性的值代表的意思

type

查詢時的訪問方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

ALL 全表掃描，對于數據表從頭到尾找一遍

select * from tb1;

特別的：如果有limit限制，則找到之后就不在繼續向下掃描

select * from tb1 where email = 'seven@live.com'

select * from tb1 where email = 'seven@live.com' limit 1;

雖然上述兩個語句都會進行全表掃描，第二句使用了limit，則找到一個后就不再繼續掃描。

INDEX 全索引掃描，對索引從頭到尾找一遍

select nid from tb1;

RANGE 對索引列進行范圍查找

select * from tb1 where name < 'alex';

PS:

between and

in

> >= < <= 操作

注意：!= 和 > 符號

INDEX_MERGE 合并索引，使用多個單列索引搜索

select * from tb1 where name = 'alex' or nid in (11,22,33);

REF 根據索引查找一個或多個值

select * from tb1 where name = 'seven';

EQ_REF 連接時使用primary key 或 unique類型

select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

CONST 常量

表最多有一個匹配行,因為僅有一行,在這行的列值可被優化器剩余部分認為是常數,const表很快,因為它們只讀取一次。

select nid from tb1 where nid = 2 ;

SYSTEM 系統

表僅有一行(=系統表)。這是const聯接類型的一個特例。

select * from (select nid from tb1 where nid = 1) as A;

7.動態執行sql語句

示例：

delimiter \\

DROP PROCEDURE IF EXISTS proc_sql \\

CREATE PROCEDURE proc_sql ()

BEGIN

declare p1 int;

set p1 = 11;

set @p1 = p1;

PREPARE prod FROM 'select * from tb2 where nid > ?';

EXECUTE prod USING @p1;

DEALLOCATE prepare prod;

END\\

delimiter ;

8.如何優化SQL語句

1.列類型盡量定義成數值類型，且長度盡可能短，如主鍵和外鍵，類型字段等等

2.建立單列索引

3.根據需要建立多列聯合索引

當單個列過濾之后還有很多數據，那么索引的效率將會比較低，即列的區分度較低，那么如果在多個列上建立索引，那么多個列的區分度就大多了，將會有顯著的效率提高。

4.根據業務場景建立覆蓋索引

只查詢業務需要的字段，如果這些字段被索引覆蓋，將極大的提高查詢效率

5.多表連接的字段上需要建立索引這樣可以極大的提高表連接的效率

6.where條件字段上需要建立索引

7.排序字段上需要建立索引

8.分組字段上需要建立索引

9.where條件上不要使用運算函數，以免索引失效

總結

以上是生活随笔為你收集整理的mysql进阶3_MySQL进阶的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。