PowerPoint 簡報

Size: px

Start display at page:

Download "PowerPoint 簡報"

岱俞
5 years ago
Views:

1 巨量資料與統計分析政治大學統計系余清祥 2017 年 10 月 3 日第三週 :SQL 和 R 軟體介紹 1

2 SQL 介紹使用方法 2

3 Agenda 認識 SQL 與資料庫 Database Basic, Data Input SQL 的基本指令實例操作

4 CH1 認識 SQL 與資料庫

5 1.1 SQL 簡介結構化查詢語言 (Structured Query Language), 簡稱 SQL, 為專門用於關聯式資料庫的一種查詢語言可用來定義資料庫結構建立表格指定欄位型態與長度 ; 也能新增異動或查詢資料統計分析軟體結合 SQL 的程式能力為必備技能

6 1.2 資料庫簡介資料庫 (Data Base) 為一存放大量資料的地方, 由各式各樣的資料匯集而成資料庫管理系統 (Database Management System) 提供使用者一個環境, 使其能有效率且方便地對資料庫進行管理透過 SQL 語法, 順利達到資料庫之間的溝通與管理資料

7 CH2 Database Basic & Input

8 2.1 Database Basic 使用資料庫一開始, 需在電腦內建立一個資料夾來作為存放 SQL 之 Database 的檔案路徑如下圖, 先在 D 槽, 新增一個資料夾 SQL SYSTEM DATABASE

9 2.1 Database Basic 開啟 SQL Server Management Studio, 並點選連結, 接著點選新增查詢, 就會出現可輸入程式碼的工作區, 如下圖 :

10 2.1 Database Basic 在工作區, 輸入下列程式碼, 並按執行, 以建立資料庫程式碼 : CREATE DATABASE 教學資料庫 ON(NAME = 教學資料庫, FILENAME= 'D:\SQL SYSTEM DATABASE\ 教學資料庫.MDF')

11 2.1 Database Basic 執行完成後, 在左邊物件總管上方, 先按重新整理, 再點開資料庫, 就會看到剛才所建立的教學資料庫

12 2.2 Data Input 首先, 將 Excel 檔匯入資料庫成資料表 Step1 : 在欲加入資料表之資料庫按右鍵工作匯入資料

13 2.2 Data Input Step2 : 選取資料來源設定檔案路徑和版本瀏覽選取要匯入的檔案

14 2.2 Data Input Step3 : 將框線處 ( 工作表 1$) 改成欲在 SQL 內顯示之資料表名稱 (table1) 完成

15 2.2 Data Input Step4 : 執行完成後, 在左邊物件總管上方, 先按重新整理, 再點開教學資料庫, 就會看到剛才所匯入的資料表在資料表按右鍵, 點選選取前 1000 個資料列 (W), 就可以看到資料輸入後的型態

16 2.2 Data Input NOTE: 若匯入 EXCEL 檔有問題, 請至以下網站下載 : 2007 Office system 驅動程式 : 資料連線元件

17 2.2 Data Input 練習 : 在 Excel 輸入以下五個資料表, 並匯入 SQL Server A : icd9 c1 c B1 : ID SEX AGE Disease A F 1 A5 B F 2 B6 C M 3 C7

18 3 103 c 2.2 Data Input B2 : B3 : ID SEX AGE Disease A F 1 A4 D M 2 D5 E M 5 E4 ID SEX AGE Disease C M 3 C7 F F 4 F2 G M 6 G3 CD : ID icd9 col a b

19 CH3 SQL 的基本指令

20 3.1 Learning Concept SQL 的資料欄位, 主要分為數值欄位字串欄位兩大類數值欄位 : int: 整數, 範圍從 -2^31~ 2^31 bigint: 整數, 範圍從 -2^63 ~ 2^63-1 float: 近似小數資料的資料類型, 範圍從 -1.79E+308~1.79E+308 real: 近似小數資料的資料類型, 範圍從 -3.04E+38~3.04E+38

21 3.1 Learning Concept 字串欄位 : char: 固定大小浪費空間, 所需計算時間少, 只能儲存英文字元 varchar: 不固定長度, 必須要花費較多的 CPU 計算時間, 只能儲存英文字元 nchar nvarchar: 與前兩者的差異, 可儲存其他字元 ( 中文 ) NOTE: 數值欄位常用 float, 字串欄位則是 nvarchar 若欄位名稱是由中文與數字組成, 則在指令中需加上 [ ] (ex.[ 資料行 0])

