SQL server基础理论学习

SQLserver基础理论学习：信息体系结构原则1、信息体系结构原则1.1信息管理的3个主要方面：数据库设计和开发、企业数据中心的管理、商业智能分析1.2信息体系结构原则首先必须有详细的信息清单，包括信息的位置、来源、敏感性、当前和未来信息的价值以及当前的拥有者1.3要确保数据能够在未来使用，必须降低当前设计的耦合程度。1.4数据存储目标1.4.1简单性要对复杂性感到敬畏，应不断调整设计，直到整个设计小组都认为它是能够满足需求的最简单解决方案。字典中“困难”的同义词包含“复杂”是有道理的。简单的解决方案通常是优雅而容易理解的。(应让事情尽可能简单，且不能再简单一爱因斯坦)做到简单性的要求:对需求有全面认识掌握大量可供参考的模式和解决方案对技术规则和行业术语有全面认识创造性掌握行业知识，直到何时及如何改善技术系统对用于实现设计的工具和设备以及解决方案的运行环境有深入认识设计人员互相信任、分享想法而不考虑个人得失不断优化设计，直到设计出优雅而简单的设计方案（8）对复杂性抱有健康的敬畏心态1.4.2有用性有用性包括三点：（1）满足组织需求（2）适应性：设计与数据存储的范围和用途相称数据存储设计模型包括：关系DBMS、对象数据库、对象/关系数据库、通用模式、数据仓储数据库的范围包含数据在组织内的使用度（个人、部门、营业单位、公司、企业）以及数据的持续性（当前、当年、永远）（3）数据格式可供应用程序使用以及易于提取数据存储配置包括5种类型：（1）主数据存储：运营数据库或联机数据处理数据库，用于收集第一代交易数据。在组织中必不可少，且是唯一的。主数据库存储需要在方便数据检索和数据更新之间取得平衡。每个运营单位都可能有个主数据存储。（2）缓存数据存储：可选，是主数据存储的只读拷贝，用于提供数据，减轻主数据库的负载。优化它，可提供数据库检索性能。（3）引用数据存储：主要为只读，存储组织中较少变更的数据。类似图书馆的目录区。优化它，也可很高检索性能。（4）数据仓库：从企业多个主数据存储收集大量数据，并使用提取、转化和加载过程，将格式和架构转为通用，简化数据检索。同时存储历史记录，减轻运营数据存储的负载。组织必须有一个通用的数据仓库。主要任务是数据检索和分析。5）数据集市：数据仓库的子集，包含经过预先汇总的数据。主数据存储的设计方法（1）关系DBMS传统的数据库，它讲类似或相关的数据放到一个表中。适合满足稳定的数据架构需求，这种需求只包含少量的is_a关系。（2）面向对象的DBMS00DBMS必须存储对象，因此判断OODBMS优劣的一个重要标准就是它能否存储复杂的对象，如XML文件、.net文件OODBMS非常适合这样的程序：架构可能发生重大变化、包括复杂数据类型、类之间涉及多个is-a关系、包括复杂的多关联以及要求将数据同应用程序轻松关联起来。有三种主要的面向对象的数据库A对象持久化数据存储（0PDBMS）:相当于一个存储对象的状态仓库B面向对象的数据存储（00DBMS）C对象/关系数据存储（0/RDBMS）（3）通用模式DBMS也称为动态钻石模式，有时被用作关系数据库产品中的面向对象的数据库设计。在应用程序需要动态属性时，这种设计非常有用。1.4.3完整性数据实际上是实体和属性，因此数据完整性由实体完整性和域完整性组成。（1）实体完整性：本质就是规范化2）域完整新：保证属性只取有效值，包括为空性。引用完整性：外键域的完整性用户定义完整性事务完整性：事务是一个逻辑工作单元，如插入100行。数据库产品的质量是根据其事务对ACID属性的遵循程度衡量的。A(atomicity)原子性：事务必须是原子的，这意味着在事务结束时，耀目所有操作成功，要么都失败。C(consistency)—致性：这意味着在事务执行前数据库处于一致性状态，事务结束后，数据库回到一致性状态。一致性意味着每行和每个值都与描述一致且满足所有的约束。I(isolation)隔离性：每个事务都必须是隔离的，不受其他事务的影响，不管是否有其他事务执行，它都必须使用它开始时的数据集继续执行下去。D(durability)持久性：不管系统是否发生故障，事务的结果都是永久性的。(6)事务缺陷脏读：如果事务能够读取其他商务尚未提交的更新，则被称为脏读。脏读可能导致在读取的数据未提交前，而写入该数据的事务可能被回滚。不可重复读：类似脏读，指事务能看到其他事务已提交的数据更新。如果两次读取得到的结果不同，则存在不可重复读性事务缺陷。虚幻行：危害最小，指事务的更新操作不仅影响结果集的数据值，还导致select语句返回不同记录集。丢失更新：两个用户同时更新一行数据时，第二个覆盖了第一个的更新，导致更新丢失。死锁：多个项目的多个事务争用一个数据资源时容易发生死锁，需要一个事务主动放弃或退出。（7）隔离级别：未提交读、已提交读、可重复读和可序列化，2005还引入了快照隔离，但容易导致更新丢失。1.4.4性能物理磁盘的性能是最严重的瓶颈，因此提高性能的关键之处在于减少执行任务时需要读取的物理页数量。提高性能的技术有：（1）设计：简化设计，不要包含过多的表，过多游标等（2）基于集合的处理：基于集合的优秀查询能够对整个数据集进行处理，且只读取一次所需数据页，而基于游标的解决方案分别对每一行进行处理。（3）索引是查询和数据之间的桥梁，它能减少执行表读取操作时需要读取的物理页数。要更精确滴设计索引，必须了解数据库引擎的查询优化器和索引结果的工作原理。索引有主力提高数据读取的性能，但给写入性能带来负面影响，插入和更新数据时，要确保索引数据同步。（4）分区：将数据分散到多个磁盘中。（5）缓存：预先从磁盘中读取数据，以便数据库操作需求时能够在内存中找到它们。1.4.5可用性恢复计划、冗余性、存档方式以及网络速度都将影响可用性，可用性通常用几个9表示，7个9表示在99.99999%情况下可用，5个9表示在99.999%可用。以此类推。（1）冗余：找出可存在的故障点，并通过过程备用解决方案来避免或降低故障带来的影响。（2）恢复：利用备份文件还原数据。1.4.6可扩展性（1）抽象层：降低数据存储和应用程序之间的耦合程度，一种最主要的方法是使用抽象层。这样可以避免因为缺乏封装导致两者之间的耦合过深。（2）归一化：将类似的尸体组合成一个实体。1.4.7安全性（1）数据中心的物理安全性（2）操作系统的安全性信息安全还包括：（1）限制访问（2）信息所有权（3）审核跟踪优化理论和SQLserver（1）架构设计避免过于复杂精心挑选键处理可选数据实现抽象层（2）查询问题最佳解决方案复杂的业务逻辑查询、子查询、CTE动态生成DDL游标对列表进彳丁逆规范化多赋值标量和游标交叉表包含透视和case表达式的查询导航层次结构UDF或CTE计算累计总计和动态和游标（3）索引（4）并发性：锁定和阻塞问题。nolock并不是很好的解决方式，很危险。可以采用缩短事务的持续时间来实现。（5）高级伸缩性快照隔离分区表索引视图servicebrokerSQLserver基础理论学习：关系数据库建模3.关系数据库建模模拟现实数据建模过程：（1）手机观察结果和需求（2）现实世界的逻辑表示（3）可见实体的识别和设计（4）架构设计（辅助和支持实体）5）应用程序结构设计可见实体可见实体通常是大多数人能够识别的对象。（1）每个元组（行）都是独立的（2）主键（3）表、行和列3.3识别多个实体（1）多个对象（2）对象之间的关系（3）组织对象（4）一致的查找值（5）复杂对象3.4关系模式关系使用键将实体关联起来，包含如下两个主要属性：基数：在关系的每一端可能存在的对象数可选性：关系是可选的也是强制的（1）辅助实体和外键两个对象彼此相关联时，通常一个实体为主实体，另一个实体为辅助实体。主实体中的一个对象将关联到辅助实体中的多个对象或元组。（2）关系基数指在关系的每一端有多少元组。在关系的主键端只能有一个元组，而在外键端可以有多个元组。（3）关系的可选性：可选关系和强制关系对于数据库的完整性非常重要。4）绘制数据模型关系图ChenER关系图绘制方法非常简单，用sisio即可。（5）关系种类一对多一对一超类/子类多对多（6）类别实体有时也称为查找表，（7）自反关系在两个相同类型的实体之间建立关系，比如人与人之间的上下级关系，家庭成员之间的关系等。规范化规范化是减少更新异常的工具。（1）实体/属性的设计原则：每个数据库实体必须描述一个“事务”属性和事务之间必须存在一对一的关系每个属性必须描述其所属实体，而不能描述其相关的实体。（2）范式规范化通常以范式的方式定义。每种范式都描述了设计实体和属性时可能出现的错误，并提供了纠正这种错误的规则。因此范式类似于书面语言的语法规则。规范化数据库与平面文件数据库比，优点如下：消除重复存储减少锁的争用缩小文件数据建模人员应该一开始就按照第三范式设计逻辑架构。（3）第一范式符合条件A每个数据单元都是用标量属性表示的，标量值指的是“可以用标尺上的一个点表示”的值B所有数据必须用唯一属性表示，每个属性都必须有唯一的名称和用途。C所有的数据都必须用唯一的元组表示。（4）第二范式确保每个属性是实体属性。每个属性都必须有对应的主键。如果其他属性只依赖于主键的一个属性，而不依赖于主键的其他属性，这被称为部分依赖，违反了第二范式。（5）第三范式检查传递依赖。传递依赖和部分依赖的相似之处在于，他们值得都是属性不完全依赖于主键。当A依赖于B,B依赖于主键时，就发生了传递依赖。同第二范式一样，解决方式是将非依赖属性移到新实体中。*如果实体有很好的主键，每个属性都是标量且都完全依赖于主键，则逻辑设计符合第三方式，大多数数据库设计满足第三范式即可。（6）BCNF范式位于三四范式之间，用于处理实体有两组主键的问题。BCNF规定，在这种情况下，应将实体拆分为两个实体，每个实体包含一个主键。7）第四范式用于处理复杂的符合主键导致的问题，如果将两个相互独立的属性组合成主键，同时还要有第三个属性，如果没有第三个属性，仅靠着两个属性不能唯一标识实体，则违反了第四范式。（8）第五范式用于设计涉及多个（3个以上）实体的复杂关系，如果设计正确可以设计出三元关系，其中每个关系的基数都可以是一或多。关系代数关系代数有8个关系运算符组成（1）限制：返回符合制定条件的行（2）投影：从数据集中返回指定列（3）积：关系乘法，返回两个数据集的所有可能的数据集合。（4）联合：关系加法和减法，将一个表垂直堆叠到另一个表上，将两个表合并（5）交集：返回两个表中都有的行（6）差集：返回一个数据集中特有的行（7）连接：返回两个表的水平合并结果，合并是根据都有的行来匹配（8）除：返回两个数据集中完全匹配的内容另外sqlserver还开发以下功能：子查询在关系代数中，使用以下术语：表或数据集被称为关系或实体行被称为元组列被称为属性SQLserver基础理论学习：SQLserver2005体系结构4SQLserver2005体系结构4.1数据访问体系结构sqlserver首先是一个数据库服务器（1）客户/服务器数据库模型客户/服务器数据库介绍：客户进程请求服务器进程提供服务，后者处理客户发出的请求。所有的数据库工作都是在数据库服务器中执行的，完成后讲数据集的拷贝发送到客户端，实际的数据和索引不会离开服务器。相对于桌面数据库（acess），它性能更高，更安全可靠。客户/服务器数据库职能数据库服务器职责：A处理数据修改和检索要求B执行数据密集型处理C实施所有的数据库规则和约束D实现数据安全性数据库客户端职责：A以易于识别、引人入胜和适用的格式将数据呈现给用户B提供使用各种工具、数据和报表的界面C将请求提交给服务器（2）N层设计在客户/服务器应用程序中，除了客户端和服务器进程外，还涉及其他进程，经常使用中间层来处理连接、连接池和业务逻辑。使用中间层可以让多个用户使用为数不多的数据库服务器连接，到要考虑用户的身份验证方式。在有多个服务器可用的情况下，通过使用通用连接对象，在服务器A发生故障时，将用户从服务器A切换到服务器B将更容易，连接对象成为能够检测到故障并在服务器之间切换的单个点。这种解决方案在使用数据库镜像时非常有用。（3）面向服务的体系结构面向服务的体系结构（SOA）是客户/服务器数据库模型的替代品，SOA使用标准的HTTP调用和XML,而不是多个系统之间编写自定义的客户/服务器接口，这样多个系统可以用相同的连接口。SOA使用与有多个超大型系统的大企业，适应用作辅助数据访问方法，以简化同其他超大型系统的通信，除此，不要使用SOASQLserver中的服务SQLserver不仅仅是一个关系数据库引擎，它是一组与数据库相关的服务和组件，可结合使用它们来创建功能强大的数据库后端解决方案。1）关系引擎关系引擎有时也被称为数据库引擎，它是sqlserver的核心，该进程负责处理所有关系数据库的工作。sql是一种描述性语言，这意味着SQL向引擎描述要处理的查询，引擎负责完成其他工作。SQLserver支持在同一台物理服务器中安装多达50个关系引擎实例，虽然它们共享一些组件，但每个实例都是一个独立的SQLserver（2）Transact-SQLSQL小组扩展了ANSI定义，在很多方面进行了改进，添加了新命令，删了一些命令，这就是Transact-SQL（T-SQL）,SQLserver只理解一种语言，那就是T-SQL。（3）VisualStudio和CLRSQLserver最激动人心的功能之-'是集成了visualStudio.ManagementStudio是基于VisualStudio集成开发环境（IDE）,SQLserver内部操作系统（SQLOS）实际上是在SQLserver内部托管（host）了.net的共同语言运行时（CLR）名词解释：CLR是公共语言运行时，CommonLanguageRuntime）和Java虚拟机一样也是一个运行时环境，它负责资源管理（内存分配和垃圾收集），并保证应用和底层操作系统之间必要的分离。IDE，即IntegratedDevelopmentEnvironment，是“集成开发环境"的英文缩写，可以辅助开发程序的应用软件。软件是用于程序开发环境的应用程序，一般包括代码编辑器、编译器、调试器和图形用户界面工具。就是集成了代码编写功能、分析功能、编译功能、debug功能等一体化的开发软件套。所有具备这一特性的软件或者软件套（组）都可以叫做IDE。如微软的VisualStudio系列，Borland的C++Builder,Delphi系列等。该程序可以独立运行，也可以和其它程序并用。（4）ServiceBrokersqlserver2005新增的功能，通过一个托管数据队列，通过平滑负载，提高了性能和伸缩性。创建面向服务的体系结构时，ServiceBroker是一个重要服务。复制服务全文搜索服务通过windows管理工具＞服务来启动和停止NotificationServices可通过编程使其根据数据变更发送信息，消息几乎可以发送给任何设备。(8)服务器管理对象(SMO)它是暴露sqlserver的配置和管理功能的一组对象，使得能够在.net前段代码中使用它们。(9)SQL代理可选进程，可配置为系统启动时自动运行。分布式事务协调器(DTC)负责以两阶段的方式提交跨越多个SQLserver的事务。通过windows管理工具〉服务来启动和停止SQLmail让sqlserver能够通过SMTP将电子与偶见发送到外部邮箱。BusinessIntelligenceServices(BI)商业智能(BI)是SQLserver2005最擅长的领域之一。SQLserver2005提供了三种智能服务：(1)IntegrationServices(ISorSSIS，它表示SQLServerIntegrationServices)：几乎可以在任何数据源之间传输数据，它是SQLserver的提取-转换-加载(ETL)工具。BI使用图形工具来定义如何将数据从一个数据源移到另一个数据源。IS包能够复制数据列，执行复杂的转换和处理数据移动发生的异常。IS非常适用于数据转换。(2)ReportingServices(RS)(3)AnalysisServices(AS):是一种提供商业智能或联机分析处理的服务。能让开发人员定义包含多维的数据集。多维数据集中包含根据超大型数据库预先计算的汇总数据，让用户快捷浏览多维数据集中的总计和小计。这三种服务都是使用BusinessIntelligenceDevelopmentStudio开发的。SQLserver2005的版本这里说的版本(edition)指的是包含的功能集不能的安装包。而不是SQLserver2000SQLserver2005等版本(version)企业(开发)版标准版工作组版SQLserver速成版SQLservereverywhere版SQLserver的工具和组件(1)SQLservermanagementstudio供管理员和程序开发人员使用的类似visualstudio的集成环境，其核心是强大的对象资源管理器。2)SQLServerConfigurationManager这个工具用于启动和停止各种服务器，可以通过开始菜单或managementstudio启动它。（3）外围应用配置器默认状况下，很多sqlserver的功能都是被禁用，以减少sqlserver的暴露程度，这些功能大部分都可以使用T-SQL来启用。（4）BusinessIntelligenceDevelopmentStudio类似managementstudio，但针对BI进行优化。（5）SQL集成帮助（6）SQLProfiler监视sqlserver的流量和事件（7）性能监视器（8）数据库优化顾问（9）命令行实用程序：SQLCmdBulkCopy这些能够让开发人员在DOS提示符下或命令行调度程序中执行SQL代码或大容量复制操作。SQLserver2005功能包下载包提供了二十多个驱动程序以及支持向后兼容及其他系统兼容的代码，还包含以下有用的工具有实用程序：SQLserver升级顾问数据库迁移助理最佳实践工具AdventureWorks示例数据库，替代以前的Northwind和Pubs4.8探索元数据SQLserver使用四个系统数据库进行自我管理，另外还有一个model数据库。Master:包含有关服务器中数据库的信息MSDB：包含诸如备份和作业等活动列表Tempdb:包含用户、批处理、存储过程和SQLserver引擎本书使用的临时表。Reference:该数据库被隐藏，从master中分离出来，旨在使得升级服务包更容易。Model:用于创建新数据库的模板数据库。元数据是有关数据的信息，SQLserver支持4种元数据：目录视图：提供有关静态元数据的信息动态管理视图和函数：通过探索服务器的当前状态，提供有关诸如内存、线程、缓存中的存储过程和连接等的数据。兼容性视图：模拟以前版本中的表，用于向后兼容系统架构视图：ANSISQL92标准视图。可用于查看任何数据库产品的架构。SQL补充学习(一)：CROSSJOIN1.CROSSJOIN除了内连接(innerjion)，外连接(leftjoin,rightjoin,fulljoin)，还支持交叉连接。交叉连接返回被连接两个表的卡迪尔乘积，即：卅氐Q拥曲且曲

