语法结构
使用一个连接从多个表中查询数据。
• 在 WHERE 子句中写连接条件。
• 当多个表中有相同的列名时，将表名或者表的别名作为列名的前缀。
定义连接
当数据从多表中查询时，要使用连接 (join) 条件。一个表中的行按照存在于相应列中的值被连接到另一个表中的行。
原则
• 在写一个连接表的 SELECT 语句时，在列名前面用表名或者表别名可以使语义清楚，
并且加快数据库访问。
• 为了连接 n 个表在一起，你最少需要 n-1 个连接条件。例如，为了连接 4 个表，
最少需要 3 个连接条件。
4 等值连接
什么等值连接
等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins)。是通过等号来判断连
接条件中的数据值是否相匹配。
抉择矩阵 (decision matrix)
是通过行与列来分析一个查询的方式。
例如，如果你想显示同一个部门中所有名字为 Taylor 的雇员的名字和部门名称，可以
写出下面的决策矩阵：
投影列 源表 条件
last_name employees last_name = ‘Taylor’
department_name departments employees.department_id =
departments.department_id
4.2.1示例一
查询所有雇员名字以及他们所在的部门名称。
select last_name,department_name from
employees , departments where
employees.department_id =
departments.department_id;
4.3使用 AND 操作符附加搜索条件
除连接之外，还可以要求用 WHERE 子句在连接中限制一个或多个表中的行。4.3.1示例二
显示同一个部门中所有名字为 Taylor 的雇员的名字和部门号。
select last_name,department_name from
employees , departments where
employees.department_id =
departments.department_id and last_name =
'Taylor';
4.4使用表别名
使用表别名简化查询语句的长度。
4.4.1表别名
可以使用表别名代替表名。就象列别名给列另一个名字一样。表别名有助于保持 SQL
代码较小，因此使用的存储器也少。
4.4.2使用表别名原则
• 表别名最多可以有 30 个字符，但短一些更好。
• 如果在 FROM 子句中表别名被用于指定的表，那么在整个 SELECT 语句中都可以
使用表别名。
• 表别名应该是有意义的。
• 表别名只对当前的 SELECT 语句有效。
4.4.3示例
使用表别名方式改写显示同一个部门中所有名字为 Taylor 的雇员的名字和部门号。
select em.last_name,de.department_name
from employees em,departments de where
em.department_id = de.department_id and
em.last_name = 'Taylor';.5多于两个表的连接
为了连接 n 个表，你最少需要 n-1 个连接条件。例如，为了连接 3 个表，最少需要
两个连接。
4.5.1示例一
查询每个雇员的 last name、departmentname 和 city(city 来源于 locations 表)。
select
em.last_name,de.department_name ,lo.city - Registered at Namecheap.com
from employees em ,departments de,locations lo
where em.department_id = de.department_id and
de.location_id = lo.location_id;
4.5.2示例二
查询 Taylor 的雇员 ID、部门名称、和工作的城市。
select em.employee_id,
em.last_name,de.department_name ,lo.city - Registered at Namecheap.com
from employees em ,departments de,locations lo
where em.department_id = de.department_id and
de.location_id = lo.location_id and
em.last_name = 'Taylor';
5 非等值连接
5.1非等值连接
一个非等值连接是一种不使用相等(=)作为连接条件的查询。如!=、>、=、<=、
BETWEEN AND 等都是非等值链接的条件判断。表连接语法
3.1语法结构
使用一个连接从多个表中查询数据。
• 在 WHERE 子句中写连接条件。
• 当多个表中有相同的列名时，将表名或者表的别名作为列名的前缀。
3.2定义连接
当数据从多表中查询时，要使用连接 (join) 条件。一个表中的行按照存在于相应列中.2创建案例表
create table JOB_GRADES
(
lowest_sal NUMBER,
highest_sal NUMBER,
gra VARCHAR2(10)
)
5.3示例
查询所有雇员的薪水级别。
select em.last_name,em.salary,gr.gra
from employees em ,job_grades gr where
em.salary between gr.lowest_sal and
gr.highest_sal;
6 自连接
什么是自连接
使用一个表连接它自身的操作。
6.2示例
查询每个雇员的经理的名字以及雇员的名字。select
worker.last_name,manager.last_name from
employees worker,employees manager where
worker.manager_id = manager.employee_id;
7 外连接(OUTER JOIN)
7.1什么是外连接
外连接是指查询出符合连接条件的数据同时还包含孤儿数据。左外链接包含左表的孤儿
数据，右外连接包含右表的孤儿数据，全外连接包含两个表中的孤儿数据。
7.2孤儿数据(Orphan Data)
孤儿数据是指被连接的列的值为空的数据。
7.3外连接类型
左外(LEFT OUTER JOIN)：包含左表的孤儿数据。
右外(RIGHT OUTER JOIN)：包含右表的孤儿数据。
全外(FULL OUTER JOIN)：包含两个表中的孤儿数据。
7.4SQL99 中的外连接
SQL99 外连接语法格式：
用 LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN 定义连接类型，用
ON 子句创建连接条件。
7.4.1左外链接(LEFT OUTER JOIN)
7.4.1.1 示例
用左外链接查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员。
select e.last_name,d.department_name from
employees e left outer join departments d on
e.department_id = d.department_id;7.4.2右外链接(RIGHT OUTER JOIN)
7.4.2.1 示例
用右外链接查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门。
select e.last_name,d.department_name from
employees e right outer join departments d on
e.department_id = d.department_id;
全外链接(FULL OUTER JOIN)
示例
查询所有雇员和部门，包含那些没有雇员的部门以及没有部门的雇员。
select e.last_name,d.department_name from
employees e full outer join departments d on
e.department_id = d.department_id;
Oracle 扩展的外连接
在 Oracle 数据库中对外连接中的左外与右外连接做了扩展，可以简化外连接的语法。
通过在连接条件的后侧使用(+)来表示是否显示孤儿数据，有(+)表示不显示孤儿数据而另一
侧则显示孤儿数据。但是该种写法仅能在 Oracle 数据库中使用。
示例一
查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门。
select e.last_name,d.department_name from
employees e ,departments d where
e.department_id(+) = d.department_id;
示例二
查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员。select e.last_name,d.department_name from
employees e ,departments d where
e.department_id = d.department_id(+);
8 SQL99 中的交叉连接
• CROSS JOIN 子句导致两个表的交叉乘积
• 该连接和两个表之间的笛卡尔乘积是一样的
示例
查询 Employees 表与 Departments 表的笛卡尔乘积。
select * from employees cross join
departments;
9 SQL99 中的自然连接(NATURAL JOIN)
• NATURAL JOIN 子句基于两个表之间有相同名字的所有列。
• 它从两个表中选择在所有的匹配列中有相等值的行。
• 如果有相同名字的列的数据类型不同，返回一个错误。
使用自然连接需要注意
1.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为
自然连接的条件。
2.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。
3.由于 oracle 中可以进行这种非常简单的 natural join，我们在设计表时对具有相同含
义的字段需要考虑到使用相同的名字和数据类型。
示例
查询部门 ID，部门名称以及他们所在的城市。
selectfrom employees e inner join departments d on
e.department_id = d.department_id inner join
locations l on d.location_id = l.location_id
where e.employee_id = 202;
在内连接中使用 USING 子句定义等值连接
select e.last_name,d.department_name,LOST&FOUND
from employees e inner join departments d
using(department_id) inner join locations l
using(location_id) where e.employee_id = 202;
epartment_id,d.department_name,LOST&FOUND
from departments d natural join locations l;
用 USING 子句创建连接
• 当有多个列匹配时，用 USING 子句匹配唯一的列。
• 如果某列在 USING 中使用，那么在引用该列时不要使用表名或者别名。
• NATURAL JOIN 和 USING 子句是相互排斥的。
示例
查询 location_id 为 1800 的部门名称以及他们所在的城市名称，指定 location_id 为连接
列。
select d.department_name,LOST&FOUND from
departments d join locations l
using(location_id) where location_id = 1800;
SQL99 中的内连接(INNER JOIN)
内连接(INNER JOIN)： 内连接通过 INNER JOIN 来建立两个表的连接。在内连接中使
用 INNER JOIN 作为表的连接，用 ON 子句给定连接条件。INNER JOIN 语句在性能上其他
语句没有性能优势。
10.1示例
查询雇员 id 为 202 的雇员名字，部门名称，以及工作的城市。
等值连接：
select e.last_name,d.department_name,LOST&FOUND
from employees e,departments d ,locations l
where e.department_id = d.department_id and
d.location_id = l.location_id and
e.employee_id = 202;co
内连接：
select e.last_name,d.department_name,LOST&FOUND

