SQLserver基础理论学习

1、SQL server基础理论学习 ：信息体系结构原则 1、信息体系结构原则1.1信息管理的3个主要方面：数据库设计和开发、企业数据中心的管理、商业智能分析1.2信息体系结构原则首先必须有详细的信息清单，包括信息的位置、来源、敏感性、当前和未来信息的价值以及当前的拥有者1.3要确保数据能够在未来使用，必须降低当前设计的耦合程度。1.4数据存储目标1.4.1简单性要对复杂性感到敬畏，应不断调整设计，直到整个设计小组都认为它是能够满足需求的最简单解决方案。字典中“困难”的同义词包含“复杂”是有道理的。简单的解决方案通常是优雅而容易理解的。（应让事情尽可能简单，且不能再简单爱因斯坦）做到简单性的要求：

2、（1）对需求有全面认识（2）掌握大量可供参考的模式和解决方案（3）对技术规则和行业术语有全面认识（4）创造性掌握行业知识，直到何时及如何改善技术系统（5）对用于实现设计的工具和设备以及解决方案的运行环境有深入认识（6）设计人员互相信任、分享想法而不考虑个人得失（7）不断优化设计，直到设计出优雅而简单的设计方案（8）对复杂性抱有健康的敬畏心态1.4.2有用性有用性包括三点：（1）满足组织需求（2）适应性：设计与数据存储的范围和用途相称数据存储设计模型包括：关系DBMS、对象数据库、对象/关系数据库、通用模式、数据仓储数据库的范围包含数据在组织内的使用度（个人、部门、营业单位、公司、企业）以及数据

3、的持续性（当前、当年、永远）（3）数据格式可供应用程序使用以及易于提取数据存储配置包括5种类型：（1）主数据存储：运营数据库或联机数据处理数据库，用于收集第一代交易数据。在组织中必不可少，且是唯一的。主数据库存储需要在方便数据检索和数据更新之间取得平衡。每个运营单位都可能有个主数据存储。（2）缓存数据存储：可选，是主数据存储的只读拷贝，用于提供数据，减轻主数据库的负载。优化它，可提供数据库检索性能。（3）引用数据存储：主要为只读，存储组织中较少变更的数据。类似图书馆的目录区。优化它，也可很高检索性能。（4）数据仓库：从企业多个主数据存储收集大量数据，并使用提取、转化和加载过程，将格式和架构转为

4、通用，简化数据检索。同时存储历史记录，减轻运营数据存储的负载。组织必须有一个通用的数据仓库。主要任务是数据检索和分析。（5）数据集市：数据仓库的子集，包含经过预先汇总的数据。主数据存储的设计方法（1）关系DBMS传统的数据库，它讲类似或相关的数据放到一个表中。适合满足稳定的数据架构需求，这种需求只包含少量的is_a关系。（2）面向对象的DBMSOO DBMS必须存储对象，因此判断OO DBMS优劣的一个重要标准就是它能否存储复杂的对象，如XML文件、.net文件OO DBMS非常适合这样的程序：架构可能发生重大变化、包括复杂数据类型、类之间涉及多个is-a关系、包括复杂的多关联以及要求将数据同

5、应用程序轻松关联起来。有三种主要的面向对象的数据库A对象持久化数据存储（OP DBMS）:相当于一个存储对象的状态仓库B面向对象的数据存储（OO DBMS）C对象/关系数据存储（O/R DBMS）（3）通用模式DBMS也称为动态钻石模式，有时被用作关系数据库产品中的面向对象的数据库设计。在应用程序需要动态属性时，这种设计非常有用。1.4.3完整性数据实际上是实体和属性，因此数据完整性由实体完整性和域完整性组成。（1）实体完整性：本质就是规范化（2）域完整新：保证属性只取有效值，包括为空性。（3）引用完整性：外键域的完整性（4）用户定义完整性（5）事务完整性：事务是一个逻辑工作单元，如插入100

