Oracle学习笔记2

一． Select

1） select * from tablename;

2） select salary*12 from tablename;  // 对列运算

3） select sysdate from dual;                     // 获取当前时间

4） select salary*12 annual_salary from tablename;        // 为列起别名

// 为列起区分大小写或着带空格的别名

select salary*12 “annual salary” from tablename;    

5） select ename || salary from emp;                 // 连接两个列

6） select distinct deptno from emp;          // 去掉重复的值

          Select distinct ename, job from emp;  // 去掉 ename ， job 都相同的记录

7） select * from emp where ename > ‘cba’;    // 比较字符串条件，按 ascii 码

8） select * from emp where salary between 800 and 1500;  //between and 查询

9） //in 查询

select * from emp where ename in (‘abc’, ‘def’);

select * from emp where salary not in (‘abc’, ‘def’);

10）              // 空值比较

select * from emp where ename is null;

select * from emp where ename is not null;

11）              //% 代表 0 或多个字符， _ 代表一个字符

select * from emp where ename like ‘_a%’;

select * from emp where ename like ‘abc/%’ escape ‘/’;              // 定义转义字符

12）              select * from dept order by deptno desc/asc;                //order by 排序

                     // 先按 deptno 升序排列，相同的 deptno 按 ename 的降序排列

                     Select * from emp order by deptno asc, ename desc;

13）              // 日期比较

select * from emp where indate > ’20-2 月 -09’ ;

14）              // 对 deptno 和 job 都相同的记录进行分组

Select deptno, job from emp group by deptno, job;

15）              // 用 having 过滤 group by

Select avg(sal), deptno from emp group by deptno having avg(sal)>2000

16）              // 一般结构

Select deptno, avg(sal)

From emp

Where sal > 1200

Group by deptno

Having avg(sal) >1500

Order by avg(sal) desc;

17）              // 子查询

Select cname, sal from emp

Where sal = (select max(sal) from emp);

// 复杂子查询

Select ename, sal

From emp join (select max(sal) max_sal, deptno from emp group by depno) t

On (emp.sal = t.max_sal and emp.deptno = t.deptno);

18）              // 自连接（ SQL1992 ）

Select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

// 连接（ SQL1999 ）

// 笛卡尔连接

Select ename, dname from emp cross join dept;

// 等值连接

Select ename, dname from emp join dept on (emp.deptno = dept.deptno);

// 非等值连接

Select ename, grade from emp e join salgrade s on (e.sal between s.lowsal and s.highsal);

// 多表连接

Select ename ,dname, grade from

Emp e join dept d on (e.deptno = d.deptno)

Join salgrade s on (e.sal between s.lowsal and s.highsal);

// 左外连接

Select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);

// 全连接

Select ename, dname from

Emp e full join dept d on (e.deptno = d.deptno);

19）              // 伪列 rownum( 只能与 <= 或 < 用 )

// 取前五列

Select empno, ename from emp where rownum <= 5;

// 取任意范围的列

Select ename from (select rownum r, ename from emp) where r between 5 and 10;