常用SQL语句总结(2)

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

操作数据列

1、alter table dbo.UserInfoadd PhoneNumber nvarchar(20) null2、alter table dbo.UserInfoalter column PhoneNumber varchar(25)3、alter table dbo.UserInfodrop column PhoneNumber

建库

use mastergo if exists(select * from sysdatabases where name='TestDB')begin  alter database TestDB set single_user with rollback immediate  drop database TestDBendcreate databse TestDBon 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 TestDBgoif exists(select * from sysobjects where name='Students')drop table Studentscreate 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 StudentClasscreate table StudentClass(  ClassID varchar(10) primary key,  ClassName nvarchar(20) not null)if exists(select * from sysobjects where name='ScoreList')drop table ScoreListcreate table ScoreList(  ID int identity(1,1) primary key,//值自增1主键列  StudentID int not null,  CSharp float null,  SQLServer float,  C语言 float)

建约束

//外键约束alter table Studentsadd constraint fk_ClassID foreign key(ClassID) references StudentClass(ClassID)//默认值约束alter table Studentsadd constraint df_StudentAddress default('地址不详') for StudentAddress//检查约束alter table Studentsadd constraint ck_Gender check(Gender='男'or Gender='女')//检查约束alter table Studentsadd constraint ck_Age check(Age>10 and Age<=30)//唯一约束alter table Studentsadd constraint uq_IDCard unique(IDCard)//外键约束alter table ScoreListadd constraint fk_StudentID foreign key(StudentID) references Students(StudentID)//添加约束的列只要设置可为null,那该列的值就照样可以为null，不过要注意添加唯一约束的列的值，值同为null也违反唯一约束//添加约束的列的值可为null，但是如果有值就必须符合约束规范，否则执行出错