【SQL server】基础入门5——子查询，数据透视

█ 视图

将一个复杂的select语句进行封装，以便使用

-- 创建视图
create view Student_Class_view				//创建了一个名为Student_Class_view的视图
as 
select StudentInfo.*, ClassInfo.className 	// 如果直接select*，会因为cId的重复而创建失败；这里巧妙的只保留了一个cId
from StudentInfo
inner join ClassInfo on ClassInfo.cId = StudentInfo.cId

-- 使用它 ：
select *
from Student_Class_view						// 直接当做一个连接好的表使用

-- 修改视图
alter view Student_Class_view				// 很简单，create换成alter
as ...

-- 删除视图
drop view Student_Class_view

注 ：

1. 视图中存储的是select语句，而不是结果集数据（不是表）
2. 但效果是：使用视图就相当于使用了结果集（这正是使用视图的目的）
3. 视图主要进行查询操作,不适合也不推荐增、删、改等操作
4. 实现了代码重用
    
    

█ 子查询

将一个查询语句嵌入另一个查询语句中（即select的嵌套）

-- demo: 查询参与了考试的学生信息(假设有的学生没有参与考试，ScoreInfo表中没有其sId)

-- ▶ 关键字in
select * from StudentInfo
where sId in (select distinct sId from ScoreInfo)
---- 括号中的内容是“ScoreInfo中出现过的SId”；
---- 实际上是把一次select的结果用在了另一次select上（可以作为筛选条件，也可以作为被from的表）

-- ▶ 关键字exists(效率更高)
select * from StudentInfo
where exists 
(select * from ScoreInfo where StudentInfo.sId = ScoreInfo.sId)
---- 语法格式背下来
---- 另外，exists还可以实现空值null的判断

关于exists优化 ：
 
select * from TableA a
where exists
(select 1 from TableB b where a.Id = b.Id )
 
当然这里子查询里写成select * 也无所谓。
理解一遍：select的是什么压根无所谓，因为若"不存在"，两表连接后为[空]，不管select什么都是空——只有[空]和[不空]两种情况
再理解一遍：exists存在性测试的关键思路在于：是否能在连接后(并筛选)的表中select出内容
对于exists引出的子查询，只要子查询结果非空，就返回True，否则返回False —— 与select的是什么压根没有关系
子查询在分页查询中的重要应用 ↓

首先明确一个事情，经过增、删等操作，sId编号已经不是连续的了（我们知道identity的标识并不能自动补齐）

分页查询时，恰恰需要通过一串连续的编号进行分页：[ [1, 2, 3], [4, 5, 6], [7, 8, 9] ]

现在首先要解决的问题，就是怎样对数据重新编号，得到一串连续编号 ？

--demo：筛选出所有不是3班的学生；并进行连续编号
select *, ROW_NUMBER() over(order by sId asc) 
from StudentInfo
where cId != 3

↑ 可以看出，筛选后sId已经不连续了，无法作为分页的依据

因此我们利用ROW_NUMBER()和over(order by…)的组合使用，得到了一列新的顺序编号
 

以上就是所有难点。分页查询本身简单的很 ↓

-- demo: 查询分页后(每页3个数据)的第二页的数据
-- 很简单！就是"编上连续的编号后，编号从4到6的行的信息"！ 

select * from
(select *, ROW_NUMBER() over(order by sId asc) as newIndex from StudentInfo) as newTable	// 一个新的带有连续编号的结果集【关键】(另外还要给新表取个别名)
where newIndex between 4 and 6

 
 
█ 子查询的进阶

上面讲的都是【无关子查询】———— 子查询只用一次，也只执行一次

下面讲的是【相关子查询】———— 子查询重复执行，外部查询每遍历一行，子查询就执行一次

★☆★【相关子查询】有个极为显著的特点：

就是因为子查询需要用到外部查询的表(“相关”)，因此外部查询用的表通常有别名（或者子查询内部直接使用外部表全名）！！！

★☆★【两句话区分无关子查询和相关子查询】

【无关子查询】的子查询是独立的，单独就能执行

【相关子查询】的子查询依赖于外部的表进行迭代，每次给它传入一个数据，才能够执行

--查询成绩比该课的平均成绩低的学生的学号、课程号、成绩
select sno, cno, score
from scoreinfo as a
where score < (select avg(score) from scoreinfo as b where a.cno = b.cno)

--查询两门及以上课程的成绩在80分以上(包括80)的学生的学号、姓名、年级、专业
select sno, sname, grade, specialty
from student s		
where (select count(*) from scoreinfo sc where s.sno = sc.sno and score >= 80) >= 2

--查询所有选修了1号课程的学生姓名
select s.sname
from student s
where exists (select * from scoreinfo sc where s.sno = sc.sno and cno = 1)
--★带有exists的子查询也是一种特殊的【相关子查询】，也称为“存在性测试”

☀ ☀ ☀ 写无关/有关子查询的思路？

【无关子查询】的思路是对整个表进行筛选；【相关子查询】的思路是逐行拿出来，进行判断——就像一个for语句

 
 

█ case函数

if-else逻辑： 适用于范围

-- demo: 根据成绩分为优、良、中

select score, 等级=case					-- 是范围(if-else) score写在when中
		when score>=95 then '优'
		when score>=80 then '良'
		when score>=60 then '中'
		else '差' end
from ScoreInfo

switch-case逻辑： 适用于离散的值

-- demo: 根据班级编号写上班级

select *, 班级=case cId					-- 是特定的值(switch-case) cId写在最前面
		  when 1 then '一班'
		  when 2 then '二班'
		  when 3 then '三班'
		  else '无' end
from StudentInfo

 
 

█ 数据透视（行列转换）

效果和基本步骤 ：

过程

↓

先做出了一个视图，该视图包含了要用到的数据和属性

create view Stu_Sub_Score_view
as
select stu.stuName, sub.subName, score.score
from ScoreInfo score
inner join StudentInfo stu on stu.sId = score.sId
inner join SubjectInfo sub on sub.subId = score.subId

↓

增加列(as)，利用case语句给列赋值

select 
stuName as 姓名,
case subName when 'Chinese' then score end as 语文,
case subName when 'English' then score end as 英语,
case subName when 'Computer' then score end as CS
from Stu_Sub_Score_view

↓

根据姓名进行行合并(group by)，同时使用聚合函数(max)

select 
stuName as 姓名,
max(case subName when 'Chinese' then score else 0 end) as 语文,
max(case subName when 'English' then score else 0 end) as 英语,
max(case subName when 'Computer' then score else 0 end) as CS
from Stu_Sub_Score_view
group by stuName

 

 

 

 

 

 

 
 
 

 
 

  
> _ <