next up previous contents
Next: Update 變更紀錄 Up: Query 紀錄詢問 Previous: Insert into 新增紀錄   Contents   DYWANG_HOME

Select 查詢資料表

  1. 查詢資料表 tbl 的內容。
    mariadb root@localhost:dyw> select * from tbl;                                  
    +----+---------+--------+-----------------+
    | id | title   | author | submission_date |
    +----+---------+--------+-----------------+
    | 1  | PHP     | david  | 2020-01-28      |
    | 2  | MariaDB | dywang | 2020-01-01      |
    | 3  | Linux   | linux  | 2020-01-11      |
    +----+---------+--------+-----------------+
    
    3 rows in set
    Time: 0.023s
    
  2. 查詢資料表 tbl 的內容,且以欄位 title 排序。
    mariadb root@localhost:dyw> select * from tbl order by title;                   
    +----+---------+--------+-----------------+
    | id | title   | author | submission_date |
    +----+---------+--------+-----------------+
    | 3  | Linux   | linux  | 2020-01-11      |
    | 2  | MariaDB | dywang | 2020-01-01      |
    | 1  | PHP     | david  | 2020-01-28      |
    +----+---------+--------+-----------------+
    
    3 rows in set
    Time: 0.031s
    
  3. 查詢資料表 tbl 的內容,限制只列出一筆。
    mariadb root@localhost:dyw> select * from tbl limit 1;                          
    +----+-------+--------+-----------------+
    | id | title | author | submission_date |
    +----+-------+--------+-----------------+
    | 1  | PHP   | david  | 2020-01-28      |
    +----+-------+--------+-----------------+
    
    1 row in set
    Time: 0.031s
    
  4. 查詢資料表 tbl 的內容,限制只列出一筆,且位移一筆。
    mariadb root@localhost:dyw> select * from tbl limit 1 offset 1;                 
    +----+---------+--------+-----------------+
    | id | title   | author | submission_date |
    +----+---------+--------+-----------------+
    | 2  | MariaDB | dywang | 2020-01-01      |
    +----+---------+--------+-----------------+
    
    1 row in set
    Time: 0.032s
    
  5. Where 條件運算子:
    = != > < >= <=
    
  6. 查詢 author='dywang' 的紀錄。
    mariadb root@localhost:dyw> select * from tbl where author='dywang';            
    +----+---------+--------+-----------------+
    | id | title   | author | submission_date |
    +----+---------+--------+-----------------+
    | 2  | MariaDB | dywang | 2020-01-01      |
    +----+---------+--------+-----------------+
    
    1 row in set
    Time: 0.032s
    
  7. 可用 AND 及 OR 結合多個條件。
    mariadb root@localhost:dyw> select * from tbl where author='dywang' or id=1;    
    +----+---------+--------+-----------------+
    | id | title   | author | submission_date |
    +----+---------+--------+-----------------+
    | 1  | PHP     | david  | 2020-01-28      |
    | 2  | MariaDB | dywang | 2020-01-01      |
    +----+---------+--------+-----------------+
    
    2 rows in set
    Time: 0.032s
    
  8. LIKE 配合 WHERE 使用,只是將等號改成 LIKE 即可,LIKE 後接的字串配合百分比 % 符號,可進行字串搜尋。例如:LIKE dyw% 表示搜尋 dyw 開頭的字串。
    mariadb root@localhost:dyw> select * from tbl where author like 'dyw%';         
    +----+---------+--------+-----------------+
    | id | title   | author | submission_date |
    +----+---------+--------+-----------------+
    | 2  | MariaDB | dywang | 2020-01-01      |
    +----+---------+--------+-----------------+
    
    1 row in set
    Time: 0.030s
    



De-Yu Wang 2020-04-07