SQL语句学习手册实例版.doc

1、整理SQL 语句学习手册实例版 表操作 例 1 对于表的教学管理数据库中的表 STUDENTS ，可以定义如下： CREATE TABLE STUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACE CHAR(20) PRIMARY KEY(SNO) 例 2 对于表的教学管理数据库中的表 ENROLLS ，可以定义如下： CREATE TABLE ENROLLS (SNO NUMERIC(6,0) NOT NULL CNO CHAR(4) NOT NULL G

2、RADE INT PRIMARY KEY(SNO,CNO) 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 。 DR

3、OP TABLE TEACHER 例 5 在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50) /增加Alter table teachersDrop column ADDR /在 MSSQL中删除字段例 6 把 STUDENTS 表中的 BPLACE 列删除，并且把引用 BPLACE 列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例 7 补充定义 ENROLLS 表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO) ； 视

4、图操作（虚表） 例 9 建立一个只包括教师号、姓名和年龄的视图 FACULTY 。 ( 在视图定义中不能包含 ORDER BY 子句 ) CREATE VIEW FACULTY AS SELECT TNO, TNAME, AGE FROM TEACHERS 例 10 从学生表、课程表和选课表中产生一个视图 GRADE_TABLE ， 它包括学生姓名、课程名和成绩。 CREATE VIEW GRADE_TABLE AS SELECT SNAME,CNAME,GRADE FROM STUDENTS,COURSES,ENROLLS WHERE STUDENTS.SNO ENROLLS.SNO AND

5、 COURSES.CNO=ENROLLS.CNO 例 11 删除视图 GRADE_TABLE DROP VIEW GRADE_TABLE RESTRICT 索引操作 例 12 在学生表中按学号建立索引。 CREATE UNIQUE INDEX ST ON STUDENTS (SNO,ASC) /在 MSSQL中没有 ASC例 13 删除按学号所建立的索引。 DROP INDEX ST /在 MSSQL中删除要加表名:drop index students.st数据库模式操作 例 14 创建一个简易教学数据库的数据库模式 TEACHING_DB ，属主为 ZHANG 。 CREATE SCHEM

6、A TEACHING_DB AUTHRIZATION ZHANG 例 15 删除简易教学数据库模式 TEACHING_DB 。（ 1 ）选用 CASCADE ，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全部被删除。（ 2 ）选用 RESTRICT ，即本数据库模式下属的基本表、视图、索引等事先已清除，才能删除本数据库模式，否则拒绝删除。） DROP SCHEMA TEACHING_DB CASCADE 单表操作 例 16 找出 3 个学分的课程号和课程名。 SELECT CNO, CNAME FROM COURSES WHERE CREDIT 3 例 17 查询年龄大于

7、 22 岁的学生情况。 SELECT * FROM STUDENTS WHERE AGE 22 例 18 找出籍贯为河北的男生的姓名和年龄。 SELECT SNAME, AGE FROM STUDENTS WHERE BPLACE 河北 AND SEX 男 例 19 找出年龄在 20 23 岁之间的学生的学号、姓名和年龄，并按年龄升序排序。 (ASC （升序）或 DESC （降序）声明排序的方式，缺省为升序。 ) SELECT SNO, SNAME, AGE FROM STUDENTS WHERE AGE BETWEEN 20 AND 23 ORDER BY AGE 例 20 找出年龄小于 2

8、3 岁、籍贯是湖南或湖北的学生的姓名和性别。（条件比较运算符、 和逻辑运算符 AND （与），此外还可以使用的运算符有：（大于）、（大于等于）、（小于等于）、（不等于）、 NOT （非）、 OR （或）等。 谓词 LIKE 只能与字符串联用，常常是 “ 列名 LIKE pattern” 的格式。特殊字符 “_” 和 “%” 作为通配符。 谓词 IN 表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的 OR （或）的缩写。谓词 NOT IN 表示指定的属性不与后面的集合中的某个值相匹配。 谓词 BETWEEN 是 “ 包含于 之中 ” 的意思。）

9、SELECT SNAME, SEX FROM STUDENTS WHERE AGE 23 AND BPLACE LIKE 湖 或 SELECT SNAME, SEX FROM STUDENTS WHERE AGE 23 AND BPLACE IN （ 湖南 ， 湖北 ） 例 22 找出学生表中籍贯是空值的学生的姓名和性别。（在 SQL 中不能使用条件：列名 NULL 。值：） 这里这样写是为了避免使用 is null，可以用 nvl()函数来代替Select t.sname,t.sex From (Select sex, sname ,nvl(bpace,0) bplace From stud

10、ents ) t Where t.bplace=0SELECT SNAME, SEX FROM STUDENTS WHERE BPLACE IS NULL 多表操作 例 23 找出成绩为 95 分的学生的姓名。（子查询） Select s.SnameFrom Students sWhere Exists (Select s.SnoFrom EnrollsWhere s.Sno = Enrolls.SnoAnd Grade = 95)下面的写法在子查询返回多个结果时会出错，sno=(里面是个集合)，所以没办法比较SELECT SNAME FROM STUDENTS WHERE SNO (SELE

11、CT SNO FROM ENROLLS WHERE GRADE 95) 例 24 找出成绩在 90 分以上的学生的姓名。多种学法Select s.SnameFrom Students sWhere Exists (Select s.SnoFrom EnrollsWhere s.Sno = Enrolls.SnoAnd Grade 90) Select s.sname From students s ,enrolls e Where e.sno=s.sno And e.grade90SELECT SNAME FROM STUDENTS WHERE SNO IN (SELECT SNO FROM

12、ENROLLS WHERE GRADE 90) 或 SELECT SNAME FROM STUDENTS WHERE SNO ANY (SELECT SNO FROM ENROLLS WHERE GRADE 90) 例 25 查询全部学生的学生名和所学课程号及成绩。（连接查询） SELECT SNAME, CNO, GRADE FROM STUDENTS, ENROLLS WHERE STUDENTS.SNO ENROLLS.SNO 此题可以进行扩展为：查询全部学生的学生名和所学课程名及成绩。（内连接）select sName,cName,Grade from students as s in

