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降序排列