关联查询、子查询与分页查询

2018-01-18 本文已影响0人程序员必修课

关联查询

关联关系分为一对一，一对多，多对多关系
关联查询就是从多张表中查询数据，当我们使用n张表使用关联查询，至少要有n-1个连接条件。否则可能产生笛卡尔积。笛卡尔积是数据库中第一大杀手，没意义，占用资源。

外键就是保存另一张表主键的那一列，还有外键的表，在关联关系中处于“多”的哪一方。

等值连接

连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用等号"="连接相关的表。

内连接（JOIN ON）

内连接返回所有满足连接条件的记录。
当查询的字段，在多张表中都存在时，应当用表别名.字段名来区分。

例：

SELECT d.loc,e.ename FROM emp e JOIN dept d ON(e.deptno=d.deptno);

外连接

外连接分为左外连接，右外连接和全外连接。

左外连接：以左面的表为主，若右边的表有不满足条件的，补null。
右外连接正好相反。
全外连接，所有不满足的内容都补null；
MySQL不支持FULL OUTER JOIN。

自连接

自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列表中的列参照同一个表中的其它列的情况称作自参照表。
自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。

自连接特点：自己表中有一个外键保存自己表的主键。

子查询

使用子查询的情景：当一次查询建立在另一次查询的基础之上的，就需要子查询。

子查询在WHERE中

子査询嵌入的语句称作主査询或父査询；
主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。

单行单列子查询可以用于 > < = >= <= <>等比较操作；
多行单列子查询可以使用IN，ANY，ALL等
多行多列或单行多列（一般没意义），多行多列子查询通常用于建立在二次查询，常出现在FROM子句中。

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。
EXISTS后便的子查询至少返回一行数据，则整个条件返回TRUE。

    SELECT ename,job FROM emp
    WHERE job=(SELECT job FROM emp WHERE ename='SCOTT');

    SELECT deptno,ename,sal FROM emp 
    WHERE sal > (SELECT AVG(sal) FROM emp);

    SELECT empno,ename,job,sal,deptno FROM emp
    WHERE deptno IN(SELECT deptno FROM emp
    WHERE job='SALESMAN') AND job<>'SALESMAN';
    
    --EXISTS在这里的用法是看部门表中所有部门编号在员工表中是否能关联查询出数据，能查询出来就保留。
    SELECT deptno,dname FROM dept d WHERE EXISTS
    (SELECT * FROM emp e WHERE d.deptno=e.deptno);

子查询在HAVING子句中

    SELECT deptno,MIN(sal) FROM emp GROUP BY deptno 
    HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno=30);
    
    SELECT empno,ename,job,sal,e.deptno 
    FROM emp e,(SELECT AVG(sal) avg_sal,deptno FROM emp m GROUP BY deptno) s
    WHERE e.deptno=s.deptno AND e.sal>s.avg_sal;

把一个查询结果集当做一张表看待，这种形式称之为视图。视图实际也是数据库对象

分页查询

ROWNUM被称作伪列，用于返回标识行数据顺序的数字。
就是将当前查出的结果集为每一行编一个号，编号始终从1开始。
SELECT ROWNUM, empno, ename, sal FROM emp;
想使用ROWNUM作为过滤条件，应先使用ROWNUM进行一次查询，在此结果之上进行一次查询即可。

SELECT FROM (SELECT ROWNUM rn,e.* FROM emp e) WHERE rn>3;

分页三步：1，排序；2，编号；3，取范围；

MySQL的分页查询是用limit函数：
取前5条数据
select * from table_name limit 0,5 
或者
select * from table_name limit 5 
查询第11到第15条数据
select * from table_name limit 10,5
limit关键字的用法：
LIMIT [offset,] rows
offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。*

MySQL的ROWNUM实现方法：

SELECT @rownum:=@rownum+1 AS rownum, frutas.*
FROM (SELECT @rownum:=0) r, frutas;

DECODE函数

DECODE (expr, search1, result1[, search2, result2...][, default])
DECODE用于比较参数exp的值，如果匹配到哪一个 search条件，就返回对应的result结果；
可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值
default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

和DECODE函数功能相似的有CASE语句，实现类似于if- else的操作•

MySQL中没有DECODE函数，只可以用case，when，then，end语句解决。

对于一个列中，几个不同的值看做一组，这时可以使用DECODE函数，先将不同的值转为一个值，因为GROUP BY根据值相同的看做一组

当一个字段中的默认排序方式不能满足我们的排序要求，我们可以使用DECODE将这些值转换为一个可以由小到大排序的值，这样就能按照我们的要求排序了。