考试（一）

 create database compterschool
 use compterschool
 create table yuanbo
 (st_id varchar(50),
 st_name varchar(50),
 st_gender varchar(50),
 st_birth date,
 st_tel varchar(20),
 st_addr varchar(20))
select * from yuanbo
exec sp_rename 'st_name','nm_st'
exec sp_rename 'yuanbo.st_name','nm_st'
exec sp_rename 'yuanbo.st_tel','st_telephone'
insert into yuanbo values('3216090101**','yb','男',cast('1994 03 23'as date),1833918****,'兰五447')
select * from yuanbo
delete yuanbo
--
declare @n1 varchar(50),@i int,@cs date,@gen varchar(20), @j int,@k int
set @j=1
while @j<=100000
	begin
	set @k=rand()*10
	if(@k>5)
	set @gen='M'
	else set @gen='F'
	set @n1 =char(rand()*27+65)
	set @n1=@n1+char(rand()*27+65)
	--set @cs=DATEADD(day,-(rand()*14600+7300),getdate())
	set @cs=dateadd(DAY,-(rand()*3650+7300),getdate())
    insert into yuanbo(st_id,st_name,st_gender,st_birth) values(newid(),@n1,@gen,@cs)
	set @j=@j+1
end
--
select * from yuanbo
delete yuanbo

create database compterschool
create table niuaopeng(
st_id varchar(10),
st_name varchar(20),
st_gender varchar(20),
st_birth datetime,
st_tel varchar(20),
st_addr varchar(20)
)
select * from niuaopeng

alter table niuaopeng drop column st_name
alter table niuaopeng add nm_st varchar(20)

alter table niuaopeng drop column st_tel
alter table niuaopeng add st_telephone varchar(20)

insert into niuaopeng values('32161','男','2002-3-2','河南','李四','15623457896')
insert into niuaopeng values('32162','女','2001-4-2','河南','张三','15623457893')
insert into niuaopeng values('32163','男','2002-7-2','河南','王二','15623457891')
insert into niuaopeng values('32164','男','1997-3-2','河南','小明','15623457897')
insert into niuaopeng values('32165','男','1992-3-2','河南','小红','15623457896')
insert into niuaopeng values('32166','男','1993-3-2','河南','小杰','15623457806')

alter table niuaopeng drop column st_addr
alter table niuaopeng alter column st_id varchar(50)
delete from niuaopeng
declare @id varchar(50),@name varchar(5),@gender varchar(5),@birth datetime,@up datetime,@down datetime
declare @i int,@j int,@s int
set @i=1
set @up='19900101'
set @down='20101231'
while @i<=100000
begin
set @name=char(rand()*26+65)+char(rand()*26+65)
if((floor(rand()*2))=0)
set @gender='M'
else
set @gender='F'
set @birth=DATEADD(second,ABS(CHECKSUM(NEWID()))%DATEDIFF(second,@up,@down),@up)
insert into niuaopeng values(newid(),@gender,@birth,@name,'null')
set @i=@i+1
end

print (floor(rand()*2))