《数据库技术与SQL综合复习题》课件

数据库技术与综合复习题SQL本课件涵盖数据库原理及语言的基础知识，集成了精选的道练习题SQL100SQL及详细解析，帮助学习者全面掌握数据库理论与实践技能本资料为年20255月更新版，包含最新的数据库技术发展与应用案例通过系统学习这套教材，您将能够深入理解数据库系统的核心概念，熟练掌握语言的各种操作技巧，并能够应用这些知识解决实际工作中的数据库设SQL计与查询优化问题课程概述数据库基础概念与理论语言基本语法与应用SQL深入讲解数据库系统架构、数据模型、关系代数等理论基础，全面介绍的四大组成部分数据定义语言、数据操SQL DDL建立系统化的数据库知识框架纵语言、数据查询语言和数据控制语言DML DQLDCL数据库设计原则与实践综合习题与答案解析讲解数据库设计的标准流程、规范化理论以及实际应用中的精选各类型习题，从基础到高级，配合详细解析，帮助巩固性能优化策略所学知识并提升应用能力第一部分数据库基础概念关系代数基础选择、投影、连接等操作数据模型的类型与特征概念、逻辑、物理三大模型数据库系统的组成与特点数据、硬件、软件与用户数据库基础概念部分是理解整个数据库技术体系的基石通过学习数据库系统的组成结构、各类数据模型的特点以及关系代数的基本操作，能够建立起系统化的数据库知识框架，为后续学习提供坚实的理论支撑数据库系统概述数据库定义与发展历史数据库是按照数据结构来组织、存储和管理数据的仓库，从最早的层次模型、网状模型发展到如今的关系模型、对象关系模型以及NoSQL数据库数据库系统的三级模式结构包括外模式（用户视图）、概念模式（全局视图）和内模式（存储视图），这种结构实现了数据的物理独立性和逻辑独立性数据库管理系统的功能DBMS包括数据定义、数据操纵、数据库的建立与维护、数据库的重组与重构以及数据库的安全性、完整性控制等数据库系统的特点实现数据共享、减少数据冗余、保证数据一致性、提供数据独立性以及实现数据的集中控制，大大提升了数据管理的效率和安全性数据模型分类逻辑数据模型包括层次模型、网状模型和关系模型，其概念数据模型中关系模型因其简单直观的特点成为当今物理数据模型主流数据库的基础理论模型以图为代表，通过实体、属性、联系来E-R描述现实世界，是现实世界到信息世界的描述数据在存储介质上的组织方式，包括第一层抽象，主要用于数据库设计的概念数据的存储结构与访问方法，直接关系到阶段数据库系统的性能表现关系数据库模型关系基本概念关系完整性约束关系（表）是关系模型的核心，包含实体完整性（主键非空且唯由元组（行）和属性（列）组成一）、参照完整性（外键取值要每个属性都有其对应的域（取值么空值要么等于被参照关系的主范围），关系模式则是对关系的键值）以及用户定义完整性（用描述，包含关系名和属性集合户定义的特定业务规则）关系代数操作包括选择（筛选行）、投影（筛选列）、连接（多表合并）、除（特殊查询）以及集合操作（并、差、交），这些操作构成了查询语言的理SQL论基础数据库设计步骤需求分析阶段全面了解用户需求，分析数据特征和处理要求，明确系统功能和性能目标，形成需求规格说明书概念设计阶段基于需求分析结果，建立数据库的E-R模型，使用实体、属性、联系描述现实世界，独立于具体的数据库管理系统逻辑设计阶段将E-R模型转换为特定数据库管理系统支持的数据模型（如关系模型），定义表结构、字段、键和约束等4物理设计阶段根据具体的数据库产品，确定存储结构、访问方法、索引设计等，优化数据在物理设备上的组织方式实现与维护阶段创建数据库、建立表、装载数据并进行测试，投入使用后还需持续监控性能并根据需要进行优化和调整实体联系模型模型-E-R基本概念图表示转换规则E-R模型是由美国计算机科学家陈于图使用特定符号表示模型中的各个元从图转换到关系模式遵循一系列规则E-R P.P.S.E-R E-R年提出的，用于描述现实世界的概素1976念模型，由三个基本概念组成矩形表示实体集每个实体转换为一个关系模式••实体客观存在并可相互区分的事物•椭圆表示属性联系可合并到任一方关系模式••1:1菱形表示联系集联系通常合并到端关系模式••1:N N属性实体所具有的特性或性质•线段连接实体与属性或实体与联系联系需创建独立的关系模式••M:N联系不同实体间的相互关联•关系数据库设计理论函数依赖属性间的制约关系，是规范化的理论基础1NF→2NF→3NF基本规范形式，消除部分依赖和传递依赖BCNF→4NF3高级规范形式，进一步优化数据库设计模式分解保持无损连接和依赖关系的分解策略关系数据库设计理论是构建高质量数据库的理论基础函数依赖描述了属性之间的约束关系，包括完全函数依赖、部分函数依赖和传递函数依赖规范化过程通过消除这些依赖关系，将关系模式逐步转换为更高级的范式，减少数据冗余，避免异常操作第二部分语言基础SQL数据定义语言DDL数据操纵语言DML用于定义数据库对象如表、视图、索引用于对数据库中的数据进行增删改操作等的结构数据控制语言数据查询语言DCL DQL用于控制数据库的访问权限和安全性用于从数据库中检索所需数据语言是关系数据库的标准语言，它的强大之处在于通过简洁明了的语法实现对数据的全面管理掌握的四大组成部分，是数据SQL SQL库应用开发和管理的基础技能通过系统学习，能够熟练运用语言进行各种数据库操作SQL语言概述SQL非过程化特性与编程语言的区别是一种非过程化语言，用语言侧重于数据操作，而SQL SQL户只需指定做什么而不必指等编程语言侧重于算法实Java定怎么做，系统会自动完成现专注于数据，程序SQL查询优化和执行计划生成，大语言专注于行为，两者结合大降低了使用难度使用才能构建完整的应用系统语言特点具有简单易学、功能强大、可移植性好的特点，既可以作为独立的SQL交互式查询语言，也可以嵌入到程序中使用，成为数据库领域的通用标准数据定义DDL创建数据库创建和修改表CREATE DATABASE学校数据库--创建表ON PRIMARYCREATE TABLE学生表NAME=学校数据,FILENAME=D:\数据\学校.mdf,学号CHAR10PRIMARY KEY,SIZE=10MB,姓名VARCHAR20NOT NULL,MAXSIZE=50MB,性别CHAR2CHECK性别IN男,女,FILEGROWTH=5MB出生日期DATE,LOG ON所在系VARCHAR30DEFAULT计算机系NAME=学校日志,;FILENAME=D:\数据\学校_log.ldf,SIZE=5MB,--修改表MAXSIZE=25MB,ALTER TABLE学生表FILEGROWTH=5MB;ADD联系电话VARCHAR15;--删除表DROP TABLE学生表;数据类型与约束类型分类具体类型存储范围应用场景数字类型INT,FLOAT,DECIMAL INT:-2^31~2^31-1精确小数整数计数、金额计算字符串类型固定长度DECIMAL:CHAR,VARCHAR,TEXT CHAR:可变长度姓名、地址、描述日期时间类型仅日期VARCHAR:DATE,TIME,DATETIME DATE:日期和时间生日、交易时间约束类型DATETIME:PRIMARY KEY,FOREIGN KEY唯一标识UNIQUE,CHECK,DEFAULT PRIMARY KEY:引用完整性数据完整性保护FOREIGN KEY:选择合适的数据类型和约束对于优化数据库性能和保证数据完整性至关重要数据类型影响存储空间和查询效率，而约束则确保数据的有效性和一致性设计数据库时应仔细考虑每个字段的特性，选择最合适的类型和约束组合数据操作DML插入数据INSERT INTO更新数据UPDATE--插入单行数据--更新单个字段INSERT INTO学生表学号,姓名,性别,出生日期UPDATE学生表VALUES2023001,张三,男,2003-05-12;SET所在系=信息工程系WHERE学号=2023001;--插入多行数据INSERT INTO学生表学号,姓名,性别--更新多个字段VALUES2023002,李四,男,UPDATE学生表2023003,王五,女;SET所在系=信息工程系,联系电话=13812345678WHERE学号LIKE2023%;删除数据与事务控制--删除数据DELETE FROM学生表WHERE学号=2023003;--事务控制BEGIN TRANSACTION;UPDATE账户表SET余额=余额-100WHERE账号=A001;UPDATE账户表SET余额=余额+100WHERE账号=A002;COMMIT;--提交事务--ROLLBACK;--回滚事务基本查询语句简单查询条件查询与排序分页查询--查询所有学生信息--条件查询--SQL Server分页查询SELECT*FROM学生表;SELECT学号,姓名,所在系SELECT学号,姓名FROM学生表FROM学生表--查询指定列WHERE所在系=计算机系ORDER BY学号SELECT学号,姓名,所在系AND性别=女;OFFSET10ROWSFROM学生表;FETCH NEXT10ROWS ONLY;--排序查询SELECT学号,姓名,出生日期--MySQL分页查询FROM学生表SELECT学号,姓名简单查询是数据库操作中最基础的查询形式，用于ORDER BY出生日期DESC;FROM学生表检索表中的全部或部分列数据通过合理选择需要ORDER BY学号查询的列，可以减少数据传输量，提高查询效率LIMIT10,10;WHERE子句用于过滤数据，可以使用比较运算符、逻辑运算符等构建复杂的过滤条件ORDER BY子句则用于对结果集进行排序，可以按一个或多个列进分页查询用于限制查询结果的数量，特别适用于数行升序或降序排列据量大的表不同数据库系统有不同的分页语法，但基本原理相同，都是通过限定起始位置和返回记录数来实现分页功能聚合函数与分组聚合函数分组过滤使用区别GROUP BYHAVINGSQL提供了多种聚合函数用于GROUP BY子句用于将数据按照HAVING子句用于过滤分组后的WHERE与HAVING的主要区别统计和计算数据指定的列进行分组，每组可以结果，类似于WHERE，但作用应用聚合函数于分组结果•COUNT:计算记录数或非•WHERE在分组前过滤记录NULL值的数量•HAVING在分组后过滤结果SELECT所在系,SELECT所在系,•SUM:计算数值列的总和•WHERE不能使用聚合函数COUNT*AS学生人数COUNT*AS学生人数•AVG:计算数值列的平均FROM学生表FROM学生表•HAVING可以使用聚合函数值GROUP BY所在系;GROUP BY所在系•MAX/MIN:找出最大值和HAVING COUNT*30;最小值多表连接查询内连接仅返回两表中匹配的行SELECT学生表.姓名,成绩表.分数FROM学生表INNER JOIN成绩表ON学生表.学号=成绩表.学号;外连接返回一个表的所有行以及另一个表中的匹配行SELECT学生表.姓名,成绩表.分数FROM学生表LEFT JOIN成绩表ON学生表.学号=成绩表.学号;自连接表与自身进行连接SELECT a.姓名AS员工,b.姓名AS上级FROM员工表aJOIN员工表bON a.上级编号=b.员工编号;子查询与嵌套查询单行子查询多行子查询返回单一值的子查询，常用于比较操作返回多个值的子查询，常与IN、ANY、ALL等运算符配合使用--查询比平均成绩高的学生--查询选修了数据库课程的学生SELECT学号,姓名,成绩SELECT学号,姓名FROM成绩表FROM学生表WHERE成绩WHERE学号IN SELECTAVG成绩SELECT学号FROM成绩表FROM选课表;WHERE课程名=数据库;相关子查询内层查询引用外层查询的表，两者相互关联--查询每个系成绩最高的学生SELECT s.学号,s.姓名,s.所在系,c.成绩FROM学生表sJOIN成绩表c ONs.学号=c.学号WHERE c.成绩=SELECT MAX成绩FROM成绩表c2JOIN学生表s2ON c

