Frase WHERE PostgreSQL

In　PostgreSQL,　when　we　need　to　query　data　from　a　single　table　or　multiple　tables　based　on　specific　conditions,　we　can　add　the　WHERE　clause　to　the　SELECT　statement　to　filter　out　the　data　we　do　not　need.

The　WHERE　clause　can　be　used　not　only　in　SELECT　statements,　but　also　in　UPDATE,　DELETE,　and　other　statements.

Syntax

The　following　is　the　common　syntax　used　in　SELECT　statements　to　read　data　from　a　database　using　the　WHERE　clause:

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

We　can　use　comparison　operators　or　logical　operators　in　the　WHERE　clause,　such　as　>,　<,　=,　LIKE,　NOT,　etc.

COMPANY.SQL　file　content　is　as　follows：

--　This　is　the　file　to　create　COMPANY　table　and　to　populate　it　with　7　records.
--　Just　copy　and　paste　them　on　psql　prompt.
DROP　TABLE　COMPANY;
CREATE　TABLE　COMPANY(
　　　ID　INT　PRIMARY　KEY　　　　　NOT　NULL,
　　　NAME　　　　　　　　　　　TEXT　　　　NOT　NULL,
　　　AGE　　　　　　　　　　　　INT　　　　　NOT　NULL,
　　　ADDRESS　　　　　　　　CHAR(50),
　　　SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00);
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00);

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

w3codeboxdb# 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 righe)

以下是一些示例，我们使用逻辑运算符来读取表中的数据。

AND

找出 AGE(eta) 字段大于等于 25，并且 SALARY(retribuzione) 字段大于等于 65000 的数据：

w3codeboxdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
　id | name | age | address | salary
----+-------+-----+------------+--------
　　4 | Mark | 25 | Rich-Mond | 65000
　　5 | David | 27 | Texas | 85000
(2 rows)

OR

找出 AGE(eta) 字段大于等于 25，或者 SALARY(retribuzione) 字段大于等于 65000 的数据：

w3codeboxdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
id | name | age | address | salary
----+-------+-----+-------------+--------
　　1 | Paul | 32 | California | 20000
　　2 | Allen | 25 | Texas | 15000
　　4 | Mark | 25 | Rich-Mond | 65000
　　5 | David | 27 | Texas | 85000
(4 righe)

NOT NULL

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

w3codeboxdb=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
　　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 righe)

LIKE

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

w3codeboxdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
id | name | age | address | salary
----+------+-----+-----------+--------
　　1 | Paul | 32 | California | 20000

IN

La seguente query SELECT elenca AGE(eta) 字段为 25 或 27 的数据：

w3codeboxdb=# SELECT * FROM COMPANY WHERE AGE IN (25, 27);
　id | name | age | address | salary
----+-------+-----+------------+--------
　　2 | Allen | 25 | Texas | 15000
　　4 | Mark | 25 | Rich-Mond | 65000
　　5 | David | 27 | Texas | 85000
(3 righe)

NOT IN

La seguente query SELECT elenca AGE(eta) 字段不为 25 或 27 的数据：

w3codeboxdb=# SELECT * FROM COMPANY WHERE AGE NOT IN (25, 27);
　id | name | age | address | salary
----+-------+-----+------------+--------
　　1 | Paul | 32 | California | 20000
　　3 | Teddy | 23 | Norway | 20000
　　6 | Kim | 22 | South-Hall | 45000
　　7 | James | 24 | Houston | 10000
(4 righe)

TRA

La seguente query SELECT elenca AGE(eta) Dati dei campi tra 25 e 27:

w3codeboxdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
　id | name | age | address | salary
----+-------+-----+------------+--------
　　2 | Allen | 25 | Texas | 15000
　　4 | Mark | 25 | Rich-Mond | 65000
　　5 | David | 27 | Texas | 85000
(3 righe)

sottoquery

La seguente query SELECT ha utilizzato anche un sottoquery SQL, la quale legge SALARY(retribuzione) campi maggiori di 65000, poi attraverso EXISTS L'operatore di confronto determina se restituisce righe, se ci sono righe restituite allora legge tutti AGE(eta) campi.

w3codeboxdb=# SELECT AGE FROM COMPANY;
　　　　　　　　WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
　age
-----
　　32
　　25
　　23
　　25
　　27
　　22
　　24
(7 righe)

La seguente query SELECT ha utilizzato anche un sottoquery SQL, la quale legge SALARY(retribuzione) campi maggiori di 65000 AGE(eta) Dati dei campi, poi con > Query operatore maggiore di AGE(eta) Dati dei campi:

w3codeboxdb=# SELECT * FROM COMPANY;
　　　　　　　　WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
　id | name | age | address | salary
----+------+-----+------------+--------
　　1 | Paul | 32 | California | 20000