复杂查询，外链接，子查询，分页查询

别名

select*from emp

select*from dept

--显示部员名，工资及所在部门名称，部门号
--如果两张表都带有相同字段，则需要带表名（别名）
select ename,sal,loc,emp.deptno from emp,dept where emp.deptno=dept.deptno

select ename,sal,loc,e.deptno from emp e,dept d where e.deptno=d.deptno--使用别名

--显示部门号为10的部门名、员工名和工资
select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=10 and e.deptno=d.deptno

--显示员工名，工资，部门，并按照部门排序

临时表

--删除一张表的重复记录

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

)
insert into cat values(2,'aa')

select*from cat
select*from #temp

--1.把cat表的记录distinct后的结果放入#temp
select distinct*into #temp from cat
--2.把cat表清空
delete from cat
--3.把#temp表的数据插入到cat
insert into cat select *from #temp
--4.删除#temp
drop table #temp

外链接

--显示每个员工的名字和他上级的名字
 --分析，把emp表看作两张表，分别是worker boss
 --外链接（左外连接  右链接）

 select worker.ename 员工,boss.ename 老板 from emp worker,emp boss where worker.mgr=boss.empno

--左外连接和右外连接
--显示公司每个员工和它的上级的名字
--内连接
select w.ename,b.ename from emp w,emp b where w.mgr=b.empno

--显示公司每个员工和它的上级的名字（没有上级的人名字也要显示）
--左外连接(指的是左边的表的记录都要出现，如果没有匹配记录就用NULL填)
--右 外连接(指的是右边的表的记录都要出现，如果没有匹配记录就用NULL填)
select w.ename,b.ename from emp w left join emp b on w.mgr=b.empno

子查询

 --子查询

 --显示与smith同一部门的所有员工（单行子查询）
 select*from emp where deptno=
 (select deptno from emp where ename='smith')

 --查询和部门10的工作相同的员工名字、岗位、工资、部门号（多行子查询）
 --如何排除10本身 
 select*from emp where job in
 (select distinct job from emp where deptno=10)

 --显示高于部门平均工资的员工名字，薪水和他部门的平均工资
 --1.首先要知道各部门的平均工资

 select avg(sal),deptno from emp group by deptno

 --2.把上面的查询结果，当作一个临时表

 select ename,sal,myavg,emp.deptno from emp,
 (select avg(sal) myavg,deptno from emp group by deptno)tem
 where emp.deptno=tem.deptno and sal>myavg

分页查询

 --分页查询
 select*from emp
 --显示第五个到第十个入职的（按时间先后顺序）

 --1.显示第一个到第四个入职的员工
 select top 4*from emp order by hiredate
 --

 select top 5*from emp where empno not in 
 (select top 4*from emp order by hiredate)
 order by hiredate;


 --测试
 create table test(
 testId int primary key identity(1,1),
 testName varchar(30),
 testPass varchar(30)
 
 )

 insert into test(testName,testPass)values('shun','shun')

 insert into test(testName,testPass) select testName,testpass from test

 select*from test

 select count(*)from test

  select testId from test

--test表按照ID排序100-105
select top 6*from test where testId not in
(select top 999 testId from test)