SQL 复杂查询,单行子查询，多行子查询，分页查询，删除一张表中的重复记录，左外连接和右外连接

--复杂查询--多表查询
--显示雇员的名字，雇员的工资和所在部门的名称
--如果两张表都有相同名字的字段，则需要带表明
select  ename,sal,loc,emp.deptno,dept.deptno from emp ,dept  where dname='sales' and dept.deptno=emp.deptno

--显示部门号位10的部门号、员工名和工资

select dept.deptno 部门号,emp.ename 员工名,emp.sal 工资 from emp,dept where dept.deptno=10 and dept.deptno=emp.deptno


--显示雇员名，雇员工资及所在部门的名字，并按部门排序

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno order by dname


--显示雇员上级领导的姓名，比如显示‘FORD’的上级

select mgr from emp where ename='FORD'  

select 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


--单行子查询
--请思考：如何返回与smith同一部门的所有员工

select * from emp where deptno=
(select deptno from emp where ename='smith')


--多行子查询
--如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门号
select *from emp e where job in
(select distinct job from emp e where deptno=10)

--如何显示高于部门平均工资的员工的姓名，薪水，和他所在部门的平均工资


select *from emp

select *from dept
--1、首先要知道各个部门的平均工资
select avg(sal) ,emp.deptno  from emp group by emp.deptno
--2、把上面的查询结果，当做一个临时表来对待,当在from子句中使用子查询时，
--必须给子查询制定别名
select emp.ename,emp.sal,temp.myavg,emp.deptno from 
emp,(select avg(sal) myavg ,emp.deptno  from emp group by emp.deptno) temp
where emp.deptno=temp.deptno and emp.sal>temp.myavg


select *from emp
--请显示第5个到第10个入职的雇员（按照时间的先后顺序）
--1、显示第1个到第4个入职的雇员
select top 4 *from emp order by emp.hiredate


--top 后面的数表示要取出几条记录
select top 6 *from emp where emp.empno not in
(select top 4 emp.empno from emp order by emp.hiredate)
order by emp.hiredate



--分页查询
--请显示第十一个到十三个入职雇员的信息
select top 3 *from emp where emp.empno not in
(select top 10 emp.empno from emp order by emp.hiredate)
order by emp.hiredate



--请显示第5个到9个入职雇员的信息（安薪水的高低）


select top 4 *from emp order by emp.sal

select top 5 *from emp where emp.empno not in
(select top 4  emp.empno from emp order by emp.sal desc)
order by emp.sal desc











--identity(1,1)，表示testId字段自增长，从一开始，每次加1

create table test(

testId int primary key identity(1,1),
testName varchar(30),
testPass varchar(30))

insert into test(testName,testPass) values('海涛','海涛')
--用查询结果创建新表
--这个命令式一种快捷建表的方法
--select *(这里可以选择字段) into 另一表名 from 表
insert into test(testName,testPass) select testName,testPass from test

select *from test
select count (*) from test


 --test表按照id排序

 select top 6 *from test where testId not in
 (select top 99 testId from test )









 --如何删除掉一张表的重复记录

 create table cat(
 catId int ,
 catName varchar(30))

 insert into cat values(2,'bb')

select *from cat



--把cat的记录distinct后的结果放入到#temp4临时表中
select distinct * into #temp4 from cat
--把cat表的记录清空
delete  from  cat
--把#temp4临时表（无重复记录）中的记录再放入到cat表
insert into cat select * from #temp4
--删除临时表
drop table #temp4



--讲左外连接和右外连接


--思考:显示公司每个员工和它上级的名字,要求没有上级的人,名字也要显示
--左外连:指左边的表的记录全部显示,如果没有匹配的记录就用null填
--右外连:指右边的表的记录全部显示,如果没有匹配的记录就用null填
select w.ename,b.ename from emp w left  join emp b on w.mgr=b.empno