PostgreSQL 新增、修改與刪除資料

基本語法

• 新增資料表中的資料 SQL 語法：
  

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  INSERT INTO table_name [ ( column_name [, ...] ) ]
      { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
      [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
  
  

• 刪除資料表中的資料 SQL 語法：
  

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
      [ USING using_list ]
      [ WHERE condition | WHERE CURRENT OF cursor_name ]
      [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
  
  
  

• 更新資料表中的資料 SQL 語法：
  

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
      SET { column_name = { expression | DEFAULT } |
            ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
      [ FROM from_list ]
      [ WHERE condition | WHERE CURRENT OF cursor_name ]
      [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
  
  

操作練習

1. 登入 psql 操作介面：

   #su - postgres
   $psql
   #postgres=
   

2. 連結 student 資料庫：

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

3. 查詢資料庫內的資料表：

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

4. 查詢資料表 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)
   

5. 插入一筆資料：

   student=# INSERT INTO baselist(id,name,phones,address)
   student-# VALUES('s0002','Jupiter','0987654321','test Rd., No. 123');
   
   INSERT 0 1
   

6. 查詢 baselist 資料表內容：

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

7. 刪除一筆 id 為 s0002 的資料：

   student=# DELETE FROM baselist WHERE id = 's0002';
   DELETE 1
   

8. 更新除一筆 id 為 S0001 的資料內容：

   student=# UPDATE baselist SET address='Hd. Rd., No. 4321' WHERE id = 'S0001';
   UPDATE 1
   

9. 查詢 baselist 資料表內容：

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

參考文獻：

1. https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/