# OS 介紹

# 作業系統概述

作業系統促進人類與電腦的溝通，並支援多重應用程式運行。

舊版作業系統 (Legacy OS) 因不再更新或支援，容易受到新型威脅，存在安全風險，常見於需要大量嵌入式軟體的行業中。

1. 開機啟動
啟動電腦時，BIOS 或 UEFI (統一可延伸韌體介面，Unified Extensible Firmware Interface) 微晶片會被啟動。BIOS 是舊系統的啟動指令微晶片，UEFI 取代 BIOS，並在現代系統中使用，提供增強的安全性。

兩者執行相同的啟動功能，BIOS 曾是標準，直到 2007 年後 UEFI 開始普及。微晶片中的啟動指令包括檢查硬體健康等操作，並最終啟動引導程式 (bootloader) 來加載作業系統，完成後電腦即可使用。

2. 應用程式向作業系統發送請求，作業系統轉交給硬體並接收回傳資訊，再傳回應用程式。

範例：從網頁瀏覽器下載檔案
使用者決定下載檔案，點擊下載按鈕後，網頁瀏覽器將此操作傳送給作業系統。作業系統將請求發送給適當的硬體，硬體開始下載檔案並回傳進度。最後，作業系統通知網頁瀏覽器，瀏覽器再告知使用者下載完成。

# 虛擬化技術

虛擬機 (VM)
是利用軟體創建物理機器的虛擬版本，這些虛擬系統不依賴物理硬體，而是使用軟體模擬硬體。每個虛擬機有虛擬的 CPU、儲存空間等，並且像真實電腦一樣運行。

使用虛擬機的好處

1. 安全性：虛擬化可提供隔離環境（沙盒），讓多個虛擬機與物理主機和其他虛擬機隔離，增加安全性。例如，若某虛擬機感染惡意軟體，由於隔離，處理起來更安全。安全專業人員也可將惡意軟體放入虛擬機進行檢測。

   注意：雖然虛擬機能夠隔離惡意軟體和其他風險，但也要注意惡意軟體可能突破虛擬化環境。

2. 效率：虛擬機是高效且便捷的安全任務處理方式，允許同時開啟並輕鬆切換多個虛擬機，簡化測試和應用程式探索等任務。

管理虛擬機
虛擬機可以使用虛擬機管理程式 (hypervisor) 來管理，這些程式幫助連接虛擬硬體與物理硬體，並分配物理主機的共享資源給虛擬機。

一個常用的虛擬機管理程式是基於核心的虛擬機（KVM），它是開源的並且大多數 Linux 發行版支持。KVM 內建於 Linux 核心中，無需額外軟體即可在 Linux 系統上創建虛擬機。

其他形式的虛擬化
虛擬化除了應用於虛擬機器，還有其他形式。例如，單一物理伺服器可以創建多個虛擬伺服器，或創建虛擬網路來更高效地使用物理網路硬體。

# 使用者與 OS 的互動介面

圖形使用者介面 (GUI)

• 基於圖示，操作直觀。
• 包含開始選單、工作列、桌面等元素。
• 適合一般使用者，操作簡單 (一次只能執行一個任務)。
• 例如：Windows、macOS

命令列介面 (CLI)：

• 基於文字，使用命令進行操作。
• 靈活度高，可進行複雜操作。
• 適合熟悉指令的使用者，效率高 (可以同時執行多個任務)。
• 例如：Linux 終端機、DOS 命令提示字元。
• 好處：可以看到操作歷史紀錄

# Linux 作業系統

# 介紹

• Linux 是由 Linus Torvalds 和 Richard Stallman 等人共同創建的開源作業系統。
• 基於 GNU 通用公共許可證，允許自由使用、分享和修改。
• 安全分析師使用 Linux 檢查日誌、驗證身份和存取管理。
• 作為伺服器作業系統，以穩定性著稱。

Linux 結構

• User: Linux 是多用戶系統
• 應用程式：在 Linux 會透過套件管理器安裝應用程式。
• Shell
• 檔案系統階層標準 (Filesystem Hierarchy Standard, FHS): 用來規定資料儲存的結構和位置。
• Kernel: Linux 中管理程序與記憶體的組件。它與應用程式進行通信，並控制硬體的主要功能，有助於提高任務處理效率。
• 硬體：外圍設備 (Peripheral devices) 和主機板及所有附加於其上的組件。

# 發行版

