SQl考试

create database computerschool
use computerschool


create table yangcheng(st_id varchar(50),st_name varchar(20),st_gender varchar(20),
st_birth date,st_tel varchar(20),st_addr varchar(20))


sp_rename 'yangcheng.st_name','nm_st'
sp_rename 'yangcheng.st_tel','st_telephone'


drop table yangcheng


insert into yangcheng values('123','张三',0,'1996-11-14','321','成都')
insert into yangcheng values('234','李四',0,'1995-12-16','432','广州')
insert into yangcheng values('345','王五',0,'1994-9-12','543','上海')
insert into yangcheng values('456','马云',1,'1968-7-21','654','杭州')
insert into yangcheng values('567','李彦宏',1,'1972-2-4','765','北京')
insert into yangcheng values('678','马化腾',1,'1974-6-28','876','深圳')


update yangcheng set st_name='雷军' where st_name='王五'


select *from yangcheng
alter table yangcheng add st_ss varchar(10)
alter table yangcheng drop column  st_addr


declare @i int,@id varchar(50), @na varchar(10),@ge varchar(10),@bir date,@tel varchar(10)
set @i=1
while (@i<=100)
begin
set @id=newid()
set @na= char(rand()*26+65)+char(rand()*26+65)
set @ge=cast(floor(rand()*2)as char)
if(@ge='0')
set @ge='F'
else 
set @ge='M'
set @bir=dateadd(day,-(rand()*7300+2555),getdate())  --90-10
set @tel= convert(int,rand()*900000+100000)
insert into yangcheng values(@id,@na,@ge,@bir,@tel)
set @i=@i+1
end
select *from yangcheng