【Oracle】学习笔记（五）高级查询

用例

drop table stuInfo;
drop table course;
drop table score;

drop sequence seq_stuid;
drop sequence seq_cid;
drop sequence seq_scid;

create table stuinfo(		--学生
	stuid int primary key,
	sname varchar2(50) unique,
	age int not null 
		constraint CK_sage check(age>=18 and age<=30),
	addr varchar2(50),
	sex char(2) default 'M'
		constraint CK_sexs check(sex in('M','F'))
);

create sequence seq_stuid start with 10001 increment by 1;

create table course(		--科目
	cid int primary key,
	cname varchar2(50)      
); 

create sequence seq_cid start with 10001 increment by 1;
    

create table score(		--成绩
	scid int primary key,
	stuid int
		constraint FK_stuid references stuinfo(stuid),
	courseid int
		constraint FK_courseid references course(cid),
	score int
);

create sequence seq_scid start with 10001 increment by 1;
   
insert into course values(seq_cid.nextval,'J2EE精讲');
insert into course values(seq_cid.nextval,'Oracle');
insert into course values(seq_cid.nextval,'html网页设计');
insert into course values(seq_cid.nextval,'Java基础');
   
insert into stuinfo values(seq_stuid.nextval,'张果老',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'李豹',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'老胡',22,'北京','M');
insert into stuinfo values(seq_stuid.nextval,'老江',24,'湖南','M');
insert into stuinfo values(seq_stuid.nextval,'张无忌',26,'衡阳','M');
insert into stuinfo values(seq_stuid.nextval,'二师兄',28,'长沙','M');
insert into stuinfo values(seq_stuid.nextval,'凤姐',24,'上海','F');
insert into stuinfo values(seq_stuid.nextval,'元芳',28,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'离歌',23,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'八戒',24,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'赵柳',22,'北京','F');

insert into score values(seq_scid.nextval,10005,10001,95);
insert into score values(seq_scid.nextval,10005,10002,58);
insert into score values(seq_scid.nextval,10005,10003,56);
insert into score values(seq_scid.nextval,10005,10004,81);
insert into score values(seq_scid.nextval,10005,10002,80);

insert into score values(seq_scid.nextval,10001,10001,78);
insert into score values(seq_scid.nextval,10001,10003,81);
insert into score values(seq_scid.nextval,10001,10004,92);


insert into score values(seq_scid.nextval,10002,10003,30);
insert into score values(seq_scid.nextval,10002,10003,78);
insert into score values(seq_scid.nextval,10003,10003,66);
insert into score values(seq_scid.nextval,10004,10003,66);
insert into score values(seq_scid.nextval,10006,10003,78);
insert into score values(seq_scid.nextval,10007,10003,76);
insert into score values(seq_scid.nextval,10008,10002,81);
insert into score values(seq_scid.nextval,10009,10002,90);
insert into score values(seq_scid.nextval,10010,10003,42);

insert into score values(seq_scid.nextval,10002,10001,80);
insert into score values(seq_scid.nextval,10004,10002,78);
insert into score values(seq_scid.nextval,10008,10003,69);
insert into score values(seq_scid.nextval,10008,10001,69);


select * from stuinfo;
select * from course;
select * from score;

操作

--聚合函数 多行函数
--求员工的最高底薪，最低底薪，平均底薪和底薪总和
select max(sal),min(sal),avg(sal),sum(sal) from emp;

--按部门统计，每个部门的最高底薪，最低底薪，平均底薪和底薪总和
select de.dname,ep.ename from emp ep,dept de where de.deptno = ep.deptno group by de.dname,ep.ename;

select deptno,max(sal),min(sal),avg(sal),sum(sal) from emp group by deptno;

--all 查询某列所有的值
--查询所有学生的地址
select all addr from student;

--distinct 过滤重复数据
--统计学生来自哪个地区
selelct distinct addr from student

--count 统计总共有多少个学生
select distinct count(*) from student

--统计学生来自多少个地区
select count(distinct addr) from student

--其他函数
--为空赋值函数
--查询学生姓名和地址，如果地址为空则显示地址不详
select sname,nvl(addr,'地址不详') 地址 from student

--取值条件
--查询学生姓名和性别，如果是男则显示m，如果是女，则显示f
select sname,case sex when '男' then 'M' when '女' then 'F' end '性别' from student;
select sname,decode(sex,'男','M','F') 性别 from student;

--显示部门信息，如果是ACCOUNTING则显示会计部，如果是RESEARCH则显示研发部，如果是SALES则显示销售部，如果OPERATIONS则显示市场部，否则显示行政部
select deptno,deptno(dname,'ACCOUNTING','会计部','RESEARCH','研发部') from dept;

--分析函数
--排序
--语法： order by 列名1 [desc/asc],列名 [desc/asc]
--问题

--按学号名排序
select * from student order by sid;

--按学号降序排序
select * from student order by sid desc;
--升序则asc

--row_number() 排序：如果排序字段的值相等，序号也不中断 1,2,3,4,5,6,7
select empno,ename,sal,job,row_number() over(order by sal desc) from emp

--RANK 具有相等值的行排位相同，序数随后跳跃 1、2、2、4、5、5、7
select empno,ename,sal,job, RANK() over(order by sal desc) from emp;


--DENSE_RANK 具有相等值的行排位相同，序号是连续的 1、2、2、3、4、5、5、6、7
select empno,ename,sal,job,DENSE_RANK() over(order by sal desc) from emp;

