oracle基础

总结:
table
DQL: Selete
单行函数 / 组函数 / 关联查询(内连接 / 外连接) / 子查询 (关联子查询 / 非关联子查询)
DML: insert / update / delete
DDL: create / drop / truncate / alter
TCL: commit / rollback / savepoint
DCL: grant  / revoke除了数据表table外,还有其他的数据库对象:
view
index
sequence(oracle独有的)
......20110110
一.index
--创建一个表,其中id是主键
create table mytable(
id number primary key,
name varchar2(20));
--查询数据字典,检查系统自动创建的索引
select index_name from user_indexes
where table_name = 'MYTABLE';
--给表mytable的name列手动增加索引
create index mytable_name_idx
on mytable(name);
--再查数据字典,检查手动创建的索引;--索引的工作原理.(rowid)
--索引的创建:自动和手动
--在哪些情况下适合创建和不适合创建索引
--哪些情况会使索引失效二.视图view
--基于基表的逻辑结构,其本质是查询语句.
--简单视图
create or replace view v_emp_sal
as
select ename, sal from emp_ninglj;--复杂视图
create or replace view v_emp_sal
(id, name, salary, bonus)
as
select empno, ename, sal,
       coalesce(comm, sal, 100)
from emp_ninglj;
--等价于上一条语句
create or replace view v_emp_sal
as
select empno id, ename name, sal salary,
       coalesce(comm, sal, 100) bonus
from emp_ninglj;
--一旦查询中有表达式或函数,必须指定列名
--使用直接在视图中定义列名或给列别名都可以.
--在数据字典中查看视图
select text from user_views 
where view_name = 'V_EMP_SAL';select count(*) from user_views;--创建复杂视图
create or replace view v_dept_sal
as
select dname, avg(sal) avg_sal
from emp join dept
on emp.deptno = dept.deptno
group by dname;--删除视图
drop view v_dept_sal;三.行内视图(Inline view)
--查询机构下哪些人的薪水比本部门的平均薪水高
--子查询出现的位置是:where / having条件中
--行内视图出现的位置:from 子句中
select e.ename, e.sal
from emp e join (select deptno, avg(sal) avg_sal
  from emp
  group by deptno)a
on e.deptno = a.deptno
and e.sal > a.avg_sal;--伪列rowid, rownum
select rownum, ename from emp
where rownum <= 5;--排名问题(TOP-N分析)
--薪水最低的五个人
select ename, sal 
from (select ename, sal from emp 
           order by sal )
where rownum  <= 5;--薪水最高的三个人,注意排除空值
select ename, sal 
from (select ename, sal from emp 
          where sal is not null
           order by sal desc)
where rownum  <= 3;--入职时间最早的三个人
select ename, hiredate 
from (select ename, hiredate from emp 
           order by hiredate)
where rownum  <= 3;--分页问题
要显示120条记录,每页显示10条,12页
Page1: 1-10
page 2: 11-20
.... 
page 5: 41-50
....
select ename, sal
from (select ename, sal, rownum rn
           from emp )
where rn between 4 and 8;
--按薪水排序取到第4到第8条记录
select ename, sal
from 
(select ename, sal, rownum rn from
 (select ename, sal
           from emp order by sal))
where rn between 4 and 8;--rownum是oracle数据库的伪列
--在其他数据库中如解决排名问题 / 分页问题不能采用现有的解决方式.
--mysql / sql server有主键自增长机制--创建序列,起点是1,步进是1.
create sequence myseq;
create table mytemp(id number primary key);
insert into mytemp values(myseq.nextval);
--消耗掉几个序列值
select myseq.nextval from dual;
--多增加几条
insert into mytemp values(myseq.nextval);
--查看结果
select * from mytemp;--新建序列,起始值是51, 步进是10;
create sequence dept_seq
start with 51
increment by 10;
insert into mytemp values(dept_seq.nextval);
select * from mytemp;--currval / nextval被叫做序列的伪列.
--current value / next value
--currval必须在nextval之后执行.--更改序列,在更改前序列产生的值不受影响.
alter sequence seq increment by 2;--删除序列
drop sequence seq;
--SQL99标准：
select e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno;--SQL92标准
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;总结:
table
DQL: Selete
单行函数 / 组函数 / 关联查询(内连接 / 外连接) / 子查询 (关联子查询 / 非关联子查询)
DML: insert / update / delete
DDL: create / drop / truncate / alter
TCL: commit / rollback / savepoint
DCL: grant  / revoke除了数据表table外,还有其他的数据库对象:
view
index
sequence(oracle独有的)
......20110110
一.index
--创建一个表,其中id是主键
create table mytable(
id number primary key,
name varchar2(20));
--查询数据字典,检查系统自动创建的索引
select index_name from user_indexes
where table_name = 'MYTABLE';
--给表mytable的name列手动增加索引
create index mytable_name_idx
on mytable(name);
--再查数据字典,检查手动创建的索引;--索引的工作原理.(rowid)
--索引的创建:自动和手动
--在哪些情况下适合创建和不适合创建索引
--哪些情况会使索引失效二.视图view
--基于基表的逻辑结构,其本质是查询语句.
--简单视图
create or replace view v_emp_sal
as
select ename, sal from emp_ninglj;--复杂视图
create or replace view v_emp_sal
(id, name, salary, bonus)
as
select empno, ename, sal,
       coalesce(comm, sal, 100)
