SQL语句学习手册实例版Word格式.docx

1、 FOREIGN KEY（SNO） REFERENCES STUDENTS（SNO） FOREIGN KEY（CNO） REFERENCES COURSES（CNO） CHECK （GRADE IS NULL） OR （GRADE BETWEEN 0 AND 100） 例 3根据表的 STUDENTS 表，建立一个只包含学号、姓名、年龄的女学生表。 CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX= 女 ;例 4删除教师表 TEACHER 。 DROPTEACHER 例 5在教师表中增加住址列。 ALTER

2、TABLE TEACHERS ADD （ADDR CHAR（50） 例 6把 STUDENTS 表中的 BPLACE 列删除，并且把引用 BPLACE 列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例 7补充定义 ENROLLS 表的主关键字。ALTER TABLE ENROLLS ADD PRIMARY KEY （SNO,CNO） ；视图操作（虚表） 例 9建立一个只包括教师号、姓名和年龄的视图 FACULTY 。 （ 在视图定义中不能包含 ORDER BY 子句 ） CREATE VIEW FACULTY AS SELEC

3、T TNO, TNAME, AGE FROM TEACHERS 例 10从学生表、课程表和选课表中产生一个视图 GRADE_TABLE ， 它包括学生姓名、课程名和成绩。 CREATE VIEW GRADE_TABLE AS SELECTSNAME,CNAME,GRADE FROMSTUDENTS,COURSES,ENROLLS WHERESTUDENTS.SNO ENROLLS.SNO AND COURSES.CNO=ENROLLS.CNO 例 11删除视图 GRADE_TABLE DROP VIEW GRADE_TABLE RESTRICT 索引操作 例 12在学生表中按学号建立索引。 C

4、REATE UNIQUE INDEX ST ON STUDENTS （SNO,ASC） 例 13删除按学号所建立的索引。 DROP INDEX ST 数据库模式操作 例 14创建一个简易教学数据库的数据库模式 TEACHING_DB ，属主为 ZHANG 。 CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG 例 15删除简易教学数据库模式 TEACHING_DB 。（ 1 ）选用 CASCADE ，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全部被删除。（ 2 ）选用 RESTRICT ，即本数据库模式下属的基本表、视图、索引等事先

5、已清除，才能删除本数据库模式，否则拒绝删除。） DROP SCHEMA TEACHING_DB CASCADE 单表操作 例 16找出 3 个学分的课程号和课程名。 SELECT CNO, CNAME FROM COURSES WHERE CREDIT 3 例 17查询年龄大于 22 岁的学生情况。 SELECT* STUDENTS AGE 22 例 18 找出籍贯为河北的男生的姓名和年龄。 SELECT SNAME, AGE FROM STUDENTS WHERE BPLACE 河北 ANDSEX 男 例 19找出年龄在 20 23 岁之间的学生的学号、姓名和年龄，并按年龄升序排序。 （AS

6、C （升序）或 DESC （降序）声明排序的方式，缺省为升序。 ） SELECT SNO, SNAME, AGE AGE BETWEEN 20 AND 23 ORDERBY AGE 例 20找出年龄小于 23 岁、籍贯是湖南或湖北的学生的姓名和性别。（条件比较运算符、 和逻辑运算符 AND （与），此外还可以使用的运算符有：（大于）、（大于等于）、（小于等于）、（不等于）、 NOT （非）、 OR （或）等。谓词 LIKE 只能与字符串联用，常常是 “ 列名 LIKEpattern” 的格式。特殊字符 “_” 和 “%” 作为通配符。谓词 IN 表示指定的属性应与后面的集合（括号中的值集或某个

7、查询子句的结果）中的某个值相匹配，实际上是一系列的 OR （或）的缩写。谓词 NOT IN 表示指定的属性不与后面的集合中的某个值相匹配。谓词 BETWEEN 是 “ 包含于 之中 ” 的意思。 SELECT SNAME, SEX AGE 23BPLACELIKE 湖 或 IN（ 湖南 ， 湖北 例 22找出学生表中籍贯是空值的学生的姓名和性别。（在 SQL 中不能使用条件：列名 NULL 。在 SQL 中只有一个特殊的查询条件允许查询 NULL 值：BPLACE IS NULL 多表操作 例 23找出成绩为 95 分的学生的姓名。（子查询） SELECT SNAME WHERE SNO （S

8、ELECT SNO ENROLLS GRADE 95） 例 24找出成绩在 90 分以上的学生的姓名。SELECTSNAME SNO IN FROM ENROLLS WHERE GRADE 90） SNO ANY 例 25查询全部学生的学生名和所学课程号及成绩。（连接查询） SNAME, CNO, GRADE STUDENTS, ENROLLS STUDENTS.SNO ENROLLS.SNO 例 26找出籍贯为山西或河北，成绩为 90 分以上的学生的姓名、籍贯和成绩。（当构造多表连接查询命令时，必须遵循两条规则。第一，连接条件数正好比表数少 1 （若有三个表，就有两个连接条件 ） ；第二，若

