【DB學習筆記】SQL(Structured Query Language，結構化查詢語言) ─ (1)

Allison

30 min readJul 22, 2023

一、Relational Database(關聯式資料庫)

一、Relational Database(關聯式資料庫)

關聯式資料庫是一種以關聯(relationship)的方式來組織和存儲數據的資料庫。在關聯式資料庫中，數據以表格的形式存儲，每個表格包含多列記錄，每列記錄代表一個實體或事物，而每行則代表該實體的屬性或特徵。

關聯式資料庫使用SQL(Structured Query Language，結構化查詢語言)來進行資料的操作和查詢。通過使用SQL可以方便地添加、查詢、更新和刪除數據，並且可以根據需要建立索引和關聯來優化查詢效能。

關聯式資料庫的特點包括：

結構化：數據以表格的形式組織，每個表格都有預定義的欄位和資料型別。
關聯性：不同表格之間可以建立關聯，通過共享共同的欄位來聯繫相關數據。
資料完整性：可以通過約束(如主鍵、外鍵)來確保數據的完整性和一致性。
易於查詢：使用SQL語言進行查詢和操作數據，提供了強大的查詢功能。
可擴展性：可以根據需要增加新的表格和欄位來擴展資料庫。
跨平台：大多數關聯式資料庫都支援跨不同操作系統和程式語言的應用。

關聯式資料庫被廣泛應用於各種應用領域，包括企業業務系統、網站應用、金融服務、教育機構等，它是當今最常用的資料庫模型之一，提供了有效、可靠且易於管理的資料存儲和處理方案。

💡 No-SQL(非關聯式資料庫)：當資料不強調即時的正確性，如：社群軟體的按讚數、被分享數，就可以考慮使用No-SQL，No-SQL常用來處理高速及大量產生的數據。相較之下，什麼是即時且正確的數據呢？例如：銀行轉帳，假設A轉帳1000元給B，一般來說，當我們重整應用時，都能即時看到A少了1000元而B多了1000元，而不會出現A少了1000元但B卻沒有入帳1000元的紀錄(前提當然是轉帳手續等步驟正確)。如果應用需要處理大量非結構化或半結構化的資料，並且不強調即時正確性，那麼No-SQL資料庫可能是一個不錯的選擇。如果應用需要強調資料完整性、一致性和複雜的查詢處理，則SQL資料庫可能更適合。所以選擇資料庫時，並不是某種應用一定要使用某種類型的資料庫，而是需要根據具體的應用來考慮。
💡 參考：NoSQL是什麼？認識關聯式資料庫RDBMS和非關聯式資料庫NoSQL — ALPHA Camp、什麼是 NoSQL？| 非關聯式資料庫，彈性的結構描述資料模型 | AWS (amazon.com)

1. table(表)

在資料庫領域中，table(表)是某種特定類型資料的結構化清單。

舉例來說：

(1) 學校的資料庫：學生資料表、老師資料表、家長資料表

(2) 公司的資料庫：員工資料表、薪資級距表、部門資料表

(3) 商家的資料庫：買家資料表、產品資料表、訂單資料表、供應商資料表

💡 為了方便檢索和訪問，我們不應該將多張表放在同一張表中，如：學生資料表及家長資料表存在同一張表中。換句話說，每張表儲存的內容不應該過於繁雜，設計表格時應該考慮如何適當分離不同的資訊內容。

表本身具備一些特性，這些特性定義了資料在表中是如何儲存的，例如：儲存什麼樣的資料、欄位如何命名，描述這些特性的就是schema(架構)。

我們以學生資料表來看看表中的row(列)和column(行)是什麼意思，如：

在學生資料表中，每一列代表一名學生，每位學生有姓名、性別、生日、地址這四個資料欄位。有時也會稱row(列)為record(記錄)，不過row是較正式的術語。

行則是表中的單一欄位，每個行都有一種特定的datatype(資料類型)，例如：姓名是字串、性別是字串、生日是日期、地址是字串。基本上，我們可以根據具體的需求來分解資料，像是學生資料表中的地址雖然可以屬於同一行，但如果今天有個需求是根據縣市進行分類，你就要考慮將地址的欄位細節化，分為：縣市、地址，讓你在進行排列或篩選時更便捷。

任何想要儲存的資料都必須符合datatype的規定，正確的分類資料可以有效地利用磁碟空間，減少資料佔用存儲空間，並提高查詢和操作的效率。

