表的复杂查询--多表查询
说明:
多表查询是指基于两个或两个以上的表或是视图的查询,在实际应用中,查询单个表可能不能满足你的需求,(如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)
--多表查询
--如果多张表都有相同名字的字段,则需要带表名(别名)
--显示sales部门位置和其员工的姓名
select emp.ename"员工姓名",dept.dname"部门名称",dept.loc"部门所在地" from emp,dept where dept.dname='sales' and emp.deptno=dept.deptno
--显示雇员名,雇员工资及所在部门的名字
select emp.ename"雇员名",emp.sal"雇员工资",dept.dname"部门名称" from emp,dept where emp.deptno=dept.deptno
--如何显示部门号为10的部门名、员工名和工资
select emp.deptno"部门号",dept.dname"部门名称",emp.ename"员工名称",emp.sal"工资" from emp,dept where emp.deptno=dept.deptno and emp.deptno=10
--显示雇员名、雇员工资及所在部门的名字并按部门排序
select e.ename"雇员名字",e.sal"工资",d.dname"部门名称" from emp e,dept d where e.deptno=d.deptno order by d.dname
表的复杂查询--多表查询
自连接
自连接是指在同一张表的连接查询
例:
--自连接
--显示某个员工的上级领导的姓名,比如显示"ford"的上级
select (select ename from emp where ename='ford')"员工姓名",ename"上级领导" from emp where empno=(select mgr from emp where ename='ford')
--显示公司每个员工姓名和他的上级的名字
--分析,把emp表看成两张表分别是worker/boss
--外连接(左外连接、右外连接)
select worker.ename"员工名字",boss.ename"领导名字" from emp worker,emp boss where worker.mgr=boss.empno
表的复杂查询--子查询
什么是子查询
子查询是批嵌入在其它sql语句中的select语句,也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
--如何显示与smith同一部门的所有员工?
select deptno"部门号",ename"员工名字" from emp where deptno=(select deptno from emp where ename='smith')
多行子查询
多行子查询指返回多行数据的子查询
--如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select * from emp where job in(select distinct job from emp where deptno=10)
在from子句中使用子查询
--如何显示高于部门平均工资的员工名字、薪水、部门的平均工资
--分析:1、首先要知道各个部门的平均工资
select avg(sal)"部门平均工资",deptno from emp group by deptno
--2、把上面的查询结果当作一个临时表对待
select e.ename"员工名字",e.sal"薪水",temp.myavg"部门平均工资",e.deptno from emp e,(select avg(sal) myavg,deptno from emp group by deptno) temp where e.deptno=temp.deptno and e.sal>temp.myavg
在from子句中使用子查询
这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个临时表来对待,当在from子句中使用子查询时,必需给子查询指定别名
分面查询
按雇员的id号升序取出
--请显示第5个到第10个入职的雇员信息(按照入职的时间先后顺序查找)
--分析:1、显示第1个到第4个入职的雇员
select top 4 * from emp order by hiredate
--top后的数表示要取出几条记录
select top 6 * from emp where empno not in(select top 4 empno from emp order by hiredate) order by hiredate
--请显示第11个到13个入职的雇员信息
select top 3 * from emp where empno not in(select top 10 empno from emp order by hiredate) order by hiredate
--请显示第5个到9个入职的雇员信息(按薪水高低排序)
select top 5 * from emp where empno not in(select top 4 empno from emp order by sal desc) order by sal desc
用查询结果创建新表
这个命令是一种快捷的建表方法
select *(这里可以选择字段) into 另一个表名 from 表
--如何删除掉一张表重复记录
create table cat(
catId int,
catName varchar(40)
)
insert into cat values(1,'aa')
select * from cat
--1、把cat表的记录distinct后的结果,放到临时表中
select distinct * into #temp from cat
--2、把cat表的记录清空
delete from cat
--3、把临时表中的数据信息加入到cat表中
insert into cat select * from #temp
--4、删除临时表
drop table #temp