数据库原理与应用 课件 第3章 关系数据库语言SQL

数据库原理与应用第3章关系数据库语言SQL本章知识导图本章学习目标了解：基础概念与环境熟悉SQL的主要标准、组成架构与运行环境，掌握过程化SQL的基础概念。理解：核心原理与逻辑深入理解SQL非过程化特点、查询逻辑执行顺序及视图消解原理。掌握：核心语法与功能熟练掌握DDL/DML语法，视图定义与使用，以及过程化SQL的核心功能。应用：业务实现与开发能够完成表创建、复杂查询，并使用存储过程、触发器处理实际业务。素养：职业规范与优化养成规范编写SQL的习惯，重视数据完整性与安全性，具备主动优化查询效率的意识。目录01.SQL概述标准、特点、组成、环境02.数据定义(DDL)定义数据库、表、索引03.数据操纵(DML)插入、更新、删除、查询数据04.视图创建、查询、更新、删除视图05.过程化SQL变量、流程控制、游标、自定义函数、存储过程、触发器06.本章小结与习题3.1SQL概述标准·特点·组成·环境3.1.1SQL的主要标准核心制定组织ANSI&ISOSQL标准由美国国家标准协会（ANSI）与国际标准化组织（ISO）联合主导制定，确保了语言的通用性和规范性。标准演进历程SQL-86/89：基础级标准，定义核心框架。SQL-1999：引入面向对象、触发器等，功能大幅扩展。2003-2023：持续迭代，新增XML、JSON支持，适配现代数据场景。标准与扩展厂商扩展语法主流数据库厂商（如MySQL、SQLServer、Oracle）会在标准基础上增加私有扩展。实际应用中需平衡标准兼容性与厂商特性。3.1.2SQL的特点综合统一集DDL、DML、DCL于一体，覆盖数据库操作全流程，避免了语言切换的复杂性。高度非过程化用户只需提出“做什么”，无需指明“怎么做”，存取路径由系统自动完成。支持两种使用方式既是自含式语言（联机交互），又是嵌入式语言（嵌入高级语言），使用场景灵活。简洁、通用、功能强语法简单，接近自然语言，仅用9个核心关键词（如SELECT,INSERT等）即可实现主要功能。3.1.3SQL的组成-数据库层次结构三级模式结构映射外模式：对应视图和部分基本表，面向用户模式：对应基本表，是数据库的整体逻辑结构内模式：对应存储文件，是数据的物理存储方式核心概念解析基本表：独立存在的表，对应一个关系，存储实际数据视图：从基本表导出的虚表，不存储数据，仅保存查询定义存储文件：物理文件，存放数据，对用户透明3.1.3SQL的组成-语句分类数据定义语言(DDL)用于创建、修改、删除数据库对象（如数据库、表、索引）。核心关键字：CREATE,ALTER,DROP数据操纵语言(DML)用于插入、删除、修改和查询数据，是最常用的操作语言。核心关键字：INSERT,DELETE,UPDATE,SELECT数据控制语言(DCL)用于数据库用户授权、角色管理及事务提交回滚等。核心关键字：GRANT,REVOKESQL会话及诊断语句用于建立连接、设置环境参数、诊断数据库状态等。说明：本书暂不涉及此部分内容3.1.3SQL的组成-数据类型数值类型包含整数与浮点数：INT,BIGINT,DECIMAL,FLOAT,DOUBLEPRECISION等，用于存储精确或近似数值。字符串类型用于存储文本数据：CHAR(n),VARCHAR(n),NCHAR(n),NVARCHAR(n)等，支持定长与变长字符。位串类型存储二进制数据：BINARY(n),VARBINARY(n),BLOB等。注意：通常不推荐用BLOB存储大文件。日期类型处理时间相关数据：DATE(日期),TIME(时间),TIMESTAMP(时间戳)等，精确到秒或毫秒级。布尔类型表示逻辑真/假值：BOOLEAN，标准取值为TRUE和FALSE，常用于条件判断和状态标记。用户自定义类型基于现有类型创建新类型：SQL标准支持用户根据特定业务场景（如枚举、结构体）扩展数据类型。3.1.4SQL环境目录(Catalog)顶层容器，可理解为“数据库级命名空间”，是SQL环境的最高逻辑划分。模式(Schema)目录下的二级容器，即“用户级命名空间”，用于逻辑组织表、视图等数据库对象。用户与权限操作主体和许可机制，通过GRANT和REVOKE语句进行精细化的权限管理。连接与会话用户与数据库的物理链路（连接）和逻辑执行上下文（会话），是交互的基础。SQL执行引擎负责解析、优化和执行SQL语句的核心模块，是数据库处理请求的“大脑”。核心逻辑框架SQL环境是支撑语句执行的完整舞台，整合了对象组织、访问控制与执行调度。3.2数据定义(DDL)定义数据库·定义数据表·定义索引3.2.1定义数据库-创建数据库核心作用创建一个新的数据库，作为存储数据表、视图等对象的顶层逻辑容器。这是使用数据库系统的第一步，类似于为数据建立一个“仓库”。基本语法CREATEDATABASE数据库名称;关键说明MySQL兼容性：DATABASE可替换为SCHEMA权限要求：执行此操作通常需要管理员（Admin）权限。案例：创建数据库案例1：创建学生成绩数据库CREATEDATABASEXSCJ;数据库名：XSCJ(学生成绩)案例2：创建公司员工数据库CREATEDATABASECompanyDB;数据库名：CompanyDB(公司员工)执行结果说明执行上述语句后，数据库管理系统会在磁盘上创建相应的文件和目录，用于存储该数据库的数据。3.2.1定义数据库-切换数据库核心作用用于指定当前会话操作的数据库，将操作上下文切换至目标库。使用说明成功执行后，后续所有操作均在指定数据库中进行。可配置用户默认数据库，避免重复使用USE语句。基本语法USE数据库名称