6、行。数据库产品的质量是根据其事务对ACID属性的遵循程度衡量的。A（atomicity）原子性：事务必须是原子的，这意味着在事务结束时，耀目所有操作成功，要么都失败。C(consistency)一致性：这意味着在事务执行前数据库处于一致性状态，事务结束后，数据库回到一致性状态。一致性意味着每行和每个值都与描述一致且满足所有的约束。I（isolation）隔离性：每个事务都必须是隔离的，不受其他事务的影响，不管是否有其他事务执行，它都必须使用它开始时的数据集继续执行下去。D（durability）持久性：不管系统是否发生故障，事务的结果都是永久性的。（6）事务缺陷脏读：如果事务能够读取其他商务尚

7、未提交的更新，则被称为脏读。脏读可能导致在读取的数据未提交前，而写入该数据的事务可能被回滚。不可重复读：类似脏读，指事务能看到其他事务已提交的数据更新。如果两次读取得到的结果不同，则存在不可重复读性事务缺陷。虚幻行：危害最小，指事务的更新操作不仅影响结果集的数据值，还导致select语句返回不同记录集。丢失更新：两个用户同时更新一行数据时，第二个覆盖了第一个的更新，导致更新丢失。死锁：多个项目的多个事务争用一个数据资源时容易发生死锁，需要一个事务主动放弃或退出。（7）隔离级别：未提交读、已提交读、可重复读和可序列化，2005还引入了快照隔离，但容易导致更新丢失。1.4.4性能物理磁盘的性能是最

8、严重的瓶颈，因此提高性能的关键之处在于减少执行任务时需要读取的物理页数量。提高性能的技术有：（1）设计：简化设计，不要包含过多的表，过多游标等（2）基于集合的处理：基于集合的优秀查询能够对整个数据集进行处理，且只读取一次所需数据页，而基于游标的解决方案分别对每一行进行处理。（3）索引是查询和数据之间的桥梁，它能减少执行表读取操作时需要读取的物理页数。要更精确滴设计索引，必须了解数据库引擎的查询优化器和索引结果的工作原理。索引有主力提高数据读取的性能，但给写入性能带来负面影响，插入和更新数据时，要确保索引数据同步。（4）分区：将数据分散到多个磁盘中。（5）缓存：预先从磁盘中读取数据，以便数据库操

9、作需求时能够在内存中找到它们。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）

10、限制访问（2）信息所有权（3）审核跟踪1.5优化理论和SQL server(1)架构设计避免过于复杂精心挑选键处理可选数据实现抽象层（2）查询问题最佳解决方案复杂的业务逻辑查询、子查询、CTE动态生成DDL游标对列表进行逆规范化多赋值标量和游标交叉表包含透视和case表达式的查询导航层次结构UDF或CTE计算累计总计和动态和游标（3）索引 （4）并发性：锁定和阻塞问题。nolock并不是很好的解决方式，很危险。可以采用缩短事务的持续时间来实现。（5）高级伸缩性快照隔离分区表索引视图service brokerSQL server基础理论学习 ：关系数据库建模3.关系数据库建模3.1模拟现实数据

11、建模过程：（1）手机观察结果和需求（2）现实世界的逻辑表示（3）可见实体的识别和设计（4）架构设计（辅助和支持实体）（5）应用程序结构设计3.2可见实体可见实体通常是大多数人能够识别的对象。（1）每个元组（行）都是独立的（2）主键（3）表、行和列3.3识别多个实体（1）多个对象 （2）对象之间的关系 （3）组织对象 （4）一致的查找值 （5）复杂对象3.4关系模式关系使用键将实体关联起来，包含如下两个主要属性：基数：在关系的每一端可能存在的对象数可选性：关系是可选的也是强制的（1）辅助实体和外键两个对象彼此相关联时，通常一个实体为主实体，另一个实体为辅助实体。主实体中的一个对象将关联到辅助实体

12、中的多个对象或元组。（2）关系基数指在关系的每一端有多少元组。在关系的主键端只能有一个元组，而在外键端可以有多个元组。（3）关系的可选性：可选关系和强制关系对于数据库的完整性非常重要。（4）绘制数据模型关系图Chen ER关系图绘制方法非常简单，用sisio即可。（5）关系种类一对多 一对一 超类/子类 多对多（6）类别实体有时也称为查找表，（7）自反关系在两个相同类型的实体之间建立关系，比如人与人之间的上下级关系，家庭成员之间的关系等。3.5规范化规范化是减少更新异常的工具。（1）实体/属性的设计原则：每个数据库实体必须描述一个“事务”属性和事务之间必须存在一对一的关系每个属性必须描述其所属

