数据库 基本sql应用创建表create table x( id int identity(1,1) primary key,)drop table x创建数据库create database schoolon primary( name='school', size=10mb, filename='c:\school.mdf')log on( name='school_log', size=5mb, filename='c:\school.ldf')可以插入自动编号set IDENTITY_INSERT x oninsert into x values(1,'fds')插入数据insert into xselect 's' unionselect 'a' unionselect 'b'用union 会去掉重复的如果用union all 不会去掉重复的//会把x表的数据全部清空，自动ID会初始化truncate table x替换update xx set url=replace(url,'http://','')update xx set url='http://'+urlupdate TblScoreset tEnglish=(case when tEnglish+5<=100 then tEnglish+5when tEnglish+5>100 then 100end), tMath=(case when tMath+5<=100 then tMath+5when tMath+5>100 then 100end)--手动删除一列(删除EmpAddress列)alter table Employees drop column EmpAddressgo--手动增加一列(增加一列EmpAddr varchar(1000))alter table Employees add EmpAddr varchar(1000)--手动修改一下EmpEmail的数据类型(varchar(200))alter table Employees alter column EmpAddr varchar(200)--为EmpId增加一个主键约束alter table Employees add constraint PK_Employees_EmpId primary key(EmpId)--非空约束,为EmpName增加一个非空约束，修改列为not null--增加一个非空约束其实就是修改列alter table Employees alter column EmpName varchar(50) not null--为EmpName增加一个唯一约束alter table Employees add constraintUQ_Employees_EmpName unique(EmpName)--为性别增加一个默认约束，默认为'男'alter table Employees add constraintDF_Employees_EmpGender default('男') for EmpGender --为年龄增加一个检查约束：年龄必须在0-120岁之间，含0岁与120岁。alter table Employees add constraintCK_Emplpoyees_EmpAge check(empage>=0 and empage<=120)--增加外键约束，表Employee中有一列EmpDeptId引用TblDepartment表中的DeptIdalter table Employees add DeptId int not nullalter table Department add constraintPK_Department_DeptId primary key(DepId)alter table Employees add constraintFK_Employees_Department foreign key(DeptId) references Department(DepId) on delete cascade--先删除原来的外键alter table Employees drop constraint FK_Employees_Department--一条语句删除多个约束，约束名用 逗号 隔开alter table Employees drop constraint FK_Employees_Department, CK_Emplpoyees_EmpAge,UQ_Employees_EmpName--用一条语句为表增加多个约束。 alter table Employees add constraint UQ_Employees_EmpName unique(EmpName),constraint CK_Emplpoyees_EmpAge check(EmpAge>=0 and EmpAge<=120)
