# SQL常用語法

### select 語法

SELECT * FROM tableName;    --查詢所有欄位
SELECT column1, column2 FROM tableName; --指定欄位查詢

--條件查詢
--比較運算子: = > >= < <= <> !=

SELECT * FROM tableName WHERE 條件; 
SELECT * FROM tableName WHERE column1 >= 10 AND column2 = 'some_value';
SELECT * FROM tableName WHERE column1 = 10 OR column2 = 20;

--是否存在於預設清單內 IN and NOT IN
SELECT * FROM tableName WHERE column IN ('10', '20', '30');
SELECT * FROM tableName WHERE column NOT IN ('10', '20', '30');

--範圍查詢：使用 BETWEEN/NOT BETWEEN 子句查詢某個範圍內的資料
SELECT * FROM tableName WHERE column1 BETWEEN 10 AND 20;
SELECT * FROM tableName WHERE column1 NOT BETWEEN 10 AND 20;

--使用 DISTINCT 語法查詢不重複的資料
SELECT DISTINCT column1, column2, ... FROM table_name; 

SELECT column FROM tableName WHERE column LIKE %條件%; --模糊條件查詢

--排序查詢。若無輸入，預設 ASC
SELECT column1 FROM tableName ORDER BY column2 ASC/DESC;

--使用 OFFSET 子句設置查詢結果的偏移量，以實現分頁功能
--從第十筆開始撈五筆
SELECT * FROM tableName LIMIT 5 OFFSET 10;
SELECT * FROM tableName LIMIT 5; --限制查詢數量, 五筆

--NULL 值判斷：使用 IS NULL 或 IS NOT NULL 判斷欄位中是否為 NULL 值
SELECT * FROM tableName WHERE column1 IS NULL;
SELECT * FROM tableName WHERE column1 IS NOT NULL;

--分組聚合：使用 GROUP BY 子句將資料分組後進行聚合計算
--AVG, 平均
SELECT account, AVG(score) 
FROM user_score
WHERE 條件 --非必要
GROUP BY account;

SELECT account, AVG(score) 
FROM user_score 
WHERE subject = 'Math' 
GROUP BY account;

--取小數第二位，第一種寫法
SELECT account, ROUND(AVG(score)::numeric, 2) 
FROM user_score
GROUP BY account;

--取小數第二位，第二種寫法
SELECT account, ROUND(AVG(CAST(score AS numeric)), 2) 
FROM user_score 
GROUP BY account;

--SUM, 加總
SELECT account, SUM(score) 
FROM user_score 
WHERE 條件 --非必要
GROUP BY account;

SELECT account, SUM(score) 
FROM user_score 
WHERE subject = 'Math' 
GROUP BY account;

--MIN MAX 最小值，最大值
SELECT account, MAX(score), MIN(score) 
FROM user_score 
WHERE subject = 'Math' 
GROUP BY account;

--COUNT 查詢筆數
SELECT COUNT(*) FROM table1;    --計算總筆數
SELECT COUNT(column) FROM tableName;    --針對特定欄位計算
SELECT COUNT(DISTINCT column) FROM tableName;    --去除重複資料

--聯合查詢
SELECT columnName FROM tableName1 UNION SELECT columnName FROM tableName2;
--聯合查詢（去除重複資料）
SELECT columnName FROM tableName1 UNION DISTINCT SELECT columnName FROM tableName2。
--從多個表格中選擇資料：可以使用 JOIN 子句將多個表格中的資料連接起來
SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2;
--各種 join 範例
select a.account, a.name, b.subject, b.score from my_user2 a, user_score b where a.account = b.account;
select a.account, a.name, b.subject, b.score from my_user2 a inner join user_score b on a.account = b.account;
select a.account, a.name, b.subject, b.score from my_user2 a left outer join user_score b on a.account = b.account;
select a.account, a.name, b.subject, b.score from my_user2 a left join user_score b on a.account = b.account order by account asc;
select a.account, a.name, b.subject, b.score from my_user2 a right join user_score b on a.account = b.account order by account asc;

--子查詢：使用子查詢將一個查詢結果作為另一個查詢的輸入
SELECT * FROM table1 WHERE column1 = (SELECT column2 FROM table2);

--資料轉換：使用 CAST 或 CONVERT 函數將欄位中的資料轉換為不同的資料類型
SELECT CAST(column1 AS INTEGER) FROM table1;

--使用 HAVING 語法對分組結果進行篩選：
SELECT column1, column2, ... FROM table_name GROUP BY column1, column2, ... HAVING condition;

### insert 語法

--一般的 INSERT：
INSERT INTO mytable VALUES ('value1', 'value2', 'value3');	//不建議
INSERT INTO mytable (col1, col2, col3) VALUES ('value1', 'value2', 'value3');

--INSERT 選取另一個表格的資料：
INSERT INTO mytable (col1, col2, col3)
SELECT othercol1, othercol2, othercol3 FROM othertable WHERE condition;

--INSERT 多筆資料：
INSERT INTO mytable (col1, col2, col3) VALUES 
('value1', 'value2', 'value3'), 
('value4', 'value5', 'value6');

