12月2日——培训第11天

今天是周六，由袁龙友老师讲解SQLServer，以后这样的来自企业的老师可能会陆续前来吧，虽然
今天不是田老师上课，但是他也来了，难道他真的是这一期的主要负责人？张老师讲课的时间又能
有多少呢？当时可是有70％的因素是因为张孝祥才过来这里的，如果他讲课的时间要是连整个培训上课
时间的60％都没有的话……（当然很可能没有了）就有点亏了。

袁老师看上去很冷静，很内敛的样子，没想到讲起课来激情四射，语言表达能力方面，临场演讲能力
较田老师要强的多（个人感觉，但是他只是讲这两天而已……）
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
上午课程开始：

袁龙友的一个遭遇：他的软件开发主要涉及煤矿方面的系统开发问题，需要正版的SQLServer数据库，
               但是那些煤矿客户老板们认为：我找你开发软件，但是你却需要用别人的软件，那么
      我为什么还要找你作这个软件呢？我找那个开发软件的人作不就可以了么？？

上面的问题其实是没有区分出系统软件和应用软件的区别。

典型数据库概念：
数据库和数据库管理系统。其实oracle或是sqlserver叫做数据库管理系统，是专门管理数据的，
我们开发基本上是基于这些管理系统之上的应用软件。

问题：
培训的目的是软件开发，那么未来作出的系统应该是什么样子呢？比如一个人力资源管理系统，
由哪几部分组成？

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
回到正题上来：

oracle数据库最难，但是以后要面对的数据库是多样的，数据库平台的选择权利不在你，而在你的上层
领导和客户需求，基本sql语句袁老师不会再介绍，主要分三个层次讲述：

1 把sqlserver“玩”起来，同时想一想底层发生了一些什么事情……也就是sqlserver数据库体系结构，
  这一部分偏重理论
从体系结构上来说，如果要建立一个系统的话，形式有：
一 基于大型主机的计算模式：
  一个大型高级机器连接着一系列的终端，那么终端和pc机的区别在什么地方呢？pc具备计算能力，
  但是终端不具备，它只具备输入输出能力。比如银行系统采用的就是这种模式，每个银行都有一台主机，
  上面是数据库，每个操作员的电脑都是一台终端。

二 基于客户激活服务器的体系结构C/S模式
   从专业角度来说，这个模式还没有被淘汰，服务器端提供服务，客户端提供相应。比如oracle，
sqlplus就是客户端，oracle服务器就是服务器端。

对于sqlserver来说，客户端是诸如企业管理器、查询分析器、事件探查器、服务管理器等。

sqlserve中数据表的存放的物理方式：
与oracle有很大的区别：一部分放数据，一部分放日志，这两部分是分开存放的。日志文件最好要和数据
文件分开，免得和数据文件一起丢失。mdf用来存储具体数据文件，ldf是日志文件，sqlserver引入了
文件组的概念，一个文件组可以存放多个数据文件。

Window操作系统如果是fat32的话，文件最大是2G，ntfs系统最大可以超过4G

如果数据库过于庞大的话，不要把这个过大的数据文件放到一个数据库系统中，I/O操作是排他性的，
写入数据时其他不能访问，所以出现了排队的问题，如果仅仅把数据放一个文件中的话，那么排队等待
时间就太长了，这就是引入文件组的原因，排队也是按照文件组进行排队的。经常访问的数据和同时访问
的数据要尽量的分开，有可能同时用的数据要放到不同的文件组中去，这样排队的时间会尽可能的少。
比如人员和人员附加信息表这两拨数据要分到不同的文件组中去。

oracle中用的不是文件组，是表空间。

内存大小，cpu速度，磁盘速度，磁盘大小这四个因素哪个对数据库系统来说最重要？
也就是说哪个因素是数据库性能的瓶颈？

其实是磁盘速度。数据库的作用就是把磁盘中东西读到内存中然后处理完毕后提交上去，操作系统能管理
的内存也就是几GB，但是超过最大内存容量的数据库很普遍，所以瓶颈还是在磁盘上面

sqlserver数据库存放数据用的是页，每8个连续的页叫做一个盘区，每页有若干个数据行。sqlserver
每次读8页，这样减少了读盘的次数，读上来后放入内存缓存，sqlserver中一个页是8kb，页与页之间形成
一个双向链表，首尾相连。

