oracle基础应用

部门表
create table dept(
deptid varchar2(4),
deptname varchar2(20),
superId varchar2(4),
superName varchar2(20)
)

学生表
create table Scores (
name varchar2(20),
item varchar2(30),
score number 
)

班级表
create table cla(
className varchar2(20),
studentName varchar2(20))
select * from cla for update
select * from Scores

select * from cla for update

test 表

create table test1(
name varchar2(30),
test varchar2(40)
)


select  * from test1 for update

学生信息表
create table StuInfo( 
classname varchar2(20),
name varchar2(20),
AllScores number, 
avg number
)



1.求

select distinct name  from Scores where name not in (select distinct name from Scores where score<80)
select name from (select distinct name , min(Score) over(partition by name order by Score ) e from Scores) where e>80
1.求每名学生的平均分
select name , avg(score) from Scores group by name 
select distinct name , avg(Score) over(partition by name)  as avgScore from Scores 
2.求平均分高于85的
select  name , avg(Score) from Scores where name <> '张三' group by name having avg(Score)>85 order by avg(Score)
3.查每个学生成绩并按照降序排列
select t.*, rank() over(partition by name order by Score desc) as count from Scores t 
4，查每个学生最好的学科成绩并输出
select distinct name,max(Score) over(partition by name ) from Scores

5，将成绩以横式显示
select name,
       max(case item
             when '语文' then
              score
             else
              0
           end) as 语文,
       max(case item
              when '数学' then
               score
              else
               0
            end) as 数学，max(case item
         when '英语' then
          score
         else
          0
       end) as 英语
  from Scores
 group by name
6
将3所要的结果分页显示每页5个
select *
  from (select rownum rn, s.*
          from (select t.*,
                       rank() over(partition by name order by Score desc) as count
                  from Scores t) s
         where rownum <= pageSize.rowSize)
 where rn >=pageSize.(rowsize-1)+1
7.层次查询
向上
select t.*,level from dept t start with deptid='1007' connect by prior superid=deptid

向下
select * from deptid start with deptid='1001' connect by prior deptid=superid
8.多表关联查询

select * from Scores for update
select cla.classname,Scores.* from cla,Scores where cla.studentname=Scores.name  
select Scores.*,cla.classname from Scores left join cla on Scores.name =cla.studentname

9.自连接查询

select t1.deptid ,t1.deptname, t2.deptid,t2.deptname from dept t1 ,dept t2 where t1.deptid=t2.superid

10,集合运算  
union 去重  union all 不去重
select * from Scores where name ='张三' or name='李四'
union all
select * from Scores where name ='张三'

差集
select * from Scores where name ='张三' or name='李四'
minus
select * from Scores where name ='张三'

交集
select * from Scores where name ='张三' or name='李四'
intersect
select * from Scores where name ='张三'

--11存储过程 存储函数 触发器  

编写存储过程将学生信息插入表中
Select * from StuInfo
select max(t.classname) classname,
       t.name,
       sum(t.score) sum,
       avg(t.score) avg
  from (select cla.classname, Scores.*
          from Scores, cla
         where Scores.name = cla.studentname
           and cla.classname = '三年一班') t
 group by t.name




存储过程
create or replace procedure getStudentInfo(classname in stuinfo.classname%type)
as
begin
 insert into stuinfo select max
   (t.classname) classname, t.name, sum
   (t.score) sum, avg
   (t.score) avg from
   (select cla.classname, Scores.*
      from Scores, cla
     where Scores.name = cla.studentname
    and cla.classname = classname) t group by t.name  ;
      commit; 
end;
存储过程的调用
begin
getStudentInfo(classname=>'三年一班');
end ;

存储函数
create or replace Function getdeptName(id in dept.deptid%type  ) return varchar2 
as 
deptname dept.deptname%type;
begin
select dept.deptname into deptname from  dept where deptid=id;

return deptname;

end;
调用 函数
select getdeptname('1001') from dual
必须在命令模式下
declare
name dept.deptname%type;
begin
name:=getdeptname('1001');
dbms_output.put_line(name);
end;




--12 事务 
四大特性：
原子性：要么都执行要么都不执行
一致性：不管事务执行多长时间，永远取的是事务开始那一刻的状态
隔离性：对于未提交的事务，其他事务对其结果不可见
持久性：对于提交的事务，数据库通过日志对其结果永远保持
dml： select update delete insert
ddl:  create alter drop
dcl : grant,deny,revoke
查看事务：select * from v$transaction
查看锁：select * from v$lock
怎么杀掉占用过长的session,即某表被占用如何杀掉

--1
select V.session_id,
       L.owner,
       L.object_name from v$locked_object V,
       dba_objects l
 where v.OBJECT_ID = l.object_id
   and l.object_name = 'TEST1'---表名
--2
select t2.username, t2.sid, t2.serial#, t2.logon_time
  from v$locked_object t1, v$session t2
 where t1.session_id = t2.sid
 and  t2.sid='22' --将上面得到的sid传过来
 order by t2.logon_time;
--3
 alter system kill session 'sid,serial#' --杀掉 








13数据库优化
1.建索引
2.做分区
3.物化视图
4.并行处理

1索引 
查看本库有哪些索引
select * from user_ind_columns

解决索引碎片问题
analyze index ASJBH validate structure
select name ,height,pct_used , del_lf_rows/lf_rows   from index_stats
如果
height>=4
pct_used<50%
del_lf_rows/lf_rows>0.2
出现其一便有索引碎片问题
Oracle 10g之后有这个功能
索引重构
alter index 索引名 rebuild online


2 物化视图 
create materialized view v_ab as select * from 
在创建物化视图时要用DBA给用户创建物化视图的权限 
grant create materialized view to system
收回权限
revoke create materialized view from system 
创建物化视图的命令  
全表刷新的物化视图
create materialized view v_ab refresh force on commit as select ......
增量刷新
create materialized view log on A with rowid
create materialized view log on B with rowid
 create materialized view mv_ab refresh fast on demand start with sysdate next sysdate+1/1440 
 as select a.rowid as arowid ,b.rowid as browid ....from a ,b  
 
 


13.对于oracle数据库每一列都有一个唯一的rowid与之对应