--示例：切换到名为'mydb'的数据库USEmydb案例：切换数据库案例1：直接切换数据库上下文直接切换当前会话的数据库上下文到XSCJ数据库。USEXSCJ案例2：配置用户默认数据库修改用户"user1"的属性，设置其登录时的默认数据库为CompanyDB。ALTERLOGIN'user1'WITHDEFAULT_DATABASE=CompanyDB3.2.1定义数据库-查看与删除数据库查看数据库查看所有数据库列表SQLServer:EXECsp_databasesMySQL:SHOWDATABASES查看指定数据库详情SQLServer:EXECsp_helpdb'DB_Name'MySQL:SHOWCREATEDATABASEDB_Name删除数据库标准语法(通用)DROPDATABASE数据库名称操作警告(Warning)注意：此操作不可逆！执行删除命令将彻底移除数据库及其包含的所有表、视图、存储过程和数据。请务必在操作前进行数据备份，并确认数据库名称无误。3.2.2定义模式核心概念：逻辑命名空间模式是数据库对象的容器，用于隔离不同用户或应用的表、视图等对象，避免命名冲突。SQLServer：多模式独立管理一个数据库包含多个模式，模式独立于用户。访问对象需指定模式名。--创建模式CREATESCHEMASales--在指定模式下创建表CREATETABLESales.Orders(IDINT)--访问跨模式对象SELECT*FROMSales.OrdersMySQL：数据库即模式Database与Schema概念完全等价，创建数据库即创建模式。--创建模式(等同于创建数据库)CREATEDATABASESales--或CREATESCHEMASales--使用模式(切换数据库)USESalesCREATETABLEOrders(IDINT)特性对比总结特性维度SQLServerMySQL与DB关系1DB可包含多个SchemaDB=Schema独立性独立于用户与DB/权限绑定3.2.3定义数据表-创建数据表核心作用在数据库中创建新的数据表，定义表的结构（列名、数据类型、约束），是存储数据的基本单元。常用约束(Constraints)PRIMARYKEY:主键，唯一标识记录FOREIGNKEY:外键，建立表间关联NOTNULL:非空，字段值不能为空UNIQUE:唯一，字段值不能重复基本语法CREATETABLE表名(列名1数据类型[列级约束],列名2数据类型[列级约束],...[表级约束])约束类型

列级约束:针对单个属性的约束，可以置于列定义中。表级约束:针对整个表的约束，必须独立于列定义。涉及该表多个列的约束必须作为表级约束。针对单个列的约束则既可以定义在列级，也可以定义在表级。CHECK:检查，字段值满足一个逻辑表达式的要求案例：创建学生表(Student)Student表的结构信息建表SQL语句CREATETABLEStudent(

snoCHAR(6)PRIMARYKEY,snameVARCHAR(15)NOTNULL,ssexCHAR(2)CHECK(ssexIN('男','女'))DEFAULT'男',

sageSMALLINTCHECK(sage>16),sdeptVARCHAR(10))列名数据类型约束snochar(6)主键snamevarchar(15)非空ssexchar(2)取值范围、默认值sagesmallint大于16sdeptvarchar(10)说明主键及两个CHECK约束均没有通过CONSTRAINT关键字取名，实际项目中建议为其取名。所有的约束都属于列级约束。ssex、sage和sdept三个列都可以取空值。案例：创建课程表(Course)Course表的结构信息建表SQL语句CREATETABLECourse(

cnoCHAR(3),cnameVARCHAR(20)NOTNULL,creditINTNOTNULLCHECK(credit>0ANDcredit<=10),pcnoCHAR(3)REFERENCESCourse(cno),CONSTRAINTPK_CoursePRIMARYKEY(cno),CONSTRAINTUK_Course_cnameUNIQUE(cname))列名数据类型约束cnochar(3)主键cnamevarchar(20)非空、唯一creditINT非空、取值范围pcnochar(3)外键（引用先修课程）说明CONSTRAINT命名部分可以省略，但不建议。外键pcno属于实体集内部的联系。指定主键或外键的属性组成时，括号不能省略（即使只包含单个属性）。案例：创建选课表(SC)Course表的结构信息建表SQL语句CREATETABLESC(

snoCHAR(6)NOTNULL,cnoCHAR(3)NOTNULL,gradeSMALLINTCHECK(grade>=0ANDgrade<=100ORgradeISNULL),CONSTRAINTPK_SCPRIMARYKEY(sno,cno),CONSTRAINTFK_SC_StudentsFOREIGNKEY(sno)REFERENCESStudent(sno),CONSTRAINTFK_SC_CoursesFOREIGNKEY(cno)REFERENCESCourse(cno))列名数据类型约束snochar(6)非空，外键cnochar(3)非空、外键gradesmallint取值范围sno和cno共同构成主键说明设计当前表中多个属性的约束（如复合主键）必须作为表级约束。sno和cno的非空约束是不必要的。为什么？3.2.3定义数据表-修改数据表基本语法(SQLServer)ALTERTABLE表名

{--添加列

ADD列名数据类型[列约束]--修改列

|ALTERCOLUMN列名新数据类型[新约束]--删除列

|DROPCOLUMN列名

--添加约束

|ADD[CONSTRAINT约束名]表级约束

--删除约束

|DROPCONSTRAINT约束名}常用操作添加列、约束修改列的数据类型删除列、约束（需要约束名）作用：用于修改已存在的数据表结构，提供灵活的数据库调整能力。案例：修改数据表案例1：添加新列为“学生”表添加“邮箱”列，数据类型为VARCHAR(50)。ALTERTABLEStudentADDemailVARCHAR(50)案例2：删除列从“学生”表中删除“邮箱”列。ALTERTABLEStudentDROPCOLUMN

email案例3：修改列类型将“学生”表的“院系”列数据类型更改为CHAR(50)。ALTERTABLEStudentALTERCOLUMNsdeptCHAR(50)案例4：删除约束删除“选课”表的主键约束。ALTERTABLESCDROPCONSTRAINT

PK_SC案例5：添加约束为“选课”表添加主键约束。ALTERTABLESCADDCONSTRAINT

