PostgreSQL学习记录

你的名字 2022-09-14 14:25 127阅读 0赞

## 一、安装 ##

使用 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命令 ###

<table> 
 <thead> 
  <tr> 
   <th>Object Type</th> 
   <th>All Privileges</th> 
   <th>Default <code>PUBLIC</code> Privileges</th> 
   <th>psql Command</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>DATABASE</code></td> 
   <td><code>CTc</code></td> 
   <td><code>Tc</code></td> 
   <td><code>\l</code></td> 
  </tr> 
  <tr> 
   <td><code>DOMAIN</code></td> 
   <td><code>U</code></td> 
   <td><code>U</code></td> 
   <td><code>\dD+</code></td> 
  </tr> 
  <tr> 
   <td><code>FUNCTION</code> or <code>PROCEDURE</code></td> 
   <td><code>X</code></td> 
   <td><code>X</code></td> 
   <td><code>\df+</code></td> 
  </tr> 
  <tr> 
   <td><code>FOREIGN DATA WRAPPER</code></td> 
   <td><code>U</code></td> 
   <td>none</td> 
   <td><code>\dew+</code></td> 
  </tr> 
  <tr> 
   <td><code>FOREIGN SERVER</code></td> 
   <td><code>U</code></td> 
   <td>none</td> 
   <td><code>\des+</code></td> 
  </tr> 
  <tr> 
   <td><code>LANGUAGE</code></td> 
   <td><code>U</code></td> 
   <td><code>U</code></td> 
   <td><code>\dL+</code></td> 
  </tr> 
  <tr> 
   <td><code>LARGE OBJECT</code></td> 
   <td><code>rw</code></td> 
   <td>none</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td><code>SCHEMA</code></td> 
   <td><code>UC</code></td> 
   <td>none</td> 
   <td><code>\dn+</code></td> 
  </tr> 
  <tr> 
   <td><code>SEQUENCE</code></td> 
   <td><code>rwU</code></td> 
   <td>none</td> 
   <td><code>\dp</code></td> 
  </tr> 
  <tr> 
   <td><code>TABLE</code> (and table-like objects)</td> 
   <td><code>arwdDxt</code></td> 
   <td>none</td> 
   <td><code>\dp</code></td> 
  </tr> 
  <tr> 
   <td>Table column</td> 
   <td><code>arwx</code></td> 
   <td>none</td> 
   <td><code>\dp</code></td> 
  </tr> 
  <tr> 
   <td><code>TABLESPACE</code></td> 
   <td><code>C</code></td> 
   <td>none</td> 
   <td><code>\db+</code></td> 
  </tr> 
  <tr> 
   <td><code>TYPE</code></td> 
   <td><code>U</code></td> 
   <td><code>U</code></td> 
   <td><code>\dT+</code></td> 
  </tr> 
 </tbody> 
</table>

#### 中文补充 ####

<table> 
 <thead> 
  <tr> 
   <th>操作</th> 
   <th>命令</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>查看所有数据库</td> 
   <td><code># \l</code></td> 
  </tr> 
  <tr> 
   <td>切换数据库</td> 
   <td><code># \c dbname</code></td> 
  </tr> 
  <tr> 
   <td>查看当前数据库中的所有表</td> 
   <td><code># \d</code></td> 
  </tr> 
  <tr> 
   <td>查看指定表中的字段</td> 
   <td><code># \d dbtable</code></td> 
  </tr> 
  <tr> 
   <td>查看表信息</td> 
   <td><code># \d+ dbtable</code></td> 
  </tr> 
  <tr> 
   <td>查看所有用户</td> 
   <td><code># \du</code></td> 
  </tr> 
  <tr> 
   <td>查看表结构</td> 
   <td><code># \dn</code></td> 
  </tr> 
  <tr> 
   <td>查看索引</td> 
   <td><code># \di</code></td> 
  </tr> 
 </tbody> 
</table>

### 创建表 ###

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` 键可自动补全。