不同的 Linux 發行版可能在預裝程式、使用者介面和父發行版上有所差異。

KALI LINUX ™

• 基於 Debian 的開源發行版。
• 預裝大量滲透測試和數位取證工具，是資安人員必備的工具。
• 建議在虛擬機器上使用，以避免對主機系統造成損壞。

滲透測試工具：

• Metasploit：用於尋找和利用系統漏洞。
• Burp Suite：用於測試 Web 應用程式弱點。
• John the Ripper：用於破解密碼。

數位取證工具：

• tcpdump：命令列資料包分析器，用於捕獲網路流量。
• Wireshark：圖形化資料包分析器，用於分析網路流量。
• Autopsy：硬碟和智慧型手機取證分析工具。

其它發行版

Ubuntu

• 基於 Debian、開源、使用者友善、廣泛應用於資安領域
• 也在雲端運算中被廣泛使用，隨著企業轉向雲伺服器，資安工作可能會更多涉及 Ubuntu 衍生版。

Parrot

• 基於 Debian、開源、使用者友善、廣泛應用於資安領域
• 預裝滲透測試和數位鑑識工具

Red Hat® Enterprise Linux®

• 訂閱制的 Linux 發行版，專為企業設計，並非免費。

AlmaLinux

• 社群驅動的 Linux 發行版，作為 CentOS 的穩定替代品。
• CentOS 8 最後一個穩定版本發佈於 2021 年 12 月，AlmaLinux 設計為其直接替代品，確保原有應用和配置能在 AlmaLinux 上繼續運行。CentOS 則來自 Red Hat。

Linux 套件管理器

套件管理器幫助使用者安裝、管理和移除套件，並解決依賴問題。Linux 使用多個套件管理器，且經常透過它來分發應用程式。

套件管理器類型
許多 Linux 發行版來自相同的父發行版，這有助於安裝應用程式時選擇正確的套件管理器。例如，Red Hat 套件管理器 (RPM) 用於 Red Hat 衍生的發行版，而 dpkg 用於 Debian 衍生的發行版。

不同套件管理器使用不同的副檔名，如 RPM 使用 .rpm ，而 dpkg 使用 .deb 。

除了 RPM 和 dpkg，還有一些套件管理工具能通過命令行簡化套件操作。常見的有:

• APT (進階打包工具，Advanced Package Tool)
  用於 Debian 衍生發行版的工具，透過命令行管理、搜尋和安裝套件。
• YUM (Yellowdog Updater Modified)
  用於 Red Hat 衍生發行版的工具，透過命令行管理、搜尋和安裝 .rpm 套件。

Lab1
使用 Qwiklabs 平台

常用快捷鍵與命令

• CTRL + C ：終止當前命令，若無命令則顯示 ^C 。
• CTRL + V ：粘貼文本。
• clear ：清除終端屏幕，或使用 CTRL + L 。
• CTRL + A ：將光標移到命令開頭。
• CTRL + E ：將光標移到命令結尾。
• 上箭頭鍵 ：查看上一條命令，按多次可查看歷史命令。
• 下箭頭鍵 ：查看下一條命令。
• Tab 鍵 ：補全命令或路徑。

實驗目標

• 學習如何使用 APT 安裝、卸載和查詢軟體包。
• 掌握 Suricata (開源的入侵檢測和防禦系統) 和 tcpdump 這兩個網路安全工具的基本用法。

實驗步驟

1. 確認 APT 在 Bash 中已安裝: apt
2. 安裝 Suricata: sudo apt install suricata ，確認安裝: suricata
3. 卸載 Suricata: sudo apt remove suricata ，確認卸載
4. 安裝 tcpdump: sudo apt install tcpdump
5. 列出所有已安裝的應用程序: apt list --installed
6. 重新安裝 Suricata，確認出現在已安裝 list 裡面

# Linux Shell

• Shell 是命令列解釋器，幫助使用者與作業系統進行互動。
• 使用者輸入命令後，Shell 返回標準輸出或標準錯誤，前者為作業系統返回的資訊，後者為錯誤訊息。

功能:

• 執行命令：使用者輸入命令，Shell 將其傳遞給系統執行。
• 連結應用程式：將不同的應用程式連接起來，實現複雜的任務。
• 自動化任務：透過腳本編寫，實現任務自動化。

種類

• Bash：最常用的 Shell，也是本課程重點。
• 其他 Shell：csh, ksh, tcsh, zsh 等。

