网站建设方案图,创业先做网站,wordpress中文购物主题,兼职做ps网站将两个表或者两个以上的表以一定的连接条件连接起来#xff0c;从中检索出满足条件的数据。
内连接
使用inner join#xff0c;inner可以省略
-- 查询员工的姓名和部门名称
select E.ename as 员工姓名, D.dname as 部门名称从中检索出满足条件的数据。
内连接
使用inner joininner可以省略
-- 查询员工的姓名和部门名称
select E.ename as 员工姓名, D.dname as 部门名称from emp Ejoin dept Don E.deptno D.deptnoselect … from A, B
假设A表有xxx行则行可以表示为集合(a1,a2,...,ax)(a_1,a_2,...,a_x)(a1,a2,...,ax) 假设B表有yyy行则行可以表示为集合(b1,b2,...,by)(b_1,b_2,...,b_y)(b1,b2,...,by) 则select ... from A,B就是将两个表的行进行笛卡尔成绩并将两个行进行合并得到(a1b1,a1b2,...,a2b1,a2b2,...)(a_1b_1,a_1b_2,...,a_2b_1,a_2b_2,...)(a1b1,a1b2,...,a2b1,a2b2,...)因此总共的行数是x∗yx*yx∗y总共的列数是两个表列数相加
即把A表的每一条记录都和B表的每一条记录组合在一起
select … from A,B where …
对上面的表用where的条件进行过滤
select E.ename as 员工姓名, D.dname as 部门名称from emp as E, dept as Dwhere E.deptno D.deptnoselect … from A join B on …
join是连接的意思 on 表示连接条件 如果使用join就必须使用on
select * from empjoin depton 11 --70*11select emp.ename as 员工姓名, dept.deptno as 部门编号from empjoin depton 1 1 --70*2select emp.ename as 员工姓名, dept.deptno as 部门编号from empjoin depton emp.deptnodept.deptno --14*2select E.ename as 员工姓名, D.deptno as 部门编号from emp as Ejoin dept as Don E.deptnoD.deptno --14*2
select *from emp as Ejoin dept as Don 1 1 select *from dept as Djoin emp as Eon 1 1 order by D.deptnoselect * from dept,emp
where dept.deptno emp.deptno
--实际中发现无论将哪个表放在前面总是用行数少的表匹配行数多的from和join后面可以使用别名如果在这里使用别名其他的地方也都必须使用别名。区别于select后面的别名不能在其他地方使用我认为根本原因在于语句的执行顺序
实际上和select ... from A,B where ...等价推荐使用join on
使用join on可以再使用where对得到的数据过滤从而实现不同的分工
混合使用
select * from emp as E, dept as D
where E.deptnoD.deptno and E.sal2000
--等价于下面的写法下面的写法更加清晰
select * from emp as Ejoin dept as Don E.deptno D.deptnowhere E.sal 2000--求出工资大于2000的员工的姓名 部门编号 薪水 薪水等级
select emp.ename as 员工姓名, dept.dname as 部门名称, emp.sal as 薪水, SALGRADE.GRADE as 薪水等级from emp,dept,SALGRADEwhere emp.deptnodept.deptno and emp.sal2000 and emp.sal SALGRADE.LOSAL and emp.sal SALGRADE.HISALselect emp.ename as 员工姓名, dept.dname as 部门名称, emp.sal as 薪水, SALGRADE.GRADE as 薪水等级from empjoin depton emp.deptnodept.deptnojoin SALGRADEon emp.salSALGRADE.LOSAL and emp.salSALGRADE.HISALwhere emp.sal2000我们也可以把查询的表当作一个表进行子查询
-- 输出部门名称,该部门所有员工的平均工资 平均工资等级select dept.dname as 部门名称, tmp.avg_sal as 平均工资, SALGRADE.GRADE as 平均工资等级from(select emp.deptno as dept_no, AVG(emp.sal) as avg_salfrom empgroup by emp.deptno) tmpjoin depton dept.deptno tmp.dept_nojoin SALGRADEon tmp.avg_sal between SALGRADE.LOSAL and SALGRADE.HISAL语句顺序
SELECT ...INTOFROMJOINONWHEREGROUP BYHAVINGORDER BY-- 输出3个姓名中不含有O的工资最高的员工的姓名、工资、工资等级、部门名称select top 3 emp.ename as 员工姓名, emp.sal as 员工工资, SALGRADE.GRADE as 工资等级, dept.dname as 部门名称from empjoin depton emp.deptnodept.deptnojoin SALGRADEon emp.sal SALGRADE.LOSAL and emp.sal SALGRADE.HISALwhere emp.ename not like %O%order by emp.sal desc当null和not in在一起的时候需要注意。如果表中有null则使用not in的时候返回的总为空。
这与SQL的比较机制有关。在SQL中比较结果分为truefalsenull只有结果为true的时候系统才认为匹配成功并返回记录in的本质是等于的ornot in的本质是不等于的and。
比较结果and nullor nulltruenulltruefalsefalsenullnullnullnull
当使用in的时候因为是or进行连接所以可以正常返回true在not in的时候是and连接因此返回总为null因此返回为空。
详细原因可以看这篇文章传送门。为了解决这个问题我们可以使用is [not] null和isnull()函数组合判断
--求出emp表中所有领导的姓名select distinct E1.ename as 领导姓名from emp E1join emp E2on E1.EMPNO E2.mgrselect emp.ename as 领导姓名from empwhere emp.EMPNO in (select distinct mgr from emp)--输出所有非领导的信息select *from empwhere emp.EMPNO not in (select distinct mgr from emp where mgr is not null)--求出平均薪水最高的部门的名称和部门平均工资select dept.dname as 部门名称, tmp.avg_sal as 平均工资from (select top 1 emp.deptno as dept_no, AVG(emp.sal) as avg_salfrom empgroup by emp.deptnoorder by AVG(emp.sal) desc) tmpjoin depton tmp.dept_nodept.deptno当子查询的值只有一个的时候可以将子查询放在表达式中
--工资大于 所有员工中工资最低的人中的工资 的人中
--前三个人的姓名 工资 部门编号 部门名称 工资等级
select top 3 emp.ename as 姓名, emp.sal as 工资, emp.deptno as 部门编号, dept.dname as 部门名称, SALGRADE.GRADE as 工资等级from empjoin (select MIN(sal) as min_sal from emp) as tmpon emp.sal tmp.min_saljoin dept on emp.deptno dept.deptnojoin SALGRADEon emp.sal between SALGRADE.LOSAL and SALGRADE.HISALorder by emp.sal select top 3 tmp.ename as 姓名, tmp.sal as 工资, tmp.deptno as 部门编号, dept.dname as 部门名称, SALGRADE.GRADE as 工资等级from ( select ename,sal,deptno from emp where sal (select MIN(sal) as min_sal from emp)) as tmpjoin dept on tmp.deptno dept.deptnojoin SALGRADEon tmp.sal between SALGRADE.LOSAL and SALGRADE.HISALorder by tmp.sal--把工资大于1500的所有员工按部门分组
--按升序输出最后两个平均工资小于3000的部门名称人数平均工资平均工资水平
select dept.dname as 部门名称, tmp.number as 部门人数, tmp.avg_sal as 平均工资, SALGRADE.GRADE as 平均给工资水平from(select top 2 deptno as dept_no, COUNT(*) as number, AVG(sal) as avg_salfrom emp where sal1500 group by deptnohaving AVG(sal)3000order by AVG(sal) desc) tmpjoin depton tmp.dept_nodept.deptnojoin SALGRADEon tmp.avg_sal between SALGRADE.LOSAL and SALGRADE.HISALorder by tmp.avg_salorder by的顺序应该在最后因此可以用别名。group by和having都不可以用别名
外连接
不但返回满足条件的所有记录而且会返回部门不满足条件的记录。
左外连接
select * from empleft join depton emp.deptnodept.deptno用左表的一行分别和右表的所有行进行连接如果没有匹配的行则一起输出如果右表有多行匹配则结果输出多行。如果没有匹配行则结果只输出一行该输出左边为左表的一行的内容右边全部输出null因为右边很可能出现有多行和左表的某一行匹配所以左连接产生的结果集的行数很可能大于左边表的行数
select * from deptleft join empon dept.deptnoemp.deptno --16行返回一个事物和该事物的相关信息如果没有相关信息就输出空
右外连接
同左外连接
完全连接
full join
两个表中匹配的所有行记录左表中那些在右表找不到匹配的行的记录右边为NULL右表中那些在左表找不到匹配的行的记录左边为NULL
交叉连接
cross join等价于join on 11后面不用加on
自连接
一张表和自己连接起来注意连接自己的时候需要标明是哪一张表中的字段
--求薪水最高的员工的信息select *from empwhere sal (select MAX(sal) from emp)-- 不准用聚合函数求薪水最高的员工的信息
select *from empjoin (select top 1 EMPNO from emp order by sal desc) tmpon emp.EMPNOtmp.EMPNOselect *from empwhere sal not in (select distinct E1.salfrom emp as E1join emp as E2on E1.sal E2.sal)联合
纵向连接表中的数据即添加一行
--输出每个员工的姓名工资上司的姓名select E1.ename as 姓名, E1.sal as 工资, E2.ename as 上司from emp as E1left join emp as E2 --用左连接的原因是有一个没有上司on E1.mgr E2.EMPNO--或者使用联合select E1.ename as 姓名, E1.sal as 工资, E2.ename as 上司from emp as E1join emp as E2on E1.mgr E2.EMPNO
union
select ename, sal, BOSS from emp where mgr is null注意
select子句输出列数相等数据类型也相同至少是兼容的
分页查询
