SQLServer數(shù)據(jù)庫數(shù)據(jù)表的一些操作腳本
2021-09-24 09:07:53

SQLServer 數(shù)據(jù)庫一些基本操作的腳本。

use master
go

create database TestDB
on primary
(
    name='TestDB_data',
	filename='F:DBTestDB_log.mdf',--絕對路徑
	size=10MB,--數(shù)據(jù)庫初始大小
	filegrowth=5MB --數(shù)據(jù)文件增長量
	--上面四個文件缺一不可
)
log on
(
    name='TestDB_log',
	filename='F:DBTestDB_log.ldf',
	size=5MB,
	filegrowth=2MB
)

go

use master
go

if exists(select * from sysdatabases where name = 'TestDB01') 
drop database TestDB01 --刪除就不能恢復了，一般測試階段數(shù)據(jù)庫內沒有數(shù)據(jù)的時候用。

create database TestDB01
on primary
(
    name='TestDB01_data',
	filename='F:DBTestDB01_data.mdf',
	size=5MB,
	filegrowth=2MB
)
,
(
    name='TestDB01_data1',
	filename='F:DBTestDB01_data.ndf',
	size=5MB,
	filegrowth=2MB
)

log on
(
    name='TestDB01_log',
	filename='F:DBTestDB01_log.ldf',
	size=2MB,
	filegrowth=1MB
)
,
(
     name='TestDB01_log1',
	filename='F:DBTestDB01_log1.ldf',
	size=2MB,
	filegrowth=1MB
)
go

use TestDB
go

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

create table Students
(
    StudentId int identity(100000,1),--學號
	StudentName varchar(20) not null,--姓名
	Gender char(2) not null, --性別
	Birthday datetime not null,--出生日期
	StudentIdNo numeric(18,0) not null,--身份證號
	Age int not null,--年齡。其實可以通過身份證號來動態(tài)獲取
	PhoneNumber varchar(50),--電話號碼
	StudentAddress varchar(500),--地址
	ClassId int not null--班級外鍵
)
go

--創(chuàng)建班級表
if exists(select * from sysobjects where name='StudentClass')
drop table StudentClass
go
create table StudentClass
(
    ClassId int primary key,--班級編號
	ClassName varchar(20) not null--班級名稱

)
go

--創(chuàng)建成績表
if exists(select * from sysobjects where name='ScoreList')
drop table ScoreList
go
create table ScoreList
(
    Id int identity(1,1) primary key,
	StudentId int not null,--學號外鍵
	CSharp int null,
	SQLServer int null,
	UpdateTime datetime not null,--更新時間
)
go

--創(chuàng)建管理員表
if exists(select * from sysobjects where name='Admins')
drop table Admins
go
create table Admins
(
    LoginId int identity(1000,1) primary key,
	LoginPwd varchar(20) not null,--登錄密碼
	AdminName varchar(20) not null
)
go

--添加相關約束
--創(chuàng)建主鍵約束
use TestDB
go
if exists(select * from sysobjects where name='pk_StudentId')
alter table Students drop constraint pk_StudentId

alter table Students add constraint pk_StudentId primary key(StudentId)

--添加相關約束
--創(chuàng)建唯一約束
use TestDB
go
if exists(select * from sysobjects where name='uq_StudentsIdNo')
alter table Students drop constraint uq_StudentsIdNo
alter table Students add constraint uq_StudentsIdNo unique(StudentIdNo)