Lab2
實驗目標

• 使用 echo 命令生成輸出。
• 使用 expr 命令進行基本運算。
• 使用 clear 命令清除 Shell 視窗。

實驗步驟

1. 使用命令輸出文字: echo {String}
   • 使用引號包裹文字，可以輸出包含空格或特殊字符的字符串。
2. 使用命令進行計算: expr {expression}
   • 可以使用 +, -, *, / 進行整數 運算，all 運算子和運算元中間都要有空格。
3. 清空終端畫面: clear

# Bash Shell 中的 Linux 命令

# Linux 檔案系統

根目錄：檔案系統的起始點，用 / 表示。
標準目錄及其內容範例：

• /home ：每個使用者的主目錄。
• /bin ：包含二進位檔案及可執行檔案。
• /etc ：儲存系統配置檔案。
• /tmp ：儲存臨時檔案，常被攻擊者利用，因為系統中的任何人都可以修改這些檔案中的資料。
• /mnt ：儲存掛載的隨身碟或硬碟。

使用者的主目錄可以用波浪符號（~）表示，如 ~/logs 代表 /home/ben/logs 。

瀏覽檔案系統的命令

• pwd : 顯示當前工作目錄的絕對路徑，例如 /home/ben 。若要查看使用者名稱，可使用 whoami 。
• ls : 若要查看其他目錄內容，可在 ls 後加上相對或絕對路徑。常用選項:
  • -l : 以長格式顯示，包括權限、擁有者名稱、擁有者所在群組、文件大小以及最後修改的時間。
  • -a : 顯示所有檔案，包括隱藏檔案 (開頭為 . )。
  • -d : 僅顯示目錄。
  • -r : 反向排序。
  • ls *.txt : 找所有文字檔案
  • ls -la : 結合隱藏和長格式顯示，以此類推。
• cd

讀文件內容的命令

• cat : 顯示檔案的全部內容，如 cat updates.txt , -n 顯示行數。
• head : 顯示檔案的前 10 行。若要調整行數，使用 head -n 行數 。
• tail : 顯示檔案的最後 10 行，通常用於查看日誌檔案的最新資訊。
• less : 逐頁顯示檔案內容，並可用鍵盤操作導航：空白鍵向前翻頁， b 向後翻頁，箭頭鍵逐行滾動， q 退出。

Lab3
實驗目標

• 使用 cd 導航目錄
• pwd 顯示當前目錄
• ls 列出目錄內容
• cat 和 head 顯示檔案內容。

實驗步驟

1. 查詢當前目錄並顯示內容
2. 進入 /home/analyst/reports 目錄並列出子目錄
3. 進入 /home/analyst/reports/users 並顯示 Q1_added_users.txt 內容
4. 進入 /home/analyst/logs 並顯示檔案的前 10 行

過濾內容指令

• grep : 搜尋檔案中的指定字串，並返回所有包含該字串的行。範例： grep OS updates.txt 會搜尋 updates.txt 中包含 OS 的行。常用選項:
  • -i : 忽略大小寫
  • -n : 顯示行號
  • -c : 計算次數
• | : 管道（Piping）將一個命令的輸出傳送為另一個命令的輸入，通常用來過濾結果。例: ls reports | grep users 會列出 reports 目錄中包含 users 的檔案和目錄名稱。管道是 Linux 中的重定向工具，可用於多種處理任務。
• find : 搜尋符合特定條件的檔案和目錄，條件可以包括名稱、大小或修改時間。例： find projects 從 projects 目錄開始搜尋 (建議先加條件選項再做搜尋)。條件選項以短橫線（ - ）開頭:
  • -name : 搜尋區分大小寫的檔案名稱。
  • -iname : 搜尋不區分大小寫的檔案名稱。
  • 範例： find projects -name "*log*" 會搜尋檔名包含"log" 的檔案，而 -iname 會搜尋包含 "log"、"Log" 或 "LOG" 的檔案。
  • -mtime : 搜尋在特定天數範圍內最後修改過的檔案或目錄。
  • 範例： find projects -mtime -3 搜尋過去三天內修改的檔案。 -mtime +1 會搜尋一天以上； -mtime -1 會搜尋不到一天的。
  • -mmin 可以基於分鐘來搜尋。

Lab4
實驗目標

