mysql內建功能

標籤：返回   超過   trigger   cursor   ...   date()   atomic   limit   復原   

事務

事務由一個或多個sql語句組成一個整體，如果所有的語句執行成功那麼修改將會全部生效，如一條sql語句將銷量+1，下一條再+1，倘若第二條失敗，那麼銷量將撤銷第一條sql語句的+1操作，只有在該事務中所有的語句都執行成功才會將修改加入到資料庫中。

特性：

事務具體四大特性，也就是經常說的ACID 
1. 原子性（Atomicity） 
　　原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗復原，因此事務的操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有任何影響。 
　　 
2. 一致性（Consistency） 
　　一致性是指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態。

　　拿轉賬來說，假設使用者A和使用者B兩者的錢加起來一共是5000，那麼不管A和B之間如何轉賬，轉幾次賬，事務結束後兩個使用者的錢相加起來應該還得是5000，這就是事務的一致性。

3.隔離性（Isolation） 
　　隔離性是當多個使用者並發訪問資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所幹擾，多個並發事務之間要相互隔離。

　　即要達到這麼一種效果：對於任意兩個並發的事務T1和T2，在事務T1看來，T2要麼在T1開始之前就已經結束，要麼在T1結束之後才開始，這樣每個事務都感覺不到有其他事務在並發地執行。

4.持久性（Durability） 
　　持久性是指一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。Mysql中會儲存有相應的動作記錄，即使遭遇故障依然能夠通過日誌恢複最後一次更新。

　　例如我們在使用JDBC操作資料庫時，在提交事務方法後，提示使用者事務操作完成，當我們程式執行完成直到看到提示後，就可以認定事務以及正確提交，即使這時候資料庫出現了問題，也必須要將我們的事務完全執行完成，否則就會造成我們看到提示交易處理完畢，但是資料庫因為故障而沒有執行事務的重大錯誤。

mysq支援事務的引擎：innodb/bdb

開啟事務：start transaction

sql....

commit  提交

rollback  復原

註：

當一個事務commit或者rollback就結束了

有些語句會造成事務的隱式提交，如 start transaction

 

 

觸發器

使用觸發器可以定製使用者對錶進行【增、刪、改】操作時前後的行為，注意：沒有查詢

建立觸發器

 

在MySQL中，建立觸發器文法如下：

CREATE TRIGGER trigger_nametrigger_timetrigger_event ON tbl_nameFOR EACH ROWtrigger_stmt

trigger_name：標識觸發器名稱，使用者自行指定；
trigger_time：標識觸發時機，取值為 BEFORE 或 AFTER；
trigger_event：標識觸發事件，取值為 INSERT、UPDATE 或 DELETE；
tbl_name：標識建立觸發器的表名，即在哪張表上建立觸發器；
trigger_stmt：觸發器程式體，可以是一句SQL語句，或者用 BEGIN 和 END 包含的多條語句。

由此可見，可以建立6種觸發器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一個限制是不能同時在一個表上建立2個相同類型的觸發器，因此在一個表上最多建立6個觸發器。

 插入前CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 插入後CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 刪除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 刪除後CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN    ...END# 更新後CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN    ...END

 

觸發器無法由使用者直接調用，是在使用者對錶的增刪改操作觸發的

drop  trigger  tri_after_insert_cmd  #刪除觸發器

 

 

 

視圖

 

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

 

使用視圖我們可以把查詢過程中的暫存資料表摘出來，用視圖去實現，這樣以後再想操作該暫存資料表的資料時就無需重寫複雜的sql了，直接去視圖中尋找即可，但視圖有明顯地效率問題，並且視圖是存放在資料庫中的，如果我們程式中使用的sql過分依賴資料庫中的視圖，即強耦合，那就意味著擴充sql極為不便，因此並不推薦使用。

建立視圖

 

#文法：CREATE VIEW 視圖名稱 AS  SQL語句create view teacher_view as select tid from teacher where tname=‘李平老師‘;#於是查詢李平老師教授的課程名的sql可以改寫為mysql> select cname from course where teacher_id = (select tid from teacher_view);+--------+| cname  |+--------+| 物理   || 美術   |+--------+rows in set (0.00 sec)#！！！注意注意注意：#1. 使用視圖以後就無需每次都重寫子查詢的sql，但是這麼效率並不高，還不如我們寫子查詢的效率高#2. 而且有一個致命的問題：視圖是存放到資料庫裡的，如果我們程式中的sql過分依賴於資料庫中存放的視圖，那麼意味著，一旦sql需要修改且涉及到視圖的部分，則必須去資料庫中進行修改，而通常在公司中資料庫有專門的DBA負責，你要想完成修改，必須付出大量的溝通成本DBA可能才會幫你完成修改，極其地不方便

#修改視圖記錄，原始表也跟著改

 

　　

 

 

修改視圖

