软件测试培训讲义-16数据库简介及SQL语句.ppt

1、软件测试培训讲义,深圳市软件行业协会培训中心,课程目的,了解软件工程的基本概念和过程 了解软件质量定义和软件质量保证过程 深入掌握软件测试原理、方法、过程 通过实战掌握测试策略、技术 通过实战了解测试周边相关技能,第十六章,数据库系统简介SQL语句,第十六章：数据库系统简介SQL语句,数据库系统简介,数据库的一些概念,数据库 数据库是具有一定结构的、互相之间有一定联系的数据的有序集合。 数据的集合 由DBMS统一管理，多用户共享 数据库管理系统DBMS DBMS是一组系统程序，负责数据库的定义、管理、使用及操作维护。 系统软件 对数据库进行统一管理和控制 数据库系统 数据库系统是实现有组织地、

2、动态地存储大量关联数据，方便用户访问的计算机软、硬件和数据资源组成的系统，它包括DB、DBA、DBMS等。 即带有数据库的整个计算机系统，包括硬件、软件、数据、人员,数据库的一些概念(续),数据模型 表示实体及其它们之间联系的记录和记录间联系的数据结构形式叫做数据模型。 数据模型的三要素 数据结构 数据及其它们之间的联系 数据操作 检索、更新等操作及其操作规则 完整性约束条件 规定数据库状态及状态变化所应满足的条件，以保证数据的正确、有效、相容,数据库的一些概念(续),关系模型 数据结构 结构单一：数据及其它们之间的联系均用表（关系）来描述 关系操作集 关系代数：用对关系的运算来表达查询，需要

3、指明所用操作 关系演算：用谓词来表达查询，只需描述所需信息的特性 元组关系演算：谓词变量是元组 域关系演算：谓词变元是域 关系完整性 实体完整性：主属性不能为空 参照完整性：外码对应属性的取值或者为空，或者是外码所参照关系中对应属性的一个值 用户定义的完整性：需要系统支持,数据管理的发展阶段,人工管理阶段（50年代中期以前） 文件系统阶段（50年代后期-60年代中期） 数据库系统阶段（60年代后期开始） 数据库系统 VS 文件系统 数据库系统的特点,人工管理阶段,背景 计算机主要用于科学计算 数据量小、结构简单，如高阶方程、曲线拟和等 外存为顺序存取设备 磁带、卡片、纸带，没有磁盘等直接存取设

4、备 没有操作系统，没有数据管理软件 用户用机器指令编码，通过纸带机输入程序和数据，程序运行完毕后，由用户取走纸带和运算结果，再让下一用户上机操作 程序员设计逻辑结构和物理结构,人工管理阶段,特点 用户完全负责数据管理工作 数据的组织、存储结构、存取方法、输入输出等 数据完全面向特定的应用程序 每个用户使用自己的数据，数据不保存，用完就撤走 数据与程序没有独立性 程序中存取数据的子程序随着存储结构的改变而改变 磁带的特点 优点：廉价地存放大容量数据 缺点：顺序访问；1%所需，100%访问,人工管理阶段,文件系统阶段,背景 计算机不但用于科学计算，还用于管理 外存有了磁盘、磁鼓等直接存取设备 直接

5、存取设备（DASD） 无须顺序存取 由地址直接访问所需记录 有了专门管理数据的软件，一般称为文件系统 文件存储空间的管理 目录管理 文件读写管理 文件保护 向用户提供操作接口,文件系统阶段,特点 系统提供一定的数据管理功能 存取方法（索引文件、链接文件、直接存取文件、倒排文件等） 支持对文件的基本操作（增、删、改、查等），用户程序不必考虑物理细节 数据的存取基本上以记录为单位 数据仍是面向应用的 一个数据文件对应一个或几个用户程序 数据与程序有一定的独立性 文件的逻辑结构与存储结构由系统进行转换，数据在存储上的改变不一定反映在程序上,文件系统阶段,数据1,数据2,数据n,存取方法,程序1,程序

6、2,程序n,文件系统阶段,数据与程序的独立性差 文件系统的出现并没有从根本上改变数据与程序紧密结合的状况，数据的逻辑结构改变则必须修改应用程序 文件系统只是解脱了程序员对物理设备存取的负担，它并不理解数据的语义，只负责存储 数据的语义信息只能由程序来解释，也就是说，数据收集以后怎么组织，以及数据取出来之后按什么含义应用，只有全权管理它的程序知道。 一个应用若想共享另一个应用生成的数据，必须同另一个应用沟通，了解数据的语义与组织方式,文件系统阶段,数据的共享性差，冗余度大 数据面向应用 即使不同应用程序所需要的数据有部分相同时，也必须建立各自的文件，而不能共享相同的数据 数据孤立 数据分散管理，

