有时数据是存储在不同的多个表中 但是表之间存在相互的关联关系,通过多表连接的方式取数据!
多表连接的基本原则
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
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 --查询两张表用户名不同的数据