第八單元 嵌套查詢

什麼時候要使用嵌套查詢？

學生信息表：

班級表：

成績表：

請問：葉星辰屬於哪個班級？

select * from 班級表 where 班級Id=
(
    Select 班級Id from 學生表 where 姓名 = '葉星辰'
)

2.嵌套查詢的格式是什麼？

select 列名 from 表名 where 欄位名 運算符   -- 外層主查詢，也叫父查詢
(
    select 列名 from 表名 where 條件      -- 內層查詢，也叫子查詢
)

子查詢的結果作為主查詢的查詢條件

--4.--查詢軟體技術1班的所有學生信息
-- 1.找表：學生表，班級表，外鍵：ClassId
-- 2.根據已經條件查詢外鍵的值
-- 3.根據外鍵的值查詢出題目的要求結果
select * from StudentInfo where ClassId =
(
    select Id from ClassInfo where Name='軟體技術1班'
)
​
    
--5.--查詢任我行同學的所有成績
-- 5.1  StudentInfo,StudentScore,找外鍵  stuId
-- 5.2  先寫已知條件 ，將外鍵查詢出來
-- 5.3  根據外鍵的值查詢題目要求的成績信息
select * from StudentScore where stuId in
(
    select stuId  from StudentInfo where stuName='任我行'
)
​
​
--6.--查詢“張三”同學所在班級信息
-- 1  StudentInfo,ClassInfo,找外鍵  classId
-- 2  先寫已知條件 ，將外鍵查詢出來
-- 3  根據外鍵的值查詢題目要求的班級信息
select * from ClassInfo where Id in
(
    select  classId from StudentInfo where stuName='張三' 
)
​
--7.-- 查詢學號為“180325011”的同學所在班級所有男生的信息
-- 1  StudentInfo,StudentInfo,關聯欄位  classId
-- 2  先寫已知條件 ，將關聯欄位查詢出來
-- 3  根據關聯欄位的值查詢題目要求的男生的信息
select * from StudentInfo where ClassId=
(
    select ClassId from StudentInfo where stuId='180325011'
) and stuSex='男'
​
​
​
--8.-- 查詢班級名為“軟體技術1班”一共有多少個女生信息
-- 1  ClassInfo,StudentInfo,關聯欄位  classId
-- 2  先寫已知條件 ，將關聯欄位查詢出來
-- 3  根據關聯欄位的值查詢題目要求的女生信息
select * from StudentInfo where stuSex='女' and ClassId=
(
    select Id from ClassInfo where Name='軟體技術1班'
)
​
​
--9.-- 查詢電話號為“18899251152”同學所在的班級信息
-- 1  ClassInfo,StudentInfo,關聯欄位  classId
-- 2  先寫已知條件 ，將關聯欄位查詢出來
-- 3  根據關聯欄位的值查詢題目要求的女生信息
select * from ClassInfo where Id=
(
    select classId from StudentInfo where stuPhone='18899251152'
)
​
​
--10.-- 查詢所有成績高於平均分的學生信息
-- 1,StudentScore, StudentInfo, 關聯欄位：StuId
-- 已知條件是:平均分
select * from StudentInfo where stuId in
(
    -- 查詢出高於平均分的Stuid
    select stuId from StudentScore where skillScore>
    (
        select avg(skillScore)  from StudentScore
    )   
)
​
​
​
--11.查詢所有年齡小於平均年齡的學生信息
​
-- 計算小於平均年齡的學生信息
select * from StudentInfo where (year(getdate())-year(stuBirthday))<
(
    -- 計算平均年齡
    select avg(year(getdate())-year(stuBirthday)) from StudentInfo
)
​
​
--12.查詢不是軟體技術1班級的學生信息
-- 關聯欄位：ClassId
select * from StudentInfo where ClassId not in -- 用in一定不會錯，如果子查詢的結果只有一條記錄時才可以寫=
(
    select Id from ClassInfo where Name='軟體技術1班'
)
​
select * from StudentInfo where ClassId !=
(
    select Id from ClassInfo where Name='軟體技術1班'
)
​
​
--13.查詢所有班級人數高於平均人數的班級信息
-- 每個班有多少人
select * from ClassInfo where Id in
(
    select  ClassId from StudentInfo group by ClassId having count(stuId)>
    (
        -- 求平均人數
        select avg(人數)from
        (
            select  count(stuId) as 人數 from StudentInfo group by ClassId
        ) aa
    )
)
​
​
--14.查詢成績最高的學生信息
select * from StudentInfo where stuId in
(
    select stuId from StudentScore where skillScore =
    (
        select MAX(skillScore)  from StudentScore
    )   
)
​
​
​
​
--16.查詢班級名是“會計1班”所有學生(使用in 關鍵字查詢)
select * from StudentInfo where ClassId in
(
    select Id from ClassInfo where Name='會計1班'
)
​
​
--17.查詢年齡是16、18、21歲的學生信息
select * from StudentInfo where (year(getdate())-year(stuBirthday)) in (16,18,21)
​
​
--18.查詢所有17-20歲且成績高於平均分的女生信息
select * from StudentInfo where (year(getdate())-year(stuBirthday)) between 17 and 20
and stuSex='女' and stuId in
(
select stuId from StudentScore where skillScore>
(
select avg(skillScore) from StudentScore
)
)
​
​
--19.查詢不包括'張三'、'王明'、'肖義'的所有學生信息(not in 關鍵字查詢)
select * from StudentInfo where stuName not in('張三','王明','肖義')
​
​
--20.查詢不是“電腦系”學院的所有學生(not in 關鍵字查詢)
select * from StudentInfo where ClassId not in
(
select Id from ClassInfo where College='電腦系'
)
​
​
--查詢成績比學生編號為'180325011','180325012'其中一位高的同學
-- any,some:某一個，其中一個  
select * from StudentInfo where stuId in
(
select stuId from StudentScore where skillScore> 
    some
(
select skillScore from StudentScore where stuId in('180325011','180325012')
)
)
​
​
--查詢成績比學生編號為'180325011','180325012'都高的同學（all）
-- all:所有
​
select * from StudentInfo where stuId in
(
select stuId from StudentScore where skillScore> 
all
(
select skillScore from StudentScore where stuId in('180325011','180325012')
)
)
​
​
--Row_Number() Over(Order by 欄位):按某個欄位進行編號排名
-- 以stuId進行升序排名
select Row_Number() Over(Order by stuId)  ,* from StudentInfo
​
​
​
-- 按總成績降序排序並給每一位同學進行編號
select Row_Number() Over(Order by skillScore desc) as 排名, * from StudentScore 
​
​
-- 按總成績降序排序後查詢4-8名的學生信息
select * from(
select Row_Number() Over(Order by (skillScore+theoryScore) desc) as 排名, * from StudentScore 
) aa where aa.排名 between 4 and 8
​
-- sqlserver 2012以後，offset rows  fetch next rows only
-- offset:在。。。位置 
select * from StudentScore order by (skillScore+theoryScore) desc offset 3 rows fetch next 5 rows only
​
​
-- 獲取按Id排序後的第3-5位同學信息
select * from(
select Row_Number() Over(Order by StuId) as 排名, * from StudentScore 
) aa where aa.排名 between 3 and 5
​
​
-- 
select * from StudentScore order by Id offset 2 rows fetch next 3 rows only
​
 
 

配套視頻鏈接：【階段二】 - SQLServer 基礎(超級詳細，口碑爆盆)_嗶哩嗶哩_bilibili