数据库系统日志作用：
怎样恢复数据的？里面都记录了些什么东西？

记录数据操作和被操作的数据，一般来说数据文件>日志文件，插入或是删除操作时，日志中不但要记录
操作还要记录数据，如果专门是插入的话，而且如果更新的次数太多的话，那么数据文件不会怎么改变，
但是日志文件会越来越大，sqlserver采用归档模式，用5个日志文件来记录，如果5个都记录满了的话，
就开始覆盖第一个日志文件，继续覆盖的方法重新存储这5个日志。

修改数据库时候，客户端发送一个update命令，数据库服务器收到update请求，数据库引擎首先会把
表里面的数据先按照8页一个盘区的方式读到缓存里面，读入缓冲区后，就要在硬盘的日志文件中记录数据
修改的操作过程以及原始参数，然后根据客户端的要求修改缓冲区中的数据，最后检查点程序向数据库中
写入已经提交的事务（一次或者多次数据的修改，也就是批处理数据的修改，这样减少写盘的次数，
提高效率）。如果内存里面的数据经过了修改，但是没有写入磁盘，也就是没有和磁盘里面的数据同步，
这就是脏数据了。

当你的数据在内存当中但是还没有写入硬盘的数据库中的时候，突然断电了，假如这时候有个银行帐户的
转帐，如果发生这种事情的话，银行系统一般如何处理这种情况呢？以前是通过填写单子的方式来保证
避免这种问题，现在都是通过日志来保证的，因为先是写入日志，然后才在内存中修改数据，这里面有个
叫做检查点进程的东西，这个checkpoint进程如果没有写入日志的话，证明没有写入硬盘，下一次启动
系统后，会检查日志中的没有checkpoint进程的记录，然后把他们再重新作一遍。

oracle中用的是还原模式，不是归档模式，如果执行delete from删除的话，如果是完全还原模式的话
，删除的所有数据都必须记录日志，sqlserver中全表的删除是不写日志的，这是简单的还原模式，
无法还原了。

sqlserver四种版本：
企业版、专业版、个人版、移动版
sqlserver一开始是和sybase公司合作的，但是后来由于sybase公司要在unix、linux和window上
都作sqlserver，但是microsoft只在windows下面作，于是分道扬镳了，IBM的DB2现在已经超过了
oracle，银行方面全被ibm垄断了，电信方面是oracle垄断，sqlserver侧重于中小型企业，便宜，
能用盗版，且和操作系统整合的很好。

企业版只能在服务器系统中用，就是server版系统才能装。
开发人员版的话可以在professional上装，应该没什么太大的问题。

sqlserver和oracle都是典型的C/S模式，现在的问题是机房里面所有的系统都是2000专业版，所以
绝对装不了企业版本的sqlserver，没办法只能用王元华的评估版本。现在正在用飞鸽传输软件给每个人
传送安装软件。

三 B/S三层架构模式

－－－－－－－－－－－－－－－－－－－－－－－－－－－
现在王元华正在安装评估版本，老师在旁边指导安装，我们这边还在用飞鸽传输过程中，传送速度仅仅
达到100kb，现在才传送了几十MB而已，一共要1G多，这要等到什么时候呢？拭目以待吧……
－－－－－－－－－－－－－－－－－－－－－－－－－－－－

sqlserver安装：
安装的实例instance：如果用的不是默认安装实例的话，而是取了一个实例名aaa，
                  那么在连接字符串的url中，要在Server=localhost后面加上一个/aaa
      也就是Servle=localhost/aaa就行了，如果是默认实例安装的话，
      直接就是Server=localhost就可以了。

oracle中一个实例对应一个数据库，每个数据库对应多个schema，每个schema对应多个表
        oracle中一个用户对应一个schema，用户被删除之前必须把这个用户所对应的表全部删除了。

sqlserver中一个实例对应多个数据库，每个数据库中对应多个表，每个表都放在了dbo这个schema中
        ，在2000版本中用户和schema是一一对应的，但是这种情况下，
        用户删除了，schema也就被删除了。

sqlserver中登陆名要和用户一一对应起来，用户要和角色关联起来，用户可以有多个角色，用户也可以
拥有多个Schema，每个Schema下面有多个表。

