sql语句

50条sql 语句

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

思路：先找到001号和002号课程的学生成绩和学生id  

SELECT talscore.`Score`, talscore.`StuId`
FROM talscore
WHERE  talscore.`CourseId`='001' 
​
SELECT talscore.`Score`, talscore.`StuId`
FROM talscore
WHERE  talscore.`CourseId`='002' 

然后where 学生id相等 and 成绩a>成绩b

SELECT  a.StuId 
FROM 
(SELECT talscore.`Score`, talscore.`StuId`
FROM talscore
WHERE  talscore.`CourseId`='001')  a ,
(SELECT talscore.`Score` ,talscore.`StuId`
FROM talscore
 WHERE talscore.`CourseId`='002')  b
WHERE a.Score>b.Score AND a.StuId=b.StuId

结果：

 StuId
1001
1009
1010

2、查询成绩大于60分的同学的学号 ；

SELECT  StuId, Score
FROM talscore
WHERE   Score>(SELECT AVG(talscore.`Score`) FROM talscore)

拓展：查询平均成绩大于60分的同学的学号和平均成绩；

SELECT StuId,AVG(Score)
FROM talscore
GROUP BY StuId HAVING AVG(Score)>60

结果：                   

3.查询所有同学的学号、姓名、选课数、总成绩；

 SELECT tblstudent.`StuId`,tblstudent.`StuName`,SUM(talscore.`Score`),COUNT(talscore.`CourseId`)
 FROM tblstudent,talscore
GROUP BY StuId,StuName

结果：

 

4/ 查询姓“李”的老师的个数

SELECT COUNT(DISTINCT (TeaName)) 
FROM tblteacher
WHERE tblteacher.`TeaName` LIKE '李%'

结果：

5、查询没学过“叶平”老师课的同学的学号、姓名：

三个子查询嵌套，优化不够

SELECT  tblstudent.`StuId`,tblstudent.`StuName`
FROM tblstudent
WHERE tblstudent.`StuId`NOT IN
(SELECT talscore.`StuId`
FROM talscore
WHERE talscore.`CourseId` IN
( SELECT tablcourse.`CourseId` 
 FROM tablcourse
 WHERE tablcourse.`TeaId`=(SELECT  TeaId FROM tblteacher
WHERE tblteacher.`TeaName`='叶平')))
 

答案是这样子的

SELECT  tblstudent.`StuId`,tblstudent.`StuName`
FROM tblstudent
WHERE StuId NOT IN (
SELECT DISTINCT (talscore.`StuId`)
FROM talscore ,tablcourse, tblteacher
WHERE  tblteacher.`TeaName`='叶平' AND tblteacher.`TeaId`=tablcourse.`TeaId` 
AND tablcourse.`CourseId`=talscore.`CourseId` )

结果：