常見的datatype如：

(1) varchar：可變長度的字串類型，最長255，會根據實際的資料長度動態分配空間。因此，優點是節省空間，缺點則是由於動態分配空間，導致速度較慢。例如：由於姓名長度不一，可考慮使用varchar。

(2) char：固定長度字串類型，最長255，不管實際的資料長度，皆會分配固定長度的空間去儲存資料。優點是不需要動態分配空間，速度較快，缺點是使用不當可能會導致空間浪費。例如：若性別長度固定，即可考慮使用char。

(3) int：數字中的整數型，最長11。

(4) bigint：數字中的長整型。

(5) float：單精度浮點型資料。

(6) double：雙精度浮點型資料。

(7) date：短日期類型，只包含年月日。

(8) datetime：長日期類型，包含年月日時分秒。

(9) clob(Character Large OBject)：字元大型物件，儲存如一篇文章、一段說明等長度超出255的字串，最多可以儲存4GB以內的資料。

(10) blob(Binary Large OBject)：二進位大物件，專門用來儲存圖片、聲音、影片等媒體資料。

💡 由於不同的資料型別佔用的存儲空間和處理速度可能不同，如果使用過大或不必要的資料型別，將佔用更多的磁碟空間，相對地，在查詢和操作資料時會浪費更多的資源；如果使用過小的資料型別，可能會造成數據截斷或溢出的問題，影響資料的完整性。因此，在設計資料庫結構時，選擇適當的資料型別是很重要的。例如：若一個欄位只需要存儲整數值，可以選擇使用整數類型(如INT或BIGINT)，而不是使用字串類型(如VARCHAR)，這樣可以節省空間並提高查詢效率。同時，還可以使用壓縮技術來進一步優化磁碟，例如使用資料壓縮功能來減少數據的存儲空間，或使用索引來加速查詢的速度。總結來說，選擇正確的資料型別對於提高系統性能和節省成本都非常重要。
💡 參考：資料型別 — PostgreSQL 正體中文使用手冊

2. primary key(主鍵)

我們知道表中的每一列都算是一筆記錄，如學生資料表中，每一列表示一名學生，但我們需要考慮的是該如何分辨出每一位學生都是唯一且獨立的，有人說看姓名辨識就好了，但如果有人重名怎麼辦？同理，使用其他欄位做辨識一樣會有同樣的問題發生，除非我們能保證這個欄位值具有唯一性，像是學生資料表中如果有個學號就能當作主鍵，或是我們也能設定自動生成的流水號ID作為主鍵。

表中的任一行，只要符合以下條件都能作為主鍵：

任意兩列都不具有相同的主鍵。例如：學號10002只有陳○婷同學，不會有另外一個同學的學號是10002。
每一列都必須具有一個主鍵值，且主鍵值不允許為空(null)。例如：學校內不會出現一個沒有學號的同學(即學號為空)。
主鍵行中的值不允許修改或更新。例如：陳○婷同學不能說她想將自己的學號改為10001或其他任意值。
主鍵值不能重用，即使某列資料從表中刪除，它的主鍵依然不能賦給之後新建的任何資料。例如：陳○婷同學從學校畢業後，即使她不會再出席學校的任何活動，我們也不能將她的學號10002給新生使用。

💡 主鍵可由一個或多個行組成，由一個行組成時，須確保該行具有唯一性；由多行組成時，雖然其中一行的值可以不是唯一的，但需保證多行主鍵排列組合後，其組合結果在各筆記錄之間不重複。

3. foreign key(外鍵)

既然我們已經瞭解到資料在資料庫中是存在於某張表中，那麼我們怎麼知道表與表之間的關聯性呢？答案就是透過外鍵。例如：我想知道王○明的家長是誰，但學生資料表上並沒有直接告訴我是誰，雖然有家長資料表，我卻沒有任何依據能夠知道哪位學生的家長是誰，這就是我們要設置外鍵的原因，透過外鍵，我們能建立起表與表之間的關係、知道表中每筆記錄之間的關係，還能確保我們填入的外鍵值是存在的：

上表中，我們能知道王○明學生的家長為張○雅，因為家長ID能讓我們將學生資料與家長資料比對並關聯。在這個例子中，也許還會有一位以上的學生家長是同一人，如：

可以試想，若我們沒有將學生與家長資料分開，這份表中一定會有重複的值，因此，分成兩張表會有以下好處：

