数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句

DQL语句

1、单表查询语句

"SELECT"关键字，表示查询，后面可以跟多个列名，列名间使用逗号分隔，其后接"FROM"，后面跟表名。另外，列名不一定是表的列名，也可以是一个表达式。如果需要查询所有列的数据，可以使用"*"代表所有列，示例：

postgres=# select a from t1;
 a
---
 1
(1 row)

postgres=# select a,1+9 from t1;
 a | ?column?
---+----------
 1 |       10
(1 row)

postgres=# select 10*10;
 ?column?
----------
      100
(1 row)

postgres=# select * from t1;
 a |  b
---+-----
 1 | abc
(1 row)

2、where子句

可以通过‘WHERE’子句指定要查询的记录

postgres=# select * from t1 where a=1;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# select * from t1 where a<2;
 a |  b
---+-----
 1 | abc
(1 row)

postgres=# select * from t1 where a>1;
 a |  b
---+-----
 2 | def
(1 row)

3、查询结果排序

SELECT语句后加‘ORDER BY’子句，可对结果进行排序

postgres=# select * from student;
 id | name | age
----+------+-----
  1 | abc  |  24
  2 | bcd  |  17
  3 | cde  |  18
  4 | def  |  21
(4 rows)

postgres=# select * from student order by age;
 id | name | age
----+------+-----
  2 | bcd  |  17
  3 | cde  |  18
  4 | def  |  21
  1 | abc  |  24
(4 rows)

‘ORDER BY’子句后接多个列名可对多个列进行排序，默认排序方式为升序‘ASC’，排序子句后加‘DESC’可进行降序排序

postgres=# select * from student order by name desc, age;
 id | name | age
----+------+-----
  4 | def  |  21
  3 | cde  |  18
  2 | bcd  |  17
  1 | abc  |  24
(4 rows)

postgres=# select * from student order by age asc;
 id | name | age
----+------+-----
  2 | bcd  |  17
  3 | cde  |  18
  4 | def  |  21
  1 | abc  |  24
(4 rows)

4、分组查询

分组查询即将查询结果按一个或多个进行分组，字段值相同的为一组。如下例按age进行分组

postgres=# select * from student;
 id | name | age
----+------+-----
  1 | abc  |  24
  2 | bcd  |  17
  3 | cde  |  18
  4 | def  |  21
  5 | aaa  |  21
  6 | bbb  |  18
(6 rows)

postgres=# select age,count(*) from student group by age;
 age | count
-----+-------
  21 |     2
  17 |     1
  24 |     1
  18 |     2
(4 rows)

一般‘GROUP BY’同聚集函数一起使用，常用的聚集函数有‘count’、‘sum’等

5、关联查询

简单关联查询，示例：

postgres=# create table t1(a int ,b text);
CREATE TABLE
postgres=# create table t2(a int ,b text);
CREATE TABLE
postgres=# insert into t1 values(1,'周一');
INSERT 0 1
postgres=# insert into t1 values(2,'周二');
INSERT 0 1
postgres=# insert into t1 values(3,'周三');
INSERT 0 1
postgres=# insert into t2 values(1,'Monday');
INSERT 0 1
postgres=# insert into t2 values(2,'Tuesday');
INSERT 0 1
postgres=# insert into t2 values(3,'Wednesday');
INSERT 0 1
postgres=# select t1.b,t2.b from t1,t2 where t1.a=t2.a;
  b   |     b
------+-----------
 周一 | Monday
 周二 | Tuesday
 周三 | Wednesday
(3 rows)

6、子查询

当一个查询是另一个查询的条件时，称为子查询

带有谓词IN的子查询

postgres=# select b from t1 where a in (select a from t2);
  b
------
 周一
 周二
 周三
(3 rows)

postgres=# insert into t1 values(4,'周四');
INSERT 0 1
postgres=# select b from t1 where a not in (select a from t2);
  b
------
 周四
(1 row)

带有EXISTS谓词的子查询，判断某些条件是否满足(跨表),EXISTS是接在where之后,EXISTS返回的结果只有0和1

postgres=# select * from t2 where exists (select * from t1 where a=5);
 a | b
---+---
(0 rows)

postgres=# select * from t2 where exists (select * from t1 where a=4);
 a |     b
---+-----------
 1 | Monday
 2 | Tuesday
 3 | Wednesday
(3 rows)

postgres=# select * from t2 where not exists (select * from t1 where a=5);
 a |     b
---+-----------
 1 | Monday
 2 | Tuesday
 3 | Wednesday
(3 rows)

postgres=# select * from t2 where not exists (select * from t1 where a=4);
 a | b
---+---
(0 rows)

带有比较运算符的子查询（标量子查询，无法返回多行）

postgres=# select * from t1 where a = (select a from t2 where b='Monday');
 a |  b
---+------
 1 | 周一
(1 row)

带有ANY(SOME)或ALL谓词的子查询

postgres=# select * from t1 where a = any(select a from t2);
 a |  b
---+------
 1 | 周一
 2 | 周二
 3 | 周三
(3 rows)

PostgreSQL基础：DQL语句