PostgreSQL修炼之道：从小工到专家（第2版）最新章节_唐成著

3.4　查询语句

本节介绍常用的查询语句的使用方法。

3.4.1　单表查询语句

查询student表中所有数据的语句如下：

select no, student_name, age from student;

由此可见，“SELECT”是关键字，表示查询，后面跟多个列名，各列之间使用逗号分隔；其后的“FROM”是关键字，后面跟表名。列可以是表的列名，也可以是一个表达式：

select age+5 from student;

表达式中可以包括表的列，也可以与表列无关：

select no, 3+5 from student;

当表达式与表列无关时，在PostgreSQL和MySQL中可以不使用“FROM表名”，这样一来就可以作为计算器使用了：

osdba=# select 55+88;
  ?column? 
----------
    143
(1 row)

osdba=# select 10*2,3*5+2;
  ?column? | ?column? 
 ----------+----------
    20 |       17
(1 row)

如果想查询表中所有列的数据，则可以使用“*”代表所有列，如下所示：

select * from student;

3.4.2　过滤条件的查询

SELECT语句后面可以指定WHERE子句，用于指定要查询哪条或哪些记录，比如，要查询学号为3的学生记录，其SQL语句如下：

osdba=# SELECT * FROM student where no=3;
 no | student_name | age 
----+--------------+-----
  3 | 王明充        |  13
(1 row)

WHERE子句中也可以使用大于、小于表达式，比如，想查询年龄大于或等于15岁的学生记录，查询语句如下：

osdba=# SELECT * FROM student where age >= 15;
 no | student_name | age 
----+--------------+-----
  1 | 张三          |  15
  2 | 李四          |  15
(2 rows)

3.4.3　排序

使用排序子句可以对查询结果进行排序，排序子句是在SELECT语句后面加上“ORDER BY”子句，比如，想将查询结果按年龄排序，则查询语句如下：

osdba=# SELECT * FROM student ORDER BY age;
 no | student_name | age 
----+--------------+-----
  3 | 王明充        |  13
  1 | 张三          |  15
  2 | 李四          |  15
(3 rows)

排序子句“ORDER BY”应该在WHERE子句之后，如果顺序错了，执行时会报错：

osdba=# SELECT * FROM student ORDER BY age WHERE age >= 15;
ERROR:  syntax error at or near "WHERE"
LINE 1: SELECT * FROM student ORDER BY age WHERE age >= 15;

把“ORDER BY”子句放到“WHERE”子句后面就不会报错了：

osdba=# SELECT * FROM student WHERE age >= 15 ORDER BY age;
 no | student_name | age 
----+--------------+-----
  1 | 张三          |  15
  2 | 李四          |  15
(2 rows)

还可以按多个列对查询结果进行排序，比如，根据“age”和“student_name”两个列来排序：

osdba=# SELECT * FROM student ORDER BY age,student_name;
 no | student_name | age 
----+--------------+-----
  3 | 王明充        |  13
  2 | 李四          |  15
  1 | 张三          |  15
(3 rows)

也可以在排序子句的列名后加“DESC”进行倒序排序：

osdba=# SELECT * FROM student ORDER BY age DESC;
 no | student_name | age 
----+--------------+-----
  1 | 张三          |  15
  2 | 李四          |  15
  3 | 王明充        |  13
(3 rows)

osdba=# SELECT * FROM student ORDER BY age DESC,student_name;
 no | student_name | age 
----+--------------+-----
  2 | 李四         |  15
  1 | 张三         |  15
  3 | 王明充       |  13
(3 rows)

3.4.4　分组查询

如果需要统计不同年龄的学生人数，可以使用分组查询，分组查询子句的关键字为“GROUP BY”，用法如下：

osdba=# SELECT age, count(*) FROM student GROUP BY age;
 age | count 
-----+-------
  15 |     2
  13 |     1
(2 rows)

从上面的查询语句中可以看出，使用“GROUP BY”语句时需要使用聚合函数，常用的聚合函数有“count”“sum”等。

3.4.5　多表关联查询

多表关联查询也称表join。假设有一张班级表“class”，建表语句如下：

CREATE TABLE class(no int primary key, class_name varchar(40));

表中的“no”表示班级编号，“class_name”表示班级名称。

现插入一些测试数据：

osdba=# INSERT INTO class VALUES(1,'初二(1)班');
INSERT 0 1
osdba=# INSERT INTO class VALUES(2,'初二(2)班');
INSERT 0 1
osdba=# INSERT INTO class VALUES(3,'初二(3)班');
INSERT 0 1
osdba=# INSERT INTO class VALUES(4,'初二(4)班');
INSERT 0 1
osdba=# SELECT * FROM class;
 no | class_name 
----+------------
  1 | 初二(1)班
  2 | 初二(2)班
  3 | 初二(3)班
  4 | 初二(4)班
(4 rows)

还有一张学生表“student”，建表语句如下：

CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int);

同样插入一些测试数据：

osdba=# INSERT INTO student VALUES(1, '张三', 14, 1);
INSERT 0 1
osdba=# INSERT INTO student VALUES(2, '吴二', 15, 1);
INSERT 0 1
osdba=# INSERT INTO student VALUES(3, '李四', 13, 2);
INSERT 0 1
osdba=# INSERT INTO student VALUES(4, '吴三', 15, 2);
INSERT 0 1
osdba=# INSERT INTO student VALUES(5, '王二', 15, 3);
INSERT 0 1
osdba=# INSERT INTO student VALUES(6, '李三', 14, 3);
INSERT 0 1
osdba=# INSERT INTO student VALUES(7, '吴二', 15, 4);
INSERT 0 1
osdba=# INSERT INTO student VALUES(8, '张四', 14, 4);
INSERT 0 1
osdba=# SELECT * FROM student;
 no | student_name | age | class_no 
