| 失效链接处理 |
|
sql高级查询函数 PDF 下载
本站整理下载：
相关截图：
主要内容：
一、带有比较运算符的子查询
例1、查询实际选课人数最多的教师信息
SELECT * from t_teacher WHERE tid =ANY(
SELECT tid FROM t_teachcourse WHERE cid =ANY
(SELECT cid FROM t_score GROUP BY cid HAVING COUNT(*)>=
(SELECT MAX(cous) FROM (SELECT COUNT(*) as cous FROM t_score GROUP BY cid) as t)
));
例2、查询java成绩最好的学生信息
SELECT * FROM t_student WHERE sid =ANY(
SELECT sid FROM t_score WHERE score>=
(SELECT max(score) FROM t_score WHERE cid =(
SELECT cid FROM t_course WHERE cname='java'))
AND cid=(SELECT cid FROM t_course WHERE cname='java') );
例3、查询选修课成绩最高的科目信息
SELECT * FROM t_course WHERE cid =ANY
(SELECT cid FROM t_score WHERE score>=
(SELECT MAX(score) FROM t_score)
);
二、带有IN 运算符的子查询
例1：查询选修课程的学生信息
SELECT * FROM t_student WHERE sid in(
SELECT sid FROM t_score);
例2：查询选修了课程名为‘音乐’ 的学生学号和姓名
SELECT sid,sname FROM t_student WHERE sid in(
SELECT sid FROM t_score WHERE cid in(
SELECT cid FROM t_course WHERE cname='音乐')
);
例3：查询选修课超过2门的学生学号、课程号、课程名
SELECT sid,t_course.cid,cname FROM t_score,t_course WHERE t_course.cid=t_score.cid
AND sid in (SELECT sid FROM t_score GROUP BY sid HAVING count(*)>2) ORDER BY sid ;
例4: 查询所选科目的成绩都超过80分的学生信息
SELECT * FROM t_student WHERE sid in(
SELECT sid FROM t_score GROUP BY sid HAVING MIN(score)>80);
三、带有any 或all运算符的子查询
例1：在学生表中查询比2班中某一学生出生日日期小的学生信息
SELECT * from t_student WHERE sbirthday<ANY
(SELECT sbirthday FROM t_student WHERE sclass=2);
例2：在学生表中查询比2班中某一学生出生日日期小的其他班的学生信息
SELECT * from t_student WHERE sclass!=2 AND sbirthday<ANY
(SELECT sbirthday FROM t_student WHERE sclass=2);
例3：在学生表中查询比2班中所有学生出生日日期小的其他班的学生信息
SELECT * from t_student WHERE sclass!=2 AND sbirthday<ALL
(SELECT sbirthday FROM t_student WHERE sclass=2);
四、带有EXISTS的子查询
例1:查询所有选修课程的学生的学号和姓名
SELECT sid,sname FROM t_student WHERE EXISTS (SELECT * FROM t_score WHERE sid=t_student.sid);
例2:查询所有选修了’mysql’的学生的学号和姓名
SELECT sid,sname FROM t_student WHERE EXISTS (
SELECT * FROM t_course WHERE EXISTS(
SELECT * FROM t_score WHERE sid=t_student.sid AND cid=t_course.cid AND cname='mysql')
);
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
