程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MYSQL進階，新手變司機，mysql進階新手司機

MYSQL進階，新手變司機，mysql進階新手司機

編輯：MySQL綜合教程

MYSQL進階，新手變司機，mysql進階新手司機

一、視圖

視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，並為其命名】，用戶使用時只需使用【名稱】即可獲取結果集，並可以將其當作表來使用。

 1 SELECT
 2     *
 3 FROM
 4     (
 5         SELECT
 6             nid,
 7             NAME
 8         FROM
 9             tb1
10         WHERE
11             nid > 2
12     ) AS A
13 WHERE
14     A. NAME > 'alex';

臨時表搜索

1、創建視圖

1 --格式：CREATE VIEW 視圖名稱 AS  SQL語句
2 CREATE VIEW v1 AS 
3 SELET nid, 
4     name
5 FROM
6     A
7 WHERE
8     nid > 4

View Code

2、刪除視圖

1 --格式：DROP VIEW 視圖名稱
2 
3 DROP VIEW v1

View Code

3、修改視圖

 1 -- 格式：ALTER VIEW 視圖名稱 AS SQL語句
 2 
 3 ALTER VIEW v1 AS
 4 SELET A.nid,
 5     B. NAME
 6 FROM
 7     A
 8 LEFT JOIN B ON A.id = B.nid
 9 LEFT JOIN C ON A.id = C.nid
10 WHERE
11     A.id > 2
12 AND C.nid < 5

View Code

4、使用視圖

1 select * from v1

View Code

二、觸發器

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

1、創建基本語法

 1 # 插入前
 2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
 3 BEGIN
 4     ...
 5 END
 6 
 7 # 插入後
 8 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 9 BEGIN
10     ...
11 END
12 
13 # 刪除前
14 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
15 BEGIN
16     ...
17 END
18 
19 # 刪除後
20 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
21 BEGIN
22     ...
23 END
24 
25 # 更新前
26 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
27 BEGIN
28     ...
29 END
30 
31 # 更新後
32 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
33 BEGIN
34     ...
35 END

View Code

 1 delimiter //
 2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
 3 BEGIN
 4 
 5 IF NEW. NAME == 'alex' THEN
 6     INSERT INTO tb2 (NAME)
 7 VALUES
 8     ('aa')
 9 END
10 END//
11 delimiter ;

插入前觸發器

 1 delimiter //
 2 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 3 BEGIN
 4     IF NEW. num = 666 THEN
 5         INSERT INTO tb2 (NAME)
 6         VALUES
 7             ('666'),
 8             ('666') ;
 9     ELSEIF NEW. num = 555 THEN
10         INSERT INTO tb2 (NAME)
11         VALUES
12             ('555'),
13             ('555') ;
14     END IF;
15 END//
16 delimiter ;

後觸發器

特別的：NEW表示即將插入的數據行，OLD表示即將刪除的數據行。

2、刪除觸發器

1 DROP TRIGGER tri_after_insert_tb1;

View Code

3、使用觸發器

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

1 insert into tb1(num) values(666)

View Code

三、存儲過程

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

1、創建存儲過程

 1 -- 創建存儲過程
 2 
 3 delimiter //
 4 create procedure p1()
 5 BEGIN
 6     select * from t1;
 7 END//
 8 delimiter ;
 9 
10 
11 
12 -- 執行存儲過程
13 
14 call p1()

View Code

對於存儲過程，可以接收參數，其參數有三類：

in 僅用於傳入參數用
out 僅用於返回值用
inout 既可以傳入又可以當作返回值

 1 -- 創建存儲過程
 2 delimiter \\
 3 create procedure p1(
 4     in i1 int,
 5     in i2 int,
 6     inout i3 int,
 7     out r1 int
 8 )
 9 BEGIN
10     DECLARE temp1 int;
11     DECLARE temp2 int default 0;
12     
13     set temp1 = 1;
14 
15     set r1 = i1 + i2 + temp1 + temp2;
16     
17     set i3 = i3 + 100;
18 
19 end\\
20 delimiter ;
21 
22 -- 執行存儲過程
23 set @t1 =4;
24 set @t2 = 0;
25 CALL p1 (1, 2 ,@t1, @t2);
26 SELECT @t1,@t2;

有參存儲過程

2、刪除存儲過程

1 drop procedure proc_name;

View Code

3、執行存儲過程

 1 -- 無參數
 2 call proc_name()
 3 
 4 -- 有參數，全in
 5 call proc_name(1,2)
 6 
 7 -- 有參數，有in，out，inout
 8 set @t1=0;
 9 set @t2=3;
10 call proc_name(1,2,@t1,@t2)

View Code

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4 
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 7 # 執行存儲過程
 8 cursor.callproc('p1', args=(1, 22, 3, 4))
 9 # 獲取執行完存儲的參數
10 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
11 result = cursor.fetchall()
12 
13 conn.commit()
14 cursor.close()
15 conn.close()
16 
17 
18 print(result)

pysql執行存儲過程

