怎么用PostgreSQL学习SQL语言

最新推荐文章于 2024-05-05 17:53:08 发布
最新推荐文章于 2024-05-05 17:53:08 发布
阅读量449 收藏 1
点赞数
分类专栏： SQL 文章标签： postgresql sql 数据库 python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhuzipan/article/details/105717988
版权

1，安装 PostgreSQL

i，Mac OS 上安装 PostgreSQL 

请参考下面链接中的文章：

https://www.runoob.com/postgresql/mac-install-postgresql.html

ii，Windows 上安装 PostgreSQL 

请参考下面链接中的文章：

https://www.runoob.com/postgresql/windows-install-postgresql.html

iii，Linux 上安装 PostgreSQL

请参考下面链接中的文章：

https://www.runoob.com/postgresql/linux-install-postgresql.html

2，PostgreSQL 创建数据库

CREATE DATABASE 创建数据库 

CREATE DATABASE 命令需要在 PostgreSQL 命令窗口来执行，语法格式如下：

CREATE DATABASE dbname;

例如，我们创建一个 runoobdb 的数据库： 

postgres=# CREATE DATABASE runoobdb;

3，PostgreSQL 选择数据库 

数据库的命令窗口

PostgreSQL 命令窗口中，我们可以命令提示符后面输入 SQL 语句：

postgres=#

使用 \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

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

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

4，PostgreSQL 删除数据库

DROP DATABASE 删除数据库

DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录。

DROP DATABASE 只能由超级管理员或数据库拥有者执行。

DROP DATABASE 命令需要在 PostgreSQL 命令窗口来执行，语法格式如下：

DROP DATABASE [ IF EXISTS ] name

参数说明：

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

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

postgres=# DROP DATABASE runoobdb;

此时，可能会有个错误报出来，如下所示： 

ERROR:  database "runoobdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

这是因为当前数据库也在被别的用户使用，这里要删除也要慎重，确保数据库是可以被删除的再继续操作。
解决方式：
断开连接到这个数据库上的所有链接，再删除数据库。怎么断开呢？在PostgreSQL 9.2 及以上版本，执行下面的语句：

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='mydb' AND pid<>pg_backend_pid();

再执行

postgres=# DROP DATABASE runoobdb;

删除成功

语句说明：

  • pg_terminate_backend：用来终止与数据库的连接的进程id的函数。
  • pg_stat_activity：是一个系统表，用于存储服务进程的属性和状态。
  • pg_backend_pid()：是一个系统函数，获取附加到当前会话的服务器进程的ID。

5，PostgreSQL 创建表格 

PostgreSQL 使用 CREATE TABLE 语句来创建数据库表格。 

语法

CREATE TABLE 语法格式如下：

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

CREATE TABLE 是一个关键词，用于告诉数据库系统将创建一个数据表。

表名字必需在同一模式中的其它表、 序列、索引、视图或外部表名字中唯一。

CREATE TABLE 在当前数据库创建一个新的空白表，该表将由发出此命令的用户所拥有。

表格中的每个字段都会定义数据类型，如下：

实例

以下创建了一个表，表名为 COMPANY 表格，主键为 ID，NOT NULL 表示字段不允许包含 NULL 值：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

接下来我们再创建一个表格，在后面章节会用到：

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

\d tablename 查看表格信息：

runoobdb=# \d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default 
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | 
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
runoobdb=# \d department
                Table "public.department"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 id     | integer       |           | not null | 
 dept   | character(50) |           | not null | 
 emp_id | integer       |           | not null | 
Indexes:
    "department_pkey" PRIMARY KEY, btree (id)

6，PostgreSQL 删除表格 

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

语法

DROP TABLE 语法格式如下：

DROP TABLE table_name;

 删除上节中已创建的两个表格 ： 

runoobdb=# drop table department, company;
DROP TABLE

再使用 \d 命令来查看就找不到表格了：

runoobdb=# \d
Did not find any relations.

 7，PostgreSQL 模式（SCHEMA）

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

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

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

使用模式的优势：

  • 允许多个用户使用一个数据库并且不会互相干扰。

  • 将数据库对象组织成逻辑组以便更容易管理。

  • 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

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

语法

我们可以使用 CREATE SCHEMA 语句来创建模式，语法格式如下： 

CREATE TABLE myschema.mytable (
...
);

实例

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

runoobdb=# create schema myschema;
CREATE SCHEMA

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

接下来我们再创建一个表格：

runoobdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以上命令创建了一个空的表格，我们使用以下 SQL 来查看表格是否创建：

runoobdb=# select * from myschema.company;
 id | name | age | address | salary 
----+------+-----+---------+--------
(0 rows)