家長資料不會重複，不會浪費存儲空間，建立新資料時也不需浪費時間重存一樣的訊息。
如果家長資料變動，只需要改動單筆記錄即可，不需要一一檢查修改。
因為資料不重複，能保證資料的一致性，使得處理資料和生成相關報表變得較容易。

💡 關聯式資料可以有效地存儲、方便處理。因此，比起非關聯式，關聯式具有更好的scale(擴展性)，scale指的是能夠適應日漸增加的工作量且不失敗，換句話說，系統能有效地應對增長的需求和負載，並在增加使用者、資料量或流量時仍保持高性能和穩定性，設計良好的資料庫或是應用程式則稱為scale well(良好擴展性)。

二、DB & RDBMS & SQL

本節會對常見的名詞做說明。

1. DB(Database，資料庫)

按照一定格式儲存資料的地方，可以將資料庫想像成是一個文件櫃，裡面存放了許多文件，而每份文件中記錄著特定格式的資料。

2. RDBMS(Relational Database Management System，關聯式資料庫管理系統)

專門用來管理資料庫中的資料，資料庫管理系統可以對資料庫中的記錄做增刪改查。常見的RDBMS如：MySQL、Oracle等等。

💡 DBMS：資料庫管理系統

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

SQL是一種用於操作關聯式資料庫的語言，我們可以對DBMS下SQL命令，DBMS會執行SQL命令並對資料庫內容進行管理。SQL包含了不同類型的命令，根據功能可以分為以下幾類：

(1) DQL(Data Query Language，資料查詢語言)

用於查詢資料，如：SELECT語句。

(2) DML(Data Manipulation Language，資料操作語言)

用於管理資料。

INSERT：將資料插入表中。
UPDATE：更新表中現有的資料。
DELETE：從表中刪除記錄。

(3) DDL(Data Definition Language，資料定義語言)

用於定義資料表的結構。這些命令主要是對資料庫物件的結構進行操作，而非操作表中的數據。資料庫物件可以指表、索引等等，這些都是資料庫中用於組織和管理資料的結構。

CREATE：用於創建資料庫或表。
DROP：用於從資料庫中刪除表。
ALTER：用於修改表的結構。

(4) DCL(Data Control Language，資料控制語言)

用於管理資料庫系統的權限。

GRANT：用於授予使用者訪問權限。
REVOKE：用於撤銷使用者訪問權限。

(5) TCL(Transaction Control Language，事務控制語言)

事務是一組任務的集合，每個事務都以特定任務開始，並在事務中的所有任務成功完成時結束，只要有一個任務失敗，則事務結果為失敗。因此，一個事務只會有兩個結果：成功或失敗。

BEGIN：開始一項事務。
COMMIT：提交(確認)事務。
ROLLBACK：在發生任何錯誤的情況下回滾事務。

4. SQL不兼容的原因

這邊討論的SQL不兼容指的是由於SQL標準版本差異、不同的DBMS實現、廠商擴展和資料類型等因素，導致SQL語句在不同系統上可能有不相容的語法或執行結果不同，如：

(1) SQL標準版本：由 ANSI (American National Standards Institute, 美國國國家標準局) 制定，新舊版間可能有不相容的語法。

(2) 不同的DBMS：每個DBMS實現的SQL語法都有些不同之處，導致SQL語句在不同系統上有兼容性問題。

(3) 廠商擴展：一些資料庫管理系統在SQL標準基礎上添加了自己的擴展功能或非標準語法，這些擴展可能在其他系統上無法正確執行，造成不兼容。

(4) 資料類型：雖然大多數基本類型都會獲得一致的支持，但許多高級資料類型並沒有，還有些同樣的資料類型在DBMS中具有不同的名稱。

我還想到之前在學習網頁開發時，使用的是ORM工具而非SQL原生語法，當時我還有點疑惑為何不直接用SQL語法，後來瞭解到ORM可以減少開發者某些困擾。因為ORM能在應用程式的程式碼和資料庫之間建立映射關係，將資料表和資料行映射成應用程式中的物件和屬性。而依不同的資料庫類型，會分別使用不同的映射技術，如：SQL → ORM (Object Relational Mapping，物件關聯映射)、No-SQL → ODM (Object Document Mapper，物件文件映射)。

以下是我使用ORM至今的一些心得，關於ORM工具的方便之處：

