oracle数据库对象篇Database Object

                  Other Database Object
____________________________________________________________________
Database Object
Sequence
Index
View
===================================================================
Sequence

sequence (oracle专用)是共享的每一个表都可以用,但是序列会不连续
所以最好每一张表单独建一个序列
sequence 1.序列名.nexval
         2.序列名.cullval

create sequence lhj_seq;
insert into studentlhj values(lhj_seq.nextval,'zhou',16,1);
insert into studentlhj values(lhj_seq.nextval,'mei',22,2);
sequence从1开始.

create sequence lhj_seq increment by 4 maxvalue 20
start with 4 ;

insert into studentlhj values(lhj_seq.nextval,'xu',35,2);

alter sequence lhj_seq nomaxvalue;

user_squences
SQL> desc user_sequences
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
 MIN_VALUE                                          NUMBER
 MAX_VALUE                                          NUMBER
 INCREMENT_BY                              NOT NULL NUMBER
 CYCLE_FLAG                                         VARCHAR2(1)
 ORDER_FLAG                                         VARCHAR2(1)
 CACHE_SIZE                                NOT NULL NUMBER
 LAST_NUMBER                               NOT NULL NUMBER

insert into studentlhj values(lhj_seq.currval,'mei',22,2);
//插入当前值
select lhj_seq.currval from dual;//查看序列的当前值

______________________________________________________
练习1
用一条sql语句删除表中的重复记录(名字重复的即为重复记录)
select * from studentlhj;
select rowid from studentlhj;

SQL> select * from studentlhj;

        ID SNAME             AGE        CID
---------- ---------- ---------- ----------
       101 zhang              23          1
       102 liu                33          2
       103 sun                25          2
       104 wu                 23          2
       106 ccc                28
       105 wang               18          1
       107 wu                 33          2
         1 zhou               18          1
         2 zhou               16          1
         3 mei                22          2
         4 xu                 26          2

        ID SNAME             AGE        CID
---------- ---------- ---------- ----------
         8 xu                 35          2

1>
***
delete form studentlhj a where id>
(select min(id) from studentlhj b where a.sname=b.sname)
***
2>效率高
***
delete from  studentlhj a where a.rowid>(select min(rowid) from
studentlhj b where  a.sname=b.sname);
***
________________________________________________

练习2
随机取表中的三条记录
dbms_random.random会自动加入表中
select dbms_random.random  from studentlhj;
select * from (
select * from studentlhj order by dbms_random.random)
where rownum<=3;


select * from studentlhj order by 'a';

====================================================================
index
index_table(两个字段)
建索引的字段 rowid
create index index_stulhj on studentlhj(sname);
数据字典表user_indexes
select index_name from user_indexes where table_name='STUDENTLHJ';

Single column
*create bitmap index  index_lhj1 on studentlhj(id);

Concatenated or complsite(multiple columns used in the index)
*create index index_lhj2 on studentlhj(id,age);

====================================================================
view
user_views数据字典视图

//两张表进行连接查询
select b.sname,b.id,a.name from classlhj a,studentlhj b
where a.id=b.cid;
 
//创建视图,视图的内容是两表连接查询(子查询不能用order by)的结果.
create or replace view c_s_lhjview as
select b.sname,b.id,a.name from classlhj a,studentlhj b
where a.id=b.cid;

select * from c_s_lhjview;
insert into c_s_lhjview values('www','108',1);
--------------------------------------------------------------------
insert into c_s_lhjview values('www','108',1)
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
--------------------------------------------------------------------
不能向复杂视图中插入数据(即通过视图向两张表中加入数据)
复杂视图不能作dml操作
_______________________________

create or replace view studentlhj_view as
select sname,age,cid from studentlhj;

insert into studentlhj_view(sname,age,cid) values('ff',34,1);
//向视图中加了一条记录,对应的表中也加了一条记录.
解决方法:(with read only)禁止通过视图对表进行增,删,改
create or replace view studentlhj_view as
select sname,age,cid from studentlhj with read only;

