本文共 11875 字，大约阅读时间需要 39 分钟。

图形界面操作

--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') s1inner join (select * from sc where cno ='c002') s2on s1.sno=s2.snowhere 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 sleft join (select sum(score) ss,count(*) cc,sno from sc group by sno) s1on 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 <>'谌燕')) son stsc.cno = s.cno ;--6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；select s.sno,s.sname from student sinner join (select sno from sc where cno='c002' and sno in  (select sno from sc where cno='c001')) s1on 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') s1inner join (select * from sc where cno ='c001') s2on s1.sno =s2.snoinner join student son s.sno = s1.snowhere s1.score < s2.score;--9、查询所有课程成绩小于60 分的同学的学号、姓名；select distinct s.sno,s.sname from student sinner join (select * from sc where sc.score < 60)  s1on 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 sinner join (select sno from sc group by sno having count(cno) <(select distinct count(*) from course)) s1on 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') son s.cno =sc.cnoinner join student s1on  sc.sno = s1.snowhere s1.sno <>'s001'; --12、查询至多学过学号为“s001”同学所有一门课的其他同学学号和姓名；select distinct s.sno ,s.sname from student sleft join sc on s.sno = sc.snowhere 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 tinner join course c on c.tno = t.tnoinner join sc on sc.cno =c.cnogroup by sc.cno order by avg(sc.score) asc;--20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]--case when then else end的用法，--输出name可以使用maxselect 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.cnogroup 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) awhere a.ro <4;--22、查询每门课程被选修的学生数select count(*) cno from sc group by cno;--23、查询出只选修了一门课程的全部学生的学号和姓名select s.sno,s.sname from student sinner join (select sno from sc group by sno having count(cno) = 1) s1on 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 sinner join (select sno,avg(score) ass from sc group by sno having avg(score)> 85) s1on s.sno = s1.sno;--30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数select s.sname,sc.score from student s inner join sc on  s.sno = sc.snowhere sc.cno in (select cno from course where cname ='数据库')----------------------------------------------------------------------------------------------------------------31、查询所有学生的选课情况；select  distinct s.sno,s.sname,c.cname from student sleft join scon s.sno = sc.snoleft join course con sc.cno = c.cnoorder by s.sno;--32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；select s.sname,c.cname,sc.score from student sinner join sc on s.sno =sc.snoinner join course con c.cno = sc.cnowhere 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 sinner join scon s.sno = sc.snowhere 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 s2inner join sc s1on s1.sno = s2.snoinner join student son s.sno = s1.snowhere 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) awhere 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) son s.cno = c.cnowhere s.cc = (select count(sno) from student);--43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名select  distinct s.sno,s.sname from student sleft join sc on s.sno = sc.snoleft join (select cno from course c where c.tno in (select tno from teacher where tname <>'谌燕')) s1on 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;

转载地址：http://rzwki.baihongyu.com/