2024 iThome 鐵人賽

DAY 16

Security

【Google資安證書課程重點整理】系列第 16 篇

【Google資安證書重點整理】Day 16_SQL資安相關指令_基礎教學

16th鐵人賽 sql

greenmaple

2024-09-17 13:51:17

132 瀏覽

分享至

導讀

本節內容

了解 SQL 及其在數據分析中的應用
學習關係資料庫及其結構
探索 SQL 查詢及使用方法
介紹 SQL 過濾器
探討 SQL 連接
資料庫基礎
- 資料庫是有組織的資訊集合，通常比電子表格更適合儲存大量資料
- 資料庫支持多人同時訪問和複雜的存取任務
關係資料庫
- 包含多個表格，表格之間可以通過鍵連接
- 主鍵：唯一標識每一行的列
- 外鍵：連接兩個表格的列，可以有空值和重複項
SQL 的作用
- SQL 是用於創建、互動和請求資訊的程式語言
- 查詢是對資料庫中資料的請求
- SQL 在安全分析中非常重要，用於檢索和分析數據
使用 SQL 的實務經驗
- 檢索日誌中的數據，識別配置不正確的機器或異常模式
- 支援安全相關決策，如識別未更新的機器或最佳更新時間

查詢資料庫 (Query a Database)

在從資料庫中檢索信息時，使用 SELECT、FROM 和 ORDER BY 關鍵字。

`FROM`

指定要查詢的表格；執行查詢時必需。

FROM employees
指示查詢 employees 表格。

`ORDER BY`

根據指定的列或多列對查詢結果進行排序。

ORDER BY department
按 department 列的升序排序結果。
ORDER BY department ASC 也會按 department 列的升序排序結果。
ORDER BY city DESC
按 city 列的降序排序結果。
ORDER BY country, city
首先按 country 列升序排序，如果 country 列的值相同，則進一步按 city 列升序排序。

`SELECT`

指定要返回的列；執行查詢時必需。

SELECT employee_id
返回 employee_id 列的數據。
SELECT *
返回表格中的所有列。

應用過濾器於 SQL 查詢 (Apply Filters to SQL Queries)

WHERE 及其他 SQL 關鍵字和字符用於應用過濾器於 SQL 查詢中。

`AND`

指定過濾器中必須同時滿足兩個條件。

WHERE region = 5 AND country = 'USA'
返回 region 列值為 5 且 country 列值為 'USA' 的所有記錄。

`BETWEEN`

過濾數字或日期在範圍內；BETWEEN 後跟隨範圍的第一個值、AND 運算符以及範圍的最後一個值。

WHERE hiredate BETWEEN '2002-01-01' AND '2003-01-01'
返回 hiredate 列值在 '2002-01-01' 和 '2003-01-01' 之間的所有記錄。

`=` (等於)

用於過濾器中僅返回指定列中等於某一特定值的記錄。

WHERE birthdate = '1980-05-15'
返回 birthdate 列值等於 '1980-05-15' 的所有記錄。

`>` (大於)

用於過濾器中僅返回指定列中大於某一特定值的記錄。

WHERE birthdate > '1970-01-01'
返回 birthdate 列值大於 '1970-01-01' 的所有記錄。

`>=` (大於或等於)

用於過濾器中僅返回指定列中大於或等於某一特定值的記錄。

WHERE birthdate >= '1965-06-30'
返回 birthdate 列值大於或等於 '1965-06-30' 的所有記錄。

`<` (小於)

用於過濾器中僅返回指定列中小於某一特定值的記錄。

WHERE date < '2023-01-31'
返回 date 列值小於 '2023-01-31' 的所有記錄。

`<=` (小於或等於)

用於過濾器中僅返回指定列中小於或等於某一特定值的記錄。

WHERE date <= '2020-12-31'
返回 date 列值小於或等於 '2020-12-31' 的所有記錄。

`LIKE`

與 WHERE 一起使用，搜索列中的模式。

WHERE title LIKE 'IT%'
返回 title 列值匹配 'IT%' 模式的所有記錄。
WHERE state LIKE 'N_'
返回 state 列值匹配 'N_' 模式的所有記錄。

`NOT`

否定條件。

WHERE NOT country = 'Mexico'
返回 country 列值不為 'Mexico' 的所有記錄。

`<>` (不等於)

用於過濾器中僅返回指定列中不等於某一特定值的記錄；!= 也用作不等於的運算符。

