2021-05-13

 

创建视图：

create view 视图名称

as

select语句

go

 

删除视图：

drop view 视图名称

 

创建视图的语句文本存储在sysobjects系统表中

返回值为一个数据点的函数被称为标量值函数

视图机制提高了数据库系统的安全性

 

视图与表之间的联系与区别：

联系：

视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系
区别：

1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。

 

注意：在大多数情况下，视图是用来查询的，而非增加、修改和删除。当必须要对视图进行增加或修改操作时，经常会用到 WITH CHECK OPTION，此选项是为了限制增、改操作，保证增改后，数据仍然能够被查出来： 视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。

a) 对于 update,有 with check option，要保证 update 后，数据要被视图查询出来

b) 对于 delete,有无 with check option 都一样

c) 对于 insert,有 with check option，要保证 insert 后，数据仍要被视图查询出来

d) 对于没有 where 子句的视图，使用 with check option 是多余的。

存储过程

1. sp_who：用来查看当前用户、会话、进程的信息，其语法如下：sp_who [[@loginname=]'登录名'|回话 ID|'ACTIVE'] 其中 active 排除正在等待用户发出下一个命令的会话。

 

sp_monitor：显示有关 SqlServer 的统计信息。

 

sp_helpdb：用来报告有关指定数据库或所有数据的信息。语法为：sp_helpdb [[@dbname=]'库名']

 

注意：上边的语句得以执行，则当前登录者必须具有数据库中的 public 角色成员身份。

 

自定义存储过程

只要拥有 create procedure 的权限，就能在本地库中创建自己的存储过程。在定义时，我们可以指定 输入参数、输出(带回) 参数、执行任务的语句等。 在 procedure 中可以进行增、删、改、查 各种操作。

其最简化语法如下：

create proc[edure] 过程名称

@参数名称 参数类型, --输入参数

 ……

@参数名称 参数类型 output --输出参数

 As

执行语句

Go

 

调用方式

--添加数据 declare @id int exec 存储名 @id output,100,'是正品','2013-3-3',3,5 print @id

--更新数据 exec 存储名 对应字段

 

1. 存储过程中参数默认值的用法，在调用时如果使用默认值就可以少写参数；

SELECT @Id=@@identity --也可以使用 SCOPE_IDENTITY()来获取自增值

1. 输出(带回)参数在定义和调用时的用法，定义时使用 output，调用时仍然要使用 output。

 

注意：调用存储过程时，必须保证调用时的实际参数和定义时的形式参数一一对应。

       注意事项：在执行语句中，不能包含以下语句：

1. create 系列，包括（aggregate、rule、default、schema、function、trigger、procedure、view）。

2. alter 系列，包括（function、trigger、procedure、view）。

3. set 系列，包括（parseonly、showplan_all、showplan_text、showplan_xml）。

4. use 数据库名

 

存储过程注意事项：

1. 可以创建和引用临时表（生命周期为当前存储过程）。

 2. 可以调用另一个存储过程，被调用者可以访问有第一个存储过程创建的所有对象。

3. 参数的最大数目为 2100 个。

4. 局部变量的个数可以多个，仅仅受到内存的限制。

5. 存储过程最大可达 128M。

 

查看过程定义文本：

sp_helptext 存储过程名

过程加密：

       如果希望加密 过程的定义文本，需要 在参数之后 as 之前加入 with encryption，因 为加密后无法再看到定义文本，因而开发者需要保留好定义语句。

 

数据库三级模式：

数据库领域公认的标准结构是三级模式结构，它包括外模式、概念模式、内模式，有效地组织、管理数据，提高了数据库的逻辑独立性和物理独立性。用户级对应外模式，概念级对应概念模式，物理级对应内模式，使不同级别的用户对数据库形成不同的视图。所谓视图，就是指观察、认识和理解数据的范围、角度和方法，是数据库在用户“眼中"的反映，很显然，不同层次（级别）用户所“看到”的数据库是不相同的。

外模式:

