单表查询 | OceanBase 文档中心

单表查询

更新时间：2023-08-09 14:11:21

单表查询是指仅涉及一个表的查询。

单表查询的语法

单表查询语法如下：

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 这些关键字一起使用时，先后顺序有明确的限制。关键字执行顺序如下：

执行 FROM 找到表。
执行 WHERE 指定约束条件。
执行 GROUP BY 将取出的每条记录进行分组。如果没有 GROUP BY，则整体作为一组。
执行 HAVING 将分组的结果进行筛选。
执行 SELECT。
执行 DISTINCT 去重。
执行 ORDER BY 将结果按条件升序或降序排序。
执行 ROWNUM 限制结果的条数。

WHERE 和 HAVING 的区别是，WHERE 是在分组前对数据进行筛选，而 HAVING 是在分组后的结果里进行筛选，最后返回整个 SQL 的查询结果。

SELECT 查询基础

创建示例表 emp 和 dept，并插入数据。

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');

查询全部列

* 表示返回表中所有字段。示例如下：

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

等价于：

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

查询指定列

obclient> SELECT empname, deptno FROM emp;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
6 rows in set

在查询时可以为列设置别名。示例如下：

obclient> SELECT empname AS 员工姓名, deptno AS 部门编号 FROM emp;
+--------------+--------------+
| 员工姓名     | 部门编号     |
+--------------+--------------+
| SMITH        |           20 |
| ALLEN        |           35 |
| JONES        |           40 |
| BLAKE        |           30 |
| SCOTT        |           25 |
| FORD         |           45 |
+--------------+--------------+
6 rows in set

在查询时可以对结果进行去重。示例如下：

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 用来限制返回的行数，常用于分页等操作。示例如下：

obclient> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     30 |
| JONES   |     40 |
+---------+--------+
3 rows in set

查询经过计算的值

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 结果列展示为小写字母。

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 条件查询，请参见条件查询。

更多用法请参见在查询中使用操作符和函数。