(1) 抽象化資料庫：ORM工具提供了統一的API來執行資料庫操作，它可以根據應用程式的設置，自動生成適用於不同資料庫的SQL語句，開發者不需要詳細瞭解DBMS的具體實現和操作細節。

(2) 易於維護：使用ORM可以使程式碼更加清晰和易於維護，開發者可以專注於應用程式的業務邏輯，而不必擔心SQL語句的細節和資料庫兼容性問題。

(3) 遷移和轉換：ORM工具通常提供了資料庫遷移和轉換的支援，使得在不同資料庫之間遷移和轉換資料更加容易和方便。

儘管ORM工具便於開發者專注在主要功能的開發上，但也需要注意選擇合適的ORM工具，並熟悉其使用方式。因為有些高度訂製的資料庫操作可能仍然需要直接使用原生SQL語句，因此開發者還是需要一定的SQL知識作為輔助。

三、MySQL

接下來我會整理一些命令，我是使用MySQL Workbench進行操作，如果本地沒安裝MySQL也可以使用線上版來玩玩看。

💡 SQL語句返回的內容一般是原始的、無格式的資料。在不同DBMS和客戶端上顯示的格式略有不同，如：對齊格式、小數位數等等，須留意格式問題並非檢索問題。網上也能找到許多線上版：
Online SQL Editor (programiz.com)
MySQL — OneCompiler — Write, run and share MySQL code online

我們會用員工資料表、薪資級距表、部門資料表來說明命令的使用方式：

程式碼如下：

-- 部門資料表
CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    dname VARCHAR(10),
    loc VARCHAR(10)
);
INSERT INTO dept (deptno,dname,loc) VALUES 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO '),
(40,'OPERATIONS','BOSTON');

-- 員工資料表
CREATE TABLE emp(
    empno INT PRIMARY KEY,
    ename VARCHAR(10),
    job VARCHAR(10),
    mgr INT,
    hiredate DATE,
    sal INT,
    comm INT,
    deptno INT,
    FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
INSERT INTO emp VALUES 
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 薪資級距表
CREATE TABLE salgrade(
    grade INT PRIMARY KEY,
    losal INT,
    hisal INT
);
INSERT INTO salgrade (grade,losal,hisal) VALUES 
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

四、資料庫操作命令

SHOW DATABASES; -- 查看目前共有哪些資料庫

SELECT DATABASE(); -- 查看目前使用的是哪個資料庫

USE company; -- 使用一個叫做company的資料庫

CREATE DATABASE db01; -- 創建一個叫做db01的資料庫

SHOW TABLES; -- 查看目前有哪些表

SELECT VERSION(); -- 查看目前的mysql版本

五、建表操作命令

1. 創建表

-- 創建部門資料表
CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    dname VARCHAR(10),
    loc VARCHAR(10)
);

-- 可以使用DEFAULT設定預設值
CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    dname VARCHAR(10),
    loc VARCHAR(10) DEFAULT 'TAIWAN'
);

2. 刪除表

DROP TABLE salgrade; -- 刪除叫做salgrade的表

DROP TABLE IF EXISTS t; -- 刪除不存在的表會報錯，可用IF EXISTS確認表的存在

DESC salgrade; -- 補充：查看表的結構

3. 插入資料

-- INSERT INTO 表名(欄位名1,欄位名2,欄位名3...) VALUES(值1,值2,值3);
INSERT INTO salgrade (grade,losal,hisal) VALUES (1,700,1200);

-- 省略欄位名
INSERT INTO salgrade VALUES (1,700,1200);

-- 插入多筆資料
INSERT INTO salgrade (grade,losal,hisal) VALUES 
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

4. 修改資料

-- UPDATE 表名 SET 欄位名1 = 值1, 欄位名2 = 值2, 欄位名3 = 值3... WHERE 條件;
UPDATE salgrade SET hisal = 5000 WHERE grade = 4;

-- 沒有新增條件的話，會導致所有資料都被更新。
UPDATE salgrade SET hisal = 5000;

5. 刪除資料

DELETE：屬於DML，刪除後可以回滾資料，因為資料並沒有真正地從儲存空間中刪除。缺點是刪除效率較TRUNCATE低。

BEGIN; -- 開始事務
DELETE FROM salgrade WHERE grade = 2; -- 刪除GRADE等於2的記錄
-- 如果後續有其他操作，可以在這裡進行
ROLLBACK; -- 回滾事務，撤消DELETE操作

-- 如果沒有寫條件的話，整張表會被刪除
DELETE FROM salgrade;