WHERE date <> '2023-02-28'
返回 date 列值不等於 '2023-02-28' 的所有記錄。

`!=` (不等於)

用於過濾器中僅返回指定列中不等於某一特定值的記錄；<> 也用作不等於的運算符。

WHERE date != '2023-05-14'
返回 date 列值不等於 '2023-05-14' 的所有記錄。

`OR`

指定過濾器中任一條件可滿足。

WHERE country = 'Canada' OR country = 'USA'
返回 country 列值為 'Canada' 或 'USA' 的所有記錄。

`%` (百分號)

替代任意數量的字符；用作 LIKE 模式中的通配符。

'a%'
表示模式由字母 'a' 開頭，後面跟隨零個或多個字符。
'%a'
表示模式由零個或多個字符開頭，最後是字母 'a'。
'%a%'
表示模式由字母 'a' 圍繞零個或多個字符。

`_` (下劃線)

替代一個字符；用作 LIKE 模式中的通配符。

'a_'
表示模式由字母 'a' 後跟一個字符。
'a__'
表示模式由字母 'a' 後跟兩個字符。
'_a'
表示模式由一個字符後跟字母 'a'。
'_a_'
表示模式由字母 'a' 圍繞一個字符。

`WHERE`

指定過濾器的條件；必須用於開始過濾器。

WHERE title = 'IT Staff'
返回 title 列值包含 'IT Staff' 的所有記錄；WHERE 用於條件 title = 'IT Staff' 前，來創建過濾器。

連接表格 (Join Tables)

以下 SQL 關鍵字用於連接表格。

`FULL OUTER JOIN`

返回兩個表格中的所有記錄；指定用於連接表格的列，語法包括 ON 和等於 (=)。

SELECT * FROM employees FULL OUTER JOIN machines ON employees.device_id = machines.device_id;
返回 employees 表格和 machines 表格中的所有記錄；使用 device_id 列來連接這兩個表格。

`INNER JOIN`

返回在多個表格中指定列匹配的記錄；指定用於連接表格的列，語法包括 ON 和等於 (=)。

SELECT * FROM employees INNER JOIN machines ON employees.device_id = machines.device_id;
返回 employees 表格中 device_id 列值與 machines 表格中的 device_id 列值匹配的所有記錄。

`LEFT JOIN`

返回第一個表格中的所有記錄，但僅返回第二個表格中匹配指定列的記錄；第一個（或左）表格出現在 FROM 關鍵字後面；指定用於連接表格的列，語法包括 ON 和等於 (=)。

SELECT * FROM employees LEFT JOIN machines ON employees.device_id = machines.device_id;
返回 employees 表格中的所有記錄，但僅返回 machines 表格中 device_id 列值與 employees 表格中的 device_id 列值匹配的記錄。

`RIGHT JOIN`

返回第二個表格中的所有記錄，但僅返回第一個表格中匹配指定列的記錄；第二個（或右）表格出現在 RIGHT JOIN 關鍵字後面；指定用於連接表格的列，語法包括 ON 和等於 (=)。

SELECT * FROM employees RIGHT JOIN machines ON employees.device_id = machines.device_id;
返回 machines 表格中的所有記錄，但僅返回 employees 表格中 device_id 列值與 machines 表格中的 device_id 列值匹配的記錄。

執行計算 (Perform Calculations)

以下 SQL 關鍵字是聚合函數，對執行計算非常有幫助。

`<mark>AVG`

返回一個數字，表示列中數值的平均值；放在 SELECT 後面。

SELECT AVG(height)
返回 height 列的所有記錄的平均高度。

`COUNT`

返回一個數字，表示從查詢中返回的記錄數；放在 SELECT 後面。

SELECT COUNT(firstname)
返回 firstname 列中有值的記錄數。

`SUM`

返回一個數字，表示列中數值的總和；放在 SELECT 後面。

SELECT SUM(cost)
返回 cost 列的所有記錄的總成本。

於課程中的SQL範例

這一部份就是課程裏的實際上線操作的範例，主要的概念就是透過先前收集到的各類Log，將其關聯經由SQL作查詢，將資料化為有參考價值的資訊，供資安分析師作威脅及漏洞的偵測與防範。

任務 1: 檢索員工設備數據

目標: 獲取有關員工設備的資訊以進行更新。資訊位於資料庫 machines 的表格 organization 中。

檢索所有設備資訊