13、实体，而不能描述其相关的实体。（2）范式规范化通常以范式的方式定义。每种范式都描述了设计实体和属性时可能出现的错误，并提供了纠正这种错误的规则。因此范式类似于书面语言的语法规则。规范化数据库与平面文件数据库比，优点如下：消除重复存储减少锁的争用缩小文件数据建模人员应该一开始就按照第三范式设计逻辑架构。(3)第一范式符合条件A每个数据单元都是用标量属性表示的，标量值指的是“可以用标尺上的一个点表示”的值B所有数据必须用唯一属性表示，每个属性都必须有唯一的名称和用途。C所有的数据都必须用唯一的元组表示。（4）第二范式确保每个属性是实体属性。每个属性都必须有对应的主键。如果其他属性只依赖于主键的一个

14、属性，而不依赖于主键的其他属性，这被称为部分依赖，违反了第二范式。（5）第三范式检查传递依赖。传递依赖和部分依赖的相似之处在于，他们值得都是属性不完全依赖于主键。当A依赖于B，B依赖于主键时，就发生了传递依赖。同第二范式一样，解决方式是将非依赖属性移到新实体中。*如果实体有很好的主键，每个属性都是标量且都完全依赖于主键，则逻辑设计符合第三方式，大多数数据库设计满足第三范式即可。（6）BCNF范式位于三四范式之间，用于处理实体有两组主键的问题。BCNF规定，在这种情况下，应将实体拆分为两个实体，每个实体包含一个主键。（7）第四范式用于处理复杂的符合主键导致的问题，如果将两个相互独立的属性组合成主

15、键，同时还要有第三个属性，如果没有第三个属性，仅靠着两个属性不能唯一标识实体，则违反了第四范式。（8）第五范式用于设计涉及多个（3个以上）实体的复杂关系，如果设计正确可以设计出三元关系，其中每个关系的基数都可以是一或多。3.6关系代数关系代数有8个关系运算符组成（1）限制：返回符合制定条件的行（2）投影：从数据集中返回指定列（3）积：关系乘法，返回两个数据集的所有可能的数据集合。（4）联合：关系加法和减法，将一个表垂直堆叠到另一个表上，将两个表合并（5）交集：返回两个表中都有的行（6）差集：返回一个数据集中特有的行（7）连接：返回两个表的水平合并结果，合并是根据都有的行来匹配（8）除：返回两个

16、数据集中完全匹配的内容另外sql server还开发以下功能：子查询在关系代数中，使用以下术语：表或数据集被称为关系或实体行被称为元组列被称为属性SQL server基础理论学习 ：SQL server 2005体系结构4 SQL server 2005体系结构4.1数据访问体系结构sql server 首先是一个数据库服务器（1）客户/服务器数据库模型客户/服务器数据库介绍：客户进程请求服务器进程提供服务，后者处理客户发出的请求。所有的数据库工作都是在数据库服务器中执行的，完成后讲数据集的拷贝发送到客户端，实际的数据和索引不会离开服务器。相对于桌面数据库（acess），它性能更高，更安全可靠

17、。客户/服务器数据库职能数据库服务器职责：A处理数据修改和检索要求B执行数据密集型处理C实施所有的数据库规则和约束D实现数据安全性数据库客户端职责：A以易于识别、引人入胜和适用的格式将数据呈现给用户B提供使用各种工具、数据和报表的界面C将请求提交给服务器（2）N层设计在客户/服务器应用程序中，除了客户端和服务器进程外，还涉及其他进程，经常使用中间层来处理连接、连接池和业务逻辑。使用中间层可以让多个用户使用为数不多的数据库服务器连接，到要考虑用户的身份验证方式。在有多个服务器可用的情况下，通过使用通用连接对象，在服务器A发生故障时，将用户从服务器A切换到服务器B将更容易，连接对象成为能够检测到故

