数据库程序设计－SQL Server 2000数据库程序设计

摘自：http://blog.csdn.net/bluebluet/archive/2007/09/06/1774053.aspx
0001可以使用SQLServer管理两种类型的数据库：联机事务处理（OLTP，Online Transaction Processing）数据库
和联机分析处理（OLAP，Online Analytical Processing）数据库。
----------------------------------------------------------------------------------------------------
0002：
    驱动程序和设备驱动程序是两个不同的概念，设备驱动程序更接近硬件。
----------------------------------------------------------------------------------------------------
0003：
    当仅仅指定计算机名称时，使用的默认实例。必须以指定computer name/instance name的方式连接到指定的
实例上。
----------------------------------------------------------------------------------------------------
0004：
    系统数据库：
    【master】：控制用户数据库和SQL Server的整体运行情况。
    【model 】：为新用户数据库提供模版或原型。
    【tempdb】：为临时表和其他临时工作存储需要提供一个存储区域。
    【msdb  】：为调度信息和作业历史提供一个存储区域。
----------------------------------------------------------------------------------------------------
0005：
    完整的数据库对象名称包括四个部分：服务器名称，数据库名称，所有者名称，对象名称。
----------------------------------------------------------------------------------------------------
0006：
    每一个数据库都包含了一组用于存储有关特定数据库的元数据系统表。这组系统表就是数据库目录，它包含了
数据库中所有对象的定义以及权限。
----------------------------------------------------------------------------------------------------
0007：
    当编写从系统表中检索元数据的应用程序时，应该使用系统存储过程、系统函数或系统提供的信息架构视图。
可以像查询任何其他数据库表一样，以同样的方式查询系统表，检索有关系统的信息。但是，不应该编写直接查询
系统表的脚本，原因在于，如果在未来的产品版本中系统表发生变化，脚本可能失效。
    不应该直接修改系统表中的信息。
----------------------------------------------------------------------------------------------------
0008：
    系统存储过程sp_help [object_name]：提供指定数据库对象的信息。例如：EXEC sp_help [Tbl]。
----------------------------------------------------------------------------------------------------
0009：
    系统存储过程sp_helpdb [database_name]：提供指定数据库的信息。例如：EXEC sp_helpdb [Test]。注意，
在任何会话中执行都可以运行。
----------------------------------------------------------------------------------------------------
0010：
    系统函数DB_ID：返回数据库ID。例如：SELECT DB_ID('Test')。
----------------------------------------------------------------------------------------------------
0011：
    系统函数COL_LENGTH：返回列的宽度。例如：SELECT COL_LENGTH('Tbl','ID')。
----------------------------------------------------------------------------------------------------
0012：
    信息模式视图[INFORMATION_SCHEMA].[TABLES]：数据库中表的列表。例如：
SELECT * FROM [INFORMATION_SCHEMA].[TABLES]。注意，在哪个数据库中运行就返回哪个数据库表的列表。
----------------------------------------------------------------------------------------------------
0013：
    信息模式视图[INFORMATION_SCHEMA].[COLUMNS]：数据库中所定义的列的信息。例如：
SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS]。注意，在哪个数据库中运行就返回哪个数据库所有表列的列表。
----------------------------------------------------------------------------------------------------
0014：
    SQL Server使用两层安全机制确认用户的有效性：登陆验证、数据库用户和角色的权限确认。
----------------------------------------------------------------------------------------------------
0015：
    当创建新的数据库时，它是model数据库的一个副本。model数据库中的任何选项或设置都将复制到新建数据库
中。
----------------------------------------------------------------------------------------------------
0016：
    每当创建、修改或删除数据库时，都应该备份master数据库。因为master数据库记录着数据库的所有信息。
----------------------------------------------------------------------------------------------------
0017：
    可以使用文件组的技术将经常查询和经常修改的表放在不同的文件中。不同的文件可以分布在不同的磁盘上。
----------------------------------------------------------------------------------------------------
0018：
    收缩数据库：SQL Server不会把文件收缩到比数据所占空间更小，也不会把文件大小收缩到超过CREATE DATABASE