指令:
```
SELECT * FROM machines;
```
說明: 使用星號 * 來選擇 machines 表中的所有資料。請注意，MySQL 中的表名區分大小寫。

檢索設備 ID 和電子郵件用戶端

指令:

SELECT device_id, email_client FROM machines;

說明: 選擇 device_id 和 email_client 列。

檢索作業系統及其最後修補日期

指令:

SELECT device_id, operating_system, OS_patch_date FROM machines;

說明: 選擇 device_id、operating_system 和 OS_patch_date 列。

任務 2: 調查登入活動

目標: 分析 log_in_attempts 表中的資訊，以確定是否發生任何異常活動。

調查登入嘗試的地區

指令:

SELECT event_id, country FROM log_in_attempts;

問題: 是否有來自澳洲的登入嘗試？
- 選擇: 不 或 是的

檢查是否在組織工作時間之外進行了登入嘗試

指令:

SELECT username, login_date, login_time FROM log_in_attempts;

問題: 第五行回傳的使用者名稱是什麼？
- 選擇: 姆拉赫、德科特、阿帕特爾、傑拉斐爾

全面了解所有登入嘗試

指令:
```
SELECT * FROM log_in_attempts;
```
說明: 使用單一符號 * 來選擇 log_in_attempts 表中的所有列。

檢查進度:

按一下「檢查我的進度」以驗證任務是否完成。

任務 3: 訂購登入嘗試數據

目標: 使用 ORDER BY 關鍵字根據登入日期和時間對資料進行排序。

按日期排序資訊

指令:

SELECT *  
FROM log_in_attempts 
ORDER BY login_date;

問題: 傳回的第一筆記錄的使用者名稱和登入日期是多少？
- 選擇:
  - 斯巴利甚 於 2022-05-10
  - 達奎諾 於 2022-05-08
  - 馬巴迪 於 2022-05-10
  - 伊維拉斯科 於 2022-05-08

按日期和時間進一步排序資訊

指令:

SELECT *  
FROM log_in_attempts 
ORDER BY login_date, X;

替換 X 為適當的列名稱，例如 login_time。

問題: 傳回的第一筆記錄的使用者名稱和登入時間是多少？
- 選擇:
  - 賈弗里 於 00:15:55
  - 蓋斯帕札 於 00:40:00
  - bsand 於 00:19:11
  - 洗 於 00:36:12

任務4 : 列出所有組織機器

目標: 取得所有組織電腦及其作業系統的清單。

執行查詢以檢索設備 ID 和作業系統

指令:

SELECT device_id, operating_system
FROM machines;

問題: 從 machines 表回傳了多少行？
- 選擇:
  - 100
  - 200
  - 250
  - 300

檢查進度:

按一下「檢查我的進度」以驗證任務是否完成。

任務 5: 檢索作業系統 2 的電腦列表

目標: 取得所有使用作業系統 'OS 2' 的電腦列表。

執行查詢以過濾作業系統為 'OS 2' 的記錄

指令:

SELECT device_id, operating_system
FROM machines
WHERE operating_system = 'OS 2';

說明: WHERE 子句用於過濾查詢結果，只傳回符合條件的記錄。
問題: 資料庫中有多少台機器使用作業系統 'OS 2'？
- 選擇:
  - 88
  - 44
  - 80
  - 200

檢查進度:

按一下「檢查我的進度」以驗證任務是否完成。

任務 6: 列出特定部門的員工

目標: 檢索財務和銷售部門的所有員工的辦公室號碼。

檢索財務部門的員工

指令:

SELECT *  
FROM employees  
WHERE department = 'Finance';

說明: WHERE 子句用於過濾查詢結果，僅返回部門為 'Finance' 的員工。
問題: 傳回的第一行的 employee_id 是多少？
- 選擇:
  - 1049
  - 1003
  - 1001
  - 1119

修改查詢以檢索銷售部門的員工

指令:

SELECT *  
FROM employees  
WHERE department = 'Sales';

問題: 銷售部門有多少名員工？
- 選擇:
  - 33
  - 17
  - 10
  - 42

檢查進度:

按一下「檢查我的進度」以驗證任務是否完成。

任務 7: 辨識員工機器

目標: 確定使用南樓機器的員工，以便發送警報。

辨識使用特定機器的員工

指令:

SELECT employee_name
FROM employees
WHERE office = 'South-109';