CROSSJOIN语法select列名from表名1crossjoin表名2where表名1.列名=表名2.列名CROSSJOIN实例样例数据：序号姓名语文数学序号姓名语文数学地理历史5虫虫6775596名名閃7743554虹红益吟4的47834558775AU2MLAU2MJLL要求:用crossjoin语句找出两表中学生序号相同的学生,并将其所有字段组合在一个表中。selecta.序号,b.姓名,a.班级,a.年龄,b.语文,b.数学,b.地理,b.历史fromTable_4asbcrossjoinTable_5asawherea.序号=b.序号执行结果：审結果1也消息|序号」姓名班…年•••」语••，|数…地理历史i4Lj红红56检34489472■:■5屯由676775NULL5?-3G名名15757-743f55'48小小j6558f?75我们可以发现，用下面语句，效果是一致的：selecta.序号,b.姓名,a.班级,a.年龄,b.语文,b.数学,b.地理,b.历史fromTable_4asb,Table_5asawherea.序号=b.序号注：实际上将crossjoin替换成“,，”效果相同。如果不加后面的where条件语句，将会返回32行。SQL补充学习（二）：自身连接自身连接是指数据表与其自身产生连接，因此在实现时，需要使用别名来配合使用2.1自身连接实例样例数据：响田FAN切收丸..託mTabljF/ikiiFAJCIlJbh…一工話"4”-WAHFOGDATA...dbo.Table2序号姓名语文數学地理历史:5虫虫6775flM596名名757755斗虹红曲斗4遼47834E58775MLMULL用自身连接得出历史成绩大于70的人的信息。语句：selecta.序号,a.姓名,a.语文,a.数学,a.地理,a.历史fromTable_4asa,Table_4asbwherea.序号=b.序号andb.历史>70执行结果：可以看出，它与下面语句效果一致：select*fromTable_4where历史＞70注：实际上自身连接的操作对象是数据表本身。SQL补充学习（三）：INTERSECT语句和EXCEPT语句3.INTERSECT语句和EXCEPT语句intersect语句可以用来查两个表中的“公共”数据信息，即“交叉”数据信息。INTERSECT返回INTERSECT操作数左右两边的两个查询都返回的所有非重复值。EXCEPT语句则是用来查询上述“公共”信息“除外”才信息。EXCEPT从左查询中返回右查询没有找到的所有非重复值。而与UNION联合查询语句一样，INTERSECT语句和EXCEPT语句查询的两个表必须具有相同的结构，即他们的列必须相似，数目相等，顺序相同，且数据类型兼容。INTERSECT语句和EXCEPT语句select列名from表名1intersectselect列名from表名2select列名from表名1exceptselect列名from表名23.2intersect实例样例数据.dbi>.Taltle.lWABF^DATA...LgueryS.5,ql*：<W^A1T5DATA...db^.Table,idname5^hooladdress.dsts►1开明BEIJIPJG■2010-1-1OiOOiOO2'小明三峽XIANGFAN切1詁-环0:00:00WIIII育才BEIJING19SS-4-Z0:Q0:Q04虫里明天2006-4-30:00:00*ML402/1O2,f[!ULLAUZiA9FAI«DATA...dbo.Table_B?WABFAJJGDm...dbo.TableJ'ffAHTMGBATA...LQueryl.sqlfid姓躬地址时间1步名肓才BEIJINGio-L-ia：oa：oa2水明三映地AM略FAM201-3-4-4ft:0ft:00►*ML^ULLAU271Wnu.i要求：求出上面两表中的共同部分。姓名姓名,SELECT[id],[姓名],[学校],[地址],[时间]FROM[mydata].[dbo].[Table_6]intersectSELECT[id],[name],[school],[address],[data]FROM[mydata].[dbo].[Table_1]执行结果：r结果自消息id姓名1学校地址时间1不明三嗾:.^lANGFAN2013-04-0400:30:00.000要求：输出上面两表中共同信息除外的信息SELECT[id],[姓名],[学校],[地址],[时间]FROM[mydata].[dbo].[Table_6]exceptSELECT[id],[name],[school],[address],[data]FROM[mydata].[dbo].[Table_1]执行结果：J结果|由消息idi姓名｛学校也址时间11小名育才BEIJING'2010-01-0100:00:00.300注：输出结果列名与第一个select语句中的表的列名一致。SQL补充学习（四）：在select语句中使用子查询4.子查询子查询是指将查询结果作为另一个查询参数的查询4.1在select语句中使用子查询实例-血-tfANFAUGDATA-.ueryl4.sql+摘要姓名|学校地址年龄时间小毛巨九.南昌…i&2004-6-70:00:00张与新东方北京…i+21]05-6-50:00:00化化育才北京…2008-^-40;00;00MILrwmL/im要求：要求求出每个学生与平均年龄的差距语句：select年龄,(selectAVG(年龄)fromTable_2)as平均年龄，(年龄-(selectAVG(年龄)fromTable_2))as与平均年龄差距fromTable_2执行结果：SQL补充学习（五）：在from语句中使用子语句5.在from语句中使用子语句在select语句中的from子句中，也经常会用到子查询5.1在from语句中使用子语句实例样例数据：丸..心o.TabljF丿曾AWAJGIllbk…起。一工話臨4上WAHFA1TGDATA...dbo.Tat.le2序号姓名语文數学地理匝史■5虫虫6775596名名.7577胃55斗虹红斗4S947834558775MSAM4WMS要求：输出学生各科成绩总分，按从高到底排

