SQL Server 2005 复杂查询实例

SQL Server 2005 复杂查询 --多表查询
SQL Server 2005 复杂查询 --多表查询
子查询 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套 查询 单行子查询 单行子查询是指只返回一行数据的子查询语句 1.如何显示与smith同一部门的所有员工 select * from emp where deptno = (select deptno from emp where ename = 'smith')
SQL Server 2005 复杂查询 --多表查询
多行子查询 多行子查询是指只返回多行数据的子查询语句 1.如何查询和部门10的工作相同的员工的名字、岗位、工资、 部门号 select * from emp where job = (select job from emp where deptno = 10) select * from emp where job in (select job from emp where deptno = 10)
简单查询
1.查询所有列 select * from 表名 （where 条件） 2.查询指定列 select 字段 from 表名 where 条件 注意：SQL Server不区分字段值大小写 3.如何取消重复行 select distinct 字段 from 表名 where 条件 注意：各个字段值必须完全一样，才算相同的行， distinct只保留一行查出共有多少个部门编号？
SQL Server 2005 复杂查询 --单表查询
数据分组小结： 1.分组函数只能出现在选择列表、having、order by 子句中 2.如果在select语句中同时包含有group by 、having、order by, 那么它们的顺序是group by 、having、order by 3.在选择列中，如果有列、表达式、分组函数，那么这些列 和表达式必须有一个出现在group by子句中，否则就会出错 例如： select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000 这里deptno就一定要出现在group by 中
数据分组 一、max , min , avg , sum , count 1.如何显示所有员工中最高工资和最低工资 select min(sal) from emp select max(sal) from emp
SQL Server 2005 复杂查询 --单表查询
2.如何显示最低工资和员工的姓名 select ename , sal from emp where sal = (select min(sal) from emp)
简单查询
• 4.使用算数表达式 • 显示每个员工的年工资 select ename , sal*12 from emp; • 显示年工资加上奖金 select ename , sal*12+comm from emp; • 如何处理空值的问题 select ename, sal*12 + isnull(comm,0) from emp;
SQL Server 2005 复杂查询 --单表查询
SQL Server 2005 复杂查询 --单表查询
3.显示平均工资低于2000的部门号和它的平均工资 select deptno , avg(sal) '平均工资‘ from emp group by deptno having avg(sal) < 2000 4.显示平均工资高于2000的部门号和它的平均工资，并按照 从低到高的顺序排序 select deptno , avg(sal) from emp group by deptno having avg(sal)>2000 order by avg(sal)
SQL Server 2005 复杂查询 --多表查询
在from子句中使用子查询 1.如何显示高于部门平均工资的员工的姓名、薪水、和他部 门的平均工资
①首先要知道各个部门的平均工资
select avg(sal) , deptno from emp group by deptno
②然后再查询 select e.ename , e.sal , e.deptno , temp.das from emp e , (select avg(sal) das , deptno from emp group by deptno ) temp where e.deptno = temp.deptno and e.sal > temp.das
SQL Server 2005 复杂查询 --多表查询
说明： 在from子句中使用子查询时，该子查询会被作为一个临时表来对待， 必须给子查询指定列名
SQL Server 2005 复杂查询 --多表查询
用查询结果创建新表 这个命令是一种快捷的建表方 法 select * (字段) into 另一个 表名 from 表 1.如何删除掉一张表重复记录 • create table test( • id int, • name varchar(30) • ) • select * from test • insert into test values (1,'aaa') • insert into test values (2,'bbb') • select distinct * into temp from test • delete from test • insert into test select * from temp • drop table temp
SQL Server 2005 复杂查询 --多表查询
多表查询是指基于两个和两个以上的表或是视图的查询， 在实际应用中，查询单个表可能不能满足你的需求，如显示 sales部门位置和其员工的姓名，这种情况下需要使用到 （dept表和emp表） 笛卡尔集 select * from emp , dept select * from emp , dept where dept.dname = ‘sales’
SQL Server 2005 复杂查询 --多表查询
自连接 自连接是指同一张表的连接查询 1.显示某个员工的上级领导的姓名，比如显示‘ford’上级 select ename from emp where empno = (select mgr from emp where ename = 'ford') 2.显示公司每个员工和它的上级的名字 select worker.ename , boss.ename from emp worker , emp boss where worker.mgr = boss.empno
SQL Server 2005 复杂查询 --多表查询
分页查询 显示多少条记录到多少条记录 1.按照时间先后显示第五个到第十个入职的员工
select top 6 * from emp where empno not in (select top 4 empno from emp order by hiredate) order by hiredate
select stu.id,exam.id,,exa m.grade from stu inner join exam on stu.id=exam.id 简写：select stu.id,exam.id,,exa m.grade from stu.id=exam.id
SQL Server 2005 复杂查询 --多表查询
1.显示sales部门位置和其员工的姓名 select * from emp , dept where dept.dname = 'sales' and emp.deptno = dept.deptno 2.显示员工姓名、员工工资及所在部门的名字 (如果两张表都有相同的字段则需要带表名) select ename , sal , dname , emp.deptno from emp , dept where emp.deptno = dept.deptno 3.显示部门号为10的部门名、员工名和工资 select e.ename , d.dname , d.deptno , e.sal from emp
SQL Server 2005 复杂查询 --多表查询
内连接（只显示两表id匹配的） create table stu( id int, name varchar(30)) insert into stu values (1,'Jack') insert into stu values (2,'Tom') insert into stu values (3,'Kity') insert into stu values (4,'nono') create table exam( id int, grade int) insert into exam values (1,56) insert into exam values (2,76) insert into exam values (11,89)
左连接(也叫左外连接)(显示join左边的表的所有数据， exam只有两条记录，所以stu.id,grade都用null显示) select stu.id,exam.id,,exam.grade from stu left join exam on stu.id=exam.id 右连接(也叫右外连接)(与左连接相反,显示join右边表的所 有数据) select stu.id,exam.id,,exam.grade from stu right join exam on stu.id=exam.id
3.显示工资高于平均工资的员工信息 select * from emp where sal>(select avg(sal) from emp) 4.统计共有多少员工 select count(*) from emp