PK_SCPRIMARYKEY(sno,cno)3.2.2定义数据表-删除数据表功能作用用于删除一个已存在的数据表，操作会同时移除该表的所有数据记录和相关索引。基本语法DROPTABLE[IFEXISTS]表名重要说明删除操作不可逆，会永久删除表结构和数据，非常危险，请谨慎操作。若存在外键引用，需先删除相关引用。练习：定义数据表01.创建“员工”表字段：员工ID(主键),姓名,职位,入职日期。请写出创建表的SQL语句。02.添加“薪资”列为已存在的“员工”表添加一列，列名为“薪资”，数据类型为DECIMAL(10,2)。03.删除“员工”表写出删除整个“员工”表结构的SQL语句。3.2.3定义索引–基本概念索引的作用核心目标：创建索引的主要目的是提高数据查询效率，避免全表扫描。本质定义：索引是对数据库表中一列或多列的值进行排序的一种数据结构，类似于书籍的目录，能让数据库快速定位数据。图解索引123456SnoSname010310101031060103104010310201031100103105Student记录号索引文件301031046010310520103106501031104010310210103101指针Sno

7

010310340103102301031046010310520103106501031107010310310103101指针Sno3.2.3定义索引-创建索引索引的分类（1）按物理存储结构分类聚集索引：索引项逻辑顺序与表中记录的物理顺序一致。（一个基本表至多有一个聚集索引）一般索引：索引项逻辑顺序与表中记录的物理顺序不要求一致。（2）按功能分类普通索引：通过复制索引列的值并建立指向数据行的指针，不强制唯一性，也不影响物理结构。唯一索引：在普通索引的基础上强制要求索引列的值不重复。（唯一性约束的底层实现）主键索引：基于主键自动创建的特殊索引结构SQL基本语法CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(列名[ASC|DESC],...)关键参数说明UNIQUE：唯一索引，确保索引列的值唯一CLUSTERED：聚集索引，物理存储与索引顺序一致NONCLUSTERED：非聚集索引，物理顺序无关，可有多个ASC/DESC：升序/降序，前者为缺省值排序依据：可以包含多个列，越靠前优先级越高案例：创建索引案例1：创建非聚集索引场景：经常根据“姓名”查询学生信息，需提升查询效率。CREATENONCLUSTEREDINDEXidx_stu_snameONStudent(snameASC)案例2：创建唯一索引场景：确保“课程名”的唯一性，防止数据重复。CREATEUNIQUEINDEXidx_course_cname_unqONCourse(cname)3.2.3定义索引-删除索引核心作用用于删除一个已存在于数据库表中的索引。

注：删除操作不可逆，请谨慎执行。基本语法DROPINDEX索引名ON表名执行说明删除索引不会影响表中的实际数据。查询效率可能下降。适用于索引不再被使用或成为性能瓶颈时。案例：删除索引案例1：删除普通索引删除为“学生”表的“姓名”列创建的普通索引。DROPINDEXidx_stu_sname

ONStudent案例2：删除唯一索引删除为“课程”表的“课程名”列创建的唯一索引。DROPINDEXidx_course_cname_unqON

Course习题：定义索引题目一：创建唯一索引写出为“选课”表创建普通索引（按学号升序排列，学号相同时按课程号降序排列）的SQL语句。题目二：删除索引写出删除上一题所创建的索引的SQL语句。小节练习：数据定义(DDL)01.创建数据库创建名为“LibraryDB”的数据库，作为系统的基础容器。02.创建“图书”表定义字段：图书ID(主键),书名,作者,出版社,出版日期。03.添加字段为“图书”表新增“价格”字段，数据类型为DECIMAL(8,2)。04.修改字段类型将“出版日期”字段的数据类型从DATE改为TIMESTAMP。05.创建索引为“书名”字段创建一个非聚集索引，以优化查询性能。06.删除表操作编写删除“图书”表的SQL语句（DROPTABLE）。3.3数据操纵(DML)核心操作概览：插入数据·更新数据·删除数据·查询数据插入数据(INSERT)更新数据(UPDATE)删除数据(DELETE)查询数据(SELECT)3.3.1查询数据–语法结构核心语法结构SELECT

[ALL

|

DISTINCT]

列1

[AS

别名1]

[,

列2,

...]FROM

表名1

[AS

表别名1]

[,

表名2,

...]WHERE

条件表达式GROUP

BY

列1

[,

列2,

...]HAVING

分组条件表达式

ORDER

BY

列1

[ASC/DESC],

列2

