資料庫 - 生活Hsin空間

code

<img loading="lazy" width="768" height="405" data-src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image lazyload" alt="code" decoding="async" data-srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" data-sizes="(max-width: 768px) 100vw, 768px" src="data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==" style="--smush-placeholder-width: 768px; --smush-placeholder-aspect-ratio: 768/405;" /><noscript><img loading="lazy" width="768" height="405" src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image" alt="code" decoding="async" srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" sizes="(max-width: 768px) 100vw, 768px" />

IT好技術 | Python

Python 如何使用 pandas+sqlalchemy 來操作資料庫！三分鐘讓你知道！

ByHsin 15 12 月, 202215 12 月, 2022

平常寫了不少東西去各地方抓資料，抓來的資料總要找地放存

所以就找了跟資料庫連線的模組sqlalchemy，看起來Python很多使用者都用這套

稍微整理了一下基本使用的方法讓大家知道，接下來就來看看怎麼使用sqlalchemy吧

安裝sqlalchemy
pip install SQLAlchemy
連線方式

code

<img loading="lazy" width="768" height="405" data-src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image lazyload" alt="code" decoding="async" data-srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" data-sizes="(max-width: 768px) 100vw, 768px" src="data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==" style="--smush-placeholder-width: 768px; --smush-placeholder-aspect-ratio: 768/405;" /><noscript><img loading="lazy" width="768" height="405" src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image" alt="code" decoding="async" srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" sizes="(max-width: 768px) 100vw, 768px" />

Java | 資料庫

資料庫 – SQL效能優化LIKE模糊查詢

ByHsin 14 2 月, 202214 2 月, 2022

我們在寫sql的時候應該儘量避免在一個複雜查詢裡面使用 LIKE ‘%XXX%’

由於XXX前面用到了“%”,因此該查詢必然走全表掃描,導致相關列的索引無法使用，除非必要,否則不要在關鍵詞前加%

如果後臺邏輯必須使用’%XXX%’，可以考慮以下解決辦法

code

<img loading="lazy" width="768" height="405" data-src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image lazyload" alt="code" decoding="async" data-srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" data-sizes="(max-width: 768px) 100vw, 768px" src="data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==" style="--smush-placeholder-width: 768px; --smush-placeholder-aspect-ratio: 768/405;" /><noscript><img loading="lazy" width="768" height="405" src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image" alt="code" decoding="async" srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" sizes="(max-width: 768px) 100vw, 768px" />

資料庫

資料庫 – 用 SQL 去除重複資料 : Distint

ByHsin 14 2 月, 202214 2 月, 2022

這邊剛大家分享一個，在工作中碰到常用的SQL，如何去除重複的資料

答案就是 Distint

code

<img width="768" height="405" src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image" alt="code" decoding="async" loading="lazy" srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" sizes="auto, (max-width: 768px) 100vw, 768px" />

IT好技術 | Java

java – 查詢速度很慢！IN 改為 JOIN/EXIST 效能衝上天！

ByHsin 20 1 月, 202220 1 月, 2022

查詢速度很慢！IN 改為 JOIN/EXIST 效能衝上天！

最近遇到查詢很慢的問題

網路上有大量的資料說將 IN 改成 JOIN 或者 exist，然後修改完成之後確實變快了

就來看看要怎麼做！

code

<img width="768" height="405" src="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?fit=768%2C405&ssl=1" class="attachment-medium_large size-medium_large wp-post-image" alt="code" decoding="async" loading="lazy" srcset="https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?w=1280&ssl=1 1280w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=300%2C158&ssl=1 300w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=1024%2C540&ssl=1 1024w, https://i0.wp.com/hsin-life.com/wp-content/uploads/2021/07/da18cf6e-a674-4d0c-9357-c02f3a814d02.png?resize=768%2C405&ssl=1 768w" sizes="auto, (max-width: 768px) 100vw, 768px" />

Java | 自我成長

資料庫SQL – 使用Delete、Truncate Table與Drop Table的差異

ByHsin 25 7 月, 202117 1 月, 2022

相信大部分的開發人員一定都會碰到資料庫SQL

在開發階段常常要測試，我們需要刪除整個資料表內的資料，

有三種方法可以參考使用，分別是使用Delete、Trancate與Drop，使這三種方式的差異在哪裡呢?

使用Delete語法，不加任何Where條件下，是可以移除整個Table的資料，但是卻會耗用大量資源