2023年6月21日发(作者：)

sqlserver2012学习总结笔记Sqlserver2012 学习总结笔记第⼀节 创建和维护库sqlserver的版本企业版(Enterprise) 是⼀种综合的数据平台，可以为运⾏安全的业务关键应⽤程序提供企业级可扩展性、性能、⾼可⽤性和⾼级商业智能功能。标准版(Standard) 是⼀个提供易⽤性和可管理性的完整数据平台,部门级的应⽤提供⽀持。开发版(Developer) ⽀持开发⼈员构建基于 Server 的任⼀种类型的应⽤程序,拥有企业级的特性，但是有限制。⼯作组版(Workgroup) 是运⾏分⽀位置数据库的理想选择，它提供⼀个可靠的数据管理和报告平台，其中包括安全的远程同步和管理功能。⽹络版(Web) 对于为从⼩规模⾄⼤规模 Web 资产提供可扩展性和可管理性功能的 Web 宿主和⽹站来说成本低。移动版(Compact) 3.5 免费提供，是⽣成⽤于基于各种 Windows 平台的移动设备、桌⾯和 Web 客户端的独⽴和偶尔连接的应⽤程序的嵌⼊式数据库理想选择。免费版(Express) 适⽤于学习构建⼩型的应⽤。、sqlserver的数据库分类1.系统数据库master 数据库：master 数据库记录 SQL 系统的所有系统级别信息。它记录所有的登录帐户和系统配置设置。master 数据库是这样⼀个数据库，它记录所有其它的数据库，其中包括数据库⽂件的位置。master 数据库记录 SQL Server 的初始化信息，它始终有⼀个可⽤的最新 master 数据库备份。msdb 数据库:msdb 数据库供 SQL Server 代理程序调度警报和作业以及记录操作员时使⽤。tempdb 数据库:tempdb 数据库保存所有的临时表和临时存储过程。model 数据库:model 数据库⽤作在系统上创建的所有数据库的模板。当发出 CREATE DATABASE 语句时，新数据库的第⼀部分通过复制 model 数据库中的内容创建，剩余部分由空页填充。由于 SQL Server 每次启动时都要创建 tempdb 数据库，model 数据库必须⼀直存在于 SQLServer 系统中。Resource 数据库:Resource 数据库⼀个只读数据库，包含 SQL Server 包括的系统对象。系统对象在物理上保留在 Resource 数据库中，但在逻辑上显⽰在每个数据库的sys 架构中。2.⽤户数据库sqlserver数据库的组成sqlserver将数据库映射为⼀组可操作的⽂件:数据⽂件.mdf⽂件,主数据⽂件,只有⼀个,⼤⼩不得⼩于3Mb。.ndf⽂件,次要数据⽂件,可以0个或多个,可以在⼀个或者多个磁盘存放。⽇志⽂件.ldf⽂件，事务⽇志⽂件，⾄少有⼀个⽂件。数据库存储空间的分配创建⽤户数据库时，model数据库会被⾃动的复制到新建的数据库。数据库存储的最⼩单位是，数据页（page 简称 页）。1页是8KB的连续的磁盘存储空间。页的⼤⼩决定了数据库表⼀⾏数据的最⼤的⼤⼩。⾏不可以跨页存储。数据库⽂件组俩种类型的⽂件组：主⽂件组包含主要的数据⽂件和任何没有明确分配的其他⽂件组的其他数据⽂件，系统表的所有表页都分配在主⽂件组中。⽤户定义⽂件组：在定义或者修改数据库时⽤ ‘FILEGROUP’关键字指定。注意：：1.⽇志⽂件不在⽂件组中，⽇志⽂件和数据⽂件分开。2.⼀个⽂件不可以时多个⽂件组成员。3.若⽂件组含有多个⽂件，则在所有⽂件被填满后才会⾃动循环增长。4.⽂件加⼊数据库后，不能移动到其他⽂件组。5.只能指定⼀个⽂件组为默认⽂件组。数据⽂件的属性定义数据⽂件和⽇志⽂件的所有信息：⽂件名及位置：逻辑⽂件名，物理⽂件名。初始⼤⼩：不能⼩于model数据库主要⽂件的⼤⼩。增长⽅式：可以指定⽂件是否⾃动增长（默认）⽂件⼤⼩：⽂件增长的最⼤限制，默认⽆限制。T-sql创建数据库 server management studio2.T-sql创建数据库T-SQL格式语法举例：create databasedb_zhj --数据库名字on(name=‘db_zhj_data’,–逻辑数据⽂件名filename=‘E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datadatadb_zhj_’,–主数据库⽂件逻辑名字size=3mb,–主数据⽂件初始化⼤⼩maxsize=50Mb,–主数据⽂件最⼤⼤⼤⼩filegrowth=10% --超过初始化⼤⼩后⽂集⾃动增长率。)log on(name=‘db_zhj_log’,–逻辑⽇志⽂件名filename=‘E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datalogdb_zhj_’,–⽇志存放的逻辑物理名size=2mb,–⽇志初始化⼤⼩maxsize=50Mb,–⽇志最⼤⼤⼩filegrowth=1mb --超过初始化⼤⼩后⽂集⾃动增长率。)go修改数据库收缩数据库空间：即释放数据库中未使⽤的空间，收缩从尾部开始:⾃动收缩：auto_shrink 默认 false⼿⼯收缩:收缩数据库中某个⽂件的⼤⼩。按⽐例收缩整个数据库⼤⼩。添加删除数据库⽂件：扩⼤指定⽂件的⼤⼩alter database db_zhjmodify file(name=‘dbz_zhj’,size=8MB)添加新的数据⽂件alter database db_zhjadd file(name=‘db_zhj_data1’,–逻辑数据⽂件名filename=‘E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datadatadb_zhj_’,–次数据库⽂件逻辑名字.扩展名字为ndfsize=3mb,–主数据⽂件初始化⼤⼩maxsize=50Mb,–主数据⽂件最⼤⼤⼤⼩filegrowth=10% --超过初始化⼤⼩后⽂集⾃动增长率。)注意:::添加⽂件时候，每个⽂件组的数据⽂件是按⽐例填充，⽇志⽂件是依次添加。收缩整个数据库的⼤⼩dbcc shrinkdatabase(db_zhj,20) 收缩数据库db_zhj所有⽂件为20%可以空间收缩指定⽂件的⼤⼩dbcc shrinkdatabase(db_zhj_data1,4) 收缩数据库的⽂件db_zhj_data1为4mb删除数据库⽂件alter database db_zhjremove file db_zhj_data1注意：：：⽂件为空才可以被删除。分离和附加数据库分离数据库：作⽤是实现数据库从⼀台计算机移动到另⼀台计算机上，不需要重建。从实例中删除，但是不删除数据⽂件和⽇志⽂件，保持了数据⽂件和⽇志⽂件的完整性。使⽤sp_detach_db系统存储过程。eg： exec sp_detach_db ‘数据库名字’ true1234附加数据库：将分离的数据库附加到数据库管理当中去。必须指定主数据⽂件的物理存储位置和⽂件名字。create databa db_zhj on