文法：ALTER VIEW 視圖名稱 AS SQL語句mysql> alter view teacher_view as select * from course where cid>3;Query OK, 0 rows affected (0.04 sec)mysql> select * from teacher_view;+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+|   4 | xxx   |          2 ||   5 | yyy   |          2 |+-----+-------+------------+rows in set (0.00 sec)

　　

 

刪除視圖

文法：DROP VIEW 視圖名稱DROP VIEW teacher_view

　　

 

 

 

 

 

儲存過程

預存程序包含了一系列可執行檔sql語句，預存程序存放於MySQL中，通過調用它的名字可以執行其內部的一堆sql

使用預存程序的優點：

#1. 用於替代程式寫的SQL語句，實現程式與sql解耦#2. 基於網路傳輸，傳別名的資料量小，而直接傳sql資料量大

 

使用預存程序的缺點：

#1. 程式員擴充功能不方便

　　

補充：程式與資料庫結合使用的三種方式

#方式一：    MySQL：預存程序    程式：調用預存程序#方式二：    MySQL：    程式：純SQL語句#方式三：    MySQL:    程式：類和對象，即ORM（本質還是純SQL語句）

　　

 
建立簡單的預存程序（無參）

delimiter //create procedure p1()BEGIN    select * from blog;    INSERT into blog(name,sub_time) values("xxx",now());END //delimiter ;#在mysql中調用call p1() #在python中基於pymysql調用cursor.callproc(‘p1‘) print(cursor.fetchall())

　　

建立預存程序（有參）

對於預存程序，可以接收參數，其參數有三類：#in          僅用於傳入參數用#out        僅用於傳回值用#inout     既可以傳入又可以當作傳回值

　　

delimiter //create procedure p2(    in n1 int,    in n2 int)BEGIN    select * from blog where id > n1;END //delimiter ;#在mysql中調用call p2(3,2)#在python中基於pymysql調用cursor.callproc(‘p2‘,(3,2))print(cursor.fetchall())in:傳入參數

　　

delimiter //create procedure p3(    in n1 int,    out res int)BEGIN    select * from blog where id > n1;    set res = 1;END //delimiter ;#在mysql中調用set @res=0; #0代表假（執行失敗），1代表真（執行成功）call p3(3,@res);select @res;#在python中基於pymysql調用cursor.callproc(‘p3‘,(3,0)) #0相當於set @res=0print(cursor.fetchall()) #查詢select的查詢結果cursor.execute(‘select @_p3_0,@_p3_1;‘) #@p3_0代表第一個參數，@p3_1代表第二個參數，即傳回值print(cursor.fetchall())out：傳回值

　　

delimiter //create procedure p4(    inout n1 int)BEGIN    select * from blog where id > n1;    set n1 = 1;END //delimiter ;#在mysql中調用set @x=3;call p4(@x);select @x;#在python中基於pymysql調用cursor.callproc(‘p4‘,(3,))print(cursor.fetchall()) #查詢select的查詢結果cursor.execute(‘select @_p4_0;‘) print(cursor.fetchall())inout:既可以傳入又可以返回

　　

執行預存程序

-- 無參數call proc_name()-- 有參數，全incall proc_name(1,2)-- 有參數，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)執行預存程序在MySQL中執行預存程序-- 無參數call proc_name()-- 有參數，全incall proc_name(1,2)-- 有參數，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)執行預存程序在MySQL中執行預存程序

#!/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)# 執行預存程序cursor.callproc(‘p1‘, args=(1, 22, 3, 4))# 擷取執行完儲存的參數cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result)在python中基於pymysql執行預存程序

　　

刪除預存程序

drop procedure proc_name;

　

 

　

函數

MySQL中提供了許多內建函數，例如：

