介紹

在上一篇部落格文章中，我們共同探討了以下內容的綜合指南：

https://dev.to/louaiboumediene/mastering-relational-database-design-a-compressive-guide-3jh8

在本文中，我們將在較高層次上發現實際用於實現資料庫模式設計的語言的語法和核心功能... SQL。

那麼...讓我們深入探討吧！

什麼是 SQL

SQL，即結構化查詢語言，是用於與所有RDMS對話的外觀語言，但是大多數時候，作為後端工程師（我們不是資料庫工程師），我們不直接使用 SQL，而是使用物件關係映射器ORM它在原始SQL 之上提供了一個更好、更可靠的接口，以便用我們首選的程式語言與資料庫進行通信，但是我們仍然需要SQL 的基本知識，以便快速、簡單地操作以及與資料庫互動。

基本 SQL 查詢

基本 SQL 查詢涉及使用 SELECT、DISTINCT、WHERE、LIMIT 和 OFFSET 等命令從資料庫表中選擇特定資料。

-- Selecting all columns from a table
SELECT * FROM employees;

-- Selecting specific columns
SELECT first_name, last_name FROM employees;

-- Using DISTINCT to get unique values
SELECT DISTINCT department_id FROM employees;

-- Using WHERE to filter results
SELECT * FROM employees WHERE department_id = 10;

-- Using LIMIT to limit the number of results
SELECT * FROM employees LIMIT 5;

-- Using OFFSET to skip certain rows
SELECT * FROM employees OFFSET 5;

過濾資料

在 SQL 中篩選資料可讓您根據某些條件從表格中擷取特定記錄，例如使用 >、<、= 等比較運算子以及 AND、OR 和 NOT 等邏輯運算子。

-- Using comparison operators
SELECT * FROM employees WHERE salary > 50000;

-- Using logical operators
SELECT * FROM employees WHERE department_id = 10 AND salary > 50000;

-- Using IN and NOT IN
SELECT * FROM employees WHERE department_id IN (10, 20);

-- Using BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

-- Using LIKE for pattern matching
SELECT * FROM employees WHERE last_name LIKE 'S%';

資料排序

SQL 中的資料排序是使用 ORDER BY 子句根據指定列以升序或降序排列檢索到的記錄。

-- Sorting data in ascending order
SELECT * FROM employees ORDER BY salary;

-- Sorting data in descending order
SELECT * FROM employees ORDER BY salary DESC;

-- Sorting by multiple columns
SELECT * FROM employees ORDER BY department_id, salary DESC;

聚合函數

SQL 中的聚合函數對一組值執行計算並傳回單一值。常見的聚合函數包括 COUNT、SUM、AVG、MIN 和 MAX。

-- Counting the number of rows
SELECT COUNT(*) FROM employees;

-- Calculating total salary
SELECT SUM(salary) FROM employees;

-- Finding average salary
SELECT AVG(salary) FROM employees;

-- Finding minimum salary
SELECT MIN(salary) FROM employees;

-- Finding maximum salary
SELECT MAX(salary) FROM employees;

資料分組

在 SQL 中將資料分組可讓您使用 GROUP BY 子句（通常與聚合函數結合使用）對指定列中具有相同值的行進行分組。

-- Grouping data by department
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- Using HAVING to filter grouped data
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

加入

SQL 中的聯結會根據相關資料列組合來自多個資料表的資料，以擷取跨這些資料表的資料。

-- Inner Join
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

-- Left Join
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;

-- Right Join
SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;

-- Full Outer Join
SELECT * FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

子查詢

SQL 中的子查詢是另一個查詢中的巢狀查詢，用於檢索依賴另一個查詢結果的資料。

-- Subquery example
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Correlated subquery example
SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

意見

SQL 中的檢視是根據查詢結果產生的虛擬表，提供了一種簡化複雜查詢並限制對某些資料的存取的方法。

-- Creating a view
CREATE VIEW high_paid_employees AS SELECT * FROM employees WHERE salary > 80000;

-- Updating a view
CREATE OR REPLACE VIEW high_paid_employees AS SELECT * FROM employees WHERE salary > 90000;

