《数据库操作基础第11讲》课件

数据库操作基础第讲课程11导入欢迎各位同学参加《数据库操作基础第讲》本课程旨在帮助大家掌11握数据库操作的核心知识与技能，为今后的学习和实践奠定坚实基础在今天的课程中，我们将全面介绍数据库操作的各个方面，包括语SQL言的基本语法、数据定义与操作、查询技巧、事务管理以及索引和视图等高级特性通过系统学习，希望大家能够熟练运用这些知识解决实际问题本讲的学习目标是掌握语言的基本结构和用法，能够独立完成数SQL据库的基本操作与管理，并理解数据库操作的最佳实践让我们一起开始这段数据库学习之旅！数据库基础回顾数据库核心概念常见数据库管理系统数据库是按照数据结构来组织、存储和管理数据的仓库关系型数据库包括（开源的关系型数据库，被广MySQL它是一个长期存储在计算机内的、有组织的、可共享的、泛应用于中小型网站和应用）、（企业级数据库，Oracle统一管理的大量数据的集合具有高可靠性和可扩展性）、（微软开发的关SQL Server系型数据库）和（功能强大的开源对象关系型PostgreSQL数据库管理系统（）是用于创建和管理数据库的软DBMS数据库）件系统，提供数据的存储、检索、更新和管理等功能数据库系统由数据库、数据库管理系统、数据库应用程序和非关系型数据库有（文档型）、（键值MongoDB Redis数据库管理员组成型）、（图形型）和（列式存储）等，适Neo4j Cassandra用于不同的应用场景数据库操作的基本流程执行语句SQL编写语句SQL将编写好的语句提交给数据库管理系统执SQL连接数据库（结构化查询语言）是与数据库通信的标准行执行过程中，数据库会解析语句，生成SQL SQL连接数据库是进行任何数据库操作的第一步连语言根据操作目的，编写相应的语句，如执行计划，然后执行操作并返回结果对于查询SQL接时需要提供主机地址、端口号、用户名和密码创建表的语句、插入数据的操作，会返回符合条件的数据集；对于更新操CREATE TABLEINSERT等认证信息，以及要操作的数据库名称连接可语句或查询数据的语句编写时需要作，会返回影响的行数或执行状态SELECT SQL以通过命令行工具（如、）、图遵循特定数据库的语法规则和最佳实践MySQL Clientpsql形化界面工具（如、MySQL Workbench）或程序代码中的数据库连接实现pgAdmin API语言概述SQL的起源（年代）的发展（年代至今）SQL1970SQL1990最初由在世纪年代开发，当时称为（随后的几十年里，标准经历了多次修订和扩展，包括、、SQL IBM2070SEQUEL StructuredEnglish SQL SQL-89SQL-92）它最初是作为的关系数据库管理系统的查询语、、、直至等版本每个版本都增Query LanguageIBM SystemR SQL:1999SQL:2003SQL:2008SQL:2011SQL:2016言开发的，目的是为了操作和检索存储在数据库中的数据加了新的功能和改进，使更加强大和灵活IBM SQL123标准化（年代）SQL1980年，美国国家标准协会（）发布了第一个标准，称为1986ANSI SQLSQL-861987年，国际标准化组织（）也采纳了这一标准这标志着成为了关系数据ISO SQL库领域的国际标准语言语句分类SQL数据定义语言（）DDL数据操作语言（）DML用于定义数据库结构的语句，包括用于操作数据库中数据的语句，主创建、修改和删除数据库对象如要包括（插入数据）、INSERT表、索引、视图等主要命令有（更新数据）和（删UPDATE DELETE、、、CREATE ALTERDROP TRUNCATE除数据）这类语句改变的是表中等这类语句改变的是数据的结的数据内容，而不改变表的结构构，而非数据本身数据控制语言（）数据查询语言（）DCL DQL用于控制数据库访问权限的语句，用于从数据库中检索数据的语句，主要包括（授予权限）和主要是语句可以与GRANT SELECTDQL（撤销权限）语句用、、、REVOKE DCLWHERE GROUP BY HAVING于管理数据库的安全性，控制用户等子句结合使用，实现复ORDER BY对数据库对象的访问权限杂的数据查询和分析功能数据定义语言（）详解DDL（创建）（修改）CREATE ALTER用于创建数据库对象，如数据用于修改已存在的数据库对象库、表、索引、视图等例的结构例如，可以使用如，创建新添加、修改或删CREATE DATABASEALTER TABLE的数据库，创建除表中的列，添加或删除各种CREATE TABLE新的表，创建索约束条件，或更改列的数据类CREATE INDEX引，创建视图型命令让数据库结构CREATE VIEWALTER创建语句需要指定对象的名称能够随着应用需求的变化而灵和相关属性活调整（删除）DROP用于删除数据库对象例如，删除表及其所有数据，DROP TABLEDROP删除整个数据库，删除索引，删除视DATABASE DROP INDEX DROP VIEW图删除操作通常是不可逆的，执行前应谨慎考虑创建表（）CREATE TABLE语法结构常用数据类型说明CREATE TABLE整数类型、、等，用于存储整数•INT SMALLINTBIGINTCREATE TABLE表名浮点类型、、等，用于存储小数•FLOAT DOUBLEDECIMAL列名1数据类型[约束条件],字符串类型、、等，用于存储文本列名2数据类型[约束条件],•CHAR VARCHARTEXT...日期时间类型、、、等•DATE TIMEDATETIME TIMESTAMP列名n数据类型[约束条件],布尔类型或，存储真假值•BOOLEAN BOOL/[表级约束定义]二进制类型、等，存储二进制数据•BLOB BINARY;在创建表时，需要指定表名、列名、每列的数据类型以及可选的约束条件约束条件可以是、、NOT NULL UNIQUE PRIMARY、、等，用于确保数据的完整性和一致KEY FOREIGN KEY CHECK性表设计注意事项主键约束（）唯一约束（）PRIMARY KEYUNIQUE主键用于唯一标识表中的每一行数唯一约束确保列中的所有值都是唯据，每个表只能有一个主键主键一的，但允许有值（除非同NULL列不允许有值，且值必须唯时指定了）一个表可NULL NOT NULL一主键可以是单列，也可以是多以有多个唯一约束唯一约束常用列组合（复合主键）于如电子邮件、用户名等需要唯一性但不作为主键的列示例或id INTPRIMARY KEY示例PRIMARY KEY id emailVARCHAR100UNIQUE外键约束（）FOREIGN KEY外键建立表与表之间的关系，确保引用完整性外键列的值必须是另一个表（被引用表）主键列中已存在的值，或者为外键约束防止无效数据的NULL插入，并可以设置级联操作示例FOREIGN KEYdept_id REFERENCESdepartmentsid修改表结构（）ALTER TABLE添加列使用命令可以在现有表中添加新列可以指定列的数据类型、约ALTER TABLEADD COLUMN束条件和默认值示例ALTER TABLEstudents ADDCOLUMN emailVARCHAR100;删除列使用命令可以从表中删除不需要的列及其数据这是一个不可ALTER TABLEDROP COLUMN逆操作，删除前应确保该列及其数据不再需要示例ALTER TABLEstudents DROPCOLUMN email;修改列使用命令可以更改列的数据类型、长度或其他属性修改列ALTER TABLEMODIFY COLUMN的数据类型可能导致数据截断或转换问题示例ALTER TABLEstudents MODIFYCOLUMN nameVARCHAR50;增加删除约束/可以使用添加约束（如主键、外键、唯一约束等），或使用ALTER TABLEADD CONSTRAINT删除约束ALTER TABLEDROP CONSTRAINT示例ALTER TABLEstudents ADDCONSTRAINT pk_student PRIMARY KEYid;删除表与数据库（）DROP及其影响DROP TABLEDROP DATABASE命令用于从数据库中删除表，包括表的结构、命令用于删除整个数据库，包括其中的所DROP TABLEDROP DATABASE索引、触发器等，以及表中存储的所有数据这是一个不有表、视图、存储过程、函数等对象以及所有数据这是可逆的操作，一旦执行，表中的数据将永久丢失（除非有一个极其危险的操作，会导致数据库中所有信息的永久丢备份）失语法示例表名语法示例数据库名DROP TABLE;DROP DATABASE;在删除有外键关系的表时，需要先删除或禁用引用该表的在生产环境中执行命令前，应确保已经进DROP DATABASE外键约束，或者使用选项级联删除相关对象行了完整备份，并且确认不再需要该数据库中的任何数据CASCADE和结构许多系统会限制的权限，只允许DROP DATABASE数据库管理员执行此操作数据操作语言（）简介DML（插入）INSERT语句用于向表中插入新的数据行可以一次插入单行或多行数INSERT据，也可以从其他表或查询结果中插入数据操作添加的是完INSERT整的新记录，而不是修改现有记录（更新）UPDATE语句用于修改表中已存在的数据可以更新一列或多列的UPDATE值，可以使用子句指定要更新的行如果不使用子句，WHERE WHERE则会更新表中的所有行，这可能导致意外的大规模数据变更（删除）DELETE语句用于从表中删除现有的数据行可以使用子句指定DELETE WHERE要删除的行如果不使用子句，则会删除表中的所有数据行WHERE（但保留表结构），这是一个高风险操作插入数据（）INSERT插入单行数据使用语法插入单行数据列名和值INSERT INTOtable_name column1,column2,...VALUES value1,value2,...的顺序必须对应，数据类型也要匹配插入多行数据使用INSERT INTOtable_name column1,column2,...VALUES row1_value1,语法一次插入多行这比多次执行单行row1_value2,...,row2_value1,row2_value2,...插入效率更高默认值与主键自增定义表时可以为列设置默认值（关键字）主键列常DEFAULT设置为（）或（AUTO_INCREMENT MySQLIDENTITY SQL），自动生成唯一值，插入时无需指定Server修改数据（）UPDATE语句语法条件限制UPDATE WHERE基本语法为子句用于筛选需要更新的UPDATE table_name WHERE行没有子句时，会SET column1=value1,column2=WHERE UPDATE子更新表中所有行条件可以使用比value2,...[WHERE condition]SET句指定要修改的列及其新值，可以较运算符（等）、逻辑运算符=,,更新多个列（）等AND,OR,NOT更新注意事项计算更新值执行更新前应先使用测试更新值可以是常量、函数结果或基SELECT条件是否筛选出预期的行于当前值的计算例如WHERE UPDATE大型表的更新可能影响性能，考虑可以将products SETprice=price*

