北京邮电大学软件工程研究生的课程数据库设计开发.ppt

北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3SQL 自从20世纪80年代以来 SQL就是一个通用的 功能极强的关系数据库语言 现在 SQL语言正从关系形式 ANSISQL 92标准 转向对象 关系形式 ANSISQL 99标准 1999年颁布 SQL语言是1974年由Boyce和Chamberlin提出 1986年10月美国国家标准局 ANSl 批准了SQL作为关系数据库语言的美国标准 同年公布了SQL标准文本 简称SQL 86 1987年6月国际标准化组织 ISO 也采纳了此标准 1989年 美国国家标准局 ANSI 采纳了新的规范SQL 89标准 取代SQL 86 同时SQL 89标准也被国际标准化组织 ISO 采纳 1992年 ANSI ISO颁布了SQL2版本 标准的名称为SQL 92 SQL 92分称几个顺序级别 从代表SQL 89最小扩展集的 Entry 到 Intermediate 和 Full 完成于己于1999年的SQL 99具有更加高级的特征 包括对象 关系特性 亦称SQL3 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3SQL 主要的几个数据库生产厂商并不可能完全遵守SQL 99 以及更老的SQL 92 我们一般更加关注在产品中已经实现的SQL 99特征 1 3 1SQL特点1 3 2数据定义1 3 2 1SQL模式的创建和删除1 3 2 2SQL提供的基本数据类型1 3 2 3定义 删除与修改基本表1 3 2 4建立与删除索引1 3 2 5视图的创建和删除1 3 3数据查询1 3 3 1简单查询1 3 3 2子查询1 3 3 3UNION运算和FORALL条件 1 3 3 4高级SQL语法1 3 3 5集合函数1 3 3 6行分组1 3 4数据更新1 3 4 1插入数据1 3 4 2修改数据1 3 4 3删除数据 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 1SQL特点 1 综合统一2 高度非过程化3 面向集合的操作方式4 以同一种语法结构提供两种使用方式5 语言简捷 易学易用和关系代数相比 就查询能力而言 SQL并没有根本的改进 在关系代数查询方面的经验可以成为用SQL来实现查询的良好借鉴 在构造查询时SQL的select语句比关系代数要灵活 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2数据定义 SQL的数据定义功能包括对模式 Schema 表 关系 Table 视图 View 和索引 Index 的创建 删除和修改操作 如下表所示 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 1SQL模式的创建和删除 在SQL 99中 模式是表 索引及其他数据库对象的集合 模式名通常是一个用户名 在CoreSQL 99和当前大多数产品中 当用户的数据库帐户建立时 其模式在用户名之后给出 他们不能再建立其他模式 SQL 99的扩展特性允许用户建立附加模式 一个SQL模式由模式名和模式拥有者的用户名或账号来确定 SQL模式的创建可用CREATE语句实现 其句法如下 CREATESCHEMAAUTHORIZATION目前只有DB2UDB允许用户建立附加模式 其它产品中模式用用户名替代 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 1SQL模式的创建和删除 在SQL中还有一个 目录 概念 目录是SQL环境中所有模式的集合 包含数据库中定义的对象的信息的表 由系统维护 ORACLE叫数据字典 DB2UDB叫目录表 INFORMIX叫系统目录 目录表在建立数据库时建立 用户不能更新 但DBA可以用select获取这些信息 当一个SQL模式及其所属的基本表 视图等元素都不需要时 可以用DROP语句撤消这个SQL模式 DROP语句的句法如下 DROPSCHEMA CASCADE RESTRICT 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 2SQL提供的基本数据类型 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 3定义 删除与修改基本表 建立数据库最重要的一步就是定义一些基本表 SQL语言使用CREATETABLE语句定义基本表 一般格式如下 CREATETABLE 列级完整性约束条件 列级完整性约束条件 例 CREATETABLEStudent SnoCHAR 5 PRIMARYKEY SnameCHAR 8 NOTNULL SageSMALLINTCHECK SageBETWEEN17AND22 SsexCHAR 2 CHECK SsexIN 男 女 SdeptCHAR 20 DEFAULT 软件学院 创建学生表 Sno为主键 非空唯一 Sname非空 Sage在17到20之间取值 Ssex只能取 男 或 女 Sdept默认值为 软件学院 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 3定义 删除与修改基本表 实际使用时要有用户ID和密码 进入交互式环境 才能完成数据库操作 修改基本表ALTERTABLE ADD 完整性约束 DROP MODIFY 其中是要修改的基本表 ADD子句用于增加新列和新的完整性约束条件 DROP子句用于删除指定的完整性约束条件 MODIPY子句用于修改原有的列定义 包括修改列名和数据类型 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 3定义 删除与修改基本表 删除基本表DROPTABLE基本表一旦删除 表中的数据 此表上建立的索引和视图都将自动被删除掉 因此执行删除基本表的操作一定要格外小心 注意 有的系统 如Oracle 删除基本表后建立在此表上的视图定义仍然保留在数据字典中 但是 当用户引用时就报错 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 4建立与删除索引 索引的功能表现在以下3方面 1 使用索引可以明显地加快数据查询的速度 2 使用索引可保证数据的唯一性 3 使用索引可以加快连接速度建立索引的原则 1 索引的建立和维护由DBA和DBMS完成 2 大表应当建索引 小表则不必建索引 3 对于一个基本表 不要建立过多的索引 4 根据查询要求建索引 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 4建立与删除索引 CREATE UNIQUE CLUSTER INDEXON 如果数据增加删改频繁 系统会花费许多时间来维护索引 这时 可以删除一些不必要的索引 DROPINDEX 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 5视图的创建和删除 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制 视图一经定义 就可以和基本表一样被查询 被删除 我们也可以在一个视图之上再定义新的视图 但对视图的更新 增加 删除 修改 操作则有一定的限制 CREATVIEW 列名 AS WITHCHECKOPTION 其中子查询可以是任意复杂的SELECT语句 但通常不允许含有ORDERBY子句和DISTINCT短语 WITHCHECKOPTION表示对视图进行UPDATE INSERT和DELETE操作时要保证更新 插入或删除的行满足视图定义中的谓词条件 即子查询中的条件表达式 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 2 5视图的创建和删除 DROPVIEW 视图删除后视图的定义将从数据字典中删除 但是由该视图导出的其他视图定义仍在数据字典中 不过该视图已失效 用户使用时会出错 要用DROPVIEW语句将它们一一删除 DBMS执行CREATEVIEW语句的结果只把视图的定义存入数据字典 并不执行其中的SELECT 在关系数据库中 并不是所有的视图都是可更新的 因为有些视图的更新不能的有意义的转换成对基本表的更新 行列子集视图是可更新的 各个DBMS对视图的更新有自己的规定 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3数据查询 数据查询是数据库的核心操作 SQL语言的数据查询只有一条SELECT语句 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC ASC DESC 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3数据查询 Select语句的执行过程可以理解为 首先 对FROM子句中的所有表做关系乘积接着 删除不满足WHERE子句的行根据GROUPBY子句对剩余的行进行分组然后删除不满足HAVING子句的组求出SELECT子句选择列表的表达式的值若有关键词DISTINCT存在 则删除重复的行Select中的标识符 一般的SQL标识符是大小写无关的 实际上SQL在解释以前会把它们转化为大写形式 一个标识符必须以一个字母打头 EntrySQL 92和CoreSQL 99将一个标识符字节数限制在18个以内 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3数据查询 表达式可以是数值表达式 字符串表达式和日期表达式等 数值表达式由常数 表属性 算术运算符 算术函数所组成 字符串表达式由常数 表属性 字符串运算符 字符串函数所组成 日期表达式由常数 表属性 日期运算符 日期函数所组成WHERE中使用谓词来表示条件 一般情况谓词运算结果为TRUE或FALSE 但如果遇到空值时 可能为UNKNOWN SQL查询的一个争议点即 对于同一个查询会存在众多不同的构造方法 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3ORACLE INFORMIX DB2UDB中的一些数学函数 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3一些标准的和特定产品的串处理函数 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3SQL中的标准谓词 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 1简单查询 例 检索定货记录中所有pid值 selectpidfromorders 结果中有重复的pidselectdistinctpidfromorders 结果中pid唯一select缺省为all distinct没有出现时允许重复行 缺省情况不遵守行唯一性规则 例 在orders表上生成每笔业务的利润profit 收入减去60 的成本 顾客的折扣以及代理商的酬金 selectordno x cid x aid x pid 40 x qty p price 01 c discnt a percent x qty p price asprofitfromordersasx customerasc agentsasa productsaspwherec cid x cidanda aid x aidandp pid x pid 乘积 投影 选择 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 1简单查询 注 1 SQL 99规定FROM子句中执行连接运算 但大多数产品通过笛卡尔积运算并且在WHERE子句中包含表示参与连接的列值相等的条件来模拟连接运算 具体实现方法 执行计划 查询优化 各不相同 2 FROM中AS被省略 SQL仍能识别表别名 ORACLE和INFORMIX使用别名或表别名 DB2UDB使用相关名 3 列表达式的列名可以通过AS指明 ORACLE中称列别名 INFORMIX中称显示标签 DB2UDB中简单称为列名 如果没有AS ORACLE中将完整表达式文本作为列名 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 2子查询 每个Select查询都会生成一张表 但我们不能像关系代数表达式那样任意将一个Select语句嵌入另一个Select语句 这是SQL与关系代数的一个很重要的不同点 例如 from子句中不能出现select SQL 99标准已去掉该限制 但数据库产品中并未完全实现 where子句中显然可以出现select 出现在另一个select语句之内的select语句形式称为子查询 一个子查询能以许多种方式出现在另一个select语句的WHERE子句条件中 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 2子查询 1 IN谓词 NOTIN 例 求通过住在北京或上海的代理商订货的顾客的姓名和折扣 selectcname discntfromcustomerswherecidin selectcidfromorderswhereaidin selectaidfromagentswherecityin 北京 上海 例 求由住在北京的顾客和住在北京的代理商组成的所有订货ordno selectordnofromorderswhere cid aid in selectcid aidfromcustomersc agentsawherec city 北京 anda city 北京 以上两例为不相关查询 内层子查询独立于外层的select 例 找出订购了产品p05的顾客的名字 selectdistinctcnamefromcustomerswhere p05 in selectpidfromorderswherecid customers cid 本例为相关查询 子查询使用外层select语句提供的数据 SQL 99允许 有些系统不允许 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 2子查询 2 量化比较谓词 expr SOME ANY ALL subquery 为比较运算符 SOME和ANY含义相同 SOME是最新版本的推崇形式 例 找出佣金百分率最小的代理商aid selectaidfromagentswherepercent all selectpercentfromagents 例 求出满足以下条件的顾客cid 该顾客的discnt小于任一住在北京的顾客的discnt 错误 selectcidfromcustomerwherediscnt any selectdiscntfromcustomerswherecity 北京 正确 selectcidfromcustomerwherediscnt all selectdiscntfromcustomerswherecity 北京 注意any不是任意 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 2子查询 3 EXISTS谓词 EXISTS Subquery 为真当且仅当子查询返回一个非空集合 NOTEXISTS Subquery 为真当且仅当返回集合为空 例 求出既订购了产品p01有订购了产品p07的顾客cid 关系代数 cid pid p01 O cid pid p07 O selectdistinctcidfromordersxwherepid p01 andexists select fromorderswherecid x cidandpid p07 或selectdistinctx cidfromordersx ordersywherex pid p01 andx cid y cidandy cid p07 EXISTS的查询一般能找到等价的其他查询形式 使用和不使用exists 交运算 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 2子查询 NOTEXISTS确实为我们带来了一些新的功能 notexists能被用来实现关系代数的MINUS运算 如果R和S是两个兼容表 属性相同A1 An R S用SQL计算 selectA1 AnfromRwherenotexists select fromSwhereS A1 R A1and andS An R An 例 找出没有通过代理商a03订货的顾客cid 关系代数 cid O cid aid a03 O selectdistinctcidfromordersxwherenotexists select fromorderswherecid x cidandaid a03 差运算 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 3UNION运算和FORALL条件 为了提供关系代数的 运算 SQL使用UNION subqueryUNION ALL subquery例 包含了顾客所在的或代理商所在或两者皆在的城市名单 selectcityfromcustomersunionselectcityfromagents 或selectcityfromcustomersunionallselectcityfromagents 行不重复 行重复 运算 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 3UNION运算和FORALL条件 SQL中没有等价的 运算 如果面临的查询 要求被检索的对象集合必须符合 所有 这类关键词的条件 FORALL 时 关系代数要用到除运算 SQL中可以 1 表述要检索的候选对象的一个反例 至少一个对象不符合条件 并建立select语句 选出所有反例 2 建立表示这类反例不存在的条件 notexists 3 建立最终select 例 求通过住在北京的所有代理商订了货的顾客cid 1 反例 住在北京但没有为所求顾客c cid订货的代理商 select fromagentswherea city 北京 andnotexists select fromordersxwherex cid c cidandx aid a aid 2 反例不存在 notexists 反例 3 最终 selectc cidfromcustomerswherenotexists select fromagentswherea city 北京 andnotexists select fromordersxwherex cid c cidandx aid a aid 运算 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 3UNION运算和FORALL条件 例 找出订购了所有被顾客c006订购的商品的顾客的cid 反例 被c006订购但没有被c cid订购的商品 selectp pidfromproductspwherep pidin selectpidfromordersxwherex cid c006 andnotexists select fromordersywherey pid p pidandy cid c cid 反例不存在 notexists selectp pidfromproductspwherep pidin selectpidfromordersxwherex cid c006 andnotexists select fromordersywherey pid p pidandy cid c cid 最终 selectcidfromcustomerswherenotexists selectp pidfromproductspwherep pidin selectpidfromordersxwherex cid c006 andnotexists select fromordersywherey pid p pidandy cid c cid 运算 被c006订购 没有被c cid订购 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 4高级SQL语法 以下介绍的高级SQL运算符不是EntrySQL 92的部分 但几乎都属于SQL 99 目前产品中不一定支持 但可能出现在未来的数据库产品中 1 INTERSECT 和EXCEPT 运算符Subquery UNION ALL INTERSECT ALL EXCEPT ALL Subquery 注 CoreSQL 99只有EXCEPT而没有EXCEPTALL 两个子查询从左到右的列类型是兼容的 即可以并 交 差 如类型char 5 和类型char 10 的列运算 结果为char 10 ORACLE提供UNION UNIONALL INTERSECT MINUS EXCEPT 但不支持INTERSECTALL或MINUSALL DB2UDB都支持 ALL考虑重复行及数目 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 4高级SQL语法 例 QUNIONALLQUNIONALLQ INTERSECTALL QUNIONALLQ 结果是 QUNIONALLQ 包含两个重复行 QUNIONALLQUNIONALLQ INTERSECT QUNIONALLQ 结果是 Q 没有包含重复行 QUNIONALLQUNIONALLQ EXCEPTALL QUNIONALLQ 结果是 Q 没有包含重复行 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 4高级SQL语法 2 连接形式通用形式 FROMtablename AS corr name SQL 99 FROM后面还可以是 一般形式 tablename AS corr name colname colname 子查询 subquery AS corr name colname colname 显式连接 table1 INNER LEFT RIGHT FULL OUTER JOINtable2ONcondition显然SQL 99中允许子查询出现在FROM子句中 以上三种形式在产品中并没有完全实现 使用时可以试用或帮助 可以是视图名 ORACLE不能有AS 允许为表的列重新命名 允许是子查询 此时别名必须 内连 左连 右连 外连 连接条件 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 4高级SQL语法 例 检索至少订购了一件价格低于0 5的商品的顾客姓名 selectdistinctcnamefrom ordersojoinproductspono pid p pid joincustomerscono cid c cidwherep price 0 5 注 ORACLE仅提供左连和右连 而且语法与标准SQL也不同 SELECT FROMT1 T2WHERE T1 c1 T2 c2 T1 c1 T2 c2 ANDcondition T2保留所有行 与T1连不上的用NULL 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 5SQL中的集合函数 SQL中称集合函数 setfunction ORACLE称组函数 groupfunction DB2UDB称列函数 columnfunction INFORMIX称聚集函数 aggregatefunction 集合函数语法 SET FUNCTION ALL DISTINCT col COUNT 注 ALL时包括重复行 DISTINCT不包括重复行 WHERE子句比较操作中不能使用集合函数 如discnt max 集合函数忽略了所有空值 集合函数不允许嵌套使用 如AVG selectMAX dollars 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 6SQL中行的分组 SQL报表功能 根据某些列值的共性把一个表所包含的全部行分成若干个子集 然后对每个子集执行集合函数 例 打印每个代理商为顾客c002和c003订购产品及产品总数量 selecta aid aname p pid pname sum qty fromordersx productsp agentsawherex pid p pidandx aid a aidandx cidin c002 c003 groupbya aid a aname p pid p pname GROUPBY对象的列上的空值会被分在同一组里 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 6SQL中行的分组 如果要去掉分组后的某些行 不能用where 只能用HAVING子句 例 求被至少两个顾客订购的产品pid selectpidfromordersgroupbypidhavingcount distinctcid 2 如果没有GROUPBY只有HAVING 则整个结果为一组 基本SQLselect语句的通用形式不允许集合函数的嵌套 但可以有变通的GROUPBY形式 例 求所有代理商的最大销售额的平均值 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 3 6SQL中行的分组 例 求所有代理商的最大销售额的平均值 错 selectavg selectmax dollars fromordersgroupbyaid 对 SQL高级形式 ORACLE中可行 selectavg t x from selectaid max dollars asxfromordersgroupbyaid t 常用作法 creatviewtas selectaid max dollars asxfromordersgroupbyaid selectavg t x fromt 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 4SQL数据更新 SQL中数据更新包括插入数据 Insert 修改数据 Update 和删除数据 Delete 三条语句 1 3 4 1插入数据INSERTINTO VALUES expr1 NULL expr2 NULL Subquery 将新元组插入指定表中 其中新记录属性列1的值为expr1 属性列2的值为expr2 INTO子句中没有出现的属性列 新记录在这些列上将取空值 还可通过子查询批量插入数据 在表定义时说明了NOTNULL的属性列不能取空值 否则会出错 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 4SQL数据更新 如果INTO子句中没有指明任何列名 则新插入的记录必须在每个属性列上均有值 给定值对应于表定义的字段顺序 子查询不需要用括号括起来 insertintomy cselect fromcustomerswherecity 北京 insertintomy c select fromcustomerswherecity 北京 1 3 4 1修改数据UPDATESET WHERE 与customers表结构一样 错误 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 4SQL数据更新 其功能是修改指定表中满足WHERE子句条件的元组 其中SET子句给出expr的值用于取代相应的属性列值 如果省略WHERE子句 则表示要修改表中的所有元组 只有一个表可以作为UPDATE的对象 有些系统中不允许在SET子句中使用限定属性名 updateagentssetagents percent 1 1 agents percentEntrySQL 92中SET子句中不能用子查询 但SQL 99及ORACLE DB2UDB等都支持 用customers表中最新discnt更新my c中discntupdatemy csetdiscnt selectdiscntfromcustomerswherecid my c cid 错误 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 1 3 4SQL数据更新 删除语句的一般格式为 DELETEFROM WHERE 从指定表中删除满足WHERE子句条件的所有元组 如果省略WHERE子句 表示删除表中全部元组 但表的定义仍在字典中 DELETE语句删除的是表中的数据 而不是关于表的定义 删除条件中可以有子查询删除总订货金额小于600的代理商 deletefromagentswhereaidin selectaidfromordersgroupbyaidhavingsum dollars 600 增删改操作只能对一个表操作 因此在执行增删改操作时 要注意数据库中数据的一致性 北京邮电大学软件学院郭文明2003 06 数据库设计与开发 讲义 作业 1 检索佣金百分率大于最小百分率的代理商aid 2 求通过住在北京或上海的代理商订货的顾客cid 使用子查询和不使用子查询两种 3 R S和T具有相同的属性列A1 An 不用高级SQL来实现关系表达式 RUNIONS MINUST 4 求出没有为任何住在北京的顾客订购任何在天津生产的产品的代理商的aid 5 没有一个包含GROUPBY子句的查询会返回重复行 这一命题为真吗 如果为真 解释原因 否则 给出反例 6 说出你所用到DBMS和其中的SQL语句 他们与讲课时的不一样或者不能用