SQL基礎與資料函式庫模型與工具

SQL 的執行順序並非按照程式碼撰寫順序，而是遵循 FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY 的邏輯。理解這個順序對於撰寫高效能的 SQL 查詢至關重要。此外，資料函式庫模型以圖表形式呈現資料函式庫中各表格間的關係，包含表格結構、欄位和關聯性等資訊，有助於開發者理解資料函式庫架構。在實際應用中，開發者通常會先參考資料函式庫模型，再撰寫 SQL 查詢。

SQL基礎與資料函式庫模型解析

SQL（Structured Query Language）是一種用於管理和操作關聯式資料函式庫的標準語言。其執行順序與書寫順序不同，瞭解其執行邏輯對於撰寫高效的SQL查詢至關重要。

SQL執行順序解析

SQL查詢的執行順序如下：

1. FROM：收集所有需要的資料表。
2. WHERE：根據條件篩選資料列。
3. GROUP BY：將資料列分組。
4. HAVING：對分組後的資料進行篩選。
5. SELECT：指定要顯示的欄位。
6. ORDER BY：對結果進行排序。

這種順序確保了SQL查詢能夠正確地處理資料並傳回所需的結果。

資料函式庫模型簡介

資料函式庫模型是用於描述資料函式庫中各個表格之間關係的視覺化表示。它提供了關於表格結構、欄位以及表格之間關係的重要資訊。

資料函式庫模型中的關鍵術語

資料函式庫模型的實際應用

瞭解資料函式庫模型有助於撰寫涉及多個表格的SQL查詢。在實際工作中，常見的做法是先查閱資料函式庫模型，以瞭解各個表格之間的關係，然後再根據需求撰寫相應的SQL查詢。

撰寫SQL程式碼的環境

SQL程式碼可以在多種環境中撰寫，包括：

1. 關聯式資料函式倉管理系統（RDBMS）：如MySQL、Oracle、PostgreSQL、SQL Server和SQLite等。選擇合適的RDBMS取決於專案需求和公司環境。
2. 資料函式庫工具：提供圖形使用者介面（GUI），使得管理和查詢資料函式庫更加直觀和方便。
3. 其他程式語言：如Python和R等，可以透過相應的函式庫連線到RDBMS，直接在這些語言中撰寫和執行SQL查詢。

快速入門SQLite

對於想要快速開始使用SQL的人來說，SQLite是一個很好的選擇。它設定簡單，能夠提供基本的SQL功能，儘管在安全性和多使用者支援方面可能不如其他RDBMS。

資料函式倉管理系統軟體介紹

在學習SQL的過程中，瞭解不同的資料函式倉管理系統（Relational Database Management System, RDBMS）及其操作方式是非常重要的。本章節將介紹幾種常見的RDBMS軟體，包括SQLite、MySQL、Oracle、PostgreSQL和SQL Server，並說明如何使用終端機與這些系統互動。

終端機視窗

終端機視窗是電腦上的一個應用程式，通常具有黑色背景，只允許輸入文字。不同作業系統下的終端機應用程式名稱不同：

• Windows：Command Prompt
• macOS和Linux：Terminal

開啟終端機視窗後，您會看到命令列提示符號，通常以>符號表示，後面跟著一個閃爍的游標，表明終端機已準備好接收使用者輸入的文字命令。

SQLite

SQLite是一款免費且輕量級的資料函式庫系統，適合用於小型專案或學習用途。您可以從SQLite官方網站下載安裝檔案。

快速測試SQLite

1. 開啟終端機，輸入sqlite3 my_new_db.db來啟動SQLite並連線到一個資料函式庫檔案。

   sqlite> CREATE TABLE test (id int, num int);
   sqlite> INSERT INTO test VALUES (1, 100), (2, 200);
   sqlite> SELECT * FROM test LIMIT 1;
   

2. 使用以下命令來顯示資料函式庫、表格，或離開SQLite：

   sqlite> .databases
   sqlite> .tables
   sqlite> .quit
   

程式碼解析：

• CREATE TABLE test (id int, num int);：建立一個名為test的表格，包含兩個整數欄位id和num。
  • 作用：定義表格結構，用於儲存資料。
• INSERT INTO test VALUES (1, 100), (2, 200);：向test表格插入兩筆資料。
  • 作用：將資料新增至指定的表格中。
• SELECT * FROM test LIMIT 1;：查詢test表格中的第一筆資料。
  • 作用：從表格中擷取特定的資料列。

MySQL

MySQL是另一款流行的開放原始碼資料函式庫系統，目前由Oracle公司維護。您可以從MySQL官方網站下載MySQL Community Server。

快速測試MySQL

