SQL Server --索引、视图和事物

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，它将数据列中的数据按照一定方式有序编排起来，供搜索数据时使用，其作用是提高数据的检索速度，改善数据库的性能。

聚集索引

聚集索引（CLUSTERED INDEX）数据表中各行记录存放的物理顺序与索引的顺序相同，索引中键值的逻辑顺序决定了数据表中相应行的物理顺序。
例如，汉语字典的正文的顺序与按拼音检索的索引的顺序是一致的，因此拼音检索对于汉语字典就是聚集索引。按姓氏笔画顺序排列的电话簿中，按姓氏笔画检索电话的索引也是聚集索引。
每个表只能有一个聚集索引，因为数据的物理顺序只能按照一种方法进行排序。

非聚集索引

非聚集索引（NONCLUSTERED INDEX）非聚集索引建立在聚集索引之上，是在索引页中建立一个对聚集索引进行检索的聚集索引，它通过对聚集索引的检索来查询数据。

唯一索引

唯一索引（UNIQUE）表中两行记录在建立唯一索引的列不允许有相同的值。比如学号、身份证号等等不能重复的数据列上可以建立唯一索引。
如果某表的某数据列中已有重复的数据，那么将不允许在此列建立唯一索引。
创建了唯一约束的列将自动创建唯一索引。在聚集索引和非聚集索引列上都可以建立唯一索引。

主键索引

创建主键索引就是添加主键约束同时自动创建了一个同名的唯一索引。
SQL Server创建主键时将自动创建主键索引；删除主键时，则同时删除主键约束和对应的唯一索引。SQL Server的主键索引是聚集索引。

复合索引

在多个列上建立索引。
在已经建立了某种单一索引的数据列上可以再建立复合索引。
只有使用复合索引的第一列或者整个复合索引列作为条件进行查询时该索引才起作用。

作业

-- 创建索引
if exists (select name from sysindexes where name = 'IX_BANK')
 drop index bank.IX_BANK
GO
    create unique index IX_BANK on Bank(name)

--删除索引
drop index bank.IX_BANK 

-- 使用索引
select * from bank with(index = IX_BANK) where name like '%北京%'
select * from bank where name like '%北京%'

--上机练习9_1
--在学生成绩表Score的score列创建索引，并使用索引查询成绩在90分以上的学生姓名和成绩。
if exists (select name from sysindexes where name = 'IX_score')
    drop index bank.IX_score
create index IX_score on score(score)
GO
select sd.name, s.score from student sd, Score s
 where s.score > 90 and sd.studentid = s.studentid

课上写的东西

create unique index ix_name on student(name)

select * from sysindexes

create function getScore()
returns table 
return
select name, subjectname, score 
    from student stu, subject sub, score s 
        where stu.studentid = s.StudentID and s.SubjectId = sub.SubjectID

select * from dbo.getScore();
select * from dbo.getScore() where name = '金蝶'

视图

视图是从一个或几个基本表或其他视图中选择某些列而组成的表。
视图是虚拟表，因为在数据库系统仅存放了视图的定义，而不存放视图对应的数据，只有当使用视图时，视图才从基础表中查询数据。一般来说视图不应该含有表的全部列。
视图经常用来进行以下操作：
1、筛选若干个表或视图中的数据行。
2、防止未经许可的用户访问敏感数据。
3、将若干个基础表抽象为一个逻辑数据表。
使用视图的好处：
1、结果更容易理解。因为可以使用列别名使用户更容易理解查询结果的含义。
2、通过视图查询数据，用户无法完全了解基础表的结构，增加了系统安全性。
3、维护应用程序更容易，因为调试视图比调试查询更容易。

为什么需要视图

示例：

--求出平均工资最高的部门的编号和部门的平均工资
select top 1 deptno, avg(sal) "avg_sal" --不可移植 top在SQL server存在
    from emp 
    group by deptno
    order by avg_sal desc
--这样子太麻烦
select * 
    from(
        select deptno, avg(sal) "avg_sal" 
            from emp 
            group by deptno
    ) "T"
    where "T"."avg_sal" = (
        select max("E"."avg_sal") from (
            select deptno, avg(sal) "avg_sal" 
                from emp 
                group by deptno
        )"E" 
    )
----
create view v$_emp_1 
as 
    select deptno, avg(sal) "avg_sal"
        from emp
        group by deptno
 --使用视图后
select * from v$_emp_1
    where avg_sal = (select max(avg_sal) from v$_emp_1)

• 总结： 简化查询，避免了代码的冗余

什么是视图

• 视图从代码上看是一个 select 语句
• 视图从逻辑上被当作一个虚拟表看待

格式

create view 视图的名字
    as 
        -- 不能添加 begin
     	select 语句
        -- 不能添加 end

视图的优缺点

优点

简化查询，增加数据的保密性

课上写的东西

create view view_score
as
select name, subjectname, score 
    from student stu, subject sub, score s 
        where stu.studentid = s.StudentID and s.SubjectId = sub.SubjectID
go


select * from view_score where name = '金蝶'

事物

事物和线程的关系： 事物也是通过锁来解决很多问题的，线程同步也是通过锁来解决的

begin transaction
declare @err int
update bankcard set balance = balance+1000 where cardid = 10000003
select @err = @@error
update bankcard set balance = balance-1000 where cardid = 10000004
select @err = @err + @@error
if(@err <> 0)
    begin 
        print '转账失败'
        rollback transaction
    end
else 
    print '成功'
    commit