[ASC/DESC]核心作用：从一个或多个数据源中检索数据。SELECT子句（必不可少）：指定查询目标（列、算术表达式、函数、子查询、常量等），支持去重（DISTINCT）、取别名、限制结果数量（TOP）；对应关系代数中的投影运算。各子句说明FROM子句：指定查询的数据来源（一个或多个基本表、视图、导出表、公用表达式等）；对应关系代数中的广义笛卡尔积和连接运算。WHERE子句：指定查询的查询条件（例如比较大小、字符串匹配、是否为空、比较子查询、IN为此和EXISTS谓词），用于从数据源中筛选出满足特定条件的记录；对应关系代数中的选择运算。GROUPBY子句：指定分组依据，用于对WHERE子句的结果进行分组（完成后的基本单位是“组”），从而对数据进行精细化的统计（聚集函数）；可进一步通过HAVGING子句对分组结果进行筛选。ORDERBY子句：指定排序依据，在输出前对最终结果进行排序（其原理与索引中的排序相同）。关于“非过程化”的说明：查询语句是按照特定顺序执行的（FROM→WHERE→GROUPBY→HAVING→SELECT→ORDER）。用户编写的SQL语句会经过DMBS的查询优化器处理，实际执行的语句与用户写的不一致，因而查询的过程与用户语句指定的过程不同（即“非过程化”）。3.3.2单表查询–概述什么是单表查询？仅从一个数据库表中检索数据的操作，不涉及多表连接。所有筛选、投影和计算均在单表内完成。学习路径三大核心目标选择列(投影)确定结果包含哪些字段，剔除冗余信息选择行(选择)根据条件筛选记录，精准定位所需数据数据处理排序、去重、分组及聚合统计等操作01.选择列：从简单字段到复杂计算列02.选择行：使用WHERE子句构建条件03.结果处理：排序与去重(ORDERBY/DISTINCT)04.数据统计：聚合函数(SUM/AVG/COUNT)05.分组统计：GROUPBY高级应用3.3.2单表查询–选择指定列基本语法与核心要点SELECT列名1,列名2,...FROM表名列名顺序灵活结果列的显示顺序由SELECT子句决定，可与原表不同。多列查询规范多列之间必须用英文逗号分隔，最后一列后禁止加逗号。案例1:查询全体学生的学号和姓名SELECTSno,SnameFROMStudent2:查询姓名、学号和所在系（自定义顺序）SELECTSname,Sno,sdeptFROMStudent分析案例2中，查询结果列的顺序是“姓名、学号、所在系”，这完全遵循了SELECT子句中的书写顺序，而非原表结构。3.3.2单表查询–选择全部列基本语法：通配符*使用通配符可以表示查询表中的所有列，无需逐列写出。SELECT*FROM表名核心要点与性能提示列的顺序：显示顺序与表定义顺序完全一致。使用场景：快速查看全表数据或不确定列名时。性能建议：开发时尽量明确列名，减少数据传输，提升效率。案例：查询全体学生的全部数据SELECT*FROMStudent分析：该语句返回Student表中所有学生的全部信息（学号、姓名、性别等）。结果列的顺序与建表时定义的列顺序相同，包含所有字段。3.3.2单表查询–选择计算列与列别名选择经过计算的值SELECT子句的目标列不仅可以是列名，还可以是算术表达式、字符串常量或函数。SELECTSno,Sname,2025–SageFROMStudent分析：表达式2026-Sage计算出学生的出生年份（结果中这一列没有列名）。定义列的别名使用AS关键字为表达式定义清晰的名称，AS可省略。SELECTSno,Sname,2026–SageAS出生年份FROMStudentSELECTSname姓名,Sno学号FROMStudent分析：案例2使用AS定义别名“出生年份”；案例3省略AS，直接使用空格分隔列名与别名。3.3.2单表查询–函数处理目标列选择经过函数处理的值在输出结果前，可以通过函数对其中的一个或多个列进行处理。SELECTsno,cno,ISNULL(grade,0)ASfinal_gradeFROMSC分析：如果选课表的成绩列中存在空值，则将其转换为0（ISNULL函数）并且重命名为final_grade后再输出。核心要点这里的函数都是指对单值进行处理的函数（如提取日期值中的年份，数字转字符串），或统计函数。SELECTSno,Sname,YEAR(GETDATE())–SageFROMStudentSELECTCOUNT(Sno)FROMStudent分析：案例2先用GETDATE函数获取当前日期，然后用YEAR函数从中提取年份，最后计算学生的出生年份；案例3用于统计学生的总人数；案例4使用LEN函数统计每个学生的名字的长度。SELECTSno,LEN(Sname)FROMStudent3.3.2单表查询–消除重复行（DISTINCT）基本语法在输出结果前，可以通过DISTINCT关键字去除结果中的重复行。SELECTDISTINCT列名1,列名2...FROM表名案例核心要点解析作用于所有列非单列去重，而是对查询列的整体组合进行唯一性校验。与ALL的区别默认选项是ALL(保留所有行)。1:查询有哪些院系SELECTDISTINCTsdeptFROMStudent2:查看每门课出现过哪些成绩SELECTDISTINCTCno,GradeFROMSC分析案例1中，若多个学生同属一个系，结果中该系名仅出现一次；案例2中，若某课程中出现多人考试成绩相同的情况，结果中仅保留一个成绩。3.3.2单表查询–条件查询：比较大小基本语法在WHERE子句中设置相应的条件（逻辑表达式）实现对行的筛选。SELECT目标列1...FROM表名WHER比较表达式案例常用比较运算符1:查询“计算机系”学生的学号和姓名SELECTSno,SnameFROMStudentWHEREsdept='计算机系'2:

查询年龄小于20岁的学生的姓名和年龄SELECTSname,SageFROMStudentWHERESage<20运算符含义说明=,>,<等于、大于、小于>=,<=大于等于、小于等于<>,!=不等于(部分数据库支持!=)3:查询成绩不等于85分的记录SELECTSname,SageFROMStudentWHERESage<>85提示：比较运算符是筛选数据最基础的工具，熟练掌握这些符号能帮助我们快速定位目标数据。3.3.2单表查询–条件查询：范围比较（BETWEEN）基本语法通过BETWEEN关键限定某些数值类型列的范围。SELECT列名1,列名2...FROM表名WHERE列名[NOT]

BETWEEN下限AND上限案例核心要点解析闭区间特定（包含边界）BETWEEN...AND...是闭区间，包含下限值和上限值本身，等价于>=AND<=。取反操作（NOT，不包含边界）使用NOTBETWEEN...AND...查询不在指定范围内的行，等价于<OR>。1:查询年龄在20到23岁之间的学生姓名和年龄SELECTSname,SageFROMStudentWHERESageBETWEEN20AND232:查询年龄不在20到23岁之间的学生姓名和年龄SELECTSname,SageFROMStudentWHERESageNOTBETWEEN20AND233:查询成绩在80到90分之间的记录SELECT*FROMSCWHEREGradeBETWEEN80AND903.3.2单表查询–条件查询：集合范围（IN）基本语法通过IN关键限定某些列的范围。SELECT列名1,列名2...FROM表名WHERE列名[NOT]

IN(值1,值2,…)案例核心要点解析1:查询多系部学生信息(IN)SELECT*FROMStudentWHEREsdeptIN('计算机系','信息系','数学系')2:排除特定课程(NOTIN)SELECT*FROMCourseWHERECnoNOTIN('001','002','003')3:筛选特定成绩记录(IN)SELECT*FROMSCWHEREGradeIN(85,90,95)集合定义括号内为多个值构成的集合，值之间用逗号分隔。逻辑等价IN运算符等价于多个OR条件的组合，写法更简洁。取反操作使用NOTIN来查询列值不属于指定集合的行。3.3.2单表查询–条件查询：字符串匹配（LIKE）基本语法与通配符通过LIKE关键字限定某些字符类型列的模式。SELECT列名1,列名2...FROM表名WHERE列名[NOT]

