一、視圖

視圖是一個(gè)虛擬表（非真實(shí)存在），其本質(zhì)是【根據(jù)SQL語句獲取動(dòng)態(tài)的數(shù)據(jù)集，并為其命名】，用戶使用時(shí)只需使用【名稱】即可獲取結(jié)果集，并可以將其當(dāng)作表來使用。

視圖和上一篇學(xué)到的臨時(shí)表搜索類似。

SELECT* FROM(SELECTnid,NAMEFROMtb1WHEREnid > 2) AS A WHEREA. NAME > 'alex'; 臨時(shí)表搜索
1. 創(chuàng)建視圖
--格式：CREATE VIEW 視圖名稱 AS SQL語句 CREATE VIEW v1 AS SELET nid,name FROMA WHEREnid > 4

2. 刪除視圖

DROP VIEW v1

3.修改視圖-- 格式：ALTER VIEW 視圖名稱 AS SQL語句ALTER VIEW v1 AS SELET A.nid,B. NAME FROMA LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHEREA.id > 2 AND C.nid < 5
4. 查看視圖DESCRIBE V1SHOW TABLE STATUS LIKE V1SHOW CREATE VIEW V1使用視圖時(shí)，將其當(dāng)作表進(jìn)行操作即可，由于視圖是虛擬表，所以無法使用其對(duì)真實(shí)表進(jìn)行創(chuàng)建、更新和刪除操作，僅能做查詢用。select * from v1

二、觸發(fā)器

基本語法CREATE TRIGGER trigger_name trigger_time triger_event ON tb1_name FOR EACH ROW trigger_stmt 基本語法
1. 創(chuàng)建單行觸發(fā)器
create table account(acc_num int, amount DECIMAL(10,2)); create TRIGGER ins_sum BEFORE INSERT on account for each ROW set @sum = @sum +NEW.amount; set @sum=0 單行觸發(fā)器?2. 創(chuàng)建多行觸發(fā)器
delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGINIF NEW. NAME == 'bolen' THENINSERT INTO tb2 (NAME) VALUES('aa') END// delimiter ; 插入前觸發(fā)器

delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGINIF NEW. num = 666 THENINSERT INTO tb2 (NAME)VALUES('666'),('666') ;ELSEIF NEW. num = 555 THENINSERT INTO tb2 (NAME)VALUES('555'),('555') ;END IF; END// delimiter ;插入后觸發(fā)器 插入后觸發(fā)器

注意：NEW表示即將插入的數(shù)據(jù)行，OLD表示即將刪除的數(shù)據(jù)行。

3.? 刪除觸發(fā)器

DROP TRIGGER trigger_name;

三、存儲(chǔ)過程

存儲(chǔ)過程是一個(gè)SQL語句集合，當(dāng)主動(dòng)去調(diào)用存儲(chǔ)過程時(shí)，其中內(nèi)部的SQL語句會(huì)按照邏輯執(zhí)行。可視為批文件，但其作用不僅限于批處理。

1、創(chuàng)建存儲(chǔ)過程

-- 創(chuàng)建存儲(chǔ)過程delimiter // create procedure p1() BEGINselect * from t1; END// delimiter ;-- 執(zhí)行存儲(chǔ)過程call p1()無參數(shù)存儲(chǔ)過程 無參數(shù)存儲(chǔ)過程

對(duì)于存儲(chǔ)過程，可以接收參數(shù)，其參數(shù)有三類：

• in????????? 僅用于傳入?yún)?shù)用
• out??????? 僅用于返回值用
• inout???? 既可以傳入又可以當(dāng)作返回值

-- 創(chuàng)建存儲(chǔ)過程 delimiter \\ create procedure p1(in i1 int,in i2 int,inout i3 int,out r1 int ) BEGINDECLARE temp1 int;DECLARE temp2 int default 0;set temp1 = 1;set r1 = i1 + i2 + temp1 + temp2;set i3 = i3 + 100;end\\ delimiter ;-- 執(zhí)行存儲(chǔ)過程 set @t1 =4; set @t2 = 0; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2;有參數(shù)的存儲(chǔ)過程 有參數(shù)存儲(chǔ)過程delimiter //create procedure p1()beginselect * from v1;end //delimiter ;1. 結(jié)果集 結(jié)果集