删除模式

删除一个为空的模式（其中的所有对象已经被删除）：

DROP SCHEMA myschema;

删除一个模式以及其中包含的所有对象：

DROP SCHEMA myschema CASCADE;

 再验证一下该命令是否执行成功了。

runoobdb=# DROP SCHEMA myschema;
ERROR:  cannot drop schema myschema because other objects depend on it
DETAIL:  table myschema.company depends on schema myschema
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
runoobdb=# DROP SCHEMA myschema CASCADE;
NOTICE:  drop cascades to table myschema.company
DROP SCHEMA
runoobdb=# select * from myschema.company;
ERROR:  relation "myschema.company" does not exist
LINE 1: select * from myschema.company;
                      ^
runoobdb=#

 8，PostgreSQL INSERT INTO 语句

PostgreSQL INSERT INTO 语句用于向表中插入新记录。

我们可以插入一行也可以同时插入多行。

语法

INSERT INTO 语句语法格式如下：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

  • column1, column2,...columnN 为表中字段名。

  • value1, value2, value3,...valueN 为字段对应的值。

在使用 INSERT INTO 语句时，字段列必须和数据值数量相同，且顺序也要对应。

如果我们向表中的所有字段插入值，则可以不需要指定字段，只需要指定插入的值即可：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

下表列出执行插入后返回结果的说明：

序号输出信息 & 描述
1

INSERT oid 1

只插入一行并且目标表具有 OID的返回信息， 那么 oid 是分配给被插入行的 OID。

2

INSERT 0 #

插入多行返回的信息， # 为插入的行数。

实例

在 runoobdb 数据库中创建 COMPANY 表：

runoobdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);

 输入\d确认表是否新建成功。

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

在 COMPANY 表中插入以下数据：

runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1

以下插入语句忽略 SALARY 字段：

runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1

以下插入语句 JOIN_DATE 字段使用 DEFAULT 子句来设置默认值，而不是指定值：

runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1

以下实例插入多行：

runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2

使用 SELECT 语句查询表格数据：

runoobdb=# SELECT * FROM company;

ID        NAME        AGE        ADDRESS     SALARY   JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

9，PostgreSQL SELECT 语句

PostgreSQL SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

语法

SELECT 语句语法格式如下：

SELECT column1, column2,...columnN FROM table_name;

  • column1, column2,...columnN 为表中字段名。

  • table_name 为表名。

如果我们想读取表中的所有数据可以使用以下 SQL 语句：

SELECT * FROM table_name;

在上一节 INSERT INTO 语句 中，已经向表 company 插入了一些数据，使用 * 号可以读取该表的所有数据： 

runoobdb=# SELECT * FROM company;

ID        NAME        AGE        ADDRESS     SALARY   JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

 也可以读取指定字段 ID 和 NAME：

runoobdb=# SELECT ID,NAME FROM company;
 id | name  
----+-------
  1 | Paul
  2 | Allen
  3 | Teddy
  4 | Mark
  5 | David
(5 rows)

10，PostgreSQL 运算符 

运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。

PostgreSQL 运算符是一个保留关键字或字符，一般用在 WHERE 语句中，作为过滤条件。

常见的运算符有：

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 按位运算符

算术运算符

假设变量 a 为 2，变量 b 为 3，则：

运算符描述实例
+加a + b 结果为 5
-减a - b 结果为 -1
*乘a * b 结果为 6
/除b / a 结果为 1
%模（取余）b % a 结果为 1
^指数a ^ b 结果为 8
|/平方根|/ 25.0 结果为 5
||/立方根||/ 27.0 结果为 3
!阶乘5 ! 结果为 120
!!阶乘（前缀操作符）!! 5 结果为 120

实例

runoobdb=# select 2+3;
 ?column? 
----------
        5
(1 row)

runoobdb=# select 2*3;
 ?column? 
----------
        6
(1 row)

runoobdb=# select 10/5;
 ?column? 
----------
        2
(1 row)

runoobdb=# select 10%5;
 ?column? 
----------
        0
(1 row)

runoobdb=# select 2^3;
 ?column? 
----------
        8
(1 row)

runoobdb=# select |/ 25.0;
 ?column? 
----------
        5
(1 row)

runoobdb=# select ||/ 27.0;
 ?column? 
----------
        3
(1 row)

runoobdb=# select 5 !;
 ?column? 
----------
      120
(1 row)

runoobdb=# select !! 5;
 ?column? 
----------
      120
(1 row)

比较运算符

假设变量 a 为 10，变量 b 为 20，则：