TRUNCATE：屬於DDL，刪除後不可回滾資料，儲存空間中不會留下任何被刪除的記錄。刪除效率比DELETE高，因為它是表被一次截斷。

TRUNCATE TABLE salgrade; -- 刪除整個資料表的內容

💡 DROP刪除整個資料表並移除其結構，DELETE刪除表中的紀錄但保留表的結構，TRUNCATE刪除表中的所有紀錄但保留表的結構。

六、查詢資料

使用SELECT進行查詢時，至少需要思考兩件事：1. 想選擇什麼資料、2. 從哪個地方選擇。

1. 搜尋操作

基本條件查詢：使用 * 來選取所有欄位雖然方便，但有時也可能不是最佳的寫法，特別是在處理大型資料表時，選擇只需要的欄位會更有效率，在實際應用中，應該根據需要僅選取必要的欄位，以減少資料傳輸的負擔。

-- SELECT 欄位名1, 欄位名2, 欄位名3... FROM 表名 WHERE 條件;
SELECT grade, losal FROM salgrade WHERE grade = 5;
-- 「*」稱為 "星號" 或 "萬用字元"(Wildcard)的符號，用於表示選取所有欄位的意思。
SELECT * FROM salgrade;

= 等於

-- 查詢薪資等於800的員工姓名和編號
SELECT empno, ename FROM emp WHERE sal = 800;
-- 查詢SMITH的薪資和編號
SELECT empno, sal FROM emp WHERE ename = 'SMITH'; -- 字串要用單引號包起來

<> 或 != 不等於

-- 查詢薪資不等於800的員工姓名和編號
SELECT empno, ename FROM emp WHERE sal <> 800;
SELECT empno, ename FROM emp WHERE sal != 800;

< 小於或 <= 小於等於

-- 查詢薪資小於等於3000的員工姓名和編號
SELECT empno, ename FROM emp WHERE sal <= 3000;

> 大於或 >= 大於等於

-- 查詢薪資大於等於3000的員工姓名和編號
SELECT empno, ename FROM emp WHERE sal >= 3000;

-- BETWEEN… AND… 兩個值之間，等同於 >= AND <=
-- 查詢薪資2450和3000之間的員工資料？(包括2450和3000)
SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 2450 AND 3000;

IS NULL 為空、IS NOT NULL 不為空

-- 查詢哪些員工的津貼為null
SELECT empno, ename, sal, comm FROM emp WHERE comm IS NULL;
-- 查詢哪些員工的津貼不為null
SELECT empno, ename, sal, comm FROM emp WHERE comm IS NOT NULL;

AND 並且、OR 或者

-- 查詢薪資大於2500且部門在10或20的員工資訊
SELECT * FROM emp WHERE sal > 2500 AND (deptno = 10 OR deptno = 20);
-- AND優先級高於OR，因此若要OR的部分先執行，請記得用小括號先括起來

IN 包含，相當於多個 OR(NOT IN不在這個範圍中)

-- 查詢職位是MANAGER和SALESMAN的員工
SELECT empno, ename, job FROM emp WHERE job = 'MANAGER' OR job = 'SALESMAN';
SELECT empno, ename, job FROM emp WHERE job IN('MANAGER', 'SALESMAN');
-- 注意：IN不是區間，而是一個具體的值

NOT 非

IS NULL → IS NOT NULL
IN → NOT IN

LIKE 模糊查詢，「%」匹配任意多個字符或者0個字符、「_」匹配任意一個字符

-- 找出名子含有O的員工
SELECT ename FROM emp WHERE ename LIKE '%O%';
-- 找出名子含有「_」，由於「_」是特殊符號，需使用轉譯字符「\」
SELECT ename FROM emp WHERE ename LIKE '%\_%';

DISTINCT 查詢結果去重

-- 單一欄位去重
SELECT DISTINCT job FROM emp;
-- 多重欄位去重，DISTINCT只能出現在所有欄位名的最前面
SELECT DISTINCT job, deptno FROM emp; -- 結果會是job和deptno排列組合再去重
-- 統計職位總數
SELECT COUNT (DISTINCT job) FROM emp;

2. 排序

ORDER BY

-- 查詢所有員工的薪資，默認是升序(asc)
SELECT ename, sal FROM emp ORDER BY sal;
-- 查詢所有員工的薪資，指定降序
SELECT ename, sal FROM emp ORDER BY sal DESC;
-- 查詢所有員工的薪資並排序，若薪資相同則使用名子排序
SELECT ename,sal FROM emp ORDER BY sal ASC, ename ASC;

