SQL_Server_Day05_T_SQL高级2

1.    表表达式（将结果集作为数据源）
    1）派生表（将结果集当作一张表来使用）
    select * from (结果集) as 表名[(列名)]
    
    可以用来做分页，
    第一种分页的sql语句：    m-->第m页  n-->每页n个内容
    select top n
        *
    from
        MyTestDB..Student
    where
        stuId not in (
            select top ((m-1)*n) stuId from MyTestDB..Student order by stuId
        )
    order by
        stuId    
        
    第二种分页的sql语句：-->使用ROW_NUMBER()函数获得连续的id的自增列，根据自增列进行筛选    
    m-->第m页  n-->每页n个内容
    select
        *
    from    
        (
            select
                ROW_NUMBER() over(order by stuId) as num
                , *
            from
                MyTestDB..Student
        )     as tbl
    where
        tbl.num between (m-1)*n-1 and m*n
    
    使用派生表的问题：造成肚子非常大，头和脚太小 --> 公用表表达式
    
    
    2）公共表表达式CTE(Common Table Expression)
    with 别名[(字段)]
    as
    (
        结果集
    )
    查询
    
    3）视图-->持久化了，避免了每次都要执行sql语句
    把结果集以别名的方式持久化到数据库中
    create view vw_视图名
    as
        结果集
    使用视图的好处：
    用户角色是分配权限用的，架构是用来提供访问权限的，
    我们的原始数据在一个架构下，而创建出的视图在另一个架构下，
    分配权限只分配视图的权限，即用户可以访问数据库，但只能访问某个架构下的视图，
    原始表是看不到的，架构和视图的优势结合在一起体现
        
    4）内联表值函数（带有参数的视图）
    视图是不能存变量的，但有一个与视图一模一样的东西是可以存变量的
    create function fn_函数名
    （@变量 as 类型） returns table
    as
        return
            结果集
            
    案例--使用表值函数进行分页
    
2.    基于T-SQL的编程
    什么是编程？
    声明变量，使用变量，调用函数，做逻辑判断和循环处理
    
    声明变量
    declare @变量名 类型名；
    set @变量名 = 值；
    select @变量名
    
    @@系统变量

    逻辑判断
    if ..
    begin
        ...
    end
    else
    begin
        ...
    end
    
    循环处理
    while ...
    begin
        ...
    end
    
    利用T-SQL的编程，就可以写sql的脚本了。比如说满足什么什么条件，我创建一个什么什么东西

3.    事务-->最难的东西
    满足原子性、一致性、持久性、隔离性的操作就称为事务
    原子性：不能再分，只能同时成功或失败
    一致性：无论在操作中创建多少个副本，所有的数据都是一致的
    隔离性：两个事务之间是独立的，读一张表和写一张表是不能同时进行的，涉及到等待，锁
    持久性：事务执行完成之后，数据就保存下来了
    
    实际上，之前我们的每条sql语句就都是事务
    
    操作事务
    显式开启事务
    begin transaction
    
    关闭事务
    commit     事务提交
    rollback    事务回滚 事务所做的操作全部取消
    系统变量@@error  记录最近一次的错误码
    
    案例--银行转账的案例
    begin transaction
        declare @money money;
        set @money = 1000;
        
        declare @myError int;
        set @myError = 0;
        
        update bank set balance = balance - @money where cid = '0001'
        set @myError = @myError + @@error
        update bank set balance = balance + @money where cid = '0002'
        set @myError = @myError + @@error
        
        if @myError > 0
            begin
                rollback;
            end
        else
            begin
                commit;
            end
            
    利用ADO.NET执行事务
    using(SqlConnection)
    {
        using(SqlCommand)
        {
            conn.Open();
            //连接开启事务
            SqlTransaction st = conn.BeginTransaction();
            //将事务与cmd对象绑定
            cmd.Transaction = st;
            //执行事务
            cmd.ExecuteNonQuery();
            st.commit();
        }
    }

4.    存储过程 存储在数据库中的一步一步执行的sql语句
    以sp开头  sp_....  StoredProcedure
    sp_databases sp_tables sp_columns
    
    1）不带参数的存储过程  --> 产生一个存储过程(可编程型/存储过程) 一般用视图来实现
    go
        create proc usp_...
        as
        begin
            --sql语句
        end
    go
    
    执行存储过程 exec usp_...
    
    2）带有参数的存储过程 --> 有些像内联表值函数
    create proc usp_分页
    @pageIndex int
    @pageCount int
    as
    begin
        --
    end
    
    3）带有默认参数的存储过程
    
    4）带有返回值的存储过程
    
    使用T-SQL编程和使用ADO.NET编程  本质是一样的
    只是一个把逻辑放在sql语句上，一个把逻辑放在ADO.NET上
    
    使用T-SQL编程的好处是 sql语句放到数据库中，这样省去了词法解析的过程
    劣势是违背了分层理论
    
    案例：在ADO.NET上使用sql存储过程
    SqlCommand cmd = new SqlCommand("usp_分页",conn);
    cmd.CommandType = CommandType.storedProcedure;
    
5.    触发器
    是一个特殊的存储过程
    instead of 触发器  --> 代替原有功能
    after 触发器 -->
    临时表
        inserted和deleted
        inserted是存储 插入修改更新数据的临时表
        
    
    
    针对什么进行触发的？增加、删除与修改
    create trigger tr_update|delete|insert_触发器名字 on 表名
    for    | instead of | after
    动作（update|delete|insert）
    as
    begin
        sql语句
    end