创建数据库笔记（一）

use master
go
--创建数据库
if exists(select * from sysdatabases where name='DemoDB')
drop database DemoDB
go
create database DemoDB
on primary
(
name='DemoDB_data',
filename='C:\\DB\DemoDB_data.mdf',
size=10MB,
filegrowth=2MB
)
log on
(
    name='DemoDB_log',
filename='C:\\DB\DemoDB_log.ldf',
size=5MB,
filegrowth=2MB
)
go
--创建需要的各种数据表
use DemoDB
go
if exists(select * from sysobjects where name='Sutudents')
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,
PhoneNumber varchar(50),
StudentAddress varchar(500),
ClassId int not null  --班级编号（外键）
)
go
--创建班级表
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
--创建成绩表
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,
SQLServerDB int null,
UpdateTime datetime not null
)
go
--创建管理员表
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
--创建主键约束
use DemoDB
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)


--创建唯一约束
alter table Students add constraint uq_StudentIdNo unique (StudentIdNo)


--创建检查约束
alter table Students add constraint ck_Age check (Age between 18 and 25)


--创建默认约束
alter table Students add constraint df_StudentAddress default('地址不详') for  StudentAddress


--创建外键约束
alter table Students add constraint fk_ClassId foreign key(classId) references StudentClass(ClassId)