数据表创建完毕后,对数据表中的列进行增、删、改,对数据表建约束。
操作数据列
1、alter table dbo.UserInfo
add PhoneNumber nvarchar(20) null
2、alter table dbo.UserInfo
alter column PhoneNumber varchar(25)
3、alter table dbo.UserInfo
drop column PhoneNumber
建库
use master
go
if exists(select * from sysdatabases where name='TestDB')
begin
alter database TestDB set single_user with rollback immediate
drop database TestDB
end
create databse TestDB
on primary
(
name='TextDB_data',
filename='E:\MrGuo\TestDB.mdf',
size=10MB,
filegrowth=5MB
)
log on
(
name='TestDB_log',
file='E:\MrGuo\TestDB.ldf',
size=5MB;
filegrowth=2MB
)
go
建表
use TestDB
go
if exists(select * from sysobjects where name='Students')
drop table Students
create table Students
(
StudentID int primary key,
StudentName nvarchar(20) not null,
Gender nvarchar(2) not null,
Age int not null,
ClassID nvarchar(20) not null,
IDCard varchar(18) not null,
PhoneNumber varchar(11) null,
StudentAddress nvarchar(20)//不写null或not null,默认是null(值可为null)
)
if exists(select * from sysobjects where name='StudentClass')
drop table StudentClass
create table StudentClass
(
ClassID varchar(10) primary key,
ClassName nvarchar(20) not null
)
if exists(select * from sysobjects where name='ScoreList')
drop table ScoreList
create table ScoreList
(
ID int identity(1,1) primary key,//值自增1主键列
StudentID int not null,
CSharp float null,
SQLServer float,
C语言 float
)
建约束
//外键约束
alter table Students
add constraint fk_ClassID foreign key(ClassID) references StudentClass(ClassID)
//默认值约束
alter table Students
add constraint df_StudentAddress default('地址不详') for StudentAddress
//检查约束
alter table Students
add constraint ck_Gender check(Gender='男'or Gender='女')
//检查约束
alter table Students
add constraint ck_Age check(Age>10 and Age<=30)
//唯一约束
alter table Students
add constraint uq_IDCard unique(IDCard)
//外键约束
alter table ScoreList
add constraint fk_StudentID foreign key(StudentID) references Students(StudentID)
//添加约束的列只要设置可为null,那该列的值就照样可以为null,不过要注意添加唯一约束的列的值,值同为null也违反唯一约束
//添加约束的列的值可为null,但是如果有值就必须符合约束规范,否则执行出错