文章目录
写这个对于前面的 博客的DQL 的举例说明
表中的结构
## 表中的数据
实验任务
- 将雇员的工资情况按 Income 由高到低排列。（提示：由高到低排列使用 ORDER BY…DESC
- 求所有姓刘雇员的姓名、地址和电话号码
- 求收入 3500 到 4000 并且支出为 1500 的雇员编号
- 查询各个部门雇员的最低收入
- 查询各个部门雇员的平均收入
- 查询所有部门详细信息和部门人数
- 查询所有和‘张立’ 在同一部门的雇员姓名
- 查询所有部门的名称及在该部门雇员的姓名
- 查询所有收入为 3800 的雇员的姓名及所在部门名称
- 查询最低收入大于 3700 的部门信息
- 查询在’财务部’的所有雇员的个人信息
- 查询在’财务部’的所有雇员的收入
- 查询所有雇员的姓名、部门名称和收入
- 查询收入比’刘晨’高的所有雇员的个人信息
- 查询实际收入(Income-Outcome)大于 2300 的所有雇员的个人信息
- 查询所有实际收入比人力资源部所有雇员实际收入大的雇员姓名
- 查询所有实际收入比人力资源部某个雇员实际收入大的雇员姓名
- 查询收入大于平均收入的所有雇员的个人信息
MySQL语句
-- 1） 将雇员的工资情况按 Income 由高到低排列。（提示：由高到低排列使用 ORDER BY…DESCselect Salary.Income from Salaryorder by Income DESC ;-- 2） 求所有姓刘雇员的姓名、地址和电话号码select Employee.Name , Employee.Address , Employee.PhoneNumberfrom Employeewhere Employee.Name like '刘%';--3） 求收入 3500 到 4000 并且支出为 1500 的雇员编号select Employee.EmployeeIDfrom Employee , Salarywhere Income >= 3500 and Income <= 4000 and OutCome = 1500;--4） 查询各个部门雇员的最低收入select DepartmentID, MIN(Income)from Employee,Salarywhere Employee.EmployeeID = Salary.EmployeeIDgroup by DepartmentID;--5） 查询各个部门雇员的平均收入select DepartmentID,AVG(Income) 平均工资from Employee,Salarywhere Employee.EmployeeID = Salary.EmployeeIDgroup by DepartmentID;--6） 查询所有部门详细信息和部门人数select d.DepartmentID,DepartmentName,Note ,部门人数from Department dright join (select e.DepartmentID, COUNT(*)'部门人数' from Employee e group by DepartmentID) d1on d.DepartmentID = d1.DepartmentID;--7） 查询所有和‘张立’ 在同一部门的雇员姓名 select Employee.Name from Employee where DepartmentID in (select DepartmentID from Employee where Name = '张立' );--8） 查询所有部门的名称及在该部门雇员的姓名select DepartmentName,Namefrom Department dleft join (select Name,DepartmentID from Employee ) eon d.DepartmentID = e.DepartmentID;--9） 查询所有收入为 3800 的雇员的姓名及所在部门名称select Name , DepartmentNamefrom Employee,Departmentwhere Employee.DepartmentID = Department.DepartmentID and EmployeeID in (select EmployeeID from Salary where Income = 3800 );--10） 查询最低收入大于 3700 的部门信息select *from Departmentwhere 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 Employeewhere DepartmentID in (select DepartmentID from Department where DepartmentName='财务部')--12） 查询在'财务部'的所有雇员的收入select EmployeeID,Incomefrom Salarywhere EmployeeID in (select EmployeeID from Employee where DepartmentID in ( select DepartmentID from Department where DepartmentName ='财务部'))--13） 查询所有雇员的姓名、部门名称和收入select Name,DepartmentName,Incomefrom Employee eleft join (select Departmentid,DepartmentName from Department )don e.DepartmentID = d.DepartmentIDleft join (select EmployeeID,Income from Salary )son e.EmployeeID = s.EmployeeID;-- 14） 查询收入比'刘晨'高的所有雇员的个人信息select * from Employeewhere EmployeeID in (select EmployeeIDfrom Salarywhere Income > (select Income from Salary where EmployeeID = (select EmployeeID from Employee where Name = '刘晨')))-- 15） 查询实际收入(Income-Outcome)大于 2300 的所有雇员的个人信息select *from Employeewhere EmployeeID in (select EmployeeIDfrom Salarywhere (Income-OutCome) > 2300)-- 16） 查询所有实际收入比人力资源部所有雇员实际收入大的雇员姓名select Namefrom Employeewhere 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 Namefrom Employeewhere 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 Employeewhere EmployeeID in (select EmployeeIDfrom Salarywhere income > (select AVG(Income)from Salary))