语句中SIZE参数指定的大小。例如：DBCC SHRINKDATABASE('Test','TRUNCATEONLY')。TRUNCATEONLY参数的意思是：
释放数据文件中的任何未用空间给操作系统。
----------------------------------------------------------------------------------------------------
0019：
    分离数据库必须确定没人在使用此数据库。分离数据库要确保在数据库里没有未完成的事务。一旦数据库被分
离后，将会删除master数据库里的sysdatabases表中的登录信息，从SQL Server的角度来看，相当于已经删除了数
据库。已分离的数据库文件仍然存在，而操作系统认为是已关闭的文件，所以可以复制、移动这些文件。
----------------------------------------------------------------------------------------------------
0020：
    SQL Server以每块8KB的连续磁盘空间进行数据存储和读/写。这意味着SQL Server数据库每兆字节有128页。
----------------------------------------------------------------------------------------------------
0021：
    行不能跨页。因此，一行内最多包含的数据量是8060字节（减去行首所占据的空间后）。
----------------------------------------------------------------------------------------------------
0022：
    获得数据库服务器的当前时间：SELECT current_timestamp。
----------------------------------------------------------------------------------------------------
0023：
    使用可变长度数据类型能显著的节省空间，因此而损失的性能是很小的。
----------------------------------------------------------------------------------------------------
0024：
    创建用户定义数据类型：例如，EXEC sp_addtype 'City','NVARCHAR(20)',NULL。
----------------------------------------------------------------------------------------------------
0025：
    查询某一个表所包含的列：SELECT * FROM [syscolumns] WHERE [id] = object_id('Tbl')。
----------------------------------------------------------------------------------------------------
0026：
    用户定义数据类型在创建表时被解析，只要当前一个或多个表正在使用它，就不能删除或更改用户定义数据类
型。一旦声明，用户定义数据类型是静态和不可变的。
----------------------------------------------------------------------------------------------------
0027：
    数据行由4字节的头部和数据库部分组成。4字节的行头包含了数据行中有关列的信息。
----------------------------------------------------------------------------------------------------
0028：
    由于text、ntext和image数据类型通常很大，所以SQL Server在行的外面存储它们。
----------------------------------------------------------------------------------------------------
0029：
    为表增加列：例如，
IF NOT EXISTS(SELECT * FROM [syscolumns] WHERE [id] = object_id('Tbl') AND [name] = 'ProvinceName')
 ALTER TABLE Tbl ADD ProvinceName NVARCHAR(30) NULL
----------------------------------------------------------------------------------------------------
0030：
    系统函数SCOPE_IDENTITY：返回同一范围中插入到标识列的最新的标识值。
----------------------------------------------------------------------------------------------------
0031：
    标识列并不能保证唯一性。
----------------------------------------------------------------------------------------------------
0032：
    在数据库规划过程中最重要的一步是确定最好方法用于强制数据完整性。数据完整性是指存储在数据库中的数
据的一致性和准确性。
    数据完整性应该在数据库这一层实现，这样可以防止绕过前端应用程序写入数据的情况。
----------------------------------------------------------------------------------------------------
0033：
    增加约束：ALTER TABLE [dbo].[Tbl] ADD CONSTRAINT [CK_Tbl_ID] CHECK ([ID] > 5 and [ID] < 10)
----------------------------------------------------------------------------------------------------
0034：
    在向表中增加一个约束的时候，不会验证这个约束的正确性。
----------------------------------------------------------------------------------------------------
0035：
    增加一个唯一性约束：ALTER TABLE [dbo].[Tbl] ADD CONSTRAINT [U_Tbl_ID] UNIQUE  NONCLUSTERED ([ID])
----------------------------------------------------------------------------------------------------
0036：
    在声明约束时，可以指定索引属性为聚集（CLUSTERED）或非聚集（NOCLUSTERED）。每个表最多只可以有一个
聚集索引。默认情况下，主键建立索引的时候会被设置为聚集索引。
----------------------------------------------------------------------------------------------------
0037：
    默认值和规则是一些对象，这些对象可以绑定到一列、多列或用户定义的数据类型上。因此，只需要定义它们
一次就可以重复使用。默认值和规则的缺点是它们不是ANSI兼容的。例如：
    CREATE RULE [R_Tbl_ID] AS @ID > 10 AND @ID < 20
    GO
    EXEC sp_bindrule [R_Tbl_ID],'Tbl.ID'-- 取消绑定sp_unbindrule
