Ict-創新/LPI/105.3
考生應該能夠使用基本 SQL 命令查詢資料庫和操作資料。此目標包括執行涉及兩個表連線和/或子查詢的查詢。
關鍵知識領域
- 使用基本 SQL 命令。
- 執行基本資料操作。
結構化查詢語言 (SQL) 是一種資料庫管理程式語言。SQL 是訪問資料庫的工具，更具體地說，是關係資料庫，可以與不同的資料庫產品一起使用。本章將幫助您學習使用這種語言進行基本資料庫管理。
Linux 中一些更常見的選擇包括以下內容
MySQL - 這種 SQL 實現由 Suns 擁有，並根據 GPL 釋出。大多數主要 Linux 發行版都將在其軟體包資料庫中包含 MySQL。
PostgreSQL - 釋出 在 BSD 許可下，PostgreSQL 從 Ingres 軟體演變而來。（PostgreSQL = post-Ingres SQL）。它作為大多數 Linux 發行版中的多個軟體包提供。
SQLite – 要將 SQL 作為庫實現，您需要 SQLite。SQLite 旨在為使用者和程式提供一種使用 SQL 介面在程式中儲存資料的方法。SQLite3 可用於為主要 Linux 發行版操作 SQLite 資料庫。
SQL 基礎
SQL 用於訪問關係資料庫。每個資料庫包含或多或少的表，這些表又包含或多或少的行和列。因此，單個行被視為具有由表列表示的特徵的獨立物件。要訪問表的 data，您首先必須連線到其資料庫。
1. mysql -u USERNAME -p PASSWORD 2. use DATABASE
SELECT
SELECT 語句從一個或多個數據庫表或資料庫檢視中檢索零行或多行。在大多數應用程式中，SELECT 是最常用的資料操作語言 (DML) 命令。由於 SQL 是一種宣告式程式語言，因此 SELECT 查詢指定了一個結果集，但沒有指定如何計算它。資料庫將查詢轉換為“查詢計劃”，該計劃可能在執行、資料庫版本和資料庫軟體之間有所不同。此功能稱為“查詢最佳化器”，因為它負責在適用約束範圍內找到查詢的最佳執行計劃。
SELECT 語句有許多可選子句
- WHERE 指定要檢索哪些行。
- GROUP BY 對共享屬性的行進行分組，以便可以對每個組應用聚合函式。
- HAVING 在 GROUP BY 子句定義的組中進行選擇。
- ORDER BY 指定返回行的順序。
示例
給定一個表 T，查詢 SELECT * FROM T 將導致顯示錶中所有行所有元素。
使用相同的表，查詢 SELECT C1 FROM T 將導致顯示錶中所有行中來自列 C1 的元素。
使用相同的表，查詢 SELECT * FROM T WHERE C1 = 1 將導致顯示列 C1 的值為 '1' 的所有行的所有元素。
WHERE
WHERE 子句指定 SQL 語句應該隻影響滿足指定條件的行。條件以謂詞的形式表示。WHERE 子句不是 SQL 語句的強制性子句，但應該用於限制 SQL DML 語句影響的行數或查詢返回的行數。
以下查詢僅返回表 mytable 中列 mycol 中的值大於 100 的那些行。
SELECT *
FROM mytable
WHERE mycol > 100
DISTINCT
DISTINCT 將從選擇中消除所有重複行。DISTINCT ON column 將消除指定列中的所有重複項；這等效於使用 GROUP BY column。ALL 將返回所有候選行，包括重複項。
SELECT DISTINCT jobTitle FROM employees;
+----------------------+
| jobTitle |
+----------------------+
| 總裁 |
| 銷售副總裁 |
| 營銷副總裁 |
| 銷售經理 |
(亞太地區) |
+----------------------+
GROUP BY
GROUP BY 子句用於將具有公共值的投影行投射到更小的行集中。GROUP BY 通常與 SQL 聚合函式一起使用或用於從結果集中消除重複行。WHERE 子句在 GROUP BY 子句之前應用。
以下示例演示了多個表、分組和聚合的查詢，透過返回書籍列表以及與每本書關聯的作者數量。
SELECT Book.title,
count(*) AS Authors
FROM Book JOIN Book_author
ON Book.isbn = Book_author.isbn
GROUP BY Book.title;
示例輸出可能類似於以下內容
標題 作者
---------------------- --------------- ---------
SQL 示例和指南 4
SQL 的樂趣 1
SQL 入門 2
SQL 的陷阱 1
HAVING
SQL 中的 HAVING 子句指定 SQL SELECT 語句應該只返回聚合值滿足指定條件的行。它被新增到 SQL 語言中，因為 WHERE 關鍵字不能與聚合函式一起使用。
要返回部門 ID 列表，這些 ID 在 2000 年 1 月 1 日的總銷售額超過 1000 美元，以及該日期的總銷售額
SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000
ORDER BY
ORDER BY 子句允許使用者指定他/她希望行根據 ASCending 或 DESCending 模式運算子排序。
SELECT * FROM Employees
ORDER BY LastName, FirstName
IN
IN 將查詢候選集中存在的任何值。
SELECT ename WHERE ename IN ('value1', 'value2', ...)
如果其值為候選值集之一，則所有行都匹配謂詞。這與以下行為相同
SELECT ename WHERE ename='value1' OR ename='value2'
除了後者可能允許比較多個列，而每個 IN 子句都不允許。對於大量的候選者，IN 的話語量較少。
BETWEEN
BETWEEN 將查詢範圍內存在的任何值。
SELECT ename WHERE ename BETWEEN 'value1' AND 'value2'
如果其值介於 'value1' 和 'value2' 之間（包括兩者），則所有行都匹配謂詞。
LIKE
LIKE 將查詢符合特定描述的字串。
- 結尾萬用字元
- 查詢以字母 'S' 開頭的任何字串
SELECT ename FROM emp WHERE ename LIKE 'S%';
- 開頭萬用字元
- 查詢以字母 'S' 結尾的任何字串
SELECT ename FROM emp WHERE ename LIKE '%S';
- 多個萬用字元
- 查詢任何包含字母 'S' 的字串
SELECT ename FROM emp WHERE ename LIKE '%S%';
- 單字元萬用字元
- 查詢任何包含字母 'A' 後面跟著任何單個字元，然後是字母 'E' 的字串
SELECT ename FROM emp WHERE ename LIKE '%A_E%';
SQL 程式設計師需要注意，LIKE 謂詞通常執行搜尋，而沒有索引的正常效能優勢。使用 '=', '<>' 等代替將提高效能。LIKE 謂詞的使用者應該注意，大小寫敏感性（例如，'S' 與 's'）可能根據資料庫產品或配置而有所不同。
UNION
在 SQL 中，UNION 子句將兩個 SQL 查詢的結果組合成一個包含所有匹配行的單表。這兩個查詢必須產生相同數量的列以及相容的資料型別才能合併。除非使用 UNION ALL，否則所有重複記錄都會自動刪除。
請注意，UNION 不保證行順序。來自第二個運算元的行可能出現在第一個運算元的行之前、之後或與之混合。在需要特定順序的情況下，必須使用 ORDER BY。
請注意，UNION ALL 可能比普通的 UNION 快得多。
執行此語句
SELECT * FROM sales2005
UNION
SELECT * FROM sales2006;
產生此結果集，儘管行的順序可能不同，因為沒有提供 ORDER BY 子句
請注意，喬有兩行，因為這些行在其列之間是不同的。亞歷克斯只有一行，因為這些行在兩列之間並不不同。
JOIN
SQL join 子句將資料庫中兩個或多個表中的記錄組合在一起。它建立一個可以儲存為表或按原樣使用的集合。JOIN 是一種透過使用每個表共有的值來組合兩個表中的欄位的方法。ANSI 標準 SQL 指定了四種類型的 JOIN：INNER、OUTER、LEFT 和 RIGHT。在特殊情況下，表（基本表、檢視或已連線表）可以在自連線中連線到自身。
程式設計師編寫 JOIN 謂詞來識別用於連線的記錄。如果評估的謂詞為真，則會以預期格式（記錄集或臨時表）生成組合記錄。
SELECT column_list FROM table_1 [INNER | LEFT | RIGHT] table_2 ON conditions_2 [INNER | LEFT | RIGHT] table_3 ON conditions_3 ... WHERE conditions
當您希望即使在其他表（table_2、table_3……）中沒有匹配項的情況下也檢索來自主表（table_11）的資料時，可以使用 LEFT JOIN。而 RIGHT JOIN 用於即使在主表中沒有匹配項的情況下也檢索來自所有其他表（table_2、table_3……）的資料。
INNER JOIN
內連線是應用程式中最常見的連線操作，可以被認為是預設的連線型別。內連線透過基於連線謂詞組合兩個表（A 和 B）的列值來建立一個新的結果表。查詢比較 A 中的每一行與 B 中的每一行，以找到滿足連線謂詞的所有行對。當連線謂詞滿足時，A 和 B 中每對匹配行的列值將組合成一個結果行。連線的結果可以定義為首先對錶中所有記錄進行笛卡爾積（或交叉連線）（將表 A 中的每條記錄與表 B 中的每條記錄組合在一起） - 然後返回所有滿足連線謂詞的記錄。當前的 SQL 實現通常使用其他方法，例如雜湊連線或排序合併連線（如果可能），因為計算笛卡爾積效率非常低。
SQL 指定了兩種不同的語法方式來表達連線：“顯式連線表示法”和“隱式連線表示法”。
“顯式連線表示法”使用 JOIN 關鍵字指定要連線的表，並使用 ON 關鍵字指定連線的謂詞，如以下示例所示
SELECT *
FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;
“隱式連線表示法”只是列出要連線的表（在 SELECT 語句的 FROM 子句中），使用逗號分隔它們。因此，它指定了交叉連線，並且 WHERE 子句可以應用額外的篩選謂詞（其功能類似於顯式表示法中的連線謂詞）。
插入
INSERT INTO table [ ( column [, ...] ) ]
{ VALUES ( expression [, ...] ) | SELECT query }
INSERT 允許您將新行插入表中。您可以一次插入一行或將查詢結果插入多行。目標列表中的列可以按任何順序排列。在目標列表中不存在的每個列中，將插入其預設值。如果列沒有宣告預設值，則將假定為 NULL。如果每列的表示式不是正確的資料型別，將嘗試進行自動型別強制轉換。
INSERT INTO tbl_movies(title,year) VALUES(‘Alexander’,’2004’)
更新
UPDATE table SET column = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]
UPDATE 更改滿足 condition 的所有行的指定列的值。只有要修改的列需要顯示為 column。陣列引用使用與 SELECT 中相同的語法。也就是說，單個數組元素、陣列元素範圍或整個陣列都可以用單個查詢替換。您必須對錶具有寫入訪問許可權才能對其進行修改，以及對 WHERE 條件中提到的任何表的讀取訪問許可權。
UPDATE tbl_movies SET genre = 'Dramatic' WHERE genre = 'Drama';
以下是使用過的檔案、術語和實用程式的部分列表。
- 插入
- 更新
- 選擇
- 刪除
- 從
- 哪裡
- 分組依據
- 排序依據
- 加入