约束+典型的查询+几种常见的联结

[size=large]1.约束[/size]
alter table student
add constraint pk_stu_stuId primary key(stuId)


alter table student
add constraint ck_stu_stuAge check(stuAge>0)

alter table student
add constraint uq_stu_stuName unique(stuName)


alter table student
add constraint fk_stu_stuCid foreign key(stuCid) references class(cid)

[size=large]2查询简述[/size]
除了cross join 不可以加on外，其他的join连接必须加上on关键字。

[size=large]--说明：student 有m条记录，class有n条记录数，两表有t条记录的id匹配.注意：比如Student表有一条记录的id为4，在class表中有10条id为4的记录，则至少有10条记录匹配。所以，即使是内连接，得到的记录也可能大于两表记录之和[/size]
(1)--内连接：join，inner join
--内连接：只返回两表连接列的匹配项
--以下三种查询结果一样。
--显示t条记录
select * from student s inner join class c on s.classid=c.id;
select * from student s join class c on s.classid=c.id;
select * from student s ,class c where s.classid=c.id;

(2)笛卡尔乘积：cross join
笛卡尔乘积的连接：不加任何条件时，得到M*N的结果集（注：如果在cross join 后面加where条件，就会与inner join on的结果一样了）
--以下两种查询结果一样
--显示m*n条记录

select * from student s cross join class c;
select * from student ,class;


(3)左外连接：left join
左外连接：列出左边的表的全部，以及右边表符合条件的，不符合条件的以空值代替。
注：下面两个结果一样。哪个带加号，即哪个需要条件符合，下面的是左连接。
--显示m条记录

select * from student s left join class c on s.classid=c.id;
select * from student s,class c where s.classid=c.id(+);

（4）右外连接
--显示n条记录
select * from student s right join class c on s.classid=c.id;

（5）全外连接：full join（完全外连接） （在右边表最多只有一个符合条件时，记录数为：max in (M ,N)条记录到M+N条记录之间）
产生M+N的结果集并列出两表的全部的，不符合条件的，以空值代替
--显示m+n-t条记录
select * from student s full join class c on s.classid=c.id
.


[size=large]3.查询（19个经典查询）[/size]

select * from (
select emp.*,rownum as rn from emp where rownum<=10 order by sal desc
)b where b.rn>=5

--2. 查询各部门工资最高的员工信息
select * from emp;

select * from dept;

select *　from emp where (deptno,sal) in (
select deptno, max(sal)　 from emp group by deptno
)

--3查询每个部门工资最高的前2 名员工
--此题十分经典 ！！！！！！！！！！！！！！！！！！！！！！！！！！
select * from emp a where (select count(*) from emp where sal>a.sal and deptno=a.deptno ) <2
order by a.deptno


--4. 查询出有3 个以上下属的员工信息
select * from emp where empno in(
select b.empno from emp a inner join emp b on a.mgr=b.empno group by b.empno having count(*)>=3
)

--5. 查询所有大于本部门平均工资的员工信息()
select * from emp a where sal > (select avg(sal) from emp where deptno=a.deptno)


--6. 查询平均工资最高的部门信息
select * from dept where deptno in(
select deptno from emp group by deptno having avg(sal)>=all (
select avg(sal) from emp group by deptno
)
)

--7. 查询大于各部门总工资的平均值的部门信息
select * from dept where deptno in (
select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp )
)

--8. 查询大于各部门总工资的平均值的部门下的员工信息
select * from emp where sal >( select avg(sal) from emp)

--9、查询出工资成本最高的部门的部门号和部门名称
select deptno,dname from dept where deptno in (
select deptno from emp group by deptno having sum(sal)>=all(
select sum(sal) from emp group by deptno
)
)


--10、查询出工资不超过2500的人数最多的部门名称
SELECT dname FROM DEPT WHERE DEPTNO IN (
select deptno from emp where sal<=2500 group by deptno HAVING COUNT(*)>=ALL (
select count(*) from emp where sal<=2500 group by deptno
)
)


--11、查询出没有下属员工的人的名字和他的职位
select ename,job from emp where empno not in (
select distinct a.mgr from emp a,emp b where a.mgr is not null
)

--12、查询出人数最多的那个部门的部门编号和部门名称
select deptno,dname from dept where deptno in (
select deptno from emp group by deptno having count(*) >=all (
select count(*) from emp group by deptno
)
)


--13、查询出没有员工的那个部门的部门编号和部门名称(要求用两种方法,其中一种要用集合运算)
--方法一
select deptno,dname　 from dept where deptno not in (
select distinct deptno from emp
)　
--方法二

select deptno,dname　 from dept where deptno in (
select distinct deptno from dept --所有的部门号
minus
select distinct deptno from emp --存在的
)


--14、查询出员工名字以A打头的人数最多的部门名称和员工名字


select b.dname,a.ename from emp a inner join dept b on a.ename like 'A%' and a.deptno =b.deptno and a.deptno in (
select deptno from emp where ename like 'A%' group by deptno having count(*) >=all(
select count(*) from emp where ename like 'A%' group by deptno
)
)


--15、现在公司要给员工增加工龄工资,规则是:30*工作年限，请按以下格式显示下面结果:
部门名称 员工姓名 原工资 增加额度 新工资
select b.dname 部门名称,a.ename 员工姓名,a.sal 原工资,
floor( months_between(sysdate,hiredate)/12)*30 增加额度,
floor( months_between(sysdate,hiredate)/12)*30+a.sal 新工资 from emp a
inner join dept b on a.deptno =b.deptno;

--16、针对DEPT和EMP表，查询出下面格式的结果并要求按部门编号和工资降序排列。
部门名称 员工姓名 工资
select b.dname,a.ename,a.sal from
emp a left join dept b on a.deptno=b.deptno order by a.deptno desc,a.sal desc;


--17、针对DEPT和EMP表，查询出下面格式的结果。
部门编号 部门名称 部门工资最小值 部门工资最大值 部门工资平均值 部门工资合计值
select b.deptno,b.dname,min(sal),max(sal),avg(sal),sum(sal)
from emp a inner join dept b on a.deptno=b.deptno group by b.deptno,b.dname

--18、针对DEPT和EMP表，查询出SMITH所在部门的部门名称、部门工资平均值。（要求使用子查询）
select a.dname,b.avgsal from dept a inner join (
select deptno,avg(sal) avgsal from emp where deptno in(
select deptno from emp where ename='SMITH'
)group by deptno
)b on a.deptno =b.deptno


--19、针对DEPT和EMP表，查询出下面格式的结果。（要求使用外连接，没有员工的部门名也要显示。 员工姓名如果是空值，要求用“不存在”代替；如果工资是空值，要求用0代替。）
部门名称 员工姓名 工资

select a.dname, NVL( b.ename,'不存在') ,NVL(b.sal,0) from dept a full join emp b on a.deptno = b.deptno