Oracle学习笔记4

 
一．索引
1）//可以跟多个字段，表示多个字段组成的索引
Create index idx_stu_email on stu(email)
2）Drop index idx_stu_email
3）加主键约束或者唯一约束时，系统会自动加索引
4）加了索引会使读更快，但修改更慢，不要轻易建立索引
 
二．视图
1）视图就是一个子查询，视图越多维护越难
2）视图可以更新数据，但是很少用它更新数据
3）Create view v$_avg_sal as
  select deptno, grade
  from (
select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on
                       (t.avg_sal between s.lowsal and s.highsal)
     ) t1 join dept d on (t1.deptno = d.deptno);
 
三．序列（sequence，Oracle特有）
1）维护主键，线程安全
2）Create sequence seq;
3）Create sequence seq start with 1 increment by 1;
4）Select seq.nextval from dual;
5）Drop sequence seq;
 
四．DML
1）insert into tablename(col1, col2, col3) values(v1, v2, v3);
2）update tablename set col1 = value1 where…
3）delete from tablename where ….
 
五．DDL
1）Create table emp2 as select * from emp;
2）Create table stu
(
         Id number(6) primary key,
         --给约束起别名，字段级约束
         Name varchar2(20) constraint stu_name_notnull not null,
         Sex number(1),
         Age number(3),
         Sdate date,
         Grade number(2) default 1,
         Classid number(4) references class(id),
         --字段唯一，但是可以插入null
         Email varchar2(50) unique,
     &nb