22 3.2 Basic Command SQL 基本符號介紹 : *:ALL ' ': 中間輸入特定值, 主要是用於加入字串 --: 後面可輸入註解, 不會被程式語言讀入 /* */: 中間可輸入註解, 不會被程式語言讀入

23 3.2 Basic Command SQL 基本語法 : SELECT 欄位名稱 INTO 表名 FROM 表名 WHERE 條件敘述 GROUP BY 欄位名稱 ORDER BY 欄位名稱

24 3.2 Basic Command EX1 : 將 CD 表中,col1 欄位是 a 的所有欄位, 挑出並存到新的資料表 CD1 中 EX2 : 將 CD 表中, 欄位順序調整, 並存到新的資料表 CD2 中有問題都可以互相討論噢!

25 3.2 Basic Command <Ans1> : SELECT * /* 挑出所有欄位的資料 */ INTO CD1 /* 存到新的資料表 CD1 中 */FROM CD /* 從 CD 表抓資料 */ WHERE col1 = 'a' /* 選取資料的條件為 : col1 欄位是 a */

26 3.2 Basic Command <Ans2> : select col1,icd9,id /* 依序挑出 col1,icd9,id 欄位的資料 */ into CD2 /* 存到新的資料表 CD1 中 */ from CD /* 從 CD 表抓資料 */

27 3.2 Basic Command 資料表合併 : 將 B1 與 B2 合併成 B

28 3.2 Basic Command Code: SELECT * /* 挑出所有欄位的資料 */ INTO B /* 存到新的資料表 B */ FROM B1 /* 從 B1 表抓資料 */ UNION ALL SELECT * FROM B2 /* 再從 B2 表抓資料 */

29 3.2 Basic Command EX3: 將 B1 與 B3 合併成 B4 B1: B4: B3:

30 3.2 Basic Command <Ans3>: SELECT * /* 挑出所有欄位的資料 */ INTO B4 /* 存到新的資料表 B4 */ FROM B1 /* 從 B1 表抓資料 */ UNION SELECT * FROM B3 /* 再從 B3 表抓資料 */

31 3.2 Basic Command 修改欄位屬性 Step1: 先在要修改欄位所屬的資料表按右鍵, 點選設計 (G), 可以看到目前個欄位的資料類型

32 3.2 Basic Command Step2: 現在想把 AGE 的欄位格式, 從 float 轉換成 nvarchar(15) Code: ALTER TABLE B /* 欲修改的欄位在 B 表中 */ ALTER COLUMN AGE nvarchar(15) /* 將 AGE 欄位格式, 從原本的 float 轉換成 nvarchar(15)*/

33 3.2 Basic Command 修改欄位名稱 : 將 Disease 欄位名稱改為 ID_Disease Code: SP_RENAME 'B.Disease', 'ID_Disease', 'COLUMN' /* 將 B 表中之 Disease 欄位名稱修改成 ID_Disease */

3.2 Basic Command 3.2.6 新增欄位 : 新增出生年份欄位 Code: ALTER TABLE B

34 3.2 Basic Command 新增欄位 : 新增出生年份欄位 Code: ALTER TABLE B ADD 出生年份 NVARCHAR(15) /* 將 B 表加入 ' 出生年份 ', 欄位格式為 NVARCHAR(15) */

3.2 Basic Command 3.2.7 刪除欄位 : 刪除出生年份欄位 Code:

35 3.2 Basic Command 刪除欄位 : 刪除出生年份欄位 Code: ALTER TABLE B DROP COLUMN 出生年份 /* 將 B 表中出生年份欄位刪除 */

36 3.2 Basic Command 填入欄位 Step1: 在 B 表中新增欄位 ID1 Code: ALTER TABLE B ADD ID1 NVARCHAR(15) /* 在 B 表中新增 ID1 欄位 */

37 3.2 Basic Command Step2: 將 ID_Disease 欄位的第一個英文字母, 填入 ID1 Code: UPDATE B /* 欲填入的欄位, 位於資料表 B 中 */ SET ID1 = SUBSTRING (ID_Disease,1,1) /* 將 ID_Disease 欄位的第一個英文字母, 填入 ID1*/

38 3.2 Basic Command NOTE:SUBSTRING(str,pos,len), 是由 <str> 中的第 <pos> 位置開始, 選出接下去的 <len> 個字元 EX4: 在 B4 表中新增 ID_SEX 欄位, 並將 ID 與 SEX 合併填入其中

