| 導購 | 订阅 | 在线投稿
分享
 
 
 

在SQL Server2005中用語句創建數據庫和表

來源:互聯網  2008-06-01 02:15:18  評論

在SQL Server2005中用語句創建數據庫和表:

具體示例如下:

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

create database Study--創建數據庫

on primary

(

name='Study_data',--主數據文件的邏輯名

fileName='D:\data\Study_data.mdf',--主數據文件的物理名

size=10MB,--初始大小

filegrowth=10% --增長率

)

log on

(

name='Study_log',--日志文件的邏輯名

fileName='D:\data\Study_data.ldf',--日志文件的物理名

size=1MB,

maxsize=20MB,--最大大小

filegrowth=10%

)

go

use Study

go

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,

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

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 ('男','女')),--檢查約束,性別必須是男或女

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('第三組',3)

insert into Team values('第四組',4)

insert into Team values('第五組',5)

insert into Student values('小強','男','1982-6-9','23456789451','來自河北',1)

insert into Student values('小昭','男','1987-6-9','78945678945','山東',4)

insert into Student values('小溪','男','1982-6-9','65987845651','撫順',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 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('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')

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

在SQL Server2005中用語句創建數據庫和表: 具體示例如下: 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 create database Study--創建數據庫 on primary ( name='Study_data',--主數據文件的邏輯名 fileName='D:\data\Study_data.mdf',--主數據文件的物理名 size=10MB,--初始大小 filegrowth=10% --增長率 ) log on ( name='Study_log',--日志文件的邏輯名 fileName='D:\data\Study_data.ldf',--日志文件的物理名 size=1MB, maxsize=20MB,--最大大小 filegrowth=10% ) go use Study go 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, 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 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 ('男','女')),--檢查約束,性別必須是男或女 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('第三組',3) insert into Team values('第四組',4) insert into Team values('第五組',5) insert into Student values('小強','男','1982-6-9','23456789451','來自河北',1) insert into Student values('小昭','男','1987-6-9','78945678945','山東',4) insert into Student values('小溪','男','1982-6-9','65987845651','撫順',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 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('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') 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
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有