第四章 中级 SQL

第四章 中级 SQL4.1 连接表达式4.1.1 连接操作概述连接操作接收两个关系并返回另一个关系作为结果。一个连接操作是笛卡尔积要求两个关系中的元组满足某些匹配条件。连接操作包含两个关键要素要素含义连接条件定义两个关系中哪些元组匹配以及哪些属性出现在连接结果中连接类型定义如何处理那些在另一个关系中没有匹配元组的元组连接操作通常作为from 子句中的子查询表达式使用。4.1.2 自然连接自然连接在所有公共属性上匹配具有相同值的元组并且每个公共列只保留一个副本。查询每位学生所选课程的名称-- 常规写法SELECTname,course_idFROMstudent,takesWHEREstudent.IDtakes.ID;-- 自然连接写法SELECTname,course_idFROMstudentNATURALJOINtakes;多个关系的自然连接SELECTA1,A2,...AnFROMr1NATURALJOINr2NATURALJOIN...NATURALJOINrnWHEREP;自然连接的隐患⚠️ 注意两个关系中同名但无关的属性会被错误地等值连接。错误示例列出学生姓名及其所选课程名称-- 错误student 和 course 都有 dept_name 属性会被意外连接SELECTname,titleFROMstudentNATURALJOINtakesNATURALJOINcourse;正确写法SELECTname,titleFROMstudentNATURALJOINtakes,courseWHEREtakes.course_idcourse.course_id;错误版本会漏掉那些选了非本系课程的学生。4.1.3 使用 USING 子句的连接为避免自然连接中属性错误匹配的风险使用USING子句精确指定哪些列需要等值连接SELECTname,titleFROM(studentNATURALJOINtakes)JOINcourseUSING(course_id);4.1.4 使用 ON 条件的连接ON子句允许在连接的关系上指定通用谓词写法类似WHERE子句区别是使用关键字ONSELECT*FROMstudentJOINtakesONstudent.IDtakes.ID;等价于SELECT*FROMstudent,takesWHEREstudent.IDtakes.ID;4.1.5 外连接外连接是连接操作的扩展避免信息丢失。它在计算连接后将另一个关系中不匹配的元组添加到连接结果中缺失部分以null填充。三种外连接形式形式含义关系代数符号左外连接保留左侧关系中所有元组⟕右外连接保留右侧关系中所有元组⟖全外连接保留两侧关系中所有元组⟗示例-- 左外连接保留所有 course 信息courseNATURALLEFTOUTERJOINprereq-- 右外连接保留所有 prereq 信息courseNATURALRIGHTOUTERJOINprereq-- 全外连接保留两侧全部信息courseNATURALFULLOUTERJOINprereq连接类型与条件的组合示例-- 右外连接 USINGcourseFULLOUTERJOINprereqUSING(course_id);-- 内连接 ONcourseINNERJOINprereqONcourse.course_idprereq.course_id;-- 左外连接 ONcourseLEFTOUTERJOINprereqONcourse.course_idprereq.course_id;对比自然连接ON 条件连接匹配方式自动匹配所有同名列按指定谓词匹配公共列处理只保留一份两份均保留4.2 视图4.2.1 为什么需要视图不是所有用户都需要看到数据库的完整逻辑模型。例如某人需要知道教师姓名和所在院系但不需要看到薪水。视图提供了一种机制向特定用户隐藏某些数据。任何非概念模型中的关系但作为虚拟关系对用户可见的称为视图 (View)。4.2.2 视图定义视图使用CREATE VIEW语句定义CREATEVIEWvAS查询表达式;v是视图名称查询表达式是任何合法的 SQL 表达式视图定义不是通过执行查询来创建新关系——它只是保存了表达式当使用视图时表达式会被替换到查询中示例-- 创建不含薪水的教师视图CREATEVIEWfacultyASSELECTID,name,dept_nameFROMinstructor;-- 使用视图查询SELECTnameFROMfacultyWHEREdept_nameBiology;-- 创建院系工资总额视图带列名重命名CREATEVIEWdepartments_total_salary(dept_name,total_salary)ASSELECTdept_name,SUM(salary)FROMinstructorGROUPBYdept_name;4.2.3 基于其他视图定义视图视图可以嵌套定义依赖关系含义直接依赖v1 的定义中直接使用 v2依赖直接依赖或通过依赖链传递递归视图视图依赖于自身示例-- 第一层视图2017年秋季物理课程CREATEVIEWphysics_fall_2017ASSELECTcourse.course_id,sec_id,building,room_numberFROMcourse,sectionWHEREcourse.course_idsection.course_idANDcourse.dept_namePhysicsANDsection.semesterFallANDsection.year2017;-- 第二层视图基于上一个视图CREATEVIEWphysics_fall_2017_watsonASSELECTcourse_id,room_numberFROMphysics_fall_2017WHEREbuildingWatson;4.2.4 视图展开视图展开定义了基于其他视图的视图的含义重复以下替换步骤找到视图中的任何视图关系viv_ivi​将其替换为定义viv_ivi​的表达式直到视图中不再存在视图关系。只要视图定义不是递归的此循环就会终止展开后的查询可直接执行4.2.5 物化视图某些数据库系统允许物理存储视图关系物理副本在视图定义时创建 → 称为物化视图若底层关系发生变化物化视图结果会过时需要在底层关系更新时维护视图4.2.6 视图的更新对视图执行插入操作需要转换为对底层关系的插入-- 向 faculty 视图插入INSERTINTOfacultyVALUES(30765,Green,Music);问题instructor表缺少salary值两种处理方式拒绝插入插入元组缺失值填null(30765, Green, Music, null)无法唯一转换的更新CREATEVIEWinstructor_infoASSELECTID,name,buildingFROMinstructor,departmentWHEREinstructor.dept_namedepartment.dept_name;INSERTINTOinstructor_infoVALUES(69987,White,Taylor);问题若 Taylor 楼有多个院系 → 插入到哪个若 Taylor 楼没有院系 → 怎么办视图更新的限制大多数 SQL 实现只允许在简单视图上更新FROM子句只有一个数据库关系SELECT子句只包含属性名无表达式、聚合或DISTINCTSELECT未列出的属性可被设为null查询不含GROUP BY或HAVING子句4.3 事务事务由一系列查询和/或更新语句组成是工作的单元。SQL 标准规定事务在 SQL 语句执行时隐式开始必须以以下语句之一结束语句效果COMMIT WORK事务的更新在数据库中永久生效ROLLBACK WORK事务中所有 SQL 更新的影响被撤销事务的关键特性原子性 (Atomicity)事务要么完全执行要么完全回滚好像从未发生过一样。隔离性 (Isolation)事务之间互不干扰。4.4 完整性约束完整性约束确保对数据库的授权更改不会导致数据一致性的丢失防止对数据库的意外损坏。举例支票账户余额必须大于 $10,000.00银行雇员时薪至少 $4.00客户必须有非空电话号码4.4.1 单关系上的约束约束类型说明NOT NULL属性不允许空值PRIMARY KEY主键约束UNIQUE候选键约束允许为 null区别于主键CHECK §关系中的每个元组必须满足谓词 P示例-- NOT NULLnameVARCHAR(20)NOTNULL,budgetNUMERIC(12,2)NOTNULL-- UNIQUEUNIQUE(A1,A2,...,Am)-- CHECKCREATETABLEsection(course_idVARCHAR(8),sec_idVARCHAR(8),semesterVARCHAR(6),yearNUMERIC(4,0),buildingVARCHAR(15),room_numberVARCHAR(7),time_slot_idVARCHAR(4),PRIMARYKEY(course_id,sec_id,semester,year),CHECK(semesterIN(Fall,Winter,Spring,Summer)));4.4.2 参照完整性确保一个关系中出现的某组属性值也出现在另一个关系的指定属性集中。设 A 为属性集R 和 S 为包含 A 的关系且 A 是 S 的主键。若 R 中出现的任何 A 值也出现在 S 中则 A 称为 R 的外键 (Foreign Key)。例若instructor中出现院系名 “Biology”则department中必须存在对应的 “Biology” 元组。FOREIGNKEY(dept_name)REFERENCESdepartment默认情况下外键引用被引用表的主键属性。也可以显式指定被引用关系的属性列表FOREIGNKEY(dept_name)REFERENCESdepartment(dept_name)4.4.3 级联操作当参照完整性约束被违反时默认行为是拒绝导致违反的操作。在删除或更新时可设置级联操作CREATETABLEcourse(...dept_nameVARCHAR(20),FOREIGNKEY(dept_name)REFERENCESdepartmentONDELETECASCADEONUPDATECASCADE,...);级联选项效果CASCADE级联执行相同操作SET NULL将外键设为 nullSET DEFAULT将外键设为默认值4.4.4 事务中的约束违反CREATETABLEperson(IDCHAR(10),nameCHAR(40),motherCHAR(10),fatherCHAR(10),PRIMARYKEY(ID),FOREIGNKEYfatherREFERENCESperson,FOREIGNKEYmotherREFERENCESperson);如何在插入时不违反约束先插入父亲和母亲再插入该人先设 father/mother 为 null全部插入后再更新需允许 null延迟约束检查4.4.5 复杂 CHECK 条件CHECK子句中的谓词可以包含子查询CHECK(time_slot_idIN(SELECTtime_slot_idFROMtime_slot))⚠️ 此条件不仅在 section 中插入或修改元组时需要检查在time_slot关系变化时也需检查。4.4.6 断言断言是表达我们希望数据库始终满足的条件的谓词。适用场景每个学生的tot_cred必须等于其成功完成课程的学分之和教师不能在同一时间段的同一学期在两个不同教室上课CREATEASSERTION断言名CHECK(谓词);示例CREATEASSERTION credits_earned_constraintCHECK(NOTEXISTS(SELECTIDFROMstudentWHEREtot_cred(SELECTSUM(credits)FROMtakesNATURALJOINcourseWHEREstudent.IDtakes.IDANDgradeISNOTNULLANDgradeF)));4.5 SQL 数据类型与模式4.5.1 内置日期/时间类型类型说明示例DATE日期含 4 位年、月、日DATE 2005-7-27TIME时间时、分、秒TIME 09:00:30.75TIMESTAMP日期 时间TIMESTAMP 2005-7-27 09:00:30.75INTERVAL时间段INTERVAL 1 DAY日期/时间/时间戳相减 → 得到 intervalinterval 可加到日期/时间/时间戳上4.5.2 大对象类型用于存储大型对象照片、视频、CAD 文件等类型含义BLOB二进制大对象——无解释的二进制数据集合CLOB字符大对象——大量字符数据集合查询返回大对象时返回的是指针而非对象本身。4.5.3 用户自定义类型CREATETYPEDollarsASNUMERIC(12,2)FINAL;CREATETABLEdepartment(dept_nameVARCHAR(20),buildingVARCHAR(15),budget Dollars);4.5.4 域域与类型类似但域可以有约束CREATEDOMAIN person_nameCHAR(20)NOTNULL;CREATEDOMAIN degree_levelVARCHAR(10)CONSTRAINTdegree_level_testCHECK(VALUEIN(Bachelors,Masters,Doctorate));对比类型 (Type)域 (Domain)约束支持不直接支持可指定 NOT NULL、CHECK 等约束语义纯数据类型别名带有语义约束的值范围4.6 索引4.6.1 为什么需要索引许多查询只引用表中很小一部分记录。逐条扫描整个表定位某个特定值的记录效率极低。索引是一种数据结构允许数据库系统高效地找到关系中具有指定属性值的元组而无需扫描全部元组。4.6.2 索引的创建与删除-- 创建索引CREATEINDEX索引名ON关系名(属性);-- 删除索引DROPINDEX索引名;示例CREATETABLEstudent(IDVARCHAR(5),nameVARCHAR(20)NOTNULL,dept_nameVARCHAR(20),tot_credNUMERIC(3,0)DEFAULT0,PRIMARYKEY(ID));CREATEINDEXstudentID_indexONstudent(ID);-- 此查询可利用索引快速定位记录SELECT*FROMstudentWHEREID12345;4.6.3 索引的基本概念概念说明搜索键用于在文件中查找记录的属性或属性集索引项由搜索键 指针组成的记录索引文件通常比原始文件小得多4.6.4 索引的两种基本类型类型原理有序索引搜索键按排序顺序存储哈希索引搜索键通过哈希函数均匀分布到各个桶中4.6.5 其他索引分类维度B 树索引最常见的数据库索引结构支持范围查询内存索引 vs 磁盘索引取决于是否支持持久化和快速恢复不同数据类型需要针对不同数据类型和条件的索引4.7 授权4.7.1 授权类型我们可以为用户在数据库的不同部分授予多种形式的授权数据操作权限权限含义Read (读取)允许读取数据但不能修改Insert (插入)允许插入新数据但不能修改已有数据Update (更新)允许修改数据但不能删除Delete (删除)允许删除数据模式修改权限权限含义Index允许创建和删除索引Resources允许创建新关系Alteration允许添加或删除关系中的属性Drop允许删除关系每种授权形式称为一个权限 (Privilege)。可为用户授予全部、无、或部分权限的组合。4.7.2 授予权限GRANT语句用于授予授权GRANT权限列表ON关系或视图TO用户列表;用户列表可以是一个用户 IDPUBLIC——所有有效用户一个角色示例GRANTSELECTONdepartmentTOAmit,Satoshi;⚠️ 在视图上授予权限不代表在底层关系上也授予了权限。授权者必须已经拥有被授权项上的权限或者是数据库管理员。4.7.3 各权限详解-- SELECT允许读取关系GRANTSELECTONinstructorTOU1,U2,U3;-- INSERT允许插入元组-- UPDATE允许使用 UPDATE 语句-- DELETE允许删除元组-- ALL PRIVILEGES所有权限的简写4.7.4 撤销权限REVOKE语句用于撤销授权REVOKE权限列表ON关系或视图FROM用户列表;示例REVOKESELECTONstudentFROMU1,U2,U3;撤销规则权限列表可以是ALL撤销被撤销者持有的所有权限若被撤销者列表包含PUBLIC所有用户失去该权限显式授予的除外若同一权限由不同授权者授予同一用户两次撤销其一后用户仍保留该权限所有依赖于被撤销权限的权限也一并被撤销4.7.5 角色角色是区分不同用户访问/更新数据库权限的方式-- 创建角色CREATEROLE instructor;-- 将用户分配给角色GRANTinstructorTOAmit;-- 为角色授予权限GRANTSELECTONtakesTOinstructor;角色可以传递授予形成角色链CREATEROLE teaching_assistant;GRANTteaching_assistantTOinstructor;-- instructor 继承 teaching_assistant 的所有权限CREATEROLE dean;GRANTinstructorTOdean;GRANTdeanTOSatoshi;-- Satoshi 间接获得全部权限4.7.6 视图上的授权CREATEVIEWgeo_instructorAS(SELECT*FROMinstructorWHEREdept_nameGeology);GRANTSELECTONgeo_instructorTOgeo_staff;需要考虑若geo_staff没有instructor的权限或视图创建者没有instructor的某些权限会发生什么4.7.7 其他授权特性REFERENCES 权限——创建外键约束需要GRANTREFERENCES(dept_name)ONdepartmentTOMariano;权限的传递 (WITH GRANT OPTION)GRANTSELECTONdepartmentTOAmitWITHGRANTOPTION;级联 vs 限制撤销REVOKESELECTONdepartmentFROMAmit,SatoshiCASCADE;-- 级联撤销REVOKESELECTONdepartmentFROMAmit,SatoshiRESTRICT;-- 限制撤销若有关联则拒绝本章重点掌握各种连接类型自然连接、USING、ON、外连接、视图的创建与更新限制、事务的原子性和隔离性、完整性约束NOT NULL / UNIQUE / CHECK / 参照完整性 / 断言、索引的作用与类型、以及 SQL 的授权机制GRANT / REVOKE / 角色 / 传递授权。这些都是实际数据库开发中不可或缺的中级 SQL 技能。