3. 分頁

LIMIT 將查詢結果中的一部分取出，通常用在分頁查詢上

-- LIMIT (起始索引, 擷取長度)

-- 搜尋員工薪資並降序排列，只取前5筆資料
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 0, 5;
-- 若起始索引從0開始，還有省略寫法如下
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 5;
-- 搜尋員工薪資並降序排列，只取排名3~5的資料
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 2, 3;

-- 第1頁：LIMIT 0,3 [0 1 2] → 第2頁：LIMIT 3,3 [3 4 5] → 第3頁：LIMIT 6,3 [6 7 8]
-- 若每頁顯示pageSize筆資料，第pageNo頁：
LIMIT (pageNo - 1)*pageSize, pageSize
-- 還有另一種寫法如下，數值順序的意思和上面的是對調的，LIMIT一樣是限制筆數、OFFSET則是從哪筆開始
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 3 OFFSET 2;

💡 在不同的DBMS中，限制結果的語法有些不同。同理，在搜尋語法相關問題時一定要留意DBMS之間的差異，如：
(1) SQL Server：SELECT ename FROM emp TOP 5;
(2) DB2：SELECT ename FROM emp FETCH FIRST 5 ROWS ONLY;

七、函式

1. 常見的函式

LOWER：轉換為小寫。與之相對的是UPPER，轉換為大寫

SELECT LOWER(ename) FROM emp;
-- 可以取別名
SELECT LOWER(ename) AS ename FROM emp;

SUBSTR：擷取字串

-- SUBSTR(被擷取的欄位名, 起始索引, 擷取長度)
SELECT SUBSTR(ename, 1, 1) AS ename FROM emp;
-- 找出名子為A開頭的員工資訊
SELECT ename FROM emp WHERE ename LIKE 'A%'; -- 方法一
SELECT ename FROM emp WHERE SUBSTR(ename, 1, 1) = 'A'; -- 方法二

CONCAT：拼接字串

SELECT CONCAT(empno, ':', ename) FROM emp;

LENGTH：長度

SELECT LENGTH(ename) FROM emp;

TRIM：去除字串前後的空白格

-- 這樣搜尋不到任何結果
SELECT * FROM emp WHERE ename = ' KING';
-- 使用TRIM
SELECT * FROM emp WHERE ename = TRIM(' KING');

ROUND：四捨五入

-- ROUND(欄位名, 四捨五入至哪個位數)
SELECT ROUND(sal, 1) FROM emp; -- 四捨五入至小數點第一位，但本範例沒有小數位，所以結果看起來沒有變化
SELECT ROUND(sal, -3) FROM emp; -- 四捨五入：-1表示十位數、-2表示百位數、-3表示千位數，依此類推

RAND：隨機生成數字

SELECT RAND(); -- 隨機生成0~1之間的小數
SELECT ROUND(RAND()*100, 0); -- 隨機生成0~100之間的整數

IFNULL：處理值為NULL的欄位

SELECT ename, (sal+comm)*12 as yearSal FROM emp; -- 在資料庫中，只要有NULL參與運算，其結果皆為NULL
SELECT ename, (sal+IFNULL(comm, 0))*12 as yearSal FROM emp; -- IFNULL(欄位名, 被當作什麼值)

STR_TO_DATE

-- STR_TO_DATE('字串日期', '日期格式')
-- %Y 年、%m 月、%d 日、%h 時、%i 分、%s 秒

-- 舉例：emp表中的hiredate欄位型別是date，使用STR_TO_DATE將字串型別轉換為date
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902, STR_TO_DATE('10-12-1990', '%d-%m-%Y') ,800,NULL,20);

-- 如果字串中的日期格式剛好是'%Y-%m-%d'，則不須使用STR_TO_DATE(自動轉換)
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902, '1990-12/10' ,800,NULL,20)

-- 使用date_format將查詢結果格式化
SELECT ename, date_format(hiredate, '%Y/%m/%d') AS hiredate FROM emp;
SELECT ename, date_format(hiredate, '%Y年%m月%d日') AS hiredate FROM emp;

2. 分組函式

以下函式稱為分組函式，必須先分組才能使用，通常整張表會默認為一組。使用分組函式時有幾點注意事項：

