9月23日培训日记

今天由袁龙友老师继续讲数据库，我没有去听课，下面是张挺记录的培训日记。

回顾上次作业

根据上次留下的作业，回顾了建两个表，表间互相引用以及主外键约束，其中约束又分为表级约束和列级约束。怎样使一段脚本可以重复执行？需要先判断是否已经存在创建的数据库或者表，如果有，则先删除，但是考虑一种情况，两表间互相存在引用，则删不掉。此时正确的处理方式应该是，先删掉约束，再删表，具体办法见上次课的笔记。

T-SQL编程

Sql Server中的系统函数：时间和日期函数是比较重要的，在编程中经常会使用。

（1） GetDate()

（2） DateAdd()

（3） DateName()与DatePart()的区别？前者返回字符串，后者返回整数

（4） DateDiff()

一个具体问题：

表A是一个不断有新记录增加的表，字段t是记录插入的时间，现在想知道每10秒钟插入到该表的记录总数大于N条的时间和记录条数，并按由大到小的顺序排列，请写出完成该功能的语句。

解决思路：将开始插入的时间定为基准时间，求出每个时间与基准时间相差的秒数比上10的商，用求出的商来分组。

答案：select max(t) as maxT,min(t) as minT,count(*) as Num from dt group by datediff(ss,’<chsdate year="2006" month="9" day="23" islunardate="False" isrocdate="False" w:st="on">2006-9-23</chsdate>’,t)/10 having count(*)>N order by desc

如果用定义变量的方式，可以先定义一个基准时间@Start

declare @start datetime

select @start=min(t) from dt

……

作业：

用SQL语句求出一个选手的得分（10个评委，去掉一个最高分和一个最低分，再求平均分）

思路1：先定义两个变量最高分和最低分的id，排除这两个id，再求平均值

思路2：不用定义变量，总分减去一个最高分和一个最低分再除8

局部变量：储存临时值的变量

会话级的变量：通过classpath对比讲解，一个会话中的变量，不影响另外的会话

全局变量：全局的变量，影响整个库，如@@RowCount这个系统变量的值，会随着SQL语句的执行而改变

Sql Server中不能由用户定义全局变量，标准的SQL中定义了标准的全局变量，但是各个厂家的实现中，决大多数都不是通用的。

实现分页的SQL语句：（每页显示3条记录）

select top 3 * from grade where id not in (select top 3 ID from grade)

通用形式：

declare @pageIndex int, @pageSize int

set @pageSize=3 set @pageIndex=2

select top(@pageSize) * from grade where id not in(select top(@pageIndex-1)* @pageSize) ID from grade

declare @RecordNum int

select @RecordNum=Count(*) from grade

可以只用一条SQL语句实现分页，其策略为：

select * from grade select @@RowCount

(MySQL中分页可以使用limit限制取记录的行号)

使用top 分页，如果记录很多，越到后面分页越慢，性能差

跳页时选择性能好的分页

declare @id int

select top 3 @id=id from grade

select top 3 * from grade where id>@id

使用会话级变量

declare @id int

set RowCount 6

select @id=id from grade

select top 3 * from grade where id>@id（这样比子查询快很多）

会话级变量如果设置为0，则表示无此变量

查询数据远比传输数据的速度快，由此引出了面向连接的数据库编程的概念

如果将查询到的结果马上送入ResultSet，则是面向无连接的数据库编程，这样做的缺点是占内存资源

写脚本完成学生选课的功能，表结构如下图：

<shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></path><lock v:ext="edit" aspectratio="t"></lock></shapetype><shape id="_x0000_i1025" style="WIDTH: 237.75pt; HEIGHT: 118.5pt" type="#_x0000_t75"><imagedata src="file:///C:%5CDOCUME~1%5CIBM_USER%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.png" o:title=""></imagedata></shape>

查学生ID使用全局变量@@Identity，该全局变量显示标识列的值，向标识列插入数据，需要将变量Indetity_insert设置为on。

例如，set IDENTITY_INSERT grade on

可以设置标识列从某个值开始编号，使用DBCC CHECKIDENT语句，例如，从200开始编号，则dbcc checkident(grade,RESEED,199)

全局变量 @@error

每条SQL语句执行后，都要检查@@error变量，增，删，改的SQL语句执行后，系统都会设置@@error的值。

如：

if @@error=0,表示语句正确，则继续进行以后的语句，否则将破坏数据的完整性。

SQL语句执行后，要不要输出@@RowCount变量的值，可以设置set nocount on，这样就会关闭回显功能，但是还是会将值赋给@@RowCount变量。

SQL SERVER中引入了TRY……CATCH语句块

CATCH中可以用特定的系统函数（见帮助）

临时表及表级变量

变量中存单个的值，如果要存集合，可以存到表中，并且可以使用临时表，表名前有字符”#”，临时表保存在tempDB数据库中。会话窗口关闭时，临时表被删除。

临时表分全局临时表和会话级临时表，全局临时表前面有”##”，所有的会话军共享全局临时表。

临时表是有编号的。建临时表不要建索引。

临时表的应用：

应用临时表实现分页处理（百度就是用这种方式）

前面的几种方式每翻一页要查两次数据库，这种方式每增加一页查一次数据库。

create table #PageTable

(

NewID int identity(1,1),

ID int

)

insert into #PageTable(ID) select ID from Grade select a.* from Grade a join(select ID from #PageTable where NewID between 3 and 5) b on a.ID=b.ID

前面讲的几种方式要求表有单一主键，如果想按name页排序分页，将不能实现，应用临时表可解决该问题。

临时表也要写磁盘，比写内存要慢，考虑到性能，可以使用表级变量，定义表级变量：

Declare @Table Table

(

NewID int identity(1,1),

ID int

)

使用表级变量与临时表相同。

系统函数（字符串函数）

（1） 验证身份证号（分18位和15位两种，年龄在18-60岁之间）

使用逻辑表达式，

If(len(cardNo=15 and datediff(yyyy,subString(cardNo,7,6),getdate())>18 and datediff(yyyy,subString(cardNo,7,6),getdate())<60) or len(cardNo=18……)

图书馆借书的实际案例

一个图书馆借书的规定是：科技类的书可以借30天，文艺类的45天，其它的书20天

用SQL语句查询出，三天之类即将过期的图书的借阅记录。

Declare @userID int,@bookType varchar(20),@H_Date datetime

If @bookType=’文艺’ select @H_Date=DateAdd(dd,getDate(),45)

Else if ……

Case……when语句

Select Name,(case when sex=1 then ‘男’ else ‘女’end) as sex from student]