SQL

By 小黑

Database

電腦如何儲存資料的？

- 一切資料都是數字

- 符號 => 用數字來表示（ASCII）

- 文字檔 => 一堆符號

- 圖片 => 一個記錄像素的矩陣

- 影片 => 一張張圖片

- 總之都是一堆存到檔案裡的符號

要如何存取資料？

- 一行一行讀取檔案的資料

- 然後把這些資料轉成程式語言裡的資料結構

- 這是最暴力簡單的方法，但有些隱藏問題

有好幾個人同時想改同個資料怎麼辦？

有人在年紀的欄位填了名字要如何發現？

不同語言、不同人的讀取方法不一樣怎解？

於是我們有了資料庫

- 資料庫是一種特別的程式，負責儲存、修改、刪除資料

- 資料庫裡的資料必須依照特定格式儲存

- 資料庫裡的資料都有其資料類型（解決第二個問題）

- 資料庫有幾種特殊方法來處理不同人同時使用的情況

（解決第一個問題）

- 資料庫使用一套共同的語言來讀寫資料（SQL）

資料庫格式

- 可以把資料庫的格式想像成一個試算表

- 試算表的欄位就是每筆資料的屬性

- 試算表的每一列（row）就是一筆資料（稱為 Record）

屬性

Record

SQL

- 如前面講的，SQL 是專門用來操控資料的語言

- 假設我們要查詢資料

- 新增資料

- 修改資料

- 刪除資料

-- 搜尋使用者名稱
SELECT user_name FROM users;

-- 新增一個球員
INSERT INTO players(name, age, position) VALUES('Luka Doncic', 26, 'PG');

-- 更新 "Aaron Wu" 這個人的帳戶金額
UPDATE accounts SET balance = 0.00 WHERE user_name = 'Aaron Wu';

-- 刪除 name 為 "Gura" 的 vtuber
DELETE FROM vtubers WHERE name = 'Gura';

Syntax

SQL 的語法可以分成三部分

Verb

