子查询 .

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  --> 指定结果需满足的条件
    [GROUP BY ...]  --> 指定结果按照哪几个字段来分组
    [HAVING]  --> 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  --> 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}]; --> 指定查询的记录从哪条至哪条

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照ASC升序对记录进行排序； DESC 降序

分页：limit  当前页，页面的大小  eg：limit 0,10

子查询--->在查询语句中的WHERE条件子句中,又嵌套了一个查询

SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学' AND StudentResult>=80

//子查询
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等数学'
)

//分组
SELECT subjectname,AVG(studentresult) AS avg,MAX(studentresult) max,MIN(studentresult) min
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno //通过GROUP BY来分组
HAVING avg>80