– 需求:
– 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
– 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
– 3.查询员工姓名,工资,工资等级
– 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
– 5.查询出部门编号、部门名称、部门位置、部门人数
– 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
– 1
SELECT a.id,a.ename,a.salary,
b.jname,b.description
FROM emp a,job b
WHERE a.job_id=b.id
;
– 2
SELECT a.id,a.ename,a.salary,
b.jname,b.description,
c.dname,c.loc
FROM emp a,job b,dept c
WHERE a.job_id=b.id
AND a.dept_id=c.id;
– 3
SELECT a.ename,a.salary,d.grade
FROM emp a,salarygrade d
WHERE d.grade IN(
SELECT d.grade
FROM salarygrade d
WHERE a.salary
>=d.losalary – 可使用between and
AND a.salary<=d.hisalary
);
– 4
SELECT a.ename,a.salary,
b.jname,b.description,
c.dname,c.loc,
d.grade
FROM emp a,job b,dept c,salarygrade d
WHERE a.job_id=b.id
AND a.dept_id=c.id
AND
d.grade IN(
SELECT d.grade
FROM salarygrade d
WHERE a.salary
>=d.losalary
AND a.salary<=d.hisalary
);
– 5
SELECT c.id,c.dname,c.loc, a.t1 部门人数
FROM dept c,(
SELECT dept_id,COUNT(id) t1
FROM emp
GROUP BY dept_id
) a
WHERE a.dept_id
=c.id
;
– 6左外连接 左表所有内容
SELECT a.ename
被管理者 ,b.ename 管理者
FROM emp a
LEFT JOIN emp b
ON a.mgr
=b.id
;