SQL多表查询最直接最终.docx
《SQL多表查询最直接最终.docx》由会员分享，可在线阅读，更多相关《SQL多表查询最直接最终.docx（13页珍藏版）》请在冰豆网上搜索。
SQL多表查询最直接最终
SQL多表连接查询总结
1SQL查询的基本原理
1.1单表查询
根据where条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据select的选择列选择相应的列进行返回最终结果。
1.2两表连接查询
对两表求积（笛卡尔积），再用on条件和连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
1.3多表连接查询
先对from后第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
1.4SQL逻辑处理执行顺序
1.4.1T-SQL逻辑查询的各个阶段
（5）SELECTDISTINCTTOP（）
（1）FROMJOINON
（2）WHERE
（3）GROUPBY
（4）HAVING
（6）ORDERBY
1.4.2T-SQL在查询各个阶级分别干了什么
（1）FROM阶段
FROM阶段标识出查询的来源表，并处理表运算符。
在涉及到联接运算的查询中（各种join），主要有以下几个步骤：
a.求笛卡尔积。
不论是什么类型的联接运算，首先都是执行交叉连接（crossjoin），求笛卡儿积，生成虚拟表VT1-J1。
b.ON筛选器。
这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的条件进行筛选，让条件取值为true的行通过了考验，生产VT1-J2。
c.添加外部行。
如果指定了outerjoin，还需要将没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。
经过以上步骤，FROM阶段就完成了。
概括地讲，FROM阶段就是进行预处理的，根据提供的运算符对语句中提到的各个表进行处理（除了join，还有apply，pivot，unpivot）。
（2）WHERE阶段
WHERE阶段是根据中条件对VT1-J3中的行记录进行筛选，让条件成立的行才会插入到VT2中。
（3）GROUPBY阶段
GROUPBY阶段按照指定的列名列表，将VT2中的行进行分组，生成VT3。
最后每个分组只有一行。
（4）HAVING阶段
该阶段根据HAVING子句中出现的条件对VT3的分组进行筛选，并将符合条件的组插入到VT4中。
（5）SELECT阶段
这个阶段是投影的过程，选择所连接的表中的字段（列），产生VT5。
这个步骤一般按下列顺序进行
a.计算SELECT列表中的表达式，生成VT5-1。
b.若有DISTINCT，则删除VT5-1中的重复行，生成VT5-2。
c.若有TOP，则根据ORDERBY子句定义的逻辑顺序，从VT5-2中选择签名指定数量或者百分比的行，生成VT5-3。
（6）ORDERBY阶段
根据ORDERBY子句中指定的列明列表，对VT5-3中的行，进行排序，生成游标VC6。
2多表连接的类型
2.1交叉连接（crossjoin）
交叉连接（CROSSJOIN）：
有两种形式，分为显式的和隐式的，都不带where条件。
返回的是两表的乘积，也叫笛卡尔积。
下面的语句1和语句2的结果是相同的。
2.1.1隐式的交叉连接
隐式的交叉连接，在语句中是没有CROSSJOIN的。
语句1：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefroms,sc;
结果如下：
2.1.2显式的交叉连接
显式的交叉连接，在语句中使用crossjoin。
语句2：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefromscrossjoinsc;
结果如下：
语句1和语句2的结果是相同的。
2.2内连接（innerjoin）
内连接有两种形式，也是分显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。
（所谓的链接表就是数据库在做查询过程中形成的中间表）。
2.2.1隐式的内连接
隐式的内连接，在语句中没有INNERJOIN。
语句3:
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefroms,scwheres.sno=sc.sno;
结果如下：
2.2.2显式的内连接
显式的内连接，一般称为内连接，使用INNERJOIN。
语句4：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefromsinnerjoinscons.sno=sc.sno;
结果如下：
语句3和语句4的结果是相同的。
2.3外连接（outerjoin）
外连接不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。
外连接分三类：
左外连接（LEFTOUTERJOIN）、右外连接（RIGHTOUTERJOIN）和全外连接（FULLOUTERJOIN）。
三者的共同点是都返回符合连接条件和查询条件（即：
内连接）的数据行。
不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行；右外连接还返回右表中不符合连接条件单符合查询条件的数据行；全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外UNION右外”。
左表就是在“（OUTERJOIN）”关键字左边的表，右表当然就是右边的了。
在三种类型的外连接中，OUTER关键字是可省略的。
2.3.1左外连接（leftouterjoin）
语句5：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefromsleftjoinscons.sno=sc.sno;
结果如下：
2.3.2右外连接（rightouterjoin）
语句6：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefromsrightjoinscons.sno=sc.sno;
结果如下：
2.3.4全外连接
全外连接（FULLOUTERJOIN），除了返回符合条件的数据外，还返回左表和右表不符合条件的数据行。
语句7：
selects.sno,s.sname,s.nianl,s.sex,ame,sc.sccre,c.teacherfromsfulljoinscons.sno=sc.snofulljoincono=o;
结果如下：
MySQL是不支持全外连接的，这里给出的写法适合Oracle和DB2。
但是可以通过左外和右外求合集来获取全外连接的查询结果。
下图是MySQL语句在Oracle11g下测试的结果：
语句8：
selects.sno,s.sname,s.nianl,s.sex,ame,sc.sccre,c.teacherfromsleftjoinscons.sno=sc.snoleftjoincono=o
union
selects.sno,s.sname,s.nianl,s.sex,ame,sc.sccre,c.teacherfromsrightjoinscons.sno=sc.snorightjoincono=o;
结果如下：
语句7和语句8的查询结果是相同的。
2.4联合连接（unionjoin）
这是一种很少见的连接方式。
Oracle、MySQL均不支持，其作用是：
找出全外连接和内连接之间差异的所有行。
这在数据分析中排错中比较常用。
也可以利用数据库的集合操作来实现此功能。
2.5自然连接（naturaljoin）
说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的，就给出个例子看看吧。
自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。
不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。
对于每种连接类型（除了交叉连接外），均可指定NATURAL。
下面给出几个例子：
2.5.1内自然连接
语句9：
select*fromsnaturalinnerjoinsc;
结果如下：
2.5.2左自然连接
语句10：
select*fromsnaturalleftjoinsc;
结果如下：
2.5.3右自然连接
语句11：
select*fromsnaturalrightjoinsc;
结果如下：
3on条件和where条件的区别
on条件是过滤两个链接表笛卡尔积形成中间表的约束条件；where条件是在有on条件的查询语句中过滤中间表的约束条件。
在没有on的单表查询中，是限制物理表或者中间查询结果返回记录的约束。
在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将where条件移入on后面是不恰当的。
推荐的做法是：
on只进行连接操作，where只过滤中间表的记录。
语句12：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefromsrightjoinscons.sno=sc.sno
wheresc.sccre=29;
将语句12中的WHERE条件放到ON后面。
语句13：
selects.sno,s.sname,s.nianl,s.sex,sc.sccrefromsrightjoinscons.sno=sc.snoandsc.sccre=29;
结果如下：
从语句12和语句13查询的结果来看，显然是不相同的，语句8显示的结果是难以理解的。
因此，推荐在写连接查询的时候，ON后面只跟连接条件，而对中间表限制的条件都写到WHERE子句中。
4附件：
--附件中内容为数据库脚本。
--
createtables（/*学生表*/
snochar（10）primarykey,
snamechar（10）,
nianlint,
sexchar（10）
）;
createtablesc（/*分数表*/
snochar（10）,
cnochar（10）,
sccreint,
primarykey（sno,cno）
）;
createtablec（/*课程表*/
cnochar（10）primarykey,
cnamechar（10）,
teacherchar（10）
）;
insertintosvalues（'001','张三','18','男'）;
insertintosvalues（'002','李四','16','男'）;
insertintosvalues（'003','王五','19','女'）;
insertintosvalues（'004','周六','15','男'）;
insertintosvalues（'005','张七','17','女'）;
insertintoscvalues（'001','01','98'）;
insertintoscvalues（'002','01','43'）;
insertintoscvalues（'003','01','29'）;
insertintoscvalues（'004','01','45'）;
insertintoscvalues（'006','01','97'）;
insertintoscvalues（'001','02','98'）;
insertintoscvalues（'002','02','45'）;
insertintoscvalues（'003','02','76'）;
insertintoscvalues（'004','02','49'）;
insertintoscvalues（'006','02','77'）;
insertintoscvalues（'001','03','93'）;
insertintoscvalues（'002','03','42'）;
insertintoscvalues（'003','03','77'）;
insertintoscvalues（'004','03','45'）;
insertintoscvalues（'006','03','74'）;
insertintocvalues（'01','语文','刘玉娟'）;
insertintocvalues（'02','数学','周晓'）;
insertintocvalues（'03','英语','屈水初'）;
commit;
升级会员 