(filename='E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datadatadb_zhj_') for attach --for attach 是附加的意思第⼆节 架构架构(Schema ,也称为模式)，是数据库的⼀个逻辑命名空间，是数据库对象的容器，⼀个数据库包含多个架构或者⼀个，同⼀个数据库架构名字唯⼀。创建架构： create schema 架构名字 authorization ⽤户名字删除架构： drop schema 架构名字第三节 分区表1.基本概念：分区表是把表中的数据⽔平分割成不同的⼦集，并将数据⼦集存放在⼀个或者多个⽂件组中。物理上是⼤表分成⼏个⼩表，逻辑上还是⼀个表。合理分区可以提⾼数据库的性能。是否创建分区表取决于表当前数据量的⼤⼩，以及将来的数据量，还取决于表中的数据操作特点。表包含或者将包含以多种不同⽅式使⽤的⼤量数据。数据是分段的，⽐如按年份分段。2.创建分区表三个步骤：（1）创建分区函数，告诉DBMS以什么⽅式进⾏分区。create partition function（2）创建分区⽅案，作⽤是将分区函数⽣成的分区映射到⽂件组中。create partition schema（3）使⽤分区创建表eg：create partition function myf1(int) as range left for value (1,100,1000);–left 为包含左侧的1create partition function myf2(int) as range right for value (1,100,1000);–left 为包含右侧的100 分区函数gocreate partition schema myfa as partition myf1 to (test1,test2,test3,test4)—创建分区⽅案gocreate table table_partition --创建分区表(coll int,name char(100)on myfa(coll) --分区⽅案上)第四节 索引1.创建索引语法：create [unique] --表⽰唯⼀索引，可选[clustered] —聚集索引[nonclustered] —⾮聚集索引index index_name --索引名字on table_name --所在的表(column_name…) --所在的列[with fillfactor=x] --填充因⼦，指0-100之间，该值指索引页填满的空间所占的百分⽐。2.删除索引语法：drop index table__name 或者 view__name索引可以在表可以在视图上第五节 索引视图1.基本概念：标准视图页称为虚拟表，返回结果与基本表⼀致，结果集不永久存放。建⽴唯⼀聚集索引的视图称为索引视图，也成物化视图，建⽴索引视图后视图的结果存放在数据库中。对基本表的修改，也反映在索引视图存储的数据中。2.建⽴索引视图的场景很少更新基础数据，索引视图效果好。若基础数据是批处理的⽅式定期更新，且主要作为读取数据处理，可考虑更新前删除索引视图，然后重建，效果会更好。索引视图可提⾼哪些性能：处理⼤量的连接和聚合时候许多查询经常执⾏连接和聚合时候索引视图不可提⾼哪些性能：具有⼤量写操作的OLPT操作系统具有⼤量更新操作的数据库不涉及聚合或连接的查询group by具有⾼基数度的数据聚合3.定义索引视图创建聚合索引前视图需要符合的条件：定义索引视图时候，视图只能引⽤基本表，不能是其他视图。引⽤的基本表和视图在同⼀库，所属同⼀⽤户。视图中表达式所引⽤的函数必须确定。对视图建⽴的第⼀个索引是聚合索引，然后是其他。create view;schemabinding;create unqiue clustered index…第七章 ⾼级数据库查询第⼀节 ⼀般数据库查询功能扩展语句select [distinct] [top n] 查询的数据表达式[into 插⼊的数据表][from 数据表][while 查询条件][group by 表达式][having 表达式] --聚合查询的条件[order by 表达式[asc]|[desc]][compute 表达式] --在查询的结果末尾产⽣汇总2.使⽤top限制结果集top n [percent] [with ties]top n --前n⾏top n [percent]–前百分之n[with ties]–包含最后⼀⾏取值并列的结果函数是⼀种分⽀表达式俩种类型:简单case函数搜索case函数语法:casewhen 条件 then 结果表达式when 条件 then 结果表达式[else 结果表达式n+1]end4.将查询结果保存在新表中select 查询列表 into 新表 from 源表 while 条件注意:表前⾯加# 为局部临时表,加##为全局临时表eg:select xxxx into #临时表 from 源表 while 条件第⼆节 交,并,差运算1.并 union注意:列数相同列隐⾝兼容合并后第⼀个select列为列标题若需要排序,则 order by 写最后⼀个select 之后,且排序的语句是第⼀个select列的列名。2.交 intersect返回在俩个集合都有的结果3.差 except返回第⼀个有，第⼆个没有的记录、第三节 ⼦查询1.写在圆括号的语句2.派⽣表，也称内联表第四节 其他形式的⼦查询第五章 其他⼀些查询功能1.开窗函数在sql server中 ⼀组⾏被称为⼀个窗⼝。与聚合函数⼀样，开窗函数也是对⾏级组进⾏聚合计算，但是他不像普通的起聚合函数那样每组返回⼀个值，开创函数可以为每组返回多个值，因为开窗函数所执⾏的聚合计算的⾏级组是窗⼝。与聚合函数不同的是开窗函数在聚合函数的后⾯加over关键字调⽤格式为: 聚合函数名字(列) over(选项)over关键字，表⽰把函数当成开窗函数⽽不是聚合函数。sql标准允许把所有的聚合函数当做开窗函数，⽤over区分就好了。sum(列名字)over(partition by 列名字 )max(列名字)over(partition by 列名字 )min(列名字)over(partition by 列名字 )avg(列名字)over(partition by 列名字 )over与排名函数⼀起使⽤rank() over(partition by 列名字 order by 列名字 desc/asc) :结果可能不是连续的 如1.2.2.4.5注意：rank()具有不确定性。排名从⼀开始，不具有连续性。dense_rank() over(partition by 列名字 order by 列名字 desc/asc) : 排名是连续的整数ntile() over(partition by 列名字 order by 列名字 desc/asc) :将有序分区中的⾏划分到指定数⽬的组中，编号从1开始，函数返回此⾏所属组的编号。row_number() over(partition by 列名字 order by 列名字 desc/asc) : 返回结果集中每个分区内的序号，每个分区的第⼀⾏从⼀开始。2.公⽤表表达式公⽤表表达式（CommonTableExpression,cte）:将结果集指定⼀个临时的名字，这些命名的结果集就是公⽤表表达式。格式：with [,…n]::=Expression_Table[(column [,…n])]as select…eg: 定义 —with 公⽤表表达式名 (列1,列2) as (select 列1,列2 from 表 where 条件)使⽤ —as select 列1,列2 from 公⽤表表达式名 where 条件第⼋章 数据库后台编程技术第⼀节 存储过程1.基本概念使⽤T-sql语⾔编写代码时，有两种⽅式存储和执⾏代码：（1）在客户端存储代码，通过客户端程序或sql命令向dbms发送请求，由dbms将结果返回给⽤户程序。（2）以⼦程序的形式将程序模块存储在数据库中，仅供有权限的⽤户反复执⾏使⽤。存储过程：即存储在数据库中供所有⽤户程序使⽤的⼦程序。存储过程的分类:系统存储过程⽤户⾃定义存储过程扩展存储过程扩张存储过程：是sqlserver可以动态转载并执⾏的动态链接库（dll），扩展存储过程使您得以使⽤C这样的编程语⾔创建⾃⼰的外部例程。对⽤户来说扩展存储过程和普通存储过程⼀样，执⾏⽅法相同。存储的有点：执⾏效率⾼。增强代码的重⽤性和共享性。使⽤存储过程可以减少⽹络流量。使⽤存储过程保证安全性。在⼤型数据中,应⽤程序访问数据库的最主要的⽅式是存储过程。存储过程可以在系统启动⾃动执⾏。2.创建，执⾏和删除存储过程create procedure 存储过程名字 [;number] --定义名字[{parm data_type}[[varying][=default][output]][…n1][with {pecompile|encryption|pecompile,}] —encryption 是否加密[for replication]–是否不可以执⾏复制的存储过程as sql_statement[,…n2]执⾏存储过程:exec[ute] 存储过程名字 参数eg：create procedure p_custbuy@area varchar(20)=‘长沙’,@price moneyas select * from …where address=@area执⾏：exec p_custbuy @price=1000删除存储过程：drop procedure 存储过程名字第⼆节 ⽤户⾃定义函数⽤户⾃定义函数：类似于编程语⾔的函数，其结构和存储过程相似，但函数必须有⼀个return语句，⽤于返回函数值。俩类⽤户定义函数：标量函数和表值函数，前者返回时单个数值，后者返回表。1.创建和调⽤标量函数定义标量函数：create function 函数名 returns return_data_typeasbegin【函数体】return scalar_expressionendeg:create function 函数名 (@class varchar())return intasbegin【函数体】return @xend调⽤标量函数:注意:调⽤时需要提供函数的拥有者和函数名字可以在任何出现表达式的sql语句中调⽤类型⼀致的标量函数。2.创建表值函数：create function 函数名 returns tableasreturn [(select…)]表值函数没有返回变量，没有函数体，只返回⼀个查询结果。调⽤内联表值函数：使⽤内联表值函数与视图类似，其作⽤相当于带参的视图。12345eg：create function 拥有者.函数名(@参数 参数类型) returns tableasreturn [()]调⽤：select* from 拥有者.函数名(参数)3.创建和调⽤多语句表值函数create function dbo.f_food(@class varchar(200))returns @f_food table（name varchar(20),age int,address varchar(200)）asbegininsert into @f_foodselect name, age,address , @f_food(@class)from table_name where name=@class)returnend调⽤：select* from 拥有者.函数名(参数)4.删除⾃定义函数:drop function 拥有者.函数名第三节 触发器1.基本概念：触发器：特殊的存储过程,在对表的数据进⾏update、insert、delete操作时⾃动触发执⾏，常⽤于保证业务规则和数完整性，增强数据完整性约束的能⼒。sql server2008⽀持三种类型的触发器：DML、DDL、登录触发器。适⽤场合：完成⽐check（只能实现同⼀表列之间的取值约束）约束更复杂的数据约束。保证数据库的性能⽽维护的⾮规范化的数据。可实现复杂的业务规则。评估数据修改前后的表状态，并采取对策。2.创建触发器：create trigger trigger_name{table|view}[with encryption]{for|after|instead of}{ [insert][,updata][,delete][,…]}assql_statament{…n]参数说明:for或after:后触发器,操作、约束检查完成后触发。instead of：前出发器，数据操作语句最多定义⼀个触发器。执⾏触发器⽽⾮引发语句。满⾜完整性约束则需要重新执⾏这些数据的操作。注意：⼀个表可建多个触发器，每个触发器可由三个操作触发。alter类型同⼀操作上建⽴多个触发器，instead of类型同⼀操作建⽴⼀个触发器。

