菜鸟笔记——8月15日

1.视图 视图的基本概念： 视图是一个虚拟表，表是视图的基础，数据库不存放视图的对应数据，视图的对应数据存放在视图引用的表中。 视图的作用： 1.集中数据 2.简化复杂查询的结构，方便对数据的操作 3.对数据提供安全保护 4.便于组织数据导出 视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。

select name as 视图名称,crdate as 创建日期,refDate as 最后修改时间 from sysobjects where xtype='v'

v代表数据表

参考：http://wenku.baidu.com/view/78a23a83d4d8d15abe234ea1.html

2:存储过程

1:获取数据库中全部的存储过程

select name as 存储过程名称,crdate as 创建时间,refdate as 最后一次修改时间 from sysobjects where xtype ='p'

2：存储过程可进行加密：

ALTER  PROCEDURE [insert_加密学生信息表](@姓名_2 [varchar](50),@性别_3 [varchar](50),@年龄_4 [int],@籍贯_5 [varchar](50),@课程编号_6 [varchar](50)) with encryption AS INSERT INTO [db_09].[dbo].[学生信息表] ([姓名],[性别],[年龄], [籍贯],[课程编号]) VALUES (@姓名_2, @性别_3, @年龄_4, @籍贯_5, @课程编号_6)

（出于安全性考虑，不让可以访问数据库服务器的用户读取存储过程的代码，SQL   Server自身不支持解密。详细参考http://www.cnblogs.com/dgjack/archive/2011/11/10/2244505.html）

3：原来access不支持存储过程和触发器的使用。实现这一类功能通过宏/VBA来完成

参考http://access911.net/fixhtm/72FAB21E11DCE7F3.htm?tt=

4：关于存储过程的写法：

当 SET NOCOUNT 为 ON 时，不返回计数

sysname数据类型是什么

sysname

SQL Server 实例包括用户定义的名为 sysname 的数据类型。sysname 用于表列、变量以及用于存储对象名的存储过程参数。sysname 的精确定义与标识符规则相关；因此，SQL Server 的各个实例会有所不同。sysname 与nvarchar(128) 作用相同。SQL Server 6.5 或早期版本仅支持较小的标识符；因此，在早期版本中，sysname 被定义为varchar(30)。

 

使用 cursor 数据类型的参数
　　存储过程只能将 cursor 数据类型用于 OUTPUT 参数。如果为某个参数指定了 cursor 数据类型，也必须指定 VARYING 和 OUTPUT 参数。如果为某个参数指定了 VARYING 关键字，则数据类型必须是 cursor，并且必须指定 OUTPUT 关键字。
　　http://www.cnblogs.com/xiaozhuoyun/archive/2008/06/11/1217415.html

5:关于数据库关系表可参照http://msdn.microsoft.com/zh-cn/library/ms171971.aspx

6:关于游标：游标的效率比较低，在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。同样的，在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量……

游标不是最佳选择，关于游标的一般使用情况：

1.现存系统有一些游标，我们查询必须通过游标来实现

2.作为一个备用方式，当我们穷尽了while循环,子查询，临时表，表变量,自建函数或其他方式扔来无法实现某些查询的时候，使用游标实现.

 关于游标的使用参考：

http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

 http://www.cnblogs.com/zijinguang/archive/2007/12/17/1003281.html

7:关于存储过程的优势：

（1） 存储过程允许标准组件式编程

    存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。

    （2） 存储过程能够实现较快的执行速度

    如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

    （3） 存储过程能够减少网络流量

    对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。

    （4） 存储过程可被作为一种安全机制来充分利用

    系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。（我们将在14 章“SQLServer 的用户和安全性管理”中对存储过程的这一应用作更为清晰的介绍）

    注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字。

8:临时表：相关定义和实现

表名前使用一个#号，临时表是局部的，使用两个#号，临时表是全局的，在断开连接后sql会自动删除临时表
create table #a
(
id int,
name varchar(50)
)
insert into #a(id,name) values(1,'123')
select * from #a
drop table #a
临时表除了名称前多了#号外，其他操作与普通表完全一样。
tb_Student是已建立好的表，我们通过临时表temp把tb_Student表中的内容复制到tb_lizi表中，可以使用如下的代码实现：
use mcf
SELECT * INTO  #temp FROM tb_Student
SELECT * INTO  tb_lizi FROM #temp
执行后断开sql连接并重新连接(也可以退出sq再l重新启动sql)，发现tb_lizi表中的内容tb_Student表中的内容完全一致,实现了复制，同时我们没有用代码删除temp表，但mcf数据库中却没有temp表了，这是因为断开连接时sql自动删除了temp表

 

9.表变量：关于表变量的定义和相关操作如下：

DECLARE @a TABLE (id INT, NAME NVARCHAR)

INSERT INTO @a( id, [NAME])VALUES(1,'abc')

SELECT * FROM @a