select*from（select姓名,sum（语文+数学+历史+地理）as总分fromTable_4groupby姓名）tablorderby总分执行结果：J■■果爲捎息姓名总分[中至HNULL红红20^名名25D不小251SQL补充学习（六）：在where语句中使用子查询6.在where语句中使用子查询实例：样例数据■-叢-血一饥业2丫""WANFAUGDATA-.u&ryl4.eqL*j摘要姓名学校地址年龄时间小毛巨九.南昌i&2004-6-70:00:00张与新东方北京…i+2j]05-6-50:00:00化化育才北京…2008-^-40;00;00»*MILrwmL/im要求：要求求出年龄大于平均年龄的每个学生与平均年龄的差距语句：select年龄,(selectAVG(年龄)fromTable_2)as平均年龄，(年龄-(selectAVG(年龄)fromTable_2))as与平均年龄差距fromTable_2where年龄>=(selectAVG(年龄)fromTable_2)执行结果：圉皓果］冶消息1姓名年…平均年…与平均年龄差距-ft--1-1-化化2418SQL补充学习（七）：IN语句的子查询7.in语句的子查询7.1in语句的子查询实例样例数据表-Abo.TaBle2WAHFAITGDATA...ueryl4.sql*摘要姓名丨学校地址年龄时间小毛巨人,南畠|>11&2004-6-70;00;00張与新东方北京…1+21105-6-50:00:00化化育才北京…24-2008-4-40:00:00MILML/im要求：用in语句子查询查询出年龄小于20的语句如下：select姓名，学校，地址，年龄fromTable_2where姓名in（select姓名fromTable_2where年龄＜20）执行结果：结果卧消息|姓名学校'地址年龄|1i小車j:m南2张与新东方北京14SQL补充学习（八）：在多表查询中使用子查询在多表查询中使用子查询在多表查询中使用子查询实例样例数据：’fAaFAM^BATA...dbo.Iable5WABFAJTGDkTA...dbo.Table.序昙班级年龄115E45Q454E6567615756036眦£/imML要求：输出语文成绩高于70分的学生的序号班级年龄语句如下：SELECT序号,班级,年龄FROMTable_5where序号in（select序号fromTable_4where语文＞70）SQLSQL补充学习（十）：ANY运算符和ALL运算符的子查询执行结果：SQL补充学习（九）：使用EXISTS语句的子查询使用EXISTS语句的子查询EXISTS语句用来测试集合是否为空，在子查询中返回true和false。通常情况下，EXISTS语句与子查询结合使用，并且只要子查询至少返回一个值，则EXISTS语句的值为true。9.1使用EXISTS语句的子查询样例莎HA1JFAN切收丸..吐mT命"5…一砒■工話・4丫-WAHFOGDATA...dbo.Table2序号姓名语文數字,地理庾枣■5虫虫6775他f596名名757755斗虹红罚斗4的47834558775Mi...dLo.Iable5WAHFAJTGLkTA...dbo.Table.序昙班级•年龄115E453454565676157560酬6ML/imML查询在Table_4中有成绩的学生信息select序号,班级,年龄fromTable_5asawhereexists（select*fromTable_4where序号=a.序号）执行结果：序号班级5625E586固结果|自执行结果：序号班级5625E586固结果|自消息|EXISTS表达式前面还可以加上NOT运算符，表示不存在某个子查询条件中。比如上面的代码前加notselect序号,班级,年龄fromTable_5asawherenotexists（select*fromTable_4where序号=a.序号）执行结果：同皓果］E备消息序号班级|年龄1545■34疔斗75610.ANY运算符的子查询和ALL运算符的子查询在子查询中经过会用到any运算符，表示跟子查询中的每一个值进行比较。在比较过程中,如果有一个值为真，则整个子查询结果集的比较值就为真。all运算符与any不同在于，它需要所有比较结果都为真，整个子查询结果集才为真。10.1ANY运算符的子查询实例要求：从表中查询除了语文成绩最低的人以为的所有人的各科成绩。select*fromTable_4where语文＞any(select语文fromTable_4)执行结果：討结果鞘息序号「姓名］语「数…地建历史［［5|虫虫6名名8土小G775NULL757143斜更0759.55?510.2比较运算符与any连用时的取值比较运算符+any所取子查询的结果“〉、＞二、！〈”+“any”取最小值“二”+“any”取所有值，相当于in“◊”+“any”相当于notin“＜、＜=、！＞”+“any”最大值SQLSQL补充学习（十一）：Having语句的子查询SQLSQL补充学习（十一）：Having语句的子查询因为any表示认识一个的意思，比如大于，大于任何一个值即可，所以只要大于最小值就可以了。10.3ALL运算符的子查询实例样例数据：要求：查出分数scroe大于所有BEIJING地区学生score的学生信息语句：SELECT*FROM[mydata].[dbo].[Table_1]wherescore>all(selectscorefromTable_1whereaddress='BEIJING')执行结果：I的消息泊namej：^Ehooladdressscoredata1I+虫虫耳月天NULL90'^006-04-0300:00:00.000SQLSQL补充学习（十二）：嵌套子查询SQLSQL补充学习（十二）：嵌套子查询10.Having语句的子查询having只能与select语句一起使用，通常在groupby子句中使用，否则跟where子句一样。Having语句的子查询样例数据/fkBTASGDATJL...dbo.TabledWMTFAlTG-DATk...里号!姓名购物花即1LIming34,00002wanqzi'54.00003LIming4^.00004Liaodong69.00005-壬子50,0000卜■650.0000采71W4M2AW要求：查询出表中每个人的购物总花费，并输出大于人平均花费的人的信息SELECT姓名,sum（购物花费）as消费总额fromTable_3groupby姓名havingsum（购物花费）＞（selectAVG（购物花费）fromTable_3）执行结果：囿结果|陰消息姓名消费总额'129.0^76'84.983269.333412.嵌套子查询子查询也可以出现在其他子查询语句中，通常将位于其他子查询的子查询称为嵌套子查询。12.1嵌套子查询实例样例数据：语句如下：SELECT序号,班级,年龄fromTable_5where序号in(select序号fromTable_4where数学＞(selectAVG(数学)fromTable_4))执行结果：要求：Table_5中的查询出序号、班级、年龄以及Table_4中的语文的平均成绩，并要求学口结果岛消息1序号班级年舲角iE71;615生的语文成绩要低于平均语文成绩。selectdistincta.序号,a.班级,a.年龄，(selectAVG(语文)fromTable_4)as语文平均成绩fromTable_4asb,Table_5asawherea.序号in(select序号fromTable_4where语文＞(selectAVG(语文)fromTable_4))执行结果：a结果|由消息序号旺"年…丁语文平均成続G74S2、•卡1549SQL补充学习（十三）：CASE语句交叉表查询CASE13.交叉表查询使用交叉表查询不仅使数据容易管理，而且能生成一种方便阅读的表格数据。CASE语句CASE语句可以返回多个结果的表达式，将某个表达式与一组简单表达式进行比较，从而得到所需结果。13.1.1CASE语句语法Caseinput_expressionWHENwhen_expressionTHENresult_expression[…n][ELSEelse_expression]END13.1.2CASE语句实例样例数据：要求：查询出时间中三个时间段，每个人的总消费金额。语句：SELECT时间,sum（case姓名when'LIming'Then购物花费elsenullend）as'LIming',sum（case姓名when'wangzi'Then购物花费elsenullend）as'wangzi',sum（case姓名when'xiaodong'Then购物花费elsenullend）as'xiaodong'FROM[mydata].[dbo].[Table_3]groupby时间执行结果：1结黑-J消息时问|LImirigwangzi扁odorig12011-03；2300:00:00.00050.4433NULLNULL?2011-04-03W;00;OC,000J4.6SS&34.4463.3.3343.2011-04-3030:00:00.00043；987750.543^:：NULL要求：查询出三个人在每个时间段的消费金额SELECT姓名,sum(case时间when'2011-03-2300:00:00.000'Then购物花费elsenullend)as'2011-03-2300:00:00.000',sum(case时间when'2011-04-0300:00:00.000'Then购物花费elsenullend)as'2011-04-0300:00:00.000',sum(case时间when'2011-04-3000:00:00.000'Then购物花费elsenullend)as'2011-04-3000:00:00.000'FROM[mydata].[dbo].[Table_3]groupby姓名执行结果：園结弟弗消息姓名2011-03-Z300:00:00.0002011-04-0300:0000.0002011-04-3000:00:00.0001LIming50.4433f,：'Ja4.66GG'43.937?士wangziNULL34.4450.5433','wiaodongNULL69.3334NULLSQL补充学习（十五）：select和selectinto语句插入数据15.select和selectinto语句插入数据15.1select语句插入数据用insertintoselect语句插入数据的一个重要用途就是备份表。15.1.1select语句插入数据语法insertinto表名1select列名from表名2where条件15.1.2select语句插入数据实例帀邯凰切垃丸..心o.TabljF丿曾ABTAJGIllJjrT一起。一工話.4”WAHFAUGDATA...dbo.Tal.le2序号姓名语文数学地理历史■5虫虫6775596名名757755斗虹红益吟447834558775卜楽AU2MJLLMLA/ULLAU2MJLL要求：创建新表，将上面数据中语文大于60分的人的成绩备份。语句：createtable表4备份表(序号int,姓名varchar(20)null,语文intnull,数学intnull,地理intnull,历史intnull)goinsertinto表4备份表select*fromTable_4where语文＞60select*from表4备份表执行结果：o语句插入数据o也可向表中插入数据。o语法select列名,列名,.into表名2from表名1where条件语句o实例样例数据：要求：将序号1-5的人成绩备份到新表1-5中select序号,姓名,语文,数学,地理,历史into前5fromTable_4where序号between1and5select*from前5执行结果：结果1□消息序号姓名语…数…|地理历史茫［虫玄6775NULL5924红红；23448347注：每次最后的select*只是为了查看操作的效果。SQL补充学习（十六）：使用子查询插入数据信息16使用子查询插入数据信息使用子查询插入数据信息实际上是insertinto…se语句，并将子查询放置在查询条件中。16使用子查询插入数据信息实例样例数据：..dbc.Table$-/lASTkH^BATA...dLo.TaBle<WJJJFOGDATA...dbo.Table2姓名语文數学地理历史■5虫虫6775W/59名名75774355斗虹红2344894734556775/1MZML磁fW/A/m