运算符描述实例
=等于(a = b) 为 false。
!=不等于(a != b) 为 true。
<>不等于(a <> b) 为 true。
>大于(a > b) 为 false。
<小于(a < b) 为 true。
>=大于等于(a >= b) 为 false。
<=小于等于(a <= b) 为 true。

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01

读取 SALARY 字段大于 50000 的数据：

runoobdb=# select * from company where salary > 50000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(2 rows)

读取 SALARY 字段等于 20000 的数据：

runoobdb=# select * from company where salary = 20000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
(2 rows)

读取 SALARY 字段不等于 20000 的数据：

runoobdb=# select * from company where salary != 20000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(5 rows)

runoobdb=# select * from company where salary <> 20000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(5 rows)

读取 SALARY 字段大于等于 65000 的数据：

runoobdb=# select * from company where salary >= 65000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(2 rows)

逻辑运算符

PostgreSQL 逻辑运算符有以下几种：

序号运算符 & 描述
1

AND

逻辑与运算符。如果两个操作数都非零，则条件为真。

PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。

2

NOT

逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。

PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。

 

3

OR

逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。

PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。

SQL 使用三值的逻辑系统，包括 true、false 和 null，null 表示"未知"。

aba AND ba OR b
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSEFALSEFALSEFALSE
FALSENULLFALSENULL
NULLNULLNULLNULL
aNOT a
TRUEFALSE
FALSETRUE
NULLNULL

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

读取 AGE 字段大于等于 25 且 SALARY 字段大于 6500 的数据：

runoobdb=# select * from company where age >= 25 and salary > 6500;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(4 rows)

读取 AGE 字段大于等于 25 或 SALARY 字段大于 6500 的数据：

runoobdb=# select * from company where age >= 25 or salary > 6500;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

读取 SALARY 字段不为 NULL 的数据：

runoobdb=# select * from company where salary is not null;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

位运算符

位运算符作用于位，并逐位执行操作。&、 | 和 ^ 的真值表如下所示：

pqp & qp | q
0000
0101
1111
1001

假设如果 A = 60，且 B = 13，现在以二进制格式表示，它们如下所示：

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A|B = 0011 1101

A^B = 0011 0001

~A  = 1100 0011

下表显示了 PostgreSQL 支持的位运算符。假设变量 A 的值为 60，变量 B 的值为 13，则：

运算符描述实例
&

按位与操作，按二进制位进行"与"运算。运算规则：

0&0=0;   
0&1=0;    
1&0=0;     
1&1=1;
(A & B) 将得到 12，即为 0000 1100
|

按位或运算符，按二进制位进行"或"运算。运算规则：

0|0=0;   
0|1=1;   
1|0=1;    
1|1=1;
(A | B) 将得到 61，即为 0011 1101
#

异或运算符，按二进制位进行"异或"运算。运算规则：

