数据库查询事例

连接查询

SELECT Teacher.教师编号, 教师姓名, 课程号 FROM Teacher, Course WHERE(Teacher.教师编号 = Course.教师编号) AND (教师姓名 = '刘伟');

自连接查询

SELECT X.教师名, X.工资, Y.工资 FROM Teacher X, Teacher Y WHERE X.工资 > Y.工资 AND Y.教师名 = '刘伟';SELECT FIRST.课程号, SECOND.先修课程号 FROM Course FIRST, Course SECOND WHERE FIRST.先修课程号 = SECOND.课程号;

嵌套子查询

SELECT 教师编号, 教师姓名 FROM Teacher WHERE 职称 = (SELECT 职称 FROM Teacher WHERE 教师姓名 = ‘刘伟’);SELECT 教师姓名 FROM Teacher WHERE 工资 < (SELECT avg(工资) FROM Teacher);SELECT 姓名 FROM  Students WHERE 学号IN(SELECT学号 FROM Grade WHERE 成绩 < (SELECT avg(成绩) FROM Grade));

相关子查询

//顺序：选取父查询表中的第一行记录，子查询利用此行中的属性，返回结果，判断此行是否满足条件，放入结果集，重复执行SELECT DISTINCT C1.课程名 FROM Course C1 WHERE C1.课程名 IN(SELECT C2.课程名 FROM Course C2 WHERE C1.课程号 != C2.课程号);SELECT T1.教师姓名, T1.工资, T1.民族 FROM Teacher T1 WHERE T1.工资 < (SELECT avg(T2.工资) FROM Teacher T2 WHERE T2.民族 = T1.民族);

副HAVING子句

SELECT T1.民族 FROM Teacher T1 GROUP BY  T1.民族 HAVING max(T1.工资) >= (SELECT 1.5*avg(T2.工资) FROM Teacher T2 WHERE T2.民族 = T1.民族);

副EXISTS子查询

//以下三种查询结果等价SELECT 学号, 姓名 FROM  Students WHERE 学号IN(SELECT学号 FROM Grade WHERE 课程号 like 'C01');SELECT 学号, 姓名 FROM  Students WHERE EXISTS(SELECT* FROM Grade WHERE Students.学号 = Grade.学号AND课程号 like 'C01');SELECT 学号， 姓名 FROM  Students WHERE  NOT EXISTS(SELECT* FROM Grade WHERE Students.学号 = Grade.学号 AND 课程号 like 'C01');

视图查询

CREATE VIEW CS_Stu(课程编号, 选课人数) AS SELECT 课程名, 选课人数, count(课程编号) as 选课人数 from Grade WHERE 课程编号IN(SELECT 课程名 FROM Course);CREATE VIEW S_Stu_G(学号, 姓名, 课程名, 成绩) AS SELECT 学号, 姓名, 课程名, 成绩 FROM Students WHERE 课程名 IN(SELECT 课程编号 FROM Course WHERE 成绩 IN(SELECT 成绩 FROM Grade WHERE成绩 < 60));