--- title: "单表查询 | OceanBase 文档中心" description: "单表查询 单表查询是指仅涉及一个表的查询。 单表查询的语法 单表查询语法如下： SELECT [ALL | DISTINCT] select_list FROM table_name [ WHERE query_condition ] [ GROUP BY group_by_expression ] [ HAVING…" image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS # 单表查询 更新时间：2023-08-09 14:11:21 单表查询是指仅涉及一个表的查询。 ## 单表查询的语法 单表查询语法如下： ```sql SELECT [ALL | DISTINCT] select_list FROM table_name [ WHERE query_condition ] [ GROUP BY group_by_expression ] [ HAVING group_condition ] [ ORDER BY column_list ][ASC | DESC] [ row_limiting_clause ] column_list： column_name[,column_name...] ``` 当 `WHERE`、`GROUP BY`、`HAVING`、`ORDER BY` 这些关键字一起使用时，先后顺序有明确的限制。关键字执行顺序如下： 1. 执行 `FROM` 找到表。 2. 执行 `WHERE` 指定约束条件。 3. 执行 `GROUP BY` 将取出的每条记录进行分组。如果没有 `GROUP BY`，则整体作为一组。 4. 执行 `HAVING` 将分组的结果进行筛选。 5. 执行 `SELECT`。 6. 执行 `DISTINCT` 去重。 7. 执行 `ORDER BY` 将结果按条件升序或降序排序。 8. 执行 `ROWNUM` 限制结果的条数。 `WHERE` 和 `HAVING` 的区别是，`WHERE` 是在分组前对数据进行筛选，而 `HAVING` 是在分组后的结果里进行筛选，最后返回整个 SQL 的查询结果。 ## SELECT 查询基础 创建示例表 `emp` 和 `dept`，并插入数据。 ```sql CREATE TABLE emp( empno NUMBER(4,0), empname VARCHAR(10), job VARCHAR(9), mgr NUMBER(4,0), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2,0), age NUMBER(2,0), CONSTRAINT PK_emp PRIMARY KEY (empno), CONSTRAINT FK_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno) ); CREATE TABLE dept( deptno NUMBER(2,0), dname VARCHAR(14), location VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno) ); insert into emp values (1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22), (1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22), (1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22), (1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33), (1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33), (1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22); insert into dept values (20,'Finance','beijing'), (35,'Administration','hangzhou'), (40,'Development','xian'), (30,'Workshop','guangzhou'), (25,'Legal affairs','shanghai'), (45,'Office','suzhou'); ``` ### 查询全部列 `*` 表示返回表中所有字段。示例如下： ```sql obclient> select * from emp; +-------+---------+----------+------+-----------+------+------+--------+------+ | EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE | +-------+---------+----------+------+-----------+------+------+--------+------+ | 1369 | SMITH | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 | 22 | | 1499 | ALLEN | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 | 22 | | 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 | | 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 | | 1788 | SCOTT | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 | 33 | | 1902 | FORD | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 | 22 | +-------+---------+----------+------+-----------+------+------+--------+------+ 6 rows in set ``` 等价于： ```sql obclient> SELECT empname, empno, job, mgr, hiredate, sal, comm, deptno FROM emp; +---------+-------+----------+------+-----------+------+------+--------+ | EMPNAME | EMPNO | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +---------+-------+----------+------+-----------+------+------+--------+ | SMITH | 1369 | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 | | ALLEN | 1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 | | JONES | 1566 | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | | BLAKE | 1698 | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | | SCOTT | 1788 | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 | | FORD | 1902 | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 | +---------+-------+----------+------+-----------+------+------+--------+ 6 rows in set ``` ### 查询指定列 ```sql obclient> SELECT empname, deptno FROM emp; +---------+--------+ | EMPNAME | DEPTNO | +---------+--------+ | SMITH | 20 | | ALLEN | 35 | | JONES | 40 | | BLAKE | 30 | | SCOTT | 25 | | FORD | 45 | +---------+--------+ 6 rows in set ``` - 在查询时可以为列设置别名。示例如下： ```sql obclient> SELECT empname AS 员工姓名, deptno AS 部门编号 FROM emp; +--------------+--------------+ | 员工姓名 | 部门编号 | +--------------+--------------+ | SMITH | 20 | | ALLEN | 35 | | JONES | 40 | | BLAKE | 30 | | SCOTT | 25 | | FORD | 45 | +--------------+--------------+ 6 rows in set ``` - 在查询时可以对结果进行去重。示例如下： ```sql obclient> SELECT age FROM emp; +------+ | AGE | +------+ | 22 | | 22 | | 22 | | 33 | | 33 | | 22 | +------+ 6 rows in set obclient> SELECT DISTINCT age FROM emp; +------+ | AGE | +------+ | 22 | | 33 | +------+ 2 rows in set ``` - 在查询时可以使用 `ROWNUM` 用来限制返回的行数，常用于分页等操作。示例如下： ```sql obclient> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3; +---------+--------+ | empname | deptno | +---------+--------+ | SMITH | 20 | | ALLEN | 30 | | JONES | 40 | +---------+--------+ 3 rows in set ``` ### 查询经过计算的值 ```sql obclient> SELECT empname, sal-100, deptno FROM emp; +---------+---------+--------+ | empname | sal-100 | deptno | +---------+---------+--------+ | SMITH | 700.00 | 20 | | ALLEN | 1500.00 | 30 | | JONES | 2875.00 | 40 | | BLAKE | 2750.00 | 30 | | SCOTT | 2900.00 | 20 | | FORD | 2900.00 | 40 | +---------+---------+--------+ 6 rows in set ``` 在查询中可以对指定列进行函数处理，如下示例将 `empname` 结果列展示为小写字母。 ```sql obclient> SELECT LOWER(empname), sal, deptno FROM emp; +---------+---------+---------------+ | LOWER(empname) | sal | deptno | +----------------+---------+--------+ | smith | 800.00 | 20 | | allen | 1600.00 | 30 | | jones | 2975.00 | 40 | | blake | 2850.00 | 30 | | scott | 3000.00 | 20 | | ford | 3000.00 | 40 | +----------------+---------+--------+ 6 rows in set ``` 此外，指定列的查询还支持 `WHERE` 条件查询，请参见 [条件查询](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000355228)。 更多用法请参见 [在查询中使用操作符和函数](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000357594)。 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线