postgreSQL第一天——關係、CRUD和聯結

標籤：

建立表：

CREATE TABLE countries(country_code char(2) PRIMARY KEY,country_name text UNIQUE);

 

插入資料：

INSERT INTO countries(country_code,country_name)VALUES (‘us‘,‘United States‘),(‘mx‘,‘Mexico‘),(‘au‘,‘Australia‘),(‘gb‘,‘United Kingdom‘),(‘de‘,‘Gemany‘),(‘ll‘,‘Loompaland‘);

或

INSERT INTO countriesVALUES (‘us‘,‘United States‘),(‘mx‘,‘Mexico‘);

 

查詢資料：

SELECT * FROM countries;

 

刪除資料：

DELETE FROM countriesWHERE country_code = ‘ll‘;

 

外鍵：為了保證cities中所有的country_code都在countries出現過，
所以給cities的country_code添加references外鍵約束。

CREATE TABLE cities(name text NOT NULL,postal_code varchar(9) CHECK(postal_code <> ‘‘),country_code char(2) REFERENCES countries,PRIMARY KEY (country_code,postal_code));

 

但cities表的country_code引用可以為NULL，代表一個值空缺，
如果不允許cities.country_code為空白，可以這樣定義：

country_code char(2) REFERENCES countries NOT NULL

 

插入外鍵資料：

INSERT INTO citiesVALUES (‘Portland‘,‘87200‘,‘us‘);

 

更新上面的資料：

UPDATE citiesSET postal_code = ‘97205‘WHERE name = ‘Portland‘;

 

使用聯結查詢：

SELECT cities.*,country_nameFROM cities INNER JOIN countriesON cities.country_code = countries.country_code;

 

外鍵引用兩個主鍵：

CREATE TABLE venues(venue_id SERIAL PRIMARY KEY,name varchar(255),street_address text,type char(7) CHECK (type in (‘public‘,‘private‘)) DEFAULT ‘public‘,postal_code char(2),country_code char(2),FOREIGN KEY (country_code,postal_code)REFERENCES cities(country_code,postal_code) MATCH FULL);

 

左外聯結：

SELECT e.title,v.nameFROM events e LEFT JOIN venues vON e.venues_id = v.venues_id;

 

建立表時，postgresql會預設在主鍵上建立索引，
使用UNIQUE強制在一列上建立索引。
建立索引：

CREATE INDEX envents_titleON envents USING hash (title);

 

對於操作符為大於/小於/等於這樣的匹配查詢，使用B樹索引，相當於彙編的段寄存器。

CREATE INDEX events_startsON events USING btree (starts);

 

使用B樹索引查詢：

SELECT *FROM eventsWHERE starts >= ‘2012-04-01‘;

 

postgreSQL第一天——關係、CRUD和聯結