SQL 彙整 - VECTOR COOL 威得數位行銷

[mysql] 外鍵 Foreign Key 速查

2016-09-09 作者 ann71727

當關聯父資料表的主鍵紀錄行被刪除或修改時，InnoDB 對子資料表中紀錄行的處理方式：
CASCADE – 會將有所關聯的紀錄行也會進行刪除或修改。
SET NULL – 會將有所關聯的紀錄行設定成 NULL。
NO ACTION – 有存在的關聯紀錄行時，會禁止父資料表的刪[……]

MIS WAMP手動安裝及設定教學 Apache2.4 + PHP7 for windows10

2017-09-152016-09-01 作者 ann71727

手動安裝 Apache2.4 + PHP7 for windows10

首先，我該選x64或x86安裝呢?

何謂 x64 和 x86，可閱讀下方網址文章，本篇不多做解釋
x86和x64到底有什麼差異？

http://www.ithome.com.tw/node/56880

注意 !!![……]

[SQL] MySQL to PostgreSQL 好痛轉移，SQL語法差異

2016-08-31 作者 ann71727

換資料庫真的好機掰，會有一堆未知問題，見招拆招，紀錄於下

LIMIT OFFSET

<span style="color: #9999a9;">/* MySQL */</span><br /><span style="color: #e66170; font-weight: bold;">SELECT</span> <span style="color: #b060b0;">*</span> <span style="color: #e66170; font-weight: bold;">FROM</span> abc <span style="color: #e66170; font-weight: bold;">WHERE</span> <span style="color: #e66170; font-weight: bold;">status</span><span style="color: #d2cd86;">=</span><span style="color: #008c00;">1</span> <span style="color: #e66170; font-weight: bold;">ORDER</span> <span style="color: #e66170; font-weight: bold;">BY</span> create_time <span style="color: #e66170; font-weight: bold;">DESC</span> <span style="color: #e66170; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">0</span> <span style="color: #b060b0;">,</span> <span style="color: #008c00;">10</span><br /><span style="color: #9999a9;">/* PostgreSQL */</span><br /><span style="color: #e66170; font-weight: bold;">SELECT</span> <span style="color: #b060b0;">*</span> <span style="color: #e66170; font-weight: bold;">FROM</span> abc <span style="color: #e66170; font-weight: bold;">WHERE</span> <span style="color: #e66170; font-weight: bold;">status</span><span style="color: #d2cd86;">=</span><span style="color: #008c00;">1</span> <span style="color: #e66170; font-weight: bold;">ORDER</span> <span style="color: #e66170; font-weight: bold;">BY</span> create_time <span style="color: #e66170; font-weight: bold;">DESC</span> <span style="color: #e66170; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">10</span> OFFSET <span style="color: #008c00;">0</span>

/* MySQL */ SELECT * FROM abc WHERE status=1 ORDER BY create_time DESC LIMIT 0 , 10 /* PostgreSQL */ SELECT * FROM abc WHERE status=1 ORDER BY create_time DESC LIMIT 10 OFFSET 0

# UPDATE 不能加 ORDER BY and LIMIT
/* MySQL */
UPDATE abc SET aaa=1 WHERE id<=10[……]

MySQL 判斷兩個時間區間是否重疊 Determine Whether Two Date Ranges Overlap

2015-10-31 作者 ann71727

判斷兩個時間區間是否重疊

<span style="background: rgb(40, 24, 0); color: #f6c1d0;"><pre style="background: rgb(0, 0, 0); color: #d1d1d1;">

1	<span style="background: rgb(40, 24, 0); color: #f6c1d0;"><pre style="background: rgb(0, 0, 0); color: #d1d1d1;">

<pre style="background: rgb(0, 0, 0); color: #d1d1d1;"><span style="background: rgb(40, 24, 0); color: #f6c1d0;">&lt;?php</span><span style="background: rgb(40, 24, 0); color: white;"></span><br /><span style="background: rgb(40, 24, 0); color: white;"></span><br /><span style="background: rgb(40, 24, 0); color: #9999a9;">/* 判斷兩個時間區間是否重疊 */</span><span style="background: rgb(40, 24, 0); color: white;"></span><br /><span style="background: rgb(40, 24, 0); color: #9999a9;">/* that it is sufficient to say that the two ranges overlap */</span><span style="background: rgb(40, 24, 0); color: white;"></span><br /><span style="background: rgb(40, 24, 0); color: white;">WHERE </span><span style="background: rgb(40, 24, 0); color: #d2cd86;">(</span><span style="background: rgb(40, 24, 0); color: white;">StartDate1 </span><span style="background: rgb(40, 24, 0); color: #d2cd86;">&lt;</span><span style="background: rgb(40, 24, 0); color: #d2cd86;">=</span><span style="background: rgb(40, 24, 0); color: white;"> EndDate2</span><span style="background: rgb(40, 24, 0); color: #d2cd86;">)</span><span style="background: rgb(40, 24, 0); color: white;"> </span><span style="background: rgb(40, 24, 0); color: #d2cd86;">AND</span><span style="background: rgb(40, 24, 0); color: white;"> </span><span style="background: rgb(40, 24, 0); color: #d2cd86;">(</span><span style="background: rgb(40, 24, 0); color: white;">StartDate2 </span><span style="background: rgb(40, 24, 0); color: #d2cd86;">&lt;</span><span style="background: rgb(40, 24, 0); color: #d2cd86;">=</span><span style="background: rgb(40, 24, 0); color: white;"> EndDate1</span><span style="background: rgb(40, 24, 0); color: #d2cd86;">)</span><span style="background: rgb(40, 24, 0); color: white;"></span><br /><span style="background: rgb(40, 24, 0); color: white;"></span><br /><span style="background: rgb(40, 24, 0); color: #f6c1d0;">?&gt;</span>