约束+典型的查询+几种常见的联合
约束+典型的查询+几种常见的联结
1.约束
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)
2查询简述
除了cross join 不可以加on外，其他的join连接必须加上on关键字。
(1)--内连接：join，inner join
--内连接：只返回两表连接列的匹配项
--以下三种查询结果一样。
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的结果一样了）
--以下两种查询结果一样
select * from student s cross join class c;
select * from student ,class;
(3)左外连接：left join
左外连接：列出左边的表的全部，以及右边表符合条件的，不符合条件的以空值代替。
注：下面两个结果一样。哪个带加号，即哪个需要条件符合，下面的是左连接。
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）右外连接
（5）全外连接：full join（完全外连接） （在右边表最多只有一个符合条件时，记录数为：max in (M ,N)条记录到M+N条记录之间）
产生M+N的结果集并列出两表的全部的，不符合条件的，以空值代替
select * from student s full join class c on s.classid=c.id
.
3.查询（19个经典查询）
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
1.约束
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)
2查询简述
除了cross join 不可以加on外，其他的join连接必须加上on关键字。
(1)--内连接：join，inner join
--内连接：只返回两表连接列的匹配项
--以下三种查询结果一样。
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的结果一样了）
--以下两种查询结果一样
select * from student s cross join class c;
select * from student ,class;
(3)左外连接：left join
左外连接：列出左边的表的全部，以及右边表符合条件的，不符合条件的以空值代替。
注：下面两个结果一样。哪个带加号，即哪个需要条件符合，下面的是左连接。
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）右外连接
（5）全外连接：full join（完全外连接） （在右边表最多只有一个符合条件时，记录数为：max in (M ,N)条记录到M+N条记录之间）
产生M+N的结果集并列出两表的全部的，不符合条件的，以空值代替
select * from student s full join class c on s.classid=c.id
.
3.查询（19个经典查询）
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