0#0=0;   
0#1=1;   
1#0=1;  
1#1=0;
(A # B) 将得到 49，即为 0011 0001
~

取反运算符，按二进制位进行"取反"运算。运算规则：

~1=0;   
~0=1;
(~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<<二进制左移运算符。将一个运算对象的各二进制位全部左移若干位（左边的二进制位丢弃，右边补0）。A << 2 将得到 240，即为 1111 0000
>>二进制右移运算符。将一个数的各二进制位全部右移若干位，正数左补0，负数左补1，右边丢弃。A >> 2 将得到 15，即为 0000 1111

 实例

runoobdb=# select 60 | 13;
 ?column?
----------
       61
(1 row)


runoobdb=# select 60 & 13;
 ?column?
----------
       12
(1 row)


runoobdb=#  select  (~60);
 ?column?
----------
      -61
(1 row)


runoobdb=# select  (60 << 2);
 ?column?
----------
      240
(1 row)


runoobdb=# select  (60 >> 2);
 ?column?
----------
       15
(1 row)


runoobdb=#  select 60 # 13;
 ?column?
----------
       49
(1 row)

11，PostgreSQL 表达式 

表达式是由一个或多个的值、运算符、PostgresSQL 函数组成的。

PostgreSQL 表达式类似一个公式，我们可以将其应用在查询语句中，用来查找数据库中指定条件的结果集。

语法

SELECT 语句的语法格式如下：

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

 

PostgreSQL 的表达式可以有不同类型，我们接下来会讲到。

布尔表达式

布尔表达式是根据一个指定条件来读取数据：

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

以下使用了布尔表达式（SALARY=10000）来查询数据：

runoobdb=# select * from company where salary = 10000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(1 row)

 

数字表达式

数字表达式常用于查询语句中的数学运算：

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

numerical_expression 是一个数学运算表达式，实例如下：

runoobdb=# select (17 + 6) as ADDITION;
 addition 
----------
       23
(1 row)

此外 PostgreSQL 还内置了一些数学函数，如：

  • avg() ： 返回一个表达式的平均值
  • sum() ： 返回指定字段的总和
  • count() ： 返回查询的记录总数

以下实例查询 COMPANY 表的记录总数：

runoobdb=# select count(*) as "RECORDS" from company;                   
 RECORDS 
---------
       7
(1 row)

日期表达式

日期表达式返回当前系统的日期和时间，可用于各种数据操作，以下实例查询当前时间：

runoobdb=# select current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-25 09:06:52.101221+08
(1 row)

12，PostgreSQL WHERE 子句

在 PostgreSQL 中，当我们需要根据指定条件从单张表或者多张表中查询数据时，就可以在 SELECT 语句中添加 WHERE 子句，从而过滤掉我们不需要数据。

WHERE 子句不仅可以用于 SELECT 语句中，同时也可以用于 UPDATE，DELETE 等等语句中。

语法

以下是 SELECT 语句中使用 WHERE 子句从数据库中读取数据的通用语法：

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]

我们可以在 WHERE 子句中使用比较运算符或逻辑运算符，例如 >, <, =, LIKE, NOT 等等。

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

 以下几个实例我们使用逻辑运算符来读取表中的数据。

AND

找出 AGE(年龄) 字段大于等于 25，并且 SALARY(薪资) 字段大于等于 65000 的数据：

runoobdb=# select * from company where age >= 25 and salary >= 65000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(2 rows)

OR

找出 AGE(年龄) 字段大于等于 25，或者 SALARY(薪资) 字段大于等于 65000 的数据：

runoobdb=# select * from company where age >= 25 or salary >= 65000;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(4 rows)

NOT NULL

在公司表中找出 AGE(年龄) 字段不为空的记录：

runoobdb=# select * from company where age is not null;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

LIKE

在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：

runoobdb=# select * from company where name like 'Pa%';
 id | name | age |                      address                       | salary | join_date  
----+------+-----+----------------------------------------------------+--------+------------
  1 | Paul |  32 | California                                         |  20000 | 2001-07-13
(1 row)

IN

以下 SELECT 语句列出了 AGE(年龄) 字段为 25 或 27 的数据：

runoobdb=# select * from company where age in (25, 27);
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(3 rows)

NOT IN

以下 SELECT 语句列出了 AGE(年龄) 字段不为 25 或 27 的数据：

runoobdb=# select * from company where age not in (25, 27);
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(4 rows)

BETWEEN

以下 SELECT 语句列出了 AGE(年龄) 字段在 25 到 27 的数据：

runoobdb=# select * from company where age between 25 and 27;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
(3 rows)

子查询

以下的 SELECT 语句使用了 SQL 的子查询，子查询语句中读取 SALARY(薪资) 字段大于 65000 的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的 AGE(年龄) 字段。

runoobdb=# select age from company where exists (select age from company where salary > 65000);
 age 
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

以下的 SELECT 语句同样使用了 SQL 的子查询，子查询语句中读取 SALARY(薪资) 字段大于 65000 的 AGE(年龄) 字段数据，然后用 > 运算符查询大于该 AGE(年龄) 字段数据：

runoobdb=# select * from company where age > (select age from company where salary > 65000);
 id | name | age |                      address                       | salary | join_date  
----+------+-----+----------------------------------------------------+--------+------------
  1 | Paul |  32 | California                                         |  20000 | 2001-07-13
(1 row)

13，PostgreSQL UPDATE 语句

如果我们要更新在 PostgreSQL 数据库中的数据，我们可以用 UPDATE 来操作。

语法

以下是 UPDATE 语句修改数据的通用 SQL 语法：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
  • 我们可以同时更新一个或者多个字段。
  • 我们可以在 WHERE 子句中指定任何条件。

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 | 2019-06-26
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
(7 rows)

以下实例将更新 COMPANY 表中 id 为 3 的 salary 字段值，并得到结果如下：

runoobdb=# update company set salary = 18000 where id = 3;
UPDATE 1
runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
  3 | Teddy |  23 | Norway                                             |  18000 | 2019-06-26
(7 rows)

从结果上看，COMPANY 表中的 id 为 3 的 salary 字段值已被修改。

以下实例将同时更新 salary 字段和 address 字段的值：

runoobdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

得到结果如下：

id | name  | age | address | salary
----+-------+-----+---------+--------
  1 | Paul  |  32 | Texas   |  20000
  2 | Allen |  25 | Texas   |  20000
  4 | Mark  |  25 | Texas   |  20000
  5 | David |  27 | Texas   |  20000
  6 | Kim   |  22 | Texas   |  20000
  7 | James |  24 | Texas   |  20000
  3 | Teddy |  23 | Texas   |  20000
(7 rows)

14，PostgreSQL DELETE 语句

你可以使用 DELETE 语句来删除 PostgreSQL 表中的数据。

语法

以下是 DELETE 语句删除数据的通用语法：

DELETE FROM table_name WHERE [condition];

如果没有指定 WHERE 子句，PostgreSQL 表中的所有记录将被删除。

一般我们需要在 WHERE 子句中指定条件来删除对应的记录，条件语句可以使用 AND 或 OR 运算符来指定一个或多个。

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |  15000 | 2007-12-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
  3 | Teddy |  23 | Norway                                             |  18000 | 2019-06-26
(7 rows)

以下 SQL 语句将删除 ID 为 2 的数据：

runoobdb=# delete from company where id = 2;
DELETE 1

得到结果如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2018-09-29
  5 | David |  27 | Texas                                              |  85000 | 2017-10-01
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2016-01-01
  7 | James |  24 | Houston                                            |  10000 | 2015-05-01
  3 | Teddy |  23 | Norway                                             |  18000 | 2019-06-26
(6 rows)

从上面结果可以看出，id 为 2 的数据已被删除。

以下语句将删除整张 COMPANY 表：

DELETE FROM COMPANY;

15，PostgreSQL LIKE 子句

在 PostgreSQL 数据库中，我们如果要获取包含某些字符的数据，可以使用 LIKE 子句。

在 LIKE 子句中，通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中，主要有以下两种通配符：

  • 百分号 %
  • 下划线 _

如果没有使用以上两种通配符，LIKE 子句和等号 = 得到的结果是一样的。

语法

以下是使用 LIKE 子句搭配百分号 % 和下划线 _ 从数据库中获取数据的通用语法：

SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';

你可以在 WHERE 子句中指定任何条件。

你可以使用 AND 或者 OR 指定一个或多个条件。

XXXX 可以是任何数字或者字符。

实例

下面是 LIKE 语句中演示了 % 和 _ 的一些差别:

实例描述
WHERE SALARY::text LIKE '200%'找出 SALARY 字段中以 200 开头的数据。
WHERE SALARY::text LIKE '%200%'找出 SALARY 字段中含有 200 字符的数据。
WHERE SALARY::text LIKE '_00%'找出 SALARY 字段中在第二和第三个位置上有 00 的数据。
WHERE SALARY::text LIKE '2 % %'找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据。
WHERE SALARY::text LIKE '%2'找出 SALARY 字段中以 2 结尾的数据
WHERE SALARY::text LIKE '_2%3'找出 SALARY 字段中 2 在第二个位置上并且以 3 结尾的数据
WHERE SALARY::text LIKE '2___3'找出 SALARY 字段中以 2 开头，3 结尾并且是 5 位数的数据

在 PostgreSQL 中，LIKE 子句是只能用于对字符进行比较，因此在上面列子中，我们要将整型数据类型转化为字符串数据类型。

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12

下面实例将找出 AGE 以 2 开头的数据：

runoobdb=# select * from company where age::text like '2%';
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
(6 rows)

 下面实例将找出 address 字段中含有 - 字符的数据：

runoobdb=# select * from company where address like '%-%';                                                                                                                                        id | name | age |                      address                       | salary | join_date  
----+------+-----+----------------------------------------------------+--------+------------
  4 | Mark |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  6 | Kim  |  22 | South-Hall                                         |  45000 | 2019-09-12
(2 rows)

16，PostgreSQL LIMIT 子句

PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。

语法

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

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

下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

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

 

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
(7 rows)

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

runoobdb=# select * from company limit 4;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
(4 rows)

但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。

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

runoobdb=# select * from company limit 4 offset 2;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
(4 rows)

17，PostgreSQL ORDER BY 语句

在 PostgreSQL 中，ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。

语法

ORDER BY 子句的基础语法如下：

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

 

您可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。

ASC 表示升序，DESC 表示降序。

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
(7 rows)

下面实例将对结果根据 AGE 字段值进行升序排列：

runoobdb=# select * from company order by age asc;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
(7 rows)

下面实例将对结果根据 NAME 字段值和 SALARY 字段值进行升序排序：

runoobdb=# select * from company order by name, salary asc;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
(7 rows)

下面实例将对结果根据NAME字段值进行降序排列：

runoobdb=# select * from company order by name desc;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
(7 rows)

18，PostgreSQL GROUP BY 语句

在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。

GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

语法

下面给出了 GROUP BY 子句的基本语法:

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

 GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

在 GROUP BY 子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中。

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
(7 rows)

面实例将根据 NAME 字段值进行分组，找出每个人的工资总额：

runoobdb=# select name, sum(salary) from company group by name;
 name  |  sum  
-------+-------
 Teddy | 18000
 David | 85000
 Paul  | 20000
 Kim   | 45000
 Mark  | 65000
 Allen | 27500
 James | 10000
(7 rows)

现在我们添加使用下面语句在 CAMPANY 表中添加三条记录：

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00, '2017-5-4');
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00, '2018-6-18');
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00, '2019-11-11');