dinTable5dinTable5WWA2TGDHA...dbo.Table.班级年熬115斗53.4S'4565676157568蕉6眦£/im要求：将在表4中有成绩的同学的序号、班级和年龄备份到新表中。createtable备份表3（序号int,班级intnull,年龄intnull）insertinto备份表3select序号,班级,年龄fromTable_5asTwhereEXISTS（select序号fromTable_4where序号=T.序号）orderby序号select*from备份表3执行结果：詰果■i|J消息I班级［年魏—i°|5'62、67TjE154836SQL补充学习（十七）：使用SQL删除数据17使用SQL删除数据

样例数据：z^ABFAH&DATA...lbo.Table5?-MFAJItJDkTA...dbo-Tstl.—班级1年嚴►115乞4吕3斗54E65f7&1575：&&365461051155/IU£Am/bUZ帀硼翹DAT丸..心o.TabljF/tAWAJGnibh…Mb。--WAHFA1TGDATA...dbo.Table2序号姓名语文數字地理匝史5虫虫6775596名名J57755斗虹红44的47e小爪558775Ml..VLU4WMlMJL117.1删除指定条件的单行数据要求：删除Table_5中序号为11的记录行deletefromTable_5where序号=11select*fromTable_5执行结果：回皓果岛消1.11序号班一.|：年鹼hT52'…二453’_4544-5&5561e157568E2G'9a-4〕Q5.5删除指定条件的多行记录要求：在上述基础上，删除Table_5中序号在8-10之间的记录deletefromTable_5where序号between8and10select*fromTable_5执行结果：■E1结果][序号班..!年龄1!15224.5345445G5561G615115617.3删除所有记录删除前面备份的前5语句如下：deletefrom前5select*from前5执行结果：17.4使用TRUNCATETABLE语句删除数据truncatetable语句用于删除表中所有数据，它比用delete省略where删除所有数据要快得多。区别在于:delete是一次一行删除，在事务处理日志中记录相关删除操作及删除行中的列值，这样在删除失败时可以用事务处理日志回复数据。但truncatetalbe则一次性删除所有数据，事务处理日志不能回复。17.5子查询删除数据信息删除Table_4中数学成绩小于平均分的人的信息语句：deletefromTable_4where数学v(selectAVG(数学)fromTable_4)select*fromTable_413•结果|虫消息.|序号姓名’语…数…地理j虫虫67'75NULL592_6名名75774355SQL补充学习（十七）：使用SQL修改数据17.使用SQL修改数据17.1使用SQL修改数据语法update表名set列名1,列名2=值,…where条件样例数据：

■帀X1JFAN亦收丸..吐o.Tab：LeEXAaFAJCHJbh…一曲。一WAHFOGDATA...dbo.Tabled序号姓名语文辔地理历史■5虫虫6775/bi血596名名.75774355斗虹红斗4逋478岭4558775卜崇MlMl修改制定条件的单行数据要求：将Table_5中序号为5的学生的班级更改为1语句：updateTable_5set班级=1where序号=5select*fromeTable_5执行结果：

圉结果於消息|序号班…年龄i1'l"5453斗牙456517；6'6157755改制定条件的多行数据将上表中年龄＜6的人的年龄编到1班去updateTable_5set班级=1where年龄＜6select*fromTable_5执行结果：園鳍果|墻消息|序号班级年龄123h11213T54.4.561J51—7b6.15775■617.4使用子查询修改数据记录虫虫的语文成绩加上语文成绩*0.2语句：updateTable_4set语文=语文+语文*0.2where语文v(selectAVG(语文)fromTable_4)select*fromTable_4执行结果：|二结果］匕消息|j„j,,j'—rr:—」，，—~r序导姓名语…數…地理阳史|"s"""""虫虫8075NULL59G名名Z5??4355a4红红2了448372.404055S77517.5修改所有数据updata表名set列名=值,…要求：将表5中的年龄全部设置为8岁语句：updateTable_5set年龄=8select*fromTable_5SQL补充学习（十八）：SQLserver中的事务18.SQLserver中的事务事务是指一系列语句组成的逻辑工作单元，其中可以包括许多操作，但是它们在逻辑上是个整体，要么全部完成，要么全部失败。关键词说明：begintran开始事务committran提交事务starttransaction执行开始事务setconstrains在当前事务中设置约束settransaction设置下一个要执行的事务属性savepoint在事务中设置保存点，可以理解为断点，用来标识重新开始事务的位置。releasesavepoint释放保存点rollback标识中止事务18.1SQLserver中的事务语法begintran事务名称SQL语句操作committran事务名称使用事务修改数据信息样例数据：$消息|序号姓名语…数…地理I碇Ti580'75NULL5926名名击77435524红红448572840558775要求：使用事务将语文成绩小于30分的人语文成绩给30分begintranupdate_scoreupdateTable_4set语文=30where语文<30committranupdate_scoreselect*fromTa