2.学号=s

2.学号WHERE s

2.所在系=s.所在系;视图与索引视图索引视图是基于SQL查询的虚拟表，不存储实际数据，但可以像表一样被查询视图具有多种优势索引是数据库中用于提高查询性能的数据结构，类似于书的目录合理使用索引可以•加速数据检索操作•简化复杂查询，提高开发效率•实现数据的唯一性约束•增强数据安全性，只显示授权数据•优化排序和分组操作•提供数据独立性，屏蔽底层表结构变化--创建索引--创建视图CREATE INDEX idx_学生_姓名CREATE VIEW计算机系学生视图AS ON学生表姓名;SELECT学号,姓名,性别FROM学生表--创建唯一索引WHERE所在系=计算机系;CREATE UNIQUEINDEXidx_学生_身份证ON学生表身份证号;--使用视图SELECT*FROM计算机系学生视图;--创建复合索引CREATE INDEXidx_学生_系别_性别--删除视图ON学生表所在系,性别;DROP VIEW计算机系学生视图;--删除索引DROP INDEXidx_学生_姓名;第三部分数据库综合应用数据库综合应用部分主要介绍数据库系统中的高级功能，包括存储过程、触发器、事务控制以及安全管理这些功能共同构成了现代数据库系统的核心架构，使得数据库不仅能够安全地存储数据，还能高效地处理复杂的业务逻辑通过学习这部分内容，可以掌握如何利用数据库的高级特性来实现业务需求，提高系统的性能、安全性和可维护性这些知识对于构建企业级应用系统至关重要存储过程概念与语法存储过程是一组预编译的SQL语句集合，存储在数据库中，可以接受参数、执行操作并返回结果它类似于编程语言中的函数，但直接在数据库服务器上执行，提高了执行效率和安全性参数类型存储过程支持三种参数类型IN（输入参数，默认类型）、OUT（输出参数，用于返回值）和INOUT（既可输入也可输出的参数）通过灵活使用不同类型的参数，可以实现复杂的数据处理逻辑创建与调用创建存储过程使用CREATE PROCEDURE语句，定义参数和SQL语句；调用存储过程使用EXEC或CALL语句，传入必要的参数存储过程支持变量声明、条件判断、循环结构等编程元素，可以实现复杂的业务逻辑优缺点存储过程的主要优点包括提高执行效率、减少网络流量、增强安全性和提高代码重用性；缺点则包括可移植性较差、调试困难以及增加数据库服务器负担在系统设计时需权衡这些因素触发器触发器概念触发器类型特殊的存储过程，自动响应数据库事件触发器和触发器两大类DML DDL2行级与语句级应用场景行级对每行执行，语句级对整个操作执数据验证、自动计算、记录变更等行一次触发器是数据库对象的一种，与表关联并在指定事件发生时自动执行它可以在、或操作之前或之后触发，用于INSERT UPDATEDELETE实现复杂的业务规则、维护数据完整性、自动更新冗余数据以及审计跟踪等功能触发器虽然强大，但过度使用会导致数据库性能下降，因此需谨慎设计事务管理事务隔离级别读未提交、读已提交、可重复读、串行化事务的特性ACID原子性、一致性、隔离性、持久性事务控制语句、、、BEGIN COMMITROLLBACK SAVEPOINT事务是数据库操作的基本单位，由一组逻辑相关的数据库操作组成，要么全部执行成功，要么全部失败回滚事务的特性确保了数据库在ACID并发访问和故障恢复情况下的数据一致性和完整性合理设置事务隔离级别可以平衡数据一致性和并发性能，而正确使用事务控制语句则是确保业务逻辑正确执行的关键在事务管理中，死锁是一个常见问题，指两个或多个事务互相等待对方释放资源的情况数据库系统通常会自动检测和解决死锁，但良好的应用设计应当尽量避免死锁的发生，如按固定顺序访问资源、减少事务持续时间等数据库安全与权限用户与角色管理数据库安全的第一层防护是建立有效的用户和角色体系用户代表连接数据库的个体身份，而角色则是权限的集合，可以分配给多个用户通过创建适当的角色并为用户分配合适的角色，可以实现权限的集中管理和精细控制授权与撤销通过GRANT和REVOKE语句，可以为用户或角色授予和撤销数据库对象的访问权限权限可以精确到表、视图、存储过程甚至列级别，并可以指定是否允许将权限传递给其他用户，形成完整的权限控制链数据加密与备份敏感数据应当采用加密存储，包括传输加密、存储加密和列级加密等多种方式同时，定期备份数据库并测试恢复过程，是防止数据丢失和确保业务连续性的关键措施审计与日志数据库审计功能可记录用户活动和敏感操作，有助于追踪操作历史、发现异常行为并满足合规性要求日志管理则包括错误日志、事务日志等，对于故障排除和安全事件调查至关重要数据库结构SQL Server数据库文件组成系统数据库数据库管理SQL Server数据库由三种类型的SQL Server包含四个重要的系统SQL Server提供多种管理工具文件组成数据库•主数据文件.mdf包含数•master记录所有系统级•SQL ServerManagement据库启动信息和系统表信息StudioSSMS•次数据文件.ndf存储用•model新建数据库的模板•T-SQL命令行户数据，可跨磁盘分布•msdb SQL Server代理使•动态管理视图DMV•日志文件.ldf记录事务用的数据库•性能监视器日志，用于恢复操作•tempdb存储临时对象和中间结果分离与附加分离与附加是数据库迁移的重要方法•sp_detach_db将数据库从实例中分离•CREATE DATABASE...FORATTACH将现有数据库文件附加到实例•适用于数据库迁移和备份恢复场景第四部分高级查询技巧高级函数SQL掌握字符串、日期和聚合函数的高级用法查询优化通过执行计划分析和索引策略提升性能复杂查询构建多表连接、子查询和递归查询的高级技术高级查询技巧部分将带领学习者超越基础，探索更复杂、更高效的数据处理方法通过学习复杂查询的构建技术，数据库查询性能优SQL化策略以及高级函数的应用，能够应对各种复杂的数据分析需求SQL这部分知识特别适合已经掌握基础的学习者，希望进一步提升自己的数据库应用开发能力，能够设计和实现高效、复杂的数据处理解SQL决方案掌握这些高级技能，将为大数据分析和复杂业务场景提供强大的技术支持复杂查询案例多表连接查询案例子查询与派生表WITH公用表表达式CTE与递归查询--查询每个学生的基本信息、所学课程和成绩--使用子查询查找比本系平均成绩高的学生--使用CTE简化复杂查询SELECT s.学号,s.姓名,c.课程名,sc.成绩SELECT s.学号,s.姓名,s.所在系,sc.成绩WITH系统平均成绩系名,课程号,平均成绩AS FROM学生表s FROM学生表s SELECT s.所在系,sc.课程号,AVGsc.成绩INNER JOIN选课表sc ONs.学号=sc.学号JOIN选课表sc ONs.学号=sc.学号FROM学生表sINNER JOIN课程表c ON sc.课程号=c.课程号WHERE sc.成绩JOIN选课表sc ONs.学号=sc.学号WHERE s.所在系=计算机系SELECT AVGsc

