tag:blogger.com,1999:blog-87399893373446604552024-02-21T16:50:37.293+08:00Hannibal愛用Open Source Software(OSS)Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.comBlogger166125tag:blogger.com,1999:blog-8739989337344660455.post-31233486643307597962023-03-11T20:05:00.002+08:002023-03-11T20:22:35.470+08:00LibreOffice線上書籍,可下載PDF或ODF或線上閱讀<iframe width=700px height=720px src="https://docs.google.com/document/d/e/2PACX-1vRyMYcHxNWIxtW_amS8z152p_MoacsgBeOec4WDEqF_jpKouft72R22loq5bdrcA-G3T_gAdTqJqMUI/pub?embedded=true"></iframe>Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-41748021593300489752022-06-16T16:57:00.003+08:002022-06-17T16:51:47.452+08:00《OpenOffice / LibreOffice Draw》繪製一個像React Logo的圖形<p style="background-color:#FFFFCC;font-weight:bold;">《<a href="https://blog.bod.idv.tw/">OSS首頁</a>》《<a href="https://blog.bod.idv.tw/2011/02/hannibalopenoffice.html" >文章分類列表</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Base">Base</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Calc">Calc</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Draw">Draw</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Impress">Impress</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Writer">Writer</a>》</p><ol style="text-align: left;"><li> 查看react logo的外觀及顏色。</li><ol type="i"><li>開啟網頁 <a href="https://zh-hant.reactjs.org/" target="_blank">https://zh-hant.reactjs.org/</a> </li><li>使用瀏覽器(以chrome為例)的開發人員工具→選取網頁中的元素 (React這個div)<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcFv897qOl_3E1hxyJVL1fxsdF5PgIV_MllOBGHBijMZXDatZmD1VakIw-Cpxd8wrrmmUT32i7qA7BJKXXCGCmheU9l7zPlwELKp4nokytWFX0bpADhpOxgZwU8wDP93fejqiCQRfVorR3y4L7HIypIVw5VCzAJKU8bZjhgtc72Ba6DGzaRpCRk-iM/s579/20220615_getReactLogoColor.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="372" data-original-width="579" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcFv897qOl_3E1hxyJVL1fxsdF5PgIV_MllOBGHBijMZXDatZmD1VakIw-Cpxd8wrrmmUT32i7qA7BJKXXCGCmheU9l7zPlwELKp4nokytWFX0bpADhpOxgZwU8wDP93fejqiCQRfVorR3y4L7HIypIVw5VCzAJKU8bZjhgtc72Ba6DGzaRpCRk-iM/s16000/20220615_getReactLogoColor.png" /></a></div><br /></li><li>假設logo的顏色跟React文字的顏色一樣,都是#61DAFB<br /><br /></li></ol><li>整理一下關於react logo圖形的描述。</li><ol type="i"><li>由三個橢圓形及一個圓形圖點組成</li><li>各圖形區塊的比例為→橢圓高:橢圓寬:圓高=6:2:1<br /><br /></li></ol><li>開啟Draw,顯示網格(方便對齊),繪製一個寬6cm、寬2cm的橢圓形,先不用考慮精確的尺寸及中心位置,再使用:格式(O)→位置和大小(Z)來調整即可。</li><ol type="i"><li>將Position的Base Point 調整至中心點,X: 4cm、Y: 4cm (點選一下 Position 圖示的正中心)</li><li>將Size的的Base Point 調整至中心點。</li><li>確定Size的Width是6cm,Height是2cm。<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj754oN4vIm7MpF5THoRP6Xi7ISTUK418CRpN2qWkU4hXhMwt-CKRPYRRhzeNZCLXEHLcTdHySswml5kJU2oA1Kv3w-9bZO78J_u3BYozWsLo9eBe_x7Tk8_v3U1kD4F76Pb1F6Ok9uqV7-6uwdJCvb9nxuZKn0u1EKgIwra31eyvqykPDlHpwWKpsx/s708/react-logo_01.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="548" data-original-width="708" height="496" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj754oN4vIm7MpF5THoRP6Xi7ISTUK418CRpN2qWkU4hXhMwt-CKRPYRRhzeNZCLXEHLcTdHySswml5kJU2oA1Kv3w-9bZO78J_u3BYozWsLo9eBe_x7Tk8_v3U1kD4F76Pb1F6Ok9uqV7-6uwdJCvb9nxuZKn0u1EKgIwra31eyvqykPDlHpwWKpsx/w640-h496/react-logo_01.png" width="640" /></a></div><br /></li></ol><li>調整橢圓的填滿及線條:</li><ol type="i"><li>將橢圓形區調整為無填滿:格式(O)→區域(R),選擇:None(無填滿)</li><li>調整線條:格式(O)→線條(I)</li><li>調整橢圓形外框線條的粗細:將線條的寬度設為0.15cm (或輸入4.5pt,會自動轉換為0.15cm)</li><li>調整橢圓形外框線條的顏色:自訂顏色,在Hex#輸入:61DAFB<br /><br /></li></ol><li>複製橢圓形,並調整旋轉的角度:</li><ol type="i"><li>選取橢圓,先複製再貼上,這時候兩個橢圓重疊在一起。</li><li>將已選取的橢圓,以中心點 (4cm,4cm),旋轉60度。格式(O)→位置和大小(Z)→旋轉,角度(Angle):60</li><li>再一次複製、貼上,再將選取的橢圓旋轉120度。<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjugC7gvts62luTLMx51M5X0wR1g4oWmWvETHjEPtIfDrTJCxbI-u0vaIQNo-wm8In1HjbYnbyvcJ7uj5Cl0gIfMhkvqy6isqN4vcv1DX74VJXheOS4MTcesJinf80CXVLpaqwEGWuDywQ6DLZBQbOR50UJcZy1S7cdbBzY-XdRgfcc5UDhhTryVkac/s708/react-logo_02.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="614" data-original-width="708" height="556" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjugC7gvts62luTLMx51M5X0wR1g4oWmWvETHjEPtIfDrTJCxbI-u0vaIQNo-wm8In1HjbYnbyvcJ7uj5Cl0gIfMhkvqy6isqN4vcv1DX74VJXheOS4MTcesJinf80CXVLpaqwEGWuDywQ6DLZBQbOR50UJcZy1S7cdbBzY-XdRgfcc5UDhhTryVkac/w640-h556/react-logo_02.png" width="640" /></a></div><br /></li></ol><li>繪製一個直徑1cm,顏色為#61DAFB(邊框及填滿)的園:</li><ol type="i"><li>格式(O)→位置和大小(Z):調整確認:中心點 (X, Y) 為 (4cm, 4cm)</li><li>將圓的區調整為填滿#61DAFB:格式(O)→區域(R),色彩Color Hex填入:61DAFB</li><li>外框的顏色,也調整為#61DAFB<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjXaL4GplYejNi6D8NwUG50WOZkU2MCgXbfVSrMlNqNFwlSiWlA19BdaqoGR3aeqOcaDjnXhtSDpAO78AypokvmVcZY-aAN9KpEUa-YRMsn4G5Q1tECi8_soVBtqtKUGahxTRQaQ69YtzgWgZdZlxva_VtXzFgQHnLG6gVNaVAB2URd2OVGP_i9vCN/s708/react-logo_03.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="585" data-original-width="708" height="528" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjXaL4GplYejNi6D8NwUG50WOZkU2MCgXbfVSrMlNqNFwlSiWlA19BdaqoGR3aeqOcaDjnXhtSDpAO78AypokvmVcZY-aAN9KpEUa-YRMsn4G5Q1tECi8_soVBtqtKUGahxTRQaQ69YtzgWgZdZlxva_VtXzFgQHnLG6gVNaVAB2URd2OVGP_i9vCN/w640-h528/react-logo_03.png" width="640" /></a></div><br /></li></ol><li>完成了!<br /><br /></li></ol><p></p>Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-20070848718859739832021-12-29T19:06:00.004+08:002022-06-17T16:52:53.485+08:00《OpenOffice / LibreOffice Draw》繪製一個太極圖案<p style="background-color:#FFFFCC;font-weight:bold;">《<a href="https://blog.bod.idv.tw/">OSS首頁</a>》《<a href="https://blog.bod.idv.tw/2011/02/hannibalopenoffice.html" >文章分類列表</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Base">Base</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Calc">Calc</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Draw">Draw</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Impress">Impress</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Writer">Writer</a>》</p><ol style="text-align: left;"><li>建立一個直徑6cm的大圓<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg05hztZeqN4Xao4Jgik1KeIYdsx8uUXjFmyBBzAcjwcTfgKOeNSv7apP21uarKz-_SvIz8lRprr39wn1GexkaEr6mCZqvCjnMol3WyOMlbLQG7dKVrQcloyZ6OgAXkGQAEFkwt-1GWXPmIM1W7KibUGfwg6N9tdoNjX16qCPYoNJIM9wA2Ie6gd2vD=s729" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="729" data-original-width="675" src="https://blogger.googleusercontent.com/img/a/AVvXsEg05hztZeqN4Xao4Jgik1KeIYdsx8uUXjFmyBBzAcjwcTfgKOeNSv7apP21uarKz-_SvIz8lRprr39wn1GexkaEr6mCZqvCjnMol3WyOMlbLQG7dKVrQcloyZ6OgAXkGQAEFkwt-1GWXPmIM1W7KibUGfwg6N9tdoNjX16qCPYoNJIM9wA2Ie6gd2vD=s16000" /></a></div><br /></li><li>在大圓內建立兩個直徑3公分的小圓<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEh3yolK8mek5_EubnzaTcXomjV_HlbFw4uB9MTfXU3J8sdqM6Y3Vs5qbiEKsczaNmJQAVVKSAhAt4dfjDjrT6ZBzS-yc1jh2BPB9GAfVDuw-5KDJHDqhbRLv2-fSnssg4cvc75vv1dxm5pXwLeWr1hlrQbbIUKvZ6WKLz-7XESWrei_lxieQR2EXDdR=s730" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="730" data-original-width="673" src="https://blogger.googleusercontent.com/img/a/AVvXsEh3yolK8mek5_EubnzaTcXomjV_HlbFw4uB9MTfXU3J8sdqM6Y3Vs5qbiEKsczaNmJQAVVKSAhAt4dfjDjrT6ZBzS-yc1jh2BPB9GAfVDuw-5KDJHDqhbRLv2-fSnssg4cvc75vv1dxm5pXwLeWr1hlrQbbIUKvZ6WKLz-7XESWrei_lxieQR2EXDdR=s16000" /></a></div><br /></li><li>選取這三個圓,並將這三個圓「結合」<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEimyDTJKutrjrmpa7tp81RdpbVcQYf7f1MnCEF8gKJnoXpOnWCpEHBAL9nz8Jvkd1q7VjkDynRB7sGrBR_tewUaGMELPbfaz-wIjAp799n-B6KGSuwIs0mEa-sAVrgjTccGUounAw5t3A0NIuxF02iA98yzA8qR5_crhWEvFQ6p0c_fbmLltWbbYgJF=s546" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="546" data-original-width="449" src="https://blogger.googleusercontent.com/img/a/AVvXsEimyDTJKutrjrmpa7tp81RdpbVcQYf7f1MnCEF8gKJnoXpOnWCpEHBAL9nz8Jvkd1q7VjkDynRB7sGrBR_tewUaGMELPbfaz-wIjAp799n-B6KGSuwIs0mEa-sAVrgjTccGUounAw5t3A0NIuxF02iA98yzA8qR5_crhWEvFQ6p0c_fbmLltWbbYgJF=s16000" /></a></div><br /></li><li>將這個結合的圖形「轉換至輪廓線」<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhKua2nDWgBMYjKW37DUt7p_qWEXlHQfsManE1hV6Q9llT77UFG9LgaSdfbRydBjwrl05pTbSBS7LE61mcCB2_mqjzRAOqIfnu-__JUPkPZzg6z9zZPcxBUmKxdtJvOODJwuvyy5KMJkGrRSSU-tRqRCOl8IOUo3IgAmfqHv1yaXSuTMXdIIk4iB1MA=s554" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="554" data-original-width="540" src="https://blogger.googleusercontent.com/img/a/AVvXsEhKua2nDWgBMYjKW37DUt7p_qWEXlHQfsManE1hV6Q9llT77UFG9LgaSdfbRydBjwrl05pTbSBS7LE61mcCB2_mqjzRAOqIfnu-__JUPkPZzg6z9zZPcxBUmKxdtJvOODJwuvyy5KMJkGrRSSU-tRqRCOl8IOUo3IgAmfqHv1yaXSuTMXdIIk4iB1MA=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">將輪廓線「分開」<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEh0X4k2g58KtzF2ipaz9To-SOpSK7yAlXXzsbWpv6MbBhO64hogQdajARM2IseZR0dP0PZOO5BSE7MspragC2lqXpIhCcz5ippG46WHduM_g0572prjsb4NrE_pnUQx5o0Sl3kLV_Vfr-tkhaHhKdC-vXTfvmbJrUZ7WSRkjuRv-2bsqMqD7iSOpJR9=s574" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="574" data-original-width="427" src="https://blogger.googleusercontent.com/img/a/AVvXsEh0X4k2g58KtzF2ipaz9To-SOpSK7yAlXXzsbWpv6MbBhO64hogQdajARM2IseZR0dP0PZOO5BSE7MspragC2lqXpIhCcz5ippG46WHduM_g0572prjsb4NrE_pnUQx5o0Sl3kLV_Vfr-tkhaHhKdC-vXTfvmbJrUZ7WSRkjuRv-2bsqMqD7iSOpJR9=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">將輪廓線「分裂」<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjS5o25ShqN7-0TGeiQoqWK1KgTjBGM1Mj7swnRrb9N6OZVnUawaNtu1rYlilLUvyXzw5dKLpsl1uYcvQVZxdqEu7fk7HFoM1pDG_PUNXU9x1pPlGKlEw1K3vD71CrtGcFKFW-p0cyrjpw9dlRzm05BiloumGlqBb5T3kh8cnrSGzpIGNc8YopaBivX=s581" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="581" data-original-width="424" src="https://blogger.googleusercontent.com/img/a/AVvXsEjS5o25ShqN7-0TGeiQoqWK1KgTjBGM1Mj7swnRrb9N6OZVnUawaNtu1rYlilLUvyXzw5dKLpsl1uYcvQVZxdqEu7fk7HFoM1pDG_PUNXU9x1pPlGKlEw1K3vD71CrtGcFKFW-p0cyrjpw9dlRzm05BiloumGlqBb5T3kh8cnrSGzpIGNc8YopaBivX=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">逐點選取不保留的接點「刪除接點」。<br />選取接點後,按Delete鍵刪除。或 選取接點後,按工具鈕「刪除接點」<br /><br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiDMPukI5u7QtWoIDZ9cTnzXCwXhiEHrT3qIWbdYUcWXCuVFGlIl-q8XFigxc2hQcK7EV6TvF4EhkGdidjXOqwCUNvate6DPxiZkLnYuBLAp96f8Y3ooLyiXoYQTYUpUC_NoEKMSSQC_hYlB2lPTiLnYt8MRPGhSsPZ6vwukU0cliNljLHiilSJHehY=s438" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="365" data-original-width="438" src="https://blogger.googleusercontent.com/img/a/AVvXsEiDMPukI5u7QtWoIDZ9cTnzXCwXhiEHrT3qIWbdYUcWXCuVFGlIl-q8XFigxc2hQcK7EV6TvF4EhkGdidjXOqwCUNvate6DPxiZkLnYuBLAp96f8Y3ooLyiXoYQTYUpUC_NoEKMSSQC_hYlB2lPTiLnYt8MRPGhSsPZ6vwukU0cliNljLHiilSJHehY=s16000" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj3T5-RsmuYjEbvtnaVBmEgdqzw8ylBmMmxIaon3a4etZ-i9E0GfGGs88vUH2ZQ18x9G4r6paazfVtiy8nI6SuIrjsprO97z2Wvkowf5rh9CVB8UieyscGke2M4s0UQRhxNJU4YnQX7U9LbAeZrW9G4aJF1q5p-JsSx_TVS1dD3aQPvwrsju2RR1qCi=s416" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="273" data-original-width="416" src="https://blogger.googleusercontent.com/img/a/AVvXsEj3T5-RsmuYjEbvtnaVBmEgdqzw8ylBmMmxIaon3a4etZ-i9E0GfGGs88vUH2ZQ18x9G4r6paazfVtiy8nI6SuIrjsprO97z2Wvkowf5rh9CVB8UieyscGke2M4s0UQRhxNJU4YnQX7U9LbAeZrW9G4aJF1q5p-JsSx_TVS1dD3aQPvwrsju2RR1qCi=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">逐段「連接」保留下來的線段:按住「Shift鍵」再點選下一線段,完成兩曲線段的選取,再進行「連接」<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjTUxSqRahjZriDdsmwNhhZHxkofZKhJCP5xJQu6fLx64rxxXESe-aXEh6cynUQDkespE-ElNsL_SM-DXOw677noAw5kjgVJeIgHcd0hiyfpToJwtsOzIWye5rO7xKv_tDFTNOXpBCtbt6WHs7QWpf5McbkFxzFTaHZk_1at_TQ9k8kmpHA3GGVE2C7=s501" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="501" data-original-width="413" src="https://blogger.googleusercontent.com/img/a/AVvXsEjTUxSqRahjZriDdsmwNhhZHxkofZKhJCP5xJQu6fLx64rxxXESe-aXEh6cynUQDkespE-ElNsL_SM-DXOw677noAw5kjgVJeIgHcd0hiyfpToJwtsOzIWye5rO7xKv_tDFTNOXpBCtbt6WHs7QWpf5McbkFxzFTaHZk_1at_TQ9k8kmpHA3GGVE2C7=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">全部曲線斷連接後,原區域內的顏色,會自動填滿。<br />將完成連接的曲線段,轉換至輪廓線。<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjYh0Wh2I_aUjRz6PfmP08XXrrIrGoXz-NcsDlnwytafZGITv3z9WhgRT1Y23mudhDVp78nTbhtG2q6G7x-Yyc2lZVzCgaquLHHUXmQ0gkQi6ANphHMdC3A3rjK2Zo66_ck9bbNXTs8qiajwDK62FGwU4UneRk9FZl3NgRv4nI93pF8nvkN4NYqioNt=s549" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="548" data-original-width="549" src="https://blogger.googleusercontent.com/img/a/AVvXsEjYh0Wh2I_aUjRz6PfmP08XXrrIrGoXz-NcsDlnwytafZGITv3z9WhgRT1Y23mudhDVp78nTbhtG2q6G7x-Yyc2lZVzCgaquLHHUXmQ0gkQi6ANphHMdC3A3rjK2Zo66_ck9bbNXTs8qiajwDK62FGwU4UneRk9FZl3NgRv4nI93pF8nvkN4NYqioNt=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">複製這個區域,並貼上。將其中一區域垂直反轉,在水平反轉。調整位置,將兩圖組合成一個圓。<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiuL2jP724UkQTHez0UjONJpUxBwX82uZyD465BnuWuHcw-W1S4PwqWmg6sUj3dS6Ewf8gTvCyqywJ0RyQpcM3wkqh3MaKA7yrto157jIZilCJoU7MYf5RXkDAmLC-N8gLdelyYfbQF7qyI2WrECAquqsYfScu5PfJTFHaQOXaRbeaSyaAyrstT9hXy=s710" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="710" data-original-width="674" src="https://blogger.googleusercontent.com/img/a/AVvXsEiuL2jP724UkQTHez0UjONJpUxBwX82uZyD465BnuWuHcw-W1S4PwqWmg6sUj3dS6Ewf8gTvCyqywJ0RyQpcM3wkqh3MaKA7yrto157jIZilCJoU7MYf5RXkDAmLC-N8gLdelyYfbQF7qyI2WrECAquqsYfScu5PfJTFHaQOXaRbeaSyaAyrstT9hXy=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">在兩區域中分別填入黑色、白色。再加入兩個1cm直徑的小圓,一黑一白。<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgW0n241mXJ_WOWRIqgJFeJxSmCbGixuCCKlgKHrWbmw3YmtzCoz1SNOZ6yMiLHOnWVREcaj54BGGTfh_veydJBTKdNy_R0JJ1WIh9EtjhFVKlNiHIyuyhMlLZI6ypBZqcljl9Mq77GU26G5X4X7iChb9ONzBvPRBWO3r8l4vUBsIrQ34wMVnboI2vZ=s681" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="681" data-original-width="647" src="https://blogger.googleusercontent.com/img/a/AVvXsEgW0n241mXJ_WOWRIqgJFeJxSmCbGixuCCKlgKHrWbmw3YmtzCoz1SNOZ6yMiLHOnWVREcaj54BGGTfh_veydJBTKdNy_R0JJ1WIh9EtjhFVKlNiHIyuyhMlLZI6ypBZqcljl9Mq77GU26G5X4X7iChb9ONzBvPRBWO3r8l4vUBsIrQ34wMVnboI2vZ=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">依照需求,轉換成所需的檔案格式。<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhrLdH8A-Wh18p_XDNXCnJH_iSsi8n4k0ySXnZbo6OXkHtSnasGhKSm8ncKEaWEdqA8ZpMDP11k2HRFDrbD9_Ny3Ba_Gn7J56TWe5CXETDkILz3I-OKcvXaadH_pft04vR8Wy2MbR-blhLrgDiHyObf4hSXZvmy2S1Td0E1AMo3QpEVjRLvd64c63Bq=s543" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="543" data-original-width="509" src="https://blogger.googleusercontent.com/img/a/AVvXsEhrLdH8A-Wh18p_XDNXCnJH_iSsi8n4k0ySXnZbo6OXkHtSnasGhKSm8ncKEaWEdqA8ZpMDP11k2HRFDrbD9_Ny3Ba_Gn7J56TWe5CXETDkILz3I-OKcvXaadH_pft04vR8Wy2MbR-blhLrgDiHyObf4hSXZvmy2S1Td0E1AMo3QpEVjRLvd64c63Bq=s16000" /></a></div><br /></li><li><p lang="zh-TW" style="margin-bottom: 0cm;">選取圖形,另存圖片。<br /><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgPA3qIDEau6i0lPzE2PzlJH6ohLDqNJLniQJ1oK5rUJYIhpIQfYDo21bR-91fjuXBd-53tCPIh0S9ROg2FQyPXrxEeLDrj3tA9slkHAtH6G9-sfer48x5sws8kAeHPG26wXHg1xyu4IZS8sFdzUS4g4i9C0ACumkLPDnj6F_Xb5rdTY9KAG96GByIo=s648" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="648" data-original-width="428" src="https://blogger.googleusercontent.com/img/a/AVvXsEgPA3qIDEau6i0lPzE2PzlJH6ohLDqNJLniQJ1oK5rUJYIhpIQfYDo21bR-91fjuXBd-53tCPIh0S9ROg2FQyPXrxEeLDrj3tA9slkHAtH6G9-sfer48x5sws8kAeHPG26wXHg1xyu4IZS8sFdzUS4g4i9C0ACumkLPDnj6F_Xb5rdTY9KAG96GByIo=s16000" /></a></div><br /></li></ol><p></p>Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-17934543819951868872021-09-30T15:51:00.003+08:002022-06-17T17:20:45.155+08:00《OpenOffice / LibreOffice Calc》除了HLookup, VLookup,有些時候用match, address, indirect來組合運用會更方便<p style="background-color:#FFFFCC;font-weight:bold;">《<a href="https://blog.bod.idv.tw/">OSS首頁</a>》《<a href="https://blog.bod.idv.tw/2011/02/hannibalopenoffice.html" >文章分類列表</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Base">Base</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Calc">Calc</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Draw">Draw</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Impress">Impress</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Writer">Writer</a>》</p>
<p>如下附圖,要把A, B, C三欄整理成E1:E25, J1:J25, L1:L25,去除節氣時間、節氣是空白的,通常會想到排序整理一下,但有些時候就是要保留原資料,不能讓原排序亂掉!<br /></p>
<p></p><div class="separator" style="clear: both;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifVVNQEEUQhkGnju1kQ2GG0JB0_RhI5TxtJ_ICsy2FlQ88PY2xEqf4dUBSo1bxs0Fryt8GMIg6BUG3ap96OkZkBGc4nPaAhpdRu1Fj2EsqHXSl7VUQZ3fq9iOEGZeFv11iVjn4Z43H_Rk/s1018/20210930_match-address-indirect.PNG" style="display: block; padding: 1em 0px; text-align: center;"><img alt="" border="0" data-original-height="988" data-original-width="1018" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifVVNQEEUQhkGnju1kQ2GG0JB0_RhI5TxtJ_ICsy2FlQ88PY2xEqf4dUBSo1bxs0Fryt8GMIg6BUG3ap96OkZkBGc4nPaAhpdRu1Fj2EsqHXSl7VUQZ3fq9iOEGZeFv11iVjn4Z43H_Rk/s600/20210930_match-address-indirect.PNG" width="600" /></a></div>
<div><br /><p>在已將所有節氣名稱資料都備妥的情況下,只是要取得節氣對應的日期即發生的時間點,該如何下公式呢?</p><p>通常會想到要用VLOOKUP,但是來看一下vlookup的公式參數:<br /></p><p>vlookup 公式語法<br />=VLOOKUP(SearchCriterion; Array; Index; SortOrder)<br />SearchCriterion: 是指要在參照陣列範圍的第一欄中搜尋的條件值。<br />Array:是參照範圍,至少包含兩欄,被搜尋條件的範圍,必須放在參照範圍的第一欄。<br />Index:回傳值再參照範圍所在的欄。第一欄的編號為 1,以此類推。<br />SortOrder:<br /> 是選用的參數,指出陣列中的第一欄是否向上排序(遞增排序)。<br /> 若第一欄<b>未向上排序(遞增排序)</b>,請輸入布林值<b> FALSE 或0</b>。<br /> 搜尋排序過後的欄可更快速,且即使搜尋值未完全符合,只要該值介於排序清單的最低值與最高值之間,函數仍會一律傳回值。<br /> 在未排序的清單中,搜尋值必須完全符合。否則函數會傳回訊息:錯誤:數值不存在(#NA)</p><p>這裡搜尋的條件值為「節氣」這個欄位不適合放在第一欄當作被搜尋的參照範圍,所以這裡試著用match, address, indirect來完成預期的效果:</p><p></p><ol style="text-align: left;"><li>用match來找到指定節氣名稱在原始資料中的所在位置(第?列) <br />MATCH(SearchCriterion; LookupArray; Type)<br />Type:1:遞增, -1:遞減, 0:未排序<br /><br />=MATCH(E2;colJQ;0)<br />colJQ:原始資料的節氣資料所在範圍名稱,這對應的是C2:C366<br />原始資料的節氣資料夾雜空白和中文字,所以是未經排序的資料,Type用0<br /><br /></li><li>用address列出所找到的<br />ADDRESS(Row; Column; Abs; A1; "Sheet")<br />Row:表示儲存格參照的列號<br />Column:表示儲存格參照的欄號 (數字,而非字母)<br />Abs 決定參照的類型:<br /> 1: absolute ($A$1)<br /> 2:列絕對;欄相對 (A$1)<br /> 3:列相對;欄絕對 ($A1)<br /> 4:相對 (A1)<br />A1 (選擇性) - 若設為 0,則使用 R1C1 表示法。若此參數不存在或設為非 0 值,則使用 A1 表示法。<br />Sheet 表示工作表的名稱。必須在前後加上雙引號。<br /><br />=ADDRESS(F2+1;1)</li><div>只用所在列數、欄數,結果顯示為:$A$6</div><br /><li>用indirect顯示指定儲存格內容的值<br />INDIRECT(Ref; A1)<br /><b>Ref</b> 表示將傳回內容的儲存格或區域 (使用文字格式)
之參照。<br /><b>A1</b> (選擇性) - 若設為 0,則使用 R1C1
表示法。若此參數不存在或設為非 0 值,則使用 A1
表示法。<br /><br />=INDIRECT(H2)<br />顯示指定位址的內容 (指定節氣小寒的對應日期)<br /><br /></li><li>指定節氣小寒的對應時間,就直接將address, indirect合併使用顯示內容<br />=INDIRECT(ADDRESS(F2+1;2))<br /><br /></li><li>直接將match, address, indirect合併使用顯示內容<br />=INDIRECT(ADDRESS(MATCH(E29;colJQ;0)+1;1))<br />=INDIRECT(ADDRESS(MATCH(E29;colJQ;0)+1;2))<br /><br /><br /></li></ol><p></p>
</div>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-55928025088707298152021-01-21T14:52:00.002+08:002022-06-17T17:03:17.258+08:00透過SQLite ODBC Driver讓OpenOffice / LibreOffice 存取 SQLite的資料<p style="background-color:#FFFFCC;font-weight:bold;">《<a href="https://blog.bod.idv.tw/">OSS首頁</a>》《<a href="https://blog.bod.idv.tw/2011/02/hannibalopenoffice.html" >文章分類列表</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Base">Base</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Calc">Calc</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Draw">Draw</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Impress">Impress</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Writer">Writer</a>》</p>
<ul style="text-align: left;">
<li>先確認已經有OpenOffice / LibreOffice 的工作環境</li>
<ul>
<li><a href="http://www.openoffice.org/download/index.html" target="_blank">http://www.openoffice.org/download/index.html</a> (目前最新版本4.1.8)</li>
<li><a href="https://www.libreoffice.org/download/download/" target="_blank">https://www.libreoffice.org/download/download/</a> (目前最新版本6.4.7 / 7.0.4)</li>
<li>以下網址有portable版本</li>
<ul>
<li><a href="https://portableapps.com/apps/office/openoffice_portable" target="_blank">https://portableapps.com/apps/office/openoffice_portable</a> (目前最新版本4.1.7)</li>
<li><a href="https://portableapps.com/apps/office/libreoffice_portable" target="_blank">https://portableapps.com/apps/office/libreoffice_portable</a> (目前最新版本7.0.4)</li>
</ul>
</ul>
<li>是否已經有管理SQLite資料檔的工具?</li>
<ul>
<li>不是必備,你可能只是接收其他人提供的SQLite資料檔</li>
<li>如果你自行建立SQLite資料檔的需求,可以考慮:</li>
<ul>
<li><a href="https://sqlitestudio.pl/index.rvt?act=download" target="_blank">https://sqlitestudio.pl/index.rvt?act=download</a> (目前最新版本3.2.1)</li>
<li><a href="https://sqlitebrowser.org/dl/" target="_blank">https://sqlitebrowser.org/dl/</a> (目前最新版本3.12.1)</li>
</ul>
</ul>
<li>下載取得SQLite ODBC Driver</li>
<ul>
<li><a href="http://ch-werner.de/sqliteodbc/" target="_blank">http://ch-werner.de/sqliteodbc/</a> </li>
<li>driver提供的網頁Christian's Homepage這樣說:<br /><br />The SQLite Database Engine provides a lightweight C library to access database files using a large subset of SQL92 without the overhead of RDBMS server processes. In order to use that functionality as a desktop database I wrapped the SQLite library into an ODBC driver.<br /><br />So far it has been tested with SQLite 2.8.17 and SQLite 3.32.3 on Windows (>= NT/2000) in MS Excel 97 (MSQUERY) with SQLite database files created on Linux. Support for Win64 exists for both SQLite 2.x and SQLite 3.x versions of the driver. It also runs on Linux with unixODBC 2.[0-2].x and with libiodbc 3.[05].x.<br /><br />For Win32 operating systems a binary package is available as an NSIS installer in sqliteodbc.exe. It was made with SQLite 2.8.17/3.32.3 and a MinGW cross compiler, and contains the driver DLLs and programs for installation and uninstallation of the ODBC driver.<br /><br />For Win64 operating systems a binary package is available as an NSIS installer in sqliteodbc_w64.exe. It was made with SQLite 3.32.3 and a MinGW cross compiler, and contains the driver DLLs and programs for installation and uninstallation of the ODBC driver. Note that this is a 64 bit only driver. If you're using 32 bit software on Win64 you should install the Win32 driver, too. <br /><br /></li>
</ul>
<li>取得 sqliteodbc.exe (32位元) /或/ sqliteodbc_w64.exe (64位元),並安裝:</li>
<ul>
<li>OpenOffice / LibreOffice 的安裝程式沒有區分 32/64位元(那就是32位元),如果沒有OpenOffice / LibreOffice 以外的ODBC連接需求,可以只安裝sqliteodbc 32位元版本,如果有32/64位元的需求,就兩個版本都安裝。。<br /><br /></li><li>sqlodbc.exe / sqliteodbc_w64_dl.exe 的安裝幾乎一樣,以下用64位元版本說明。<br />(EXPERIMENTAL: variant which needs System.Data.SQLite.dll or sqlite3.dll in the Windows system folder)</li>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-7fgL5fAspA-lmuYSaM519uJtZWcumdWG8EDrlqm_OBIp9voB52X_EN4_4wg1VUdyhZOgbLyFgqxuo9SJEECtgNwULUNAEMQ3LbStwtQoK5ZdHKyjMIfqifeOlcuv7rZivL-p0oZqu04/s1600/SQLiteODBC_step1.JPG"><img border="0" data-original-height="392" data-original-width="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-7fgL5fAspA-lmuYSaM519uJtZWcumdWG8EDrlqm_OBIp9voB52X_EN4_4wg1VUdyhZOgbLyFgqxuo9SJEECtgNwULUNAEMQ3LbStwtQoK5ZdHKyjMIfqifeOlcuv7rZivL-p0oZqu04/s1600/SQLiteODBC_step1.JPG" /></a>
<li><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVu4xOfRN59i8ukxiCLoPX87TY_QACisQWNxD_ph5IMvhQ32YKNtS7hyxVs2Bb2bz7P8prDIOk4MBhMIlGaaYoeJNAhNN35oz33Ynj6l25p-mDEE58-5n6BiT1qJ6AsKVdeA2Bjd7ztfs/s1600/SQLiteODBC_02_Agree.JPG"><img border="0" data-original-height="392" data-original-width="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVu4xOfRN59i8ukxiCLoPX87TY_QACisQWNxD_ph5IMvhQ32YKNtS7hyxVs2Bb2bz7P8prDIOk4MBhMIlGaaYoeJNAhNN35oz33Ynj6l25p-mDEE58-5n6BiT1qJ6AsKVdeA2Bjd7ztfs/s1600/SQLiteODBC_02_Agree.JPG" /></a>
</li>
<li><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJGYSx7t3RjedQAlA-OtvGriQO6k_YGygSiWcmqHLbyzLEiLwDLyZhCaCzZNNE5f_X_ey9qltCpLTu9YUmgBRBFOWqDUARx-K_xnVQG3Lf_KuQ5O-iVN5Fz1ewhJpWZi5HoZIPfNLWgBo/s1600/SQLiteODBC_03_ChooseInstallLocation.JPG"><img border="0" data-original-height="392" data-original-width="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJGYSx7t3RjedQAlA-OtvGriQO6k_YGygSiWcmqHLbyzLEiLwDLyZhCaCzZNNE5f_X_ey9qltCpLTu9YUmgBRBFOWqDUARx-K_xnVQG3Lf_KuQ5O-iVN5Fz1ewhJpWZi5HoZIPfNLWgBo/s1600/SQLiteODBC_03_ChooseInstallLocation.JPG" /></a>
</li>
<li><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJEHwsHCgHLgA84HWhdqVgweMVeGTOYk1Q2Wr-Hz7NgJNaso7KsAsd2jQPl9_lloDmD228E99Sx9hXLg6qhMOlsfbMuV4bKqRVe6RMXfW6fabZI7bBQlneQ8Y5zNSt9WIA3iSBJr9_iG8/s1600/SQLiteODBC_04_ChooseComponents.JPG"><img border="0" data-original-height="392" data-original-width="503" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJEHwsHCgHLgA84HWhdqVgweMVeGTOYk1Q2Wr-Hz7NgJNaso7KsAsd2jQPl9_lloDmD228E99Sx9hXLg6qhMOlsfbMuV4bKqRVe6RMXfW6fabZI7bBQlneQ8Y5zNSt9WIA3iSBJr9_iG8/s1600/SQLiteODBC_04_ChooseComponents.JPG" /></a>
</li>
<li><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd4vv2Wu5BrygfGfI412cfeED4YQ80hh0seVrW3XQSqVGzmIMLLrxM5C7e4TbDjmBKHNugbVwVVVY24681TfZz1QQF2nhzY3H0VA0c-eGO-pFVKaj7qDe2m8XeZWz0pYN9VaXAXAiFbkE/s1600/SQLiteODBC_05_Finished.JPG"><img border="0" data-original-height="392" data-original-width="504" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd4vv2Wu5BrygfGfI412cfeED4YQ80hh0seVrW3XQSqVGzmIMLLrxM5C7e4TbDjmBKHNugbVwVVVY24681TfZz1QQF2nhzY3H0VA0c-eGO-pFVKaj7qDe2m8XeZWz0pYN9VaXAXAiFbkE/s1600/SQLiteODBC_05_Finished.JPG" /></a></li></ul><li>使用Win 10的搜尋功能,搜尋ODBC(或:更精準搜尋ODBC資料來源),準備建立SQLite資料檔的ODBC連線設定,透過DSN(Data Source Name)ODBC連線設定,提供給相關程式存取時使用,如:OpenOffice / LibreOffice ...</li>
<ul><br /><li><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK5gXKZk6_VUdj4NOlPvDt9ddb_mVFerxsGNIb47FRp9WvNE20oxIPvT0da6VgadsGqBseDckxM8vjL0Iv5zVW6w4eUV27pbIZ-jxsoSWB2NKNgu7ylMliPOlnl0qOuH32pytKxXBAh48/s661/Run_SQLiteODBC32.png" style="clear: left; float: left; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="595" data-original-width="661" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK5gXKZk6_VUdj4NOlPvDt9ddb_mVFerxsGNIb47FRp9WvNE20oxIPvT0da6VgadsGqBseDckxM8vjL0Iv5zVW6w4eUV27pbIZ-jxsoSWB2NKNgu7ylMliPOlnl0qOuH32pytKxXBAh48/s16000/Run_SQLiteODBC32.png" /></a></div><br /></li>
<li>ODBC資料來源管理員(32位元)<br />分頁:使用者資料來源名稱→新增(D)→建立新資料來源→選:SQLite3 ODBC Driver <br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjB_Jyd4Z5MW9HuLxLn-X7CIP3mU6yGL1WwVjtLljdGpni1LhxdpbVRXMsb5wsNIqyTVbq_XCMN2y7XmdRrMkoGAJPFDgf4m14QxGrolFeOGJ6a-uDmHBUZDnv24c7zKOkMnbV7wWkdYjs/s692/AddUserDataSourceName.png" style="clear: left; float: left; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="504" data-original-width="692" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjB_Jyd4Z5MW9HuLxLn-X7CIP3mU6yGL1WwVjtLljdGpni1LhxdpbVRXMsb5wsNIqyTVbq_XCMN2y7XmdRrMkoGAJPFDgf4m14QxGrolFeOGJ6a-uDmHBUZDnv24c7zKOkMnbV7wWkdYjs/s16000/AddUserDataSourceName.png" /></a></div><br /></li>
<li>設定DSN:<br />假設要連結的SQLite資料庫檔案在桌面的chinook/chinook.db<br />Data Source Name : 可依據需求設定,這裡設為chinook<br />Database Name : 用Browse選取chinookdb檔案。<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEic7ztoVSmzwrLozhN2IngePm1OFTgH6gOY7VAhFw9IfwWsVO7Kp58q-Neqbl4vAwlf97846L3iz5_woi9eWs1IE_YFX5edKrxJeok_ztRtLNW__2SxhRwF3CSSNGBVKV5PHtKEnnVpDkQ/s419/ConfigureDSN.JPG" style="clear: left; margin-bottom: 1em; margin-left: 1em; text-align: center;"><img border="0" data-original-height="343" data-original-width="419" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEic7ztoVSmzwrLozhN2IngePm1OFTgH6gOY7VAhFw9IfwWsVO7Kp58q-Neqbl4vAwlf97846L3iz5_woi9eWs1IE_YFX5edKrxJeok_ztRtLNW__2SxhRwF3CSSNGBVKV5PHtKEnnVpDkQ/s16000/ConfigureDSN.JPG" /></a></li><li>完成設定<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3P_A3NTfJy2t2fHVd_n8RIkXoGNQYzYNIGyo3TvKk38O28CbaJHEzHEnbKLgMczvr0Z6tH7R58HFzXrjw2xOVcY8QKn9dJNuQNhvi3ad6j9hqNi-QW58QyFvsvMZdw6TcntribjmDOEs/s350/ConfigureDSNok.JPG" style="clear: left; margin-bottom: 1em; margin-left: 1em; text-align: center;"><img border="0" data-original-height="165" data-original-width="350" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3P_A3NTfJy2t2fHVd_n8RIkXoGNQYzYNIGyo3TvKk38O28CbaJHEzHEnbKLgMczvr0Z6tH7R58HFzXrjw2xOVcY8QKn9dJNuQNhvi3ad6j9hqNi-QW58QyFvsvMZdw6TcntribjmDOEs/s16000/ConfigureDSNok.JPG" /></a><br /><br /></li></ul><li>使用Base,建立資料庫連線</li><ul><li>連線到現有資料庫:選取ODBC<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXvAUmKbXIp9LWi102V4SpCuDYHQm7uepaacRl331nwMMEXe_MaW4HAior33H20W3u-Ps_TFWhpzCE90WNzjItLMJzlBjFkKdo0ziQ5C8oVXXgyUCovjqO7EAic43o6s8g33cAuNSjbWw/s709/Base_SelectDB.JPG" style="clear: left; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="464" data-original-width="709" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXvAUmKbXIp9LWi102V4SpCuDYHQm7uepaacRl331nwMMEXe_MaW4HAior33H20W3u-Ps_TFWhpzCE90WNzjItLMJzlBjFkKdo0ziQ5C8oVXXgyUCovjqO7EAic43o6s8g33cAuNSjbWw/s16000/Base_SelectDB.JPG" /></a></li><li>設定ODBC連線:選取設定OK的DSN。例:Chinook<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfBXmxeA2k_B9skyTzPqbpfBwc_rTm2_22I8ZDIopS791x7L-7biv7FiJkfS1FGsU4gPHxmxz2BEPOCNgJ5pYEsTXsLGx3wQhJeeFphC5NnOIGR15WcCzxB-I5iMlWfpypYQsbIOP13Vg/s709/Base_SettingODBC.JPG" style="clear: left; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="462" data-original-width="709" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfBXmxeA2k_B9skyTzPqbpfBwc_rTm2_22I8ZDIopS791x7L-7biv7FiJkfS1FGsU4gPHxmxz2BEPOCNgJ5pYEsTXsLGx3wQhJeeFphC5NnOIGR15WcCzxB-I5iMlWfpypYQsbIOP13Vg/s16000/Base_SettingODBC.JPG" /><br /></a><br /></li><li>設定使用者認證:不需輸入<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf9-KsAYYm_avQpuYFKI9mMH8_0ti8YSml_bt6863x4Kjs7guRDU81EiQYZAm_4OiMVwwwz1pHkcN782PBnAwDF9BHUsFnD6E1HFrZQ9gD5t8l4jPu-aWRqJzDSj_MEORjEcYBrvD4LqU/s707/Base_AuthUser.JPG" style="clear: left; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="461" data-original-width="707" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf9-KsAYYm_avQpuYFKI9mMH8_0ti8YSml_bt6863x4Kjs7guRDU81EiQYZAm_4OiMVwwwz1pHkcN782PBnAwDF9BHUsFnD6E1HFrZQ9gD5t8l4jPu-aWRqJzDSj_MEORjEcYBrvD4LqU/s16000/Base_AuthUser.JPG" /></a><br /><div class="separator" style="clear: both; text-align: center;"><br /></div></li><li>完成設定:<br />註冊資料庫:註冊後,在Calc, Writer, ... 等,也可以使用。<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHUusd7L4kUlf8e2S93nkesxrOpdI0woBUUc5jCEcv2f7OgfoX0nyfM_LH3miG01f659V9j6X-3Nq64T_QlDVAEAiRhN6yKa0mrcgp8lYevoih__Y9tarQ6_XCNN8TAyep6IiwSid9WSU/s707/Base_odbcFinished.JPG" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="463" data-original-width="707" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHUusd7L4kUlf8e2S93nkesxrOpdI0woBUUc5jCEcv2f7OgfoX0nyfM_LH3miG01f659V9j6X-3Nq64T_QlDVAEAiRhN6yKa0mrcgp8lYevoih__Y9tarQ6_XCNN8TAyep6IiwSid9WSU/s16000/Base_odbcFinished.JPG" /></a><br /><div class="separator" style="clear: both; text-align: center;"><br /></div><br /></li><li><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3hPByh5oBAUJ-kxzAFQc1vRHnUKX6gLTU3y0xoW_3oeD2xgk2aXkHulkBxCtWz4OMzXUg1tzwwo7ulKcmkMW8OPSetv3fd7vc2zFCkOnowGVFibfeBQJ_pgLM3UnM1dmZJx3J5WOSlIY/s584/Base_dev.JPG" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="584" data-original-width="579" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3hPByh5oBAUJ-kxzAFQc1vRHnUKX6gLTU3y0xoW_3oeD2xgk2aXkHulkBxCtWz4OMzXUg1tzwwo7ulKcmkMW8OPSetv3fd7vc2zFCkOnowGVFibfeBQJ_pgLM3UnM1dmZJx3J5WOSlIY/s16000/Base_dev.JPG" /></a><br /><br /><div class="separator" style="clear: both; text-align: center;"><br /></div></li><li>在Calc查看、使用SQLite檔案的資料<br />檢視(V)→資料來源(D) .... (F4)<br /><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCGgznT4CqlRE7LJpVBrHFh4QPX4XFKIxHOwYZaxpq1bPC5Epm9t9S3HYMYTrhe3LzBHJPuNxPyn6cJj1SCmvjaD1pnubcZtSoJJegc1AWHoU9kUSIdroYYbZGpEIyZtXYJhFTVYmnubg/s595/Calc_DataSource.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="459" data-original-width="595" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCGgznT4CqlRE7LJpVBrHFh4QPX4XFKIxHOwYZaxpq1bPC5Epm9t9S3HYMYTrhe3LzBHJPuNxPyn6cJj1SCmvjaD1pnubcZtSoJJegc1AWHoU9kUSIdroYYbZGpEIyZtXYJhFTVYmnubg/s16000/Calc_DataSource.JPG" /></a></div><br /></li>
</ul>
<ul>
</ul>
</ul>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-42928228108092740102019-09-23T14:34:00.000+08:002019-09-23T14:34:54.655+08:00SQL : INSERT INTOSQL : INSERT INTO 將資料插到資料表內<br />
<br />
<b>目標:</b><br />
<ol>
<li>使用CREATE TABLE ... AS SELECT ...,複製資料表MediaType的結構及MediaTypeId=1的這筆資料,到資料表MediaType2。</li>
<li>使用 INSERT INTO table (field1, field2, ...) VALUES (value1, value2, ...); 插入1筆資料。</li>
<li>使用 INSERT INTO table VALUES (value1, value2, ...); 插入2筆資料。</li>
<li>使用 INSERT INTO table (field1, field2, ...) SELECT fielda, fieldb, ... FROM table_a WHERE ...; 插入1筆資料。</li>
</ol>
<b>作法:</b><br />
<ol>
<li>測試環境的資料庫,可以參閱以下網址連結來建立:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a></li>
</ul>
<li>選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor</li>
<li>在Query分頁中輸入所要建立測試資料表MediaType2及插入資料的執行指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE MediaType2 AS
SELECT MediaTypeId, Name
FROM MediaType
WHERE MediaTypeId=1;
INSERT INTO MediaType2 (MediaTypeId,Name)
VALUES (2,'Protected AAC audio file');
INSERT INTO MediaType2
VALUES (3,'Protected MPEG-4 video file');
INSERT INTO MediaType2
SELECT MediaTypeId,Name FROM MediaType
WHERE MediaTypeId=4 OR MediaTypeId=5;
</code></pre>
<br />
</li>
<li>執行SQL指令:(F9) Execute SQL (選取要執行的部分SQL指令,按F9,會只執行選取的部分)<br />先執行 CREATE TABLE MediaType2,再執行一次INSERT INTO MediaType2 ...</li>
<li>Status : 確認SQL指令 執行無誤<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgx7sbSTDYy61QU0wBE_IL6TltYHqgzwztckrRLAdtElJ353jHrIttiUP0Th5oJoRNr6g4O5RV7l4mP-qPyg7OBLzGxpl1WH43CQu9yOn4lfPuwQCJWr21jz_SuvA21A3yDRymQ5KmJIg8/s1600/SQL_InsertInto.PNG" imageanchor="1"><img border="0" data-original-height="493" data-original-width="519" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgx7sbSTDYy61QU0wBE_IL6TltYHqgzwztckrRLAdtElJ353jHrIttiUP0Th5oJoRNr6g4O5RV7l4mP-qPyg7OBLzGxpl1WH43CQu9yOn4lfPuwQCJWr21jz_SuvA21A3yDRymQ5KmJIg8/s1600/SQL_InsertInto.PNG" /></a>
<br /><br />
</li>
<li>查看執行結果:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuODgH03WYLNjliLzzBp65fQsWLIBOO9WEcIQ-wzkKjtV6_WxdSgJiQB0WRRctAh2d6dx9h_eI6yDsF_fc_1XTYZ0owmYhXkmdnNmkKZyi9dDNBw2y3Z6XsZ8TzDq5ykc__u65aJDZ-8c/s1600/SQL_InsertInto_OK.PNG" imageanchor="1"><img border="0" data-original-height="493" data-original-width="520" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuODgH03WYLNjliLzzBp65fQsWLIBOO9WEcIQ-wzkKjtV6_WxdSgJiQB0WRRctAh2d6dx9h_eI6yDsF_fc_1XTYZ0owmYhXkmdnNmkKZyi9dDNBw2y3Z6XsZ8TzDq5ykc__u65aJDZ-8c/s1600/SQL_InsertInto_OK.PNG" /></a>
</li>
</ol>
<br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-29402027123013874482019-09-23T09:54:00.000+08:002019-09-23T10:02:21.637+08:00SQL : WITH RECURSIVE ... REPLACE INTO ...SQL : WITH RECURSIVE ... REPLACE INTO ...<br />
INSERT INTO 不會做資料插入前的檢查,發生錯誤就中斷程式。<br />
在程式開發的過程,時常會遇到一個情境:<br />
如果資料不存在,就插入一筆資料;如果資料已經存在,就更新這一筆資料。<br />
INSERT INTO 衍申的語法:REPLACE INTO 剛好可以滿足這個需求,但資料表必須有主鍵PRIMARY KEY,資料會透過所提供的PRIMARY KEY鍵值,來決定要增加一筆資料?還是更新這筆資料?<br />
<br />
<b>目標:</b><br />
<ol>
<li>延續上一篇:SQL : WITH RECURSIVE ... INSERT INTO ... <a href="http://blog.bod.idv.tw/2019/09/sql-with-recursive-insert-into.html" target="_blank">http://blog.bod.idv.tw/2019/09/sql-with-recursive-insert-into.html</a> ,這完成INSERT INTO 相關的資料後,這這裡繼續做REPLACE INTO的動作。</li>
<li>在 tabWith 已 INSERT INTO : (1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa') 的情況下,要再繼續REPLACE INTO : (4,'b'), (5,'bb'), (6,'bbb'), (7,'bbbb'), (8,'bbbbb')</li>
</ol>
<div>
<b>作法:</b></div>
<ol>
<li>測試環境的資料庫,可以參閱以下網址連結來建立:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a> </li>
<li>確認 已完成 SQL : WITH RECURSIVE ... INSERT INTO ... <a href="http://blog.bod.idv.tw/2019/09/sql-with-recursive-insert-into.html" target="_blank">http://blog.bod.idv.tw/2019/09/sql-with-recursive-insert-into.html</a> </li>
</ul>
<li>選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor</li>
<li>在Query分頁中輸入SQL的執行指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>WITH RECURSIVE cte AS (
SELECT 4 AS c1, 'b' AS c2
UNION ALL
SELECT c1 + 1, c2 || 'b'
FROM cte
WHERE c1 < 8
)
REPLACE INTO tabWith (score, grade) SELECT c1, c2 FROM cte;</code></pre>
</li>
<li>執行SQL指令:(F9) Execute SQL,Status : 確認SQL指令 WITH RECURSIVE ... REPLACE INTO ... 執行無誤<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_k0sHaaMyFGKaGEx1LxiJW8RXLtpGbq8TfA89bR0mGg1UAFS8opc6TU9IZJP_ZqScKwFQgC9CjBDTTXmevHE0OyU6bSsA1FiRncDLFpbowVAllQkJk4MEwB3wB8HniFcCuaoNwlkTpYA/s1600/tabWith_WithRecursiveReplaceInto.PNG" imageanchor="1"><img border="0" data-original-height="473" data-original-width="452" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_k0sHaaMyFGKaGEx1LxiJW8RXLtpGbq8TfA89bR0mGg1UAFS8opc6TU9IZJP_ZqScKwFQgC9CjBDTTXmevHE0OyU6bSsA1FiRncDLFpbowVAllQkJk4MEwB3wB8HniFcCuaoNwlkTpYA/s1600/tabWith_WithRecursiveReplaceInto.PNG" /></a><br /></li>
<li>查看執行結果<br />score=4, (4,'aaaa') 已更新為 (4, 'b')<br />score=5, (5,'aaaaa') 已更新為 (5, 'bb')<br />已插入 (6,'bbb'), (7,'bbbb'), (8,'bbbbb')<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSs60wEvlIHx_kqvNImzs-ejk6rAKfrLKuSMYYdMszWgd9PjsGVxjLy7tdcft7GrhmM0Cp0bQu7VIF49xEfjiAQ5dpoA_8MgHe5yharr2XHHyjXndjfnT9puSX4vrjXzrHaNQWEaDbAHI/s1600/tabWith_WithRecursiveReplaceInto_ok.PNG" imageanchor="1"><img border="0" data-original-height="473" data-original-width="451" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSs60wEvlIHx_kqvNImzs-ejk6rAKfrLKuSMYYdMszWgd9PjsGVxjLy7tdcft7GrhmM0Cp0bQu7VIF49xEfjiAQ5dpoA_8MgHe5yharr2XHHyjXndjfnT9puSX4vrjXzrHaNQWEaDbAHI/s1600/tabWith_WithRecursiveReplaceInto_ok.PNG" /></a></li>
</ol>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-51221901443813991962019-09-23T08:05:00.001+08:002019-09-23T09:00:41.792+08:00SQL : WITH RECURSIVE ... INSERT INTO ...SQL : WITH RECURSIVE ... INSERT INTO ...<br />
<br />
<b>目標:</b><br />
<ol>
<li>先建立一個空資料表格tabWith,有兩個資料欄位 score INT PRIMARY KEY, grade NVARCHAR(120)</li>
<li>用WITH RECURSIVE的方式,插入五筆資料:(1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa')</li>
</ol>
<div>
<b>作法:</b></div>
<ol>
<li>測試環境的資料庫,可以參閱以下網址連結來建立:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a><br /> </li>
</ul>
<li>選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor</li>
<li>在Query分頁中輸入所要建立測試資料表tabWith的執行指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE tabWith (
score INTEGER NOT NULL,
grade NVARCHAR(120),
PRIMARY KEY (score)
);
</code></pre>
<br />
</li>
<li>執行SQL指令:(F9) Execute SQL</li>
<li>Status : 確認SQL指令 CREATE TABLE tabWith 執行無誤<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjESni_Fg_kPO-HL1V3mX0NRwNoCoteKjLEqYsRb-flVqp0jmFdCMjZ6qhp4DDWC-sBwLrCrma-t_1wvZjP90pqy0RnCH3hFo_3H56pmxo9q1sI38SrhGA8TVkuPB55Ejb3BglKo0z8t78/s1600/tabWith_Create.PNG" imageanchor="1"><img border="0" data-original-height="473" data-original-width="453" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjESni_Fg_kPO-HL1V3mX0NRwNoCoteKjLEqYsRb-flVqp0jmFdCMjZ6qhp4DDWC-sBwLrCrma-t_1wvZjP90pqy0RnCH3hFo_3H56pmxo9q1sI38SrhGA8TVkuPB55Ejb3BglKo0z8t78/s1600/tabWith_Create.PNG" /></a>
<br /><br />
</li>
<li>用WITH RECURSIVE的方式,插入五筆資料:(1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa')<br />在Query分頁中輸入WITH RECURSIVE INSERT INTO的SQL指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>WITH RECURSIVE cte AS (
SELECT 1 AS c1, 'a' AS c2
UNION ALL
SELECT c1 + 1, c2 || 'a'
FROM cte
WHERE c1 < 5
)
INSERT INTO tabWith (score, grade) SELECT c1, c2 FROM cte;
</code></pre>
</li>
<li>執行SQL指令:(F9) Execute SQL,Status : 確認SQL指令WITH RECURSIVE INSERT INTO 執行無誤<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjHUq_PDz6y-e3DP-9IVQ7-nRY9DwilGMVPegwLpgyjB-FRcB72mAkqqDQReZnVLWPXzEnFvgqIUT17N5AE1on4CmEt9L6qZ2iZl3QG-YMbt5-bTlku_FvpjOPXLH0FEMy6jbh5q_yFag/s1600/tabWith_WithRecursiveInsertInto.PNG" imageanchor="1"><img border="0" data-original-height="472" data-original-width="452" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjHUq_PDz6y-e3DP-9IVQ7-nRY9DwilGMVPegwLpgyjB-FRcB72mAkqqDQReZnVLWPXzEnFvgqIUT17N5AE1on4CmEt9L6qZ2iZl3QG-YMbt5-bTlku_FvpjOPXLH0FEMy6jbh5q_yFag/s1600/tabWith_WithRecursiveInsertInto.PNG" /></a>
</li>
</ol>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-45963897737949190282019-09-22T16:55:00.001+08:002019-09-23T13:36:41.596+08:00SQL : INSERT INTO 語法(Syntax)格式SQL : INSERT INTO 的語法(Syntax),有三個基本的語法:<br />
<ul>
<li>將指定的資料值插入資料表格內:<br />
<div style="background-color: lightgrey;">
INSERT INTO 資料表名稱 [(資料欄位名稱, ...)] VALUES (資料欄位值, ...);</div>
<br />
</li>
<li>將SELECT執行結果插入資料表格內:<br />
<div style="background-color: lightgrey;">
INSERT INTO 資料表名稱 [(資料欄位名稱, ...)] SELECT執行結果;</div>
<br />
</li>
<li>以資料表結構定義時所設定的預設值插入資料表格內:<br />
<div style="background-color: lightgrey;">
INSERT INTO 資料表名稱 [(資料欄位名稱, ...)]DEFAULT VALUES;</div>
</li>
</ul>
INSERT INTO的 語法格式:<br />
<div style="background-color: lightgrey;">
[ WITH [ RECURSIVE ] cte-table-name AS (select-stmt), ...]<br />
{INSERT | REPLACE | INSERT OR REPLACE | insert-type ... } INTO <br />
資料表名稱 [AS 別名] [( 資料欄位名稱,...)] { [VALUES (資料值or運算式,) | select-stmt | DEFAULT VALUES}<br />
ON CONFLICT [(索引欄位, ... ) | [WHERE 條件式] ]<br />
DO { NOTHING | UPDATE SET 欄位名稱=運算式, ... [ WHERE 條件式 ]}</div>
<br />
<ol>
<li>cte : common table expression</li>
<li>select-stmt : select statements</li>
<li>insert-type : INSERT, REPLACE, INSERT OR REPLACE, INSERT OR ROLLBACK, INSERT OR ABORT, INSERT OR FAIL, INSERT OR IGNORE</li>
<li>如果主鍵的對應值存在,就REPLACE指定的資料值;如果主鍵的對應值不存在,就插入這筆資料。有效的利用這個語法規則,可以大大的降低程式設計的複雜度。</li>
</ol>
<br />
參考資料:<br />
<ul>
<li>SQL As Understood By SQLite : INSERT <a href="https://sqlite.org/lang_insert.html" target="_blank">https://sqlite.org/lang_insert.html</a> </li>
<li>PostgreSQL: Documentation: 11: INSERT <a href="https://www.postgresql.org/docs/11/sql-insert.html" target="_blank">https://www.postgresql.org/docs/11/sql-insert.html</a> </li>
</ul>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-47853060823154858142019-09-21T13:01:00.001+08:002021-01-26T13:54:52.431+08:00SQL : ALTER TABLESQL : ALTER TABLE<br />
ALTER TABLE 修改資料表的名稱、資料欄位名稱,或新增資料欄位<br />
<br />
ALTER TABLE 的 SQL語法(Syntax)格式:<br />
<div style="background-color: lightgrey;">
ALTER TABLE 資料表名稱<br />
{RENAME TO 新資料表名稱 | RENAME [COLUMN] 資料欄位名稱 TO 資料欄位新名稱 | ADD [COLUMN] 資料欄位定義};
</div>
<br />
<ol>
<li>ALTER TABLE 可以 修改 資料表名稱、資料欄位名稱,增加資料欄位等。</li>
<li>SQLite 3.25.0 起,將原先必須使用設定調整的作法(PRAGMA legacy_alter_table = ON 或 sqlite3_db_config() 的 SQLITE_DBCONFIG_LEGACY_ALTER_TABLE選項)才可以啟用ALTER TABLE的功能,改為可以直接使用的指令。<br />目前最新的SQLite版本 3.29.0。</li>
</ol>
<div>
以下將說明,修改Emp_Id_Name資料表結構:<br />
資料表名稱:Emp_Id_Name → EmpIdName<br />
資料欄位名稱:EmployeeId → EmpId<br />
增加資料欄位:IDCardNo CHAR(10)<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFJp9XnDh0X1ZrgpO4Pm4_C2AZwEPZm2FYLM3-onZ0Nr3t8-XYqT7kNv3u2pYmQ3_v0FOVdsBU0pY52h7EVzfMOVUbllU1Cwa2ql6rNePMRRUy_2uAlh6lJIPCZdeQW_wQ-bGS6Ad3jio/s1600/SQL_AlterTable_src.PNG" imageanchor="1"><img border="0" data-original-height="453" data-original-width="532" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFJp9XnDh0X1ZrgpO4Pm4_C2AZwEPZm2FYLM3-onZ0Nr3t8-XYqT7kNv3u2pYmQ3_v0FOVdsBU0pY52h7EVzfMOVUbllU1Cwa2ql6rNePMRRUy_2uAlh6lJIPCZdeQW_wQ-bGS6Ad3jio/s1600/SQL_AlterTable_src.PNG" /></a>
</div>
<br />
ALTER TABLE SQL指令的使用:<br />
<ol>
<li>測試環境的資料庫,可以參閱以下網址連結來建立:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a><br /> </li>
</ul>
<li>選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor</li>
<li>Emp_Id_Name資料表,可以透過以下指令製造:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE Emp_Id_Name AS
SELECT EmployeeId, LastName, FirstName FROM Employee;
</code></pre>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMUaiAFtAj9tCUKOCQiDiFt8MFO_zZn_99Hh3Dv72j5WGddyNixVy1yiulSs1ebL1u7NfG3Y5z2FG761QDXyoQdh1WwFvJJ16wxDB45aOUiTn5uiHeXbNiQsflEpHk1Db76o3zbsPa3jE/s1600/SQL_Drop_Table_Data.PNG" imageanchor="1"><img border="0" data-original-height="330" data-original-width="545" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMUaiAFtAj9tCUKOCQiDiFt8MFO_zZn_99Hh3Dv72j5WGddyNixVy1yiulSs1ebL1u7NfG3Y5z2FG761QDXyoQdh1WwFvJJ16wxDB45aOUiTn5uiHeXbNiQsflEpHk1Db76o3zbsPa3jE/s1600/SQL_Drop_Table_Data.PNG" /></a>
</li>
<li>先修改資料表名稱,在Query分頁中輸入所要執行的指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code> ALTER TABLE Emp_Id_Name RENAME TO EmpIdName</code>;</pre>
</li>
<li>執行SQL指令:(F9) Execute SQL,Status : 確認SQL指令執行無誤</li>
<li>因目前 SQLite Studio 的版本 v3.2.1,是基於SQLite 3.24.0開發的,如前所述SQLite 3.25.0後,在ALTER TABLE上以加強功能上的實作,所以接下來用sqlite3 3.29.0來完成資料欄位的修改新增。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit4_VTmclbmZh5iNfOvrO1vpX_gN650Cg_2e6u21WhYZy_3eKZzw2XB72H1HyMs1EdDAlJlSKuGuZzzXoYDrzVvJ-RBbVMFkYXittgW1NG3-E6hAoekrhRG1oyvKRgqBPWGatMvBTtbCw/s1600/SQL_AlterTable_sqlite3.PNG" imageanchor="1"><img border="0" data-original-height="147" data-original-width="549" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit4_VTmclbmZh5iNfOvrO1vpX_gN650Cg_2e6u21WhYZy_3eKZzw2XB72H1HyMs1EdDAlJlSKuGuZzzXoYDrzVvJ-RBbVMFkYXittgW1NG3-E6hAoekrhRG1oyvKRgqBPWGatMvBTtbCw/s1600/SQL_AlterTable_sqlite3.PNG" /></a>
</li>
<li>
回SQLiteStudio查看一下,確認完成修改<br /><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIB-niaj_gai_trL-nKm3drxU4wXYqeL3XRIeOlhtye5YifdU0DD7idjyIaja4Kq7bNUc7ridVRQzCk4kyEVhRayPfUWDXl_ONRujoWoeR_WwebViEuQE7hZ6qSuyoaHohutgFmDkjlWw/s1600/SQL_AlterTable_new.PNG" imageanchor="1"><img border="0" data-original-height="452" data-original-width="516" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIB-niaj_gai_trL-nKm3drxU4wXYqeL3XRIeOlhtye5YifdU0DD7idjyIaja4Kq7bNUc7ridVRQzCk4kyEVhRayPfUWDXl_ONRujoWoeR_WwebViEuQE7hZ6qSuyoaHohutgFmDkjlWw/s1600/SQL_AlterTable_new.PNG" /></a>
<br /></li>
<li>SQL Features That SQLite Does Not Implement (<a href="https://sqlite.org/omitted.html" target="_blank">https://sqlite.org/omitted.html</a>)<br />SQLite雖然已提供幾乎所的功能特性,但並沒有實作標準SQL的每一項功能特性,以ALTER TABLE的功能,僅提供:RENAME TABLE, ADD COLUMN, 及 RENAME COLUMN的支援,DROP COLUMN, ALTER COLUMN, 及 CONSTRAINT則不在功能支援的範圍內。</li>
</ol>
參考資料:<br />
SQL As Understood By SQLite : ALTER TABLE <a href="https://sqlite.org/lang_altertable.html" target="_blank">https://sqlite.org/lang_altertable.html</a>Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-12066845679291698012019-09-20T16:36:00.000+08:002019-09-20T16:56:56.719+08:00SQL : DROP TABLESQL : DROP TABLE<br />
DROP TABLE 除了會清除資料表內的資料,也會將這個資料表在資料庫中的結構定義資料一併清除,整個資料表都清光光。<br />
<br />
DROP TABLE的語法(Syntax)格式:<br />
<div style="background-color: lightgrey;">
DROP TABLE [IF EXISTS] 資料表名稱;</div>
<br />
<ol>
<li>可以增加 [IF EXISTS] 檢查,確認資料表存在,再予清除。</li>
<li>DELETE 跟 DROP 不一樣,DELETE只清除資料,不清除資料表的結構定義。</li>
</ol>
<br />
以下在測試資料庫下執行,預計刪除TestWind資料庫下的Emp_Id_Name資料表:<br />
<ol>
<li>測試環境的資料庫,可以參閱以下網址連結來建立:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a><br /> </li>
</ul>
<li>選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor</li>
<li>Emp_Id_Name資料表,可以透過以下指令製造:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE Emp_Id_Name AS
SELECT EmployeeId, LastName, FirstName FROM Employee;
</code></pre>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMUaiAFtAj9tCUKOCQiDiFt8MFO_zZn_99Hh3Dv72j5WGddyNixVy1yiulSs1ebL1u7NfG3Y5z2FG761QDXyoQdh1WwFvJJ16wxDB45aOUiTn5uiHeXbNiQsflEpHk1Db76o3zbsPa3jE/s1600/SQL_Drop_Table_Data.PNG" imageanchor="1"><img border="0" data-original-height="330" data-original-width="545" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMUaiAFtAj9tCUKOCQiDiFt8MFO_zZn_99Hh3Dv72j5WGddyNixVy1yiulSs1ebL1u7NfG3Y5z2FG761QDXyoQdh1WwFvJJ16wxDB45aOUiTn5uiHeXbNiQsflEpHk1Db76o3zbsPa3jE/s1600/SQL_Drop_Table_Data.PNG" /></a>
</li>
<li>在Query分頁中輸入所要執行的指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>DROP TABLE Emp_Id_Name</code>;</pre>
<br />
</li>
<li>執行SQL指令:(F9) Execute SQL</li>
<li>Status : 確認SQL指令執行無誤<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMSZZ0-rrA-c-Z5dXgaFk1Da-Eo8iymcl3h1-4-5dy1q4wFUBg3FF1Gsk96RPuK7WHJWq0h_5jg1WfGgbkIozSC4DpIfL7RPKfS1wSXIf_q7xYS7At-R6SMBaBEVMZC7se1GnaHvd59Ro/s1600/SQL_Drop_Table_Ok.PNG" imageanchor="1"><img border="0" data-original-height="477" data-original-width="545" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMSZZ0-rrA-c-Z5dXgaFk1Da-Eo8iymcl3h1-4-5dy1q4wFUBg3FF1Gsk96RPuK7WHJWq0h_5jg1WfGgbkIozSC4DpIfL7RPKfS1wSXIf_q7xYS7At-R6SMBaBEVMZC7se1GnaHvd59Ro/s1600/SQL_Drop_Table_Ok.PNG" /></a>
</li>
</ol>
<br />
參考資料:<br />
SQL As Understood By SQLite : DROP TABLE <a href="https://sqlite.org/lang_droptable.html" target="_blank">https://sqlite.org/lang_droptable.html</a><br />
<br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-6017928553424783632019-09-20T12:38:00.000+08:002019-09-20T12:38:15.349+08:00SQL : CREATE TABLE ... AS SELECT ...SQL : CREATE TABLE ... AS SELECT ... 使用SELECT的結果建立資料表,並將條件過濾後的查詢結果,匯入新建立的資料表中。<br />
<ol>
<li>測試環境的資料庫,可以參閱以下網址連結來建立:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a><br /> </li>
</ul>
<li>選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor</li>
<li>在Query分頁中輸入所要執行的指令<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE Album_20190920 as
SELECT AlbumId, Title FROM Album WHERE Title like 'A%';
</code></pre>
<br />
</li>
<li>執行SQL指令:(F9) Execute SQL</li>
<li>Status : 確認SQL指令執行無誤<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh60oTRrKPEoJpR_JM7qGItBz_P6vaH6ZOXVEgJAq4u-wXFVrhtOfXo0011tby4_Osnstybv1hCJ94boqV44xMySMpF6POhCPayLvcfCrynbJB12mmIC5GrsQS_O-l6OwByCOugRW4Ygrs/s1600/SQL_Create_Table_as_Select_ok.PNG" imageanchor="1"><img border="0" data-original-height="435" data-original-width="603" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh60oTRrKPEoJpR_JM7qGItBz_P6vaH6ZOXVEgJAq4u-wXFVrhtOfXo0011tby4_Osnstybv1hCJ94boqV44xMySMpF6POhCPayLvcfCrynbJB12mmIC5GrsQS_O-l6OwByCOugRW4Ygrs/s1600/SQL_Create_Table_as_Select_ok.PNG" /></a><br /><br />
</li>
<li>確認資料表Album_20190920已建立,<br />包含兩個資料欄位:AlbumId, Title,<br />只匯入A開頭的資料<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwPaLDH-Qu9llxrwmuIkynv7eUNgz8ZBAKmzetG2S1ME0oRRjYFeDX9I79UpmPloxdSHiCk8huS-sm0cz5q2nbunQDjcdRk0sY_hLOut9cU4MC4IUNQoFu1mL_Rf6e5Szod7OfcxDqm8c/s1600/SQL_Create_Table_as_Select_DataOk.PNG" imageanchor="1"><img border="0" data-original-height="860" data-original-width="546" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwPaLDH-Qu9llxrwmuIkynv7eUNgz8ZBAKmzetG2S1ME0oRRjYFeDX9I79UpmPloxdSHiCk8huS-sm0cz5q2nbunQDjcdRk0sY_hLOut9cU4MC4IUNQoFu1mL_Rf6e5Szod7OfcxDqm8c/s1600/SQL_Create_Table_as_Select_DataOk.PNG" /></a><br /><br />
</li>
<li>注意:查看DLL分頁<br />PRIMARY KEY 等限制條件(Constraints)、結構定義的內容,不會被匯入。<br />資料型態:INTEGER → 被轉換成 INT,NVARCHAR(160) →被轉換成 TEXT<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBXrRs5EEf4ny8YChDV8UpYSF6glGPKUeDgWjTW7tMkEB73cdaKaGomCfdLnr4KobLEAv3RnMa4rluFJQxMtBHgW8y8976LjDIjqcud2fosA509Tv_dYRgjbRhZmvfNQvfOn9DJm6l20c/s1600/SQL_Create_Table_as_Select_DDL.PNG" imageanchor="1"><img border="0" data-original-height="524" data-original-width="545" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBXrRs5EEf4ny8YChDV8UpYSF6glGPKUeDgWjTW7tMkEB73cdaKaGomCfdLnr4KobLEAv3RnMa4rluFJQxMtBHgW8y8976LjDIjqcud2fosA509Tv_dYRgjbRhZmvfNQvfOn9DJm6l20c/s1600/SQL_Create_Table_as_Select_DDL.PNG" /></a>
</li>
</ol>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-1546236037469772652019-09-20T09:51:00.001+08:002019-09-20T09:51:36.612+08:00用SQLiteStudio跨資料庫複製或搬移資料表內容SQLite Studio 資料表的複製、移動功能,可以讓SQLite 的資料庫間,快速地達到資料複製(copy)或搬移(move)的需求,尤其是在測試資料的過程,更能感受到這個功能的妙用。<br />
<br />
<ol>
<li>這個學習範例所需的資料庫環境,可以參閱:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a></li>
</ul>
<li>來源資料庫:Chinook,有11個資料表,每個資料表都有資料<br />目的資料庫:TestWind ,沒有資料表、沒有資料</li>
<li>選取Chinook 的 Artist 資料表,按住滑鼠左鍵,拖曳到 TestWind 的 Tables 位置,放開左鍵。<br />勾選:include data, include indexes, include triggers<br />點選:Copy選項<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiacZMFCc0U970VAR5gZ5gATecGkgVcKvO4EcD6q8elb4ncQP4B6XJ_Op_AKtUh3fYrS0Y_prlsRYzxnt3ADVecUQehEL01V7z7MU6dlKNixKErKnaGhmWbB9nP8JS_T6JAnHiG5SPiGhQ/s1600/SQLiteStudio_CopyMove.PNG" imageanchor="1"><img border="0" data-original-height="568" data-original-width="484" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiacZMFCc0U970VAR5gZ5gATecGkgVcKvO4EcD6q8elb4ncQP4B6XJ_Op_AKtUh3fYrS0Y_prlsRYzxnt3ADVecUQehEL01V7z7MU6dlKNixKErKnaGhmWbB9nP8JS_T6JAnHiG5SPiGhQ/s1600/SQLiteStudio_CopyMove.PNG" /></a>
<br /><br />
</li>
<li>Referenced tables的提醒:<br />SQLite Studio 提醒 Artist 這個資料表被 Albumn, PlaylistTrack, Track, InvoiceLine 等資料 Reference了,提示:要不要一併匯入這些資料表?<br />這裡選擇:No,只Copy Artist資料表<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWSxMHoBfLAPzfv4SX5SX7U18gwEIESv4PfegdBXy_RhxEVzFR3iKnL_gS_-W1xUV2s4R9bzNx_ktSJoE3L-OlBSoRChyphenhyphenEsrKNcMUyxnTEHSmkURFlkeB_WFM-MVZsm48HhIqy6b8yNTU/s1600/SQLiteStudio_CopyMove_Referenced.PNG" imageanchor="1"><img border="0" data-original-height="568" data-original-width="482" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWSxMHoBfLAPzfv4SX5SX7U18gwEIESv4PfegdBXy_RhxEVzFR3iKnL_gS_-W1xUV2s4R9bzNx_ktSJoE3L-OlBSoRChyphenhyphenEsrKNcMUyxnTEHSmkURFlkeB_WFM-MVZsm48HhIqy6b8yNTU/s1600/SQLiteStudio_CopyMove_Referenced.PNG" /></a>
</li>
<li>確認複製成功。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieIblecYvzix50HsdeBJsH1jPvs5Q5eDfuqXeXwWYBbrPrbJLT_JCbZOWUliZ_2e20rSHdBo0EF6GjBhWAt_MNjfNA6GQOOKIfGau9C04khLwrDkWoGeQB7xOKW6vYAk5bJ8cDaecLY4g/s1600/SQLiteStudio_CopyMove_CopiedOK.PNG" imageanchor="1"><img border="0" data-original-height="569" data-original-width="484" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieIblecYvzix50HsdeBJsH1jPvs5Q5eDfuqXeXwWYBbrPrbJLT_JCbZOWUliZ_2e20rSHdBo0EF6GjBhWAt_MNjfNA6GQOOKIfGau9C04khLwrDkWoGeQB7xOKW6vYAk5bJ8cDaecLY4g/s1600/SQLiteStudio_CopyMove_CopiedOK.PNG" /></a>
</li>
</ol>
<br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-28659480647653680442019-09-19T20:56:00.002+08:002019-09-20T10:02:17.308+08:00用SQLite Studio來CREATE TABLE自行輸入CREATE TABLE的指令,在對CREATE TABLE指令,還不是很清楚孰悉的情況下,是有一些難度的,SQLite Studio這時候,可以發揮極佳的輔助功能,輕鬆地幫忙使用者CREATE TABLE。<br />
完成資料表建立後,還可以檢視取得CREATE這個TABLE的SQL指令內容。<br />
<br />
這裡CREATE TABLE2的目標,是要完成一個像下圖內容的資料表:<br />
資料表名稱:Album,包含三個資料欄位:AlbumId, Title, ArtistId,... 詳細資料如下表 ...<br />
<img border="0" data-original-height="332" data-original-width="570" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgP08r7zKJci_zVJxJlgfILurUEj8hwWHDveRcjpQwi66caKCm7jZxfbAbyGNXQ_1ytc23dIFV1mhNuJbEx6nLCzEQNJuXRc0RhueNRwYhLL882AZgzvU-idfKwzErzmA5OI9UYqqxP434/s1600/TestWind_createAlbum.PNG" />
><br />
<ol>
<li>選取要執行這段指令的資料庫,可以參閱:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a></li>
</ul>
<li>Structure → Create a table<br />或 點選 工具列上的『Create a table』<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnVPLFlELBeBniN76Wnqk3K2pbDwpjgPcU02HUP0mEMggbCMJvwkW36PLYGi3ftwWH4iF9rjYJXiw4JikvUfzLJXaG8_VLePGnSKBsxpKa9FJuv56BXYfyqRA15JnYFMuA6uUxOT_dqUU/s1600/TestWind_CreateTable.PNG" imageanchor="1"><img border="0" data-original-height="203" data-original-width="553" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnVPLFlELBeBniN76Wnqk3K2pbDwpjgPcU02HUP0mEMggbCMJvwkW36PLYGi3ftwWH4iF9rjYJXiw4JikvUfzLJXaG8_VLePGnSKBsxpKa9FJuv56BXYfyqRA15JnYFMuA6uUxOT_dqUU/s1600/TestWind_CreateTable.PNG" /></a><br /><br />
</li>
<li>輸入表格名稱(Table name):Album<br />按下『Add Column(Ins)』鈕<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNK5noit5Kwg5WPXbVf8yjfCAaKEuEOUPrwECNpvfKBB7uv7_34TAYdY3OcPiSeVnoqwZwB5FDr9Xl6YjjhyphenhyphenTqV9gw__yaQeSYhDBMVyB7mx3FpARbIMto-Q0IH81VGcUoH-DBtGDuTQY/s1600/TestWind_addColumn.PNG" imageanchor="1"><img border="0" data-original-height="213" data-original-width="652" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNK5noit5Kwg5WPXbVf8yjfCAaKEuEOUPrwECNpvfKBB7uv7_34TAYdY3OcPiSeVnoqwZwB5FDr9Xl6YjjhyphenhyphenTqV9gw__yaQeSYhDBMVyB7mx3FpARbIMto-Q0IH81VGcUoH-DBtGDuTQY/s1600/TestWind_addColumn.PNG" /></a>
<br /><br />
</li>
<li>新增資料欄位:<br />以AlbumId為例,輸入 Column name,選取Data type ,<br />限制(Constraints)定義的選項,有:Primary Key, Foreign Key, Unique, Check conditions, Not NULL, Collate, Default等,每一個選項都可以按『Configure』鈕,進行進一步的設定。<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMXs8eyryyqHi07ezyHyx1xGrisi9KJiBgx95TLgV_osuTy5WDxOg9rsMIgsNnCPY3arfhp5_3uJpCRnuVi7EwbFUad4JWzzbhfg6kYpwMKP8C5TxT_HRVGDUNki7T0XqbnhuRxjKX8LI/s1600/TestWind_addColumn2.PNG" imageanchor="1"><img border="0" data-original-height="468" data-original-width="652" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMXs8eyryyqHi07ezyHyx1xGrisi9KJiBgx95TLgV_osuTy5WDxOg9rsMIgsNnCPY3arfhp5_3uJpCRnuVi7EwbFUad4JWzzbhfg6kYpwMKP8C5TxT_HRVGDUNki7T0XqbnhuRxjKX8LI/s1600/TestWind_addColumn2.PNG" /></a>
<br /><br />
</li>
<li>新增資料欄位 :<br />Column name : ArtistId 的 Foreign Key 設定<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8MxSLFwVfe2PciHIyPoEP3Pddiu2hm_TtRm4UU2twvYYTYFYHFGZ52Pwl3341nHGW0HdZMD1nmTa-Ooopw4g2aiBbVGKDrbFHw7XYHGQb_vNGsPZisY9ahW35jmvVtLztGFlNX-dpBGc/s1600/TestWind_addColumn3.PNG" imageanchor="1"><img border="0" data-original-height="662" data-original-width="548" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8MxSLFwVfe2PciHIyPoEP3Pddiu2hm_TtRm4UU2twvYYTYFYHFGZ52Pwl3341nHGW0HdZMD1nmTa-Ooopw4g2aiBbVGKDrbFHw7XYHGQb_vNGsPZisY9ahW35jmvVtLztGFlNX-dpBGc/s1600/TestWind_addColumn3.PNG" /></a></li>
<li>Commit structure change,儲存表格新增。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggAmQegJfiQi1WZZ8yX9epCGLFqJixrh3qYpN5ae-11fGwvwx1igqKXOdqUyyhCl2FABXhyphenhyphenbEPVuxUJTYuja0qQMNPGXF0LmqJ9HCYdRFcDd-iVVowsn9i0eEeVq2jBVKwqmwNN2APzbI/s1600/TestWind_commitStructureChange.PNG" imageanchor="1"><img border="0" data-original-height="278" data-original-width="651" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggAmQegJfiQi1WZZ8yX9epCGLFqJixrh3qYpN5ae-11fGwvwx1igqKXOdqUyyhCl2FABXhyphenhyphenbEPVuxUJTYuja0qQMNPGXF0LmqJ9HCYdRFcDd-iVVowsn9i0eEeVq2jBVKwqmwNN2APzbI/s1600/TestWind_commitStructureChange.PNG" /></a>
<br /><br />
</li>
<li>切換到DDL分頁,查看剛剛透過經由程式頁面操作所得到的DDL SQL指令碼
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwGKdA62dwNzV4B20XkeJ57UTcGNDtn-PoSCfF2Fgd8iKjvJIgZz6_3WKHty-PJakcUPeTZVRTRbzoB0BD_Ea0zzl1bMHykLTNRR4PNM73zDHO3I_OYAkgEb8aJC4jHs08KgmNlI7eAfQ/s1600/TestWind_addColumnDDL.PNG" imageanchor="1"><img border="0" data-original-height="466" data-original-width="646" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwGKdA62dwNzV4B20XkeJ57UTcGNDtn-PoSCfF2Fgd8iKjvJIgZz6_3WKHty-PJakcUPeTZVRTRbzoB0BD_Ea0zzl1bMHykLTNRR4PNM73zDHO3I_OYAkgEb8aJC4jHs08KgmNlI7eAfQ/s1600/TestWind_addColumnDDL.PNG" /></a>
<br /><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE Album (
AlbumId INTEGER CONSTRAINT PK_Album PRIMARY KEY
NOT NULL
DEFAULT NULL,
Title NVARCHAR (160) NOT NULL
DEFAULT NULL,
ArtistId INTEGER REFERENCES Artist (ArtistId) ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT NULL
DEFAULT NULL
);
</code></pre>
<br />
</li>
</ol>
<br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-35210089840806307322019-09-19T16:14:00.003+08:002019-09-19T16:14:26.294+08:00SQL : CREATE TABLE IF NOT EXISTS table_name在CREATE TABLE 之前,先確認目前連線的資料庫,確定不存在所要CREATE的資料表,這在透過程式管理的資料庫管控上,可以避免程式coding的複雜度,減少例外狀況的排出。<br />
簡單的加上 IF NOT EXISTS 即可輕鬆地達到事先檢查的目的。<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE IF NOT EXISTS Artist (
ArtistId INTEGER NOT NULL,
Name NVARCHAR (120),
CONSTRAINT PK_Artist PRIMARY KEY (ArtistId)
);
</code></pre>
<br />
在SQLite Studio執行這個CREATE TABLE IF NOT EXISTS指令:<br />
<ol>
<li>選取要執行這段指令的資料庫,可以參閱:</li>
<ul>
<li>取得SQLite版本的Chinook範例資料庫 <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>用SQLiteStudio建立SQL學習環境 <a href="http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitestudiosql.html</a> </li>
</ul>
<li>Tools → Open SQL Editor</li>
<li>在Query分頁中輸入所要執行的指令</li>
<li>(F9) Execute SQL</li>
<li>Status : 確認SQL指令執行無誤</li>
</ol>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiX-m_S0CBSuPgRh8BwtdqJ_x4xwFPNAe-kxZxOjPH4GGwlJSF8qdN8CCCT2OeD1PLM7twLh7cE9Y-7PGirGCUPHxGuMY2gQ_LtFf-5cKcVPyQjUvX8vDQ0vUTIEvwHIBhVFsCHAnNoVWs/s1600/TestWind_CreateTableIfNotExits.PNG" imageanchor="1"><img border="0" data-original-height="480" data-original-width="555" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiX-m_S0CBSuPgRh8BwtdqJ_x4xwFPNAe-kxZxOjPH4GGwlJSF8qdN8CCCT2OeD1PLM7twLh7cE9Y-7PGirGCUPHxGuMY2gQ_LtFf-5cKcVPyQjUvX8vDQ0vUTIEvwHIBhVFsCHAnNoVWs/s1600/TestWind_CreateTableIfNotExits.PNG" /></a>
<br />
<br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-42800092924908554102019-09-19T15:09:00.002+08:002019-09-20T14:53:46.829+08:00SQL : CREATE TABLE語法(Syntax)格式SQL : CREATE TABLE語法(Syntax)格式:<br />
<br />
<div style="background-color: lightgrey;">
CREATE TABLE [IF NOT EXISTS] 資料表名稱 (<br />
欄位名稱 [資料型態] [NULL | NULL] [AUTO_INCREMENT] [DEFAULT 預設值] [定義整合限制] ,<br />
...,<br />
PRIMARY KEY (欄位名稱, ... )<br />
UNIQUE (欄位名稱, ... )<br />
FOREIGN KEY (欄位名稱, ... ) REFERENCES 資料表(欄位名稱, ... )<br />
[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]<br />
[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]<br />
CHECK(限制的檢查條件)<br />
);
</div>
<ol>
<li>SQL程式碼採用自由格式,不限制一行只接受多少個字元,也不限制如何斷行。</li>
<li> [IF NOT EXISTS] :先確認資料表不存在,再予CREATE;可以不使用這個判斷選項。</li>
<li>PRIMARY KEY:用來定義某一或某些欄位為主鍵,不可為空值</li>
<li>UNIQUE:用來定義某一或某些欄位具有唯一的索引值,可以有空值</li>
<li>FOREIGN KEY:用來定義某一或某些欄位為外部鍵</li>
<li>REFERENCES 資料表(欄位名稱, ... ) :外鍵所要參考的資料表、資料欄位。</li>
<li>[NULL | NOT NULL]:可以為空值(NULL)、不可為空值(NOT NULL)選其中一項,或都不選。</li>
<li>[AUTO_INCREMENT]:當資料型態宣告為INT整數時,如果使用[AUTO_INCREMENT]選項,當新增一筆資料時,該欄位資料,會自動加一作為該欄位的資料值。</li>
<li> [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]:可使用或不使用ON DELETE,但選用後,必須選用{NO ACTION | CASCADE | SET DEFAULT | SET NULL}的其中一項。</li>
<li>CHECK 用來額外的檢查條件</li>
</ol>
CREATE TABLE的SQL範例:
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE [Artist]
(
[ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)
);
CREATE TABLE [Album]
(
[AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Title] NVARCHAR(160) NOT NULL,
[ArtistId] INTEGER NOT NULL,
FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
</code></pre>
<br />
參考資料:<br />
<ol>
<li>SQL As Understood By SQLite : CREATE TABLE<br /><a href="https://sqlite.org/lang_createtable.html" target="_blank">https://sqlite.org/lang_createtable.html</a> <br /></li>
<li>CREATE TABLE (Transact-SQL) <br /><a href="https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017" target="_blank">https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017</a></li>
</ol>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-81017020029883324642019-09-19T12:58:00.000+08:002019-09-20T10:01:36.686+08:00用SQLiteStudio建立SQL學習環境<ul>
<li>SQLiteStudio的取得及其相關功能,可以參閱:<br /><a href="http://blog.bod.idv.tw/2019/09/sqlitesqlite-studio.html" target="_blank">SQLite管理工具SQLite Studio</a> <a href="http://blog.bod.idv.tw/2019/09/sqlitesqlite-studio.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitesqlite-studio.html</a> </li>
<li>在這網站內的SQL學習,會以Chinook範例資料庫,當資料操作的資料來源。<br />如何取得Chinook?<br /><a href="https://github.com/lerocha/chinook-database" target="_blank">https://github.com/lerocha/chinook-database</a><br /><a href="https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources" target="_blank">https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources</a><br /><a href="https://archive.codeplex.com/?p=chinookdatabase" target="_blank">https://archive.codeplex.com/?p=chinookdatabase</a><br />可以參考:<a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">取得SQLite版本的Chinook範例資料庫</a> <a href="http://blog.bod.idv.tw/2019/09/sqlitechinook.html" target="_blank">http://blog.bod.idv.tw/2019/09/sqlitechinook.html</a></li>
<li>這裡除了準備Chinook Sample Database外,我們將在SQLiteStudio新增建立一個測試資料庫TestWind.sqlite的資料庫。<br /><br />Databases區塊內按滑鼠右鍵→Database→Add a database <br />或 Database→Add a database<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9NQsQOhWlF-NcdMXY6HBDMABZHHqHlr7S4yiGUv8OHb7iZB79Or0GLJW2G9aY1QEEi5Kfdbp_S2lLgZBREsdCJeD2pv8ZJlclqPLUARu-OMGRn17mq_dRCb39ESO7mABIc1i_pi8gQpY/s1600/TestWind_create.PNG" imageanchor="1"><img border="0" data-original-height="476" data-original-width="653" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9NQsQOhWlF-NcdMXY6HBDMABZHHqHlr7S4yiGUv8OHb7iZB79Or0GLJW2G9aY1QEEi5Kfdbp_S2lLgZBREsdCJeD2pv8ZJlclqPLUARu-OMGRn17mq_dRCb39ESO7mABIc1i_pi8gQpY/s1600/TestWind_create.PNG" /></a>
<br /><br />按『+』鈕,選擇存放的資料夾位置,輸入資料庫檔名:TestWind.sqlite,存檔。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJ_78RKy4VIpn4_SanXZyPyYW_j7IBod5FlUlsF9dWfXIxvVKFmsJEHYNLHxlF3C5N9OT-9kz9GJuci_RwhdvgnYWZb-RbAHbI-8HzYb9Dg2jUqGAxwPRt3m520mSk-1o0QK5YwBY0v7M/s1600/TestWind_assignFileName.PNG" imageanchor="1"><img border="0" data-original-height="656" data-original-width="654" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJ_78RKy4VIpn4_SanXZyPyYW_j7IBod5FlUlsF9dWfXIxvVKFmsJEHYNLHxlF3C5N9OT-9kz9GJuci_RwhdvgnYWZb-RbAHbI-8HzYb9Dg2jUqGAxwPRt3m520mSk-1o0QK5YwBY0v7M/s1600/TestWind_assignFileName.PNG" /></a>
<br />
<br />OK,建立TestWind資料庫。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaTdeocZ5OnZJMDZr5MduDazDDJsXKdc1GYzEICc5cOf3UHB3dnvJlRBPEQPXe277zFTdmyMYUdWmQxYrYf7aOxrB-QPRB869B_UbWrJ-0BH9XUKzkk1ltjqAwvV7RXqM7T70H0jIkDLg/s1600/TestWind_assignFileName_ok.PNG" imageanchor="1"><img border="0" data-original-height="466" data-original-width="653" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaTdeocZ5OnZJMDZr5MduDazDDJsXKdc1GYzEICc5cOf3UHB3dnvJlRBPEQPXe277zFTdmyMYUdWmQxYrYf7aOxrB-QPRB869B_UbWrJ-0BH9XUKzkk1ltjqAwvV7RXqM7T70H0jIkDLg/s1600/TestWind_assignFileName_ok.PNG" /></a>
<br /><br />確認SQLiteStudio的資料庫區塊,已有:Chinook, TestWind<br />選取TestWind,Connect to the database<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFeyQC4f2VUi85XKqu32aAa8xBHBh52DnPLOm0xm5bzb2AoNNavYusJzU6VCLdWO1V_qIpP_nnDgSb953q0hQ8NgkXoWHryTi6QdjJXownSpB_lSEJkfg9LJZMK6NlhCmewbecPmjGqFo/s1600/TestWind_databaseReady.PNG" imageanchor="1"><img border="0" data-original-height="476" data-original-width="653" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFeyQC4f2VUi85XKqu32aAa8xBHBh52DnPLOm0xm5bzb2AoNNavYusJzU6VCLdWO1V_qIpP_nnDgSb953q0hQ8NgkXoWHryTi6QdjJXownSpB_lSEJkfg9LJZMK6NlhCmewbecPmjGqFo/s1600/TestWind_databaseReady.PNG" /></a>
</li>
</ul>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-58998330259760820482019-09-19T08:21:00.003+08:002019-09-19T08:21:59.918+08:00SQL指令及主要分類(DDL, DML, DCL, TCL)SQL指令及主要分類(DDL, DML, DCL, TCL):<br />
<ul>
<li> DDL (Data Definition Language)</li>
<ul>
<li>用來定義資料庫結構的指令,例如:綱要schemas, 資料表tables ...。</li>
<li>CREATE, DROP, ALTER, RENAME, TRUNCATE<br /></li>
</ul>
<li> DML (Data Manipulation Language)</li>
<ul>
<li>用來處理資料庫中的資料的指令,一般資料的新增、修改、刪除、查詢等運算,都是屬於DML指令類。</li>
<li>SELECT, INSERT, UPDATE, DELETE<br /></li>
</ul>
<li> DCL (Data Control Language)</li>
<ul>
<li>用來控制管理資料庫的使用權限及相關安全設定的管控指令。</li>
<li>GRANT, REVOKE<br /></li>
</ul>
<li> TCL is Transaction Control Language</li>
<ul>
<li>管理資料庫中交易的指令。</li>
<li>START TRANSACTION (BEGIN, BEGIN WORK), COMMIT, ROLLBACK</li>
</ul>
</ul>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-57385237139008247412019-09-18T15:47:00.000+08:002019-09-20T10:01:06.092+08:00SQLite3的資料類型<ul>
<li>SQLite的官網提到,目前大部份的SQL資料庫引擎(除了SQLite之外)都使用靜態、嚴格的資料類型。 使用靜態類型時,資料值的數據類型由儲存這項資料的資料欄位型態決定。<br />SQLite的資料類型,使用更通用的動態類型系統(dynamic type system)。 在SQLite中,資料值的資料類型與資料值本身相關聯,而不是與存放資料的資料欄位型態相關聯。 SQLite的動態類型系統向下相容其他資料庫引擎中更常見的靜態類型系統,因為在靜態類型資料庫上工作的SQL語句應該在SQLite中以相同的方式工作。 但是,SQLite中的動態類型允許它執行傳統的嚴格類型資料庫中無法實現的操作。<br /><br />
</li>
<li>每一個存放在SQLite資料庫中的資料值,都具有下列的資料型態中的一個資料類型:</li>
<ol type="i">
<li><b><span style="color: red;">NULL</span></b> : 空值。</li>
<li><b><span style="color: red;">INTEGER</span></b> : 整數。是帶有正負值的整數,可能會使用1, 2, 3, 4, 6, 8個位元組(Bytes)來存放資料,實際使用的Bytes數,以存放的值來決定。</li>
<li><span style="color: red;"><b>REAL</b></span> : 浮點數值。以 8 Bytes來存放IEEE浮點數。</li>
<li><b><span style="color: red;">TEXT</span></b> : 文字字串值。以資料庫的文字編碼方式:UTF-8, UTF-16BE, UTF-16LE來儲存資料。</li>
<li><b><span style="color: red;">BLOB</span></b> : 二進位大型物件(<b>B</b>inary <b>L</b>arge <b>OB</b>ject)。</li>
<li>沒有Boolean值的資料儲存型別,SQLite3用0來儲存False(假),用1來儲存True(真)。</li>
<li>沒有日期(Date)、時間(Time)的資料儲存型別,SQLite內建的日期和時間函數,將日期和時間存儲為TEXT,REAL或INTEGER值。<br />如果是TEXT,存為ISO8601字符串(“YYYY-MM-DD HH:MM:SS.SSS”)。<br />如果是REAL,是記錄一個Julian的日期數,是西元前4714年11月24日格林威治中午以來的天數。<br />如果是INTEGER,是紀錄1970-01-01 00:00:00 UTC以來的秒數(Unix Time)。</li>
</ol>
<li>近似型別(Type Affinity)<br />SQLite3資料庫中的每一個資料欄位,都會被指定為下列型別之一的近似型別:TEXT, NUMERIC, INTEGER, REAL, BLOB。<br />欄位近似型別的決定規則(Determination Of Column Affinity),規則依序如下:</li>
</ul>
<ol>
<ol type="i">
<li>宣告的資料類型包含字串“INT”,視為INTEGER 的近似型別。</li>
<li>宣告的資料類型包含字串“CHAR”、“CLOB”或“TEXT”,視為TEXT的近似型別。 VARCHAR / NVARCHAR 類型包含字串“CHAR”,視為TEXT的近似型別。</li>
<li>宣告的類型包含字串“BLOB”、或未指定類型,視為BLOB的近似型別。</li>
<li>宣告的類型包含字串“REAL”、“FLOA”或“DOUB”,視為REAL的近似型別。</li>
<li>上述似規則以外,視為NUMERIC的近似型別。</li>
</ol>
</ol>
<ul>
<ol type="i">
</ol>
<li>近似型別歸類舉例:<br />
<table>
<tbody>
<tr>
<td width="500px">CREATE TABLE宣告或CAST5轉換式</td>
<td>歸類</td>
<td>規則</td>
</tr>
<tr>
<td>INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8</td>
<td valign="top">INTEGER</td>
<td valign="top">i</td>
</tr>
<tr>
<td>CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT,CLOB</td>
<td valign="top">TEXT</td>
<td valign="top">ii</td>
</tr>
<tr>
<td>BLOB, no datatype specified</td>
<td>BLOB</td>
<td>iii</td>
</tr>
<tr>
<td>REAL, DOUBLE, DOUBLE PRECISION, FLOAT</td>
<td>REAL</td>
<td>iv</td>
</tr>
<tr>
<td>NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME</td>
<td>NUMERIC</td>
<td>v</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</tbody></table>
</li>
<li>SQLite Studio資料欄位可以選擇的選項:<br />BIGINT, BLOB, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, DOUBLE, INTEGER, INT, NONE, NUMERIC, REAL, STRING, TEXT, VARCHAR<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqmWhE8TspDsrHIF8WMbFUp7K8HOdVZWOrn9RbioyxvVB_3fnsHRHRDPDlj2DmnyvcknYCP1W0BTsVppXX9_WbYEUrN2eCqP9NqEp5_g9IyKSdmE1hov0htVND5qg4b78yZS9-2neBERg/s1600/SQLite3_DataTypes.PNG" imageanchor="1"><img border="0" data-original-height="546" data-original-width="626" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqmWhE8TspDsrHIF8WMbFUp7K8HOdVZWOrn9RbioyxvVB_3fnsHRHRDPDlj2DmnyvcknYCP1W0BTsVppXX9_WbYEUrN2eCqP9NqEp5_g9IyKSdmE1hov0htVND5qg4b78yZS9-2neBERg/s1600/SQLite3_DataTypes.PNG" /></a>
</li>
</ul>
<br />
參考資料:Datatypes In SQLite Version 3 <a href="https://www.sqlite.org/datatype3.html" target="_blank">https://www.sqlite.org/datatype3.html</a>Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-41436563010088411172019-09-18T10:19:00.001+08:002019-09-22T15:45:10.652+08:00SQL參考資料<ol>
<li>(ANSI SQL 92) SQL-92 standards draft document<br /><a href="http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt" target="_blank">http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt</a><br /></li>
<li>SQL As Understood By SQLite<br /><a href="https://sqlite.org/lang.html" target="_blank">https://sqlite.org/lang.html</a> <br /></li>
<li>PostgreSQL: Documentation: 11: SQL Commands<br /><a href="https://www.postgresql.org/docs/11/sql-commands.html" target="_blank">https://www.postgresql.org/docs/11/sql-commands.html</a> <br /></li>
<li>SQL - 維基百科,自由的百科全書<br /><a href="https://zh.wikipedia.org/wiki/SQL" target="_blank">https://zh.wikipedia.org/wiki/SQL</a><br /></li>
<li>ANSI_SQL FAQ<br /><a href="https://www.tek-tips.com/faqs.cfm?fid=1073" target="_blank">https://www.tek-tips.com/faqs.cfm?fid=1073</a><br /></li>
<li>SQL Standard - Whitemarsh is SQL Standards, Metabase Software Products and Services to achieve Enterprise Database, through Data Quality, and sound Database Design.<br /><a href="https://www.wiscorp.com/SQLStandards.html" target="_blank">https://www.wiscorp.com/SQLStandards.html</a> <br /></li>
<li>SQLite -- Small. Fast. Reliable. Choose any three.<br /><a href="https://sqlite.org/index.html" target="_blank">https://sqlite.org/index.html</a> <br /></li>
<li>SQLiteStudio - Free (GPL licence), advanced SQLite database manager with intuitive user interface, with no installation needed (single executable file to run).<br /><a href="https://sqlitestudio.pl/index.rvt" target="_blank">https://sqlitestudio.pl/index.rvt</a><br /></li>
<li>DB Browser for SQLite -- DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.<br /><a href="https://sqlitebrowser.org/" target="_blank">https://sqlitebrowser.org/</a> </li>
</ol>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-60822206945337757892019-09-17T19:44:00.000+08:002019-09-20T10:00:17.114+08:00取得SQLite版本的Chinook範例資料庫下載取得Chinook範例資料庫:<br />
<a href="https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite" target="_blank">https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite</a><br />
<br />
簡化資料庫名稱,把Chinook_Sqlite.sqlite 修改為 Chinook.sqlite<br />
開啟SQLite Studio,以SQLite Studio開啟資料庫Chinook.sqlite :<br />
把資料庫檔案拖曳到SQLite Studio的區塊內,放開滑鼠,會開啟Database小視窗,按下OK鈕,就可以成功開啟這個資料庫了。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEAdEcdSwt_ZhUg3CJ5LNqUjR7c5ZF5y2gyCm4O4LYYmtotyRvtu_JK-sIL-z627oGB4sStVIro6i3uMCLtsVtBgru_J-Bn8ln_t2JW9rqqkzkrzyghM10UXd6CqbGbo1LpVTcJQDjgcg/s1600/Chinook_OpenUsingSQLiteStudio.PNG" imageanchor="1"><img border="0" data-original-height="668" data-original-width="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEAdEcdSwt_ZhUg3CJ5LNqUjR7c5ZF5y2gyCm4O4LYYmtotyRvtu_JK-sIL-z627oGB4sStVIro6i3uMCLtsVtBgru_J-Bn8ln_t2JW9rqqkzkrzyghM10UXd6CqbGbo1LpVTcJQDjgcg/s1600/Chinook_OpenUsingSQLiteStudio.PNG" /></a>
<br />
<br />
可以透過SQLite Studio查看Chinook.sqlite的資料表、資料欄位、Primary Key、索引(Index)、資料內容、Constraint(限制條件) ...<br />
將資料庫檔案拖曳到Database區塊內後,Database→Connect to the database<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD7wo-4Fsjie6pAupQSGJcl5ELld1fAf76f-z29ThsERSGQhkGGpU3TUPGJZ4ETiinWbT4OlE2HNvbKPNsIbRM-z1-BEEviuuGJSd_56w7hYop0SK5hnzQogHTM4NbKBnGWfcJWcJwMZ0/s1600/Chinook_ConnectToDB.PNG" imageanchor="1"><img border="0" data-original-height="547" data-original-width="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD7wo-4Fsjie6pAupQSGJcl5ELld1fAf76f-z29ThsERSGQhkGGpU3TUPGJZ4ETiinWbT4OlE2HNvbKPNsIbRM-z1-BEEviuuGJSd_56w7hYop0SK5hnzQogHTM4NbKBnGWfcJWcJwMZ0/s1600/Chinook_ConnectToDB.PNG" /></a>
<br />
<br />
Chinook資料庫內,共包含11個資料表:Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track,每個資料表內所包含的資料欄位,說明如下:(以Chinook_sqlite.sql的CREATE TABLE來查看資料表的資料欄位內容)<br />
<br />
<ul>
<li>Album :<br />
CREATE TABLE [Album]<br />
(<br />
[AlbumId] INTEGER NOT NULL,<br />
[Title] NVARCHAR(160) NOT NULL,<br />
[ArtistId] INTEGER NOT NULL,<br />
CONSTRAINT [PK_Album] PRIMARY KEY ([AlbumId]),<br />
FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);<br /><br />
</li>
<li>Artist :<br />
CREATE TABLE [Artist]<br />
(<br />
[ArtistId] INTEGER NOT NULL,<br />
[Name] NVARCHAR(120),<br />
CONSTRAINT [PK_Artist] PRIMARY KEY ([ArtistId])<br />
);<br /><br />
</li>
<li>Customer :<br />
CREATE TABLE [Customer]<br />
(<br />
[CustomerId] INTEGER NOT NULL,<br />
[FirstName] NVARCHAR(40) NOT NULL,<br />
[LastName] NVARCHAR(20) NOT NULL,<br />
[Company] NVARCHAR(80),<br />
[Address] NVARCHAR(70),<br />
[City] NVARCHAR(40),<br />
[State] NVARCHAR(40),<br />
[Country] NVARCHAR(40),<br />
[PostalCode] NVARCHAR(10),<br />
[Phone] NVARCHAR(24),<br />
[Fax] NVARCHAR(24),<br />
[Email] NVARCHAR(60) NOT NULL,<br />
[SupportRepId] INTEGER,<br />
CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerId]),<br />
FOREIGN KEY ([SupportRepId]) REFERENCES [Employee] ([EmployeeId])
ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);<br /><br />
</li>
<li>Employee :<br />
CREATE TABLE [Employee]<br />
(<br />
[EmployeeId] INTEGER NOT NULL,<br />
[LastName] NVARCHAR(20) NOT NULL,<br />
[FirstName] NVARCHAR(20) NOT NULL,<br />
[Title] NVARCHAR(30),<br />
[ReportsTo] INTEGER,<br />
[BirthDate] DATETIME,<br />
[HireDate] DATETIME,<br />
[Address] NVARCHAR(70),<br />
[City] NVARCHAR(40),<br />
[State] NVARCHAR(40),<br />
[Country] NVARCHAR(40),<br />
[PostalCode] NVARCHAR(10),<br />
[Phone] NVARCHAR(24),<br />
[Fax] NVARCHAR(24),<br />
[Email] NVARCHAR(60),<br />
CONSTRAINT [PK_Employee] PRIMARY KEY ([EmployeeId]),<br />
FOREIGN KEY ([ReportsTo]) REFERENCES [Employee] ([EmployeeId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);<br />
<br />
</li>
<li>Genre :<br />
CREATE TABLE [Genre]<br />
(<br />
[GenreId] INTEGER NOT NULL,<br />
[Name] NVARCHAR(120),<br />
CONSTRAINT [PK_Genre] PRIMARY KEY ([GenreId])<br />
);<br /><br />
</li>
<li>Invoice :<br />
CREATE TABLE [Invoice]<br />
(<br />
[InvoiceId] INTEGER NOT NULL,<br />
[CustomerId] INTEGER NOT NULL,<br />
[InvoiceDate] DATETIME NOT NULL,<br />
[BillingAddress] NVARCHAR(70),<br />
[BillingCity] NVARCHAR(40),<br />
[BillingState] NVARCHAR(40),<br />
[BillingCountry] NVARCHAR(40),<br />
[BillingPostalCode] NVARCHAR(10),<br />
[Total] NUMERIC(10,2) NOT NULL,<br />
CONSTRAINT [PK_Invoice] PRIMARY KEY ([InvoiceId]),<br />
FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([CustomerId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);<br /><br />
</li>
<li>InvoiceLine :<br />
CREATE TABLE [InvoiceLine]<br />
(<br />
[InvoiceLineId] INTEGER NOT NULL,<br />
[InvoiceId] INTEGER NOT NULL,<br />
[TrackId] INTEGER NOT NULL,<br />
[UnitPrice] NUMERIC(10,2) NOT NULL,<br />
[Quantity] INTEGER NOT NULL,<br />
CONSTRAINT [PK_InvoiceLine] PRIMARY KEY ([InvoiceLineId]),<br />
FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([InvoiceId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION,<br />
FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);<br /><br />
</li>
<li>MediaType :<br />
CREATE TABLE [MediaType]<br />
(<br />
[MediaTypeId] INTEGER NOT NULL,<br />
[Name] NVARCHAR(120),<br />
CONSTRAINT [PK_MediaType] PRIMARY KEY ([MediaTypeId])<br />
);<br />
<br />
</li>
<li>Playlist :<br />
CREATE TABLE [Playlist]<br />
(<br />
[PlaylistId] INTEGER NOT NULL,<br />
[Name] NVARCHAR(120),<br />
CONSTRAINT [PK_Playlist] PRIMARY KEY ([PlaylistId])<br />
);<br />
</li>
<li>PlaylistTrack :<br />
CREATE TABLE [PlaylistTrack]<br />
(<br />
[PlaylistId] INTEGER NOT NULL,<br />
[TrackId] INTEGER NOT NULL,<br />
CONSTRAINT [PK_PlaylistTrack] PRIMARY KEY ([PlaylistId], [TrackId]),<br />
FOREIGN KEY ([PlaylistId]) REFERENCES [Playlist] ([PlaylistId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION,<br />
FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);<br />
</li>
<li>Track<br />
CREATE TABLE [Track]<br />
(<br />
[TrackId] INTEGER NOT NULL,<br />
[Name] NVARCHAR(200) NOT NULL,<br />
[AlbumId] INTEGER,<br />
[MediaTypeId] INTEGER NOT NULL,<br />
[GenreId] INTEGER,<br />
[Composer] NVARCHAR(220),<br />
[Milliseconds] INTEGER NOT NULL,<br />
[Bytes] INTEGER,<br />
[UnitPrice] NUMERIC(10,2) NOT NULL,<br />
CONSTRAINT [PK_Track] PRIMARY KEY ([TrackId]),<br />
FOREIGN KEY ([AlbumId]) REFERENCES [Album] ([AlbumId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION,<br />
FOREIGN KEY ([GenreId]) REFERENCES [Genre] ([GenreId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION,<br />
FOREIGN KEY ([MediaTypeId]) REFERENCES [MediaType] ([MediaTypeId]) <br /> ON DELETE NO ACTION ON UPDATE NO ACTION<br />
);</li>
</ul>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-28443315079093341652019-09-17T16:28:00.000+08:002019-09-17T16:55:49.014+08:00範例資料庫(Sample Database)使用現成的範例資料庫,可以有效幫助SQL語法的學習,微軟在GitHub上釋出多個範例資料庫(Sample Database):<br />
<ul>
<li>Official Microsoft GitHub Repository containing code samples for SQL Server<br /><a href="https://github.com/microsoft/sql-server-samples" target="_blank">https://github.com/microsoft/sql-server-samples</a><br /><a href="https://github.com/microsoft/sql-server-samples/tree/master/samples/databases" target="_blank">https://github.com/microsoft/sql-server-samples/tree/master/samples/databases</a> </li>
<ul>
<li>wide-world-importers</li>
<li>contoso-data-warehouse</li>
<li>AdventureWorks</li>
<li>Northwind </li>
<li>Pubs</li>
</ul>
<li>範例資料庫的資料庫關聯圖(database diagram)(實體關聯圖 ER Diagram)</li>
<ul>
<li>AdventureWorks OLTP Database Diagram</li>
https://improveandrepeat.com/wp-content/uploads/2018/12/AdvWorksOLTPSchemaVisio.png
<li>An ER Diagram for the Northwind Sample Database<br />https://documentation.red-gate.com/dms6/files/49646072/49646073/3/1559655630714/ERDiagramNorthwind.png</li>
<li>An ER Diagram for the PUBS Sample Database<br />https://documentation.red-gate.com/dms6/files/49646075/49646076/2/1559655574834/ERDiagramPUBS.png</li>
</ul>
</ul>
<br />
除了上述微軟提供的範例資料庫,還有一個常用來替代Northwind的資料庫:Chinook<br />
Chinook : Sample database for SQL Server, Oracle, MySQL, PostgreSQL, SQLite, DB2<br />
下載取得Chinook的資料:<br />
<a href="https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources" target="_blank">https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources</a><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEithe4p2sVXnxrpbZzDd_-y-QZ6e_WtnQKLgHfik7me3Obf321RGSIMRJdPcsJy3ad93xeHuAkq-2KbW9KqpO2IvCIo43ZCAAWVuYJqFkNsIFqoj7_bGxzOv_J2hNoXOLYnWhI4i3gXfKE/s1600/SampleDatabase_Chinook.PNG" imageanchor="1"><img border="0" data-original-height="1040" data-original-width="591" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEithe4p2sVXnxrpbZzDd_-y-QZ6e_WtnQKLgHfik7me3Obf321RGSIMRJdPcsJy3ad93xeHuAkq-2KbW9KqpO2IvCIo43ZCAAWVuYJqFkNsIFqoj7_bGxzOv_J2hNoXOLYnWhI4i3gXfKE/s1600/SampleDatabase_Chinook.PNG" /></a>
<br />
<br />
Chinook 範例資料庫的Database diagram:<br />
<a href="http://schemaspy.org/sample/relationships.html" target="_blank">http://schemaspy.org/sample/relationships.html</a> <br />http://schemaspy.org/sample/diagrams/summary/relationships.real.compact.png <br />
http://schemaspy.org/sample/diagrams/summary/relationships.real.large.png <br /><br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com1tag:blogger.com,1999:blog-8739989337344660455.post-1431350537152238242019-09-17T10:25:00.000+08:002020-03-11T11:54:21.607+08:00SQLite管理工具SQLite Studio<br />
<span style="background-color: yellow;">資料更新:<br />2019-12-30起,SQLiteStudio的原始碼及程式下載,已經移至GitHub<br /><a href="https://github.com/pawelsalawa/sqlitestudio/releases" target="_blank">https://github.com/pawelsalawa/sqlitestudio/releases</a><br />如需要舊版的資料:<br />(3.x.x) : <a href="https://www.dropbox.com/sh/ao4nz2qjfsz2yuy/AABwiiss3do7n0wNecuk-uyna?dl=0" target="_blank">https://www.dropbox.com/sh/ao4nz2qjfsz2yuy/AABwiiss3do7n0wNecuk-uyna?dl=0</a><br />(2.x.x) : <a href="https://www.dropbox.com/sh/iyilxtepgswpdlm/AADmYlJ4QRYWn_eo9u4fPn0Aa?dl=0" target="_blank">https://www.dropbox.com/sh/iyilxtepgswpdlm/AADmYlJ4QRYWn_eo9u4fPn0Aa?dl=0</a></span><br />
<br />
原內容:<br />
之前介紹的SQLite-tools是文字介面的管理程式,還有圖形介面的DB Browser for SQLite,但我最常用的是SQLite Studio,現在就來介紹一下SQLite Studio:<br />
<br />
SQLite Studio官方網站:<a href="https://sqlitestudio.pl/index.rvt" target="_blank">https://sqlitestudio.pl/index.rvt</a><br />
SQLite Studio下載網誌:<a href="https://sqlitestudio.pl/index.rvt?act=download" target="_blank">https://sqlitestudio.pl/index.rvt?act=download</a><br />
<br />
提供Windows / Linux / MacOSX 安裝版即可攜版<br />
有提供SHA-256,下載後先比對一下再使用,用起來會更放心。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKzMuj4SEuxeZdumWmmT_JX_KTdM2q-ra70OarcEcHikVhoyrHg9XXr4rY8my48CslikoKBcTFeviGvDyfoReANutKMANy_c03Bcglh47iErxJEtpJR_gw9oKiS2fUyE5q1fDhKPuBcN8/s1600/SQLiteStudio_download.PNG" imageanchor="1"><img border="0" data-original-height="596" data-original-width="658" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKzMuj4SEuxeZdumWmmT_JX_KTdM2q-ra70OarcEcHikVhoyrHg9XXr4rY8my48CslikoKBcTFeviGvDyfoReANutKMANy_c03Bcglh47iErxJEtpJR_gw9oKiS2fUyE5q1fDhKPuBcN8/s1600/SQLiteStudio_download.PNG" /></a>
<br />
<br />
以Windows portable SQLiteStudio-3_2_1.zip為例:(解壓縮後,可以直接使用)<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgv89Sli7G4z07Yn1OG-tEt3oxLZhWfIEatSCG-rZeJJIV2ir798NrKNTtTI-OVYuC1MuQCVsToyW-w8Ygdj-8a5lL_QgTm4W3r4rexUKGuoUVJjJ2aDFWk0K3bRrBEbA392CK-HqnQlrY/s1600/SQLiteStudio_portable.PNG" imageanchor="1"><img border="0" data-original-height="284" data-original-width="614" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgv89Sli7G4z07Yn1OG-tEt3oxLZhWfIEatSCG-rZeJJIV2ir798NrKNTtTI-OVYuC1MuQCVsToyW-w8Ygdj-8a5lL_QgTm4W3r4rexUKGuoUVJjJ2aDFWk0K3bRrBEbA392CK-HqnQlrY/s1600/SQLiteStudio_portable.PNG" /></a>
<br />
<br />
SQLite Studio 主要的功能簡介:<br />
<ul>
<li>主畫面:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDAuLShv68IUPvA9gS6E0g5bPdwkEblUdb87aQGoKqcDNtFhBMRGVL9IBvtXLYXUqcofEge77MlYhmfPNDKYUspqy8rxyj7dtwUwEM7UDYgT_H4M95Zf6kdIdsInR9yjAAaN_MEn7TW4I/s1600/SQLiteStudio_Main.PNG" imageanchor="1"><img border="0" data-original-height="489" data-original-width="649" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDAuLShv68IUPvA9gS6E0g5bPdwkEblUdb87aQGoKqcDNtFhBMRGVL9IBvtXLYXUqcofEge77MlYhmfPNDKYUspqy8rxyj7dtwUwEM7UDYgT_H4M95Zf6kdIdsInR9yjAAaN_MEn7TW4I/s1600/SQLiteStudio_Main.PNG" /></a>
</li>
<li>Database→Connect to database</li>
<li>Database→Disconnect from database</li>
<li>Database→Add a database</li>
<li>Database→Edit the database</li>
<li>Database→Remove the database</li>
<li>Database→Export the database<br />可以匯出的格式:HTML / JSON / PDF / SQL / XML<br />可以指定文字編碼(text encoding)</li>
<li>Database→Convert database type<br />以開啟SQLite3為例,可以指定轉換為:SQLite2 / SQLCipher / System.Data.SQLite / WxSQLite3</li>
<li>Database→Vacuum</li>
<li>Database→Integrity check</li>
<li>Database→Refresh selected database schema</li>
<li>Database→Refresh all database schema<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_OrOnVaMNfVzf4s7Yb4rxsMTap9doIerP1f_NUWwTGVnLB1PO3By7vtQKblExva-bfLElRtzKiDDSJEb6PnE49NYbbOlv9ciztHa-klv6Uq2qacBnTMJUngMYscraP5wLGaqFNuMmOV0/s1600/SQLiteStudio_Database.PNG" imageanchor="1"><img border="0" data-original-height="329" data-original-width="355" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_OrOnVaMNfVzf4s7Yb4rxsMTap9doIerP1f_NUWwTGVnLB1PO3By7vtQKblExva-bfLElRtzKiDDSJEb6PnE49NYbbOlv9ciztHa-klv6Uq2qacBnTMJUngMYscraP5wLGaqFNuMmOV0/s1600/SQLiteStudio_Database.PNG" /></a>
</li>
<li>Structure→Create a table</li>
<li>Structure→Edit the table</li>
<li>Structure→Delete the table</li>
<li>Structure→Create an index</li>
<li>Structure→Edit the index</li>
<li>Structure→Create a trigger</li>
<li>Structure→Edit the trigger</li>
<li>Structure→Delete the trigger</li>
<li>Structure→Create a view</li>
<li>Structure→Edit the view</li>
<li>Structure→Delete the view<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDJh0eO1Ce8HjbPWaq4UOh9uncanvcdXCJf8yMt962xeS2JBPeAzdRFzqjl7SrgM4qhPdrA3_3h4WJT3o2bIpQyhT2OttwilKT2MhYj9j2MAoKMQ-8EdZ10rs2Zyf63EDe3nEcAXCvxWM/s1600/SQLiteStudio_View.PNG" imageanchor="1"><img border="0" data-original-height="350" data-original-width="389" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDJh0eO1Ce8HjbPWaq4UOh9uncanvcdXCJf8yMt962xeS2JBPeAzdRFzqjl7SrgM4qhPdrA3_3h4WJT3o2bIpQyhT2OttwilKT2MhYj9j2MAoKMQ-8EdZ10rs2Zyf63EDe3nEcAXCvxWM/s1600/SQLiteStudio_View.PNG" /></a>
</li>
<li>View→Databases</li>
<li>View→Status</li>
<li>View→Database toolbar</li>
<li>View→Structure toolbar</li>
<li>View→Tools</li>
<li>View→Window list</li>
<li>View→View toolbar</li>
<li>View→Tile windows</li>
<li>View→Tile windows horizontally</li>
<li>View→Tile windows vertically</li>
<li>View→Cascade window</li>
<li>View→Close selected windows</li>
<li>View→Close all windows but selected</li>
<li>View→Close all windows</li>
<li>View→Restore recently closed window</li>
<li>View→Rename selected windows</li>
<li>View→Window list<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-Mf_R994GvEbbcsguUc7l1S2PAHLcOyubNmJPBemITrqiWOeZhmItLxDMiehnxsg9zLPjvfl8JOts7QF9EtEN2ADhaKt7NF3TODF1rkzw0Lwg8dVKK2-6tFSbh4x32DRm7fWx7VO2Ubs/s1600/SQLiteStudio_View.PNG" imageanchor="1"><img border="0" data-original-height="509" data-original-width="658" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-Mf_R994GvEbbcsguUc7l1S2PAHLcOyubNmJPBemITrqiWOeZhmItLxDMiehnxsg9zLPjvfl8JOts7QF9EtEN2ADhaKt7NF3TODF1rkzw0Lwg8dVKK2-6tFSbh4x32DRm7fWx7VO2Ubs/s1600/SQLiteStudio_View.PNG" /></a>
</li>
<li>Tools→Open SQL editor</li>
<li>Tools→Open DDL history</li>
<li>Tools→Open SQL functions editor</li>
<li>Tools→Open collations editor</li>
<li>Tools→Import </li>
<li>Tools→Export</li>
<li>Tools→Open configuration dialog<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwBwoiUmQlToiZmcdBQC5LKbzR1B3sVY-x2zaeYzPQhVDEquCcvgXIbJtWAPS_-LPI-GHJ0cOfjVrF75Dat5jGVjAwKpVh6h1DIsQpyN501Xr2R56ZyC-W25oA0I89rYQGaVVHaLX7tMs/s1600/SQLiteStudio_Tools.PNG" imageanchor="1"><img border="0" data-original-height="242" data-original-width="473" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwBwoiUmQlToiZmcdBQC5LKbzR1B3sVY-x2zaeYzPQhVDEquCcvgXIbJtWAPS_-LPI-GHJ0cOfjVrF75Dat5jGVjAwKpVh6h1DIsQpyN501Xr2R56ZyC-W25oA0I89rYQGaVVHaLX7tMs/s1600/SQLiteStudio_Tools.PNG" /></a><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpwQu3d64JciwgwG7nUkR7flLpltfBmjAyCVqinFK-NC4xDkWV4dkdtbg9zG0oCd1AYKP-kd-KyoTI6ULtEPFd163MCmnMAbvXZeeAdQB0R9pOFlc3go_WaslIxnl0zwcbHk956NuL3zQ/s1600/SQLiteStudio_Tools_config.PNG" imageanchor="1"><img border="0" data-original-height="419" data-original-width="586" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpwQu3d64JciwgwG7nUkR7flLpltfBmjAyCVqinFK-NC4xDkWV4dkdtbg9zG0oCd1AYKP-kd-KyoTI6ULtEPFd163MCmnMAbvXZeeAdQB0R9pOFlc3go_WaslIxnl0zwcbHk956NuL3zQ/s1600/SQLiteStudio_Tools_config.PNG" /></a>
</li>
<li>Help→User Manual</li>
<li>Help→SQLite documentation</li>
<li>Help→Open home page</li>
<li>Help→Open forum page</li>
<li>Help→Check for updates</li>
<li>Help→Report a bug</li>
<li>Help→Propose a new feature</li>
<li>Help→Bugs and feature requests</li>
<li>Help→Licence</li>
<li>Help→About<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizWHqw46KteseXJtkQsSlOOcJQ-C-YyKxsYLTY1GyzCl9tikVlmFET-YrL4ugtXQfF96ycJKr3-LKyM70P5mahcxjZ6wOVz0IIBJpxCYi9jfyXXdq6VvEiwn06oSLifsHWxBR3OSbhhH4/s1600/SQLiteStudio_Help.PNG" imageanchor="1"><img border="0" data-original-height="308" data-original-width="464" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizWHqw46KteseXJtkQsSlOOcJQ-C-YyKxsYLTY1GyzCl9tikVlmFET-YrL4ugtXQfF96ycJKr3-LKyM70P5mahcxjZ6wOVz0IIBJpxCYi9jfyXXdq6VvEiwn06oSLifsHWxBR3OSbhhH4/s1600/SQLiteStudio_Help.PNG" /></a></li>
</ul>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-83881582692577722532019-09-15T20:03:00.000+08:002019-09-15T20:03:08.030+08:00SQLite管理工具DB Browser for SQLiteSQLite-tools雖然是官方網站提供的管理工具,但文字界面的操作方式,操作上總有親和力不足的狀況,還好除了SQLite-tools,並不是別無選擇,還可以有DB Browser for SQLite, SQLite Studio...,這裡先介紹DB Browser for SQLite。<br />
<br />
DB Browser for SQLite官方網站:<a href="https://sqlitebrowser.org/" target="_blank">https://sqlitebrowser.org/</a><br />
DB Browser for SQLite的下載網址:<a href="https://sqlitebrowser.org/dl/" target="_blank">https://sqlitebrowser.org/dl/</a><br />
提供各種作業系統(Windows, Mac, Linux ...) 安裝、免安裝、可攜程式的下載,或線上更新安裝的指令做法等。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZTd8HuWmNUCELwNDe4ZoOZNFubY4oMa0sB2GezjKDPgfjqDvn5CoIY7CfdmoCiU32sS6UKJaH4TeWsyz0PddEQ0QESHSmRrvCspTYCUa561t2L_BmQ1p6AuIpSoKWbZk9BOykcSaj9pE/s1600/SQLiteBrowser_download.PNG" imageanchor="1"><img border="0" data-original-height="472" data-original-width="744" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZTd8HuWmNUCELwNDe4ZoOZNFubY4oMa0sB2GezjKDPgfjqDvn5CoIY7CfdmoCiU32sS6UKJaH4TeWsyz0PddEQ0QESHSmRrvCspTYCUa561t2L_BmQ1p6AuIpSoKWbZk9BOykcSaj9pE/s1600/SQLiteBrowser_download.PNG" /></a>
<br />
<br />
以.zip (no installer) for 64-bit Windows 為例:(解壓縮後,可以直接使用)<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif8wQSR5C_l2HeZQhU-zym6Jnbp8KQYKnaaLDciIuyIXgxxhHkt6tRvR-7vtA5rE52MMsWRYWsntXhvWpuzB3bTCMw5Se6K1wi9mOZsNU6D4jwJkKe3xgay1Js9cjprzT2xmnjfChsbGc/s1600/SQLiteBrowser_NoInstaller.PNG" imageanchor="1"><img border="0" data-original-height="186" data-original-width="615" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif8wQSR5C_l2HeZQhU-zym6Jnbp8KQYKnaaLDciIuyIXgxxhHkt6tRvR-7vtA5rE52MMsWRYWsntXhvWpuzB3bTCMw5Se6K1wi9mOZsNU6D4jwJkKe3xgay1Js9cjprzT2xmnjfChsbGc/s1600/SQLiteBrowser_NoInstaller.PNG" /></a>
<br />
<br />
DB Browser 主要的功能簡介:<br />
<ul>
<li>主畫面→Database Structure<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1X_EPMNxFT6msTE9iVXVS8M2Eur1njEAxW6YndvM4tzjf1BcSLXqwkhXxFPMqBxyZxIjAYxw5KLUHP72dMicZL4ZuFdsRXmtyLqaqjR8x51WpaKqTnV07NEsWc_vCZnoa5F9yNkJSDms/s1600/SQLiteBrowser_Main_DatabaseStructure.PNG" imageanchor="1"><img border="0" data-original-height="106" data-original-width="491" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1X_EPMNxFT6msTE9iVXVS8M2Eur1njEAxW6YndvM4tzjf1BcSLXqwkhXxFPMqBxyZxIjAYxw5KLUHP72dMicZL4ZuFdsRXmtyLqaqjR8x51WpaKqTnV07NEsWc_vCZnoa5F9yNkJSDms/s1600/SQLiteBrowser_Main_DatabaseStructure.PNG" /></a>
</li>
<li>主畫面→Browse Data<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdZPwcm6UR2YV9epHR0ZZwJKnlyIxzrVzWrgm1N_VDpSjvaqm_vp3hCbPCSCxgFg5ygDPHznLXj0ip7let_D-kaa-Mbo3zLLBqy5MepzWTtxiGRZmXZ4mrro1JCeagCUYPirxvHst8H8w/s1600/SQLiteBrowser_Main_BrowseData.PNG" imageanchor="1"><img border="0" data-original-height="298" data-original-width="505" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdZPwcm6UR2YV9epHR0ZZwJKnlyIxzrVzWrgm1N_VDpSjvaqm_vp3hCbPCSCxgFg5ygDPHznLXj0ip7let_D-kaa-Mbo3zLLBqy5MepzWTtxiGRZmXZ4mrro1JCeagCUYPirxvHst8H8w/s1600/SQLiteBrowser_Main_BrowseData.PNG" /></a>
</li>
<li>主畫面→Edit Pragmas<br />選項:Auto Vacuum / Automatic Index / Case Sensitive Like / Checkpoint Full FSYNC / Foreign Keys / Full FSYNC / Ignore Check Contraints / Journal Mode / Journal Size Limit / Locking Mode / Max Page Count / Page Size / Recursive Triggers / Secure Delete / Sychronous / Temp Store / User Version / WAL Auto Checkpoint<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj8kCKCs381GmERQyDxfUTs4JYaCwWzKkDuq5xRosaM_-CyE65BiPbFESjkN3GBKEB5c3Ocld4XgQtbdFixP_orYpZJB1aMeZLBt3xjsTj9bv2vKLNFSHy9fO3h25d8QVbGqv1V3Tf_RI/s1600/SQLiteBrowser_Main_EditPragmas.PNG" imageanchor="1"><img border="0" data-original-height="634" data-original-width="505" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj8kCKCs381GmERQyDxfUTs4JYaCwWzKkDuq5xRosaM_-CyE65BiPbFESjkN3GBKEB5c3Ocld4XgQtbdFixP_orYpZJB1aMeZLBt3xjsTj9bv2vKLNFSHy9fO3h25d8QVbGqv1V3Tf_RI/s1600/SQLiteBrowser_Main_EditPragmas.PNG" /></a>
</li>
<li>主畫面→Execute SQL<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL3bPEi_SDsSutGTfsIUId3f5TRuJTULoaHj0aAd3msdnK0TkhHY43cHbuUIdPsm_gut7PaGU8cEJRhRkaYWqiV2DgBMYpPXjk1R02OwIFieuw6DPQAI4kI3EwSfQ6UR48uWorjDaNGoI/s1600/SQLiteBrowser_Main_ExecuteSQL.PNG" imageanchor="1"><img border="0" data-original-height="299" data-original-width="505" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL3bPEi_SDsSutGTfsIUId3f5TRuJTULoaHj0aAd3msdnK0TkhHY43cHbuUIdPsm_gut7PaGU8cEJRhRkaYWqiV2DgBMYpPXjk1R02OwIFieuw6DPQAI4kI3EwSfQ6UR48uWorjDaNGoI/s1600/SQLiteBrowser_Main_ExecuteSQL.PNG" /></a>
</li>
<li>檔案→新建資料庫</li>
<li>檔案→New In-Memory Database</li>
<li>檔案→打開資料庫</li>
<li>檔案→Open Database Read Only ...</li>
<li>檔案→Attach Database ...</li>
<li>檔案→關閉資料庫</li>
<li>檔案→Write Changes</li>
<li>檔案→Revert Changes</li>
<li>檔案→匯入→Database from SQL file ...</li>
<li>檔案→匯入→Table from CSV file ...</li>
<li>檔案→匯出→Database to SQL file ...</li>
<li>檔案→匯出→Table(s) as CSV file ...</li>
<li>檔案→匯出→Table(s) to JSON ...</li>
<li>檔案→Open Project...</li>
<li>檔案→Save Project...</li>
<li>檔案→最近開啟過的五個資料庫檔案</li>
<li>檔案→退出<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9-n_l6pDd8KN08h5Re3y34m8AQcdv8jpoYHKTvef1nDxtR17mm0_URNtYohL_0gjXGqupjiYAkmbDMvBFAWu4yRgIJTPdUO4D1zM7jFeTbMLqEXWTAKuZuXZod8jKnKrhtC0nbtM5XvY/s1600/SQLiteBrowser_File.PNG" imageanchor="1"><img border="0" data-original-height="504" data-original-width="509" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9-n_l6pDd8KN08h5Re3y34m8AQcdv8jpoYHKTvef1nDxtR17mm0_URNtYohL_0gjXGqupjiYAkmbDMvBFAWu4yRgIJTPdUO4D1zM7jFeTbMLqEXWTAKuZuXZod8jKnKrhtC0nbtM5XvY/s1600/SQLiteBrowser_File.PNG" /></a>
</li>
<li>編輯→Create Table...</li>
<li>編輯→Modify Table...</li>
<li>編輯→Delete Table...</li>
<li>編輯→Create Index...</li>
<li>編輯→偏好選項...<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy0XouKrLNfrCLx_oqQiVwQPjz6wErEhbAcm7CSp8t8R6N3Qld38dRXoYC0248rIEJgHsvn4CLTgYzVQtLM8Znj-2ojKA-BG_MsKZeYGuYTVLWDK3fo8OzgPsI0Wc1Df4SBEWjw7kFYfQ/s1600/SQLiteBrowser_Edit.PNG" imageanchor="1"><img border="0" data-original-height="199" data-original-width="508" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy0XouKrLNfrCLx_oqQiVwQPjz6wErEhbAcm7CSp8t8R6N3Qld38dRXoYC0248rIEJgHsvn4CLTgYzVQtLM8Znj-2ojKA-BG_MsKZeYGuYTVLWDK3fo8OzgPsI0Wc1Df4SBEWjw7kFYfQ/s1600/SQLiteBrowser_Edit.PNG" /></a></li>
<li>編輯→偏好選項...→General<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqtw63DX_pFi0no-t8hxiElCbCIFx8xYYluW9lo5T0dZ0a23lfzROZf2yK09eApxuwjlJ9sqxwQAfHLeknty37i4u9kIhczX-XdIyg6ftW4N09u-6GUPaMHMovau568uvEHd99zCciBt8/s1600/SQLiteBrowser_Edit_Preference.PNG" imageanchor="1"><img border="0" data-original-height="507" data-original-width="729" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqtw63DX_pFi0no-t8hxiElCbCIFx8xYYluW9lo5T0dZ0a23lfzROZf2yK09eApxuwjlJ9sqxwQAfHLeknty37i4u9kIhczX-XdIyg6ftW4N09u-6GUPaMHMovau568uvEHd99zCciBt8/s1600/SQLiteBrowser_Edit_Preference.PNG" /></a>
</li>
<li>編輯→偏好選項...→資料庫<br />SQL to execute after opening database,這個功能可以紀錄資料庫的開啟歷程<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhibLf1IF7NH-kxYRzLsXsFArgU4f5uMTaObK1zTdGnGdZ73SnizmgSsIQiiIW8LVBw5_us_Xqz6wm-MKkdeJquo3KlSMl-Y-0xfDI6jeM8pGLgBlzK9CUWNmXdAHzXKSto-nR3KiXo_zc/s1600/SQLiteBrowser_Edit_Preference_database.PNG" imageanchor="1"><img border="0" data-original-height="422" data-original-width="467" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhibLf1IF7NH-kxYRzLsXsFArgU4f5uMTaObK1zTdGnGdZ73SnizmgSsIQiiIW8LVBw5_us_Xqz6wm-MKkdeJquo3KlSMl-Y-0xfDI6jeM8pGLgBlzK9CUWNmXdAHzXKSto-nR3KiXo_zc/s1600/SQLiteBrowser_Edit_Preference_database.PNG" /></a>
</li>
<li>編輯→偏好選項...→Data Browser<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieHxFzceqr59Pgt6r7SvKp46O8QzoMgSyi78nugXr9jnssi_pugmP9-xFOIl7qbh9yIgG-iQQXNPkPVVAGfeaSLTDDKGQ6So5XpzhGeeGHffYceSOiG7g9d-cwvK5DxejP7fscxrl2V9E/s1600/SQLiteBrowser_Edit_Preference_DataBrowser.PNG" imageanchor="1"><img border="0" data-original-height="509" data-original-width="728" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieHxFzceqr59Pgt6r7SvKp46O8QzoMgSyi78nugXr9jnssi_pugmP9-xFOIl7qbh9yIgG-iQQXNPkPVVAGfeaSLTDDKGQ6So5XpzhGeeGHffYceSOiG7g9d-cwvK5DxejP7fscxrl2V9E/s1600/SQLiteBrowser_Edit_Preference_DataBrowser.PNG" /></a>
</li>
<li>編輯→偏好選項...→SQL<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv1ulvquK8PMRhw8iAmahwE3dJ5mg0NDKgEb4tJT-WwhFtIiHJ1gZwQhK0GVl357jUFJpjr7XaVAaDrAvufy9VN03W1ARZU4msjpXdBxCNEMoy3ZJ_CgvFMSsZej6erBz_55qNa9bCNpk/s1600/SQLiteBrowser_Edit_Preference_SQL.PNG" imageanchor="1"><img border="0" data-original-height="526" data-original-width="728" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv1ulvquK8PMRhw8iAmahwE3dJ5mg0NDKgEb4tJT-WwhFtIiHJ1gZwQhK0GVl357jUFJpjr7XaVAaDrAvufy9VN03W1ARZU4msjpXdBxCNEMoy3ZJ_CgvFMSsZej6erBz_55qNa9bCNpk/s1600/SQLiteBrowser_Edit_Preference_SQL.PNG" /></a>
</li>
<li>編輯→偏好選項...→擴充套件<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggpMAuDMke237fac7omIzMsMqE5iGm_JNedT_4jc9H_m1ePR9q_l6hSQ6zV4yEQCIw_7JRwvmnC65Qei3F207iGYG-BrvL6y2tt4fqq4pEazNeherVxKeLWNBzIA-FrYEdJldSfu1fs9s/s1600/SQLiteBrowser_Edit_Preference_Extend.PNG" imageanchor="1"><img border="0" data-original-height="506" data-original-width="726" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggpMAuDMke237fac7omIzMsMqE5iGm_JNedT_4jc9H_m1ePR9q_l6hSQ6zV4yEQCIw_7JRwvmnC65Qei3F207iGYG-BrvL6y2tt4fqq4pEazNeherVxKeLWNBzIA-FrYEdJldSfu1fs9s/s1600/SQLiteBrowser_Edit_Preference_Extend.PNG" /></a>
</li>
<li>編輯→偏好選項...→Remote<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiM1K6CNe4miAEfzY4hy8y-Xxs6YOLYtWAOz1n9Ds4CeL1egLrznGzdFChmom4CB3q0ORMEn_Oag-RwGxKfR91Q76pkmJLMx3og_a1cE11cgb9Fsetv_mIwU8MpM-bkPnQ3TJvq2ilYzjQ/s1600/SQLiteBrowser_Edit_Preference_Remote.PNG" imageanchor="1"><img border="0" data-original-height="508" data-original-width="731" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiM1K6CNe4miAEfzY4hy8y-Xxs6YOLYtWAOz1n9Ds4CeL1egLrznGzdFChmom4CB3q0ORMEn_Oag-RwGxKfR91Q76pkmJLMx3og_a1cE11cgb9Fsetv_mIwU8MpM-bkPnQ3TJvq2ilYzjQ/s1600/SQLiteBrowser_Edit_Preference_Remote.PNG" /></a>
</li>
<li>查看→SQL Log</li>
<li>查看→Plot</li>
<li>查看→DB Schema</li>
<li>查看→Edit Database Cell</li>
<li>查看→Remote</li>
<li>查看→資料庫工具列</li>
<li>查看→Extra DB Toolbar</li>
<li>查看→Project Toolbar<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPJp_3fH0tTEIkHTSFHF_fiRPwDUmzncSVY38-01fHjT0FdHuqruYOQZ8xfYeYIvuTG0HtyOEYz7mvp3PW3PWvF68TN4JADycfODXv6gIb3mUypt-6WVCEIPs8sKZMFRnA0QxSuFdjoec/s1600/SQLiteBrowser_View.PNG" imageanchor="1"><img border="0" data-original-height="249" data-original-width="507" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPJp_3fH0tTEIkHTSFHF_fiRPwDUmzncSVY38-01fHjT0FdHuqruYOQZ8xfYeYIvuTG0HtyOEYz7mvp3PW3PWvF68TN4JADycfODXv6gIb3mUypt-6WVCEIPs8sKZMFRnA0QxSuFdjoec/s1600/SQLiteBrowser_View.PNG" /></a>
</li>
<li>Tools→Compact Database...</li>
<li>Tools→Load Extension...</li>
<li>Tools→Integrity...</li>
<li>Tools→Quick Integrity Check</li>
<li>Tools→Foreign-Key Check</li>
<li>Tools→Optimize<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghtZ03E60-v9x0m9SW0jXLjFxbOC-ZpTRaGcTQVgNGKTPBnc92rGxSKmetQBRkmeKi2Kmd1e54DElQJtZUsUmAMjeR6RjtVfJWlVA-Srx9rCVS8vxx9PbPA1ZOiFPv0hM9sqbYVZNowls/s1600/SQLiteBrowser_Tools.PNG" imageanchor="1"><img border="0" data-original-height="213" data-original-width="504" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghtZ03E60-v9x0m9SW0jXLjFxbOC-ZpTRaGcTQVgNGKTPBnc92rGxSKmetQBRkmeKi2Kmd1e54DElQJtZUsUmAMjeR6RjtVfJWlVA-Srx9rCVS8vxx9PbPA1ZOiFPv0hM9sqbYVZNowls/s1600/SQLiteBrowser_Tools.PNG" /></a>
</li>
<li>Help→What's This?</li>
<li>Help→Website</li>
<li>Help→Wiki</li>
<li>Help→Bug Report</li>
<li>Help→Feature Request...</li>
<li>Help→Nonate on Patreon...</li>
<li>Help→About<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVlG0ZwBC8UbxxNPF0jYPhHABmxKvb7ryF7FvW91m52Ig1j0YXZsY4_bpiqTQGrlCpGXtGBpWNGhDDPEzUofkve_-WDsZo7UQdM6pko2xWe29Iuo1P9kO3GwzHZS_zDycygPT2pG-5EpI/s1600/SQLiteBrowser_Help.PNG" imageanchor="1"><img border="0" data-original-height="246" data-original-width="505" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVlG0ZwBC8UbxxNPF0jYPhHABmxKvb7ryF7FvW91m52Ig1j0YXZsY4_bpiqTQGrlCpGXtGBpWNGhDDPEzUofkve_-WDsZo7UQdM6pko2xWe29Iuo1P9kO3GwzHZS_zDycygPT2pG-5EpI/s1600/SQLiteBrowser_Help.PNG" /></a>
</li>
</ul>
<br />Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0tag:blogger.com,1999:blog-8739989337344660455.post-35676907752757938012019-09-14T15:08:00.003+08:002022-06-17T17:21:36.256+08:00《OpenOffice / LibreOffice /Calc》在試算表貼上匯入文字,並組合成HTML TABLE格式的內容<p style="background-color:#FFFFCC;font-weight:bold;">《<a href="https://blog.bod.idv.tw/">OSS首頁</a>》《<a href="https://blog.bod.idv.tw/2011/02/hannibalopenoffice.html" >文章分類列表</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Base">Base</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Calc">Calc</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Draw">Draw</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Impress">Impress</a>》《<a href="https://blog.bod.idv.tw/search/label/OpenOffice%2FLibreOffice%20Writer">Writer</a>》</p>
以下將說明,要如何將命令提示字元內的格式化內容,複製貼到(匯入)Calc試算表中,並重新組合成HTML TABLE格式的內容。<br />
<ol>
<li>資料來源:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijxmw8DvmKZJOqn3judzVKG0BcQhozs33D1UufrDd-HZwoldr6b5jJNruOhK35x_7xAP366DXhOFu34G7OkT6ypTZLLkDXrh4T0TtSEtn2TtMEioAY809NcuQMKrU7rxwDMELuxaZqyYY/s1600/FormatText_source.png" imageanchor="1"><img border="0" data-original-height="274" data-original-width="641" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijxmw8DvmKZJOqn3judzVKG0BcQhozs33D1UufrDd-HZwoldr6b5jJNruOhK35x_7xAP366DXhOFu34G7OkT6ypTZLLkDXrh4T0TtSEtn2TtMEioAY809NcuQMKrU7rxwDMELuxaZqyYY/s1600/FormatText_source.png" /></a>
</li>
<li>目標:<br />如上圖,依照紅線的區隔類推,將文字內容轉換成HTML TABLE格式的內容。
</li>
<li>選取來源的文字內容,複製:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbKPxG7P5zlXbLX_q6DqBtOhkKVoA7EaXWPb2oZcXUrA1TQQv650X2sq8ngWG7B1ACVFmoPdMZUuStMJhlcYa8W94HJk3qvsdtWi6DBKNo-Vq9ykcrVmcp1ouPOs8dIC8-lnHoxJFfi0g/s1600/FormatText_selectCopy.png" imageanchor="1"><img border="0" data-original-height="313" data-original-width="642" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbKPxG7P5zlXbLX_q6DqBtOhkKVoA7EaXWPb2oZcXUrA1TQQv650X2sq8ngWG7B1ACVFmoPdMZUuStMJhlcYa8W94HJk3qvsdtWi6DBKNo-Vq9ykcrVmcp1ouPOs8dIC8-lnHoxJFfi0g/s1600/FormatText_selectCopy.png" /></a>
</li>
<li>開啟OpenOffice 或 LibreOffice Calc:編輯→貼上<br />分隔選項:固定的寬度(F),確認欄位自動分割OK,否則自行手動調整一下<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisUgZPwrdiyNiy2p2x7ZWxbDD33ZsdcQeJWS2Mg8ekPKwcMnKRMuM0SFv4X-vbACAHoRgTEweGq1FKIjbt4pgGk_953MSj9JOa5jpXMBYKyyzMTtZqEQIX1xMRgcLRyWeF40L6ZsnOAmA/s1600/FormatText_import.png" imageanchor="1"><img border="0" data-original-height="797" data-original-width="679" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisUgZPwrdiyNiy2p2x7ZWxbDD33ZsdcQeJWS2Mg8ekPKwcMnKRMuM0SFv4X-vbACAHoRgTEweGq1FKIjbt4pgGk_953MSj9JOa5jpXMBYKyyzMTtZqEQIX1xMRgcLRyWeF40L6ZsnOAmA/s1600/FormatText_import.png" /></a>
</li>
<li>資料匯入後<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6vGTiU4o8yankThkR6WmGuVRAcy6yKdzbslf-2blic0tfd0hHNSe4YVoaU_pTgSiHAbSDeqTR9gJABjoSyz6ba7Tag0_HuC2yd-5a7JXGyPot2Du-BrLgsPuuzaq1ttEnwlkfKfVAbB8/s1600/FormatText_imported.png" imageanchor="1"><img border="0" data-original-height="469" data-original-width="609" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6vGTiU4o8yankThkR6WmGuVRAcy6yKdzbslf-2blic0tfd0hHNSe4YVoaU_pTgSiHAbSDeqTR9gJABjoSyz6ba7Tag0_HuC2yd-5a7JXGyPot2Du-BrLgsPuuzaq1ttEnwlkfKfVAbB8/s1600/FormatText_imported.png" /></a>
</li>
<li>輸入HTML Tag<br />A1 : <table><br />A2..A16 : <tr><td><br />A17 : </table><br />C2..C16 : </td><td><br />E2..E16 : </td></tr><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhN4oTisW7nlQPLaOFTIr5XajX9Nmmctjj1PSZT-EWwSITowzyJPQxLmEyDwMdHVccbEeVDXjYO9yOK9JYJnSV6Aj8NEyhU2zKORULcfaTQGzyMzeCVgg0KgDv3-BvJz7cGoL_PYcyTlMg/s1600/FormatText_inputTags.png" imageanchor="1"><img border="0" data-original-height="470" data-original-width="739" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhN4oTisW7nlQPLaOFTIr5XajX9Nmmctjj1PSZT-EWwSITowzyJPQxLmEyDwMdHVccbEeVDXjYO9yOK9JYJnSV6Aj8NEyhU2zKORULcfaTQGzyMzeCVgg0KgDv3-BvJz7cGoL_PYcyTlMg/s1600/FormatText_inputTags.png" /></a></li>
<li>組合字串,取用組合結果<br />F1=CONCATENATE(A1;B1;C1;D1;E1) 並複製公式到 F2..F17<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRCrxqFahZWPKMnBhTVJfs2_ceodZWAytCG2oRrRf_39rN5_cW8rKqzHBn3cI46eoKvc36WQGKC-mqG78QypbBShefdIolXk8PbfHh0dzKvpc5DAowUazkI9d50Zs5iewy7JG-YjVofj0/s1600/FormatText_concatenate.png" imageanchor="1"><img border="0" data-original-height="470" data-original-width="761" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRCrxqFahZWPKMnBhTVJfs2_ceodZWAytCG2oRrRf_39rN5_cW8rKqzHBn3cI46eoKvc36WQGKC-mqG78QypbBShefdIolXk8PbfHh0dzKvpc5DAowUazkI9d50Zs5iewy7JG-YjVofj0/s1600/FormatText_concatenate.png" /></a>
</li>
<li>複製 F2..F17 的內容,貼到需使用HTML格式內容的地方。</li>
</ol>
Hannibalhttp://www.blogger.com/profile/05319952819155728398noreply@blogger.com0