常用SQL语句总结(2)

数据表创建完毕后，对数据表中的列进行增、删、改，对数据表建约束。

操作数据列

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，但是如果有值就必须符合约束规范，否则执行出错