-- Dropping a view
DROP VIEW IF EXISTS high_paid_employees;

索引

SQL 中的索引透過在列上建立索引來提高查詢效能，從而實現更快的資料檢索。

-- Creating an index
CREATE INDEX idx_lastname ON employees(last_name);

-- Dropping an index
DROP INDEX idx_lastname;

交易

SQL 中的交易透過將 SQL 語句分組為原子單元來確保資料完整性，從而確保所有語句都成功執行或全部都不執行。

-- Beginning a transaction
BEGIN TRANSACTION;

-- Committing a transaction
COMMIT;

-- Rolling back a transaction
ROLLBACK;

儲存過程

SQL中的預存程序是儲存在資料庫中的預編譯的SQL程式碼，可以透過單一命令執行，通常用於封裝頻繁執行的任務

-- Creating a stored procedure
CREATE PROCEDURE get_employee (IN employee_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = employee_id;
END;

-- Executing a stored procedure
CALL get_employee(100);

-- Modifying a stored procedure
ALTER PROCEDURE get_employee (IN employee_id INT)
BEGIN
    SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = employee_id;
END;

-- Dropping a stored procedure
DROP PROCEDURE IF EXISTS get_employee;

備份與復原

SQL 中的備份和復原涉及建立資料庫備份以防止資料遺失，並在資料庫發生故障或損壞時還原它們。

-- Creating a full backup
BACKUP DATABASE dbname TO disk = 'path_to_backup';

-- Creating a differential backup
BACKUP DATABASE dbname TO disk = 'path_to_backup' WITH DIFFERENTIAL;

-- Creating a transaction log backup
BACKUP LOG dbname TO disk = 'path_to_backup';

-- Restoring from a backup
RESTORE DATABASE dbname FROM disk = 'path_to_backup';

SQL 方言和特定於供應商的擴展

雖然 SQL 是一種標準化語言，但不同的資料庫供應商已經實作了自己的擴充和方言，導致不同 RDBMS 之間 SQL 的編寫和執行方式略有不同。這些變更可能會影響使用特定資料庫系統的開發人員可用的語法、函數和特性。

例如，以下是特定於供應商的 SQL 方言和擴展的一些範例：

甲骨文SQL ：

• Oracle SQL 包括專有擴展，例如分層查詢、分析函數和用於資料建模的 MODEL 子句。

• 範例： SELECT CUBE(product, region) FROM sales; （CUBE 是 Oracle 特定的用於產生小計的擴充）

SQL Server（微軟） ：

• SQL Server 包含 T-SQL (Transact-SQL)，它透過串流控制語言建構、錯誤處理和流程程式設計建構等功能擴展了標準 SQL。

• 範例： SELECT ISNULL(column_name, 'default_value') FROM table_name; （ISNULL 是一個 T-SQL 函數）

MySQL ：

• MySQL 包括空間資料類型和函數、視窗函數以及用於正規表示式匹配的 REGEX 運算子等擴充。

• 範例： SELECT column_name REGEXP '^pattern' FROM table_name; （REGEXP 是 MySQL 特定的運算子）

PostgreSQL ：

• PostgreSQL 包括陣列、JSON 支援、全文搜尋和範圍資料類型等擴充。

• 範例： SELECT column_name || 'suffix' FROM table_name; （|| 是 PostgreSQL 特定的字串連接運算子）

所以最合乎邏輯的方法是：

1. 基礎知識：了解關聯式資料庫設計的基礎：實體、鍵、關係…。 。

2. SQL ：學習 SQL 作為獨立語言的基礎知識。

3. 選擇 RDBM ：選擇最適合您的 RDBMS，然後按照其所有特殊語法和結構來使用它。

結論

SQL 本身就是一個廣闊的領域，包含許多我們沒有涉及的概念。事務、備份和文件管理只是更廣泛範圍的幾個範例。然而，對於像我們這樣的後端工程師來說，我們所介紹的基礎知識足以作為 SQL 的基礎概述。

原文出處：https://dev.to/louaiboumediene/mastering-sql-comprehensive-guide-to-structured-query-language-4gh