92语法

–关联查询

/*

select t1.c1,t2.c2 from t1,t2 where t1.c3 = t2.c4

在进行连接的时候，可以使用等值连接，可以使用非等值连接
*/

–等值连接，两个表中包含相同的列名

–非等值连接，两个表中没有相同的列名，但是某一个列在另一张表的列的范围之中

–查询雇员的名称和部门的名称

select ename,dname from emp,dept where emp.deptno = dept.deptno;–等值连接

–查询雇员名称以及自己的薪水等级

select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;—非等值连接
–外连接

select * from emp;

select * from dept;

–需要将雇员表中的所有数据都进行显示,利用等值连接的话只会把关联到的数据显示，

–没有关联到的数据不会显示，此时需要外连接

–分类：左外连接（把左表的全部数据显示）和右外连接（把右表的全部数据显示）

select * from emp e,dept d where e.deptno = d.deptno；–等值连接

select * from emp e,dept d where e.deptno = d.deptno(+);–左外连接

select * from emp e,dept d where e.deptno(+) = d.deptno;–右外连接

–自连接,将一张表当成不同的表来看待，自己关联自己

–将雇员和他经理的名称查出来

select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

–笛卡尔积,当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，

–关联后的总记录条数为M*n，一般不要使用

select * from emp e,dept d;
–92的表连接语法有什么问题？？？？