7、许多文件，许多数据格式 数据的不一致性 由于数据存在很多副本，给数据的修改与维护带来了困难，容易造成数据的不一致性,文件系统阶段,补贴,系别,姓名,学号,财务处,住址,系别,性别,姓名,学号,后勤处,学位,学分,系别,姓名,学号,教务处,学位,出身,年龄,系别,性别,姓名,学号,学工处,文件系统阶段,数据查询困难 记录之间无联系 应用自己编程实现 对每个查询都重新编码 数据完整性难于保证,数据库系统阶段,背景 计算机管理的数据量大，关系复杂，共享性要求强（多种应用、不同语言共享数据） 外存有了大容量磁盘，光盘 软件价格上升，硬件价格下降，编制和维护软件及应用程序成本相对增加，其中维护的成本更高

8、，力求降低,数据库系统阶段,特点 有了数据库管理系统 面向全组织，面向现实世界 独立性较强 由DBMS统一存取，维护数据语义及结构,数据库系统阶段,数据库观点 数据不是依赖于处理过程的附属品，而是现实世界中独立存在的对象,数据库系统 vs 文件系统,文件系统的弱点 记录之间无联系 难于维护数据的完整性 数据库系统的优点 有查询 整体数据结构化 效率方面的考虑 运行效率 开发效率,数据库系统 vs 文件系统,数据库系统的特点,面向全组织的复杂的数据结构 支持全企业的应用而不是某一个应用 数据反映了客观事物间的本质联系，而不是着眼于面向某个应用，是有结构的数据。这是数据库系统的主要特征之一，与文件

9、系统的根本差别。文件系统只是记录的内部有结构，一个文件的记录之间是个线性序列，记录之间无联系,数据库系统的特点,数据的冗余度小，易扩充 数据面向整个系统，而不是面向某一应用，数据集中管理，数据共享，因此冗余度小 节省存储空间，减少存取时间，且可避免数据之间的不相容性和不一致性 每个应用选用数据库的一个子集，只要重新选取不同子集或者加上一小部分数据，就可以满足新的应用要求，这就是易扩充性,学号,姓名,性别,系别,年龄,住址,出身,学位,学分,补贴,教务处,后勤处,学工部,财务处,数据库系统的特点,数据库系统的特点,具有较高的数据和程序的独立性 把数据库的定义和描述从应用程序中分离出去 数据描述是

10、分级的（全局逻辑、局部逻辑、存储） 数据的存取由系统管理，用户不必考虑存取路径等细节，从而简化了应用程序,数据库系统的特点,统一的数据控制功能，数据共享程度高 数据的安全性控制（Security） 保护数据以防止不合法的使用所造成的数据泄露和破坏 措施：用户标识与鉴定，存取控制 数据的完整性控制（Integrity） 数据的正确性、有效性、相容性 措施：完整性约束条件定义和检查 并发控制（Concurrency） 对多用户的并发操作加以控制、协调，防止其互相干扰而得到错误的结果并使数据库完整性遭到破坏 措施：封锁,第十六章：数据库系统简介SQL语句,SQL语句,本节内容,SQL概述 SQL数据

11、定义功能 SQL数据查询功能 SQL数据修改功能 嵌入式SQL,SQL概述,历史 SQL: Structured Query Language 1974年，由Boyce和Chamber提出 1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel,SQL概述,用户1,用户2,用户4,视图V1,视图V1,用户3,基本表B1,基本表B2,基本表B4,基本表B3,存储文件S1,存储文件S2,存储文件S4,存储文件S3,SQL数据库体系结构,SQL用户,VIEW,Base Table,Stored file,SQL概述,标准化 有关组织 ANSI(Amer

12、ican Natural Standard Institute) ISO(International Organization for Standardization) 有关标准 SQL-86 “数据库语言SQL” SQL-89 “具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持 SQL-92 “数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 SQL-1999 增加了对象关系特征,SQL概述,特点 一体化 集DDL，DML，DCL于一体 单一的结构-关系，带来了数据操作符的统一 面向集合的操作方式 一次一集