----------------------------------------------------------------------------------------------------
0038：
    Transact-SQL语句的种类：数据定义语言（DDL，Data Definition Language）、数据控制语言（DCL，Data
Control Language）、数据操作语言（DML，Data Manipulation Language）。
----------------------------------------------------------------------------------------------------
0039：
    GO 命令用于向实用工具描述Transact－SQL语句的批处理结束，它不是真正的Transact－SQL语句。Transact－SQL
语句不能和 GO 命令同处一行。
    局部变量的作用范围局限于一个批处理内，并且在 GO 命令后不能再引用这个变量。
----------------------------------------------------------------------------------------------------
0040：
    全局变量：
    SELECT @@CONNECTIONS--服务器上次启动后的连接或试图连接的数目
    SELECT @@DATEFIRST--返回将某一天设置为一周的第一天，7代表周日
    SELECT @@LANGUAGE--返回当前用户的语言
    SELECT @@SERVERNAME--返回本地服务器的名称
    SELECT @@VERSION--返回版本信息
    SELECT @@ROWCOUNT--返回受最后一条语句影响的行数
----------------------------------------------------------------------------------------------------
0041：
    日期和时间函数：
    SELECT DATEADD(Yy,3,GetDate())--在当前时间上加3年
    SELECT DATEDIFF(Yy,'2010-10-1','2020-10-1')--返回两个时间的年差
    SELECT DATENAME(Yy,GetDate())--获得当前日期的年部分，返回的是字符串
    SELECT DATEPART(Yy,GetDate())--获得当前日期的年部分，返回的是整数
    SELECT DAY(GetDate())--获得号
    SELECT MONTH(GetDate())--获得月
    SELECT YEAR(GetDate())--获得年
    缩写：
    Year            Yy
    Quarter         Qq
    Month           Mm
    Dayofyear       Dy
    Day             Dd
    Week            Wk
    weekday         Dw
    Hour            Hh
    minute          Mi
    second          Ss
    millisecond     Ms
    例如：获得星期几SELECT DATENAME(Dw,GetDate())
----------------------------------------------------------------------------------------------------
0042：
    字符串函数：
    SELECT CHARINDEX('a','bca')--返回指定模式出现的位置
    SELECT LEFT('abcdef',2)--返回字符串左边指定长度的部分
    SELECT LEN('abc')--返回字符串的长度
    SELECT LOWER('ABC')--返回小写格式
    SELECT LTRIM(' A BC')--去掉字符串左边的空格
    SELECT PATINDEX('%ab%','asdscdswedewabdasda')--返回指定模式出现的位置
    SELECT REPLACE('asdasdansjudashjdas','a','M')--将字符串中的a替换为M
    SELECT REPLICATE('a',5)--以指定的次数重复字符
    SELECT REVERSE('abc')--翻转字符串
    SELECT STUFF('S00000001',2,7,'HHHHHHH')--删除指定位置开始，指定长度的字符，替换为后面的字符
    SELECT SUBSTRING('abcdefg',2,3)--截取从指定位置开始，指定长度的字符
    SELECT UPPER('abc')--返回大写格式
----------------------------------------------------------------------------------------------------
0043：
    系统函数：
    SELECT COL_LENGTH('Tbl','Age')--返回列的宽度
    SELECT HOST_NAME()--返回工作站的名称
    SELECT ISDATE('2001')--检查是不是时间格式，真返回1，假返回0
    SELECT ISNULL(null,'空')--判断是不是为空，为空则替换
    SELECT ISNUMERIC('3')--判断是不是数字格式，真返回1，假返回0
    SELECT OBJECT_ID('Tbl')--返回数据库对象的ID
----------------------------------------------------------------------------------------------------
0044：
    CASE的用法：
    SELECT
      CASE WHEN [ID] > 5 THEN [ID]-5
      ELSE [ID]
      END,
      [Age]
    FROM [Tbl]
----------------------------------------------------------------------------------------------------
0045：
    osql：
    ●使用QUIT或EXIT语句退出ospl 命令行工具。
    ● osql语句是区分大小写的。
    ●RESET清除已经输入的所有语句。
    ●!!command调用操作系统的 命令。
    ●Ctrl＋C结束查询，而不退出 osql。
    ●打开数据库连接： osql -Ssmartkernel-PC/SQLSERVER2000 -Usa -Psa -dTest -i"C:/sql.Txt"
