🚩SQL的五十道練習 學習紀錄

• 🚩SQL的五十道練習 學習紀錄
  • ✏1.基本語法
  • ✏2.函數
    • 📍1 通用函數(作用於水平方向)
    • 📍2 聚合函數(作用於垂直方向)
  • ✏3.排序及篩選
    • 📍1 排序
    • 📍2 篩選
  • ✏4. 條件邏輯 (CASE語法)
  • ✏5. 分組聚合 (GROUP BY 語法)
  • ✏6. 子查詢&合併查詢
    • 📍1 子查詢
    • 📍2 垂直合併
    • 📍3 水平合併
  • ✏7.虛擬資料表
  • ✏8.建立更新與刪除資料表

---

✏1.基本語法

1. SELECT 選取關鍵字(欄位) 查詢{選取多個 a,b,c 用逗號隔開}
2. FROM 從哪個資料表
3. LIMIT限制顯示筆數

SELECT column_names AS NEW_NAME   
 FROM table_name         
 LIMIT 5;                  

DISTINCT 省略查詢結果中重複的資料，只顯示類別不會有重複的觀測值

SELECT DISTINCT confName AS distinct_conf_name
  FROM teams;                  

✏2.函數

📍1 通用函數(作用於水平方向)

• COALESCE : 將NULL替換成指定常數,或'字串'

SELECT COALESCE(column_name, '替代值') AS new_column
FROM teams;                

• ** 將空值替代成常數**

(空白明確表示該值被設定為空白，而 NULL 則表示未提供該值或該值未知)
1.透過NULLIF 將指定字符轉換成NULL

SELECT COALESCE(NULLIF(column_name, NULL), 'unknown') AS new_column
FROM;

2.透過when設定條件

• ROUND : 控制小數位數

ROUND(結果*1.0, '幾位小數') --*1.0為的是轉換為浮點數，以確保進行浮點數除法。

📍2 聚合函數(作用於垂直方向)

• AVG(X) 平均數
• COUNT(X) 資料筆數
• MAX(X) 最大值
• MIN(X) 最小值
• SUM(x) 總和

✏3.排序及篩選

📍1 排序

1. 以ORDER BY 排序查詢結果
2. ORDER BY ASC/DESC( ASC是遞增 /DESC是遞減，預設遞增)
3. 可以使用多個變數做排序
4. 結合 ORDER BY 與 LIMIT，就可以進行「前 m 高」或「前 m 低」的資料分析(資料沒有重複的前提下)

SELECT column_names
  FROM table_name
 ORDER BY column_names DESC
 LIMIT 5;   --找出前五名

📍2 篩選

1. WHERE : 能夠以條件（Conditions）作為篩選觀測值的依據

SELECT column_names
  FROM table_name
 WHERE X=2 OR X='關鍵字';   --字串型態記得加' '

2. 條件有多個的時候，必須使用邏輯運算符結合這些件
   • AND 結合條件的交集。
   • BETWEEN 結合數值比較條件的交集。
   • OR 結合條件的聯集。
   • IN 結合條件的聯集。(OR 結合比較條件在字串型態時，更推薦使用 IN)
   • NOT反轉真假

SELECT *
  FROM teams
 WHERE divName IN ('Atlantic', 'Pacific');  -- IN 結合條件的聯集

3. LIKE : 對文字類型的變數撰寫條件時，還能夠使用具備特徵比對（Pattern matching）性質

   使用 LIKE 比較運算符的時候需要搭配萬用字元（Wildcards）

   萬用字元	作用描述
   %	表示任意文字，包含空字串
   _	表示剛好一個文字

4. NULL 遺漏值（或稱空值）不適用基礎比較運算符，要判斷是否為遺漏值，必須使用 IS NULL 作為比較運算符

✏4. 條件邏輯 (CASE語法)

1. 使用 CASE 衍生計算欄位，能夠運用在篩選資料

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else
        END AS alias;

2. 如果在某個觀測值發生 condition_1 與 condition_2 都判斷為真的情況下，會以 result_1 表示，所以

當條件沒有互斥的時候，撰寫 CASE 要特別注意順序

3. 如果不想特別注意順序，可以將條件設計為互斥(suggest)

SELECT DISTINCT CASE WHEN heightMeters > 1.90 AND 
                          heightMeters <= 2.10 THEN 'Medium'
                     WHEN heightMeters > 2.10 THEN 'Tall'
                     ELSE 'Short' END AS height_category
  FROM players;

4. CASE 除了搭配 SELECT、ORDER BY 使用，亦能夠搭配 WHERE 使用

SELECT DISTINCT CASE WHEN pos IN ('C', 'C-F') THEN 'Center'
                     WHEN pos IN ('G', 'G-F') THEN 'Guard'
                     ELSE 'Forward' END AS pos_new,pos
  FROM players
 WHERE pos_new = 'Center';

✏5. 分組聚合 (GROUP BY 語法)

1. 分組 GROUP BY 的功能可以視為 DISTINCT 與 ORDER BY 兩者同時作用
2. 遇到種類很多又有重複時，想要計算個別平均，可以透過GROUP BY 就不用DISTINCT篩選出類別再分別計算

SELECT column_names
  FROM table_name
 GROUP BY column_names;

3. 若希望篩選具合過後的資料，可以使用作用在「分組聚合結果」的 HAVING。

SELECT column_names
  FROM table_name
 GROUP BY column_names
 HAVING conditions;

✏6. 子查詢&合併查詢

📍1 子查詢

📍2 垂直合併

📍3 水平合併

✏7.虛擬資料表

• 以 CREATE VIEW 建立虛擬資料表

​CREATE VIEW database_name.view_name (column_names)
​   AS
SELECT Statement;

建立完就可以做使用

• 虛擬資料表（Views），也被稱為檢視表
• 相較於使用過後即被捨棄的子查詢，虛擬資料表會被保存在資料庫中。
• 虛擬資料表就等同於資料表的存在。
• 實際上虛擬資料表中儲存的並不是資料，而是一段 SELECT 敘述。
• 以 DROP VIEW 即可刪除虛擬資料表

✏8.建立更新與刪除資料表

1. 以 CREATE TABLE 建立資料表

CREATE TABLE database_name.table_name (
    column_names COLUMN_TYPES
);

• EX.

CREATE TABLE [dbo].[options](
[Cat] [nvarchar](50) NOT NULL, --nvarchar要定義資料長度
[ID] [int]  NOT NULL,
[ItemName] [nvarchar](50) NOT NULL,
[Val] [nvarchar](50) NOT NULL,
[updateTime] [date] NOT NULL,
[RecordID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

2. 以 INSERT INTO 建立觀測值

實務上先檢查所有欄位及資料準備就緒無須增減後，再INSERT為佳

INSERT INTO database.table_name (column_names)
VALUES 
       (observations);

• EX.

insert into Weightlifting(updateTime,RegYear,GamesName,SchoolCode)
values('2023-11-06','2017','全國中等學校運動會','064532',')

3. 以 UPDATE SET WHERE 更新觀測值

UPDATE database_name.table_name
   SET update_columns = update_values
 WHERE conditions;

5. 以 DELETE FROM 刪除觀測值

UPDATE database_name.table_name
   SET update_columns = update_values
 WHERE conditions;

若刪除資料表的「所有」觀測值

DELETE FROM test.favorite_movies;

6. 以 DROP TABLE 刪除資料表