在進行資料收集的過程中，大部分的人應該都有聽過資料庫。資料庫基本上是一種資料存取的界面， 存取資料需要透過一種名為 SQL 的語法，我們透過程式語言撰寫 SQL 語法來對資料庫內的資料進行存取這樣。 所以，所謂的 SQL 伺服器，基本上就是提供一個 SQL 存取界面的軟體就是了。在 Linux 上常見的資料庫軟體， 大體上有 Mariadb 及 PostgreSQL 我們這裡介紹的是 Mariadb 系統。

既然是 Mariadb 為何標題上面寫的是 MySQL 呢？這是因為 MySQL 在某一段時間被商業企業所併購，所以不能再是自由軟體， 因此由 MySQL 尚未被併購之前所衍生出來的版本，就稱為 Mariadb，這是因為 MySQL 商標已經被商業企業所擁有， 不能再使用的緣故。除了軟體名稱並不相同之外，其他的指令等，則沒有任何變化。

• 在樹莓派上面安裝 Maraidb 軟體

Mariadb 分為數個部份，我們比較需要的其實僅有 mariadb-server 與 mariadb-client 兩部份，先來查詢一下， 目前有沒有 mariadb 的關鍵字軟體？找到的話，就直接安裝吧！

$ apt-cache --names-only search mariadb
....
mariadb-client - MariaDB database client (metapackage depending on the latest version)
...
mariadb-server - MariaDB database server (metapackage depending on the latest version)
....

$ sudo apt-get -y install mariadb-client mariadb-server

# 安裝完畢之後，檢查一下伺服器軟體狀態！
$ systemctl status mariadb
● mariadb.service - MariaDB 10.5.15 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2022-11-06 13:43:35 CST; 1min 2s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 8281 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld
    ....
   Main PID: 8331 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 9 (limit: 779)
        CPU: 1.857s
     CGroup: /system.slice/mariadb.service

# 測試一下，一般用戶是不能登入的！
$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

# 管理員身份才可以不用密碼登入！
$ sudo mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit

Mariadb 有自己的帳號/密碼系統，與樹莓派上面的帳號無關。而 Mariadb 裡面最重要的帳號，就是 root 管理員。 一開始的管理員 root 登入是不需要密碼的！這樣很恐怖！所以，我們可以使用底下的方式讓 Mariadb 變得稍微安全一點！ 至少讓 root 的登入強迫需要密碼才好！

$ sudo mysql_secure_installation
Enter current password for root (enter for none):  <==按下 [enter] 就好

Switch to unix_socket authentication [Y/n] <==按下 [enter] 就好

Change the root password? [Y/n] <==按下 [enter] 就好
New password:           <==暫時輸入 2727175
Re-enter new password:  <==暫時輸入 2727175

Remove anonymous users? [Y/n] <==按下 [enter] 就好

Disallow root login remotely? [Y/n] <==按下 [enter] 就好

Remove test database and access to it? [Y/n] <==按下 [enter] 就好

Reload privilege tables now? [Y/n] <==按下 [enter] 就好

$ mysql -u root -p
Enter password:  <==這裡就得要輸入 2727175 喔！
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit

這樣，你就可以用你的樹莓派一般帳號來使用 Maraidb 內的 root 管理員帳號來管理資料庫系統了！

• 建立個人資料庫與所需要的管理帳號/密碼

使用 root 管理你的 Mariadb 實在不是好想法！因此，我們需要建立使用者自己的帳號密碼，並且擁有自己的資料庫， 這樣就讓每個人擁有獨自的資料庫環境。底下以 rasppi 這個帳號為例，你要使用這樣的方式連線到你系統的 MySQL， 基本上應該會有這些資料：

• 伺服器名稱： localhost (大家都一樣)
• 資料庫名稱： rasppidb
• 使用者名稱： rasppi
• 使用者密碼： rasppipwd

底下就讓我們一步一步處理上面所需要的環境：

# 1. 先登入 MySQL，然後建立資料庫！
$ mysql -u root -p
Enter password:

MariaDB [(none)]> create database rasppidb;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rasppidb           |
+--------------------+
4 rows in set (0.003 sec)

# 2. 建立帳號與密碼
MariaDB [(none)]> create user rasppi@localhost identified by 'rasppipw';

# 3. 將資料庫使用權交給 rasppi 
MariaDB [(none)]> grant all privileges on rasppidb.* to rasppi@localhost;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit

這樣就完成自己資料庫的使用狀態了！現在，讓我們嘗試用自己的資訊登入看看！

$ mysql -u rasppi -p
Enter password:  <輸入你的密碼

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| rasppidb           |
+--------------------+
2 rows in set (0.002 sec)

MariaDB [(none)]> use rasppidb
Database changed
MariaDB [rasppidb]> exit

這樣就建置好你個人專屬的帳號密碼資料了！

資料庫單純就是建立一個空間 (有點像是目錄的概念)，然後，接下來的重點是，你要放置些什麼資料到這個空間中！ 這就得要使用所謂的資料表 (table)，資料表其實有點像是 excel 表格當中的工作表，只是， 在這個工作表 (table) 當中，你必須要指定好，每一個直行內的資料格式是什麼，這樣接下來， 才有辦法將資訊一欄一欄的加入工作表當中！

• 建立資料表的基本語法

建立資料表的語法有點像底下這樣：

create table 表格名 (
	欄位名  資料類型 (長度),
	欄位名  資料類型 (長度),
	欄位名  資料類型 (長度),
	...
	primary key (欄位名)
);

欄位名我們大概都知道是什麼，資料類型就有點像是整數、浮點數、字串文字等，長度則是規範可能的資料長短。 詳細的基本資料類型大概有這些：

• TEXT (文字)：主要有底下幾種類型：
  • character：固定長度字元，最大到 255
  • varchar：可變長度字元，最大到 255
  • text：不限長度字元
  • longtext：超長字元
• 數值方面的類型主要有底下這幾種：
  • tinyint：從 -128~127 或 0~255
  • samllint：從 -32768~3277
  • integer (int)：整數型態，從 -2147483648~2147483647
  • float：單精確浮點數：
  • double：倍精確度浮點數
• 可儲存特別資料類型：(例如圖片、聲音、影片等資料！)
  • blob：最大佔用 64K 大小左右
  • longblob：最大佔 4G 大小左右
• 日期格式：
  • date：YYYY-MM-DD的格式
  • datetime：YYYY-MM-DD hh:mm:ss的格式
  • timestamp：與 datetime 相似，但可以使用目前的時間帶入！
• 其他的類型：
  • 座標資料等

另外，在建立資料表內的各個欄位時，每個欄位的資料類型還能加上某些特別的屬性，包括要不要有空值？是否要自動累加數值等等：

• signed, unsigned：是否允許負值？signed 為允許負值的存在。
• auto_increment：該欄位的數值會自動的增加，通常用在整數類型！
• null, no null：是否允許欄位中不填入資料，null 為允許。
• default 'value'：若沒有指定，則填入此預設值。
• primarykey：可以加強索引功能的主鍵

舉例來說，建立一個如下的資料表格，你應該如何處理？

建立的方式有點像這樣：

MariaDB [rasppidb]> create table `users` (
    -> `id` int auto_increment not null,
    -> `login_name` varchar(40) not null,
    -> `login_pass` varchar(64) not null,
    -> `realname` varchar(40) not null,
    -> `u_email` varchar(40) null,
    -> `u_regd` timestamp not null default current_timestamp,
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.041 sec)

MariaDB [rasppidb]> show tables;
+--------------------+
| Tables_in_rasppidb |
+--------------------+
| users              |
+--------------------+
1 row in set (0.002 sec)

MariaDB [rasppidb]> describe users;
+------------+-------------+------+-----+---------------------+----------------+
| Field      | Type        | Null | Key | Default             | Extra          |
+------------+-------------+------+-----+---------------------+----------------+
| id         | int(11)     | NO   | PRI | NULL                | auto_increment |
| login_name | varchar(40) | NO   |     | NULL                |                |
| login_pass | varchar(64) | NO   |     | NULL                |                |
| realname   | varchar(40) | NO   |     | NULL                |                |
| u_email    | varchar(40) | YES  |     | NULL                |                |
| u_regd     | timestamp   | NO   |     | current_timestamp() |                |
+------------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.007 sec)

MariaDB [rasppidb]> exit

這樣就做好了一個資料表了！如果覺得資料表做錯，想要刪除，可以『 drop table users; 』即可！