现在 COMPANY 表中存在重复的名称，数据如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  8 | Paul  |  24 | Houston                                            |  20000 | 2017-05-04
  9 | James |  44 | Norway                                             |   5000 | 2018-06-18
 10 | James |  45 | Texas                                              |   5000 | 2019-11-11
(10 rows)

现在再根据 NAME 字段值进行分组，找出每个客户的工资总额：

runoobdb=# select name, sum(salary) from company group by name order by name; 
 name  |  sum  
-------+-------
 Allen | 27500
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 18000
(7 rows)

下面实例将 ORDER BY 子句与 GROUP BY 子句一起使用：

runoobdb=# select name, sum(salary) from company group by name order by name desc;
 name  |  sum  
-------+-------
 Teddy | 18000
 Paul  | 40000
 Mark  | 65000
 Kim   | 45000
 James | 20000
 David | 85000
 Allen | 27500
(7 rows)

19，PostgreSQL WITH 子句

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。

WITH 子句在使用前必须先定义。

语法

WITH 查询的基础语法如下：

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

WITH 递归

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  8 | Paul  |  24 | Houston                                            |  20000 | 2017-05-04
  9 | James |  44 | Norway                                             |   5000 | 2018-06-18
 10 | James |  45 | Texas                                              |   5000 | 2019-11-11
