文章

目前顯示的是 六月, 2018的文章

如何使用EXCEL連接MYSQL

圖片
(1) 建立ODBC連線 取得ODBC驅動程式並安裝 https://dev.mysql.com/downloads/connector/odbc/ Install MySQL Connector ODBC (2) 建立資料來源 (3) 啟用EXCEL的開發人員 (4) 設定巨集安全性 (5) 透過VBA使用ODBC連線MYSQL 範例一、從MYSQL抓出資料秀在EXCEL上 Sub ShowData() Range("A1:C99").Clear On Error GoTo ErrorHandler Set myCon = CreateObject("ADODB.Connection") myCon.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};Server= server-ip ;Database= dbname ;User= root ;Password= yourpassword ;Option=3;" A = CStr(Cells(1, 1)) B = CStr(Cells(1, 2)) Sql = "select * from t" Set myRs = myCon.Execute(Sql) Range("A3").CopyFromRecordset myRs Exit Sub ErrorHandler: MsgBox (Err.Description) End Sub 範例二、插入EXCEL上的資料到MYSQL表單 Sub InsertData() On Error GoTo ErrorHandler Set myCon = CreateObject("ADODB.Connection") myCon.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};Server= server-ip ;Database= dbname ;User= root ;Password=

MySQL Lock : Table Lock與Row Lock

圖片
I 、基本觀念 問題:什麼是 Lock (鎖定) ? 答案:Lock 的主要目的是避免資料發生錯誤,把不應該進行動作的指令排除在外,並讓應該進行動作的指令能夠順利完成。 問題:什麼是Table Lock (表單鎖定) ? 什麼是Row Lock (紀錄鎖定)? 答案:表單鎖定就是將整個資料表鎖定,讓其他連線無法讀取及異動,直到資料處理完畢為止。紀錄鎖定就是將指定的紀錄鎖定,讓其他連線無法讀取及異動,直到資料處理完畢為止。 問題:InnoDB與MyISAM的鎖定有何差別? 答案:MyISAM 沒有交易功能 (Transaction),若要避免多個連線交互執行 SQL 指令,造成資料錯亂,只好使用鎖定資料表 (Table Lock) 的方式,InnoDB則可以使用Table Lock 與Row Lock。 問題:MySQL不同版本的鎖定有何差別? 答案:MySQL不同版本的鎖定原理一樣,只是語法上有些差異。 例如 : MySQL 5.5 使用 Select ... lock in share mode; MySQL 5.5 使用 Select ... for update; MySQL 8.0 還可以使用 Select ... for share; MySQL 8.0 還可以使用 Select ... for update NOWAIT; II、實際範例 以下來看看這個版本的MySQL怎麼進行資料鎖定吧。 檢查MYSQL版本 SHOW VARIABLES LIKE "%version%"; 建立一個表單 CREATE TABLE T ( ID INT NOT NULL, F1 INT, PRIMARY KEY (ID)); 插入資料 INSERT INTO T (ID,F1) VALUES (1,2), (2,1), (3,2), (4,4); (1) TABLE level LOCK for READ LOCK TABLE T READ; 這是屬於表單鎖定,也就是鎖定整個表單。自身連線只能針對該表單T讀取資料,不能寫入更改該表單T,也不能再去讀取其他表單。其他連線可以讀取該表單T,但是不能寫入更改該表單T(會進入等待,直到解除)。 UNLOCK TABL

實機練習

