SQL Server 2000 读书笔记

SQL Server 2000 读书笔记

                                   孟培远 2005.7---2005.8

数据完整性：

实体完整性---表中数据的唯一性

区域完整性---字段数据范围的完整性

参考完整性----表与表之间关联的完整性

使用者定义的完整性----用户自己定义的完整性。如：客户欠6个月的钱，下次再下定单的时候就不卖给他。

 

数据库分离：使用SQL语句分离为EXEC sp_detach_db ‘销售数据库’ß指明欲分离的数据库名称即可。

分离后想再用该数据库，则需要附加该数据库，具体SQL语句为：

CREATE  DATABASE 销售数据库

ON PRIMARY

（FILENAME=’C:/SQLTEST/销售数据文件_1。MDF’）

FOR  ATTACH

或者使用sp_attach_db系统存储过程来附加数据库：

sp_attach_db  销售数据库

‘C:/SQLTEST/销售数据文件_1.MDF’

 

UNIQUE 和Primary key的差异

1、  UNIQUE允许输入NULL值（最多为一个），而Primary key不允许；

2、  一个数据表中可以定义多个UNIQUE条件约束，但只能定义一个Primary key条件约束。

 

CHECK约束可用来限制字段值是否在所允许的范围内。

例如：

CREATE TABLE 客户02

(

       客户编号 int IDENTITY  NULL  UNIQUE,

       身份证号 char[10] NOT NULL  UNIQUE,

       年龄  int  CHECK (年龄>0) DEFAULT 25

)

 

建立计算列：

CREATE TABLE 估价

(

       编号  int  IDENTITY,               //自动编号

       单价  numeric(5,1),

       数量  int,

总价  AS 单价*数量

)

insert 估价 values(21.5,8)

insert 估价 values(12,3)

delete from 估价 where 编号=5 or 编号=6

drop table 估价

 

创建约束字段：

Create table 客户

{

客户编号  int  IDENTITY  Primary Key,------------------------IDENTITY自动产生编号

身份证号  char 18  NOT NULL UNIQUE,

地址      char(50),

电话      char (12),

杂志编号  int  REFERENCES 杂志种类（杂志编号）,

订单编号  int  NOT NULL,

FOREIGN Key(杂志编号，订户编号)

                     REFERENCES  杂志订户（杂志编号，订户编号），

Check(地址 is NOT NULL  OR 电话  is  NOT NULL)

}

修改数据表：

例如：

ALTER TABLE 客户A

ADD

类别编号 int

DEFAULT 1 WITH VALUES

CONSTRAINT  FK_类型编号

FOREIGN KEY

       REFERENCES  客户类别（类别编号）

 

删除约束字段

在表中建立了一个叫PK_订购项目A的PRIMARY KEY约束，现在将它删除掉：

ALTER TABAL订购项目A

       DROP CONSTRAINT PK_订购项目A

下面删除订购项目A的两个字段

ALTER TABLE 订购项目A

       DROP COLUMN 定单编号，项目编号

 

激活/关闭约束

ALTER TABLE table

{ CHECK | NOCHECK} CONSTRAINT

       {ALL | constrant_name{,…….n}}

 

临时数据表以#或##开头，用户断开连接后表被自动删除

 

数据表中字段的种类：

Primary key,  Foreign Key,  NULL,  NOT NULL,  DEFAULT,  UNIQUE,  CHECK

用查询结果建立新数据表

Select * into  newtablename  from table_source where 条件

 

当记录中有重复的时候可以采用DISTINCT来获取单独的数据。

 

TOP  N 为显示前N个数据

TOP  N  30  PERSENT * FROM 表名

表示显示N的前30%的数据

 

ORDER BY 按什么方式排序显示结果

ASC升DESC降

 

JOIN

SELECT  A,B,C  FROM  T1  JOIN  T2  ON  T1.NO=T2.NO

JOIN 的类型有多种：

INNER（默认）

