sqlserver語句總結

以前從來沒有注意過寫sql語句,現在陸續學下

/*建立資料庫dl*/
use master
if exists(select 1 from master..sysdatabases where name='dl')
drop database dl
go

use master
go
create database dl
on
(
name=dl_data,
filename='D:/Program Files/Microsoft SQL Server/MSSQL/Data/dl_data.mdf',
size=4,
maxsize=10,
filegrowth=1
)
log on
(
name=dl_log,
filename='D:/Program Files/Microsoft SQL Server/MSSQL/Data/dl_log.ldf',
size=4,
maxsize=10,
filegrowth=1
)
go

/*建立科目表*/
use dl
/* if exists(select 1 from master..sysdatabases where object_id('dl..student') is not null)*/
if object_id('dl..subjects') is not null
drop table subjects

create table subjects
(
subId int primary key , /*主鍵*/
subName nvarchar(50) not null
)
go

/*建立學生資訊表*/
use dl
if object_id('dl..student') is not null
drop table student

create table student
(
stuId int primary key,/*主鍵*/
stuName nvarchar(50) not null,
stuGrade int not null
)

/*建立單個科目表*/
use dl
if object_id('dl..subject') is not null
drop table subject

create table subject
(
subId int not null,
stuId int not null,
stuGrade int not null
)
/*設定限制*/
begin transaction
go
alter table subject
add constraint FK_STUID foreign key(stuId)
references  student(stuId)
on delete cascade on update cascade /*如果刪除則一起刪除*/

alter table subject
add constraint FK_SUBID foreign key(subId)
references  subjects(subId)
on delete cascade on update cascade
go

/*插入資料*/
use dl

declare @iNum int
set @iNum=1

truncate table subject
while @iNum<10
begin
insert into subject(subId,stuId,stuGrade)
values(1,@iNum,50+@iNum*3/2)
set @iNum=@iNum+1
end

truncate table subjects
set @iNum=1
while @iNum<10
begin
insert into subjects(subId,subName)
values(@iNum,@iNum+1)
set @iNum=@iNum+1
end

關於寫那個constraint有問題，每次到那之後就會出現運行很長時間甚至sql企業管理器卡死的現象.再查原因.