• 搜尋檔案中的錯誤訊息。
• 搜尋包含特定字串的檔案。
• 在使用者檔案中搜尋資訊。

實驗步驟

1. 進入 /home/analyst/logs ，並 grep error server_logs.txt
2. 進入 /home/analyst/reports/users ，並 ls | grep Q1 和 ls | grep access (也可 find . -name "*access*" )
3. grep jhill Q2_deleted_users.txt ， grep "Human Resources" Q4_added_users.txt

# 用 Bash 管理檔案

創建和修改目錄

• mkdir : 可以使用絕對路徑或相對路徑指定目錄位置。
• rmdir : 若目錄內有檔案或子目錄，則無法刪除，會返回錯誤。

創建和修改文件

• touch : 創建無內容的檔案。如: touch permissions.txt 。
• rm : 刪除檔案。因難以恢復，需小心使用。
• mv : 移動檔案或目錄，使用: mv 檔案或目錄 目標位置 。
  • 也可用來重新命名檔案，例如 mv permissions.txt perm.txt 會將檔案重新命名為 perm.txt 。
• cp : 複製檔案或目錄，使用同上。

nano 文本編輯器

• 簡單易用的命令列文本編輯器，廣泛應用於 Linux 和資訊安全領域。
• nano 檔名 ：會打開檔案進行編輯。也可以提供絕對路徑。
• nano 新檔案名字 ：創建新檔案。
• 由於沒有自動保存功能，編輯後需使用快捷鍵 Ctrl + O 保存，再用 Ctrl + X 退出。
• Vim 和 Emacs 也是常見的命令列文本編輯器。

標準輸出重定向 (Standard output redirection)

• 也可以使用重定向將輸出寫入檔案。除了 | ，還可以使用 > 和 >> 來重定向輸出。
• > : 會覆蓋檔案內容，需小心，因為覆蓋的檔案難以恢復。
• >> : 會將內容新增至檔案末尾。
• 例如， echo "last updated date" >> perm.txt 會將字串新增至檔案， echo "time" > perm.txt 會覆蓋檔案內容。
• 若檔案不存在， > 和 >> 會自動創建新檔案。

Lab5
實驗目標

• 創建目錄
• 刪除目錄
• 移動與刪除檔案
• 使用 nano 創建檔案並編輯內容

實驗步驟

1. 在 /home/analyst 中， mkdir logs 並用 ls 確認。
2. rmdir temp ，用 ls 確認。
3. 進入 /home/analyst/notes ， mv Q3patches.txt ../reports ，用 ls ~/reports 確認。
4. rm tempnotes.txt , ls.
5. touch tasks.txt , ls.
6. nano tasks.txt ，貼上指定文字後按 CTRL+X ，跳出詢問儲存後按 Y 儲存，再按 enter 確認檔名。用 cat tasks.txt 確認。

# 權限

Linux 中，檔案權限以 10 個字符表示，包括以下三種權限：

1. 讀取 (read): 對檔案為讀取內容，對目錄為讀取目錄內容。
2. 寫入 (write): 對檔案為修改內容，對目錄為創建新檔案。
3. 執行 (execute): 對檔案為執行 (如果該檔案是程式)，對目錄為進入並存取檔案。

這些權限分配給:

• 使用者 (user, u): 檔案的擁有者。
• 群組 (group, g): 檔案擁有者所屬的群組。
• 其他 (other, o): 系統中所有其他使用者。

顯示方法:

• drwxrwxrwx ，有該權限會顯示字母；若無則是顯示 - 。
• d 表示為目錄；反之為檔案。
• 其餘依序分配給使用者、群組和其他使用者。

更改權限

chmod : 用來更改檔案和目錄的權限。第一個引數是如何更改，第二個引數則是要更改的檔案或目錄。有兩種更改方法:

• 數字法
• 符號法，例: chmod u=r,g-rwx,o+rwx login_sessions.txt
  • 若超過一種所有者類型，要用逗號來分開且不應添加空格
  • + : 添加權限
  • - : 刪除權限
  • = : 分配權限

Lab6
實驗目標

• 檢查目錄中文件的權限
• 檢查文件權限配置並根據需要更改權限
• 移除對目錄的非授權訪問

實驗步驟

1. 進入 /home/researcher2/projects ， ls -la
2. chmod o-w project_k.txt , chmod g-r project_m.txt
3. chmod u=r,g=r .project_x.txt
4. chmod g-x drafts