(10 rows)

 下面将使用 WITH 子句在上表中查询数据：

runoobdb=# with cte as (select id, name, age, address, salary, join_date from company) select * from cte;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  8 | Paul  |  24 | Houston                                            |  20000 | 2017-05-04
  9 | James |  44 | Norway                                             |   5000 | 2018-06-18
 10 | James |  45 | Texas                                              |   5000 | 2019-11-11
(10 rows)

接下来让我们使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和：

runoobdb=# with recursive t(n) as ( values(0) union all select salary from company where salary < 20000) select sum(n) from t;
  sum  
-------
 38000
(1 row)

下面我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中：

runoobdb=# create table company1(
            id int primary key not null, 
            name text not null, 
            age int not null, 
            address char(50), 
            salary real,
            join_date date);   

runoobdb=# select * from company1;                                               
id | name | age | address | salary | join_date 
----+------+-----+---------+--------+-----------
(0 rows)

runoobdb=# with moved_rows as ( delete from company where salary >= 30000 returning *) insert into company1 (select * from moved_rows);
INSERT 0 3

此时，CAMPANY 表和 CAMPANY1 表的数据如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  7 | James |  24 | Houston                                            |  10000 | 2020-02-12
  8 | Paul  |  24 | Houston                                            |  20000 | 2017-05-04
  9 | James |  44 | Norway                                             |   5000 | 2018-06-18
 10 | James |  45 | Texas                                              |   5000 | 2019-11-11
(7 rows)

runoobdb=# select * from company1;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2017-04-26
  5 | David |  27 | Texas                                              |  85000 | 2018-04-06
  6 | Kim   |  22 | South-Hall                                         |  45000 | 2019-09-12
(3 rows)

20，PostgreSQL HAVING 子句

HAVING 子句可以让我们筛选分组后的各组数据。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

语法

下面是 HAVING 子句在 SELECT 查询中的位置：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

 HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

实例

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |   6500 | 2018-05-20
  5 | David |  27 | Texas                                              |  80500 | 2018-07-20
  6 | Kim   |  22 | South-Hall                                         |  40500 | 2019-07-10
  7 | James |  24 | Houston                                            |  10000 | 2017-03-02
  8 | Paul  |  24 | Houston                                            |  20000 | 2018-09-02
  9 | James |  44 | Norway                                             |   5000 | 2017-09-20
 10 | James |  45 | Texas                                              |   5000 | 2015-10-02
(10 rows)

 下面实例将找出根据 NAME 字段值进行分组，并且 name(名称) 字段的计数少于 2 数据：

runoobdb=# select name from company group by name having count(name) < 2;
 name  
-------
 Teddy
 David
 Kim
 Mark
 Allen
(5 rows)

下面实例将找出根据 name 字段值进行分组，并且名称的计数大于 1 数据：

