Day 10 : Postgres 操作

昨天介绍资料库的概念，今天会开始操作 Postgres 资料库。GOGO!
以下示范均是直接对 postgres 的 terminal 进行操作哦！
(开启 Postgres 并点选你要的 DB 就可以开启)

1. 切换资料库

psql -U 帐号 -h hostname -d database

2. 查看所有表格

\dt

3. 获得某表的详细内容

\d 表名

4. CREATE TABLE

• 说明

CREATE TABLE IF NOT EXISTS schema_name.table_name(
    column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
);

• 范例
  PRIMARY KEY 代表主键值，可以用来识别该表格的唯一值，就像身分证字号。通常利用这个栏位可以跟其他表做合并。

CREATE TABLE IF NOT EXISTS public.train(
    customer_id character varying(100) PRIMARY KEY,
    sex character varying(1) NOT NULL,
    height numeric(5,2) NOT NULL,
    weight numeric(5,2) NOT NULL
);

5. INSERT

• 说明

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

• 范例

INSERT INTO train(customer_id, sex, height, weight)
VALUES ('A123456789', 'M', '180.45', '70.7');

若一次想塞入多笔资料也可以办得到！

INSERT INTO train(customer_id, sex, height, weight)
VALUES ('A123456789', 'M', '180.45', '70.7'),
       ('X123456789', 'M', '175.12', '65.7'),
       ('W123456789', 'W', '160.67', '45.7'),
       ('H123456789', 'W', '167.93', '52.7');

6. UPDATE

更新资料

• 说明

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

• 范例

UPDATE train set sex='F' where customer_id='A123456789';

7. SELECT

筛选资料

• 说明

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
all expression(*) [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

• 范例

select * from train where sex='M';

8. DELETE

删除资料

• 说明

DELETE FROM [ ONLY ] table [ WHERE condition ]

• 范例

DELETE FROM train where sex='M';

9. TRUNCATE

清空一个表

• 说明

TRUNCATE [ TABLE ] name

• 范例

TRUNCATE train;

10. DROP TABLE

删除表

• 说明

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

• 范例

DROP TABLE train;

11. GRANT

给予其他人权限

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT ALL PRIVILEGES ON train to postgres;