数据库

sql查询语句:

  select *from　student　inner join result

  on student.'studentNo'=result.'studentNo';

查询没有参加考试的学生

select * from student where studentNo not in 

(select studentNo from result)

sql exists子查询语法

select avg(studentresult)+5 as 平均分 from result

where not exists(

select * from studentNo from subject where subjectName ='LogicJava'

)AND 'examDate'=(

select MAX(examDate) from result where subjectNo=(

select subjectNo from subject

where subjectName='LogicJava'

)AND studentResult>60

)AND subjectNo=(select subjectNo from subject

where subjectName='LogicJava'

)and examDate=(

select max(examDate) from result where subjectNo=(

select subjectNo from subject where subjectName='Logic Java' 

)

)

sql嵌套子查询

select studentName 姓名,(select gradeName from grade where

gradeId=student.'gradeId'

)as 课程所属年纪,

subjectName 课程名称,examDate 考试日期,studentResult 成绩

from result r1

 inner join student on r1.'studentNo'=studnet.'studentNo'

 inner join subject on subject.'subjectNo'=r1.'subjectNo'

where r1.examDate in (

 select max(examDate) from result r2

 where r1.'subjectNo'=r2.'subjectNo'

 group by r2.'subjectNo'

)

order by subject.gradeId;

sql 分组查询

select subjectNo,avg(studentResult) as 课程平均成绩

from result

group by subjectNo

order by avg(studentResult);

select gradeId as 年纪编号, sex as 性别,count(* ) as 人数

from student

group by gradeId,sex

order by gradeId;

sql 分组筛选语句

 select subjectNo ,avg(studentResult) as 课程平均成绩

from result

group by subjectNo

having avg(studentResult)>=60;

sql 内连接语句

select student.'studentName',result.'subjectNo',result.'studentResult'

from student.'result'

select s.'studentName',R.'subjectNo',R.'studentResult'

from 'student' as s

inner join result as r on (s.'studentNo'=r.'studentNo');

sql 三表连接查询

select S.studnetName as 姓名,SU.subjectName as 课程,

R.studentResult as 成绩

from student AS s

inner join result as R on  (s.'studentNo'=R.'studentNo')

inner join  subject as SU on (SU.subjectNo=R.subjectNo);

sql 左外连接

select S.studentName,R.subjectNo,R.studentResult

from student AS s

left join result as R

on S.studentNo=R.studentNo;

select S.studentName,R.subejctNo,R.studentResult

from  result as r

left join student as s

on S.studentNo=R.studentNo;