查詢資料表資料

1. 登入 MariaDB。

   [root@kvm5 ~]# mysql -uroot -pqweqwe
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 26
   Server version: 5.5.41-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   

2. 使用資料庫 Contacts。

   MariaDB [(none)]> use Contacts;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Database changed
   

3. 查看資料庫 Contacts 中共有三個的資料表。

   MariaDB [Contacts]> show tables;
   +--------------------+
   | Tables_in_Contacts |
   +--------------------+
   | User_Contacts      |
   | User_Logins        |
   | User_Names         |
   +--------------------+
   3 rows in set (0.00 sec)
   

4. 查看資料庫 Contacts 中的資料表 User_Names 的欄位結構。

   MariaDB [Contacts]> desc User_Names;
   +------------+-------------+------+-----+---------+-------+
   | Field      | Type        | Null | Key | Default | Extra |
   +------------+-------------+------+-----+---------+-------+
   | user_id    | int(11)     | NO   | PRI | NULL    |       |
   | last_name  | varchar(35) | YES  |     | NULL    |       |
   | first_name | varchar(35) | YES  |     | NULL    |       |
   +------------+-------------+------+-----+---------+-------+
   3 rows in set (0.00 sec)
   

5. 查看資料庫 Contacts 中的資料表 User_Logins 的欄位結構。

   MariaDB [Contacts]> desc User_Logins;
   +------------+-------------+------+-----+---------+-------+
   | Field      | Type        | Null | Key | Default | Extra |
   +------------+-------------+------+-----+---------+-------+
   | id         | int(11)     | NO   | MUL | NULL    |       |
   | User_Login | varchar(25) | YES  |     | NULL    |       |
   | User_Pass  | varchar(35) | YES  |     | NULL    |       |
   +------------+-------------+------+-----+---------+-------+
   3 rows in set (0.00 sec)
   

6. 查看資料庫 Contacts 中的資料表 User_Contacts 的欄位結構。

   MariaDB [Contacts]> desc User_Contacts;
   +-----------+-------------+------+-----+---------+-------+
   | Field     | Type        | Null | Key | Default | Extra |
   +-----------+-------------+------+-----+---------+-------+
   | id        | int(11)     | NO   | MUL | NULL    |       |
   | Location  | varchar(25) | YES  |     | NULL    |       |
   | Email     | varchar(35) | YES  |     | NULL    |       |
   | Telephone | varchar(25) | YES  |     | NULL    |       |
   | Fax       | varchar(25) | YES  |     | NULL    |       |
   +-----------+-------------+------+-----+---------+-------+
   5 rows in set (0.00 sec)
   

7. 經觀察 Contacts 中的三個資料表之關聯性為：同一人員以 user_id, id 做關聯，也就是三個資料表中的 id 編號相同，表示同一人。以此結論查詢 first_name 為 John 共有兩人。

   MariaDB [Contacts]> select * from User_Names where first_name='John';
   +---------+-----------+------------+
   | user_id | last_name | first_name |
   +---------+-----------+------------+
   |    1917 | Falena    | John       |
   |    3915 | Walker    | John       |
   +---------+-----------+------------+
   2 rows in set (0.00 sec)
   

8. 依據查到的 user_id, id 再查詢資料表 User_Contacts，可以查到這兩人的「住地」、Email、電話及傳真。

   MariaDB [Contacts]> select * from User_Contacts where id=1917 or id=3915;
   +------+-----------+-----------------+------------+------------+
   | id   | Location  | Email           | Telephone  | Fax        |
   +------+-----------+-----------------+------------+------------+
   | 1917 | Santa Cla | jfalena@example | +1 408 555 | +1 408 555 |
   | 3915 | Cupertino | jwalker@example | +1 408 555 | +1 408 555 |
   +------+-----------+-----------------+------------+------------+
   2 rows in set (0.00 sec)
   
   MariaDB [Contacts]>
   

9. 只有一兩筆資料可以用上述的方法查詢比對，但如果資料量龐大，還是要使用 JOIN 結合查詢。以下是查到 first_name='John 且住在 Cupertino 的人有一筆。

   MariaDB [Contacts]> select a.user_id, a.first_name, b.Location \
   from User_Names a,User_Contacts b where a.user_id=b.id and \
   a.first_name='John' and b.Location='Cupertino';
   +---------+------------+-----------+
   | user_id | first_name | Location  |
   +---------+------------+-----------+
   |    3915 | John       | Cupertino |
   +---------+------------+-----------+
   1 row in set (0.01 sec)
   

10. 如果只想得知查詢結果有幾筆，可以使用 count(*) 函數計算結果。

    MariaDB [Contacts]> select count(*) from User_Names a,User_Contacts b \
    where a.user_id=b.id and a.first_name='John' and b.Location='Cupertino';
    +----------+
    | count(*) |
    +----------+
    |        1 |
    +----------+
    1 row in set (0.00 sec)
    MariaDB [Contacts]>
    

11. 查詢密碼為 mainland 的名字 (first_name)，可以先查密碼為 mainland 的用戶 id 為 3819。

    MariaDB [Contacts]> select * from User_Logins where User_Pass='mainland';
    +------+------------+-----------+
    | id   | User_Login | User_Pass |
    +------+------------+-----------+
    | 3819 | mcarter    | mainland  |
    +------+------------+-----------+
    1 row in set (0.00 sec)
    

12. 再以 id 3819 查到用戶名為 Mike、姓為 Carter。

    MariaDB [Contacts]> select * from User_Names where user_id=3819;
    +---------+-----------+------------+
    | user_id | last_name | first_name |
    +---------+-----------+------------+
    |    3819 | Carter    | Mike       |
    +---------+-----------+------------+
    1 row in set (0.00 sec)
    

13. 以 JOIN 結合查詢查得密碼為 mainland 的名字 (first_name) 為 Mike。

    MariaDB [Contacts]> select a.first_name \
    from User_Names a,User_Logins b \
    where a.user_id=b.id and b.User_Pass='mainland';
    +------------+
    | first_name |
    +------------+
    | Mike       |
    +------------+
    1 row in set (0.00 sec)
    

14. 退出資料庫。

    MariaDB [Contacts]> exit
    Bye