2.成绩GROUP BYs.所在系,sc.课程号ORDER BYs.学号,c.课程名;FROM选课表sc2JOIN学生表s2ONsc

2.学号=s

2.学号SELECT s.学号,s.姓名,s.所在系,WHERE s

2.所在系=s.所在系sc.课程号,sc.成绩,sp.平均成绩AND sc

2.课程号=sc.课程号FROM学生表s;JOIN选课表sc ONs.学号=sc.学号JOIN系统平均成绩spON s.所在系=sp.系名AND sc.课程号=sp.课程号WHERE sc.成绩sp.平均成绩;--递归查询示例组织结构树WITH组织结构员工ID,姓名,上级ID,层级AS--基础查询:查找顶级管理者SELECT员工ID,姓名,上级ID,0AS层级FROM员工表WHERE上级ID ISNULLUNION ALL--递归查询:查找下属SELECT e.员工ID,e.姓名,e.上级ID,o.层级+1FROM员工表eJOIN组织结构o ONe.上级ID=o.员工IDSELECT员工ID,姓名,REPLICATE,层级+姓名AS组织树FROM组织结构ORDER BY层级;查询性能优化执行计划分析执行计划是SQL Server优化查询的关键工具，通过EXPLAIN或SET SHOWPLAN_TEXT ON等命令可以查看查询的执行步骤和资源消耗分析执行计划能够识别性能瓶颈，如表扫描、排序操作和低效连接等问题索引优化策略合理设计索引是提高查询性能的基础应在WHERE子句、JOIN条件和ORDER BY频繁使用的列上创建索引，避免过度索引导致插入/更新性能下降针对复杂查询可考虑创建覆盖索引，包含查询所需的所有列查询重写技巧优化SQL查询语句结构可显著提升性能技巧包括减少子查询改用连接操作、避免使用SELECT*只查询必要列、使用EXISTS代替IN、拆分复杂查询为简单步骤、利用临时表或CTE简化逻辑等性能监控与调优持续监控数据库性能指标（CPU使用率、内存分配、磁盘I/O、锁等待）对及时发现和解决性能问题至关重要使用数据库内置的动态管理视图DMV和第三方性能分析工具可实现全面监控和精准调优第五部分综合练习题SQL数据定义与操作练习这部分练习题主要涵盖数据库对象的创建、修改和管理，包括数据库和表的创建、约束定义、索引设计以及数据的增删改操作这些题目旨在巩固DDL和DML的基础知识，提升对数据库结构设计的理解单表查询练习该类练习题专注于在单个表上进行的各种查询操作，包括条件筛选、排序、分组、聚合函数应用等这些题目有助于掌握SQL查询的基本语法和常用技巧，为复杂查询打下基础多表查询练习这类练习题集中于多表联合查询的各种场景，包括内连接、外连接、交叉连接以及自连接等这些题目能够强化对表关系的理解，提升处理复杂数据关系的能力高级查询练习高级查询练习涉及子查询、派生表、公用表表达式、递归查询等复杂技术，以及性能优化和特殊函数的应用这些题目旨在挑战学习者的综合应用能力，培养解决复杂数据问题的思维方式练习题数据库基础概念数据库模型类型数据库设计范式关系代数与完整性约束【问题】以下哪种数据模型最早出现？【问题】关系数据库中，消除部分函数依赖可以达【问题】以下哪项不属于关系代数的基本操作？A.网状模型到哪一个范式？A.选择SELECTB.关系模型A.1NF B.投影PROJECTC.层次模型B.2NF C.连接JOIND.对象关系模型C.3NF D.分组GROUP BYD.BCNF【答案】C.层次模型【答案】D.分组GROUP BY【答案】B.2NF【解析】数据模型的发展顺序大致是【问题】学生表的学号字段设为唯一标识，不层次模型20世纪60年代→【解析】范式的递进关系:允许为空，这体现了哪种完整性约束？网状模型20世纪70年代初→1NF:消除重复组，保证原子性A.实体完整性关系模型1970年由E.F.Codd提出→2NF:消除部分函数依赖非主属性对码的部分依赖B.参照完整性对象关系模型20世纪90年代3NF:消除传递函数依赖非主属性对码的传递依赖C.用户定义完整性BCNF:消除主属性对码的部分和传递依赖D.域完整性【答案】A.实体完整性练习题语句基础SQL练习题单表查询条件查询练习【问题】查询学生表中年龄在20至25岁之间且性别为女的学生信息SELECT*FROM学生表WHERE年龄BETWEEN20AND25AND性别=女;排序与限制查询【问题】按成绩降序查询前5名学生的学号、姓名和成绩SELECT学号,姓名,成绩FROM成绩表ORDER BY成绩DESCOFFSET0ROWS FETCHNEXT5ROWS ONLY;--SQL Server--或:LIMIT5;--MySQL聚合函数使用【问题】计算各科目的平均成绩、最高分和最低分SELECT科目,AVG成绩AS平均分,MAX成绩AS最高分,MIN成绩AS最低分FROM成绩表GROUP BY科目;4分组与过滤【问题】查询平均成绩超过80分的班级编号和平均分SELECT班级编号,AVG成绩AS平均分FROM成绩表GROUP BY班级编号HAVING AVG成绩80;练习题多表查询各类连接查询子查询应用集合运算与复合查询【问题】查询所有学生的学号、姓名和所选课程，【问题】查询选修了数据库课程的学生的【问题】查询选修了数据库或操作系统课程的包括没有选课的学生学号、姓名和所在系学生学号SELECTs.学号,s.姓名,c.课程名--使用IN子查询SELECT学号FROM学生表s SELECT学号,姓名,所在系FROM选课表LEFT JOIN选课表sc ONs.学号=sc.学号FROM学生表WHERE课程号IN LEFTJOIN课程表c ONsc.课程号=c.课程号WHERE学号INSELECT课程号ORDER BYs.学号;SELECT学号FROM课程表FROM选课表WHERE课程名=数据库WHERE课程号INSELECT课程号UNIONFROM课程表SELECT学号WHERE课程名=数据库FROM选课表WHERE课程号IN;SELECT课程号FROM课程表--使用连接查询替代方案WHERE课程名=操作系统SELECT DISTINCTs.学号,s.姓名,s.所在系;FROM学生表sJOIN选课表sc ONs.学号=sc.学号【优化版】使用IN代替UNIONJOIN课程表c ONsc.课程号=c.课程号SELECT DISTINCT学号WHERE c.课程名=数据库;FROM选课表WHERE课程号IN SELECT课程号FROM课程表WHERE课程名IN数据库,操作系统;综合试题分组查询GROUP BY的使用HAVING子句过滤【问题】以下哪个语句可以计算每个系的学生人数？【问题】查询学生人数超过30人的系名和人数，正确的SQL语句是