1.1在低峰期操作或分批更新所有产品价格提高10%删除数据（）DELETE语句结构与的区别DELETE TRUNCATE语句用于从表中删除行基本语法为是一个命令，它删除表中的所有行，DELETE FROMTRUNCATE TABLEDDL子句但保留表结构与不同，通常不能带DELETE FROM table_name[WHERE condition]WHERE DELETE TRUNCATE指定要删除的行，如果省略，则删除表中所有行子句，只能删除全表数据WHERE操作是可以回滚的（在事务中），并且会触发表上操作比快，因为它不记录单行删除，而DELETETRUNCATEDELETE的触发器也会记录日志，便于审计和恢是直接释放存储表数据的数据页会重置表的DELETE DELETETRUNCATE复但这也意味着大批量删除数据时，操作可能比自增计数器，而不会在大多数数据库中，DELETE DELETE慢操作不能回滚，也不会触发触发器TRUNCATE TRUNCATE数据查询语言（）简介DQL数据检索的核心强大的过滤能力多表关联查询数据查询语言（）是中用于语句通过子句提供强通过操作，能够关联多DQL SQL SELECT WHEREJOIN SELECT从数据库检索数据的部分，主要由大的数据过滤功能，可以根据各种个表中的数据，实现复杂的数据整语句组成是中条件筛选出需要的数据它还支持合这是关系型数据库的核心优势SELECT SELECTSQL最常用的命令，可以完成从简单的排序（）、分组（之一，使用户能够以灵活的方式组ORDER BYGROUP单表查询到复杂的多表联合查询、）、聚合函数（如、合和分析分布在不同表中的相关数BY COUNT子查询等各种数据检索操作、）等高级功能，使数据据SUM AVG分析更加灵活选择列与表（）SELECT...FROM查询指定列查询所有列别名与显示优化使用使用语法查询表可以使用关键字为列和表指定别名，使SELECT column1,column2,...FROM SELECT*FROM table_name AS语法查询特定列这种方式只返中所有列虽然语法简单，但在生产环境结果集更易读或避免名称冲突例如table_name回需要的数据，减少数据传输量和客户端中应谨慎使用，尤其是对于有大量列的SELECT first_name ASname,last_name AS内存占用，提高查询效率表，因为它可能导致返回不必要的数据，surname FROMusers降低查询性能可以在子句中使用表达式或函数处别名在复杂查询、多表联接或使用自联接SELECT理列值，如最佳实践是明确列出需要的列名，除非确时特别有用表别名还可以简化语句的SELECT name,price*

