next up previous contents
Next: 查詢練習 Up: MariaDB 資料庫 Previous: 建立用戶   Contents

查詢資料表資料

  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 Clara | jfalena@example.com | +1 408 555 8133 | +1 408 555 7472 |
    | 3915 | Cupertino   | jwalker@example.com | +1 408 555 1476 | +1 408 555 1992 |
    +------+-------------+---------------------+-----------------+-----------------+
    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
    



De-Yu Wang 2018-09-07