delimiter //create procedure p2(in n1 int,inout n3 int,out n2 int,)begindeclare temp1 int ;declare temp2 int default 0;select * from v1;set n2 = n1 + 100;set n3 = n3 + n1 + 100;end //delimiter ;2. 結(jié)果集+out值 結(jié)果集+out值

delimiter \\create PROCEDURE p1(OUT p_return_code tinyint)BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset p_return_code = 2;rollback;END;START TRANSACTION;DELETE from tb1;insert into tb2(name)values('seven');COMMIT;-- SUCCESSset p_return_code = 0;END\\delimiter ;3. 事務(wù) 事務(wù)

delimiter //create procedure p3()begindeclare ssid int; -- 自定義變量1declare ssname varchar(50); -- 自定義變量2DECLARE done INT DEFAULT FALSE;DECLARE my_cursor CURSOR FOR select sid,sname from student;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;open my_cursor;xxoo: LOOPfetch my_cursor into ssid,ssname;if done thenleave xxoo;END IF;insert into teacher(tname) values(ssname);end loop xxoo;close my_cursor;end //delimter ;4. 游標(biāo) 游標(biāo)
delimiter \\CREATE PROCEDURE p4 (in nid int)BEGINPREPARE prod FROM 'select * from student where sid > ?';EXECUTE prod USING @nid;DEALLOCATE prepare prod; END\\delimiter ;5. 動(dòng)態(tài)執(zhí)行SQL

2、刪除存儲(chǔ)過程

drop procedure proc_name;

3、執(zhí)行存儲(chǔ)過程

-- 無參數(shù) call proc_name()-- 有參數(shù)，全in call proc_name(1,2)-- 有參數(shù)，有in，out，inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2)執(zhí)行存儲(chǔ)過程 執(zhí)行存儲(chǔ)過程

#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 執(zhí)行存儲(chǔ)過程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 獲取執(zhí)行完存儲(chǔ)的參數(shù) cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall()conn.commit() cursor.close() conn.close()print(result)pymysql執(zhí)行存儲(chǔ)過程 pymysql執(zhí)行存儲(chǔ)過程

四、函數(shù)