練習: Use Linux commands to manage file permissions
模板: Google docs
解答: Google docs

負責任使用 sudo

要修改系統，您需要是 root 使用者或擁有提升特權的使用者。由於使用 root 用戶有安全風險，建議使用 sudo 來授予臨時提升的權限。

sudo 來自「super user do」，用來授予使用者提升的權限，並且只授權在「sudoers 文件」中列出的使用者。雖然使用 sudo 優於以 root 用戶登入，但具有 sudo 權限的使用者在攻擊事件中風險較高。

使用 sudo 就像酒店的主鑰匙，只有真正需要的使用者才應該使用。謹慎選擇要執行的命令，避免不必要的提升特權操作，並且要小心從網上來源複製命令時意外使用 sudo 。

使用 sudo 進行身份驗證與授權管理

身份驗證 (authentication) 是驗證身份，授權 (authorization) 是授予訪問資源的權限。以下是一些常見指令：

• useradd : 將使用者新增到系統。如: sudo useradd james 。附加選項:
  • -g : 設定使用者的預設群組，也稱為主群組。如: sudo useradd -g security james
  • -G : 將使用者新增至其他群組，也稱為輔助群組。如: sudo useradd -G finance,admin james
• usermod : 修改現有的使用者帳戶。附加選項:
  • 更改主群組: sudo usermod -g executive james
  • 新增輔助群組: sudo usermod -a -G marketing james ，注意：若未使用 -a 選項，-G 會替換所有現有的輔助群組。
  • -d ：更改主目錄，如: sudo usermod -d /home/james_h james
  • -l ：更改登入名稱。
  • -L ：鎖定帳戶。
• userdel : 刪除系統中的使用者。如: sudo userdel james 。
  • -r : 連帶刪除該使用者主目錄中的檔案。 sudo userdel -r james ，刪除檔案前請確保已備份。
  • 注意：也可以使用 usermod -L 停用帳戶，防止登入並保留權限以待轉移所有權給其他人。
• chown : 更改檔案或目錄的擁有權。例如:
  • 使用者擁有權更改 james: sudo chown james access.txt
  • 群組擁有權更改為 security: sudo chown :security access.txt ，注意：指定群組名稱時，需在群組前加冒號。

Lab7
實驗目標

• 新增員工
• 更改檔案擁有權
• 新員工加入新群組
• 刪除員工

實驗步驟

1. sudo useradd researcher9 , sudo usermod -g research_team researcher9
   • or just sudo useradd researcher9 -g research_team
2. sudo chown researcher9 ../researcher2/projects/project_r.txt
3. sudo usermod -a -G sales_team researcher9
4. 執行 sudo userdel researcher9 ，會提示使用者同名群組未刪除，再 sudo groupdel researcher9 .

# 查詢手冊

• 網頁: UNIX and Linux Stack Exchange
• CLI
  • man 指令 ：查詢指令詳盡的說明，輸出稱為 man page（手冊頁）。
    • 顯示第一頁資訊時，會暫停。按 ENTER 鍵逐行顯示，或按 空白鍵 顯示下一頁。按 Q 才可以離開。
  • whatis 指令 ：以單行顯示指令簡介，適合用於快速回憶或初步了解新指令。
  • apropos : 可在手冊頁中搜尋關鍵字。加上 -a 可搜尋多個詞，如 apropos -a graph editor 會找出同時含 "graph" 和 "editor" 的項目。
    • 它是精準搜尋工具，僅回傳符合關鍵字的指令。結果不符時可嘗試其他關鍵字。使用 -a 時，僅顯示同時符合所有關鍵字的結果。

Lab8
實驗目標

• 探索幫助了解其他指令的 man 命令
• 查找指令選項
• 比較兩個指令的差異
• 確定創建新群組的指令

實驗步驟

1. whatis cat , man cat , apropos -a first part file
2. man useradd
3. whatis rm , whatis rmdir
4. apropos -a create new group

# 資料庫與 SQL

# 介紹資料庫和 SQL

資料庫支持多人同時訪問和複雜的存取任務

關聯式資料庫

• 包含多個表格，表格之間可以通過鍵連接
• 主鍵 (primary key)：唯一標識每一 row (record) 的 col.
• 外鍵 (Foreign Key)：連接兩個表格的 col.，可以有空值和重複項，而外鍵一定是其他資料表的主鍵。

SQL (結構化查詢語言，Structured Query Language)

