《数据库原理习题课》PPT课件.ppt

主讲：王新颖 E-mail：,吉林大学软件学院,1、基本的关系代数运算 2、附加的关系代数运算 3、扩展的关系代数运算 4、数据库修改,Copyright: Xinying Wang,employee(person-name,street,city) works(person-name,company-name,salary) company(company-name,city) manages(person-name,manager-name),Copyright: Xinying Wang,找出First Bank Corporation的所有员工的姓名 person-name (company-name=“First Bank Corporation” (works) 找出First Bank Corporation的所有员工的姓名和居住城市 person-name, city (company-name=“First Bank Corporation” (employee works) 找出First Bank Corporation的所有年收入在10,000美元一时的员工的姓名和居住的街道、城市 person-name, street,city (salary10000 (company-name=“First Bank Corporation” (employee works) 找出所有居住地与工作的公司在同一城市的员工姓名 person-name (employee works company),Copyright: Xinying Wang,找出与其经理居住在同一城市同一街道的所有员工的姓名 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. 找出比Small Bank Corporation所有员工收入都高的所有的员工的姓名 person-name (works) (works.person-name (works.salary works2.salary pany-name=“Small Bank Corporation” (works (works2(works),Copyright: Xinying Wang,7.找出数据库中不在First Bank Corporation工作的所有员工的姓名 每个人只能在一个公司工作（不允许兼职） person-name (company-name“First Bank Corporation” (works) 每个人可以在不只一个公司工作（允许兼职） person-name (employee) person-name (company-name = “First Bank Corporation”(works),Copyright: Xinying Wang,8. 修改数据库，使Jones现在居住在NewTown employee 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(employeeworks) 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: Xinying Wang,10. 删除works关系中Small Bank Corporation的员工的所有元组 t1 person-name, company-name, salary ( company-name=“Small Bank Corporation” (works) works (works - t1) 11.找出员工最多的公司 r1 company-namegcount (person-name) as person-number (works) r2 gmax(person-number) (r1) company-name ( person-number=r2 (r1),Copyright: Xinying Wang,1、数据定义 2、集合运算 3、聚集函数 4、空值 5、嵌套子查询 6、复杂查询 7、视图 8、数据库修改 9、完整性约束,Copyright: Xinying Wang,create table 表名（ 列名 数据类型 default 缺省值 not null ，列名 数据类型 default 缺省值 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件）,Copyright: Xinying Wang,create unique/distinct cluster index 索引名 on 表名 (列名 asc/desc , 列名asc/desc),Copyright: Xinying Wang,select A1 , A2 , , An from r1 , r2 , , rm where P A1 , A2 , , An(p(r1 r2 rm),Copyright: Xinying Wang,需注意的问题 目标列形式 通配符*、算数表达式、聚集函数 语法约束 all：保留重复元组 distinct或unique：去掉重复元组,Copyright: Xinying Wang,语法成分 比较运算符 、 、=、 逻辑运算符 and，or，not between条件 判断表达式的值是否在某范围内,Copyright: Xinying Wang,格式 old_name as new_name 为关系和属性重新命名，可出现在select和from子句中 注：as可选,Copyright: Xinying Wang,命令格式 格式 列名 not like 字符串 匹配规则 “%” 匹配零个或多个字符 “_”匹配任意单个字符 转义字符 escape,Copyright: Xinying Wang,分组命令 group by 列名 having 条件表达式 group by将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值 having则对分组进行选择，只将聚集函数作用到满足条件的分组上,Copyright: Xinying Wang,select SNO，avg(SCORE) from SC group by SNO having avg(SCORE) = 60 select SNO，avg(SCORE) from SC where SCORE =60 group by SNO,Copyright: Xinying Wang,聚集函数 平均值：avg 最小值：min 最大值：max 总和：sum 记数：count,Copyright: Xinying Wang,空值测试 表达式 is not null 注意事项 除is not null之外，空值不满足任何查找条件 如果null参与算术运算，则该算术表达式的值为null 如果null参与比较运算，则结果可视为false如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null,Copyright: Xinying Wang,元组显示顺序 order by 列名 asc | desc 集合操作 集合并：union 集合交：intersect 集合差：except,Copyright: Xinying Wang,in 子查询 表达式 not in （子查询） some/all子查询 表达式 比较运算符 some （子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式 比较运算符 all （子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符,Copyright: Xinying Wang,测试集合是否为空 表达式 not exists （子查询） 判断子查询的结果集合中是否有任何元组存在 测试集合是否存在重复元组 表达式 unique (子查询） 如果子查询结果中没有重复元组，则返回true,Copyright: Xinying Wang,插入 insert into 表名 (列名,列名) values (值 ,值) insert into 表名 (列名,列名) (子查询) 删除 delete from 表名 where 条件表达式 修改 update 表名 set 列名 = 表达式 | 子查询 列名 = ，表达式 | 子查询 where 条件表达式,Copyright: Xinying Wang,create view 视图名称 as select 查询,Copyright: Xinying Wang,employee(employee-name, street, city) works(employee-name, company-name, salary) company(company-name, city),Copyright: Xinying Wang,找出所有为First Bank Corporation工作的员工的名字 select employee-name from works where company-name=First Bank Corporation 找出各个公司员工的平均工资，并按照公司名称排序（逆序） Select company-name, avg (salary) From works Group by company-name Order by company-name desc,Copyright: Xinying Wang,找出First Bank Corporation的所有年收入在10,000美元以上的员工的姓名和居住的街道、城市 select a.employee-name, street, city from works as a, employee as b where company-name=First Bank Corporation and a.employee-name=b.employee-name and salary10000 找出所有居住地与工作的公司在同一城市的员工姓名 select a.employee-name from works a, employee b, company c where pany_name= pany_name and a.employee-name=b.employee-name and b.city=c.city,Copyright: Xinying Wang,找出与其经理居住在同一城市同一街道的所有员工的姓名 select a.employee_name from employee as a, manager as b , (select a.employee_name , street , city from employee as a, manages as b where a.employee_name=b.manager_name) as mg(manager_name,street,city) where a.employee_name=mg.employee_name and b.manager_name=mg.manager_name and a.city=b.city and a.street=b.street,Copyright: Xinying Wang,找出数据库中不在First Bank Corporation工作的所有员工的姓名 每个人只能在一个公司工作（不允许兼职） select employee-name from works where company-nameFirst Bank Corporation 每个人只能在一个公司工作（不允许兼职） select employee-name from works except select employee-name from works where company-name=First Bank Corporation,Copyright: Xinying Wang,找出比Small Bank Corporation所有员工收入都高的所有的员工的姓名 select employee_name from works where salary all (select max(salary) from works where company_name=S.B.C.) 假设一个公司可以位于几个城市中，找出位于Small Bank Corporation所在的某个城市的所有公司 select company-name from company where city in (select city from company where company_name=S.B.C.),Copyright: Xinying Wang,假设一个公司可以位于几个城市中，找出位于Small Bank Corporation所在的各个城市的所有公司 select company-name from company as c where not exists ( select city from company where company-name=S. B. C except select city from company as a where pany_name=pany_name),Copyright: Xinying Wang,C,找出工资高于其所在公司员工平均工资的所有员工 select employee_name from works as w where salary all (select avg(salary) from works as a where pany_name= pany_name) 找出员工最多的公司 select company_name，max(count(*) as n from works group by company_name,Copyright: Xinying Wang,找出平均工资高于First Bank Corporation平均工资的所有公司 select company_name，avg(salary) as avgSalary from works group by company_name having avgSalary =all (select avg(salary) from work where company_name =F.B.C group by company_name),Copyright: Xinying Wang,为First Bank Corporation所有员工增加10%的薪水 Update works Set salary=salary*1.1 where company-name= First Bank Corporation 为数据库中所有的经理都提高工资10% update works set salary=salary*1.1 where employee-name in (select manager-name from manages),Copyright: Xinying Wang,为工资不高于100,000的经理提高工资10%，高于100,000的提高工资3% update works set salary=case when salary100000 then salary*1.03 else salary*1.1 end where company-name=F. B. C. 删除works关系中Small Bank Corporation的员工的所有元组 delete from works where company-name=S. B. C.,Copyright: Xinying Wang,创建带有“经理名字”和其下属员工的平均工资的视图 create view v_m_avg as select manager_name , avg(salary) from works as a, manages as b where a.employee_name=b.employee_name group by manager_name,Copyright: Xinying Wang,数据模型,数据库结构的基础 描述数据、数据联系、数据语义以及一致性约束的概念工具集合 分类 基于对象的逻辑模型：ER模型 基于记录的逻辑模型：关系模型 物理模型,Copyright: Xinying Wang,Copyright: Xinying Wang,实体：现实世界中，可以区别其他对象的一个“事件”或者“物体” 联系：实体之间的关联 实体集：同一类实体的集合 联系集：同一类联系的集合 组成 矩形：实体 椭圆形：属性 菱形：联系,具有相同类型或相同属性的实体集合 实体集可以相交 实体通过属性来表示，属性是实体集中每个成员具有的描述性性质 简单属性和复合属性 单值属性和多值属性 NULL属性 派生属性,Copyright: Xinying Wang,联系集是多个实体集间的数学关系，是同类联系的集合 联系也可以具有描述性属性 参与联系的实体集的个数称为联系集的度。二元关系的度为2，三元关系的度为3,Copyright: Xinying Wang,存在依赖关系：实体x存在依赖于实体y 超码：可以唯一表示一个实体的属性集合 候选码：最小的超码 主码：被选来在实体集中区分不同实体的候选码,Copyright: Xinying Wang,Copyright: Xinying Wang,主要构件 矩形：实体集 椭圆形：属性 菱形：联系集 线段：属性链接实体集或实体集链接联系集 双椭圆：多值属性 虚椭圆：派生属性 双线：一个实体全部参与到联系集当中 箭头：多对一，多对多，一对多关系 双矩形：弱实体集（没有主码）,关系表：（二维表） 表示数据和数据之间的联系 每个表有多个列，列名唯一 一行称为一条记录（元组）；一列称为一个属性（域） 超码、候选码、主码的区别,Copyright: Xinying Wang,1 为车辆保险公司设计一个E-R图 该公司有很多客户 每个客户有一辆或多辆车 每辆车可能发生0次或多次交通事故,Copyright: Xinying Wang,Copyright: Xinying Wang,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: Xinying Wang,person (driver-i