現在，讓我們使用一個 python 的腳本來進行資料的寫入。會使用 python 是因為， 很多時候我們會透過非資料庫的程式進行資料的存取，例如在台灣很常見的 PHP 網頁程式語言， 這時，就得要透過一個介接程式來進行資料庫的連線，也就是需要一個外部程式來進行『 mysql -u rasppi -p 』這樣的功能！ 否則，就無法寫入/讀出資料了。

• 使用 mysql-connector-python 模組 (2022/11)

要使用 python 直接進行資料庫的存取，就需要上面寫到的『 mysql -u rasppi -p 』這樣的功能！ 這時就得要安裝 mysql-connector-python 模組才行！

$ sudo pip install mysql-connector-python==8.0.29

因為新版 (2022/11, 8.0.31) 會有中文 utf8 編碼的錯誤，建議回到 8.0.29 版，語系變更沒這麼大，使用上會比較簡單！ 之後，開始來確認一下能不能連結到你的資料庫？這樣做一下：

$ vim mysql-1.py
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="rasppi",
  password="rasppipw",
  database="rasppidb"
)

print(mydb)

$ python mysql-1.py
<mysql.connector.connection.MySQLConnection object at 0x7fb4e3dbb0>

請將上述用戶、密碼與資料庫名稱改掉，主機名稱則統一都是 localhost 喔！之後直接執行，就可以看到一個物件的生成！ 這個 mydb 物件，就是可以連結到資料庫的物件，等等我們就會用這個 mydb 物件進行各項新增/查詢/刪除/更新的行為喔！ 所以，這裡一定要出現一個 object，如果沒有出現，那就是連結不上了！

• 插入資料到資料表當中

接下來，我們需要將資料塞入到資料表當中，再次回想一下，我們的 users 資料表當中，有幾個資料是一定要有的，那就是：

• login_name
• login_pass
• realname
• u_email

其他兩個欄位 (id, u_regd) 系統可以自動生成，就不需要管它。現在，讓我們來寫另外一隻腳本，可以讓你的資料寫入到資料表中！

$ vim mysql-2.py
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="rasppi",
  password="rasppipw",
  database="rasppidb"
)

# 目前資料表所在位置
mycursor = mydb.cursor()

# 資料寫入的 SQL 語法！
sql = "insert into users ( login_name, login_pass, realname, u_email ) \
       values ( %s, %s, %s, %s )"
val = ( "rasppi", "thisisabook", "VBird Tsai", "user@some.server" )

# 在目前的資料表所在位置執行底下的 SQL 與數據
mycursor.execute(sql, val)

# 送出執行結果
mydb.commit()

# 列出這次執行的數據處理結果！
print(mycursor.rowcount, "筆資料已匯入")

$ python mysql-2.py
1 筆資料已匯入

• 從資料表當中讀取全部資料

那如何讀取資料庫中的資料呢？就得要透過 select 來處理了。讀取資料的方式，會先將所有的資料讀出來後， 變成一個陣列，然後我們得要從陣列當中將資料列出來即可。

$ vim mysql-4.py
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="rasppi",
  password="rasppipw",
  database="rasppidb"
)

mycursor = mydb.cursor()

# 基本語法
sql = "select * from users"
mycursor.execute(sql)

# myres 是個陣列，該陣列會讀入所有的資料 (fetchall())
myres = mycursor.fetchall()

# 最後，透過迴圈，將資料一列一列印出來
for x in myres:
    print(x)

