SQL语句之多表查询

最新推荐文章于 2024-03-20 16:19:43 发布

陈勇劲

最新推荐文章于 2024-03-20 16:19:43 发布

阅读量318

点赞数

分类专栏： SQL(数据库) 文章标签： sql mysql 数据库 sqlite

本文链接：https://blog.csdn.net/p715306030/article/details/115363739

版权

SQL(数据库) 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

SELECT a.Sno,a.Sname,Ssex,Sage,Sdept,cno grade FROM student a JOIN sc ON (a.Sno=sc.Sno);

Grade>=(SELECT AVG(grade) FROM sc scy WHERE scx.sno=scy.sno)AND 
student.Sno=scx.Sno AND scx.Cno=course.Cno;
 
SELECT Student.Sno,Sname,Cname,Grade 
FROM student,sc,course
WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno;

SELECT Sname FROM student,sc Where student.Sno=sc.Sno AND sc.Cno='2';
 

SELECT scx.cno,Sname,cname,course.cno,grade FROM SC scx ,course,student WHERE 
SELECT a.Sno,a.Sname,a.Sdept
FRom student a,student b
WHERE a.Sdept=b.Sdept and b.Sname="刘晨";

SELECT student.Sno,Sname,Cname FROM student,sc,course WHERE  student.Sno=sc.Sno AND sc.Cno=course.Cno AND Cname="信息系统";

SELECT Sname ,Sage FROM student WHERE Sage<ANY(SELECT Sage FROM student WHERE Sdept='CS') AND Sdept<>'CS' ;
SELECT * FROM sc,student,course WHERE sc.Sno= student.Sno
					AND sc.Cno=course.Cno;

SELECT DISTINCT Sno
From sc scx
WHERE NOT EXISTS(
	SELECT * FROM sc scy
	WHERE NOT E

#全称量词，不存在全程量词需要用存在量词转换，p=-（-p）
#离散数学，蕴含表达式p->q=-q V q
SELECT Sname FROM student
WHERE NOT EXISTS 
	(SELECT * FROM course WHERE NOT EXISTS
			(SELECT * FROM sc ,student,course WHERE sc.Sno= student.Sno
					AND sc.Cno=course.Cno
		)
	);
XISTS(
		SELECT * FROM sc scz
		WHERE scz.Sno=scx.sno AND scz.Cno =scy.Cno
		)
);



#WHERE 语句不能加聚合函数，having可以使用聚合函数