数据库复习二

摘要：数据库查询，数据库内连接和外连接，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好像不行

5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

select A.dname,B.* from scott.dept A left join scott.emp B on A.deptno=B.deptno

6.列出所有"clerk"(办事员)的姓名及其部门名称

select A.ename,B.dname from scott.emp A inner join scott.dept B where A.deptno=B.deptno and A.job='clerk'

7.列出最低薪金大于1500的各种工作
#第一种方法
#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

)

8.列出在部门“sales”(销售部)工作的员工的姓名,假定不知道销售部的部门编号
select ename from scott.emp where deptno=some
(
select deptno from scott.dept where dname="sales"

)

9.列出薪金高于公司平均薪金的所有员工
select ename from scott.emp where sale>some
(
select avg(sale) from scott.emp #avg()函数的用法

)

10.列出与“scott”从事相同工作的所有员工
select ename from scott.emp where job=some
(
select job from scott.emp where ename='scott'

)

11.列出薪金等于部门30中员工薪金的所有员工的姓名和薪金
select ename,sale from scott.emp where deptno!=30 and sale=some
(
select sale from scott.emp where deptno=30

)

12.列出薪金高于在部门30中工作的所有员工的薪金的员工姓名和薪金
select ename,sale from scott.emp where deptno!=30 and sale>some
(
select max(sale) from scott.emp where deptno=30

)

13.列出在每个部门工作的员工数量、平均工资(工资=薪金+佣金)
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

14.列出所有员工的姓名、部门名称和工资

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

15.列出所有部门的详细信息和部门人数

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

16.列出各种工作的最低工资

select A.job,min(A.sale+if(A.comm is null,0,A.comm)) as minPay from scott.emp A group by A.job

17.列出各个部门的manager(经理)的最低薪金

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

18.列出所有员工的年工资，按年薪从低到高排序

select A.ename,(A.sale+if(A.comm is null,0,A.comm))*12 as annualSale from scott.emp A order by annualSale

19.用一条SQL语句查询出scott.emp表中每个部门工资前三位的数据
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