說明: 該查詢從 employees 表中選擇使用辦公室 South-109 的員工名稱。
問題: 下列哪位員工使用有問題的計算機？
- 選擇:
  - 雪諾
  - 傑希爾
  - 吉蘭斯基
  - 尼米切爾

修改查詢以檢索南樓所有機器的員工

指令:

SELECT employee_name
FROM employees
WHERE office LIKE 'South%';

說明: 使用 LIKE 運算子和 % 通配符來匹配所有以 South 開頭的辦公室名稱。這樣可以找到所有南樓的機器相關員工。
問題: 南樓列出的第一位員工屬於哪個部門？
- 選擇:
  - 資訊科技
  - 銷售
  - 行銷
  - 金融

檢查進度:

按一下「檢查我的進度」以驗證任務是否完成。

任務 8: 檢索特定日期之後的登入嘗試

目標: 收集特定日期之後的登入嘗試資訊。

檢索特定日期之後的登入嘗試

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_date > '2022-05-09';

說明: 使用 > 運算子來選擇 2022-05-09 之後的登入嘗試。
問題: 2022 年 5 月 9 日之後嘗試登入的次數有多少？
- 選擇:
  - 134
  - 185
  - 125
  - 111

擴大日期範圍以包含特定日期

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_date >= '2022-05-09';

說明: 使用 >= 運算子來選擇 2022-05-09 或之後的登入嘗試。
問題: 2022 年 5 月 9 日或之後進行了多少次登入嘗試？
- 選擇:
  - 165
  - 186
  - 190
  - 143

檢查進度:

按一下「檢查我的進度」以驗證您是否已正確完成此任務。

任務 9: 檢索某個日期範圍內的登入訊息

目標: 縮小搜尋範圍，排除 2022 年 5 月 11 日之後的登入嘗試。

檢索特定日期範圍內的登入訊息

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_date BETWEEN '2022-05-09' AND '2022-05-11';

說明: 使用 BETWEEN 和 AND 運算子來選擇 2022-05-09 和 2022-05-11 之間的登入嘗試。
問題: 2022 年 5 月 9 日至 2022 年 5 月 11 日期間進行了幾次登入嘗試？
- 選擇:
  - 134
  - 157
  - 160
  - 123

檢查進度:

按一下「檢查我的進度」以驗證您是否已正確完成此任務。

任務 10: 調查特定時間的登入情況

目標: 調查在特定時間進行的登入，了解在典型工作時間之外的登入情況。

檢索 07:00:00 之前的登入嘗試

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_time < '07:00:00';

說明: 使用 < 運算符來選擇在 07:00:00 之前的登入嘗試。
問題: 此查詢傳回的第五筆記錄的使用者名稱是什麼？
- 選擇:
  - 埃拉布
  - 傑拉斐爾
  - 阿庫克
  - 比勒斯

檢索 06:00:00 到 07:00:00 之間的登入嘗試

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_time BETWEEN '06:00:00' AND '07:00:00';

說明: 使用 BETWEEN 運算子來選擇 06:00:00 到 07:00:00 之間的登入嘗試。
問題: 06:00:00 到 07:00:00 之間最早的登入嘗試是什麼時間？
- 選擇:
  - 06:03:41
  - 06:01:31
  - 06:04:34
  - 06:15:41

檢查進度:

按一下「檢查我的進度」以驗證您是否已正確完成此任務。

任務 11: 按事件 ID 調查登入

目標: 根據事件 ID 號碼調查登入嘗試，只返回特定欄位。

查詢事件 ID 大於或等於 100 的登入嘗試

指令:

SELECT event_id, username, login_date  
FROM log_in_attempts  
WHERE event_id >= 100;

說明: 使用 >= 運算符來選擇事件 ID 大於或等於 100 的登入嘗試。
問題: 查詢傳回的第三個結果的登入日期是什麼？
- 選擇:
  - 2022-05-10
  - 2022-05-11
  - 2022-05-09
  - 2022-05-08

修改查詢以傳回事件 ID 在 100 和 150 之間的登入嘗試

指令:

SELECT event_id, username, login_date  
FROM log_in_attempts  
WHERE event_id BETWEEN 100 AND 150;

說明: 使用 BETWEEN 運算子來選擇事件 ID 在 100 和 150 之間的登入嘗試。
問題: 查詢傳回的第七個結果的使用者名稱是什麼？
- 選擇:
  - 特米切爾
  - 蓋斯帕札
  - 馬巴迪
  - 比勒斯