--INSERT 不存在的資料，如果已經存在就忽略：
INSERT INTO mytable (col1, col2, col3)
VALUES ('value1', 'value2', 'value3')
ON CONFLICT DO NOTHING;

--INSERT 已經存在的資料，如果已經存在就更新：
INSERT INTO mytable (col1, col2, col3)
VALUES ('value1', 'value2', 'value3')
ON CONFLICT (col1) 
DO UPDATE SET col2 = EXCLUDED.col2, col3 = EXCLUDED.col3;

--插入預設值
INSERT INTO mytable (id, name, age, address) 
    VALUES (1, 'John', DEFAULT, DEFAULT);

--插入預設值EXAMPLE
INSERT INTO my_user2 (account, password, name, test) 
    VALUES ('account123', 'passwd123', 'Johnny', DEFAULT);
INSERT INTO my_user2 (account, password, name) 
    VALUES ('account456', 'passwd456', 'Walker');

### update 語法

--更新單一欄位：
--這會將 my_table 表格中 id 為 1 的列的 column1 欄位更新為 'new_value'。
UPDATE my_table SET column1 = 'new_value' WHERE id = 1;

--更新多個欄位：
--這會將 my_table 表格中 id 為 1 的列的 column1 欄位更新為 'new_value1'，
--將 column2 欄位更新為 'new_value2'。
UPDATE my_table SET column1 = 'new_value1', column2 = 'new_value2' WHERE id = 1;

--更新符合條件的多個列：
--這會將 my_table 表格中所有 column2 欄位值為 'some_value' 的列
--的 column1 欄位更新為 'new_value'。
UPDATE my_table SET column1 = 'new_value' 
WHERE column2 = 'some_value';

--使用子查詢更新：
--這會將 my_table 表格中所有 id 值在子查詢中選出，
--且 other_table 表格中 column2 欄位值為 'some_value' 的列
--的 column1 欄位更新為 'new_value'。
UPDATE my_table SET column1 = 'new_value' 
WHERE id IN (SELECT id FROM other_table WHERE column2 = 'some_value');

UPDATE mytable SET column1 = 
(SELECT column2 FROM anothertable WHERE id = 1) WHERE id = 2;

--根據 JOIN 來更新欄位：
UPDATE mytable SET column1 = 'new_value'
FROM myothertable WHERE mytable.id = myothertable.id
AND myothertable.condition = true;

### delete 語法

--刪除特定行
DELETE FROM my_table
WHERE column1 = 'value1';

--刪除所有行
DELETE FROM my_table;

--刪除來自另一個表格的匹配行
DELETE FROM my_table
WHERE column1 IN (
  SELECT column1 FROM another_table WHERE column2 = 'value2'
);

--使用 JOIN 刪除來自多個表格的匹配行
DELETE my_table
FROM my_table
JOIN another_table ON my_table.column1 = another_table.column1
WHERE another_table.column2 = 'value2';

### DDL語法

--CREATE DATABASE
--創建一個名為mydb的新資料庫：
CREATE DATABASE mydb;

--ALTER DATABASE
--將mydb資料庫的擁有者更改為新擁有者new_owner：
ALTER DATABASE mydb OWNER TO new_owner;

--DROP DATABASE
--刪除mydb資料庫：
DROP DATABASE mydb;

--CREATE TABLE
--創建一個名為users的新表，該表包含id（主鍵）、name和age三個欄位：
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INTEGER
);

--ALTER TABLE
--將users表的age欄位修改為new_age，並更改其數據類型為BIGINT：
ALTER TABLE users
ALTER COLUMN age TYPE BIGINT
RENAME TO new_age;

--DROP TABLE
--刪除名為users的表：
DROP TABLE users;

--CREATE INDEX
--創建一個名為idx_name的索引，該索引適用於users表中的name欄位：
CREATE INDEX idx_name
ON users (name);

--DROP INDEX
--刪除名為idx_name的索引：
DROP INDEX idx_name;

### 其他補充

--字串連接
SELECT *, first_name || ' ' || last_name AS full_name
FROM customer;

--使用內建函數
    SELECT char_length(first_name), first_name    --字串長度
    FROM customer;

    SELECT upper(first_name), first_name    --轉大寫
    FROM customer;

    SELECT lower(first_name), first_name    --轉小寫
    FROM customer;

    --分割字串 (結果 hom, hom)
    SELECT substring('Thomas' from 2 for 3), substr('Thomas',2, 3);

    --查找字元 (結果 ab, de)
    SELECT left('abcde', 2), right('abcde', 2);

    --無條件捨去, 無條件進位, 四捨五入, 取小數第二位
    SELECT ceil(-42.8), floor(-42.8), round(12.45), round(12.54), trunc(12.8124, 2);

    --平方, 開根號, 立方根
    SELECT power(2, 3), sqrt(4), cbrt(27);

--清空指定的資料表
truncate tableName

--直接計算
select 2 + 3 as total;
select 3 % 2 as "mod";

--條件表示式
SELECT account, subject, score,
   CASE
       WHEN score >= 90 THEN 'A'
       WHEN score >= 80 THEN 'B'
       WHEN score >= 70 THEN 'C'
       WHEN score >= 60 THEN 'D'
       ELSE 'E'
   END AS grade
FROM user_score;