程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> MSSQL2005用語句創建數據庫和表

MSSQL2005用語句創建數據庫和表

編輯：關於SqlServer

use master
go

if exists (select * from sysdatabases where name='Study')--判斷Study數據庫是否存在，是則刪除
drop database Study
go

EXEC sp_configure 'show advanced options', 1
GO
-- 更新當前高級選項的配置信息
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
-- 更新當前功能（xp_cmdshell）的配置信息.
RECONFIGURE
GO

exec xp_cmdshell 'mkdir D:\data', NO_OUTPUT
--利用xp_cmdshell 命令創建文件夾，此存儲過程的第一個參數為要執行的有效DOS命令，第二個參數為是否輸出返回信息。
go

e="FONT-SIZE: 10pt; COLOR: blue">create database Study--創建數據庫
on primary
    (
       name='Study_data',--主數據文件的邏輯名
       fileName='D:\data\Study_data.mdf',--主數據文件的物理名
       size=10MB,--初始大小
       filegrowth=10%    --增長率
    )
log on

g>
    (
       name='Study_log',--日志文件的邏輯名
       fileName='D:\data\Study_data.ldf',--日志文件的物理名
       size=1MB,
       maxsize=20MB,--最大大小
       filegrowth=10%
    )
go

use Study
go

<

span style="FONT-SIZE: 10pt; COLOR: blue">if exists (select * from sysobjects where name='Student')--判斷是否存在此表
    drop table Student
go
create table Student
(
    id int identity(1,1) primary key,--id自動編號，並設為主鍵
    [name] varchar(20) not null,
    sex char(2) not null,

"#0000ff"> birthday datetime not null,
phone char(11) not null,

remark text,
    tId int not null,
    age as datediff(yyyy,birthday,getdate())--計算列。
)
go

if exists (select * from sysobjects where name='Team')
    drop table Team<

/span>
go
create table Team
(
    id int identity(1,1) primary key,
    tName varchar(20) not null,
    captainId int
)
go

alter table Student
add
    constraint CH_sex check(sex in ('男','
style="FONT-SIZE: 10pt; COLOR: red">女')),--檢查約束，性別必須是男或女
    constraint CH_birthday check(birthday between '1950-01-01' and '1988-12-31'),
    constraint CH_phone check(len(phone)=11),
    constraint FK_tId foreign key(tId) references Team(id),--外鍵約束，引用Team表的主鍵
    constraint DF_remark default('請在這裡填寫備注') for remark--默認約束，

go

alter table Team
add
    constraint UK_captainId unique(captainId)--唯一約束
go

insert into Team values('第一組',1)
insert into Team values('第二組',2)
insert into Team values('NT-SIZE: 10pt; COLOR: red">第三組',3)
insert into Team values('第四組',4)
insert into Team values('第五組',5)

insert into Student values('張三','男','1982-6-9','23456789451','來自天津

10pt; COLOR: red">',1)
insert into Student values('李四','男','1987-6-9','78945678945','安徽',4)
insert into Student values('王五','男','1982-6-9','65987845651','大連

OLOR: red">',3)
insert into Student values('趙六','男','1981-6-9','25487965423','湖南',5)
insert into Student(name,sex,birthday,phone,tId) values('江七','男','1984-6-9',

'25487965423',5)

select * from Team
select * from Student

if exists (select * from sysobjects where name='teacher')
    drop table teacher
go

create table teacher
(
    id int identity (1,1) primary blue">key,
    name varchar(20),
    address varchar(20)
)

go

insert into teacher values('zhang','hubei')
insert into teacher values('wang','hubei')
insert into teacher values('li','hubei')
insert into teacher values

OR: gray">('chen','hunan')
insert into teacher values('zhao','hunan')
insert into teacher values('tian','guangdong')
insert into teacher values('ma','guangdong')
insert into teacher values('chang','tianjin')
insert into teacher values('liang','beijing')

trong>select * from teacher

select count(*),address from teacher group by address having address<>'hunan'
--按地址分組查詢並用having字句篩選出地址不是‘hunan’的

EXEC sp_configure 'xp_cmdshell', 0
GO
-- 還原當前功能（xp_cmdshell）的配置信息為初始狀態.

RECONFIGURE

GO

EXEC sp_configure 'show advanced options', 0
GO
-- 還原當前高級選項的配置信息為初始狀態
RECONFIGURE
GO