理学结构化查询语言SQL续.pptx

4/19/2024数据库原理与应用数据库原理与应用第三章第三章 结构化查询语言结构化查询语言 SQL3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定义3.4 数据查询数据查询3.5 数据更新（数据操纵语言）数据更新（数据操纵语言）3.6 视图视图3.7 小结小结4/19/2024数据库原理与应用数据库原理与应用3.5 数数 据据 更更 新新 3.5.1 插入数据插入数据3.5.2 修改数据修改数据3.5.3 删除数据删除数据 4/19/2024数据库原理与应用数据库原理与应用3.5.1 插入数据插入数据v两种插入数据方式1.插入元组2.插入子查询结果可以一次插入多个元组 4/19/2024数据库原理与应用数据库原理与应用一、插入元组一、插入元组v语句格式INSERTINTO (，)VALUES(，)v功能n将新元组插入指定表中4/19/2024数据库原理与应用数据库原理与应用插入元组（续）插入元组（续）v INTO子句n属性列的顺序可与表定义中的顺序不一致n没有指定属性列n指定部分属性列v VALUES子句n 提供的值必须与INTO子句匹配值的个数值的类型4/19/2024数据库原理与应用数据库原理与应用插入元组（续）插入元组（续）例1 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERT INTO Student(Sno，Sname，Ssex，Sdept，Sage)VALUES(200215128，陈冬，男，IS，18)；4/19/2024数据库原理与应用数据库原理与应用插入元组（续）插入元组（续）例2 将学生张成民的信息插入到Student表中。INSERT INTO Student VALUES(200215126，张成民，男，CS,18);4/19/2024数据库原理与应用数据库原理与应用插入元组（续）插入元组（续）例3 插入一条选课记录(200215128，1)。INSERT INTO SC(Sno，Cno)VALUES(200215128，1)；RDBMS将在新插入记录的Grade列上自动地赋空值。或者：INSERT INTO SC VALUES(200215128，1，NULL)；4/19/2024数据库原理与应用数据库原理与应用二、插入子查询结果二、插入子查询结果v语句格式 INSERT INTO (，)子查询；v功能 将子查询结果插入指定表中4/19/2024数据库原理与应用数据库原理与应用插入子查询结果（续）插入子查询结果（续）v INTO子句(与插入元组类似)v 子查询nSELECT子句目标列必须与INTO子句匹配值的个数值的类型4/19/2024数据库原理与应用数据库原理与应用插入子查询结果（续）插入子查询结果（续）例4 对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15)/*系名*/Avg_age SMALLINT)；/*学生平均年龄*/4/19/2024数据库原理与应用数据库原理与应用插入子查询结果（续）插入子查询结果（续）第二步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age)SELECT Sdept，AVG(Sage)FROM Student GROUP BY Sdept；4/19/2024数据库原理与应用数据库原理与应用插入子查询结果（续）插入子查询结果（续）RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性NOT NULL约束UNIQUE约束值域约束4/19/2024数据库原理与应用数据库原理与应用3.5 数数 据据 更更 新新 3.5.1 插入数据插入数据3.5.2 修改数据修改数据3.5.3 删除数据删除数据 4/19/2024数据库原理与应用数据库原理与应用3.4.2 修改数据修改数据v语句格式 UPDATE SET =，=WHERE；v功能n修改指定表中满足WHERE子句条件的元组4/19/2024数据库原理与应用数据库原理与应用修改数据（续）修改数据（续）nSET子句指定修改方式要修改的列修改后取值nWHERE子句指定要修改的元组缺省表示要修改表中的所有元组4/19/2024数据库原理与应用数据库原理与应用修改数据（续）修改数据（续）v三种修改方式1.修改某一个元组的值2.修改多个元组的值3.带子查询的修改语句4/19/2024数据库原理与应用数据库原理与应用1.修改某一个元组的值修改某一个元组的值例5 将学生200215121的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno=200215121；4/19/2024数据库原理与应用数据库原理与应用2.修改多个元组的值修改多个元组的值例6 将所有学生的年龄增加1岁 UPDATE Student SET Sage=Sage+1；4/19/2024数据库原理与应用数据库原理与应用3.带子查询的修改语句带子查询的修改语句例7 将计算机科学系全体学生的成绩置零。UPDATE SC SET Grade=0 WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)；4/19/2024数据库原理与应用数据库原理与应用修改数据（续）修改数据（续）RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则n实体完整性n参照完整性n用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束4/19/2024数据库原理与应用数据库原理与应用3.5 数数 据据 更更 新新 3.5.1 插入数据插入数据3.5.2 修改数据修改数据3.5.3 删除数据删除数据 4/19/2024数据库原理与应用数据库原理与应用3.5.3 删除数据删除数据v语句格式 DELETE FROM WHERE；v功能n删除指定表中满足WHERE子句条件的元组vWHERE子句n指定要删除的元组n缺省表示要删除表中的全部元组，表的定义仍在字典中4/19/2024数据库原理与应用数据库原理与应用删除数据（续）删除数据（续）v三种删除方式1.删除某一个元组的值2.删除多个元组的值3.带子查询的删除语句4/19/2024数据库原理与应用数据库原理与应用1.删除某一个元组的值删除某一个元组的值例8 删除学号为200215128的学生记录。DELETE FROM Student WHERE Sno=200215128；4/19/2024数据库原理与应用数据库原理与应用2.删除多个元组的值删除多个元组的值例9 删除所有的学生选课记录。DELETE FROM SC；4/19/2024数据库原理与应用数据库原理与应用3.带子查询的删除语句带子查询的删除语句例10 删除计算机科学系所有学生的选课记录。DELETE FROM SC WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)；4/19/2024数据库原理与应用数据库原理与应用第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定义3.4 数据查询数据查询3.5 数据更新数据更新3.6 视图视图3.7 小结小结4/19/2024数据库原理与应用数据库原理与应用3.6 视视 图图视图的特点v虚表，是从一个或几个基本表（或视图）导出的表v只存放视图的定义，不存放视图对应的数据v基表中的数据发生变化，从视图中查询出的数据也随之改变4/19/2024数据库原理与应用数据库原理与应用3.6 视视 图图基于视图的操作v 查询v 删除v 受限更新v 定义基于该视图的新视图4/19/2024数据库原理与应用数据库原理与应用3.6 视视 图图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用4/19/2024数据库原理与应用数据库原理与应用3.6.1 定义视图定义视图v建立视图v删除视图4/19/2024数据库原理与应用数据库原理与应用一、建立视图一、建立视图v语句格式 CREATE VIEW (，)AS WITH CHECK OPTION；v组成视图的属性列名：全部省略或全部指定v子查询不允许含有ORDER BY子句和DISTINCT短语4/19/2024数据库原理与应用数据库原理与应用 建立视图（续）建立视图（续）vRDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。v在对视图查询时，按视图的定义从基本表中将数据查出。4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）例1 建立信息系学生的视图。CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS；4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）例2建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS WITH CHECK OPTION；4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）对IS_Student视图的更新操作：v修改操作：自动加上Sdept=IS的条件v删除操作：自动加上Sdept=IS的条件v插入操作：自动检查Sdept属性值是否为IS 如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为IS4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）v基于多个基表的视图例3 建立信息系选修了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；4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）v基于视图的视图例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）v带表达式的视图例5 定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno，Sname，Sbirth)AS SELECT Sno，Sname，2011-Sage FROM Student；4/19/2024数据库原理与应用数据库原理与应用建立视图（续）建立视图（续）v分组视图例6 将学生的学号及他的平均成绩定义为一个视图 CREAT VIEW S_G(Sno，Gavg)AS SELECT Sno，AVG(Grade)FROM SC GROUP BY Sno；4/19/2024数据库原理与应用数据库原理与应用二、删除视图二、删除视图v语句的格式：DROP VIEW ；该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE删除语句，把该视图和由它导出的所有视图一起删除 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 4/19/2024数据库原理与应用数据库原理与应用删除视图删除视图(续）续）例8 删除视图BT_S：DROP VIEW BT_S；删除视图IS_S1：DROP VIEW IS_S1；拒绝执行级联删除：DROP VIEW IS_S1 CASCADE;4/19/2024数据库原理与应用数据库原理与应用3.6 视视 图图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用4/19/2024数据库原理与应用数据库原理与应用3.6.2 查询视图查询视图v用户角度：查询视图与查询基本表相同vRDBMS实现视图查询的方法视图消解法（View Resolution）进行有效性检查转换成等价的对基本表的查询执行修正后的查询4/19/2024数据库原理与应用数据库原理与应用查询视图（续）查询视图（续）例9 在信息系学生的视图中找出年龄小于20岁的学生。SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Student视图的定义(参见视图定义例1)CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS；4/19/2024数据库原理与应用数据库原理与应用查询视图（续）查询视图（续）视图消解转换后的查询语句为：SELECT Sno，Sage FROM Student WHERE Sdept=IS AND Sage20；4/19/2024数据库原理与应用数据库原理与应用查询视图（续）查询视图（续）例10 查询选修了1号课程的信息系学生SELECT IS_Student.Sno，SnameFROM IS_Student，SCWHERE IS_Student.Sno=SC.Sno AND SC.Cno=1；4/19/2024数据库原理与应用数据库原理与应用3.6 视视 图图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用4/19/2024数据库原理与应用数据库原理与应用更新视图（续）更新视图（续）例12 将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。UPDATE IS_StudentSET Sname=刘辰WHERE Sno=200215122；转换后的语句：UPDATE StudentSET Sname=刘辰WHERE Sno=200215122 AND Sdept=IS；4/19/2024数据库原理与应用数据库原理与应用更新视图（续）更新视图（续）例13 向信息系学生视图IS_S1中插入一个新的学生记录：200215129，赵新，20岁INSERTINTO IS_StudentVALUES(95029，赵新，20)；转换为对基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(200215129，赵新，20，IS)；4/19/2024数据库原理与应用数据库原理与应用更新视图（续）更新视图（续）例14删除信息系学生视图IS_S1中学号为200215129的记录 DELETEFROM IS_StudentWHERE Sno=200215129；转换为对基本表的更新：DELETEFROM StudentWHERE Sno=200215129 AND Sdept=IS；4/19/2024数据库原理与应用数据库原理与应用3.6 视视 图图3.6.1 定义视图定义视图3.6.2 查询视图查询视图3.6.3 更新视图更新视图3.6.4 视图的作用视图的作用4/19/2024数据库原理与应用数据库原理与应用3.6.4 视图的作用视图的作用v1.视图能够简化用户的操作v2.视图能够对机密数据提供安全保护v3.视图使用户能以多种角度看待同一数据 4/19/2024数据库原理与应用数据库原理与应用3.7 数据字典数据字典v 数据字典的用途进行详细的数据收集和数据分析所获得的主要结果，存放数据库系统中有关数据的数据v 数据字典的内容数据项数据结构数据流数据存储处理过程4/19/2024数据库原理与应用数据库原理与应用 数据项数据项v 数据项是不可再分的数据单位v 对数据项的描述 数据项描述 数据项名，数据项含义说明，别名，数据类型，长度，取值范围，取值含义等4/19/2024数据库原理与应用数据库原理与应用 数据结构数据结构v数据结构反映了数据之间的组合关系。v 一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成。v 对数据结构的描述 数据结构描述数据结构名，含义说明，组成:数据项或数据结构4/19/2024数据库原理与应用数据库原理与应用 数据流数据流v 数据流是数据结构在系统内传输的路径。v 对数据流的描述 数据流描述 数据流名，说明，数据流来源，数据流去向，组成:数据结构，平均流量，高峰期流量4/19/2024数据库原理与应用数据库原理与应用 数据存储数据存储v数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一。v对数据存储的描述 数据存储描述数据存储名，说明，编号，输入的数据流，输出的数据流，组成:数据结构，数据量，存取频度，存取方式4/19/2024数据库原理与应用数据库原理与应用数据字典举例数据字典举例例：学生学籍管理子系统的数据字典。数据项，以“学号”为例：数据项：学号 含义说明：唯一标识每个学生别名：学生编号 类型：字符型 长度：8 取值范围：00000000至99999999取值含义：前两位标别该学生所在年级，后六位按顺序编号与其他数据项的逻辑关系：4/19/2024数据库原理与应用数据库原理与应用处理过程（续）处理过程（续）数据结构，以“学生”为例“学生”是该系统中的一个核心数据结构：数据结构：学生 含义说明：是学籍管理子系统的主体数据结构，定义了一个学生的有关信息 组成：学号，姓名，性别，年龄，所在系，年级4/19/2024数据库原理与应用数据库原理与应用处理过程（续）处理过程（续）数据流，“体检结果”可如下描述：数据流：体检结果 说明：学生参加体格检查的最终结果 数据流来源：体检 数据流去向：批准 组成：平均流量：高峰期流量：4/19/2024数据库原理与应用数据库原理与应用处理过程（续）处理过程（续）数据存储，“学生登记表”可如下描述：数据存储：学生登记表 说明：记录学生的基本情况流入数据流：流出数据流：组成：数据量：每年3000张 存取方式：随机存取 4/19/2024数据库原理与应用数据库原理与应用数据字典数据字典v数据字典是关于数据库中数据的描述，而不是数据本身v数据字典在需求分析阶段建立，在数据库设计过程中不断修改、充实、完善 4/19/2024数据库原理与应用数据库原理与应用作用：v 有利于DBA掌握系统结构和运行情况 已建立的数据库 数据库的结构 数据库的用户 v方便用户使用 属性、元组的逻辑结构、关系等数据字典数据字典4/19/2024数据库原理与应用数据库原理与应用1、SQL语言的发展过程2、SQL语言的特点 3、数据定义（重点）4、数据查询（重点）单表查询；多表查询：等值查询，不等值查询，复合条件连接 嵌套查询：IN谓词、比较运算符、ANY或ALL谓词，EXISTS谓词5、数据更新（重点）6、数据控制7、视图的定义和作用，视图的基本操作8、数据字典的含义和作用小结小结