1. 開啟終端機，輸入相應命令啟動MySQL。

   mysql> CREATE TABLE test (id int, num int);
   mysql> INSERT INTO test VALUES (1, 100), (2, 200);
   mysql> SELECT * FROM test LIMIT 1;
   

2. 使用以下命令來顯示資料函式庫、切換資料函式庫、顯示表格，或離開MySQL：

   mysql> show databases;
   mysql> connect another_db;
   mysql> show tables;
   mysql> quit
   

程式碼解析：

• show databases;：顯示目前MySQL伺服器上的所有資料函式庫。
  • 作用：列出所有可用的資料函式庫。
• connect another_db;：切換到名為another_db的資料函式庫。
  • 作用：將目前的操作上下文切換到指定的資料函式庫。

Oracle

Oracle是一款商業資料函式庫系統，提供強大的功能和支援。您可以下載Oracle Database Express Edition免費版本。

快速測試Oracle

1. 開啟終端機，輸入相應命令啟動Oracle。

   SQL> CREATE TABLE test (id int, num int);
   SQL> INSERT INTO test VALUES (1, 100);
   SQL> SELECT * FROM test WHERE ROWNUM <=1;
   

2. 使用以下命令來顯示資料函式庫名稱、所有表格，或離開Oracle：

   SQL> SELECT * FROM global_name;
   SQL> SELECT table_name FROM all_tables;
   SQL> quit
   

程式碼解析：

• SELECT * FROM global_name;：查詢目前資料函式庫的名稱。
  • 作用：取得目前連線的資料函式庫名稱。
• SELECT table_name FROM all_tables;：列出目前使用者可見的所有表格名稱。
  • 作用：檢視目前資料函式庫中的所有表格。

PostgreSQL

PostgreSQL是一款功能強大的開放原始碼物件關聯式資料函式庫系統。您可以從PostgreSQL官方網站下載安裝檔案。

快速測試PostgreSQL

1. 開啟終端機，輸入相應命令啟動PostgreSQL。

   postgres=# CREATE TABLE test (id int, num int);
   postgres=# INSERT INTO test VALUES (1, 100), (2, 200);
   postgres=# SELECT * FROM test LIMIT 1;
   

2. 使用以下命令來顯示資料函式庫、切換資料函式庫、顯示表格，或離開PostgreSQL：

   postgres=# \l
   postgres=# \c another_db
   postgres=# \d
   postgres=# \q
   

程式碼解析：

• \l：列出PostgreSQL伺服器上的所有資料函式庫。
  • 作用：檢視所有可用的資料函式庫。
• \c another_db：切換到名為another_db的資料函式庫。
  • 作用：將目前的操作上下文切換到指定的資料函式庫。

SQL Server

SQL Server是由Microsoft開發的關聯式資料函式倉管理系統。您可以下載SQL Server Express免費版本。

快速測試SQL Server

1. 開啟終端機，輸入相應命令啟動SQL Server。

   1> CREATE TABLE test (id int, num int);
   2> INSERT INTO test VALUES (1, 100), (2, 200);
   3> go
   1> SELECT TOP 1 * FROM test;
   2> go
   

2. 使用以下命令來顯示資料函式庫、切換資料函式庫、顯示表格，或離開SQL Server：

   1> SELECT name FROM master.sys.databases;
   2> go
   1> USE another_db;
   2> go
   1> SELECT * FROM information_schema.tables;
   2> go
   1> quit
   

程式碼解析：

• SELECT name FROM master.sys.databases;：列出SQL Server例項上的所有資料函式庫名稱。
  • 作用：檢視目前SQL Server例項上的所有資料函式庫。
• USE another_db;：切換到名為another_db的資料函式庫。
  • 作用：將目前的操作上下文切換到指定的資料函式庫。

資料函式庫工具

除了直接使用終端機與RDBMS互動外，大多數人會使用圖形化介面的工具來操作資料函式庫。這些工具提供了友好的使用者介面，使寫SQL查詢和操作資料函式庫更加方便。

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 資料函式庫工具

rectangle "使用圖形介面" as node1
rectangle "透過驅動程式與資料函式庫互動" as node2
rectangle "直接輸入命令" as node3

node1 --> node2
node2 --> node3

@enduml

此圖示說明：

• 使用者可以透過圖形化介面的工具或直接在終端機輸入命令來與RDBMS互動。
• 資料函式庫工具透過特定的驅動程式與RDBMS進行通訊。

資料函式庫操作工具與 SQL 編寫環境

資料函式倉管理工具比較

在進行 SQL 編寫之前，首先需要了解各種資料函式倉管理系統（RDBMS）所使用的工具。以下是常見的 RDBMS 及其對應的工具：

