PostgreSQL基礎整理（一）

標籤：

1. 建立資料庫：
　　1）登入bin目錄，createdb.exe -U postgres -e mydb;
　　-U 表示本次操作的登入使用者名稱，如果不寫會取windows登入的賬戶，如Administrator；會提示無建立許可權；

2. 登入資料庫：
　　1）用postgre內建的sql shell，登入時選擇資料庫為建立庫

3. CRUD
　　書寫習慣：SQL語句大寫，其他小寫
　　3.1）建立表：
　　　　CREATE TABLE users( username char(20) PRIMARY KEY, password char(20));
　　3.2) 插入資料：
　　　　INSERT INTO users(username, password) VALUES(‘fredric‘,‘fredric‘),(‘sinny‘,‘sinny‘);
　　3.4) 查詢資料：
　　　　SELECT * FROM users;
　　3.5) 刪除資料：

　　　　DELETE FROM users WHERE username = "fredric";

4. JOIN
　　準備：
　　CREATE TABLE clubs(clubname char(20) PRIMARY KEY, note text);
　　CREATE TABLE customers( username char(20) PRIMARY KEY, clubname char(20),FOREIGN KEY(clubname) REFERENCES clubs(clubname));

　　INSERT INTO clubs(clubname, note) VALUES(‘myclub‘,‘good clubs‘);
　　INSERT INTO clubs(clubname, note) VALUES(‘myclub1‘,‘good clubs‘);
　　INSERT INTO customers(username, clubname) VALUES(‘fredric‘, ‘myclub‘);

　　4.1) INNER JOIN
　　　　SELECT * FROM clubs INNER JOIN customers ON clubs.clubname = customers.clubname;
　　　　返回資料 myclub
　　4.2) OUTTER JOIN(例如 LEFT JOIN)
　　　　SELECT * FROM clubs LEFT JOIN customers ON clubs.clubname = customers.clubname;
　　返回資料 myclub 和 myclub1

5. 索引
　　5.1) 建立索引
　　　　CREATE INDEX clubs_index ON clubs USING hash(clubname);
　　　　CREATE INDEX clubs_index ON clubs USING btree(clubname);//B樹類型的索引支援多欄位
　　　　CREATE UNIQUE INDEX clubs_index ON clubs;//唯一索引，postgre預設在主鍵上產生唯一索引
　　5.2) 刪除索引
　　　　DROP INDEX clubs_index;

6. 彙總函式
　　6.1) COUNT
　　　　SELECT COUNT(*) FROM clubs;
　　6.2) MIN/MAX
　　　　SELECT MIN(clubname) FROM clubs; //輸出myclub
　　　　SELECT MAX(clubname) FROM clubs; //輸出myclub1

7. GROUP BY
　　SELECT * FROM clubs GROUP By(clubname);
　　SELECT COUNT(*) FROM clubs GROUP By(note);//1,2
　　SELECT COUNT(*) FROM clubs GROUP By(note) HAVING COUNT(*) > 1;//2
　　備忘：SQL執行順序 FROM -> WHERE -> 彙總函式-> GROUP BY -> HAVING -> ORDER BY -> SELECT;

8. 事務
　　BEGIN TRANSACTION;
　　DELETE FROM customers WHERE clubname = ‘myclub‘;
　　DELETE FROM clubs WHERE clubname = ‘myclub‘;
　　COMMIT; //執行刪除
　　ROLLBACK; //復原操作

　　SAVEPOINT;為儲存點，復原的位置
　　例如：
　　　　SAVEPOINT mypoint;
　　　　ROLLBACK TO mypoint;

PostgreSQL基礎整理（一）