最近面试发现了一个规律,那就是招聘岗位级别越高的越是注重面试者的基础知识;
比如以下问题,明明开发中经常使用的东西,换了一种问法就不知道是什么了;
1.分别说说 内连接,外连接,交叉连接,笛卡尔积 是什么?
看见题目的第一反应是脑袋顶上出现三个 “???” ,我只知道有 left join(左连接) ,right join(右连接) ,inner join (全连接),那什么是内连接,外连接,交叉连接笛卡尔积呢?
赶紧翻开小课本,介绍内容如下:
表的连接类型有:自连接,内连接,外连接,右外连接
自连接:是指表与其自身进行连接,在自连接时必须给表取别名,就好像是玩 cosplay,不管扮演了多少个角色,但其实都是自己与自己在玩,例如:
select a.name as a_name
,b.name as b_name
,...
from table_name a,table_name b
where a.id = b.id
内连接:也称为等同连接,返回的结果是两张表中所有相匹配的数据,舍弃不匹配的数据,语法是这样的:
select ... from 表 1 [inner] join 表 2 on <表达式>
代入表和条件就是这样的:
select a.name as a_name
,b.name as b_name
,...
from table_name a
inner join table_name b on a.id = b.id
where ...
外连接:无论是内连接还是带 where 子句的多表查询,都只返回相匹配的数据,即若果任何一个源表中的行在另一个源表中没有匹配,系统将不会把该行放在最后的结果表中,简单来说:也就是返回相同的数据,不同的就不返回了。而在外连接系统生成的结果表中,不仅包含符合条件的行,还包括左表(左外连接),右表(右外连接)或两个链接表(全外连接)中所有的数据行,SQL 的外连接有三种:左外连接(left [outer] join),右外连接(right [outer] join)和全外连接(full [outer] join),外连接的语法和内连接一样,只需要将 inner join 替换为相应的外连接关键字即可; 看到这里,我有种 ”原来我只知道孩子的名字,却不知道孩子父亲的名字“ 的感觉;
语法如下:
例如有以下两张表:
table_1 有数据:
name age
E0001 18
E0002 20
E0003 25
table_2 有数据:
name bod
E0001 2002-12-01
E0002 2000-02-05
E0004 2000-10-09
-- 左外连接:返回两张表相同的数据 和 左连表(表 1) 的所有数据行
select ... from 表 1 left outer join 表 2 on <表达式>
代入表和条件就是这样的:
select a.name
,a.age
,b.bod
from table_1 a
left outer join table_2 b on a.name = b.name
--返回结果:
name age bod
E0001 18 2002-12-01
E0002 20 2000-02-05
E0003 25 NULL
--右外连接:返回两张表相同的数据 和 右连表(表 1)的所有数据行
select ... from 表 1 right outer join 表 2 on <表达式>
代入表和条件就是这样的:
select a.name
,a.age
,b.bod
from table_1 a
left outer join table_2 b on a.name = b.name
--返回结果:
name age bod
E0001 18 2002-12-01
E0002 20 2000-02-05
E0004 NULL 2000-10-09
--全外连表:除了包括匹配行之外,还包括 join 左右表的不匹配行,其中,左表或是右表中缺少的属性值用 NULL 表示
select ... from 表 1 full outer join 表 2 on <表达式>
--全外连接脚本
select a.name,
,a.age
,b.bod
from table_1 a
full outer join table_2 b on a.name = b.name
--返回结果:
name age bod
E0001 18 2002-02-01
E0002 20 2002-02-05
E0003 25 NULL
E0004 NULL 2020-12-09
看完之后,心里是不是万马奔腾…
别着急还没完,还有交叉连接和笛卡尔积呢…
笛卡尔积是这么定义的:
设 A,B 为集合,即 A 中元素为第一元素,B中元素为第二元素构成的有序对,所有这样的有序对组成的集合叫做 A 与 B 的笛卡尔积,记作 AxB,例如:
A = {a,b}, B = {0,1,2} 则
AxB = {<a,0>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}
BxA = {<0,a>,<0,b>,<1,a>,<1,b>,<2,a>,<2,b>,}
而交叉连接就是笛卡尔积在 sql 中的实现,sql 中的关键字用 “CROSS JOIN” 来表示交叉连接
--还是拿上面 table_1 和 这里的 table_3 看结果
table_3:
course
语文
数学
select * from table_1 a cross join table_3 c
--返回结果
name age course
E0001 18 语文
E0001 18 数学
E0002 20 语文
E0002 20 数学
E0003 25 语文
E0003 25 数学
select * from table_3 c,cross join table_1 a
-- 返回结果
sourse name age
语文 E0001 18
数学 E0001 18
语文 E0002 20
数学 E0002 20
语文 E0003 25
数学 E0003 25
2.说说 UNION 与 UNION ALL 的区别
笑话了,写存储过程时,某些查询的 sql 都要自行先分段测试起结果的准确性的,也就经常会用到 union ,也就是将多个相同字段个数,相同字段类型,字段名称可不同的结果集合并为一个结果集返回,可与 union all 的区别呢?说实话,那是真没在意过,翻小课本了。。。
书本中是这样介绍的:
UNION 与 UNION ALL 运算符用于执行集合的”并“运算
相同点:
都是对多个结果集进行并操作,且字段个数相同,字段类型相同也要相同,字段名称不强求(结果以第一个 select 中的字段名为准)
不同点:
UNION:还会去掉重复行,并进行默认规则排序
UNION ALL:不会去掉重复行,也不进行默认规则排序
都说到了这里,就还说一下 Intersect(交集),Minus(差集),Except(差集) :
Intersect:对多个结果集进行交集操作(返回多个结果集都存在的数据),不包括重复行,并进行默认规则排序,多个结果集之间 相同字段个数,相同字段类型,字段名称不强制要求,以第一个 select 的字段名为准
Minus:对多个结果集进行差操作(相当于 表 A 减去 与 表 B 相同的数据后,返回 表 A 剩下的数据行),不包括重复行,并进行默认规则排序(因本人本机数据库是 sql server 2008 ,并没有 Minus ,也不知道具体是否如描述所言的这样,有知道的大神,可下方留言,一起讨论一二!!!)
except:对两个结果集进行差操作(相当于 表 A 减去 与 表 B 相同的数据后,返回 表 A 剩下的数据行),不包括重复行,不进行默认规则排序,结果集之间 相同字段个数,相同字段类型,字段名称不强求,以第一个 select 的字段为准
3.将下列表数据 纵表变横表(多行转多列),横表变纵表(多列转多行)
sql server 2008 中有两个函数:
- pivot:多行转多列
- unpivot:多列转多行
先看看多行转多列的语法:
select * from table pivot(聚合函数(<列名1>) for <列名2> in (<转换的行中列2的值1,转换的行中列2的值2,…>)) as 别名
create table #student
(
stu_ID int identity(1,1),
stu_name nvarchar(20),
stu_course nvarchar(20),
stu_grade float
)
insert into #student
select 'E0001',N'语文',90
union
select 'E0001',N'数学',94
union
select 'E0001',N'英语',88
union
select 'E0002',N'语文',99
union
select 'E0002',N'数学',96
union
select 'E0002',N'英语',97
select * from #student
-- 多行转多列(按语法套就好)
select * from #student pivot(sum(stu_grade) for stu_course in (语文,数学,英语)) as pvt
结果如下图:
看上图这个结果和我们预想中的结果不一样,这是为什么呢?这时候我们就回过头去看看表结构,stu_ID int identity(1,1) 是一个自增长的 stu_ID,是不会有重复值,那么这时候应该怎么处理呢?
解决思路,既然是 stu_ID 自增引起的,那么我们在多行转多列时,可不可以不要这个自增长字段呢?下面看看具体代码:
select * from (select stu_name,stu_course,stu_grade from @student) as a pivot(sum(stu_grade) for stu_course in (语文,数学,英语)) as pvt
上面 sql 语句,第一个 from 后面括号内的 sql 是一个子查询,sql 内没有返回指定字段的值,这样就可以解决了上面多行转多列出现 NULL 值,数据行不变的问题,如下图:
多列转多行语法:
select * from table unpivot(<转换后的值> for <转换后的列> in <需要转换的列1,需要转换的列2,…>) as pvt
create table #course
(
姓名 nvarchar(20),
语文 float,
数学 float,
英语 float
)
insert into #course
select 'E0001',90,88,79
union
select 'E0002',93,87,67
select * from #course
多列转多行语法:
select * from table unpivot(<转换后的值> for <转换后的列> in <需要转换的列1,需要转换的列2,...>) for pvt
按语法套 sql 语句:
select 姓名,课程,分数 from #course unpivot(分数 for 课程 in (语文,数学,英语)) as pvt
结果如下图: