Learning Objectives
- 說出 Ubuntu 上 MySQL 的程式、資料目錄、設定檔與錯誤記錄放在哪裡
- 說明 mysqladmin、mysqlshow、mysqldump、mysqlbinlog 等工具的用途
- 用 SHOW DATABASES、USE、SHOW TABLES、DESCRIBE 認識一個資料庫，並解讀 ERROR 1046
- 查看 mysql 系統資料庫的 user 表格，用 SHOW CREATE TABLE 與 \G 看表格的完整定義
- 把 SELECT 當成計算機使用，並用 source 或 mysql < 檔案 一次執行多個敘述
本課要看的東西
上一課裝好了 MySQL，也建立了管理帳號。這一課先在伺服器上四處看看，分成兩部分：
- 作業系統這一側：MySQL 的程式裝在哪裡、資料存成哪些檔案、有哪些命令列工具
- mysql 用戶端裡面：伺服器上有哪些資料庫、每個資料庫有哪些表格、表格有哪些欄位
第二部分用的是本軌的範例資料庫 employee（一家顧問公司的部門、員工、技能、客戶與派工），
第 04 課會從頭建立它，這一課先參觀已經建好的版本。
MySQL 的檔案放在哪裡
原課程用 XAMPP，所有東西都在 C:\xampp\mysql 底下的 bin（執行檔）、data（資料）、share（錯誤訊息等共用檔）。
Ubuntu 的套件則依 Linux 的慣例分散在幾個固定位置：
| 位置 | 內容 |
|---|---|
/usr/sbin/mysqld |
伺服器程式 |
/usr/bin/mysql、mysqladmin、mysqldump… |
用戶端與管理工具 |
/var/lib/mysql/ |
資料目錄（datadir）：所有資料庫的資料 |
/etc/mysql/ |
設定檔，伺服器的設定主要在 /etc/mysql/mysql.conf.d/mysqld.cnf（第 12 課） |
/var/log/mysql/error.log |
錯誤記錄，伺服器啟動失敗時先看這裡 |
資料目錄裡，每個資料庫是一個子目錄，InnoDB 表格預設每張一個 .ibd 檔案（例如 employee/department.ibd）。
MySQL 5.7 以前每張表格還有一個記錄欄位定義的 .frm 檔，8.0 改為把所有定義集中在內部的資料字典，.frm 已經不存在。
資料目錄只能由伺服器存取：不要直接複製、刪除裡面的檔案來備份或移除資料，要透過 SQL 或 mysqldump（第 14 課）。
常用的命令列工具
| 工具 | 用途 |
|---|---|
mysqld |
伺服器本身，平常由 systemd 啟動，不必手動執行 |
mysql |
用戶端（MySQL monitor），輸入 SQL 敘述 |
mysqladmin |
管理工具：查看伺服器狀態、連線清單、關閉伺服器等（第 13 課） |
mysqlshow |
不進入 mysql 就列出資料庫、表格與欄位 |
mysqldump |
把資料庫匯出成 SQL 檔案，用來備份與搬移（第 14 課） |
mysqlbinlog |
讀取二進位日誌（binlog），災難復原時使用（第 14 課） |
myisamchk |
檢查、修復 MyISAM 表格的檔案（第 09、14 課） |
這些工具都是「用戶端」：除了 myisamchk 直接讀檔案以外，都要用帳號密碼連上伺服器，所以都接受 -u、-p、-h 這些選項。
導覽：程式、資料目錄與命令列工具
在 Ubuntu 上執行。輸出很長的地方只節錄重點，「…」代表省略；mysqladmin 的數字是示意。
-
1
找出程式的位置
which印出指令實際執行的是哪個檔案。 -
2
查詢資料目錄
@@datadir是伺服器的系統變數，記錄資料目錄的位置。用-e可以不進入 mysql 直接執行一個敘述。資料目錄屬於作業系統的mysql使用者，一般帳號沒有權限讀取，所以ls要加sudo。資料目錄裡除了每個資料庫一個資料夾（employee、mysql、sys…），還有 InnoDB 的系統檔（ibdata1、undo_001、#innodb_redo等）與二進位日誌（binlog.000001…）。資料庫資料夾裡一張表格一個.ibd檔，表格名稱的大小寫和檔名一致。 -
3
mysqlshow：不進入 mysql 就看表格
mysqlshow 資料庫列出表格，mysqlshow 資料庫 表格列出欄位（多了定序、權限等欄位）。 -
4
mysqladmin：伺服器的狀態
status印出一行摘要：已經執行多少秒（Uptime）、目前的連線執行緒數、累計收到多少個敘述（Questions）等。第 13 課會詳細說明。
SHOW DATABASES、USE、SHOW TABLES、DESCRIBE
進入 mysql 之後，用這四個敘述由外往內認識伺服器：
SHOW DATABASES; -- 伺服器上有哪些資料庫（只列出你有權限看到的）
USE employee; -- 選擇 employee 作為「目前的資料庫」
SHOW TABLES; -- 目前的資料庫有哪些表格
DESCRIBE employee; -- employee 表格有哪些欄位
USE之後，敘述裡的表格名稱都指這個資料庫的表格。還沒有 USE 就執行SHOW TABLES，會出現ERROR 1046 (3D000): No database selected- 也可以不切換，直接寫「資料庫.表格」，例如
DESCRIBE mysql.user、SHOW TABLES FROM mysql DESCRIBE可以縮寫成DESC，也等於SHOW COLUMNS FROM 表格SELECT DATABASE();傳回目前的資料庫，還沒選擇時是 NULLUSE是少數可以不加分號的敘述；本軌一律加上，養成習慣
DESCRIBE 的六個欄位：
| 欄位 | 意思 |
|---|---|
Field |
欄位名稱 |
Type |
資料型別，例如 int、varchar(80)（第 04 課） |
Null |
能不能是 NULL（沒有值） |
Key |
PRI 主鍵、UNI 唯一索引、MUL 可以重複的索引（例如外鍵欄位） |
Default |
沒有給值時的預設值 |
Extra |
其他屬性，例如 auto_increment（自動編號） |
剛安裝好的伺服器有四個系統資料庫：mysql（帳號、權限等系統資料）、information_schema（所有資料庫與表格的說明資料）、
performance_schema（效能統計）、sys（把效能統計整理成容易閱讀的檢視表）。不要在這些資料庫裡建立自己的表格。
由外往內：資料庫、表格、欄位
一開始還沒有選擇資料庫，就像剛登入的時候。第 3 行的 SHOW TABLES 會出現 ERROR 1046，USE employee 之後再執行一次就正常了。右邊會顯示目前的資料庫裡的表格。
mysql 系統資料庫
上一課提到帳號存在 mysql.user 表格。原課程的練習是 USE mysql; SHOW TABLES; DESCRIBE user;，現在也來看一次。
user表格一列是一個帳號，以Host和User兩個欄位為主鍵；Select_priv、Insert_priv這些_priv欄位是全域權限（對所有資料庫有效）- 只對特定資料庫的權限記在
db表格，只對特定表格、特定欄位的權限記在tables_priv、columns_priv - 密碼的雜湊值在
authentication_string欄位，驗證方式在plugin欄位。5.7 以前是Password欄位，原課程的投影片看到的是那個版本
模擬環境的限制：真實的 MySQL 8.0.44 的 mysql 資料庫約有 38 張表格（時區、說明文件、複寫、元件等），
本課的模擬伺服器只做了和帳號權限有關的 user、db、tables_priv、columns_priv 四張；user 表格的 51 個欄位和真實的 8.0.44 相同。
這些表格只用來查看。新增帳號、修改權限一律用 CREATE USER、GRANT 等敘述，不要直接 INSERT 或 UPDATE。
看看 mysql.user 表格
DESCRIBE user 的結果很長（51 列），可以捲動看看有哪些 _priv 權限欄位，最後幾個欄位是 MySQL 8.0 新增的密碼管理設定。最後一行 USE employee 切換回範例資料庫。
想一想：認識伺服器
剛登入 mysql，還沒有執行任何 USE，就輸入 SHOW TABLES;，會得到什麼？
DESCRIBE employee; 的結果中，departmentID 那一列的 Key 欄位是 MUL。這代表什麼？
MySQL 8.0 的 InnoDB 表格存放在 Ubuntu 的哪裡？
SHOW CREATE TABLE 與直向顯示
DESCRIBE 只列出欄位。要看表格的完整定義（索引、外鍵、儲存引擎、字元集），用 SHOW CREATE TABLE：
它印出一個能重新建立這張表格的 CREATE TABLE 敘述。
結果只有兩個欄位，但第二個欄位是好幾行的長字串，用一般的表格框線顯示會很難讀。這時把結尾的分號換成 \G，
改成直向顯示：每個欄位一行，欄位名稱靠右對齊，多行的值照原樣換行。
SHOW CREATE TABLE employee\G
\G 可以用在任何查詢上。欄位很多的表格（例如 mysql.user）查一列資料時特別好用。
SHOW TABLES LIKE 'emp%' 只列出名稱符合樣式的表格：% 代表任意長度的文字，_ 代表一個字元。
看表格的完整定義
對照 DESCRIBE employee 的結果：SHOW CREATE TABLE 多了索引 KEY departmentID、外鍵 CONSTRAINT employee_ibfk_1、儲存引擎 ENGINE=InnoDB、下一個自動編號 AUTO_INCREMENT=9843，以及字元集與定序。最後兩行是同一個查詢，比較表格顯示與 \G 直向顯示。
把 SELECT 當計算機
SELECT 不一定要從表格讀資料，後面直接寫運算式，就會把結果算出來，很適合用來測試函式的行為：
| 運算 | 說明 |
|---|---|
+ - * / |
四則運算，先乘除後加減，括號優先。/ 一定得到小數，7 / 2 是 3.5000 |
DIV、% |
整數除法與餘數：7 DIV 2 是 3，7 % 2 是 1 |
10 / 0 |
結果是 NULL，並產生一個警告，用 SHOW WARNINGS 查看內容 |
CHAR_LENGTH()、LENGTH() |
字元數與位元組數。中文字在 utf8mb4 是 3 個位元組 |
NOW()、DATE_ADD()、DATEDIFF() |
目前時間、日期加減、兩個日期相差幾天 |
結果的欄位名稱就是運算式本身。第 08 課會系統地介紹各類內建函式。
SELECT 當計算機
注意第 3 行結果下方的 1 row in set, 1 warning：除以 0 不會出現錯誤，而是得到 NULL 加一個警告。
導覽：一次執行一個檔案裡的 SQL
把多個 SQL 敘述寫在一個文字檔裡（副檔名習慣用 .sql），就能一次執行，類似 Windows 的批次檔。假設 tour.sql 的內容是：
SHOW TABLES;
SELECT name, job FROM employee WHERE departmentID = 128 ORDER BY name;
-
1
在 mysql 裡用 source
source 檔名（或\. 檔名）是用戶端指令，由 mysql 讀取檔案、逐一送出裡面的敘述。檔名是相對於你啟動 mysql 時所在的資料夾，也可以寫完整路徑。 -
2
在終端機用 mysql < 檔案
<是作業系統的輸入重新導向：把檔案內容當成 mysql 的輸入。指令列最後的employee是要使用的資料庫。這種「批次模式」的輸出沒有框線，欄位之間用 Tab 分隔，方便其他程式處理；加上-t就會印出框線。
想一想：查看定義與計算
想看 employee 表格有哪些外鍵、使用哪個儲存引擎，應該用哪個敘述？
下面的查詢結果是什麼？（mysql -B -N 的輸出，兩個值以 Tab 分隔）
SELECT 7 / 2, 7 DIV 2;
source tour.sql 和 mysql -u admin -p employee < tour.sql 的差別是什麼？
動手寫：認識 assignment 表格
目前還沒有選擇資料庫。請切換到 employee 資料庫，列出它的表格，再查看 assignment 表格的欄位。看完想想：assignment 的主鍵由哪幾個欄位組成？下一課會說明為什麼這樣設計。
重點整理
| 想知道 | 敘述或指令 |
|---|---|
| 有哪些資料庫 | SHOW DATABASES;，終端機用 mysqlshow |
| 選擇資料庫 | USE 資料庫;，目前的資料庫 SELECT DATABASE(); |
| 有哪些表格 | SHOW TABLES;、SHOW TABLES FROM 資料庫;、SHOW TABLES LIKE '樣式'; |
| 表格有哪些欄位 | DESCRIBE 表格;（DESC、SHOW COLUMNS FROM） |
| 表格的完整定義 | SHOW CREATE TABLE 表格\G |
| 資料放在哪裡 | SELECT @@datadir;，Ubuntu 是 /var/lib/mysql/ |
| 執行 SQL 檔案 | mysql 裡 source 檔名；終端機 mysql -u 帳號 -p 資料庫 < 檔名 |
- 帳號與權限存在
mysql資料庫的user、db、tables_priv、columns_priv，只查看、不直接修改 - MySQL 8.0 沒有
.frm檔，表格定義在資料字典裡
下一課暫時離開鍵盤，談資料庫設計：employee 資料庫為什麼要拆成這五張表格。