SQLServer学习笔记1

--分页查询
--查询前两个
select top 2* from #c;
--查询前50%
select top 50 percent * from #c;
--分页，从第三个开始，每页显示2个
/*
ROW_NUMBER() OVER (ORDER BY name1)rownumber
根据name1字段排序生成自定义的rownumber列
rownumber>2 也就是从rownumber=3开始显示
TOP 2* 只显示前两个*/
select top 2* from (select row_number() over(order by name1)rownumber,* from #c)temp 
where rownumber >2;

select *,count(C#) over() from SC;

select *,rank()over(order by score desc)排名 from SC;

select *,dense_rank()over(order by score desc)排名 from SC;

select * from (select *,rank() over(partition by C# order by score desc)A from SC)B 
where B.A<=3;

'Chef Anton''s Cajun Seasoning'

select * from Products where ProductName between 'Carnarvon Tigers' 
and 'Mozzarella' order by ProductName;

--定义一个变量
declare @Country varhcar(20);
--赋值
set @Country = 'Germany';
--查询Country为Germany的信息
select * from Customers where Country = @Country;
--查询CustomerID = 1的Country的值 赋值给@Country
select @Country = Country from Customers where CustomerID =1;
--if else 判断
declare @flag int;
set @flag = 10;
if @flag>5
begin --开始相当于大括号
    print '你好'
end --结束，如果不写，则只能写一条语句，和JAVA等高级语言类似
else
    print 'hello'

--while 循环
--建表
create table Score (
    Code int primary key,
    Degree int not null,
    Sex varchar(3)
);
--插入数据略。。。
declare @degree decimal(18,2)  --定义一个变量接受最高分
/*
      18:表示定点精度，小数点左边和右边可以存储的十进制数字的最大个数，最大精度为38
      2：表示小数位数，小数点右边可以存储的十进制数字的最大个数。
       小数位必须是0~18之间（18是上面规定的定点精度）。默认的小数位是0。
*/
select @degree = MAX(Degree) from Score; --存下最高分
declare @code int; --定义一个变量接收学号
select @code = Code from Score where Degree = @degree; --存下最高分的学号
declare @sex varchar(3);
select @sex = Sex from Score where Code = @code;
--存入最高分学号对应的学生性别
if @sex = '男'
    print '这是一个男同学'
else
    print '这是一个女同学'
while @degree > 90 --while循环
begin
    print '优秀！'
    break --打断循环
end

declare @degree int;
set @degree = 90;
while @degree <95
begin
    print '考得很好！'
    set @degree = @degree + 1
    if @degree = 93 --当@degree = 93时，跳出循环
        break
end

--建表
create table Student (
   Sid int primary key,
   Age int not null 
);
--数据插入略。。。
select Age,
    case Age
        when 19 then '青年'
        when 18 then '小伙'
        else '少年'
    end
from Student;

select C#,(convert(decimal(5,2),sum(case when score >=60 then 1 else 0 end)*1.00/count(*))*100)及格率 from SC group by C#;

alter trigger trigger_学生_Delete 
on 学生
instead of Delete
as 
begin 
select 学号, 姓名 from deleted
end 
delete from 学生 where 学号 = 4

--建表
create table Classes (
   ClassID int primary key,
   ClassName varchar(20) not null,
   Counts int not null  
);
--判断触发器是否存在，有则删除
if(object_id('tgr_classes_insert','TR')is not null)
drop trigger tgr_classes_insert
go
--创建触发器，每当一条数据被插入时，会另外再插入一条数据
create trigger trg_classes_insert
on Classes --触发器应用于Classes表
for inert --插入数据时触发
as
--定义变量
declare @Cid int,@Cname varchar(20),@Count int;
--在inserted表中查询已插入的信息，并给变量赋值
select @Cid = ClassID+1,@Cname = ClassName,@Counts = Counts +3 from inserted;
insert into Classes --插入数据
values (
           @Cid,
           --将Cid转为varchar，同时截取‘班’（截取下标基于1，截取长度），+号执行字符串拼接
           convert(varchar,@Cid)+substring(@Cname,2,1),
           @Counts
          )    
print '添加数据成功'
go
--该触发器的的作用就是Classes表插入数据时，会再向表中插入一条数据，
实用情况下，可以再向一张表插入数据时，同时给另一张表也插入一条数据
insert into Classes values(1,'1班',20);--触发器被触发

--删除数据时备份
if(object_id('tgr_classes_delete','TR')is not null)
drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on Classes
for delete
as 
print '备份数据中...'
if(object_id('ClassesBackUp','U')is not null)
--如果有备份表，直接插入数据
--将deleted表中的数据复制
insert into ClassesBackUp select * from Deleted;
--如果没有备份表，创建表再插入数据
else
select * into ClassesBackUp from Deleted;
print '备份数据成功！'
go
delete Classes where ClassID = 2;--delete触发器执行
select * from Classes;
select * from ClassesBackUp;

select * from sysobjects where xtype = 'TR' and parent_obj = object_obj('tablename');

select * from sysobjects where xtype = 'TR'；