SQL Server2017(三)数据查询

二、连接查询

若一个查询同时涉及两个以上的表,则称之为连接查询。连接查询是关系数据库中最主要的查询,包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。
1.等值与非等值连接查询
例1:等值连接(嵌套循环连接算法)

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

例2:自然连接(特殊等值连接)

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

例3:复合条件连接查询

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

2.自身连接

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

3.外连接(保留悬浮元组)

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

4.多表连接

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

三、嵌套查询

1.带有IN谓词的子查询
在嵌套查询中,子查询的结果往往是一个集合,所以谓词IN是嵌套查询中最经常使用的谓词。
例1

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN 
    (SELECT Sdept
     FROM Student
     WHERE Sname='刘晨');
也可以用自身连接来完成
SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';
或用比较运算符
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = 
    (SELECT Sdept
     FROM Student
     WHERE Sname='刘晨');

例2

SELECT Sno,Sname
FROM Student
WHERE Sno IN
    (SELECT Sno
     FROM SC
     WHERE Cno IN
	     (SELECT Cno
	      FROM Course
	      WHERE Cname='信息系统'));

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Cname='信息系统';

2.带有比较运算符的子查询

SELECT Sno,Cno
FROM SC x
WHERE Grade>=
    (SELECT AVG(Grade)
     FROM SC y
     WHERE y.Sno=x.Sno);

3.带有ANY(SOME)或ALL谓语的子查询
例1

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
			   FROM Student
			   WHERE Sdept='CS')
AND Sdept<>'CS';
或用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage<
	(SELECT MAX(Sage)
	 FROM Student
	 WHERE Sdept='CS')
AND Sdept<>'CS';

例2

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL
    (SELECT Sage
     FROM Student
     WHERE Sdept='CS')
AND Sdept<>'CS';
或用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage<
    (SELECT MIN(Sage)
     FROM Student
     WHERE Sdept='CS')
AND Sdept<>'CS';

4.带有EXISTS谓词的子查询
例1

SELECT Sname
FROM Student
WHERE EXISTS
    (SELECT *
     FROM SC
     WHERE Sno=Student.Sno AND Cno='1');
或连接运算实现
SELECT Sname
FROM Student,SC
WHERE SC.Sno=Student.Sno AND Cno='1';

例2

SELECT Sname
FROM Student
WHERE NOT EXISTS
    (SELECT *
     FROM SC
     WHERE Sno=Student.Sno AND Cno='1');

例3

SELECT Sno,Sname,Sdept
FROM Student x
WHERE EXISTS
    (SELECT *
     FROM Student y
     WHERE y.Sname='刘晨' AND y.Sdept=x.Sdept);

用EXIST/NOT EXIST实现全称量词的查询。
例1

SELECT Sname
FROM Student
WHERE NOT EXISTS
   (SELECT *
    FROM Course
    WHERE NOT EXISTS   
	   (SELECT *
	    FROM SC
	    WHERE Student.Sno=Sno AND Cno=Course.Cno));

例2:查询至少选修了学生201215122选修的全部课程的学生号码

SELECT DISTINCT Sno
FROM SC x
WHERE NOT EXISTS
    (SELECT *
     FROM SC y
     WHERE y.Sno='201215122' AND NOT EXISTS
	     (SELECT *
	      FROM SC z
	      WHERE z.Sno=x.Sno AND z.Cno=y.Cno));

此博客中的热门博文

Rational Rose 2007破解版下载、安装教程

SQL概述

1.25USD,V2Ray搭建,让你奔向自由!