资源描述
主讲：王新颖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(salary10000(company-name=“FirstBankCorporation”(employeeworks)找出所有居住地与工作的公司在同一城市的员工姓名person-name(employeeworkscompany),Copyright:XinyingWang,找出与其经理居住在同一城市同一街道的所有员工的姓名person-name(manager-name=employee2.person-namemanages.person-name=employee.person-nameemployee.street=employee2.streetemployee.city=employee2.city(employee(manages(employee2(employee)6.找出比SmallBankCorporation所有员工收入都高的所有的员工的姓名person-name(works)(works.person-name(works.salaryworks2.salarypany-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现在居住在NewTownemployeeemployee-person-name=“Jones”(employee)employeeemployee(“Jones”,null,“Newtown”)9.为工资不高于100,000的经理提高工资10%，高于100,000的提高工资3%t1person-name,company-name,salary(person-name=manager-name(employeeworks)t2person-name,company-name,salary*1.1(salary100000(t1)t3person-name,company-name,salary*1.03(salary100000(t1)works(works-t1)t2t3,Copyright:XinyingWang,10.删除works关系中SmallBankCorporation的员工的所有元组t1person-name,company-name,salary(company-name=“SmallBankCorporation”(works)works(works-t1)11.找出员工最多的公司r1company-namegcount(person-name)asperson-number(works)r2gmax(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,createunique/distinctclusterindex索引名on表名(列名asc/desc,列名asc/desc),Copyright:XinyingWang,selectA1,A2,Anfromr1,r2,rmwherePA1,A2,An(p(r1r2rm),Copyright:XinyingWang,需注意的问题目标列形式通配符*、算数表达式、聚集函数语法约束all：保留重复元组distinct或unique：去掉重复元组,Copyright:XinyingWang,语法成分比较运算符、=、逻辑运算符and，or，notbetween条件判断表达式的值是否在某范围内,Copyright:XinyingWang,格式old_nameasnew_name为关系和属性重新命名，可出现在select和from子句中注：as可选,Copyright:XinyingWang,命令格式格式列名notlike字符串匹配规则“%”匹配零个或多个字符“_”匹配任意单个字符转义字符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,空值测试表达式isnotnull注意事项除isnotnull之外，空值不满足任何查找条件如果null参与算术运算，则该算术表达式的值为null如果null参与比较运算，则结果可视为false如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null,Copyright:XinyingWang,元组显示顺序orderby列名asc|desc集合操作集合并：union集合交：intersect集合差：except,Copyright:XinyingWang,in子查询表达式notin（子查询）some/all子查询表达式比较运算符some（子查询）表达式的值至少与子查询结果中的一个值相比满足比较运算符表达式比较运算符all（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符,Copyright:XinyingWang,测试集合是否为空表达式notexists（子查询）判断子查询的结果集合中是否有任何元组存在测试集合是否存在重复元组表达式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=FirstBankCorporationanda.employee-name=b.employee-nameandsalary10000找出所有居住地与工作的公司在同一城市的员工姓名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-nameFirstBankCorporation每个人只能在一个公司工作（不允许兼职）selectemployee-namefromworksexceptselectemployee-namefromworkswherecompany-name=FirstBankCorporation,Copyright:XinyingWang,找出比SmallBankCorporation所有员工收入都高的所有的员工的姓名selectemployee_namefromworkswheresalaryall(selectmax(salary)fromworkswherecompany_name=S.B.C.)假设一个公司可以位于几个城市中，找出位于SmallBankCorporation所在的某个城市的所有公司selectcompany-namefromcompanywherecityin(selectcityfromcompanywherecompany_name=S.B.C.),Copyright:XinyingWang,假设一个公司可以位于几个城市中，找出位于SmallBankCorporation所在的各个城市的所有公司selectcompany-namefromcompanyascwherenotexists(selectcityfromcompanywherecompany-name=S.B.Cexceptselectcityfromcompanyasapany_name=pany_name),Copyright:XinyingWang,C,找出工资高于其所在公司员工平均工资的所有员工selectemployee_namefromworksaswwheresalaryall(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.Cgroupbycompany_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=casewhensalary100000thensalary*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,数据模型,数据库结构的基础描述数据、数据联系、数据语义以及一致性约束的概念工具集合分类基于对象的逻辑模型：ER模型基于记录的逻辑模型：关系模型物理模型,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)car(license,model,year,driver-id)accident(report-number,location,date)owns(license,driver-id)participated(license,report-number,driver-id,damage-amount),Copyright:XinyingWang,Copyright:XinyingWang,2为大学设计一个教务管理E-R图包括以下实体：课程：课程编号、名称、学分、课程提纲和选修条件课程提供：课程编号、年度、学期、节数、教室、时间、教室学生：学号、姓名、计划教师：教师代码、姓名、院系、职称,Copyright:XinyingWang,3将上述E-R图转换成表student(student-id,name,program)instructor(instructor-id,name,dept,title)course(courseno,title,syllabus,credits)course-offering(courseno,secno,year,semester,time,room)enrols(student-id,courseno,secno,semester,year,grade)teaches(courseno,secno,semester,year,instructor-id)requires(maincourse,prerequisite),Copyright:XinyingWang,记录学生选修的不同课程的成绩A：将考试作为一个实体，用一个三元关系完成上述数据库B：设计跟上述功能完全相同的E-R图在students和course-offering之间仅有一个二元关系对于不同学生，不同课程，给出成绩,Copyright:XinyingWang,Copyright:XinyingWang,Copyright:XinyingWang,marks,
展开阅读全文