5.UML与数据库应用系统
5.1 DBAS建模
UML四层建模概念框架——1.元元模型层 2.元模型层 3.模型层 4.用户模型层
5.2 DBAS业务流程与需求表达
5.2.1 业务流程与活动图——活动图-用于描述系统、用例和程序模块中的逻辑流程的先后执行次序，并行次序
5.2.2 系统需求与用例图——用例图-是把满足用户需求所有功能表示出来的工具 1.系统 2.角色 3.用例
5.3 DBAS系统内部结构的表达——1.静态结构：类图 2.动态结构：顺序图，通信图
5.3.1系统结构与类图——类图-展现了一组类、接口和协作以及它们间的关系
1.属性——包括1.可见性 2.名称 3.类型 4.缺省值 5.约束性
2.操作——描述了类的动态行为
3.关系——1.关联关系 2.继承关系 3.依赖关系 4.精化关系
5.3.2 系统结构与顺序图——顺序图-用于描述系统内对象之间的消息发送和接收序列（时间）
5.3.3 系统结构与通讯图——通讯图-用于展示对象之间的的联系以及对象间发送和接收的消息（空间）
5.4 DBAS系统微观设计的表达
5.4.1 微观设计与对象图——对象图-某个特定时间点上，系统所存在的所有对象的一个快照
5.4.2 微观设计与状态机图——状态机图-陈述系统中有关事件或对象的状态转移
5.4.3 微观设计与时间图——时间图-描述时间对状态的影响
5.5 DBAS系统宏观设计的表达
5.5.1 宏观设计与包图——包图-包以及包之间的关系的类图
5.5.2 宏观设计与交互概述图——交互概览图类似于活动图，因为它们都可视化一系列活动。
5.5.3 宏观设计与复合结构图——复合结构图显示类元内部结构，包括它与系统其他部分的交互点。
5.6 DBAS系统实现与部署的表达
5.6.1系统实现与组件图——组件图表示系统的静态实现视图
5.6.2 系统实现与部署图——部署图描述系统中硬件和软件的物理配置情况和系统体系结构
6.高级数据查询
6.1 一般数据查询功能拓展
6.1.1 使用TOP限制结果集
SELECT (TOP语句) ... FROM .... ORDER BY ... TOP语句 TOP n [percent] [WITH THIS] 1 TOP n 取查询结果前n行 2 TOP n percent 取查询结果前n%行 3 WITH THIS 包括最后一行并列的结果
6.1.2 使用CASE函数
1.简单CASE CASE 测试表达式 WHEN 简单表达式1 THEN 结果表达式1 WHEN 简单表达式2 THEN 结果表达式2 ... [ELSE 结果表达式] END 2.搜索CASE函数 CASE WHEN 布尔表达式1 THEN 结果表达式1 WHEN 布尔表达式2 THEN 结果表达式2 ... [ELSE 结果表达式] END
6.1.3 将查询结果保存到新表中
SELECT ... INTO 新表名 FROM ... ...
6.2 查询结果的并、交、差运算
并： (SELECT ...) UNION (SELECT ...) 交： (SELECT ...) INTERSECT (SELECT ...) 差： (SELECT ...) EXCEPT (SELECT ...)
6.3 相关子查询
1.使用子查询进行基于集合的测试 WHERE 表达式 [NOT] IN (子查询) 2.使用子查询进行比较测试 WHERE 表达式 比较运算符 (子查询) 3.使用子查询进行存在性测试 WHERE 表达式 [NOT] EXISTS (子查询)
6.4 其他形式的子查询
1.代替表达式的子查询 SELECT ... (子查询) AS ‘列名’ FROM ... ... 2.派生表 SELECT ... FROM (子查询) AS ‘表名’
6.5 其他一些查询功能
6.5.1 开窗函数
1.将 OVER 子句与聚合函数结合使用 SELECT ... 聚合函数(...)OVER(PARTITION BY ...) AS ... FROM ... ... 2.将 OVER 子句与排名函数一起使用 1.RANK() 分区内排名 有并列 SELECT...RANK()OVER(PARTITION BY... ORDER BY...)AS... 2. DENSE_RANK() 没有并列排名 SELECT...DENSE_RANK()OVER(PARTITION BY... ORDER BY...)AS... 3.NTILE(N) 将查询到的数据分N组 SELECT ... NTILE( 3 ) OVER (ORDER BY ...)AS... 4. ROW_NUMBER() 对数据进行编号 SELECT...ROW_NUMBER() OVER(PARTITION BY... ORDER BY...)AS...
6.5.2 公用表达式
WITH 表达式名(属性1，属性2...属性n) AS( SELECT ...（与表达式属性数量一致） FROM ... ... )
7.数据库以及数据库对象
7.1创建以及维护数据库
7.1.1 SQL Server数据库概述
两类数据库：系统数据库，用户数据库
7.1.2 SQL数据库组成
两类文件：数据文件，日志文件
1.数据文件：存放数据库数据
2.事务日志文件：用于存放恢复数据库的所有日志信息
3.数据库存储空间的分配：
一页Page是8KB，其中8060B存放数据，132B存放系统信息
不允许一行数据存储在不同页上（根据行数据大小不同，未利用空间不同）
7.1.3 数据库文件组
两类文件组：主文件组，用户定义的文件组
7.1.4 数据库文件属性
1.文件名以及其位置 2.初始大小 3.增长方式 4.最大大小
7.1.5 用T-SQL语句创建数据库
CREATE DATABASE 数据库名 ON [PRIMARY] --在主文件组 ( NAME= --文件名 FILENAME= --文件路径 SIZE= --初始大小MB MAXSIZE= --最大大小MB FILEGROWTH= --自动增长的递增量MB ）， FILEGROUP 文件组名 ( NAME= --文件名 FILENAME= --文件路径 SIZE= --初始大小MB MAXSIZE= --最大大小MB FILEGROWTH= --自动增长的递增量MB ） ( NAME= --文件名 FILENAME= --文件路径 SIZE= --初始大小MB MAXSIZE= --最大大小MB FILEGROWTH= --自动增长的递增量MB ） LOG ON ( NAME= --日志名 FILENAME= --文件路径 SIZE= --初始大小MB MAXSIZE= --最大大小MB FILEGROWTH= --自动增长的递增量MB ) ( NAME= --日志名 FILENAME= --文件路径 SIZE= --初始大小MB MAXSIZE= --最大大小MB FILEGROWTH= --自动增长的递增量MB )
7.1.6 修改数据库
--加数据文件 ALTER DATABASE 数据库名 ADD FILE( NAME= FILENAME= SIZE= FILEGROWTH= ) --加日志文件 ATTER DATABASE 数据库名 ADD LOG FILE( NAME= FILENAME= SIZE= FILEGROWTH= MAXSIZE= )
--收缩数据库 使该数据库中所有文件都有20%可用空间 DBCC SHRINKDATABASE(数据库名，20) --收缩数据库文件大小 使数据库文件收缩到4MB DBCC SHRINKFILE(数据库文件名，4) --删除数据库文件 ALTER DATABASE 数据库名 REMOVE FILE 文件名
7.1.7分离和附加数据库
--分离数据库：删除数据库实例，但是不删除数据库文件 EXEC sp_detach_db '数据库名' --附加数据库 CREATE DABASE 数据库名 ON (FILENAME='数据库路径'), (FILENAME='数据库路径') FOR ATTACH
7.2 架构
架构（Sehema）是数据库下的一个逻辑命名空间，可以存放表、视图等数据库对象，它是一个数据库的容器。
一个数据库可以包含一个或者多个架构，架构由特定的授权用户所拥有
一个架构可以由零个或者多个架构对象组成
--1定义架构 --不匿名架构 CREATE SCHEMA 架构名 AUTHORIZATION 用户名 --匿名架构 CREATE SCHEMA AUTHORIZATION 用户名 --定义架构同时定义表 CREATE SCHEMA 架构名 AUTHORIZATION 用户名 CREATE TABLE 表名(...) ---2删除架构 DROP SCHEMA 架构名 {CASCADE|RESTRICT} CASCADE: 删除架构的同时将该架构中的所有架构对象一起全部删除 RESTRICT: 如果被删除的架构中包含架构对象，则拒绝删除此架构
7.3 分区表
7.3.1 基本概念
分区表：分区表是将表中数据按水平方式划分成不同的子集，这些数据子集存储在数据库的一个或者多个文件组中。
如果数据量大，而且数据是分段的，并且对不同段的数据使用的操作不同，则适用于属于分区表
7.3.2 创建分区表
--创建分区函数 CREATE PARTITION FUNCTION 分区函数名 AS RANGE {LEFT|RIGHT} --LEFT:(<x<=) RIGHT：(<=x<) FOR VALUES(1,10) --以1，10为边界分为3个区 --创建分区方案 分文件 CREATE PARTITION FUNCTION 方案名(int) --对于int类型 AS RANGE LEFT FOR VALUES(1,10,100) --分成4个区 GO CREATE PARTITION SCHEME 方案名 --schema构架 scheme方案 AS PARTITION 方案名 TO(文件名1，文件名2，文件名3，文件名4); --创建分区方案 单个文件 CREATE PARTITION FUNCTION 方案名(int) --对于int类型 AS RANGE LEFT FOR VALUES(1,10,100) --分成4个区 GO CREATE PARTITION SCHEME 方案名 --schema构架 scheme方案 AS PARTITION 方案名 ALL TO (文件名);
--创建使用该分区的表 CREATE PARTITION FUNCTION 方案名(int) --对于int类型 AS RANGE LEFT FOR VALUES(1,10,100) --分成4个区 GO CREATE PARTITION SCHEME 方案名 --schema构架 scheme方案 AS PARTITION 方案名 TO(文件名1，文件名2，文件名3，文件名4); GO CRATE TABLE 表名（...）
7.4 索引
--1创建索引 CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX 索引名 ON 表名(属性名[ASC|DESC],属性名[ASC|DESC]....) ON 表名(属性名[ASC|DESC],属性名[ASC|DESC]....) ...（如有多个分区） UNIQUE：为该表的唯一索引 CLUSTERED：创建聚集索引 NONCLUSTERED：创建非聚集索引 ASC：升序 DESC：降序 --2.删除索引 DROP INDEX 索引名
7.5 索引视图
索引视图概念：建有唯一聚集索引的视图
作用：可以提高查询性能
--定义视图 CREATE VIEW 视图名 WITH SCHEMABINDING --索引视图必须使用SCHEMABINDING AS (SELECT 语句) GO --在视图上定义唯一聚集索引 CREATE UNIQUE CLUSTERED INDEX 索引名 ON 视图名（属性名，属性名...） GO
8数据库后台编程技术
8.1存储过程
8.1.1基本概念
实质上就是存储在数据库中供所有用户程序调用的子程序
好处：1.允许模块化设计 2.改善性能 3.减少网络流量 4.增强应用安全性
8.1.2创建、执行和删除存储过程
--1创建存储过程 CREATE [PROC|PROCEDURE][SCHEMA_NAME] 程序名 @参数名 参数类型[OUTPUT]，@test int @test2 int OUTPUT AS [SELECT语句|SET语句(set @test2=@test*5)|UPDATE|DELETE] --2执行存储过程 DECLARE @返回变量名 EXEC 程序名 @输入参数=...，@返回变量名 OUTPUT --3删除存储过程 DROP [PROC|PROCEDURE][SCHEMA_NAME] 程序名
8.2用户定义函数
8.2.1创建和调用标量函数
标量函数：返回单个数据值的函数
CREATE FUNCTION 函数名 (@ 参数 类型，@test int) RETURNS int --返回类型 AS DECLARE @ 返回变量名 返回变量类型 BEGIN (SQL语句) RETURN @ 返回变量名 END
--调用函数 SELECT 函数名(参数，参数...)
8.2.2创建和调用内联表值函数
内联表值函数：返回的是一个表
--创建函数 CREATE FUNTION 函数名 (@参数 类型，@参数 类型 ....) RETURNS table AS RETURN(SELECT语句...) --调用函数 SELECT * FROM 函数名(参数，参数...)
8.2.3创建和调用多语句表值函数
--创建函数 CREATE FUNCTION 函数名 (参数 类型，参数 类型，...) RETURNS @表名 table（属性 类型，属性 类型，...） AS BEGIN INSERT INTO @表名 SELECT 语句... RETURN END --调用函数 SELECT * FROM 函数名(参数，参数...)
8.2.4删除用户自定义函数
--删除函数 DROP FUNCTION 函数名
8.3触发器
8.3.1基本概念
触发器：是特殊的存储过程，在对表进行操作时自动触发
三种类型触发器：DML触发器（INSERT,UPDATE,DELETE），DDL触发器(CREATE,ALTER,DROP)，登录触发器(LOGON)
8.3.2创建触发器
--可以使用两个临时表 inserted表：用于存储inserted和update语句所影响的副本 deleted表：用于存储delete和update语句所影响的副本 update相当于先delete再inserted，故可用deleted表和inserted表代替 --创建 后触发型触发器 CREATE TRIGGER 触发器名 ON 表名 FOR [INSERT,UPDATE,DELETE] --执行语句后触发器触发 AS IF EXISTS(SELECT语句...) --如果出现了不正常的情况 BEGIN ROLLBACK --撤销之前操作 PRINT '错误提示' END ELSE .... --正常情况下的执行的操作 --创建 前触发型触发器 CREATE TRIGGER 触发器名 ON 表名 INSTEAD OF [INSERT,UPDATE,DELETE] --执行语句前触发器触发 AS IF NOT EXISTS(SELECT语句...) --如果没有对正常的情况产生影响 重复先前 [INSERT,UPDATE,DELETE] 语句 --使被 前触发器拦截的语句执行 ELSE PRINT '错误提示'
8.3.3删除触发器
--删除触发器 DROP TRIGGER 触发器名字
8.4游标
8.4.1游标的组成
两部分组成：
1.游标结果集：指定义游标的SELECT语句返回的结果的集合
2.游标当前行指针：指向该结果集的某一行的指针
8.4.2使用游标
--1.声明游标 DECLARE 游标名 [INSENSITIVE][SCROLL] CURSOR FOR SELECT语句 --定义游标结果集 [FOR {READ ONLY|UPDATE [OF 属性[...,n]]}] INSENSITIVE:使用临时副本，该游标不修改基本表的数据 SCROLL：指定所有提取项均可用（无SCROLL只能用NEXT） READ ONLY:禁止该游标修改数据 UPDATE [OF 属性[...,n]]:只允许修改列出的列 --2打开游标 OPEN 游标名 --3提取数据 FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] FROM 游标名 [INTO @ 变量名] NEXT：下一行 PRIOR：上一行 FIRST：第一行 LAST：最后一行 ABSOLUTE n：从第一行开始的第n行 RELATIVE n；从当前行开始的第n行 [INTO @ 变量名]：将提取的数据保存到局部变量中 @@FETCH_STATUS取值的含义 0：FETCH语句成功 -1：FETCH语句失败 或 此行不在结果集中 -2：提取的行不存在 --4关闭游标 CLOSE 游标名 --5释放游标 DEALLOCATE 游标名