PostgreSQL - 数据库与表格操作

最新推荐文章于 2025-12-28 02:24:01 发布

原创

最新推荐文章于 2025-12-28 02:24:01 发布 · 2k 阅读

标签

#postgresql #big data #数据库

PostgreSQL 数据库与表格操作

1. 创建数据库
- 1.1 CREATE DATABASE 创建数据库
- 1.2 createdb 命令创建数据库
2. 选择数据库
3. 删除数据库
- 3.1 DROP DATABASE 删除数据库
- 3.2 dropdb 命令删除数据库
4. 创建表格
5. 删除表格
6. 模式（SCHEMA）
- 6.1 语法
- 6.2 实例
- 6.3 删除模式
7. INSERT INTO 语句
8. SELECT
9. 运算符
- 9.1 算术运算符
- 9.2 比较运算符
- 9.3 逻辑运算符
- 9.4 位运算符
10. 表达式
- 10.1 语法
- 10.2 布尔表达式
- 10.3 数字表达式
- 10.4 日期表达式
11. WHERE 子句
- 11.1 语法
- 11.2 AND
- 11.3 OR
- 11.4 NOT NULL
- 11.5 LIKE
- 11.6 IN
- 11.7 NOT IN
- 11.8 BETWEEN
- 11.9 子查询
12. UPDATE 语句
13. DELETE 语句
14. LIKE 子句
15. LIMIT 子句
16. ORDER BY 语句
17. GROUP BY 语句
18. WITH 语句
19. HAVING 子句
20. DISTINCT 关键字

1. 创建数据库

PostgreSQL 创建数据库可以用以下三种方式：

使用 CREATE DATABASE SQL 语句来创建；
使用 createdb 命令来创建；
使用 pdAdmin 工具；

1.1 CREATE DATABASE 创建数据库

语法：

CREATE DATABASE dbname;

栗子：

postgres=# CREATE DATABASE testdb;

1.2 createdb 命令创建数据库

createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
语法：

createdb [option ...] [dbname [description]]

参数说明：

dbname：要创建的数据库名；
description：关于新创建的数据库相关的说明；
options：参数可选项；

选项	描述
-D tablespace	指定数据库默认表空间。
-e	将 createdb 生成的命令发送到服务端。
-E encoding	指定数据库的编码。
-l locale	指定数据库的语言环境。
-T template	指定创建此数据库的模板。
–help	显示 createdb 命令的帮助信息。
-h host	指定服务器的主机名。
-p port	指定服务器监听的端口，或者 socket 文件。
-U username	连接数据库的用户名。
-w	忽略输入密码。
-W	连接时强制要求输入密码。

使用封装的命令 createdb创建命令时，不能已经登录到 pg 数据库命令行，而需要在数据库安装的 bin 目录下，直接使用命令创建；
栗子：

[10307440@zte.intra@LIN-874286D0CB5 bin]$ createdb -h localhost -p 5432 -U postgres sdtestbd
口令: 
[10307440@zte.intra@LIN-874286D0CB5 bin]$

以上命令表示：使用 postgres 用户登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并创建 sdtestdb 数据库。

2. 选择数据库

使用\l 查看已经存在的数据库：

postgres=# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 sdtestbd  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 行记录)

使用 \c + 数据库名 进入数据库：

postgres=# \c sdtestbd 
您现在已经连接到数据库 "sdtestbd",用户 "postgres".

3. 删除数据库

PostgreSQL 删除数据库可以用以下三种方式：

使用 DROP DATABASE SQL 语句来删除；
使用 dropdb 命令来删除；
使用 pgAdmin 工具；

3.1 DROP DATABASE 删除数据库

DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录。
DROP DATABASE 只能由超级管理员或数据库拥有者执行。
DROP DATABASE 命令需要在 PostgreSQL 命令窗口来执行，语法格式如下：

DROP DATABASE [ IF EXISTS ] name

参数说明：
- IF EXISTS：如果数据库不存在则发出提示信息，而不是错误信息。
- name：要删除的数据库的名称。
栗子：

postgres=# DROP DATABASE sdtestbd;
DROP DATABASE
postgres=# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 行记录)

