sqlserver练习

1.基本表的练习：

create table Test(
    name varchar(4),
    age int,
    sex varchar(2)
)
alter table Test
add id char(16)
alter table Test
alter column id varchar(16)
alter table Test
drop column id
drop table Test
select * from Test

 

2.记录操作DML：

create table Test(
    name varchar(4),
    age int,
    id char(16)
)
alter table Test
 alter column name varchar(6) 
insert into Test 
values('崔哥哥',15,'311409060209');--注意单引号

delete from Test
where name='崔哥哥'

update Test set age=18 where name='崔哥哥'

bulk insert Test 
from 'C:\Users\Administrator\Desktop\test.txt'
with(
    fieldterminator=' ',rowterminator='\n'
)

select name from Test where LEN(id)=7 order by age 
--desc asc
select * from Test

 

3.function用法：

--declare @a int, @b int
--print rand()*26+65
--select @a=cast('123' as int), @b=convert(int,'456');
--print @b
--print ascii('A')
--print char(rand()*26+65)

--print substring('李四',1,1);
--select * from Test
--where left(name,1)='崔' or right(name,1)='三'or substring(name,2,1)='四'

print getdate()
print dateadd(day,2,getdate())
print dateadd(minute,2,getdate())
print dateadd(second,2,getdate())
print dateadd(hour,2,getdate())
print datediff(second,getdate(),dateadd(hour,2,getdate()))
print datepart(week,getdate())-datepart(week,dateadd(month,-1,getdate()))
print str(1.25,3,1)+'asd'

--print str(参数1,参数2,参数3)
--参数2表示转换后的数据的总位数，包括小数点，正负号
--参数3表示转换后的数据的小数位数

 

4.bulk insert用法：

bulk insert Test 
from 'C:\Users\Administrator\Desktop\test.txt'
with(
    fieldterminator=' ',rowterminator='\n'
)

 

5.cursor用法：

--declare mycursor scroll cursor for select * from Test
open mycursor
declare @name varchar(6),@age int,@id char(16)

fetch first from mycursor into @name,@age,@id
print @name
print @age
print @id
while @@FETCH_STATUS=0
begin
    fetch next from mycursor into @name,@age,@id
    --absolute 10
    print @name
    print @age
    print @id
end
close mycursor
deallocate mycursor

 

6.primary key，unique，not null, check用法:

alter table Test 
drop column name

select * from Test
delete from Test

select * from Test

alter table Test
add name varchar(6) primary key

alter table Test
alter column id varchar(16) not null

alter table Test
drop column age
alter table Test
add age int check( age>=0 and age<=100)

 

7.view and index:

create view myview as select * from Test
select * from myview
select * from myview where age=18

create clustered index myindex on Test(age)
alter index myindex on Test rebuild
drop index Test.myindex

 

8.trigger用法:

建立车牌信息表：

--io10

declare @id varchar(10), @i int, @j int, @k int, @temp varchar(10), @tmp varchar(26);
set @temp = '豫京津沪';
set @tmp = 'HABCDEFGHJKLMN23456789';
set @i = 0;

while(@i<200000)
begin
    set @id = '';
    select @k = 0;
    set @j = rand()*4+1;
    set @id = @id + substring(@temp, @j, 1);
    set @j =rand()*14+1;
    set @id = @id + substring(@tmp, @j, 1);
    print @id;
    while(@k < 5)
    begin
        set @j =rand()*22+1;
        set @id = @id + substring(@tmp, @j, 1);
        set @k = @k + 1;
    end
    set @i = @i + 1;
    insert into viechle values(@id,0,0,0);
end

--select * from viechle

建立日志表：

--use handsomecui
--drop table mylog
--create table mylog(
--    tablename varchar(10),
--    altername varchar(10),
--    altertimr date
--)

insert into viechle values('123',0,0,0);

update viechle set number_id='456' where number_id='123'

select * from mylog

触发器插入日志文件：

--drop trigger mytrigg
create trigger mytrigger
on viechle after insert,update, delete
as
begin
if exists(select 1 from inserted) and exists(select 1 from deleted)
insert into mylog values('viechle','update',getdate());
if exists(select 1 from inserted) and not exists(select 1 from deleted)
insert into mylog values('viechle','insert',getdate());
if not exists(select 1 from inserted) and exists(select 1 from deleted)
insert into mylog values('viechle','delete',getdate());
end

 

 

数据库考核，统计职工生日，以及生日相同的人的个数；

1.建表，插入数据

--姓名>3 age 20-30
--drop table Worker
--create table Worker(
--    stname varchar(10),
--    stbirth date,
--    styear int,
--    stdate varchar(4),
--    cnt int
--)

declare @bir date, @yy int, @mm int, @dd int, @name varchar(10), @i int, @j int, @stdate varchar(4);
select @i = 0, @j = 0;
while(@i < 365)
begin
    select @name = '', @j = 0, @stdate = '';
    while(@j < 5)
    begin
        set @name = @name + char(rand()*26 + 65);
        set @j = @j + 1;
    end
    set @bir = dateadd(day, -20*365, dateadd(day, -10*365*rand(), getdate()));
    set @yy = datepart(year, @bir);
    set @mm = datepart(month, @bir);
    set @dd = datepart(day, @bir);
    if(@mm < 10)
    begin
        set @stdate = @stdate + '0';
        set @stdate = @stdate + char(@mm + 48);
    end
    else
    begin
        set @stdate = @stdate + char(@mm/10 + 48);
        set @stdate = @stdate + char(@mm%10 + 48);
    end
    if(@dd < 10)
    begin
        set @stdate = @stdate + '0';
        set @stdate = @stdate + char(@dd + 48);
    end
    else
    begin
        set @stdate = @stdate + char(@dd/10 + 48);
        set @stdate = @stdate + char(@dd%10 + 48);
    end
    insert into Worker values(@name, @bir, @yy, @stdate, 1);
    set @i = @i + 1;
end

--select * from Worker

2.游标修改cnt员工相同人的个数：

--select stname from Worker where left(stname, 1)='A'

--create clustered index myindex on Worker(stdate)

--select  *  from Worker order by stdate 

--close mycursor
--deallocate mycursor

declare mycursor scroll cursor for select stname,stdate from Worker


declare @date varchar(4), @cnt int, @stname varchar(10);
open mycursor
fetch first from mycursor into @stname,@date
select @cnt = count(*) from Worker where stdate=@date group by stdate
update Worker set cnt=@cnt where stdate = @date;
while @@FETCH_STATUS=0
begin
    fetch next from mycursor into @stname,@date
    
    select @cnt = count(*) from Worker where stdate=@date group by stdate
    update Worker set cnt=@cnt where stdate = @date;
end
close mycursor
deallocate mycursor


--select * from Worker order by cnt desc

3.查询最多人生日的方法：

select * from Worker where stdate in(
    select top 1 stdate from Worker group by stdate
    order by count(1) desc
)