[ 筆記 ] 後端基礎 - 資料庫、SQL 語法

後端環境建置

XAMPP 官網：https://www.apachefriends.org/zh_tw/index.html

後端三要素

1. 伺服器 Apache : 需要有一個伺服器來處理 Request 跟 Response
   • Server => 程式，專門處理 request & response 的程式
2. 寫程式 PHP : 需要寫程式來處理
   • PHP => 接收 request 處理成 output，通常是 html
3. 資料庫 MySQL : 需要有資料庫可以儲存資料
   • 資料庫系統 => 程式，專門處理有關資料的程式，提供一些更有效率查找、修改資料的方法。

資料庫系統

資料庫元件：

• 資料庫 => Database
• 資料表 => Table
• 欄位 => column

關聯式資料庫 Relational database

• 用不同的 table 去存取不同類型的內容，但各個 table 之間是有相關性的。
  好處是避免不相關的資料互相干擾。
• ex. 分數相關的資料存在分數的 table, 學生資訊的資料存在學生的 table ，兩個 table 之間都有 學號 這個欄位，可以用這個欄位來做關聯，藉由這份 學號 關聯，來獲取這位學生在所有 table 的資料。

關聯式資料語言 SQL （ Structured Query Language ）

• 是一種程式語言，專門拿來操作關聯式資料庫，簡單來說，就是用指令去操作資料庫。
  一般來說，是比較常用的資料庫系統。
• 比較有名的是 MySQL, PostgreSQL

非關聯式資料語言 NoSQL （ Not only SQL ）

相較於 SQL 只能儲存單一型態的資料， NoSQL 可以儲存的資料更複雜一些，比較常用於存 log 日誌，優點是比較彈性，如果要新增欄位，不用去更改資料庫設計。

• 比較有名的是 mongoldb

管理資料庫 phpMyAdmin

• 輸入此網址： http://localhost:8080/phpmyadmin
• 為一個用 PHP 寫的 GUI 資料庫管理軟體，其本質就是一個 PHP 檔案，用處是以網頁的介面來管理你的資料庫。
• 類似的資料庫管理軟體： sequel pro

Table schema 資料庫結構

資料型態

資料庫結構 Table schema 是在開一個資料庫前，要設定好資料型態、有無預設值、是否為唯一… 等等的設定，而之後的資料都要以符合當初設定的結構，否則無法新增。

• id : int
  • 勾選 ai (auto increment)
    • 保證遞增，但不保證 id 是連續的
  • 設定為 primary index，代表是唯一值
• 短的文字（ 如標題 ）可以用 varchar
  • 也可以限定長度（ 字元數 ）
• 長文可以用 text
• 日期 datetime
  • 預設值改成 current

varchar 跟 text 的差別？

• char ： 長度為 0 ~ 255，當儲存字串不夠 255 的長度時，會用空格補齊剩餘的空間，因此讀取時必須把後面空格去除。
• varchar： 可以設定最大長度，適合用在文字量少的欄位，可以有預設值。
• text： 不可設定長度，適合用在文字量多的欄位，最大长度为 2 ^ 31 - 1 個字符，不可以有預設值。

查詢速度：

• char 最快， varchar 次之，text 最慢。
• 由于 varchar 查询速度更快，所以能用 varchar 的时候就不用 text。

索引 index

資料庫系統會幫你建立某欄位的索引，目的是 加快搜尋的速度。

Index

• 建立索引需要額外的空間來存資料，所以通常只會幫 比較常用的欄位 建立索引。
• Index 也可以是一個複合欄位，例如把 username 跟 password 兩個常用欄位，建立成一個 index。

Primary Index (PK) 主鍵

• 一個 table 只能有一個 primary
• 不能為空值、不能重複，是 table 裡面最主要的欄位
• 通常會加在 id 上面
• 當你設置某欄位為 Primary index 時，該欄位會自動加上 unique index

Unique Index 唯一
唯一性，如果資料重複的話，系統會報錯

MySQL 基礎語法介紹

查詢 SELECT

• 某個 column ： SELECT + <column>
• 某個 table ： FROM + <table>
• 取出某個條件的那列 row ： WHERE + 條件 ( <column> = <value> )
• 也可以放兩個條件

where name = 'peter' and id=2 // => &
where name = 'peter' or id=2 // => ||

實際查詢案例：

select `id`, `content` from `users`
select * from `users`
select `phone` from `users` where `name` = 'peter'

刪除 DELETE

• 注意：操作 DELETE 指令之前，先以 SELECT 指令撈取要操作的資料，確保 WHERE 的資料是正確的

select * from `users` where `name` = `peter` //先確定 where 條件沒有錯

delete from `users` where `name` = 'peter' // 才可以刪除資料

• 若是使用者想刪除像「訂單」這種比較重要的資料，通常不會直接刪除，怕造成無法挽回的後果。 所以比較常見的是新稱一個 is_deleted 欄位，放一個 boolean 值，如果要刪除他就把此欄位設成 1。

更新 UPDATE

• 注意：操作 UPDATE 指令之前，先以 SELECT 指令撈取要操作的資料，確保 WHERE 的資料是正確的
• 某個 table ： UPDATE + <table>
• 設定某個欄位值 ： SET + <column>=<value>

select * from `users` where `name` = peter //先確定 where 條件沒有錯

update `users` set `phone` = 123, `age` = 20 where `name` = peter

新增 INSERT INTO

• 在某個 table 新增 某欄位 ： INSERT INTO + <table> + (<column1>, <column2>)
• 新增的值為 ： VALUES + (<value1>, <value2>)

insert into users(name, phone) values ('peter', 123)

資料庫 table 的命名規則

• 小寫
• 會加 s 表明是複數
• 用底線 _ 分開字詞（不是駝峰式命名）

內建函式

count 數量

• 計算 comment 這個 table 有幾個 id

SELECT COUNT(id) FROM 'comment'

SUM 總和

• 計算 comment 這個 table 的 id 數字總和

SELECT SUM(id) FROM 'comment'

AVG 平均

• 計算 comment 這個 table 的 id 數字平均

SELECT AVG(id) FROM 'comment'

CONCAT 合併欄位（ 字串連接 ）

• 合併 comment 這個 table 的 id & name 的值

SELECT CONCAT(id, name) FROM 'comment'

between 用在數字、日期上（ 條件篩選 ）

• 找出在某個區間的資料

where number between 2 & 5

in 有點像陣列的 indexOf

• 選出 id 等於 1 || 2 || 3 的欄位

SELECT * FROM 'comments' WHERE id in (1, 2, 3)

like 用在字串上（ 比對字元 ）

• %h : h 開頭的字串

• %h% : 任何包含 h 的字串

• 選出包含 o 字串的所有欄位

SELECT * FROM 'comments' WHERE content like '%o%'

ORDER BY 排序

• 升冪 ascending （小 => 大）

SELECT * FROM users ORDER BY time ASC

• 降冪 descending（大 => 小）

SELECT * FROM users ORDER BY time DESC

LIMIT

• 回傳前 30 筆資料

SELECT * FROM users LIMIT 30

OFFSET 起始點

• 回傳 61~90 筆資料 （ 從 60 開始、回傳 30 筆資料 ）

SELECT * FROM users LIMIT 30 OFFSET 60
SELECT * FROM users LIMIT 60, 30 // 跟上面一樣（OFFSET 60, LIMIT 30 的簡寫）

as 改名

• 用 as 關鍵字，後面接 <new-name>，用來縮減字詞還實用的

SELECT created_at as time, nickname as name FROM users

SELECT c.id, c.content, u.nickname as name
FROM comments as c, users as u
WHERE c.user_id = u.id

SQL Join 合併 table

圖片來源：SQL-liitokset visuaalisesti

• inner join 交集
• left join 左邊區塊 + 交集
  • 保存左邊，右邊有資料就放進來
• right join 右邊區塊 + 交集
  • 保存右邊，左邊有資料就放進來
• full outer join 連集