CHAR_LENGTH(str)返回值為字符串str 的長(zhǎng)度，長(zhǎng)度的單位為字符。一個(gè)多字節(jié)字符算作一個(gè)單字符。對(duì)于一個(gè)包含五個(gè)二字節(jié)字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。CONCAT(str1,str2,...)字符串拼接如有任何一個(gè)參數(shù)為NULL ，則返回值為 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接（自定義連接符）CONCAT_WS()不會(huì)忽略任何空字符串。 (然而會(huì)忽略所有的 NULL）。CONV(N,from_base,to_base)進(jìn)制轉(zhuǎn)換例如：SELECT CONV('a',16,2); 表示將 a 由16進(jìn)制轉(zhuǎn)換為2進(jìn)制字符串表示FORMAT(X,D)將數(shù)字X 的格式寫為'#,###,###.##',以四舍五入的方式保留小數(shù)點(diǎn)后 D 位， 并將結(jié)果以字符串的形式返回。若 D 為 0, 則返回結(jié)果不帶有小數(shù)點(diǎn)，或不含小數(shù)部分。例如：SELECT FORMAT(12332.1,4); 結(jié)果為： '12,332.1000'INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos：要替換位置其實(shí)位置len：替換的長(zhǎng)度newstr：新字符串特別的：如果pos超過原字符串長(zhǎng)度，則返回原字符串如果len超過原字符串長(zhǎng)度，則由新字符串完全替換INSTR(str,substr)返回字符串 str 中子字符串的第一個(gè)出現(xiàn)位置。LEFT(str,len)返回字符串str 從開始的len位置的子序列字符。LOWER(str)變小寫UPPER(str)變大寫LTRIM(str)返回字符串 str ，其引導(dǎo)空格字符被刪除。RTRIM(str)返回字符串 str ，結(jié)尾空格字符被刪去。SUBSTRING(str,pos,len)獲取字符串子序列LOCATE(substr,str,pos)獲取子序列索引位置REPEAT(str,count)返回一個(gè)由重復(fù)的字符串str 組成的字符串，字符串str的數(shù)目等于count 。若 count <= 0,則返回一個(gè)空字符串。若str 或 count 為 NULL，則返回 NULL 。REPLACE(str,from_str,to_str)返回字符串str 以及所有被字符串to_str替代的字符串from_str 。REVERSE(str)返回字符串 str ，順序和字符順序相反。RIGHT(str,len)從字符串str 開始，返回從后邊開始len個(gè)字符組成的子序列SPACE(N)返回一個(gè)由N空格組成的字符串。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不帶有l(wèi)en 參數(shù)的格式從字符串str返回一個(gè)子字符串，起始于位置 pos。帶有l(wèi)en參數(shù)的格式從字符串str返回一個(gè)長(zhǎng)度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式為標(biāo)準(zhǔn) SQL 語法。也可能對(duì)pos使用一個(gè)負(fù)值。假若這樣，則子字符串的位置起始于字符串結(jié)尾的pos 字符，而不是字符串的開頭位置。在以下格式的函數(shù)中可以對(duì)pos 使用一個(gè)負(fù)值。mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)返回字符串 str ， 其中所有remstr 前綴和/或后綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個(gè)是給定的,則假設(shè)為BOTH 。 remstr 為可選項(xiàng)，在未指定情況下，可刪除空格。mysql> SELECT TRIM(' bar ');-> 'bar'mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-> 'barxxx'mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');-> 'bar'mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');-> 'barx'部分內(nèi)置函數(shù) 部分內(nèi)置函數(shù)delimiter \\ create function f1(i1 int,i2 int) returns int BEGINdeclare num int;set num = i1 + i2;return(num); END \\ delimiter ; 自定義函數(shù)
刪除函數(shù)drop function func_name;# 獲取返回值 declare @i VARCHAR(32); select UPPER('bolen') into @i; SELECT @i;# 在查詢中使用 select f1(11,nid) ,name from tb2; 執(zhí)行函數(shù)

五、事務(wù)

事務(wù)用于將某些操作的多個(gè)SQL作為原子性操作，一旦有某一個(gè)出現(xiàn)錯(cuò)誤，即可回滾到原來的狀態(tài)，從而保證數(shù)據(jù)庫數(shù)據(jù)完整性。delimiter \\ create PROCEDURE p1(OUT p_return_code tinyint ) BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset p_return_code = 2;rollback;END;START TRANSACTION;DELETE from tb1;insert into tb2(name)values('seven');COMMIT;-- SUCCESSset p_return_code = 0;END\\ delimiter ;支持事務(wù)的存儲(chǔ)過程 支持事務(wù)的存儲(chǔ)過程

set @i =0;

call p1(@i);

select @i;

六、其他

1、條件語句delimiter \\ CREATE PROCEDURE proc_if () BEGINdeclare i int default 0;if i = 1 THENSELECT 1;ELSEIF i = 2 THENSELECT 2;ELSESELECT 7;END IF;END\\ delimiter ;if條件語句 if條件語句
2、循環(huán)語句delimiter \\ CREATE PROCEDURE proc_while () BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END\\ delimiter ; while循環(huán)

delimiter \\ CREATE PROCEDURE proc_repeat () BEGINDECLARE i INT ;SET i = 0 ;repeatselect i;set i = i + 1;until i >= 5end repeat;END\\ delimiter ; repeat循環(huán)
BEGINdeclare i int default 0;loop_label: loopset i=i+1;if i<8 theniterate loop_label;end if;if i>=10 thenleave loop_label;end if;select i;end loop loop_label;END loop

3、動(dòng)態(tài)執(zhí)行SQL語句

delimiter \\ DROP PROCEDURE IF EXISTS proc_sql \\ CREATE PROCEDURE proc_sql () BEGINdeclare 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 ; 動(dòng)態(tài)執(zhí)行SQL

轉(zhuǎn)載于:https://www.cnblogs.com/bolenzhang/p/8176435.html

總結(jié)

以上是生活随笔為你收集整理的MySQL基础（二）：视图、触发器、函数、事务、存储过程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。