数据库的查询总结

已建好数据库：

dept:

emp:

一、连接查询

1.相等连接：

【例2-27】查询工资大于等于3000的员工的员工编号、姓名、工资、所在部门编号及部门所在地址，结果按部门编号进行排序。

SELECT empno,ename,sal,deptno FROM emp WHERE sal>=3000 ORDER BY deptno;

2.自身连接：

【例2-28】查询emp表中在部门21工作的雇员的姓名及其管理员的姓名 

 SELECT e.ename 雇员,m.ename 管理员 FROM emp e,emp m WHERE m.empno=e.mgr AND e.deptno=21; 

 注意：起别名AS可省

结果：

3.不等连接：

【例2-29】salgrade表中存放着工资等级的信息，查询在部门编号为20工作的雇员的工资及工资等级的信息。

SELECT e.ename,e.sal,s.grade
   FROM emp e,salgrade s
   WHERE e.sal  BETWEEN s.losal  AND s.hisal
    AND e.deptno=20;

 4.左外连接：

FROM 表1  LEFT  【OUTER】 JOIN  表2 

            ON  表1.列=表2.列

SELECT dept.deptno,emp.deptno,ename,empno
   FROM  dept  LEFT JOIN emp
   ON  dept.deptno=emp.deptno
   WHERE dept.deptno=2 OR dept.deptno=3;

结果：

注：1.outer可以省略；

      2.如果右表信息没有匹配，右表补空

例：

SELECT dept.deptno,emp.deptno,ename,empno
   FROM  dept  LEFT JOIN emp
   ON  dept.deptno=emp.deptno
   WHERE dept.deptno=6 OR dept.deptno=3;

结果：

5.右外连接：

FROM 表1  RIGHT  【OUTER】  JOIN  表2

                ON  表1.列=表2.列

注：如果左表信息没有匹配，左表补空

【例2-31】示例。
 

SELECT empno,ename,emp.deptno,dept.deptno
  FROM  emp  RIGHT  OUTER JOIN  dept
  ON  emp.deptno=dept.deptno
  WHERE dept.deptno=2 OR dept.deptno=3;

结果：

二、子查询（select语句的嵌套）

1.返回单值的子查询：

【例2-32】查询与Draw工作岗位相同的员工的员工编号、姓名、工资、岗位信息。

SELECT empno,ename,sal,Job FROM emp WHERE Job='Draw';

【例2-33】查询工资大于平均工资而且与Draw工作岗位相同员工的信息。

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp) AND Job='Draw';

错误：SELECT * FROM emp WHERE sal>AVG(sal) AND Job='Draw';

注意：where子句中不能直接跟聚合函数；

2．返回多值的子查询

在WHERE子句中使用多值子查询时，必须使用多值比较运算符：[NOT]  IN、[NOT]  EXISTS、ANY、ALL，其中ALL、ANY必须与比较运算符结合使用。

使用IN操作符的多值子查询

【例2-34】查询工资为所任岗位最高的员工的职工编号、姓名、岗位和工资的信息，不包含岗位为CLERK和PRESIDENT的员工。

SELECT Job,MAX(sal) FROM emp GROUP BY Job HAVING Job
NOT IN('Draw','Pant');

使用ALL操作符的多值子查询

【例2-35】查询高于部门2的所有雇员工资的雇员信息。

SELECT * from emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=2);

使用ANY操作符的多值子查询

【例2-36】查询高于部门10的任意雇员工资的雇员信息。

SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=2);

使用EXISTS操作符的多行查询

【例2-37】查询工作在NEW YORK的雇员姓名、部门编号、工资、岗位的信息。

SELECT ename,deptno,sal,Job FROM emp WHERE EXISTS(SELECT loc FROM emp WHERE loc='NEW YORK');

三、合并查询查询

UNION的语法格式为：

 SELECT 语句1

   UNION [ALL]

 SELECT 语句2

SELECT empno,ename,deptno,job FROM emp WHERE job='MANAGER'
UNION 
SELECT empno,ename,deptno,job FROM emp WHERE deptno=10;