13、合 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径 两种使用方式，统一的语法结构 SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用） 语言简洁，易学易用,SQL概述,SQL数据定义功能,域定义 基本表的定义 索引的定义 数据库的建立与撤消 SQL数据定义特点,示例关系,系DEPT(D# , DNAME , DEAN) 学生S(S# , SNAME , SEX , AGE , D#) 课程COURSE(C# , CNAME , PC# , CREDIT) 选课SC(S# , C# , SCORE) 教师PROF(P# , PNAME, AGE, D# ,

14、SAL) 讲授PC(P# , C#),经过数据库的逻辑设计之后（关系模式）,域定义,域类型（SQL-92） char（n）：固定长度的字符串 varchar（n）：可变长字符串 int：整数 smallint：小整数类型 numeric（p，d）：定点数，小数点左边p位，右边q位 real：浮点数 double precision：双精度浮点数 date：日期（年、月、日） time：时间（小时、分、秒） interval：两个date或time类型数据之间的差,域定义,域定义 格式 create domain 域名 数据类型 示例 create domain person-name char

15、（20） 类似C语言中： typedef ADDRESS_LIST char name10; char telephone20; char location20 char email20; ADDRESS_LIST tom;,基本表的定义,基本表的定义（CREATE） 格式 create table 表名（ 列名 数据类型 default 缺省值 not null ，列名 数据类型 default 缺省值 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件）,基本表的定义

16、,示例 CREATE TABLE S ( S# CHAR（4）, SNAME CHAR（8） NOT NULL, SEX CHAR（1）, AGE SMALLINT, D# CHAR （10）, PRIMARY KEY (S#), FOREIGN KEY (D#) REFERENCES DEPT(D#), CHECK (SEX=0 OR SEX=1) ),基本表的定义,CREATE TABLE COURSE ( C# CHAR（4）, CNAME CHAR（10） NOT NULL, PC# CHAR（4）, CREDIT SMALLINT（8）, PRIMARY KEY (C#),基本表的定

17、义,CREATE TABLE DEPT ( D# CHAR（10）, DNAME CHAR（20） NOT NULL, DEAN CHAR（10）, PRIMARY KEY (D#),基本表的定义,CREATE TABLE SC (S# CHAR（4）, C# CHAR（4）, SCORE SAMLLINT, PRIMARY KEY (S#，C#), FOREIGN KEY (S#) REFERENCES S(S#), FOREIGN KEY (C#) REFERENCES C(S#), CHECK(SCORE IS NULL) OR SCORE BETWEEN 0 AND 100),基本表的

18、定义,示例 CREATE DOMAIN person_name CHAR (20) CREATE TABLE PROF ( P# CHAR（10）, person_name PNAME NOT NULL, SAL INT, AGE INT, D# CHAR（10）, PRIMARY KEY (P#), FOREIGN KEY (D#) REFERENCES DEPT(D#), CHECK(SAL 0),基本表的定义,CREATE TABLE PC (P# CHAR（4）, C# CHAR（4）, PRIMARY KEY (P#，C#), FOREIGN KEY (P#) REFERENCES

19、PROF(P#), FOREIGN KEY (C#) REFERENCES COURSE(C#),基本表的定义,修改基本表定义（ALTER） 格式： alter table 表名 add 子句增加新列 drop 子句删除列 modify 子句修改列定义 示例 alter table PROF add LOCATION char30,新增列一律为空值,基本表的定义,撤消基本表定义（drop） 格式 drop table 表名 示例 drop table DEPT DANGER 撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除,索引的定义,索引的定义 格式 creat

20、e unique/distinct cluster index 索引名 on 表名 (列名 asc/desc , 列名asc/desc) unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统会拒绝违背唯一性的插入、更新 cluster：聚簇索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚簇索引 asc/desc：索引表中索引值的排序次序，缺省为asc,属性（组）的值和记录位置的对应关系，可以加快查询速度。,索引的定义,示例： create cluster index s-index

21、on S（S#） 索引的删除 格式 drop index 索引名,索引的定义,索引的有关说明 可以动态地定义索引，即可以随时建立和删除索引 不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性 应该在使用频率高的、经常用于连接的列上建索引 一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率,数据库的建立与撤消,有的数据库系统支持多库 建立一个新数据库 create database 数据库名 撤消一个数据库 drop database 数据库名 指定当前数据库 database 数据库名 关闭当前数据库 close d

22、atabase 数据库名,SQL数据定义特点,SQL中，任何时候都可以执行一个数据定义语句，可以随时修改数据库结构。而在非关系型的数据库系统中，必须在数据库的装入和使用前全部完成数据库的定义。若要修改已投入运行的数据库，则需停下一切数据库活动，把数据库卸出，修改数据库定义并重新编译，再按修改过的数据库结构重新装入数据 数据库定义不断增长（不必一开始就定义完整） 数据库定义随时修改（不必一开始就完全合理） 可进行增加索引、撤消索引的实验，检验其对效率的影响,SQL数据查询功能,SQL数据查询基本结构 select子句 重复元组的处理 from子句 where子句 更名运算 字符串操作 元组显示顺

23、序 集合操作,SQL数据查询功能,分组和聚集函数 空值 嵌套子查询 派生关系 视图 关系的连接,SQL数据查询基本结构,基本结构 select A1 , A2 , , An from r1 , r2 , , rm where P A1 , A2 , , An(p(r1 r2 rm),SQL数据查询基本结构,示例 给出所有老师的姓名 select PNAME from PROF,select子句,目标列形式 可以为列名，* ，算术表达式，聚集函数 “*”：表示“所有的属性” 给出所有老师的所有信息 select * fromPROF,select子句,带， ， 的算术表达式 给出所有老师的姓名及

24、税后工资额 select PNAME，SAL 0.95 from PROF,重复元组的处理,语法约束 缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明 示例 找出所有选修课程的学生 select distinct S from SC,from子句,说明 from子句列出查询的对象表 当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系 示例 例：找出工资低于500的职工的姓名、工资、系别 selectPNAME , SAL , DNAME from PROF , DEPT where SAL 500 and PROF.

25、D= DEPT.D,from子句,例：列出教授“哲学”课程的老师的教工号及姓名 select PROF. PNO , PNAME from PROF , PC , COURSE where PROF.P = PC.P and PC.C = COURSE.C and COURSE.CNAME = “哲学”,where子句,语法成分 比较运算符 、 、=、 逻辑运算符 and，or，not between条件 判断表达式的值是否在某范围内,where子句,示例 列出工资在500800之间的老师姓名 select PNAME from PROF where SAL between 500 and 8

26、00,更名运算,格式 old_name as new_name 为关系和属性重新命名，可出现在select和from子句中 注：as可选,更名运算,示例 属性更名 例：给出所有老师的姓名、所纳税额及税后工资额 select PNAME，SAL0.05 as taxi， SAL*0.95 as incoming from PROF,更名运算,关系更名 找出工资比所在系主任工资高的老师姓名及工资 select P1.PNAME，P1.SAL from PROF as P1，PROF as P2，DEPT where P1.D = DEPT.D and DEPT.DEAN = P2.P and P1

27、.SAL P2.SAL,字符串操作,命令格式 格式 列名 not like “字符串” 找出满足给定匹配条件的字符串,字符串操作,匹配规则 “%” 匹配零个或多个字符 “” 匹配任意单个字符 Escape 定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待 如escape “”，定义 作为转义字符，则可用%去匹配%，用去匹配,思考：用什么去匹配 ？,字符串操作,示例 列出姓名以“张”打头的教师的所有信息 select * from PROF where PNAME like “张%”,字符串操作,列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的系的所有信息 s

28、elect * from PROF where PNAME like “% d ”,元组显示顺序,命令 order by 列名 asc | desc 示例 按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列 select DNAME，PNAME from PROF，DEPT where PROF.D = DEPT.D order by DNAME asc，PNAME desc,集合操作,命令 集合并：union 集合交：intersect 集合差： except 提示 集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明,集合操作,示例 求选修了001或（且）00

29、2号课程的学生号 (select S from SC where C = 001) union (intersect) (select S from SC where C = 002),集合操作,求选修了001和002号而没有选003号课程的学生号 (select S from SC where C = 001 or C = 002 ) except (select S from SC where C = 003),分组和聚集函数,分组命令 group by 列名 having 条件表达式 group by将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值 hav

30、ing则对分组进行选择，只将聚集函数作用到满足条件的分组上,分组和聚集函数,列出每个学生的平均成绩,列出每门课程的平均成绩,group by S#,group by C#,92,85,90,92,92,90,分组和聚集函数,聚集函数 平均值：avg 最小值：min 最大值：max 总和：sum 记数：count,count（*）与count（列名）的差别,分组和聚集函数,示例 列出各系的老师的最高、最低、平均工资 select D，max(SAL)，min(SAL)，avg(SAL) from PROF group by D,分组和聚集函数,火眼金睛 求选修了课程的学生人数 select co

31、unt (S ) from SC select PNAME，max(SAL) from PROF select D，avg(SAL) from PROF group by D where AGE 60,分组和聚集函数,列出及格的学生的平均成绩 select S，avg(SCORE) from SC group by S having min(SCORE) = 60 select S，avg(SCORE) from SC where SCORE =60 group by S,哪个正确？,分组和聚集函数,列出每一年龄组中男学生（超过50人）的人数 select AGE，count(S#) from

32、 S where SEX = M group by AGE having count(*) 50,空值,空值测试 is not null 测试指定列的值是否为空值 示例 找出年龄值为空的老师姓名 select PNAME from PROF where AGE is null 不可写为where AGE = null,空值,注意事项 除is not null之外，空值不满足任何查找条件 如果null参与算术运算，则该算术表达式的值为null 如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽

33、略null,空值,例：select sum(G) from SC 例：select count(*) from SC,360,6,上一节课内容,SQL数据定义功能 SQL数据查询功能 SQL数据查询基本结构 select子句 重复元组的处理 from子句 where子句 更名运算 字符串操作 元组显示顺序 集合操作,嵌套子查询,集合成员资格 集合之间的比较 集合基数的测试 测试集合是否为空 测试集合是否存在重复元组 SQL语言中，一个selectfromwhere语句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。 嵌套查询的求解方法

34、是由里向外处理。,集合成员资格,in 子查询 表达式 not in （子查询） 判断表达式的值是否在子查询的结果中 示例 列出张军和王红同学的所有信息 select * from S where SNAME in （“张军”，“王红”） 查询与吴国强在同一个系学习的学生的学号、姓名及所在系。,集合成员资格,选修了001号课程的学生的学号及姓名 select S，SNAME from S where S in (select S from SC where C = 001） 选修了课程名为“数据库系统”的学生号和姓名。,集合成员资格,列出选修了001号和002号课程的学生的学号 select S

35、# from SC where SC.C# = 001 and S# in （select S# from SC where C# = 002）,集合之间的比较,some/all子查询 表达式 比较运算符 some （子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式 比较运算符 all （子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符,集合之间的比较,示例 找出平均成绩最高的学生号 select S# from SC group by S# having avg(SCORE) = all （select avg(SCORE) from SC group

36、 by S#） 查询其它系中比软件学院某一（所有）学生年龄小的学生姓名及年龄。,集合基数的测试,测试集合是否为空 not exists （子查询） 判断子查询的结果集合中是否有任何元组存在,in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询,集合基数的测试,列出选修了01号课程的学生的学号及姓名 select S#，SNAME from S where exists （select * from SC where C# = 01 and S# = S.S#） 注：查学生库，条件为选修了01号课程，即存在一个选课记录。 依次取

37、S中元组的S值，检查SC关系，如果SC中存在这样的元组，其S等于S.S且C01，则将S.Sname送入结果关系（相关子查询）,集合基数的测试,列出选修了001号和002号课程的学生的学号 select S# from SC SC1 where SC1.C# = 001 and exists （select S# from SC SC2 where SC2. C# = 002 and SC2.S# = SC1.S#）,列出选修了全部课程的学生姓名 select SNAME from S where not exists （select C# from C where not exists （se

38、lect * from SC where SC.C# = C.C# and SC.S# = S.S# ）,任意课程，所求学生选之 不存在任何一门课程，所求学生没有选之,集合基数的测试,列出至少选修了001号学生选修的所有课程的学生名 select SNAME from S where not exists （select C# from COURSE where exists （select * from SC where SC.C = COURSE.C and SC.S = 001） and not exists （select * from SC where SC.C = COURSE.C

39、 and SC.S = S.S）,任意课程，001号学生选之，所求学生选之 不存在任何一门课程，001号学生选之，所求学生没有选之,集合基数的测试,测试集合是否存在重复元组 unique (子查询） 如果子查询结果中没有重复元组，则返回true,集合基数的测试,示例 找出所有只讲授一门课程的老师姓名 select PNAME from PROF where unique （select P from PC where PC.P = PROF.P),集合基数的测试,找出至少选修了两门课程的学生姓名 select SNAME from S where not unique （select S fr

40、om SC where SC.S = S.S),be TRUE or not be TRUE , that is the question unique (a , b , null) , (a , b , null) ?,派生关系,命令 （子查询） as 关系名（列名，列名，） SQL-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用 表达复杂查询时可以考虑引入派生关系（子程序）,派生关系 vs 视图？,派生关系,示例 找出平均成绩及格的学生 先求出每个学生的平均成绩，再从中找出及格的学生 select SNAME ， avg（SCORE） fro

41、m S，SC where SC.S = S.S group by SC .S,派生关系,select SNAME ， AVG_SCORE from (select SNAME ， avg（SCORE） from S，SC where SC.S = S.S group by SC .S) as result(SNAME ， AVG_SCORE ) where AVG_SCORE = 60,视图,定义视图 create view view_name(列名，列名 ) as （查询表达式） with check option 视图的属性名缺省为子查询结果中的属性名，也可以显式指明 with check

42、 option指明当对视图进行insert，update时，要检查是否满足视图定义中的条件（视图的完整性） 撤消视图 drop view view_name,视图,示例：软件学院教师及其工资 create view SOS_PROF as (select P , PNAME , SAL from PROF，DEPT where PROF.P = DEPT.P and DEPT.DNAME = “软件学院”）,视图,系的工资概况 create view DEPTSAL( D, LOW, HIGH, AVERAGE, TOTAL ) as ( select D, min(SAL), max(SAL

43、), avg(SAL), sum(SAL) from PROF group by D ),视图,给出软件学院工资超过800的老师姓名 select PNAME from COMPUTER_PROF where SAL 800 给出软件学院老师的最低、最高、平均工资以及工资总额 select LOW , HIGH , AVERAGE , TOTAL from DEPTSAL , DEPT where DEPTSAL.D# = DEPT.D# and DEPT.DNAME =“软件学院”,关系的连接,基本分类 连接成分 包括两个输入关系、连接条件、连接类型 连接条件 决定两个关系中哪些元组相互匹配

44、，以及连接结果中出现哪些属性 连接类型 决定如何处理与连接条件不匹配的元组,关系的连接,关系的连接,自然连接 出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次 on 出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条件P，且公共属性出现两次 using (A1, A2 , An) (A1, A2 , An)是两个连接关系的公共属性的子集，元组在(A1, A2 , An)上取值相等，且(A1, A2 , An)只出现一次,关系的连接,内连接 舍弃不匹配的元组 左外连接 内连接 + 左边关系中失配的元组（缺少的右边关系属性值用null表示） 右外连接 内连

45、接 + 右边关系中失配的元组（缺少的左边关系属性值用null表示） 全外连接 内连接 + 左边关系中失配的元组（缺少的右边关系属性值用null表示）+ 右边关系中失配的元组（缺少的左边关系属性值用null表示）,关系的连接,cross join 两个关系的笛卡儿积 union join 左边关系中失配的元组+ 右边关系中失配的元组 对于外连接，连接条件是必须的 对于内连接，连接条件是可选的，没有连接条件等价于两个关系的笛卡儿积,关系的连接,列出老师的教工号、姓名、工资、所教课程号 select P#，PNAME，SAL，C# from （PROF nature left outer join

46、PC）,关系的连接,R inner join S on R.C = S.C,R,S,关系的连接,R left outer join S on R.C = S.C,R nature right outer join S,关系的连接,R full outer join S on R.C = S.C,SQL的数据修改功能,插入 删除 修改 视图更新,插入操作,命令 insert into 表名 （列名，列名 values （值 ，值） 插入一条指定好了值的元组 insert into 表名 （列名，列名 （子查询） 插入子查询结果中的若干条元组,插入操作,示例 insert into PROF va

47、lues ( P123, “王明”, 35, D08, 498 ) insert into PROF (P#, PNAME, D#) values ( P123, “王明”, D08 ) 思考：SAL取何值？,注：INTO子句中未出现的属性列，新记录中取空值，NOT NULL的属性列不能为空。 如果INTO子句中未指定列，插入的记录必须在每个列上均有值。,插入操作,将平均成绩大于90的学生加入到EXCELLENT中 insert into EXCELLENT ( S#, SCORE) select S# , avg(SCORE) from SC group by (S#) having avg

48、(SCORE) 90,插入操作,不支持修改在子查询中出现的表，为什么？,insert into PROF select * from PROF,若支持，则完成查询后，再执行修改操作,删除操作,命令 delete from 表名 where 条件表达式 从表中删除符合条件的元组，如果没有where语句，则删除所有元组,删除操作,示例 清除所有选课记录 delete from SC 删除王明老师所有的任课记录 delete from PC where P# in (select P# from PROF where PNAME = “王明”),删除操作,删除低于平均工资的老师记录 delete f

49、rom PROF where SAL (select avg(SAL) from PROF) 思考：是先找到所有符合条件的元组，一并删除，还是找到一个删除一个？,更新操作,命令 update 表名 set 列名 = 表达式 | 子查询 列名 = ，表达式 | 子查询 where 条件表达式 指定对哪些列进行更新，以及更新后的值是什么,更新操作,示例 老师工资上调5% update PROF set SAL = SAL * 1.05,更新操作,将D01系系主任的工资改为该系的平均工资 update PROF set SAL = (select avg(SAL) from PROF where D

50、# = D01) where P# = (select DEAN from DEPT where D# = D01),更新操作,当C1课程的成绩小于该课程的平均成绩时，将该成绩提高5% update SC set SCORE = SCORE* 1.05 where C# = C1 and SCORE (select avg(SCORE) from SC where C# = C1),更新操作,工资超过2000的缴纳10%所得税，其余的缴纳5%所得税 update PROF set SAL = SAL * 0.9 where SAL 2000 update PROF set SAL = SAL

51、* 0.95 where SAL = 2000,执行顺序是，还是，？ 可以利用case结构。,视图更新,示例 create view P_SAL as (select P# , PNAME , SAL from PROF ),insert into P_SAL values ( P08 , “张立” , 750 ),insert into PROF values ( P08 , “张立” , null , null , 750 ),转换为,视图更新,create view SN_AGE as (select SNAME, AGE from S ),insert into SN_AGE valu

52、es ( “张立” ,23 ),insert into S values (null, “张立”, 23, null),转换为,缺主码信息,视图,create view S_G(S#, AVERAGE ) as ( select S#, avg(SCORE) from SC group by S# ) update S_G set AVERAGE = 85 where S# = S1 如何更新SC？,视图更新,示例 create view S_C_G as (select SNAME, CNAME , SCORE from S, C, SC where S.S# = SC.S# and C.C

53、# = SC.C# ) insert into S_C_G values ( “张立” , “物理”，97 ) 往SC中插入对应姓名为张立的学号和对应课程名为物理的课程号，以及成绩为97的元组 如果有多个张立怎么办？,视图更新,视图更新约束 select子句中的目标列不能包含聚集函数 select子句中不能使用unique或distinct关键字 不能包括group by子句 不能包括经算术表达式计算出来的列 对于行列子集视图可以更新（视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主码）,嵌入式SQL,为什么使用嵌入式SQL？ 嵌入式SQL执行过程 需要解决的几个问题,为什么使

54、用嵌入式SQL？,有些操作对于交互式SQL是不可能的任务 SQL的表达能力相比高级语言有一定的限制，有些数据访问要求单纯使用SQL无法完成。一方面，SQL在逐渐增强自己的表达能力（参见数据库系统概念习题4.10），另一方面，太多的扩展会导致优化能力及执行效率的降低 非声明性动作 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现,嵌入式SQL执行过程,需要解决的几个问题,区分SQL语句与C语言语句 嵌入的SQL语句以EXEC SQL开始，以分号(;) 或END_EXEX结束 EXEC SQL delete from PROF whe

55、re DNO = 10；,需要解决的几个问题,嵌入SQL语句与C语言之间的数据传递 宿主变量 C变量，既可以用在C语句中，也可用在SQL语句中，用来在两者之间传递数据 宿主变量的声明 声明为通常的C变量，并将其放在下列标识语句之间 EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION,需要解决的几个问题,EXEC SQL BEGIN DECLARE SECTION int prof_no; char prof_name30; int salary; EXEC SQL END DECLARE SECTION,需要解决的几个问题,注：宿主变量出现于SQL语句中时，前面加（:）以区别列名 宿主变量可出现的地方：SQL的数据操纵语句中可出现常数的任何地方，select，fetch等语句的into字句中 示例：EXEC SQL select PNAME , SAL into :prof_name , :salary from PROF where PNO = prof_no ;,需要解决的几个问题,指示变量 C变量，用来指示返回给宿主变量的值是否为null值，以及返回给宿主变量的字符