用户名存在于数据库的范围里面，每个用户拥有一个默认的schema

数据库包含schema，shema包含表。
database.schema.table

创建登陆名，将之赋予某一个用户，该用户可位于几个角色里面，他也可以拥有几个Schema

有权限登陆，不一定有权利操作。

－－－－－－－－－sqlserver的系统数据库：

master、model、msdb、tempdb
master：里面有一切创建了的数据库的信息，master数据库一定要备份
model：模板
msdb：放的一些自动执行计划
tempdb：临时表都创建在这里面。

后面讲的一些东西涉及具体的设置和sqlserver的一些特性，这些东西原理性太强，和实践联系
非常紧密，听不大懂，记录下来也没有意义，唉……确实不可能都听懂，这些东西不实际开发的话，
鬼知道是怎么回事!

----------------------------------------------------------
看来中午要装一下sqlserver2005的客户端了
试了一下，没有装上……大家也都装不上
----------------------------------------------------------
下午课程开始：

先装开发版，再装企业版管理工具，注册时候如果装的是个人版本的话，那么应该使用域帐户比较好，
而且注意新建查询的时候要切换到你想要使用的数据库才可以进行sql语句。

T－SQL编程（oracle中叫做P-SQL）
创建存储过程：
create proc YuanProc //YuanProc是存储过程名称
as
begin
 //代码块
 select * from Article
 select * from Grade

end

计分器：多个人给一个选手打分，去掉一个最高分，去掉一个最低分，然后取平均值。
select max(Score),min(Score) from Grade 取出最高分和最低分

select sum(Score) as Total,max(Score),min(Score),count(*) as Number from Grade
求出了总分，最高分，最低分，和总人数

也就是
select (sum(Score)-max(Score)-min(Score))/(count(*)-2) from Grade

如果还要知道打最高分的评委和打最低分的评委，还要知道最高分和最低分的话，一句话就已经
出不来了。虽然逻辑很简单，但是如果要用一句话很难搞定的……

其实也很简单，多来几句sql，把它们组合起来就ok了。

T_SQL里面的变量声明：
下面这堆代码可以放在存储过程的代码块中！！
declare @MaxScore float,
        @MinScore float,
  @MaxJudge varchar(20),
  @MinJudge varchar(20)

select @MaxScore = Max(Score),@MinScore=Min(Score) from Grade
select @MaxJudge = Judge from Grade where Score = @MaxScore
select @MinJudge = Judge from Grade where Score = @MinScore
select (sum(Score)-@MaxScore-@MinScore)/(count(*)-2) as AverScore,
     @MaxScore,@MinScore,@MaxJudge,@MinJudge from Grade

然后用exec YuanProc就可以执行出结果了。

－－－－－－－－－－－
if (object_id('YuanProc') is not null)
{
 drop proc YuanProc
}
go //相当于oracle语句的分号，也就是批处理

create proc YuanProc(@Player varchar(20))
as
begin
 
declare @MaxScore float,
        @MinScore float,
  @MaxJudge varchar(20),
  @MinJudge varchar(20)

select @MaxScore = Max(Score),@MinScore=Min(Score) from Grade where Player=@Player
select @MaxJudge = Judge from Grade where Score = @MaxScore and Player=@Player
select @MinJudge = Judge from Grade where Score = @MinScore and Player=@Player
select (sum(Score)-@MaxScore-@MinScore)/(count(*)-2) as AverScore,
     @MaxScore,@MinScore,@MaxJudge,@MinJudge from Grade

end

exec YuanProc '选手一' 即可执行
－－－－－－－－－－－－－－－－－－－－－－－－－
现在要写一个新的存储过程实现分页的效果：

你看第几页我就把第几页的数据给你，其实就是这个效果！

oracle里面可以用rownumber这个伪列来实现分页，sqlserver中没有伪列。

select top 3 * from Grade where Player='选手一' 第一页的数据

select top 3 * from Grade where Player='选手一' and ID not in
        (select top 3 ID from Grade where player='选手一')
上面那句话就达到了跳过第一页的三个之后的前三个，也就是第四个到第六个。

分页的变量：每页的数量pageSize，总记录数量TotalCount，总页数PageCount，
    当前的页数CurPage
          这四个东西是分页需要考虑的重要的变量！！

