1. Database files include:
Master data file: *.mdf
Secondary data file: *.ndf
Log file: *.ldf (l is lowercase L)
2. Create a database using T-SQL
code
use master
go
----------Create database------------
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. Use T-SQL to create database tables
code
----------Create database table------------
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),--ID card
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, --examination number
stuNo char(6) not null,--student number
writtenExam int not null,--written test results
LabExam int not null--computer-based test results
)
go
4. Add constraints
code
------------------Add constraints-----------------
alter table stuinfo --modify the stuinfo table
add constraint PK_stuNo primary key (stuNo)--Add the primary key PK_stuNo is a custom primary key name and can be omitted
alter table stuinfo
add constraint UQ_stuID unique (stuID) --Add a unique constraint
alter table stuinfo
add constraint DF_stuAddress default ('unknown address') for stuAddress--Add default, do not fill in the default 'unknown address'
alter table stuinfo
add constraint CK_stuAge check(stuAge between 18 and 60) --Add check constraint 18-60 years old
alter table stuMarks
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
go
5. Delete constraints
-------------Delete constraints-------------
alter table stuinfo
drop constraint constraint name--such as: FK_stuNo CK_stuAge DF_stuAddress UQ_stuID PK_stuNo