oracle5:oracle的图形界面操作，分页查询，练习

图形界面操作

--oracle的图形界面
  --创建表（对于表的各种设置约束）
  --对于表的数据的填充，
  --创建视图
  --创建索引（自动生成的就可以）
  --创建序列
  --常见用户。
  --视图的另一种作用：
  比如一个三表的联合查询经常用到，这样就可以将他设置成视图，下面用到就可以直接使用视图进行sql语句的查询，
  而不用在写复杂的三表联合查询的语句
--------------------------------------------------------------------------------------------------------------------------------------

分页查询

--分页查询：当一个表的数据量特别大时，如果一次性全部显示，浏览性会很差。
          --使用分页查询
          --使用关键字rownum :oracle对外自动提供查询结果编号的关键字，跟每行的数据没有关系
          select rownum,e.* from emp e;
          --注意：rownum只能做<,<=的操作，不能做>,>=的操作。
          (首先会执行from,在执行where，这是查询到rownum>12的数据，但是在执行select的时候，rownum会再次自动分配查询结果的编号，
          从1开始，这就跟要rownum>12的结果冲突了)
                 select rownum ,e.* from emp e where rownm > 12;--错误的
 --查询第一页的数据，每页5个数据
         select rownum r ,e.* from emp e where rownum <=5;
         select * from (select rownum r ,e.* from emp e where rownum <=5) s where s.r >0;
-- 查询第二页的数据，每页5个数据 
         select rownum r ,e.* from emp e where rownum <=10;
         select * from (select rownum r ,e.* from emp e where rownum <=10) s where s.r>5;
-- 查询第三页的数据，每页5个数据 
         select rownum r ,e.* from emp e where rownum <=15;
         select * from (select rownum r ,e.* from emp e where rownum <=15) s where s.r>10;
--分页查询的规律总结：查询第n页的数据，每页存m条数据。
          select * from (select rownum r ,e.* from 要分页的表 e where rownum <=m*n) s where s.r>m*(n-1);
          --要分页的表既可以是一个表，也可以是一个查询语句形成的结果集的表。
--分页查询员工信息按照工资排序
          select * from emp e order by e.sal;
          select rownum r ,s.* from ( select * from emp e order by e.sal) s where rownum<5;
          select * from ( select rownum r ,s.* from ( select * from emp e order by e.sal) s where rownum<5) t where t.r>0;
         

数据库备份

命令行

plsql工具中

数据库的备份：
使用plsql进行备份
      --导出数据库
          --选择Tools
          --选择Export Tables
          --选择要备份的表
                     --oracle export(选择地址，进行导出)
                     这是导出表的结构
                     --sql insert (选择地址，进行导出)
                     这是导出表的数据。 
      --导入数据库
          --选择Tools
          --选择import Tables
                            

练习

create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
drop table student;
drop table teacher;
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;
 
 select * from student;
 select * from teacher;
 select * from course;
 select * from sc;
drop table sc;
练习：
注意：以下练习中的数据是根据初始化到数据库中的数据来写的SQL 语句，请大家务必注意。
 
 select * from student;
 select * from teacher;
 select * from course;
 select * from sc;
--1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select s1.sno from (select * from sc where cno='c001') s1
inner join (select * from sc where cno ='c002') s2
on s1.sno=s2.sno
where s1.score > s2.score;
--2、查询平均成绩大于60 分的同学的学号和平均成绩；
--where中不允许出现多行函数
select sno,avg(score)from sc group by sno having avg(score) > 60;
--3、查询所有同学的学号、姓名、选课数、总成绩；
select s.sno,s.sname, s1.ss 总成绩,s1.cc 选课数 from student s
left join (select sum(score) ss,count(*) cc,sno from sc group by sno) s1
on s.sno = s1.sno;
--4、查询姓“刘”的老师的个数；
select count(*) from teacher where tname like '刘%';
--5、查询没学过“谌燕”老师课的同学的学号、姓名；
select distinct stsc.sno, stsc.sname from stsc 
inner join (select cno from course where tno in (select tno from teacher where tname <>'谌燕')) s
on stsc.cno = s.cno ;
--6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select s.sno,s.sname from student s
inner join (select sno from sc where cno='c002' and sno in  (select sno from sc where cno='c001')) s1
on s.sno = s1.sno;
--7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
 select distinct stsc.sno,stsc.sname from  stsc 
 inner join (select cno from course where tno in (select tno from teacher where tname ='谌燕')) s1
 on stsc.cno = s1.cno;
