MySQL 子查询和表连接查询

SELECT * FROM grade;
SELECT * FROM result;
SELECT * FROM student;
SELECT * FROM subject;
SELECT s.StudentNo ,s.StudentName ,j.SubjectName ,r.StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN subject j
ON r.SubjectNo=j.SubjectNo
WHERE r.StudentResult>80 AND SubjectName=‘JAVA第一学年’
ORDER BY r.StudentResult DESC
LIMIT 0,5;

#查找所有姓李同学的学生成绩
SELECT s.StudentName,j.SubjectName,r.StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN subject j
ON r.SubjectNo=j.SubjectNo
WHERE s.StudentName LIKE ‘%李%’ AND r.StudentResult
ORDER BY r.StudentResult DESC
LIMIT 0,10

USE school

SELECT * FROM student
SELECT * FROM grade
SELECT * FROM subject
SELECT * FROM result
SELECT * FROM subject WHERE subjectName=‘U1’

#查询密码长度不足六位的学员
SELECT studentNo,studentName,LoginPwd,GradeName
FROM student s,grade g
WHERE s.GradeId=g.gradeid
AND LENGTH(LoginPwd)<6 #查询密码长度不足六位的学员

#查询某同学的所有考试成绩
SELECT subjectName AS 科目名称,StudentResult AS 考试成绩
FROM subject AS s
INNER JOIN result AS r
ON s.subjectno=r.subjectNo
INNER JOIN student
ON r.StudentNo= student.StudentNo
WHERE studentname=‘郭靖’
GROUP BY subjectname

#使用表连接的方式查询所有XX年级学员学号，姓名及年级
SELECT studentNo 学号, studentName 姓名,gradename 年级
FROM student s , grade g
WHERE s.GradeId=g.GradeId
AND gradename=‘大一’;

#使用子查询的方式查询所有XX年级学员学号，姓名及ID
SELECT studentNo 学号, studentName 姓名,GradeId 年级ID
FROM student s
WHERE gradeid IN(SELECT gradeid FROM grade)
ORDER BY gradeid ;

#使用表连接方式查询“Java”课程至少一次考试刚好等于60分的学生姓名
SELECT studentName ,SubjectName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN subject sj
ON r.SubjectNo=sj.SubjectNo
WHERE sj.SubjectName LIKE ‘%Java%’
AND r.StudentResult=60;

#使用子查询方式查询“Java”课程至少一次考试刚好等于60分的学生姓名
SELECT studentNo,studentName
FROM student s
WHERE s.StudentNo
IN(SELECT StudentNo FROM result r WHERE r.StudentResult=60 AND SubjectNo
IN(SELECT SubjectNo FROM subject sj WHERE sj.SubjectName LIKE ‘%Java%’
));

#使用子查询方式+表连接方式混合查询“Java”课程至少一次考试刚好等于60分的学生姓名
SELECT studentNo,studentName
FROM student s
WHERE s.StudentNo
IN(SELECT StudentNo FROM result r
INNER JOIN subject sj
ON r.SubjectNo=sj.SubjectNo
WHERE r.StudentResult=60 AND sj.SubjectName LIKE ‘%Java%’);