SQL数据库多表联查等

--单元格中NULL/null/''/' '的区别
select * from [Student] where [Address]=''
select [StudentNo] '学号',[Phone] as '电话','性别'=[sex] from [Student] where [sex]='女'
select [StudentNo],[Phone] from [Student] where [sex]='女'
select [Address] from [Student] where [StudentName] like '张__' and [Sex]='女'
select * from [Student] where [BornDate] >'1989/12/31' and [BornDate] <'2000/01/01' and [Sex]='女'
select [Student].*,[Grade].[GradeId]
from [Student],[Grade]
where [Student].[GradeId]=[Grade].[GradeId] and [Grade].[GradeName]='大一'
--笛卡尔集(无脑链接，后筛选)
select s.*
from [Student] s,[Grade] g
where s.[GradeId]=g.[GradeId] and g.[GradeName]='大一'
--内连接（边链接，边筛选）
select s.*
from [Student] s inner join [Grade] g on s.[GradeId]=g.[GradeId]
where g.[GradeName]='大一'
--常量列
select '北京大学' as '学校' from [Student]
select TOP 3* from [Student]
select * from [Result] order by [StudentResult] desc
--三表连查(有语文成绩的学生的学号)
select s.*
from [Student] s inner join [Result] r on s.[StudentNo]=r.[StudentNo]
inner join [Subject] t on r.[SubjectId]=t.[SubjectId]
where t.[SubjectName]='语文'
--查询按日期由前到后，成绩由高到低，查询参加.net考试的信息
select r.*
from [Result] r inner join [Subject] su on r.[SubjectId]=su.[SubjectId]
where su.[SubjectName]='.net' order by r.[StudentResult] desc ,r.[ExamDate]
--查询年纪最小的学生的姓名和年级
select s.[StudentName],g.[GradeName]
from [Student] s inner join [Grade] g on s.[GradeId]=g.[GradeId]
where s.BornDate =(select MAX(BornDate) from [Student])
--查询学号为‘20110001’的学生参加过的所有考试信息，并按照实际前后排序
select s.[StudentName],r.*
from [Student] s inner join [Result] r on s.[StudentNo]=r.[StudentNo]
where s.[StudentNo]='20110001' order by r.[ExamDate] desc
--查询学号为'20110001'的选手参加过的所有考试的最高分及时间、科目
select [StudentResult],[SubjectId],[ExamDate]
from [Result]
where [StudentResult]=(select MAX(StudentResult) from [Result] where [StudentNo]='20110001') and [StudentNo]='20110001'
select COUNT(*),[Sex] from [Student] group by [Sex]
--查询每个年级的总学时数，并按照升序排列
select SUM(ClassHour),[GradeId] from [Subject] group by [GradeId]
--查询每个参加考试的学员的平均分
select A VG(StudentResult) as '平均分',[StudentNo] from [Result] group by [StudentNo]
--查询每门课程的平均分并降序排列
select A VG(StudentResult) from [Result] group by [StudentNo] order by A VG(StudentResult) desc
--查询每个学生的总分，并降序
select SUM(StudentResult) from [Result] group by [StudentNo] order by SUM(StudentResult) desc
--获得年级人数超过1人
select [GradeId],COUNT(*) from [Student] group by [GradeId] having COUNT(*)>1
--left join 左外连接中Student是主表，t是从表，从而找寻没有成绩的(可以替代not in) select s.*,t.*
from [Student] s left join
(select distinct [StudentNo] from [result]) t on s.[StudentNo]=t.[StudentNo]
where t.[StudentNo] is null
--right join 右外连接则相反
--找寻既学了科目1 又学了科目2的学生号
select t1.StudentNo
from (select StudentNo from Result where SubjectId=1) t1 inner join
(select StudentNo from Result where SubjectId=2) t2 on t1.StudentNo=t2.StudentNo。