1.1AS SQL实需要表中的所有数据编写，如new_price FROMproducts SELECTu.name,o.order_date FROMusersu JOINorders oON u.id=o.user_id条件查询（子句）WHERE条件表达式写法常用运算符示例子句用于过滤满足特定条件的行基本语法是具体应用示例WHERESELECT column1,column2,...FROM table_name WHERE精确匹配张三•WHERE name=条件表达式可以是简单的比较（如等于、大condition范围查询于、小于），也可以是复杂的逻辑组合•WHERE priceBETWEEN100AND200集合匹配北京上海广州•WHERE cityIN,,比较运算（等于）、（大于）、（小于）、•==模糊查询张（是通配符，匹配•WHERE nameLIKE%%（大于等于）、（小于等于）、或（不等于）=!=任意字符）逻辑运算（与）、（或）、（非）•AND ORNOT复合条件女•WHERE age18AND gender=特殊条件、、、、•BETWEEN...AND...IN...LIKE IS NULL空值检查（注意不能用•WHERE phoneISNULL NULL=IS NOTNULL比较）排序查询（）ORDER BY排序的基本语法子句用于对查询结果进行排序它位于语句的最后部分，基本语ORDER BYSELECT法为SELECT columnsFROM tableWHERE conditionORDER BYcolumn1[ASC|DESC],column2[ASC|DESC],...升序排序（）ASC关键字指定升序排序（从小到大），这是默认的排序方式如果不指定或ASC ASC，数据库将默认使用例如或简写为DESC ASCORDER BYage ASCORDER BYage降序排序（）DESC关键字指定降序排序（从大到小）例如，要按工资从高到低排序DESC ORDER针对日期，降序排序会使最近的日期排在前面BY salary DESC多列排序可以按多个列排序，前面列的值相同时才会按后面的列排序例如ORDER BY会先按部门升序，同一部门内再按薪资降序department ASC,salaryDESC聚合函数使用COUNT SUM计数函数求和函数计算符合条件的行数计算所有行数，包括值；计算指定列非计算指定列所有值的总和，自动忽略值只能用于数值类型列COUNT*NULL COUNTcolumnNULL值的行数NULL示例SELECT SUMamountAS total_sales FROM orders示例SELECT COUNT*FROM studentsWHERE score60AVG MAX/MIN平均值函数最大最小值函数/计算指定列的平均值，自动忽略值通常与函数一起使用控制小数位数返回指定列的最大值或最小值，适用于数值、日期和字符串类型NULL ROUND示例示例SELECT ROUNDAVGsalary,2AS avg_salary FROM employees SELECTMAXprice,MINprice FROMproducts分组查询（）GROUP BY分组筛选（子句）HAVING与区别聚合后过滤数据HAVING WHERE和都用于过滤数据，但作用阶段不同子句通常用于根据聚合结果进行过滤，例如筛选出HAVING WHERE HAVING在分组前过滤行，而在分组后过滤组平均分大于分的班级、订单数超过的客户、总销售WHERE HAVING80100过滤的是原始表中的行，不能使用聚合函数；额超过万的产品等这些条件都是基于分组后的聚合计算WHERE1过滤的是分组后的结果，可以使用聚合函数作为条结果，因此需要用而非HAVING HAVINGWHERE件示例SELECT department,COUNT*as emp_count,处理顺序→→→→FROM WHEREGROUP BYHAVING AVGsalaryas avg_salary FROMemployees GROUP BY理解这个顺序有助于正确编写和优→SELECT ORDER BY departmentHAVING COUNT*5AND AVGsalary化查询；SQL10000多表操作概述为什么需要多表操作联结（）简介表关系类型JOIN在关系型数据库设计中，联结是中用于组合两个表之间的关系主要有三SQL数据通常被分割存储在多或多个表中的行的操作种一对一关系（如用户个表中以避免冗余并保持它基于这些表之间的相关与用户详情）、一对多关数据一致性（规范化）列创建连接例如，系（如部门与员工）和多users例如，将用户信息和订单表中的列与表中的对多关系（如学生与课id orders信息分别存储在表和列建立关系，通过程，通常需要中间表）users user_id表中然而，在实这些列可以将两表的数据理解表之间的关系类型有orders际应用中我们经常需要同联结在一起支持多种助于选择合适的连接方式SQL时查询这些相关表中的数类型的联结，包括内连接和编写高效的连接查询据，例如查询每个用户的（）、左外连INNER JOIN姓名及其订单总数这就接（）、右外连LEFT JOIN需要使用联结（）操接（）和全外连JOIN RIGHT JOIN作来组合多个表中的数接（）等FULL JOIN据内连接（）INNER JOIN内连接原理只返回两表中满足连接条件的匹配行基本语法SELECT*FROM table1INNER JOINtable2ON table