WITH CHECK OPTION Clause
________________________________________
create or replace view stu_lhj_view2 as
select * from student_lhj where age=23
with  check option constraint stulhj_view_ck;
//constraint后的名字任意取
SQL>  select * from stu_lhj_view2;

        ID SNAME             AGE
---------- ---------- ----------
       104 wu                 23

insert into stu_lhj_view2 values(101,'lhj',20);
SQL> insert into stu_lhj_view2 values(101,'lhj',20);
insert into stu_lhj_view2 values(101,'lhj',20)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
//age 必须等于23时才插入视图

create table lhj_null2(
  id number(3) primary key,
  name varchar(10),
  age number(3) not null
);
create or replace view lhj_null2_view as
select id,name from lhj_null2;

insert into lhj_null2_view values(101,'dba');

SQL> insert into lhj_null2_view values(101,'dba');
---------------------------------------------------------------
insert into lhj_null2_view values(101,'dba')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPENLAB"."LHJ_NULL2"."AGE")
----------------------------------------------------------------
-error 原因虽然视图是简单视图,但他没有选出表中的非空(not null)列,
向视图中插入数据反映到表中时违反非空约束.

======================================================================
总结：
sql  ----select
         dml(insert delete update)
         ddl(create table/view/index drop table)
         dcl(commit rollback grant)
连接查询(补充)
等值连接
不等值连接
内连接
外连接
1>select a.name,b.sname from classlhj a,studentlhj b where a.id=b.cid;
2>select a.name,b.sname from classlhj a join studetnlhj b on a.id=b.cid;
//等值连接1>等价于2>
左外连接
3>select a.name,b.sname from classlhj a,studentlhj b where a.id(+)=b.cid;
4>select a.name,b.sname from classlhj a right join studetnlhj b
on a.id=b.cid;
//right以右边为准匹配3>等价于4>

=======================================================================
                       Inline Views
create table lhj_emp as select * from s_emp;
select id,userid,dept_id,salary from lhj_emp;
----找出工资大于所在部门平均工资的员工
1>
select id,userid,salary from lhj_emp a where a.salary>
(select avg(salary) from lhj_emp b where
a.dept_id=b.dept_id);
2>
select id,salary from lhj_emp a,
(select dept_id,avg(salary) avgs from lhj_emp group by dept_id)b
where a.dept_id=b.dept_id and a.salary>b.avgs;
//inline views

---in
查找管理者的信息
select id,userid from lhj_emp where id in(select manager_id
from lhj_emp group by manager_id having count(*)>1);
---exists
select id,userid from lhj_emp a where exists(
select 'm' from lhj_emp where manager_id=a.id);

如果用exists和in都能查找出所需的结果最好用exists
因为(in 子查询需要排序,全表查找)

练习
select last_name,title,dept_id from s_emp outer
where exists (select 'x' from s_emp where manager_id=outer.id);

select a.*,rowid from studentlhj a where id=1;

====================================================================

UNION
查询各年龄段的人数
1>
select count(*) from studentlhj group by trunc(age/10) ;
2>
select * from studentlhj where age between 20 and 29
union
select * from studentlhj where age between 30 and 39;

select * from studentlhj where age>20
union
select * from studentlhj where age>30;
//有重复记录时只取其中一条(union all不去重)

INTERSECT
select * from studentlhj where age>21
intersect
select * from studentlhj where age<30;
//取交集

MINUS Page97
select * from studentlhj where rownum<=5
minus
select * from studentlhj where rownum<=2;

=====================================================================
Altering Tables and Constraints

----alter
add column
alter table  student_lhj add deptno number(3);
//添加一个字段
drop column
alter table student_lhj drop column deptno;
//删除字段要加cloumn关键字
modify type
alter table student_lhj modify age varchar(10);
//表中的记录为空时才可以修改字段的类型,要修改需要删除已有的数据.
alter table student_lhj modify sname varchar(1);
---------------------------------------------------------
alter table student_lhj modify sname varchar(1)
                               *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big
-----------------------------------------------------------------------
//修改length的时候只能比原来的length大,否则就会出现如上所示错误.

----Constraints (Page177)
alter table s_emp
disable constraint s_emp_id_pk CASCADE;
rename
truncate
//删除表中的记录时,不能回滚.