----------------------------------------------------------------------------------------------------
0046：
    LIKE的使用：
    ●LIKE '[CK]%'--每个以C或K开头的名称
    ●LIKE '[A-D]ing'--每个以A到D任何字母开头，以ing结尾的情况
    ●LIKE 'M[^c]%'--每个以字母M开头，第二个字母不是c的情况
----------------------------------------------------------------------------------------------------
0047：
    BETWEEN AND的使用：注意，结果集包括边界值。
----------------------------------------------------------------------------------------------------
0048：
    在多列的情况下，在列名前加逗号，第一列除外。这种样式容易注释和粘贴整行。
----------------------------------------------------------------------------------------------------
0049：
    只有在使用ORDER BY子句时才能使用WITH TIES子句。
----------------------------------------------------------------------------------------------------
0050：
    COMPUTE产生额外数据汇总行：SELECT * FROM [Tbl] ORDER BY [ID] COMPUTE SUM([ID]) BY [ID]
----------------------------------------------------------------------------------------------------
0051：
    一般情况下，包含子查询的查询语句也可以写成联接查询语句。联接查询的查询性能和子查询的查询性能比较
相似。通常，查询优化器可以优化子查询语句，使子查询语句可以使用与其相当的联接查询语句所使用的样本执行
计划。
----------------------------------------------------------------------------------------------------
0052：
    子查询要使用括号括起来。
----------------------------------------------------------------------------------------------------
0053：
    子查询中也可以再包含子查询，嵌套最多可以达32层。
----------------------------------------------------------------------------------------------------
0054：
    把子查询用做派生表：
    可以用子查询产生一个派生表，用于代替FROM子句中的表。派生表是FROM子句中子查询的一个特殊用法，用一
个别名或用户自定义的名字来引用这个派生表。FROM子句中的子查询将返回一个结果集，这个结果集所形成的表将
用于外层SELECT语句。
    SELECT * FROM
    (
     SELECT * FROM [Tbl]
    ) AS TblA
----------------------------------------------------------------------------------------------------
0055：
    相关子查询：
    相关子查询可用做动态表达式，这个表达式的值随着外层查询的每一行的变化而变化。查询处理器为外部查询
的每一行计算子查询的值，每次计算一行，而这个子查询每次都会作为该行的一个表达式来取值并返回给外层查询。
相关子查询是动态执行的子查询和外部查询行的一个非常有效的联接。
    创建相关子查询时，将反复执行内部子查询，外部查询有多少行，内部子查询就执行多少次。
    SELECT * FROM [orders] AS A
    WHERE 20 <
    (
     SELECT [quantity] FROM [order details] AS B WHERE A.[orderid] = B.[orderid] AND B.productid = 23
    )
----------------------------------------------------------------------------------------------------
0056：
    两个结构完全相同的表，保存相同的数据，列出在表A中，而不在表B中的数据。
    SELECT * FROM [TblA]
    WHERE NOT EXISTS
    (
     SELECT * FROM [TblB] WHERE [TblA].[ID] = [TblB].[ID]
    )
----------------------------------------------------------------------------------------------------
0057：
    使用INSERT...SELECT比使用多个单行的INSERT语句效率要高的多。
    INSERT [Tbl]
    SELECT * FROM [Tbl]
----------------------------------------------------------------------------------------------------
0058：
    使用事务：
    BEGIN TRANSACTION
     UPDATE...

     IF @@ERROR <> 0
     BEGIN
      RAISERROR('事务不能正确执行！',16,-1)
      ROLLBACK TRANSACTION
     END

     INSERT...

     IF @@ERROR <> 0
     BEGIN
      RAISERROR('事务不能正确执行！',16,-1)
      ROLLBACK TRANSACTION
     END

    COMMIT TRANSACTION
----------------------------------------------------------------------------------------------------
0059：
    可以通过用SELECT INTO语句把任何查询结果集放置到一个新表中。
    SELECT * INTO #TblTemp FROM [Tbl] WHERE [Age] = 'a'
    SELECT * FROM #TblTemp
