MySQL資料庫(二)

標籤：存在   內建函數   set   sel   交易處理   多位元組   保留   字串   --   

事務

• 資料庫開啟事務命令

#start transaction 開啟事務#Rollback 復原事務,即撤銷指定的sql語句(只能回退insert delete update語句)，復原到上一次commit的位置#Commit 提交事務，提交未儲存的事務#savepoint 保留點 ，交易處理中設定的臨時預留位置 你可以對它發布回退(與整個交易回復不同)

• 事務操作示範

create table account(    id int,    name varchar(32),    balance double);insert into account values(1,"alex",8000);insert into account values(2,"egon",2000);#方式一: 更改資料後復原,資料回到原來select * from account;    +------+------+---------+    | id   | name | balance |    +------+------+---------+    |    1 | alex |    8000 |    |    2 | egon |    2000 |    +------+------+---------+start transaction;      #開啟事務後,更改資料探索資料變化update account set balance=balance-1000 where id=1;   #alex減去1000select * from account;    +------+------+---------+    | id   | name | balance |    +------+------+---------+    |    1 | alex |    7000 |    |    2 | egon |    2000 |    +------+------+---------+rollback;               #復原後,探索資料回到原來select * from account;    +------+------+---------+    | id   | name | balance |    +------+------+---------+    |    1 | alex |    8000 |    |    1 | egon |    2000 |    +------+------+---------+#方式二： 更改資料後提交select * from account;    +------+------+---------+    | id   | name | balance |    +------+------+---------+    |    1 | alex |    8000 |    |    2 | egon |    2000 |    +------+------+---------+update account set balance=balance-1000 where id=1;pdate account set balance=balance+1000 where id=2;Commit; select * from account;    +------+------+---------+    | id   | name | balance |    +------+------+---------+    |    1 | alex |    7000 |    |    2 | egon |    3000 |    +------+------+---------+

• python中調用資料庫啟動事務

import pymysql#添加資料conn = pymysql.connect(host='10.37.129.3', port=3306, user='egon', passwd='123456', db='wuSir',charset="utf8")cursor = conn.cursor()try:    insertSQL0="INSERT INTO account (name,balance) VALUES ('oldboy',4000)"    insertSQL1="UPDATE account set balance=balance-1000 WHERE id=1"    insertSQL2="UPDATE account set balance=balance+1000 WHERE id=2"    cursor = conn.cursor()    cursor.execute(insertSQL0)    conn.commit()                       #主動觸發Exception,交易回復,復原到上面conn.commit()     cursor.execute(insertSQL1)    raise Exception    cursor.execute(insertSQL2)    cursor.close()    conn.commit()except Exception as e:    conn.rollback()    conn.commit()cursor.close()conn.close()

視圖

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

• 建立視圖

#格式：CREATE VIEW 視圖名稱 AS  SQL語句  #示範create view v1 as select * from student where sid > 10; #下次調用select * from v1;

• 刪除視圖

#格式：DROP VIEW 視圖名稱 #示範drop view v1;

• 修改視圖

#格式：ALTER VIEW 視圖名稱 AS SQL語句 #示範alter view v1 as select * from student where sid > 13;

觸發器

對某個表進行【增/刪/改】操作的前後如果希望觸發某個特定的行為時，可以使用觸發器，觸發器用於定製使用者對錶的行進行【增/刪/改】前後的行為。
特別的：NEW表示即將插入的資料行，OLD表示即將刪除的資料行。

• 建立基本文法

# 插入前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

• 觸發器示範

#----------------------樣本一：#建立觸發器delimiter //create trigger t2 before insert on student for each rowBEGINinsert into teacher(tname) values("張三");END //delimiter ;#student插入資料,查看teacher表中是否有資料insert into student(gender,class_id,sname) values("女",3,"王五");#刪除觸發器drop trigger t2;#----------------------樣本二#重新建立  new   delimiter //create trigger t2 before insert on student for each rowBEGINinsert into teacher(tname) values(NEW.sname);END //delimiter ;#建立的觸發的name 相同insert into student(gender,class_id,sname) values("女",3,"李五");

函數

• 部分內建函數

CHAR_LENGTH(str)        傳回值為字串str 的長度，長度的單位為字元。一個多位元組字元算作一個單字元。        對於一個包含五個二位元組字元集, LENGTH()傳回值為 10, 而CHAR_LENGTH()的傳回值為5。    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)        變大寫    LTRIM(str)        返回字串 str ，其引導空白字元被刪除。    RTRIM(str)        返回字串 str ，結尾空白字元被刪去。    SUBSTRING(str,pos,len)        擷取字串子序列    LOCATE(substr,str,pos)        擷取子序列索引位置    REPEAT(str,count)        返回一個由重複的字串str 組成的字串，字串str的數目等於count 。        若 count <= 0,則返回一個Null 字元串。        若str 或 count 為 NULL，則返回 NULL 。    REPLACE(str,from_str,to_str)        返回字串str 以及所有被字串to_str替代的字串from_str 。    REVERSE(str)        返回字串 str ，順序和字元順序相反。    RIGHT(str,len)        從字串str 開始，返回從後邊開始len個字元組成的子序列    SPACE(N)        返回一個由N空格組成的字串。    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'    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)        返回字串 str ， 其中所有remstr 首碼和/或尾碼都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項，在未指定情況下，可刪除空格。        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'

https://dev.mysql.com/doc/refman/5.7/en/functions.html

• 顯示時間月份

select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m") 2019-11   22019-10   2

• 自訂函數

#自訂函數delimiter //create function f1(    i1 int,    i2 int)returns intBEGIN    declare num int;    set num = i1 + i2;    return(num);END //delimiter ;#執行函數select f1(1,99);+----------+| f1(1,99) |+----------+|      100 |+----------+

• 刪除函數

#drop function func_name;

• 執行函數

# 擷取傳回值declare @i VARCHAR(32);select UPPER('alex') into @i;SELECT @i;# 在查詢中使用select f1(11,nid) ,name from tb2;

預存程序

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

• 建立預存程序

1. 無參數預存程序 示範

#定義預存程序delimiter //create procedure p1()BEGIN  select * from student;  insert into teacher(tname) values("alex");END //delimiter ;#調用call p1();

1. pymysql 調用預存程序

import pymysqlconn = pymysql.connect(host='10.37.129.3', port=3306, user='egon', passwd='123456', db='wuSir',charset="utf8")cursor = conn.cursor()cursor.callproc("p1")conn.commit()result = cursor.fetchall()print(result)cursor.close()conn.close()

MySQL資料庫(二)