LIKE'匹配字符串'ESCAPE'转义字符'案例核心要点解析1:查询所有姓张的学生SELECTSnameFROMStudentWHERESnameLIKE'张%'2:查询第二个字是“小”的学生SELECTSnameFROMStudentWHERESnameLIKE'_小%'3:查询姓名中包含“伟”字的学生SELECTSnameFROMStudentWHERESnameLIKE'%伟%'通配符%——任意长度的字符串，_——单个字符。取反操作使用NOTLIKE来查询列值不符合指定模式的行。转移字符若要求条件列中包含通配符时，可以通过ESCAPE关键字来定义转义字符，从而将通配符转为普通字符。4:查课程名以“DB_”开头的课程（这里的_是普通字符）SELECT*FROMCourseWHERECnameLIKE'DB\_%'ESCAPE'\'3.3.2单表查询–条件查询：空值判断（IS[NOT]NULL）基本语法通过IS[NOT]NULL关键限定某些列是否为空值。SELECT列名1,列名2...FROM表名WHERE列名IS

[NOT]

NULL案例核心要点解析1:查询没有录入性别的的学生SELECTSnameFROMStudentWHERESsexISNULL2:查询已经录入成绩的选课记录SELECT*FROMSCWHEREGradeISNOTNULL严禁使用等号判断绝对不能用=NULL或<>NULL。取反操作使用NOT来查询列值不为空的行。NULL含义NULL表示“未知”或“不存在”，使用等号判断会返回UNKNOWN。错误示范：永远不会返回结果--错误写法SELECT*FROMStudentWHERESsex=NULL;3.3.2单表查询–条件查询：逻辑运算（AND/OR/NOT）基本语法通过逻辑运算符将若干条件表达式组合为逻辑表达式。SELECT列名1,列名2...FROM表名WHERE条件1AND/OR/NOT条件2案例核心要点解析运算符含义优先级NOT逻辑非，取反高AND逻辑与，全为真中OR逻辑或，任一为真低提示：可使用括号()强制改变运算优先级顺序。1:查询“计算机系”中年龄小于20岁的男生姓名SELECTSnameFROMStudentWHEREsdept='计算机系'ANDSage<20ANDSsex='男'2:查询“计算机系”学生或年龄在25岁以上的学生SELECT*FROMStudentWHEREsdept='计算机系'ORSage≥253:查询不是“计算机系”的学生SELECT*FROMStudentWHERENOT(sdept='计算机系')3.3.2单表查询–聚集函数核心概念：什么是聚合函数？对一组值进行计算并返回一个单一的值，常用于统计汇总，忽略NULL值。案例常用聚合函数函数功能说明COUNT(*)统计结果集中的行数SUM(列)计算指定列的总和AVG(列)计算指定列的平均值MAX/MIN找出指定列的最大值/最小值1:查询学生总人数(COUNT)SELECTCOUNT(*)FROMStudent2:查询选课学生人数(去重)SELECTCOUNT(DISTINCTSno)FROMSC3:计算指定课程的总成绩与平均分SELECTSUM(Grade),AVG(Grade)FROMSCWHERECno='C001'4:查询学生的最大/最小年龄SELECTMAX(Sage),MIN(Sage)FROMStudent3.3.2单表查询–分组统计（GROUPBY）核心概念：什么是聚合函数？案例核心要点解析1:查询每个学生的选课门数SELECTSno,COUNT(*)AS选课门数FROMSCGROUPBYSno2:查询各系学生人数与平均年龄SELECTdept,COUNT(*),AVG(Sage)FROMStudentGROUPBYsdept3:查询每门课的选课人数与最高分SELECTCno,COUNT(*),MAX(Grade)FROMSCGROUPBYCno通过GROUPBY关键字将查询的中间结果划分为若干组。SELECT[分组依据中的列],聚合函数(列)FROM表名[WHERE行筛选条件]GROUPBY分组依据分组依据：值相同的行归为一组，作为聚合统计的基础。SELECT子句限制：只能包含分组依据中的列（非必须）或聚合函数，不能有其他列。执行顺序：WHERE先筛选行，再执行GROUPBY进行分组统计。总结：通过GROUPBY子句配合COUNT,AVG,MAX等聚合函数，可以灵活实现各类数据的分类统计需求。3.3.2单表查询–分组筛选（HAVING）基本语法通过HAVING子句对分组的结果进行筛选（操作对象是“组”）。SELECT[分组依据中的列],聚合函数(列)FROM表名[WHERE条件]GROUPBY分组依据HAVING组筛选条件案例核心区别：WHEREvsHAVING1:筛选选课门数大于等于3的学生姓名SELECTSno,COUNT(*)FROMSCGROUPBYSnoHAVINGCOUNT(*)≥32:筛选平均成绩大于85的课程信息SELECTCno,AVG(Grade)FROMSCGROUPBYCnoHAVINGAVG(Grade)>853:WHERE与HAVING结合使用:统计至少选修2门课程（不考虑008号选修课）的学生的平均成绩，列出学号和平均成绩。SELECTSno,AVG(Grade)FROMSCWHERECno<>'008'GROUPBYSnoHAVINGAVG(Grade)>85ANDCOUNT(cno)>2WHERE(行筛选):分组前执行，不能使用聚合函数。HAVING(组筛选):分组后执行，必须使用聚合函数时使用。二者均不能使用别名，这是为什么呢？3.3.3连接查询（多表查询）核心作用：从两个或多个表中基于关联列（通常是主键和外键）查询数据，整合分散的信息。内连接(INNERJOIN)只返回两个表中满足连接条件的记录。显式：SELECT...FROMAINNERJOINBONA.col=B.col等价形式：SELECT...FROMA,BWHEREA.col

=

