-- OUTER JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK
A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL
(11 row(s) affected)
高级Join类型
LEFT JOIN EXCLUDING INNER JOIN
选择A与B中，A没有与B有交集的部分
1 2 3 4 5 6 7 8 9 10 11 12 13 14
-- LEFT EXCLUDING JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL
-- RIGHT EXCLUDING JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL
A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11
(3 row(s) affected)
OUTER JOIN EXCLUDING INNER JOIN
选择A与B中，A没有与B有交集的部分和B与A没有交集的部分
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
-- OUTER EXCLUDING JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL
select e.empno, e.name, e.job, e.sal, e.deptno from emp e, (select ename, job, sal from emp where job = "CLERK") V where V.ename = e.ename and V.job = e.job and V.sal = e.sal
Join的处理手法
1 2 3 4 5
select e.empno, e.name, e.job, e.sal, e.deptno from emp e join (select ename, job, sal from emp where job = "CLERK") V on ( V.ename = e.ename V.job = e.job V.sal = e.sal)
查询只存在于一个表中的数据
一般来说，直接使用not in 就可以了。但是对于如果含有Null的数据，就不能直接使用这样的方法处理。 那为什么null的数据就会出现问题呢？这个就要看一下他可能的实现方式 对于Mysql的实现， not in 和 in 本质上是 or的关系运算。 由于null 参与Or的逻辑运算方式不一致，In 和Not in 将产生不同的结果。
// 处理存在于EMP 不存于v的查询 select * from ( select e.empno, e.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) ) )
// 处理存在于V 不存于EMP的查询 select * 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 not exists ( select null from ( select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno, count(*) as cnt from v group by empno, ename, job, mgr, hiredate, sal, comm, deptno) e 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) ) )
// 总体 select * from ( select e.empno, e.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) ) ) Unoin all select * 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 not exists ( select null from ( select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno, count(*) as cnt from v group by empno, ename, job, mgr, hiredate, sal, comm, deptno) e 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) ) )
select x.name from ( select a.ename , ( select count(*) from emp b where b.ename <= a.ename ) as rn from emp a ) x where mod(rn,2) = 1
外查询使用OR逻辑
先去Join表，然后再去进行Or的逻辑判断
1 2 3 4
select e.ename, d.deptno , d.dname, d.loc from dept d left join emp e on (d.deptno = e.deptno and (e.deptno=10 or e.deptno=20)) order by 2
先创建一个中间表，然后再去进行Join的操作
select e.ename, d.deptno , d.dname, d.loc from dept d left join (select * from emp e where e.deptno=10 or e.deptno=20) on d.deptno = e.deptno order by 2
对单表需要做数据运算情况
情况1： 找出互逆的记录（本例） 情况2： 查找表中某列1相差为1，并且某列2差为5的记录
总体的思路，把自己与自己(或者与自己的子集)求笛卡尔积，然后去进行条件的筛选
1 2 3 4
select distinct v1.* from V v1, V v2 where v1.test1 = v2.test2 and v1.test2 = v2.test1 and v1.test1 <= v1.test2
找出最靠前的N条记录
此处使用了标量子查询来创建了一张临时表的RNK的列
1 2 3 4 5 6 7 8 9
// select ename, sal from ( select ( select (count(distinct b.sal) from emp b where a.sal <= b.sal) as rnk, a.sal, a.ename ) from emp a ) where rnk <=5
OrderBy
基础查询
1 2 3 4
// 升序查询 select * from emp order by col2 asc; // 降序查询 select * from emp order by col2 desc;
多字段排序
1
select empno, deptno, sal, ename, job from emp order by deptno (asc), sal desc;
动态排序
1 2 3 4
select ename, sal, job, comm from emp order by case when job = "salesman" then comm else sal end;
update
基础语法
1
update table name set col_name = xxx where $cond
delete
基础语法
1
delete from table_name where $cond
删除重复记录
1
delete from table where id not in (select min(id) from table group by name)
Having & GroupBy
1 2 3 4
wiki原文 A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added to the SQL language because the WHERE keyword could not be used with aggregate functions. The HAVING clause filters the data on the group row but not on the individual row. To view the present condition formed by the GROUP BY clause, the HAVING clause is used.
Having的语句是必须要在GroupBy后面才能使用。并且与Where的区别是，Where不能直接接入聚合的函数（如Sum()、Count()、Avg()） 这种的聚合函数， 意思是不能 where sum(column_a) 这样的用法）, 并且Having可以对按Group区分的Row进行过滤的操作
所以常规语法一般是
1
select * from table_a A group by columa_a having count (A.column_a ) > 200
特殊注意
类似于Sum， max， min , avg 这些也是可以直接用于select 的条件上面的
1
select max(Salary) as SecondHighestSalary from employee where salary<(select max(distinct(salary)) from employee)
sql 三元运算符 if (expr1, expr2, expr3) 跟正常编程语言中的三元运算符一致，只是语法有变动。也是满足条件一，则返回expr2，否则返回expr3