39 3.2 Basic Command <Ans4>: Step1: ALTER TABLE B4 ADD ID_SEX NVARCHAR(15) Step2: UPDATE B4 SET ID_SEX = ID+SEX

40 3.2 Basic Command 進階條件指令 (WHERE IN): 從 B 表中抓出 ID 有出現在 B2 表中的資料 (A D E)

41 3.2 Basic Command Code: select * /* 挑出所有欄位的資料 */ into where_in /* 存到新的資料表 where_in 中 */ from B /* 從 B 表抓資料 */ where ID IN (SELECT ID FROM B2) /* 條件為 ID 有出現在 B2 表中 (A D E) 資料 */

42 3.2 Basic Command 進階條件指令 (LEFT JOIN): 以表 CD 為主, 去表 A 中擷取在表 CD 中沒有的資料欄位, 並用 icd9 去連結兩表

43 3.2 Basic Command Code: select CD.ID, 教學資料庫.dbo.CD.icd9,col1,A.c1,c2 /* 表 CD 挑出 ID,icd9,col1 欄位的資料 ; 表 A 挑出 C1,C2 欄位的資料 */ into Left_join /* 存到新的資料表 Left_join 中 */ from CD LEFT JOIN A /* 以表 CD 為主, 去表 A 中擷取在表 CD 中沒有的資料欄位 */ on CD.icd9 = A.icd9 /* 用欄位 icd9 去連結兩個表 */

44 CH4 實例操作

45 4.1 表單合併 Question: 如何利用第二章的指令, 將左邊兩個表單, 整理成右邊的表單?

46 4.1 表單合併 <Answer2>: Step1: 手動匯入 table9 與 table10 Step2: 將 table9 與 table10 合併成 table11 SELECT * INTO table11 FROM table9 UNION ALL SELECT * FROM table10

47 4.1 表單合併 Step3: 將 table11, 依據 ID, 求出 Stat 與 Doe 的總和, 存入 table12 SELECT ID, Class, Region, SUM(Stat) as Stat, SUM(Doe) as Doe into table12 FROM table11 GROUP BY ID, Class, Region Step4: 於 table12 新增欄位 ID_Region, 並填入 ID 加上 Region a. ALTER TABLE table12 ADD ID_Region nvarchar(15) b. UPDATE table12 SET ID_Region = ID+Region

48 4.1 表單合併 Step5: 於 table12 刪除欄位 Region ALTER TABLE table12 DROP COLUMN Region Step6: 重新排列 table12 的欄位, 依序為 ID, Class, ID_Region, Stat, Doe, 並存入 table13 SELECT ID, Class, ID_Region, Stat, Doe INTO table13 FROM table12

49 4.1 表單合併 Step7: 新增欄位 Total, 並填入 Stat 與 Doe 的加總 a. ALTER TABLE table13 ADD Total float b. UPDATE table13 SET Total = Stat+Doe

50 Thanks for your listening!

51 R 語言與文字採礦 51

52 R 語言簡介 52 R 語言自 1993 年問世, 用於統計分析繪圖資料採礦矩陣運算與機器學習等多個面向兩大特色 : 免費下載開放原始碼套件 :ggmap, ggplot R Studio/R Pubs/GitHub

53 53 安裝軟體請到 R 軟體的網站, 點左側 Download: CRAN 之後, 搜尋 Taiwan 並任選一個下載點 ( 臺灣大學或元智大學 ), 再選擇作業系統 Download R for Windows ( 以 Windows 為例 ), 點選 base 即可下載安裝

54 進入使用者介面 54 進入圖形使用者介面程式進行簡單的程式練習

55 55 讀取文字資料利用 readline() 語法讀取文字檔

56 56 刪除標點符號與數字安裝套件 tm 或 tmcn 後, 輸入 library () 語法載入套件, 即可使用 removepunctuation() removenumbers() 等語法依序將標點符號數字移除

57 57 將各句連結成一整個不分行的段落

58 58 中文斷詞

59 59 雙字詞出現次數統計

60 60 安裝套件 wordcloud 繪製文字雲

投影片 1

投影片 1 資料庫管理程式 ( 補充教材 -Part2) 使用 ADO.NET 連結資料庫 ( 自行撰寫程式碼以實現新增刪除修改等功能 ) Private Sub InsertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertButton.Click ' 宣告相關的 Connection