--8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select s.sno ,s.sname from 
(select * from sc where cno ='c002') s1
inner join (select * from sc where cno ='c001') s2
on s1.sno =s2.sno
inner join student s
on s.sno = s1.sno
where s1.score < s2.score;
--9、查询所有课程成绩小于60 分的同学的学号、姓名；
select distinct s.sno,s.sname from student s
inner join (select * from sc where sc.score < 60)  s1
on s1.sno = s.sno;
--10、查询没有学全所有课的同学的学号、姓名；
--select sno from sc where cno  <> all (select cno from course);
--select distinct count(*) from course;
--select sno from sc group by sno having count(cno) <(select distinct count(*) from course);
select distinct s.sno ,s.sname from student s
inner join (select sno from sc group by sno having count(cno) <(select distinct count(*) from course)) s1
on s1.sno = s.sno;
--11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
--select cno from sc where sno='s001';
--思路：找出学号为's001'的课程，连接表sc通过cno相等即可。在连接student表。
select distinct s1.sno,s1.sname from sc  
inner join (select cno from sc where sno='s001') s
on s.cno =sc.cno
inner join student s1
on  sc.sno = s1.sno
where s1.sno <>'s001'; 
--12、查询至多学过学号为“s001”同学所有一门课的其他同学学号和姓名；
select distinct s.sno ,s.sname from student s
left join sc 
on s.sno = sc.sno
where s.sno <> 's001' and sc.cno in (select cno from sc where sno ='s001');
--13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
select cno from course where tno in (select tno from teacher where tname='谌燕');
select avg(score),sc.sno from sc ,course c where sc.cno = c.cno  group by sc.sno ;
update sc set score = (select avg(score),sc.sno from sc ,course c where sc.cno = c.cno  group by sc.sno )
where sc.cno  in (select cno from course where tno in (select tno from teacher where tname='谌燕'));
--14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
不会
--15、删除学习“谌燕”老师课的SC 表记录；
delete from sc where sc.cno in (select cno from teacher t,course c where c.tno=t.tno and t.tname='谌燕');

--16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
不会
--17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cno 课程ID,max(score) 最高分,min(score) 最低分 from sc group by cno ;
--18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
--case when then else end的用法
select cno,avg(score) 平均成绩,sum(case when score >=60 then 1 else 0 end)/count(*) as 及格率
 from sc group by cno order by 平均成绩,及格率 asc;
