大家好,又见面了,我是你们的朋友全栈君。
连接查询
SELECT Student.*,Study.*
FEOM Student,Study
WHERE Student.Sno=Study.Sno /*将Student与Study中同一学生的元祖连接起来*/
自然连接:在等值连接中把目标中重复的属性列去掉的连接查询
SELECT Student.Sno,SName,SSex,Sdept,Cno,GradeFROM Student,StudyWHERE Student.Sno=Study.Sno
结果:
SELECT C1.Cpno
FEOM Course AS C1,Course AS C2 --为Course表起两个别名C1、C2
WHERE C1.Pcno=C2.Cno --两个Course表的连接
查询结果:
左外连接:根据左表的记录,在被连接的右表中找出符合条件的记录与之匹配,找不到匹配的,用null填充
查询缺少成绩的的学生号和课程号:
SELECT Student.Sno,CnoFROM StudentLEFT JOIN StudyON Student.Sno=Study.SnoWHERE Grade IS NULL
SELECT Student.Sno AS 学号,SName AS 姓名, Grade AS 成绩FROM StudentLEFT JOIN Study ON Student.Sno=Study.Sno
SELECT Student.Sno AS 学号,SName AS 姓名, Grade AS 成绩FROM Study RIGHT JOIN StudentON Study.Sno=Student.Sno
–1、WHRER 语句
–2、INNER JOIN.. 语句
SELECT Student.SName AS 学生姓名,Grade AS 成绩,CName AS 课程名
FROM Student
INNER JOIN Study ON Student.Sno=Study.Sno
INNER JOIN Course ON Study.Cno=Course.Cno
WHERE Course.Cno='C601'
SELECT Student.SName AS 学生姓名,Grade AS 成绩,CName AS 课程名
FROM Student,Course,Study
WHERE Student=Study.Sno AND Study.cno=Course.Cno ADN Course.Cno=C601
嵌套查询
SELECT SNameFROM StudentWHERE Sex='女' AND Sno NOT IN( SELECT Sno FROM Stduy WHERE Grade<90)
SELECT Sno,SNameFROM StudentWHERE Sno IN( SELECT Sno FROM Study WHERE Cno IN ( SELECT Cno FROM Course WHERE CName='高等数学' ))
相当于连接查询:
SELECT Student.Sno,SName
FROM Student,Course,Study
WHERE Student.Sno=Study.Sno AND Course.Cno=Study.Cno AND Course.CName='高等数学'
SELECT SName FROM Student
WHERE Sno IN
(
SELECT Study1.Sno
FROM Study AS Study1
JOIN Study AS Study2
ON Study1.Sno=Study2.Sno
WHERE Study1.Cno='C601' AND Study2.Cno='C602'
)
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/136906.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...