数据库视屏总结（三）——连接与查询

数据库视频第七章主要讲解了连接和查询两大部分，首先连接包括基本连接、内连接、左外连接、右外连接、全连接、交叉连接、自连接。查询包括：联合查询、使用子查询、XML查询、for xml查询、exists关键字查询、交查询intersect

基本连接

使用原则：
1、每个目标列钱都要加上基表名称
2、FROM字句应包括所有使用的基表
3、where字句应定义一个同等连接
举例将班级信息和学生信息相连接
select A.姓名,a.性别,a.出生年月,a.学号,b.班级名,a.家庭住址
from 学生信息 A,班级信息 B
where a.所属班级=b.班级编号

内连接

语法格式
Select 查找的目标列
From table1 inner join table2 [on join+条件]
[where条件]
[order by 条件]
举例：
select a.班级名,a.班级人数,b.姓名,b.联系方式
from 班级信息 A inner join 辅导员信息 b
on a.辅导员=b.辅导员编号

左外连接

内连接消除与另一个表的任何行不匹配的行，而外链接会返回FROM字句中提到的至少一个表或视图中的所有行，只有这些行符合任何搜索条件
在外链接有主从之分，如果符合条件，则直接返回到查询结果中，如果主表中的行在从表中没有找到匹配的行，主表的行任然保留，并返回到查询结果中

右外连接

Select A.班级名,a.班级人数,b.姓名,b.联系方式
From 辅导员信息B RIGHT OUTER join 班级信息a
On A.辅导员=B.辅导员编号
左右外连接的互换：只需要把RIGHT改成LEFT,把right或者是left的左右两侧的表互换即可

全连接

select a.班级名,a.班级人数,B.姓名,b.家庭住址
from 学生信息 b full outer join 班级信息 a
on a.班级编号=b.所属班级

交叉连接

它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数

语法格式：
Select 列名
From table1 cross join table2
Where 条件进行筛选

举例
select a.班级名,a.班级人数,b.姓名
from 班级信息 A cross join 学生信息 B

自连接

在一个表上进行连接
举例：

select A.班级名,a.班级人数,b.班级名 
from 班级信息 A ,班级信息 B
where a.班级人数=b.班级人数 and a.班级编号<>b.班级编号

联合查询

将多个查询结果联合到一个表中
语法格式：
Select 列名
From
Union all
Select

注意：使用UNION查询时，连接的连个结果集必须在其目标列表总有相同数目的表达式，且数据内容尽量保持一致
举例：

select a.课程编号, A.成绩,b.姓名
from 成绩信息 A ,学生信息 b
where a. 学生编号=B.学号 
union 
select '',sum(成绩),'合计'
from 成绩信息 A ,学生信息 b
where a. 学生编号=B.学号 
union
select '',avg(成绩),'平均'
from 成绩信息 A ,学生信息 b
where a. 学生编号=B.学号 
 order by 成绩
使用子查询
查找成绩小于平均分的查询：
select a.课程编号, A.成绩,b.姓名
from 成绩信息 A ,学生信息 b
where a. 学生编号=B.学号 
and a.成绩<(
select avg(成绩) from 成绩信息 A,学生信息 B
where a. 学生编号=B.学号
)

查询效果如下：

举例：
select a.课程编号, A.成绩,b.姓名
from 成绩信息 A ,学生信息 b
where a. 学生编号=B.学号
and b.学号 in(
select 学号 from 班级信息 A,学生信息 B
where a.班级编号=B.所属班级 and b.所属班级=’125’）

XML查询

目的：获取某项的某个节点的一个数据
举例：

create table student
(
s_id int,
s_data xml
)
insert into student values(
'1',
'<学生信息><姓名>范志慧</姓名><性别>女</性别><班级>计算机科学与技术1班</班级>
<职位>班长</职位></学生信息>'
)
select * from student
declare @data xml
set @data = (select s_data  from student where s_id=1)

select @data.query('学生信息/姓名')姓名,@data.query('学生信息/性别')性别,@data.query('学生信息/班级')班级

结果如下：

For xml 字句

通过FOR XML字句并制定模式可以将从数据库系统的表中检索出来的数据自动表示成XML格式。有以下四种模式：RAW 模式、AUTO模式、EXPLICIT模式和PATH模式
raw模式：

select A.班级名,a.班级人数,B.姓名,B.联系方式
from 班级信息 A ,辅导员信息 B
where A.辅导员=b.辅导员编号
for xml raw

效果如下：

auto模式：

select A.班级名,a.班级人数,B.姓名,B.联系方式
from 班级信息 A ,辅导员信息 B
where A.辅导员=b.辅导员编号
for xml auto

效果如下：

EXISTS关键字查询

判断用户名是否有记录：

declare @username varchar(20)
declare @pwd varchar(20)
set @username='123456'
set @pwd='美丽'
 if exists(select * from 学生信息 where 学号=@username and 姓名=@pwd)
 print'登录成功'
 else
 print'登录失败'

交查询INTERSECT

intersect前不允许加order by

举例使用交查询

select a.成绩,b.姓名  from 成绩信息 A ,学生信息 B
 where A.学生编号=b.学号 and a.课程编号=2 and a.考试编号='0801' 

 intersect
 select  a.成绩,a.姓名 from 
 (
 select top 1 a.成绩,b.姓名  from 成绩信息 A ,学生信息 B
 where A.学生编号=b.学号 and a.考试编号='0801' order by a.成绩
 ) a