–在92语法中，多张表的连接条件会放到where子句中，同时where需要对表进行条件过滤

–因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法
99语法


–99语法

/*

CROSS　JOIN

NATURAL JOIN

USING子句

ON子句

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Inner join
*/

–cross join 等同于92语法中的笛卡儿积

select * from emp cross join dept;
–natural join  相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接

–当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系

select * from emp e natural join dept d ;

select * from emp e natural join salgrade sg;
–on子句，可以添加任意的连接条件，

–添加连接条件 相当于92语法中的等值连接

select * from emp e join dept d on e.deptno = d.deptno;

–相当于92语法中的非等值连接，

select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
–left outer join ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可

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

select * from emp e,dept d where e.deptno = d.deptno(+);
–right outer join ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可

select * from emp e right outer join dept  d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno(+) = d.deptno;
–full outer join ,相当于左外连接和右外连接的合集

select * from emp e full outer join dept d on e.deptno = d.deptno;
–inner join，两张表的连接查询，只会查询出有匹配记录的数据

select * from emp e inner join dept d on e.deptno = d.deptno;

select * from emp e join dept d on e.deptno = d.deptno;—与上一条等价
–**using,**除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表，只出现一次，

select deptno from emp e join dept d using(deptno);

select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;
–总结:两种语法的SQL语句没有任何限制，再公司中可以随意使用，但是建议使用99语法，不要使用92语法，SQL显得清楚明了
练习：

–检索雇员名字、所在单位、薪水等级

select e.ename, d.loc, sg.grade

from emp e

join dept d

on e.deptno = d.deptno

join salgrade sg

on e.sal between sg.losal and sg.hisal;

/*

子查询：

嵌套再其他sql语句中的完整sql语句，可以称之为子查询

分类：

单行子查询

多行子查询

分开步骤一步一步写，如下：

*/

–有哪些人的薪水是在整个雇员的平均薪水之上的

–1、先求平均薪水

select avg(e.sal) from emp e;

–2、把所有人的薪水与平均薪水比较

select * from emp e where e.sal > (select avg(e.sal) from emp e);
–我们要查在雇员中有哪些人是经理人

–1、查询所有的经理人编号

select distinct e.mgr from emp e;

–2、再雇员表中过滤这些编号即可

select * from emp e where e.empno in (select distinct e.mgr from emp e);
–每个部门平均薪水的等级

–1、先求出部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

–2、跟薪水登记表做关联，求出平均薪水的等级

select t.deptno, sg.grade

from salgrade sg

join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t

on t.vsal between sg.losal and sg.hisal;
–1、求平均薪水最高的部门的部门编号

–求部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

–求平均薪水最高的部门

select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t

–求部门编号

select t.deptno

from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t

where t.vsal =

(select max(t.vsal)

from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);
–2、求部门平均薪水的等级
–3、求部门平均的薪水等级

–求部门每个人的薪水等级

select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal;

–按照部门求平均等级

select t.deptno, avg(t.grade)

from (select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal) t

group by t.deptno;
–限制输出，limit，mysql中用来做限制输出的，但是oracle中不是

–在oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，

–但是rownum不能直接使用，需要嵌套使用，需要一个虚拟字段


–4、求薪水最高的前5名雇员

select *

from (select * from emp e order by e.sal desc) t1

where rownum <= 5

select * from emp e  where rownum <=5 order by e.sal desc
–5、求薪水最高的第6到10名雇员

select t1.,rownum

from (select * from emp e order by e.sal desc) t1

where rownum <= 10

–使用rownum的时候必须要再外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出

select *

from (select t1., rownum rn

from (select * from emp e order by e.sal desc) t1

where rownum <= 10) t

where t.rn >  5

and t.rn <= 10;

或者

select *

from (select t1.*, rownum rn

from (select * from emp e order by e.sal desc) t1) t

where t.rn > 5

and t.rn <= 10;

联接条件可在FROM或WHERE子句中指定，建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选联接条件所选的行。    

联接可分为以下几类：    

1、内联接（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。     

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   

    
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     