B.col左外连接(LEFT[OUTER]JOIN)返回左表所有记录+右表匹配记录，无匹配的列取空值。语法：SELECT...FROMALEFTJOINBONA.col=B.col;右外连接(RIGHT[OUTER]JOIN)返回右表所有记录+左表匹配记录，无匹配的列取空值。语法：SELECT...FROMARIGHTJOINBONA.col=B.col;全外连接(FULL[OUTER]JOIN)返回两表所有记录，无匹配的列取空值。MySQL不支持。语法：SELECT...FROMAFULLJOINBONA.col=B.col注意：若多个表存在同名列，必须明确指出其来源，否则会报错。交叉连接(CROSSJOIN)实现了关系代数中的广义笛卡尔积（两个表中的行进行组合）。显式：SELECT...FROMACROSSJOINB隐式：SELECT...FROMA,B3.3.3连接查询–交叉连接（CROSSJOIN）语法结构方式一：SELECT*FROM表1CROSSJOIN表2方式二：SELECT*FROM表1,表2案例注意事项1:查询所有学生和所有课程的笛卡尔积SELECTS.Sname,C.CnameFROMStudentSCROSSJOINCourseC2:通过交叉连接模拟内连接SELECTS.Sname,SC.Cno,SC.GradeFROMStudentSCROSSJOINSCWHERES.Sno=SC.Sno交叉连接的结果集是两个表的笛卡尔积，行数是两表行数的乘积，结果集通常非常庞大。在实际应用中极少直接使用，通常需要与WHERE子句结合来过滤数据，从而实现其他类型的连接。方法二（逗号分隔）在语义上等同于CROSSJOIN，但代码可读性差，不推荐使用。提示：在实际开发中，非必要不要使用交叉连接。3.3.3连接查询–内连接（INNERJOIN）语法结构SELECT*FROM表1INNERJOIN表2ON表1.A=表2.B案例注意事项1:查询每个学生的姓名、所选课程号和成绩（不考虑未选课情况）SELECTS.Sname,SC.Cno,SC.GradeFROMStudentSINNERJOINSCONS.Sno=SC.Sno2:查询每个学生的姓名、所选课程的名称以及对应的成绩SELECTS.Sname,C.Cname,SC.GradeFROMStudentSINNERJOINSCONS.Sno=SC.SnoINNERJOINCourseCONSC.Cno=C.CnoINNERJOIN是最常用的连接类型，只返回两个表中满足连接条件的行。若需要完成三表内连接，只需要在双表内连接后继续INNERJOIN…ON即可。SQLServer不支持NATURALJOIN（自然连接），需要通过内连接实现自然连接。即使其他数据库支持NATURALJOIN，也不推荐使用：（1）隐式匹配列可能导致意外结果（比如表中新增了同名但无关的列，会被错误匹配）；（2）代码可读性差，维护者无法快速看出连接条件。提示：直接使用内连接的效率要高于使用交叉连接的等价形式。3.3.3连接查询–左外连接（LEFT[OUTER]JOIN）基本语法SELECT*

FROM左表LEFTOUTERJOIN右表ON左表.Ａ=右表.Ｂ--

OUTER关键字可以省略案例注意事项1:查询所有学生的选课情况，包括未选课的SELECTS.Sname,SC.Cno,SC.GradeFROMStudentSLEFTJOINSCONS.Sno=SC.Sno2:查询没有选课的学生SELECTCno,AVG(Grade)FROMSCGROUPBYCnoHAVINGAVG(Grade)>853:查询所有学生及其所选课程的详细信息SELECTS.Sname,C.Cname,SC.GradeFROMStudentSLEFTJOINSCONS.Sno=SC.SnoLEFTJOINCourseCONSC.Cno=C.Cno保证左表的所有行都被返回，无论在右表中是否能找到匹配项。如果右表中没有匹配的行，结果集中右表的列将显示为NULL。3.3.3连接查询–右外连接（RIGHT[OUTER]JOIN）基本语法SELECT*

FROM左表RIGHTOUTERJOIN右表ON左表.Ａ=右表.Ｂ--

OUTER关键字可以省略案例注意事项1:列出所有课程及选修学生，包括未被选课程SELECTC.Cname,S.SnameFROMStudentSRIGHTJOINSCONS.Sno=SC.SnoRIGHTJOINCourseCONSC.Cno=C.Cno2:查询没有被选的课程，仅列出成绩表中无记录的课程SELECTC.CnameFROMSCRIGHTJOINCourseCONSC.Cno=C.CnoWHERESC.CnoISNULL3:案例1的左外连接等价形式SELECTC.Cname,S.SnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoLEFTJOINStudentSONSC.Sno=S.Sno右外连接保证右表的所有行都被返回，无论在左表中是否能找到匹配项。如果左表中没有匹配的行，结果集中左表的列将显示为NULL。任何右外连接都可以通过交换两个表的位置，改写为左外连接。3.3.3连接查询–全外连接（FULL[OUTER]JOIN）基本语法SELECT*FROM表1FULLOUTERJOIN表2ON表1.关联列=表2.关联列--