--19、查询不同老师所教不同课程平均分从高到低显示
select sc.cno ,avg(sc.score) from teacher t
inner join course c 
on c.tno = t.tno
inner join sc 
on sc.cno =c.cno
group by sc.cno 
order by avg(sc.score) asc;
--20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
--case when then else end的用法，
--输出name可以使用max
select sc.cno,max(c.cname),
sum(case when sc.score <=100 and sc.score >=85 then 1 else 0 end),
sum(case when sc.score <85 and sc.score >=70 then 1 else 0 end),
sum(case when sc.score <75 and sc.score >=60 then 1 else 0 end),
sum(case when sc.score <60 then 1 else null end)
from course c 
inner join sc 
on sc.cno = c.cno
group by sc.cno;
--21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
--row_number() over(partition by 分组列 order by 排序列 desc) 分组排序函数
select * from 
(select sc.sno,sc.cno,sc.score,row_number()over(partition by cno order by score) ro from sc) a
where a.ro <4;
--22、查询每门课程被选修的学生数
select count(*) cno from sc group by cno;
--23、查询出只选修了一门课程的全部学生的学号和姓名
select s.sno,s.sname from student s
inner join (select sno from sc group by sno having count(cno) = 1) s1
on s.sno = s1.sno; 
--24、查询男生、女生人数
select ssex,count(*) from student group by ssex;
--25、查询姓“张”的学生名单
select * from student s where s.sname like '张%';
--26、查询同名同性学生名单，并统计同名人数
--思路：按照名字分组
select s.sname,count(s.sno) from student s group by sname  having count(s.sno) >1; 
--27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)
select s.sname from student s where s.sage = to_number((to_char(sysdate,'yyyy')-1981)); 
--28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cno,avg(score) from sc group by cno order by avg(score) asc,cno desc;
--29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
select s.sno,s.sname,s1.ass  from student s
inner join (select sno,avg(score) ass from sc group by sno having avg(score)> 85) s1
on s.sno = s1.sno;
--30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
select s.sname,sc.score from student s 
inner join sc 
on  s.sno = sc.sno
where sc.cno in (select cno from course where cname ='数据库')
--------------------------------------------------------------------------------------------------------------
--31、查询所有学生的选课情况；
select  distinct s.sno,s.sname,c.cname from student s
left join sc
on s.sno = sc.sno
left join course c
on sc.cno = c.cno
order by s.sno;
--32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select s.sname,c.cname,sc.score from student s
inner join sc 
on s.sno =sc.sno
inner join course c
on c.cno = sc.cno
where sc.score > 70;

--33、查询不及格的课程，并按课程号从大到小排列
select  distinct cno from sc  where score < 60 order by cno asc;
--34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select distinct s.sno ,s.sname from student s
inner join sc
on s.sno = sc.sno
where sc.cno='c001' and sc.score > 80;
--35、求选了课程的学生人数
select count(s.sno) from (select sno from sc group by sno) s ;
--36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
 select s.sname, s1.ms from student s
 inner join ( select max(sno) mss, cno,max(score) ms from sc group by cno) s1
 on s.sno = s1.mss
 inner join (select cno from course c where c.tno in(select tno from teacher where tname ='谌燕')) s2
 on s2.cno = s1.cno;
--37、查询各个课程及相应的选修人数
select cno ,count(*) from sc group by cno ;
--38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select s1.sno,s1.score,s1.cno from sc s2
inner join sc s1
on s1.sno = s2.sno
inner join student s
on s.sno = s1.sno
where s1.cno <>s2.cno and s1.score =s2.score;
--39、查询每门功课成绩最好的前两名
select * from 
(select sc.sno,sc.cno,sc.score,row_number()over(partition by cno order by score) ro from sc) a
where a.ro <3;
--40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cno 课程号,count(*) 选修人数 from sc group by cno having count(*) > 10 order by count(*);
--41、检索至少选修两门课程的学生学号
select  sno from sc group by sno having count(*) >=2;
--42、查询全部学生都选修的课程的课程号和课程名
select c.cno,c.cname from course c 
inner join (select count(*) cc,cno from sc group by cno) s
on s.cno = c.cno
where s.cc = (select count(sno) from student);
--43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

select  distinct s.sno,s.sname from student s
left join sc 
on s.sno = sc.sno
left join (select cno from course c where c.tno in (select tno from teacher where tname <>'谌燕')) s1
on s1.cno = sc.cno;
--44、查询两门以上不及格课程的同学的学号及其平均成绩
select sno,avg(score) from (select * from sc where score <60) group by sno having count(*) >2;
--45、检索“c004”课程分数小于60，按分数降序排列的同学学号
select sno from sc where sc.cno='c004' and score < 60 order by score ;

--46、删除“s002”同学的“c001”课程的成绩
delete from sc where sc.sno='s002' and sc.cno='c001';
select * from sc;