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;web
#查找全部姓李同窗的学生成绩
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,10svg
USE schoolcode
SELECT * FROM student
SELECT * FROM grade
SELECT * FROM subject
SELECT * FROM result
SELECT * FROM subject
WHERE subjectName=‘U1’xml
#查询密码长度不足六位的学员
SELECT studentNo,studentName,LoginPwd,GradeName
FROM student s,grade g
WHERE s.GradeId
=g.gradeid
AND LENGTH(LoginPwd)<6 #查询密码长度不足六位的学员it
#查询某同窗的全部考试成绩
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 subjectnamewebkit
#使用表链接的方式查询全部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;di
#使用子查询方式查询“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%’
));co
#使用子查询方式+表链接方式混合查询“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%’);