PostgreSQL常用语法总结

PostgreSQL常用语法总结。

codernavcom

1979人浏览 · 2024-03-18 15:15:26

codernavcom · 2024-03-18 15:15:26 发布

PostgreSQL常用语法总结

一、数据库连接

Psql -h IP -p PORT -U 用户名 -d 数据库名

以湖北电信postgresql为例：PostgreSQL 12.3

xxxx_test/Hb_xxxx2023
122.xxx.xxx.xxx:5432
psql -h 122.xxx.xxx.xxx -p 5432 -U xxxx_test -d xxxx_test

二、数据库操作

2.1 创建数据库

CREATE DATABASE dbname;

2.2 选择数据库

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

postgres=# \l

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 runoobdb  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

接下来我们可以使用 \c + 数据库名来进入数据库：

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

2.3 删除数据库

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

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

例如，我们删除一个 runoobdb 的数据库：

postgres=# DROP DATABASE runoobdb;

三、表操作

3.1 创建表

CREATE TABLE 语法格式如下：
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

3.2 查看表

我们可以使用 \d 命令来查看表格是否创建成功：

runoobdb=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

3.4 删除表

DROP TABLE table_name;

3.5 删除表数据

truncate table table_name；

3.6 修改表结构

1. 修改表名

alter table tbl_userrename tbl_user2;

2. 添加约束

altertable 表名 add constraint 表名_列名_nncheck (is not null);

3. 修改约束

alter table tbl_user modify password default'000000' not null;

4. 增加字段

alter table tab_gw_device add dev_sub_sn VARCHAR(6);

在指定列后增加字段

alter table tbl_useradd age number(3) default 18 not null after sex;

5. 修改字段

ALTER TABLE table_name ALTER column_name TYPE datatype;

6. 修改列名

alter table tbl_user rename column password to pwd;

7. 删除列

ALTER TABLE table_name DROP COLUMN column_name;

四、schema操作

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

4.1 创建schema

接下来我们连接到 runoobdb 来创建模式 myschema：

runoobdb=# create schema myschema;CREATE SCHEMA

输出结果 "CREATE SCHEMA" 就代表模式创建成功。

4.2 删除Schema

删除一个为空的模式（其中的所有对象已经被删除）：
DROP SCHEMA myschema;
删除一个模式以及其中包含的所有对象：
DROP SCHEMA myschema CASCADE;

五、DML语句

遵循标准的DML语句规范。

5.1 LIMIT语句

1. 读取几条数据

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

下面实例将找出限定的数量的数据，即读取 4 条数据：

runoobdb=# SELECT * FROM COMPANY LIMIT 4;

得到以下结果：

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

2. 读取一定范围内条数的数据

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]

下面是一个实例，从第三位开始提取 3 个记录：

runoobdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

得到以下结果：

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)