文章目录

在 Windows 上安装 PostGreSql

下载安装包

打开下面的网址

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

1 2	https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

选择你需要的版本:

PostgreSQL 基础教程-Python 技术分享 Java技术分享 Python 爬虫技术_微信公众号:zeropython—昊天博客

一路 next ,直到成功

开始菜单中找到:

登录到 Sql 控制台

说明已经安装完成

常用的SQL

数据库

创建数据库

postgres=# create database songhao;
CREATE DATABASE

postgres=# create database songhao;

CREATE DATABASE

列出数据库

# \l

# \l

选择数据库

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

postgres=# \c songhao;

您现在已经连接到数据库 "songhao",用户 "postgres".

删除数据库

songhao=# create database songhao01;
CREATE DATABASE
songhao=# drop database songhao01;
DROP DATABASE

songhao=# create database songhao01;

CREATE DATABASE

songhao=# drop database songhao01;

DROP DATABASE

表操作

创建表

songhao=# create table company(
songhao(# id int primary key not null,
songhao(# name text not null,
songhao(# age int not null,
songhao(# address char(50),
songhao(# salary real);
CREATE TABLE

songhao=# create table company(

songhao(# id int primary key not null,

songhao(# name text not null,

songhao(# age int not null,

songhao(# address char(50),

songhao(# salary real);

CREATE TABLE

查看当前表是否成功

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

songhao=# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+---------+--------+----------

public | company | 数据表 | postgres

(1 行记录)

查看表信息

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

songhao=# \d company

数据表 "public.company"

栏位 | 类型 | 校对规则 | 可空的 | 预设

---------+---------------+----------+----------+------

id | integer | | not null |

name | text | | not null |

age | integer | | not null |

address | character(50) | | |

salary | real | | |

索引：

"company_pkey" PRIMARY KEY, btree (id)

删除表格

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

songhao=# drop table company,department;
DROP TABLE
songhao=# \d
没有找到任何关系.
songhao=#

songhao-# \d

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+------------+--------+----------

public | company | 数据表 | postgres

public | department | 数据表 | postgres

(2 行记录)

songhao=# drop table company,department;

DROP TABLE

songhao=# \d

没有找到任何关系.

songhao=#

创建 schema

create schema myschema;
# 根据 schema 创建表
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)
);

create schema myschema;

# 根据 schema 创建表

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)

);

删除 schema

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

DROP SCHEMA myschema;
# 删除一个模式以及其中包含的所有对象：

DROP SCHEMA myschema CASCADE;

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

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

插入数据

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

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

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)

VALUES (value1, value2, value3,...valueN);

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

插入多行数据

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');

1 2	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');

查询表数据

songhao=# select * from company;

1 2	songhao=# select * from company;

运算符

算数运算符

songhao=# select 2+3;
 ?column?
----------
        5
(1 行记录)


songhao=# select 2*3;
 ?column?
----------
        6
(1 行记录)


songhao=# select 10/5;
 ?column?
----------
        2
(1 行记录)


songhao=# select 12%5;
 ?column?
----------
        2
(1 行记录)


songhao=# select 2^3;
 ?column?
----------
        8
(1 行记录)


songhao=# select |/ 25;
 ?column?
----------
        5
(1 行记录)


songhao=# select ||/ 23;
      ?column?
--------------------
 2.8438669798515654
(1 行记录)


songhao=# select ||/ 27;
 ?column?
----------
        3
(1 行记录)


songhao=# select !5;
错误:  操作符不存在: ! integer
第1行select !5;
            ^
提示:  没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
songhao=# select !!5;
 ?column?
----------
      120
(1 行记录)

songhao=# select 2+3;

?column?

----------

(1 行记录)

songhao=# select 2*3;

?column?

----------

(1 行记录)

songhao=# select 10/5;

?column?

----------

(1 行记录)

songhao=# select 12%5;

?column?

----------

(1 行记录)

songhao=# select 2^3;

?column?

----------

(1 行记录)

songhao=# select |/ 25;

?column?

----------

(1 行记录)

songhao=# select ||/ 23;

?column?

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

2.8438669798515654

(1 行记录)

songhao=# select ||/ 27;

?column?

----------

(1 行记录)

songhao=# select !5;

错误: 操作符不存在: ! integer

第1行select !5;

提示: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.

songhao=# select !!5;

?column?

----------

120

(1 行记录)

比较运算符

songhao=# select * from COMPANY;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |        | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(5 行记录)


songhao=# SELECT * FROM COMPANY WHERE SALARY > 50000;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(2 行记录)


songhao=# 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 |
(2 行记录)


songhao=# SELECT * FROM COMPANY WHERE SALARY != 20000;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(2 行记录)


songhao=# SELECT * FROM COMPANY WHERE SALARY <> 20000;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(2 行记录)


songhao=# SELECT * FROM COMPANY WHERE SALARY >= 65000;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(2 行记录)

songhao=# select * from COMPANY;

----+-------+-----+----------------------------------------------------+--------+------------

1 | Paul | 32 | California | 20000 | 2001-07-13

2 | Allen | 25 | Texas | | 2007-12-13

3 | Teddy | 23 | Norway | 20000 |

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(5 行记录)

songhao=# SELECT * FROM COMPANY WHERE SALARY > 50000;

----+-------+-----+----------------------------------------------------+--------+------------

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(2 行记录)

songhao=# SELECT * FROM COMPANY WHERE SALARY = 20000;

----+-------+-----+----------------------------------------------------+--------+------------

1 | Paul | 32 | California | 20000 | 2001-07-13

3 | Teddy | 23 | Norway | 20000 |

(2 行记录)

songhao=# SELECT * FROM COMPANY WHERE SALARY != 20000;

----+-------+-----+----------------------------------------------------+--------+------------

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(2 行记录)

songhao=# SELECT * FROM COMPANY WHERE SALARY <> 20000;

----+-------+-----+----------------------------------------------------+--------+------------

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(2 行记录)

songhao=# SELECT * FROM COMPANY WHERE SALARY >= 65000;

----+-------+-----+----------------------------------------------------+--------+------------

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(2 行记录)

逻辑运算符

songhao=# select * from COMPANY;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |        | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(5 行记录)


songhao=#  SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(3 行记录)


songhao=# 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                                              |        | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(5 行记录)


songhao=# SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(4 行记录)

songhao=# select * from COMPANY;

----+-------+-----+----------------------------------------------------+--------+------------

1 | Paul | 32 | California | 20000 | 2001-07-13

2 | Allen | 25 | Texas | | 2007-12-13

3 | Teddy | 23 | Norway | 20000 |

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(5 行记录)

songhao=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;

----+-------+-----+----------------------------------------------------+--------+------------

1 | Paul | 32 | California | 20000 | 2001-07-13

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(3 行记录)

songhao=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;

----+-------+-----+----------------------------------------------------+--------+------------

1 | Paul | 32 | California | 20000 | 2001-07-13

2 | Allen | 25 | Texas | | 2007-12-13

3 | Teddy | 23 | Norway | 20000 |

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(5 行记录)

songhao=# SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;

----+-------+-----+----------------------------------------------------+--------+------------

1 | Paul | 32 | California | 20000 | 2001-07-13

3 | Teddy | 23 | Norway | 20000 |

4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13

5 | David | 27 | Texas | 85000 | 2007-12-13

(4 行记录)

PostgreSQL 表达式

语法

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

SELECT column1, column2, columnN

FROM table_name

WHERE [CONDITION | EXPRESSION];

PostgreSQL WHERE 语句

语法

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

SELECT column1, column2, columnN

FROM table_name

WHERE [condition1]

PostgreSQL UPDATE 语句

语法

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

举个例子

UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

1 2	UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

PostgreSQL DELETE 语句

语法

DELETE FROM table_name WHERE [condition];
eg:
DELETE FROM COMPANY WHERE ID = 2;
# 删除整张表
DELETE FROM COMPANY;

DELETE FROM table_name WHERE [condition];

eg:

DELETE FROM COMPANY WHERE ID = 2;

# 删除整张表

DELETE FROM COMPANY;

PostgreSQL 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_';

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_';

实例

下面是 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 位数的数据

songhao=# select * from company where age::text like '2%';
# 将找出 AGE 以 2 开头的数据：

songhao=# select * from company where age::text like '2%';

# 将找出 AGE 以 2 开头的数据：

PostgreSQL LIMIT 子句

语法

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

SELECT column1, column2, columnN

FROM table_name

LIMIT [no of rows]

实例

select * from company limit 2;
# 获取限定数量的数据

select * from company limit 2 offset 3;
# 但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。
# 下面是一个实例，从第三位开始提取 3 个记录

select * from company limit 2;

# 获取限定数量的数据

select * from company limit 2 offset 3;

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

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

PostgreSQL ORDER BY 语句

语法

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

SELECT column-list

FROM table_name

[WHERE condition]

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

实例

select * from company order by age asc;

1 2	select * from company order by age asc;

select * from company order by name,salary asc;
# 多字段排序
# 等同于
select * from company order by name,salary asc;
# 先按照 name 排序 然后按照 salary 排序

select * from company order by name,salary asc;

# 多字段排序

# 等同于

select * from company order by name,salary asc;

# 先按照 name 排序然后按照 salary 排序

PostgreSQL GROUP BY 语句

语法

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

SELECT column-list

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2....columnN

ORDER BY column1, column2....columnN

实例

表数据:

 id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

id | name | age | address | salary

----+-------+-----+--------------+--------

1 | Paul | 32 | California | 20000

2 | Allen | 25 | Texas | 15000

3 | Teddy | 23 | Norway | 20000

4 | Mark | 25 | Rich-Mond | 65000

5 | David | 27 | Texas | 85000

6 | Kim | 22 | South-Hall | 45000

7 | James | 24 | Houston | 10000

8 | Paul | 24 | Houston | 20000

9 | James | 44 | Norway | 5000

10 | James | 45 | Texas | 5000

(10 rows)

# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
name  |  sum
-------+-------
 Allen | 15000
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 20000
(7 rows)

# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

name | sum

-------+-------

Allen | 15000

David | 85000

James | 20000

Kim | 45000

Mark | 65000

Paul | 40000

Teddy | 20000

(7 rows)

PostgreSQL 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]

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 语句，允许您在同一个查询中执行多个不同的操作。

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

原来数据

songhao # select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

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


WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT ID,NAME,AGE,ADDRESS,SALARY FROM moved_rows);


songhao=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


songhao=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)

原来数据

songhao # select * from COMPANY;

id | name | age | address | salary

----+-------+-----+-----------+--------

1 | Paul | 32 | California| 20000

2 | Allen | 25 | Texas | 15000

3 | Teddy | 23 | Norway | 20000

4 | Mark | 25 | Rich-Mond | 65000

5 | David | 27 | Texas | 85000

6 | Kim | 22 | South-Hall| 45000

7 | James | 24 | Houston | 10000

(7 rows)

CREATE TABLE COMPANY1(

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL

);

WITH moved_rows AS (

DELETE FROM COMPANY

WHERE

SALARY >= 30000

RETURNING *

)

INSERT INTO COMPANY1 (SELECT ID,NAME,AGE,ADDRESS,SALARY FROM moved_rows);

songhao=# SELECT * FROM COMPANY;

id | name | age | address | salary

----+-------+-----+------------+--------

1 | Paul | 32 | California | 20000

2 | Allen | 25 | Texas | 15000

3 | Teddy | 23 | Norway | 20000

7 | James | 24 | Houston | 10000

(4 rows)

songhao=# SELECT * FROM COMPANY1;

id | name | age | address | salary

----+-------+-----+-------------+--------

4 | Mark | 25 | Rich-Mond | 65000

5 | David | 27 | Texas | 85000

6 | Kim | 22 | South-Hall | 45000

(3 rows)

PostgreSQL HAVING 子句

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

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

语法

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

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

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

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

COMPANY 表的记录如下：

 id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

id | name | age | address | salary

----+-------+-----+--------------+--------

1 | Paul | 32 | California | 20000

2 | Allen | 25 | Texas | 15000

3 | Teddy | 23 | Norway | 20000

4 | Mark | 25 | Rich-Mond | 65000

5 | David | 27 | Texas | 85000

6 | Kim | 22 | South-Hall | 45000

7 | James | 24 | Houston | 10000

8 | Paul | 24 | Houston | 20000

9 | James | 44 | Norway | 5000

10 | James | 45 | Texas | 5000

(10 rows)

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

songhao-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
筛选出名字出现两次的

songhao-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

筛选出名字出现两次的

得到结果如下：

 name
-------
 Paul
 James
(2 rows)

name

-------

Paul

James

(2 rows)

PostgreSQL DISTINCT 关键字

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

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

语法

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

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

SELECT DISTINCT column1, column2,.....columnN

FROM table_name

WHERE [condition]

现在数据如下：

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

id | name | age | address | salary

----+-------+-----+------------+--------

1 | Paul | 32 | California | 20000

2 | Allen | 25 | Texas | 15000

3 | Teddy | 23 | Norway | 20000

4 | Mark | 25 | Rich-Mond | 65000

5 | David | 27 | Texas | 85000

6 | Kim | 22 | South-Hall | 45000

7 | James | 24 | Houston | 10000

8 | Paul | 32 | California | 20000

9 | Allen | 25 | Texas | 15000

(9 rows)

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

runoobdb=# SELECT name FROM COMPANY;

1 2	runoobdb=# SELECT name FROM COMPANY;

得到结果如下：

 name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

name

-------

Paul

Allen

Teddy

Mark

David

Kim

James

Paul

Allen

(9 rows)

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

runoobdb=# SELECT DISTINCT name FROM COMPANY;

1 2	runoobdb=# SELECT DISTINCT name FROM COMPANY;

得到结果如下：

name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

name

-------

Teddy

Paul

Mark

David

Allen

Kim

James

(7 rows)

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

HTTPX 基础教程-新乡seo|网站优化,网站建设_微信公众号:zeropython—昊天博客

PostgreSQL 基础教程

在 Windows 上安装 PostGreSql

下载安装包

登录到 Sql 控制台

常用的SQL

数据库

创建数据库

列出数据库

选择数据库

删除数据库

表操作

创建表

查看当前表是否成功

查看表信息

删除表格

创建 schema

删除 schema

插入数据

插入多行数据

查询表数据

运算符

算数运算符

比较运算符

逻辑运算符

PostgreSQL 表达式

语法

PostgreSQL WHERE 语句

语法

PostgreSQL UPDATE 语句

语法

举个例子

PostgreSQL DELETE 语句

语法

PostgreSQL LIKE 子句

语法

实例

PostgreSQL LIMIT 子句

语法

实例

PostgreSQL ORDER BY 语句

语法

实例

PostgreSQL GROUP BY 语句

语法

实例

PostgreSQL WITH 子句

语法

PostgreSQL HAVING 子句

语法

PostgreSQL DISTINCT 关键字

语法

给 Spring Boot 增加一个 拦截器

PostgreSQL Jsonb between 查询

猜你喜欢

Spring Boot Tk Mybatis PostgreSQL 整合 实现 json 增删改查

centos 安装 PostgreSQL 详细教程

发表评论 点击这里取消回复。

关注我们的公众号

给 Spring Boot 增加一个拦截器

PostgreSQL　Jsonb　between　查询

Spring Boot Tk Mybatis PostgreSQL 整合实现 json 增删改查

发表评论点击这里取消回复。