$ python mysql-4.py
(1, 'rasppi', 'thisisabook', 'VBird Tsai', 'user@some.server', datetime.datetime(2022,
(2, 'rasppi', 'thisisabook', 'VBird Tsai', 'user@some.server', datetime.datetime(2022,

鳥哥不小心丟了兩次以上的插入資料，因此如上所示，就會出現多筆紀錄了。要注意的是， select (選擇) 的語法中， 如果每個欄位都要列出來，就用星號 (*)，如果想要特定欄位，那就直接將欄位資料取代 * 即可！ 例如底下的方式中，只列出序號、帳號、真實姓名與註冊時間時，就可以這樣做：

$ cp mysql-4.py mysql-5.py
$ vim mysql-5.py
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="rasppi",
  password="rasppipw",
  database="rasppidb"
)

mycursor = mydb.cursor()

# 基本語法
sql = "select id, login_name, realname, u_regd from users"
mycursor.execute(sql)

# myres 是個陣列，該陣列會讀入所有的資料 (fetchall())
myres = mycursor.fetchall()

# 最後，透過迴圈，將資料一列一列印出來
for x in myres:
    print(x)

$ python mysql-5.py
(1, 'rasppi', 'VBird Tsai', datetime.datetime(2022, 11, 14, 20, 48, 41))
(2, 'rasppi', 'VBird Tsai', datetime.datetime(2022, 11, 14, 20, 48, 53))

• 從資料表當中讀取『特定欄位』資料

當執行 mysql-4.py 時，輸出的資料有多筆紀錄。那如果你只想要列出第 2 筆資料時，該資料的特徵其實僅有 id 為 2 這個項目而已。這個時候，你可以使用『 where 條件 』的方式來處理喔！方式很簡單， 透過如下的功能即可處理：

$ cp mysql-5.py mysql-7.py
$ vim mysql-7.py
....
# 基本語法
sql = "select id, login_name, realname, u_regd from users where id = 2"
....

$ python mysql-7.py
(2, 'rasppi', 'VBird Tsai', datetime.datetime(2022, 11, 14, 20, 48, 53))

你需要注意的是， where 後面的欄位與數值，需要有數值型態的概念！因為 id 是整數，因此不需要加上引號， 如果你是想要抓帳號名稱，那就得要使用類似『 where login_name = 'rasppi' 』之類的方式來處理！

資料庫內的資料既然有插入與搜尋，當然也會有更新與刪除啊！底下就讓我們來使用看看。

• 更新資料

更新資料的 SQL 使用的是 update 的語法，update 的語法可以透過 set 去設定好需要變更的欄位。 假設我們要將 users 的第 2 筆資料修改，那就得要這樣做才行：

$ vim mysql-9.py
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="rasppi",
  password="rasppipw",
  database="rasppidb"
)

mycursor = mydb.cursor()

# 這裡是進行資料更新
sql = "update users set login_name = 'melody', realname = 'Melody Liu' where id = 2"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 筆數據更新")

# 做完更新之後，立刻呼叫出來看看結果正確否！
sql = "select id, login_name, realname from users"
mycursor.execute(sql)
myres = mycursor.fetchall()

for x in myres:
    print(x)

$ python mysql-9.py
0  筆數據更新
(1, 'rasppi', 'VBird Tsai')
(2, 'melody', 'Melody Liu')
...

可以看到第 2 筆數據明顯的更改了喔！

• 刪除資料

要刪除某一筆資料，簡單的使用 delete 即可！不過，刪除資料相對嚴重許多！你一定要加入 where 設定好規則才行！ 不然，誤刪資料就無法救回來～相當可怕喔！假設你要將 users 的第 2 筆資料刪除，那就這樣做吧：

$ vim mysql-11.py
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="rasppi",
  password="rasppipw",
  database="rasppidb"
)

mycursor = mydb.cursor()

sql = "delete from users where id = 2"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 筆數據刪除")

sql = "select id, login_name, realname from users"
mycursor.execute(sql)
myres = mycursor.fetchall()

for x in myres:

$ python mysql-11.py
1  筆數據刪除
(1, 'rasppi', 'VBird Tsai')
(3, 'rasppi', 'VBird Tsai')

還是提醒一下，刪除資料表的內容時，千萬要注意到 where 後面寫的資料是否有針對性喔！刪除後應該是無法救回來的！

找出上一章的 dht-plot.py 實做方式，建立名為 ~/sql/mydht-time.py 的檔案，這個檔案結合本章內容提到的資料庫查詢方式， 找出溫度、相對濕度的數據，直接繪圖。這個繪圖的結果會在底下的網址上出現：

• http://your.raspberry.pi/yourname/mydht-time.png

SQL 的語法有點像這樣：

sql = "select chtime, temp, rh from mydht"
mycursor.execute(sql)

將時間、溫度、相對濕度抓出來成為陣列的方法有點像這樣：

mytime = []
mytemp = []
myrh   = []
for x in myres:
    mytime.append(str(x[0]))   # 轉成字串
    mytemp.append(x[1])        # 抓出溫度
    myrh.append(x[2])          # 抓出濕度

最終的圖示結果會有點像這樣：

• 參考資料

1. W3C school MySQL： https://www.w3schools.com/python/python_mysql_getstarted.asp
2. 簡易 mysql 資料庫建置： https://pimylifeup.com/raspberry-pi-mysql/
3. 天花板隨記： https://atceiling.blogspot.com/2020/03/raspberry-pi-61mysqlmariadb.html