檢查進度:

按一下「檢查我的進度」以驗證您是否已正確完成此任務。

任務 12: 檢索特定日期之後的登入嘗試

目標: 收集特定日期之後的登入嘗試資訊。

查詢特定日期之後的登入嘗試

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_date > '2022-05-09';

說明: 使用 > 運算符來選擇登入日期大於 '2022-05-09' 的記錄。
問題: 2022 年 5 月 9 日之後嘗試登入的次數有多少？
- 選擇:
  - 125
  - 111
  - 185
  - 134
答案: 2022 年 5 月 9 日後嘗試登入的次數為 125 次。

擴大日期範圍以包含指定日期

指令:

SELECT *  
FROM log_in_attempts  
WHERE login_date >= '2022-05-09';

說明: 使用 >= 運算符來選擇登入日期大於或等於 '2022-05-09' 的記錄。
問題: 2022 年 5 月 9 日或之後進行了多少次登入嘗試？
- 選擇:
  - 143
  - 186
  - 165
  - 190
答案: 從 2022 年 5 月 9 日起，嘗試登入的次數為 165 次。

檢查進度:

按一下「檢查我的進度」以驗證您是否已正確完成此任務。

任務 13. 按事件 ID 調查登入

查詢大於或等於指定事件 ID 的登入嘗試

目標：根據事件 ID 號碼調查登入嘗試，僅返回 event_id、username 和 login_date 欄位。
查詢指令：

SELECT event_id, username, login_date
FROM log_in_attempts
WHERE event_id >= 100;

問題：

查詢傳回的第三筆結果的登入日期是多少？

答：第三筆回傳結果的登入日期是 2022-05-09。

查詢在特定範圍內的事件 ID 的登入嘗試

目標：修改查詢以僅傳回 event_id 介於 100 和 150 之間的登入嘗試。
查詢指令：

SELECT event_id, username, login_date
FROM log_in_attempts
WHERE event_id BETWEEN 100 AND 150;

問題：

查詢傳回的第七筆結果的使用者名稱是什麼？

答：第七筆結果的使用者名稱是 tmitchel。

任務 14. 在幾小時後檢索失敗的登入嘗試

查詢目的

調查在工作時間（18:00）之後的失敗登入嘗試。

查詢指令

篩選工作時間後的失敗登入嘗試：

SELECT *  
FROM log_in_attempts 
WHERE login_time > '18:00' AND success = 0;

注意事項：

success 列中的布林值 TRUE 和 FALSE 在 SQL 中表示為 1 和 0。因此，success = 0 代表失敗的登入嘗試。
login_time 的值應以 'HH:MM' 格式提供，且不需要加上引號的布林值 TRUE 和 FALSE。

問題：

18:00 之後發生了多少次失敗的登入嘗試？

答：20 次。

任務 15. 檢索墨西哥境外的登入嘗試

查詢目的

調查不來自墨西哥的登入嘗試。國家/地區欄位中包含 'MEX' 和 'MEXICO'。

查詢指令

檢索墨西哥境外的登入嘗試：

SELECT *  
FROM log_in_attempts 
WHERE country NOT LIKE 'MEX%';

注意事項：

使用 NOT 運算符和 LIKE 模式來排除墨西哥的登入嘗試。

問題：

在墨西哥境外進行了多少次登入嘗試？

答：144 次。

任務 16. 檢索行銷部門的員工

查詢目的

從 employees 表格中檢索行銷部門（'Marketing'）位於東樓的所有辦公室的員工資訊。

查詢指令

檢索所有列：

SELECT *  
FROM employees;

檢索東樓市場部門的員工：

SELECT *  
FROM employees  
WHERE department = 'Marketing' 
AND office LIKE 'East-%';

注意：

使用 AND 運算符來結合條件。
使用 LIKE 'East-%' 模式來匹配東樓的所有辦公室。

問題：

東樓市場部門的第一個員工的用戶名是什麼？

答：jclark。

任務 16. 檢索財務或銷售部門的員工

查詢目的

檢索 Finance 或 Sales 部門的所有員工資訊。

查詢指令

檢索財務或銷售部門的員工：

SELECT *  
FROM employees  
WHERE department = 'Finance' 
OR department = 'Sales';

注意：

使用 OR 運算符來結合兩個條件。

問題：

銷售部門第一個員工的用戶名是什麼？

答：bisles。