1.SELECT所在系,COUNT*FROM学生表;A.SELECT所在系,COUNT*AS人数

2.SELECT所在系,COUNT*AS人数FROM学生表GROUP BY所在系;FROM学生表

3.SELECT所在系,COUNT*WHERE性别=男FROM学生表GROUP BY所在系;WHERE COUNT*

304.SELECT所在系,COUNT*FROM学生表GROUP所在系;GROUP BY所在系;【答案】B.SELECT所在系,COUNT*AS人数FROM学生表GROUP BY所在系;B.SELECT所在系,COUNT*AS人数FROM学生表【解析】GROUP BY子句用于将查询结果按指定列分组，每组可应用聚合函数必须将SELECT中出现的GROUP BY所在系非聚合列都包含在GROUP BY子句中HAVING COUNT*30;C.SELECT所在系,COUNT*AS人数FROM学生表GROUP BY所在系WHERE COUNT*30;D.SELECT所在系FROM学生表GROUP BY所在系HAVING人数30;【答案】B.SELECT所在系,COUNT*AS人数FROM学生表GROUP BY所在系HAVING COUNT*30;综合试题语句DELETE10DELETE语法解析错误执行结果DELETE语句用于从表中删除行，基本语法为试题DELETE*FROM学生表的执行结果是语法DELETE FROM表名WHERE条件；与SELECT不错误，因为DELETE语句不支持星号表示法同，DELETE后不能跟星号*或列名100%正确语法删除表中所有数据的正确语法为DELETE FROM学生表，删除特定记录则需添加WHERE子句如DELETE FROM学生表WHERE学号=2021001DELETE语句是SQL中重要的数据操作语言DML命令之一，用于从数据库表中删除现有记录使用时应特别注意条件的正确性，因为执行后数据不可恢复除非在事务中并进行回滚对于大型表的删除操作，应考虑使用事务或分批处理，以减小对系统性能的影响综合试题数据完整性实体完整性参照完整性确保表中每一行的唯一性，通常通过主确保表之间的引用关系正确，通过外键键约束实现例如，学生表的学号字段约束实现如成绩表中的学号必须引用设为主键，保证每个学生记录的唯一标学生表中已存在的学号，防止出现无效识引用用户定义完整性域完整性针对特定业务规则的约束，可通过确保字段值符合定义的类型和约束，通约束、触发器或存储过程实现过数据类型、约束、约束CHECK CHECKDEFAULT如限制学生年龄必须在合理范围内或成和约束实现，保证数据的有效NOT NULL绩必须在之间性0-100综合试题约束类型约束类型功能描述SQL语法示例主键约束确保表中每一行的唯一性ALTER TABLE学生表ADD CONSTRAINTPK_学生PRIMARYKEY学号;外键约束确保引用的完整性ALTER TABLE成绩表ADD CONSTRAINTFK_学生成绩FOREIGN KEY学号REFERENCES学生表学号;唯一性约束确保列或列组合的值唯一ALTER TABLE学生表ADD CONSTRAINTUQ_身份证UNIQUE身份证号;检查约束限制列的值范围ALTER TABLE成绩表ADD CONSTRAINTCK_成绩CHECK成绩BETWEEN0AND100;默认约束为列提供默认值ALTER TABLE学生表ADD CONSTRAINTDF_性别DEFAULT男FOR性别;数据库约束是维护数据完整性的重要机制，可在创建表时定义，也可通过ALTER TABLE语句后期添加合理使用约束可以防止无效数据进入数据库，减少应用程序中的验证逻辑，提高数据质量综合试题默认约束默认约束实现错误实现方案约束优先级别在教师表中为级别字段设置默认值为讲使用约束不能设置默认值，它只能当多种约束同时存在时，系统会按照特定CHECK师，应当创建约束这种约束确验证输入的有效性；触发器虽然可以实现优先级执行首先应用约束，然DEFAULT NOTNULL保在没有明确指定值时，系统会自动插入类似功能，但过于复杂且影响性能；存储后是约束验证值的有效性，再检查CHECK设定的默认值，简化数据输入并保持数据过程适用于复杂业务逻辑，不适合简单的和约束，最后验证UNIQUE PRIMARYKEY一致性默认值设置约束约束仅在未指FOREIGN KEYDEFAULT定值时生效综合试题系统SQL Servermaster数据库model数据库msdb数据库SQL Server的核心系统数据库，新建数据库的模板，其中包含用于SQL Server代理功能，存储存储所有服务器级系统信息，包括•默认数据库选项•作业调度信息•登录账户信息•系统对象定义•备份和还原历史•系统配置设置•用户定义的自定义对象•数据库邮件配置•所有数据库的位置信息•服务代理设置•链接服务器信息tempdb数据库存储临时对象的工作区•用户创建的临时表•内部临时对象•行版本信息•排序操作的中间结果综合试题数据库文件主数据文件.mdf包含启动信息和系统表指针次数据文件，可选.ndf存储用户数据，可分布在不同磁盘日志文件.ldf记录事务日志，用于恢复操作每个数据库至少由一个主数据文件和一个日志文件组成主数据文件包含数据库的启动信息并指向其他文件，是数据库不SQL Server.mdf.ldf可或缺的部分次数据文件是可选的，用于存储额外的用户数据，可以将其分布在不同的磁盘上以提高性能.ndf日志文件用于记录对数据库所做的所有修改，是数据库恢复过程的关键组成部分通过事务日志，数据库系统能够在发生故障后恢复到一.ldf致状态，确保数据完整性在大型系统中，通常将数据文件和日志文件放置在不同的物理磁盘上，以提高性能和可靠性I/O综合试题数据类型选择商品名的数据类型选择不同数据类型的应用【问题】商品表的商品名应选用哪种数据类型？数据类型适用场景示例

