where
本质:在where语句中嵌套一个子查询语句
where (select*from)
-- 1.查询 高等数学-1 的所有考试结果(学号,科目名称,成绩) 降序
-- 方式一: 使用连接查询
SELECT `studentno`,`subjectname`,`studentresult`
FROM result AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
-- 方式二: 使用子查询(由里及外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM result AS r
WHERE `subjectno`=(
SELECT `subjectno`
FROM `subject`
WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC
-- 查询所有该科目的学生的学号
SELECT `subjectno`
FROM `subject`
WHERE subjectname = '高等数学-1'
-- 练习 分数不小于80分的学生姓名与学号
-- 去重 distinct
-- 在此基础上增加一项 高等数学-2
SELECT DISTINCT s.`studentno`,`studentname`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
WHERE `studentresult`>=80 AND `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-2'
)
-- 改造写法 子查询进行嵌套 (由里及外)
SELECT studentno,studentname FROM student
WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult>60
AND subjectno =(
SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-2'
)
)
select完整的语法:
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}];
-- 指定查询的记录从那条到那条
[]括号代表可选 {}括号代表必选