PostgreSQL学习记录
一、安装
使用 docker 安装：
$ docker pull postgres:alpine
二、启动
起 docker 容器：
$ docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres:alpine
默认用户：postgres
默认密码：mysecretpassword
或者通过
psql，验证不成功：$ docker run -it --rm --network some-network postgres:alpine psql -h some-postgres -U postgres:alpine报错：
docker: Error response from daemon: network some-network not found.
使用容器连接：
$ docker run --rm -t -i --link some-postgres:pg postgres:alpine bash
在客户端 docker run 中直接使用 -link remote_name:local_alias 使容器连接到另一个容器端口。比如上面的例子就是使容器 some-postgres 连接到另一个容器 pg。
然后，通过 psql：
$ psql -h $PG_PORT_5432_TCP_ADDR -p $PG_PORT_5432_TCP_PORT -d postgres -U postgres --password
注意用户要是之前的默认用户 postgres
然后会出现输入密码，输入之前设置的默认密码：mysecretpassword
以下界面表示成功连接到 PostgreSQL：
psql (13.3)
Type "help" for help.
postgres=#
三、PSQL 操作数据库
psql命令
| Object Type | All Privileges | Default PUBLIC Privileges |
psql Command |
|---|---|---|---|
DATABASE |
CTc |
Tc |
\l |
DOMAIN |
U |
U |
\dD+ |
FUNCTION or PROCEDURE |
X |
X |
\df+ |
FOREIGN DATA WRAPPER |
U |
none | \dew+ |
FOREIGN SERVER |
U |
none | \des+ |
LANGUAGE |
U |
U |
\dL+ |
LARGE OBJECT |
rw |
none | |
SCHEMA |
UC |
none | \dn+ |
SEQUENCE |
rwU |
none | \dp |
TABLE (and table-like objects) |
arwdDxt |
none | \dp |
| Table column | arwx |
none | \dp |
TABLESPACE |
C |
none | \db+ |
TYPE |
U |
U |
\dT+ |
中文补充
| 操作 | 命令 |
|---|---|
| 查看所有数据库 | # \l |
| 切换数据库 | # \c dbname |
| 查看当前数据库中的所有表 | # \d |
| 查看指定表中的字段 | # \d dbtable |
| 查看表信息 | # \d+ dbtable |
| 查看所有用户 | # \du |
| 查看表结构 | # \dn |
| 查看索引 | # \di |
创建表
CREATE TABLE products (
product_no integer,
name text,
price numeric
);
删除表
DROP TABLE my_first_table;
设置默认值
如果不设置默认值，默认为 null。
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99
);
创建一个已生成的列
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
限制
- check
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
修改表
增加列
ALTER TABLE products ADD COLUMN description text;
默认值为 null。
移除列
ALTER TABLE products DROP COLUMN description;
增加限制
表限制
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
非空限制
不能以表限制的形式写，需要：
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
移除限制
所有限制类型（除 not null）
ALTER TABLE products DROP CONSTRAINT some_name;
not null
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
改变列的默认值
不会影响已存在的值，只会影响默认值和未来插入的值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
移除任何默认值
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
改变列的数据类型
只有列中的每一个已存在数据可以转变为新的数据类型，才会成功
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
重命名列
ALTER TABLE products RENAME COLUMN product_no TO product_number;
重命名表
ALTER TABLE products RENAME TO items;
更新表里的某一个值
update items set name = 'book';
插入数据
INSERT INTO tablename(xxx, xxx) VALUES('xxx', 'xxx');
四、function
函数可以重载，根据参数不同匹配同名不同函数。
求某一列值为 'xxx' 的所有和：
代码1（有错）：
create function sum_price1() returns numeric(10,2)
as 'select sum(price) from items where name='book';'
language sql;
应该是单引号里面不能再使用单引号。
代码2（有错）：
create function sum_price2() returns numeric(10,2) as $$
begin
return select sum(price) from items where name='book';
end; $$
language plpgsql;
报错：
ERROR: syntax error at or near "select"
LINE 3: return select sum(price) from items where name='book';
代码3（成功）：
create or replace function sum_price(g text)
returns numeric(10,2) as $$
select sum(price) from items where name=g;
$$
language sql;
传参命名不能与表里面的字段相同（比如 sum_price(g text) 里的 g，数据库字段不能有 g），否则不能根据设置的条件查询相应数据。
已创建的 function，不能使用 replace 修改其之前的传入参数，会报错：ERROR: cannot change name of input parameter "xxx" 。
调用自定义函数
select * from sum_price('book');
五、注意
-
除 sql 语句外的命令大小写敏感。
-
sql 语句要加上分号结束。
-
如果表名是敏感词，如 user 等，查询表时要在 user 上添加双引号。
-
当一行命令没输完（指没有写
;），则postgres=#会变为postgres-#，之后输入的命令语句和前一行都属于同一个命令。 -
语法查询：
\h，详细了解各个参数的含义和使用，再搭配官方文档和网上的快餐式教程，了解其他人没有用到的参数。 -
命令行支持联想，即
Tab键可自动补全。