1.INT整数值商品数量、学生人INT

2.FLOAT数

3.VARCHAR精确小数价格、工资、金额DECIMAL

4.DATETIME【答案】C.VARCHAR CHAR固定长度字符性别男/女、邮编【解析】商品名是文本数据，需要存储可变长度的字符串，因此应选用可变长度字符姓名、地址、商品VARCHARVARCHAR类型INT和FLOAT是数值类型，不适合存储文本；DATETIME用名于存储日期和时间信息，也不适合商品名DATE/DATETIME日期/时间值出生日期、订单时间综合试题聚合函数综合试题语句INSERT基本插入向表中指定列插入单行数据INSERT INTO学生表学号,姓名,性别VALUES2023001,张三,男;自增列处理自增列可省略或使用NULL/DEFAULTINSERT INTO产品表产品名,价格VALUES笔记本电脑,5999;--ID列为自增，会自动生成批量插入一次插入多行数据提高效率INSERT INTO课程表课程号,课程名,学分VALUESC001,数据库,4,C002,操作系统,3,C003,计算机网络,3;INSERT语句是数据操作语言DML中用于向表中添加数据的命令除了基本的VALUES语法外，还可以使用SELECT子句从其他表中查询数据并插入，例如INSERT INTO表1SELECT*FROM表2WHERE条件使用INSERT时常见错误包括列数与值数不匹配、违反主键或唯一约束、数据类型不兼容以及违反CHECK约束等综合试题数据完整性参照完整性是关系数据库中维护表间关系一致性的重要机制，通常通过外键约束实现当定义外键约束时，可以指定在删除或更新被参照表中的行时要采取的操作，主要有四种选项默认如果存在相关的外键值，则禁止删除或更新主键