任務 17. 檢索所有非 IT 員工

查詢目的

檢索所有不屬於 Information Technology 部門的員工資訊。

查詢指令

檢索所有非 IT 員工：

SELECT *  
FROM employees  
WHERE department <> 'Information Technology';

注意：

使用 <> 運算符來排除 Information Technology 部門的員工。

問題：

有多少員工不屬於資訊科技部門？

答：161。

任務 18. 在幾小時後檢索失敗的登入嘗試

查詢目的

檢索在工作時間之後 (18:00) 發生的所有失敗登入嘗試。

查詢指令

查詢語法：

SELECT *  
FROM log_in_attempts 
WHERE login_time > '18:00' AND success = FALSE;

注意：

success 欄位的布林值 TRUE 和 FALSE 在 MySQL 中分別為 1 和 0，不需要放在單引號中。

問題：

18:00 之後發生了多少次失敗的登入嘗試？

答：19。

任務 19. 檢索特定日期的登入嘗試

查詢目的

檢索 2022-05-08 和 2022-05-09 這兩天內發生的所有登入嘗試。

查詢指令

查詢語法：

SELECT *  
FROM log_in_attempts  
WHERE login_date = '2022-05-09' OR login_date = '2022-05-08';

注意：

使用 OR 運算符來檢索多個日期的記錄。

問題：

這兩天嘗試登入的次數是多少？

答：75。

任務 20. 檢索墨西哥境外的登入嘗試

查詢目的

檢索並非源自墨西哥的登入嘗試。國家/地區欄位包含 'MEX' 和 'MEXICO' 的條目。

查詢指令

查詢語法：

SELECT *  
FROM log_in_attempts  
WHERE NOT Country LIKE 'MEX%';

說明：

使用 NOT 運算符和 LIKE 匹配模式 'MEX%' 來排除所有以 'MEX' 開頭的條目。

問題：

在墨西哥境外進行了多少次登入嘗試？

答：144。

任務 21. 檢索行銷部門的員工

查詢目的

檢索位於東樓 (East%) 且部門為 Marketing 的所有員工資訊。

查詢指令

查詢語法：

SELECT *  
FROM employees  
WHERE department = 'Marketing' AND office LIKE 'East%';

說明：

使用 AND 運算符結合 department 和 office 兩個條件。
LIKE 'East%' 用來匹配所有以 East 開頭的辦公室代碼。

問題：

東樓市場部第一位員工的使用者名稱是什麼？

答：elarson。

## 任務 22. 檢索財務或銷售部門的員工

查詢目的

檢索部門為 Finance 或 Sales 的所有員工資訊。

查詢指令

查詢語法：

SELECT *  
FROM employees  
WHERE department = 'Finance' OR department = 'Sales';

說明：

使用 OR 運算符來結合 department 列的兩個條件，查詢財務部門或銷售部門的所有員工。

問題：

查詢回傳的銷售部門第一位員工的使用者名稱是什麼？

答：lrodriqu。

任務 23. 檢索所有非 IT 員工

查詢目的

檢索不屬於 Information Technology 部門的所有員工資訊。

查詢指令

查詢語法：

SELECT *  
FROM employees  
WHERE NOT department = 'Information Technology';

說明：

使用 NOT 運算符來排除 department 列中為 Information Technology 的員工。

問題：

有多少員工不屬於資訊科技部門？

答：共有 161 名員工不屬於資訊科技部門。

任務 24. 將員工與他們的機器相匹配

查詢目的

確定哪些員工正在使用哪些機器，並根據 device_id 連結 machines 表和 employees 表。

查詢指令

檢視 machines 表中的所有記錄：

SELECT *  
FROM machines;

完成內聯接查詢：

SELECT *  
FROM machines  
INNER JOIN employees ON machines.device_id = employees.device_id;

說明：

使用 INNER JOIN 根據 device_id 連結 machines 表和 employees 表。
device_id 是兩個表中的公共列，用於執行聯結。

查詢回傳的行數：

132 行

任務 25. 返回更多數據

查詢目的

檢索所有機器及其擁有者的資訊，以及所有員工及其分配的機器資訊。使用左聯接和右聯接來達成這些目的。

查詢指令

左聯接 (Left Join) 查詢：

SELECT *  
FROM machines  
LEFT JOIN employees ON machines.device_id = employees.device_id;

說明：

使用 LEFT JOIN 連接 machines 表和 employees 表。
包含 machines 表中的所有記錄，即使它們沒有對應的 employees 記錄。