使用 SQL 的資安實務經驗

• 檢索 log，識別配置不正確的機器或異常模式
• 支援相關決策，如識別未更新的機器或最佳更新時間

SQL 過濾 vs Linux 過濾
可透過 Linux 命令列存取 SQL。輸入對應版本的指令，例如 sqlite3 。此後輸入的內容會被當作 SQL 指令執行。

# SQL 查詢

基本查詢

• SELECT : 指定要返回的欄位，欄位間用逗號隔開。
  • SELECT * : 返回所有欄位。
• FROM : 指定要查詢的表格
• 例子如下，這兩個是必要的關鍵字且注意結尾的分號。

  	SELECT customerid, city, country
  	FROM customers;

  (可小寫，但習慣大寫；習慣在新的一行接續 from)
• ORDER BY : 排序查詢結果，預設為遞增。數值資料會從小到大排序；文字資料則從 A 到 Z。例: ORDER BY city
  • ORDER BY col1, col2 : 如果 col1 的值相同，則按 col2 排序。
  • ORDER BY DESC : 可將結果按遞減排序。

Lab1
使用 MariaDB 的 shell，一樣的平台。

實驗目標

• 查詢員工裝置資訊
• 檢視登入嘗試
• 排序查詢結果

實驗步驟
1.

--1.1

SELECT *

FROM machines;

-- 聚焦在 email

SELECT device_id, email_client FROM machines;

--1.3

SELECT device_id, operating_system, OS_patch_date

FROM machines;

--2.1 找有無澳洲登入者

SELECT event_id, country

FROM log_in_attempts

ORDER BY country;

--2.2

SELECT username, login_date, login_time

FROM log_in_attempts;

--2.3

SELECT *

FROM log_in_attempts;

--3.1 前面同 2.3

ORDER BY login_date;

--3.2 前面同 2.3

ORDER BY login_date, login_time;

# 過濾結果

WHERE : 創建過濾器，並使用等號設置過濾條件。

-- 只返回 `title` 欄位包含「IT Staff」的紀錄

SELECT firstname, lastname, title, email

FROM employees

WHERE title = 'IT Staff';

通配符 (wildcard)
通配符是能夠替代其他字符的特殊符號，可根據過濾模式放置在字符串的前後。常用的有:

• 百分號（%）替代任意數量的字符。
• 底線（_）替代一個字符。

要使用通配符過濾，應使用 LIKE 運算符而非等號。

-- 包含「IT Staff」和「IT Manager」

SELECT lastname, firstname, title, email

FROM employees

WHERE title LIKE 'IT%';

Lab2
實驗目標

• 查詢機器及操作系統資訊
• 過濾特定操作系統的機器
• 過濾特定部門的員工
• 過濾使用特定機器的員工

實驗步驟
1.

SELECT device_id, operating_system
FROM machines;

--前面同1
WHERE operating_system = 'OS 2';

SELECT * 
FROM employees 
WHERE department = 'Finance';
--3.2 同上，改成'Sales'

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

--4.2 前面同4.1
WHERE office LIKE 'South%';

資安中的數字、日期與時間
安全分析師可能會遇到以下資料：

• 數字
  • 登錄嘗試次數
  • 特定類型日誌條目的數量
  • 來源發送的資料量
  • 目的地接收的資料量
• 日期與時間
  • 登錄日期
  • 時間戳
  • 補丁日期
  • 連線持續時間

比較運算符
過濾數字和日期時間資料時，常用以下運算符來篩選資料行：

• < , > , = , <= : 小於或等於， >=
• != 和 <> : 不等於

通常在查詢結尾的 WHERE 子句中使用，例子:

SELECT firstname, birthdate
FROM employees
WHERE birthdate > '1970-01-01';

BETWEEN : 過濾在特定範圍內的數字或日期，首尾有包含在內

--前面同上
WHERE hiredate BETWEEN '2002-01-01' AND '2003-01-01';

Lab3
實驗目標

• 過濾在特定日期之後的登錄嘗試
• 過濾在特定日期範圍內的登錄嘗試
• 過濾在特定時間進行的登錄嘗試
• 按 ID 過濾登錄嘗試

實驗步驟

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

--2 前面同上
WHERE login_date BETWEEN '2022-05-09' AND '2022-05-11';

--3.1 前面同上
WHERE login_time < '07:00:00';
--3.2
WHERE login_time BETWEEN '06:00:00' AND '07:00:00'
ORDER BY login_time;

