齊來學習系列#3 齊來學習在EXCEL使用SteemSQL | Learning Use SteemSQL in EXCEL TogethersteemCreated with Sketch.

in #cn4 years ago (edited)

SQL用途廣泛,可能大家工作上亦有應用到,大家可以係Microsoft EXCEL, ACCESS 以SQL連接各資料庫,再進一步可以用ORACEL產品SQL Developer或 @oflyhigh 哥 介紹過的pymssql,詳情可看第一次使用STEEMSQL查询谷哥点名数据。 今天先談怎樣在EXCEL連接STEEMSQL和一些基本SQL語法,希望大家有機會可應用在工作/功課上。

There are widely use for SQL in daily work, we can use Microsoft EXCEL, ACCESS to connect database through SQL, we also can use ORACLE SQL developer or pymssql that introduced by @oflyhigh before, the detail we can see 第一次使用STEEMSQL查询谷哥点名数据. Today, i want to introduce how to use SQL in EXCEL and some basic SQL statement. 

步驟1: 首先大家在EXCEL上按「資料」

Step 1: We can click the 「Data」button.

步驟2: 按「從其他來源」,再按「從Microsoft Query」.

Step 2: Then click the 「From Other Sources」, and 「From Microsoft Query」

步驟3: 按「新資料來源」,再按「確定」

Step 3: Click 「Data Source」.

步驟4: 先輸入「資料來源名稱」,我就以「DBSteem」作名稱,再選擇資料庫驅動程式類型,選擇「SQL Server」

Step 4: Click「Data Source Name」and Type 「DBSteem」, select 「SQL Server」.

步驟5: 再按「連接」

Step 5: Click 「Conncet」.

步驟6: 輸入伺務器,登入識別碼,密碼,輸入後按確定,不用選擇預設表格
伺務器: sql.steemsql.com
登入識別碼: steemit
密碼: steemit

Step 6: Enter Server , User, Password and then dont choose the default spreadsheet.
Server: sql.steemsql.com
User: steemit
Password: steemit

步驟7: 大家可看到剛建立的資料來源,選擇「DBSteem」,再按確定

Step 7: We can see the data source「DBSteem」and click enter.

步驟8: 大家可看到很多資料包,常用的有
Accounts
Comments
我們先選Comments

Step 8: We can see many tables, in general, we always use
Accounts
Comments
Now we choose Comments table

步驟9: 其後我們不作出篩選,按「下一步」,不作出排序,再按「下一步」,選擇「在Microsoft Query 中編輯和查詢和檢視」

Step 9: We dont choose filter and sorting function and choose 「Microsoft Query」.

步驟10: 先按再上角「SQL」的按鈕,在彈出的空格打入想要的SQL句子,再按確定

Step 10: We click the 「SQL」button on the left top hand-side and type the SQL statement.

步驟11: 打入SQL 句子,現在想尋找「齊來學習系列」,所以尋找「Learning」,下面會講述一些SQL簡單語句
SELECT Comments.title
FROM DBSteem.dbo.Comments Comments
WHERE author='ryanfan11' AND title like '%Learning %'

Step 11: Now we type the 「Learning」in the where condition in SQL statement and I will explain as below.

步驟12: 結果出來了
齊來學習系列#1 齊來學密碼管理器 : LassPass| Learning Password Manager Together: LassPass
齊來學習系列#2 齊來學WWW | Learning World-Wide-Web Together
如想匯出EXCEL,按左上「將資料傳回EXCEL」的按鈕

Step 12: The result is shown.

步驟13: 選擇「表格」,再按確定

Step 13: Choose 「spreadsheet」and enter.

查詢結果便會匯出EXCEL

The result is exported to EXCEL.
現在簡單講述一下SQL的組成部分 SELECT: 選擇你想要的欄位(Column) FROM : 由那一個表抽取資料 WHERE: 條件 Like: 依據一個模式 (pattern) 上面SELECT Comments.title FROM DBSteem.dbo.Comments Comments WHERE author='ryanfan11' AND title like '%Learning %' 代表由Comments表中選擇title 這欄位,並只選擇作者是ryanfan11和title 內有Learning的字眼 
Now, i will explain the SQL statement SELECT: Choose the column you want FROM : Choose the retrieved data table WHERE: Condition Like: Pattern Form the SQL statement, SELECT Comments.title FROM DBSteem.dbo.Comments Comments WHERE author='ryanfan11' AND title like '%Learning %' represent we want choose column "title "in table comments and we want only choose the author is "ryanfan11" and the title contain "Learning"
希望大家睇完今次文章,可以知道如何在EXCEL中使用SQL去查詢,包括查詢自己在STEEM的資料。
After reading the post, hope you guys can know how to use SQL to connect database in EXCEL just like read the STEEM data in databases.
PS: 特別感谢 @arcange 提供 SteemSQL服务令大家可查詢STEEM數據庫。
Special thanks for @arcange provide the SteemSQL service for us to retrieve STEEM data .


謝謝收看。 有興趣可跟隨,留言和投我一票@ryanfan11 , 會定期推齊來學習系列。 Thanks for reading, I will keep sharing Learning Together series. If you like it, Please Upvote, Resteem & Follow me @ryanfan11

過往的齊來學習系列
齊來學習系列#1 齊來學密碼管理器
齊來學習系列#2 齊來學WWW 

Sort:  

Congratulations @ryanfan11! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

Award for the number of upvotes

Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click here

If you no longer want to receive notifications, reply to this comment with the word STOP

By upvoting this notification, you can help all Steemit users. Learn how here!