Oracle多表操作 子查询

--union all把所有的表联合起来,不去除重复 数据库通用的
create or replace view v$student1(id ,name,age)
as
select 1,'tom01',18 from dual
union all
select 2,'tom02',20 from dual
union all
select 3,'tom03',30 from dual;

select * from v$student1;
--union把几张表联合起来,有相同的就覆盖掉
create or replace view v$student2(id ,name,age)
as
select 1,'tom01',18 from dual
union
select 2,'tom02',20 from dual
union
select 2,'tom02',20 from dual
union  
select 4,'tom04',40 from dual;

select * from v$student2;
--交集--oracle专用的
select * from v$student1
intersect
select * from v$student2;
--差集--oracle专用的
select * from v$student1
minus

select * from v$student2;

--学生
create or replace view vw_student(id,name)
as
       select 1,'tom'from dual union
       select 2,'tom2'from dual union
       select 3,'tom3'from dual ;
select* from vw_student;
--课程
create or replace view vw_course(id,name)
as
       select 1,'java'from dual union
       select 2,'C#' from dual union
       select 3,'sql' from dual;
select* from vw_course;
--成绩
create or replace view vw_score(studentID,courseID,grade)
as
       select 1,1,90 from dual union
       select 1,2,85 from dual union
       select 1,3,70 from dual union
       select 2,1,85 from dual union
       select 2,2,70 from dual;
select*from vw_score;

--多表查询--笛卡尔集+过滤
select
vw_student.*,
cor.*,
sc.*
from vw_student,vw_course cor,vw_score sc--前面到这里进行多表相乘
where sc.studentid=vw_student.id and sc.courseid=cor.id

select
st.id,
st.name,
cor.name,
sc.grade
from vw_student st,vw_course cor,vw_score sc
where sc.studentid=st.id and sc.courseid=cor.id
order by st.id;


select
      cor.name,
      sum(sc.grade),
      avg(sc.grade),
      max(sc.grade),
      min(sc.grade)
from vw_student st,vw_course cor,vw_score sc
where sc.studentid=st.id and sc.courseid=cor.id
group by cor.name--前面必须选才可以按这个分组
order by cor.name;--排序可以根据表中的任意排

------------------------内连接(是有条件的)
select
st.name,
cor.name,
sc.grade
from vw_student st
inner join vw_score sc on st.id=sc.studentid--vw_score表和vw_student st内连接
inner join vw_course cor on cor.id=sc.courseid--用前面内链接的表再和vw_course内连接
order by cor.name,sc.grade;
-------------------------外链接(左链接left outer join和右链接right outer join)
select                              
   st.id,
  st.name,
   nvl(sc.grade,0),--分数是空格就补0
    cor.name   
from  vw_student st
cross join  vw_course cor --cross join相当于逗号
--左外莲是左边的多打印出来,即使条件不满足
left outer join vw_score sc on st.id=sc.studentid and sc.courseid=cor.id
--按照课程名字升序排,名字相同,按照成绩排
order by cor.name,sc.grade

----------------------------子查询()
--1,select位置
select e.*,(select d.dname from dept d where d.deptno=e.deptno) dname from emp e;

select* from dept;
--2,where位置
select e.* from emp e

where e.deptno=(select deptno from dept where dname='SALES');
--3,from位置
select* from(select e.*,rownum num from emp e where rownum<=10) temp

where num>5

--练习
--1,查询公司工资最高的个人信息
select max(sal) from emp;--先找出工资最高的

select* from emp where sal=(select max(sal) from emp);--再根据最高工资查找个人信息
--2,查询比scott工资高的个人信息
select sal from emp where ename='SCOTT';--先找出scott的分数

select* from emp where sal>(select sal from emp where ename='SCOTT');--再根据分数查找信息
--3,查询部门工资最高的个人信息
select max(sal) from emp group by emp.deptno

select* from emp e where e.sal=

(select max(sal) from emp e2 where e2.deptno=e.deptno group by e2.deptno)