(这里有关于临时表和表变量的对比：

特性	表变量	临时表
作用域	当前批处理	当前会话，嵌套存储过程，全局：所有会话
使用场景	自定义函数，存储过程，批处理	自定义函数，存储过程，批处理
创建方式	DECLARE statement only.只能通过DECLEARE语句创建	CREATE TABLE 语句 SELECT INTO 语句.
表名长度	最多128字节	最多116字节
列类型	可以使用自定义数据类型 可以使用XML集合	自定义数据类型和XML集合必须在TempDb内定义
Collation	字符串排序规则继承自当前数据库	字符串排序规则继承自TempDb数据库
索引	索引必须在表定义时建立	索引可以在表创建后建立
约束	PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用，但必须在表建立时声明	PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用，可以在任何时后添加，但不能有外键约束
表建立后使用DDL (索引，列)	不允许	允许.
数据插入方式	INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC).	INSERT 语句, 包括 INSERT/EXEC. SELECT INTO 语句.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	不支持SET IDENTITY_INSERT语句	支持SET IDENTITY_INSERT语句
Truncate table	不允许	允许
析构方式	批处理结束后自动析构	显式调用 DROP TABLE 语句. 当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)
事务	只会在更新表的时候有事务，持续时间比临时表短	正常的事务长度，比表变量长
存储过程重编译	否	会导致重编译
回滚	不会被回滚影响	会被回滚影响
统计数据	不创建统计数据，所以所有的估计行数都为1,所以生成执行计划会不精准	创建统计数据，通过实际的行数生成执行计划。
作为参数传入存储过程	仅仅在SQL Server2008, 并且必须预定义 user-defined table type.	不允许
显式命名对象 (索引, 约束).	不允许	允许，但是要注意多用户的问题
动态SQL	必须在动态SQL中定义表变量	可以在调用动态SQL之前定义临时表

)

10.自定义函数：

用户定义自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回

用户自定义函数的类型:

标量函数:返回一个标量值

表格值函数{内联表格值函数、多表格值函数}:返回行集（即返回多个值）

如下

Create function tabcmessalot (@code varchar(10))

Returns @ctable table(code varchar(10) null,cname varchar(100) null)

As

Begin

Insert @ctable

Select ccode,explain from cmessage

Where scode like @code

return

End

Select * from tabcmessalot(‘s0003’)

 

 

3：经常遇到dictionary，hashtable，namevaluecollection的情况

具体来说有以下区别：

C#基础之 NameValueCollection 与 HashTable 以及 Dictionary 的区别

1.NameValueCollection 和 HashTable的定义

散列表（ Hash table，也叫哈希表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。

HashTable 表的优点：HashTable是System.Collections命名空间提供的一个容器，HashTable中的key/value均为object类型，所以HashTable可以支持任何类型的key/value键/值对。优点就在于其索引的方式，速度非常快。

NameValueCollection类集合是基于 NameObjectCollectionBase 类。但与 NameObjectCollectionBase 不同，该类在一个键下存储多个字符串值（就是键相同，值就连接起来如下例子）。该类可用于标头、查询字符串和窗体数据。每个元素都是一个键/值对。NameValueCollection 的容量是 NameValueCollection 可以保存的元素数。
　　NameValueCollection 的默认初始容量为零。随着向 NameValueCollection 中添加元素，容量通过重新分配按需自动增加。
　　如下例子：
　　NameValueCollection myCol = new NameValueCollection();
　　myCol.Add("red", "rojo");//如果键值red相同结果合并 rojo,rouge
　　myCol.Add("green", "verde");
　　myCol.Add("blue", "azul");
　　myCol.Add("red", "rouge");

2.NameValueCollection与Hashtable的区别

1. 首先前者不是以键值对存放，故这种类型的变量可以同时存放多个Name，当存放多个Name时,该Name的值不变，但是他的Value将会被追加到前一 个Value值得后面同时以逗号隔开，而同一个Hashtable里面绝对允许出现两个相同的键值，遵循数据结构中的哈希表。

2. 在存取方面，在NameValueCollection中，可用GetKey(i)获取指定索引的Name，Get(i)获取指定的索 引出值，而Hashtable将每个键值对存放到DictionaryEntry中，故在取值操作的时候需要用DictionaryEntry。

3. 在输出顺序上，前者是按存放顺序输出，而后者的顺旬不是。下面是如何对他们进行简单操作：

　　a.引用区别
　　hashtable:using System.Collections;
　　NameValueCollection:using System.Collections.Specialized;

　　b.键是否重复
　　NameValueCollection:允许重复.
　　HashTable是键-值集合,但键不能出现重复.
　　Hashtable ht = new Hashtable();
　　ht.Add("key","value");
　　ht.Add("key", "value1"); //出错
　　ht["key"] = "value1"; //正确

　　c.初始化的区别
　　初始化NameValueCollection需引用using System.Collections.Specialized;

3.Hashtable与Dictionary的区别

HashTable是没有强类型的字典，Dictionary是.NET 2.0后支持泛型的字典，可以避免拆箱装箱。