runoobdb=# select name from company group by name having count(name) > 1;
 name  
-------
 Paul
 James
(2 rows)

21，PostgreSQL DISTINCT 关键字

在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

语法

用于去除重复记录的 DISTINCT 关键字的基本语法如下：

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

创建 COMPANY 表，数据内容如下：

runoobdb=# select * from company;
 id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2014-01-01
  2 | Allen |  25 | Texas                                              |  27500 | 2015-02-18
  3 | Teddy |  23 | Norway                                             |  18000 | 2016-03-28
  4 | Mark  |  25 | Rich-Mond                                          |   6500 | 2018-05-20
  5 | David |  27 | Texas                                              |  80500 | 2018-07-20
  6 | Kim   |  22 | South-Hall                                         |  40500 | 2019-07-10
  7 | James |  24 | Houston                                            |  10000 | 2017-03-02
  8 | Paul  |  24 | Houston                                            |  20000 | 2018-09-02
  9 | James |  44 | Norway                                             |   5000 | 2017-09-20
 10 | James |  45 | Texas                                              |   5000 | 2015-10-02
(10 rows)

接下来我们找出 COMPANY 表中的所有 NAME：

runoobdb=# select name from company;
 name  
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 James
 James
(10 rows)

现在我们在 SELECT 语句中使用 DISTINCT 子句：

runoobdb=# select distinct name from company;
 name  
-------
 Teddy
 David
 Paul
 Kim
 Mark
 Allen
 James
(7 rows)

从结果可以看到，重复数据已经被删除。

 

参考资料：

https://www.runoob.com/postgresql/postgresql-tutorial.html

 

