摘要:数据库查询,数据库内连接和外连接,if函数用法,limit用法
数据库内连接和外连接
#简便理解外连接:从左表第一行开始匹配右表,匹配完成后,如果是左外连接,寻找左表未匹配行,右表用null填充,#如果是右外连接,寻找右表未匹配行,左表用null填充,
#如果是全外连接,寻找左表未匹配行,右表用null填充,然后,寻找右表未匹配行,左表用null填充
#select * from scott.emp A inner join scott.dept B on A.deptno = B.deptno #内连接,包括等值连接,自然连接(去除重复列)
#select * from scott.emp A left join scott.dept B on A.deptno = B.deptno #左外连接,从左表第一行开始匹配右表,如果右表无匹配项,将右表用null填充
#select * from scott.emp A right join scott.dept B on A.deptno = B.deptno #右外连接,等价于右表左外连接左表
#select * from scott.dept B left join scott.emp A on A.deptno = B.deptno
#select * from scott.emp A full join scott.dept B on A.deptno = B.deptno #全外连接,MySQL好像不行
select A.dname,B.* from scott.dept A left join scott.emp B on A.deptno=B.deptno
select A.ename,B.dname from scott.emp A inner join scott.dept B where A.deptno=B.deptno and A.job='clerk'
#第一种方法
#select distinct job from scott.emp group by job having min(sale)>1500
#第二种方法:选择薪金大于1500的工作,并且该工作不存在薪金小于等于1500的
select distinct job from scott.emp A where A.sale>1500 and not exists
(
select null from scott.emp B where A.job=B.job and B.sale<=1500
)
select ename from scott.emp where deptno=some
(
select deptno from scott.dept where dname="sales"
)
select ename from scott.emp where sale>some
(
select avg(sale) from scott.emp #avg()函数的用法
)
select ename from scott.emp where job=some
(
select job from scott.emp where ename='scott'
)
select ename,sale from scott.emp where deptno!=30 and sale=some
(
select sale from scott.emp where deptno=30
)
select ename,sale from scott.emp where deptno!=30 and sale>some
(
select max(sale) from scott.emp where deptno=30
)
if(exp1,exp2,exp3)函数的用法:如果exp1为真,返回exp2,否则返回exp3
select B.deptno, count(A.ename) as empNumber,avg(if(A.sale is null,0,A.sale)+if(A.comm is null,0,A.comm)) as avgPay
from scott.emp A right join scott.dept B on A.deptno=B.deptno group by B.deptno
select A.ename,B.dname,A.sale+if(A.comm is null,0,A.comm) as pay from scott.emp A inner join scott.dept B on A.deptno=B.deptno
select A.*,count(B.deptno) as deptNumber from scott.dept A left join scott.emp B on A.deptno=B.deptno group by A.deptno
select A.job,min(A.sale+if(A.comm is null,0,A.comm)) as minPay from scott.emp A group by A.job
select B.deptno,min(B.sale) as minManagerSale from (select A.sale,A.deptno from scott.emp A where job='manager') as B group by B.deptno
select A.ename,(A.sale+if(A.comm is null,0,A.comm))*12 as annualSale from scott.emp A order by annualSale
limit用法:limit param1, param2
param1 : 开始搜索的指针 .从0开始计。
param2 : 搜索的条数。
select A.deptno,max(A.sale) as sale1,
(select B.sale from scott.emp B where B.deptno=A.deptno order by B.sale desc limit 1,1) as sale2,
(select C.sale from scott.emp C where C.deptno=A.deptno order by C.sale desc limit 2,1) as sale3
from scott.emp A group by A.deptno