18、障并在服务器之间切换的单个点。这种解决方案在使用数据库镜像时非常有用。（3）面向服务的体系结构面向服务的体系结构（SOA）是客户/服务器数据库模型的替代品，SOA使用标准的HTTP调用和XML，而不是多个系统之间编写自定义的客户/服务器接口，这样多个系统可以用相同的连接口。SOA使用与有多个超大型系统的大企业，适应用作辅助数据访问方法，以简化同其他超大型系统的通信，除此，不要使用SOA4.2 SQL server中的服务SQL server不仅仅是一个关系数据库引擎，它是一组与数据库相关的服务和组件，可结合使用它们来创建功能强大的数据库后端解决方案。（1）关系引擎关系引擎有时也被称为数据库引擎

19、，它是sql server的核心，该进程负责处理所有关系数据库的工作。sql是一种描述性语言，这意味着SQL向引擎描述要处理的查询，引擎负责完成其他工作。SQL server支持在同一台物理服务器中安装多达50个关系引擎实例，虽然它们共享一些组件，但每个实例都是一个独立的SQL server(2)Transact-SQLSQL 小组扩展了ANSI定义，在很多方面进行了改进，添加了新命令，删了一些命令，这就是Transact-SQL(T-SQL),SQL server只理解一种语言，那就是T-SQL。（3）Visual Studio 和CLRSQL server最激动人心的功能之一是集成了vis

20、ual Studio.Management Studio是基于Visual Studio集成开发环境（IDE），SQL server内部操作系统（SQLOS）实际上是在SQL server内部托管（host）了.net的共同语言运行时（CLR）名词解释：CLR是公共语言运行时，Common Language Runtime)和Java虚拟机一样也是一个运行时环境，它负责资源管理（内存分配和垃圾收集），并保证应用和底层操作系统之间必要的分离。IDE，即Integrated Development Environment，是“集成开发环境”的英文缩写，可以辅助开发程序的应用软件。软件是用于程序开发

21、环境的应用程序，一般包括代码编辑器、编译器、调试器和图形用户界面工具。就是集成了代码编写功能、分析功能、编译功能、debug功能等一体化的开发软件套。所有具备这一特性的软件或者软件套（组）都可以叫做IDE。如微软的VisualStudio系列，Borland的C+Builder,Delphi系列等。该程序可以独立运行，也可以和其它程序并用。（4）Service Brokersql server2005新增的功能，通过一个托管数据队列，通过平滑负载，提高了性能和伸缩性。创建面向服务的体系结构时，Service Broker是一个重要服务。（5）复制服务（6）全文搜索服务 通过windows 管理

22、工具>服务来启动和停止（7）Notification Services可通过编程使其根据数据变更发送信息，消息几乎可以发送给任何设备。（8）服务器管理对象（SMO）它是暴露sql server的配置和管理功能的一组对象，使得能够在.net前段代码中使用它们。（9）SQL 代理可选进程，可配置为系统启动时自动运行。（10）分布式事务协调器（DTC）负责以两阶段的方式提交跨越多个SQL server的事务。通过windows 管理工具>服务来启动和停止（11）SQL mail让sql server能够通过SMTP将电子与偶见发送到外部邮箱。4.3 Business Intelligen

23、ce Services(BI)商业智能（BI）是SQL server2005最擅长的领域之一。SQL server2005提供了三种智能服务：（1）Integration Services(IS or SSIS，它表示SQL Server Integration Services)：几乎可以在任何数据源之间传输数据，它是SQL server的提取-转换-加载（ETL）工具。BI使用图形工具来定义如何将数据从一个数据源移到另一个数据源。IS包能够复制数据列，执行复杂的转换和处理数据移动发生的异常。IS非常适用于数据转换。（2）Reporting Services(RS)（3）Analysis S

24、ervices(AS)：是一种提供商业智能或联机分析处理的服务。能让开发人员定义包含多维的数据集。多维数据集中包含根据超大型数据库预先计算的汇总数据，让用户快捷浏览多维数据集中的总计和小计。这三种服务都是使用Business Intelligence Development Studio开发的。4.4 SQL server 2005的版本这里说的版本（edition）指的是包含的功能集不能的安装包。而不是SQL server 2000 SQL server 2005等版本（version）企业（开发）版标准版工作组版SQL server速成版SQL server everywhere 版4.5