3.2 dropdb 命令删除数据库

dropdb 是 DROP DATABASE 的包装器。
dropdb 用于删除 PostgreSQL 数据库。
dropdb 命令只能由超级管理员或数据库拥有者执行。
语法格式：

dropdb [connection-option...] [option...] dbname

参数说明：

dbname：要删除的数据库名。
options：参数可选项，可以是以下值：

选项	描述
-e	显示 dropdb 生成的命令并发送到数据库服务器。
-i	在做删除的工作之前发出一个验证提示。
-V	打印 dropdb 版本并退出。
–if-exists	如果数据库不存在则发出提示信息，而不是错误信息。
–help	显示有关 dropdb 命令的帮助信息。
-h host	指定运行服务器的主机名。
-p port	指定服务器监听的端口，或者 socket 文件。
-U username	连接数据库的用户名。
-w	连接数据库的用户名。
-W	连接时强制要求输入密码。
–maintenance-db=dbname	删除数据库时指定连接的数据库，默认为 postgres，如果它不存在则使用 template1。

跟创建数据库时的命令一样，还是得进入 bin 目录下：

dropdb -h localhost -p 5432 -U postgres runoobdb

4. 创建表格

PostgreSQL 使用 CREATE TABLE 语句来创建数据库表格。
CREATE TABLE 语法格式如下：

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

CREATE TABLE 是一个关键词，用于告诉数据库系统将创建一个数据表。
表名字必需在同一模式中的其它表、序列、索引、视图或外部表名字中唯一。
CREATE TABLE 在当前数据库创建一个新的空白表，该表将由发出此命令的用户所拥有。
表格中的每个字段都会定义数据类型，如下：
栗子：

postgres=# CREATE DATABASE pgtestdb
postgres-# ;
CREATE DATABASE
postgres=# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 pgtestdb  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 行记录)

postgres=# \c pgtestdb 
您现在已经连接到数据库 "pgtestdb",用户 "postgres".
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(#   ID    INT     PRIMARY KEY    NOT NULL,
pgtestdb(#   NAME  TEXT    NOT NULL,
pgtestdb(#   ADDRESS    CHAR(50),
pgtestdb(#   SALARY    REAL
pgtestdb(# );
CREATE TABLE

然后使用 \d 查看表是否创建成功：

pgtestdb=# \d
                关联列表
 架构模式 |  名称   |  类型  |  拥有者  
----------+---------+--------+----------
 public   | company | 数据表 | postgres
(1 行记录)

使用 \d tablename 查看表格信息：

pgtestdb=# \d COMPANY
               数据表 "public.company"
  栏位   |     类型      | 校对规则 |  可空的  | 预设 
---------+---------------+----------+----------+------
 id      | integer       |          | not null | 
 name    | text          |          | not null | 
 address | character(50) |          |          | 
 salary  | real          |          |          | 
索引：
    "company_pkey" PRIMARY KEY, btree (id)

即兴，在创建一个表（教程说后续会用）：

pgtestdb=# CREATE TABLE DEPARTMENT(
pgtestdb(#   ID    INT    PRIMARY KEY    NOT NULL,
pgtestdb(#   EDPT  CHAR(50)    NOT NULL,
pgtestdb(#   EMP_ID    INT    NOT NULL
pgtestdb(# );
CREATE TABLE
pgtestdb=# \d
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者  
----------+------------+--------+----------
 public   | company    | 数据表 | postgres
 public   | department | 数据表 | postgres
(2 行记录)

5. 删除表格

PostgreSQL 使用 DROP TABLE 语句来删除表格，包含表格数据、规则、触发器等，所以删除表格要慎重，删除后所有信息就消失了。
语法：

DROP TABLE table_name;

栗子：

pgtestdb=# \d
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者  
----------+------------+--------+----------
 public   | company    | 数据表 | postgres
 public   | department | 数据表 | postgres
(2 行记录)

pgtestdb=# DROP TABLE department;
DROP TABLE
pgtestdb=# \d
                关联列表
 架构模式 |  名称   |  类型  |  拥有者  
----------+---------+--------+----------
 public   | company | 数据表 | postgres
(1 行记录)