SQL语句查询写法

insert into students(name,number,age,code,seatNumber,address) values
( '中秋天','s25301','男','18',1,'北京还定'


)


insert into students(name,number,age,code,seatNumber,address) values
( '李斯文','s25303','女','22',2,'河南洛阳'


)




insert into students(name,number,age,code,seatNumber,address) values
( '李文采','s25302','男','31',3,''


)


insert into students(name,number,age,code,seatNumber,address) values
( '欧阳出演','s25304','男','28',4,'新疆哈哈'


)


insert into score(examinationNumber,studentId,writtenResults,machineTest) values
( 's271811','s25303',80,58


)




insert into score(examinationNumber,studentId,writtenResults,machineTest) values
( 's271813','s25302',50,90


)


insert into score(examinationNumber,studentId,writtenResults,machineTest) values
( 's271815','s25302',65,0


)


insert into score(examinationNumber,studentId,writtenResults,machineTest) values
( 's271816','s25301',77,80


)




select id from score where writtenResults<60 and machineTest<60
select id from score where writtenResults<60 or machineTest<60
select count(id) from score where writtenResults<60 or machineTest<60
select AVG(writtenResults) AS 平均分数,AVG(machineTest) AS 平均分数 from score 


select *,writtenResults+machineTest as 总分 from score


select MIN(machineTest) from score


select * from score where machineTest = (select min(machineTest) from score)
select * from score order by machineTest asc  limit 1


# select top 1 from score order by machineTest desc  ##sql server


select * from score where machineTest = (select max(machineTest) from score)
select * from score order by machineTest desc limit 1


select * from (select *,(writtenResults+machineTest)/2 as avgscore from score) scoreTemp order by avgscore desc limit 1
select * from  score order by (writtenResults+machineTest)/2 desc limit 1




select * from score where machineTest > (select AVG(machineTest) AS 平均分数 from score) 




select * from (select *,(writtenResults+machineTest)/2  avgscore from score) scoreTemp where avgscore > (select AVG(writtenResults+machineTest)/2 AS 班级平均分 from score)
select * from score where (writtenResults+machineTest)/2 > (select AVG(writtenResults+machineTest)/2 AS 班级平均分 from score)


select name,studentId,machineTest,writtenResults from score  join students on (score.`studentId` = students.`number`) where writtenResults<60 or machineTest<60


select name,studentId,machineTest,writtenResults from score,students where score.studentId = students.number and ( writtenResults<60 or machineTest<60)




select name,studentId,machineTest,writtenResults from score  join students on (score.`studentId` = students.`number`) where (writtenResults+machineTest)/2 < 60


select name ,number from students as st where number not in ( select studentId from score sc where (writtenResults+machineTest)/2 >=60)


select name ,number from students as st where EXISTS ( select * from score sc where sc.studentId = st.number and (writtenResults+machineTest)/2 >=60 )


select name,number from score join students on (score.`studentId` = students.`number`) where (writtenResults+machineTest)/2 < (select avg((writtenResults+machineTest)/2) as classavg from score)


select name from students where  name like '%李%'


select studentId,score.`examinationNumber`,avg(writtenResults+machineTest)/2 avgscore from score group by studentId


select studentId, name ,avg(writtenResults+machineTest)/2 avgscore from score join students on(score.`studentId` = students.`number`) where name like '李%'  group by studentId