组合查询
★使用子查询
•子查询:指嵌套在其他查询中的查询,子查询也称为内部查询。
•子查询的分类:相关子查询、非相关子查询。
•子查询的形式:SELECT ColumnA,(子查询) AS ColumnB FROM 表名;
•使用子查询的方法:△完整SQL结构:SELECT 列1,列2,...
FROM 表1,表2,...
WHERE 列1=值,...
GROUP BY 列1,(列1)=值,...
HAVING 聚合函数(列1)=值,...
ORDER BY 列1,列2,...
△嵌套子查询的位置:SELECT、FROM 、WHERE 、GROUP BY 、HAVING、 ORDER BY
△在SELECT中嵌套:SELECT 列1,(子查询) AS 别名 FROM 表名;(查询的值只能是单行或单列)
eg:SELECT StudentID,Name,
(SELECT COUNT(*) FROM StudentExam
WHERE StudentExam.StudentID = Student.StudentID)
AS ExamsTaken
FROM Student
ORDER BY ExamsTaken DESC;
△在FROM中嵌套:SELECT ColumnA FROM(子查询);
eg:CREATE TABLE t_stu2 LIKE t_stu;
SELECT * FROM t_stu2;
△在WHERE中嵌套:SELECT ColumnA FROM TABLEA WHERE ColumnA = (子查询);
比较运算符:>、<、>=、<=、!=
in 和not in 运算符
运算符:(ALL、EXISTS、ANY)
•子查询:指嵌套在其他查询中的查询,子查询也称为内部查询。
•子查询的分类:相关子查询、非相关子查询。
•子查询的形式:SELECT ColumnA,(子查询) AS ColumnB FROM 表名;
•使用子查询的方法:△完整SQL结构:SELECT 列1,列2,...
FROM 表1,表2,...
WHERE 列1=值,...
GROUP BY 列1,(列1)=值,...
HAVING 聚合函数(列1)=值,...
ORDER BY 列1,列2,...
△嵌套子查询的位置:SELECT、FROM 、WHERE 、GROUP BY 、HAVING、 ORDER BY
△在SELECT中嵌套:SELECT 列1,(子查询) AS 别名 FROM 表名;(查询的值只能是单行或单列)
eg:SELECT StudentID,Name,
(SELECT COUNT(*) FROM StudentExam
WHERE StudentExam.StudentID = Student.StudentID)
AS ExamsTaken
FROM Student
ORDER BY ExamsTaken DESC;
△在FROM中嵌套:SELECT ColumnA FROM(子查询);
eg:CREATE TABLE t_stu2 LIKE t_stu;
SELECT * FROM t_stu2;
△在WHERE中嵌套:SELECT ColumnA FROM TABLEA WHERE ColumnA = (子查询);
比较运算符:>、<、>=、<=、!=
in 和not in 运算符
运算符:(ALL、EXISTS、ANY)