樊晓鑫
关注
专栏目录
PostgreSQL入门与进阶学习，体系化的SQL知识，完成终极目标高可用与容灾，性能优化与架构设计，以及安全策略
一个追逐梦想的码农
07-28 3014
对于后端开发者而言，对数据库的熟悉程度不仅是岗位需求的基础，更是职业发展中不可或缺的一环，其全面性对于技术定级具有显著的推动作用。在选择学习的数据库时，建议从广泛应用的postgresql入手，因其在互联网领域占据重要地位。接下来，让我们逐步深入，从基础到进阶，系统梳理数据库技术的关键点。
史上最全：PostgreSQL SQL的基础使用及技巧
iihero@CSDN
03-24 2002
refer to: https://www.postgresql.org/docs/14/datatype.html 为避免一些不必要的麻烦，不要用关键字去定义数据库对象的名字。它会给PG的解析造成潜在的问题。尤其是在生产环境中，不要给自己制造麻烦。 E前缀，C风格的转义符 位串常量，二进制使用B前缀，16进制采用X作前缀 3、简单SQL 3.1、基础SQL 建表 删表 更改表 see: https://www.postgresql.org/docs/14/sq
PostgreSQL的基本使用
最新发布
m0_72560900的博客
05-05 2508
在 PostgreSQL 中，Schema（模式）是数据库对象的集合，用于组织和命名数据库对象，如表、视图、索引、函数等。Schema 可以看作是一个命名空间，用于将数据库对象划分为逻辑上的分组，方便管理和控制访问权限。命名空间管理：Schema 提供了命名空间，可以将数据库对象按照业务逻辑或功能进行组织和分类，避免对象命名冲突，提高数据库的可维护性和可扩展性。对象隔离。
POSTSQL
人生可不可以停下来休息休息
04-21 2279
推荐一个 http://zhaozhenhua88.spaces.msn.comCREATE FUNCTION archive_customer() RETURNS TRIGGER AS   BEGIN     INSERT INTO customer_archive         VALUES       (         OLD.customer_id,         OLD.cu
postgresql基本使用
进行中
12-19 521
1、将字符串转成数字 select sum(to_number(xq_total,'99G999D9S')) from food_basic_info_1_copy 2、修改字段类型 alter table food_xq_work_param_shinei alter COLUMN is_remote type varchar(100) ; 3、增加自增长的列 ALTER tab...
PostgreSQL使用with一例
weixin_33709364的博客
01-20 144
为什么80%的码农都做不了架构师？>>> ...
PostgreSQL 8.1 SQL语言详解
"PostgreSQL 8.1 中文文档提供了关于使用SQL语言在PostgreSQL数据库管理系统中的详细指导。文档从SQL语法基础开始，包括词法结构和值表达式，逐步介绍如何创建和管理数据结构，如表、默认值、约束、系统字段、权限和...
PostgreSQL:SQL资源
04-08
SQL（Structured Query Language）是用于管理关系数据库的标准语言，而PostgreSQL提供了丰富的SQL支持，使其成为开发人员、数据分析师和系统管理员的重要工具。在本文中，我们将深入探讨PostgreSQL中的SQL资源，以及...
PostgreSQL 8.1 SQL语言教程与数据操作详解
本资源是一份详细的PostgreSQL 8.1中文文档，主要涵盖SQL语言的使用、数据定义、数据操作、查询方法、数据类型及相应的函数和操作符，适合不同层次的用户学习。以下是各部分的主要知识点概要： 1. **SQL语言**： -...
SQL：Exemplos e Projetos和一种语言SQL（Utilizando MySQL，PostgreSQL，服务器SQL）
02-19
本资源集合主要提供了SQL语言的示例和项目，涵盖了MySQL、PostgreSQL以及SQL Server这三种流行的数据库管理系统。 MySQL是一款开源、免费的关系型数据库管理系统，因其高效性和易用性而在Web应用中广泛应用。它支持...
postgersql中文手册
03-12
postgersql 8的数据库 中文手册 ，很全面的。
postgreSQL空间SQL操作
10-29
需要安装Postgis插件都懂的吧，资源是postgreSQL的关于空间数据字段the_geom的一些SQL示例，写的比较模糊，主要是想网上留一份以防丢失。有需要或不明白的找我吧，我研究的也不多，基本的会一点，有需要的可以一起学习。（QQ：541480365）
PostgreSQL教程（三）：SQL语言
mr~li的博客
02-19 1731
比如，假设我们想找出那些在其它天气记录的温度范围之外的天气记录。虽然列在每行里的顺序是固定的，但一定要记住SQL并不对行在表中的顺序做任何保证（但你可以为了显示的目的对它们进行显示地排序）。， 因为在连接操作符左部的表中的行在输出中至少要出现一次， 而在右部的表的行只有在能找到匹配的左部表行是才被输出。如果输出的左部表的行没有对应匹配的右部表的行，那么右部表行的列将填充空值（null）。在随后的例子里，我们假设你已经创建了名为mydb的数据库，就像在前面的章节里介绍的一样，并且已经能够启动psql。
PostgreSQL with子句使用
kwame211的博客
11-28 352
PostgreSQL提供的一种方法，用于写一个大的查询中使用的辅助报表与查询，能使复杂和大型查询简单易读。 1：with子句的使用  使用现有数据库查询，查询在2017.05.01号0点到2017.05.08号0点之间的充值玩家的id,sid,期间充值总数num。  命令如下：  with u as (select id, sum(amount) as num from pay where pa...
PostgreSQL初识 之 SQL语句
Wwinky的博客
07-09 458
文章目录SQL语法数据类型1. 精确的数字数据类型2. 近似数字数据类型3. 日期和时间数据类型4. 字符串数据类型5. Unicode字符串数据类型6. 二进制数据类型7. 其它数据类型运算符1. SQL算术运算符2. SQL比较运算符3. SQL逻辑运算符 SQL语法  在数据库中大部分操作都是由SQL语句完成的，SQL语句都是以 select，insert，update，delete，alter，drop，create，use，show等关键字开头，以 ; 结尾，SQL语句对大小写不敏感。其中： S
PostgreSQL 基础（一）-- 新手教程
热门推荐
chinusyan的专栏
05-29 1万+
PostgreSQL 基础（一）-- 教程
PostgreSQL（一）教程 -----SQL语言
chinaherolts2008的博客
08-12 793
一、概念 PostgreSQL是一种关系型数据库管理系统（RDBMS）。这意味着它是一种用于管理存储在关系中的数据的系统。关系实际上是表的数学术语。 今天，把数据存储在表里的概念已经快成了固有的常识了， 但是还有其它的一些方法用于组织数据库。在类 Unix 操作系统上的文件和目录就形成了一种层次数据库的例子。 更现代的发展是面向对象数据库。 每个表都是一个命名的行集合。一个给定表的每一行由同一组的命名列组成，而且每一列都有一个特定的数据类型。虽然列在每行里的顺序是固定的， 但一定要记住 S...
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 7 章 查询_7.8. WITH查询（公共表表达式）...
weixin_34029949的博客
10-01 280
7.8.WITH查询（公共表表达式） 7.8.1.WITH中的SELECT 7.8.2.WITH中的数据修改语句 WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDA...
postgresql 中的 with 用法
weixin_30419799的博客
04-17 2034
1 with 可以替代连接 让sql 更简洁 逻辑更清晰 2 with 语句备份要删除的语句 WITH delete_rows AS ( DELETE FROM company WHERE salary < 10000 RETURNING * ) INSERT INTO company1 ( SELECT * FROM delete_rows ); 　　注意：不要忘记加上re...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值