9、一个表中的主关键字是由多个列组成，则对此主关键字中的每一个列都要有一个连接条件（也有少数例外情况） SNAME, BPLACE, GRADE BPLACE IN （ 山西 ， 河北 ）GRADE 90AND STUDENTS.SNO=ENROLLS.SNO 例 28查出课程成绩在 80 分以上的女学生的姓名、课程名和成绩。（ FROM 子句中的子查询） SNAME,CNAME, GRADE （SELECT SNAME, CNAME , GRADE STUDENTS, ENROLLS,COURSES AS TEMP （SNAME, CNAME,GRADE） GRADE 80 表达式与函数的使用

10、例 29查询各课程的学时数。（算术表达式由算术运算符、 * 、与列名或数值常量所组成。CNAME,COURSE_TIME CREDIT*16 COURSES 例 30找出教师的最小年龄。（内部函数： SQL 标准中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函数，称之为聚集函数（ Set Function ）。 COUNT 函数的结果是该列统计值的总数目， SUM 函数求该列统计值之和， AVG 函数求该列统计值之平均值， MAX 函数求该列最大值， MIN 函数求该列最小值。MIN（AGE） TEACHERS 例 31统计年龄小于等于 22 岁的学生人数。（统计） C

11、OUNT（*） AGE = 3 相关子查询 例 37查询没有选任何课程的学生的学号和姓名。（当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（ Correlated Subquery） 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。 SELECT SNO, SNAME NOT EXISTS （SELECT * WHERE ENROLLS.SNO=STUDENTS.SNO） 例 38 查询哪些课程只有男生选读。 SELECT DISTINCT CNAME COURSES C WHERE ALL （SELECT SE

12、X FROMENROLLS ， STUDENTS WHERE ENROLLS.SNO=STUDENTS.SNO AND ENROLLS.CNO=C.CNO） 例 39要求给出一张学生、籍贯列表，该表中的学生的籍贯省份，也是其他一些学生的籍贯省份。SNAME, BPLACE STUDENTS A EXISTS （SELECT * FROM STUDENTS B WHERE A.BPLACE=B.BPLACE AND A.SNO B.SNO） 例 40找出选修了全部课程的学生的姓名。 本查询可以改为：查询这样一些学生，没有一门课程是他不选修的。WHERE FROM COURSES WHERE EN

13、ROLLS.SNO STUDENTS.SNO AND ENROLLS.CNO COURSES.CNO） 关系代数运算 例 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 为所在部门经理的

14、编号 , OFFICE 为工作地点。若查询全部商场工作人员，可以用下面的 SQL 语句： （SELECT * FROM SP_SUBORD） UNION （SELECT * FROM SP_MGR） 或等价地用下面的 SQL 语句： SELECT * FROM （TABLE SP_SUBORD UNION TABLE SP_MGR） （ 2 ） INTERSECT INTERSECT FROM （TABLE SP_SUBORD INTERSECT TABLE SP_MGR） 或用带 ALL 的 SQL 语句：INTERSECT ALL FROM （TABLE SP_SUBORD INTERSE

15、CT ALL TABLE SP_MGR） （ 3 ） EXCEPT EXCEPT SELECT * FROM （TABLE SP_MGR EXCEPT TABLE SP_ SUBORD） EXCEPTALL 例 42查询籍贯为四川、课程成绩在 80 分以上的学生信息及其成绩。（自然连接） （SELECT * FROM STUDENTS WHERE BPLACE= 四川 ） NATURAL JOIN （SELECT * FROM ENROLLS WHERE GRADE =80） 例3.43 列出全部教师的姓名及其任课的课程号、班级。（外连接与外部并外连接允许在结果表中保留非匹配元组，空缺部分填以

16、 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 OUT

17、ER JOIN TEACHING USING （TNO） SQL 的数据操纵 例 44把教师李映雪的记录加入到教师表 TEACHERS 中。（插入） INSERT INTO TEACHERS VALUES（1476 ， 李映雪 ， 44 ， 副教授 例 45成绩优秀的学生将留下当教师。 INSERT INTO TEACHERS （TNO ， TNAME） SELECT DISTINCT SNO ， SNAME STUDENTS ， ENROLLS WHERE STUDENTS.SNO ENROLLS.SNO AND GRADE 90 例 47把所有学生的年龄增加一岁。（修改） UPDATE STUDENTS SET AGE AGE+1 例 48学生张春明在数据库课考试中作弊，该课成绩应作零分计。 UPDATE ENROLLS SET GRADE 0 WHERE CNO C1 AND 张春明 （SELECT SNAME WHERE STUDENTS.SNO=ENROLLS.SNO） 例 49从教师表中删除年龄已到 60 岁的退休教师的数据。（删除） DELETE FROM TEACHERS AGE 60 SQL 的数据控制