mysql的多表操作查询

超详细mysql left join,right join,inner join用法分析 下面是例子分析

表A记录如下：

aID aNum

1 a20050111

2 a20050112

3 a20050113

4 a20050114

5 a20050115

表B记录如下:

bID bName

1 2006032401

2 2006032402

3 2006032403

4 2006032404

8 2006032408

创建这两个表SQL语句如下：

CREATE TABLE a

aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,

aNum char( 20 )

)

CREATE TABLE b(

bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , bName char( 20 )

)

INSERT INTO a

VALUES ( 1, ‘a20050111′ ) , ( 2, ‘a20050112′ ) , ( 3, ‘a20050113′ ), ( 4, ‘a20050114′ ) , ( 5, ‘a20050115′ ) ; INSERT INTO b

VALUES ( 1, ‘ 2006032401′ ) , ( 2, ’2006032402′ ) , ( 3,’2006032403′ ) , ( 4, ’2006032404′ ) , ( 8, ’2006032408′ ) ; 实验如下:

1.left join(左联接)

sql语句如下:

SELECT * FROM a

LEFT JOIN b

ON a.aID =b.bID

结果如下:

aID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

4 a20050114 4 2006032404

5 a20050115 NULL NULL

（所影响的行数为 5 行）

结果说明:

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID =B.bID).

B表记录不足的地方均为NULL.

2.right join(右联接)

sql语句如下:

SELECT * FROM a

RIGHT JOING b

ON a.aID = b.bID

结果如下:

aID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

NULL NULL 8 2006032408

（所影响的行数为 5 行）

结果说明:

仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join(相等联接或内联接)

sql语句如下:

SELECT * FROM a

INNER JOIN b

ON a.aID =b.bID

等同于以下SQL句:

SELECT *

FROM a,b

WHERE a.aID = b.bID

结果如下:

aID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

4 a20050114 4 2006032404

结果说明:

很明显,这里只显示出了 A.aID = B.bID的记录.这说明innerjoin并不以谁为基础,它只显示符合条件的记录.

LEFT JOIN操作用于在任何的 FROM 子句中，

组合来源表的记录。使用 LEFT JOIN运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即

使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compoprtable2.field2

说明：table1, table2参数用于指定要将记录组合的表的名称。 field1,field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的 名称。

compopr 参数指定关系比较运算符： = ， < ， > ， <= ， >= 或 <> 。

如果在 INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误。

MySQL中的各种JOIN

1. 笛卡尔积(交叉连接)

在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如

SELECT * FROM table1 CROSS JOIN table2

SELECT * FROM table1 JOIN table2

SELECT * FROM table1,table2

由于其返回的结果为被连接的两个数据表的乘积，因此当有

WHERE,ON或USING条件的时候一般不建议使用，因为当数据表项目太多的时候，会非常慢。

一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

2. 内连接INNER JOIN

在MySQL中把INNER JOIN叫做等值连接，即需要指定等值连接条件 在MySQL中CROSS和INNER JOIN被划分在一起，不明白。 参看MySQL帮助手册 http:///doc/refman/5.0/en/join.html

join_table:

table_reference [INNER | CROSS] JOIN

table_factor[join_condition]

3. MySQL中的外连接，分为左外连接和右连接，

即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果，相对应的使用NULL对应。 a. LEFT [OUTER] JOIN

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ONtable1.column=table2.column

除了返回符合连接条件的结果之外，还需要显示左表中不符合连接条件的数据列，相对应使用NULL对应

b. RIGHT [OUTER] JOIN

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ONtable1.column=table2.column

RIGHT与LEFTJOIN相似不同的仅仅是除了显示符合连接条件的结果之外，还需要显示右表中不符合连接条件的数据列，相应使用NULL对应

--------------------------------------------

添加显示条件WHERE, ON, USING

1. WHERE子句

2. ON

3. USING子句，如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING

例如

SELECT FROM LEFT JOIN USING ()

连接多余两个表的情况

举例：

mysql> SELECT artists.Artist,cds.title, genres.genre -> FROM cds

-> LEFT JOIN genres

-> ON cds.genreID = genres.genreID

-> LEFT JOIN artists

-> ON cds.artistID = artists.artistID;

或者

mysql> SELECT artists.Artist, cds.title,genres.genre -> FROM cds

-> LEFT JOIN genres

-> ON cds.genreID = genres.genreID

-> LEFT JOIN artists

-> ON cds.artistID = artists.artistID

-> WHERE (genres.genre = 'Pop');

--------------------------------------------

另外需要注意的地方

在MySQL中涉及到多表查询的时候，需要根据查询的情况，想好使用哪种连接方式效率更高。

1. 交叉连接(笛卡尔积)或者内连接

[INNER | CROSS] JOIN