各 RDBMS 對應工具一覽

連線資料函式庫的步驟

使用資料函式庫工具時，首先需要連線到資料函式庫。連線方式有三種：

1. 建立新的資料函式庫：使用 CREATE DATABASE 命令建立新的資料函式庫。

   CREATE DATABASE my_new_db;
   

   建立後，可以在新資料函式庫中建立表格並進行資料操作。

2. 開啟現有的資料函式庫檔案：如果擁有 .db 檔案，可以直接在工具中開啟並進行操作。

3. 連線遠端或本地的現有資料函式庫：輸入主機位址、埠號、資料函式庫名稱、使用者名稱和密碼等資訊以連線資料函式庫。

連線資訊欄位說明

連線資料函式庫時需要填寫以下資訊：

• 主機（Host）：資料函式庫所在位置，可以是 localhost、127.0.0.1 或遠端伺服器的 IP 位址。
• 埠號（Port）：根據 RDBMS 不同，使用預設的埠號，例如 MySQL 為 3306，PostgreSQL 為 5432。
• 資料函式庫名稱：要連線的資料函式庫名稱。
• 使用者名稱：用於登入資料函式庫的使用者帳號。
• 密碼：對應的使用者密碼。

若使用 SQLite，只需提供 .db 檔案的路徑即可。

在其他程式語言中使用 SQL

除了直接在資料函式庫工具中編寫 SQL 外，也可以在其他程式語言中執行 SQL 命令。本章節主要介紹如何在 Python 和 R 中進行 SQL 操作。

基本工作流程

1. 在資料函式庫工具中編寫 SQL 查詢。
2. 將查詢結果匯出為 .csv 檔案。
3. 在 Python 或 R 中匯入 .csv 檔案進行進一步分析。

進階工作流程

1. 在 Python 或 R 中直接連線資料函式庫。
2. 在 Python 或 R 中編寫並執行 SQL 查詢。
3. 繼續在 Python 或 R 中進行資料分析。

在 Python 中使用 SQL

連線步驟

1. 安裝資料函式庫驅動程式：根據所使用的 RDBMS，安裝對應的 Python 驅動程式。常見的驅動程式安裝指令如下表：

   RDBMS	安裝指令（pip）	安裝指令（conda）
   SQLite	無需安裝（Python 3 自帶 sqlite3）	無需安裝
   MySQL	pip install mysql-connector-python	conda install -c conda-forge mysql-connector-python
   Oracle	pip install cx_Oracle	conda install -c conda-forge cx_oracle
   PostgreSQL	pip install psycopg2	conda install -c conda-forge psycopg2
   SQL Server	pip install pyodbc	conda install -c conda-forge pyodbc

2. 設定資料函式庫連線：使用對應的 Python 程式碼連線到資料函式庫。範例如下表：

   # SQLite 示例
   import sqlite3
   conn = sqlite3.connect('my_new_db.db')
   
   # MySQL 示例
   import mysql.connector
   conn = mysql.connector.connect(
       host='localhost',
       database='my_new_db',
       user='alice',
       password='password'
   )
   

詳細程式碼解說

以下為每個範例程式碼的作用與邏輯說明：

1. SQLite 連線範例：

   import sqlite3
   conn = sqlite3.connect('my_new_db.db')
   

   • 這段程式碼首先匯入 sqlite3 模組，這是 Python 的內建模組，用於處理 SQLite 資料函式庫。
   • 接著使用 connect 方法連線到名為 my_new_db.db 的 SQLite 資料函式庫檔案。如果該檔案不存在，SQLite 將會自動建立它。
   • conn 物件代表了與資料函式庫的連線，可以用於後續的 SQL 操作。

2. MySQL 連線範例：

   import mysql.connector
   conn = mysql.connector.connect(
       host='localhost',
       database='my_new_db',
       user='alice',
       password='password'
   )
   

   • 首先匯入 mysql.connector 模組，這是 MySQL 的官方 Python 聯結器。
   • 使用 connect 方法建立與 MySQL 資料函式庫的連線，需要提供主機位址（host）、資料函式庫名稱（database）、使用者名稱（user）和密碼（password）。
   • 這裡的主機設為 localhost，表示資料函式庫在本機上。如果資料函式庫位於遠端伺服器，應填寫對應的 IP 位址或網域名稱。
   • 正確填寫連線資訊後，conn 物件將用於執行後續的 SQL 操作。

這些範例展示瞭如何在 Python 中連線到不同的資料函式庫系統，並準備好進行 SQL 操作。透過這些連線，可以在 Python 指令碼中直接執行 SQL 查詢並處理結果。