LEFT（以左边为条件，如果右边没有符合的条件，则以NULL）

例如：SELECT 旗.产品名称AS旗旗公司产品名称，旗.价格，标.成品名称AS标标公司产品名称，标.价格

FROM 旗旗公司 AS 旗 LEFT JOIN 标标公司AS标

ON旗.产品名称=标产品名称

 

RIGHT（以右边为条件，如果左边没有符合的条件，则以NULL）

FULL（左边右边都显示，如果没有则以NULL）

CROSS（将两个表的数据进行比较，全部显示出来）

有时需要自己JOIN自己

例如：

SELECT 员工.姓名，员工.职位，长官.姓名AS主管

FROM 员工 LEFT JOIN 员工AS 长官

ON 员工.主管编号=主管.编号

	姓名	职位	主管
1	陈圆圆	主任	刘敏敏
2	刘敏敏	经理	NULL

 

GROUP BY 按什么分组显示

 

UNION把两个查询结果合并到一个表中显示出来

 

查询时Group的使用，例如：

Name	Date	Quantity
A	2001-1-1	2
B	2001-10-10	6
A	2003-4-20	8
B	2004-5-21	9
A	2005-8-18	10

 

要求查询上面表格中A和B的数量分别为多少？

Select name  AS名称,Sum(Quantity)数量  Form  table  Group  By  name

结果为：

名称	数量
A	20
B	15

 

GROUP BY后面如果有WITH CUBE则把各个组进行汇总计算后显示结果

例如：

Name	Date	Bookname	Quantity
A	2001-1-1	X1	2
B	2001-10-10	Y1	6
A	2003-4-20	X1	8
B	2004-5-21	Y2	9
A	2005-8-18	X2	10

 

查询：select name  AS出版社名称,bookname AS 书名,sum(quantity) AS 数量 from table group by name,bookname with cube

结果如下：

出版社名称	书名	数量
A	X1	10
A	X2	10
A	NULL	20
B	Y1	6
B	Y2	9
B	NULL	15

 

 

查询时改变要显示的数据：（1代表“男”，0代表“女”）

Name	Sex
小华	1
小刘	0
小王	1

要求查询时如果是1则显示“男”，否则显示“女”

Select name  AS 姓名,case  sex  when  0  then “女” else  “男”  End  AS 性别  From  table

 

比较清单有：

IN，ALL，ANY（SOME）结果为TRUE或FALSE

测试存在用EXISTS结果返回TRUE，FALSE

 

索引分为聚集索引和非聚集索引

聚焦索引数据的存放会依照该索引的顺序来存放

非聚焦索引数据的存放不会依照索引的顺序来存放

因为非聚焦索引不影响数据排列顺序，则可以设置多个非聚焦索引，而聚焦索引则最多只能设置一个，因为实际数据只能有一种排列顺序。

(Unique index)索引值是否唯一：如果索引值为唯一，则成为唯一索引

多个字段做为索引称为复合索引（Composite index）

Create  [UNIQUE]  -----指定唯一

[CLUSTERED | NONCLUSTERED]  ------指定聚集/非聚集

INDEX  index_name

ON  table_name

(column [ASC|DESC][,…..n])      -----可指定排序方式（默认为升序ASC）

[WITH  [PAD_INDEX]                     --------索引页预留空间

        [,FILLFACTOR=X]                     ------填充因子

              [,IGNORE_DUP_KEY]                ------忽略重复值

        [,DROP_EXISTING]                  ------删除现有的索引

              [,STATISTICS_NORECOMPUTE]]      --------不重新计算统计数据

              [ON  filegroup]                --------指定文件组

例如：

CREATE  TABLE  KKK

(

ProductID  smallint  not null  primary key,

ProductName   char(30),

Price   smallmoney,

Manufacturer   char(30)

)          ------创建表完毕

//创建索引

CREATE        UNIQUE        NONCLUSTERED        INDEX          indexname

