SQL高级查询笔记

此文章主要记录有  嵌套子查询，聚合技术，排序函数和公示表表达式

该笔记都用到这两个表

建表数据如下

create table stuinfo
(
stuid int primary key,
stuName varchar(50),
stusex varchar(2),
classid int
)

create table exam
(
examno int primary key,
stuid int,
subject varchar(30),
score int
)

insert into stuinfo values(1,'张三','男',1)
insert into stuinfo values(2,'李四','女',1)
insert into stuinfo values(3,'王五','男',2)
insert into stuinfo values(4,'赵六','女',1)
insert into stuinfo values(5,'田七','男',2)

insert into exam values(1,1,'HTML',85)
insert into exam values(2,1,'JAVA',80)
insert into exam values(3,1,'SQL',82)
insert into exam values(4,2,'HTML',70)
insert into exam values(5,2,'JAVA',81)
insert into exam values(6,2,'SQL',60)
insert into exam values(7,3,'HTML',70)
insert into exam values(8,3,'JAVA',90)
insert into exam values(9,3,'SQL',85)
insert into exam values(10,4,'HTML',61)
insert into exam values(11,4,'JAVA',68)
insert into exam values(12,4,'SQL',90)
insert into exam values(13,5,'HTML',81)
insert into exam values(14,5,'JAVA',65)
insert into exam values(15,5,'SQL',75)

嵌套子查询

         在有些时候，一个SQL语句不能直接查询出结果来，因为查询的条件需要来自另一个查询，这种查询我们叫子查询

子查询基本形式

例如：要查询比学生王五小的所有学生，我不知道学号，要实现这个需要

1.先要查询出王五的学号

2.在查询小于这个学号的学生

select * from stuinfo where stuid>(select stuid from stuinfo where stuname='王五')

子查询可以提高查询语句的可读性，降低SQL复杂度

 

子查询语句结构一般有三种形式，1.在查询条件中出现，2.以查询的临时表出现，3.在列中出现，在查询条件中出现的情况最多

其他形式例如：

--在查询表中使用
select s1.*,s2.score from stuinfo s1 
left join(select * from exam where subject ='HTML')s2 on s1.stuid=s2.stuid
--在列中使用
select s1.*,(select score from exam s2 where s1.stuid=s2.stuid and subject='HTML') score from stuinfo s1

in 或者 not in

        在where子句后面跟的查询条件如果是多个，大于、小于、等于、不等于不能满足要求，可以用in或者not in关键字

In表示where条件在什么什么范围内

Not in 表示where条件不在什么什么范围内

例如

select * from stuinfo where stuid in(1,3,5)
--表示学号是1,3,5其中就查询出来，如果是使用not in则刚好相反

 exists not exists

         如果where子句跟着的查询条件不仅是多个，而且是多个记录的比较，in not in也不能满足要求，可以用exists或者not exists关键子

exists表示存在符合条件的

not exists表示不存在符号条件的

例如：查询分数不为空的学生信息

select * from stuinfo s where exists (select * from exam e where score is not null and e.stuid=s.stuid)

some  any  all

some:几个，一些，any:任何的，some和any的效果是相同的

all:表示一切，所有的

如果要查询比某个学生成绩好的其他学生，就可以用any

select s.*,e.score,e.subject from stuinfo s,exam e where s.stuid=e.stuid and e.subject='JAVA'
and e.score >any( select score from exam where subject ='JAVA')

any all的查询条件必须是SQL查询，any是判断符合其中任意一个条件，all是判断是否符号所有条件

从形式上看any all和in not in比较像。

聚合技术

聚合函数

聚合函数：count、sum、max、min、avg

         Count:求记录个数

         例如：统计学生人数

select count(stuId) from stuinfo

Sum:求参与聚合记录的列值的合计

         例如统计学生java的总成绩

select sum(score) from exam where subject ='JAVA'

         Max:求参与聚合记录的列中最大值

         例如：求每个科目的最高分

select subject,max(score) from exam group by subject

Min：求参与聚合记录的列中最小值

         与求最大值相反

select subject,min(score) from exam group by subject

         Avg:求参与聚合记录的列的平均值

         例如：求各科目的平均分数

select subject,avg(score) from exam group by subject

    聚合函数可以单独使用，但是当select后面出现了其他字段就必须group by子句，或者说group by子句中出现的分组条件字段都必须跟在select后面，如果需要对分组后的结果再判断，则用having 分组结果来过滤。

computeby

group by分组统计只能产生汇总数据，不能显示详细信息

例如汇总统计学生平均成绩

select * from exam compute avg(score)

如果需要按学习科目分组，再汇总各科的平均分数，必须要用到compute by，如果使用compute by则必须加入order by子句

select subject,score from exam order by subject,score compute  avg(score) by subject

rollup函数

在group by 的基础上再合计，使用时直接在gourp by后面加上 with rollup就可以，他可以对汇总后的结果再汇总

select subject,avg(score) from exam group by subject with ROLLUP

排序函数

row_number

返回结果集，分配连续的序列号

select tid,name,row_number() over(order by tid asc) from temp

RANK

返回结果集，分配不连续的序列号，不会重复

select tid,name,rank() over(order by tid asc) from temp

DENSE_RANK

返回结果集，分配连续的序列号，会重复

select tid,name,dense_rank() over(order by tid asc) from temp

语法上注意：

排序函数是跟在select后面的

排序函数后面跟着over，指定排序的字段

公式表达式

在查询过程中往往需要一些中间表，而这些中间表会在查询中重复使用，在SQL2005以前的数据库中，这些子查询通常被嵌套在外部查询反复调用，不但效率很低，而且SQL语句看上去很复杂，公式表达式可以创建临时结果集，类似后面将要学习的视图，可以降低SQL的复杂度。

公式表达式的语法

with 临时表名（字段名1，字段明2）
as (select 语句)
例如：
WITH StuInfo_Mark (StuID, StuName, Subject, Score) 
AS 
( 
	SELECT S1.StuID, S1.StuName, S2.Subject, 
		S2.Score FROM StuInfo S1, StuMarks S2 
		WHERE S1.StuID=S2.StuID 
) 
select * from stuInfo_mark

但是以上操作必须是再一个批处理里执行

批处理就是指在一段sql语句后加一个GO  那么这一段sql语句就是一个批处理了

晚上更新sql中的视图和索引