1. 數(shù)據(jù)庫文件包括:

主數(shù)據(jù)文件：*.mdf

次要數(shù)據(jù)文件：*.ndf

日志文件：*.ldf ( l 是 L 的小寫)

2.使用T-SQL創(chuàng)建數(shù)據(jù)庫

代碼
use master
go
-----------創(chuàng)建數(shù)據(jù)庫------------

if exists (select * from sysdatabases where name='stuDB')
drop database stuDB
create database stuDB
on PRimary
(
name='stuDB_data',
filename='D:/stuDB_data.mdf',
size=3mb,
maxsize=10mb,
filegrowth=1mb
)
log on
(
name='stuDB_log',
filename='D:/stuDB_data.ldf',
size=1mb,
filegrowth=1mb
)

3.使用T-SQL 創(chuàng)建數(shù)據(jù)庫表

代碼
-----------創(chuàng)建數(shù)據(jù)庫表------------
use stuDB
go
if exists (select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(
    stuName varchar(20) not null,
    stuNo char(6) not null,
    stuAge int not null,
    stuID numeric(18,0),--身份證
    stuSeat smallint identity(1,1),
    stuAddress text
)
go

if exists (select * from sysobjects where name='stuMarks')
drop table stuMarks
create table stuMarks
(
    ExmaNo char(7) not null, --考號
    stuNo char(6) not null,--學(xué)號
    writtenExam int  not null,--筆試成績
    LabExam int not null--機試成績
)
go
 

4. 添加約束

代碼
--------------添加約束-----------------

alter table stuinfo --修改stuinfo表
add constraint PK_stuNo primary key (stuNo)--添加主鍵 PK_stuNo是自定義的主鍵名 也可以省略

alter table stuinfo
add constraint UQ_stuID unique (stuID) --添加唯一約束

alter table stuinfo
add constraint DF_stuAddress default ('地址不詳') for stuAddress--添加默認  不填默認’地址不詳‘

alter table stuinfo
add constraint CK_stuAge check(stuAge between 18 and 60) --添加檢查約束 18-60歲

alter table stuMarks
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
go
 

5.刪除約束

-------------刪除約束--------------
alter table stuinfo
drop constraint 約束名 --如：FK_stuNo  CK_stuAge DF_stuAddress UQ_stuID PK_stuNo