【SQL Server】查询操作之二

连接查询定义

• 连接查询：将两个表或者两个以上的表以一定的连接条件连接起来从中检索出满足条件的数据

内连接

-- 内连接

--1. select ... from A, B 结果是笛卡尔积 把A表的每一条记录和B表的每一条记录组合在一起
select * from emp, dept -- emp 14行8列，dept 5行 3列 结果 14*5=70行 8+3=11列

--2. select ... from A, B where 对 1 产生的笛卡尔积用where的条件过滤
select * from emp, dept 
	where empno = 7369 -- 5行结果，因为A表1行 B表5行 1*5=5行

--3. select ... from A join B on
select emp.ename, dept.dname from emp
	join dept -- join 连接 
	on emp.deptno = dept.deptno -- on 是连接条件 不能省略,有join就有on

--4. select ... from A, B where 和 select ... from A join B on
-- 结果一样，前者是SQL92，后者是SQL99推荐用SQL99标准
select emp.ename, dept.dname from emp, dept
	where emp.deptno = dept.deptno

select emp.ename, dept.dname from emp
	join dept 
	on emp.deptno = dept.deptno

select "E".ename "员工名称", "D".dname "部门名称"
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno

--把工资大于2000的员工的 姓名 和 部门的编号 和 工资等级
	--SQL99标准
select "E".ename, "D".dname, "S".grade
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno
	join salgrade "S"
	on "E".sal > "S".losal and "E".sal < "S".hisal
	where "E".sal>2000
	
	--SQL92标准
select "E".ename, "D".dname, "S".grade
	from emp"E",dept"D",salgrade"S"
	where"E".deptno="D".deptno and ("E".sal < "S".hisal and "E".sal>"S".losal and "E".sal>2000)

--SQL92标准
select *
	from emp "E", dept "D"
	where "E".sal>2000 and "E".deptno = "D".deptno
--SQL99标准
select *
	from emp "E"
	join dept "D" 
	on "E".deptno = "D".deptno
	where "E".sal>2000

练习题

1. 每个员工的姓名 部门编号 薪水 和薪水的等级

select emp.ename "姓名", dept.deptno "部门编号", emp.sal "薪水", salgrade.grade "薪水的等级"
   from emp
   join dept on emp.deptno = dept.deptno
   join salgrade on emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal

2. 查找每个部门的编号，该部门所有员工的平均工资，平均工资的等级

-- 方法一
select emp_new."部门编号", emp_new."部门平均工资", salgrade.grade from salgrade
join
	(
		select emp.deptno "部门编号", avg(emp.sal) "部门平均工资" from emp
		group by emp.deptno
	) "emp_new"
on emp_new."部门平均工资" between salgrade.losal and salgrade.hisal

-- 方法二
select "T".deptno "部门编号", "T".avg_sal "部门平均工资", "S".grade "工资等级"
	from(	
		select emp.deptno, avg(sal) "avg_sal" from emp
		group by deptno
	) "T"
	join salgrade "S"
	on "T".avg_sal between "S".losal and "S".hisal

-- 方法三
select "T".deptno "部门编号", "T".avg_sal "部门平均工资", "S".grade "工资等级"
from salgrade "S", 
	(
		select deptno, avg(sal) "avg_sal" from emp
		group by deptno
	) "T"
where "T".avg_sal between "S".losal and "S".hisal

3. 查找每个部门的编号，部门名称，该 部门平均工资 和 平均工资的等级

-- 方法一
select "emp_new".deptno "部门编号", "emp_new".dname "部门名称","emp_new".部门平均工资, salgrade.grade from salgrade
join
	(
		select emp.deptno, dept.dname, avg(emp.sal) "部门平均工资" from emp
		join dept on dept.deptno = emp.deptno
		group by emp.deptno, dept.dname
	) "emp_new"
on "emp_new"."部门平均工资" between salgrade.losal and salgrade.hisal

-- 方法二	
select "T".deptno, "T".avg_sal "部门平均工资", "S".grade "工资等级", "D".dname "部门名称"
from
	(
		select deptno, avg(sal) "avg_sal" from emp
		group by deptno
	) "T"
	join salgrade "S" on "T".avg_sal between "S".losal and "S".hisal
	join dept "D" on "T".deptno = "D".deptno

4. 求出emp表中所有领导的姓名

select * from emp where emp.EMPNO in (select mgr from emp)

5. 求出平均薪水最高的部门的编号和部门的平均工资

select top 1 deptno, avg(sal)
	from emp
	group by deptno
	order by avg(sal) desc

6. 有一个人工资最低，把这个人排除掉，剩下的人中工资最低的前三个人的 姓名 工资 部门编号 部门名称 工资等级

 select top 3 "emp_new".ename, "emp_new".sal, "emp_new".deptno, dept.dname, salgrade.grade
from(
	select * from emp
	where emp.sal > (select min(sal) from emp)
	) "emp_new"
join dept on "emp_new".deptno = dept.deptno
join salgrade on "emp_new".sal between salgrade.losal and salgrade.hisal
order by "emp_new".sal

外连接

• 外连接：不但返回满足条件的所有记录，而且会返回部分不满足条件的记录

• 分类：

  • 左外连接：不但返回满足连接条件的所有记录，而且会返回左表不满足连接条件的记录

    select * from emp
    left join dept
    on emp.deptno = dept.deptno
    

  • 左外连接运行原理：

  1. 用左表的第一行分别和右表的所有行进行链接，如果又匹配行，则一起输出，如果右表有多行比配，则结果集输出多行，如果没有匹配行，则结果集中之输出一行，该输出行左边为左表第一行内容，右边全部输出null。
  2. 然后再用左表第二行和右表所有行进行链接，依次类推，直到左边所有行链接完毕。
  3. 因为右边很可能出现有多行和左边的某一行匹配，所以左连接产生的结果集的行数很可能大于 left join 左边表的记录总数
  • 右外连接：不但返回满足连接条件的所有记录，而且会返回右表不满足连接条件的记录

完全连接

select * from emp
full join dept
on emp.deptno = dept.deptno

• 结果集包含三部分内容：
  1. 两个表中匹配的所有行记录
  2. 左表中那些在右表中找不到匹配的行的记录，这些记录的右边全部为null
  3. 右表中那些在左表中找不到匹配的行的记录，这些记录的左边全部为null

交叉连接

select * from emp cross join dept
-- 等价于
select * from emp, dept

• 产生的是笛卡尔积

自连接

• 自连接：一张表自己和自己连接起来查询数据

• 例子：不准用聚合函数 求薪水最高的员工的信息

• 使用聚合函数

  select * from emp
  where sal = (
  	select max(sal) from emp
  )
  

• 不使用聚合函数

  select * from emp
  where empno not in (
  	select distinct "E1".empno from emp "E1"
  	join emp "E2"
  	on "E2".sal > "E1".sal
  )
  

联合

• 联合：表和表之间的数据以纵向的方式连接到一起
• 例子：输出每个员工的姓名，工资，上司的姓名

select "E2".ename"老板", "E1".ename"员工" from emp "E1"
	join emp "E2"
	on "E1".mgr = "E2".empno 
union
select '最大老板', ename from emp where mgr is null

• 注意：若干个select子句要联合成功的话，必须满足两个条件
  1. select 子句输出的列数必须是相同的
  2. select 子句输出的列的数据类型至少是兼容的