SQL • 無限: PostgreSQL 查詢資料語法

基本語法

查詢資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING 運算子 ] [ NULLS { FIRST | LAST }
 ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

其中 from_item 可以是:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...]
 | column_definition [, ...] ) ]
    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join
_column [, ...] ) ]

並且 with_query 是:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert |
update | delete )

TABLE [ ONLY ] table_name [ * ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

查詢資料表 baselist 的全部資料：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
(2 筆資料列)

以 phones 欄位逆向排序，查詢資料表 baselist 的全部資料：

student=# SELECT * FROM baselist ORDER BY phones DESC;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
(2 筆資料列)

以 name 欄位為查詢條件，查詢資料表 baselist 內的資料：

student=# SELECT * FROM baselist WHERE name='Peter';
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

使用萬用字元 '%' 的查詢方式：

student=# SELECT * FROM baselist WHERE name LIKE 'Pe%';
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

只列出部份欄位的查詢方式：

student=# SELECT name,phones FROM baselist WHERE name LIKE 'Pe%';
 name  |   phones
-------+------------
 Peter | 0912345678
(1 筆資料列)

2016年7月11日 星期一

PostgreSQL 查詢資料語法

2016年7月11日星期一