ON               KKK       (Price   DESC)

WITH     PAD_INDEX, FILLFACTOR=30,IGNORE_DUP_KEY

 

//删除索引

DROP  INDEX   table.index_name

 

//修改索引

使用DROP_EXISTING修改索引

如果要修改索引，只要在CREATE    INDEX语句的最后加上DROP_EXISTING即可（如果存在该索引，则修改之，否则会发生错误）

使用DBCC  DBREINDEX重建索引

如果只想重建索引（而不修改索引的相关设置），那么使用DBCC  DBREINDEX语句比较方便，而且使用时也可指定重建的填充因子。

DBCC DBREINDEX（’database_name.owner.table_name’,  index_name,   fillfactor）

 

                                    数据库  数据库所有者 表名  重建的索引名 设置新的填充因子

[WITH  NO_INFOMSGS]   -------有此项，则重建索引不会显示出信息

 

存储过程

系统存储过程都是以 sp_开头

扩展存储过程通常以xp_开头

 

EXECUTE 执行存储过程或SQL字符串（EXEC）

EXEC  存储过程名   参数1   参数2   参数3……

 

变量的声明：

DECLARE  @A  int -------------- 生命了一个为int 类型的A 变量。

 

建立存储过程

使用CREATE  PROC或者CREATE  PROCEDURE + 过程名 + AS +SQL语句

CREATE  PROC  MyProcl

AS  SELECT * FROM 标标公司  WHERE 价格 >5

GO

EXEC MyProcl

结果为：

产品名称     价格

--------------    -----------

钢笔          90.0000

荧光笔        40.0000

 

EXEC  lookup  ‘杨小雄’，@地址 OUTPUT

OUTPUT表示参数的值是可以返回的

 

一个存储过程为一个批，因此在查询分析器中遇到GO时，表示存储过程的定义已结束.

在存储过程中,有些创建对象的语句不可使用

       CREATE  DEFAULT       CREATE  TRIGGER

CREATE  PROCEDURE    CREATE  VIEW

CREATE  RULE

 

临时存储过程分为局部和全局

局部要以#开头

全局要以##开头

 

修改存储过程

ALTER PROC

删除存储过程

DROP PROC

 

建立函数                   修改函数                 删除函数

CREATE  FUNCTION       ALTER  FUNCTION       DROP  FUNCTION

 

返回单个数据的函数和一般函数一样

返回大量数据集的函数如下例：

CREATE FUNCTION 依售价查询书籍

（@由 money,  @到money）

RETURNS TABLE

RETURN  （SELECT 书籍编号，书籍名称，单价 FROM 书籍 WHERE 单价>=@由AND单价<=@到）

GO

SELECT * FROM依售价查询书籍（400，500）

ORDER  BY  单价

 

在调用系统内置的函数时，需要加上   ：：

如：SELECT * FROM ：：fn_helpcollations()

 

在创建表时可以使用这些函数。

 

函数分为确定性函数和非确定性函数

确定性函数为当传入的参数不变时，返回值不变，而非确定性函数为传入参数不变，但每次返回的值会改变（如：GETDATE（）函数）

 

触发器（Trigger）是一种与数据表紧密结合的存储过程，当该数据表有新建（INSERT）更改（UPDATE）或删除（DELETE）事件发生时，所设置的触发器会自动被执行，以进行数据完整性，或其他一些特殊的数据处理工作。

触发器是针对单一数据表所撰写的特殊存储过程。

触发器的种类与操作：

触发器分为2类

AFTER触发器：这类触发器要在数据已变动完成之后（AFTER），才会被激活并进行必要的善后处理或检查。若发现错误，则可用ROLLBACK  TRANSATION回滚全部数据。

INSTEAD  OF触发器：INSTEAD  OF 是取代的意思，就是这类触发器会取代原本要进行的操作（例如新建或更改数据库操作），因此会在数据变动前就发生，而且数据要如何变动也完全取决于触发器。（该触发器能应用与数据表与视图）

