SQL 查询语句 SELECT (1)

1. SELECT – FROM – WHERE 句型

算术比较运算符	逻辑运算符	集合成员资格运算符	谓词	聚合函数	嵌套	SELECT查询结果运算符
>, <, >=,<=,=,<>或!=	AND, OR, NOT	IN, NOT IN	EXISTS, ALL, SOME, UNIQUE	AVG, MIN, MAX, SUM, COUNT	F≡(SELECT 语句)	UNION(并),INTERSECT(交), EXCEPT(差)

课堂练习

1. 检索学习课程号为C2的学生学号与成绩

关系代数表达式

SELECT S#, SCORE
FROM SC
WHERE C#='C2'

2. 检索学习课程号为C2的学生学号与姓名

关系代数表达式

SELECT S.S#, SNAME
FROM S, SC
WHERE SC.C# = 'C2' AND S.S# = SC.S# //注意这里是自然连接

3. 检索选修课程名为MATHS的学生学号与姓名

关系代数表达式

SELECT S.S#, SNAME
FROM S, SC, C
WHERE S.S# = SC.S# AND SC.C# = C.C# AND CNAME = 'MATH'

4. 检索选修课程号为C2或C4的学生学号

关系代数表达式

SELECT DISTINCT S# //避免重复，所以加一个DISTINCT
FROM SC
WHERE C# = 'C2' OR C# = 'C4'

5. 检索至少选修课程号为C2和C4的学生学号

关系代数表达式

SELECT X.S#
FROM SC AS X, SC AS Y
WHERE X.S# = Y.S# AND X.C# = 'C2' AND Y.C# = 'C4'

6. 检索不学习C2课程的学生姓名与年龄

关系代数表达式

SELECT SNAME, AGE
FROM S
WHERE S# NOT IN (SELECT S#
					FROM SC
					WHERE C# = 'C2')

1. 检索学习全部课程的学生姓名

关系代数表达式

SELECT SNAME 
FROM S
WHERE NOT EXISTS(SELECT *
				FROM C
				WHERE NOT EXISTS(SELECT *
								FROM SC
								WHERE S.S# = SC.S# AND SC.C# = C.C#))

8. 检索所学课程包含学生S3所学课程的学生学号

关系代数表达式

SELECT S#
FROM S
WHERE NOT EXISTS(SELECT *
				FROM SC AS X
				WHERE X.S#='S3' AND NOT EXISTS(SELECT *
												FROM SC AS Y
												WHERE X.C# = Y.C# AND Y.S# = S.S#))