# 資料庫和查詢指令
###### tags: `DataBase`
資料庫系統=> 程式,專門處理資料的程式
讀寫分離, 讓資料庫的壓力不會那麼大,保證服務不會中斷
一般來說, 使用者通常是讀多寫少
(Replication:複製)
## 資料庫類型
為什麼要有資料庫？
1. 因為要存資料呀，存在資料庫比較方便，可以查詢、新增、刪除。
可以用指令操控資料庫。
2.要可以永久的保存保存資料
```
檔案叫 Database （就像是excel中的檔案一樣）
表格叫 Table
行叫 Column （像|）
列叫 Row (像中文一)
```
### 關聯式(大部分狀況)
ex: table和table之間透過'學號' 來設定關聯,存的東西是有固定的資料型態
實務上MySQL,Postgresql,MsSQL
### 非關聯式No SQL(不用改結構,可隨意增減資料)
原意是:not only SQL
ex: 存放的資料沒有固定型態,可以想像成存JSON資料進DB.
存大量的資料會用到的, 存程式log.
實務上Mongodb.
## 操作
- CLI => 用指令去操作資料庫
幾個常見指令 CRUD (Create, Read, Update, Delete)：
- 查詢 (Read）
```
SELECT phone FROM users WHERE name=peter
//從 users 裡面找到 name 是 peter 的那列，並把 phone 的值取出來
```
```
SELECT * FROM users
// * ＝所有。取出 users 裡面所有欄位的值
```
- 刪除 (Delete)
```
DELETE FROM users WHERE name=peter
//刪除 users 裡面 name 是 peter 那列
```
> 在BE101,補充得更深了
如果要刪除資訊,不一定非得用delete這個命令,可以在新增一個欄位(name= is_deleted),並設定屬性為boolean, 若是當前端的使用者刪除後,後端可以由0改為1,並且不顯示在前端頁面上,這樣可以確保重要的資料還是保存著,但是會造成資料庫的容量越來越大.
- 更新 (Update)
```
UPDATE users SET phone=123 WHERE name=peter
//更新 users 裡面 name 是 peter 的那列，把 phone 設成 123
```
- 新增 (Create)
```
INSERT INTO users(name, phone) VALUES (peter, 123)
//新增一筆記錄 name 是 peter，phone 是 123
//記得value裡面是要存入字串
```
- GUI => phpmyadmin , Adminer
==沒有phpmyadmin也可以用來操作資料庫,因為資料庫本身就只是一隻程式==
## PHP與資料庫連接
https://www.w3schools.com/php/php_mysql_select.asp
第一步:前置作業
```php=
//來判斷要抓什麼東西,算是一個前置作業
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mentor";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
```
[- select資料的狀況下](https://www.w3schools.com/php/php_mysql_select.asp)
```php=
$sql = "SELECT id,username from users ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()){
echo "id: " . $row["id"]. ', username:' . $row['username'] . '<br>';
}
} else {
echo "0 results";
}
```
-[ update資料的狀況底下](https://www.w3schools.com/php/php_mysql_update.asp)
```php=
//變數和變數之間是用逗號隔開
$sql = "UPDATE users SET username='norris' , content= 'string' WHERE id=11";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
```
## 用 Require 引入檔案
`require("conn.php")`
```php=
<?
require_once("conn.php");
?>
<h1>登入</h1>
<form action="/lidemy/login.php" method="POST">
username: <input name="username" />
password: <input name="password" type="password"/>
<input type="submit" />
</form>
<h1>註冊</h1>
<form action="/lidemy/login.php" method="POST">
username: <input name="username" />
password: <input name="password" type="password"/>
<input type="submit" />
</form>
```
新增一個conn.php 當作是把些前置作業都放進去
```php=
<?
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mentor";
$conn = new mysqli($servername, $username, $password, $dbname);
?>
```
程式導師實驗計畫：Lesson 5-1 之後端基礎
`1:04:33 ` 示範會員系統(包含註冊和登入功能)
## Schema 結構
其實沒什麼好講的,忘記了再看就好了
通常都會有名稱,儲存資料的屬性定義
比較值得注意的是,裡面的名稱可以設定成created_at
靠資料庫幫你儲存這筆資料的時間點
剛才有了一點小發現,如果要增加欄位
例如我要在username和created裡面增加content
旁邊側欄式可以先按索引,然後增加一個欄位,最後按執行
> 奇怪是我誤會了嗎? 我怎麼覺得這資料表很簡單 就熟悉度而已
做資料庫的第一步就是想這個schemma要怎麼建置,需要有哪些資料
例如說我要做一個會員系統,那我除了會員名單外,我還需要購物清單等資訊,需要一開始就先想好需要哪些table
## 正規化
原因:比較有彈性
可以參考這張圖片
我這邊來解說下
- 先看左上角的orders 中的 user_id,對到的是底下的users table
- 再看orders的 中的 id, 對到的是 order_product_id
- 再從prouduct_id 對到products name
簡單來講拉,這次huli買了mac但是下一次不會在買mac
買了watch 那我orders和users的表格不用更動.
如果是一般的狀況,就要再重新製作表了
## Join
簡單來說,就是把表格串接起來拉
這部分會分成四個狀況
> 其實講那麼多拉,還不如直接把圖片貼出來
1. innerJOIN: 取交集,若是無法取得交集,則交集不會出現
自己剛了大概試了一下
``` sql
SELECT comments.id, comments.content, users01.nickname FROM comments,users01 WHERE comments.user01_id = users01.id
// select 後面接的其實是要呈現出來的欄位
// where 後面接的是要合併的資料
別種表示方法如下:(這是比較常用的)
SELECT comments.id, comments.content, users01.nickname FROM comments JOIN users01 ON comments.user01_id = users01.id
// 簡寫(這個真的超實用的)
SELECT c.id, c.content, u.nickname FROM comments as c JOIN users01 as u ON c.user01_id = u.id
```
2. LEFTJOIN: 取交集,若是無法取得交集,則資料還是會出現
3. RIGHTJOIN: 取交集,若是無法取得交集,則資料還是會出現
4. FULLOUTERJOIN: 取連集
### table
1. 命名規則: 小寫/+s 代表是複數(通常都會是+s)
2. 用＿分開
## 索引
- 為了增進搜尋效率,就像書前面的目錄一樣
- 建立索引是需要時間和空間的,所以只會幫較常搜尋的建立
### Unique index 唯一
可以直接在結構裡面勾選,例如username這個column就適合勾選
因為遊戲玩家的帳號是無法重複的
### Primary key 主鍵
設了這個以後,就會在這個column加上唯一和索引. 通常都加在id.
只能有一個primary key.
## 函式
透過select的方式,`SELECT COUNT(id) FROM `user
就可以算出有幾筆資料,用法和excel一樣.
1. 字串連結.`SELECT CONCAT(id,username) FROM `user
2. 模糊搜尋 SELECT * FROM `user` WHERE username like '%o%'
## 排序
> 其實這個我已經會了
```sql
SELECT * FROM USERS ORDER BY time ASC
```
## 分頁
> 媽的我就說了吧,要用limit
```sql
//回傳前30筆資料
SELECT * FROM USERS LIMIT 30
//回傳前61~90筆資料
SELECT * FROM USERS LIMIT 30 OFFSET(跳過) 60
從第61筆資料開始取,取30個
//或是
SELECT * FROM USERS LIMIT 60, 30
```
limit要放在最後面才有效果
```sql
$sql = 'SELECT c.content, c.created_at , c.users_id , u.nickname , c.id FROM comments as c LEFT JOIN users as u ON c.users_id = u.id ORDER BY created_at DESC LIMIT 0, 3';
```
## 字串拼接太麻煩?
> 老實說,我他媽的不小心用出來了,我渾然不知
> 其實也沒多想,反正裡面要包住的東西,先用大括號包著,裡面的變數在用單引號包著.
```php=
$sql = "SELECT * FROM user where username = '$username' and password = '$password'";
```
[PHP裡單引號('')跟雙引號("")的差別](https://www.ptt.cc/bbs/PHP/M.1141277549.A.A95.html)
## Transaction 交易
把他想像成轉帳, a轉帳20給b, a帳面上少20, b帳面上多20
所以會有兩筆query同時發生
實際應用:
1. 轉帳
2. 購物
3. 一次牽扯到多個query的操作
4. 新增1000比資料,效率會比較好
為了保證transaction的正確性,所以要符合四種特性(ACID)
1. Atomicity（原子性）：一個事務（transaction）中的所有操作，或者全部完成，或者全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
2. Consistency（一致性）：維持資料的一致性(錢的總數不變)
3. Isolation（隔離性）：多筆交易不會互相影響(不能同時改一個值)
4. Durability（持久性）：交易成功後,寫入的資料不會不見
[延伸閱讀-知乎](https://www.zhihu.com/question/31346392)
相關語法:
```php
//每執行一次query,就幫你自動型成一次transaction
$conn->autocommit(FALSE);
$conn->begin_transaction();
//執行兩個query
$conn->query("update from money set amount = 20");
$conn->query("update from money set sum = 10");
$conn->commit();
```
### 型態
innoDB: 支援transaction和lock
MyISAM: 不支援
### LOCK
若是發生race condition, 就是會發生超賣, 所以要先lock住.
只有在transaction上才能用lock.
但是會有效能上的問題,因為要等先到的request處理完後,才會再往下處理.
lock可以針對整個table鎖起來,也可以針對單一個row
```PHP
$conn->autocommit(FALSE);
$conn->begin_transaction();
$conn->query("SELECT amount from products where id = 1 for update");
$conn->commit();
```
可以參考[這個](http://localhost:8080/week14/index.php?)
## VIEW(沒有人在管database,不要亂用)
就只是一張虛擬的table. 建立一個只讓人檢視的表格
做報表, 但是有些更細節的資訊不想給其他人看.
只想讓別人讀.
product有價錢, orders也有價錢 為什麼?
因為product和order的價格不一定會相等
~~product的價錢不能調整, 但是orders隨時可以調整(例如有折扣時)~~
因為products價格有可能修正，但order要存當下賣出的價格
join兩次,其實也沒什麼特別,只是原始的order table要先建立這個column不然會沒辦法join.
原始要先建立這樣的關係
### 語法
```php
create view order_detail as
SELECT o.id
FROM
ORDER as o
JOIN
users as u
ON
o.users_id = u.id
JOIN
product as p
ON
o.product_id = p.id
ORDER BY o.id DESC
```
### 缺點
view的結構無法改變,因為一開始建立的時候就寫死惹,如果要改變要在編輯的功能中, 直接變更query.
## Stored Procedure (沒有人在管database,不要亂用)
其實就是把常用的query存放在資料庫中,而不是寫在code中
就像是sql的function.
ex: select sum(price) from order detail.
另外一種是可以取代整個query
#### 語法
``` php
create procedure GetOrders(id, INT)
BEGIN
SELECT * FORM ORDERS WHERE user_id = id;
END
```
因為分號放在這邊會被誤認,所以要加上Delimiter //
```php
Delimiter //
create procedure GetOrders(id, INT)
BEGIN
SELECT * FORM ORDERS WHERE user_id = id;
END//
Delimiter ;
```
接下來就在sql裡面直接下
```php
call GetOrders(1)
```
### 缺點
因為把程序寫在資料庫裡面,所以debug的時候要來回跑,造成麻煩,不如直接寫進utilis中.
## triggers(觸發器)
git hook: 發生某事的時候通知我
主要用在我要儲存我曾經操作過的指令(老實說我有點懶＠＠)
我先把語法加進來.
Sign in with Wallet
Connect another wallet