圖片
需求~建立一個資料庫系統,紀錄公司的產品資料,並且記錄客戶的訂單、進貨、出貨、收款。 需要產出以下內容以及回答問題~ (因為答案不是只有一種,資料表結構不同,想法就可能不同) (1) 這個系統的ERD  (2) 這個系統的資料表單結構(Schema) (3) 老闆想知道在某個時間區間內,哪個客戶購買金額最大?最少? 思考 : 從這個需求知道,需要一個客戶資料表(customer),以及紀錄訂購的資料表,因為order是保留字,所以我們用order_main當訂購的表頭檔,用order_body當訂購的表身檔。 為何紀錄訂購資料需要訂購的表頭檔跟訂購的表身檔呢? 要知道某個時間區間內,哪個客戶購買金額最大?最少? 需要客戶資料表(customer)、order_main 與 order_body。 某個時間區間內,哪個客戶購買金額最大?最少? --> 從客戶資料表+訂購的表頭檔+訂購的表身檔,找到加總訂購金額最大( 最少 )的客戶名稱,條件是訂購資料在特定時間區間內。 (4) 老闆想知道目前總共還有多少應收款? 應收款最多的是哪個客戶? 思考 : 從這個需求知道,訂購的價錢需要紀錄已付或是未付,這個可以用一個欄位來表示,你可以有一個paid欄位,放在訂購的表頭檔或是訂購的表身檔。當然這個paid欄位最好再紀錄付款日期paid_date。或是省下paid欄位,只要有paid_date資料,表示已付。 已付或是未付放在訂購的表頭檔或是訂購的表身檔,差異在哪裡呢? 目前總共還有多少應收款?  只要把時間區間內未付的加總就好啦。應收款最多的是哪個客戶? 也是需要客戶資料表(customer)、order_main 與 order_body。 老闆想知道目前總共還有多少應收款? --> 從訂購的表頭檔+訂購的表身檔,找到未付款的加總。 應收款最多的是哪個客戶? --> 從客戶資料表+訂購的表頭檔+訂購的表身檔,找到未付款的加總最大的客戶名稱。 (5) 老闆想知道目前還有那些未出貨的產品? 那些未出貨的產品是缺貨的? 那些產品庫存量是低於安全庫存量的? 思考 : 因為出貨一定是根據訂單的資料,所以紀錄是否已經出貨,可以使用一個shipped欄位來記錄。如果你要完整記錄出貨,也可以另外用一個

(範例) MySQL Stored Procedure/Stored Function/Trigger

圖片
在MySQL中,把Procedure和Function統稱為Routine,我們會把常用的程序用Stored Procedure或是Stored Function來表示,需要的時候就可以重複呼叫。 Stored Procedure(預儲程序):把一連串的SQL程序步驟儲存起來,最後透過 『call 預儲程序名稱;』來呼叫。 Stored Function(預儲函數):或稱為使用者定義函數,跟預儲程序很像,不過最後會傳回值。呼叫方式不是 call,而是Select,例如:『select 預儲函數名稱(引數值);』或『select 預儲函數名稱();』。 MySQL 正式支援觸發器(trigger)是在 MySQL 5.0.2 的版本之後。觸發器是註冊在資料庫表格上的程式。所以在事件發生(對資料列做新增/修改/刪除)時,資料庫會依照觸發條件(事件前/事件後)幫你執行預先儲存好的程式。 現在來看看一個實際需求的案例 ~ 資料表格式如下 Student(sid, idno, sname, did, syear, sclass) OpenCourse(oid, cid, tid, yearlimit, roomno, tot) Schedule(oid, timeno) Roll(sid, oid, score) 現在需要建立預存程序/預存函數/觸發 【需求】 預存函數希望可以檢查學生要修某個開課編號的課程時,是否衝堂? 預存程序則是檢查是否衝堂之外,如果不衝堂,則加入選課資料表。 並且希望在加入選課資料表時,可以用觸發tot加1。 在刪除選課資料表時,可以用觸發tot減1。 【 STORED PROCEDURE 】 delimiter // drop procedure if exists addcourse// CREATE PROCEDURE addcourse(psid char(5), poid char(5), OUT flag int) BEGIN DECLARE a char(5); SET a =(SELECT oid FROM opencourse WHERE oid=poid and oid NOT IN (SELECT o.oid FROM opencourse o, schedule