SQL语句大全实例(2)

7. 表达式与函数的使用

例 29 查询各课程的学时数。（算术表达式由算术运算符＋、－、 * 、／与列名或数值常量所组成。）

SELECT CNAME,COURSE_TIME ＝ CREDIT*16 FROM COURSES 例 30 找出教师的最小年龄。（内部函数： SQL 标准中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函数，称之为聚集函数（ Set Function ）。 COUNT 函数的结果是该列统计值的总数目， SUM 函数求该列统计值之和， AVG 函数求该列统计值之平均值， MAX 函数求该列最大值， MIN 函数求该列最小值。） SELECT MIN(AGE) FROM TEACHERS

例 31 统计年龄小于等于 22 岁的学生人数。（统计） SELECT COUNT(*) FROM STUDENTS WHERE AGE < ＝ 22

例 32 找出学生的平均成绩和所学课程门数。

SELECT SNO, AVG(GRADE), COURSES ＝ COUNT(*) FROM ENROLLS GROUP BY SNO

例 34 找出年龄超过平均年龄的学生姓名。 SELECT SNAME FROM STUDENTS WHERE AGE ＞

(SELECT AVG(AGE) FROM STUDENTS)

例 35 找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩。（ GROUP BY 与 HAVING

GROUP BY 子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。

GROUP BY 子句总是跟在 WHERE 子句后面，当 WHERE 子句缺省时，它跟在 FROM 子句后面。

HAVING 子句常用于在计算出聚集之后对行的查询进行控制。） SELECT CNO, AVG(GRADE), STUDENTS ＝ COUNT(*) FROM ENROLLS GROUP BY CNO

HAVING COUNT(*) >= 3

8. 相关子查询

例 37 查询没有选任何课程的学生的学号和姓名。（当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（ Correlated Subquery) 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。） SELECT SNO, SNAME FROM STUDENTS WHERE NOT EXISTS (SELECT *

FROM ENROLLS

WHERE ENROLLS.SNO=STUDENTS.SNO) 例 38 查询哪些课程只有男生选读。 SELECT DISTINCT CNAME FROM COURSES C WHERE ' 男 ' ＝ ALL (SELECT SEX

FROM ENROLLS ， STUDENTS

WHERE ENROLLS.SNO=STUDENTS.SNO AND ENROLLS.CNO=C.CNO)

例 39 要求给出一张学生、籍贯列表，该表中的学生的籍贯省份，也是其他一些学生的籍贯省份。

SELECT SNAME, BPLACE FROM STUDENTS A WHERE EXISTS (SELECT *

FROM STUDENTS B

WHERE A.BPLACE=B.BPLACE AND A.SNO < > B.SNO)

例 40 找出选修了全部课程的学生的姓名。

本查询可以改为：查询这样一些学生，没有一门课程是他不选修的。 SELECT SNAME FROM STUDENTS WHERE NOT EXISTS (SELECT *

FROM COURSES

WHERE NOT EXISTS (SELECT *

FROM ENROLLS

WHERE ENROLLS.SNO ＝ STUDENTS.SNO

AND ENROLLS.CNO ＝ COURSES.CNO))

9. 关系代数运算

例 41 设有某商场工作人员的两张表：营业员表 SP_SUBORD 和营销经理表 SP_MGR ，其关系数据模式如下：

SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE) SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)

其中，属性 SALPERS_ID 为工作人员的编号 , SALPERS_NAME 为工作人员的姓名 , MANAGER_ID 为所在部门经理的编号 , OFFICE 为工作地点。 若查询全部商场工作人员，可以用下面的 SQL 语句： (SELECT * FROM SP_SUBORD) UNION

(SELECT * FROM SP_MGR) 或等价地用下面的 SQL 语句： SELECT *

FROM (TABLE SP_SUBORD UNION TABLE SP_MGR) （ 2 ） INTERSECT

(SELECT * FROM SP_SUBORD) INTERSECT

(SELECT * FROM SP_MGR) 或等价地用下面的 SQL 语句： SELECT *

FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR) 或用带 ALL 的 SQL 语句：

(SELECT * FROM SP_SUBORD) INTERSECT ALL

(SELECT * FROM SP_MGR) 或

SELECT *

FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR) （ 3 ） EXCEPT

(SELECT * FROM SP_MGR) EXCEPT

(SELECT * FROM SP_SUBORD) 或等价地用下面的 SQL 语句： SELECT *

FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD) 或用带 ALL 的 SQL 语句： (SELECT * FROM SP_MGR) EXCEPT ALL

(SELECT * FROM SP_SUBORD)

例 42 查询籍贯为四川、课程成绩在 80 分以上的学生信息及其成绩。（自然连接） (SELECT * FROM STUDENTS

WHERE BPLACE=? 四川 ?) NATURAL JOIN

(SELECT * FROM ENROLLS WHERE GRADE >=80)

例3.43 列出全部教师的姓名及其任课的课程号、班级。

（外连接与外部并外连接允许在结果表中保留非匹配元组，空缺部分填以 NULL 。外连接的作用是在做连接操作时避免丢失信息。 外连接有 3 类：

（ 1 ）左外连接（ Left Outer Join ）。连接运算谓词为 LEFT [OUTER] JOIN ，其结果表中保留左关系的所有元组。

（ 2 ）右外连接（ Right Outer Join ）。连接运算谓词为 RIGHT [OUTER] JOIN ，其结果表中保留右关系的所有元组。

（ 3 ）全外连接（ Full Outer Join ）。连接运算谓词为 FULL [OUTER] JOIN ，其结果表中保留左右两关系的所有元组。） SELECT TNAME, CNO, CLASS

FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)

10. SQL 的数据操纵

例 44 把教师李映雪的记录加入到教师表 TEACHERS 中。（插入） INSERT INTO TEACHERS

VALUES(1476 ， ' 李映雪 ' ， 44 ， ' 副教授 ') 例 45 成绩优秀的学生将留下当教师。

INSERT INTO TEACHERS (TNO ， TNAME) SELECT DISTINCT SNO ， SNAME FROM STU …… 此处隐藏：2948字，全部文档内容请下载后查看。喜欢就下载吧 ……