4.数据库的查询

use YGGL
--查询每个雇员的所有数据
select * from Employees

--查询雇员表中的地址和电话
select Addres,PhoneNum from Employees

--查询Employees表中的部门号和性别（消除重复行）
select  DepID,EmpSex from Employees
select distinct DepID,EmpSex from Employees

--查询EmployeeId为000001的雇员的地址和电话
select Addres,PhoneNum from Employees
   where EmpID='000001'

--查询月收入高于2000元的员工号码
select PhoneNum from Employees
where EmpID in(
select EmpID from Salary
where InCome > 2000
)

--查询财务部的员工的号码和姓名
select PhoneNum as 号码,EmpName as 姓名 from Employees
where DepID in (
select DepID from Departments
where DepName='财务部'
)

--查询1970年以后出生的员工的姓名和住址
select * from Employees
select EmpName,Addres from Employees
where EmpBir > '1970'

--查询Employees表中员工的姓名和性别，sex为1时显示“男”，为0时显示“女”
select EmpName as 姓名,
  case
   when EmpSex=1 then '男' 
   when EmpSex=0 then '女'
  end  as 性别
from Employees

--查询Employees员工的姓名，住址和收入水平，2000元以下显示为低收入，
--2000~3000显示为中等收入，3000元以上，显示为高收入
select EmpName as 姓名,Addres as 地址,
  case 
  when EmpID in(select EmpID from Salary where InCome<2000) then '低收入'
  when EmpID in(select EmpID from Salary where InCome<=3000) then '中等收入'
  when EmpID in(select EmpID from Salary where InCome>3000) then '高收入'
  end as 收入水平
from Employees 

--计算每个雇员的实际收入
select EmpID as 工号,InCome - OntCome as '实际收入'
 from Salary

 --聚合函数有sum,avg,max,min,count
 --获得员工总数
 select count(*) from Employees
 --计算Salary表中员工月收入的平均数
 select avg(InCome) from Salary
 --获得Employees表中最大的员工号码
 select max(PhoneNum) from Employees
 
 --找出所有姓王的雇员的部门号
 select DepID from Employees
 where EmpName like '王%'
--查找员工号码中倒数第二个数字为0的员工的姓名、地址和学历
select EmpName,AddRes,EmpEdu from Employees
where PhoneNum like '%0_'

--使用into子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和收入
select EmpID as 编号,InCome as 收入
  into 收入在1500元以上的员工
  from Salary
  where InCome>1500

--查找财务部年龄不低于研发部雇员年龄的雇员的姓名
select EmpName from Employees
where DepID in(
select DepID from Departments
where DepName = '财务部'
)and EmpBir !> all(
select EmpBir from Employees
where DepID in(
select DepID from Departments
where DepName='研发部'
)
)

--查找比所有财务部的雇员收入都高的雇员的姓名
select EmpName from Employees
where EmpID in (
select EmpID from Salary
where InCome > all(
select InCome from Salary
where EmpID in(
select EmpID from Employees
where DepID in(
select DepID from Departments
where DepName='财务部'
))))

--查找所有年龄比研发部雇员年龄都大的雇员姓名
select EmpName from Employees
where EmpBir<all(
select EmpBir from Employees
where DepID in(
select DepID from Departments
where DepName='研发部'
)
)

--查询每个雇员的情况及其薪水情况
select Employees.*,Salary.* from Employees,Salary
--等价于select * from Employees,Salary
where Employees.EmpID=Salary.EmpID

--查询每个雇员的情况及工作部门的情况
select  Employees.*,DepName from  Employees,Departments
where Employees.DepID=Departments.DepID

--查询“王林”的员工所在的部门
select DepName from Departments
where DepID in(
select DepID from Employees
where EmpName='王林'
)
--使用内连接的方法查询“王林”的员工所在的部门
select DepName from Departments
inner join Employees on Departments.DepID=Employees.DepID
where EmpName='王林'

--使用内连接方法查找出不在财务部工作的所有员工的信息
select * from Employees
inner  join Departments 
on Departments.DepID=Employees.DepID
where DepName!='财务部'

--使用外连接的方法查找出所有员工的月收入
select InCome from Salary
left outer join Employees
on Salary.EmpID=Employees.EmpID

--内连接和外连接的区别？

--查询财务部收入在2000元以上的雇员姓名及其薪水详情
select EmpName,InCome,OntCome from Employees
join Salary on Salary.EmpID=Employees.EmpID
where InCome>2000 and DepID in (select DepID from Departments where DepName='财务部')

select EmpName,InCome,OntCome from Employees
join Salary on Salary.EmpID=Employees.EmpID
join Departments on Departments.DepID=Employees.DepID
where DepName='财务部' and InCome>2000

--查询研发部在1976年以前出生的雇员姓名及其薪水详情
select EmpName,InCome from Employees
join Departments on Departments.DepID=Employees.DepID
join Salary on Salary.EmpID=Employees.EmpID
where DepName='研发部' and EmpBir < = '1976'

--求财务部雇员的平均收入
select avg(InCome) as '财务部平均收入' from Salary
where EmpID in(
select EmpID from Employees
where DepID in(
select DepID from Departments
where DepName='财务部'
)
)

--求财务部雇员的平均实际收入
select avg(InCome - OntCome) as '财务部平均实际收入' from Salary
where EmpID in(
select EmpID from Employees
where DepID in(
select DepID from Departments
where DepName='财务部'
)
)

--求财务部雇员的总人数
select count(*) from Employees
join Departments on Employees.DepID=Employees.DepID
where DepName='财务部'  --该结果有误 

select count(DepID) from Employees --DepID可使用*替换
where DepID in(
select DepID from Departments
where DepName='财务部'
)

--查找Employees表中男性和女性的人数
select EmpSex,count(*) from Employees
group by EmpSex

--按员工的学历分组，排列出本科、大专和硕士的人数
select Employees.EmpEdu,count(*)AS 人数 from Employees
group by EmpEdu

--查找员工数超过2的部门名称和员工数量
select count(EmpID) as 员工人数 from Departments,Employees
where Departments.DepID=Employees.DepID
group by Employees.DepID
  having count(*)>2

--按员工的工作年份分组，统计各个工作年份的人数
select * from Employees
select count(WorkYear) from Employees
group by WorkYear

--将各雇员的情况按收入由低到高排序
select Employees.*,InCome from Employees
join Salary on Employees.EmpID=Salary.EmpID
order by InCome asc

--在order by子句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从大到小排序
select EmpName,EmpSex,WorkYear,InCome-OntCome from Employees
join Salary on Salary.EmpID=Employees.EmpID
order by InCome-OntCome desc