自動忽略NULL，不需要提前對NULL進行處理
COUNT(*)和COUNT(欄位名)的差異：COUNT(*)只要是一列紀錄中的某行不為空，則計數加一；COUNT(欄位名)則是一列紀錄中的該欄位不為空，計數才加一
分組函式不能直接用在WHERE子句中，如：找出倒數第二的薪資是多少 → SELECT sal FROM emp WHERE sal > MIN(sal); → 會報錯

-- 找出最高薪資
SELECT MAX(sal) FROM emp;
-- 找出最低薪資
SELECT MIN(sal) FROM emp;
-- 計算薪資和
SELECT SUM(sal) FROM emp;
-- 計算平均薪資
SELECT AVG(sal) FROM emp;
-- 計算員工總數
SELECT COUNT(ename) FROM emp;

3. 分組查詢

GROUP BY：當該行命令有使用GROUP BY時，SELECT 後方只能接著參加分組的欄位名或分組函式，若加入除了這兩者以外的內容，即使DBMS有顯示相應的結果，多半也是沒有意義的。

-- 找出每個職位的工資和
SELECT job, SUM(sal) FROM emp GROUP BY job;
-- 找出每個部門的最高薪資
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
-- 加入一個comm欄位，mysql雖然有返回結果，但該數據是沒意義的
SELECT job, SUM(sal), comm FROM emp GROUP BY job;
-- 找出每個部門的每個職位的最高薪資 ─ 多個分組條件
SELECT deptno, job, MAX(sal) FROM emp GROUP BY deptno, job ORDER BY deptno;

HAVING：用來對已經分完組的數據再過濾，HAVING不能代替WHERE，且HAVING須和GROUP BY一起使用

-- 找出每個部門的平均薪資且其值高於2500的資料
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2500;
-- 優先選擇WHERE，除非WHERE無法完成再選擇HAVING ─ 找出最高薪資大於3000的部門
SELECT deptno, MAX(sal) FROM emp WHERE sal > 3000 GROUP BY deptno; -- WHERE
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal) > 3000; -- HAVING

這邊要補充SQL執行順序，目前為止，我們熟悉的寫法可能會長成這樣：

SELECT 選項 
FROM 表名 
WHERE 條件 
GROUP BY 分組 
HAVING 篩選 
ORDER BY 排序 
LIMIT 限制結果

然而，實際上的執行順序是：

FROM ... 
WHERE ... 
GROUP BY ... 
HAVING ... 
SELECT ... 
ORDER BY ... 
LIMIT ...

先從某張表查詢資料，再經過條件篩選出有意義的資料，並對這些資料分組，若有需要可以進一步篩選，接著挑出需要的資料項目，最後才是排序。舉例如下：

-- 找出平均薪資大於1500的職位，並排除MANAGER後，將結果降序排列
SELECT 
job, AVG(sal) AS avgsal 
FROM emp 
WHERE job <> 'MANAGER' 
GROUP BY job 
HAVING AVG(sal) > 1500 
ORDER BY avgsal DESC;

還記得上一小節提到，使用分組函式前一定要先分組嗎？報錯的原因就是執行WHERE時，GROUP BY根本還沒執行，導致我們無法在WHERE中使用分組函式：

-- 報錯：Error Code : 1111. Invalid use of group function
SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2500 GROUP BY deptno;

小心得：每次都覺得文章應該不會太長，寫著寫著才發現是幻覺(ง ͡ʘ ͜ʖ ͡ʘ)ง
有興趣繼續了解的人歡迎來看看下篇 ᖰʕ˘Ⱉ˘⠕ʔᖳ：【DB學習筆記】SQL(Structured Query Language，結構化查詢語言) ─ (2) | by Allison | Jul, 2023 | Medium

【DB學習筆記】SQL(Structured Query Language，結構化查詢語言) ─ (1)

一、Relational Database(關聯式資料庫)

1. table(表)

2. primary key(主鍵)

3. foreign key(外鍵)

二、DB & RDBMS & SQL

1. DB(Database，資料庫)

2. RDBMS(Relational Database Management System，關聯式資料庫管理系統)

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

4. SQL不兼容的原因

三、MySQL

四、資料庫操作命令

五、建表操作命令

1. 創建表

2. 刪除表

3. 插入資料

4. 修改資料

5. 刪除資料

六、查詢資料

1. 搜尋操作

2. 排序

3. 分頁

七、函式

1. 常見的函式

2. 分組函式

3. 分組查詢

Written by Allison