4月19SQL学习

---4.19数据完整性及编程结构的学习---

---实施来分类，分为两类，一：引用数据完整性-插入、修改和删除数据时自动实施数据完整性。二：引用的行动完整性-
---这种完整性可以通过视图、触发器或存储过程来维护，其中视图和存储过程不能自动执行。
---数据维护上定义，数据完整性分四类。一：实体完整性；二：值域完整性；三：引用完整性；四：用户定义完整性。

--返回表格所有约束信息--
USE New
GO
sp_helpconstraint customer
GO

--- 定义列级的主健约束--
USE New
GO
CREATE TABLE test1
             (job_id smallint IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY CLUSTERED,
              job_desc varchar(50) NOT NULL DEFAULT 'new position-title not formalized yet',
              )
GO

---多列建立主健时，必须建立表级约束--
USE New
GO
CREATE TABLE test2
             (event_type int,
              event_time datetime,
              event_site char(50),
              event_desc char(1024),
              CONSTRAINT event_key PRIMARY KEY(event_type,event_time)
              )
GO
--利用存储过程sp_pkeys浏览主健约束信息--
USE New
GO
sp_pkeys test2
GO

---删除test2的主健约束,主健约束名为event_key---
USE New
GO
ALTER TABLE test2
DROP CONSTRAINT event_key
GO

--添加的主健约束--
USE New
GO
ALTER TABLE test1
ADD CONSTRAINT PK_id
PRIMARY KEY CLUSTERED (job_id)
GO

--添加test2的主健约束--
USE New
GO
ALTER TABLE test2
ADD CONSTRAINT PK_eventid
PRIMARY KEY CLUSTERED (eventid)
GO

--- 使用UNIQUE--
USE New
GO
CREATE TABLE test3
            (event_name char(20),
             event_type char(20),
             event_time datetime,           
             event_id int PRIMARY KEY CLUSTERED,
             CONSTRAINT UNIQ_EVENT UNIQUE(event_type,event_time)
               )
GO

---删除UNIQUE--
USE New
GO
ALTER TABLE test3
DROP CONSTRAINT UNIQ_EVENT
GO
---添加UNIQUE---
USE New
GO
ALTER TABLE test3
ADD CONSTRAINT UNIQ_NEW1
UNIQUE (event_type,event_time)
GO

---使用CHECK--
USE New
GO
CREATE TABLE sales
            (SaleID int IDENTITY(100000,1) NOT FOR REPLICATION,
             CHECK NOT FOR REPLICATION(SaleID<=199999),
             SalesRegion char(2),
             CONSTRAINT ID_PK PRIMARY KEY(SaleID)
             )
GO

USE New
GO
INSERT INTO sales
VALUES ('dj')
GO

---使用FOREIGN KEY约束---
USE New
GO
CREATE TABLE test4
            (pub_id varchar(20) PRIMARY KEY,
             pub_name varchar(50),
             addrss varchar(20),
             city varchar(10),
             state char(2),
             country char(10)
)
GO
CREATE TABLE test5
             (author_id varchar(20) PRIMARY KEY,
              author_name varchar(50),
              phone varchar(20),
              zipcode char(10)
)
---[ON DELETE{CASCADE｜NO ACTION}]表示删除与主健相对应的主健所在的行时，级联删除（CASCADE）外健所在的
---行的数据或者不做任何操作（NO ACTION）,NO ACTION是默认的设置。
---[ON UPDATE{CASCADE｜NO ACTION}]表示修改与主健相对应的主健所在的行时，级联修改（CASCADE）外健所在的
---行的数据或者不做任何操作（NO ACTION）,NO ACTION是默认的设置。
GO
CREATE TABLE test6
             (title_id int PRIMARY KEY,
              title_name varchar(50),
              author_id varchar(20) CONSTRAINT foreignkey_auid FOREIGN KEY REFERENCES test5(author_id)
               ON DELETE CASCADE    NOT FOR REPLICATION,
               pud_id varchar(20) CONSTRAINT foreignkey_pubid FOREIGN KEY REFERENCES test4(pub_id)
               NOT FOR REPLICATION )
GO

--删除外健约束---
USE New
GO
ALTER TABLE test6
DROP CONSTRAINT foreignkey_pubid
GO

--添加外健约束--
USE New
GO
ALTER TABLE test6
WITH CHECK ADD CONSTRAINT foreignkey_pub_id FOREIGN KEY   REFERENCES test4(pub_id) NOT FOR REPLICATION
GO

---创建规则--
USE New
GO
CREATE RULE SalesRegion_rule1
AS @SalesRegion IN ('sh','bj','sz','rj')
GO
---使用规则---
sp_bindrule SalesRegion_rule1, 'sales.SalesRegion'

--删除规则--
USE New
GO
DROP RULE SalesRegion_rule1
GO
--存储过程解除列的规则---
sp_unbindrule 'sales.SalesRegion'

--使用默认值--
USE New
GO
CREATE DEFAULT def_christmas
AS '12-25-2007'
GO

sp_bindefault 'def_christmas','test2.event_time'

--解除绑定--
sp_unbindefault 'test2.event_time'

--编程结构
--注释“--”一般注释一行。多行用“/*”和“*/”
--批处理
/*在批处理中可以包含存储过程，但是如果在一个批处理中不只包含一个存储过程，那么除第一个存储过程外
，所有剩下的存储过程都必须在前面使用exec关键字，某些特殊的SQL指令，不能和别的SQL指令共存于一个批
处理中。例如，用来创建表格的CREATE TABLE命令，就只能独自存在与一个单独的批处理中。CREATE VIEW命
令也有同样的情况*/

--局部变量的赋值
--当返回多个值时，把最后一个查询结果给变量
USE New
GO
DECLARE @sale_id1 varchar(5)

SELECT @sale_id1=SalesRegion
FROM sales
WHERE SaleID='10002'
GO

---SELECT赋值功能不能与查询功能共用如：
USE New
GO
DECLARE @sale_id1 varchar(5)

SELECT @sale_id1=SalesRegion,
--       SalesID
FROM sales
WHERE SaleID='10002'
GO

---SET赋值

DECLARE @sale_id1 varchar(5)
SET @sale_id1='12345'
--显示变量
SELECT @sale_id1 '显示变量'
GO