MySQL-子查询

子查询

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}];
	-- 指定查询的记录从那条到那条
	
[]括号代表可选 {}括号代表必选