数据库原理习题课.ppt

主讲 王新颖E mail xinying 吉林大学软件学院 1 基本的关系代数运算2 附加的关系代数运算3 扩展的关系代数运算4 数据库修改 Copyright XinyingWang employee person name street city works person name company name salary company company name city manages person name manager name Copyright XinyingWang 找出FirstBankCorporation的所有员工的姓名 person name company name FirstBankCorporation works 找出FirstBankCorporation的所有员工的姓名和居住城市 person name city company name FirstBankCorporation employeeworks 找出FirstBankCorporation的所有年收入在10 000美元一时的员工的姓名和居住的街道 城市 person name street city salary 10000 company name FirstBankCorporation employeeworks 找出所有居住地与工作的公司在同一城市的员工姓名 person name employeeworkscompany Copyright XinyingWang 找出与其经理居住在同一城市同一街道的所有员工的姓名 person name manager name employee2 person name manages person name employee person name employee street employee2 street employee city employee2 city employee manages employee2 employee 6 找出比SmallBankCorporation所有员工收入都高的所有的员工的姓名 person name works works person name works salary works2 salary pany name SmallBankCorporation works works2 works Copyright XinyingWang 7 找出数据库中不在FirstBankCorporation工作的所有员工的姓名每个人只能在一个公司工作 不允许兼职 person name company name FirstBankCorporation works 每个人可以在不只一个公司工作 允许兼职 person name employee person name company name FirstBankCorporation works Copyright XinyingWang 8 修改数据库 使Jones现在居住在NewTownemployee employee person name Jones employee employee employee Jones null Newtown 9 为工资不高于100 000的经理提高工资10 高于100 000的提高工资3 t1 person name company name salary person name manager name employee works t2 person name company name salary 1 1 salary 100000 t1 t3 person name company name salary 1 03 salary 100000 t1 works works t1 t2 t3 Copyright XinyingWang 10 删除works关系中SmallBankCorporation的员工的所有元组t1 person name company name salary company name SmallBankCorporation works works works t1 11 找出员工最多的公司r1 company namegcount person name asperson number works r2 gmax person number r1 company name person number r2 r1 Copyright XinyingWang 1 数据定义2 集合运算3 聚集函数4 空值5 嵌套子查询6 复杂查询7 视图8 数据库修改9 完整性约束 Copyright XinyingWang createtable表名 列名数据类型 default缺省值 notnull 列名数据类型 default缺省值 notnull primarykey 列名 列名 foreignkey 列名 列名 references表名 列名 列名 check 条件 Copyright XinyingWang create unique distinct cluster index索引名on表名 列名 asc desc 列名asc desc Copyright XinyingWang selectA1 A2 Anfromr1 r2 rmwhereP A1 A2 An p r1 r2 rm Copyright XinyingWang 需注意的问题目标列形式通配符 算数表达式 聚集函数语法约束all 保留重复元组distinct或unique 去掉重复元组 Copyright XinyingWang 语法成分比较运算符 逻辑运算符and or notbetween条件判断表达式的值是否在某范围内 Copyright XinyingWang 格式old nameasnew name为关系和属性重新命名 可出现在select和from子句中注 as可选 Copyright XinyingWang 命令格式格式列名 not like 字符串 匹配规则 匹配零个或多个字符 匹配任意单个字符转义字符escape Copyright XinyingWang 分组命令groupby列名 having条件表达式 groupby将表中的元组按指定列上值相等的原则分组 然后在每一分组上使用聚集函数 得到单一值having则对分组进行选择 只将聚集函数作用到满足条件的分组上 Copyright XinyingWang selectSNO avg SCORE fromSCgroupbySNOhavingavg SCORE 60selectSNO avg SCORE fromSCwhereSCORE 60groupbySNO Copyright XinyingWang 聚集函数平均值 avg最小值 min最大值 max总和 sum记数 count Copyright XinyingWang 空值测试表达式is not null注意事项除is not null之外 空值不满足任何查找条件如果null参与算术运算 则该算术表达式的值为null如果null参与比较运算 则结果可视为false如果null参与聚集运算 则除count 之外其它聚集函数都忽略null Copyright XinyingWang 元组显示顺序orderby列名 asc desc 集合操作集合并 union集合交 intersect集合差 except Copyright XinyingWang in子查询表达式 not in 子查询 some all子查询表达式比较运算符 some 子查询 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式比较运算符 all 子查询 表达式的值与子查询结果中的所有的值相比都满足比较运算符 Copyright XinyingWang 测试集合是否为空表达式 not exists 子查询 判断子查询的结果集合中是否有任何元组存在测试集合是否存在重复元组表达式unique 子查询 如果子查询结果中没有重复元组 则返回true Copyright XinyingWang 插入insertinto表名 列名 列名 values 值 值 insertinto表名 列名 列名 子查询 删除deletefrom表名 where条件表达式 修改update表名set列名 表达式 子查询列名 表达式 子查询 where条件表达式 Copyright XinyingWang createview视图名称asselect查询 Copyright XinyingWang employee employee name street city works employee name company name salary company company name city Copyright XinyingWang 找出所有为FirstBankCorporation工作的员工的名字selectemployee namefromworkswherecompany name FirstBankCorporation 找出各个公司员工的平均工资 并按照公司名称排序 逆序 Selectcompany name avg salary FromworksGroupbycompany nameOrderbycompany namedesc Copyright XinyingWang 找出FirstBankCorporation的所有年收入在10 000美元以上的员工的姓名和居住的街道 城市selecta employee name street cityfromworksasa employeeasbwherecompany name FirstBankCorporation anda employee name b employee nameandsalary 10000找出所有居住地与工作的公司在同一城市的员工姓名selecta employee namefromworksa employeeb pany name pany nameanda employee name b employee nameandb city c city Copyright XinyingWang 找出与其经理居住在同一城市同一街道的所有员工的姓名selecta employee namefromemployeeasa managerasb selecta employee name street cityfromemployeeasa managesasbwherea employee name b manager name asmg manager name street city wherea employee name mg employee nameandb manager name mg manager nameanda city b cityanda street b street Copyright XinyingWang 找出数据库中不在FirstBankCorporation工作的所有员工的姓名每个人只能在一个公司工作 不允许兼职 selectemployee namefromworkswherecompany name FirstBankCorporation 每个人只能在一个公司工作 不允许兼职 selectemployee namefromworksexceptselectemployee namefromworkswherecompany name FirstBankCorporation Copyright XinyingWang 找出比SmallBankCorporation所有员工收入都高的所有的员工的姓名selectemployee namefromworkswheresalary all selectmax salary fromworkswherecompany name S B C 假设一个公司可以位于几个城市中 找出位于SmallBankCorporation所在的某个城市的所有公司selectcompany namefromcompanywherecityin selectcityfromcompanywherecompany name S B C Copyright XinyingWang 假设一个公司可以位于几个城市中 找出位于SmallBankCorporation所在的各个城市的所有公司selectcompany namefromcompanyascwherenotexists selectcityfromcompanywherecompany name S B C exceptselectcityfromcompanyasapany name pany name Copyright XinyingWang C 找出工资高于其所在公司员工平均工资的所有员工selectemployee namefromworksaswwheresalary all selectavg salary fromworksasapany name pany name 找出员工最多的公司selectcompany name max count asnfromworksgroupbycompany name Copyright XinyingWang 找出平均工资高于FirstBankCorporation平均工资的所有公司selectcompany name avg salary asavgSalaryfromworksgroupbycompany namehavingavgSalary all selectavg salary fromworkwherecompany name F B C groupbycompany name Copyright XinyingWang 为FirstBankCorporation所有员工增加10 的薪水UpdateworksSetsalary salary 1 1wherecompany name FirstBankCorporation 为数据库中所有的经理都提高工资10 updateworkssetsalary salary 1 1whereemployee namein selectmanager namefrommanages Copyright XinyingWang 为工资不高于100 000的经理提高工资10 高于100 000的提高工资3 updateworkssetsalary casewhensalary 100000thensalary 1 03elsesalary 1 1endwherecompany name F B C 删除works关系中SmallBankCorporation的员工的所有元组deletefromworkswherecompany name S B C Copyright XinyingWang 创建带有 经理名字 和其下属员工的平均工资的视图createviewv m avgasselectmanager name avg salary fromworksasa managesasbwherea employee name b employee namegroupbymanager name Copyright XinyingWang 数据模型 数据库结构的基础描述数据 数据联系 数据语义以及一致性约束的概念工具集合分类基于对象的逻辑模型 E R模型基于记录的逻辑模型 关系模型物理模型 Copyright XinyingWang Copyright XinyingWang 实体 现实世界中 可以区别其他对象的一个 事件 或者 物体 联系 实体之间的关联实体集 同一类实体的集合联系集 同一类联系的集合组成矩形 实体椭圆形 属性菱形 联系 具有相同类型或相同属性的实体集合实体集可以相交实体通过属性来表示 属性是实体集中每个成员具有的描述性性质简单属性和复合属性单值属性和多值属性NULL属性派生属性 Copyright XinyingWang 联系集是多个实体集间的数学关系 是同类联系的集合联系也可以具有描述性属性参与联系的实体集的个数称为联系集的度 二元关系的度为2 三元关系的度为3 Copyright XinyingWang 存在依赖关系 实体x存在依赖于实体y超码 可以唯一表示一个实体的属性集合候选码 最小的超码主码 被选来在实体集中区分不同实体的候选码 Copyright XinyingWang Copyright XinyingWang 主要构件矩形 实体集椭圆形 属性菱形 联系集线段 属性链接实体集或实体集链接联系集双椭圆 多值属性虚椭圆 派生属性双线 一个实体全部参与到联系集当中箭头 多对一 多对多 一对多关系双矩形 弱实体集 没有主码 关系表 二维表 表示数据和数据之间的联系每个表有多个列 列名唯一一行称为一条记录 元组 一列称为一个属性 域 超码 候选码 主码的区别 Copyright XinyingWang 1为车辆保险公司设计一个E R图该公司有很多客户每个客户有一辆或多辆车每辆车可能发生0次或多次交通事故 Copyright XinyingWang Copyright XinyingWang person driver id name address car license model year accident report number location date owns license driver id participated license report number driver id damage amount Copyright XinyingWang person driver id name address