SQL – Database normalization

測試環境為 CentOS 7 x86_64 虛擬機.

規劃資料庫欄位很重要.

下面範例有兩個 Table.

  1. project (存放專案名稱與相對應人員名單)
  2. staff (存放員工姓名與部門)
[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 5.5.60-MariaDB MariaDB Server

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)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

project 這個資料表主要儲存專案的相關資料.

MariaDB [testdb]> CREATE TABLE project (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , member varchar(199) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> DESCRIBE project;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| K1     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(199) | NO   |     | NULL    |                |
| member | varchar(199) | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

staff 這個資料表主要儲存員工的相關資料.

MariaDB [testdb]> CREATE TABLE staff (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , dept varchar(199) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> DESCRIBE staff;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| K1    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(199) | NO   |     | NULL    |                |
| dept  | varchar(199) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

並在 staff Table 建立了員工 Ben , Jason , Thomas , Jack , Andy , Chuck 與其相對應的部門資料.

MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Ben','HR');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Jason','HW');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Thomas','Test');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Jack','PM');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Andy','Test');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Chuck','SW');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> SELECT * FROM staff;
+----+--------+------+
| K1 | name   | dept |
+----+--------+------+
|  1 | Ben    | HR   |
|  2 | Jason  | HW   |
|  3 | Thomas | Test |
|  4 | Jack   | PM   |
|  5 | Andy   | Test |
|  6 | Chuck  | SW   |
+----+--------+------+
6 rows in set (0.01 sec)

在 project Table 建立了專案 A , B , C 與其相對應的成員 (專案相對應的成員儲存在 member 欄位,多名人員時使用 “,” 來隔開).

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Ben,Jason,Thomas');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Jack,Andy,Chuck');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jack,Jason,Chuck');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> SELECT * FROM project;
+----+------+------------------+
| K1 | name | member           |
+----+------+------------------+
|  1 | A    | Ben,Jason,Thomas |
|  2 | B    | Jack,Andy,Chuck  |
|  3 | C    | Jack,Jason,Chuck |
+----+------+------------------+
3 rows in set (0.00 sec)

現在問題來了,我要怎麼透過 SELECT 把專案 A 與其成員和單位顯示出來.

一開始想到可以用 WHERE 的 IN 功能,但是無法達成,網路查詢可用 FIND_IN_SET() 函數來達成,使用很簡單,回傳值為 string 位於 string_list (須以 , 分隔)的哪一個位置(不存在時,回傳 0 ).

FIND_IN_SET(string, string_list)

直接來看範例.

MariaDB [(none)]> SELECT FIND_IN_SET('b' , 'a,b,c');
+----------------------------+
| FIND_IN_SET('b' , 'a,b,c') |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT FIND_IN_SET('f' , 'a,b,c');
+----------------------------+
| FIND_IN_SET('f' , 'a,b,c') |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

透過這個方式可以得到專案 A 與其成員和單位.

MariaDB [testdb]> SELECT 'A' as Project, name , dept FROM testdb.staff WHERE FIND_IN_SET(name, (SELECT member FROM testdb.project WHERE name LIKE 'A'));
+---------+--------+------+
| Project | name   | dept |
+---------+--------+------+
| A       | Ben    | HR   |
| A       | Jason  | HW   |
| A       | Thomas | Test |
+---------+--------+------+
3 rows in set (0.00 sec)

在上網查詢的過程中,不少文章提到使用 FIND_IN_SET 函數可以解決欄位中的 “,( comma separated )” 所造成的搜尋問題,也不建議儲存這樣的欄位內容.不少文章有提到資料庫正規化 (Database normalization) 重新定義 Table 與其相關性.

下面重新定義 project 的 Tables, 先把 project 舊資料移除.

MariaDB [testdb]> DELETE FROM project;
Query OK, 3 rows affected (0.01 sec)

這次不把所有的成員資料儲存在 member 欄位,而是紀錄專案與個別成員資料成為單一筆資料.

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Ben');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Jason');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Thomas');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Jack');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Andy');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Chuck');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jack');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jason');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Chuck');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> SELECT * FROM project;
+----+------+--------+
| K1 | name | member |
+----+------+--------+
|  4 | A    | Ben    |
|  5 | A    | Jason  |
|  6 | A    | Thomas |
|  7 | B    | Jack   |
|  8 | B    | Andy   |
|  9 | B    | Chuck  |
| 10 | C    | Jack   |
| 11 | C    | Jason  |
| 12 | C    | Chuck  |
+----+------+--------+
9 rows in set (0.00 sec)

現在透過 Join ( http://benjr.tw/101855 ) 的方式查詢即可得到 專案與其成員和單位.

MariaDB [testdb]> SELECT project.name , project.member , staff.dept FROM project , staff WHERE project.member=staff.name;
+------+--------+------+
| name | member | dept |
+------+--------+------+
| A    | Ben    | HR   |
| A    | Jason  | HW   |
| A    | Thomas | Test |
| B    | Jack   | PM   |
| B    | Andy   | Test |
| B    | Chuck  | SW   |
| C    | Jack   | PM   |
| C    | Jason  | HW   |
| C    | Chuck  | SW   |
+------+--------+------+
9 rows in set (0.00 sec)

查詢 專案 A 與其成員和單位.

MariaDB [testdb]> SELECT project.name , project.member , staff.dept FROM project , staff WHERE project.member=staff.name AND project.name='A';
+------+--------+------+
| name | member | dept |
+------+--------+------+
| A    | Ben    | HR   |
| A    | Jason  | HW   |
| A    | Thomas | Test |
+------+--------+------+
3 rows in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料