所有建⽴和更改数据库以及数据库对象的语句，drop语句不允许在触发器中使⽤。触发器不要返回任何结果。eg：create trigger trigger_nameon table_namefor insert,updateasdeclare @newM moneyselect @newM=salep from inserted --inserted逻辑表,保存插⼊前的数据。update table_name1 set salep=@newMwhere id in (select id from insered )–deleted 逻辑表，保存删除后的数据。3.删除触发器drop trigger 触发器名第四节 游标游标：实现select 结果集的逐⾏处理。1.游标组成游标结果集（select 返回集）与游标当前指针（指向结果集中⼀⾏）特点：定位特定⾏；从当前位置检索⼀⾏或者多⾏；⽀持当前⾏数修改；对修改结果提供不同级别的可见⽀持。2.使⽤游标a.声明游标：ISO标准语法：declare cursor_name[1] cursor forselect_statement[2]参数说明：[1]insenstitive:创建临时副本，对临时副本操作，否则基本表；scroll范围，否则⽀持next；[2]read only:禁⽌更新 update 更新列指定列或者所有。b.打开游标open cursor_namec.提取数据fetch [1]from cursor_name into @variable_name[,…n]d.关闭游标close cursor_namee.释放游标deallocate cursor_name --释放游标的所有资源。第九章 安全管理内容摘要1.理解安全控制的基本概念2.了介sql server2008的存取控制3.掌握sql server2008的安全孔⼦的实现⽅式4.了解oracle的安全管理第五节 sql server 的安全控制建⽴登录账户create login login_name修改登录账户属性alter login login_names删除登录账户drop login login_name3.数据库⽤户⽤户有了登录账户，只是连接到了sql服务器，并不具有访问数据库的权限。映射：让登录账户成为数据库⽤户的操作 成为映射。⼀个登录账户可以映射为多个数据库⽤户。默认情况下，新建数据库只有⼀个⽤户：dbo，数据库⽤户的拥有者。建⽴数据库⽤户create user user_name [|for|from] login login_nameGuest⽤户，特殊数据库⽤户，匿名访问，没有映射到登录账户的时候使⽤grant connect to guestrevoke connect to guest --revoke 激活删除数据库⽤户drop user user_name4.权限管理登录账户成为合法⽤户后没有任何操作权限，就需要为⽤户授予数据库及其对象的操作权限。a.对象级别的权限（6种）select、insert、update、delete、references、 execute --references引⽤授权语句：grant 对象权限 on 对象 to (主体：数据库⽤户或者⾓⾊)[with grant option]拒绝权限:deny 对象权限 on 对象 to (主体：数据库⽤户或者⾓⾊)[cascade[as 主体]]收权语句：revoke 对象权限 on 对象 to (主体：数据库⽤户或者⾓⾊)[cascade[as 主体]]b.语句级别的权限grant create database ,create view …to user_name,user_name1deny create database ,create view …to user_name,user_name1revoke create database ,create view …from user_name,user_name15.⾓⾊定义:⼀组具有相同权限的⽤户就是⾓⾊。分为:系统⾓⾊和⽤户⾓⾊；系统⾓⾊⼜分为固定服务器⾓⾊（服务器级别⾓⾊）和固定数据库⾓⾊（数据库级别⾓⾊）⽤户⾓⾊均是数据库⾓⾊。固定服务器⾓⾊：Bulkadmin：执⾏bulk insert 语句权限。Dbcreator：创建、修改、删除还原数据库权限。Diskadmin：具有管理磁盘⽂件的权限。Processadmin：管理运⾏进程权限。Secutyadmin：专门管理登录账户、读取错误⽇志执⾏create database权限的账户。Serveradmin：服务器级别的配置和关闭服务权限。Setupadmin：添加删除除链接服务器。Sysadmin：系统管理员，Windows超级⽤户，⾃动映射为系统管理员。Public：系统预定义服务器⾓⾊，每个登录名都是这个⾓⾊成员。没有授予或者特定权限，则将具有这个⾓⾊权限。为固定服务器⾓⾊添加成员EXEC sp_addsrvrolemember ‘user_name’,‘sysadmin’为固定服务器⾓⾊删除成员EXEC sp_dropsrvrolemember ‘user_name’,‘sysadmin’固定数据库⾓⾊：定义在数据库级别上,存在于每个数据库中。⽤户加⼊固定数据库⾓⾊九具有数据库⾓⾊权限。Db_accessadmin：添加或者删除数据库权限。Db_backupoperator:备份数据库、⽇志权限。Db_datareader：查询数据库数据权限。Db_datawriter:具有插⼊、删除、更改权限。Db_ddladmin：执⾏数据定义的权限。Db_denydatareader：不允许具有查询数据库中所有⽤户数据的权限。Db_denydatawriter：不允许具有插⼊、删除、更改数据库中所有⽤户的数据权限。Db_owner：具有全部操作权限，包括配置，维护，删除数据库。Db_securityadmin：具有管理数据库⾓⾊、⾓⾊成员以数据库中语句和对象的权限。为固定数据库⾓⾊添加成员EXEC sp_addsrvrolemember ‘user_name’,‘Db_owner’为固定数据库⾓⾊删除成员EXEC sp_dropsrvrolemember ‘user_name’,‘Db_owner’⽤户⾃定义⾓⾊：属于数据库级别。创建⾓⾊：create role role_name授权和固定⼀样；删除⾓⾊：create role role_name第六节 oracle安全管理oracle的安全机制分为数据级别的安全控制，表级，列级，⾏级的安全控制。数据库级别的安全通过⽤户⾝份认证和授权给⽤户来保证。表级、列级、⾏级对的安全通过授予或回收对象权限保证，⽀持集中式、分布式、跨平台应⽤。oracle系统通常设置俩级别安全管理员：全局级：负责管理、协调、维护全局数据库⼀致性和安全性。场地级：负责本节点的数据库安全性、⽤户管理、系统特权与⾓⾊的管理。1.⽤户与资源管理:安权限划分⼤⼩分为DBA⽤户和普通⽤户。dba⽤户由dbms⾃动创建，sys和system⽤户，拥有全部的系统特权。普通⽤户：由dba⽤户或者是具有dba权限的⽤户创建，并授予特权。建⽴⽤户create user user_name identified by 123456 default tablespace xxxquota 5m on xxx(限制使⽤空间5m)管理⽤户和资源：dba特权⽤户可以该表⼀个⽤户资源使⽤限额、密码、登录次数等alter user user_name quote 69m on xxxxalter user user_name identified by123456799删除⽤户drop user user_name cascade （删除⽤户以及所拥有的全部对象）2.权限管理a.系统特权：三种特权：connect:不能建⽴任何对象，可以查询数据字典及访问数据库对象。recourse：可建⽴数据库对象（表，视图，索引…）。dba:拥有在预定义的全部权限。1234b.对象特权：⽤户维护表级，⾏级，列级数据的安全性。eg：grant all on 表 to ⽤户 grant select（列1，列2,.....）on 表 to ⽤户第⼗章 数据库的维护和优化内容摘要：1.了解数据库库运⾏的基本原理。2.了解运⾏状态监控与分析。3.了解数据库存储空间管理。4.掌握数据库优化⽅法。DBAS进⾏维阶段的主要任务：保证数据库系统安全，可靠⾼效的运⾏。数据库的运⾏除了DBMS与数据库外，还需要各种系统部件协调⼯作。⾸先必须有各种相应的应⽤程序。其次各应⽤程序与dbms都需要在操作系统os⽀持下⼯作。维护⼯作包括：数据库转存与恢复。数据库安全性、完整性控制。检查并改善数据库性能。数据库重组和重构。重组不修改数据库原有的逻辑结构和物理结构。重构部分修改模式和内模式。数据库的监控分析：指管理员借助⼯具检测DBMS的运⾏情况，掌握数据库当前或者以往的负荷、配置、应⽤等信息，并分析检测数据的性能参数和环境信息，评估dbms的整体运⾏状态。根据检测分析实现不同，分为：数据库系统建⽴的⾃动监控机制，由DBM⾃动检测数据库的运⾏情况。管理员⼿动实施的检测机制。根据监控对象不同，分为：数据库架构体系的监控监控空间基本信息，空间的使⽤率与剩余空间⼤⼩等。数据库性能监控监控数据缓存命中率，库缓冲、⽤户锁、索引使⽤、等待事件等。对数据空间进⾏管理是⼀项⾮常重要的⼯作。空间使⽤变化带来的问题：降低数据系统服务性能空间溢出导致灾难停机事故数据的存储结构分为：逻辑存储结构物理存储结构sql server数据库中⼀个逻辑上的数据库直接和⼀组物理上的数据⽂件对应，没有空间的概念。DBMS对空间的管理包括：创建数据空间、修改空间⼤⼩、删除空间、修改空间状态、新建、移动、关联数据⽂件。数据库性能优化是dbas系统上线后常见的运⾏维护任务之⼀。进⾏数据库性能优化时，⾸先要确定优化⽬标，⼀般从数据库运⾏环境、参数调整、模式调整、数据库存储优化、查询优化⼏⽅⾯考虑。1.数据库运⾏环境与参数调整可以从外部环境、调整内存分配、整磁盘i/o、调整资源竞争等⼏⽅⾯着⼿改表数据库参数，提⾼性能。外部调整：cpu，⽹络调整竞争：修改控制连接数据库的最⼤进程数。减少调度进程的竞争。减少多线程服务进程竞争。减少重做⽇志缓存区竞争。减少回滚段竞争。2.模调整式优化数据库的规范化过程:⾼效率利⽤存储空间,减少数据冗余,减少数据的不⼀致性。反规范化：将规范化关系转换为⾮规范化的关系过程。反规范化⽅法：增加派⽣冗余列、增加冗余列、重新组表、分割和增加汇总表的⽅法。都破坏数据完整性采⽤反规范化技术从实际出发均衡利弊。分割表 ：⽔平，垂直。3.存储优化：a.物化视图b.聚集，创建聚合索引。4.查询优化a.合理索引，但是系统开销⼤了。索引原则：是否为⼀个属性建⽴索引：该属性是码或存在某个查询中被使⽤。在哪些属性建⽴索引：若⼀个关系的多个属性共同出现在查询中，⼀般采⽤多属性索引。是否建⽴聚合索引：聚合索引适合范围查询，可建⽴多属性索引，优点体现在数据记录存取过程中。使⽤散列还是树索引：散列索引适合等值查询，关系数据库多使⽤B+索引，⽀持作为搜码的属性的等值查询和范围查询。使⽤索引原则：经常在查询中被作为条件使⽤的列。应为其建⽴索引。频繁进⾏排序或分组的列，为其建⽴索引。⼀个列的值域很⼤时，建索引。如果待排列的列有多个，建复合索引。可以使⽤系统⼯具来检查索引的完整性，必要时进⾏修复，当数据表更新⼤量数据后，删除并重建索引。索引建⽴完成后，运⾏期间还需要优化。调优的⽬的：动态评估需求。索引调整和修改的原因：缺索引，查询语句时间长。某些索引⾃开始没使⽤，却占⽤了较多的磁盘空间。索引建在频繁该表的属性上，导致系统开销⼤。1718 b.避免或者简化排序： order by 和group by语句涉及的排序，磁盘开销很⼤，应利⽤索引⾃动适当的次序输出。 影响优化器的因素： 由于现在索引不⾜，导致排序索引中不包括⼀个或者⼏个待排序的因素、 order by 和group by⼦句中列的次序和索引次序不⼀致。排序的列来⾃不同的表 为避免不必要的排序。要正确的建索引，合理合并数库表，如排序不可避免，则简化它，缩⼩范围、 c.消除对⼤型表数据的顺序存储 嵌套查询，对表的顺序存取严重影响查询效率。 优化⽅法：对连接列进⾏索引，或者使⽤并集避免顺序存取。 d.避免正则表达式 e.使⽤临时表加速查询 将表的⼀个⼦集排序创建临时表。 f.排序取代⾮排序存储磁盘。 g.不充分的连接条件。 h.存储过程优化。 l.不要随意使⽤游标。 m.事务处理。 server性能⼯具sql server Profiler 检测性能。数据库引擎优化顾问第⼗⼆章 备份与恢复第⼀节 备份恢复概念1.备份数据库数据备份的原因：防⽌丢失，数据的转移⽅式。2.恢复数据库俩种类型：介质故障恢复，⾮介质故障恢复。第⼆节 sql server的备份与恢复机制1.恢复模式三种恢复模式：简单恢复模式，完整恢复模式，⼤容量⽇志恢复模式。2.备份内容和时间备份内容⽤户数据库系统数据库备份时间系统数据库：修改之后进⾏备份⽤户数据库：周期备份⽴刻进⾏备份：创建数据库之后，或批量加载数据库之后。创建索引之后。执⾏清理事务⽇志之后。执⾏⼤容量数据操作之后。 server的备份机制a.备份设备：磁带，磁盘备份设备类型：永久备份设备：在备份之前需要先建⽴。临时备份设备：不需要预先建⽴，在备份的时候将数据库直接拷贝在物理⽂件上。使⽤系统存储过程创建备份设备：sp_addumpdecice[@devtype=]‘device_type’–设备类型 ，可以是disk和tape[@logicalname]‘logical_name’–设备逻辑名称[@physicalname]‘physicalname’–设备实际名称eg：创建⼀个名字为my_bak的磁盘设备，其物理名称为D:c sp_addumpdecice ‘disk’ ,‘my_bak’,‘D:’1234567891b.备份类型 1）数据库备份：完整备份，差异备份 2）⽂件备份：⽂件备份，差异⽂件备份 3）事务⽇志备份c.常⽤备份策略： 完整备份 完整备份+⽇志备份 完整备份+差异备份+⽇志备份e.实现备份： 使⽤sql server平台 使⽤t-sql语句 eg：backup database 数据库 to my_bak；完整数据库备份。 backup log pubs to my_bak_log；事务⽇志备份 ，pubs数据库名字 server的恢复机制数据库（数据库完整还原）数据⽂件（⽂件还原）还原顺序：恢复最近的完整备份恢复最近的差异备份恢复数据库实现还原：使⽤sql server 管理平台；使⽤t-sql语⾔；eg：restore database 数据库 from mybak_2 --完整备份where file=1,norecovery --norecovery不要覆盖restore database 数据库 from mybak_2 --差异备份where file=2,norecovery --⽇志备份restore log 数据库 from mybak_log第三节 oracle的备份与恢复机制数据逻辑备份和恢复常见故障:sql语句失效进程失效实例失效⽹络失效123456上⾯的四种内部机制处理。⽤户错误介质失效利⽤备份数据恢复。逻辑备份：指通过⼯具将数据库的数据转换成格式的⽂件，保存在⽂件系统中。⼯具 ：exp/imp expdp/ipdp物理备份:⽤户管理备份RECOVER MANAGER(RMAN)备份第三⽅软件恢复数据库restore :从备份数据库找到完整的备份⽂件，恢复完整的过程⽂件。recover ：将⽇志和增量备份的改变⽤户到数据⽂件，使数据⽂件恢复指定时间点的过程。第⼗三章 ⼤规模数据库架构第⼀节 分布式数据库1.分布式数据库系统与分布式数据库的区别：分布式数据库系统：数据分布存储于若⼲场地，并且每个场地由独⽴于其他场地的dbms进⾏数据管理。物理上分散，逻辑上集中的数据库系统。分布式数据库:分布式数据库系统中各场地数据库的逻辑集合。2.分布式数据库⽬标与数据分布策略分布式数据库⽬标：12个⽬标：本地⾃治，⾮集中式管理，⾼可⽤性。位置独⽴，数据分⽚独⽴性，数据复制独⽴性。分布式查询，事务管理。硬件独⽴性，操作系统独⽴性，⽹络独⽴性数据库管理系统独⽴性。数据库分布策略：从数据分⽚和数据分配考虑数据分⽚（对关系操作）按⼀定规则将某⼀个全局关系划分为多个⽚段，⽔平分⽚——垂直分⽚–、导出分⽚–混合分⽚–4.分布式数据库的相关技术分布式查询⽤户与分布式数据系统的接⼝。分布式查询优化考虑：1.操作顺序2.操作算法3.不同场地的数据流动的顺序。分布事务管理第⼆节 并⾏数据库1.并⾏数据库概述并⾏数据库系统：通过并⾏实现各种数据操作.优势：增强可⽤性。2.并⾏数据库系统结构共享内存系统共享磁盘⽆共享资源系统层次结构第三节 云计算数据库的结构第四节 xml系统数据库 ；⽀持xml格式数据库。第⼗四章 数据仓库和数据挖局第⼀节 决策⽀持系统的发展⼀般数据分为：分析型数据操作型数据决策⽀持系统（DSS）DSS建⽴在OLTP数据库仓库是核⼼，操作型系统是数据源。数据仓库的其他特点：粒度：影响数据量，粒度越⼩回答越细。