分页如果写入存储过程的话，是十分方便的：

//CurPage 是当前的页数
if (object_id('GetPlayerScoreListByPage') is not null)
{
 drop proc GetPlayerScoreListByPage
}
go //相当于oracle语句的分号，也就是批处理

create proc GetPlayerScoreListByPage(@Player varchar(20),CurPage int,
                                     @PageSize int )
as
Begin
  if @CurPage=1
 select top (@PageSize) * from Grade where Player=@Player
  else
  select top (@PageSize) * from Grade where Player=@Player and ID not in
        (select top ((@CurPage-1)*(@PageSize)) ID from Grade where player=@Player)
End

exec GetPlayerScoreListByPage '选手一'

------------------------------------------

create proc GetPlayerScoreListByPage(@Player varchar(20),CurPage int,
                                     @PageSize int,  @TotalRec int output )
as
Begin
  if @CurPage=1
 select top (@PageSize) * from Grade where Player=@Player
  else
  select top (@PageSize) * from Grade where Player=@Player and ID not in
        (select top ((@CurPage-1)*(@PageSize)) ID from Grade where player=@Player)
  select @TotalRec=Count(*) from Grade where Player=@Player

End

declare @TotalRec int
exec GetPlayerScoreListByPage '选手一',2,6, @TotalRec output
print @TotalRec

－－－－－－－－－－－－－－－－－－－－－－－－－－－－
上面这个分页的代码非常非常的重要

－－－－－－－－－－－－－－－－－－－－－－－－－－－
上面的分页还不是最好的，还可以进一步改进，因为上面的那个存储过程还不是通用
比如top函数仅仅在sql2005中可以用！但是在2000中top不是函数，在2000中
top后面必须跟常数。

execute('select * from') : 动态执行sql语句
top只在微软中的sql中有，其他数据库，mysql和oracle中都没有，所以找一个别的通用的分页方法
比较好，让这个分页在oracle和sql中都通用，有没有办法呢？

RowCount是会话级（session）的变量
set RowCount 4 (RowCount这种东西在oracle和sql中都有，在mysql中叫做limit)
select * from Grade
这样一执行的话，select语句只能返回4条记录了……

现在利用RowCount来设计分页的方法，第一种分页方法用了子查询，子查询的in不能使用索引。
现在的策略：

set RowCount 0 //不限制select的返回记录数量。
declare @ID int
select top 3 @ID=ID from Grade  //取第一页的前三行，最后的第三行ID数值被赋给了@ID
        //但是这对主键的要求比较高，要求必须升序且不跳数。
print @ID
上面的代码结果是3。

这时如果执行下面的语句
select top 3 * from Grade where ID>@ID 那么返回的是4到6行的语句

－－－－－－－－－－－－－－－
还有一种方法是游标的方法来实现分页……

其中set RowCount的方法最好，子查询的效率最差，其次差的是游标分页
－－－－－－－－－－－－－－－－－－－－－－－－－－－－

索引对管理开发的影响：

加快查询速度，但是假如数据库中总共就有一列姓名，这就没有必要加载索引了，索引使用的首要原则是
数据比索引多很多，这时索引才有意义，否则如果索引大于数据大小的话就本末倒置了

还有就是字段重复率太高了或是列的可选择的不同值太少了，比如性别。
还有就是列基本上不怎么用，也不必建立索引。
还有如果一个字段宽度大于了100的话，从开发角度来讲就不必建立索引了，如果非要针对这种情况建立索引的话，
   建立出的是全文索引。

如果一个列被插入数据过于频繁的话，也不建立索引。因为不仅要在数据页中加入数据，还要在索引中恰当的位置加入那个
刚刚插入的数，比较浪费时间。

索引的填充因子：索引是按照页来存储的，最好每一页都不填满，这样如果要往恰当的页插入数据的话，
             不至于因为每个页面都满了而需要在页之间来回移动（把每个页尾的最后一条记录拿到
    下一页的页首，循环下去，直到想要的页面空出了一个位置来保存那个数据）。

但是即便索引设置了填充因子，索引终究会有一天所有的页都满了，这时需要索引重建，恢复索引的填充因子。

