sql 复杂查询 多表查询 分页查询

 

select * from emp;
select * from dept;

--多表查询
--显示雇员名，雇员薪水，及所在部门的名字，部门号

select e.ename,e.sal ,d.dname,e.deptno from emp e,dept d

这样将出现迪卡尔积

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

--自连接
--显示某个员工的上级领导的姓名 ，比如zhangsan
--原来的方法

select ename from emp where empno=(select mgr from emp where ename='zhangsan');

--使用自连接的方法

select e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename='zhangsan';

--显示公司每个员工的名字，编号和他的上司的名字，编号

select e1.ename,e1.empno,e2.ename,e2.empno from emp e1,emp e2 where e1.mgr=e2.empno;

-----子查询（重要）
---单行子查询
--显示与lisi同部门的员工的信息

select * from emp where deptno=(select deptno from emp where ename='lisi');

---多行子查询
--查询和部门10的工作相同的雇员的名字
--第一步，我们先来看一下10部门都有哪些工作
select distinct job from emp where deptno=10;
--第二步
select * from emp where job in(select job from emp where deptno=10);

--在from 子句中使用子查询(重要）
--高于部门平均工资的员工的信息（姓名，部门，所在部门平均工资，薪水）
--第一步：先看一下各部门平均工资
select deptno,avg(sal) from emp group by deptno;
--第二步：把上边的结果当成一个临时表来对待。
select e.ename,e.deptno,e.sal,t1.myavg
from (select avg(sal) myavg,deptno from emp group by deptno) t1,emp e
where t1.deptno=e.deptno and e.sal>t1.myavg

----------------分页查询（非常重点）
-----top后面的数表示要取出几条记录。
--显示第5-10个入职的职员。（按入职时间顺序）
--显示全部按入职时间顺序

select * from emp order by hiredate;

--第一步：显示第1-4个入职的职员

select top 4 * from emp order by hiredate

--第二步：

select top 6 * from emp where empno not in (select top 4 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,empno) order by sal,empno;

--全部员工的薪水排序

select * from emp order by sal,empno;

--为什么要加empno,因为排序后，第四个和第五个的薪水相同，所以上面两行语句如果不加empno则可能结果一致。

-----identity(1,1) 表示该字段自增，从1开始，每次加1.
--作一个小测试，哈哈

create table test(
testId int primary key identity(1,1),
testName varchar(20),
testDate datetime
)
insert into test(testName,testDate) values('zhangxin',getdate());

select * from test;

--反复插入，非常疯狂的插入

insert into test(testName,testDate) select testName,testDate from test;
select count(*) from test;

drop table test;

 

--------------如何删除重复的行数，（当然保留一行）
----用查询结果创建新表
----第一种方案

create table testRepeat(
testId int,
testName varchar(20)
)
insert into testRepeat values(1,'lulu');
insert into testRepeat values(2,'wang');

select * from testRepeat;

drop table testRepeat;

select distinct * into #temp from testRepeat;
drop table testRepeat;
select * into testRepeat from #temp;
drop table #temp;

---第二种方案

create table testRepeat(
testId int,
testName varchar(20)
)
insert into testRepeat values(1,'lulu');
insert into testRepeat values(2,'wang');

select * from testRepeat;

drop table testRepeat;

select distinct * into #temp from testRepeat;
delete from  testRepeat;
insert into testRepeat select * from #temp;
drop table #temp;

-----------左外连接和右外连接
--显示公司每个员工和他的上司的名字
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
--上面的显示结果没有显示没有上级的人，那怎么显示呢？
---左连接 from 表名１　left join 表名２　on 条件。
select e1.ename,e2.ename  from emp e1 left join emp e2 on e1.mgr=e2.empno;