PostgreSQL的基本使用

目录

登录数据库

数据库操作

列出库

创建库

删除库

切换库

查看库大小

数据表操作

列出表

创建表

复制表

删除表

查看表结构

数据操作

添加数据

查询数据

修改数据

删除数据

---

本文讲述了PostgreSQL的一些基本操作，主要是针对库、表、数据的一些操作。

登录数据库

# 切换用户
su - postgres
 
# 登录数据库
/usr/local/pgsql/bin/psql

[root@bogon ~]# # 切换用户
[root@bogon ~]# su - postgres
Last login: Thu May 16 22:13:52 EDT 2024 on pts/0
[postgres@bogon ~]$  
[postgres@bogon ~]$ # 登录数据库
[postgres@bogon ~]$ /usr/local/pgsql/bin/psql
psql (16.3)
Type "help" for help.

postgres=# 

数据库操作

列出库

# 列出数据库
postgres=# \l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU 
Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+-------------+-------------+-----
-------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |     
       |           | 
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |     
       |           | =c/postgres          +
           |          |          |                 |             |             |     
       |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |     
       |           | =c/postgres          +
           |          |          |                 |             |             |     
       |           | postgres=CTc/postgres
(3 rows)

 

# 查看更多关于数据库的信息
# \l+ 的输出比 \l 多了 Size, Tablespace 和 Description 列
postgres=# \l+
                                                                                     
    List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU 
Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                De
scription                 
-----------+----------+----------+-----------------+-------------+-------------+-----
-------+-----------+-----------------------+---------+------------+------------------
--------------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |     
       |           |                       | 7628 kB | pg_default | default administr
ative connection database
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |     
       |           | =c/postgres          +| 7473 kB | pg_default | unmodifiable empt
y database
           |          |          |                 |             |             |     
       |           | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |     
       |           | =c/postgres          +| 7708 kB | pg_default | default template 
for new databases
           |          |          |                 |             |             |     
       |           | postgres=CTc/postgres |         |            | 
(3 rows)

postgres=# SELECT datname FROM pg_database;
  datname  
-----------
 postgres
 template1
 template0
(3 rows)

创建库

postgres=# create database mydb;
CREATE DATABASE

删除库

postgres=# drop database mydb;
DROP DATABASE

切换库

postgres=# \c mydb 
You are now connected to database "mydb" as user "postgres".
mydb=# 

查看库大小

# pg_database_size() 函数以字节为单位返回数据库的大小
postgres=# SELECT pg_database_size('mydb');
 pg_database_size 
------------------
          7893475
(1 row)

# pg_size_pretty() 函数将字节转为更易于阅读值
postgres=# SELECT
postgres-#   pg_size_pretty(
postgres(#     pg_database_size('mydb')
postgres(#   );
 pg_size_pretty 
----------------
 7708 kB
(1 row)

# 所有库大小
postgres=# SELECT
postgres-#   datname,
postgres-#   pg_size_pretty(pg_database_size(datname)) AS size
postgres-#   FROM pg_database;
  datname  |  size   
-----------+---------
 postgres  | 7628 kB
 template1 | 7708 kB
 template0 | 7473 kB
 mydb      | 7708 kB
(4 rows)

数据表操作

列出表

mydb=# \dt;
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | cities  | table | postgres
 public | weather | table | postgres
(2 rows)

mydb=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | weather | table | postgres
(1 row)

mydb=# \d+
                                     List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method |  Size   | Descri
ption 
--------+---------+-------+----------+-------------+---------------+---------+-------
------
 public | weather | table | postgres | permanent   | heap          | 0 bytes | 
(1 row)

mydb=# SELECT * FROM pg_tables WHERE schemaname = 'public';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastrigge
rs | rowsecurity 
------------+-----------+------------+------------+------------+----------+----------
---+-------------
 public     | weather   | postgres   |            | f          | f        | f        
   | f
 public     | cities    | postgres   |            | f          | f        | f        
   | f
(2 rows)

创建表

        PostgreSQL支持标准的SQL类型int、smallint、real、double precision、char(N)、varchar(N)、date、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型。PostgreSQL中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字，除了SQL标准要求支持的特例外。

# weather 是表名
# varchar(80)指定了一个可以存储最长 80 个字符的任意字符串的数据类型
# int是普通的整数类型
# real是一种用于存储单精度浮点数的类型
# date类型应该可以自解释
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

# 在PostgreSQL中，point 是一个内置的几何数据类型，用于表示二维空间中的一个点
# point 数据类型用于存储一对坐标值（通常是X和Y）
CREATE TABLE cities (
    name            varchar(80),
    location        point
);

复制表

# 要将已有的 table_name 表复制为新表 new_table，包括表结构和数据，请使用以下语句
CREATE TABLE new_table AS TABLE table_name;

# 如果仅复制表结构，不复制数据，请在以上 CREATE TABLE 语句中添加 WITH NO DATA 子句
CREATE TABLE new_table AS TABLE table_name WITH NO DATA;

删除表

mydb=# drop table cities;
DROP TABLE

查看表结构

mydb=# \d weather;
                      Table "public.weather"
 Column  |         Type          | Collation | Nullable | Default 
---------+-----------------------+-----------+----------+---------
 city    | character varying(80) |           |          | 
 temp_lo | integer               |           |          | 
 temp_hi | integer               |           |          | 
 prcp    | real                  |           |          | 
 date    | date                  |           |          | 

# 如果要查看更多关于 test_date 表的信息，请使用 \d+ 命令
mydb=# \d+ weather;
                                                 Table "public.weather"
 Column  |         Type          | Collation | Nullable | Default | Storage  | Compre
ssion | Stats target | Description 
---------+-----------------------+-----------+----------+---------+----------+-------
------+--------------+-------------
 city    | character varying(80) |           |          |         | extended |       
      |              | 
 temp_lo | integer               |           |          |         | plain    |       
      |              | 
 temp_hi | integer               |           |          |         | plain    |       
      |              | 
 prcp    | real                  |           |          |         | plain    |       
      |              | 
 date    | date                  |           |          |         | plain    |       
      |              | 
Access method: heap

数据操作

添加数据

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)  VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

查询数据

mydb=# select * from weather;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
(2 rows)

修改数据

mydb=# UPDATE weather  SET prcp = 0.15  WHERE date = '1994-11-29';
UPDATE 1

删除数据

mydb=# DELETE FROM weather WHERE date = '1994-11-29';
DELETE 1