原有表Tb2,内含两个字段Id、Name,其中id为自增长主键(int类型),Name为非空varchar(20),现向表中插入数据语句如下所示:
--1 创建表Tb2
create table Tb2(
Id int not null primary key identity(1,1),
Name varchar(20) not null
)select *from Tb2--2 向表中添加数据
insert into Tb2 values('aa')
insert into Tb2 values('bb')
insert into Tb2 values('aa')
insert into Tb2 values('dd')
insert into Tb2 values('bb')select * from Tb2 现在要求向表中增加字段password (32个不可变字符长度,非空),请写出sql脚本。要保证添加后的语句执行后不要报错
--1 创建表Tb2
create table Tb2(
Id int not null primary key identity(1,1),
Name varchar(20) not null
)select *from Tb2--2 向表中添加数据
insert into Tb2 values('aa')
insert into Tb2 values('bb')
insert into Tb2 values('aa')
insert into Tb2 values('dd')
insert into Tb2 values('bb')select * from Tb2 现在要求向表中增加字段password (32个不可变字符长度,非空),请写出sql脚本。要保证添加后的语句执行后不要报错
alter table tb2 add password char(32)
go
update tb2 set password=''
go
alter table tb2 add password char(32) null
alter table tb2 add password char(32)
go
update tb2 set password=''
go
alter table tb2 add password char(32) not null
alter table tb2 add password char(32)
go
update tb2 set password=''
go
alter table tb2 alter column password char(32) not null
---create table tb(col varchar(10))
insert into tb select 'aa' union all select 'bb'
go
alter table tb add password varchar(32)
go
update tb set password=''
go
alter table tb alter column password varchar(32) not null
select * from tb
/*
col password
---------- --------------------------------
aa
bb (2 行受影响)*/
go
drop table tb
alter table tb2 add password char(32) default('') not null
alter table tb2 add password varchar(32) not null default ('')
--更新之前的数据--
update tb2 set password=''