数据库编程1

发表于：2006年10月13日 13时25分33秒本文链接：http://user.qzone.qq.com/183481361/blog/14
数据库编程1
--create database
use master
if exists(select name from sysdatabases where name = 'test')
drop database test
go
create database test
on
(name='test_data',
filename='D:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf',
size=1,
filegrowth=5)
log on
(name='test_log',
filename='D:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf',
size=1,
filegrowth=5)
go
use test
go
select * from sysfiles1
go

--create table
use test
go
if exists (select name from sysobjects where name='t_test2')
drop table t_test2
go
if exists(select name from sysobjects where name='t_test1')
drop table t_test1
go
--on [update|delete] [cascade|no action]判断相关联的表中,父表中更新或删除是否相应的改变或不动
--FOREIGN KEY...REFERENCES
--是为列中数据提供引用完整性的约束。FOREIGN KEY 约束要求列中的每个值在被引用表的指定列中都存在
create table t_test1
(col1 int identity(1,1) constraint pk_col1 primary key clustered,
col2 varchar(20) not null default 'morle',
col3 varchar(20) check(len(col3)>5),
col4 varchar(10) constraint ck_col4 check(col4 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
go
create table t_test2
(col1 int identity(100,1),
col2 int not null constraint Fk_col2 foreign key (col2) references t_test1(col1) on update cascade on delete cascade,
col3 varchar(50),
constraint pk_col1_col2 primary key(col1,col2)
)
go
insert into t_test1(col3,col4) values('tangjimo1','0000000')
insert into t_test2(col2,col3) values(1,'hello,welcome my friends')
go
select * from t_test1
delete from t_test1
select * from t_test2
go
--修改数据表
--只有列中不包含空值时，ALTER COLUMN 中才可指定 NOT NULL。
--必须将空值更新为非空值后，才允许执行 ALTER COLUMN NOT NULL 语句，
--比如：
--  UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
--  ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
------------------------with check|nocheck------------------------
--WITH CHECK 和 WITH NOCHECK 子句不能用于 PRIMARY KEY 和 UNIQUE 约束
--{ CHECK | NOCHECK} CONSTRAINT
--指定启用或禁用 constraint_name。如果禁用，将来插入或更新该列时将不用该约束条件进行验证。
--此选项只能与 FOREIGN KEY 和 CHECK 约束一起使用。
--ALL
--指定使用 NOCHECK 选项禁用所有约束，或者使用 CHECK 选项启用所有约束。
------------------------not for replication-----------------------
--NOT FOR REPLICATION
--指定当复制登录（如 sqlrepl）向表中插入数据时，不强制 IDENTITY 属性。也可对约束指定 NOT FOR REPLICATION。
--当复制登录向表中插入数据时，不检查约束条件。

alter table t_test2 with nocheck add
constraint pk_col1_col2 primary key clustered
(
col1,col2
)
go
alter table t_test2 add
constraint fk_col2 foreign key
(
col2
)references t_test1(
col1
)on delete cascade onupdate cascade
go

--create view
--创建视图 视图表存在information_schema.views中
if exists(select table_name from information_schema.views where table_name='view1')
drop view view1
go
create view view1(a,b,c,d,e) with encryption
as
select col1,col2,col3,col4,@@ROWCOUNT from t_test1
go
update view1 set c='tangjimo31' where a=7
select * from view1
select * from t_test1

--create the tables and insert the values
CREATE TABLE SUPPLY1 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
   supplier CHAR(50)
   )
CREATE TABLE SUPPLY2 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
   supplier CHAR(50)
   )
CREATE TABLE SUPPLY3 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
   supplier CHAR(50)
   )
CREATE TABLE SUPPLY4 (
   supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
   supplier CHAR(50)
   )
go
INSERT SUPPLY1 VALUES ('1', 'CaliforniaCorp')
INSERT SUPPLY1 VALUES ('5', 'BraziliaLtd')
INSERT SUPPLY2 VALUES ('231', 'FarEast')
INSERT SUPPLY2 VALUES ('280', 'NZ')
INSERT SUPPLY3 VALUES ('321', 'EuroGroup')
INSERT SUPPLY3 VALUES ('442', 'UKArchip')
INSERT SUPPLY4 VALUES ('475', 'India')
INSERT SUPPLY4 VALUES ('521', 'Afrique')
go
--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
   UNION ALL
SELECT *
FROM SUPPLY2
   UNION ALL
SELECT *
FROM SUPPLY3
   UNION ALL
SELECT *
FROM SUPPLY4
go
select * from all_supplier_view
go
use master
go
if exists(select * from sysdatabases where name = 'test')
drop database test
go
create database test
on
(name= 'test_dat',
filename ='d:\morle\data\test_dat.mdf',
size = 2,
filegrowth=10%)
log on
(name= 'test_log',
filename='d:\morle\data\test_log.ldf',
size=2,
filegrowth=10%)
go