(要執行的動作）

Target

（目標）

Condition

(條件）

-- 查詢評分超過 3.0 的電影名稱
SELECT movie_name FROM movies WHERE rate >= 3.0;

Table

就是試算表啦

CREATE TABLE workers(
  name text, 
  age integer
);

name	age

資料庫通常會有好幾個 Table

Primary Key - 身份證字號

- 假設剛剛出現兩個人名字跟年齡一樣怎麼辦？

- 可以給每個人一個特殊的 id

- 一個屬性若是 Primary Key ，則資料不會重複

name	age
葉子	15
葉子	15

name	age	id
葉子	15	1
葉子	15	2

CREATE TABLE workers (
  id INT PRIMARY KEY,
  name TEXT, 
  age INT
);

CREATE TABLE workers (
  id INT PRIMARY KEY AUTO INCREMENT, 
  name TEXT, 
  age INT
);

Foreign Key - 串聯 Table

- 假設有個 Table 叫 Users，有 name 和 id 兩個欄位

- 有另外一個 Table 叫 Movies ，記錄使用者喜歡的電影

- 這時就會用到 Foreign Key ，把 Movies 跟 Users 的 id 串起來

name	id
小黑	1
小白	2

name	user_id
三個傻瓜	1
教父	2

Foreign Key

Foreign Key - 串聯 Table

CREATE TABLE Users(
  id INT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE Movies(
  user_id INT PRIMARY KEY,
  name TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

DROP - 刪除 Table

-- 刪除 Users 這個 Table
DROP TABLE Users;

Constraints - 限定資料內容

CREATE TABLE KidsPlayroom (
	-- Unique ID for each kid
    KidID INT PRIMARY KEY, 
    -- Must have a name
    Name VARCHAR(50) NOT NULL,  
     -- Age must be 0-12
    Age INT CHECK (Age >= 0 AND Age <= 12),
    -- No duplicate emails
    Email VARCHAR(50) UNIQUE,   
    -- Default color is Blue
    FavoriteColor VARCHAR(20) DEFAULT 'Blue'
);

Query Data

從 XX 幹 XX 出來

-- 查詢評分超過 3.0 的電影名稱
SELECT movie_name FROM movies WHERE rate >= 3.0;

* - 拿所有 row 出來

-- 查詢所有工人的資料
SELECT * FROM workers;

DISTINCT - 篩不重複資料

- 假設你想知道建北電資所有人會寫的語言總數

- 不少人會的語言可能只有 C++、Python 之類的

- SELECT 的後面加 DISTINCT 可以顯示所有不重複的語言

-- 查詢所有建北電資社員會的語言
SELECT DISTINCT pl FROM CKFGISC_people;

WHERE - 條件

- 很顯然只查詢資料不夠，我們還得根據條件篩選

- WHERE 後面能加各種條件篩資料

- AND, OR, >, <, = 這些都能用

-- 查詢評分超過 3.0 的電影名稱
SELECT movie_name FROM movies WHERE rate >= 3.0;

Create Data

把 XX 塞到 XX 裡

-- 新增 Leon 這隻角色到角色名單裡
INSERT INTO 
  Brawlers(name, rarity, hp)
VALUES 
  ('Leon', 'Legendary', 200);

Update Data

將 XX 的 XX 欄位設成 XX

-- 把 employees 中 id = 192 的 row 的 last_name 設定為 Lopez
UPDATE employees
SET
  last_name = 'Lopez'
WHERE
  employee_id = 192;

Delete Data

刪除所有符合條件的 row

-- 刪除所有不及格的小孩
DELETE FROM children WHERE grade < 60;

Subquery

Subquery -多點條件篩選

- 當我們抓資料時，通常也會利用其他 Table 的資料篩選

- SQL 讓你可以一行內寫多個 Query ，利用不同 Table 的資料

-- 選擇薪資最高的員工名字和薪資
SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary = (
    SELECT
      MAX(salary)
    FROM
      employees
  );

JavaScript & SQLite

SQLite

- 一套資料庫系統，是類似系統中最簡單的

- 拿來寫小專案夠用，大一點的需要技巧

- 因為這東西真的很直覺，所以教這個

- 市面上最紅的資料庫是 MySQL ，供餐

安裝

- 如果你用 Mac or Linux，恭喜你不用裝

- Windows 先點這載

- 載完後解壓縮，然後把解壓縮完的資料夾加到環境變數

- 接著 Windows 打開 CMD、Mac 開 Terminal

- 輸入 sqlite3 有東西就代表裝對了

使用 SQLite

- 在終端機輸入 sqlite3 test.db

- 沒意外的話，你會進入 sqlite3 的界面

- 接著你就能開始輸入前面教的指令了

- 輸入 .schema 可以列出所有 Table

- .quit 可以退出

在 JS 裡用 SQLite

- 老樣子，開個新的資料夾（或者你要用上上禮拜的也行

- 初始化 npm，然後安裝 better-sqlite3

- 裝完看 package.json 有沒有 better-sqlite （有就對了）

npm init -y
npm i better-sqlite3

在 JS 裡用 SQLite

- 載 shows.db 放到資料夾

- 開一個 JS 檔，貼一下內容，存檔執行

;; 引用 better-sqlite3 裡的 Database class
const Database = require("better-sqlite3");

;; 建立一個 Database
const db = new Database("shows.db");

;; 輸出 ratings 中的第一項
console.log(db.prepare("SELECT * FROM ratings").all()[0]);

process.on('SIGINT', () => {
    db.close();
});

db.prepare()

- 準備一行要執行的 SQL Statement

;; 返回所有性別為男生的學生
db.prepare("SELECT * FROM students WHERE gender = ?").all('Male');

;; 新增一個學生
db.prepare("INSERT INTO students(name, gender) VALUES (?, ?)").run('Aaron', 'Male');

- 後面接 .all() 可返回所有 SELECT 的結果

- 後面接 .run() 可執行 SQL

Practice

Backend[1]: SQL

By Aaron Wu

SQL

By 小黑

目錄

Database

電腦如何儲存資料的？

要如何存取資料？

有好幾個人同時想改同個資料怎麼辦？

有人在年紀的欄位填了名字要如何發現？

不同語言、不同人的讀取方法不一樣怎解？

於是我們有了資料庫

資料庫格式

SQL

SQL

Syntax

SQL 的語法可以分成三部分

Table

就是試算表啦

資料庫通常會有好幾個 Table

Primary Key - 身份證字號

Foreign Key - 串聯 Table

Foreign Key - 串聯 Table

DROP - 刪除 Table

Constraints - 限定資料內容

Query Data

從 XX 幹 XX 出來

* - 拿所有 row 出來

DISTINCT - 篩不重複資料

WHERE - 條件

Create Data

把 XX 塞到 XX 裡

Update Data

將 XX 的 XX 欄位設成 XX

Delete Data

刪除所有符合條件的 row

Subquery

Subquery -多點條件篩選

JavaScript & SQLite

SQLite

安裝

使用 SQLite

在 JS 裡用 SQLite

在 JS 裡用 SQLite

db.prepare()

Practice

Backend[1]: SQL

More from Aaron Wu