1.column=table

2.column常见应用场景查询有订单的客户信息，没有订单的客户不会出现在结果中内连接（）是最常用的连接类型，它只返回两个表中满足连接条件的记录如果一个表中的记录在另一个表中没有匹配项，那么这条记录不会INNER JOIN出现在连接结果中例如，要查询所有顾客及其订单信息，可以使用SELECT c.customer_name,o.order_date,o.amount FROMcustomers cINNER JOINorders oON c.customer_id这个查询只会返回有订单的顾客记录，没有订单的顾客不会出现在结果中=o.customer_id内连接的简写形式是使用子句WHERE SELECT c.customer_name,o.order_date,o.amount FROMcustomers c,orders oWHERE c.customer_id=o.customer_id虽然这种写法更简洁，但使用标准的语法更清晰，特别是在涉及多表连接时JOIN外连接（）LEFT/RIGHT JOIN左外连接（）右外连接（）LEFT JOINRIGHT JOIN左外连接返回左表中的所有记录，即使右表中没有匹配右外连接返回右表中的所有记录，即使左表中没有匹配项如果右表中没有匹配项，结果中右表的列将包含项如果左表中没有匹配项，结果中左表的列将包含NULLNULL值左连接常用于查询主表数据及其相关的从表数据，即值右连接的功能可以通过调整表的顺序，使用左连接来使从表中没有对应记录实现，因此在实践中使用较少语法语法SELECT*FROM table1LEFT JOINtable2ON SELECT*FROM table1RIGHT JOINtable2ONtable

1.column=table

2.column table

1.column=table

