---
# System prepended metadata

title: 資料庫和查詢指令
tags: [DataBase]

---

# 資料庫和查詢指令

###### tags: `DataBase`

資料庫系統=> 程式,專門處理資料的程式

讀寫分離, 讓資料庫的壓力不會那麼大,保證服務不會中斷
一般來說, 使用者通常是讀多寫少

(Replication:複製)
![](https://i.imgur.com/Wqzg9OO.png)



## 資料庫類型

為什麼要有資料庫？

1. 因為要存資料呀，存在資料庫比較方便，可以查詢、新增、刪除。
可以用指令操控資料庫。

2.要可以永久的保存保存資料


```
檔案叫 Database （就像是excel中的檔案一樣）
表格叫 Table
行叫 Column （像|）
列叫 Row (像中文一)
```


### 關聯式(大部分狀況)
ex: table和table之間透過'學號' 來設定關聯,存的東西是有固定的資料型態
實務上MySQL,Postgresql,MsSQL


### 非關聯式No SQL(不用改結構,可隨意增減資料)
原意是:not only SQL
ex: 存放的資料沒有固定型態,可以想像成存JSON資料進DB.
存大量的資料會用到的, 存程式log. 
實務上Mongodb.


![](https://i.imgur.com/MnvltSo.png)


## 操作
-  CLI => 用指令去操作資料庫

幾個常見指令 CRUD (Create, Read, Update, Delete)：

- 查詢 (Read）
```
SELECT phone FROM users WHERE name=peter
//從 users 裡面找到 name 是 peter 的那列，並把 phone 的值取出來
```
![](https://i.imgur.com/bvIJTsh.png)


```
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
靠資料庫幫你儲存這筆資料的時間點

![](https://i.imgur.com/wWPLPZW.png)

剛才有了一點小發現,如果要增加欄位
例如我要在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的表格不用更動. 
如果是一般的狀況,就要再重新製作表了

![](https://i.imgur.com/fqZU0JU.png)


## Join 
簡單來說,就是把表格串接起來拉
這部分會分成四個狀況
![](https://i.imgur.com/B2v85MX.png)


> 其實講那麼多拉,還不如直接把圖片貼出來

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: 取交集,若是無法取得交集,則資料還是會出現
![](https://i.imgur.com/9Nh7O4A.png)


3. RIGHTJOIN: 取交集,若是無法取得交集,則資料還是會出現
![](https://i.imgur.com/9BABqGx.png)

4. FULLOUTERJOIN: 取連集
![](https://i.imgur.com/zaph72A.png)




### 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
![](https://i.imgur.com/MNNq0VB.png)

2. 模糊搜尋 SELECT * FROM `user` WHERE username like '%o%'
![](https://i.imgur.com/QYnjLWz.png)


## 排序
> 其實這個我已經會了

```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. 
![](https://i.imgur.com/1JZt3BI.png)

原始要先建立這樣的關係
![](https://i.imgur.com/bFGWSgd.png)

### 語法

```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: 發生某事的時候通知我

主要用在我要儲存我曾經操作過的指令(老實說我有點懶＠＠)
我先把語法加進來. 
![](https://i.imgur.com/Ln4lGh3.png)