結果：
傳回的最後一筆記錄的使用者名稱是 NULL。

右聯接 (Right Join) 查詢：

SELECT *  
FROM machines  
RIGHT JOIN employees ON machines.device_id = employees.device_id;

說明：

使用 RIGHT JOIN 連接 machines 表和 employees 表。
包含 employees 表中的所有記錄，即使它們沒有對應的 machines 記錄。

任務 26. 檢索登入嘗試數據

查詢目的

檢索所有嘗試登入的員工資訊。使用內部聯接將 employees 表和 log_in_attempts 表連結，通過公共的 username 列。

查詢指令

SELECT *  
FROM employees  
INNER JOIN log_in_attempts ON employees.username = log_in_attempts.username;

說明：

使用 INNER JOIN 連接 employees 表和 log_in_attempts 表。
連接條件是 employees.username 等於 log_in_attempts.username。
table.column 用於指定表和列的名稱。

指令整理

在這次課程中，我們涵蓋了以下 SQL 查詢和數據操作技巧：

檢索失敗的登入嘗試：

查詢語法：SELECT * FROM log_in_attempts WHERE login_time > '18:00' AND success = FALSE;
目標：找出工作時間（18:00 之後）的所有失敗登入嘗試。

檢索特定日期的登入嘗試：

查詢語法：SELECT * FROM log_in_attempts WHERE login_date = '2022-05-09' OR login_date = '2022-05-08';
目標：檢索指定日期和前一天的所有登入嘗試。

檢索行銷部門的員工：

查詢語法：SELECT * FROM employees WHERE department = 'Marketing' AND office LIKE 'East%';
目標：找出所有位於東樓的行銷部門員工。

檢索財務或銷售部門的員工：

查詢語法：SELECT * FROM employees WHERE department = 'Finance' OR department = 'Sales';
目標：檢索財務部門或銷售部門的所有員工。

檢索所有非 IT 員工：

查詢語法：SELECT * FROM employees WHERE NOT department = 'Information Technology';
目標：找出所有不屬於資訊科技部門的員工。

內部聯接（INNER JOIN）：

查詢語法：SELECT * FROM machines INNER JOIN employees ON machines.device_id = employees.device_id;
目標：聯接 machines 和 employees 表，根據 device_id 列來檢索機器和相應的員工資訊。

左聯接（LEFT JOIN）：

查詢語法：SELECT * FROM machines LEFT JOIN employees ON machines.device_id = employees.device_id;
目標：檢索所有 machines 表中的記錄，包括那些未分配給任何員工的機器。

右聯接（RIGHT JOIN）：

查詢語法：SELECT * FROM machines RIGHT JOIN employees ON machines.device_id = employees.device_id;
目標：檢索所有 employees 表中的記錄，包括那些未分配任何機器的員工。

檢索登入嘗試數據：

查詢語法：SELECT * FROM employees INNER JOIN log_in_attempts ON employees.username = log_in_attempts.username;
目標：聯接 employees 和 log_in_attempts 表，檢索所有登入嘗試的員工資訊。

返回所有機器及其擁有者：

左聯接查詢語法：SELECT * FROM machines LEFT JOIN employees ON machines.device_id = employees.device_id;
右聯接查詢語法：SELECT * FROM machines RIGHT JOIN employees ON machines.device_id = employees.device_id;
目標：檢索所有機器以及它們的擁有者，並進行相反操作以檢索所有員工及其機器。

總結

我們所進行的工作包含以下幾個關鍵步驟：

數據匯入與整合：

數據匯入：將多個 Linux 伺服器產生的日誌檔案匯入資料庫，確保數據的完整性與準確性。這些日誌檔案可能涵蓋系統事件、登入嘗試、錯誤報告等重要信息。
資料表設計：設計和配置相應的資料表（如 log_in_attempts, machines, employees 等），以便有效儲存和管理不同類型的日誌數據。

使用 SQL 查詢進行數據分析：

