sql表格查询

ALTER TABLE grade CHANGE sname gradename VARCHAR(255) NOT NULL;
/*  左外连
以左边表的数据为主,右边表中无数据匹配用null表示
Select  列名,列名…..  from 主表  left  join  表 on  两表之间关系*/
ALTER TABLE grade CHANGE gradeid gradeid VARCHAR(255);
SELECT g.gradename
FROM grade g LEFT JOIN student s
on s.gradeid=g.gradeid
WHERE sname='wu';-- 年纪名称
SELECT subjects.subname FROM subjects,student,result
WHERE student.sname='wu' AND subjects.subid=result.subid and student.sid=result.sid;-- 学习的科目因为外键约束，所以可以想象为整合表格

#  12. 查询出姓名为王丽丽的所在年级名称并查出学习的科目
SELECT student.stuname,grade.gradename,`subject`.subname
FROM student,grade,`subject` 
WHERE student.gradeid=grade.gradeid AND `subject`.gradeid=grade.gradeid AND student.stuname='王丽丽';

#  13. 查询出姓名为王丽丽参加U2考试所有科目的平均分
SELECT student.stuname,AVG(score) AS '平均分'
FROM student,result,`subject`
WHERE student.stuid=result.stuid AND `subject`.subid=result.subid AND student.stuname='王丽丽';

#  14. 查出王丽丽参加 走进Java编程世界 考试的最后一次考试时间和成绩
SELECT student.stuname,`subject`.subname,result.score,result.examdate
FROM student,result,`subject`
WHERE student.stuid=result.stuid AND `subject`.subid=result.subid AND student.stuname='王丽丽' AND `subject`.subname='算法分析';

#  15. 查出比赵七 年龄大的所有学生的信息
SELECT * FROM student WHERE CAST(stubirth AS datetime)<CAST((SELECT stubirth FROM student WHERE stuname='胡茜') AS datetime);

#  16. 查出成绩表中每门科目考试的平均分 并显示科目名称
SELECT `subject`.subname,AVG(score) FROM result,`subject` WHERE `subject`.subid=result.subid GROUP BY result.subid;

#  17. 查出所有学生最近一次考试的成绩 科目名称 和姓名
SELECT ch1.stuname,ch1.score,ch1.subname,ch1.examdate 
FROM (SELECT student.stuname,result.score,`subject`.subname,result.examdate 
FROM student,`subject`,result
WHERE student.stuid=result.stuid AND `subject`.subid=result.subid ORDER BY result.examdate DESC
) AS ch1 GROUP BY ch1.stuname;

-- SELECT a1.sname FROM (SELECT sid,sname,ssex,sbirth,stel,saddress,gradeid from student WHERE ssex='man') AS a1 WHERE a1.sid='2';