考试（二）

use compterschool
--1、建立文本编辑10行数据，tab分割，行末分行
--身份证号 姓名 性别 年龄 专业 电话 
create table inf1
(st_sfz varchar(20),
st_xm varchar(20),
st_xb varchar(5),
st_age int,
st_zy varchar(20),
st_tel varchar(15))
--用bulk insert 导入到inf表
bulk insert inf
from 'D:\exam.txt'
with(fieldterminator='	',rowterminator='\n')
select * from inf1
select * from inf
--2、复制inf为inf1，插入100调记录，身份证号改为41001到41100
declare @i int ,@sfz varchar(20)
set @i=1
while @i<=100
	begin 
	set @sfz=floor(rand()*100+41000)
	insert into inf1 values(@sfz,'张三','男',22,'计算机科学与技术','18339180622')
	set @i=@i+1
end
--
delete inf1
--复制inf表为inf1，将原数据
--将身份证号改为inf1
declare @s varchar(20)
declare cur cursor for select st_sfz from inf1
open cur
fetch from cur into @s
while @@FETCH_STATUS=0
begin
update inf1 set st_sfz=floor(rand()*100+41001)
where current of cur
fetch next from cur into @s
end
close cur
--
deallocate cur
--显示70到90条记录
select * from inf1
select * from (select row_number() over (order by st_sfz) as hanghao,* from
 inf1) 
as t where hanghao between 70 and 90
--3、生成两个字段的表（其中一个是关键字），插入100万条记录，按关键字索引，查找值为0或'0'的记录的时间
create table stu1
(stu_id int primary key,
stu_name varchar(20))
--
declare @j int,@na varchar(20)
set @j=1
while @j<=1000000
begin
	--set @na=rand()*1000--+char(rand()*26+65)
	set @na=char(rand()*26+65)+char(rand()*26+65)
	insert into stu1 values(@j,@na)	
	set @j=@j+1
end
--查询记录
select * from stu1
--删除记录
delete stu1
--删除表
drop table stu1
--创建索引
create unique index ind on stu1(stu_id)
declare @dt datetime   
set @dt=getdate()  
--select * from stu1 where stu_id=0    --or stu_name=0
select * from stu1 where stu_name='o'
select 'result',datediff(ms,@dt,getdate())
删除索引
drop index ind on stu1
---
---
set @dt=getdate()  
create clustered index ind on stu(stu_name)  
select 'ing',datediff(ms,@dt,getdate())  
set @dt =getdate()  
select * from stu where stu_name=0  
select 'after',datediff(ms,@dt,getdate())