2023年6月21日发(作者：)

sqlserver2012学习总结笔记Sqlserver2012 学习总结笔记第⼀节 创建和维护库sqlserver的版本企业版(Enterprise) 是⼀种综合的数据平台，可以为运⾏安全的业务关键应⽤程序提供企业级可扩展性、性能、⾼可⽤性和⾼级商业智能功能。标准版(Standard) 是⼀个提供易⽤性和可管理性的完整数据平台,部门级的应⽤提供⽀持。开发版(Developer) ⽀持开发⼈员构建基于 Server 的任⼀种类型的应⽤程序,拥有企业级的特性，但是有限制。⼯作组版(Workgroup) 是运⾏分⽀位置数据库的理想选择，它提供⼀个可靠的数据管理和报告平台，其中包括安全的远程同步和管理功能。⽹络版(Web) 对于为从⼩规模⾄⼤规模 Web 资产提供可扩展性和可管理性功能的 Web 宿主和⽹站来说成本低。移动版(Compact) 3.5 免费提供，是⽣成⽤于基于各种 Windows 平台的移动设备、桌⾯和 Web 客户端的独⽴和偶尔连接的应⽤程序的嵌⼊式数据库理想选择。免费版(Express) 适⽤于学习构建⼩型的应⽤。、sqlserver的数据库分类1.系统数据库master 数据库：master 数据库记录 SQL 系统的所有系统级别信息。它记录所有的登录帐户和系统配置设置。master 数据库是这样⼀个数据库，它记录所有其它的数据库，其中包括数据库⽂件的位置。master 数据库记录 SQL Server 的初始化信息，它始终有⼀个可⽤的最新 master 数据库备份。msdb 数据库:msdb 数据库供 SQL Server 代理程序调度警报和作业以及记录操作员时使⽤。tempdb 数据库:tempdb 数据库保存所有的临时表和临时存储过程。model 数据库:model 数据库⽤作在系统上创建的所有数据库的模板。当发出 CREATE DATABASE 语句时，新数据库的第⼀部分通过复制 model 数据库中的内容创建，剩余部分由空页填充。由于 SQL Server 每次启动时都要创建 tempdb 数据库，model 数据库必须⼀直存在于 SQLServer 系统中。Resource 数据库:Resource 数据库⼀个只读数据库，包含 SQL Server 包括的系统对象。系统对象在物理上保留在 Resource 数据库中，但在逻辑上显⽰在每个数据库的sys 架构中。2.⽤户数据库sqlserver数据库的组成sqlserver将数据库映射为⼀组可操作的⽂件:数据⽂件.mdf⽂件,主数据⽂件,只有⼀个,⼤⼩不得⼩于3Mb。.ndf⽂件,次要数据⽂件,可以0个或多个,可以在⼀个或者多个磁盘存放。⽇志⽂件.ldf⽂件，事务⽇志⽂件，⾄少有⼀个⽂件。数据库存储空间的分配创建⽤户数据库时，model数据库会被⾃动的复制到新建的数据库。数据库存储的最⼩单位是，数据页（page 简称 页）。1页是8KB的连续的磁盘存储空间。页的⼤⼩决定了数据库表⼀⾏数据的最⼤的⼤⼩。⾏不可以跨页存储。数据库⽂件组俩种类型的⽂件组：主⽂件组包含主要的数据⽂件和任何没有明确分配的其他⽂件组的其他数据⽂件，系统表的所有表页都分配在主⽂件组中。⽤户定义⽂件组：在定义或者修改数据库时⽤ ‘FILEGROUP’关键字指定。注意：：1.⽇志⽂件不在⽂件组中，⽇志⽂件和数据⽂件分开。2.⼀个⽂件不可以时多个⽂件组成员。3.若⽂件组含有多个⽂件，则在所有⽂件被填满后才会⾃动循环增长。4.⽂件加⼊数据库后，不能移动到其他⽂件组。5.只能指定⼀个⽂件组为默认⽂件组。数据⽂件的属性定义数据⽂件和⽇志⽂件的所有信息：⽂件名及位置：逻辑⽂件名，物理⽂件名。初始⼤⼩：不能⼩于model数据库主要⽂件的⼤⼩。增长⽅式：可以指定⽂件是否⾃动增长（默认）⽂件⼤⼩：⽂件增长的最⼤限制，默认⽆限制。T-sql创建数据库 server management studio2.T-sql创建数据库T-SQL格式语法举例：create databasedb_zhj --数据库名字on(name=‘db_zhj_data’,–逻辑数据⽂件名filename=‘E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datadatadb_zhj_’,–主数据库⽂件逻辑名字size=3mb,–主数据⽂件初始化⼤⼩maxsize=50Mb,–主数据⽂件最⼤⼤⼤⼩filegrowth=10% --超过初始化⼤⼩后⽂集⾃动增长率。)log on(name=‘db_zhj_log’,–逻辑⽇志⽂件名filename=‘E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datalogdb_zhj_’,–⽇志存放的逻辑物理名size=2mb,–⽇志初始化⼤⼩maxsize=50Mb,–⽇志最⼤⼤⼩filegrowth=1mb --超过初始化⼤⼩后⽂集⾃动增长率。)go修改数据库收缩数据库空间：即释放数据库中未使⽤的空间，收缩从尾部开始:⾃动收缩：auto_shrink 默认 false⼿⼯收缩:收缩数据库中某个⽂件的⼤⼩。按⽐例收缩整个数据库⼤⼩。添加删除数据库⽂件：扩⼤指定⽂件的⼤⼩alter database db_zhjmodify file(name=‘dbz_zhj’,size=8MB)添加新的数据⽂件alter database db_zhjadd file(name=‘db_zhj_data1’,–逻辑数据⽂件名filename=‘E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datadatadb_zhj_’,–次数据库⽂件逻辑名字.扩展名字为ndfsize=3mb,–主数据⽂件初始化⼤⼩maxsize=50Mb,–主数据⽂件最⼤⼤⼤⼩filegrowth=10% --超过初始化⼤⼩后⽂集⾃动增长率。)注意:::添加⽂件时候，每个⽂件组的数据⽂件是按⽐例填充，⽇志⽂件是依次添加。收缩整个数据库的⼤⼩dbcc shrinkdatabase(db_zhj,20) 收缩数据库db_zhj所有⽂件为20%可以空间收缩指定⽂件的⼤⼩dbcc shrinkdatabase(db_zhj_data1,4) 收缩数据库的⽂件db_zhj_data1为4mb删除数据库⽂件alter database db_zhjremove file db_zhj_data1注意：：：⽂件为空才可以被删除。分离和附加数据库分离数据库：作⽤是实现数据库从⼀台计算机移动到另⼀台计算机上，不需要重建。从实例中删除，但是不删除数据⽂件和⽇志⽂件，保持了数据⽂件和⽇志⽂件的完整性。使⽤sp_detach_db系统存储过程。eg： exec sp_detach_db ‘数据库名字’ true1234附加数据库：将分离的数据库附加到数据库管理当中去。必须指定主数据⽂件的物理存储位置和⽂件名字。create databa db_zhj on