25、 SQL server的工具和组件（1）SQL server management studio供管理员和程序开发人员使用的类似visual studio的集成环境，其核心是强大的对象资源管理器。（2）SQL Server Configuration Manager这个工具用于启动和停止各种服务器，可以通过开始菜单或management studio启动它。（3）外围应用配置器默认状况下，很多sql server的功能都是被禁用，以减少sql server的暴露程度，这些功能大部分都可以使用T-SQL来启用。（4）Business Intelligence Development Studio

26、类似management studio，但针对BI进行优化。（5）SQL集成帮助（6）SQL Profiler监视sql server的流量和事件（7）性能监视器（8）数据库优化顾问（9）命令行实用程序：SQLCmd Bulk Copy这些能够让开发人员在DOS提示符下或命令行调度程序中执行SQL代码或大容量复制操作。4.6 SQL server 2005功能包下载包提供了二十多个驱动程序以及支持向后兼容及其他系统兼容的代码，还包含以下有用的工具有实用程序：SQL server升级顾问数据库迁移助理最佳实践工具4.7 AdventureWorks示例数据库，替代以前的Northwind 和Pu

27、bs4.8探索元数据SQL server使用四个系统数据库进行自我管理，另外还有一个model数据库。（1）Master:包含有关服务器中数据库的信息（2）MSDB：包含诸如备份和作业等活动列表（3）Tempdb:包含用户、批处理、存储过程和SQL server引擎本书使用的临时表。（4）Reference:该数据库被隐藏，从master中分离出来，旨在使得升级服务包更容易。（5）Model:用于创建新数据库的模板数据库。元数据是有关数据的信息，SQL server支持4种元数据：目录视图：提供有关静态元数据的信息动态管理视图和函数：通过探索服务器的当前状态，提供有关诸如内存、线程、缓存中的存

28、储过程和连接等的数据。兼容性视图：模拟以前版本中的表，用于向后兼容系统架构视图：ANSI SQL 92标准视图。可用于查看任何数据库产品的架构。SQL补充学习（一）：CROSS JOIN1.CROSS JOIN除了内连接(inner jion)，外连接（left join ,right join,full join），还支持交叉连接。交叉连接返回被连接两个表的卡迪尔乘积，即：1.2 CROSS JOIN 语法select 列名from 表名1 cross join 表名2where 表名1.列名=表名2.列名1.3 CROSS JOIN 实例样例数据：要求：用cross join 语句找出两表

29、中学生序号相同的学生，并将其所有字段组合在一个表中。select a.序号,b.姓名,a.班级,a.年龄,b.语文,b.数学,b.地理,b.历史from Table_4 as b cross join Table_5 as awhere a.序号=b.序号执行结果：我们可以发现，用下面语句，效果是一致的：select a.序号,b.姓名,a.班级,a.年龄,b.语文,b.数学,b.地理,b.历史from Table_4 as b , Table_5 as awhere a.序号=b.序号注：实际上将cross join替换成“,”，效果相同。如果不加后面的where条件语句，将会返回32行。

30、SQL补充学习（二）：自身连接自身连接是指数据表与其自身产生连接，因此在实现时，需要使用别名来配合使用。2.1自身连接实例样例数据：用自身连接得出历史成绩大于70的人的信息。语句：select a.序号,a.姓名,a.语文,a.数学,a.地理,a.历史from Table_4 as a,Table_4 as bwhere a.序号=b.序号and b.历史>70执行结果：可以看出，它与下面语句效果一致：select * from Table_4where 历史>70注：实际上自身连接的操作对象是数据表本身。SQL补充学习（三）：INTERSECT语句和EXCEPT语句3.INTER

31、SECT语句和EXCEPT语句intersect语句可以用来查两个表中的“公共”数据信息，即“交叉”数据信息。INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。EXCEPT语句则是用来查询上述“公共”信息“除外”才信息。EXCEPT 从左查询中返回右查询没有找到的所有非重复值。而与UNION联合查询语句一样，INTERSECT语句和EXCEPT语句查询的两个表必须具有相同的结构，即他们的列必须相似，数目相等，顺序相同，且数据类型兼容。3.1 INTERSECT语句和EXCEPT语句select 列名 from 表名1intersectselect 列名