四、事務

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

 1 delimiter \\
 2 create PROCEDURE p1(
 3     OUT p_return_code tinyint
 4 )
 5 BEGIN 
 6   DECLARE exit handler for sqlexception 
 7   BEGIN 
 8     -- ERROR 
 9     set p_return_code = 1; 
10     rollback; 
11   END; 
12  
13   DECLARE exit handler for sqlwarning 
14   BEGIN 
15     -- WARNING 
16     set p_return_code = 2; 
17     rollback; 
18   END; 
19  
20   START TRANSACTION; 
21     DELETE from tb1;
22     insert into tb2(name)values('seven');
23   COMMIT; 
24  
25   -- SUCCESS 
26   set p_return_code = 0; 
27  
28   END\\
29 delimiter ;
30 set @i =0;
31 call p1(@i);
32 select @i;

支持事務的存儲過程

五、索引

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

             30
 
        10                        40
 
   5         15            35             66
 
1   6     11    19      21      39     55     100

MySQL中常見索引有：

普通索引
唯一索引
主鍵索引
組合索引

1、普通索引

普通索引僅有一個功能：加速查詢

1 create table in1(
2     nid int not null auto_increment primary key,
3     name varchar(32) not null,
4     email varchar(64) not null,
5     extra text,
6     index ix_name (name)
7 )

創建表和索引

1 create index index_name on table_name(column_name)

創建索引

1 drop index_name on table_name;

刪除索引

1 show index from table_name;

查看索引

注意：對於創建索引時如果是BLOB 和 TEXT 類型，必須指定length。

create index ix_extra on in1(extra(32));

2、唯一索引

唯一索引有兩個功能：加速查詢和唯一約束（可含null）

1 create table in1(
2     nid int not null auto_increment primary key,
3     name varchar(32) not null,
4     email varchar(64) not null,
5     extra text,
6     unique ix_name (name)

表+唯一索引

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

創建唯一索引

1 drop unique index 索引名 on 表名

刪除唯一索引

3、主鍵索引

主鍵有兩個功能：加速查詢和唯一約束（不可含null）

 1 create table in1(
 2     nid int not null auto_increment primary key,
 3     name varchar(32) not null,
 4     email varchar(64) not null,
 5     extra text,
 6     index ix_name (name)
 7 )
 8 
 9 OR
10 
11 create table in1(
12     nid int not null auto_increment,
13     name varchar(32) not null,
14     email varchar(64) not null,
15     extra text,
16     primary key(ni1),
17     index ix_name (name)
18 )

創建主鍵和主鍵索引

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

創建主鍵

1 alter table 表名 drop primary key;
2 alter table 表名  modify  列名 int, drop primary key;

刪除主鍵

4、組合索引

組合索引是將n個列組合成一個索引

其應用場景為：頻繁的同時使用n列來進行查詢，如：where n1 = 'alex' and n2 = 666。

1 create table in3(
2     nid int not null auto_increment primary key,
3     name varchar(32) not null,
4     email varchar(64) not null,
5     extra text
6 )

創建表

1 create index ix_name_email on in3(name,email);

創建組合索引

如上創建組合索引之後，查詢：

name and email -- 使用索引
name -- 使用索引
email -- 不使用索引

注意：對於同時搜索n個條件時，組合索引的性能好於多個單一索引合並。

六、其他

 1 delimiter \\
 2 CREATE PROCEDURE proc_if ()
 3 BEGIN
 4     
 5     declare i int default 0;
 6     if i = 1 THEN
 7         SELECT 1;
 8     ELSEIF i = 2 THEN
 9         SELECT 2;
10     ELSE
11         SELECT 7;
12     END IF;
13 
14 END\\
15 delimiter ;

if語句

 1 delimiter \\
 2 CREATE PROCEDURE proc_while ()
 3 BEGIN
 4 
 5     DECLARE num INT ;
 6     SET num = 0 ;
 7     WHILE num < 10 DO
 8         SELECT
 9             num ;
10         SET num = num + 1 ;
11     END WHILE ;
12 
13 END\\
14 delimiter ;

while循環

 1 delimiter \\
 2 CREATE PROCEDURE proc_repeat ()
 3 BEGIN
 4 
 5     DECLARE i INT ;
 6     SET i = 0 ;
 7     repeat
 8         select i;
 9         set i = i + 1;
10         until i >= 5
11     end repeat;
12 
13 END\\
14 delimiter ;

repeat循環

 1 delimiter \\
 2 CREATE PROCEDURE proc_loop ()
 3 BEGIN
 4     
 5     declare i int default 0;
 6     loop_label: loop
 7         select i;
 8         set i=i+1;
 9         if i>=5 then
10             leave loop_label;
11             end if;
12     end loop;
13 
14 END\\
15 delimiter ;

loop

 1 delimiter \\
 2 DROP PROCEDURE IF EXISTS proc_sql \\
 3 CREATE PROCEDURE proc_sql ()
 4 BEGIN
 5     declare p1 int;
 6     set p1 = 11;
 7     set @p1 = p1;
 8 
 9     PREPARE prod FROM 'select * from tb2 where nid > ?';
10     EXECUTE prod USING @p1;
11     DEALLOCATE prepare prod; 
12 
13 END\\
14 delimiter ;

動態執行mysql