2. 左外连接LEFT [OUTER] JOIN或者右外连接RIGHT [OUTER] JOIN 注意指定连接条件WHERE, ON，USING.

查询语句《三》：多表查询 (也叫连接查询，此处为基于两个表的连接查询)

如果一个查询需要对多个表进行操作就称为连接查询,连接查询的结果集或结果称为表之间的连接.

连接查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系数据库查询最主要的特征.

select 表1.字段名1，表2.字段名2，...

from 表1，表2

where 连接条件

SQL-92标准所定义的FROM子句的连接语法格式为：

FROM 表名 join_type 表名 [ON (连接条件)]

连接操作中的ON (连接条件) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构

成。

连接查询分类：

1.自连接查询，对同一个表进行连接操作

2.内连接查询，<又分为：自然连接、等值连接、不等值连接三种>

3.外连接查询，<又分为：左外连接、右外连接、全外连接三种>

4.交叉连接查询，也作无条件查询。

5.联合查询 ----------------------------

一。自连接查询:

一个表自己与自己建立连接称为自连接或自身连接。

进行自连接就如同两个分开的表一样，可以把一个表的某一行与同一表中的另一行连接起来。

例：

查询选学“101”课程的成绩高于“9505201”号学生成绩的所有学生记录， 并按成绩从高到低排列。

select x.* from sclass x,sclass y

where http://o=''101'' and x.degree>y.degree and y.sno=''9505201'' and http://o=''101''

order by x.degree desc

----------------------------

二。内连接(INNER JOIN):

内连接是最常用的一种连接方式,它只返回两个数据集合之间匹配关系的那些行. 将位于两个互相交叉的数据集合中重叠部分以内的那些数据行连接起来. 内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，

并列出这些表中与连接条件相匹配的数据行。

根据所使用的比较方式不同，内连接查询操作列出与连接条件匹配的数据行， 它使用比较运算符比较被连接列的列值。

语法：

select 字段名列表 from 表名 [inner] join 表名 on 连接条件 [where 条件表达式]

例：

select 学生表.学号,学生表.姓名,学生表.班级代号,成绩表.课程代号,成绩表.课程成绩

from 学生表 INNER JOIN 成绩表

on 学生表.学号=成绩表.学号

where 学生表.班级代号=''200201''

select x.sno,x.sname,http://o,y.degree from student x

INNER join sclass y on x.sno=y.sno where x.sclass=''95文秘1'' 内连接分三种：

1、等值连接：

所谓等值连接，是指表之间通过“等于”关系连接起来，产生一个临时表，

然后对该临时表进行处理后生成最终结果。其查询结果中列出被连接表中的所有列，

包括其中的重复列。

(1)查询所有学生的sno,cname,degree列:

SELECT x.sno,http://ame,x.degree

FROM score x,course y

WHERE http://o=http://o

(2)查询“95033”班所选课程的平均分:

SELECT http://o,avg(y.degree) as "平均分"

FROM student x,score y

WHERE x.class=''95033'' and x.sno=y.sno group by http://o

(3)查询所有学生的student,score表信息：

select x.*,y.* from student x,score y where x.sno=y.sno

用等值连接列出authors和publishers表中位于同一城市的作者和出版社： SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city

2、不等值连接：

是指表之间的连接关系不是“等于”，而是其它关系。

这些运算符包括>、>=、<=、<、!>、!<和<>。

(1)查询所有学生的sno,cno,rank列:

select sno,cno,rank

from score,grade

where degree between low and upp order by rank

(2)

select x.sno,x.sname,http://o,y.degree

from student x,score y

where x.sno!=y.sno

3、自然连接：

在等值连接中消除重复列就是自然连接。

(1)

select x.sno,x.sname,http://o,y.degree

from student x,score

where x.sno=y.sno

用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：

SELECT a.*?p.pub_id?p.pub_name?p.country

FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city ----------------------------

三。外连接(OUTER JOIN):

与内连接不同的是，外连接不只列出与连接条件相匹配的行，

而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时) 中所有符合搜索条件的数据行。

外连接是对内连接的扩充,除了将两个数据集合中重叠部分以内的那些数据行连接起来之外,

还可以根据要求返回左侧或右侧数据集合中非匹配的数据或全部的数据. 即 左外连接(LEFT OUTER JOIN);

右外连接(RIGHT OUTER JOIN);

全部连接(FULL OUTER JOIN).

外连接返回的结果集中的一些数据看起来和内连接返回的数据完全一样,

但有一些数据也与内连接返回的数据不同,这些数据行的某些部分是来自于表中的数据，

另一部分是NULL值,产生这些NULL值的原因是因为另一表中不包含与指定表相匹配的数据项

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和

连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，

而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

注： 外连接强以使不满足条件的记录也出现在结果集中。

外连接语法：

Select 字段名列表 from 表名 Left|Right|Full [Outer] join 表名 ON 连接条件

