mysql整理7-高级查询分类整理

高级查询分类：

关联查询（连接查询）
    交叉连接（笛卡尔积 cross join）
    内连接
    外连接
                左外连接
                右外连接
                全外连接
    自然连接(natural join)
    自连接
子查询（嵌套查询）
    单行子查询
    多行子查询
联合查询（结果集连接union（去重）,union all）
    用union实现交集并集差集
    利用group by id 进行分组
    利用having count(id) = 1
        利用是否等于1判断是差集（=1）还是 交集（！=1）
        a 差 b： 先查询b中id然后利用not in 来查询a

a)关联查询(连接查询)

i.内连接

select ename,dname from emp,dept where emp.deptno = dept.deptno;
select * from emp inner join dept on emp.deptno = dept.deptno;
select * from emp inner join dept using(deptno); 

注意：

 通用列字段名称必须一致;去除重复字段
 关联表中都出现的字段值最终才能出现在结果集中；
 内连接与连接顺序无关，没有主从表之分。

ii.外连接

有主从表之分，与连接顺序有关。
依次遍历主表中记录，与从表中记录进行匹配；如果匹配到则连接展示，否则以null填充。
1.左(右)外连接 left/right [outer] join …on

select * from emp left join dept on emp.deptno = dept.deptno;

问题出现null怎样办？join出现null的解决办法

iii.自然连接

(自然连接肯定是等值连接，但等值连接不一定是自然连接)

select * from emp NATURAL join dept;

iv.自连接

自己连接自己的表

b)子查询(嵌套查询)

嵌套查询：将一个查询结果当做另一个查询的条件或者结果集。
子查询最接近思考方式，最自然的查询。

1)单行子查询:子查询的返回结果只有一条记录。

select * from emp where sal > (select sal from emp where ename = 'scott');

2)多行子查询:子查询返回结果有多条记录

select * from emp where sal in ( select distinct sal from emp where deptno = 20) and deptno <>20;

any all

=any:相当于in    >any:大于最小值    <any:小于最大值
>all:大于最大值   <all小于最小值

c)联合查询(索引:or导致索引失效)

union/union all
union:去重

注意：
联合的结果集必须一致。

下面的例子中使用的原始表：
Employees_China:
sql脚本代码如下:

E_ID E_Name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming

Employees_USA:
sql脚本代码如下:

E_ID E_Name
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill

使用 UNION 命令实例
列出所有在中国和美国的不同的雇员名：
sql脚本代码如下:

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果：
sql脚本代码如下:

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill