数据库系统原理及应用-丁忠俊-第三章-关系DB的标准语言SQLPPT课件.pptVIP

第三章关系DB的标准语言SQL SQL的数据定义 基本表 视图 索引SQL的数据查询 重点 SQL的数据更新 内容 第一节SQL StructuredQueryLanguage 基本简介 SQL起源于1974年 作为关系DB的标准语言 用于 ORACLE SQL DB DB2等中 标准版本 SQL89 SQL2 92年 SQL3 正在修改 一 SQL的特点 集DB定义 操纵 控制功能于一体 高度非过程化 面向集合的操作方式 提供两种用方式 交互式和嵌入式 语言简洁 易学易用 二 SQL结构 基本表 BaseTable 关系模式支持DB的三级模式的结构 视图 View 子模式存储文件 StoredFile 存储模式 其中 元组称为行 row 属性称为列 column 1 SQL中的基本表对应DB中的一个关系 一行对应一个元组 一列对应一个属性值域 2 SQL中的视图由基本表或视图导出 基本表是实际存储在DB中的表 视图是虚表 它的数据仍在导出的基本表中 它的定义存在数据字典中 3 一个基本表可以跨一个或多个存储文件 一个存储文件也可以存放一个或多个基本表 每一个存储文件对应外存上一个物理文件 4 用户可用SQL语句对视图和基本表作查询等操作 在用户来看视图与基本表都是关系 5 SQL的语句既可交互使用 也可嵌套到宿主语言的程序中使用 结构要点 三 SQL的构成 数据定义 SQLDDL 用于定义基本表 视图 索引等 数据操纵 SQLDML 用于对DB的查询和更新等操作 数据控制 包括对基本表和视图的授权 完整性规则的描述 事务控制等 嵌入式SQL的使用规定 4个部分构成 第二节SQL的数据定义 包括 定义和撤消 基本表 table 视图 view 索引 index 完整性约束 一 基本表的创建 修改和撤消 1 SQL提供的主要数据类型 1 数值型 2 字符串型INTEGER 或INT 长整型CHAR n 长度为n的定长字符串SMALLINT短整型VARCHAR n 具有最大长度nREAL浮点型 取决于机器 可变字符串FLOAT n 浮点型精度为n位NCMERIC P d 定点数 p位数字 小数点后d位 3 位串型 4 时间型BIT N 长度为N的二进制的往事 DATE日期 YYYY MM DDTIME时间 时 分 秒 HH MM SS算术操作仅限于数值型的数据 2 基本表的创建 修改和撤消 1 创建基本表句型 CreateTable基本表名 列名类型 列完整性约束 主关键字子 PRIMARYKEY 其中 完整性约束定义有三种子句 检查子句 CHECK 外关键字子句 FOREIGNKEY 例 教学DB中有三个关系模式 S sno sname sex age dept Sc sno cno grade C cno cname credit 定义其基本表 Createtables snoCHAR 5 NOTNULLUNIQUE snameCHAR 8 sexCHAR 2 ageINT deptCHAR 6 primaryKEY sno Createtablesc snoCHAR 5 cnoCHAR 4 gradeSMALLINT PRIMARYKEY sno cno FOREIGNKEY cno REFERENCESC cno FOREIGNKEY sno REFERENCESS sno CHECK gradeISNULL OR gradeBETWEEN0AND100 其基本表定义如下 表C定义略 2 基本表结构的修改增加新的属性 ALTERTABLE基本表名ADD列名类型例 ALTERTABLESADDPHONCHAR 8 删除原有的属性 ALTERTABLE基本表名DROP CASCADE RESTRICT 其中 CASCADE 所有引用该列的视图和约束也要一起被删除 RESTRICT 只有视图和约束没有引用该属性时 才能删除 否则拒绝 例 ALTERTABLESDROPageCASCADE 3 基本表的撤消句型 DROPTABLE基本表名 例 DROPTABLES 三 视图的创建与撤消视图 外模式 子模式 由基本表或其它已建视图构造出的表 1 视图的创建句型 CreateView视图名 列名表 ASSELECT查询子句例 若用户经常用到sno sname cname和grade信息 则建立视图供查询CreateViewSg sno sname cname grade Asselects sno sname cname gradefroms sc cwheres sno sc snoAo o 2 视图撤消句型 DROPView视图名 四 索引的创建及撤消建立索引的目的 加快对关系的查找 浪费外存空间 在基本表上可建立一个或多个索引 索引存在存储文件中称索引文件 句型 Create UNIQUE INDEX索引名ON基本表名 列名 例 CreateindexSIONS sno 按sno升序排列CreateUniqueindexSIONS sno sno中值对应唯一的记录值CreateUniqueindexSCIONSC snoASC cnoDESC 索引定义的结构存在数据字典库中 学号按升序排列 CNO值按降序排列 索引的撤消 DROPindex索引名例 DROPindexSI 第三节SQL的数据查询 数据查询是DB中最重要 最基本的操作 查询操作是更新操作的基础 查询功能的强弱直接影响DB使用性能 一 select查询语句的句型select 目标表的属性名或目标列表达式 from 表名或视图名 from子句 where 行条件表达式 行条件子句 groupby 列名1 having 条件表达式 列条件子句 orderby 列名2 asc desc 排序子句执行过程根据where子句的条件表达式 从from子句指定的基本表或视图中找出满足条件的元组 再按select子句中的目标列表达式 选出元组中的属性值形成结果表 如果有group子句 将结果按 列名1 的值进行分组 列值相等的元组分为一组 每一组会产生结果表中的一条记录 having子句将满足条件的组给出输出 order子句对输出的目标表按asc升序或desc降序排列 select基本句型selectA1 A2 An 属性名Ai fromR1 R2 Rn 关系名或视图名Ri A1 A2 An F R1 R2 Rn whereF 逻辑表达式F 其中 F可使用以下运算符算术比较符 或 逻辑符 AND OR NOT集合运算符 union 并 intersect 交 except 差 IN 属于 谓词 exists 存在量词 all any some unique 唯一 LIKEF 中还可以是select子句 嵌套 使用的函数 avg 列名 求平均值min 列名 求最小值max 列名 求最大值sum 列名 求和count 列名 求列中值的个数count 求元组个数 二 SELSCT语句的使用单表查询多表关联查询 嵌套查询 集合查询1 单表查询例 教学DB三个基本表 s sno sname sex age dept 学号 姓名 性别 年龄 系名c cno cname pno credit 课号 课名 先修课名 学分sc sno cno grade 学号 课号 成绩 列上查询例1 查询学生的学号 姓名和系名selectsno sname deptfroms 例2 查询全部学生的详细情况select froms 例3 查询全部学生的姓名及出生年份selectsname 2003 agefroms 结果 sname2003 age李一1981刘二1982王三1983例4 查询全部学生的姓名 出生年份和系名要求 指定别名改变查询结果的列标题selectsnameNAME 2003 ageBIRTHDAY ISLOWER dept DEPARTMENTfroms 结果 NAMEBIRTHDAYDEPARTMENT李一1981cs刘二1982is王三1983ma 行上查询例1 查询所有选修课程的学生的学号 SelectdistinctsnofromSC 例2 查找成绩不及格的学生的学号及课号 selectdistinctsno cnofromscwheregrade 90ANDgrade 100 或 wheregradebetween90and100 例4查找信息系 IS 数学系 MA 和计算机系 CS 的学生的姓名和性别selectsname sexfromswheredeptIN IS MA CS wheredeptNOTIN CS 排除CS系 SC表原来值 SnoCnogradeSno20001180操作后 200012852000239020002280 例5 查找所有姓 刘 的学生的姓名 学号和性别 谓词LIKE可实现字符串的匹配 满足模糊查询的要求格式 NOT LIKE 匹配串 ESCAPE 换码字符 含义 查找指定的属性列值与 匹配串 相匹配的元组 一个完整字符串通配符 和 表示任意长度 长度可为0 的字符串 如 a b表示以a开头 b结尾任意长度的字符串 下划线 表示任意单个字符 如 ab表示a开头 b结尾的长度为3的任意字符串selectsname sno sexfromSwheresnameLIKE 刘 其中 匹配串 例6 查找名字中第二个字为 涛 字的学生的学号及姓名Selectsno snamefromsWheresnameLIKE 涛 前一个字为汉字占二个字符位置 例7 查找课程名为 DB 开头 且倒数第3个字符为i的课号 课名和学分Select FromcwherecnameLIKE DB i ESCAPE 结果 CnoCnamecredit8DBDesing410DBPrograming3例8 查找选课后 没有参加考试的学生的学号和课号Selectsno cnofromscWheregradeISNULL 注 IS不能用 代替 例9 查找选修3号课程的学生的学号及成绩 按降序排列 Selectsno gradefromscWherecno 3 orderbygradeDESC 例10 统计查询的例子selectcount selectcount distinctsno froms 统计学生总人数fromsc 统计选了课程的学生的人数Selectmax grade 查找1号课程考试的最高分数fromscwherecno 1 例11 查找信息系 IS 选修了3门以上的课程的学生的学号 假定SC中有一列dept记录了系名 selectsnoFromscWheredept IS groupbysnohavingcount 3 结果分组查询 SnoCnodept11IS12IS13IS14IS21IS22IS23IS24IS 2 关联查询多个表联接查询 等值与非等值的连接查询a等值连接查询 b笛卡尔积的连接c自然连接Selects sc selects sc selectsno sname sex dept cno gradefroms scfroms scfroms scwheres sno sc snowheres sno sc sno 设 S SC a 等值连接结果 b 笛卡尔积连接结果产生 3 5 15个元组 产生无意义的元组 因此 该连接很少使用 c 自然连接的结果 只是去掉重复的sno snosnamesexdeptcnograde 自身连接例 查找至少选择选修课程号为 1 号和为 2 号的学生的学号 selectx snofromSCx SCywherex sno y snoANDx cno 1 ANDy cno 2 结果 x sno20001 外连接 为外连接符 或 它可将一些为空值列连接到结果表中 将 中C改写为外连接selectsno sname sex dept cno gradefroms scwheres sno sc sno 注 出现连接符的右边称左外连接 出现连接符的左边称右外连接 结果 嵌套结构的查询例 查询选修课程号为 2 的学生姓名与学号a Selects sno s sname 涉及表s和sc连接查找 froms scs sno sname sex age dept wheres sno sc snoANDcno 2 sc sno cno grade b 写法 Selectsno sname 嵌套查询结构 fromswheresnoIN selectsnofromscwherecno 2 注 嵌套结构层次分明 具有结构化程序设计特点嵌套结构比不嵌套结构查询效率高 因先作选择 子查询中不能出现orderby子句 即不能针对子查询排序 c 写法 使用存在量词的嵌套查询 selectsno snamefromswhereEXISTS select fromSCwhereSC Sno S SnoANDCno 2 例 查找选修了课程名为 信息系统 的学生学号与姓名selectsno snamefromswheresnoIN selectsnofromscwherecnoIN selectcnofromcwhere cname 信息系统 涉及 三个表 s sno sname sex age dept sc sno cno grade c cno cname pno credit 例 查找其他系中比IS系任何学生年龄小的学生名单selectsname agefromswhereage ALL selectagefromswheredept IS ANDdept IS OrderbyageDESC 例 查询选修了全部课程的学生姓名SelectsnamefromswhereNOTEXISTS select fromcwhereNOTEXISTS select fromscwheresc sno s snoANDo c cno 1 涉及s sc c三个表 2 SQL无全程量词 运算符 因此将题意转化为等价的存在量词的形式 查询这样的学生姓名 没有一门课是他不选的 x p x p 例 查找至少有一门课的成绩超过学号为20002的一门课成绩的学生学号selectdistinctsnofromscwheregrade some selectgradefromscwheresno 20002 3 集合查询例 查找计算机系的学生及年龄不大于19岁的学生实际求计算机系的所有学生与年龄不大于19岁其它系的学生的并集select fromswheredept cs UNIONSelect fromswhereage 19 第四节SQL的数据更新 数据插入数据删除数据修改 更新包括 一 数据插入 元组值的插入查询结果的插入 包括 1 元组值的插入一般句型 INSERTINTO基本表名 列名表 VALUES 元组值 一次仅插入一个元组或INSERTINTO基本表名 列名表 TABLE 元组值 元组值 一次插入多个元组如 INSERTINTOSC TABLE 20005 1 85 20005 2 90 20005 3 95 2 查询结果的插入一般句型 INSERTINTO基本表名 列名表 SELECT查询语句 把当前表中满足条件的元组送到已存在的表中 如 从基本表SC中 把平均成绩 80的男学生的学号和平均成绩存入另一个已知的基本表SG sno sg1 中 INSERTINTOSG sno sg1 SELECTsno AVG grade FROMSCWHEREsnoIN SELECTsnoFROMSWHERESEX 男 GROUPBYsnoHAVINGAVG grade 80 二 数据的删除一般句型 DELETEFROM基本表名 WHERE条件表达式 从一个基本表中删除满足条件的元组例 删除SC中 1 号课程中小于该课程平均成绩的成绩元组DELETEFROMSCWHEREcno 1 ANDgrade SELECTAVG grade FROMSCWHEREcno 1 三 数据的修改修改基本表中元组的某些列值一般句型 UPDATE基本表名SET列名 值表达式 列名 值表达式 WHERE条件表达式 例 将女同学的成绩提高10 UPDATESCSETgrade grade 1 1WHEREsnoIN selectsnofromswheresex 女 四 视图的更新操作1 视图的概念视图从一个或多个基本表 或已定义的视图 导出的表 提供给用户查询DB中数据使用 视图是一个虚表 它的框架定义的属性存于数据字典库中 其数据仍在原来基本表中 视图的查询与基本表的查询使用select语句 视图的作用 1 视图能简化用户的操作 通过视图可以从一个基本表或多个基本表中抽取用户感兴趣的数据 方便用户处理 2 视图对重构DB提供了一定程度的逻辑独立性 例 将表s sno sname sex age dept 垂直划分为两个表 sx sno sname dept sy sno sex age 若建立一个视图s Createviews sno sname sex age dept asselectsx sno sx sname sy sex sy age sx deptfromsx sywheresx sno sy sno 这样尽管DB逻辑结构改变了 但应用程序不必修改 因为新建立的视图定义了用户原来的关系 使用户的外模式保持不变 3 视图能对保密数据提供保护 2 视图的更新操作视图是不实际存储数据的虚表 因此 对视图的更新 最终要转换为对基本表的更新 必须对视图的更新操作作一些限制 查询不限制 视图更新的三条规则 1 不允许对从多个基本表作连接操作导出的视图作更新操作 2 不允许对使用了分组 groupby 和函数 如 MIN等 操作导出的视图作更新 3 如果视图从单个基本表 使用选择 投影操作导出的 并包含了基本表的主关键字或某个候选关键字 那么这样的视图称为 行列子集视图 可作更新操作 例 从s表中定义一个有关男同学的视图CreateviewS1 sno snam age asselectsno sname agefromSwheresex 男 该视图从单基本表导出 且包含关键字sno 可以更新 执行插入操作 INSERTINTOS1VALUES 20006 王五 20 例 定义视图CreateviewSSG sno cnum Avgg asselectsno count sno Avg grade fromscwheregradeISNOTNULLGroupbysno SSG由分组和函数操作导出 据 2 规则不能更新 第五节嵌入式SQL的使用 交互式SQL 用户在终端上以命令形式使用嵌入式SQL 在高级语言源程序中嵌入SQL语句 SQL语言使用形式 一 嵌入式SQL的使用规定 扩充宿主语言的编译程序功能 使之能处理SQL语句采用预处理方式处理SQL语句 嵌入式SQL两种处理方式 宿主语言语句 SQL语句 预处理方式SQL语句的过程 预备处理程序 宿主语言语句 函数调用 宿主语言编译程序 宿主语言目标程序 SQL函数定义库 SQL与宿主语言的接口SQL与宿主语言的接口是共享变量 共享变量由宿主语言程序定义 再用SQL的DECLARE语句说明 随后SQL语句可引用这些变量 SQL2规定 SQLSTATE是一个特殊的共享变量 起着解释SQL语句执行状况的作用 如报错 嵌入式SQL的使用规定 1 在SQL语句前使用前缀标识 EXECSQL END EXEC 后缀标识 括起来 以示区别宿主语言语句 注 END EXEC后缀标识在C和PASCAL语言中用 号表示 2 允许嵌入的SQL语句引用宿主语言的程序变量 称为共享变量 但有两条规定 a 引用时 变量前必须加冒号 作前缀标识 以示与数据库中变量有区别 b 这些变量由宿主语言的程序定义 并用SQL的DECLARE语句说明 如 C语言程序中使用如下形式定义共享变量 EXECSQLBEGINDECLARESECTION CharSno 5 name 9 CharSQLSTATE 6 EXECSQLENDDECLARESECTION 3 SQL的集合处理方式与宿主语言单记录处理方式的协调由于SQL语句处理的是记录集合 而宿主语言语句一次只能处理一个记录 因此需要用游标 cursor 机制 把集合操作转换成单记录处理方式 与游标有关的SQL语句有下列四个 a 游标定义语句 游标是与某一查询结果相联系的符号名 定义形式 EXECSQLDECLARE 游标名 CURSORFOR SELECT语句 END EXECb 游标打开语句 OPEN 执行游标定义中的SELECT语句 使游标 实际是指针 指向查询结果的第一行之前 语句型 EXECSQLOPEN 游标名 END EXEC c 游标推进语句 FETCH 游标推进一行 将游标所指的当前行的值送共享变量 句型 EXECSQLFETCHFROM 游标名 INTO 变量表 END EXEC注 FETCH常置于宿主语言程序的循环语句中 d 游标关闭语句 CLOSE 句型 EXECSQLCLOSE 游标名 END EXEC注 在游标打开后 可修改和删除游标所指的元组 二 嵌入式SQL的使用技术 涉及游标机制的使用技术不涉及游标机制的使用技术 方式 1 不涉及游标的SQLDML语句INSERT DELETE和UPDATE语句加上前缀标识 EXECSQL 可嵌入使用SELECT语句查找一个元组时 直接将该元组转送共享变量中 如 EXECSQLSELECTsname age sex 在s中寻找学生姓名 年龄和性别 INTO sn sa ssFROMSWHERES give 2 涉及游标的SQLDML语句a SELECT语句使用当查找多个元组时 则用游标机制将多个元组一次一个从DB中读出并处理 具体过程 用游标定义语句定义一个游标与某一个SELECT语句对应 用游标打开语句 OPEN 打开 使游标指向满足条件的元组之前 每执行一次FECTH 游标指向下一个满足条件的元组 并将其值送共享变量 继续循环 关闭语句 CLOSE 关闭游标 例 在SC基本表中查找某学生 学号由give给出 的学习成绩信息 S C GRADE 下面是该查询的C语言函数定义 defineNOM strcmp SQLSTATE 02000 voidsel EXECSQLBEGINDECLARESECTION Charsno 5 cno 6 give 5 Intg CharSQLSTATE 6 EXECSQLENDDECLARESECTION EXECSQLDECLARESCXCURSORFORSELECTS C gradeFROMSCWHERES give EXECSQLOPENSCX While 1 EXECSQLFETCHFROMSCXINTO sno cno g If NOM break Printf s