13、ner join enrolls ase on s.sNo=e.sNoinner join course as c on c.cNo=e.cNo例 26 找出籍贯为山西或河北，成绩为 90 分以上的学生的姓名、籍贯和成绩。（当构造多表连接查询命令时，必须遵循两条规则。第一，连接条件(在这里指的是学生的学号 SNO)数正好比表数少 1 （若有三个表，就有两个连接条件 ) ；第二，若一个表中的主关键字是由多个列组成，则对此主关键字中的每一个列都要有一个连接条件（也有少数例外情况） Select s.Sname, s.Bpace, e.GradeFrom Students s, Enrolls eW

14、here s.Sno = e.SnoAnd e.Grade 90And (s.Bpace = 山西 Or s.Bpace = 河北)SELECT SNAME, BPLACE, GRADE FROM STUDENTS, ENROLLS WHERE BPLACE IN ( 山西 ， 河北 ) AND GRADE 90 AND STUDENTS.SNO=ENROLLS.SNO 或者：select sName,BPlace,Grade from students as s inner join enrolls as e on e.sNo=s.sNo where BPlace in (山西,河北) an

15、d Grade=90例 28 查出课程成绩在 80 分以上的女学生的姓名、课程名和成绩。（ FROM 子句中的子查询） Select s.sno,s.sname,ame,e.grade From students s,enrolls e,courses c Where e.sno=s.sno And o=o And s.sex=女And e.grade80;SELECT SNAME,CNAME, GRADE FROM (SELECT SNAME, CNAME , GRADE FROM STUDENTS, ENROLLS,COURSES WHERE SEX 女 ) AS TEMP (SNAME,

16、 CNAME,GRADE) WHERE GRADE 80 在此对其扩展，查询姓名为张春明同学，课程为数据库的成绩。select sName,cName,Grade from(select e.sNo,c.cName,e.Grade from course as c ,enrolls as e where c.cNo=e.cNo)as courseT(sNo,cName,Grade) ,students as s where s.sNo=courseT.sNo and s.sName=张春明 and courseT.cName=数据库表达式与函数的使用 例 29 查询各课程的学时数。（算术表达式

17、由算术运算符、 * 、与列名或数值常量所组成。） 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 岁的学

18、生人数。（统计） SELECT COUNT(*) FROM STUDENTS WHERE AGE (Select Avg(Age) From Students)SELECT SNAME FROM STUDENTS WHERE AGE (SELECT AVG(AGE) FROM STUDENTS) 例 35 找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩。（ GROUP BY 与 HAVING GROUP BY 子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。 GROUP BY 子句总是跟在 WHERE 子句后面，当 WHERE

19、 子句缺省时，它跟在 FROM 子句后面。 HAVING 子句常用于在计算出聚集之后对行的查询进行控制。）Select c.Cname, Avg(Grade), Count(*) 人数From Courses c, Enrolls eWhere e.Cno = c.CnoGroup By c.CnameHaving Count(*) = 3SELECT CNO, AVG(GRADE), STUDENTS COUNT(*) FROM ENROLLS GROUP BY CNO HAVING COUNT(*) = 3 相关子查询 例 37 查询没有选任何课程的学生的学号和姓名。（当一个子查询涉及到一

20、个来自外部查询的列时，称为相关子查询（ Correlated Subquery) 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。） SELECT SNO, SNAME FROM STUDENTS WHERE NOT EXISTS (SELECT * FROM ENROLLS WHERE ENROLLS.SNO=STUDENTS.SNO) 此题可扩展成查询有过不及格（grade B.SNO) 例 40 找出选修了全部课程的学生的姓名。 本查询可以改为：查询这样一些学生，没有一门课程是他不选修的。 SELECT SNAME

21、FROM STUDENTS WHERE NOT EXISTS (SELECT * FROM COURSES WHERE NOT EXISTS (SELECT * FROM ENROLLS WHERE ENROLLS.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, SA

22、LPERS_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 * FR

23、OM 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) EXC

24、EPT (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 。外连接的作用是在做连接操作时避免丢失信息。