表的复杂查询---子查询

1.什么是子查询

   指嵌入在其他sql语句中的select语句，也叫做嵌套查询

2.单行子查询

  是指只返回一行数据的子查询语句

eg.如何显示和SMITH同一部门的所有员工

   select * from emp where depno=(select depno from emp where ename='SMITH')

3.多行子查询

  指返回多行数据的子查询

eg.如何查询和部门10的工作相同的雇员名称，岗位，工资，部门号

select ename,job,sal,deptno from emp where job in (select  distinct job from emp where deptno=10)

4.在from 子句中使用子查询

 当在from子句中使用子查询时，该子查询会被作为临时表对待，当在from中使用子查询时，必须给子查询指别名

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

---分析：1.首先要知道各个部门的平均工资

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

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

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

5.分页查询

eg. 请显示第5个到第10个入职的雇员（按照时间先后顺序）

     1.显示第1个到第4个入职的雇员

  select top 4 * from emp order by hiredate

    2.第5到第10

    select top 6 * from emp where empno not in (select top 4 empno from emp order by hiredate) orrder by hiredate

6.用查询结果创建新表

  select *(字段) into 另一个表名  from 表

7.如何删除掉一张表重复记录

  select distinct * into #temp from cat ,

 delete * from cat,

  inset into cat select * from #temp,

 drop #temp

8.左外连接和右外连接

 eg.显示公司每个员工和他的上级的名字

select w.ename ,b,ename from emp w,emp b where w.mgr=b.empno

 eg.显示公司每个员工和他的上级的名字，要求没有上级的人，名字也要显示

---左外连接:指左边的表的记录全部显示，如果没有匹配的记录就用null填

select w.ename ,b,ename from emp w left  join emp b on w.mgr =b.empno 

---右外连接:指右边的表的记录全部显示，如果没有匹配的记录就用null填

弄个例题，直观一点。两个表：
--表stu
id name 
1, Jack
2, Tom
3, Kity
4, nono
--表exam
id grade
1, 56
2, 76
11, 89

内连接 （显示两表id匹配的）显示所有学生的成绩
select stu.id,exam.id,stu.name, exam.grade from stu inner join exam on stu.id=exam.id
stu.id exam.id name grade
--------------------------------
1 1 Jack 56
2 2 Tom 76

左连接（显示join 左边的表的所有数据，exam只有两条记录，所以stu.id,grade 都用NULL 显示）
select stu.id,exam.id,stu.name, exam.grade from stu left join exam on stu.id=exam.id
1 1 Jack 56
2 2 Tom 76
3 NULL Kity NULL
4 NULL nono NULL

右连接（与作连接相反，显示join右边表的所有数据）
select stu.id,exam.id,stu.name, exam.grade from stu right join exam on stu.id=exam.id
1 1 Jack 56
2 2 Tom 76
NULL 11 NULL 89