OUTER关键字可以省略案例注意事项1:查询所有学生的选课情况，包括未选课的学生和未被选课程SELECTS.Sname,C.Cname,SC.GradeFROMStudentSFULLOUTERJOINSCONS.Sno=SC.SnoFULLOUTERJOINCourseCONSC.Cno=C.Cno全外连接在日常业务开发中使用频率高，远低于INNERJOIN和LEFTJOIN。并非所有数据库都支持FULLOUTERJOIN，例如MySQL。3.3.4子查询核心作用：将一个查询的结果（单值或集合）作为另一个查询的一部分。分类相关子查询查询条件依赖于外部父查询某些值的子查询（即子查询用到了父查询的数据（当前行））。（无法独立执行）exists谓词引出的子查询多属于此类。此类子查询在父查询执行过程中会被多次执行。不相关子查询不依赖外部父查询的子查询。（可以独立执行）In谓词引出的子查询多属于此类。此类子查询一般只执行一次，执行结果交给父查询使用。基本概念将一个SELECT-FROM-WHERE结构（查询块）嵌套在另一个查询语句中，称为嵌套查询，也叫子查询。嵌套的位置可以是Select子句、From子句、Where子句或是Having子句。3.3.4子查询-作为目标列核心概念与特点功能定义将子查询结果作为SELECT子句的目标列，返回单一值（标量子查询），作为结果集中的一列展示。关键特点必须返回且仅返回一行一列，否则报错。常用于行内计算或获取额外的聚合信息。案例1：查询每个学生的基本信息及选课数量，列出学号、姓名、性别和选课门数SELECTsno,sname,ssex,(SELECTCOUNT(*)FROMSCWHERESC.sno=Student.sno)AS选课数量FROMStudent说明：为每行学生计算其所在班级的总人数，作为新列展示。2：查询课程名称及其最高分SELECTC.Cname,(SELECTMAX(Grade)FROMSCWHERECno=C.Cno)AS最高分FROMCourseC;说明：为每门课程关联其最高成绩，实现信息的横向补充。3.3.4子查询–作为数据来源核心概念与特点核心功能子查询出现在FROM子句中，结果集被视为一个临时表（导出表）。父查询可像操作普通表一样进行查询。关键特点可返回多行多列的结果集必须为临时表指定别名（Alias），否则无法引用1：查询平均分大于80的学生信息SELECTS.Sname,AvgGrade.平均分FROMStudentSINNERJOIN(SELECTSno,AVG(Grade)AS平均分FROMSCGROUPBYSno)ASAvgGradeONS.Sno=AvgGrade.SnoWHEREAvgGrade.平均分>80;2：统计各课程选课人数与最高分SELECTC.Cname,Stats.选课人数,Stats.最高分FROMCourseCINNERJOIN(SELECTCno,COUNT(*)AS选课人数,MAX(Grade)AS最高分FROMSCGROUPBYCno)ASStatsONC.Cno=Stats.Cno;3.3.4子查询–作为查询条件核心作用：将一个查询的结果（单值或集合）作为外层查询的筛选条件出现在WHERE或HAVING子句中，实现复杂筛选。使用方法标量比较：子查询返回一个单值（一行一列），将其作为一个常量单值参与条件逻辑表达式。集合比较：子查询返回一个集合（包含1个或多个值），由于集合无法直接参与比较，需要通过关键字SOME/ANY/ALL来进行比较。IN谓词：子查询返回一个集合，将其置于IN谓词后面作为条件的一部分，一般作为不相关子查询出现。EXISTS谓词：从“当前行”行中取出一个或者多个值，将这些值作为常量带入一个子查询（相关子查询）并执行；然后检查执行结果是否为空，若不为空则满足条件，否则不满足（NOTEXISTS谓词正好相反）。3.3.4子查询-作为查询条件(1/4)核心概念：标量子查询与比较运算符基本功能当子查询返回单一值（一行一列）时，可直接使用比较运算符（=,>,<,>=,<=,<>）将主查询列与子查询结果进行比较。关键约束必须是标量子查询。如果子查询返回多行或多列，会产生运行时错误。这是最直接的子查询用法之一。案例1:查询与26001号学生同龄的学生学号和姓名SELECTSno,SnameFROMStudentWHERESage=(SELECTSageFROMStudentWHERESno='26001')2:查询成绩高于“数据库原理”平均分的选课记录SELECT*FROMSCWHEREGrade>(SELECTAVG(Grade)FROMSCINNERJOINCourse

ONSC.Cno=Course.CnoWHERECname='数据库原理')条件改为Sanme='张三’是否可行？3.3.4子查询-作为查询条件(2/4)多值比较：ANY（SOME）与ALL关键字>ANY：大于子查询结果中的任意一个值（即大于最小值）>ALL：大于子查询结果中的所有值（即大于最大值）<ANY：小于子查询结果中的任意一个值（即小于最大值）<ALL：小于子查询结果中的所有值（即小于最小值）<>ALL：功能上等同于NOTIN关键字案例1:查询成绩高于“张三”任意一门课的学生学号和姓名SELECTS.Sno,SnameFROMStudentASSJOINSCONS.Sno=SC.SnoWHERESC.Grade>ANY(SELECTGradeFROMSCWHERESnoIN(SELECTSnoFROMStudentWHERESname='张三'))2:

查询比所有计算机系学生年龄都大的学生学号、姓名和年龄SELECTSno,Sname,SageFROMStudentWHERESage>ALL(SELECTSageFROMStudentWHERESdeptLIKE'计算机系')=ANY：功能上等同于IN关键字3.3.4子查询-作为查询条件(3/4)IN谓词核心功能判断值是否存在于子查询返回的集合中，效果等同于`=ANY`。基本语法WHERE列名IN(子查询)特点与扩展实现“属于”或“包含于”逻辑。反向操作可使用`NOTIN`，用于判断值是否不在集合中。案例1：查询选修了“数据库原理”或“操作系统”的学生姓名SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECnameIN('数据库原理','操作系统')))说明：三层嵌套，从课程名->课程号->学号->学生姓名，层层筛选。2：查询没有选修任何课程的学生姓名(等价于<>ALL)SELECTSnameFROMStudentWHERESnoNOTIN(SELECTDISTINCTSnoFROMSC)说明：先获取所有有选课记录的学号集合，再筛选出不在此集合中的学生。3.3.4子查询-作为查询条件(4/4)EXISTS谓词核心机制▍功能定义判断子查询是否返回至少一行数据。若存在记录返回TRUE，否则返回FALSE。▍关键特性常用于实现“存在性”判断，是一种相关子查询。子查询SELECT通常用*或者1，只关心是否有记录。子查询会引用外部父亲查询的列（如S.Sno）。对应逻辑非操作：NOTEXISTS。案例1：查询选修了课程'001'的学生SELECTSnameFROMStudentASSWHEREEXISTS(SELECT*FROMSCWHERESC.Sno=S.SnoANDCno='001')2：查询没有选修任何课程的学生(NOTEXISTS)SELECTSnameFROMStudentSWHERENOTEXISTS(SELECT*FROMSCWHERESC.Sno=S.Sno);3.3.5集合查询核心概念与本质什么是集合查询？将多个SELECT语句的结果集进行集合运算（并、交、差），生成新的结果集。核心本质操作对象是“结果集”（关系），而非直接操作表。这与连接查询从行的角度组合数据不同。运算前提条件列数必须相同参与运算的SELECT语句返回的列数必须一致，顺序也要一致。列类型兼容对应位置的列数据类型需相同或可隐式转换。列名的决定最终结果集的列名由第一个SELECT语句决定。三种基本运算并运算(UNION)合并两个结果集，自动去除重复记录。交运算(INTERSECT)获取两个结果集中都存在的共同记录。差运算(EXCEPT)获取在第一个结果集存在但第二个不存在的记录。3.3.5集合查询-并运算(UNION)语法结构SELECTcol1,col2FROMtable1[WHEREcond1]UNION[ALL]SELECTcol1,col2FROMtable2[WHEREcond2];核心要点UNION：合并结果集，自动去除重复记录。UNIONALL：保留所有记录(含重复)，执行效率更高。案例分析(SQLServer)1:查询计算机系和数学系学生信息(去重)SELECT*FROMStudentWHERESclass='计算机系'UNIONSELECT*FROMStudentWHERESclassLIKE'数学系'2:查询选修001或002号课程的学号(保留重复)SELECTSnoFROMSCWHERECno='001'UNIONALLSELECTSnoFROMSCWHERECno='002'3:查询选修001或002的学生学号SELECTSnoFROMSCWHERECno='001'UNIONSELECTSnoFROMSCWHERECno='002'3.3.5集合查询-交运算(INTERSECT)语法结构SELECT列1,列2FROM表1[WHERE条件1]INTERSECTSELECT列1,列2FROM表2[WHERE条件2];核心要点功能：返回两个结果集的交集（同时存在的记录）。去重：结果集自动去除重复记录。兼容性：MySQL不支持该关键字，需用IN或JOIN模拟。案例1：查询同时选修001和002的学生SELECTSnoFROMSCWHERECno='001’INTERSECTSELECTSnoFROMSCWHERECno='002'2：计算机系且年龄大于20的学生SELECT*FROMStudentWHERESdept='计算机系'

