多行子查询：返回多行数据的子查询。

39,查询和部门号为10的工作相同的雇员的名字、岗位、工资、部门号。(注意要使用in，不能用=)

(1)select distinct job from emp where deptno=20

(2)select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20);

40，在上面查询结果的基础上排除20部门的员工

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

deptno not in (20);

或select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20) and deptno <>20;

在from字句中使用子查询

41 ,显示各个部门高于本部门平均工资的员工的信息

(1)先查出各个部门的平均工资 www.2cto.com

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

(2)把上面的表当成一个临时表来对待

select e.ename,e.sal,tem.myavg,e.deptno

from emp e,(select avg(sal) myavg,deptno from emp group by deptno) tem

where e.deptno=tem.deptno and e.sal>tem.myavg

注意：（1）当在from字句中使用子查询时，该子查询会被当做一个临时表来对待，

（2）当在from字句中使用子查询时，必须给子查询指定一个别名

分页查询：按雇员的hiredate属性升序取出第5条到第10条记录

42，显示第5条到第10条记录

（1）显示第1到第4条记录

select top 4 empno from emp order by hiredate

（2）显示后6条记录（第5条到第10条记录）

select top 6 * from emp where empno

not in(select top 4 empno from emp order by hiredate)

order by hiredate;

43,显示第11个到第13个入职的人的信息（写法同上）

44，显示第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;

45，同一张表中的数据复制

（1）创建一张临时表：identity(1,1)表示该testId字段自增，从1开始每次+1

create table test(

testId int primary key identity(1,1),

testName varchar(30),

testPass varchar(30)

)

（2）插入一条数据

insert into test(testName, testPass) values('zhangsan','123456');

（3）复制数据

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

46，查询testId为第10000-10009的数据，看看性能。

select top 10 * from test where testId not in

(select top 9999 testId from test order by testId)

order by testId

用查询结果创建一张新表（一种快捷的建表方法）

47，语法：select *(这里可以选择字段) into 另一张表面 from 表

select testName,testPass into mytest from test where testId<8

表mytest在上述语句中已经创建好了，并且初始化好了数据

并且把testId设置为主键：ALTER TABLE test01 ADD primary key(testId)

www.2cto.com

48，删除一张表中的重复数据

（1）create table cat(

catId int,

catName varchar(40)

)

（2）insert into cat values(1,'aa'); //重复执行几次

insert into cat values(2,'bb'); //重复执行几次

（3）select distinct * into #temp from cat;//把cat的记录distinct后的结果，插入到临时表#temp中

delete from cat;//把cat表的记录清空

insert into cat select * from #temp;//把#temp表的数据（没有重复的数据）插入到cat表中

drop table #temp;//删除表#temp3

左外连接和右外连接

左外连接：左边表的查询数据全部显示，右边的表中如果没有匹配的数据则用null填充

右外连接：右边表的查询数据全部显示，左边的表中如果没有匹配的数据则用null填充

49，显示emp表中所有雇员的及其上级的名字(看看区别)

（1）左外连接：select e.ename 雇员名字,b.ename 上级名字

from emp e left join emp b on e.mgr=b.empno;

（2）右外连接：select e.ename 雇员名字,b.ename 上级名字

from emp e right join emp b on e.mgr=b.empno;

常见约束：

(1)not null, 非空

(2)unique, 唯一，允许出现一个null

(3)primary key, 主键，唯一，非空

(4)foreign key, 外键,定义主表和从表的关联关系