----------------------------------------------------------------------------------------------------
0060：
    快速删除表中的所有数据，并且不写入日志：TRUNCATE TABLE [Tbl]。虽然不写入日志，但是也可以使用事务
恢复。
    如果表中有一个IDENTITY列，那么TRUNCATE TABLE语句会重新设置种子值。
----------------------------------------------------------------------------------------------------
0061：
    判断数据库是否启用全文检索：SELECT DATABASEPROPERTY('Test','IsFullTextEnable')
----------------------------------------------------------------------------------------------------
0062：
    最好在高选择性的列（列或组合列上的大部分数据是惟一的）上创建索引。
----------------------------------------------------------------------------------------------------
0063：
    在使用视图修改数据时：
    ●不能影响多于一个的基表。可以修改由两个或多个表派生而来的视图，但是每次更新或修改都只能影响一个
表。
    ●不能对某些列进行该操作。SQL Server不允许改变计算结果的列。
    ●如果更改影响了在视图中没有引用的列，将会发生错误。
----------------------------------------------------------------------------------------------------
0064：
    尽量多的使用存储过程和视图。这样可以提高系统的可扩展性。因为更新视图和存储过程，总比从新编译代码
来的容易。代码中固定的SQL语句，可以存储在XML文件中。
----------------------------------------------------------------------------------------------------
0065：
    什么时候直接访问基表都是危险的，因为基表的改变往往是不可避免的。
----------------------------------------------------------------------------------------------------
0066：
    对于从不同系统中取数的情况，应该使用推模式，而不是拉模式。这样能有效的提高系统的可扩展性。
----------------------------------------------------------------------------------------------------
0067：
    如果存储过程创建了一个本地临时表，该临时表只在存储过程执行期间存在，在存储过程执行完毕后消失。
----------------------------------------------------------------------------------------------------
0068：
    嵌套存储过程：
    ●存储过程可以嵌套32级。
    ●当前嵌套的级数存储在@@nestlevel系统函数中。
    ●如果一个存储过程调用了另一个，第二个存储过程可以访问在第一个存储过程中创建的所有对象，包括临时
表。
    ●嵌套存储过程可以递归。
----------------------------------------------------------------------------------------------------
0069：
    创建存储过程的指导原则：
    ●设计每个存储过程以实现单一的任务。
    ●尽量不要使用临时存储过程，以避免在tempdb上对系统表的争夺，这会明显地影响性能。
    ●在存储过程中使用sp_executesql替代EXECUTE语句来动态执行一个字符串命令。sp_executesql的效率更高。
    ●如果不希望用户查看存储过程的文本，必须通过WITH ENCRYPTION选项来加密存储过程。
    ●在每个存储过程的开始处检查所有的输入参数，以便尽早捕获漏掉的和无效的值。
    ●在开始事务处理前，执行任务和业务逻辑检查及数据有效性检查，保持事务的简短。
----------------------------------------------------------------------------------------------------
0070：
    在INSERT语句中执行存储过程：INSERT INTO Tbl EXEC PROC1
----------------------------------------------------------------------------------------------------
0071：
    RAISERROR语句能将错误信息写到SQL Server错误日志和Windows 2000应用程序日志中。
----------------------------------------------------------------------------------------------------
0072：
    自定义函数：
    CREATE FUNCTION [dbo].[FUN1]()
    RETURNS TABLE
    AS
    RETURN
    (
     SELECT * FROM Tbl
    )
   
    SELECT * FROM FUN1()
----------------------------------------------------------------------------------------------------
0073：
    触发器是一个在修改指定表中的数据时执行的存储过程。经常通过创建触发器来强制实现不同表中的逻辑相关
数据的引用完整性或一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以此确保数据
的完整性。
    触发器是一种特殊的存储过程，它在遇到试图更改其所保护的表中的数据时自动执行。触发器与指定的表相关
联。
    与标准的存储过程不同，触发器不能直接被调用，也不能传递或接受参数。
    冗余数据和派生数据通常要使用触发器。
    大多数触发器是后反应的，而约束和INSTEAD OF触发器是前反应的。
    如果在触发器表上存在约束，则触发器执行之前检查约束，如果违反约束，则触发器不执行。
    SQL Server2000允许在一个表上嵌套多个触发器。一个表可以定义多个触发器。当一个表上有多个触发器时，
