mysql_对于DQL 的简单举例
写这个对于前面的 博客的DQL 的举例说明
表中的结构
## 表中的数据
实验任务
- 将雇员的工资情况按 Income 由高到低排列。(提示:由高到低排列使用 ORDER BY…DESC
- 求所有姓刘雇员的姓名、地址和电话号码
- 求收入 3500 到 4000 并且支出为 1500 的雇员编号
- 查询各个部门雇员的最低收入
- 查询各个部门雇员的平均收入
- 查询所有部门详细信息和部门人数
- 查询所有和‘张立’ 在同一部门的雇员姓名
- 查询所有部门的名称及在该部门雇员的姓名
- 查询所有收入为 3800 的雇员的姓名及所在部门名称
- 查询最低收入大于 3700 的部门信息
- 查询在’财务部’的所有雇员的个人信息
- 查询在’财务部’的所有雇员的收入
- 查询所有雇员的姓名、部门名称和收入
- 查询收入比’刘晨’高的所有雇员的个人信息
- 查询实际收入(Income-Outcome)大于 2300 的所有雇员的个人信息
- 查询所有实际收入比人力资源部所有雇员实际收入大的雇员姓名
- 查询所有实际收入比人力资源部某个雇员实际收入大的雇员姓名
- 查询收入大于平均收入的所有雇员的个人信息
MySQL语句
-- 1) 将雇员的工资情况按 Income 由高到低排列。(提示:由高到低排列使用 ORDER BY…DESC
select Salary.Income
from Salary
order by Income DESC ;
-- 2) 求所有姓刘雇员的姓名、地址和电话号码
select Employee.Name , Employee.Address , Employee.PhoneNumber
from Employee
where Employee.Name like '刘%';
--3) 求收入 3500 到 4000 并且支出为 1500 的雇员编号
select Employee.EmployeeID
from Employee , Salary
where Income >= 3500 and Income <= 4000 and OutCome = 1500;
--4) 查询各个部门雇员的最低收入
select DepartmentID, MIN(Income)
from Employee,Salary
where Employee.EmployeeID = Salary.EmployeeID
group by DepartmentID;
--5) 查询各个部门雇员的平均收入
select DepartmentID,AVG(Income) 平均工资
from Employee,Salary
where Employee.EmployeeID = Salary.EmployeeID
group by DepartmentID;
--6) 查询所有部门详细信息和部门人数
select d.DepartmentID,DepartmentName,Note ,部门人数
from Department d
right join (select e.DepartmentID, COUNT(*)'部门人数' from Employee e group by DepartmentID) d1
on d.DepartmentID = d1.DepartmentID;
--7) 查询所有和‘张立’ 在同一部门的雇员姓名
select Employee.Name
from Employee
where DepartmentID in (select DepartmentID from Employee where Name = '张立' );
--8) 查询所有部门的名称及在该部门雇员的姓名
select DepartmentName,Name
from Department d
left join (select Name,DepartmentID from Employee ) e
on d.DepartmentID = e.DepartmentID;
--9) 查询所有收入为 3800 的雇员的姓名及所在部门名称
select Name , DepartmentName
from Employee,Department
where Employee.DepartmentID = Department.DepartmentID and
EmployeeID in (select EmployeeID from Salary where Income = 3800 );
--10) 查询最低收入大于 3700 的部门信息
select *
from Department
where DepartmentID in (select DepartmentID from Employee where EmployeeID in (
select EmployeeID from Salary where Income > 3700 and Income in (
select MIN(Income) from Employee,Salary where Employee.EmployeeID = Salary.EmployeeID
group by DepartmentID) ) );
--11) 查询在'财务部'的所有雇员的个人信息
select *
from Employee
where DepartmentID in (select DepartmentID from Department where DepartmentName='财务部')
--12) 查询在'财务部'的所有雇员的收入
select EmployeeID,Income
from Salary
where EmployeeID in (select EmployeeID from Employee where DepartmentID in (
select DepartmentID from Department where DepartmentName ='财务部'))
--13) 查询所有雇员的姓名、部门名称和收入
select Name,DepartmentName,Income
from Employee e
left join (select Departmentid,DepartmentName from Department )d
on e.DepartmentID = d.DepartmentID
left join (select EmployeeID,Income from Salary )s
on e.EmployeeID = s.EmployeeID;
-- 14) 查询收入比'刘晨'高的所有雇员的个人信息
select *
from Employee
where EmployeeID in (select EmployeeID
from Salary
where Income > (select Income from Salary where EmployeeID = (select EmployeeID from Employee where Name = '刘晨')))
-- 15) 查询实际收入(Income-Outcome)大于 2300 的所有雇员的个人信息
select *
from Employee
where EmployeeID in (select EmployeeID
from Salary
where (Income-OutCome) > 2300)
-- 16) 查询所有实际收入比人力资源部所有雇员实际收入大的雇员姓名
select Name
from Employee
where EmployeeID in(select EmployeeID from Salary where (Income-OutCome) > (
select MAX(Income-Outcome)from Salary where EmployeeId in (
select EmployeeID from Employee where DepartmentID in (
select DepartmentID from Department where DepartmentName='人力资源部'))))
--17) 查询所有实际收入比人力资源部某个雇员实际收入大的雇员姓名
-- 重点是 某个雇员,,就是 你只要比那个部的最小的实际收入最大就可以了,,
select Name
from Employee
where EmployeeID in(select EmployeeID from Salary where (Income-OutCome) > (
select Min(Income-Outcome)from Salary where EmployeeId in (
select EmployeeID from Employee where DepartmentID in (
select DepartmentID from Department where DepartmentName='人力资源部'))))
--18) 查询收入大于平均收入的所有雇员的个人信息
-- 1. 查出平均收入
-- 2. 比较 显示employeeID
-- 3. 显示个人信息
select *
from Employee
where EmployeeID in (select EmployeeID
from Salary
where income > (select AVG(Income)
from Salary))