--分页查询
/*rownum:
	1.ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。
	使用SELECT语句返回的结果集，若希望按特定条件查询前N条记录，可以使用伪列ROWNUM。
	ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。
	
	2.如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。
	因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
	select empno,ename from emp where rownum>2                    */
  select * from student where sid=1001;
  select * from student where rownum=2;
	  
	/*3.那如何才能找到第二行以后的记录呢？可以使用子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，
	这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。 
	select * from (select rownum no,empno,ename from emp) where no>2;  */
  select * from (select rownum no,sname from student) where no>2

	/*
  4.如果想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n（n>1的自然数）的条件认为是成立的，
	所以可以找到记录。  select rownum,empno,ename from emp where rownum <3;     */
  select * from student where rownum>3;
  
  /*
	5.综上几种情况，可能有时候需要查询rownum在某区间的数据，可以看出rownum对小于某值的查询条件是为true的，
	rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。
	那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们可以写以下语句，
	先让它返回小于等于3的记录行，然后在主查询中判断新的rownum的别名列大于等于2的记录行。但是这样的操作会在大数据集中影响速度。
	select * from (select rownum no,empno,ename from emp where rownum<=3 ) where no >=2;  */
  select * from emp; --ROWNUM是一个序列
	  
--进行第一次筛选   <最大值 10
   select a.*, rownum from (select * from emp) a where rownum<=10;
   
--注意a表示查询结果的别名
	  
--进行第二次筛选
   select * from(select a.*,rownum rn from(select * from emp) a where rownum<=10) where rn>=5;

--查出所有姓'张'的学员信息
select * from stuInfo where sname like '张%';

--查出所有addr字段为'NULL'值的学员的信息
select * from stuInfo where addr is null;

--查出成绩在60到70分之间的学员的id号
select stuid from score where score between 60 and 70;

--查出地址是北京和上海的学生的信息
select * from stuInfo where addr in('北京','上海');
select * from stuInfo where addr='北京' or addr='上海';

--查出成绩在60到70分之间的学员的姓名和分数
select sname,score from stuInfo sf,score sc where sf.stuid=sc.stuid and score between 60 and 70;


/*
种类:
1.内联结(inner join): 仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行. 内联接消除与另一个表中任何不匹配的行.
2.外联结：
  外联接会返回from子句中提到的至少一个表的所有行，只要这些行符合任何where或having条件。
  左外联接 left join:左边表中所有的行，右边表中没有的字段用null代替。
  右外联接 right join:右边表中所有的行, 左边表中没有的字段用null代替。
  完整外联结 full join:两表数据都返回，没有的地方用null代替。 
3.交叉联结(cross join)
*/

--内联结语法:select 列名  from 表名 inner join 表名2 on 联结条件
--案例: 显示课程名及成绩  
select cname,score from course cs inner join score sc on cs.cid=sc.courseid;



--左外联接语法: select 列名 from 表名1 left join 表名2 on 联结条件 
--特点: 优先查出表名1中所有的符合条件的数据，如果表名2中没有这个对应的数据，则用NULL填充值
--案例描述: 学生表中有一位学员1号'赵柳',没有参加过任何考试,下面请查出所有没有参加过任何考试的学员
select * from stuInfo sf left join score sc on sf.stuid=sc.stuid where score is null;

--右外联接与左外联接正好相反:
select * from stuInfo sf right join score sc on sf.stuid=sc.stuid where score is null;

--完整外联接
select * from stuInfo sf  join score sc on sf.stuid=sc.stuid;

--交叉联接：左表中的每一行与右表中的每行都组合成。笛卡尔乘积=左表数据行*右表数据行
select * from stuInfo cross join score;
select * from dept;
select * from emp;
select * from dept cross join emp;--56条


--right join:以right join关键字右边的表为基准表，也就是右边表中有的数据全部显示，
--左边表中没有对应的数据时，显示为空
select * from stuInfo sf right join score sc on sf.stuid=sc.stuid;
select * from stuInfo sf right join score sc on sf.stuid=sc.stuid;

insert into stuInfo values(seq_stuid.nextval,'周星驰'，22,null,'M');


--显示学生姓名，课程名及成绩 
select sname,cname,score from stuInfo sf inner join score sc on sf.stuid=sc.stuid inner join course cs on cs.cid=sc.courseid;


--查询所有学生的姓名、课程名和成绩，如果该生没有参加过任何考试，也需显示出来
select sname,cname,score from stuInfo sf full join score sc on sf.stuid=sc.stuid full join course cs on cs.cid=sc.courseid;


--查出学员在'Oracle'这门课程中的总成绩和平均成绩,最高分数，最低分数
--连接查询
select '连接查询'，sum(score),avg(score),max(score),min(score) from score sc,course cs where sc.courseid = cs.cid and cs.cname='Oracle';

--嵌套查询
select '连接查询'，sum(score),avg(score),max(score),min(score) from score sc where sc.courseid = (select cid from course cs.cname='Oracle');

--内联查询
select '连接查询'，sum(score),avg(score),max(score),min(score) from score sc inner join course cs on sc.courseid=cs.cid where cs.cname='Oracle';

--查询每门课程的平均成绩，显示课程名称，平均分
select cname,avg(score) from course cs,score sc where sc.courseid = cs.cid group cname;



--查出学员在'html网页设计'这门课程中不及格的学生人数
select count(*) 不及格人数 from course cs,score sc where sc.courseid=cs.cid and cs.cname='html网页设计' and sc.score<60;