oracle常用命令-常用SQL语句

oracle常用命令记录
1、desc emp
desc dept
desc salgrade
select * from emp;

select ename ,sal*12 from emp;

select 2*3 from dual;
select sysdata from dual;

select ename,sal*12 annual_sal from emp;
select ename,sal*12 "annual sal" from emp;

任何包含null值的表达式结果都是null

2、字符串拼接
select ename||'assss' from emp;
select ename||'dddsss''aaa' from emp; --->如果字符串中有单引号，则用两个单引号替代一个单引号

3、distinct
select distinct deptno from emp;
select distinct deptno , job from emp;

4、where
select * from emp where deptno=10;
select * from emp where ename='CLARK';
select * from emp where sal >= 1500;
select * from emp where sal > 1500;
select ename,deptno,sal from emp where deptno <> 10;
select ename,sal from emp where ename > 'CBA';
select ename, sal from emp where sal between 800 and 1500;

5、null
select ename,sal,comm from emp where comm is null;
select ename,sal,comm from emp where comm is not null;

6、in
select ename , sal , comm from emp where sal in (800,1000,1500);
select ename , sal , comm from emp where ename in ('SMITH','KING','ABC');

7、date
select ename , sal , hiredate from emp where hiredate > '20-2月-81';
select ename , sal , hiredate from emp where hiredate > '20-2月-1981';

8、and or not
9、通配符
select ename ,sal from emp where ename like '_A%'; ---第二个字母为A的
select ename ,sal from emp where ename like '%ALL%'; ---包含ALL的

10、转义字符
select ename ,sal from emp where ename like '%\%%'; 名称中包含%的 \为转义字符
select ename ,sal from emp where ename like '%$%%' escape '$'; 名称中包含%的 $为自定义转义字符