关系数据库语言SQL(ch3-2).ppt

数据库管理系统,连接查询,SQL中连接查询的主要类型： 广义笛卡尔积 等值连接(含自然连接) 非等值连接查询 自身连接查询 外连接查询 复合条件连接查询,（1）广义笛卡尔积,不带连接谓词的连接 很少使用 例： SELECT Student.* , SC.* FROM Student, SC,（2）自然连接,等值连接的一种特殊情况，把目标列中重复的 属性列去掉. 例 SELECT Student.Sno，Sname，Ssex， Sage， Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；,（3）自身连接,SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；,例 查询每一门课的间接先修课,即先修课的先修课,Course表结构为：Cno,Cname,Cpno,Ccredit,自身连接（续）,FIRST表（Course表）,自身连接（续）,SECOND表（Course表）,自身连接（续）,查询结果 1 7 3 5 5 6,（4）外连接,inner join 内连接（等值的F连接） 左边表和右边表满足连接条件的记录取出为结果集 left outer join 左外连接 左边表和右边表满足连接条件的记录取出为结果集，加上左边表未匹配的记录 right outer join 右外连接 左边表和右边表满足连接条件的记录取出为结果集，加上右边表未匹配的记录 full outer join 外连接,内连接,USE pubs SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city ORDER BY a.au_name DESC,左外连接,USE pubs SELECT a.au_name, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_name ASC,（5）复合条件连接,例 查询选修2号课程且成绩在90分以上的所有 学生的学号、姓名. SELECT Student.Sno, student.Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND SC.Cno= 2 AND SC.Grade 90；,（6）多表连接,例 查每个学生的学号、姓名、选修的课程名及 成绩. SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno；,（7）嵌套查询,嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,注：嵌套查询是一种比较复杂的查询方法，需要 比较细心的处理各种可能的情况.,嵌套查询（例）,SELECT Sname 外层查询/父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询/子查询 FROM SC WHERE Cno= 2 ）,构造嵌套查询,SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨 )；,例 查询与“刘晨”在同一个系学习的学生.,（8）带有ANY谓词的子查询,SELECT Sname，Sage FROM Student WHERE Sage IS ; /* 注意这是父查询块中的条件 */,例 查询其他系中比信息系任意一个（其中某一个) 学生年龄小的学生姓名和年龄.,（9）带有ALL谓词的子查询,例 查询其他系中比信息系所有学生年龄都小的学生 姓名及年龄. SELECT Sname，Sage FROM Student WHERE Sage IS ;,（10）带有IN谓词的子查询,SELECT Sno，Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname= 信息系统);,例查询选修了课程名为“信息系统”的学生学号和姓名, 最后在Student关系中,取出Sno和Sname, 然后在SC关系中找出选,修了3号课程的学生学号, 首先在Course关系中找出“信,息系统”的课程号，假设为3号,(11) 带有EXISTS谓词的子查询,EXISTS谓词（存在量词 ） 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则返回真值 若内层查询结果为空，则返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 NOT EXISTS谓词,带有EXISTS谓词的子查询(续),例 查询所有选修了1号课程的学生姓名. 用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相关子查询*/ WHERE Sno= Student.Sno AND Cno= 1 )；,带有EXISTS谓词的子查询(续),例 查询没有选修1号课程的学生姓名. SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；,（12）导出表,执行查询后产生的表 SELECT sno, avg_grade FROM ( SELECT sno,avg(grade) from SC group by (sno) as result(sno,avg_grade) WHERE avg_grade70;,集合查询,标准SQL直接支持的集合操作种类 并操作(UNION) 一般商用数据库支持的集合操作种类 并操作(UNION) 交操作(INTERSECT) 差操作(MINUS、EXCEPT),（1）并操作,例 查询计算机科学系的学生及年龄不大于19 岁的学生. SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19；,又例： Select state From authors UNION Select state From publishers Order by state,注：UNION子句会自动删除查询结果中重复的行.如果用户刻意要获得所有的行，则可以使用ALL这一关键字.,Select state From authors UNION ALL Select state From publishers Order by state,（2）交操作,例 查询计算机科学系的学生与年龄不大于19 岁的学生的交集. 本例实际上就是查询计算机科学系中年龄不大于 19岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage=19；,（3）差操作,例 查询学生姓名与教师姓名的差集. 本例实际上是查询学校中未与教师同名的学生姓名 SELECT DISTINCT Sname FROM Student WHERE Sname NOT IN (SELECT Tname FROM Teacher);,练习：SELECT句型使用实例,教学数据中有三个基本表（关系）： S（S#，SNAME，AGE，SEX） SC（S#，C#，GRADE） C（C#，CNAME，TEACHER） 下面用SELECT查询语句表达每个查询. 检索学习课程号为C2的学生学号与成绩. 检索学习课程号为C2的学生学号与姓名. 检索选修课程名为MATHS的学生学号与姓名. 检索选修课程号为C2或C4的学生学号. 检索至少选修课程号为C2和C4的学生学号., 检索学习课程号为C2的学生学号与成绩.,Select SC.Sno, Sname From Student, SC Where cno=c2 and SC.sno=student.sno, 检索学习课程号为C2的学生学号与姓名.,Select Sno, Grade From Student Where cno=c2, 检索选修课程名为MATHS的学生学号与姓名,Select SC.Sno, Sname From Student, SC，course Where cname=MATHS and SC.sno=student.sno and o=SC.cno, 检索选修课程号为C2或C4的学生学号.,Select Sno From SC Where cno=c2 or cno=c4, 检索至少选修课程号为C2和C4的学生学号.,Select X.Sno From SC AS X, SC AS Y Where X.Sno=Y.Sno and X.cno=c2 and Y.cno=c4,3.4 数据更新,3.4.1 数据插入 3.4.2 数据删除 3.4.3 数据修改,数据插入,（1）单元组的插入 INSERT INTO 基本表名 （列名表） VALUES（元组值） （2）多元组的插入 INSERT INTO 基本表名 （列名表） VALUES （元组值），（元组值），（元组值） （3）查询结果的插入 INSERT INTO 基本表名 (列名表)SELECT查询语句,（1）插入单个元组,例 将一个新学生记录 （学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中. INSERT INTO Student VALUES (95020，陈冬，男，IS，18)；,注： 在INSERT语句中，VALUES列表中的表达式的数量，必须匹配列表中的列数，且表达式的数据类型必须和表格中对应各列的数据类型兼容.,插入单个元组,例 插入一条选课记录( 95020，1 ). INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )；,注： 新插入的记录在Grade列上取空值； 如果在表格中存在定义为NOT NULL的数据列，那么该列的值必须在VALUES的列表中出现.否则插入操作失败.,（2）插入子查询结果,例 对每一个系，求学生的平均年龄，并把结 果存入数据库.(两个步骤) 第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*学生平均年龄*/,插入子查询结果（续）,第二步：插入数据 INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,数据删除,SQL的删除操作是指从基本表中删除元组，其句法如下： DELETE FROM 基本表名 WHERE 条件表达式 DELETE语句只能从一个基本表中删除元组. WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件. 一个没有带WHERE子句的DELETE语句将会删除这个表中的每一行数据.,（1）删除某一个元组的值,例 删除学号为95019的学生记录. DELETE FROM Student WHERE Sno=95019；,（2）删除多个元组的值,例 删除2号课程的所有选课记录. DELETE FROM SC； WHERE Cno=2； 例 删除所有的学生选课记录. DELETE FROM SC；,清空表格,删除表格中所有的数据，只留下表格的定义 可以用TRUNCATE TABLE语句： TRUNCATE TABLE ,注：由于TRUNCATE TABLE操作不进行日志的记录，所以删除数据后无法恢复. TRUNCATE TABLE将释放表的数据和索引所占据的所在空间以及为全部索引分配的页.但只有数据库的拥有者才可执行该命令，且权利无法转让.,（3）带子查询的删除语句,例 删除计算机科学系所有学生的选课记录. DELETE FROM SC WHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)；,数据修改,当需要修改基本表中元组的某些列值时，可以用 UPDATE语句实现，其句法如下： UPDATE 基本表名 SET 列名=值表达式，列名=值表达式ROW =（元组） WHERE 条件表达式 语义：修改基本表中满足条件表达式的那些元组中的列值，需修改的列值在SET子句中指出.,（1）修改某一个元组的值,例 将学生95001的年龄改为22岁. UPDATE Student SET Sage=22 WHERE Sno= 95001 ；,（2）修改多个元组的值,例 将信息系所有学生的年龄增加1岁. UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,（3）带子查询的修改语句,例 将计算机科学系全体学生的成绩置零. UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；,3.5 视图的定义和操作,3.5.1 视图的创建和撤销 3.5.2 视图的更新,视图同表一样，都是由数据库用户定义的、存储在数据库中的数据对象. 视图是一个虚拟的表，它由一组查询语句所定义. 其内容是由这些定义视图的查询语句被执行后所返回的结果集决定的.,视图的创建和撤销,视图的创建 CREATE VIEW owner.（列表名） AS ,Owner：指定视图的拥有者. 默认为当前用户. 列表名：视图中列的名字. 默认是沿用查询语句 中表格的列名.,注意：,当遇到以下情况时，必须为视图提供列名： 该列是从表中的计算列或者从一个常量继承下来； 当视图从多个表产生，而这些表又有数据列重名的情况； 当视图中有一个数据列使用了同表格中相应列不同名字的时候，所有列名都必须给出。,（1）行列子集视图,例 建立信息系学生的视图. CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS； 从单个基本表导出 只是去掉了基本表的某些行和某些列,（2）基于多个表的视图,例 建立信息系选修了1号课程的学生视图. CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；,（3）基于视图的视图,例 建立信息系选修了1号课程且成绩在90分以 上的学生的视图. CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；,视图的创建和撤销,视图的撤销 在视图不需要时，可以用“DROP VIEW”语句把其从系统中撤销. 其句法如下： DROP VIEW 视图名 例3.28 撤销STUDENT_GRADE视图 DROP VIEW STUDENT_GRADE；,对视图的更新操作, 设有一个视图定义 CREATE VIEW SUDENT_GRADE AS SELECT S.S#, SNAME，CNAME，GRADE FROM S，SC，C WHERE S.S# = SC.S# AND SC.C# =C.C#； 如果用户要在视图中执行插入操作: INSERT INTO STUDENT_GRADE VALUES (S24, WANG, MATHS,80)； 若在基本表C中，课程名为MATHS的课程号有多个，则往基本表SC插入元组时，课程号C#就不定了. 因此，对这个视图的更新是不允许的.,对视图的更新操作, 设有一个视图定义，包含每个学生选修课程（成绩为非空） 的门数和平均成绩： CREATE VIEW S_GRADE（S#，CNUM，AVGGRADE） AS SELECT S#，COUNT（C#），AVG（GRADE） FROM SC WHERE GRADE IS NOT NULL 视图S_GRADE虽然是从单个基本表导出，但导出时使用了分组和聚集操作，因此也是不能更新的.,对视图的更新操作, 如果定义了一个有关男同学的视图： CREATE VIEW S_MALE AS SELECT S#, SNAME, AGE FROM S WHERE SEX =M； 这个视图是从单个关系只使用选择和投影导出的，并且包含主键S#，因此是可更新的。例如，执行插入操作： INSERT INTO S_MALE VALUES（S28,WU,18）； 系统自动会把它转变成下列语句： INSERT INTO S VALUES(S28, WU,18, M)；,视图的优点,视图是用户一级的数据观点，由于有了视 图，使数据库系统具有下列优点： 视图提供了逻辑数据独立性. 简化了用户观点。数据库的全部结构是复杂的,并有多种联系. 数据的安全保护功能.,3.6 嵌入式SQL,SQL语言有两种使用方式： 一种是在终端交互方式下使用，称为交互式SQL； 另一种是嵌入在高级语言的程序中使用，称为嵌入式SQL，而这些高级语言可以是C、ASP、PASCAL、VB或JAVA等，称为宿主语言.,SQL的运行环境,宿主语言十嵌入式SQL 预处理程序 宿主语言十函数调用 SQL函数定义库 宿主语言编译程序 目标程序,图 源程序处理过程,存储过程,存储过程的特点 存储过程是存储在服务器上的一组预编译的Transact-SQL语句。存储过程是一种封装重复任务操作的一种方法，支持用户提供的变量，具有强大的编程功能。 一般地，在Microsoft SQL Server系统中使用存储过程可以带来许多好处,存储过程的好处,与其他应用程序共享应用程序的逻辑，因此确保一致的数据访问和操纵。 提供一种安全机制。如果用户被授予执行存储过程的权限，那么即使该用户没有访问在该存储过程中所参考的表或视图的权限，该用户也可以完全执行该存储过程，而不会受到影响。 由于存储过程在第一次执行之后，其执行规划就驻存在过程高速缓冲存储器中。在以后的操作中，只需要从过程高速缓冲存储器中调用编译好的存储过程的二进制形式来执行，因此可以加速存储过程的执行，提高系统的性能。 减少网络交通。,存储过程的类型,在Microsoft SQL Server 2000关系型数据库管理系统中，