第三章 SQL高级查询

                                  SQL高级查询

章节目标

• 嵌套子查询
• 聚合技术
• 排序函数
• 公式表表达式

内容

子查询

作为查询条件使用     where

--1.子查询作为条件

--查询学号在王五前边的同学
select * from StuInfo where stuid < (select stuid from StuInfo where stuname='王五')

在条件查询中使用‘<’ '>' '='后家的查询语句只能查一列

---

---

作为临时表使用         from

--子查询3：stuinfo、StuMarks都作为子查询

select stuname,subject,score from 
(select * from stuinfo where stuname = '李四') s1,
(select * from stumarks where score > 80) s2
where s1.stuid = s2.stuid

作为列使用                select

--3.子查询作为列使用

--查询所有学员html成绩，没有成绩以null显示
select s.*,
(select score from StuMarks where subject='html' and  s.stuid=StuMarks.stuid) as '成绩' 
from StuInfo s

 嵌套子查询

in  和 not  in

in 和 not in 通常在where 子句中使用，在in和not in后接的子查询中，可以有多个值出现，但必须只能有一列

--not..in 对in取反，不符合in后面所有条件都能够查询出来
--查询学号除了1和3的以外的学员信息
select * from stuinfo where stuid not in (1,3)

--in 符合in后面所有条件都能够查询出来，一系列确定的值或表中的某一列
-查询学号为1和3的学员信息
		
select * from stuinfo where stuid in (1,3)

exists  和 not exists

--EXISTS 和 NOT..EXISTS 后必须跟子查询，表示存在和不存在的意思。

--查询存在分数的学员的信息
SELECT * FROM StuInfo WHERE EXISTS (SELECT * FROM StuMarks WHERE StuMarks.StuID = StuInfo.StuID)

--查询没有成绩的学员的信息
SELECT * FROM StuInfo WHERE not EXISTS (SELECT * FROM StuMarks WHERE StuMarks.StuID = StuInfo.StuID)

same  any   all

--SOME、 ANY、 ALL后必须跟子查询

--SOME 和 ANY 的作用是一样的,表示其中的任何一项 
select * from StuInfo where stuid > any(select stuid from StuInfo where stuid>1)
select * from StuInfo where stuid > some(select stuid from StuInfo where stuid>1)

--all表示其中的所有的项
select * from StuInfo where stuid > all(select stuid from StuInfo where stuid>1)

compute  聚合技术

	--对信息进行查询并统计
	select * from StuMarks  where subject='html' 
	compute max(score),min(score),avg(score)

	--对信息进行分组查询并统计
	select * from StuMarks order by stuid desc
	compute avg(score),sum(score) by stuid

排 序 函 数

语法：排序函数 over([分组子句] 排序语句[DESC]/[ASC] )   

1.  ROW_NUMBER()函数生成的排序根据排序子句给出递增连续序号（1234）
2. RANK()有并列且跳号（1134）
3. DENSE_RANK并列且不跳号（1223）

分租子句                      PARTITION    BY  分组列 分组列

排序子句                      ORDER    BY  排序列  排序列

                                         有聚合函数记得后面加   GROUP   BY

--row_number()	行号
select row_number() over(order by score desc) as '排名',
StuInfo.stuid,stuname,score from StuInfo,StuMarks
where StuInfo.stuid=StuMarks.stuid  and StuMarks.subject='java'

--rank()  存在并列时跳空
select rank() over(order by score desc) as '排名',
StuInfo.stuid,stuname,stusex,score from StuInfo,StuMarks
where StuInfo.stuid=StuMarks.stuid  and StuMarks.subject='java'

--dense_rank()  存在并列时不跳空
select dense_rank() over(order by score desc) as '排名',
StuInfo.stuid,stuname,stusex,score from StuInfo,StuMarks
where StuInfo.stuid = StuMarks.stuid  and StuMarks.subject='java'

		
--partition by 分组子句
select dense_rank() over(partition by subject order by score desc)as '排名',
StuInfo.stuid,stuname,subject,score from StuInfo,StuMarks
where StuInfo.stuid=StuMarks.stuid 

---

临时表   公式表

除了聚合函数记得后面加 GROUP BY

	--在一个批中建立一个临时表，保存所有学生的SQL成绩
	--可以用别名，但要与查询结果列一一对应（学号,姓名,成绩）
	WITH StuInfo_StuMarks (StuID, StuName, Score)
	AS
	(
		SELECT S1.StuID, S1.StuName, S2.Score 
		FROM StuInfo S1, StuMarks S2
		WHERE S1.StuID=S2.StuID and subject = 'SQL'
	)
	select * from StuInfo_StuMarks
	go