內部聯接（INNER JOIN）：透過內部聯接，我們能夠將不同資料表中的相關記錄進行匹配，從而檢索出符合條件的綜合信息。例如，將 employees 和 log_in_attempts 表根據 username 列進行聯接，以獲取所有登入嘗試的詳細信息。
左聯接（LEFT JOIN）和右聯接（RIGHT JOIN）：這些聯接操作允許我們分別檢索所有機器和所有員工的相關記錄，無論它們是否有對應的匹配記錄。左聯接用於包含所有機器及其擁有者，右聯接則用於包含所有員工及其機器分配情況。
條件查詢：利用 SQL 的 WHERE 子句來篩選特定條件下的數據，例如檢索某些日期範圍內的登入嘗試、特定部門的員工信息等。
運算子使用：採用 AND, OR, NOT 等邏輯運算子來進一步精細化查詢條件，確保所檢索數據符合具體的業務需求。

結果分析與報告：

數據匯總：對查詢結果進行匯總和分析，以提取有用的信息，如登入嘗試的總數、特定部門的員工數量等。
數據視覺化：根據查詢結果生成報告，支持決策過程和後續的問題解決策略。

通過這些步驟，我們能夠從龐大的日誌數據中提取出關鍵資訊，從而提高系統的可監控性和安全性。有效地利用 SQL 查詢來分析和處理這些數據，不僅能夠促進系統管理和故障排除，還能為未來的優化和改進提供寶貴的依據。

【Google資安證書重點整理】Day 15_資安相關指令_進階教學

【Google資安證書重點整理】Day 17_資產、威脅和漏洞( 一 )

系列文

【Google資安證書課程重點整理】共 30 篇

RSS系列文訂閱系列文

5 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1064 組

團體組數

40 組

累計文章數

22091 篇

完賽人數

594 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js

IT邦幫忙

【Google資安證書課程重點整理】系列 第 16 篇

【Google資安證書重點整理】Day 16_SQL資安相關指令_基礎教學

導讀

查詢資料庫 (Query a Database)

FROM

ORDER BY

SELECT

應用過濾器於 SQL 查詢 (Apply Filters to SQL Queries)

AND

BETWEEN

= (等於)

> (大於)

>= (大於或等於)

< (小於)

<= (小於或等於)

LIKE

NOT

<> (不等於)

!= (不等於)

OR

% (百分號)

_ (下劃線)

WHERE

連接表格 (Join Tables)

FULL OUTER JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

執行計算 (Perform Calculations)

<mark>AVG

COUNT

SUM

於課程中的SQL範例

任務 1: 檢索員工設備數據

任務 2: 調查登入活動

任務 3: 訂購登入嘗試數據

任務4 : 列出所有組織機器

任務 5: 檢索作業系統 2 的電腦列表

任務 6: 列出特定部門的員工

任務 7: 辨識員工機器

任務 8: 檢索特定日期之後的登入嘗試

任務 9: 檢索某個日期範圍內的登入訊息

任務 10: 調查特定時間的登入情況

任務 11: 按事件 ID 調查登入

任務 12: 檢索特定日期之後的登入嘗試

任務 13. 按事件 ID 調查登入

查詢大於或等於指定事件 ID 的登入嘗試

查詢在特定範圍內的事件 ID 的登入嘗試

任務 14. 在幾小時後檢索失敗的登入嘗試

查詢目的

查詢指令

任務 15. 檢索墨西哥境外的登入嘗試

查詢目的

查詢指令

任務 16. 檢索行銷部門的員工

查詢目的

查詢指令

任務 16. 檢索財務或銷售部門的員工

查詢目的

查詢指令

任務 17. 檢索所有非 IT 員工

查詢目的

查詢指令

任務 18. 在幾小時後檢索失敗的登入嘗試

查詢目的

查詢指令

任務 19. 檢索特定日期的登入嘗試

查詢目的

查詢指令

任務 20. 檢索墨西哥境外的登入嘗試

查詢目的

查詢指令

任務 21. 檢索行銷部門的員工

查詢目的

查詢指令

## 任務 22. 檢索財務或銷售部門的員工

查詢目的

查詢指令

任務 23. 檢索所有非 IT 員工

查詢目的

【Google資安證書課程重點整理】系列第 16 篇

`FROM`

`ORDER BY`

`SELECT`

`AND`

`BETWEEN`

`=` (等於)

`>` (大於)

`>=` (大於或等於)

`<` (小於)

`<=` (小於或等於)

`LIKE`

`NOT`

`<>` (不等於)

`!=` (不等於)

`OR`

`%` (百分號)

`_` (下劃線)

`WHERE`

`FULL OUTER JOIN`

`INNER JOIN`

`LEFT JOIN`

`RIGHT JOIN`

`<mark>AVG`

`COUNT`

`SUM`