--4.1
SELECT event_id, username, login_date
FROM log_in_attempts
WHERE event_id >= 100;
--4.2
WHERE event_id BETWEEN 100 AND 150;

邏輯運算符

• AND : 如 WHERE supportrepid = 5 AND country = 'USA';
• OR , 注意：即便兩個條件來自相同的欄位，仍需寫出完整條件。
• NOT : 僅作用於單一條件。也可以使用 <> 或 != 來表示不等於，以下是相等的查詢語法:
  • WHERE country <> 'USA'
  • WHERE country != 'USA'
  • WHERE NOT country = 'USA' 。
• 邏輯運算符可以結合使用。

SELECT firstname, lastname, email, country
FROM customers
WHERE NOT country = 'Canada' AND NOT country = 'USA';

Lab4
實驗目標

• 過濾非工作時間的登錄嘗試
• 過濾特定日期的登錄嘗試
• 過濾來自特定地點的登錄嘗試
• 過濾特定部門員工資訊
• 過濾不在特定部門的員工資訊

實驗步驟

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

--2 同1
WHERE login_date = '2022-05-09' OR login_date = '2022-05-08';

--3 同1
WHERE NOT country LIKE 'MEX%';

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

--5 同4
WHERE department = 'Finance' OR department = 'Sales';

--6 同4
WHERE NOT department = 'Information Technology';

練習: Apply filters to SQL queries
表格: Google docs

# SQL 合併查詢

內部連接 (Inner Join)

INNER JOIN 會返回在指定 col 中匹配的 row，該 col 存在於多個表中。
只返回有匹配的 row，但會返回所有連接表的指定 col。例如，使用 SELECT * 時會返回兩個表的所有 col。
注意： 如果 col 在兩個表中都存在，該 col 會返回兩次。

內部連接語法
INNER JOIN 查詢的語法如下：

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

必須在 FROM 後指定左邊表，並在 INNER JOIN 後指定右邊表。使用 ON 和 = 來指示連接的 column，並用句點分隔表名和 column 名。

若只選擇特定 columns，範例如下：

SELECT username, operating_system, employees.device_id  
FROM employees  
INNER JOIN machines ON employees.device_id = machines.device_id;

注意： username 和 operating_system 只出現在一個表中，僅寫 column 名；device_id 兩表都有，需指定表名和 column 名。

外部連接 (Outer Join): 返回來自一個表或兩個表的所有 rows。

左外部連接 (Left Join)

LEFT JOIN 會返回第一個表的所有 records，並只返回第二個表中在指定 column 上匹配的 rows。

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

employees 是左邊表，所有 records 都會返回，僅匹配 device_id 的 records 來自 machines。

右外部連接 (Right Join)

RIGHT JOIN 會返回第二個表的所有 records，並只返回第一個表中在指定 column 上匹配的 rows。

--語法與 LEFT JOIN 相同，交換表的順序即可交換左邊和右邊的表。
SELECT *  
FROM employees  
RIGHT JOIN machines ON employees.device_id = machines.device_id;

全外部連接 (Full Outer Join)

FULL OUTER JOIN 會返回兩個表中的所有 records，並可以視為完全合併兩個表。

--結果包括兩個表的所有 records，表的順序不影響結果。
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;

Lab5
實驗目標

• 使用 INNER JOIN 查找員工及設備信息
• 使用 LEFT JOIN 和 RIGHT JOIN 查找員工及設備信息
• 使用 INNER JOIN 查找員工及登錄嘗試信息

實驗步驟

--1 看過全部資料後做inner join
SELECT *
FROM machines
INNER JOIN employees ON machines.device_id = employees.device_id;

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

--2.2 同2.1改right
RIGHT JOIN employees ON machines.device_id = employees.device_id;

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

聚合函數 (Aggregate Functions)

在 SQL 中，聚合函數對多個數據點進行計算並返回結果，實際數據不會返回。常見的聚合函數包括：

• COUNT ：返回查詢結果的 row 數。
• AVG ：返回某個 column 的平均值。
• SUM ：返回某個 column 的總和。

將其關鍵字放在 SELECT 之後，並在括號內指定要計算的 column。

--排除 NULL 值
SELECT COUNT(firstname)
FROM customers
WHERE country = 'USA';

SQL 中還有許多其他聚合函數，語法與 COUNT 函數相同。