Database - Creating Databases and Tables
Data Types
當我們在create table的時候我們必須要指名某個column是某個Data Types，這樣別人用我們的database時，不會用錯Data Types
Boolean
可以用boolean 或 bool 作為keyword
Character
char(n)
如果你輸入的字源比n還少，postgreSQL會幫自加入空白，但超過就會是error
varchar(n)
你可以儲存任何n以下個字元，不會幫你加入空白
Number
整數
smallint 在 -32768與32767之間的數
int 在 -214783648與214783647之間的數
serial 自己幫輸入的資料加上流水號，第一個是1、第二個是2
Temporal
date 日期
time 時間
timestamp 日期與時間
interval 儲存timestamp之間的間隔
timestamptz timestamp和時區
Key
primary key
作為一個特別的key來指名row，也只會有一個primary key，例如一個table有id 名字 姓，那麼id就會是primary key因為他不會重複，但是姓、名有可能會重複，所以就可以用id來查詢哪個客戶
CREATE TABLE table_name (
column_name data_type PRIMARY KEY,
column_name data_type, ...);
通常primary key會放在第一個
foreign key
用來跟其他table的primary key 配對用的，別的table的foreign key跟我的primary key是一樣的話叫做child table 相反的話就是parent table。可以有很多個foreign key
Create Table
CREATE TABLE table_name
就是create table並幫他取名字
(column_name TYPE column_constraint, table_constraint)
接下來列出你的column名字、類型，還有column的規則，例如NOT NULL
然後就是table的規則
INHERITS existing_table_name;
他可以繼承你之前CREATE TABLE的所有column
constraint
NOT NULL 不可以是NULL也就是說不可以跳過這個
UNIQUE 在整個table中，他不可以重複，但NULL可以一直重複，因為他會被視為不同的
PRIMARY KEY 指定primary key
CHECK 檢查輸入的資料是否符合你的條件，例如價錢都要是正數
REFERENCES 用來指定foreign key
下面是table constraint 跟上面的column constraint很像，但他是用在整個table
UNIQUE(column_list)
CREATE TABLE account( user_id serial PRIMARY KEY,
因為serial可以自己編號，因此每個user都會得到一個號碼，剛好作為primary key，因為大家的都會不一樣
username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP );
CREATE TABLE role( role_id serial PRIMARY KEY, role_name VARCHAR (255) UNIQUE NOT NULL
例如CEO 清潔工);
Don't worry about fully understanding this last example yet!
CREATE TABLE account_role ( user_id integer NOT NULL, role_id integer NOT NULL, grant_date timestamp without time zone, PRIMARY KEY (user_id, role_id), CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id) REFERENCES role (role_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id) REFERENCES account (user_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION )
insert
當我們create一個table的時候，我們裡面是空的沒有資料，所以我們可以用insert把資料放入
INSERT INTO table_name(column1, column2, ...)
VALUES(value1, value2, ...)
value1會被放入column1，value2會被放入column2
如果有很多row的時候，可以這樣用
INSERT INTO table_name(column1, column2, ...)
VALUES(value1, value2, ...),
(value1, value2, ...), ...
如果要從其他table裡面INSERT的話
NSERT INTO table_name
SELECT column1, column2, ...
FROM another_table
WHERE condition;
例子：
INSERT INTO link(url, name)
VALUES(’www.google.com’, ‘google’);
要複製一個table有一樣的column
CREATE TABLE line_2 (LIKE link);
他不會有資料
UPDATE
要改變一個column裡面的value就要用UPDATE
UPDATE table
SET column1 = value1
column2 = value2
WHERE condition;
如果沒指定的話，會整個column都帶入value
除了自己打值，也可以直接帶入整個colum作為值
如果只輸入上面的指令，他只會回應已經成功UPDATE
但如果在後面加上
RETURNING column1, column2, column3, ...
就會直接把結果return出來
DELETE
DELETE FROM table
WHERE condition;
如果沒有WHERE的話就會整個table都刪掉
他會告訴你他刪掉多少行了
ALTER TABLE
ALTER TABLE table_name action;
ADD COLUMN
ALTER TABLE link ADD COLUMN active boolean;
增加一個column名字是active 類型是boolean
DROP COLUMN
RENAME COLUMN
ADD CONSTRAINT
RENAME TO
DROP TABLE
DROP TABLE [IF EXIST] link
就會把link table刪掉，IF EXIST是為了防止萬一他不存在出現error
CHECK CONSTRAINT
用來確認值是不是在你指定的條件內
CREATE TABLE new_user(
birth_date DATE CHECK(birth_date > ‘1900-01-01’),
join_date DATE CHECK(birth_date > birth_date)
);
如果錯誤的話postgreSQL會告訴你哪邊錯，你也可以幫他命名
join_date DATE CONSTRAINT birth_positive CHECK(birth_date > birth_date)
如果有錯他就會告訴你是birth_positive錯了
NOT NULL
NULL代表的是missing information或是unknow，不代表是0或是空格，銷售是0跟NULL不同，NULL有可能是100、1000。而0或空格仍然算是information
所以他不接受value是NULL
UNIQUE
他只接受不同的值，出現相同的就會是error