32、 from 表名2select 列名 from 表名1exceptselect 列名 from 表名23.2intersect实例样例数据要求：求出上面两表中的共同部分。SELECT id,姓名,学校,地址,时间FROM mydata.dbo.Table_6intersectSELECT id,name,school,address,dataFROM mydata.dbo.Table_1执行结果：要求：输出上面两表中共同信息除外的信息SELECT id,姓名,学校,地址,时间FROM mydata.dbo.Table_6exceptSELECT id,name,school,address,d

33、ataFROM mydata.dbo.Table_1执行结果：注：输出结果列名与第一个select 语句中的表的列名一致。SQL补充学习（四）：在select语句中使用子查询4.子查询子查询是指将查询结果作为另一个查询参数的查询。4.1 在select语句中使用子查询实例要求：要求求出每个学生与平均年龄的差距语句：select姓名,年龄,(select AVG(年龄)from Table_2) as 平均年龄,(年龄-(select AVG(年龄)from Table_2)as 与平均年龄差距from Table_2执行结果：注：如果要对结果进行排序，后面可以加 order by 与平均年龄差

34、距SQL补充学习（五）：在from语句中使用子语句5.在from语句中使用子语句在select语句中的from子句中，也经常会用到子查询。5.1在from语句中使用子语句实例样例数据：要求：输出学生各科成绩总分，按从高到底排select *from (select 姓名,sum(语文+数学+历史+地理)as 总分 from Table_4 group by 姓名) tab1order by 总分执行结果：SQL补充学习（六）：在where语句中使用子查询6.在where语句中使用子查询6.1实例：样例数据要求：要求求出年龄大于平均年龄的每个学生与平均年龄的差距语句：select姓名,年龄,(s

35、elect AVG(年龄)from Table_2) as 平均年龄,(年龄-(select AVG(年龄)from Table_2)as 与平均年龄差距from Table_2where 年龄>=(select AVG(年龄)from Table_2)执行结果：SQL补充学习（七）：IN语句的子查询7.in语句的子查询7.1 in语句的子查询实例样例数据要求：用in语句子查询查询出年龄小于20的语句如下：select 姓名,学校,地址,年龄from Table_2where 姓名 in(select 姓名 from Table_2 where 年龄<20)执行结果：SQL补充学习

36、（八）：在多表查询中使用子查询8.在多表查询中使用子查询8.1在多表查询中使用子查询实例样例数据：要求：输出语文成绩高于70分的学生的序号 班级 年龄语句如下：SELECT 序号,班级,年龄FROM Table_5where 序号 in (select 序号 from Table_4 where 语文>70)执行结果：SQL补充学习（九）：使用EXISTS语句的子查询9.使用EXISTS语句的子查询EXISTS语句用来测试集合是否为空，在子查询中返回true和false。通常情况下，EXISTS语句与子查询结合使用，并且只要子查询至少返回一个值，则EXISTS语句的值为true。9.1使

37、用EXISTS语句的子查询样例查询在Table_4中有成绩的学生信息select 序号,班级,年龄from Table_5 as awhere exists (select * from Table_4 where 序号=a.序号)执行结果：EXISTS表达式前面还可以加上NOT运算符，表示不存在某个子查询条件中。比如上面的代码前加notselect 序号,班级,年龄from Table_5 as awhere not exists (select * from Table_4 where 序号=a.序号)执行结果：SQL补充学习（十）：ANY运算符和ALL运算符的子查询10.ANY运算符的子

38、查询和ALL运算符的子查询在子查询中经过会用到any运算符，表示跟子查询中的每一个值进行比较。在比较过程中，如果有一个值为真，则整个子查询结果集的比较值就为真。all运算符与any不同在于，它需要所有比较结果都为真，整个子查询结果集才为真。10.1 ANY运算符的子查询实例要求：从表中查询除了语文成绩最低的人以为的所有人的各科成绩。select * from Table_4where 语文>any (select 语文 from Table_4)执行结果：10.2 比较运算符与any连用时的取值比较运算符+any所取子查询的结果“>、>=、!<”+“any”取最小值“=

39、”+“any”取所有值，相当于in“<>”+“any”相当于not in“<、<=、!>” +“any”最大值因为any表示认识一个的意思，比如大于，大于任何一个值即可，所以只要大于最小值就可以了。10.3 ALL运算符的子查询实例样例数据：要求：查出分数scroe大于所有BEIJING地区学生score的学生信息语句：SELECT *FROM mydata.dbo.Table_1where score>all(select score from Table_1 where address='BEIJING')执行结果：SQL补充学习（十一）

40、：Having语句的子查询10.Having语句的子查询having只能与select语句一起使用，通常在group by子句中使用，否则跟where子句一样。10.Having语句的子查询样例数据要求：查询出表中每个人的购物总花费，并输出大于人平均花费的人的信息SELECT 姓名,sum(购物花费)as 消费总额from Table_3group by 姓名having sum(购物花费)>(select AVG(购物花费)from Table_3)执行结果：SQL补充学习（十二）：嵌套子查询12.嵌套子查询子查询也可以出现在其他子查询语句中，通常将位于其他子查询的子查询称为嵌套子查询

41、。12.1嵌套子查询实例样例数据：要求：查询出学生编号、班级、年龄，并且要求该学生的数学成绩大于平均数学成绩语句如下：SELECT 序号,班级,年龄from Table_5where 序号 in (select 序号 from Table_4 where 数学>(select AVG(数学)from Table_4)执行结果：要求：Table_5中的查询出序号、班级、年龄以及Table_4中的语文的平均成绩，并要求学生的语文成绩要低于平均语文成绩。select distinct a.序号,a.班级,a.年龄,(select AVG(语文) from Table_4)as 语文平均成绩fr

42、om Table_4 as b,Table_5 as awhere a.序号 in(select 序号 from Table_4 where 语文>(select AVG(语文)from Table_4)执行结果：SQL补充学习（十三）：CASE语句交叉表查询 CASE13.交叉表查询使用交叉表查询不仅使数据容易管理，而且能生成一种方便阅读的表格数据。13.1 CASE语句CASE语句可以返回多个结果的表达式，将某个表达式与一组简单表达式进行比较，从而得到所需结果。13.1.1CASE语句语法Case input_expressionWHEN when_expressionTHEN re

43、sult_expressionnELSE else_expressionEND13.1.2CASE语句实例样例数据：要求：查询出时间中三个时间段，每个人的总消费金额。语句：SELECT 时间,sum(case 姓名 when 'LI ming' Then 购物花费 else null end)as 'LI ming',sum(case 姓名 when 'wangzi' Then 购物花费 else null end)as 'wangzi',sum(case 姓名 when 'xiaodong' Then 购物花费

44、else null end)as 'xiaodong'FROM mydata.dbo.Table_3group by 时间执行结果：要求：查询出三个人在每个时间段的消费金额SELECT 姓名,sum(case 时间 when '2011-03-23 00:00:00.000' Then 购物花费 else null end)as '2011-03-23 00:00:00.000',sum(case 时间 when '2011-04-03 00:00:00.000' Then 购物花费 else null end)as '2

45、011-04-03 00:00:00.000',sum(case 时间 when '2011-04-30 00:00:00.000' Then 购物花费 else null end)as '2011-04-30 00:00:00.000'FROM mydata.dbo.Table_3group by 姓名执行结果：SQL补充学习（十五）：select和select into语句插入数据15. select和select into语句插入数据15.1 select 语句插入数据用insert into select 语句插入数据的一个重要用途就是备份表。1

46、5.1.1 select 语句插入数据语法insert into 表名1select 列名 from 表名2where 条件15.1.2 select 语句插入数据实例要求：创建新表，将上面数据中语文大于60分的人的成绩备份。语句：create table 表4备份表(序号 int,姓名 varchar(20)null,语文 int null,数学 int null,地理 int null,历史 int null)goinsert into 表4备份表select * from Table_4where 语文>60select * from 表4备份表执行结果：15.2 select i

47、nto 语句插入数据select into也可向表中插入数据。15.2.1 select into 语法select 列名,列名,into 表名2from 表名1where 条件语句15.2.2 select into 实例样例数据：要求：将序号1-5的人成绩备份到新表 1-5中select 序号,姓名,语文,数学,地理,历史into 前5from Table_4where 序号 between 1 and 5select * from 前5执行结果：注：每次最后的select *只是为了查看操作的效果。SQL补充学习（十六）：使用子查询插入数据信息16使用子查询插入数据信息使用子查询插入数据

48、信息实际上是insert intoselect 语句，并将子查询放置在查询条件中。16使用子查询插入数据信息实例样例数据：要求：将在表4中有成绩的同学的序号、班级和年龄备份到新表中。create table 备份表3(序号 int,班级 int null,年龄 int null)insert into 备份表3select 序号,班级,年龄 from Table_5 as Twhere EXISTS(select 序号 from Table_4 where 序号=T.序号)order by 序号select * from 备份表3执行结果：SQL补充学习（十七）：使用SQL删除数据17 使用S

49、QL删除数据样例数据：17.1 删除指定条件的单行数据要求：删除Table_5中序号为11的记录行delete from Table_5where 序号=11select * from Table_5执行结果：17.2删除指定条件的多行记录要求：在上述基础上，删除Table_5中序号在8-10之间的记录。delete from Table_5where 序号 between 8 and 10select * from Table_5执行结果：17.3删除所有记录删除前面备份的 前5语句如下：delete from 前5select * from 前5执行结果：17.4使用TRUNCATE TA

50、BLE语句删除数据truncate table 语句用于删除表中所有数据，它比用delete 省略where删除所有数据要快得多。区别在于：delete 是一次一行删除，在事务处理日志中记录相关删除操作及删除行中的列值，这样在删除失败时可以用事务处理日志回复数据。但truncate talbe则一次性删除所有数据，事务处理日志不能回复。17.5 子查询删除数据信息删除Table_4中数学成绩小于平均分的人的信息语句：delete from Table_4where 数学<(select AVG(数学)from Table_4)select * from Table_4SQL补充学习（十七

51、）：使用SQL修改数据17.使用SQL修改数据17.1使用SQL修改数据语法update 表名set 列名1,列名2=值,where 条件样例数据：17.2修改制定条件的单行数据要求：将Table_5中序号为5的学生的班级更改为1语句：update Table_5set 班级=1where 序号=5select * frome Table_5执行结果：17.3改制定条件的多行数据将上表中年龄<6的人的年龄编到1班去update Table_5set 班级=1where 年龄<6select * from Table_5执行结果：17.4使用子查询修改数据记录虫虫的语文成绩加上语文成

52、绩*0.2语句：update Table_4set 语文=语文+语文*0.2where 语文<(select AVG(语文)from Table_4)select * from Table_4执行结果：17.5修改所有数据updata 表名set 列名=值,要求：将表5中的年龄全部设置为8岁语句：update Table_5set 年龄=8select * from Table_5SQL补充学习（十八）：SQL server中的事务18.SQL server中的事务事务是指一系列语句组成的逻辑工作单元，其中可以包括许多操作，但是它们在逻辑上是一个整体，要么全部完成，要么全部失败。关键词说

53、明：begin tran 开始事务commit tran 提交事务start transaction 执行开始事务set constrains 在当前事务中设置约束set transaction 设置下一个要执行的事务属性savepoint 在事务中设置保存点，可以理解为断点，用来标识重新开始事务的位置。release savepoint 释放保存点rollback 标识中止事务18.1 SQL server中的事务语法begin tran 事务名称SQL语句操作commit tran 事务名称18.2 使用事务修改数据信息样例数据：要求：使用事务将语文成绩小于30分的人语文成绩给30分begin tran update_scoreupdate Table_4 set 语文=30where 语文<30commit tran update_scoreselect * from Table_4执行结果：18.3 使用事务删除信息删除历史成绩>70的人的信息语句：begin tran DEL_scoredelete from Table_4where 历史<70commit tran DEL_scoreselect * from Table_4执行结果：18.4 使用事务保存数据信息要求：用事