外连接分为:

1。左外连接(LEFT OUTER JOIN或LEFT JOIN)：

在结果表中包含第一个表中满足条件的所有记录。

如果是在连接条件上匹配的记录，则第二个表返回相应值，否则第二个表返回空值。

select 学生表.学号,学生表.姓名,成绩表.课程代号,成绩表.课程成绩 from 学生表 left outer join 成绩表

on 学生表.学号=成绩表.学号

2。右外连接(RIGHT OUTER JOIN或RIGHT JOIN)：

在结果表中包含第二个表中满足条件的所有记录。

如果是在连接条件上匹配的记录，则第一个表返回相应值，否则第一个表返回空值。

select 学生表.学号,学生表.姓名,成绩表.课程代号,成绩表.课程成绩 from 学生表 right outer join 成绩表

on 学生表.学号=成绩表.学号

3。全外连接(FULL OUTER JOIN或FULL JOIN):

在结果表中包含两个表中满足条件的所有记录。

如果是在连接条件上匹配的元组，则另一个表返回相应值，否则另一个表返回空值。

select 学生表.学号,学生表.姓名,成绩表.课程代号,成绩表.课程成绩 from 学生表 full outer join 成绩表

on 学生表.学号=成绩表.学号

例子：

SELECT a.*,b.* FROM student as a left JOIN sclass as b

ON a.sno=b.sno and a.sno=''9502101''

go

SELECT a.*,b.* FROM student as a right JOIN sclass as b

ON a.sno=b.sno and a.sno=''9502101''

go

SELECT a.*,b.* FROM student as a full JOIN sclass as b

ON a.sno=b.sno

go

----------------------------

四。交叉连接:

交叉连接不使用任何连接条件来限制结果集合,将各表的记录以“笛卡尔”积的方式组合起来,

是分别使用两个数据源中的行以所有可能的方式进行组合,

即数据集中的每一行都要与另一表每一行组成一个新的行.

例如:一表中有三条记录,另一表有4条记录,交叉连接后,结果集合将由12条记录组成.

交叉连接(CROSS JOIN)没有WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，

返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中

符合查询条件的数据行数。

例，

titles表中有6类图书，而publishers表中有8家出版社，

则下列交叉连接检索到的记录数将等于6*8=48行。

select * from student,sclass

SELECT * FROM student a CROSS JOIN sclass ORDER BY a.sno ----------------------------

五。联合查询

UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，

即执行联合查询。

UNION的语法格式为：

select_statement

UNION [ALL] selectstatement

[UNION [ALL] selectstatement][…n]

其中selectstatement为待联合的SELECT查询语句。

ALL选项表示将所有行合并到结果集合中。不指定该项时，

被联合查询结果集合中的重复行将只保留一行。

联合查询时，查询结果的列标题为第一个查询语句的列标题。

因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时， 也必须使用第一查询语句中的列名、列标题或者列序号。

在使用UNION 运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，

并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型

。

在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。

在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。

例如：

查询1 UNION (查询2 UNION 查询3)

select topicbody,posttime from bbs_topic

union all

select replybody,posttime from bbs_reply

----------------------------

六。复杂查询

---复合连接

select a.学号,a.姓名,b.课程代号,b.课程成绩,c.课程名称,d.教师代号 from 学生表 a,成绩表 b,课程表 c,教学表 d

where (a.学号=b.学号)

and (b.课程代号=c.课程代号)

and(c.课程代号=d.课程代号)

三个表以上的连接

SELECT dbo.kb.xq, dbo.kbk.kcmc, dbo.kbk.lbdh, dbo.kbk.jsmc, dbo.kb.jse, dbo.bj.bj,

dbo.kb.jc, 2 AS num, dbo.kb.zc,

CASE dbo.kb.ds WHEN ''单'' THEN ''1'' WHEN ''双'' THEN ''2'' WHEN '' '' THEN ''0'' END AS ds,

dbo.kb.zc1, dbo.kb.zc2

FROM dbo.kb INNER JOIN

dbo.bj ON dbo.kb.bh = dbo.bj.bh INNER JOIN

dbo.kbk ON dbo.kb.xq = dbo.kbk.xq AND dbo.kb.bh = dbo.kbk.bh AND

dbo.kb.kcdm = dbo.kbk.kcdm

WHERE (dbo.kb.jc = 1) OR

(dbo.kb.jc = 3) OR

(dbo.kb.jc = 5) OR

(dbo.kb.jc = 7) OR

(dbo.kb.jc = 9) OR

(dbo.kb.jc = 11)

----------------------------

总结：

无论哪种连接都不能对text、ntext和image数据类型列进行直接连接， 但可以对这三种列进行间接连接。

例如：

SELECT p1.pub_id?p2.pub_id?p1.pr_info

FROM pub_info AS p1 INNER JOIN pub_info AS p2

ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)