2.column示例查询所有顾客及其订单，包括没有下订单的顾客示例查询所有订单及其顾客信息，包括没有顾客信息的订单（例如已删除顾客的订单）SELECT c.name,o.order_id FROMcustomers cLEFT JOINSELECTc.name,orders oON c.id=o.customer_id o.order_id FROMcustomers cRIGHTJOINorders oON c.id=o.customer_id交叉连接（）CROSS JOIN笛卡尔积的概念语法CROSS JOIN交叉连接（）生成交叉连接的语法有两种形式CROSS JOIN两个表的笛卡尔积，即将第一显式形式SELECT*FROM table1个表的每一行与第二个表的每和隐式形式CROSS JOINtable2一行组合结果集的行数等于SELECT*FROMtable1,table2两个表行数的乘积例如，如（省略关键字和条JOIN ON果表有行，表有行，则件）两种形式产生的结果相A3B4的结果将有行同，但显式形式更清晰地表明CROSS JOIN12（）了连接类型3×4使用注意事项交叉连接可能产生非常大的结果集，导致性能问题例如，两个各有行的表进行交叉连接会产生行的结果因此，应谨慎使10001,000,000用交叉连接，特别是对大型表在大多数情况下，应该使用带有连接条件的其他类型的连接多表连接注意事项表别名的使用连接关键字选择消除列名歧义在多表连接查询中，使用表别名可以简根据需求选择适当的连接类型当连接的表中有同名列时（如两个表都INNER化语句，使代码更简洁易读例只返回匹配行；保留左表有列），必须使用表名或表别名来限SQL JOINLEFT JOINid如所有行；保留右表所有行；定列名，避免歧义例如，使用SELECT e.name,d.department_name RIGHTJOIN保留两表所有行连接条件通或而不是单独的在FROMemployeese JOIN departments dFULL JOINemployees.id e.id id表别名特别常使用主键与外键，但也可以是任何满子句中选择列时，明确指定表名ON e.department_id=d.id SELECT适用于自连接（同一个表连接自身）的足业务逻辑的条件可以提高代码可读性和维护性情况子查询（）SubQuery子查询定义子句中的子查询WHERE子查询是嵌套在另一个查询内部的最常见的子查询用法，用于为外部查询提SELECT语句，用于为外部查询提供数据或条件供过滤条件例如SELECT name FROM子查询可以出现在、、SELECT FROMemployees WHEREdepartment_id IN或子句中，根据位置不同WHEREHAVINGSELECT idFROM departmentsWHERE有不同的用途和限制北京location=子句中的子查询FROM子句中的子查询SELECT子查询作为派生表（临时表），外部查询子查询产生的值作为外部查询结果集的一从这个临时表中选择数据例如SELECT部分通常是标量子查询，只返回单行单avg_salary,department_nameFROM列例如SELECT name,SELECTSELECT department_id,AVGsalary asCOUNT*FROMordersWHEREavg_salary FROMemployees GROUPBYcustomer_id=c.id asorder_count FROMdepartment_id eJOINdepartmentsd ONcustomersce.department_id=d.id数据库事务基础事务（）定义事务的应用场景Transaction事务是数据库中的一个操作逻辑单元，包含一系列操作，事务广泛应用于需要保证数据完整性和一致性的场景，典这些操作要么全部成功执行，要么全部不执行事务是数型的应用包括据库管理系统（）中保证数据一致性和可靠性的重DBMS银行转账确保资金从一个账户转移到另一个账户的过•要机制程中不会因系统故障导致资金丢失或重复计算事务通常由多个数据库操作（如、、INSERT UPDATE DELETE电子商务订单创建、库存更新、支付处理等环节作为•等）组成，这些操作在逻辑上构成一个不可分割的整体一个事务处理，确保数据的一致性例如，银行转账涉及两个操作从一个账户扣款和向另一预订系统如机票预订、酒店预订等，需要锁定资源并•个账户存款，这两个操作必须同时成功或同时失败，不能更新多个相关记录只执行其中一个数据批量处理大量数据的导入、转换或更新操作，需•要保证全部成功或全部回滚事务的特性ACID原子性（）Atomicity事务中的所有操作要么全部执行成功，要么全部不执行，不存在部分执行的情况一致性（）Consistency事务执行前后，数据库必须保持一致状态，满足所有完整性约束隔离性（）Isolation多个事务并发执行时，每个事务的操作对其他事务是隔离的，不会互相影响持久性（）Durability一旦事务提交，其对数据库的修改将永久保存，即使系统发生故障也不会丢失事务控制语句BEGIN/START TRANSACTION标记事务的开始，告诉数据库系统接下来的一系列操作是单个事务的一部分在某些数据库中，第一个修改语句（如、、）会自动开始一个事务，不需要显INSERT UPDATEDELETE式的语句BEGINCOMMIT将事务中所做的所有修改永久保存到数据库中，并结束当前事务执行后，事务的更改变为永久性，不能再回滚其他事务可以看到已提交事务的结果，具体取决于隔离COMMIT级别ROLLBACK撤销事务中所做的所有修改，将数据库恢复到事务开始前的状态，并结束当前事务通常在事务执行过程中遇到错误时使用，也可以由程序显式调用以取消事务ROLLBACKSAVEPOINT在事务中创建一个保存点，允许后续回滚到该点而不是事务开始语法为；要回滚到保存点，使用保存点使事务可SAVEPOINT savepoint_name ROLLBACKTO savepoint_name以部分回滚事务隔离级别SERIALIZABLE最高隔离级别，完全串行执行事务，防止所有并发问题REPEATABLE READ防止脏读和不可重复读，但可能出现幻读问题READ COMMITTED防止脏读，但可能出现不可重复读和幻读问题READ UNCOMMITTED最低隔离级别，可能出现脏读、不可重复读和幻读问题事务隔离级别决定了一个事务的修改在什么时候对其他事务可见，以及并发事务如何相互影响标准定义了四个隔离级别，从低到高依次提供更强的隔离保证，但也带来更多SQL的性能开销不同数据库系统的默认隔离级别不同的默认为，默认为，默认为可以使用MySQL InnoDBREPEATABLE READOracle READCOMMITTED SQLServer READCOMMITTED SET语句设置当前会话的隔离级别TRANSACTION ISOLATIONLEVEL选择隔离级别应权衡数据一致性需求和性能要求高隔离级别提供更强的一致性保证，但可能导致更多的锁定和性能下降；低隔离级别性能更好，但可能导致数据一致性问题并发事务中的问题脏读（）不可重复读（Dirty ReadNon-repeatable）Read一个事务读取了另一个未提交事务在同一事务内，多次读取同一数据修改过的数据如果该未提交事务集返回了不同的结果这通常是因最终回滚，则第一个事务读取的数据就是无效的脏数据例如，事务为另一个事务在本事务的两次读取更新了一行数据但未提交，事务之间修改并提交了数据例如，事A B务读取一行数据，事务修改该行读取了这行更新后的数据，然后事A B并提交，然后事务再次读取同一务回滚，导致事务读取到的是从AA B行，发现数据已经变化未实际存在过的数据幻读（）Phantom Read在同一事务内，同一查询在不同时间返回了不同的行集合这通常是因为另一个事务在本事务的两次查询之间插入或删除了符合查询条件的行例如，事务查询所有价格大于的产品，返回条记录；事务插入了一个价格为的A10010B150新产品并提交；事务再次执行同样的查询，现在返回了条记录A11数据库视图（）基础VIEW视图定义与创建视图的优点视图是基于查询的虚拟表，它不存储数据，而是在查视图提供了多种优势SQL询时动态生成视图是对一个或多个表的查询结果的命名简化复杂查询将复杂的查询封装为简单的视图，提高•保存，可以像表一样使用视图的基本创建语法为代码复用性CREATE VIEW view_name ASSELECT...FROM...WHERE...增强安全性限制用户只能看到特定的数据列或行，保•视图可以基于底层表的部分列、多个表的联结、带有条件护敏感数据过滤的查询结果等当查询视图时，数据库系统会执行视提供数据抽象隐藏底层表结构的复杂性，提供更直观•图定义中的语句，返回查询结果SELECT的数据访问方式保持向后兼容当底层表结构变化时，可以通过修改视•图定义而不影响应用程序支持数据隔离不同用户或应用可以使用不同视图访问•同一数据，实现数据隔离视图操作创建视图使用语句创建视图，语法为CREATE VIEWCREATE[OR REPLACE]VIEWview_name[column_list]选项可以覆盖同名视图；可以AS select_statement[WITH CHECKOPTION]OR REPLACEcolumn_list为视图列指定自定义名称；限制对视图的修改必须符合视图定义的条件WITH CHECKOPTION查询视图视图一旦创建，可以像表一样在语句中使用SELECT SELECT*FROM view_name WHERE可以对视图应用语句的所有功能，包括条件、排序、联结其condition SELECTWHERE ORDER BY他表或视图等修改视图3可以通过语句修改现有视图的定义ALTER VIEWALTER VIEWview_name AS也可以使用语句替换现有视图修改视图定义new_select_statement CREATEOR REPLACEVIEW不会影响依赖该视图的存储过程或应用程序，但可能改变查询结果删除视图使用语句删除不再需要的视图选项防止DROP VIEWDROPVIEW[IF EXISTS]view_name IFEXISTS在视图不存在时产生错误删除视图不会影响底层表中的数据，但会影响依赖该视图的对象视图的应用场景简化复杂查询提高安全性数据统计与分析将多表连接、复杂条通过视图限制用户对创建包含聚合函数的件过滤和聚合操作封敏感数据的访问，只视图，预先计算常用装为视图，简化后续允许特定列或满足特统计数据例如，创查询例如，创建一定条件的行被查看建显示每个部门员工个包含产品、类别和例如，为人力资源部数量和平均薪资的视供应商信息的视图，门创建一个员工视图，或者每月销售总使得用户无需编写复图，其中不包含薪资额和同比增长率的视杂的联结查询就能获信息；或为普通用户图这类视图简化了取完整信息这种做创建一个只显示公开报表生成和数据分析法减少了代码冗余，文档的视图视图配工作，特别适合频繁提高了开发效率和维合权限控制，可以实执行的统计查询护性现细粒度的数据访问限制数据库索引（）基础INDEX索引的原理树与树索引B B+索引是数据库中用于提高查询性能大多数关系型数据库使用树或树B B+的数据结构，类似于书籍的目录作为索引结构树将所有数据存B+它存储表中一列或多列的值及其对储在叶子节点，中间节点只存储索应的行指针，使数据库系统能够快引键，有利于范围查询和减少操I/O速定位满足条件的行，而不需要扫作次数描整个表唯一索引主键索引唯一索引确保索引列中的值唯一主键索引是特殊的唯一索引，在创（可以有，除非指定NULL NOT建表时指定自动创建PRIMARY KEY）它不仅提高查询性能，还NULL它要求列值唯一且非空，并用于唯实现数据唯一性约束，适用于用户一标识表中的每一行数据名、邮箱等必须唯一的字段索引的创建与管理创建索引查看索引删除索引使用语句创建索引使用数据库特定的命令查看表的索引使用语句删除不再需要的CREATE INDEXDROPINDEX信息例如，在中使用索引CREATE[UNIQUE]INDEX index_name MySQLSHOW DROPINDEX index_name ON；在删除表时，其上的所有索ON table_name column1,column2,...INDEX FROMtable_name SQLtable_name对于单列索引，只包含一个列；对于中使用引也会被自动删除删除或禁用不必Server sp_helpindex复合索引，包含多个列也可以在表；在中查询要的索引可以减少维护开销，提高写table_name Oracle创建时通过或约视图这些命令显示索入性能PRIMARY KEYUNIQUE USER_INDEXES束隐式创建索引引名称、类型、包含的列等信息索引的使用与优化加速查询示例何时不适合创建索引索引能显著提高查询性能，特别是以下几种情况索引并非适用于所有场景，以下情况应谨慎使用精确匹配搜索表很小，全表扫描也很快时•WHERE column=value•范围查询列的基数很低（如性别列只有男女两个值）•WHERE columnBETWEEN value1AND value2•/排序操作表频繁更新，且查询较少•ORDERBYindexed_column•连接操作常在联结条件列上创建索引列很少作为查询条件或排序依据••聚合函数如、等索引列经常被值填充•MIN MAX•NULL例如，在含有百万条记录的员工表上，按员工姓名查询索引会占用存储空间，并在数据修改时需要维护，过多或时，有索引的查询可能只需几毫秒，而无索引的全表扫描不当的索引会降低写入性能应根据实际查询模式和数据可能需要几秒甚至更长时间分布特点权衡利弊约束条件概述约束是确保数据库数据满足特定规则的机制，它们在创建或修改表时定义，由数据库系统自动强制执行主要的约束类型包括约束确保列不接受值，适用于必填字段；约束确保列中所有值唯一，可用于用户名、邮箱等；是特殊的唯一约束，用于唯一标识NOTNULLNULLUNIQUEPRIMARYKEY表中的每一行，一个表只能有一个主键；约束建立表之间的关系，确保引用的值在被引用表中存在；约束定义列值必须满足的条件，如FOREIGNKEYCHECK CHECKage=18约束在维护数据完整性方面起关键作用，防止无效数据进入数据库虽然一些验证可以在应用层实现，但数据库约束提供了最后的防线，确保即使应用代码有缺陷，数据也保持一致用户权限管理授权（）操作收回权限（）用户与角色基础GRANT REVOKE命令用于授予用户对数据库对象的命令用于撤销先前授予的权限用户是访问数据库的个体账户，代表真GRANT REVOKE访问权限基本语法为基本语法为实用户或应用程序角色是权限的集GRANT REVOKEprivileges ON、和合，可以分配给多个用户，简化权限管privileges ONobject TOuser[WITH GRANTobject FROMuser privilegesobject指定要授予的权限，的含义与命令相同理角色通常对应于组织中的职位或职OPTION]privileges userGRANT如、、、、能，如管理员、数据分析师或只读用SELECT INSERT UPDATEDELETE示例REVOKE UPDATE ON employees；指定权限适用的对象，如户ALL object；收回用户FROM hr_manager hr_manager表、视图或整个数据库；指定接收权user对表的更新权限管理实践遵循最小权限原则，只授予employees限的用户或角色；允WITH GRANTOPTION用户完成工作所需的最小权限集；定期许用户将自己的权限授予其他用户审查用户权限，移除不再需要的权限；示例利用角色简化权限管理GRANT SELECT,UPDATEON；授予employees TOhr_manager用户对表的查询和hr_manager employees更新权限常见错误分析SQL语法错误数据类型错误语法错误是最常见的错误类型，数据类型不匹配错误发生在尝试SQL包括关键字拼写错误（如而将字符串插入数字列；日期格式不SLECT非）；缺少或多余的逗号、正确；数值溢出（如插入超出列定SELECT括号或引号；大小写错误（在区分义范围的数值）；使用不兼容类型大小写的数据库中）；表名或列名进行比较或计算这类错误可能导拼写错误这类错误通常会导致查致转换失败或产生意外的结果询执行失败，并返回具体的语法错误消息逻辑错误逻辑错误是指语法正确但结果不符合预期的错误，包括条件逻辑不正WHERE确（如使用而非）；条件错误导致笛卡尔积或缺少数据；OR ANDJOIN GROUPBY不完整；子查询返回多行而期望单行；排序方向错误逻辑错误不会ORDERBY产生错误消息，但会导致错误的结果调试与优化思路SQL执行计划分析使用（）或（）等命令分析查询的执行计划，了解数据EXPLAIN MySQLEXPLAIN PLANOracle库如何执行查询执行计划显示表的访问方式（全表扫描或索引扫描）、联结类型、联结顺序等信息，帮助识别性能瓶颈索引优化为频繁用作查询条件、排序或连接条件的列创建适当的索引考虑使用复合索引处理多列条件避免在低选择性列上创建单独索引分析现有索引使用情况，删除未使用或重复的索引以减少维护开销查询重写优化语句结构避免使用，只选择需要的列；限制结果集大小SQLSELECT*（）；使用等价的更高效表达式（如用替代某些子查询）；拆分复杂查LIMIT/TOP EXISTSIN询为简单查询；使用联接代替相关子查询；适当预先计算和存储频繁使用的计算结果数据库参数调优调整数据库系统参数以适应工作负载增加缓冲池大小；优化排序和联接缓冲区；调整并发连接数；配置查询缓存（如适用）这些调整通常需要数据库管理员权限，并应在测试环境验证后应用到生产环境数据备份与恢复备份方法备份策略完整备份复制整个数据库的所有数据；差异备份备份自上次完整备份以来变化设计合理的备份策略，包括备份频率（每的数据；增量备份仅备份自上次任何类天、每周等）、备份类型组合（如周日完型备份以来变化的数据；逻辑备份导出整备份每日增量备份）、备份存储位置+语句形式的数据和结构，如使用（本地和异地）和保留期限根据数据重SQL；物理备份直接复制数据文要性和变化频率调整策略mysqldump件，速度更快恢复流程备份测试确定需要恢复的时间点；如果使用物理备定期测试备份有效性，确保能够成功恢份，停止数据库并替换数据文件；如果使复在测试环境中执行恢复演练，验证备用逻辑备份，执行备份脚本；按顺序应用份数据的完整性和恢复过程的可靠性记差异或增量备份（如果有）；应用事务日录恢复时间和结果，持续改进备份策略和志恢复到特定时间点（时间点恢复）；验恢复流程证恢复的数据完整性数据导入与导出数据导出工具各数据库系统提供专用导出工具的可将数据库导出为文MySQL mysqldumpSQL件；的支持多种格式导出；的提供高性能导PostgreSQL pg_dump Oracle Data Pump出功能这些工具通常支持导出整个数据库、选定表、或自定义查询结果，并可以控制是否包含结构定义、索引等数据导入方法导入方法包括执行导出生成的脚本；使用专用导入工具（如的SQL MySQLmysql命令、的）；使用（）或OracleDataPump ImportLOAD DATAMySQL COPY（）等命令从文本文件批量导入；使用数据库客户端工具的导入功PostgreSQL能；利用（提取、转换、加载）工具处理复杂的导入需求ETL数据迁移注意事项在数据迁移过程中需要注意数据类型兼容性问题，特别是跨数据库系统迁移时；字符集和排序规则的差异；处理值和默认值的方式；主键和外键约束的NULL处理；索引和存储过程等对象的迁移；大表数据的分批导入以避免锁定和性能问题；迁移后的数据验证和一致性检查案例分析实际项目中的数据库操作实践环节课堂练习