----+--------------+-----+----------
  1 | 张三         |  14 |        1
  2 | 吴二         |  15 |        1
  3 | 李四         |  13 |        2
  4 | 吴三         |  15 |        2
  5 | 王二         |  15 |        3
  6 | 李三         |  14 |        3
  7 | 吴二         |  15 |        4
  8 | 张四         |  14 |        4

假设想查询每个学生的名字与班级名称的关系，那么就需要关联查询两张表：

SELECT student_name, class_name FROM student, class 
  WHERE student.class_no = class.no;

查询结果如下：

osdba=# SELECT student_name, class_name FROM student, class 
 WHERE student.class_no = class.no;
 student_name | class_name 
--------------+------------
 张三         | 初二(1)班
 吴二         | 初二(1)班
 李四         | 初二(2)班
 吴三         | 初二(2)班
 王二         | 初二(3)班
 李三         | 初二(3)班
 吴二         | 初二(4)班
 张四         | 初二(4)班
(8 rows)

表关联查询就是在WHERE子句中加上需要关联的条件（两张表关联）：

WHERE student.class_no = class.no;

由于两张表中有些列的名称相同，如在表“student”中“no”表示学生号，而在表“class”中表示班级号，所以在关键条件中要明确使用“表名”加“列名”来唯一定位某一列。如果输入的表名比较长，可以给表起个别名，SQL语句如下：

SELECT student_name, class_name FROM student a, class b
  WHERE a.class_no = b.no;

在上面的语句中，表“student”的别名为“a”，表“class”的别名为“b”，此时条件表达式中“b.no”就代表表“class”中的“no”列。

还可以在关联查询的WHERE子句中加上其他过滤条件，如下所示：

osdba=# SELECT student_name, class_name FROM student a, class b
  WHERE a.class_no = b.no AND a.age > 14;
  student_name | class_name 
 --------------+------------
  吴二         | 初二(1)班
  吴三         | 初二(2)班
  王二         | 初二(3)班
  吴二         | 初二(4)班
(4 rows)

3.4.6　子查询

当一个查询是另一个查询的条件时，称之为子查询。主要有4种语法的子查询：

·带有谓词IN的子查询：expression [NOT] IN (sqlstatement)。

·带有EXISTS谓词的子查询：[NOT] EXISTS (sqlstatement)。

·带有比较运算符的子查询：comparison(>,<,=,!=)(sqlstatement)。

·带有ANY（SOME）或ALL谓词的子查询：comparison [ANY|ALL|SOME] (sqlstatement)。

我们还用前面例子中班级表“class”和学生表“studtent”中的数据。

下面用带有谓词IN的子查询来查询“初二（1）班”的学生记录：

osdba=# SELECT * FROM student WHERE class_no in (select no FROM class where class_name = '初二(1)班');
 no | student_name | age | class_no
----+--------------+-----+----------
  1 | 张三         |  14 |        1
  2 | 吴二         |  15 |        1
(2 rows)

上面的查询也可以用带EXISTS谓词的子查询来实现：

osdba=# SELECT * FROM student s WHERE EXISTS (SELECT 1 FROM class c WHERE s.class_no=c.no AND c.class_name = '初二(1)班');
no | student_name | age | class_no
----+--------------+-----+----------
  1 | 张三         |  14 |        1
  2 | 吴二         |  15 |        1
(2 rows)

此查询还可以用带有比较符（这里用的是“=”）的子查询来实现：

osdba=# SELECT * FROM student WHERE class_no = (SELECT no FROM class c WHERE class_name = '初二(1)班');
no | student_name | age | class_no
----+--------------+-----+----------
  1 | 张三         |  14 |        1
  2 | 吴二         |  15 |        1
(2 rows)

此查询还可以用带有ANY（SOME）或ALL谓词的子查询来实现：

osdba=# SELECT * FROM student WHERE class_no = any(SELECT no FROM class c WHERE class_name = '初二(1)班');
no | student_name | age | class_no
----+--------------+-----+----------
  1 | 张三         |  14 |        1
  2 | 吴二         |  15 |        1
(2 rows)

但如果我们要查询两个班级的学生记录，不能使用带有等于“=”比较符的子查询：

osdba=# SELECT * FROM student WHERE no = (SELECT no FROM class c WHERE class_name in ('初二(1)班', '初二(2)班'));
ERROR:  more than one row returned by a subquery used as an expression

上面的查询报错说子查询不能返回多行。这种不能返回多行的子查询也称标量子查询，标量子查询不仅能嵌套在WHERE子句中，也可以嵌套在SELECT的列表中，如我们要查询每个班级学生的最大年龄，则可以用如下SQL语句：

osdba=# SELECT no, class_name, (SELECT max(age) as max_age FROM student s WHERE s.no= c.no) as max_age FROM class c;
 no | class_name | max_age
----+------------+---------
  1 | 初二(1)班  |      14
  2 | 初二(2)班  |      15
  3 | 初二(3)班  |      13
  4 | 初二(4)班  |      15
(4 rows)

查询两个班级的学生记录时用带有ANY（SOME）谓词的子查询就没有问题了，示例如下：

osdba=# SELECT * FROM student WHERE class_no = any(SELECT no FROM class c WHERE class_name in ('初二(1)班', '初二(2)班'));
no | student_name | age | class_no
----+--------------+-----+----------
  1 | 张三         |  14 |        1
  2 | 吴二         |  15 |        1
  3 | 李四         |  13 |        2
  4 | 吴三         |  15 |        2
(4 rows)

3.4 查询语句

3.4.1 单表查询语句

3.4.2 过滤条件的查询

3.4.3 排序

3.4.4 分组查询

3.4.5 多表关联查询