单表查询是指从 TDSQL PG 数据库的一个表或视图中检索一列或多列数据。TDSQL PG 支持对单表查询的结果数据进行过滤、去重、排序、分组等操作。
语法
SELECT 的单表查询语法如下：
SELECT [ ALL | DISTINCT ] column[, column]
[ FROM table_clause ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
参数说明：
DISTINCT： 从结果中消除重复的行ALL： 默认，返回所有行table_clause：可以是TDSQL PG中的表、视图和物化视图WHERE：由一个或多个表达式组成的条件，如果是多个条件，根据需要可以用 AND/OR 连接GROUP BY：合成由在一个或者多个值上匹配的行构成的分组，并且在其上计算聚集函数的结果HAVING：消除不满足给定条件的分组LIMIT和OFFSET：限制查询返回的行，实现分页查询功能。可以指定偏移量，以及要返回的行数或行百分比
示例
现有一个员工信息表 EMPLOYEES，包括员工ID（EMPLOYEE_ID）、员工姓名（NAME）、性别（GENDER）、出生日期（BIRTHDAY）、员工邮箱（EMAIL）、薪水（SALARY）、主管ID（MANAGER_ID）、部门ID（DEPARTMENT_ID）。
数据如下：
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0001 | john | male | 1983-03-04 | john@gmail.com | 6500 | 0081 | 003
0002 | andy | male | 1987-08-09 | andy@gmail.com | 8000 | 0081 | 003
0003 | bob | male | 1985-01-04 | bob@gmail.com | 12000 | 0082 | 005
0004 | eric | male | 1997-02-18 | eric@gmail.com | 7000 | 0081 | 003
0005 | david | male | 1990-06-12 | david@gmail.com | 9000 | 0082 | 005
0006 | frank | male | 1982-12-19 | frank@gmail.com | 8800 | 0081 | 003
0007 | henry | male | 1993-03-04 | henry@gmail.com | 11000 | 0082 | 005
0008 | emily | female | 1989-05-04 | emily@gmail.com | 8500 | 0081 | 003
0009 | lucy | female | 1992-01-03 | lucy@gmail.com | 6500 | 0081 | 003
0010 | julia | female | 1991-12-30 | julia@gmail.com | 7000 | 0082 | 005
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
建表和插入数据语句如下：
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID VARCHAR(100),
NAME VARCHAR(100),
GENDER VARCHAR(6),
BIRTHDAY DATE,
EMAIL VARCHAR(100),
SALARY NUMERIC,
MANAGER_ID VARCHAR(100),
DEPARTMENT_ID VARCHAR(100));
INSERT INTO EMPLOYEES VALUES('0001', 'john', 'male', '1983-3-4', 'john@gmail.com', 6500, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0002', 'andy', 'male', '1987-8-9', 'andy@gmail.com', 8000, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0003', 'bob', 'male', '1985-01-04', 'bob@gmail.com', 12000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0004', 'eric', 'male', '1997-02-18', 'eric@gmail.com', 7000, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0005', 'david', 'male', '1990-6-12', 'david@gmail.com', 9000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0006', 'frank', 'male', '1982-12-19', 'frank@gmail.com', 8800, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0007', 'henry', 'male', '1993-03-04', 'henry@gmail.com', 11000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0008', 'emily', 'female', '1989-5-4', 'emily@gmail.com', 8500, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0009', 'lucy', 'female', '1992-1-3', 'lucy@gmail.com', 6500, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0010', 'julia', 'female', '1991-12-30', 'julia@gmail.com', 7000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0011', 'leo', 'male', '1983-12-20', 'leo@gmail.com', 14000, '0081', '003');
示例1
查询所有员工信息
SELECT * FROM (employees);
返回结果如上。
示例2
从员工表中查询所有1990年之前出生的员工
set nls_date_format to 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM employees WHERE birthday < to_date('1990-1-1');
结果如下：
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+---------------------+--------------+--------+------------+---------------
0001 | john | male | 1983-03-04 00:00:00 | john@gmail.com | 6500 | 0081 | 003
0002 | andy | male | 1987-08-09 00:00:00 | andy@gmail.com | 8000 | 0081 | 003
0003 | bob | male | 1985-01-04 00:00:00 | bob@gmail.com | 12000 | 0082 | 005
0006 | frank | male | 1982-12-19 00:00:00 | frank@gmail.com | 8800 | 0081 | 003
0008 | emily | female | 1989-05-04 00:00:00 | emily@gmail.com | 8500 | 0081 | 003
0011 | leo | male | 1983-12-20 00:00:00 | leo@gmail.com | 14000 | 0081 | 003
示例3
从员工表中查询所有员工信息，按薪水从高到低进行排序
SELECT * FROM employees ORDER BY salary DESC;
结果如下：
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
0003 | bob | male | 1985-01-04 | bob@gmail.com | 12000 | 0082 | 005
0007 | henry | male | 1993-03-04 | henry@gmail.com | 11000 | 0082 | 005
0005 | david | male | 1990-06-12 | david@gmail.com | 9000 | 0082 | 005
0006 | frank | male | 1982-12-19 | frank@gmail.com | 8800 | 0081 | 003
0008 | emily | female | 1989-05-04 | emily@gmail.com | 8500 | 0081 | 003
0002 | andy | male | 1987-08-09 | andy@gmail.com | 8000 | 0081 | 003
0004 | eric | male | 1997-02-18 | eric@gmail.com | 7000 | 0081 | 003
0010 | julia | female | 1991-12-30 | julia@gmail.com | 7000 | 0082 | 005
0001 | john | male | 1983-03-04 | john@gmail.com | 6500 | 0081 | 003
0009 | lucy | female | 1992-01-03 | lucy@gmail.com | 6500 | 0081 | 003
示例4
从员工表中查询各个部门的员工人数
SELECT department_id, count(*) FROM employees GROUP BY department_id;
结果如下：
DEPARTMENT_ID | COUNT
---------------+-------
003 | 7
005 | 4
示例5
在示例 4 的基础上，只查询部门人数大于 5 人的部门ID
SELECT department_id, count(*) FROM employees GROUP BY department_id HAVING count(*) > 5;
结果如下：
DEPARTMENT_ID | COUNT
---------------+-------
003 | 7
示例6
查询工资最高的前3个员工信息
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
结果如下：
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
0003 | bob | male | 1985-01-04 | bob@gmail.com | 12000 | 0082 | 005
0007 | henry | male | 1993-03-04 | henry@gmail.com | 11000 | 0082 | 005