建立触发器

CREATE  TRIGGER  trigger_name

ON  {table|view}

[with  encryption]        ------加密

{FOR|AFTER|INSTEAD OF}  ------FOR和AFITER都是要等到数据完全操作完后才激活触发器，INSTEAD OF则表示要建立INSTEAD OF触发器，此时触发器将取代原来要执行的数据操作。

{[DELETE][,][INSERT][,][UPDATE]}-----指定触发器的触发操作，必须指定一个，多个时以逗号隔开，在同一数据表中，AFTER触发器数目没有限制；但对INSTEAD OF 触发器来说则限制DELETE、INSERT、UPDATE每项最多只能有一个。

AS

Sql_statements    ------用来定义触发器的内容。

例如：下面对一个表建立2个触发器：

CREATE  TRIGGER  订单修改通知

ON  订单

AFTER  INSERT ，UPDATE

AS

PRINT  ‘又有订单被修改了！’

GO

 

CREATE TRGGER 订单删除通知

ON  订单

AFTER  DELETE

AS

PRINT  ‘又有订单被删除了！’

GO

INSERT 订单(日期，客户编号)

Values(‘2000/1/1’,3)

Delete订单

Where 日期=‘2000/1/1’

 

结果：

又有订单被修改了！

又有订单被删除了！

修改触发器：ALTER TRIGGER  trigger_name

………….

………….                    -----(与CREATE TRIGGER相同)

………….

删除触发器：

DROP  TRIGGER  trigger_name

查询触发器的相关信息

Exec  sp_helptrigger   ‘订单’, ‘DELETE’  --------只列出DELETE时触发的

Trigger_name	Trigger_owner	Isupdate	Isdelete	Isinsert	Isafter	Isinsteadof
订单删除通知	Dbo	0	1	0	1	0

 

查询触发器所有者及建立日期

EXEC  sp_help  订单删除通知

Name	Owner	Type	Created_datetime
订单删除通知	Dbo	Trigger	2001-03-10  15:46:33.557

 

事务通常是由BEGIN  TRAN 语句开始，一直执行到COMMIT  TRAN或ROLLBACK  TRAN语句时才结束。

当执行完事务中的最后一项数据库的操作后，若没有任何错误，我们可以用COMMIT TRAN提交事务。反之，若发现在事务中有任何的错误，则执行ROLLBACK  TRAN取消事务，并回滚至事务执行前的状态。

每执行完一项数据库的操作后，要立即检查@@ERROR和@@ROWCOUNT，否则再执行下一项数据库操作时，这2个系统变量将会被新的值代替。在更改或删除数据时，若因指定条件不符或其他原因而没有更改到任何一条记录，由于这种情况并不是错误（@@ERROR仍为0），因此我们还要检查@@ROWCOUNT的更改条数，以确定数据已正确更改。

 

无论是COMMIT TRAN还是ROLLBACK TRAN而结束的事务后，如果后面还有其他未执行的语句，则还会继续执行这些语句，直到批结束。不过已COMMIT的事务就无法再ROLLBACK了，同理，已ROLLBACK的事务也无法再COMMIT了。

 

在事务中，把事务中所有的语句看成是一个数据处理单元，因此单元必须全部做完或全部不做。

 

事务的4大特性：

1，  原子性：整个事务被看成一个执行单元，要么全部成功，要么全部取消。

2，  一致性：当事务完成后，数据库的内容必须全部更新妥当，而且仍然具备正确性及完整性。

3，  隔离性：在事务中所使用到的数据，必须与其他同时在进行的事务适度隔离（使用锁定数据的方法来隔离事务）

4，  永久性：事务一旦提交后，其所作的数据修改将被视为永久性的，无法再用ROLLBACK回滚了。

 

事务是以连接为单位，每个连接都可以有自己的事务。

事务的执行有3种模式：