一、數學函數    ROUND(x,y)        返回參數x的四捨五入的有y位小數的值    RAND()        返回０到１內的隨機值,可以通過提供一個參數(種子)使RAND()隨機數產生器產生一個指定的值。二、彙總函式(常用於GROUP BY從句的SELECT查詢中)    AVG(col)返回指定列的平均值    COUNT(col)返回指定列中非NULL值的個數    MIN(col)返回指定列的最小值    MAX(col)返回指定列的最大值    SUM(col)返回指定列的所有值之和    GROUP_CONCAT(col) 返回由屬於一組的列值串連組合而成的結果    三、字串函數    CHAR_LENGTH(str)        傳回值為字串str 的長度，長度的單位為字元。一個多位元組字元算作一個單字元。    CONCAT(str1,str2,...)        字串拼接        如有任何一個參數為NULL ，則傳回值為 NULL。    CONCAT_WS(separator,str1,str2,...)        字串拼接（自訂串連符）        CONCAT_WS()不會忽略任何Null 字元串。 (然而會忽略所有的 NULL）。    CONV(N,from_base,to_base)        進位轉換        例如：            SELECT CONV(‘a‘,16,2); 表示將 a 由16進位轉換為2進位字串表示    FORMAT(X,D)        將數字X 的格式寫為‘#,###,###.##‘,以四捨五入的方式保留小數點後 D 位， 並將結果以字串的形式返回。若  D 為 0, 則返回結果不帶有小數點，或不含小數部分。        例如：            SELECT FORMAT(12332.1,4); 結果為： ‘12,332.1000‘    INSERT(str,pos,len,newstr)        在str的指定位置插入字串            pos：要替換位置其實位置            len：替換的長度            newstr：新字串        特別的：            如果pos超過原字串長度，則返回原字串            如果len超過原字串長度，則由新字串完全替換    INSTR(str,substr)        返回字串 str 中子字串的第一個出現位置。    LEFT(str,len)        返回字串str 從開始的len位置的子序列字元。    LOWER(str)        變小寫    UPPER(str)        變大寫    REVERSE(str)        返回字串 str ，順序和字元順序相反。    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)        不帶有len 參數的格式從字串str返回一個子字串，起始於位置 pos。帶有len參數的格式從字串str返回一個長度同len字元相同的子字串，起始於位置 pos。 使用 FROM的格式為標準 SQL 文法。也可能對pos使用一個負值。假若這樣，則子字串的位置起始於字串結尾的pos 字元，而不是字串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。        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‘四、日期和時間函數    CURDATE()或CURRENT_DATE() 返回當前的日期    CURTIME()或CURRENT_TIME() 返回當前的時間    DAYOFWEEK(date)   返回date所代表的一星期中的第幾天(1~7)    DAYOFMONTH(date)  返回date是一個月的第幾天(1~31)    DAYOFYEAR(date)   返回date是一年的第幾天(1~366)    DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);    FROM_UNIXTIME(ts,fmt)  根據指定的fmt格式，格式化UNIX時間戳記ts    HOUR(time)   返回time的小時值(0~23)    MINUTE(time)   返回time的分鐘值(0~59)    MONTH(date)   返回date的月份值(1~12)    MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);    NOW()    返回當前的日期和時間    QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);    WEEK(date)   返回日期date為一年中第幾周(0~53)    YEAR(date)   返回日期date的年份(1000~9999)    重點:    DATE_FORMAT(date,format) 根據format字串格式化date值       mysql> SELECT DATE_FORMAT(‘2009-10-04 22:23:00‘, ‘%W %M %Y‘);        -> ‘Sunday October 2009‘       mysql> SELECT DATE_FORMAT(‘2007-10-04 22:23:00‘, ‘%H:%i:%s‘);        -> ‘22:23:00‘       mysql> SELECT DATE_FORMAT(‘1900-10-04 22:23:00‘,        ->                 ‘%D %y %a %d %m %b %j‘);        -> ‘4th 00 Thu 04 10 Oct 277‘       mysql> SELECT DATE_FORMAT(‘1997-10-04 22:23:00‘,        ->                 ‘%H %k %I %r %T %S %w‘);        -> ‘22 22 10 10:23:00 PM 22:23:00 00 6‘       mysql> SELECT DATE_FORMAT(‘1999-01-01‘, ‘%X %V‘);        -> ‘1998 52‘       mysql> SELECT DATE_FORMAT(‘2006-06-00‘, ‘%d‘);        -> ‘00‘五、加密函數    MD5()            計算字串str的MD5校正和    PASSWORD(str)           返回字串str的加密版本，這個加密過程是無法復原轉的，和UNIX密碼加密過程使用不同的演算法。六、控制流程函數                CASE WHEN[test1] THEN [result1]...ELSE [default] END        如果testN是真，則返回resultN，否則返回default    CASE [test] WHEN[val1] THEN [result]...ELSE [default]END          如果test和valN相等，則返回resultN，否則返回default    IF(test,t,f)           如果test是真，返回t；否則返回f    IFNULL(arg1,arg2)         如果arg1不是空，返回arg1，否則返回arg2    NULLIF(arg1,arg2)         如果arg1=arg2返回NULL；否則返回arg1       

 

自訂函數

#！！！注意！！！#函數中不要寫sql語句（否則會報錯），函數僅僅只是一個功能，是一個在sql中被應用的功能#若要想在begin...end...中寫sql，請用預存程序

 

delimiter //create function f1(    i1 int,    i2 int)returns intBEGIN    declare num int;    set num = i1 + i2;    return(num);END //delimiter ;delimiter //create function f5(    i int)returns intbegin    declare res int default 0;    if i = 10 then        set res=100;    elseif i = 20 then        set res=200;    elseif i = 30 then        set res=300;    else        set res=400;    end if;    return res;end //delimiter ;

　　

刪除函數

drop function func_name;

　　

執行函數

# 擷取傳回值select UPPER(‘egon‘) into @res;SELECT @res;# 在查詢中使用select f1(11,nid) ,name from tb2;

　　

 

mysql內建功能