(filename='E:gongzuo_hcdb_service_datasqlserver_2012_homeuserdb_datadatadb_zhj_') for attach --for attach 是附加的意思第⼆节 架构架构(Schema ,也称为模式)，是数据库的⼀个逻辑命名空间，是数据库对象的容器，⼀个数据库包含多个架构或者⼀个，同⼀个数据库架构名字唯⼀。创建架构： create schema 架构名字 authorization ⽤户名字删除架构： drop schema 架构名字第三节 分区表1.基本概念：分区表是把表中的数据⽔平分割成不同的⼦集，并将数据⼦集存放在⼀个或者多个⽂件组中。物理上是⼤表分成⼏个⼩表，逻辑上还是⼀个表。合理分区可以提⾼数据库的性能。是否创建分区表取决于表当前数据量的⼤⼩，以及将来的数据量，还取决于表中的数据操作特点。表包含或者将包含以多种不同⽅式使⽤的⼤量数据。数据是分段的，⽐如按年份分段。2.创建分区表三个步骤：（1）创建分区函数，告诉DBMS以什么⽅式进⾏分区。create partition function（2）创建分区⽅案，作⽤是将分区函数⽣成的分区映射到⽂件组中。create partition schema（3）使⽤分区创建表eg：create partition function myf1(int) as range left for value (1,100,1000);–left 为包含左侧的1create partition function myf2(int) as range right for value (1,100,1000);–left 为包含右侧的100 分区函数gocreate partition schema myfa as partition myf1 to (test1,test2,test3,test4)—创建分区⽅案gocreate table table_partition --创建分区表(coll int,name char(100)on myfa(coll) --分区⽅案上)第四节 索引1.创建索引语法：create [unique] --表⽰唯⼀索引，可选[clustered] —聚集索引[nonclustered] —⾮聚集索引index index_name --索引名字on table_name --所在的表(column_name…) --所在的列[with fillfactor=x] --填充因⼦，指0-100之间，该值指索引页填满的空间所占的百分⽐。2.删除索引语法：drop index table__name 或者 view__name索引可以在表可以在视图上第五节 索引视图1.基本概念：标准视图页称为虚拟表，返回结果与基本表⼀致，结果集不永久存放。建⽴唯⼀聚集索引的视图称为索引视图，也成物化视图，建⽴索引视图后视图的结果存放在数据库中。对基本表的修改，也反映在索引视图存储的数据中。2.建⽴索引视图的场景很少更新基础数据，索引视图效果好。若基础数据是批处理的⽅式定期更新，且主要作为读取数据处理，可考虑更新前删除索引视图，然后重建，效果会更好。索引视图可提⾼哪些性能：处理⼤量的连接和聚合时候许多查询经常执⾏连接和聚合时候索引视图不可提⾼哪些性能：具有⼤量写操作的OLPT操作系统具有⼤量更新操作的数据库不涉及聚合或连接的查询group by具有⾼基数度的数据聚合3.定义索引视图创建聚合索引前视图需要符合的条件：定义索引视图时候，视图只能引⽤基本表，不能是其他视图。引⽤的基本表和视图在同⼀库，所属同⼀⽤户。视图中表达式所引⽤的函数必须确定。对视图建⽴的第⼀个索引是聚合索引，然后是其他。create view;schemabinding;create unqiue clustered index…第七章 ⾼级数据库查询第⼀节 ⼀般数据库查询功能扩展语句select [distinct] [top n] 查询的数据表达式[into 插⼊的数据表][from 数据表][while 查询条件][group by 表达式][having 表达式] --聚合查询的条件[order by 表达式[asc]|[desc]][compute 表达式] --在查询的结果末尾产⽣汇总2.使⽤top限制结果集top n [percent] [with ties]top n --前n⾏top n [percent]–前百分之n[with ties]–包含最后⼀⾏取值并列的结果函数是⼀种分⽀表达式俩种类型:简单case函数搜索case函数语法:casewhen 条件 then 结果表达式when 条件 then 结果表达式[else 结果表达式n+1]end4.将查询结果保存在新表中select 查询列表 into 新表 from 源表 while 条件注意:表前⾯加# 为局部临时表,加##为全局临时表eg:select xxxx into #临时表 from 源表 while 条件第⼆节 交,并,差运算1.并 union注意:列数相同列隐⾝兼容合并后第⼀个select列为列标题若需要排序,则 order by 写最后⼀个select 之后,且排序的语句是第⼀个select列的列名。2.交 intersect返回在俩个集合都有的结果3.差 except返回第⼀个有，第⼆个没有的记录、第三节 ⼦查询1.写在圆括号的语句2.派⽣表，也称内联表第四节 其他形式的⼦查询第五章 其他⼀些查询功能1.开窗函数在sql server中 ⼀组⾏被称为⼀个窗⼝。与聚合函数⼀样，开窗函数也是对⾏级组进⾏聚合计算，但是他不像普通的起聚合函数那样每组返回⼀个值，开创函数可以为每组返回多个值，因为开窗函数所执⾏的聚合计算的⾏级组是窗⼝。与聚合函数不同的是开窗函数在聚合函数的后⾯加over关键字调⽤格式为: 聚合函数名字(列) over(选项)over关键字，表⽰把函数当成开窗函数⽽不是聚合函数。sql标准允许把所有的聚合函数当做开窗函数，⽤over区分就好了。sum(列名字)over(partition by 列名字 )max(列名字)over(partition by 列名字 )min(列名字)over(partition by 列名字 )avg(列名字)over(partition by 列名字 )over与排名函数⼀起使⽤rank() over(partition by 列名字 order by 列名字 desc/asc) :结果可能不是连续的 如1.2.2.4.5注意：rank()具有不确定性。排名从⼀开始，不具有连续性。dense_rank() over(partition by 列名字 order by 列名字 desc/asc) : 排名是连续的整数ntile() over(partition by 列名字 order by 列名字 desc/asc) :将有序分区中的⾏划分到指定数⽬的组中，编号从1开始，函数返回此⾏所属组的编号。row_number() over(partition by 列名字 order by 列名字 desc/asc) : 返回结果集中每个分区内的序号，每个分区的第⼀⾏从⼀开始。2.公⽤表表达式公⽤表表达式（CommonTableExpression,cte）:将结果集指定⼀个临时的名字，这些命名的结果集就是公⽤表表达式。格式：with [,…n]::=Expression_Table[(column [,…n])]as select…eg: 定义 —with 公⽤表表达式名 (列1,列2) as (select 列1,列2 from 表 where 条件)使⽤ —as select 列1,列2 from 公⽤表表达式名 where 条件第⼋章 数据库后台编程技术第⼀节 存储过程1.基本概念使⽤T-sql语⾔编写代码时，有两种⽅式存储和执⾏代码：（1）在客户端存储代码，通过客户端程序或sql命令向dbms发送请求，由dbms将结果返回给⽤户程序。（2）以⼦程序的形式将程序模块存储在数据库中，仅供有权限的⽤户反复执⾏使⽤。存储过程：即存储在数据库中供所有⽤户程序使⽤的⼦程序。存储过程的分类:系统存储过程⽤户⾃定义存储过程扩展存储过程扩张存储过程：是sqlserver可以动态转载并执⾏的动态链接库（dll），扩展存储过程使您得以使⽤C这样的编程语⾔创建⾃⼰的外部例程。对⽤户来说扩展存储过程和普通存储过程⼀样，执⾏⽅法相同。存储的有点：执⾏效率⾼。增强代码的重⽤性和共享性。使⽤存储过程可以减少⽹络流量。使⽤存储过程保证安全性。在⼤型数据中,应⽤程序访问数据库的最主要的⽅式是存储过程。存储过程可以在系统启动⾃动执⾏。2.创建，执⾏和删除存储过程create procedure 存储过程名字 [;number] --定义名字[{parm data_type}[[varying][=default][output]][…n1][with {pecompile|encryption|pecompile,}] —encryption 是否加密[for replication]–是否不可以执⾏复制的存储过程as sql_statement[,…n2]执⾏存储过程:exec[ute] 存储过程名字 参数eg：create procedure p_custbuy@area varchar(20)=‘长沙’,@price moneyas select * from …where address=@area执⾏：exec p_custbuy @price=1000删除存储过程：drop procedure 存储过程名字第⼆节 ⽤户⾃定义函数⽤户⾃定义函数：类似于编程语⾔的函数，其结构和存储过程相似，但函数必须有⼀个return语句，⽤于返回函数值。俩类⽤户定义函数：标量函数和表值函数，前者返回时单个数值，后者返回表。1.创建和调⽤标量函数定义标量函数：create function 函数名 returns return_data_typeasbegin【函数体】return scalar_expressionendeg:create function 函数名 (@class varchar())return intasbegin【函数体】return @xend调⽤标量函数:注意:调⽤时需要提供函数的拥有者和函数名字可以在任何出现表达式的sql语句中调⽤类型⼀致的标量函数。2.创建表值函数：create function 函数名 returns tableasreturn [(select…)]表值函数没有返回变量，没有函数体，只返回⼀个查询结果。调⽤内联表值函数：使⽤内联表值函数与视图类似，其作⽤相当于带参的视图。12345eg：create function 拥有者.函数名(@参数 参数类型) returns tableasreturn [()]调⽤：select* from 拥有者.函数名(参数)3.创建和调⽤多语句表值函数create function dbo.f_food(@class varchar(200))returns @f_food table（name varchar(20),age int,address varchar(200)）asbegininsert into @f_foodselect name, age,address , @f_food(@class)from table_name where name=@class)returnend调⽤：select* from 拥有者.函数名(参数)4.删除⾃定义函数:drop function 拥有者.函数名第三节 触发器1.基本概念：触发器：特殊的存储过程,在对表的数据进⾏update、insert、delete操作时⾃动触发执⾏，常⽤于保证业务规则和数完整性，增强数据完整性约束的能⼒。sql server2008⽀持三种类型的触发器：DML、DDL、登录触发器。适⽤场合：完成⽐check（只能实现同⼀表列之间的取值约束）约束更复杂的数据约束。保证数据库的性能⽽维护的⾮规范化的数据。可实现复杂的业务规则。评估数据修改前后的表状态，并采取对策。2.创建触发器：create trigger trigger_name{table|view}[with encryption]{for|after|instead of}{ [insert][,updata][,delete][,…]}assql_statament{…n]参数说明:for或after:后触发器,操作、约束检查完成后触发。instead of：前出发器，数据操作语句最多定义⼀个触发器。执⾏触发器⽽⾮引发语句。满⾜完整性约束则需要重新执⾏这些数据的操作。注意：⼀个表可建多个触发器，每个触发器可由三个操作触发。alter类型同⼀操作上建⽴多个触发器，instead of类型同⼀操作建⽴⼀个触发器。