1，  外显事务

2，  自动提交事务

3，  隐含事务

 

嵌套事务是以最外层的事务为提交或回滚对象的。

虽然嵌套事务是以最外层为提交对象的，但其中的每个事务都有自己的BEGIN TRAN 和COMMIT

其实事务是为存储过程而设计的，因为这样我们就可以在存储过程中撰写事务程序，而不用担心该程序被调用时是否已在另一个事务之中。

 

@@TRANCOUNT事务计数

无论是那里调用ROLLBACK，都会跳转到最外层的BEGIN TRAN处，@@TRANCOUNT都会变为0。‘

有时在事务中发生错误时，我们希望只要回滚一小部分就可以了

则用SAVE TRANSACTION来设置“事务保存点”，然后在必要时使用ROLLBACK来回滚到所保存的位置，而不会中断事务。

ROLLBACK 后的事务名称只能是由SAVATRAN或最外层的BEGIN TRAN所声明的事务名称。

 

使用分布式事务

只需要在开始部分更改一下就可以了

如：BEGIN  DISTRIBUTED  TRAN

 

事务的隔离分为4个等级

默认等级为：Read committed等级

隔离等级	保证不会读取到别人修改中的数据	保证已读取的数据不被修改	保证使用到的数据表不会被修改
Read uncommitted	NO	NO	NO
Read committed	OK	NO	NO
Repeatable Read	OK	OK	NO
Serializable	OK	OK	OK

对数据加锁

SET  TRANSACTION  ISOLATION  LEVEL  SERIALIZABLE(更新数据时使用)

SET  TRANSACTION  ISOLATION  LEVEL  REPEATABLE

(在查询时防止其他用户更新数据时使用)

 

游标的使用

游标的状态：

1	Cursor已打开，其内有0，1或多条记录
0	Cursor已打开，但确定其内没有查询到任何一条信息记录
-1	Cursor已关闭
-2	Cursor变量名未参照到时间Cursor或参照的Cursor已被DeAllocale
-3	指定的游标变量名不存在

 

Cursor主要是使用于SQL批、存储过程和出发器中。

游标的格式：

DECLARE  游标名  CURSOR  -------声明游标

FOR  SELECT 姓名  FROM 通讯薄  WHERE 地址=台北    -------------游标的数据来源

OPEN 游标名   -----------打开游标

DECLARE  @name  varchar(20)

FETCH  NEXT  FROM  游标名     ------------将第一条数据存入@name

INTO  @name

WHILE(@@FETCH_STATUS=0)      -------------判断是否读到数据，0表示读到数据

BEGIN

      …………………….

         FETCH  NEXT  FROM 游标名

      INTO  @name

END

CLOSE  游标名   ----------------关闭游标与数据的关联

DEALLOCATE  游标名  ---------------将游标对象删除

 

除了Cursor中FETCH数据外，我们也可以通过Cursor来UPDATE或DELETE一条目前所指定的记录

UPDATE 标标公司   SET 价格=100  WHERE CURRENT  OF  游标名

DELETE 标标公司   WHERE CURRENT  OF  游标名

下面是完整的范例：

DECLARE  MyCursor  CURSOR

LOCAL   SCROLL_LOCKS

FOR  SELECT 价格FROM  标标公司

FOR  UPDATE

OPEN  MyCursor

DECLARE  @money   money

FETCH  MyCursor  INTO @money

WHILE(@@FETCH_STATUS=0)

BEGIN

       IF  @money<=10

       BEGIN

              SET @money=money*1.1

              UPDATE  标标公司

              SET 价格=@money               -----------------更新价格信息

              WHERE   CURRENT OF MyCursor

       END

FETCH  MyCursor  INTO @money

END

 

游标变量，使用SET将已经声明好的游标给游标变量。在使用上，游标变量和游标是一样使用的。

 

全局游标和本地游标有相同的变量时，以本地游标为优先调用