外模式又称子模式或用户模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录 (外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行操作。外模式反映了数据库系统的用户观。

      概念模式：

概念模式又称模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的。概念模式反映了数据库系统的整体观。

       内模式：

内模式又称存储模式，对应于物理级。它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义的。内模式反映了数据库系统的存储观。

在一个数据库系统中，只有唯一的数据库， 因而作为定义 、描述数据库存储结构的内模式和定义、描述数据库逻辑结构的模式，也是唯一的，但建立在数据库系统之上的应用则是非常广泛、多样的，所以对应的外模式不是唯一的，也不可能是唯一的。

 

架构：

创建架构命名规则：

       1) 名称不能超过 128 个字符。

2) 必须以英文字母打头。

3) 在同一个 DB 中，Schema 名称不能重复。

删除架构

drop schema odr --odr 为架构的名称

 

三种事务模式

1. 显示事务

显式事务（Explicit Transaction）是由设计人员明确定义事务的启动与结束的一种事务。
可使用 BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、ROLLBACK WORK 等语句来定义显式事务。

1. 自动提交事务

这是 SQL Server 的默认模式，所有未特别声明的事务，都被视为自动提交的事务。但
是只以一个操作作为事务范围，如一个 Update 或 Delete 等。当事务完成时，每个单独的
T-SQL 语句都将被提交或因出现错误而还原。

1. 隐式事务

通过 API 函数或 T-SQL 的 SET IMPLICIT_TRANSACTIONS ON 语句将隐式事务模式
设置为打开。下一个语句会自动开始一项新的事务。当该项事务完成时，下一个 T-SQL 语
句会再开始一项新的事务。

 

索引：

聚集索引（每张表只能建立一个）和非聚集索引（每张表可以建立多个）。

索引的弊端：

创建和维护索引要耗费时间。

1) 索引需要占用物理空间；

2) 当对表中的数据进行更新操作时，索引也要动态地维护，这样就降低了数据的维护效率； 索引总是建立在列上的。

建立索引的原则：

使用聚集和非聚集索引的原则：

其中聚集索引的使用对查询性能的提升尤为重要，一定要建立在：

1) 您最频繁使用的、用以缩小查询范围的字段上；

2) 您最频繁使用的、需要排序的字段上。

 

系统存储过程：

1、Sp_renameDB的含义:   重命名数据库；

2、sp_databases的含义:用于列出服务器上所有的数据库；

3、sp_columns的含义：查看某个表列信息；

4、sp_helpconstraint的含义:用于列出所有约束；

5、sp_helptext的含义:查看视图的定义信息；

6、sp_tables的含义:查看当前环境下的所有对象；

7、sp_helpDB的含义:报告有关制定数据库或所有数据库的信息；

 

权限管理：

1. 授予权限

GRANT {ALL|授权命令} ON 安全对象 TO 被授予者

1. 拒绝权限

DENY {ALL|授权命令} ON 安全对象 TO 被授予者

1. 撤销权限

REVOKE {ALL|授权命令} ON 安全对象 FROM 被授予者

 

在 ClothShop 库中，禁用 guest 用户。

 revoke connect from guest      

在 ClothShop 库中，收回 YPZ 用户对表 Customers 的删除权限。

 revoke delete on Customers from YPZ

 

函数：

1. 标量值函数

返回值为一个数据点的函数被称为标量值函数，其定义语法如下：

create function fun_name(参数 1 参数 1 类型,参数 2 参数 2 类型……)

returns 返回值类型

[with encryption]

 [as]

begin

--函数内容

--return 返回值

End

1. 内联表值函数

返回值为一个结果集的函数又可以细分为两类，现在给大家介绍的是较为简单的一种：直接根据查询条件 返回一个结果集（函数体就是一条查询语句，当然也可以使用子查询等）。其返回定义语法如下：

create function fun_name(参数 1 参数 1 类型,参数 2 参数 2 类型……)

returns table

[with encryption] --是否加密定义文本

[as]

return 一个查询的结果集

触发器：

创建触发器

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ] [ NOT FOR REPLICATION ]

AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME }