第八节 建表、约束、登录名

--学生成绩表

createtable StuMark

(

  ID intprimarykeyidentity(1,1),

  StuID int,

  WrittenExam int,

  LabExam int

)

--判断表是否存在

ifexists

(select*from sysobjects wherename='StuMark')

droptable StuMark

 

createtable StuMark

(

  ID intprimarykeyidentity(1,1),

  StuID int,

  WrittenExam int,

  LabExam int

)

 

--创建主键约束

altertable StuInfo

addconstraint PK_ID primarykey(ID)

--创建唯一约束

altertable StuInfo

addconstraint UQ_StuName unique(StuName)

--创建默认约束

altertable StuInfo

addconstraint DF_Address default('地址不详')

for Address

--创建检查约束

altertable StuInfo

addconstraint  CK_Age check(Age between 18 and 40)

--创建外键约束

altertable StuMark

addconstraint FK_StuID foreignkey(StuID)

references StuInfo(ID)

 

--删除约束

altertable StuInfo

dropconstraint DF_Address

 

--创建SQLServer登录名

execsp_addlogin'zhangsan',123

--创建数据库用户

execsp_grantdbaccess'zhangsan','zs'

--授予数据库用户操作权限

grantselect,insert,update,deleteon StuInfo to zs