数据库原理与应用chp3SQL语言.ppt

数据库原理与应用,刘伟法() 常熟理工学院计算机学院,第3章 SQL语言关系数据库标准语言,SQL概述 SQL的基本数据类型 SQL的数据定义（create,alter,drop） SQL的数据操纵（insert,update,delete） SQL的数据查询（select） SQL的数据控制（grant,revoke）,2.1 SQL概述,1、SQL语言标准的发展历程 SQL-86 SQL-89 SQL-92 （SQL1） SQL-99 （SQL2） SQL-2003 （SQL3）,2、SQL的特点 一体化（功能强大） 数据查询、数据操纵、数据定义、数据控制于一体,风格统一 高度非过程化 只需提出做什么,无需指明怎么做 面向集合的操作方式 能以多种方式使用 既可以独立使用,也可以嵌入到其他高级语言 简洁（语法简单）：9个动词,类似英语语句,2.1 SQL概述（续）,2.1 SQL概述（续）,3、SQL语言基本概念 基本表 独立存在的表,一个关系对应一个基本表。 视图 虚表。是从一个或几个基本表导出的表。 视图中存放的仅仅是定义,而不是数据,数据仍在基本表中。 索引 加速查询的速度而提供的数据结构,2.2 SQL Server的数据类型,1、数值型 int 4个字节 smallint 2个字节 tinyint 1个字节 numeric 小数 decimal 小数 float 浮点数,2.2 SQL Server的数据类型（续）,2、字符串型 char 固定长度（1-8000） varchar 可变长度（1-8000） text 可存储231-1个字符的文本 image 可存储多种格式的文件,2.2 SQL Server的数据类型（续）,3、日期时间型 datetime 8个字节（毫秒） smalldatetime 4个字节（分钟） 4、货币型 money 8个字节 smallmoney 4个字节,学生课程数据库,学生表：Student(Sno,Sname,Ssex,Sage,Sdept),学生课程数据库,课程表：Course(Cno,Cname,Cpno,Credit),学生课程数据库,选课表：SC(Sno,Cno,Grade),2.3 数据定义,2.3 数据定义（续）,1、数据库的定义与删除 数据库定义 CREATE DATABASE database_name 创建一个数据库及存储该数据库的文件。 每个数据库至少包括两个文件：主数据文件和事务日志文件。,2.3 数据定义（续）,1、数据库的定义与修改与删除 数据库修改 ALTER DATABASE database_name 。 修改数据库。 数据库删除 DROP DATABASE database_name 删除数据库所有文件及磁盘文件。,2、模式的定义与删除 模式定义 CREATE SCHEMA schema_name AUTHORIZATION owner 创建一个模式,并指明模式的所有者。 可以在模式中建立其他数据对象,如基本表,视图,索引等。,2.3 数据定义（续）,2、模式的定义与删除 模式删除 DROP SCHEMA schema_name 删除模式。 CASCADE表示级联删除,删除模式的同时删除模式下的数据库对象。 RESTRICT拒绝删除已定义了数据库对象的模式。,2.3 数据定义（续）,2.3 数据定义（续）,3、基本表的定义、删除及修改 基本表定义 CREATE TABLE （ 列级约束 , 列级约束, ,表级完整性约束定义）,CREATE TABLE Student( Sno char(7) PRIMARY KEY, Sname char(10) UNIQUE, Ssex char(2) CHECK(Ssex=男 or Ssex=女) , Sage tinyint CHECK(Sage=15 and Sage=45), Sdept char(20) DEFAULT 计算机系 ）,2.3 数据定义（续）,CREATE TABLE Course( Cno char(4) PRIMARY KEY, Cname varchar(40) NOT NULL, Cpno char(4), Ccredit tinyint, FOREIGN KEY (Cpno) REFERENCES Course(Cno) ),2.3 数据定义（续）,表级完整性约束条件,列Cpno中数据必须参照列Cno中的数据。,CREATE TABLE SC( Sno char(7) , Cno char(4) , Grade numeric(5,2) , PRIMARY KEY (Sno,Cno), FOREIGN KEY (Cno) REFERENCES Course(Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), ),2.3 数据定义（续）,表级完整性约束条件,参照完整性规则,2.3 数据定义（续）, 表结构的修改 修改列的定义 ALTER TABLE 表名 ALTER COLUMN 如：修改Student的Sdept为40个字符宽度 ALTER TABLE Student ALTER COLUMN Sdept char(40),增加新列 ALTER TABLE 表名 ADD 约束 如：在Student中增加Address（家庭住址）列 ALTER TABLE Student Add Address varchar(30),2.3 数据定义（续）,2.3 数据定义（续）,删除列 ALTER TABLE 表名 DROP COLUMN 如：删除Student的Sage列 ALTER TABLE Student DROP COLUMN Sage,添加约束 ALTER TABLE 表名 ADD CONSTRAINT constraint_name 约束类型() 如：在Student中增加Sno为主关键字 ALTER TABLE Student Add PRIMARY KEY(Sno) 如：添加SC的Sno列的外码约束为Student的Sno ALTER TABLE SC ADD FOREIGN KEY(Sno) REFRENCES Student(Sno),2.3 数据定义（续）,约束名也可以省略,2.3 数据定义（续）,例如：增加课程名必须惟一的约束条件 ALTER TABLE Course ADD CONSTRAINT UnqCname UNIQUE(Cname) 例如：增加成绩约束,成绩在0100之间 ALTER TABLE SC ADD CONSTRAINT ChkGRADE CHECK(Grade=0 AND Grade=100),2.3 数据定义（续）,删除约束 ALTER TABLE 表名 DROP 约束名 如： ALTER TABLE COURSE DROP UnqCname 如： ALTER TABLE SC DROP ChkGRADE,2.3 数据定义（续）, 表的删除 DROP TABLE RESTRICT | CASCADE RESTRICT表示删除表时有限制,若表建立了与其他表的约束、触发器、视图等关系或对象,则不允许删除。 CASCADE表示删除时将与该表相关的约束、视图等一起删除 如：删除表Student DROP TABLE Student,2.3 数据定义（续）,4、索引的建立与删除 索引是加快查询效率的一种手段。 索引是按照基本表中某个（某些）属性列上的值进行排序,以提供多种查找途径。 一个基本表可以建立一个或多个索引。,2.3 数据定义（续）,4、索引的建立与删除 建立索引 CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ),2.3 数据定义（续）,例如： (1)在Student表上建立按学号升序排列的索引 CREATE INDEX Sno_A ON Student(Sno) (2)在Course表的课程名建立惟一索引 CREATE UNIQUE Cname_U ON Course(Cname),2.3 数据定义（续）,例如： (3)在SC表上建立按学号升序,课程号降序排列的索引 CREATE INDEX SC_A ON SC(Sno,Cno DESC),2.3 数据定义（续）,删除索引 DROP INDEX 表名.索引名 例如： 删除SC表上的SC_A索引 DROP INDEX SC.SC_A 注意：索引由系统进行维护,当基本表中数据发生变化时,系统需要对根据数据重新维护索引,因此过多建立索引也会降低数据库的使用效率。,练习：用SQL语句定义下列基本表,CREATE TABLE class( CLASSNAME CHAR(20) PRIMARY KEY, MAXCREDIT SMALLINT, MINCREDIT SMALLINT ) CREATE TABLE student( STUDENTID CHAR(6) PRIMARY KEY, NAME CHAR(8), SEX CHAR(2), BIRTHDAY DATETIME, CLASSNAME , FOREIGN KEY (CLASSNAME) REFERENCES CLASS(CLASSNAME) ),练习：用SQL语句修改基本表的结构,1、在班级信息表(class)增加约束：最大学分限制大于最小学分限制,ALTER TABLE class ADD CONSTRAINT check_c CHECK(MAXCREDITMINCREDIT),2、在学生信息表(student)增加一个属性列： 列名：SAGE 数据类型：整型,ALTER TABLE student ADD SAGE INT,3、在学生信息表(student)增加一个约束： SAGE必须介于1545之间,ALTER TABLE student ADD CONSTRAINT CHECK_S CHECK(SAGE=15 AND SAGE=45),2.4 数据查询功能,1、SELECT的基本结构 SELECT FROM WHERE GROUP BY HAVING ORDER BY,2.4 数据查询功能（续）,2、简单查询 例1 查询全体学生的学号、姓名 SELECT SNO,SNAME FROM STUDENT 注意：输出列的顺序可以与表中定义的列顺序不同。,2.4 数据查询功能（续）,2、简单查询 例2 查询全部列 SELECT * FROM STUDENT SELECT * FROM COURSE SELECT * FROM SC,2.4 数据查询功能（续）,2、简单查询 例3 查询全体学生的姓名和出生年份（需要进行计算的输出列） SELECT SNAME,2009-SAGE FROM STUDENT 为输出列起个列名： SELECT SNAME,2009-SAGE AS BIRTH FROM STUDENT,计算出的出生年份,2.4 数据查询功能（续）,2、简单查询 例4 查询计算机系全体学生的姓名 例5 查询年龄小于20岁的学生的姓名和年龄,SELECT SNAME FROM STUDENT WHERE SDEPT=计算机系,SELECT SNAME,SAGE FROM STUDENT WHERE SAGE20,2.4 数据查询功能（续）,2、简单查询 例6 查询计算机系年龄小于20岁学生的姓名 例7 查询年龄介于22岁到24岁的学生姓名和年龄,SELECT SNAME FROM STUDENT WHERE SDEPT=计算机系 AND SAGE20,SELECT SNAME, SAGE FROM STUDENT WHERE SAGE=22 或：Between and 一般用于数值型的比较 SELECT SNAME,SAGE FROM STUDENT WHERE SAGE BETWEEN 22 AND 24,2.4 数据查询功能（续）,2、简单查询 例8 查询SC表中有哪些学生选修了课程。只需给出学号（消除重复值） 例9 查询信息系、数学系和计算机系学生的姓名和性别。,SELECT DISTINCT SNO FROM SC,SELECT SNAME, SSEX FROM STUDENT WHERE SDEPT IN(信息系, 数学系, 计算机系),2.4 数据查询功能（续）,2、简单查询 例10 查询姓李的同学的基本信息。 例11 查询姓李、张、刘的同学的信息。 例12 查询不姓李、张、刘的同学的信息。,SELECT * FROM STUDENT WHERE SNAME LIKE 李%,SELECT * FROM STUDENT WHERE SNAME LIKE 张李刘%,SELECT * FROM STUDENT WHERE SNAME LIKE 张李刘%,2.4 数据查询功能（续）,2、简单查询 例13 查询无考试成绩的学生的学号。 例14 查询有考试成绩的学生的学号和成绩。,SELECT SNO FROM SC WHERE GRADE IS NULL,SELECT SNO FROM SC WHERE GRADE IS NOT NULL,2.4 数据查询功能（续）,3、查询结果排序 例15 将学生按年龄由小到大排序。 例16 输出选修c02课程学生的成绩,按降序排序。,SELECT * FROM STUDENT ORDER BY SAGE,SELECT * FROM SC WHERE CNO=C02 ORDER BY GRADE DESC,2.4 数据查询功能（续）,4、使用集合函数统计查询 例17 统计学生的总人数。 例18 统计选修了课程的学生的人数。,SELECT COUNT(*) FROM STUDENT,SELECT COUNT (DISTINCT SNO) FROM SC,2.4 数据查询功能（续）,4、使用集合函数统计查询 例19 计算学号为200215121学生的考试成绩总和。 例20 计算c01课程的考试平均成绩。,SELECT SUM(GRADE) FROM SC WHERE SNO=200215121,SELECT AVG(GRADE) FROM SC WHERE CNO=C01,2.4 数据查询功能（续）,4、使用集合函数统计查询 例21 查询最大的学生年龄。 例22,SELECT MAX(SAGE) FROM STUDENT,2.4 数据查询功能（续）,5、分组查询 分组查询是根据某个列或某些列的值将记录分组,分组后,聚集函数对每组数据都进行计算,从而获得各组的统计结果。,2.4 数据查询功能（续）,5、分组查询 例23 统计每门课程的选课人数。 例24 查询每名学生的选课门数和平均成绩。,SELECT CNO,COUNT(*) FROM SC GROUP BY CNO,SELECT SNO,COUNT(*),AVG(GRADE) FROM SC GROUP BY SNO,2.4 数据查询功能（续）,5、分组查询（对分组结果进行筛选） 例25 查询选修了3门以上课程的学生的学号。 例26 查询选课门数大于或等于4门的学生的平均成绩和选课门数。,SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*)3,SELECT SNO,COUNT(*),AVG(GRADE) FROM SC GROUP BY SNO HAVING COUNT(*)=4,Colledge数据库介绍：关系图及字段描述,学号 课程代号 成绩 任课教师工号,练习： 1、查询工号为E0002的教师所承担课程的课程号。 2、查询课程代号为1301的成绩不及格的学生学号和成绩。,cj$,SELECT XH,CJ FROM cj$ WHERE KCDH=1301 AND CJ60,SELECT DISTINCT KCDH FROM cj$ WHERE GH=E0002,学号 课程代号 成绩 任课教师工号,练习： 3、查询学号为99050101的学生的各门课成绩。 4、查询99级学生的成绩情况。,cj$,SELECT KCDH,CJ FROM cj$ WHERE XH=99050101,SELECT * FROM cj$ WHERE XH LIKE 99%,学号 课程代号 成绩 任课教师工号,练习： 5、查询成绩为空值的记录。 6、查询选课的学生总数。,cj$,SELECT * FROM cj$ WHERE CJ IS NULL,SELECT COUNT(DISTINCT XH) FROM cj$,学号 课程代号 成绩 任课教师工号,练习： 7、查询每个学生的选课及格门数和及格课程的平均分（按成绩从高到低排序）。,cj$,SELECT XH,COUNT(*),AVG(CJ) FROM cj$ WHERE CJ=60 GROUP BY XH ORDER BY 3 DESC,学号 课程代号 成绩 任课教师工号,练习： 8、查询选修通过门数超过3门,且平均分在70以上的学生的学号,选修门数,平均成绩。（按成绩从高到低排序）。,SELECT XH,COUNT(*),AVG(CJ) FROM cj$ WHERE CJ=60 GROUP BY XH HAVING COUNT(*)3 AND AVG(CJ)=70 ORDER BY 3 DESC,若查询的字符串中本来就含有统配符%或_,可以使用ESCAPE对统配符进行转义。 例如：查询课程名DB_DESIGN开头的课程号与学分,2.4 数据查询功能（续）,SELECT CNO,cCREDIT FROM COURSE WHERE CNAME LIKE DB_DESIGN% ESCAPE,2.4 数据查询功能（续）,6、连接查询 若在一个查询请求中,涉及到多个表中的数据,则需要进行连接查询。 按连接方式分： 内连接 INNER JOIN 左外连接 LEFT JOIN 右外连接 RIGHT JOIN 全连接 FULL JOIN,2.4 数据查询功能（续）,6、连接查询 内连接 INNER JOIN 例27 查询每个学生的基本信息及其选课情况。,SELECT STUDENT.SNO,SNAME,SSEX, SAGE,SDEPT,CNO,GRADE FROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO,2.4 数据查询功能（续）,6、连接查询 例27 查询每个学生的基本信息及其选课情况。 或写成： SELECT STUDENT.SNO,SNAME,SSEX, SAGE,SDEPT,CNO,GRADE FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO,2.4 数据查询功能（续）,6、连接查询 例28 查询计算机系学生的选课情况,要求输出姓名,课程号,成绩。,SELECT SNAME,CNO,GRADE FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO AND SDEPT=计算机系,2.4 数据查询功能（续）,6、连接查询 例29 查询计算机系学生的选课情况,要求输出姓名,课程名,成绩。(多表连接),SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SDEPT=计算机系,2.4 数据查询功能（续）,6、连接查询 左外连接：LEFT JOIN 例30 查询学生的选课情况,要求输出学号,姓名,课程代号,成绩。(包括未选课学生的信息),SELECT student.SNO,SNAME,CNO,GRADE FROM STUDENT LEFT JOIN SC ON STUDENT.SNO=SC.SNO ORDER BY student.SNO,Colledge数据库介绍：关系图及字段描述,两个表的左外连接,若右表中没有与左表相对应的记录,则填充NULL值。,2.4 数据查询功能（续）,6、连接查询 右外连接：RIGHT JOIN 例31 查询教师的工资情况,SELECT GZ$.*,JS$.XM FROM GZ$ RIGHT JOIN JS$ ON GZ$.GH=JS$.GH ORDER BY XM,两个表的右外连接,若左表中没有与右表相对应的记录,则填充NULL值。,2.4 数据查询功能（续）,6、连接查询 全连接：FULL JOIN 例32 查询教师的工资情况,SELECT GZ$.*,JS$.* FROM GZ$ FULL JOIN JS$ ON GZ$.GH=JS$.GH,2.4 数据查询功能（续）,6、连接查询 自连接：一个表与其自身进行连接,由于是同一个表所以连接时必须用别名进行区分 例33 查询各门课程的先修课名称情况,SELECT FIRST.CNO,FIRST.CNAME, SECOND.CNAME AS 先修课 FROM COURSE FIRST,COURSE SECOND WHERE FIRST.CPNO=SECOND.CNO,SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c2 = tab2.c2 RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4 ON tab3.c1 = tab4.c1 ON tab2.c3 = tab4.c3,多个表多种连接方式的查询,纽带表,SELECT XS$.XM,KC$.KCM,cj$.CJ FROM XS$ LEFT JOIN cj$ ON XS$.XH=cj$.XH RIGHT JOIN KC$ ON cj$.KCDH=KC$.KCDH ORDER BY 3,查询全部课程课程名及全体学生的姓名与课程成绩的信息，并按成绩小到大排序,对于多表连接,若连接方式为内连接,则多采用将连接条件写在where子句中的形式,更加简洁。 如： SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SDEPT=计算机系,对于多表连接,若多个表中均有输出列,则必须在列名指定输出列来自于哪个表。 如： SELECT STUDENT.SNO,SNAME, CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SDEPT=计算机系,练习,SELECT XS$.XH,XM,AVG(CJ),COUNT(cj$.KCDH) FROM XS$,cj$ WHERE XS$.XH=cj$.XH GROUP BY XS$.XH,XM order by AVG(CJ) desc,2、输出学生成绩单,要求输出学号,姓名,平均分,选修门数,按平均分降序排序。,SELECT XS$.XH,XM,AVG(CJ),COUNT(cj$.KCDH) FROM XS$,cj$ WHERE XS$.XH=cj$.XH GROUP BY XS$.XH,XM order by XS$.XH,1、输出学生成绩单,要求输出学号,姓名, 平均成绩,所学课程数，按学号升序排序。,练习,SELECT XH,XM,JG FROM XS$,ZY$ WHERE XS$.ZYDH=ZY$.ZYDH AND ZYMC=网络工程,SELECT XIMING,ZYMC,COUNT(*) FROM XS$,XIM$,ZY$ WHERE XS$.ZYDH=ZY$.ZYDH AND XS$.XDH=XIM$.XDH GROUP BY XIMING,ZYMC ORDER BY 1,3、输出网络工程专业的学生的学号,姓名,籍贯。,4、统计各专业的学生人数,要求输出系名,专业名,总人数,按系名排序。,练习,SELECT JS$.GH,XM,XB,ZC,KCM,SKDD FROM JS$,RK$,KC$,ZC$ WHERE JS$.GH=RK$.GH AND JS$.ZCDH=ZC$.ZCDH AND RK$.KCDH=KC$.KCDH ORDER BY 1,SELECT XIMING,ZC,COUNT(*) FROM JS$,XIM$,ZC$ WHERE JS$.XDH=XIM$.XDH AND JS$.ZCDH=ZC$.ZCDH GROUP BY XIMING,ZC ORDER BY 1,2,5、查询教师的任课情况,要求输出教师工号,姓名,性别,职称,课程名,上课地点,按工号排序。,6、统计各系教师职称情况,要求输出系名,职称名,人数,按系名、职称排序。,2.4 数据查询功能（续）,7、嵌套子查询 例34 查询与刘晨在同一个系的学生。,若子查询结果只有一个值,可以用=,Select Sno,Sname,Sdept from Student where Sdept in( Select Sdept from Student Where Sname=刘晨),2.4 数据查询功能（续）,7、嵌套子查询 例35 查询有成绩大于90分的学生的学号和姓名。,Select Sno,Sname from Student where Sno in( Select Sno from SC Where Grade90),2.4 数据查询功能（续）,涉及到多个表的查询,除了可以使用多表连接查询以外,还可以利用嵌套子查询来完成。 由于连接查询比较耗时,当表中记录数很大时,嵌套查询的效率可能会比连接查询快。,2.4 数据查询功能（续）,7、嵌套子查询 例36 查询选修了c02课程且成绩高于此课程的平均成绩的学生的学号和成绩。,SELECT SNO,GRADE FROM SC WHERE CNO=C02 AND GRADE( SELECT AVG(GRADE) FROM SC WHERE CNO=C02),相关子查询,例: 查询每个学生成绩高于他选修课的平均成绩的学生的学号和课程号及成绩。 select sno,cno,grade from sc x where grade=(select avg(grade) from sc y where x.sno=y.sno),2.4 数据查询功能（续）,7、嵌套子查询 例37 查询其他系中比信息系某一学生年龄小的学生姓名和年龄,SELECT Sname,Sage FROM Student WHERE Sdept信息系 AND SageANY(SELECT Sage FROM Student WHERE Sdept=信息系),带有ANY或ALL谓词的子查询,需要配合使用比较运算符 ANY 大于子查询结果中的某个值 (min) ALL 大于子查询结果中的所有值 (max) = ANY 大于等于子查询结果中的某个值 (=min) = ALL 大于等于子查询结果中的所有值(=max) ）ANY 不等于子查询结果中的某个值 (-) !=（或）ALL 不等于子查询结果中的任何一个值 (NOT IN),2.4 数据查询功能（续）,7、嵌套子查询 带EXISTS谓词的子查询 例38 查询所有选修了C02课程的学生的姓名,SELECT SNAME FROM STUDENT WHERE EXISTS (SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=C02),用in子查询 SELECT SNAME FROM STUDENT WHERE SNO IN( SELECT SNO FROM SC WHERE CNO=C02) 或：,用连接查询 SELECT SNAME FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO AND CNO=C02,查询所有选修了数据库课程的学生的姓名,SELECT SNAME FROM STUDENT WHERE SNO IN( SELECT SNO FROM SC WHERE CNO=(select cno from course where cname=数据库)）,2.4 数据查询功能（续）,7、嵌套子查询 例39 查询所有未选修C02课程的学生的姓名,SELECT SNAME FROM STUDENT WHERE NOT EXISTS (SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=C02),SELECT SNAME FROM STUDENT WHERE SNO NOT IN( SELECT SNO FROM SC WHERE CNO=C02),2.4 数据查询功能（续）,7、嵌套子查询 例40 查询同时选修了C01和C02课程的学生的学号 例40B 查询同时选修了数据库和数据结构课程的学生的学号,SELECT SNO FROM SC WHERE CNO=C01 AND SNO IN( SELECT SNO FROM SC WHERE CNO=C02),SELECT SNO FROM STUDENT WHERE NOT EXISTS( SELECT * FROM COURSE WHERE CNO IN(C01,C02) AND NOT EXISTS( SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO) ),使用EXISTS谓词,2.4 数据查询功能（续）,用存在谓词EXISTS可以用来表达蕴涵（包含）的关系。 例如,查询选修了某些课程的学生的学号,符合查询要求的学生应选修了规定的全部课程,既其选课记录中的课程代号集合蕴涵所要求的全部课程代号。,带有EXISTS谓词的子查询(续),用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词 （For all） 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P),2.4 数据查询功能（续）,7、嵌套子查询 例41 查询选修了全部课程的学生的姓名,SELECT SNAME FROM STUDENT WHERE NOT EXISTS( SELECT * FROM COURSE WHERE NOT EXISTS( SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO),带有EXISTS谓词的子查询(续),用EXISTS/NOT EXISTS实现逻辑蕴涵(难点) SQL语言中没有蕴涵(Implication)逻辑运算 可以利用谓词演算将逻辑蕴涵谓词等价转换为： p q pq,带有EXISTS谓词的子查询(续),例44 查询至少选修了学生200215122选修的全部课程的学生号码。 解题思路： 用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，只要200215122学生选修了课程y，则x也选修了y。 形式化表示： 用P表示谓词 “学生200215122选修了课程y” 用q表示谓词 “学生x选修了课程y” 则上述查询为: (y) p q,带有EXISTS谓词的子查询(续),等价变换： (y)p q (y (p q ) (y ( p q) y(pq) 变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。,2.4 数据查询功能（续）,7、嵌套子查询 例42 查询选修了学号为“200215122”学生选修的全部课程的学号,SELECT DISTINCT SNO FROM SC SC1 WHERE NOT EXISTS( SELECT * FROM SC SC2 WHERE SNO=200215122 AND NOT EXISTS( SELECT * FROM SC SC3 WHERE SC3.SNO=SC1.SNO AND SC3.CNO=SC2.CNO),select sno,sname from student s where not exists (select * from course c where exists(select * from sc where sno=200215122 and o=o) and not exists(select * from sc where sc.sno=s.sno and o=o),2.4 数据查询功能（续）,8、集合查询：UNION并（INTERSECT交、EXCEPT差） 例43 查询计算机系的学生和教师 UNION相当于集合的并集,合并中会自动筛选掉重复记录。 若要显示重复记录,用UNION ALL,SELECT XH,XM ,STUDENT FROM XS$ WHERE XDH=09 UNION SELECT GH,XM ,TEACHERFROM JS$ WHERE XDH=09,2.4 数据查询功能（续）,9、取部分查询结果 例44 查询平均成绩前三名同学的学号,姓名,平均成绩,SELECT TOP 3 STUDENT.SNO,SNAME,AVG(GRADE) FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNO,SNAME ORDER BY 3 DESC,2.5 数据更新,1、插入数据 INSERT INTO (属性列名列表) VALUES(值列表) 注意： 值列表必须与列名列表一一对应； 若表名后没有列名表,则插入数据的顺序应该与表中列的定义一致,2.5 数据更新（续）,例1：将新生记录（200821105,陈冬,男,18,信息系 ）插入到Student表中。 例2：在SC表中插入一新记录学号为200821105,课程号为c01。,INSERT INTO STUDENT VALUES(200821105,陈冬,男 ,18,信息系),INSERT INTO SC(SNO,CNO) VALUES(200821105,C01),2.5 数据更新（续）,例3：插入计算机系学生C09课程的选课记录。 例4：将C01课程的成绩单(包括学号,姓名,成绩)输出成一个新表,表名为C01_CJ。,INSERT INTO SC(SNO,CNO) SELECT SNO,C09 FROM STUDENT where Sdept= 计算机系,SELECT STUDENT.SNO,SNAME,GRADE INTO CO1_CJ FROM STUDENT ,SC WHERE STUDENT.SNO=SC.SNO AND CNO=C01,2.5 数据更新（续）,2、数据更新 UPDATE SET WHERE 例3：所有学生年龄加1,UPDATE STUDENT SET SAGE=SAGE+1,2.5 数据更新（续）,2、数据更新 例4：将学号为200215121的学生的年龄改为21岁。,UPDATE STUDENT SET SAGE=21 WHERE SNO= 200215121,2.5 数据更新（续）,2、数据更新 例5：将计算机系全体学生的成绩加5分,UPDATE SC SET GRADE=GRADE+5 WHERE SNO IN( SELECT SNO FROM STUDENT WHERE SDEPT=计算机系),2.5 数据更新（续）,3、数据删除 DELETE FROM WHERE 例1：删除学号为200821105的学生的记录,DELETE FROM STUDENT WHERE SNO=200821105,2.5 数据更新（续）,3、数据删除 例2：删除所有的学生的记录 例3：删除计算机系所有学生的选课记录,DELETE FROM STUDENT,DELETE FROM SC WHERE 计算机系=( SELECT SDEPT FROM STUDENT WHERE SNO=SC.SNO),2.5 数据更新（续）,3、数据删除 例3：删除计算机系所有学生的选课记录 或：,DELETE FROM SC WHERE SNO IN( SELECT SNO FROM STUDENT WHERE SDEPT=计算机系),2.6 数据转换,1、数据导出 例1：导出数据到ACCESS数据库,2.6 数据转换,1、数据导出 例2：导出数据到文本文件 可以在导出时选择数据的分隔符。 例3：导出数据到EXCEL,2.6 数据转换,2、数据导入 例1：从ACCESS中导入 例2：从文本文件中导入 例3：从EXCEL文件中导入,2.7 视图,视图是一个或几个表（或视图）导出的表。 视图一经定义,就可以和基本表一样被查询、删除。 1、建立视图 CREATE VIEW (,.) AS WITH CHECK OPTION,列名可以省略,但若子查询的目标列为聚集函数或表达式、或有同名的列,则不允许省略。,不允许含有order by ,distinct,对视图进行更新时(update,insert,delete),需要保证更新的数据满足视图定义的条件,2.7 视图（续）,例1：建立系名为计算机系的学生的视图,CREATE VIEW STUDENT_09A AS SELECT sno,sname,ssex,sdept FROM student WHERE sdept=计算机系,2.7 视图（续）,例1：建立系代号为09的学生的视图,CREATE VIEW STUDENT_09A AS SELECT XH,XM,XB,XDH,JG FROM XS$ WHERE XDH=09,2.7 视图（续）,例2：建立系名为计算机系的学生的视图,要求对视图的更新进行检查。,CREATE VIEW STUDENT_09B AS SELECT sno,sname,ssex,sdept FROM student WHERE sdept=计算机系 WITH CHECK OPTION,2.7 视图（续）,例2：建立系代号为09的学生的视图,要求对视图的更新进行检查。,CREATE VIEW STUDENT_09B AS SELECT XH,XM,XB,XDH,JG FROM XS$ WHERE XDH=09 WITH CHECK OPTION,2.7 视图（续）,例3：建立信息系选修了c01课程的学生的视图,CREATE VIEW V_IS_S1(SNO,SNAME,GRADE) AS SELECT STUDENT.SNO,SNAME,GRADE FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO AND SDEPT=信息系 AND CNO=C01,2.7 视图（续）,例4：建立信息系选修了课程c01且成绩在90分以上的学生的视图,CREATE VIEW V_IS_S2 AS SELECT Sno,Sname,Grade FROM V_IS_S1 WHERE Grade=90,2.7 视图（续）,例5：建立反映学生出生年份的视图,CREATE VIEW BT_S(Sno,Sname,Birth) AS SELECT Sno,Sname,2009-Sage FROM Student,2.7 视图（续）,例6：定义一个存放学