1.NO ACTION级联删除或更新关联的外键行

2.CASCADE将关联的外键值设置为

3.SET NULLNULL将关联的外键值设置为默认值

4.SET DEFAULT综合试题数据库分离与附加数据库分离的文件类型分离与附加的操作步骤当使用存储过程分离数据库需确保没有活动连sp_detach_db分离数据库时，会将数据库从接，使用数EXEC sp_detach_db实例中分离出来，但据库名命令；附加数据库则使SQL Server保留其所有物理文件，包括主用CREATE DATABASEFOR数据文件、次数据文件语法，需指定主数据文.mdf ATTACH和日志文件这些文件路径，次数据文件和日志文.ndf.ldf件可以移动到其他位置或服务件会自动识别如文件位置改变器则需明确指定应用场景与注意事项分离与附加常用于数据库迁移、备份恢复和维护场景操作前应确保没有活动连接并备份数据库；分离后数据库对象不再可用；附加时需有足够权限；版本兼容性问题需注意，较新版本的数据库可能无法附加到旧版本SQL Server答题技巧与方法理解题目要求仔细阅读题目，明确所测试的知识点和要求的输出结果注意题目中的关键词如查询、更新、计算等，确定需要使用的SQL语句类型对于多步骤的问题，先理清逻辑关系，确定解决方案的整体思路掌握语法规则SQL熟记SQL语句的基本语法结构和常用子句的用法注意各类语句的执行顺序，如SELECT语句中FROM、WHERE、GROUP BY、HAVING、ORDER BY的顺序掌握不同数据库管理系统间的语法差异，如分页查询在MySQL和SQL Server中的不同实现方式熟悉数据库基本概念牢固掌握数据库设计理论、关系代数、规范化等基础概念理解实体完整性、参照完整性和用户定义完整性的含义及实现方式熟悉各类约束的作用和使用场景，包括主键、外键、唯一性约束等分析常见错误注意识别常见的SQL错误，如语法错误关键字拼写错误、括号不匹配、语义错误表名或列名不存在、逻辑错误查询结果不符合预期对于复杂查询，可以将其分解为多个简单步骤，逐步验证结果的正确性常见错误分析SQL语法错误数据类型不匹配常见的SQL语法错误包括关键字拼写错误、括号不匹配、引号使用不当、子句顺当操作不同数据类型的值时，如尝试将字符串与数字比较，或将日期值插入数序错误等例如，使用ORDER BY子句时必须放在SELECT语句的最后，而GROUP值字段，会导致类型转换错误应注意数据类型的兼容性，必要时使用显式类BY必须在WHERE之后、HAVING之前解决方法是熟记SQL语法规则，并使用代型转换函数如CAST或CONVERT特别注意字符串与日期之间的转换，不同数据码编辑器的语法检查功能库系统的日期格式可能不同约束条件冲突逻辑设计缺陷违反数据库约束条件是常见错误，如插入重复的主键值、违反外键参照约束或数据库设计中的逻辑缺陷可能导致数据冗余、更新异常或查询困难常见问题CHECK约束解决方法是在操作前检查数据是否符合约束条件，理解各类约束的包括不满足规范化要求、缺少适当的索引、表结构设计不合理等应遵循规范含义，并在设计数据库时合理设置约束，避免过于严格的限制导致正常操作失化理论，合理设计表结构和关系，建立必要的索引，并定期评估和优化数据库败设计以适应不断变化的业务需求学习资源推荐参考教材与网络资源•《数据库系统概念》Abraham Silberschatz等著•《SQL必知必会》Ben Forta著•《SQL Server2019实战指南》•W3School SQL教程•菜鸟教程数据库部分•Stack Overflow问答社区习题与实验指导•LeetCode数据库题目集•牛客网SQL练习题•HackerRank SQL挑战•高校数据库实验指导书•SQLZoo交互式练习开发工具与学习平台•SQLServerManagement Studio•MySQL Workbench•Navicat Premium•DBeaver通用数据库工具•在线SQL编辑器如SQLFiddle•数据库可视化设计工具如PowerDesigner实践项目建议•设计并实现学生信息管理系统•创建个人图书或电影收藏数据库•构建简易电商数据库并编写常用查询•分析公开数据集并创建报表•参与开源数据库项目考试重点总结30%数据库设计理论掌握E-R模型设计方法，熟悉从概念模型到物理模型的转换过程理解函数依赖和规范化理论1NF、2NF、3NF、BCNF，能够识别和解决不同类型的数据冗余问题40%SQL语句语法与应用熟练掌握各类SQL语句DDL、DML、DQL、DCL的语法和用法，能够编写复杂的查询语句，包括多表连接、子查询、聚合函数、分组查询等理解事务处理和并发控制的基本概念15%数据完整性维护理解并能够实现各种完整性约束，包括主键约束、外键约束、唯一性约束、CHECK约束、DEFAULT约束等掌握触发器和存储过程的基本用法，能够使用它们实现业务规则和自动化操作15%高级查询与优化了解查询优化的基本原理和方法，包括索引设计、查询重写和执行计划分析能够使用高级功能如视图、临时表、公用表表达式CTE、窗口函数等解决复杂的数据处理需求复习计划与建议基础概念梳理第1-2周系统回顾数据库基础概念、数据模型、关系代数等理论知识制作概念思维导图，建立知识点之间的联系使用闪卡工具记忆重要定义和术语语句实践SQL第3-4周搭建本地数据库环境，跟随教材案例手动编写和执行各类SQL语句重点练习复杂查询，如多表连接、子查询、分组统计等尝试使用不同的查询方式解决同一问题，比较效率差异综合习题训练第5-6周每天完成5-10道综合习题，涵盖各种类型和难度分析错题，找出薄弱环节，有针对性地强化训练参与在线SQL挑战，在实际问题解决中提升应用能力4考前重点复习最后1周重点复习常见错误和易混淆概念，如完整性约束类型、SQL语句执行顺序、数据库设计范式等进行模拟测试，检验复习效果，调整心态，保持良好的作息，以最佳状态迎接考试。