4-_数据库语言SQL.ppt

4数据库语言SQL 同济大学 本章目标 本章主要介绍SQL语言的基本语法和使用方式 要求熟练掌握SQL语言的使用规则 能够根据实际需求写出能够准确完成操作的SQL语句 4 1概述 SQL的发展1974年 由Boyce和Chamberlin提出1975 1979 IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言SQL 86是第一个SQL标准SQL 89 SQL 92 SQL2 SQL 99 SQL3 现状大部分DBMS产品都支持SQL 成为操作数据库的标准语言有方言 支持程度不同 数据定义 DDL 定义 删除 修改关系模式 基本表 定义 删除视图 View 定义 删除索引 Index 数据操纵 DML 数据查询数据增 删 改数据控制 DCL 用户访问权限的授予 收回 4 1 1SQL的功能 交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释嵌入式SQL能将SQL语句嵌入到高级语言 宿主语言 使应用程序充分利用SQL访问数据库的能力 宿主语言的过程处理能力一般需要预编译 将嵌入的SQL语句转化为宿主语言编译器能处理的语句 4 1 2SQL的形式 数据定义Create Drop Alter数据操纵数据查询 Select数据修改 Insert Update Delete数据控制Grant Revoke 4 1 3SQL的动词 数据查询是数据库应用的核心功能 基本结构 SelectA1 A2 AnFromr1 r2 rmWhereP 4 2数据查询 A1 A2 An p r1 r1 rm 4 2 1Select语句的含义 对From子句中的各关系 作笛卡儿积 对Where子句中的逻辑表达式进行选择 运算 找出符合条件的元组 根据Select子句中的属性列表 对上述结果作投影 操作 查询操作的对象是关系 结果还是一个关系 是一个结果集 是一个动态数据集 4 2 2Select子句对应于关系代数的投影 运算 用以列出查询结果集中的期望属性 重复元组SQL具有包的特性Select子句的缺省情况是保留重复元组 ALL 可用Distinct去除重复元组SelectDistinctsdeptFromStudentSelectAllsdeptFromStudent去除重复元组 费时需要临时表的支持 与属性列表星号 表示所有属性星号 按关系模式中属性的顺序排列 并具有一定的逻辑数据独立性显式列出属性名 按用户顺序排列Select FromStudentSelectStudent cno gradeFromStudent SCWhereStudent sno SC sno 更名为结果集中的某个属性改名使结果集更具可读性Selectsnoasstu no cnoascourse no gradeFromSCSelectsno sname 2001 sageasbirthdayFromStudent 4 2 3where子句 where子句对应与关系代数中的选择 查询满足指定条件的元组可以通过Where子句来实现 使where子句中的逻辑表达式返回True值的元组 是符合要求的元组 将被选择出来 运算符比较 not 确定范围 BetweenAandB NotBetweenAandB确定集合 IN NOTIN字符匹配 LIKE NOTLIKE空值 ISNULL ISNOTNULL多重条件 AND OR NOT like字符匹配 Like NotLike通配符 匹配任意字符串 匹配任意一个字符大小写敏感 列出姓张的学生的学号 姓名 Selectsno snameFromStudentWheresnameLIKE 张 列出张姓且单名的学生的学号 姓名 Selectsno snameFromStudentWheresnameLIKE 张 转义符escape例 列出课程名称中带有 的课号及课名 Selectcno cnameFromCourseWherecnameLIKE escape 4 2 4from子句 From子句对应与关系代数中的笛卡儿积 列出将被扫描的关系 表 例 列出所有学生的学号 姓名 课号 成绩 SelectSudent sno sname SC cno gradeFromStudent SCWhereStudent sno SC sno 元组变量为From子句中的关系定义元组变量方便关系名的引用在同一关系的笛卡儿积中进行辨别例 列出与95001同岁的同学的学号 姓名 年龄 SelectT sno T sname T sageFromStudentasT StudentasSWhereS sno 95001 ANDT sage S sage 4 2 5OrderBy子句 指定结果集中元组的排列次序耗时ASC 缺省 DESC 未选中的属性例 列出CS系中的男生的学号 姓名 年龄 并按年龄进行排列 升序 Selectsno sname sageFromStudentWheresdept CS OrderBysageASC 4 3数据定义语言 数据定义语言 DataDefinitionLanguage Create Drop Alter定义一组关系 基本表 说明各关系的信息各关系的模式各属性的值域完整性约束索引安全性和权限 4 3 1SQL中的域类型 字符型char n varchar n 数值型integer smallintnumeric p d real double float n 日期 时间型datetime 4 3 2SQL中的域定义 域定义语句 SQL 92支持 需重复使用的CreateDomainstu namevarchar 20 CreateDomainzip codechar 6 域的删除DropDomaindatelog各DBMS的方法是不同的Restrict Cascade使用该域的属性的处理 4 3 3SQL中的模式定义 CreateTabler A1D1 A2D2 AnDn 其中 r关系名 表名 Ai关系r的一个属性名Dn属性Ai域值的域类型主键声明 primarykey Aj1 Aj2 Ajvm CreateDomainstu namevarchar 20 CreateTableStudent snochar 10 primarykey sno snamestu name sagesmallint ssexchar 1 sdeptchar 2 CreateTableCourse cnochar 10 primarykey cno cnamevarchar 20 creditsmallint CreateTableSC snochar 10 notnull cnochar 10 notnull gradesmallint primarykey sno cno 4 3 4删除表结构 用SQL删除关系 表 将整个关系模式 表结构 彻底删除表中的数据也将被删除DropTablerDropTablestudent 4 3 5修改表结构 删除表中的某属性去除属性及相应的数据AlterTablerDropA增加表中的属性向已经存在的表中添加属性allownull已有的元组中该属性的值被置为NullAlterTablerAddADAlterTablestudentphonechar 16 4 3 6DefaultValue 属性的默认值用户不提供某属性的值时 默认值被使用初始值DDL中 ModifyDatechar 30 DefaultTimeStampAlterTablestudentAddIDintegerDefault1001 4 3 7索引Index 索引是一种数据结构 是对照表 指针表索引是为了加速对表中元组的检索而创建的一种分散存储结构 B树 索引是对表而建立的 由除存放表的数据页面以外的索引页面组成索引是把双刃剑 减慢更新的速度索引不是SQL标准的要求索引的种类聚簇索引 ClusteredIndex 非聚簇索引 Non ClusteredIndex 聚簇索引 ClusteredIndex 表中的元组按聚簇索引的顺序物理地存放根级页面 中间层页面 叶级页面 数据页面 一个表中只能有一个聚簇索引更新的复杂性 需要大量的临时空间非聚簇索引 Non ClusteredIndex 表中的元组按聚簇索引的顺序物理地存放根级页面 中间层页面 叶级页面 数据页面 一个表中只能有一个聚簇索引更新的复杂性 需要大量的临时空间 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEXindex nameOnTableName Column Column CreateIndexYearIndexOnMovie year CreateClusteredIndexSnoIndexOnstudent sno DropIndexYearIndex 4 4数据添加 用SQL的插入语句 向数据库表中添加数据按关系模式的属性顺序InsertIntoStudentValues 0095001 张三 27 M CS 按指定的属性顺序 也可以只添加部分属性 非Null属性为必需 InsertIntoStudent sno sname sage Values 0095002 李四 26 4 5聚合函数 把一列中的值进行聚合运算 返回单值的函数 五个预定义的聚合函数 平均值 Avg总和 Sum最小值 Min最大值 Max计数 CountCount Count Distinct 4 5 1GroupBy 将查询结果集按某一列或多列的值分组 值相等的为一组 一个分组以一个元组的形式出现 只有出现在GroupBy子句中的属性 才可出现在Select子句中 例 统计各系学生的人数 Selectsdept count asstu countFromStudentGroupBysdept 4 5 2Having 针对聚合函数的结果值进行筛选 选择 它作用于分组计算结果集 跟在GroupBy子句的后面 没有GroupBy则针对全表 例 列出具有两门 含 以上不及格的学生的学号 不及格的课目数 Selectsno count sno FromSCWheregrade 2 4 5 3Having与Where的区别 Where决定哪些元组被选择参加运算 作用于关系中的元组 Having决定哪些分组符合要求 作用于分组 聚合函数的条件关系必须用Having Where中不应出现聚合函数 4 5 4聚合函数忽略Null Count 不计Sum 不将其计入Avg 具有Null的元组不参与Max Min 不参与例 Selectcount sdept FromStudentSelectAvg sage FromStudent 4 6子查询 子查询是嵌套在另一查询中的Select From Where表达式 Where Having SQL允许多层嵌套 由内而外地进行分析 子查询的结果作为父查询的查找条件 可以用多个简单查询来构成复杂查询 以增强SQL的查询能力 子查询中不使用OrderBy子句 OrderBy子句只能对最终查询结果进行排序 4 6 1单值比较 返回单值的子查询 只返回一行一列父查询与单值子查询之间用比较运算符进行连接运算符 例 找出与95001同龄的学生Select FromStudentWheresage SelectsageFromStudentWheresno 95001 4 6 2多值 子查询返回多行一列运算符 In All Some Any Exists in标量值与子查询返回集中的某一个相等 truein被用来测试多值中的成员 例 查询选修 C01 课程的学生的学号 姓名 Selectsno snameFromStudentWheresnoIN SelectsnoFromSCWherecno C01 例 查询选修了 数据库 的学生的学号和姓名Selectsno snameFromStudentWheresnoIN SelectsnoFromSCWherecnoIN SelectcnoFromCourseWherecname 数据库 all多值比较 多行一列父查询与多值子查询之间的比较需用All来连接标量值s比子查询返回集R中的每个都大时 s AllR为TrueAll表示所有 all all allall等价于notin例 找出年龄最小的学生Select FromStudentWheresage all SelectsageFromStudent some any多值比较 多行一列父查询与多值子查询之间的比较需用Some Any来连接标量值s比子查询返回集R中的某一个都大时 s SomeR为True或s AnyR为TrueSome 早期用Any 表示某一个 只要有一个即返回真 some some some some等价于in some不等价于notin 例 找出不是最小年龄的学生Select FromstudentWheresage some SelectsageFromStudent 例 找出具有最高平均成绩的学号及平均成绩Selectsno avg grade FromSCGroupBysnoHavingavg grade all Selectavg grade FromSCGroupBysno existsExists 子查询用来判断该子查询是否返回元组当子查询的结果集非空时 Exists为True当子查询的结果集为空时 Exists为False不关心子查询的具体内容 因此用Select 例 列出选修了C01课程的学生的学号 姓名Selectsno snameFromStudentWhereExists Select FromSCWhereSC sno Student snoAndcno C01 例 列出得过100分的学生的学号 姓名Selectsno snameFromStudentWhereExists Select FromSCWhereSC sno Student snoAndgrade 100 例 查询选修了 C01 课程的学生的系主任SelectmanagerFromdepartmentWhereExists Select FromstudentWheresdept department depidAndExists Select FromSCWhereSC sno student snoAndcno C01 例 列出没有选C01课程的学生的学号 姓名Selectsno snameFromStudentWhereNotExists Select FromSCWhereSC sno Student snoAndcno C01 例 查询选修了所有课程的学生的姓名 ForAll SelectsnameFromStudentWhereNotExists Select FromCourseWhereNotExists Select FromSCWhereStudent sno SC snoAndSC cno Co SQL 92支持多列的成员资格测试 ASA7 0不支持 例 找出同系 同年龄 同性别的学生Select fromStudentasTWhere T sdept T sage T ssex IN Selectsdept sage ssexFromstudentasSWhereS snoT sno 4 6 3多列元组的比较 4 6 4派生关系 SQL 92允许在From中使用查询表达式必须为其取名例 查询平均成绩大于75分的学号 姓名 平均成绩Selectstu no sname avg gradeFromStudent Selectsno avg grade FromSCGroupBysno asS stu no avg grade WhereStudent sno S stu noAndavg grade 75 4 7空值与连接 4 7 1NULL表示数据的缺失一个确实存在 但我们不知道的值对本实体此数值无意义 可能是设计上的失误是SQL的关键字 用于任何类型描述缺失的值 4 7 3NULL的运算法则 对NULL值和其他任何值作算术运算时 结果为NULL对NULL值和其他任何值作比较时 结果为UNKOWNIsNull IsNotNull 4 7 4Unkown的运算规则 视True为1 False为0 Unkown为1 2AND 取小OR 取大NOT 取1的补真值表不必死记硬背 4 8数据库的修改 数据库修改包括插入数据 修改数据 删除数据 4 8 1数据删除 只能对整个元组操作 不能只删除某些属性上的值 只能对一个关系起作用 若要从多个关系中删除元组 则必须对每个关系分别执行删除命令 DeleteFromrWhereP从关系r中删除满足P的元组 只是删除数据 而不是定义 删除单个元组例 删除学号为95001的学生的选课信息DeleteFromSCWheresno 95001 删除多个元组例 删除选课而未参加考试的学生的选课信息DeleteFromSCWheregradeisnull删除整个关系中的所有数据例 删除所有学生的选课信息DeleteFromSC 4 8 2数据插入 单行插入 一次只插入一个元组例 新增一个学生信息InsertIntoStudentValues 98001 Gloria 25 F CS 多行插入 插入一个集合例 给CS系的学生开设必修课C05 建立选课信息InsertIntoSCSelectsno cno nullFromStudent CourseWheresdept CS andcno C05 4 8 3数据更新 改变符合条件的某个 某些 元组的属性值例 将95001学生转入MA系UpdateStudentSetsdept MA Wheresno 95001 例 所有学生年龄加1UpdateStudentSetsage sage 1 例 将选修C05课程的学生的成绩改为该课的平均成绩UpdateSCSetgrade Selectavg grade FromSCWherecno C05 Wherecno C05 4 9视图 是从一个或几个基本表 或视图 中导出的虚表数据库中只保存它的定义是RDBMS提供给用户以多种角度观察数据库中数据的重要机制创建视图 删除CreateViewvasDropViewv 例 计算机系的花名册CreateViewCS StuasSelectsno sname ssexFromStudentWheresdept CS 4 9 1视图 View 视图名可以出现在任何关系名可以出现的地方例 列出计算机系的男生Selectsno snameFromCS StuWheressex M 例 建立学生平均成绩视图CreateViewavg grade sno avg asSelectsno avg grade FromSCGroupBysno例 找出平均成绩大于等75的学生Select Fromavg gradeWhereavg 75注意 此例的使用方法是非标准的 视图的更新 单表 原始属性构成的视图可以更新 像基本表那样更新 由多表构成的视图由很大的限制视图中的非原始属性也不能更新例 Updateavg gradeSetavg 100Wheresno 95001 4 9 2视图的作用 简化用户的操作不同的用户可从不同的角度看待同一数据支持一定的逻辑数据独立性数据的安全性 4 9 3综合示例 CreateDomainstu namevarchar 20 CreateTableStudent snochar 10 notnull snamestu name sagesmallint ssexchar 1 sdeptchar 2 primarykey sno check ssexin M F CreateTableCourse cnochar 10 notnull cnamevarchar 20 creditsmallint primarykey cno CreateTableSC snochar 10 notnull cnochar 10 notnull gradesmallint primarykey sno cno check grade 0andgrade 100 4 10嵌入式SQL 前面所介绍的SQL语言作为一种独立的自含式语言 是联机终端用户在交互环境下使用的 称为交互式SQL INTERACTIVESQL 简称ISQL 在实际应用当中 常常还需要解决这样两个问题 有许多应用是过程化的 要求根据不同的条件来完成不同的任务 有许多应用不仅需要读出数据 还必须读查询得到的数据进行处理 对于上述问题 单独使用SQL语言是很难满足实际需求的 为了解决这些问题 SQL还提出了另外一种使用方式 即可以作为一种数据子语言嵌入某些主语言中 利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足 这种将SQL嵌入COBOL C C FORTRAN 中使用 称为嵌入式SQL EMBEDDEDSQL 简称ESQL 而接受SQL嵌入的高级语言 称为主语言或者宿主语言 对于宿主语言中的嵌入式SQl DBMS通常采用两种方法处理 一是预编译方法 一是修改和扩充主语言方法 使之能够处理SQL 当前主要采用第一种方法 其过程是 有DBMS的预处理程序对源程序进行扫描 识别出SQL语句 然后将它们转换为主语言调用语句 从而使得主语言能过识别它们 最后由主语言的编译程序将整个源程序编译成目标代码 所有在终端交互方式下使用的SQL均能在嵌入方式下使用 由于使用方式的差异 存在着具体操作方式上的不同 但无论是怎样的使用方式 都需要解决下面三个主要问题 应用程序中主语言的语句和SQL的语句 这两种语句 应用程序中既有主语言变量又有SQL列变量 如何区分这两种变量 主语言变量一般均为标量 而SQL中的列变量一般均为集合量 如何建立由集合量到标量的转换 4 10 1主语言语句和SQL语句的区分 在嵌入方式下 所有SQL语句在嵌入主语句的程序时几乎都必须在其前缀加EXECSQL 而结束标志可以因主语言不同而不同 一般是在语句结束处用END EXEC或用分号 在程序中所使用的SQL中的表 包括基表和视图都要用EXECSQLDECLEAR语句加以说明 SQL语句执行后 系统要反馈给程序以信息 这些信息均送入SQL通讯区 SQLCommunicationArea SQLCA SQLCA是一个数据结构 在应用程序中用EXECSQLINCLUDESQLCA语句加以定义 SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE 这是一个整型变量 反映SQL语句执行后的结果状态 应用程序每执行一条SQl语句之后都应测试一次SQLCODE的值 当它为0时表示正常结束 即SQL语句成功 非0时为非正常结束 SQL语句不成功 即使说 SQLCODE的基本功用是DBMS向宿主程序报告执行SQL语句的情况 例 在一些高级语言中使用嵌入式SQL语言的格式与删除S的情况 在C中使用SQL语言使以EXECSQL开始 以分号 结束 其格式为 EXECSQL 删除关系S的命令为 EXECSQLDROPTABLES 在COBLE中以EXECSQL开始 以END EXEC结束 格式为 EXECSQLEND EXEC删除S的语句为 EXECSQLDROPTABLESEND EXEC在PowerBuilder中使用SQL与通常SQL没有任何区别 语句前不需要加任何EXECSQL 只需用分号作为语句结束标记 即删除S的语句为 DROPTABLES 嵌入式SQL语句根据其作用的不同 可以分为可执行语句和说明性语句两类 可执行语句又分为数据定义 数据控制和数据操作三种 在宿主语言编写的程序 宿主程序 中任何允许出现执行高级语言语句的地方 都可以嵌入可执行SQL语句 任何允许出现说明性高级语句的地方 都可以写说明性SQL语句 4 10 2主语言变量与SQL变量的区别 主变量与指示变量主变量在嵌入方式下 程序中的SQL语句段内可使用主语言的程序变量来输入和输出数据 SQL中使用主语言程序定义的变量称为主变量 主变量根据其作用的不同 分为输入主变量和输出主变量 输入主变量由应用程序对其赋值 SQL加以引用 输出主变量由SQL语句对其赋值或设置状态信息 返回给应用程序 一个主变量可以既是输入主变量 又是输出主变量 利用输入主变量 我们可以完成以下功能 指定向数据库中插入的数据 把数据库中的数据修改为指定值 指定WHERE子句或HAVING子句中的条件 利用输出主变量 我们可以得到SQL语句的执行结果和状态信息 指示变量一个主变量可以附带一个任选的指示变量 indicatorvariable 指示变量是一个整型变量 用来 指示 所述主变量的值或者条件 指示变量可以指示输入主变量是否为空值 可以检测输出主变量是否为空值 值是否被截断等 主变量与指示变量的说明所有SQL语句中用到的主变量和指示变量都必须加以说明 说明的开头行与结尾行分别为 EXECSQLBEGINDECLEARSECTIONEXECSQLENDDECLEARSECTION 说明了之后的主变量和指示变量 可