索引虽然能够大大优化查询速度，但是对于更新、删除、插入来说很慢。

1 经常用于查询的列，经常用于连接的列要建立索引。
2 表的记录数量不多的话（比如少于4千行的记录数）不建立索引
3 这一列的可取值如果太少的话不建立索引
4 这一列的数据类型字段长度太大的话不建立普通索引，而是建立全文索引。
5 经常进行插入、修改操作的表也不建立索引。

聚簇索引：数据存储的物理顺序和数据在索引中的顺序一致
非聚簇索引：上述两者顺序不一致
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
sql里面的一些函数

函数分类：
系统函数：
1 字符串函数：Left,Len,Char,ASCII,Ltrim,Rtrim,Str
            Str,Substring,Replace,Space等等

例子：自动生成编号：

create table Person
{
 id int identity(1,1) primary key,
 CardNo varchar(18),
 Name nvarchar(10)
}
insert into values(CardNo,Name) values('422423197504124559','袁龙友')
插入袁老师的身份证号码
select * from Person

注意：422423197504124559去掉前6位和后4位，得到的是19750412，这就是袁老师的
出生日期，存储过程要求生成一个身份证号码，前6位保持不变，而且后4位要加上1，
中间的是生日。

这个存储过程给了前6位号，中间8位号，我们只要顾及最后4位号就可以了。
if object_id('GenNewCardNo') is not null
  drop proc GenNewCardNo
go

create proc GenNewCardNo(@RegionCode varchar(6),@Birthday varchar(8))
as
begin
   declare @CardNo varchar(18)
  
   select @CardNo=@RegionCode+@Birthday+replace(str(max(Right(CardNo,4))+1,4),' ','0')
     from Person where left(CardNo,4) = @RegionCode+@Birthday
 
 insert into Person(CardNo) values(@CardNo)

end

exec GenNewCardNo '422423','19750412'

如果你是符合条件的第一个人的话，那么where条件还不成立呢，因为还没有满足条件的记录呢！那么如果这样的话
刚才的存储过程就无法得到记录了！上面的存储过程无法处理当天第一个出生的人！

可以设置一个号码，如果真的是空的话，给它置为1。
begin
   declare @CardNo varchar(18)
   @Num int
 select @Num = max(right(CardNo,4))+1 from Person where left(CardNo,4) = @RegionCode+@Birthday
 if @Num is null //如果确实是当天第一个出生的人，那么肯定数据库中没有相应的身份证记录，那么@Num必然为空！
  set @Num=1
 
   select @CardNo=@RegionCode+@Birthday+replace(str(@Num,4),' ','0')
    
 
 insert into Person(CardNo) values(@CardNo)
 select @CardNo
end

exec GenNewCardNo '422421','19750412'

 

2 时间日期函数：

有一个监测系统不定期间隔采集数据，举个例子来说，是这样的
假如有一个table，名字叫做statistics
时间(秒)   数据
 Time     Data
  1        13
  5        12
  10       1
  15       2
  20       6
  30       7

  40       17
  50       82
  60       1

  61       12
  65       22

其实就是这个意思，上面的时间段如果以30秒为一个分为一个时间区段的话，
那么一共有三个区段，每个区段都采集一定的数据，这些每个区段的数据
都可以取出平均值，比如第一个区段
就是（13＋12＋1＋2＋6＋7）/6 可以得到一个平均值，
同理其他的区段也可以类似的得到相应的数据平均值

现在的问题是这样，我设一个值是x，现在想知道哪些区段的数据平均值
大于这个x，如果有的话，那么我要得到那些区段的起始时间和对应的数据平均值。

比如，如果第一个区段满足条件的话，也就是第一个区段的数据平均值大于那个
我们给出的x

那么我要得出 下列结果
起始时间（秒）      结束时间（秒）   平均值
   1               30             6

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
思路：首先要对时间区段进行分组，只有这样才能得到每一时间区段的平均值，
才能和具体的数值去进行比较

select round(avg(value)) as averageValue from statistics group by trunc(Time/30);
上面的语句可以打印出按照时间区分出的每个时间段采集到的平均值来。

select max(time),min(time), trunc(time/30),round(avg(value))  from statistics group by
trunc(Time/30) having round(avg(value)) > 7  ;

这样就达到了需求。