SQL第三章 （sql高级查询）

1、子  查  询
特点：
①使用灵活，可以成为SQL语句的多个部分
②降低SQL的语句的复杂度，提高SQL语句的可读性

/*----------------------------------------子  查  询-----------------------------------------------------*/
    --查询李四同学成绩大于80分的记录
        --联合查询
        select stuname,subject,score from StuMarks,StuInfo
        where StuInfo.stuid = StuMarks.stuid and score>80 and stuname = '李四'

        --子查询1：stuinfo作为子查询
        select stuname,subject,score from StuMarks s1,
        (select * from stuinfo where stuname = '李四') s2
        where s1.stuid = s2.stuid and s1.score > 80

        --子查询2：StuMarks作为子查询
        select stuname,subject,score from stuinfo s1,
        (select * from stumarks where score > 80) s2
        where s1.stuid = s2.stuid and s1.stuname= '李四'

        --子查询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
        
    --1.子查询作为条件
        --查询学号在王五前边的同学
        select * from StuInfo where stuid < (select stuid from StuInfo where stuname='王五')
        
    --2.子查询作为临时表
        --查询李四同学成绩大于80分的记录
        select stuname,subject,score from
        (select * from stuinfo where stuname = '李四') s1,
        (select * from stumarks where score > 80) s2
        where s1.stuid = s2.stuid
        
    --3.子查询作为列使用
        --查询所有学员html成绩，没有成绩以null显示
        select s.*,
        (select score from StuMarks where subject='html' and  s.stuid=StuMarks.stuid) as '成绩'
        from StuInfo s

 

 

2、in和not in
    in和not in 通常在where子句中使用
    在in和not in 后接的子查询中，可以有多个值出现，但必须自能有一列
/*----------------------------------------in  not..in-----------------------------------------------------*/
    --in 符合in后面所有条件都能够查询出来，一系列确定的值或表中的某一列
        --查询学号为1和3的学员信息
        --select * from stuinfo where stuid = 1 or stuid = 3
        select * from stuinfo where stuid in (1,3)
        --查找Java分数大于80分的学员姓名
        select stuname from stuinfo where stuid in (select stuid from stumarks where subject = 'java' and score > 80)
        --select stuname from stuinfo where stuid in (select stuid,subject from stumarks where subject = 'java' and score > 80)
        
    --not..in 对in取反，不符合in后面所有条件都能够查询出来
        --查询学号除了1和3的以外的学员信息
        select * from stuinfo where stuid not in (1,3)

 

 

3、exiets和 not exists  表示存在不存在
在语句中会判断exiets和 not exists 后接的句子是否存在和是否不存在，唯一的区别就是意义相反

/*----------------------------------------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)

 

4、some any all
①在sql查询中，some、any、all后必须跟子查询
②在sql查询中，some、any作用一样，表示其中任何一项；any表示其中所有项

/*----------------------------------------some 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)

 

5、compute和compute by     
①compute子句需要下列信息：
可选by关键字。它基于每一列计算指定的行聚合
行聚合函数名称 sum、avg、min、max或count。
要对其行聚合函数的列。

②还有些场景中我们需要对结果先进行分组，然后进行汇总计算。这中情况下我们可以使用compute by进行分组汇总查询。

/*----------------------------------------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

 

6、/*----------------------------------------排 序 函 数-----------------------------------------------*/
    --排序函数 over([分组子句] 排序语句[排序方式])    
        --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'

        --dense_rank()  存在并列时不跳空
        select dense_rank() over(order by sum(score) desc) as '排名',
        StuInfo.stuid,stuname,stusex,sum(score) '总分' from StuInfo,StuMarks
        where StuInfo.stuid = StuMarks.stuid
        group by StuInfo.stuid,stuname,stusex
        
        --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

 

 

 

7、/*----------------------------------------公式表表达式-----------------------------------------------*/
    --在一个批中建立一个临时表，保存所有学生的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