INTERSECTSELECT*FROMStudentWHERESage>203：案例1的MySQL模拟实现（使用IN）SELECTDISTINCTSnoFROMSCWHERECno='001'AND

SnoIN(SELECTSnoFROMSCWHERECno='002')3.3.5集合查询-差运算(EXCEPT)语法结构SELECT列FROM表1[WHERE条件1]EXCEPTSELECT列FROM表2[WHERE条件2];核心特性顺序敏感：结果集=集合A-集合B。A和B的顺序直接决定结果。自动去重：EXCEPT结果集默认去除重复记录，无需额外DISTINCT。兼容性：MySQL不支持。案例1:选了001但没选002的学生学号SELECTSnoFROMSCWHERECno='001'

EXCEPTSELECTSnoFROMSCWHERECno='002'2:找出所有没有选课的学生学号SELECTSnoFROMStudentEXCEPTSELECTDISTINCTSnoFROMSC3:案例1的MySQL模拟实现(使用NOTIN)SELECTSnoFROMSCWHERECno='001'ANDSno

NOTIN(SELECTSnoFROMSCWHERECno='C002')3.3.6查询执行顺序与优化查询执行顺序01.FROM/JOIN-确定数据源，执行表连接02.WHERE-分组前筛选行数据03.GROUPBY-按指定列进行数据分组04.HAVING-筛选满足条件的分组05.SELECT-选择返回列，执行聚合计算06.DISTINCT-去除结果集中的重复记录07.ORDERBY-对结果集进行排序08.TOP-限制返回结果的行数核心优化原则合理使用索引为WHERE/JOIN列建索引；避免索引列函数运算；定期维护索引。优化查询语句本身拒绝SELECT*；优化JOIN类型与条件；合理使用EXISTS替代IN。关注数据量与执行计划尽早使用TOP减少数据量。其他通用原则优化数据物理存储（如分区）。3.3.7插入数据-标准语法与核心要点标准语法格式INSERTINTO表名(列1,列2,...)VALUES(值1,值2,...)显式指定列名（可省略部分列名），值与列名顺序必须一一对应。INSERTINTO表名VALUES(值1,值2,...)省略列名列表，必须按表结构顺序提供所有列的值。核心要点与最佳实践严格的对应关系值的数量、顺序和数据类型必须与列名列表严格匹配，否则插入失败。慎用省略列名写法省略列名虽简洁但可读性差。若表结构变更（如新增列），语句会立即报错，维护成本高。部分被省略列名的处理这些被省略的列必须能够自动获取值，否则会报错。具体方式包括：该列定义了默认值（DEFAULT）；该列允许为NULL；该列是自增列（AUTO_INCREMENT/IDENTITY）。3.3.7插入数据-批量插入与查询结果插入批量插入一次性插入多条记录，显著减少数据库交互开销，提升效率。INSERTINTO表名(col1,col2)VALUES(v1a,v2a),(v1b,v2b),(v1c,v2c)核心要点：VALUES后接多个括号，每个括号代表一条记录所有记录的列名列表和数据类型必须保持一致适用于大数据量插入，减少网络往返次数查询结果插入将SELECT查询的结果集直接插入目标表，常用于数据迁移与汇总。INSERTINTO目标表名(col1,col2)SELECTs_col1,s_col2FROM来源表名WHERE条件核心要点：结果集的列数、顺序和类型必须与目标列完全兼容支持复杂查询（多表连接、聚合），目标表需预先存在数据备份、汇总或迁移的高效手段3.3.7插入数据-案例分析1：指定所有列INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('2023001','张三','男',20,'计算机系')2：省略部分列INSERTINTOStudent(Sno,Sname,Ssex,Sage)VALUES('2023002','李四','女',19)3：省略整个列名列表INSERTINTOStudentVALUES('2023003','王五','男',21,'数学系')4：批量插入INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('2023004','赵六','女',20,'外语系'),('2023005','孙七','男',22,'物理系')5：插入查询结果INSERTINTOSCSELECTSno,Cno,NULLASGradeFROMStudentCROSSJOINCourseWHERESdept='计算机系'ANDCname='数据库原理'ANDNOTEXISTS(SELECT1FROMSCWHERESno=Student.SnoANDCno=Course.Cno)3.3.8修改数据-单表数据修改标准语法结构UPDATE表名SET列名1=值1,列名2=值2,...[WHERE条件]核心执行逻辑SET子句：指定要修改的列及其新值。WHERE子句：筛选更新范围。强烈建议使用，否则更新全表！执行顺序：先筛选(WHERE)，后更新(SET)。1：修改单列(姓名)UPDATEStudentSETSname='张三丰'WHERESno='2023001'