from emp_ninglj;
--等价于上一条语句
create or replace view v_emp_sal
as
select empno id, ename name, sal salary,
       coalesce(comm, sal, 100) bonus
from emp_ninglj;
--一旦查询中有表达式或函数,必须指定列名
--使用直接在视图中定义列名或给列别名都可以.
--在数据字典中查看视图
select text from user_views 
where view_name = 'V_EMP_SAL';select count(*) from user_views;--创建复杂视图
create or replace view v_dept_sal
as
select dname, avg(sal) avg_sal
from emp join dept
on emp.deptno = dept.deptno
group by dname;--删除视图
drop view v_dept_sal;三.行内视图(Inline view)
--查询机构下哪些人的薪水比本部门的平均薪水高
--子查询出现的位置是:where / having条件中
--行内视图出现的位置:from 子句中
select e.ename, e.sal
from emp e join (select deptno, avg(sal) avg_sal
  from emp
  group by deptno)a
on e.deptno = a.deptno
and e.sal > a.avg_sal;--伪列rowid, rownum
select rownum, ename from emp
where rownum <= 5;--排名问题(TOP-N分析)
--薪水最低的五个人
select ename, sal 
from (select ename, sal from emp 
           order by sal )
where rownum  <= 5;--薪水最高的三个人,注意排除空值
select ename, sal 
from (select ename, sal from emp 
          where sal is not null
           order by sal desc)
where rownum  <= 3;--入职时间最早的三个人
select ename, hiredate 
from (select ename, hiredate from emp 
           order by hiredate)
where rownum  <= 3;--分页问题
要显示120条记录,每页显示10条,12页
Page1: 1-10
page 2: 11-20
.... 
page 5: 41-50
....
select ename, sal
from (select ename, sal, rownum rn
           from emp )
where rn between 4 and 8;
--按薪水排序取到第4到第8条记录
select ename, sal
from 
(select ename, sal, rownum rn from
 (select ename, sal
           from emp order by sal))
where rn between 4 and 8;--rownum是oracle数据库的伪列
--在其他数据库中如解决排名问题 / 分页问题不能采用现有的解决方式.
--mysql / sql server有主键自增长机制--创建序列,起点是1,步进是1.
create sequence myseq;
create table mytemp(id number primary key);
insert into mytemp values(myseq.nextval);
--消耗掉几个序列值
select myseq.nextval from dual;
--多增加几条
insert into mytemp values(myseq.nextval);
--查看结果
select * from mytemp;--新建序列,起始值是51, 步进是10;
create sequence dept_seq
start with 51
increment by 10;
insert into mytemp values(dept_seq.nextval);
select * from mytemp;--currval / nextval被叫做序列的伪列.
--current value / next value
--currval必须在nextval之后执行.--更改序列,在更改前序列产生的值不受影响.
alter sequence seq increment by 2;--删除序列
drop sequence seq;
--SQL99标准：
select e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno;--SQL92标准
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;
一.select...
from...
where...
order by...二.函数(单行函数)
字符函数 / 数字函数 / 日期函数0105:
一.函数
1.继续日期函数
round / trunc
2.转换函数
to_number:字符->数字
16进制转换:ab = a*16+b=10*16+11 = 171
to_char:数字->字符
to_date:字符->日期
insert into emp(empno, ename, hiredate)
values(8899, 'bono',
 to_date('2010-12-15','yyyy-mm-dd'));update emp
set hiredate = 
to_date('2010/12/10','yyyy/mm/dd')
where empno = 8899;commit;
select empno, ename, hiredate from emp;to_char:日期->字符
3.通用函数
nvl / coalesce / decode
二.多表联合查询
内连接: 等值连接 / 非等值连接 / 自连接
外连接: right / left / full三.分组函数
select...
from...
where...
group by...
having...
order by...avg / sum / max / min / count四.子查询20100106
一.子查询(非关联)
select c1,c2 from tab_name
where c3 = (select c4 from other_tab);
子查询的结果是:
1.单行单列 :子查询只有一条记录一列数据返回, 可以使用单行比较运算符= > < >=
select ename from emp
where sal = (select min(sal) from emp);
2.多行单列:子查询有多于一行记录一列数据返回,可以使用多行比较运算符: in / not in
select ename from emp
where deptno in 
(select deptno from emp
where ename = 'SCOTT');
如果机构下有多于一个人叫SCOTT,则返回多行记录.
3.单行多列,处理和单行单列一样.
select ename from emp 
where (deptno, job) = (
select deptno, job from emp
where empno = 7788);
4.多行多列.处理方式和多行单列相同,使用in / not in
select ename, deptno, sal
from emp
where (deptno, sal) in 
(select deptno, max(sal)
from emp
group by deptno);
在where条件中使用子查询
在having子句中使用子查询二.子查询(关联)
exists / not exists三.集合操作
union / union all / minus / intersect
结果集必须是同构的.四.约束条件;