sql多表查询

sql多表查询

#多表查询
SELECT*FROM emp;
SELECT*FROM dept;
SELECT*FROM salgrade
#查询雇员工资和他的部门名称
SELECT*  FROM emp,dept WHERE emp.depton=dept.deptnum 
#查询10号部门雇员工资和他的部门名称
SELECT*  FROM emp,dept WHERE emp.depton=dept.deptnum AND emp.depton=10
#显示雇员和工资级别
SELECT ename,sal,grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal
#
SELECT ename,sal,deptname,depton FROM emp,dept WHERE emp.depton=dept.deptnum ORDER BY depton DESC,sal DESC
#
SELECT ename,sal,deptname,grade FROM emp,dept,salgrade WHERE emp.depton=dept.deptnum  AND sal BETWEEN losal AND hisal ORDER BY depton DESC,grade DESC

#多表查询的自连接
SELECT worker.ename AS worker,boss.ename AS boss FROM emp worker,emp boss 
 WHERE worker.mgr=boss.empno
 #查询与萧炎一个部门的雇员
SELECT ename FROM emp  WHERE 
depton=( SELECT depton FROM emp WHERE ename='萧炎')

SELECT job FROM emp WHERE depton=20

SELECT ename,job,sal,depton FROM emp
WHERE job IN(SELECT job FROM emp WHERE depton=20)