怎么用PostgreSQL学习SQL语言

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 表格,主键为 IDNOT 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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值