（一）练习题预期操作关键点创建学生表定义表结构合理设置主键与数据类型插入示例数据添加条学生记录保证数据完整性10修改学生信息更新特定学生的联系方式使用正确的条件WHERE查询并排序按降序列出所有学生使用子句GPA ORDERBY分组统计按年级统计学生人数使用和函数GROUPBYCOUNT在这个单表操作练习中，我们将从基础的表创建开始，通过实际动手操作掌握和语句练习分为DDL DML五个环节，从表结构定义、数据插入、更新操作到简单查询和统计分析，层层深入，逐步掌握数据库操作基础第一题要求创建一个包含、、、、、、和等字段的学生id namegender birth_date contactaddress gradegpa表，需要合理设置字段类型和约束条件第二题在此基础上添加样本数据，为后续操作提供数据基础第

三、四题练习基本的查询和更新操作，第五题则引入了分组统计功能每个练习都有明确的操作目标和关注点，学生在完成练习后应能理解并掌握相应的技能练习中可能SQL遇到的常见问题包括语法错误、数据类型不匹配、值处理不当等，我们将在讲解环节详细分析这NULL些问题的解决方法实践环节课堂练习

（二）高难度子查询嵌套查找选修了所有课程的学生中等难度复杂连接与过滤查询各系平均分最高的学生基础难度多表连接关联学生、课程和成绩表在这个多表查询与子查询练习环节中，我们将使用三个关联的表进行操作（学生信息）、（课程信息）和（成绩信息）这些表通过外students coursesscores键相互关联，构成一个完整的学生成绩管理模型基础难度题目要求使用连接三个表，查询每个学生的姓名、课程名称和对应成绩这需要正确设置连接条件INNER JOINscores.student_id=students.id AND中等难度题目需要先按系别分组，再找出每个系中平均分最高的学生，这需要使用、聚合函数和子查询的组合scores.course_id=courses.id GROUPBY高难度题目要求找出选修了所有课程的学生，需要使用子查询或结合计数的方法这类复杂查询是实际数据分析中常见的场景，能NOT EXISTSGROUPBYHAVING够检验学生对高级特性的理解和应用能力完成练习后，我们将分析每个题目的多种解法，比较不同方法的效率和适用场景SQL考试测验题型说明/客观题型实操题考试中的客观题包括实操题是考试的核心部分，包括选择题包括单选和多选，考查基本概念理解和简单语数据定义创建、修改或删除数据库对象的语句编写••DDL法规则判断题辨析语句的正误，考查常见误区的识别能力数据操作插入、更新或删除数据的语句编写•SQL•DML数据查询从简单到复杂的各类查询编写•SELECT填空题补充语句中的关键部分，如缺少的关键字或•SQL综合应用结合多种操作解决实际问题的案例•SQL子句重点和难点包括多表联合查询、子查询的正确使用、聚合客观题主要考查对数据库基础概念、语法规则和常用函SQL函数与分组、复杂条件的构建等考试将提供测试数据库环数的掌握程度例如识别不同类型语句的功能、理解SQL境，要求学生编写语句并执行，根据结果的正确性和效SQL各种连接类型的区别、判断特定语句的执行结果等率评分本讲小结与知识点回顾数据库基础与语言SQL我们回顾了数据库的核心概念、语言的起源发展，以及语句的基本分类（、SQLSQLDDL、、）理解这些基础知识是掌握数据库操作的前提，也是进一步学习的基DML DQLDCL石数据定义与结构操作学习了、、等语句的使用，掌握了表结构的创建、修改与删除表设CREATE ALTERDROP DDL计中的主键、外键、唯一约束等概念对确保数据完整性至关重要，是数据库设计的核心部分数据操作与查询技术掌握了、、等数据操作语句，以及查询的各种技巧，包括条件INSERTUPDATEDELETE SELECT查询、排序、分组、聚合函数等数据查询是数据库使用中最常见的操作，熟练掌握各种查询技术能够满足复杂的数据分析需求高级特性与实践技巧探讨了事务管理、视图、索引等高级特性，以及优化、数据备份与恢复等实践技巧这SQL些知识对构建高性能、高可靠性的数据库应用至关重要，是从初级到高级数据库开发者的重要跨越下节预告与答疑互动下节课主题前瞻下一讲我们将进入《数据库编程基础》，学习如何通过编程语言与数据库交互主要内容包括数据库连接技术、使用访问数据库、预处理语句与参数化查询、存JDBC/ODBC储过程和触发器的使用、框架介绍等ORM请同学们提前准备相关的开发环境，如等编程语言环境和相应的数据库驱Java/Python动，以便能够跟随课堂实践建议复习本讲内容，尤其是查询部分，为下一讲的编程SQL实践打好基础答疑互动环节现在是我们的互动时间，欢迎大家提出本讲内容相关的疑问常见问题包括多表连接与子查询的选择依据•事务隔离级别如何影响并发操作•索引对查询性能的影响程度•如何优化复杂查询•SQL请同学们踊跃提问，也可以分享学习过程中遇到的实际问题对共性问题，我们将在课后通过学习平台发布补充材料。