所有建⽴和更改数据库以及数据库对象的语句，drop语句不允许在触发器中使⽤。触发器不要返回任何结果。eg：create trigger trigger_nameon table_namefor insert,updateasdeclare @newM moneyselect @newM=salep from inserted --inserted逻辑表,保存插⼊前的数据。update table_name1 set salep=@newMwhere id in (select id from insered )–deleted 逻辑表，保存删除后的数据。3.删除触发器drop trigger 触发器名第四节 游标游标：实现select 结果集的逐⾏处理。1.游标组成游标结果集（select 返回集）与游标当前指针（指向结果集中⼀⾏）特点：定位特定⾏；从当前位置检索⼀⾏或者多⾏；⽀持当前⾏数修改；对修改结果提供不同级别的可见⽀持。2.使⽤游标a.声明游标：ISO标准语法：declare cursor_name[1] cursor forselect_statement[2]参数说明：[1]insenstitive:创建临时副本，对临时副本操作，否则基本表；scroll范围，否则⽀持next；[2]read only:禁⽌更新 update 更新列指定列或者所有。b.打开游标open cursor_namec.提取数据fetch [1]from cursor_name into @variable_name[,…n]d.关闭游标close cursor_namee.释放游标deallocate cursor_name --释放游标的所有资源。第九章 安全管理内容摘要1.理解安全控制的基本概念2.了介sql server2008的存取控制3.掌握sql server2008的安全孔⼦的实现⽅式4.了解oracle的安全管理第五节 sql server 的安全控制建⽴登录账户create login login_name修改登录账户属性alter login login_names删除登录账户drop login login_name3.数据库⽤户⽤户有了登录账户，只是连接到了sql服务器，并不具有访问数据库的权限。映射：让登录账户成为数据库⽤户的操作 成为映射。⼀个登录账户可以映射为多个数据库⽤户。默认情况下，新建数据库只有⼀个⽤户：dbo，数据库⽤户的拥有者。建⽴数据库⽤户create user user_name [|for|from] login login_nameGuest⽤户，特殊数据库⽤户，匿名访问，没有映射到登录账户的时候使⽤grant connect to guestrevoke connect to guest --revoke 激活删除数据库⽤户drop user user_name4.权限管理登录账户成为合法⽤户后没有任何操作权限，就需要为⽤户授予数据库及其对象的操作权限。a.对象级别的权限（6种）select、insert、update、delete、references、 execute --references引⽤授权语句：grant 对象权限 on 对象 to (主体：数据库⽤户或者⾓⾊)[with grant option]拒绝权限:deny 对象权限 on 对象 to (主体：数据库⽤户或者⾓⾊)[cascade[as 主体]]收权语句：revoke 对象权限 on 对象 to (主体：数据库⽤户或者⾓⾊)[cascade[as 主体]]b.语句级别的权限grant create database ,create view …to user_name,user_name1deny create database ,create view …to user_name,user_name1revoke create database ,create view …from user_name,user_name15.⾓⾊定义:⼀组具有相同权限的⽤户就是⾓⾊。分为:系统⾓⾊和⽤户⾓⾊；系统⾓⾊⼜分为固定服务器⾓⾊（服务器级别⾓⾊）和固定数据库⾓⾊（数据库级别⾓⾊）⽤户⾓⾊均是数据库⾓⾊。固定服务器⾓⾊：Bulkadmin：执⾏bulk insert 语句权限。Dbcreator：创建、修改、删除还原数据库权限。Diskadmin：具有管理磁盘⽂件的权限。Processadmin：管理运⾏进程权限。Secutyadmin：专门管理登录账户、读取错误⽇志执⾏create database权限的账户。Serveradmin：服务器级别的配置和关闭服务权限。Setupadmin：添加删除除链接服务器。Sysadmin：系统管理员，Windows超级⽤户，⾃动映射为系统管理员。Public：系统预定义服务器⾓⾊，每个登录名都是这个⾓⾊成员。没有授予或者特定权限，则将具有这个⾓⾊权限。为固定服务器⾓⾊添加成员EXEC sp_addsrvrolemember ‘user_name’,‘sysadmin’为固定服务器⾓⾊删除成员EXEC sp_dropsrvrolemember ‘user_name’,‘sysadmin’固定数据库⾓⾊：定义在数据库级别上,存在于每个数据库中。⽤户加⼊固定数据库⾓⾊九具有数据库⾓⾊权限。Db_accessadmin：添加或者删除数据库权限。Db_backupoperator:备份数据库、⽇志权限。Db_datareader：查询数据库数据权限。Db_datawriter:具有插⼊、删除、更改权限。Db_ddladmin：执⾏数据定义的权限。Db_denydatareader：不允许具有查询数据库中所有⽤户数据的权限。Db_denydatawriter：不允许具有插⼊、删除、更改数据库中所有⽤户的数据权限。Db_owner：具有全部操作权限，包括配置，维护，删除数据库。Db_securityadmin：具有管理数据库⾓⾊、⾓⾊成员以数据库中语句和对象的权限。为固定数据库⾓⾊添加成员EXEC sp_addsrvrolemember ‘user_name’,‘Db_owner’为固定数据库⾓⾊删除成员EXEC sp_dropsrvrolemember ‘user_name’,‘Db_owner’⽤户⾃定义⾓⾊：属于数据库级别。创建⾓⾊：create role role_name授权和固定⼀样；删除⾓⾊：create role role_name第六节 oracle安全管理oracle的安全机制分为数据级别的安全控制，表级，列级，⾏级的安全控制。数据库级别的安全通过⽤户⾝份认证和授权给⽤户来保证。表级、列级、⾏级对的安全通过授予或回收对象权限保证，⽀持集中式、分布式、跨平台应⽤。oracle系统通常设置俩级别安全管理员：全局级：负责管理、协调、维护全局数据库⼀致性和安全性。场地级：负责本节点的数据库安全性、⽤户管理、系统特权与⾓⾊的管理。1.⽤户与资源管理:安权限划分⼤⼩分为DBA⽤户和普通⽤户。dba⽤户由dbms⾃动创建，sys和system⽤户，拥有全部的系统特权。普通⽤户：由dba⽤户或者是具有dba权限的⽤户创建，并授予特权。建⽴⽤户create user user_name identified by 123456 default tablespace xxxquota 5m on xxx(限制使⽤空间5m)管理⽤户和资源：dba特权⽤户可以该表⼀个⽤户资源使⽤限额、密码、登录次数等alter user user_name quote 69m on xxxxalter user user_name identified by123456799删除⽤户drop user user_name cascade （删除⽤户以及所拥有的全部对象）2.权限管理a.系统特权：三种特权：connect:不能建⽴任何对象，可以查询数据字典及访问数据库对象。recourse：可建⽴数据库对象（表，视图，索引…）。dba:拥有在预定义的全部权限。1234b.对象特权：⽤户维护表级，⾏级，列级数据的安全性。eg：grant all on 表 to ⽤户 grant select（列1，列2,.....）on 表 to ⽤户第⼗章 数据库的维护和优化内容摘要：1.了解数据库库运⾏的基本原理。2.了解运⾏状态监控与分析。3.了解数据库存储空间管理。4.掌握数据库优化⽅法。DBAS进⾏维阶段的主要任务：保证数据库系统安全，可靠⾼效的运⾏。数据库的运⾏除了DBMS与数据库外，还需要各种系统部件协调⼯作。⾸先必须有各种相应的应⽤程序。其次各应⽤程序与dbms都需要在操作系统os⽀持下⼯作。维护⼯作包括：数据库转存与恢复。数据库安全性、完整性控制。检查并改善数据库性能。数据库重组和重构。重组不修改数据库原有的逻辑结构和物理结构。重构部分修改模式和内模式。数据库的监控分析：指管理员借助⼯具检测DBMS的运⾏情况，掌握数据库当前或者以往的负荷、配置、应⽤等信息，并分析检测数据的性能参数和环境信息，评估dbms的整体运⾏状态。根据检测分析实现不同，分为：数据库系统建⽴的⾃动监控机制，由DBM⾃动检测数据库的运⾏情况。管理员⼿动实施的检测机制。根据监控对象不同，分为：数据库架构体系的监控监控空间基本信息，空间的使⽤率与剩余空间⼤⼩等。数据库性能监控监控数据缓存命中率，库缓冲、⽤户锁、索引使⽤、等待事件等。对数据空间进⾏管理是⼀项⾮常重要的⼯作。空间使⽤变化带来的问题：降低数据系统服务性能空间溢出导致灾难停机事故数据的存储结构分为：逻辑存储结构物理存储结构sql server数据库中⼀个逻辑上的数据库直接和⼀组物理上的数据⽂件对应，没有空间的概念。DBMS对空间的管理包括：创建数据空间、修改空间⼤⼩、删除空间、修改空间状态、新建、移动、关联数据⽂件。数据库性能优化是dbas系统上线后常见的运⾏维护任务之⼀。进⾏数据库性能优化时，⾸先要确定优化⽬标，⼀般从数据库运⾏环境、参数调整、模式调整、数据库存储优化、查询优化⼏⽅⾯考虑。1.数据库运⾏环境与参数调整可以从外部环境、调整内存分配、整磁盘i/o、调整资源竞争等⼏⽅⾯着⼿改表数据库参数，提⾼性能。外部调整：cpu，⽹络调整竞争：修改控制连接数据库的最⼤进程数。减少调度进程的竞争。减少多线程服务进程竞争。减少重做⽇志缓存区竞争。减少回滚段竞争。2.模调整式优化数据库的规范化过程:⾼效率利⽤存储空间,减少数据冗余,减少数据的不⼀致性。反规范化：将规范化关系转换为⾮规范化的关系过程。反规范化⽅法：增加派⽣冗余列、增加冗余列、重新组表、分割和增加汇总表的⽅法。都破坏数据完整性采⽤反规范化技术从实际出发均衡利弊。分割表 ：⽔平，垂直。3.存储优化：a.物化视图b.聚集，创建聚合索引。4.查询优化a.合理索引，但是系统开销⼤了。索引原则：是否为⼀个属性建⽴索引：该属性是码或存在某个查询中被使⽤。在哪些属性建⽴索引：若⼀个关系的多个属性共同出现在查询中，⼀般采⽤多属性索引。是否建⽴聚合索引：聚合索引适合范围查询，可建⽴多属性索引，优点体现在数据记录存取过程中。使⽤散列还是树索引：散列索引适合等值查询，关系数据库多使⽤B+索引，⽀持作为搜码的属性的等值查询和范围查询。使⽤索引原则：经常在查询中被作为条件使⽤的列。应为其建⽴索引。频繁进⾏排序或分组的列，为其建⽴索引。⼀个列的值域很⼤时，建索引。如果待排列的列有多个，建复合索引。可以使⽤系统⼯具来检查索引的完整性，必要时进⾏修复，当数据表更新⼤量数据后，删除并重建索引。索引建⽴完成后，运⾏期间还需要优化。调优的⽬的：动态评估需求。索引调整和修改的原因：缺索引，查询语句时间长。某些索引⾃开始没使⽤，却占⽤了较多的磁盘空间。索引建在频繁该表的属性上，导致系统开销⼤。1718 b.避免或者简化排序： order by 和group by语句涉及的排序，磁盘开销很⼤，应利⽤索引⾃动适当的次序输出。 影响优化器的因素： 由于现在索引不⾜，导致排序索引中不包括⼀个或者⼏个待排序的因素、 order by 和group by⼦句中列的次序和索引次序不⼀致。排序的列来⾃不同的表 为避免不必要的排序。要正确的建索引，合理合并数库表，如排序不可避免，则简化它，缩⼩范围、 c.消除对⼤型表数据的顺序存储 嵌套查询，对表的顺序存取严重影响查询效率。 优化⽅法：对连接列进⾏索引，或者使⽤并集避免顺序存取。 d.避免正则表达式 e.使⽤临时表加速查询 将表的⼀个⼦集排序创建临时表。 f.排序取代⾮排序存储磁盘。 g.不充分的连接条件。 h.存储过程优化。 l.不要随意使⽤游标。 m.事务处理。 server性能⼯具sql server Profiler 检测性能。数据库引擎优化顾问第⼗⼆章 备份与恢复第⼀节 备份恢复概念1.备份数据库数据备份的原因：防⽌丢失，数据的转移⽅式。2.恢复数据库俩种类型：介质故障恢复，⾮介质故障恢复。第⼆节 sql server的备份与恢复机制1.恢复模式三种恢复模式：简单恢复模式，完整恢复模式，⼤容量⽇志恢复模式。2.备份内容和时间备份内容⽤户数据库系统数据库备份时间系统数据库：修改之后进⾏备份⽤户数据库：周期备份⽴刻进⾏备份：创建数据库之后，或批量加载数据库之后。创建索引之后。执⾏清理事务⽇志之后。执⾏⼤容量数据操作之后。 server的备份机制a.备份设备：磁带，磁盘备份设备类型：永久备份设备：在备份之前需要先建⽴。临时备份设备：不需要预先建⽴，在备份的时候将数据库直接拷贝在物理⽂件上。使⽤系统存储过程创建备份设备：sp_addumpdecice[@devtype=]‘device_type’–设备类型 ，可以是disk和tape[@logicalname]‘logical_name’–设备逻辑名称[@physicalname]‘physicalname’–设备实际名称eg：创建⼀个名字为my_bak的磁盘设备，其物理名称为D:c sp_addumpdecice ‘disk’ ,‘my_bak’,‘D:’1234567891b.备份类型 1）数据库备份：完整备份，差异备份 2）⽂件备份：⽂件备份，差异⽂件备份 3）事务⽇志备份c.常⽤备份策略： 完整备份 完整备份+⽇志备份 完整备份+差异备份+⽇志备份e.实现备份： 使⽤sql server平台 使⽤t-sql语句 eg：backup database 数据库 to my_bak；完整数据库备份。 backup log pubs to my_bak_log；事务⽇志备份 ，pubs数据库名字 server的恢复机制数据库（数据库完整还原）数据⽂件（⽂件还原）还原顺序：恢复最近的完整备份恢复最近的差异备份恢复数据库实现还原：使⽤sql server 管理平台；使⽤t-sql语⾔；eg：restore database 数据库 from mybak_2 --完整备份where file=1,norecovery --norecovery不要覆盖restore database 数据库 from mybak_2 --差异备份where file=2,norecovery --⽇志备份restore log 数据库 from mybak_log第三节 oracle的备份与恢复机制数据逻辑备份和恢复常见故障:sql语句失效进程失效实例失效⽹络失效123456上⾯的四种内部机制处理。⽤户错误介质失效利⽤备份数据恢复。逻辑备份：指通过⼯具将数据库的数据转换成格式的⽂件，保存在⽂件系统中。⼯具 ：exp/imp expdp/ipdp物理备份:⽤户管理备份RECOVER MANAGER(RMAN)备份第三⽅软件恢复数据库restore :从备份数据库找到完整的备份⽂件，恢复完整的过程⽂件。recover ：将⽇志和增量备份的改变⽤户到数据⽂件，使数据⽂件恢复指定时间点的过程。第⼗三章 ⼤规模数据库架构第⼀节 分布式数据库1.分布式数据库系统与分布式数据库的区别：分布式数据库系统：数据分布存储于若⼲场地，并且每个场地由独⽴于其他场地的dbms进⾏数据管理。物理上分散，逻辑上集中的数据库系统。分布式数据库:分布式数据库系统中各场地数据库的逻辑集合。2.分布式数据库⽬标与数据分布策略分布式数据库⽬标：12个⽬标：本地⾃治，⾮集中式管理，⾼可⽤性。位置独⽴，数据分⽚独⽴性，数据复制独⽴性。分布式查询，事务管理。硬件独⽴性，操作系统独⽴性，⽹络独⽴性数据库管理系统独⽴性。数据库分布策略：从数据分⽚和数据分配考虑数据分⽚（对关系操作）按⼀定规则将某⼀个全局关系划分为多个⽚段，⽔平分⽚——垂直分⽚–、导出分⽚–混合分⽚–4.分布式数据库的相关技术分布式查询⽤户与分布式数据系统的接⼝。分布式查询优化考虑：1.操作顺序2.操作算法3.不同场地的数据流动的顺序。分布事务管理第⼆节 并⾏数据库1.并⾏数据库概述并⾏数据库系统：通过并⾏实现各种数据操作.优势：增强可⽤性。2.并⾏数据库系统结构共享内存系统共享磁盘⽆共享资源系统层次结构第三节 云计算数据库的结构第四节 xml系统数据库 ；⽀持xml格式数据库。第⼗四章 数据仓库和数据挖局第⼀节 决策⽀持系统的发展⼀般数据分为：分析型数据操作型数据决策⽀持系统（DSS）DSS建⽴在OLTP数据库仓库是核⼼，操作型系统是数据源。数据仓库的其他特点：粒度：影响数据量，粒度越⼩回答越细。