多表查询

T-SQL查询语句(三)：多表查询


***************************************
SQL查询语句《三》：多表查询 (也叫连接查询，此处为基于两个表的连接查询)
如果一个查询需要对多个表进行操作就称为连接查询,连接查询的结果集或结果称为表之间的连接.
连接查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系数据库查询最主要的特征.
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 x.cno='101' and x.degree>y.degree and y.sno='9505201' and y.cno='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,y.cno,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,y.cname,x.degree
FROM score x,course y
WHERE x.cno=y.cno
(2)查询“95033”班所选课程的平均分:
SELECT y.cno,avg(y.degree) as "平均分"
FROM student x,score y
WHERE x.class='95033' and x.sno=y.sno group by y.cno
(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,y.cno,y.degree
from student x,score y
where x.sno!=y.sno
     3、自然连接：
在等值连接中消除重复列就是自然连接。
(1)
select x.sno,x.sname,y.cno,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)

***************************************