比如:
SELECT Cname,Grade
FROM SC,Courses
WHERE SC.Cno = Courses.Cno AND Sno = '200002002';
这个就是多表的了,两个表SC和Courses
其中WHERE 语句中的SC.Cno = Courses.Cno就是他们的连接条件后面的Sno = ‘2000002002’自然就是查询条件
比较经典的一个
SELECT Students.Sno,Sname.AVG(Grade)
FROM SC,Students
WHERE Students.Sno = SC.Sno
GROUP BY Students.Sno,Sname
HAVING AVG(Grade)>85;
这个经典不用分析
这个
SELECT S2.Sname
FROM Students S1,Students S2
Where S1.Brithday = S2.Birthday AND
S1.Sname = 'linyan'AND
S2.Sname<>'linyan';
这个简直太好理解选取一个生日和林艳一样,但是名字不一样的同学
嵌套查询太简单了,一句话就是IN查询之类
然后有两类,不相关子查询和相关子查询,比如子查询中的条件其中的一个量需要夫查询的结果才能执行,那就是相关子查询
①IN:
<元组> [NOT] IN <子查询>
举例
SELECT Speciality
FROM Students
WHERE Sex = 'nv' AND
Speciality IN
(SELECT Speciality
FROM Students
WHERE Sname = 'linyan')
一目了然毫无可以分析之点
②集合比较:
<值表达式> ceta ALL|SOME|ANY<子查询>
SOME和ANY一样就是这个也毫无可分析
举例:
SELECT Brithday
FROM Students
WHERE Speciality <>'软件工程' AND
year (Birthday) > ALL
(SELECT year(Brithday)
FROM Students
WHERE Speciality = '软件工程'
还可以
SELECT Sno,Sname,Speciality,year(Brithday)
FROM Students
WHERE Speciality<>'软件工程' AND
year(Brithday)>
(SELECT MAX(year(Brithday))
FROM Students
WHERE Speciality = '软件工程')
③EXISTS<子查询>
这个都不想多说
直接上例子:
SELECT Sno,Sname
FROM Students S
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno = S.Sno AND
Cno = 'CS$)#');
④检查是否有重复元组
例子:
SELECT Tname,Title
FROM Teachers T
WHERE UNIQUE
(SELECT Cno
FROM Teachers TC
WHERE T.Tno = TC.Tno);
子查询导出的表 AS
给个例子一目了然
SELECT Cno,Avg_grade
FROM (SELECT Cno,AVG(Grade)
FROM SC
GROUP BY Cno) AS Course_avg_grade(Cno,Avg_grade)
WHERE Avg_grade > 85;
集合运算
元组集表达式 集合运算符 【ALL】元组表达式,
其中由UNION并,INTERSECT交,EXCEPT差
例子
SELECT Sno
FROM SC
WHERE Cno = 'CS301'
UNION
SELECT Sno
FROM SC
WHERE Cno = 'CS306'