多表联查

记录集的叠加
UNION ALL把多个表中的行组合到一起；
 SELECT mac_address from device limit union all select mac_address from art limit 10;

 卡尔积
 select * from user,ddns;
 select * from user join ddns;

 把多个表中的列组合到一起；
 select e.name,d.loc from emp e,dept d where e.depto=d.depto and e.depto=10

 差集
 select deptno from dept where deptno not in (select deptno from emp)

 select * from user where not (user_id=1 or user_id=4);===  select * from user where user_id not in (1,4);
EXISTS 指定一个子查询，检测行的存在。语法：EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 （不允许有 COMPUTE 子句和 INTO 关键字）。结果类型为 Boolean，如果子查询包含行，则返回 TRUE。
在子查询中使用 NULL 仍然返回结果集

SELECT a.* FROM a WHERE not EXISTS (SELECT b from a_copy WHERE a.a=a_copy.b);mysql exists与not exists实例详解的相关资料,鉴于 not exists 的效率往往要高于 not in , 所以一般情况下会使用前者替代后者，

 

查询并集（union all）
    SELECT oname,odesc FROM object_a
    UNION ALL
    SELECT oname,odesc FROM object_b

查询并集（union）(可以去重复)

    SELECT oname,odesc FROM object_a
    UNION
    SELECT oname,odesc FROM object_b

差集
 SELECT a.oname, a.odesc
    FROM
      object_a a
      LEFT JOIN object_b b
        ON a.oname = b.oname
        AND a.odesc = b.odesc
    WHERE b.id IS NULL

 交集
 SELECT a.oname,a.odesc FROM object_a a INNER JOIN object_b b ON a.oname=b.oname AND a.odesc=b.odesc

SELECT * from device ORDER BY 2 asc

外连接可以返回一个表中所有行以及另一个表中匹配的行，不会将没有的行清除

 

7369    SSMITH    CLERK    7902    17-DEC-1980    800        
CREATE TABLE table_name (column_name column_type);SDF

mysql> select "-----",null from t1
    -> ;
Empty set (0.01 sec)

mysql> select "-----",null from t1
    -> ;
Empty set (0.00 sec)

mysql> select ename,deptno from emp where deptno=10 union all select "-------",null from
    -> t1 union all select dname,deptno from dept;
+------------+--------+
| ename      | deptno |
+------------+--------+
| KING       |     10 |
| MILLER     |     10 |
| -------    |   NULL |
| ACCOUNTING |     10 |
| RESEARCH   |     20 |
| SALES      |     30 |
| OPERATIONS |     40 |
+------------+--------+
7 rows in set (0.00 sec)

SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t) AND order_pi is NULL and
mac_address not in (select mac_address from (select mac_address, COUNT(*) as x from (SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t1)
AND order_pi is NULL) t3  GROUP BY mac_address HAVING x > 1) t5)

create view V as SELECT device.* from device join (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) as t
on t.mac_address=device.mac_address and device.order_pi is null

 

SELECT V.* from V left join (SELECT mac_address from V GROUP BY mac_address HAVING count(*) > 1) t8 on t8.mac_address=V.mac_address WHERE t8.mac_address is null

in not in,exists和not exists区别null
mysql> select deptno from emp;
+--------+
| deptno |
+--------+
|     20 |
|   NULL |
|     30 |
|     30 |
|     20 |
|     30 |
|   NULL |
|     20 |
|     10 |
|   NULL |
|     20 |
|     30 |
|     20 |
|     10 |
+--------+
14 rows in set (0.00 sec)

mysql> select deptno from dept;
+--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
|     40 |
+--------+
4 rows in set (0.00 sec)

mysql> select d.* from dept d where  exists ( select null from emp e where d.deptno = e.deptno);
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
+--------+------------+----------+
3 rows in set (0.00 sec)

mysql> select d.* from dept d where not  exists ( select null from emp e where d.deptno = e.deptno);
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

left join是left outer join的缩写，所以作用是一样的。

另外在SQL里没有区分大小写，也就是left join和LEFT JOIN都是可以的。
left join: 包含左表的所有行，对应的右表行可能为空。
right join: 包含右表的所有行，对应的左表行可能为空。
full join: 只包含左右表都匹配并且不为空的行。

SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t) AND order_pi is NULL and
mac_address not in (select mac_address from (select mac_address, COUNT(*) as x from (SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t1)
AND order_pi is NULL) t3  GROUP BY mac_address HAVING x > 1) t5)

 

SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t) AND order_pi is NULL and
mac_address not in (select mac_address from (select mac_address, COUNT(*) as x from (SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t1)
AND order_pi is NULL) t3  GROUP BY mac_address HAVING x > 1) t5);

 

SELECT mac_address, COUNT(*) AS sumCount FROM device WHERE order_pi is null  GROUP BY mac_address HAVING sumCount > 1
reset query cache;#清除缓存

select mac_address from (select mac_address, COUNT(*) as x from (SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1

select * from (select mac_address, COUNT(*) as x from (SELECT * from device WHERE mac_address in (select mac_address from (SELECT mac_address, COUNT(*) AS sumCount FROM device GROUP BY mac_address HAVING sumCount > 1) t1)
AND order_pi is NULL) t3  GROUP BY mac_address HAVING x > 1) t5

 

select *
 from (
 select e.empno,ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,count(*) as cnt from emp e
 group by empno,ename,job,mgr,hiredate,sal,comm,deptno) e

 select *
from (
select e.empno,ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,count(*) as cnt from emp e
group by empno,ename,job,mgr,hiredate,sal,comm,deptno) e WHERE NOT EXISTS (
SELECT NULL from (SELECT V.EMPNO,V.ENAME,V.JOB,V.MGR,V.HIREDATE,V.SAL,V.COMM,V.DEPTNO,COUNT(*) as cnt FROM V
group by EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) V WHERE
V.EMPNO=e.empno
AND V.ENAME=e.ename
AND V.JOB=e.job
AND V.MGR=e.mgr
AND V.HIREDATE=e.hiredate
AND V.SAL=e.sal
and V.DEPTNO=e.deptno
and V.cnt=e.cnt
and COALESCE(V.COMM,0)=COALESCE(e.comm,0))