sql server 数据库面试中遇到的坑

最近面试发现了一个规律，那就是招聘岗位级别越高的越是注重面试者的基础知识；
比如以下问题，明明开发中经常使用的东西，换了一种问法就不知道是什么了；

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
结果如下图：