SQL 多表连接

有时数据是存储在不同的多个表中  但是表之间存在相互的关联关系,通过多表连接的方式取数据！

多表连接的基本原则

1.select 子句列表中  每个目标列前都要加基表名称。

2.from 子句应包含所有使用的基表。

3. where 子句应顶一个一个同等连接。

 

2.内连接

 select   *   from  table1   inner join （在表1和表2之间使用join关键字）    

 table2    on (连接条件 一般是等值的条件）   相当与where条件  

on 关键字后面可以写关键字   where  条件

 

 

 3.左外连接  left join

  返回主表查询的所有行  从表匹配主表的行 如果符合条件返回 不符合返回Null

  第一个表是主表  第二个表是从表

 

 

如：   第三行数据并不匹配  

student                                        users

  

select a.name,a.age,b.sex from luser a left join 
dbo.student b  on a.name=b.name
--user表和student表中的name列有相等关系
--外连接会返回主表user表中所有的name行 从表student根据条件取匹配
--如果没有匹配值则返回null

左外连接语句

select a.name,a.age,b.sex from luser a  join 
dbo.student b  on a.name=b.name
--返回所有匹配条件的数据行  不满足条件不返回

内连接语句

select a.name,a.age,b.sex from luser a join
dbo.student b on a.name=b.name 

on 后面 可以加多个条件  用 and隔开  and a.ge>=30  执行结果会进行数据上的匹配

从表不满足返回null

也可以在后面加where 但是where 是在前面所有语句执行后的结果上执行

所以显示的是 另一种结果 where a.age >=30  

两种执行结果：

       

4.右外连接  right join

逻辑相反,右侧的是主表student    前面的luser是从表     从表和主表向匹配  返回主表student的所有行,  

从表匹配则返回值  不匹配条件返回null

select a.name,a.age,b.sex from luser a right join student b on a.name=b.name 

和做外连接实现相同 只不过是可以把主表和从表互换下位置。 

如：返回的结果和左外连接相同~

select a.name,a.age,b.sex from student b right join luser a on a.name=b.name 

 5.全外连接  full join

 不仅匹配返回主表的所有数据  也返回从表要查询的的所有数据

 显示的顺序是：先返回 from 子句的第一个表的所有数据  在返回相匹配的 从表的所有数据  

 再返回所有不匹配的从表数据

  select a.name,a.age,b.sex from luser a full join student b on a.name=b.name 

 

 6.交叉连接  cross join

 

交叉连接和基本连接基本相同  加上where 就和基本连接一样返回数据

 

 7.自连接  在同一张数据表上进行连接

 

查询同一张表 只是不同的虚拟的表名不能相同

 

 

 8.联合查询

 union 关键字过滤重复的结果  union all 重复的内容也显示

 作用是将多个查询的结果  放到一个结果集中。

 多个select的结果合并到一个结果集中

 在使用union 连接多个结果时     在多个结果集中 其目标列表有相同数目的表达式 且数据内容进来保持一至

select name from luser a
union all                           --union all
select name from student            --显示重复的用户名

 复杂点的用法：  列的数目都是相同的 并且数据类型也是相同的。

 

 

 9.使用子查询

 子查询就是在where语句中 添加一个select查询     对where的条件进行更好的控制。

  select * from luser a where a.name in (select name from student) 

 

 

 10.嵌套子查询

在where 添加在子查询 子查询中继续嵌套一个子查询  

 

 11.XML查询 query 查询xml节点值

create table Classinfo
(
  id int,
  Msg xml
)    --创建班级信息表

insert into Classinfo values (1,'<班级信息>
<班级名称>一班</班级名称><班主任>张老师</班主任><年级>9年级</年级>
</班级信息>')
--插入班级信息数据

select * from Classinfo  --查询数据

declare @msg xml                                  --1. 声明变量
set @msg=(select Msg from ClassInfo where id=1)   --2. 查询班级信息赋值到变量中
select @msg.query('班级信息/班级名称') 班级名称 ,@msg.query('班级信息/班主任')班主任,@msg.query('班级信息/年级') 年级
                                                  --3. 使用变量.关键字 query('xm节点名称') 查询出xml节点的结果

 

 

 12.For xml子句

 

4种模式 将查询结果自动表示成xml格式。

如： select * from student for xml Raw 

 结果就自动转化为：

--格式就是这种
<row name="zhangsan" sex="男" />
<row name="lisi" sex="女" />
<row name="zhaoliu" sex="男" />

path:

<row>
  <name>zhangsan</name>
  <sex>男</sex>
</row>
<row>
  <name>lisi</name>
  <sex>女</sex>
</row>
<row>
  <name>zhaoliu</name>
  <sex>男</sex>
</row>

auto:

<student name="zhangsan" sex="男" />
<student name="lisi" sex="女" />
<student name="zhaoliu" sex="男" />

 

13.exists 关键字  用来判断子查询是否有结果 返回bool类型

一般使用2种情况

1.直接跟在where 后面根据返回的结果进行查询。

 

select * from luser
where exists(select * from student where id=3)  
 --where  如果是ture 则执行查询luser是有数据的 反之则查询为空

 

2.是进行数据逻辑判断 比如用户登陆判断是否存在与用户表等。

 

13.对多个结果集的交集查询 INTERSECT   

和联合查询一样 查询的多个结果集的列数目必须一样

14.差查询 except 

 结果集1和结果集2不同的结果进行输出

 如：结果集1种有4行数据  其中有1行与结果集2中的数据相匹配  则输出3行不匹配的数据

  select name from luser except select name from student --查询两张表用户名不同的数据 

 

转载于:https://www.cnblogs.com/LZZ2017/p/6683574.html