表的所有者可以使用sp_settriggerorder系统存储过程来指定最先和最后触发的触发器，但不能设置其余触发器的
触发顺序。

    DELETE触发器：
    CREATE TRIGGER Tbl_Delete ON [Tbl]
    FOR DELETE
    AS

    IF (SELECT COUNT(*) FROM DELETED) > 1
     BEGIN
      RAISERROR('一次只能删除一行数据！',16,1)
      ROLLBACK TRANSACTION
     END
    GO

    INSERT触发器：
    CREATE TRIGGER Tbl_Insert ON [Tbl]
    FOR INSERT
    AS

    DELETE [Tbl] WHERE [Tbl].[ID] IN
    (
     SELECT [ID] FROM INSERTED
    )
    GO

    UPDATE触发器：
    CREATE TRIGGER Tbl_Update ON [Tbl]
    FOR UPDATE
    AS

    IF UPDATE([ID])
     BEGIN
      RAISERROR('不能更新ID',10,1)
      ROLLBACK TRANSACTION
     END
    GO

    INSTEAD OF触发器：
    CREATE TRIGGER Tbl_InsteadOfDelete ON [Tbl]
    INSTEAD OF DELETE
    AS

    -- 取消Delete操作
    GO
----------------------------------------------------------------------------------------------------
0074：
    提供程序名称：
    SQL Server                                      N'SQLOLEDB'
    Microsoft OLE DB Provider for Access(Jet)       'Microsoft.Jet.OLEDB.4.0'
    Microsoft OLE DB Provider for Oracle            'MSDAORA'
    OLE DB Provider for ODBC                        'MSDASQL'
----------------------------------------------------------------------------------------------------
0075：
    打开远程数据库：

    打开SQL Server：
    SELECT A.* FROM OPENROWSET
    (
     'SQLOLEDB',
     'smartkernel-PC/SQLSERVER2000';--注意是分号
     'sa';--注意是分号
     'sa',
     'SELECT TOP 10 * FROM pubs.[dbo].[authors]'
    )AS A

    SELECT * FROM
    OPENQUERY
    (
     [smartkernel-PC/SQLSERVER2000],
     'SELECT TOP 10 * FROM pubs.[dbo].[authors]'
    )

    DELETE FROM
    OPENQUERY
    (
     [smartkernel-PC/SQLSERVER2000],
     'pubs.[dbo].[authors]'
    )
    WHERE [ID] = '1'

    SELECT * FROM [smartkernel-PC/SQLSERVER2000].[pubs].[dbo].[authors]--对于注册到本地的服务器，可以直接使用

    打开Access：
    SELECT A.* FROM OPENROWSET
    (
     'Microsoft.Jet.OLEDB.4.0',
     'C:/db1.mdb';--注意是分号
     'sa';--注意是分号
     'sa',
     'Tbl'
    )AS A
----------------------------------------------------------------------------------------------------
0076：
    分布式事务：
    BEGIN DISTRIBUTED TRANSACTION
     EXEC ...
     EXEC ...
    COMMIT TRANSACTION
----------------------------------------------------------------------------------------------------
0077：
    分区视图允许把数量很大的表中的数据水平的分成几个小的成员表，每个成员表有与原始表相同的格式，但仅
包括原始表的部分行。包含成员表的服务器称为成员服务器，每个成员服务器包含一个成员表和一个分布式的分区
视图。
    应用程序通过引用任何服务器上的分区视图而得到的结果和假设的每个服务器上存在一个原始表的完整副本中
得到的结果是相同的。
----------------------------------------------------------------------------------------------------
0078：
    事务使用锁定技术来阻止其他用户改变或读取尚未完成的事务中的数据。
----------------------------------------------------------------------------------------------------
0079：
    每一个INSERT，DELETE，UPDATE操作都作为一个隐性事务执行。
----------------------------------------------------------------------------------------------------
0080：
    query           查询
    aggregate       聚合
    alias           别名
    National        国家
    Institute       学会
    packaging       打包
    article         项目
    atomicity       原子性
    batch           批处理
    utility         实用工具
    business rules  业务规则
    set             集合
    constraint      约束
    filter          筛选