SELECT * | { [DISTINCT] column|expression [alias],...}
FROM table;
select 标识 列
from 标识表
null
包含 null值的 数学表达式 都为 null
为避免, 则使用
NVL(
列名,
替代值)
连接符 ||
连接两列为一列, 终值为字符串
select
'雇员'||ename||'薪水是'||sal
from emp
'雇员'||ENAME||'薪水是'||SAL
------------------------------------------------------------
雇员SMITH薪水是800
雇员ALLEN薪水是1600
雇员WARD薪水是1250
雇员JONES薪水是2975
雇员MARTIN薪水是1250
雇员BLAKE薪水是2850
去重 Distinct
单列时:简单去重, null也算一行
select distinct nvl(comm,0) from emp
多列时, 全列重复 满足行剔除条件
区域 BETWEN
1.包含边界
2.升序填
select * from emp
where sal between 800 and 1500
order by sal
/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
排序 desc
默认升序
desc还可以用来描述表, 多个排序的字段需要跟一个私有 desc关键字, 写在前面的字段排位权值高
select * from emp
where sal between 800 and 1500
order by sal desc, comm desc
/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7369 SMITH CLERK 7902 17-12月-80 800 20