# 技術｜SQL 筆記

- 練習：https://sqliteviz.com/app/

## 查詢語法

-- this is comment

SELECT * FROM "table" LIMIT 5 OFFSET 10;
SELECT * FROM "table" WHERE id = "abc";
SELECT * FROM "table" WHERE id <> "abc";
SELECT * FROM "table" WHERE id like "ab%";
SELECT * FROM "table" WHERE score >= 80;
SELECT * FROM "table" ORDER BY id, name DESC;

SELECT ROUND(AVG(score)) AS average_score, SUM(score), MAX(score), MIN(score), count(score) from "table";
SELECT ROUND(AVG(score)) GROUP BY class ORDER BY class DESC;
SELECT ROUND(AVG(score)) GROUP BY class HAVING score >= 80 ORDER BY class DESC;

SELECT COUNT(DISTINCT(club)) FROM "table";
-- -- 順序 --
-- SELECT
-- FROM
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY
-- LIMIT

## 操作 table

CREATE TABLE "table2" (
id INT PRIMARY KEY,
name VARCHAR(15)
)

INSERT INTO "table2" (id, name) VALUES (1, "aaa"), (2, "bbb"), (3, "ccc");
UPDATE INTO "table2" SET name = "ddd" WHERE id = 3;
DELETE FROM "table2" WHERE id = 3;

DROP TABLE "table2";

## JOIN
- INNER JOIN 只會取得兩個資料表中 c 有匹配上的資料列
- LEFT OUTER JOIN 會取得所有左邊資料表所有的資料列，沒有匹配就補 NULL
- RIGHT OUTER JOIN 會取得所有右邊資料表的資料列，沒有匹配就補 NULL