Oracle复习之一

oracle7 以前用varchar不支持国际化 varchar2支持
查询语句
DML 数据操作语言
DDL 数据定义语言

• 登陆sqlplus
  sqlplus sys/ocrl as sysdba  //登陆
• 解开scott用户
  alter user scott account unlock;//解开scott用户
  clear scr//清屏
• 描述表信息
  desc emp //描述表emp
  desc dept//

*********************select***************************

select * from emp;
select ename,sal*12 from emp;//
select 2*3 from dual;//dual 表就一条记录 一个字段DUMMY
select sysdate from dual;// 查询系统时间
select ename,sal* anuual_sal from emp//别名 系统会变大写ANUUAL SAL
select ename,sal*12 "ANUUAL SAL" from emp;//别名""双引号会保持双引号内的格式

任何含有空值的表达式最终的结果都是空值

select ename||sal from emp; //字符连接符
select ename||'asdf' from emp
select ename||'as''df' from emp//2个单引号来表示一个

select distinct    deptno from emp;//排除相同的deptno
select distinct    deptno,job  from emp;//排除相同的deptno job组合

select * from emp where deptno =10 ;//过滤条件wehere
select * from emp where ename='CLARK';
select ename,sal from emp where sal>1500;
select ename,sal from emp where deptno<>10;不等于10
select ename,sal from emp where ename>'CBA';
select ename,sal from emp where sal between 800 and 1500; //包含800 1500
select ename,sal from emp where sal>=800 and sal <=1500;
select ename,sal,comm from emp where comm is null;//空值
select ename,sal,comm from emp where comm is not null;//非空值
select ename,sal,comm from emp where sal in (800,1500,2000);//in语句
select ename,sal,comm from emp where ename in ('SMITH','KING','ABC');//in语句

日期处理
select ename,sal,hiredate from emp where hiredate > '13-3月-80'//in语句

正则表达式 “.”一个字母 “*”0或多个 “？”0个或一个 “+”一个或多个

模糊查询 "%" 0个或多个,  "_" 一个字母 
select ename from emp where ename like     '%ALL%'
select ename from emp where ename like     '_ALL%'
select ename from emp where ename like     '%\%%' //和含有%的  转译字符默认\
select ename from emp where ename like     '%$%%' ename '$';表示查询含有%号以$为转译字符

排序--默认为插入数据顺序
select * from emp order by deptno desc;//降序 默认升序
select empno，ename from emp order by empno asc;
selec empno，ename from emp where deptno<>10 order by empno asc;//先过滤数据后排序
select empno，ename from emp order by empno asc ,ename desc;//先按empno升序，相同后再按ename降序

select ename,sal*12 annual_sal from emp
where ename not like '_A%' and sal >800
order by sal desc;

//取出ename，sal*12从 emp 条件 ename 第二个字母不为A sal大于800降序排列