在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     
1）LEFT  JOIN或LEFT OUTER JOIN
    

左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       
2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN
    

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       
3）FULL  JOIN 或 FULL OUTER JOIN

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   

    
3、交叉联接   

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    


FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。     

    
例子：   
-------------------------------------------------

  a表     id   name     b表     id   job   parent_id   

              1   张3                   1     23     1   

              2   李四                 2     34     2   

              3   王武                 3     34     4       

  a.id同parent_id   存在关系   
--------------------------------------------------    
 1） 内连接   

  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       

  结果是     

  1   张3                   1     23     1   

  2   李四                  2     34     2   

    

  2）左连接   

  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       

  结果是     

  1   张3                   1     23     1   

  2   李四                  2     34     2   

  3   王武                  null   
 
 3） 右连接   

  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       

  结果是     

  1   张3                   1     23     1   

  2   李四                  2     34     2   

  null                       3     34     4   

    

 4） 完全连接   

  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   
  结果是     

  1   张3                  1     23     1   

  2   李四                 2     34     2   

  null               　　  3     34     4   

  3   王武                 null

最近遇到的一些问题总结：
1. mysql数据库同一张表做四次左连接查询数据冗余。
a. mysql数据库连接查询
b. mysql表数据去重


2. mybatis查询相同列赋值重复问题。
a. 使用mybatis强大的resultMap
b. mysql数据查询别名


数据库表为


Create Table


CREATE TABLE `STUDENT_SCORE` (

  `score_id` int(11) unsigned NOT NULL AUTO_INCREMENT,//主键

 `test_id` int(11) NOT NULL,//标识考试信息

  `course` varchar(45) DEFAULT NULL,//课程名

  `score` float DEFAULT NULL,//分数

  PRIMARY KEY (`score_id`)

) 


现在想要查询近四次考试改名同学的考试情况对比数据，查询语句如下：


SELECT score0.test_id,  core0.course, score0.score, score1.score, score2.score, score3.score
FROM (SELECT * FROM STUDENT_SCORE WHERE test_id='11') score0 
LEFT JOIN (SELECT * FROM STUDENT_SCORE WHERE test_id='12') score1
ON score0.course = score1.course

LEFT JOIN (SELECT * FROM STUDENT_SCORE WHERE test_id='13') score2

ON score0.course = score2.course

LEFT JOIN (SELECT * FROM STUDENT_SCORE WHERE test_id='14') score3
ON score0.course = score3.course


使用相同的sql语句写入mybatis的xml中：

<select id="findVCCFrameworkCompareData" parameterType="java.util.HashMap" resultMap="frameworkCompareMapper">
      SELECT 
      score0.test_id,  
      core0.course, 
      score0.score, 
      score1.score, 
      score2.score, 
      score3.score
      FROM (SELECT * FROM STUDENT_SCORE WHERE test_id=#{id0}) score0 
      LEFT JOIN (SELECT * FROM STUDENT_SCORE WHERE test_id=#{id1}) score1
      ON score0.course = score1.course
      LEFT JOIN (SELECT * FROM STUDENT_SCORE WHERE test_id=#{id2}) score2
      ON score0.course = score2.course
      LEFT JOIN (SELECT * FROM STUDENT_SCORE WHERE test_id=#{id3}) score3
      ON score0.course = score3.course
</select>

以上查询执行结果全部未test_id=id0时的数据，将score1.test_id与score2.test_id添加至查询结果发现四个id值全部都为id0。
推测由于使用同一个表进行连接查询，列名相同，赋值时赋了相同的值。
在网上搜索有看到通过在resultMap中配置<collection>和<asociation>解决此问题。
尝试配置resultMap未果。
通过给表查询添加别名将以上查询语句修改如下，解决参数赋值冲突的问题。

SELECT
score0.test_id,
core0.course,
score0.score,
score1.score,
score2.score,
score3.score
FROM (SELECT * FROM STUDENT_SCORE score0 WHERE score0.test_id=#{score0}) score0
LEFT JOIN (SELECT * FROM STUDENT_SCORE score1 WHERE score1.test_id=#{score1}) score1
ON score0.course = score1.course
LEFT JOIN (SELECT * FROM STUDENT_SCORE score2 WHERE score2.test_id=#{score2}) score2
ON score0.course = score2.course
LEFT JOIN (SELECT * FROM STUDENT_SCORE score3 WHERE score3.test_id=#{score3}) score3
ON score0.course = score3.course




总结：
1、处理问题时力求正确简洁，切记多个问题混杂在一起影响问题定位与解决思路。
2、在进行多个表查询时，尽量使用别名对表进行重命名加以区分。
3、使用group by去重，后面跟哪一列对哪一列去重。