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


Posted by krebikshaw on 2020-07-27

後端環境建置

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 連集

#SQL







Related Posts

Leetcode 刷題 pattern - Top K elements

Leetcode 刷題 pattern - Top K elements

[Golang] Reflect

[Golang] Reflect

Gatsby初試啼聲

Gatsby初試啼聲


Comments