SQL 語法 – Multiple Tables JOIN

adminLinux, MariaDB, Mysql, RedHat / CentOS

Loading

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

前面有討論到 SQL 語法 JOIN 與 UNION – https://benjr.tw/101855 ,可以把兩個 Table 的資料結合處理,多個 Table 也可以嗎?

是的,直接來看下面的範例.

[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

下面範例有三個 Table.

  1. project (存放專案名稱與相對應人員名單)
    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)

    在 project 資料表建立專案與個別成員資料資料.

    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 |
    +----+------+--------+
    |  1 | A    | Ben    |
    |  2 | A    | Jason  |
    |  3 | A    | Thomas |
    |  4 | B    | Jack   |
    |  5 | B    | Andy   |
    |  6 | B    | Chuck  |
    |  7 | C    | Jack   |
    |  8 | C    | Jason  |
    |  9 | C    | Chuck  |
    +----+------+--------+
    9 rows in set (0.00 sec)
  2. staff (存放員工姓名與部門)
    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)
  3. dept (存放部門與部門敘述)
    dept 這個資料表主要儲存部門與其詳細相關資料.

    MariaDB [testdb]> CREATE TABLE dept (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , info varchar(199) NOT NULL, PRIMARY KEY (K1));
    Query OK, 0 rows affected (0.01 sec)
     
    MariaDB [testdb]> DESCRIBE dept;
    +-------+--------------+------+-----+---------+----------------+
    | Field | Type         | Null | Key | Default | Extra          |
    +-------+--------------+------+-----+---------+----------------+
    | K1    | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(199) | NO   |     | NULL    |                |
    | info  | varchar(199) | NO   |     | NULL    |                |
    +-------+--------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)

    並在 dept Table 建立相對應的部門資料與其詳細資料.

    MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('HR','Human Resource');
    Query OK, 1 row affected (0.00 sec)
     
    MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('HW','HardWare');
    Query OK, 1 row affected (0.00 sec)
     
    MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('Test','Validation Test');
    Query OK, 1 row affected (0.00 sec)
     
    MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('PM','Project Manager');
    Query OK, 1 row affected (0.00 sec)
     
    MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('SW','SoftWare');
    Query OK, 1 row affected (0.00 sec)
    MariaDB [testdb]> SELECT * FROM dept;
    +----+------+-----------------+
    | K1 | name | info            |
    +----+------+-----------------+
    |  1 | HR   | Human Resource  |
    |  2 | HW   | HardWare        |
    |  3 | Test | Validation Test |
    |  4 | PM   | Project Manager |
    |  5 | SW   | SoftWare        |
    +----+------+-----------------+
    5 rows in set (0.01 sec)

JOIN

在 project 資料表中有 member 姓名,在 staff 也有員工姓名,在 staff 也有部門資料, dept 資料表中也有部門資料.相對應資料如下.

Multiple Tables JOIN 語法如下.

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

發佈留言

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

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